กลยุทธ์การทำ Database Index ฉบับใช้งานจริง: เลือก เรียงคอลัมน์ และตัด Index ที่ไม่จำเป็น

Index คือการยอมจ่ายต้นทุนการเขียนและพื้นที่ disk เพื่อแลกกับความเร็วในการอ่าน เลือกดีแล้ว query ที่ใช้ 2 วินาทีจะเหลือ 2 มิลลิวินาที เลือกผิดแล้ว ทุกการ insert จะช้าลงในขณะที่ planner ไม่แม้แต่จะใช้ index นั้น บทความนี้อธิบายวิธีเลือกให้ดี โดยใช้ตัวอย่างจาก PostgreSQL แม้ว่าแนวคิดส่วนใหญ่จะใช้ได้กับ relational database ทุกตัว

Index คืออะไรกันแน่

Index คือโครงสร้างข้อมูลแยกต่างหากที่ map ค่าในคอลัมน์ไปยังตำแหน่งของแถว โดยจัดเก็บแบบเรียงลำดับหรือแบบ hash เพื่อให้การค้นหาไม่ต้อง scan ทั้งตาราง ทุก INSERT ทุก DELETE และทุก UPDATE บนคอลัมน์ที่มี index จะต้องอัปเดต index ที่เกี่ยวข้องทุกตัวไปด้วย ทุก index มีต้นทุนต่อเนื่องที่ต้องจ่ายในทุกครั้งที่เขียนข้อมูล

ประเภทของ index และการใช้งาน

B-tree (ค่าเริ่มต้น)

Tree ที่สมดุลซึ่งเก็บคีย์แบบเรียงลำดับ รองรับ:

• การเท่ากัน: =, IN
• ช่วงค่า: <, >, BETWEEN
• การเรียงลำดับ: ORDER BY อ่าน index ตามลำดับได้เลยโดยข้ามขั้นตอน sort
• การจับคู่ prefix: LIKE 'abc%' (เมื่อใช้ collation หรือ operator class ที่เหมาะสม)

ถ้าไม่แน่ใจว่าจะใช้ประเภทไหน ให้ใช้ B-tree

Hash

รองรับเฉพาะการเท่ากัน อาจเล็กกว่า B-tree เล็กน้อยสำหรับคีย์ที่ยาว แต่รองรับช่วงค่าหรือการเรียงลำดับไม่ได้ ใช้ให้น้อย และใช้เฉพาะเมื่อวัดผลแล้วว่าได้เปรียบจริง

GIN (Generalized Inverted Index)

Map แต่ละ องค์ประกอบ ภายในค่าหนึ่ง ๆ ไปยังแถวที่มีองค์ประกอบนั้น เหมาะกับ:

• การตรวจว่ามีค่าอยู่ใน JSONB: WHERE attributes @> '{"color": "red"}'
• Array: WHERE tags && ARRAY['go', 'sql']
• Full-text search: WHERE document @@ to_tsquery('index & strategy')
• Trigram similarity (ด้วย pg_trgm): WHERE name ILIKE '%phat%'

GIN index อ่านได้เร็วแต่อัปเดตค่อนข้างช้า จึงต้องระวังกับตารางที่มีการเขียนหนัก

GiST และ SP-GiST

เป็นเฟรมเวิร์กสำหรับข้อมูลที่ซับซ้อนกว่า เช่น รูปทรงเรขาคณิต, ช่วงค่า (การซ้อนทับของ tstzrange), การค้นหา nearest-neighbor และช่วง IP ใช้เมื่อคุณ query แบบ "ซ้อนทับกัน" หรือ "ใกล้ที่สุด" มากกว่า "เท่ากับ"

BRIN (Block Range Index)

เก็บเพียงค่า min/max ของแต่ละช่วง block ทางกายภาพของตาราง จึงมีขนาดเล็กมาก เหมาะกับตารางขนาดใหญ่มากที่เขียนแบบต่อท้ายอย่างเดียว (append-only) ซึ่งคอลัมน์มีลำดับตามการ insert เช่น timestamp ใน log ของ event BRIN index บนตารางขนาด 500 GB อาจมีขนาดเพียงไม่กี่เมกะไบต์

Composite index: ลำดับคอลัมน์สำคัญ

Index บน (a, b, c) จะเรียงตาม a ก่อน จากนั้นเรียงตาม b ภายในแต่ละค่าของ a แล้วจึงเรียงตาม c ลองนึกถึงสมุดโทรศัพท์ที่เรียงตามนามสกุลแล้วตามด้วยชื่อ ลำดับนี้เป็นตัวกำหนดว่า index จะรองรับ query แบบไหนได้บ้าง:

กฎการเรียงลำดับ

1. คอลัมน์ที่เทียบเท่ากันอยู่ก่อน คอลัมน์ช่วงค่าอยู่ท้ายสุด สำหรับ WHERE tenant_id = ? AND created_at > ? ให้สร้าง index (tenant_id, created_at) ถ้าสลับลำดับ ฐานข้อมูลจะต้องไล่ดูแถวของทุก tenant ในช่วงเวลานั้น
2. ให้ตรงกับลำดับการ sort สำหรับ WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20 ให้สร้าง index (customer_id, created_at) ฐานข้อมูลจะกระโดดไปที่ลูกค้ารายนั้น อ่าน 20 รายการตามลำดับ แล้วหยุด
3. คิดจากรูปแบบของ query ไม่ใช่จากคอลัมน์ ออกแบบ index รอบ ๆ query สำคัญอันดับต้น ๆ ไม่ใช่สร้าง index แยกให้ทุกคอลัมน์

ทำไมไม่สร้าง index แยกให้ทุกคอลัมน์?

Index คอลัมน์เดี่ยวแยกกันบน a และ b สามารถนำมารวมกันได้ (bitmap AND ใน Postgres) แต่มักช้ากว่า composite index ตัวเดียวที่ออกแบบมาสำหรับ query นั้นมาก และยังเพิ่ม overhead ในการเขียนเป็นสองเท่าด้วย

Covering index และ index-only scan

ถ้า index มีครบทุกคอลัมน์ที่ query ต้องการ ฐานข้อมูลจะตอบได้จาก index อย่างเดียวโดยไม่ต้องไปแตะตาราง นั่นคือ index-only scan:

CREATE INDEX orders_customer_recent_idx
    ON orders (customer_id, created_at DESC)
    INCLUDE (status, total_cents);

-- Served entirely from the index:
SELECT created_at, status, total_cents
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 20;

INCLUDE เพิ่มคอลัมน์ข้อมูลลงไปใน leaf page โดยไม่นับเป็นส่วนหนึ่งของ sort key ทำให้ tree ยังกะทัดรัดแต่ครอบคลุม query ได้ ใน PostgreSQL การทำ index-only scan ยังขึ้นอยู่กับว่า visibility map อัปเดตล่าสุดหรือไม่ ซึ่งเป็นอีกเหตุผลหนึ่งที่ต้องดูแลให้ vacuum ทำงานได้ดี

Partial index: สร้าง index เฉพาะส่วนที่ query

ถ้า query มุ่งไปที่แถวกลุ่มเล็ก ๆ เสมอ ให้สร้าง index เฉพาะกลุ่มนั้น:

-- 98% of orders are 'completed'; the app only queries the active ones
CREATE INDEX orders_active_idx
    ON orders (created_at)
    WHERE status IN ('pending', 'processing');

Index จะมีขนาดเพียงเสี้ยวเดียว อยู่ใน memory ได้ และไม่มีต้นทุนเลยเมื่อเขียน order ที่เสร็จสมบูรณ์แล้ว Partial unique index ยังใช้บังคับกฎอย่าง "ผู้ใช้หนึ่งคนมี subscription ที่ active ได้เพียงรายการเดียว" ได้ด้วย:

CREATE UNIQUE INDEX one_active_sub
    ON subscriptions (user_id)
    WHERE cancelled_at IS NULL;

Expression index

เมื่อ query กรองด้วยค่าที่คำนวณได้ ให้สร้าง index บนนิพจน์นั้น:

CREATE INDEX users_email_ci_idx ON users (LOWER(email));
-- matches: WHERE LOWER(email) = LOWER($1)

Query ต้องใช้นิพจน์เดียวกันทุกตัวอักษร planner จึงจะจับคู่ได้

Selectivity: เมื่อ index ไม่ช่วยอะไร

Index จะคุ้มค่าเมื่อช่วยคัดผลลัพธ์ให้เหลือเพียงสัดส่วนเล็ก ๆ ของตาราง index เดี่ยวบนคอลัมน์ boolean is_active ที่ 90% ของแถวเป็น true แทบไม่มีประโยชน์ในการหาแถวที่ active การอ่านทั้งตารางแบบเรียงลำดับถูกกว่าการ lookup ใน index แบบสุ่มนับล้านครั้ง Planner รู้เรื่องนี้และจะไม่ใช้ index นั้น แต่คุณก็ยังต้องจ่ายค่าดูแลมันอยู่ดี

คอลัมน์ที่ selectivity ต่ำควรอยู่ใน composite index (ตามหลังคอลัมน์นำที่ selectivity สูง) หรืออยู่ในเงื่อนไขของ partial index

หา index ที่มีแต่ต้นทุน

Index มักสะสมเพิ่มขึ้นเรื่อย ๆ มีคนเพิ่มเข้ามาเพื่อแก้ incident แล้วก็ไม่มีใครลบออก ให้ตรวจสอบเป็นประจำ:

-- Indexes never used since statistics were last reset
SELECT schemaname, relname AS table, indexrelname AS index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

นอกจากนี้ให้มองหา:

• Index ซ้ำ: index สองตัวที่นิยามเหมือนกันทุกประการ
• Prefix ที่ซ้ำซ้อน: index บน (a) มักซ้ำซ้อนถ้ามี (a, b) อยู่แล้ว เพราะ composite index รองรับการค้นหา a = ? ได้อยู่แล้ว
• Bloat: index บนตารางที่ถูกอัปเดตหนัก ๆ อาจบวมใหญ่กว่าข้อมูลที่ใช้งานจริงมาก REINDEX CONCURRENTLY จะสร้างใหม่โดยไม่ block การเขียน

ก่อนจะ drop index ให้ตรวจว่า statistics ครอบคลุมช่วงเวลาที่เป็นตัวแทนได้ดี รวมถึง job สิ้นเดือนด้วย และตรวจว่า index นั้นไม่ได้ใช้บังคับความ unique อยู่

สร้าง index บน production อย่างปลอดภัย

CREATE INDEX แบบธรรมดาจะ block การเขียนลงตารางระหว่างสร้าง บนตารางที่มีงานหนาแน่น นั่นหมายถึง downtime ให้ใช้:

CREATE INDEX CONCURRENTLY orders_customer_created_idx
    ON orders (customer_id, created_at);

วิธีนี้ใช้เวลานานกว่าและรันภายใน transaction ไม่ได้ แต่ตารางยังเขียนได้ตามปกติ ถ้าล้มเหลวกลางทาง มันจะทิ้ง index สถานะ INVALID ไว้ ให้ drop ทิ้งแล้วลองใหม่

สรุป

• ใช้ B-tree เป็นค่าเริ่มต้น ใช้ GIN สำหรับ JSONB, array และ text search และใช้ BRIN สำหรับตารางขนาดใหญ่มากแบบ append-only
• ออกแบบ composite index ตามรูปแบบของ query: คอลัมน์เท่ากันก่อน คอลัมน์ช่วงค่าท้ายสุด และให้ตรงกับลำดับการ sort
• ใช้ INCLUDE เพื่อให้เกิด index-only scan และใช้ partial index กับกลุ่มข้อมูลที่ถูกใช้บ่อย
• อย่าสร้าง index เดี่ยวบนคอลัมน์ที่ selectivity ต่ำ
• ตรวจสอบเป็นประจำ และ drop index ที่ไม่ได้ใช้ ซ้ำกัน หรือซ้ำซ้อน
• สร้าง index แบบ CONCURRENTLY บนระบบที่ใช้งานจริง

Index จำนวนน้อยที่ออกแบบมาอย่างดี แทบจะชนะ index จำนวนมากที่สร้างเผื่อไว้แบบเดาสุ่มเสมอ