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

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

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

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

• 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;

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