แบ่งตารางขนาดใหญ่ด้วย Partitioning บน MySQL

เมื่อตารางโตถึงหลายสิบล้านแถว ปัญหาที่ตามมาไม่ใช่แค่ Query ช้า แต่คือการลบข้อมูลเก่าที่ใช้เวลาเป็นชั่วโมงและล็อกตารางจนระบบใช้งานไม่ได้ Partitioning แก้ทั้งสองเรื่องด้วยการแบ่งตารางออกเป็นส่วนย่อยที่จัดการแยกกันได้

ประโยชน์ที่ชัดเจนที่สุดคือการลบข้อมูลเก่า จากคำสั่ง DELETE ที่ใช้เวลาหลายชั่วโมง กลายเป็น DROP PARTITION ที่เสร็จในไม่กี่วินาที

สิ่งที่ต้องเตรียม

• MySQL 8.0 หรือ MariaDB 10.5 ขึ้นไป
• ตารางขนาดใหญ่ที่มีคอลัมน์วันที่ซึ่งใช้กรองเป็นประจำ
• สำรองข้อมูลก่อนเสมอ การแปลงตารางเป็นแบบแบ่งส่วนต้องเขียนข้อมูลใหม่ทั้งตาราง

ข้อจำกัดที่ต้องรู้ก่อนตัดสินใจ คอลัมน์ที่ใช้แบ่งต้องเป็นส่วนหนึ่งของ Primary Key และ Unique Key ทุกตัวในตาราง ซึ่งเป็นข้อจำกัดที่ทำให้หลายตารางแบ่งไม่ได้โดยไม่แก้โครงสร้างก่อน

ขั้นตอนที่ 1: ตรวจขนาดตารางปัจจุบัน

SELECT table_name,
       table_rows,
       ROUND(data_length/1024/1024) AS data_mb,
       ROUND(index_length/1024/1024) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'shop'
ORDER BY data_length DESC
LIMIT 10;

ขั้นตอนที่ 2: ออกแบบการแบ่ง

สำหรับข้อมูลที่มีลักษณะเป็นเหตุการณ์ตามเวลา เช่น Log คำสั่งซื้อ หรือธุรกรรม การแบ่งตามช่วงเดือนเป็นรูปแบบที่ใช้ได้ดีที่สุด

CREATE TABLE access_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  path VARCHAR(255) NOT NULL,
  status SMALLINT NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, created_at),
  KEY idx_user_created (user_id, created_at)
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
  PARTITION p202609 VALUES LESS THAN (TO_DAYS('2026-10-01')),
  PARTITION p202610 VALUES LESS THAN (TO_DAYS('2026-11-01')),
  PARTITION p202611 VALUES LESS THAN (TO_DAYS('2026-12-01')),
  PARTITION pmax    VALUES LESS THAN MAXVALUE
);

สังเกตว่า Primary Key เป็น (id, created_at) ไม่ใช่ id เพียงตัวเดียว เพราะข้อจำกัดที่กล่าวไว้ข้างต้น

พาร์ทิชัน pmax ที่ท้ายสุดรับข้อมูลที่เกินช่วงที่กำหนดไว้ ป้องกันข้อผิดพลาดเมื่อลืมเพิ่มพาร์ทิชันใหม่ แต่ต้องเฝ้าดูไม่ให้มันโตจนกลายเป็นปัญหาเสียเอง

ขั้นตอนที่ 3: แปลงตารางที่มีอยู่แล้ว

-- ตรวจว่า Primary Key มีคอลัมน์ที่จะใช้แบ่งหรือยัง
SHOW CREATE TABLE access_log\G

-- ปรับ Primary Key ก่อน
ALTER TABLE access_log DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);

-- แล้วจึงแบ่ง
ALTER TABLE access_log
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
  PARTITION p202609 VALUES LESS THAN (TO_DAYS('2026-10-01')),
  PARTITION pmax    VALUES LESS THAN MAXVALUE
);

การแปลงเขียนข้อมูลใหม่ทั้งตาราง จึงใช้เวลานานและใช้พื้นที่ดิสก์ชั่วคราวเท่ากับขนาดตาราง ทำในช่วงที่ปิดระบบได้เท่านั้น

ขั้นตอนที่ 4: ตรวจว่า Query ตัดพาร์ทิชันได้จริง

ประโยชน์ทั้งหมดขึ้นอยู่กับว่าฐานข้อมูลรู้หรือไม่ว่าต้องอ่านพาร์ทิชันไหน ตรวจด้วย EXPLAIN

EXPLAIN SELECT COUNT(*) FROM access_log
WHERE created_at >= '2026-09-01' AND created_at < '2026-10-01';

ดูคอลัมน์ partitions ควรแสดงเพียง p202609 ไม่ใช่ทุกพาร์ทิชัน

-- ผิด ใช้ฟังก์ชันครอบคอลัมน์ที่ใช้แบ่ง ทำให้ต้องอ่านทุกพาร์ทิชัน
EXPLAIN SELECT COUNT(*) FROM access_log WHERE YEAR(created_at) = 2026;

-- ถูก เขียนเป็นช่วง
EXPLAIN SELECT COUNT(*) FROM access_log
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

ดูข้อมูลของแต่ละพาร์ทิชัน

SELECT partition_name, table_rows,
       ROUND(data_length/1024/1024) AS data_mb
FROM information_schema.partitions
WHERE table_schema = 'shop' AND table_name = 'access_log'
ORDER BY partition_ordinal_position;

ขั้นตอนที่ 5: เพิ่มพาร์ทิชันใหม่ตามรอบ

เนื่องจากมี pmax อยู่ท้ายสุด ต้องแยกออกก่อนแล้วเพิ่มใหม่

ALTER TABLE access_log REORGANIZE PARTITION pmax INTO (
  PARTITION p202612 VALUES LESS THAN (TO_DAYS('2027-01-01')),
  PARTITION pmax    VALUES LESS THAN MAXVALUE
);

ทำอัตโนมัติด้วยสคริปต์รายเดือน

#!/bin/bash
set -euo pipefail

NEXT=$(date -d "+2 month" +%Y%m)
BOUND=$(date -d "+3 month" +%Y-%m-01)

EXISTS=$(mysql -N -e "SELECT COUNT(*) FROM information_schema.partitions
  WHERE table_schema='shop' AND table_name='access_log' AND partition_name='p$NEXT';")

if [ "$EXISTS" -eq 0 ]; then
  mysql -e "ALTER TABLE shop.access_log REORGANIZE PARTITION pmax INTO (
    PARTITION p$NEXT VALUES LESS THAN (TO_DAYS('$BOUND')),
    PARTITION pmax VALUES LESS THAN MAXVALUE);"
  echo "เพิ่มพาร์ทิชัน p$NEXT แล้ว"
fi

สร้างล่วงหน้าสองถึงสามเดือนเสมอ เผื่อสคริปต์ล้มเหลวแล้วไม่มีใครสังเกต

ขั้นตอนที่ 6: ลบข้อมูลเก่าในพริบตา

นี่คือประโยชน์ที่ชัดเจนที่สุดของ Partitioning

-- แบบเดิม ใช้เวลาเป็นชั่วโมงและสร้าง binary log มหาศาล
DELETE FROM access_log WHERE created_at < '2026-01-01';

-- แบบใหม่ เสร็จในไม่กี่วินาที
ALTER TABLE access_log DROP PARTITION p202512;

หากต้องเก็บข้อมูลเก่าไว้ก่อนลบ ให้ย้ายออกไปตารางอื่นก่อน

CREATE TABLE access_log_archive LIKE access_log;
ALTER TABLE access_log_archive REMOVE PARTITIONING;

ALTER TABLE access_log EXCHANGE PARTITION p202512
  WITH TABLE access_log_archive;

-- ตอนนี้ข้อมูลอยู่ในตารางเก็บถาวรแล้ว ลบพาร์ทิชันว่างทิ้งได้
ALTER TABLE access_log DROP PARTITION p202512;

คำสั่ง EXCHANGE PARTITION เพียงสลับชื่อไฟล์ ไม่ได้คัดลอกข้อมูล จึงเร็วมากแม้ข้อมูลหลายกิกะไบต์

ขั้นตอนที่ 7: บำรุงรักษา

-- ปรับปรุงสถิติของพาร์ทิชันเดียว
ALTER TABLE access_log ANALYZE PARTITION p202609;

-- คืนพื้นที่ที่ว่างหลังลบข้อมูล
ALTER TABLE access_log OPTIMIZE PARTITION p202608;

-- ตรวจความถูกต้อง
ALTER TABLE access_log CHECK PARTITION p202609;

ข้อดีคือทำทีละพาร์ทิชันได้ ไม่ต้องล็อกทั้งตารางเหมือนการบำรุงรักษาตารางปกติ

ปัญหาที่พบบ่อย

ขึ้น A PRIMARY KEY must include all columns in the table partitioning function

คอลัมน์ที่ใช้แบ่งต้องอยู่ใน Primary Key และ Unique Key ทุกตัว แก้ Primary Key ก่อนตามขั้นตอนที่ 3 หากมี Unique Key อื่นที่ไม่มีคอลัมน์นี้ ต้องเพิ่มเข้าไปด้วยหรือเปลี่ยนเป็น Index ธรรมดา

Query ยังช้าเหมือนเดิม

ตรวจ EXPLAIN ว่าคอลัมน์ partitions แสดงพาร์ทิชันเดียวหรือทั้งหมด ถ้าแสดงทั้งหมดแปลว่าไม่เกิดการตัดพาร์ทิชัน มักเกิดจาก Query ไม่ได้กรองด้วยคอลัมน์ที่ใช้แบ่ง

Foreign Key ใช้ไม่ได้

ตารางที่แบ่งพาร์ทิชันไม่รองรับ Foreign Key ทั้งขาเข้าและขาออก ต้องตรวจสอบความสัมพันธ์ในระดับแอปพลิเคชันแทน นี่คือข้อจำกัดที่ต้องยอมรับ

จำนวนพาร์ทิชันมากเกินไป

แต่ละพาร์ทิชันใช้ทรัพยากรและ file descriptor การมีหลายพันพาร์ทิชันทำให้ช้าลง แบ่งรายเดือนแล้วเก็บย้อนหลังสองถึงสามปีคือประมาณสามสิบพาร์ทิชัน ซึ่งเป็นจำนวนที่เหมาะสม

ต้องการให้ทีมวิศวกรช่วยออกแบบโครงสร้างข้อมูลขนาดใหญ่ ติดต่อ THAI DATA CLOUD ได้ที่ https://thaidata.cloud/contact/