คอลัมน์ที่ไม่ได้ทำ Index ทำฐานข้อมูลของเราพังได้อย่างไร
แค่การลืมทำ Index เพียงจุดเดียว ก็เปลี่ยน API ที่เคยเร็ว 8 ms ให้กลายเป็นฝันร้ายนาน 8 วินาที
เราทำการทดสอบ Load Test ก่อนการปล่อยเวอร์ชันสำคัญ ในขั้นตอนการพัฒนาบนเครื่อง Local ทุกอย่างทำงานได้ดีด้วยข้อมูลเพียง 100 เรคคอร์ด แต่พอเราจำลองผู้ใช้งาน 500 คน กับข้อมูลขนาดเท่า Production ที่มีถึง 1.5 ล้านแถว
ผลลัพธ์ที่ได้นั้นแย่มาก:
- เวลาตอบสนองของ API พุ่งจาก 45 ms เป็น 8,000 ms
- CPU ของฐานข้อมูลพุ่งสูงถึง 100 %
- Connection pools เต็ม จนทำให้เกิดข้อผิดพลาด Timeout
เราตรวจสอบ slow-query log และพบ endpoint หนึ่งที่ดึงข้อมูลประวัติการสั่งซื้อ (order history) โดยใช้ user ID และ status
เมื่อรัน EXPLAIN ANALYZE บน PostgreSQL ผลปรากฏว่าเป็น Sequential Scan เนื่องจากไม่มีการทำ index บน user_id ทำให้ engine ต้องอ่านข้อมูลทั้ง 1.5 ล้านแถวในทุกๆ request
เมื่อมี 100 concurrent requests ระบบต้องสแกนข้อมูลถึง 150 ล้านแถวในคราวเดียว
การแก้ไขใช้เวลาเพียงห้านาที
แทนที่จะทำ index แบบธรรมดาบน user_id เราเลือกสร้าง composite index บน (user_id, status, created_at DESC) ซึ่งช่วยให้ฐานข้อมูลสามารถ:
- กรองข้อมูลด้วย
user_id - กรองข้อมูลด้วย
status - คืนค่าแถวข้อมูลล่าสุดได้ทันที
- ข้ามขั้นตอนการเรียงลำดับ (sorting) ที่ไม่จำเป็น
เราใช้ CREATE INDEX CONCURRENTLY เพื่อให้ตารางไม่ถูกล็อก (unlocked) ในระหว่างการทำงาน
หลังการแก้ไข:
- เวลาในการ Query ลดลงจาก 8,150 ms เหลือเพียง 0.14 ms
- API latency ลดลงจาก 8 วินาที เหลือเพียง 12 ms
- การใช้งาน CPU ลดลงจาก 100 % เหลือไม่ถึง 8 %
บทเรียนที่ได้รับ:
- การทดสอบบนเครื่อง Local อาจทำให้เข้าใจผิดได้ เพราะข้อมูล 100 แถวไม่ได้สะท้อนถึงข้อมูลหลักล้าน
- ควรทำ index ให้กับ foreign keys เพราะ ORMs ส่วนใหญ่มักจะข้ามขั้นตอนนี้ไป
- รัน
EXPLAIN ANALYZEก่อนที่จะตัดสินใจซื้อฮาร์ดแวร์เพิ่ม - ออกแบบ index โดยอิงจาก query ที่คุณใช้งานจริง
อย่าเพิ่งรีบ scale server แต่ให้เริ่มจากการ scale query ก่อน