Cụm bài Tối ưu truy vấn: 1. SQL chạy thế nào và đọc execution plan · 2. Lab index PostgreSQL trên dataset hãng bay · 3. Thuật toán JOIN và partition
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.
docker run -d --name pg-lab -e POSTGRES_USER=lab -e POSTGRES_PASSWORD=lab -e POSTGRES_DB=demo \
postgres:16 -c shared_preload_libraries=pg_stat_statements
docker exec -it pg-lab psql -U lab -d demo
- Bản sao không index:
CREATE TABLE tickets0 AS TABLE ticketskhô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 bookings
SELECT 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ấp
FROM 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 tickets
SELECT 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) b
CROSS JOIN LATERAL generate_series(1, 1 + (b.rn % 3 = 0)::int + (b.rn % 7 = 0)::int) s; -- 1-3 vé mỗi booking
CREATE 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 seats
SELECT a, r || l, CASE WHEN r <= 3 THEN 'Business' WHEN r <= 8 THEN 'Comfort' ELSE 'Economy' END
FROM 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ây
EXPLAIN (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 Scan
EXPLAIN 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 MB
EXPLAIN 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 bookings0
WHERE 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.667
SELECT 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ọcbook_datetrong 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 ms
DROP 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 bookings0
WHERE book_date >= '2017-03-01' AND book_date < '2017-03-02' AND total_amount >= 400000
ORDER 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_ref trong 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
bookings(book_ref PK, book_date, total_amount), tickets(ticket_no PK, book_ref FK, passenger_id, passenger_name), flights(flight_id PK, flight_no, scheduled_departure, departure_airport, arrival_airport, status, aircraft_code), ticket_flights(ticket_no, flight_id, fare_conditions, amount, PK (ticket_no, flight_id)), boarding_passes(ticket_no, flight_id, seat_no, boarding_no).
Bài tập
Bài 1: Lặp lại bước 5 trên MySQL
Đề: 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_airporttrước, range/sortscheduled_departuresau. Cột đầu là bằng nên thứ tựscheduled_departuređược giữ, không cần Sort. INCLUDEcho 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ỏiINCLUDEđể 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
Buffershơ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
INCLUDEvà 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_scanvà độ 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.