Table Optimization ใน MySQL

Categories

เมื่อฐานข้อมูล MySQL ใช้งานไปสักระยะ ตารางจะเกิดการกระจัดกระจายของข้อมูล (Fragmentation) พื้นที่ดิสก์ถูกใช้อย่างไม่มีประสิทธิภาพ และสถิติของดัชนีไม่ตรงกับข้อมูลจริง ปัญหาเหล่านี้ทำให้ Performance ลดลงอย่างต่อเนื่องโดยที่ผู้ดูแลระบบอาจไม่ทันสังเกต

บทความนี้จะอธิบายวิธีดูแลและปรับปรุงตารางใน MySQL อย่างเป็นระบบ ตั้งแต่การตรวจสอบ Fragmentation, การใช้คำสั่ง OPTIMIZE และ ANALYZE, การเลือก ROW_FORMAT ที่เหมาะสม, เทคนิค Online DDL สำหรับปรับโครงสร้างข้อมูลขนาดใหญ่ ไปจนถึงการแบ่งพาร์ติชันเพื่อจัดการข้อมูลหลักล้าน Row ได้อย่างมีประสิทธิภาพ

Fragmentation คืออะไร และเกิดขึ้นได้อย่างไร

Fragmentation หรือการกระจัดกระจายของข้อมูล เกิดขึ้นเมื่อมีการ INSERT, UPDATE และ DELETE ข้อมูลซ้ำ ๆ ใน InnoDB ทุกครั้งที่ลบแถวข้อมูล พื้นที่นั้นจะถูกทำเครื่องหมายว่าว่าง แต่ไม่ได้ถูกคืนให้ระบบปฏิบัติการทันที เมื่อมีการเพิ่มข้อมูลใหม่ที่ขนาดไม่พอดีกับช่องว่างเดิม ก็จะถูกจัดเก็บในตำแหน่งใหม่ ทำให้ข้อมูลกระจายอยู่คนละส่วนของดิสก์

ผลกระทบของ Fragmentation ที่สำคัญ ได้แก่ ไฟล์ .ibd มีขนาดใหญ่กว่าข้อมูลจริงมาก (Wasted Space), การอ่านข้อมูลต้อง Seek หลายตำแหน่งบนดิสก์ทำให้ I/O เพิ่มขึ้น, Buffer Pool ถูกใช้ไม่คุ้มค่าเพราะโหลด Page ที่มีข้อมูลเพียงบางส่วน และ Full Table Scan ช้าลงเพราะต้องอ่าน Page จำนวนมากขึ้น

ตรวจสอบ Fragmentation ของตาราง

ก่อนจะปรับปรุงตาราง ควรตรวจสอบก่อนว่าตารางไหนมีปัญหา Fragmentation จริง ๆ โดยสามารถดูได้จาก INFORMATION_SCHEMA

SELECT
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
    ROUND(DATA_FREE / 1024 / 1024, 2) AS free_mb,
    ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE) * 100, 1) AS frag_pct
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database'
    AND DATA_FREE > 0
ORDER BY DATA_FREE DESC;

คอลัมน์ DATA_FREE แสดงพื้นที่ว่างที่ถูกจัดสรรแล้วแต่ยังไม่ได้ใช้งาน ซึ่งเป็นตัวบ่งชี้หลักของ Fragmentation ค่า frag_pct ที่คำนวณได้คือสัดส่วนพื้นที่สูญเปล่า หากเกิน 10-20% ก็ควรพิจารณาทำการปรับปรุง

นอกจากนี้ยังสามารถใช้คำสั่ง SHOW STATUS เพื่อดูข้อมูลเดียวกันแบบรวดเร็ว

SHOW TABLE STATUS FROM your_database LIKE 'orders';

ดูค่า Data_free ในผลลัพธ์ — ถ้ามีค่าสูงเทียบกับ Data_length แสดงว่าควรทำ Defragmentation

OPTIMIZE — จัดเรียงข้อมูลและคืนพื้นที่

คำสั่ง OPTIMIZE เป็นเครื่องมือหลักสำหรับแก้ปัญหา Fragmentation โดยจะจัดเรียงข้อมูลใหม่ สร้างดัชนีใหม่ทั้งหมด และคืนพื้นที่ว่างให้ระบบปฏิบัติการ

OPTIMIZE TABLE orders;

สำหรับ InnoDB คำสั่งนี้ทำงานเทียบเท่ากับ ALTER … FORCE ซึ่งจะ Rebuild โดยคัดลอกข้อมูลจากชุดเดิมไปยังชุดใหม่ แล้วลบของเดิมทิ้ง ระหว่างดำเนินการยังอ่านได้ แต่การเขียนจะถูก Block ชั่วคราว

สิ่งที่ควรรู้ก่อนใช้คำสั่ง OPTIMIZE

  • ใช้เวลานาน — ตารางขนาดหลาย GB อาจใช้เวลาหลายชั่วโมง เพราะต้องคัดลอกข้อมูลทั้งหมด
  • ต้องการพื้นที่ดิสก์เพิ่มเติม — ระหว่าง Rebuild จะมีตารางเดิมและตารางใหม่อยู่พร้อมกัน ดิสก์ต้องมีพื้นที่เพียงพอสำหรับสำเนาทั้งตาราง
  • Lock ตาราง — แม้ InnoDB จะรองรับ Online DDL บางส่วน แต่คำสั่ง OPTIMIZE ยังต้อง Lock ช่วงท้ายของกระบวนการ
  • ไม่เหมาะกับข้อมูลที่เปลี่ยนแปลงน้อย — ถ้า DATA_FREE ต่ำ การรันคำสั่งนี้แทบไม่มีประโยชน์แต่เปลืองทรัพยากร

สามารถรันหลายตารางพร้อมกันในคำสั่งเดียวได้

OPTIMIZE TABLE orders, order_items, customers;

ANALYZE — อัปเดตสถิติดัชนี

คำสั่ง ANALYZE ทำหน้าที่อัปเดต Index Statistics ซึ่ง Query Optimizer ใช้ตัดสินใจว่าจะเลือกใช้ดัชนีไหนและจะเข้าถึงข้อมูลด้วยวิธีใด ถ้าสถิติไม่ตรงกับข้อมูลจริง Optimizer อาจเลือกแผนการทำงานที่ไม่เหมาะสม เช่น เลือก Full Scan แทนที่จะใช้ดัชนี

ANALYZE TABLE orders;

คำสั่งนี้ทำงานเร็วมากเมื่อเทียบกับ OPTIMIZE เพราะไม่ต้องคัดลอกข้อมูล เพียงแค่สุ่มอ่านข้อมูลบางส่วนเพื่อประมาณค่าสถิติใหม่ สามารถรันได้บ่อยโดยไม่กระทบ Performance ควรรันคำสั่ง ANALYZE หลังจากมีการ INSERT, UPDATE หรือ DELETE ข้อมูลจำนวนมาก หรือหลังจากสร้างดัชนีใหม่

ดูสถิติดัชนีปัจจุบัน

สามารถตรวจสอบสถิติดัชนีได้จาก innodb_index_stats

SELECT * FROM mysql.innodb_index_stats
WHERE database_name = 'your_database'
    AND table_name = 'orders';

หรือดูจาก INFORMATION_SCHEMA.STATISTICS เพื่อดูค่า Cardinality ซึ่งเป็นจำนวนค่าที่ไม่ซ้ำกันโดยประมาณ

SELECT INDEX_NAME, COLUMN_NAME, CARDINALITY
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
    AND TABLE_NAME = 'orders';

CHECK และ REPAIR — ตรวจสอบและซ่อมแซมข้อมูล

คำสั่ง CHECK ใช้ตรวจสอบความสมบูรณ์ของข้อมูล เช่น ดัชนีเสียหาย ข้อมูลไม่สอดคล้อง หรือโครงสร้างผิดปกติ

CHECK TABLE orders;
CHECK TABLE orders MEDIUM;  -- ตรวจละเอียดขึ้น
CHECK TABLE orders EXTENDED; -- ตรวจทุก Row และทุก Key

ระดับการตรวจสอบมีตั้งแต่ QUICK (เร็วที่สุด ตรวจแค่โครงสร้าง), FAST (ตรวจเฉพาะที่ไม่ได้ปิดอย่างถูกต้อง), MEDIUM (ตรวจข้อมูลและดัชนี) ไปจนถึง EXTENDED (ตรวจทุกรายละเอียด แต่ช้าที่สุด)

สำหรับคำสั่ง REPAIR ใช้ซ่อมแซมข้อมูลที่เสียหาย แต่ใช้ได้กับ MyISAM เท่านั้น

REPAIR TABLE legacy_myisam_table;

สำหรับ InnoDB หากตรวจพบความเสียหาย วิธีแก้ไขที่แนะนำคือ Restore จาก Backup หรือใช้ innodb_force_recovery ในกรณีฉุกเฉิน

ROW_FORMAT — เลือกรูปแบบการจัดเก็บที่เหมาะสม

InnoDB รองรับหลาย ROW_FORMAT ซึ่งส่งผลต่อวิธีจัดเก็บข้อมูลในดิสก์ ขนาดของตาราง และ Performance แต่ละรูปแบบเหมาะกับข้อมูลต่างประเภทกัน

DYNAMIC — เป็นค่าเริ่มต้นตั้งแต่ MySQL 5.7 เป็นต้นมา เหมาะกับข้อมูลทั่วไป จัดเก็บคอลัมน์ที่มีข้อมูลขนาดใหญ่ (เช่น TEXT, BLOB) ใน Overflow Page ทำให้ Data Page หลักมีขนาดกะทัดรัด

COMPRESSED — บีบอัดข้อมูลในระดับ Page ลดขนาดบนดิสก์ได้ 30-50% เหมาะกับตารางที่อ่านบ่อยแต่เขียนน้อย หรือตารางที่เก็บข้อมูลเก่า (Archive) ข้อเสียคือใช้ CPU มากขึ้นในการบีบอัดและคลายข้อมูล และ Buffer Pool ต้องเก็บทั้ง Compressed Page และ Uncompressed Page

COMPACT — รูปแบบเก่าตั้งแต่ MySQL 5.0 จัดเก็บข้อมูลขนาดใหญ่บางส่วนไว้ใน Data Page หลักด้วย ทำให้ Page เต็มเร็วกว่า DYNAMIC

REDUNDANT — รูปแบบเก่าที่สุด ใช้พื้นที่มากที่สุด ไม่แนะนำสำหรับตารางใหม่

เปลี่ยน ROW_FORMAT

-- ดู ROW_FORMAT ปัจจุบัน
SELECT TABLE_NAME, ROW_FORMAT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database';

-- เปลี่ยนเป็น COMPRESSED
ALTER TABLE archive_logs ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

-- เปลี่ยนกลับเป็น DYNAMIC
ALTER TABLE archive_logs ROW_FORMAT=DYNAMIC;

การเลือก KEY_BLOCK_SIZE สำหรับ COMPRESSED ต้องทดสอบกับข้อมูลจริง ค่าที่ใช้บ่อยคือ 8 (KB) ซึ่งเป็นจุดสมดุลระหว่างอัตราการบีบอัดและ Performance

innodb_file_per_table — แยกไฟล์ต่อตาราง

ตั้งแต่ MySQL 5.6.6 เป็นต้นมา ค่าเริ่มต้นของ innodb_file_per_table คือ ON ซึ่งหมายความว่า InnoDB จะเก็บแต่ละชุดข้อมูลในไฟล์ .ibd แยกของตัวเอง แทนที่จะรวมอยู่ใน System Tablespace (ibdata1) เพียงไฟล์เดียว

ข้อดีของการแยกไฟล์คือ OPTIMIZE สามารถคืนพื้นที่ดิสก์ได้จริง (ไฟล์ .ibd จะเล็กลง), สามารถย้ายแต่ละชุดข้อมูลไปยังดิสก์อื่นได้ ตรวจสอบขนาดได้ง่ายโดยดูขนาดไฟล์ และ DROP หรือ TRUNCATE คืนพื้นที่ดิสก์ทันที

-- ตรวจสอบค่าปัจจุบัน
SHOW VARIABLES LIKE 'innodb_file_per_table';

-- เปิดใช้งาน (ถ้ายังปิดอยู่)
SET GLOBAL innodb_file_per_table = ON;

หากเคยใช้ System Tablespace ร่วม (innodb_file_per_table = OFF) แล้วเปลี่ยนมาเปิด ข้อมูลเดิมจะยังอยู่ใน ibdata1 ต้องทำ ALTER … FORCE ทีละรายการเพื่อย้ายข้อมูลออกมาเป็นไฟล์แยก

ALTER TABLE orders FORCE;

Online DDL — เปลี่ยนโครงสร้างโดยไม่ต้องหยุดระบบ

การเปลี่ยนโครงสร้างข้อมูลขนาดใหญ่ใน Production เป็นเรื่องที่ต้องระวัง เพราะอาจ Lock เป็นเวลานาน ตั้งแต่ MySQL 5.6 เป็นต้นมา InnoDB รองรับ Online DDL ที่อนุญาตให้อ่านและเขียนข้อมูลได้ระหว่างดำเนินการโดยไม่ต้องหยุดให้บริการ

ระบุ Algorithm และ Lock

-- เพิ่มคอลัมน์แบบ Online (อ่านเขียนได้ระหว่างดำเนินการ)
ALTER TABLE orders ADD COLUMN notes TEXT,
    ALGORITHM=INPLACE, LOCK=NONE;

-- สร้างดัชนีแบบ Online
ALTER TABLE orders ADD INDEX idx_created (created_at),
    ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM กำหนดวิธีดำเนินการ โดย INPLACE หมายถึงแก้ไขโครงสร้างบน Tablespace เดิมโดยไม่ต้อง Copy ข้อมูลทั้งหมด (เร็วกว่า) ส่วน COPY หมายถึงสร้างตารางใหม่แล้วคัดลอกข้อมูลทั้งหมด (ช้ากว่าแต่รองรับทุกประเภทการเปลี่ยนแปลง) และ INSTANT หมายถึงแก้ไขเฉพาะ Metadata ไม่ต้องแตะข้อมูลเลย (เร็วที่สุด รองรับตั้งแต่ MySQL 8.0 สำหรับบางการเปลี่ยนแปลง)

LOCK กำหนดระดับการ Lock โดย NONE อนุญาตทั้งอ่านและเขียน, SHARED อนุญาตเฉพาะอ่าน และ EXCLUSIVE ไม่อนุญาตทั้งอ่านและเขียน

การเปลี่ยนแปลงที่รองรับ INSTANT

ใน MySQL 8.0 การเปลี่ยนแปลงบางอย่างสามารถทำได้แบบ INSTANT ซึ่งเสร็จภายในเสี้ยววินาทีไม่ว่าตารางจะใหญ่แค่ไหน

  • เพิ่มคอลัมน์ใหม่ที่ท้ายตาราง (MySQL 8.0.12+)
  • เพิ่มคอลัมน์ใหม่ที่ตำแหน่งใดก็ได้ (MySQL 8.0.29+)
  • เปลี่ยน Default Value ของคอลัมน์
  • เปลี่ยนชื่อตาราง
  • DROP คอลัมน์ (MySQL 8.0.29+)
-- เพิ่มคอลัมน์แบบ INSTANT (เสร็จทันที)
ALTER TABLE orders ADD COLUMN priority TINYINT DEFAULT 0,
    ALGORITHM=INSTANT;

pt-online-schema-change — เปลี่ยนโครงสร้างตารางขนาดใหญ่

สำหรับการเปลี่ยนแปลงที่ไม่รองรับ Online DDL หรือเมื่อต้องการควบคุม Load อย่างละเอียด เครื่องมือ pt-online-schema-change จาก Percona Toolkit เป็นทางเลือกที่นิยมใช้กันอย่างแพร่หลาย

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

# ติดตั้ง Percona Toolkit บน Ubuntu
sudo apt-get install percona-toolkit

# เปลี่ยนโครงสร้างตาราง orders
pt-online-schema-change \
    --alter "ADD COLUMN shipping_method VARCHAR(50)" \
    D=mydb,t=orders \
    --user=root --ask-pass \
    --chunk-size=1000 \
    --max-load="Threads_running=25" \
    --critical-load="Threads_running=50" \
    --execute

ตัวเลือกที่สำคัญ ได้แก่ –chunk-size กำหนดจำนวนแถวที่คัดลอกต่อรอบ, –max-load หยุดชั่วคราวถ้าเซิร์ฟเวอร์โหลดเกินที่กำหนด, –critical-load ยกเลิกทันทีถ้าโหลดสูงถึงขีดวิกฤต และ –dry-run ทดสอบโดยไม่ดำเนินการจริง ควรใช้ก่อนเสมอ

Partitioning — แบ่งข้อมูลเพื่อจัดการชุดข้อมูลขนาดใหญ่

เมื่อข้อมูลมีหลายสิบล้านแถว การดูแลรักษาเช่น Backup, Archive และ Defragmentation จะใช้เวลานานมาก Partitioning ช่วยแก้ปัญหานี้โดยแบ่งข้อมูลออกเป็นส่วนย่อย (Partition) ที่จัดการแยกกันได้

Range Partitioning ตามช่วงเวลา

รูปแบบที่นิยมที่สุดคือแบ่งตามช่วงเวลา เหมาะกับข้อมูลที่มีอายุชัดเจน เช่น Logs, Orders หรือ Transactions

CREATE TABLE access_logs (
    id BIGINT AUTO_INCREMENT,
    log_date DATE NOT NULL,
    url VARCHAR(500),
    status_code SMALLINT,
    response_time INT,
    PRIMARY KEY (id, log_date)
) PARTITION BY RANGE (YEAR(log_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

เมื่อ Query มี WHERE log_date BETWEEN ‘2025-01-01’ AND ‘2025-12-31’ เซิร์ฟเวอร์จะอ่านเฉพาะ Partition p2025 เท่านั้น (Partition Pruning) ไม่ต้อง Scan ข้อมูลทั้งหมด

จัดการ Partition

-- เพิ่ม Partition ใหม่ (ต้อง Reorganize p_future ก่อน)
ALTER TABLE access_logs REORGANIZE PARTITION p_future INTO (
    PARTITION p2027 VALUES LESS THAN (2028),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- ลบข้อมูลเก่าทั้ง Partition (เร็วมาก เทียบเท่า TRUNCATE)
ALTER TABLE access_logs DROP PARTITION p2023;

-- Optimize เฉพาะ Partition
ALTER TABLE access_logs OPTIMIZE PARTITION p2025;

-- ดูข้อมูลแต่ละ Partition
SELECT PARTITION_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'your_database'
    AND TABLE_NAME = 'access_logs';

ข้อดีของ Partitioning ด้านการดูแลรักษา คือลบข้อมูลเก่าได้ทันทีด้วย DROP PARTITION (เร็วกว่า DELETE หลายเท่า), ทำ OPTIMIZE หรือ ANALYZE ทีละ Partition ได้ (ลดเวลา Lock), Backup ทีละ Partition ลดผลกระทบต่อระบบ และ Query ที่ระบุเงื่อนไขตรง Partition Key จะเร็วขึ้นอย่างเห็นได้ชัด

ออกแบบโครงสร้างตารางเพื่อ Performance

การออกแบบโครงสร้างตารางที่ดีตั้งแต่แรกช่วยลดปัญหาในระยะยาวได้มาก เทคนิคที่สำคัญมีดังนี้

เลือก Data Type ให้เหมาะสม

ใช้ Data Type ที่เล็กที่สุดที่เพียงพอกับข้อมูล เพราะข้อมูลที่เล็กกว่าหมายถึง Page จุข้อมูลได้มากกว่า Buffer Pool ทำงานได้คุ้มค่ากว่า และดัชนีมีขนาดเล็กกว่า

  • TINYINT (1 byte) แทน INT (4 bytes) สำหรับค่าที่ไม่เกิน 127/255
  • SMALLINT (2 bytes) แทน INT สำหรับค่าที่ไม่เกิน 32,767/65,535
  • DATE (3 bytes) แทน DATETIME (5/8 bytes) ถ้าไม่ต้องเก็บเวลา
  • ENUM (1-2 bytes) แทน VARCHAR สำหรับค่าที่กำหนดตายตัว เช่น สถานะ
  • INET_ATON/INET6_ATON เก็บ IP Address เป็น INT/BINARY แทน VARCHAR
-- แทนที่จะใช้
CREATE TABLE products (
    id INT,
    status VARCHAR(20),    -- 'active', 'inactive', 'draft'
    rating INT,            -- 1-5
    ip_address VARCHAR(45)
);

-- ออกแบบให้ประหยัดพื้นที่
CREATE TABLE products (
    id INT,
    status ENUM('active','inactive','draft'),
    rating TINYINT UNSIGNED,
    ip_address INT UNSIGNED  -- ใช้ INET_ATON() เมื่อ INSERT
);

VARCHAR vs CHAR

VARCHAR เก็บข้อมูลตามความยาวจริงบวก 1-2 bytes สำหรับเก็บความยาว เหมาะกับข้อมูลที่ความยาวแตกต่างกันมาก ส่วน CHAR เก็บข้อมูลขนาดคงที่ เหมาะกับข้อมูลที่ความยาวเท่ากันเสมอ เช่น รหัสประเทศ (TH, US), รหัสสกุลเงิน (THB, USD) หรือ Hash ที่มีความยาวคงที่

หลีกเลี่ยง NULL ที่ไม่จำเป็น

คอลัมน์ที่เป็น NOT NULL ใช้พื้นที่น้อยกว่าเล็กน้อยและดัชนีทำงานได้มีประสิทธิภาพกว่า ควรกำหนด NOT NULL พร้อม Default Value สำหรับคอลัมน์ที่ไม่จำเป็นต้องมีค่า NULL เช่น ใช้ 0 แทน NULL สำหรับจำนวน หรือ ” แทน NULL สำหรับข้อความ

InnoDB Buffer Pool — พื้นฐานที่ต้องตั้งค่าให้ถูก

Buffer Pool เป็นหน่วยความจำที่ InnoDB ใช้ Cache ข้อมูลและดัชนี เป็นปัจจัยที่มีผลต่อ Performance มากที่สุดของ InnoDB ยิ่ง Buffer Pool ใหญ่ ข้อมูลก็จะถูกอ่านจาก RAM แทนดิสก์ได้มากขึ้น

-- ดูขนาด Buffer Pool ปัจจุบัน
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- ตั้งค่า Buffer Pool (ใน my.cnf)
[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4

ค่าที่แนะนำคือ 70-80% ของ RAM ทั้งหมด (สำหรับเซิร์ฟเวอร์ที่รันเฉพาะ MySQL) เช่น เซิร์ฟเวอร์ RAM 8 GB ควรตั้ง Buffer Pool ที่ 5-6 GB ส่วน innodb_buffer_pool_instances ควรตั้งเป็น 1 instance ต่อทุก ๆ 1-2 GB ของ Buffer Pool

ตรวจสอบประสิทธิภาพ Buffer Pool

SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- ค่าที่ควรดู
-- Innodb_buffer_pool_read_requests: จำนวนครั้งที่อ่านจาก Buffer Pool (hit)
-- Innodb_buffer_pool_reads: จำนวนครั้งที่ต้องอ่านจากดิสก์ (miss)
-- Hit Rate = read_requests / (read_requests + reads) * 100
-- ควรได้ 99%+

ถ้า Hit Rate ต่ำกว่า 99% แสดงว่า Buffer Pool อาจเล็กเกินไป หรือมีตารางที่ไม่ได้ถูก Optimize ทำให้ Page ในหน่วยความจำถูกใช้งานอย่างไม่คุ้มค่า การ Defragment ตารางจะช่วยให้ข้อมูลถูกจัดเก็บอย่างกะทัดรัดขึ้น ลดจำนวน Page ที่ต้องโหลดเข้า Buffer Pool

สร้าง Script อัตโนมัติสำหรับ Maintenance

การตรวจสอบและปรับปรุงข้อมูลเป็นประจำควรทำอัตโนมัติ ตัวอย่างต่อไปนี้เป็น Shell Script ที่ตรวจสอบ Fragmentation แล้ว Optimize เฉพาะส่วนที่จำเป็น

#!/bin/bash
# optimize_tables.sh — Optimize InnoDB ที่มี Fragmentation สูง

DB_USER="root"
DB_PASS="your_password"
DB_NAME="your_database"
FRAG_THRESHOLD=10  # เปอร์เซ็นต์ Fragmentation ขั้นต่ำ

echo "=== Optimization Report ==="
echo "Date: $(date)"
echo ""

# ดึงรายชื่อตารางที่มี Fragmentation เกิน Threshold
TABLES=$(mysql -u$DB_USER -p$DB_PASS -N -e "
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = '$DB_NAME'
        AND ENGINE = 'InnoDB'
        AND DATA_FREE > 0
        AND (DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE) * 100) > $FRAG_THRESHOLD
    ORDER BY DATA_FREE DESC;
")

if [ -z "$TABLES" ]; then
    echo "No InnoDB needs optimization."
    exit 0
fi

for TABLE in $TABLES; do
    echo "Optimizing: $TABLE"
    mysql -u$DB_USER -p$DB_PASS -e "OPTIMIZE TABLE $DB_NAME.$TABLE;" 2>&1
    echo "---"
done

echo ""
echo "Running ANALYZE on all InnoDB..."
mysql -u$DB_USER -p$DB_PASS -e "
    SELECT CONCAT('ANALYZE TABLE $DB_NAME.', TABLE_NAME, ';')
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = '$DB_NAME' AND ENGINE = 'InnoDB';
" -N | mysql -u$DB_USER -p$DB_PASS

echo "Done."

ตั้ง Cron Job ให้รันสัปดาห์ละครั้งในช่วง Low Traffic

# Cron: ทุกวันอาทิตย์ เวลา 03:00
0 3 * * 0 /opt/scripts/optimize_tables.sh >> /var/log/table_optimize.log 2>&1

mysqlcheck — เครื่องมือบรรทัดคำสั่งสำหรับดูแลฐานข้อมูล

mysqlcheck เป็นยูทิลิตี้ที่มาพร้อมกับ MySQL ใช้ตรวจสอบ ซ่อมแซม และปรับปรุงข้อมูลจาก Command Line โดยไม่ต้องเข้า MySQL Shell

# ตรวจสอบทุกตารางในฐานข้อมูล
mysqlcheck -u root -p --check your_database

# Optimize ทุกตาราง
mysqlcheck -u root -p --optimize your_database

# Analyze ทุกตาราง
mysqlcheck -u root -p --analyze your_database

# ทำทั้ง 3 อย่าง ทุกฐานข้อมูล
mysqlcheck -u root -p --all-databases --optimize --analyze --check

# Optimize เฉพาะตาราง orders
mysqlcheck -u root -p --optimize your_database orders

ข้อดีของ mysqlcheck คือใช้งานง่ายจาก CLI รองรับ –all-databases ดำเนินการทุกฐานข้อมูลพร้อมกัน และเหมาะสำหรับใส่ใน Cron Job หรือ Maintenance Script

สรุป

การดูแลรักษาโครงสร้างข้อมูลเป็นงานที่ DBA และนักพัฒนาไม่ควรมองข้าม ควรตรวจสอบ Fragmentation เป็นประจำผ่าน INFORMATION_SCHEMA แล้วใช้ OPTIMIZE กับชุดข้อมูลที่มีพื้นที่สูญเปล่ามาก รัน ANALYZE หลังมีการเปลี่ยนแปลงข้อมูลจำนวนมากเพื่อให้ Query Optimizer ทำงานได้ถูกต้อง เลือก ROW_FORMAT ให้เหมาะกับลักษณะข้อมูล เปิดใช้ innodb_file_per_table เพื่อจัดการพื้นที่ดิสก์ได้ดีขึ้น และพิจารณาใช้ Partitioning กับข้อมูลขนาดใหญ่ที่มีอายุชัดเจน

สำหรับการเปลี่ยนโครงสร้างใน Production ควรเลือกใช้ Online DDL (ALGORITHM=INSTANT หรือ INPLACE) เมื่อเป็นไปได้ หรือใช้เครื่องมืออย่าง pt-online-schema-change สำหรับการเปลี่ยนแปลงที่ซับซ้อน และอย่าลืมตั้งค่า Buffer Pool ให้เหมาะสมกับ RAM ของเซิร์ฟเวอร์ เพื่อให้ข้อมูลที่ถูกจัดเรียงแล้วสามารถ Cache ได้อย่างเต็มประสิทธิภาพ

แนะนำบริการ DE

การดูแลรักษาฐานข้อมูล MySQL ต้องการเซิร์ฟเวอร์ที่มี I/O เร็วและ RAM เพียงพอสำหรับ Buffer Pool หากคุณต้องการเซิร์ฟเวอร์ที่ปรับแต่งค่า MySQL ได้อย่างอิสระ Cloud VPS ของ DE เป็นตัวเลือกที่เหมาะสม เพราะให้ root access เต็มรูปแบบ สามารถตั้งค่า innodb_buffer_pool_size, ติดตั้ง Percona Toolkit และจัดการ Partitioning ได้ตามต้องการ

สำหรับผู้ที่ต้องการโฮสต์เว็บไซต์ที่ใช้ฐานข้อมูลโดยไม่ต้องจัดการเซิร์ฟเวอร์เอง Cloud Hosting ของ DE ก็เป็นอีกทางเลือกที่ดี เพราะมีระบบจัดการฐานข้อมูลให้พร้อมใช้งาน สะดวกสำหรับเว็บไซต์ที่ไม่ต้องการปรับแต่งระดับเซิร์ฟเวอร์