อ่าน EXPLAIN และออกแบบ Index ให้ Query เร็วขึ้น

เมื่อเว็บช้าและพบว่าคอขวดอยู่ที่ฐานข้อมูล ทางแก้ที่ได้ผลที่สุดมักไม่ใช่การเพิ่ม CPU หรือ RAM แต่คือการเพิ่ม Index ที่ถูกต้องหนึ่งตัว ซึ่งเปลี่ยน Query จากที่ใช้เวลาสองวินาทีเหลือสองมิลลิวินาทีได้

คู่มือนี้สอนอ่านผล EXPLAIN ซึ่งเป็นเครื่องมือที่บอกว่าฐานข้อมูลตัดสินใจทำงานอย่างไร แล้วออกแบบ Index ให้ตรงกับที่ Query ต้องการจริง พร้อมกฎการเรียงลำดับคอลัมน์ที่เป็นสาเหตุให้ Index จำนวนมากไม่ถูกใช้งาน

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

• MySQL 8.0 หรือ MariaDB 10.5 ขึ้นไป
• Query ที่ช้าจริงจากระบบงาน ไม่ใช่ Query สมมติ

หาว่า Query ไหนช้าก่อนด้วย Slow Query Log

mysql -e "SET GLOBAL slow_query_log = ON;"
mysql -e "SET GLOBAL long_query_time = 1;"
mysql -e "SET GLOBAL log_queries_not_using_indexes = ON;"
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

ขั้นตอนที่ 1: อ่านผล EXPLAIN

EXPLAIN SELECT * FROM orders
WHERE customer_id = 1234 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

คอลัมน์ที่ต้องดูและความหมาย

type คือวิธีที่ใช้เข้าถึงข้อมูล เรียงจากดีที่สุดไปแย่ที่สุด (EXPLAIN)

• const และ eq_ref ดีที่สุด อ่านแถวเดียว
• ref ดี ใช้ Index หาแถวที่ตรงเงื่อนไข
• range ใช้ได้ อ่านช่วงของ Index
• index ไม่ดีนัก อ่าน Index ทั้งตัว
• ALL แย่ที่สุด อ่านทั้งตาราง คือเป้าหมายที่ต้องกำจัด

key คือ Index ที่ถูกเลือกใช้จริง

หากเป็น NULL แปลว่าไม่ได้ใช้ Index เลย

rows คือจำนวนแถวที่คาดว่าต้องอ่าน

ตัวเลขนี้คือสิ่งที่ต้องทำให้เล็กที่สุด ถ้าตารางมีล้านแถวและ rows แสดงเป็นล้าน แปลว่ากำลังสแกนทั้งตาราง

Extra คือรายละเอียดเพิ่มเติมที่สำคัญ

• Using index ดีมาก อ่านจาก Index อย่างเดียวไม่ต้องแตะตารางจริง
• Using where ปกติ
• Using filesort ต้องเรียงข้อมูลเพิ่ม ซึ่งช้า
• Using temporary ต้องสร้างตารางชั่วคราว ซึ่งช้ามาก

ดูแผนแบบละเอียดพร้อมเวลาที่ใช้จริง

EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 1234 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

คำสั่งนี้รัน Query จริงแล้วรายงานเวลาที่ใช้ในแต่ละขั้น ต่างจาก EXPLAIN ธรรมดาที่แสดงเพียงแผนที่คาดไว้

ขั้นตอนที่ 2: กฎการเรียงคอลัมน์ใน Index แบบผสม

นี่คือหัวใจของการออกแบบ Index และเป็นจุดที่ทำผิดกันมากที่สุด กฎคือเรียงตามลำดับนี้

1. คอลัมน์ที่เปรียบเทียบแบบเท่ากับ (=) ก่อน
2. คอลัมน์ที่ใช้เรียงลำดับ (ORDER BY) ถัดมา
3. คอลัมน์ที่เปรียบเทียบแบบช่วง (>, <, BETWEEN) ไว้ท้ายสุด

สำหรับ Query ตัวอย่างข้างต้น Index ที่ถูกต้องคือ

CREATE INDEX idx_orders_cust_status_created
  ON orders (customer_id, status, created_at);

เหตุผลคือ customer_id และ status ใช้เปรียบเทียบแบบเท่ากับจึงมาก่อน แล้ว created_at ใช้เรียงจึงตามมา ผลคือฐานข้อมูลอ่าน Index ตามลำดับที่เรียงไว้แล้วได้เลย ไม่ต้อง Using filesort

หากสลับลำดับเป็น (created_at, customer_id, status) Index จะแทบไม่ถูกใช้ เพราะเงื่อนไขแรกใน Query ไม่ได้อยู่ที่คอลัมน์แรกของ Index

ขั้นตอนที่ 3: หลักการคำนำหน้าซ้ายสุด

Index แบบผสมใช้ได้เฉพาะเมื่อ Query ใช้คอลัมน์เรียงจากซ้ายไปขวาต่อเนื่องกัน สมมติมี Index (a, b, c)

• WHERE a = 1 ใช้ได้
• WHERE a = 1 AND b = 2 ใช้ได้
• WHERE a = 1 AND b = 2 AND c = 3 ใช้ได้เต็มที่
• WHERE b = 2 ใช้ไม่ได้ เพราะข้าม a
• WHERE a = 1 AND c = 3 ใช้ได้แค่ a เท่านั้น

ผลที่ตามมาคือ Index (a, b) ทำให้ Index (a) ซ้ำซ้อนและควรลบทิ้ง เพราะตัวแรกครอบคลุมอยู่แล้ว

ขั้นตอนที่ 4: หา Index ที่ไม่ได้ใช้และที่ซ้ำซ้อน

-- Index ที่ไม่เคยถูกใช้เลย
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema NOT IN ('mysql','performance_schema','sys')
ORDER BY object_schema, object_name;

-- ดู Index ทั้งหมดของตาราง พร้อมความหลากหลายของค่า
SELECT index_name, seq_in_index, column_name, cardinality
FROM information_schema.statistics
WHERE table_schema = 'shop' AND table_name = 'orders'
ORDER BY index_name, seq_in_index;

ค่า cardinality คือจำนวนค่าที่ไม่ซ้ำกันโดยประมาณ ยิ่งสูงยิ่งดีสำหรับ Index เพราะกรองได้แคบ Index บนคอลัมน์ที่มีแค่สองค่า เช่น is_active มักไม่คุ้มที่จะสร้างเดี่ยว ๆ

Index ทุกตัวมีต้นทุน มันทำให้การ INSERT และ UPDATE ช้าลงและกินพื้นที่ ลบตัวที่ไม่ได้ใช้ทิ้ง

DROP INDEX idx_unused ON orders;

-- Query นี้ต้องการแค่สามคอลัมน์
SELECT customer_id, status, created_at FROM orders
WHERE customer_id = 1234;

-- Index นี้ครอบคลุมทั้งหมด จึงได้ Using index
CREATE INDEX idx_cover ON orders (customer_id, status, created_at);

-- ผิด ใช้ฟังก์ชันครอบคอลัมน์ ทำให้ Index ใช้ไม่ได้
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- ถูก เขียนเป็นช่วงแทน
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- ผิด ขึ้นต้นด้วยเครื่องหมายแทนที่
SELECT * FROM products WHERE name LIKE '%เสื้อ%';

-- ใช้ Index ได้ เพราะขึ้นต้นด้วยข้อความจริง
SELECT * FROM products WHERE name LIKE 'เสื้อ%';

-- ผิด ชนิดข้อมูลไม่ตรงกัน ทำให้ต้องแปลงทุกแถว
SELECT * FROM users WHERE phone = 0812345678;

-- ถูก ส่งเป็นข้อความให้ตรงกับชนิดคอลัมน์
SELECT * FROM users WHERE phone = '0812345678';

ANALYZE TABLE orders;
SHOW INDEX FROM orders;

SELECT * FROM orders FORCE INDEX (idx_orders_cust_status_created)
WHERE customer_id = 1234;

CREATE INDEX idx_name ON orders (customer_id), ALGORITHM=INPLACE, LOCK=NONE;