Cả bài trong một trang: ý chính, sơ đồ, điểm phỏng vấn.
Lab index PostgreSQL trên dataset hãng bay: B-tree, hash, LIKE, composite và covering
1Đo bằng page, không bằng mili giây
EXPLAIN (ANALYZE, BUFFERS) in plan thật kèm số page (khối 8 KB) đã đọc. Tin Buffers hơn thời gian, vì thời gian đổi theo cache.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM tickets0
WHERE ticket_no = '0005434508964';
-- Không index: Parallel Seq Scan
-- ~29.600 page, ~98 ms
-- B-tree: Index Scan, 4 page, ~0,5 ms
-- Bảng gấp 10: B-tree cao thêm ~1 tầng
2Hash index và LIKE
Hash chỉ phục vụ = và chỉ nhỉnh hơn B-tree một page, nên mặc định chọn B-tree. Với collation ngôn ngữ như en_US.utf8, B-tree thường không phục vụ LIKE 'abc%'.
LIKE 'abc%': index COLLATE "C" hoặc text_pattern_ops
LIKE '%abc': không B-tree nào cứu, dùng pg_trgm + GIN
Hash với >=: không dùng được, rơi về Seq Scan
MySQL InnoDB dùng index cho LIKE 'abc%' bình thường
3Khi nào planner bỏ index
Index chỉ có lợi khi lấy phần nhỏ của bảng. Dữ liệu rải rác (correlation gần 0) thì Index Scan đọc lại cùng page hàng trăm lần.
Khoảng book_date
Plan
Thời gian
1 ngày (0,26%)
Bitmap Heap Scan
17 ms
1 năm (94%)
Seq Scan
122 ms
1 năm, ép index
Index Scan, 1,97 triệu page
922 ms
4Thứ tự cột composite index
B-tree chỉ dùng range trên một cột để khoanh vùng; cột sau range chỉ còn lọc. Cột bằng trước, cột range/sort sau cùng, không theo cardinality.
flowchart TD
Q["total_amount >= 400000 AND book_date trong 1 ngày"]
Q --> A["(book_date, total_amount)"]
Q --> B["(total_amount, book_date)"]
A --> A1["Khoanh 0,26% index: 24 page, 0,8 ms"]
B --> B1["Khoanh 16% index: 1.309 page, 17 ms"]
5ORDER BY và covering index
Index (a, b) chỉ trả theo thứ tự b khi a là điều kiện bằng; range trên a vẫn phải Sort. INCLUDE thêm cột vào lá để có Index Only Scan.
CREATE UNIQUE INDEX seats0_pk
ON seats0 (aircraft_code, seat_no)
INCLUDE (fare_conditions);
-- Index Only Scan, Heap Fetches: 0, 4 page
-- UNIQUE vẫn chỉ trên (aircraft_code, seat_no)
💡 Ghi nhớ
Mỗi index gắn với một truy vấn có thật.
PostgreSQL không tự tạo index cho foreign key.
Mỗi index thêm là thêm một lần ghi mỗi INSERT.
Báo cáo quét phần lớn bảng: bảng tổng hợp hoặc BRIN.
⭐ Điểm phỏng vấn
Tự trả lời trước, rồi bấm từng câu để xem đáp án.
Khi nào Seq Scan tốt hơn index?
Lấy phần lớn bảng, dữ liệu rải rác.
Thứ tự cột composite?
Cột bằng trước, range hoặc sort sau cùng.
Vì sao LIKE 'abc%' không dùng index?
Collation khác C, cần text_pattern_ops.
Xong phần tự kiểm tra, bài này đã được đánh dấu đã học ✓
⚠️ Lỗi thường gặp
"High cardinality trước": đo trên dữ liệu thật thì sai.
Tin Index Cond đủ cột mà không nhìn Buffers.
Đọc plan của bảng khác: luôn kiểm tên bảng trong plan.
Lab này đo xem “tuỳ trường hợp” trong sách thực ra là bao nhiêu mili giây, bao nhiêu page. Bảy bước trên dataset hãng bay, mỗi bước cùng nhịp: hỏi, tự đoán, chạy EXPLAIN (ANALYZE, BUFFERS), giải thích. Đọc xong, bạn chọn được loại index, thứ tự cột và biết khi nào index vô ích, có số liệu làm chứng.
Nên đọc trước: Index, EXPLAIN, N+1 và phân trang (nền tảng B+tree, composite, covering) và bài 1 để đọc plan (cost, rows, loops, buffers, các kiểu scan). Lý thuyết bên dưới (heap và TID, HOT update, bloat, GIN/GiST) ở cụm Index chuyên sâu.
Sơ đồ tóm tắt:
flowchart TD
Q{Truy vấn kiểu gì?} -->|"= một dòng"| BT["B-tree: ~4 page"]
Q -->|"LIKE 'abc%'"| CC["COLLATE C / text_pattern_ops"]
Q -->|"LIKE '%abc'"| TR[pg_trgm]
Q -->|"lấy ~94% bảng"| SS[Seq Scan]
CP[Index composite] --> ORD["Cột bằng trước, range sau"]
CP --> INC["INCLUDE: Index Only Scan"]
W[Mỗi index thêm] --> WR[Thêm một lần ghi]
0. Chuẩn bị
Ý chính: chạy PostgreSQL 16 trong Docker, tự sinh dữ liệu giống bộ demo hãng bay (2,1 triệu booking, 3,1 triệu vé), rồi mỗi bước so một bản sao không index với bản có index.
Bản sao không index: CREATE TABLE tickets0 AS TABLE tickets không chép index hay constraint.
Thời gian là lần chạy đầu trên laptop: tin số page (Buffers, số khối 8 KB đã đọc) hơn mili giây.
Hai cách lấy dữ liệu và script sinh dữ liệu
Cách một: bộ Airlines demo database của Postgres Pro (bản “big”, một năm dữ liệu, khoảng 2,1 triệu booking). Tải file .sql từ trang demodb của họ, docker cp vào container rồi psql -U lab -f demo-big-en.sql.
Cách hai, cũng là cách cho ra các số liệu trong bài: tự sinh dữ liệu cùng hình dạng (vài chục giây).
CREATE TABLE bookings ( book_ref char(6) PRIMARY KEY, book_date timestamptz NOT NULL, total_amount numeric(10,2) NOT NULL);INSERT INTO bookingsSELECT upper(lpad(to_hex(g::bigint * 7919 % 16777216), 6, '0')), -- mã 6 ký tự hex, không trùng timestamptz '2016-07-20 00:00+00' + random() * interval '390 days', 3000 + floor(power(random(), 2) * 5667) * 100 -- 5.667 mức giá, lệch về giá thấpFROM generate_series(1, 2100000) g;CREATE TABLE tickets ( ticket_no char(13) PRIMARY KEY, book_ref char(6) NOT NULL REFERENCES bookings, passenger_id varchar(20) NOT NULL, -- dạng '6074 127932' passenger_name text NOT NULL);INSERT INTO ticketsSELECT lpad((5432000000 + b.rn * 3 + s)::text, 13, '0'), b.book_ref, lpad(floor(random() * 10000)::text, 4, '0') || ' ' || lpad(floor(random() * 1000000)::text, 6, '0'), (array['IVAN','OLGA','ANNA','PETR'])[1 + floor(random() * 4)] || ' ' || (array['IVANOV','POPOV'])[1 + floor(random() * 2)]FROM (SELECT book_ref, row_number() OVER () AS rn FROM bookings) bCROSS JOIN LATERAL generate_series(1, 1 + (b.rn % 3 = 0)::int + (b.rn % 7 = 0)::int) s; -- 1-3 vé mỗi bookingCREATE TABLE seats ( aircraft_code char(3) NOT NULL, seat_no varchar(4) NOT NULL, fare_conditions varchar(10) NOT NULL, PRIMARY KEY (aircraft_code, seat_no));INSERT INTO seatsSELECT a, r || l, CASE WHEN r <= 3 THEN 'Business' WHEN r <= 8 THEN 'Comfort' ELSE 'Economy' ENDFROM unnest(array['319','320','321','733','763','773','CN1','CR2','SU9']) a, generate_series(1, 40) r, unnest(array['A','B','C','D','E','F']) l;VACUUM ANALYZE; -- 2.100.000 bookings, 3.100.000 tickets, 2.160 seats
Bước 1: Không có index và có B-tree
Ý chính: có B-tree, tra một vé chỉ đọc 4 page và mất ~0,5 ms thay vì ~98 ms. Quan trọng hơn: số page gần như không đổi khi bảng lớn lên.
Câu hỏi: tìm một vé theo ticket_no trên bảng 3,1 triệu dòng, có và không có index.
CREATE TABLE tickets0 AS TABLE tickets;VACUUM ANALYZE tickets0;EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tickets0 WHERE ticket_no = '0005434508964';CREATE INDEX tickets0_ticket_no_idx ON tickets0 (ticket_no); -- 1,1 giâyEXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tickets0 WHERE ticket_no = '0005434508964';
Plan
Buffers
Thời gian
Không index
Parallel Seq Scan, 2 worker, bỏ 1.033.333 dòng mỗi tiến trình
~29.600 page
~98 ms
B-tree
Index Scan, cost=0.43..8.45
4 page
~0,5 ms
Nhanh hơn ~200 lần. Bảng gấp 10 lần thì B-tree chỉ cao thêm khoảng một tầng.
Bước 2: B-tree và hash index
Ý chính: với =, hash chỉ nhỉnh hơn B-tree một page; với range (>=), hash không dùng được và rơi về Seq Scan. Gần như luôn chọn B-tree.
Câu hỏi: với điều kiện bằng, hash index có nhanh hơn B-tree không? Với điều kiện range thì sao?
CREATE TABLE tickets1 AS TABLE tickets;CREATE INDEX tickets1_ticket_no_hash ON tickets1 USING hash (ticket_no);VACUUM ANALYZE tickets1;EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tickets1 WHERE ticket_no = '0005434508964';EXPLAIN ANALYZE SELECT * FROM tickets1 WHERE ticket_no >= '0005438299000';EXPLAIN ANALYZE SELECT * FROM tickets0 WHERE ticket_no >= '0005438299000';
Truy vấn
B-tree (tickets0)
Hash (tickets1)
=
Index Scan, cost 8.45, 4 page
Index Scan, cost 8.02, 3 page
>= (494 dòng)
Index Scan, 0,4 ms
không dùng được, Parallel Seq Scan 164 ms
Kích thước index
93 MB
97 MB
Hash hay B-tree: kích thước, giới hạn và khi nào đáng dùng
Bước 3: LIKE, collation và pg_trgm
Ý chính: collation (quy tắc so sánh, sắp xếp chuỗi) ngôn ngữ như en_US.utf8 làm B-tree thường không phục vụ LIKE 'abc%'. Cần index theo thứ tự byte; còn LIKE '%abc' thì cần trigram index.
Câu hỏi: index thường trên passenger_id có phục vụ LIKE '6074 12%' không? Còn LIKE '%53 429919'?
CREATE INDEX tickets0_pid_idx ON tickets0 (passenger_id);EXPLAIN SELECT * FROM tickets0 WHERE passenger_id = '3453 429919'; -- Index ScanEXPLAIN SELECT * FROM tickets0 WHERE passenger_id LIKE '6074 12%'; -- Parallel Seq Scan!
en_US.utf8 (mặc định của image Docker) không sắp theo thứ tự byte, nên PostgreSQL không đổi được prefix LIKE thành range trên index thường. Cần index theo thứ tự byte:
CREATE INDEX tickets0_pid_c_idx ON tickets0 (passenger_id COLLATE "C");-- hoặc: CREATE INDEX ... ON tickets0 (passenger_id text_pattern_ops);EXPLAIN ANALYZE SELECT * FROM tickets0 WHERE passenger_id LIKE '6074 12%';
Index Scan using tickets0_pid_c_idx on tickets0 (actual time=0.273..0.465 rows=5 loops=1) Index Cond: (((passenger_id)::text >= '6074 12'::text) AND ((passenger_id)::text < '6074 13'::text)) Filter: ((passenger_id)::text ~~ '6074 12%'::text)
Planner tự đổi prefix thành range. Wildcard đứng đầu ('%53 429919') không cho cây điểm bắt đầu, nên vẫn Parallel Seq Scan 105 ms. Tìm hậu tố hay chuỗi con thì dùng trigram index (index trên từng bộ ba ký tự liên tiếp):
CREATE EXTENSION IF NOT EXISTS pg_trgm;CREATE INDEX tickets0_pid_trgm ON tickets0 USING gin (passenger_id gin_trgm_ops); -- 4,2 giây, 74 MBEXPLAIN ANALYZE SELECT * FROM tickets0 WHERE passenger_id LIKE '%53 429919';-- Bitmap Index Scan on tickets0_pid_trgm → Bitmap Heap Scan, 5,8 ms
pg_trgm không chấm điểm độ liên quan, không có analyzer tiếng Việt có dấu, không gợi ý khi gõ. Cần những thứ đó, hoặc tải tìm kiếm bắt đầu ảnh hưởng database giao dịch, thì xem Elasticsearch: analyzer, inverted index và mapping (mở đầu bằng trade-off “PostgreSQL hay thêm Elasticsearch”).
Bước 4: Khi nào planner bỏ index
Ý chính: index chỉ có lợi khi lấy một phần nhỏ của bảng. Lấy 94% bảng thì Seq Scan đọc mỗi page một lần, còn ép index thì đọc lại cùng page hàng trăm lần và chậm hơn 7,5 lần.
Câu hỏi: có index trên book_date. Truy vấn một ngày, một tháng, một năm dùng plan gì? Tự đoán trước.
CREATE TABLE bookings0 AS TABLE bookings;CREATE INDEX bookings0_book_date_idx ON bookings0 (book_date);VACUUM ANALYZE bookings0;EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM bookings0WHERE book_date >= '2017-03-01' AND book_date < '2017-03-02'; -- đổi khoảng: 1 ngày / 1 tháng / 1 năm
Khoảng
Số dòng (tỉ lệ bảng)
Plan
Buffers
Thời gian
1 ngày
5.404 (0,26%)
Bitmap Heap Scan, 4.458 heap block
~4.500
17 ms
1 tháng
167.029 (8%)
Bitmap Heap Scan, 13.376 heap block
~13.800
51 ms
1 năm
1.965.143 (94%)
Seq Scan
13.376
122 ms
1 năm, ép index (enable_seqscan, enable_bitmapscan = off)
1.965.143
Index Scan
1.970.356
922 ms
correlation đo thứ tự dòng trên đĩa khớp thứ tự cột tới đâu (1 = khớp, 0 = lộn xộn). book_date gần 0, nên 5.404 dòng của một ngày rải trên 4.458 page. Với một năm, Index Scan chạm page heap gần 2 triệu lần (mỗi page đọc lại hàng trăm lần), Seq Scan đọc 13.376 page đúng một lần. Planner đúng.
BRIN: index siêu nhỏ cho dữ liệu ghi theo thời gian
Bước 5: Thứ tự cột trong composite index
Ý chính: B-tree chỉ dùng điều kiện range trên cột đầu tiên để khoanh vùng cần đọc. Vì thế cột so sánh bằng đặt trước, cột range đặt sau cùng; loại điều kiện quyết định, không phải cardinality (số giá trị khác nhau của cột).
Câu hỏi: truy vấn total_amount >= 400000 AND book_date trong một ngày. Index (book_date, total_amount) hay (total_amount, book_date) tốt hơn? “Quy tắc” phổ biến: cột high cardinality trước.
SELECT count(DISTINCT book_date), count(DISTINCT total_amount) FROM bookings; -- 2.100.000 và 5.667SELECT count(*) FROM bookings WHERE total_amount >= 400000; -- 341.131 (16%)CREATE INDEX bookings0_date_amount ON bookings0 (book_date, total_amount);CREATE TABLE bookings1 AS TABLE bookings;CREATE INDEX bookings1_amount_date ON bookings1 (total_amount, book_date);-- cùng một câu trên hai bảng:-- WHERE total_amount >= 400000 AND book_date >= '2017-03-01' AND book_date < '2017-03-02'
Index
Plan
Page index đọc
Thời gian
(book_date, total_amount)
Bitmap Index Scan
24
0,8 ms
(total_amount, book_date)
Bitmap Index Scan
1.309
17 ms (8,7 ms khi cache nóng)
Cả hai plan đều in cả hai điều kiện trong Index Cond; khác nhau ở số page:
(total_amount, book_date): vùng đọc là mọi entry có total_amount >= 400000 (16% index), rồi lọc book_date trong vùng đó.
(book_date, total_amount): vùng đọc chỉ là một ngày (0,26%).
Giờ đổi thành bằng trên total_amount: total_amount = 450000 và book_date trong một tháng.
Index
Page đọc
Thời gian
(total_amount, book_date)
4 page index + 10 page heap
0,05 ms
(book_date, total_amount)
653 page
5,1 ms
Thứ hạng đảo ngược, dù cardinality từng cột không đổi.
Điểm tốt: điều kiện cột thứ hai vẫn được kiểm ngay trong index, trước khi đọc heap (MySQL gọi là index condition pushdown, Using index condition; PostgreSQL làm tương tự). Nhờ vậy một composite index phục vụ được nhiều câu.
Bước 6: ORDER BY cùng với range
Ý chính: index (a, b) chỉ trả kết quả theo thứ tự b khi a là điều kiện bằng. Range trên a thì vẫn phải Sort, dù index có đủ cột.
Câu hỏi: thêm ORDER BY book_ref vào câu ở bước 5. Index nào giúp bỏ bước Sort?
-- Với (book_date, total_amount): Sort (quicksort 66kB) trên 881 dòng phía trên Bitmap Heap Scan, 11,4 msDROP INDEX bookings0_date_amount;CREATE INDEX bookings0_date_ref_amount ON bookings0 (book_date, book_ref, total_amount);VACUUM ANALYZE bookings0;EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM bookings0WHERE book_date >= '2017-03-01' AND book_date < '2017-03-02' AND total_amount >= 400000ORDER BY book_ref;
Sort (actual time=0.561..0.578 rows=881 loops=1) Sort Key: book_ref Sort Method: quicksort Memory: 66kB -> Index Only Scan using bookings0_date_ref_amount on bookings0 (actual time=0.014..0.263 rows=881 loops=1) Heap Fetches: 0 Buffers: shared hit=1 read=30
Nhanh hơn 20 lần nhờ Index Only Scan (index chứa đủ ba cột), nhưng Sort vẫn còn: index xếp theo book_reftrong từng giá trị book_date, mà một ngày có hàng nghìn timestamp. Kiểm chứng: ORDER BY book_date LIMIT 20 trên cùng index không có Sort, dừng sau 5 page.
Hai index riêng cộng LIMIT 20: plan khác và giới hạn của nó
Với hai index riêng (book_date) và (book_ref) cộng LIMIT 20, planner đi index book_ref theo thứ tự rồi lọc book_date: bỏ 6.777 dòng, ~6.800 page, 3,8 ms. Plan này chỉ tốt khi dòng khớp rải đều; với một ngày hiếm dữ liệu, nó có thể đi gần hết index.
Bước 7: Covering index với INCLUDE
Ý chính: covering index chứa đủ mọi cột câu query cần, nên PostgreSQL bỏ được bước đọc bảng (Index Only Scan). INCLUDE thêm cột vào lá của index mà không đổi khoá.
Câu hỏi:SELECT seat_no, fare_conditions FROM seats0 WHERE aircraft_code = '733' với unique index (aircraft_code, seat_no). Làm sao bỏ bước đọc bảng?
CREATE TABLE seats0 AS TABLE seats;CREATE UNIQUE INDEX seats0_pk ON seats0 (aircraft_code, seat_no);VACUUM ANALYZE seats0;EXPLAIN (ANALYZE, BUFFERS) SELECT seat_no, fare_conditions FROM seats0 WHERE aircraft_code = '733';-- Bitmap Heap Scan: 3 page index + 12 page heap (cần fare_conditions)DROP INDEX seats0_pk;CREATE UNIQUE INDEX seats0_pk ON seats0 (aircraft_code, seat_no) INCLUDE (fare_conditions);VACUUM ANALYZE seats0;EXPLAIN (ANALYZE, BUFFERS) SELECT seat_no, fare_conditions FROM seats0 WHERE aircraft_code = '733';-- Index Only Scan using seats0_pk, Heap Fetches: 0, 4 page
Lưu ý: script mẫu của khoá có câu EXPLAIN cuối truy vấn seats thay vì seats0, nên plan không phản ánh index vừa tạo. Luôn kiểm tên bảng trong plan (on seats0).
INCLUDE hay thêm cột vào khoá: so sánh chi tiết
Case study: Thiết kế index cho schema đặt vé máy bay
Ý chính: bắt đầu từ các truy vấn chính, mỗi index gắn với một truy vấn có thật. Báo cáo quét phần lớn dữ liệu thì không thêm B-tree.
Đề (bài về nhà của khoá, diễn đạt lại): thiết kế index cho schema đặt vé theo kiểu bộ demo. Ràng buộc: đọc nhiều hơn ghi khoảng 20 lần; đặt vé phải nhanh lúc mở bán; báo cáo doanh thu chạy ban đêm; không thêm index “phòng khi cần”.
#
Truy vấn
Index
Q1
Tìm chuyến: departure_airport = ? AND arrival_airport = ? AND scheduled_departure trong một ngày, sắp theo giờ
flights (departure_airport, arrival_airport, scheduled_departure): hai cột bằng trước, range và sort sau cùng
Q2
Danh sách vé của một booking
tickets (book_ref): PostgreSQL không tự tạo index cho foreign key
Q3
Hành khách trên một chuyến (check-in, xếp ghế)
ticket_flights (flight_id): PK bắt đầu bằng ticket_no không giúp được
Q4
Ghế đã được gán trên chuyến, chống gán trùng
UNIQUE boarding_passes (flight_id, seat_no): vừa là ràng buộc nghiệp vụ, vừa là index
Q5
Tra cứu theo giấy tờ hành khách
tickets (passenger_id); nếu cần tìm theo một phần chuỗi thì thêm pg_trgm có cân nhắc
Q6
Báo cáo doanh thu theo ngày đặt
Không thêm B-tree cho báo cáo đêm; dùng bảng tổng hợp theo ngày, hoặc BRIN nếu dữ liệu ghi theo thời gian
Đã chọn. Năm index cho Q1–Q5; Q6 đi đường gián tiếp vì quét phần lớn dữ liệu (bước 4). Pull request thêm index phải kèm câu truy vấn và plan trước/sau.
Đây là dữ liệu demo để đo. Bài Thiết kế database đặt vé máy bay thiết kế schema từ đầu (sơ đồ ghế, ghế trống theo từng ghế hay bộ đếm, overbooking) và chọn index, có cả partial index trên ghế trống.
Schema chi tiết, index cho foreign key và giá của mỗi index
Đề: tạo bảng bookings tương tự trên MySQL 8 và so sánh hai thứ tự index. Plan khác PostgreSQL ở đâu?
Lời giải Bài 1: số đo trên MySQL 8.4
Mình chạy trên MySQL 8.4 với 1 triệu dòng. EXPLAIN ANALYZE cho thấy MySQL không có bitmap scan; cả hai index đều là Covering index range scan.
(book_date, total_amount): range scan đọc 2.532 entry để ra 402 dòng (0,36 ms).
(total_amount, book_date): đọc 162.557 entry cho cùng 402 dòng (21 ms).
Thứ hạng giống PostgreSQL vì cùng là B+tree khoanh vùng theo cột đầu. Khác biệt đáng chú ý: plan ghi “Covering index” dù câu là SELECT *, vì bảng chỉ có ba cột và secondary index của InnoDB luôn chứa PK (book_ref). Khi index không cover, cột Extra của EXPLAIN hiện Using index condition cho điều kiện cột thứ hai (ICP).
Bài 2: Index cho màn hình “chuyến bay khởi hành hôm nay”
Đề:SELECT flight_no, scheduled_departure, status FROM flights WHERE departure_airport = 'SGN' AND scheduled_departure >= current_date AND scheduled_departure < current_date + 1 ORDER BY scheduled_departure. Thiết kế index và cho biết plan mong đợi.
Lời giải Bài 2
CREATE INDEX ON flights (departure_airport, scheduled_departure) INCLUDE (flight_no, status).
Cột bằng departure_airport trước, range/sort scheduled_departure sau. Cột đầu là bằng nên thứ tự scheduled_departure được giữ, không cần Sort.
INCLUDE cho Index Only Scan nếu bảng được vacuum đều.
Nếu status đổi liên tục trong ngày (DELAYED, BOARDING…), cân nhắc bỏ nó khỏi INCLUDE để giữ HOT update, chấp nhận đọc heap cho vài chục dòng.
Ghi nhớ
Giải thích được vì sao tin Buffers hơn mili giây, và vì sao số page B-tree gần như không tăng theo bảng.
Biết khi nào hash index đáng dùng, và vì sao LIKE 'abc%' có thể không dùng index thường trong PostgreSQL.
Giải thích được vì sao planner chọn Seq Scan khi lấy phần lớn bảng, và correlation ảnh hưởng thế nào.
Xếp được thứ tự cột composite theo loại điều kiện (bằng, range, sort), và chỉ ra vì sao “high cardinality trước” sai.
Biết khi nào composite index bỏ được Sort, và chọn giữa INCLUDE và thêm cột vào khoá.
Câu hỏi phỏng vấn
Junior
Vì sao có index mà truy vấn vẫn Seq Scan?
Thường vì truy vấn lấy phần lớn bảng, nên đọc tuần tự rẻ hơn.
Lý do khác: điều kiện không dùng được index (bọc cột trong hàm, ép kiểu, LIKE '%x', collation không khớp); statistics cũ làm planner ước lượng sai; hoặc bảng quá nhỏ.
Hash index khác B-tree index thế nào?
Hash chỉ phục vụ so sánh bằng, và chỉ nhỉnh hơn B-tree một chút ở trường hợp đó.
B-tree phục vụ bằng, range, sắp xếp, prefix LIKE, unique và nhiều cột. Mặc định chọn B-tree.
Mid
Composite index nên xếp thứ tự cột thế nào?
Cột so sánh bằng trước, cột range hoặc sắp xếp sau cùng.
Sau một range, các cột còn lại chỉ lọc trong vùng đã đọc chứ không khoanh vùng. Nhiều range thì đặt range hẹp nhất trên dữ liệu thật trước. “High cardinality trước” không phải quy tắc đúng; đo bằng Buffers.
Làm sao để index phục vụ LIKE ‘abc%’ và LIKE ‘%abc’ trong PostgreSQL?
Prefix: B-tree với COLLATE "C" hoặc text_pattern_ops. Wildcard đầu: trigram index pg_trgm.
Với prefix, planner đổi LIKE thành range khi index theo thứ tự byte (cần khi database dùng collation ngôn ngữ). Wildcard đầu dùng pg_trgm với GIN hoặc GiST; nếu chỉ tìm hậu tố thì expression index trên reverse(col).
Covering index là gì, INCLUDE khác gì thêm cột vào khoá?
Covering index chứa đủ cột câu query cần, nên có Index Only Scan.
INCLUDE để cột ở lá, không tham gia sắp xếp, không đổi nghĩa unique. Thêm vào khoá thì lọc và sắp xếp được theo cột đó, nhưng đổi unique và làm cây to hơn.
Senior
Index (a, b) có giúp WHERE a BETWEEN x AND y ORDER BY b bỏ Sort không?
Không. Trong range nhiều giá trị a, kết quả ra theo a trước rồi mới tới b.
Thứ tự của b chỉ được giữ khi a là điều kiện bằng. Lựa chọn: sort kết quả nhỏ, hoặc index theo b rồi lọc khi có LIMIT nhỏ.
Bạn quyết định thêm một index trên production thế nào?
Gắn nó với một truy vấn cụ thể có số liệu, đo plan trước/sau, rồi tạo bằng CREATE INDEX CONCURRENTLY.
Lấy tần suất và tổng thời gian từ pg_stat_statements.
Kiểm tra index hiện có mở rộng được không.
Đo plan trước/sau trên dữ liệu cỡ production; ước lượng ảnh hưởng ghi và dung lượng.
Sau khi tạo, theo dõi idx_scan và độ trễ ghi.
Từ vựng tiếng Anh
Term
Phát âm
Nghĩa
Ví dụ câu tiếng Anh dùng trong phỏng vấn
selectivity
/səˌlekˈtɪvəti/
độ chọn lọc của điều kiện
“What matters is the selectivity of the actual predicate, not the column’s cardinality.”
leading column
/ˈliːdɪŋ ˈkɒləm/
cột đầu của index
“A range on the leading column means the second column can’t narrow the scan.”
equality predicate
/iˈkwɒləti ˈpredɪkət/
điều kiện so sánh bằng
“Equality predicates go first, the range column goes last.”
covering index
/ˈkʌvərɪŋ ˈɪndeks/
index chứa đủ cột cần
“With INCLUDE it becomes a covering index and we get an index-only scan.”
collation
/kəˈleɪʃn/
quy tắc so sánh và sắp xếp chuỗi
“The database uses a linguistic collation, so a plain B-tree can’t serve LIKE prefixes.”
trigram
/ˈtraɪɡræm/
bộ ba ký tự
“For a leading wildcard I’d use a trigram GIN index.”
write amplification
/raɪt ˌæmplɪfɪˈkeɪʃn/
một lần ghi kéo theo nhiều lần ghi
“Every extra index adds write amplification to the booking transaction.”
foreign key index
/ˈfɒrən kiː ˈɪndeks/
index trên cột khoá ngoại
“Postgres doesn’t create foreign key indexes for you.”
Ghi chép dựa trên khoá Advanced Backend (Ronin Engineer), viết lại theo cách hiểu của mình.