คุณเพิ่มประสิทธิภาพ query SQL ที่ช้าได้อย่างไร?

Question

Accepted Answer

การเพิ่มประสิทธิภาพ query ที่ช้าเริ่มต้นด้วยการ **วิเคราะห์ execution plan** (`EXPLAIN`) เพื่อหาคอขวด จากนั้นจึงใช้การแก้ไขที่ตรงจุด — ที่พบบ่อยที่สุดคือ **การเพิ่ม index**, การเขียน query ใหม่ และการหลีกเลี่ยง anti-patterns ที่พบบ่อย ชัยชนะที่ใหญ่ที่สุดมักคือการเปลี่ยน full table scans ให้เป็น index lookups

## ขั้นตอนที่ 1: วิเคราะห์ด้วย EXPLAIN

```sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5;
-- shows the EXECUTION PLAN: scan type (Seq Scan = full scan, Index Scan = using an index),
-- estimated/actual rows, cost, and timing → reveals WHERE the time goes
```

`EXPLAIN` (หรือ `EXPLAIN ANALYZE`) แสดงว่าฐานข้อมูลดำเนินการ query อย่างไร — ที่สำคัญคือ ว่ามันกำลังทำ **full table scan** (ช้า) หรือใช้ **index** (เร็ว) ควรทำ profile ก่อนเสมอก่อนเพิ่มประสิทธิภาพ

## ชัยชนะที่ใหญ่ที่สุด: เพิ่ม index

```sql
-- if EXPLAIN shows a Seq Scan on a filtered/joined column → add an index
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- turns a full scan into a fast index lookup
```

การแก้ไขที่พบบ่อยและส่งผลมากที่สุด: **เพิ่ม index** บนคอลัมน์ที่ใช้ใน `WHERE`, `JOIN` และ `ORDER BY` ซึ่งเปลี่ยน full table scans ให้เป็น lookups ที่รวดเร็ว

## Anti-patterns ที่พบบ่อยที่ควรแก้ไข

```sql
-- ❌ functions on a column prevent index use
WHERE UPPER(email) = 'X@Y.COM'        -- can't use an index on email
WHERE email = 'x@y.com'               -- ✅ can use the index

-- ❌ leading wildcard LIKE can't use an index
WHERE name LIKE '%smith'              -- ✅ 'smith%' (prefix) can use an index

-- ❌ SELECT * fetches unneeded columns
SELECT * → SELECT only_needed_columns  -- less data, may use a covering index

-- ❌ N+1 queries (querying in a loop) → use a single JOIN instead
```

## เทคนิคการเพิ่มประสิทธิภาพอื่น ๆ

```text
✓ Fix N+1 queries (one query per row) → use JOINs / batch into one query
✓ Add appropriate indexes (including COMPOSITE for multi-column filters)
✓ Reduce data scanned — filter early, select only needed columns, paginate
✓ Rewrite subqueries as JOINs where more efficient
✓ Use EXISTS instead of IN for existence checks on large subqueries
✓ Keep statistics updated (ANALYZE) so the planner makes good choices
✓ Consider denormalization / materialized views for expensive repeated queries
✓ Watch for missing indexes on FOREIGN KEYS (joins scan without them)
```

## ทำไมจึงสำคัญ

การเพิ่มประสิทธิภาพ query เป็นความรู้ที่สำคัญยิ่งสำหรับประสิทธิภาพของฐานข้อมูล — query ที่ช้าเป็นสาเหตุสำคัญของประสิทธิภาพแอปพลิเคชันที่แย่ และการเข้าใจวิธีค้นหาและแก้ไขเป็นทักษะระดับ senior ที่จำเป็นสำหรับทุกคนที่ทำงานกับฐานข้อมูลในระดับใหญ่

วินัยพื้นฐานคือ **การวิเคราะห์ด้วย `EXPLAIN`** (การตรวจสอบ execution plan เพื่อหาคอขวดที่แท้จริง — ที่สำคัญคือ ว่า query ทำ full table scan ที่ช้าหรือ index lookup ที่เร็ว) ก่อนการเพิ่มประสิทธิภาพ แทนที่จะเดา — แนวทางที่ขับเคลื่อนด้วยการวัดผลนี้คือสิ่งที่ทำให้การเพิ่มประสิทธิภาพได้ผล

**ชัยชนะที่ใหญ่ที่สุดและพบบ่อยที่สุดคือการเพิ่ม index** บนคอลัมน์ที่กรอง, join และเรียงลำดับ (เปลี่ยน full table scans ให้เป็น lookups ที่รวดเร็ว — มักปรับปรุงเวลา query ได้หลายเท่าตัว) ทำให้ความรู้เรื่อง index เป็นหัวใจของการเพิ่มประสิทธิภาพ

การเข้าใจ **anti-patterns ที่พบบ่อย** สำคัญพอ ๆ กัน: functions บนคอลัมน์และ `LIKE` ที่มี wildcard นำหน้าป้องกันการใช้ index, `SELECT *` ดึงข้อมูลที่ไม่จำเป็น และโดยเฉพาะ **N+1 queries** (การ query ใน loop แทนที่จะใช้ JOIN เดียว — ตัวการทำลายประสิทธิภาพที่รุนแรงและพบบ่อยในโค้ดแอปพลิเคชัน) — การจดจำและแก้ไขสิ่งเหล่านี้จัดการกับสาเหตุที่พบบ่อยที่สุดของ query ที่ช้า

เทคนิคอื่น ๆ (composite indexes, การลดข้อมูลที่สแกน, การเขียน subqueries ใหม่เป็น JOINs, การอัปเดต statistics, materialized views สำหรับ query ที่มีต้นทุนสูง) ทำให้ชุดเครื่องมือสมบูรณ์

เนื่องจากประสิทธิภาพของ query ส่งผลโดยตรงต่อการตอบสนองและความสามารถในการ scale ของแอปพลิเคชัน และเนื่องจากแนวทางที่เป็นระบบ (วิเคราะห์ด้วย EXPLAIN จากนั้นแก้ไข — มักด้วยการเพิ่ม index และกำจัด anti-patterns เช่น N+1) คือสิ่งที่ทำให้ฐานข้อมูลเร็วภายใต้ภาระงาน การเชี่ยวชาญการเพิ่มประสิทธิภาพ query จึงเป็นความรู้ระดับ senior ที่สำคัญและมีคุณค่าสูงซึ่งแยกแยะนักพัฒนาที่สามารถสร้างแอปพลิเคชันที่ใช้ฐานข้อมูลซึ่งมีประสิทธิภาพและ scale ได้ — เป็นทักษะสำคัญที่เกี่ยวข้องบ่อยและเป็นหัวข้อสัมภาษณ์ที่พบบ่อยสำหรับประสิทธิภาพของฐานข้อมูล