Composite Index — Khi một cột index không đủ

· Mid Guy · 14 phút đọc · ♥ 48.0K · 💬 91
databasepostgresqlmysqlperformancebackend

Query chạy ngon trong dev, lên production với 10 triệu rows thì timeout. Thêm index vào cột WHERE nhưng EXPLAIN vẫn báo Seq Scan. Nghe quen không?

Vấn đề thường nằm ở chỗ bạn có nhiều điều kiện lọc nhưng chỉ index từng cột riêng lẻ. Đây là lúc Composite Index (hay Multi-column Index) phát huy tác dụng — và cũng là lúc nhiều người dùng sai.

Index đơn lẻ hoạt động thế nào

Trước khi nói composite, cần hiểu index đơn hoạt động ra sao.

B-tree index trên cột status thực chất là một cây nhị phân cân bằng, các nút lưu giá trị đã được sort. Khi query WHERE status = 'active', DB walk cây này để tìm vị trí giá trị đó, rồi đọc danh sách row ID (heap pointer) từ leaf node.

-- Bảng orders, 10 triệu rows
CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  user_id     BIGINT,
  status      VARCHAR(20),
  created_at  TIMESTAMPTZ,
  total       NUMERIC(12,2)
);

-- Index đơn
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_user_id ON orders(user_id);

Query:

SELECT * FROM orders
WHERE user_id = 12345
  AND status = 'pending';

Với hai index đơn, PostgreSQL có mấy lựa chọn:

  1. Dùng idx_user_id, scan kết quả, filter thêm status
  2. Dùng idx_status, scan kết quả, filter thêm user_id
  3. Bitmap Index Scan: dùng cả hai, AND bitmap lại

Option 3 nghe hay nhưng Bitmap Scan có overhead cao — nó tạo bitmap trong memory, phù hợp khi kết quả lớn nhưng không tối ưu cho OLTP query cần latency thấp.

Vấn đề cốt lõi: hai index đơn không thể kết hợp thành một đường đọc duy nhất hiệu quả bằng một composite index.

Composite Index là gì

Composite Index là một B-tree duy nhất được build trên nhiều cột theo thứ tự xác định. Dữ liệu trong cây được sort theo cột đầu tiên, rồi với cùng giá trị cột đầu thì sort theo cột thứ hai, và cứ thế tiếp.

CREATE INDEX idx_user_status ON orders(user_id, status);

Index này lưu dữ liệu kiểu:

(user_id=100, status='cancelled')
(user_id=100, status='pending')
(user_id=100, status='shipped')
(user_id=101, status='pending')
(user_id=101, status='shipped')
(user_id=102, status='active')
...

Với query WHERE user_id = 12345 AND status = 'pending', DB tìm user_id = 12345 trong cây (log N), rồi ngay tại đó tìm tiếp status = 'pending' (log N nữa). Một lần đọc index, một tập kết quả chính xác.

Luật then chốt: Leftmost Prefix

Đây là phần quan trọng nhất và cũng là nguồn gốc mọi nhầm lẫn.

Composite index (a, b, c) có thể được dùng cho:

  • WHERE a = ?
  • WHERE a = ? AND b = ?
  • WHERE a = ? AND b = ? AND c = ?
  • WHERE a = ? AND b BETWEEN ? AND ?

Và không thể (hoặc kém hiệu quả) cho:

  • WHERE b = ? — bỏ qua cột đầu
  • WHERE c = ? — bỏ qua hai cột đầu
  • WHERE b = ? AND c = ? — không có a

Nguyên nhân: Index chỉ sort theo a trước. Nếu không có điều kiện trên a, DB không biết bắt đầu tìm b ở đâu trong cây — phải full scan index, đôi khi còn chậm hơn Seq Scan.

-- Có index (user_id, status, created_at)

-- DÙNG ĐƯỢC index
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'pending' AND created_at > '2026-01-01';

-- KHÔNG dùng index hiệu quả
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
EXPLAIN SELECT * FROM orders WHERE created_at > '2026-01-01';

Kiểm tra thực tế với EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE status = 'pending'
  AND created_at > '2026-01-01';

Nếu output có Index Cond: (user_id = ...) nhưng Filter: (status = ...) — nghĩa là index chỉ dùng được phần user_id, phần status vẫn phải filter sau. Đây là dấu hiệu cần điều chỉnh thứ tự cột.

Thứ tự cột quyết định tất cả

Giả sử hệ thống có hai query chính:

-- Query A: lọc theo user + status
SELECT * FROM orders WHERE user_id = ? AND status = ?;

-- Query B: lọc theo status + khoảng thời gian
SELECT * FROM orders WHERE status = ? AND created_at BETWEEN ? AND ?;

Cần chọn: (user_id, status) hay (status, user_id) hay cần hai index khác nhau?

Nguyên tắc 1: Equality trước, Range sau

Cột dùng = nên đứng trước cột dùng >, <, BETWEEN, LIKE.

-- TỐT: equality (user_id = ?) trước range (created_at BETWEEN)
CREATE INDEX idx_good ON orders(user_id, created_at);

-- KÉM HƠN: range trước thì user_id không được dùng hiệu quả
CREATE INDEX idx_bad ON orders(created_at, user_id);

Khi dùng idx_good với WHERE user_id = 12345 AND created_at BETWEEN '2026-01-01' AND '2026-02-01':

  1. Nhảy thẳng đến user_id = 12345 — narrow ngay
  2. Trong đó scan range created_at

Khi dùng idx_bad:

  1. Scan range created_at — lấy ra một đống rows
  2. Filter user_id = 12345 — mới loại bỏ

Nguyên tắc 2: Cột có selectivity cao hơn thường đứng trước

Selectivity = số lượng giá trị unique / tổng số rows. Cột user_id có selectivity cao (mỗi user khác nhau), cột status thấp (chỉ có 4-5 giá trị).

-- user_id selectivity cao hơn status
-- Đặt user_id trước để narrow nhanh hơn
CREATE INDEX idx_orders ON orders(user_id, status);

Tuy nhiên nguyên tắc này phải kết hợp với query pattern thực tế. Nếu query luôn có status = 'pending' nhưng đôi khi không có user_id, thì status cần đứng trước để leftmost prefix được dùng.

Nguyên tắc 3: Cân nhắc ORDER BY và GROUP BY

Composite index có thể loại bỏ step sort nếu cột ORDER BY khớp với cột cuối của index.

CREATE INDEX idx_user_created ON orders(user_id, created_at DESC);

-- Query này không cần sort thêm
SELECT * FROM orders
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 20;

Index đã sort created_at DESC trong phạm vi từng user_id — DB chỉ cần đọc theo thứ tự sẵn có.

Covering Index: Không cần đọc heap

Composite index nâng cấp tiếp theo: Covering Index — index chứa đủ tất cả cột mà query cần, DB không cần quay lại đọc heap (table data).

-- Query chỉ cần id, status, created_at
SELECT id, status, created_at
FROM orders
WHERE user_id = 12345
ORDER BY created_at DESC;

-- Covering index: bao gồm cả cột SELECT
CREATE INDEX idx_covering ON orders(user_id, created_at DESC) INCLUDE (id, status);
-- PostgreSQL 11+ dùng INCLUDE cho cột không cần sort
-- Hoặc đưa thẳng vào key nếu cũng filter/sort theo nó

Kết quả trong EXPLAIN: Index Only Scan thay vì Index Scan. Không có bước fetch heap — nhanh hơn đáng kể khi table lớn và data không fit vào buffer.

Trong MySQL:

-- MySQL tự động covering nếu tất cả cột nằm trong index
CREATE INDEX idx_covering ON orders(user_id, created_at, id, status);

Partial Index: Thu nhỏ index chỉ chứa rows cần thiết

Nếu 80% query chỉ quan tâm đến status = 'pending', tại sao index phải chứa cả orders đã completed?

-- PostgreSQL
CREATE INDEX idx_pending_orders
ON orders(user_id, created_at)
WHERE status = 'pending';

Index này nhỏ hơn nhiều, fit vào memory dễ hơn, update nhanh hơn. Nhưng chỉ được dùng khi query có WHERE status = 'pending' — DB biết tìm ở đây.

-- Dùng được partial index
SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';

-- KHÔNG dùng được (status không phải 'pending')
SELECT * FROM orders WHERE user_id = 1 AND status = 'shipped';

Thực chiến: Phân tích một schema thật

CREATE TABLE events (
  id          BIGSERIAL PRIMARY KEY,
  project_id  BIGINT NOT NULL,
  user_id     BIGINT NOT NULL,
  event_type  VARCHAR(50) NOT NULL,  -- 20 loại khác nhau
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  metadata    JSONB
);

Các query phổ biến:

-- Q1: Dashboard — events của project trong 7 ngày
SELECT * FROM events
WHERE project_id = 42
  AND created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 100;

-- Q2: User activity — events của user theo loại
SELECT event_type, COUNT(*) FROM events
WHERE user_id = 999
  AND event_type IN ('click', 'view', 'purchase')
GROUP BY event_type;

-- Q3: Admin — tìm event type cụ thể toàn hệ thống
SELECT * FROM events
WHERE event_type = 'error'
  AND created_at > NOW() - INTERVAL '1 day';

Phân tích và tạo index:

-- Q1: project_id equality + created_at range + ORDER BY
-- Covering với INCLUDE để tránh heap fetch nếu chỉ cần các cột này
CREATE INDEX idx_events_project_time
ON events(project_id, created_at DESC);

-- Q2: user_id equality + event_type IN (equality-like)
CREATE INDEX idx_events_user_type
ON events(user_id, event_type);

-- Q3: event_type + created_at range — partial index nếu chỉ query 'error'
-- Hoặc index thường nếu query nhiều loại
CREATE INDEX idx_events_type_time
ON events(event_type, created_at DESC);
-- Partial nếu chỉ cần 'error':
-- CREATE INDEX idx_events_error ON events(created_at DESC) WHERE event_type = 'error';

Chạy EXPLAIN (ANALYZE, BUFFERS) cho từng query để verify:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM events
WHERE project_id = 42
  AND created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 100;

Tìm trong output:

  • Index Scan using idx_events_project_time — tốt
  • Index Cond: (project_id = 42) AND (created_at > ...) — cả hai cột được dùng
  • Rows Removed by Filter: 0 — không có filter thêm ngoài index
  • Không có Sort step — index đã sort sẵn

Những sai lầm phổ biến

1. Index quá nhiều, quá ít cột

-- Sai: index riêng lẻ cho query luôn dùng cả hai
CREATE INDEX idx_a ON orders(user_id);
CREATE INDEX idx_b ON orders(status);

-- Đúng:
CREATE INDEX idx_ab ON orders(user_id, status);

2. Quên Leftmost Prefix, tạo index không dùng được

-- Index (a, b, c) nhưng query WHERE b = ? AND c = ?
-- Index này vô dụng cho query đó
-- Cần index riêng (b, c) hoặc (b, c, a)

3. Để range column không phải cuối cùng

-- Kém: range ở giữa, status sau không được dùng
CREATE INDEX idx_bad ON orders(user_id, created_at, status);
-- Query: WHERE user_id = ? AND created_at > ? AND status = ?
-- Chỉ dùng được (user_id, created_at), status vẫn phải filter

-- Tốt: equality trước, range cuối
CREATE INDEX idx_good ON orders(user_id, status, created_at);
-- Query: WHERE user_id = ? AND status = ? AND created_at > ?
-- Dùng được cả ba

4. Index tất cả mọi thứ "cho chắc"

Mỗi index là chi phí: INSERT/UPDATE/DELETE chậm hơn, disk tốn hơn, vacuum mất thời gian hơn. Index phải được justify bằng query thực tế, không phải phòng thủ.

-- Xem index nào không được dùng (PostgreSQL)
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;

Tóm tắt quyết định

Khi cần tạo index cho một query, tôi theo flow này:

  1. Chạy EXPLAIN (ANALYZE, BUFFERS) — xem đang bị gì: Seq Scan, Filter sau index, hay đang dùng index sai
  2. Liệt kê các cột trong WHERE — cột nào equality, cột nào range
  3. Đặt equality trước, range sau — selectivity cao hơn trong equality thường đứng trước
  4. Xét ORDER BY — nếu khớp thứ tự, thêm vào cuối index
  5. Xét Covering — nếu query chỉ cần vài cột, dùng INCLUDE để tránh heap fetch
  6. Xét Partial — nếu 80%+ query filter một giá trị cụ thể, partial index nhỏ hơn và nhanh hơn
  7. Verify lại với EXPLAIN — confirm index mới được dùng đúng cách
  8. Monitor pg_stat_user_indexes định kỳ — drop index không được dùng

Composite Index không phức tạp về mặt khái niệm, nhưng cần hiểu đúng để không tạo ra đống index vô dụng hoặc tệ hơn — index làm chậm write mà không giúp được read.