B-tree, composite และ covering index ทำงานอย่างไร และกฎ leftmost-prefix คืออะไร?

Question

IT Interview Practice · Accepted Answer

Index ของ InnoDB เป็น **B+tree**: ต้นไม้ที่สมดุลซึ่งใบ (leaf) ทุกใบอยู่ที่ความลึกเท่ากันและใบต่าง ๆ ถูกเชื่อมโยงกันเพื่อทำ range scan ได้เร็ว การค้นหามีความซับซ้อน O(log n) — อ่านหน้าเพียงไม่กี่หน้าแม้กับตารางขนาดมหึมา

## Composite index และ leftmost prefix

Composite index `(a, b, c)` เรียงแถวตาม `a` แล้วตาม `b` แล้วตาม `c` คุณจะใช้มันได้เฉพาะกับ **leftmost prefix** ของคอลัมน์เท่านั้น: `a`, `(a, b)` หรือ `(a, b, c)` — แต่ **ไม่ใช่** `b` เดี่ยว ๆ หรือ `(b, c)` เพราะคอลัมน์เหล่านั้นยังไม่ถูกเรียงจนกว่าจะกำหนดค่า `a` ไว้ก่อน

```sql
CREATE INDEX idx_abc ON events (user_id, status, created_at);

-- USES the index (leftmost prefix, range on the last column)
SELECT * FROM events
WHERE user_id = 7 AND status = 'open' AND created_at > '2026-01-01';

-- Does NOT use it: skips the leftmost column
SELECT * FROM events WHERE status = 'open';
```

จัดลำดับคอลัมน์โดยเอาแบบที่เลือกได้มากที่สุด / แบบ equality ไว้ก่อน และวางคอลัมน์ที่เป็น range ไว้ท้ายสุด: เมื่อคุณเจอ range แล้ว (`>`, `BETWEEN`) คอลัมน์ที่อยู่หลังจากนั้นจะใช้ในการ seek ไม่ได้

## Covering index

หาก index มี **ทุกคอลัมน์ที่ query อ่าน** InnoDB จะตอบจาก index ได้เลย — ไม่ต้องย้อนกลับไปที่ clustered index EXPLAIN จะแสดง `Using index`

```sql
CREATE INDEX idx_cover ON events (user_id, status, id);

EXPLAIN SELECT id FROM events WHERE user_id = 7 AND status = 'open';
-- Extra: Using index      <- covering, no clustered-index lookup
```

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

Composite และ covering index คือวิธีที่คุณเปลี่ยน query หลายเงื่อนไขที่ช้าให้กลายเป็นการ seek index เพียงครั้งเดียว การจัดลำดับคอลัมน์ผิด — หรือคาดหวังว่า query บนคอลัมน์ที่สองจะใช้ index ได้ — เป็นสาเหตุที่พบบ่อยที่สุดที่ index ซึ่ง "มีอยู่" กลับยังไม่ถูกนำมาใช้

Composite index และ leftmost prefix

Covering index

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

คำถามที่เกี่ยวข้อง