Query Optimization ใน MySQL — เทคนิคปรับแต่ง Query ให้เร็วขึ้น

Categories

เมื่อแอปพลิเคชันเริ่มช้าลง สาเหตุหลักมักไม่ได้อยู่ที่โค้ดฝั่ง Application แต่อยู่ที่ Query ที่ส่งไปยังฐานข้อมูลทำงานไม่มีประสิทธิภาพ การปรับแต่ง Query ให้เร็วขึ้นจึงเป็นทักษะสำคัญที่นักพัฒนาและ DBA ควรมี เพราะสามารถลดเวลาตอบสนองจากหลายวินาทีเหลือไม่กี่มิลลิวินาทีได้โดยไม่ต้องเพิ่ม Hardware

บทความนี้จะแนะนำเทคนิคการปรับแต่ง Query ใน MySQL อย่างเป็นระบบ ตั้งแต่การใช้ EXPLAIN วิเคราะห์ Execution Plan, การสร้าง Index ที่เหมาะสม, รูปแบบ Query ที่ควรใช้และควรหลีกเลี่ยง ไปจนถึงเครื่องมือที่ช่วยค้นหา Slow Query

ทำไมต้องปรับแต่ง Query

ฐานข้อมูลเป็นคอขวด (Bottleneck) ของระบบส่วนใหญ่ เมื่อข้อมูลมีหลักแสนถึงหลักล้าน Row คำสั่งที่เคยทำงานเร็วในช่วงพัฒนาอาจช้าลงอย่างมากใน Production เพราะเซิร์ฟเวอร์ต้อง Scan ข้อมูลจำนวนมหาศาล การปรับแต่งช่วยลดจำนวน Row ที่ต้องอ่าน ลด I/O และลดการใช้ CPU ทำให้ระบบรองรับผู้ใช้งานได้มากขึ้น

ใช้ EXPLAIN วิเคราะห์ Execution Plan

EXPLAIN เป็นเครื่องมือสำคัญที่สุดในการวิเคราะห์ว่าเซิร์ฟเวอร์ฐานข้อมูลจะดำเนินการกับคำสั่ง SELECT อย่างไร เพียงเพิ่มคำว่า EXPLAIN ไว้หน้าคำสั่ง

EXPLAIN SELECT * FROM orders WHERE customer_id = 1234;

ผลลัพธ์จะแสดงข้อมูลเป็นตาราง คอลัมน์ที่สำคัญที่สุดมีดังนี้

type — แสดงวิธีการเข้าถึงข้อมูล เรียงจากดีที่สุดไปแย่ที่สุดคือ system, const, eq_ref, ref, range, index, ALL โดย ALL หมายถึง Full Table Scan ซึ่งควรหลีกเลี่ยงในตารางขนาดใหญ่

key — ชื่อดัชนีที่ถูกเลือกใช้จริง ถ้าแสดง NULL แปลว่าไม่ได้ใช้ดัชนีใด ๆ

rows — จำนวน Row โดยประมาณที่ต้อง Scan ยิ่งน้อยยิ่งดี

Extra — ข้อมูลเพิ่มเติม เช่น Using index (ดี ใช้ Covering ดัชนี), Using where (กรองผลลัพธ์ด้วย WHERE), Using filesort (มีการ Sort ที่อาจช้า), Using temporary (สร้าง Temporary Table)

EXPLAIN ANALYZE สำหรับข้อมูลเชิงลึก

ตั้งแต่เวอร์ชัน 8.0.18 เป็นต้นมา สามารถใช้ EXPLAIN ANALYZE เพื่อดูเวลาจริงที่ใช้ในแต่ละขั้นตอน ซึ่งให้ข้อมูลแม่นยำกว่า EXPLAIN ธรรมดาที่แสดงเฉพาะค่าประมาณ

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1234;

ผลลัพธ์จะแสดง actual time, rows, loops ของแต่ละ Operation ช่วยระบุจุดที่เป็นคอขวดได้ชัดเจน

Index — กุญแจสำคัญของ Query ที่เร็ว

ดัชนีเปรียบเสมือนสารบัญของหนังสือ ช่วยให้เซิร์ฟเวอร์ค้นหาข้อมูลได้โดยไม่ต้อง Scan ทั้งตาราง การเลือกสร้างดัชนีที่เหมาะสมเป็นวิธีที่มีผลกระทบต่อ Performance มากที่สุด

ประเภทของดัชนี

B-Tree — เป็นดัชนีแบบ Default ใช้โครงสร้าง Balanced Tree รองรับ =, <, >, BETWEEN, LIKE ‘prefix%’ และ ORDER BY เหมาะกับการค้นหาทั่วไป

Composite — ดัชนีที่ครอบคลุมหลายคอลัมน์ ทำงานตามหลัก Leftmost Prefix คือใช้ได้เฉพาะเมื่อ Query ใช้คอลัมน์จากซ้ายไปขวาตามลำดับที่สร้าง

Covering — ดัชนีที่มีคอลัมน์ครบทุกตัวที่ Query ต้องการ ทำให้ไม่ต้องกลับไปอ่านข้อมูลจากตารางหลัก (No Table Lookup) เร็วที่สุด

Full-Text — ออกแบบมาสำหรับค้นหาข้อความยาว ใช้คู่กับ MATCH…AGAINST แทนการใช้ LIKE ‘%keyword%’

สร้างดัชนีอย่างถูกต้อง

-- Single Column Index
CREATE INDEX idx_customer_id ON orders (customer_id);

-- Composite Index (ลำดับคอลัมน์สำคัญมาก)
CREATE INDEX idx_customer_date ON orders (customer_id, order_date);

-- Covering Index สำหรับ Query ที่ SELECT เฉพาะบางคอลัมน์
CREATE INDEX idx_covering ON orders (customer_id, order_date, total_amount);

หลัก Leftmost Prefix

สมมติสร้าง Composite ดัชนี (a, b, c) จะใช้ได้กับ Query ที่กรองด้วย (a), (a, b) หรือ (a, b, c) แต่ใช้ไม่ได้กับ (b), (c) หรือ (b, c) เพราะข้าม Column แรกไป การจัดลำดับคอลัมน์ในดัชนีจึงต้องพิจารณาจาก Query ที่ใช้บ่อยที่สุด

ตรวจสอบดัชนีที่มีอยู่

SHOW INDEX FROM orders;

คำสั่งนี้แสดงดัชนีทั้งหมดของตาราง พร้อม Cardinality (จำนวนค่าที่ไม่ซ้ำโดยประมาณ) ยิ่ง Cardinality สูง ดัชนียิ่งมีประสิทธิภาพ

รูปแบบ Query ที่ควรหลีกเลี่ยง

SELECT * แทน SELECT เฉพาะคอลัมน์

การใช้ SELECT * ดึงข้อมูลทุกคอลัมน์ ทำให้ต้องอ่านข้อมูลมากเกินจำเป็น สิ้นเปลือง I/O และ Memory ทั้งยังทำให้ Covering ดัชนีใช้ไม่ได้ ควรระบุเฉพาะคอลัมน์ที่ต้องการ

-- ไม่ดี
SELECT * FROM orders WHERE customer_id = 1234;

-- ดีกว่า
SELECT order_id, order_date, total_amount FROM orders WHERE customer_id = 1234;

ใช้ Function กับคอลัมน์ใน WHERE

เมื่อใช้ Function ครอบคอลัมน์ที่มีดัชนี เซิร์ฟเวอร์จะไม่สามารถใช้ดัชนีได้ ต้อง Scan ทั้งตารางแทน

-- ไม่ดี (Index ใช้ไม่ได้)
SELECT * FROM orders WHERE YEAR(order_date) = 2025;

-- ดีกว่า (ใช้ Index ได้)
SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';

LIKE ‘%keyword%’

การใช้ Wildcard นำหน้า (Leading Wildcard) ทำให้ดัชนีใช้ไม่ได้ ถ้าต้องค้นหาแบบนี้บ่อย ควรใช้ Full-Text แทน

-- ไม่ดี (Full Table Scan)
SELECT * FROM products WHERE name LIKE '%laptop%';

-- ดีกว่า (ใช้ Full-Text Index)
SELECT * FROM products WHERE MATCH(name) AGAINST('laptop' IN BOOLEAN MODE);

OR ที่ทำลายดัชนี

เงื่อนไข OR ระหว่างคอลัมน์ต่างกันอาจทำให้ Optimizer เลือก Full Table Scan แทนการใช้ดัชนี ถ้าเป็นไปได้ ให้ใช้ UNION ALL แทน

-- อาจช้า
SELECT * FROM users WHERE email = '[email protected]' OR phone = '0812345678';

-- เร็วกว่า (แต่ละส่วนใช้ Index ของตัวเอง)
SELECT * FROM users WHERE email = '[email protected]'
UNION ALL
SELECT * FROM users WHERE phone = '0812345678' AND email != '[email protected]';

Implicit Type Conversion

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

-- ไม่ดี (phone เป็น VARCHAR แต่เทียบกับ INT)
SELECT * FROM users WHERE phone = 0812345678;

-- ดีกว่า (ชนิดข้อมูลตรงกัน)
SELECT * FROM users WHERE phone = '0812345678';

เทคนิคการปรับแต่ง JOIN

JOIN เป็นคำสั่งที่มักเป็นจุดคอขวด โดยเฉพาะเมื่อเชื่อม Table หลายตัวที่มีข้อมูลมาก หลักการสำคัญคือ

สร้างดัชนีบนคอลัมน์ที่ใช้ JOIN — ทั้ง Foreign Key และ Primary Key ควรมีดัชนีเสมอ โดย Primary Key มีดัชนีอยู่แล้วโดยอัตโนมัติ แต่ Foreign Key ต้องสร้างเอง

CREATE INDEX idx_orders_customer ON orders (customer_id);

ให้ชนิดข้อมูลตรงกัน — คอลัมน์ที่ JOIN ต้องมี Data Type และ Collation เดียวกัน มิฉะนั้นเซิร์ฟเวอร์ต้องแปลงทุก Row ก่อนเปรียบเทียบ

กรองก่อน JOIN — ถ้า WHERE condition กรองข้อมูลได้มาก ให้ใส่เงื่อนไขที่ลดจำนวน Row มากที่สุดไว้ก่อน เพื่อลดจำนวน Row ที่ต้อง JOIN

-- ดี: กรอง orders ก่อนแล้วค่อย JOIN
SELECT c.name, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2025-01-01';

ปรับแต่ง Subquery ให้เร็วขึ้น

Subquery แบบ Correlated (ที่อ้างอิงตารางด้านนอก) มักช้ามากเพราะทำงานซ้ำทุก Row ของ Query หลัก ควรเปลี่ยนเป็น JOIN เมื่อทำได้

-- ช้า (Correlated Subquery — รันทุก Row)
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total_amount > 1000);

-- เร็วกว่า (JOIN)
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.total_amount > 1000;

อย่างไรก็ตาม Optimizer ในเวอร์ชัน 8.0 ฉลาดขึ้นมากและสามารถแปลง Subquery บางรูปแบบเป็น JOIN ได้อัตโนมัติ ควรใช้ EXPLAIN ตรวจสอบทุกครั้งว่า Execution Plan ต่างกันหรือไม่

Pagination ที่มีประสิทธิภาพ

LIMIT … OFFSET เป็นวิธี Pagination ที่ใช้กันทั่วไป แต่จะช้ามากเมื่อ Offset สูง เพราะเซิร์ฟเวอร์ต้อง Scan และทิ้ง Row จำนวนมากก่อนส่งผลลัพธ์

-- ช้าเมื่อ OFFSET สูง (ต้อง Scan 100,000 Row แล้วทิ้ง)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 100000;

วิธีที่ดีกว่าคือใช้ Keyset Pagination (หรือ Cursor-based Pagination) ที่ใช้ค่า ID ของ Row สุดท้ายในหน้าก่อนหน้าเป็นจุดเริ่มต้น

-- เร็ว: ใช้ Index ข้าม Row ที่ไม่ต้องการ
SELECT * FROM products WHERE id > 100000 ORDER BY id LIMIT 20;

วิธีนี้ใช้ Index ข้ามไปยังจุดเริ่มต้นได้ทันที ไม่ต้อง Scan Row ที่ไม่ต้องการ Performance จึงคงที่ไม่ว่าจะอยู่หน้าที่เท่าไหร่

ค้นหา Slow Query ด้วย Slow Query Log

Slow Query Log เป็นฟีเจอร์ที่บันทึกทุกคำสั่งที่ใช้เวลานานเกินกำหนด ช่วยให้ค้นหาคำสั่งที่ต้องปรับแต่งได้โดยไม่ต้องเดา

-- เปิดใช้งาน Slow Query Log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

คำสั่งข้างต้นเปิด Log สำหรับคำสั่งที่ใช้เวลาเกิน 1 วินาที สามารถปรับค่าให้ต่ำลงเป็น 0.5 หรือ 0.1 วินาทีเพื่อจับคำสั่งที่ช้าปานกลางได้ด้วย

วิเคราะห์ Slow Query Log ด้วย mysqldumpslow

เครื่องมือ mysqldumpslow ช่วยสรุปรายงานจาก Slow Query Log ให้อ่านง่าย

# แสดง 10 คำสั่งที่ช้าที่สุด
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# แสดงคำสั่งที่ถูกเรียกบ่อยที่สุด
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

Option -s t เรียงตามเวลา (time), -s c เรียงตามจำนวนครั้ง (count), -t 10 แสดง 10 อันดับแรก การปรับแต่งคำสั่งที่ถูกเรียกบ่อยและช้าจะให้ผลลัพธ์ดีที่สุด

Performance Schema สำหรับการวิเคราะห์ขั้นสูง

Performance Schema เป็นเครื่องมือ Monitoring ที่ติดมากับระบบฐานข้อมูล ให้ข้อมูลเชิงลึกกว่า Slow Query Log เช่น สถิติรวมของแต่ละคำสั่ง, จำนวนครั้งที่ถูกเรียก, เวลาเฉลี่ย และจำนวน Row ที่ Scan

-- คำสั่งที่ใช้เวลารวมมากที่สุด
SELECT DIGEST_TEXT, COUNT_STAR,
       ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS total_sec,
       ROUND(AVG_TIMER_WAIT/1000000000000, 4) AS avg_sec,
       SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

ผลลัพธ์แสดงคำสั่งที่กินทรัพยากรมากที่สุด ช่วยจัดลำดับความสำคัญว่าควรปรับแต่งอะไรก่อน

เทคนิคเพิ่มเติมที่ควรรู้

ใช้ LIMIT เมื่อต้องการแค่บางส่วน

หากต้องการเพียงตรวจสอบว่ามีข้อมูลอยู่หรือไม่ ให้ใช้ LIMIT 1 แทนการดึงทั้งหมดมานับ

-- ไม่ดี
SELECT COUNT(*) FROM orders WHERE customer_id = 1234;

-- ดีกว่า (ถ้าต้องการแค่รู้ว่า "มีหรือไม่มี")
SELECT 1 FROM orders WHERE customer_id = 1234 LIMIT 1;

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

DISTINCT บังคับให้เซิร์ฟเวอร์ Sort และกรองข้อมูลซ้ำ ควรใช้เมื่อจำเป็นจริง ๆ เท่านั้น บ่อยครั้งที่ DISTINCT ถูกใช้เพื่อแก้ปัญหา JOIN ที่ให้ผลลัพธ์ซ้ำ ซึ่งควรแก้ที่ JOIN แทน

Batch INSERT แทน INSERT ทีละ Row

-- ช้า: INSERT ทีละ Row (แต่ละคำสั่งเป็น 1 Transaction)
INSERT INTO logs (msg) VALUES ('log1');
INSERT INTO logs (msg) VALUES ('log2');
INSERT INTO logs (msg) VALUES ('log3');

-- เร็วกว่ามาก: INSERT หลาย Row ในคำสั่งเดียว
INSERT INTO logs (msg) VALUES ('log1'), ('log2'), ('log3');

Batch INSERT ลดจำนวน Round Trip และ Transaction Overhead ลงอย่างมาก สำหรับข้อมูลจำนวนมากอาจเร็วกว่า 10-100 เท่า

ใช้ Prepared Statements

Prepared Statements ช่วยให้เซิร์ฟเวอร์ Parse และ Compile คำสั่งเพียงครั้งเดียว แล้วรันซ้ำได้หลายครั้งโดยเปลี่ยนเฉพาะค่า Parameter ช่วยลดเวลา Parse และป้องกัน SQL Injection ด้วย

ดูแลดัชนีให้มีประสิทธิภาพต่อเนื่อง

ดัชนีที่สร้างแล้วต้องดูแลรักษา เพราะเมื่อข้อมูลถูก INSERT, UPDATE, DELETE ไปเรื่อย ๆ อาจเกิด Fragment ทำให้ช้าลง

-- วิเคราะห์สถิติ Index
ANALYZE TABLE orders;

-- สร้าง Index ใหม่ (Rebuild)
ALTER TABLE orders ENGINE=InnoDB;

ANALYZE TABLE อัพเดตสถิติที่ Optimizer ใช้ตัดสินใจ ควรรันหลังจากข้อมูลเปลี่ยนแปลงมาก ส่วน ALTER TABLE ENGINE=InnoDB เป็นเทคนิค Rebuild ตารางและ Index ทั้งหมดให้กลับมาเรียงอย่างมีประสิทธิภาพ

นอกจากนี้ควรลบดัชนีที่ไม่ได้ใช้ออก เพราะดัชนีทุกตัวทำให้ INSERT, UPDATE, DELETE ช้าลง ตรวจสอบดัชนีที่ไม่ได้ใช้ได้จาก Performance Schema

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 = 'your_database'
ORDER BY object_name;

สรุป

การปรับแต่ง Query ให้เร็วขึ้นไม่ใช่เรื่องยากหากทำอย่างเป็นระบบ เริ่มจากใช้ EXPLAIN วิเคราะห์ว่าเซิร์ฟเวอร์ทำงานอย่างไร จากนั้นสร้าง Index ที่เหมาะสมกับ Query Pattern ที่ใช้บ่อย หลีกเลี่ยงรูปแบบที่ทำลายการใช้ Index เช่น Function บนคอลัมน์, Leading Wildcard และ Implicit Type Conversion ใช้ Slow Query Log และ Performance Schema ค้นหาจุดที่ต้องปรับปรุง และดูแล Index ให้มีประสิทธิภาพต่อเนื่อง เมื่อทำตามเทคนิคเหล่านี้ ฐานข้อมูลจะตอบสนองได้เร็วขึ้นอย่างเห็นได้ชัด

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

การปรับแต่ง Query ให้ดีแค่ไหน ก็ต้องอาศัย Hardware ที่รองรับด้วย Cloud VPS ของ DE มาพร้อม SSD Storage ที่ให้ IOPS สูง ช่วยให้ Index Lookup และ Table Scan ทำงานได้เร็ว เหมาะกับฐานข้อมูลที่ต้องรองรับ Query จำนวนมาก

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