Composite index: cùng ba cột, khác khoảng phải đọc

Câu hỏi bài này trả lời: query đã có index đủ các cột lọc; vì sao đổi thứ tự cột vẫn thay đổi lượng công việc, và lựa chọn nào phù hợp với query đang cần?

Cần biết trước: SQL cơ bản, đọc EXPLAIN ANALYZE và lab database. Bài chạy PostgreSQL 18.6 trên macOS arm64 với dữ liệu giả. MySQL chỉ có phần so sánh nguyên lý; Docker/Linux của fixture chưa kiểm.

Đặt query và mục tiêu trước khi tạo index

Yêu cầu: lấy 20 đơn paid mới nhất của một khách, từ ngày đã chọn. Giữ nguyên query, dữ liệu và số hàng cần trả khi so hai cấu hình.

Tạo thư mục sạch, chép bốn file của cách A từ bài lab (lab-common.sh, lab-local.sh, seed-pg.sql, seed-mysql.sql). Lab chỉ dùng socket riêng; gọi lab_whoami trước khi tạo hoặc gỡ index.

. ./lab-local.sh
lab_up
lab_seed
lab_whoami

SELECT id, total_cents, created_at
FROM wiki_lab.orders
WHERE customer_id = 1
  AND status = 'paid'
  AND created_at >= timestamp '2025-06-01'
ORDER BY created_at DESC
LIMIT 20;

Trong fixture, created_at không trùng giữa các đơn: 37 và 525.600 nguyên tố cùng nhau, nên công thức thời gian không lặp trong 100.000 số thứ tự. Vì vậy ORDER BY created_at DESC cho thứ tự xác định ở lab. Với dữ liệu thật có thời gian trùng, cần cột phá hòa như id trong query và thiết kế lại index theo thứ tự đó.

Hai index ứng viên:

Ở A, hai điều kiện equality khóa hai cột đầu. Các entry trong nhóm đó được xếp theo thời gian, nên vừa giới hạn khoảng scan vừa đáp ứng ORDER BY/LIMIT. Ở B, range thời gian đứng trước; hai điều kiện sau vẫn có thể được kiểm ngay trong index, nhưng không tự thu hẹp thành một khoảng liên tiếp của một khách.

Không rút ra luật “cột có nhiều giá trị nhất luôn đứng đầu”. Khi cả hai cột đầu đều bị equality, mục tiêu của query và các query khác dùng prefix thường quan trọng hơn việc đổi thứ tự hai cột đó. Bài này so equality trước range với range trước equality.

Đo lượt A, chỉ có A

. ./lab-local.sh
lab_psql -c 'CREATE INDEX orders_a ON wiki_lab.orders(customer_id, status, created_at DESC)'
{ printf 'EXPLAIN (ANALYZE, BUFFERS) '; cat query.sql; } | lab_psql -At | tee plan-a.txt
{ printf 'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) '; cat query.sql; } | lab_psql -At > plan-a.json
lab_psql -At -f query.sql > rows-a.txt
lab_psql -At -c "SELECT pg_relation_size('wiki_lab.orders_a')" > size-a.txt
echo "size_a $(cat size-a.txt)"

Output thật ở lượt kiểm ngày 2026-10-03:

Limit  (cost=0.42..64.13 rows=20 width=16) (actual time=0.018..0.038 rows=20.00 loops=1)
  Buffers: shared hit=20 read=3
  ->  Index Scan using orders_a on orders  (cost=0.42..1309.66 rows=411 width=16) (actual time=0.018..0.036 rows=20.00 loops=1)
        Index Cond: ((customer_id = 1) AND (status = 'paid'::text) AND (created_at >= '2025-06-01 00:00:00'::timestamp without time zone))
        Index Searches: 1
        Buffers: shared hit=20 read=3
Planning:
  Buffers: shared hit=134 read=5
Planning Time: 0.259 ms
Execution Time: 0.048 ms
size_a 4087808

LIMIT 20 làm node cha ngừng sau đủ hàng. Cost và rows của scan bên dưới vẫn có thể biểu diễn việc chạy hết khoảng lọc; đừng coi nó đã thực sự trả hết chừng đó hàng.

Gỡ A, đo lượt B trên cùng dữ liệu

Không để cả hai index tồn tại rồi đo một query: planner có thể tiếp tục chọn A và bạn sẽ tưởng đang kiểm B.

. ./lab-local.sh
lab_psql -c 'DROP INDEX wiki_lab.orders_a'
lab_psql -c 'CREATE INDEX orders_b ON wiki_lab.orders(created_at DESC, customer_id, status)'
test -z "$(lab_psql -At -c "SELECT to_regclass('wiki_lab.orders_a')")"
{ printf 'EXPLAIN (ANALYZE, BUFFERS) '; cat query.sql; } | lab_psql -At | tee plan-b.txt
{ printf 'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) '; cat query.sql; } | lab_psql -At > plan-b.json
lab_psql -At -f query.sql > rows-b.txt
cmp rows-a.txt rows-b.txt
test "$(wc -l < rows-b.txt | tr -d ' ')" = 20
lab_psql -At -c "SELECT pg_relation_size('wiki_lab.orders_b')" > size-b.txt
echo "size_b $(cat size-b.txt)"
echo 'cùng 20 hàng theo thứ tự'

Output cùng lượt kiểm:

Limit  (cost=0.42..155.33 rows=20 width=16) (actual time=0.027..0.077 rows=20.00 loops=1)
  Buffers: shared hit=26 read=11
  ->  Index Scan using orders_b on orders  (cost=0.42..3183.89 rows=411 width=16) (actual time=0.026..0.076 rows=20.00 loops=1)
        Index Cond: ((created_at >= '2025-06-01 00:00:00'::timestamp without time zone) AND (customer_id = 1) AND (status = 'paid'::text))
        Index Searches: 1
        Buffers: shared hit=26 read=11
Planning:
  Buffers: shared hit=136 read=1
Planning Time: 0.214 ms
Execution Time: 0.087 ms
size_b 4071424
cùng 20 hàng theo thứ tự

A truy cập 23 block, B 37 block trong các lượt đo này, cả hai trả đúng 20 hàng và không có Sort. B vẫn sử dụng index, không phải index “vô dụng”; A khớp cách truy vấn chọn nhóm khách trước nên ít công việc hơn ở phép thử này. Chênh lệch kích thước index rất nhỏ và ngược chiều chênh lệch công việc đọc: index lớn hơn chút không tự có nghĩa query chậm hơn.

Đọc cả Index Cond, Buffers và node có Sort hay không. Việc tên mọi cột xuất hiện trong Index Cond không chứng minh chúng đều đã giảm khoảng scan như nhau. Planner có thể đi qua nhiều entry hơn để tìm đủ 20 hàng dù chỉ đưa 20 hàng lên node cha.

Hai lượt dùng cùng dữ liệu nhưng cache không giống hoàn toàn: CREATE INDEX vừa đọc bảng, và bản JSON chạy sau bản text. Đây là phép thử cơ chế đọc, không phải benchmark cold cache hoặc cam kết tốc độ.

Index còn có chi phí ghi

Lưu lượng ghi là một phần của lựa chọn. Thử insert 5.000 hàng mới với ba cấu hình: chỉ khóa chính, thêm A, thêm B. Mỗi lượt reset dữ liệu để không mang thay đổi của lượt trước vào; mọi insert đều rollback.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS, WAL, FORMAT JSON)
INSERT INTO wiki_lab.orders(id, customer_id, status, total_cents, created_at)
SELECT g, 1, 'paid', 1000, timestamp '2025-06-01' + g * interval '1 minute'
FROM generate_series(100001, 105000) AS g;
ROLLBACK;

. ./lab-local.sh
for variant in none a b; do
  lab_seed
  case "$variant" in
    a) lab_psql -c 'CREATE INDEX orders_a ON wiki_lab.orders(customer_id, status, created_at DESC)' ;;
    b) lab_psql -c 'CREATE INDEX orders_b ON wiki_lab.orders(created_at DESC, customer_id, status)' ;;
  esac
  lab_psql -At -f write.sql > "write-$variant.json"
  test "$(lab_psql -At -c 'SELECT count(*) FROM wiki_lab.orders')" = 100000
done
echo 'write fixtures giữ 100000 hàng'

ROLLBACK không xóa WAL đã sinh và không làm cache trở lại trạng thái cũ; nó chỉ hoàn tác việc thêm hàng vào tập dữ liệu nhìn thấy. Vì vậy không dùng số WAL này làm dự báo trực tiếp về dung lượng production. Không gọi reset là xóa cache.

import json
from pathlib import Path

def nodes(plan):
    yield plan
    for child in plan.get('Plans', []):
        yield from nodes(child)

for name in ('a', 'b'):
    document = json.loads(Path(f'plan-{name}.json').read_text())[0]
    plan = document['Plan']
    assert plan['Actual Rows'] == 20
    assert any(node.get('Index Name') == f'orders_{name}' for node in nodes(plan))
    assert not any(node.get('Index Name') == f'orders_{"b" if name == "a" else "a"}'
                   for node in nodes(plan))
    size = int(Path(f'size-{name}.txt').read_text())
    assert size > 0
    print(f'{name}: rows=20 index_bytes={size} shared_blocks='
          f'{plan["Shared Hit Blocks"] + plan["Shared Read Blocks"]}')
assert Path('rows-a.txt').read_text() == Path('rows-b.txt').read_text()
wal_records = {}
for name in ('none', 'a', 'b'):
    document = json.loads(Path(f'write-{name}.json').read_text())[0]
    plan = document['Plan']
    assert any(node['Actual Rows'] == 5000 for node in nodes(plan))
    assert plan['WAL Records'] > 0
    assert plan['WAL Bytes'] > 0
    wal_records[name] = plan['WAL Records']
    print(f'write_{name}: rows=5000 wal_records={plan["WAL Records"]} '
          f'wal_bytes={plan["WAL Bytes"]} execution_ms={document["Execution Time"]}')
assert wal_records['a'] > wal_records['none']
assert wal_records['b'] > wal_records['none']
print('kết quả query và ba phép đo ghi đạt')

python3 check.py

Output cùng lượt kiểm; giá trị dung lượng và số WAL được đo trong lab, còn thời gian đổi ở lượt chạy khác:

a: rows=20 index_bytes=4087808 shared_blocks=23
b: rows=20 index_bytes=4071424 shared_blocks=37
write_none: rows=5000 wal_records=10013 wal_bytes=764880 execution_ms=3.416
write_a: rows=5000 wal_records=15068 wal_bytes=1332444 execution_ms=8.135
write_b: rows=5000 wal_records=15064 wal_bytes=1323236 execution_ms=6.356
kết quả query và ba phép đo ghi đạt

So cấu hình chỉ khóa chính với thêm index: số WAL records tăng từ khoảng 10.000 lên 15.000 khi insert cùng 5.000 hàng. Hai index khác thứ tự có chi phí ghi gần nhau về records trong phép thử; đây không phải lý do giữ cả hai nếu workload không cần cả hai.

Một lượt đo ghi không chứng minh thứ tự A luôn ghi nhanh hơn B hoặc mức chậm cố định khi thêm index. WAL records là bằng chứng server làm thêm công việc bảo trì cấu trúc, còn bytes chịu ảnh hưởng full-page images và checkpoint; thời gian chịu ảnh hưởng cache, nền máy và các lượt chạy trước. Nếu workload ghi quan trọng, đo nhiều lượt với dữ liệu/cấu hình checkpoint ổn định rồi mới quyết định.

Covering và index-only theo engine

Query đọc id và total_cents, hai cột chưa nằm trong A hay B. PostgreSQL phải lấy chúng từ heap. Có thể thêm INCLUDE (id, total_cents) vào A để đủ dữ liệu cho một index-only scan, nhưng “đủ cột” mới chỉ là điều kiện cần: visibility map còn phải cho biết các hàng ở heap page đã visible để tránh đọc heap. Đọc thêm Heap Fetches trong plan; tên Index Only Scan tự nó không chứng minh đã bỏ hoàn toàn heap access.

MySQL/InnoDB lưu khóa chính cùng entry của secondary index; query chỉ đọc khóa chính và cột đã có trong index có thể được cover. PostgreSQL không tự thêm giá trị khóa chính vào mọi secondary index theo cách đó. Với query của bài, total_cents vẫn cần được cung cấp; không suy kết quả PostgreSQL thành MySQL hay dùng cú pháp INCLUDE của PostgreSQL cho MySQL.

Phần MySQL dựa trên InnoDB Index Types của MySQL 8.4, không có đo plan MySQL ở bài này.

Có thể dùng index dù thiếu cột đầu không?

Có thể. Tài liệu PostgreSQL 18 mô tả skip scan: planner đôi khi lặp phép tìm theo các giá trị của cột đầu để dùng điều kiện ở cột sau. Nó có lợi khi số giá trị đầu ít và các lượt tìm có thể bỏ qua nhiều leaf page; không phải lời hứa rằng thiếu cột đầu lúc nào cũng nhanh.

Bởi vậy luật “không lọc cột đầu thì index tuyệt đối không dùng được” sai cho phiên bản này. Mặt khác, “đủ cả ba cột trong WHERE nên thứ tự không quan trọng” cũng không đúng. Xem plan của query và dữ liệu cụ thể.

Reset, kiểm lại và dọn

. ./lab-local.sh
lab_seed
test -z "$(lab_psql -At -c "SELECT to_regclass('wiki_lab.orders_b')")"
lab_psql -c 'CREATE INDEX orders_a ON wiki_lab.orders(customer_id, status, created_at DESC)'
lab_psql -At -f query.sql > rows-reset.txt
cmp rows-a.txt rows-reset.txt
echo 'reset đọc đạt'

. ./lab-local.sh
lab_clean
test ! -e lab.env

Bài tập biến thể và lỗi thường gặp

1. Bỏ điều kiện customer_id: A còn gom theo khách trước, trong khi B đi theo thời gian. Ghi plan và tập kết quả mới; đừng mang kết luận của query cũ sang query này.
2. Bỏ LIMIT hoặc lấy phần lớn bảng: planner có thể chọn scan và sort. Đó có thể là quyết định hợp lý, không tự tắt scan để giữ hình đẹp.
3. Đổi khách nóng sang khách ít đơn: so row estimate với actual; tần suất giá trị là một phần của selectivity. status='paid' chiếm 70% toàn bộ fixture nên riêng điều kiện này không chọn lọc cao.
4. Thử INCLUDE rồi VACUUM trong lab, kiểm Heap Fetches trước/sau. Đây là bài tập chưa đo trong bài; không sửa database thật để tái hiện.

Plan vẫn chọn index kia: kiểm đã DROP index ứng viên trước và dùng namespace lab. Index rất lớn: xem cột payload, kiểu dữ liệu và số entry; thêm cột không miễn phí. Query đổi kết quả sau index: kiểm ORDER BY có phá hòa đủ chưa, LIMIT có ổn định không và cả hai lượt có cùng dữ liệu không.

Học tiếp và nguồn

• Đọc EXPLAIN ANALYZE: rows là đầu ra mỗi vòng; buffer và thời gian của node cha gồm con.
• Lab database dùng chung: công thức dữ liệu, reset và dấu nhận diện.
• PostgreSQL 18, Multicolumn Indexes: khoảng scan và skip scan.
• PostgreSQL 18, Indexes and ORDER BY: thứ tự, hướng scan và LIMIT.
• PostgreSQL 18, Index-Only Scans and Covering Indexes: INCLUDE và visibility map.

Nguồn chính thức đọc ngày 2026-10-03; số đo trong bài thuộc PostgreSQL 18.6/macOS arm64, không có nghiệm thu Docker/Linux, Podman hay MySQL plan.