Có index rồi, vì sao database vẫn quét cả bảng?

Bối cảnh hư cấu, bằng chứng lab thật. Fortex trong tuyến truyện này là bối cảnh hư cấu; Tom và Ben là nhân vật. Các cuộc trao đổi minh họa quá trình suy nghĩ. Số đo và assertion thuộc lab Docker được dẫn trong bài, không phải số liệu production của Fortex.

Hành trình ở Fortex · Chương 2/8

Ben đã thêm index. Dòng Seq Scan vẫn còn

Sau buổi dọn MySQL, Ben chuyển sang màn hình tra cứu sự kiện của dịch vụ PostgreSQL ở Fortex. Cậu nhớ lời Tom về page và muốn giảm phần database phải đọc. Thêm index vào cột trạng thái trong WHERE nghe là bước tiếp theo rất tự nhiên.

“Nhưng nó vẫn quét bảng anh ạ. Hay mình bắt nó dùng index?”

Tom nhìn câu query rồi hỏi: “Trạng thái này thường gặp không?”

“Có trong filter thì em nghĩ index sẽ giúp.”

“Anh hiểu. Em muốn đi thẳng tới dòng cần lấy. Giờ thử hai giá trị của cùng filter nhé. Trước khi chạy, em đoán hai query sẽ cần đọc cùng một lượng dữ liệu không?”

Ben chưa trả lời ngay. Hôm trước cậu đã nhầm số dòng với kích thước file. Hôm nay, có lẽ chữ có index cũng đang che mất một đại lượng khác.

Đọc gì trước bài này?

Ben mở lại trang ghi chú về file MySQL. Tom nhắc cậu giữ lại ý tưởng page, nhưng kiểm tra lại cơ chế khi đổi engine. Fortex có cả dịch vụ cũ lẫn dịch vụ mới; một bài học hữu ích không có nghĩa mọi chi tiết được bê nguyên sang.

Bạn cần biết index là một cấu trúc bổ sung để tìm dữ liệu, và dữ liệu được đọc theo page chứ không phải từng ký tự rời rạc. Nếu phần page còn mơ hồ, bài xóa dữ liệu và kích thước file MySQL đưa ra một ví dụ dễ quan sát. Bài đó dùng InnoDB; bài này chuyển sang PostgreSQL 17.6, nơi dữ liệu bảng nằm trong heap và index chứa thông tin giúp tìm tới tuple.

Không cần thuộc hết các loại scan. Ta sẽ bắt đầu từ output của lab và giải thích đúng ba loại thực sự xuất hiện.

Có index rồi, database còn phải làm gì?

“Index cho em vị trí dòng,” Ben nói. “Vậy phần còn lại chỉ là lấy dữ liệu.”

Tom khoanh cột payload. “Đúng. Nhưng chữ chỉ ấy đáng đo đấy. Cột này đang nằm ở đâu?”

Với index B-tree trên status, database có thể tìm những entry khớp giá trị. Nhưng truy vấn dưới đây còn cần payload, một cột không nằm trong index đó:

SELECT sum(length(payload))
FROM events
WHERE status = 'rare';

Tra được entry chưa phải đã có toàn bộ dữ liệu cần tính. Database còn phải tìm các tuple tương ứng trong heap, kiểm tra chúng có nhìn thấy được ở snapshot hiện tại không, đọc payload và tính tổng.

Seq Scan đi qua các page của bảng, kiểm tra điều kiện và lấy dòng khớp. Index Scan lần theo index rồi tới heap để lấy dữ liệu. Bitmap Index Scan + Bitmap Heap Scan thu thập vị trí cần đọc thành bitmap trước, rồi truy cập heap theo page. Đây không phải ba mức huy chương “tệ, tốt, tốt nhất”. Chúng là những cách làm với chi phí khác nhau; tài liệu EXPLAIN hướng dẫn đọc từng node.

Sơ đồ minh họa đường truy cập. Không phải mỗi ô là một page được đo của dataset.

Nếu gần hết bảng đều khớp, đi qua cấu trúc index rồi vẫn phải tới gần hết heap có thể làm thêm việc. Tuy nhiên, cũng đừng rút ra quy tắc “qua X% thì luôn bỏ index”. Phân bố dữ liệu, độ tương quan với thứ tự vật lý, cột cần lấy, cache, kích thước bảng, giới hạn LIMIT và các tham số cost đều có thể đổi quyết định.

Tạo hai nhóm dữ liệu có chủ đích

Ben muốn lấy một ít dữ liệu bất kỳ. Tom đề nghị một dataset biết trước phân bố để hai người có thể dự đoán và đối chiếu. Ben nhận phần tạo nhóm hiếm, nhóm phổ biến và giữ nguyên payload giữa các nhánh.

Lab dùng 60.000 dòng. Mỗi ID chia hết cho 100 mang trạng thái rare; các ID còn lại mang trạng thái common. Vì thế có đúng 600 dòng rare và 59.400 dòng common. Payload dài 256 ký tự, được sinh từ ID, không dùng dữ liệu ngẫu nhiên thay đổi giữa các lần chạy.

CREATE TABLE events (
    id INT PRIMARY KEY,
    status TEXT NOT NULL,
    payload TEXT NOT NULL
);

INSERT INTO events
SELECT n,
       CASE WHEN n % 100 = 0 THEN 'rare' ELSE 'common' END,
       repeat(md5(n::text), 8)
FROM generate_series(1, 60000) n;

ANALYZE events;

Mỗi truy vấn đều tính tổng độ dài payload, tránh dùng SELECT count(*) rồi vô tình so sánh một trường hợp có thể được phục vụ chỉ từ index với một trường hợp phải đọc heap. Mục tiêu là nhìn quyết định truy cập dữ liệu khi vẫn cần cột ngoài index.

Môi trường: PostgreSQL 17.6, 1 CPU, RAM tối đa 512 MB, shared_buffers=64MB, tắt parallel query cho phép đối chiếu này. Dữ liệu nằm trên tmpfs. Không dùng các số thời gian ở đây để suy ra tốc độ NVMe, throughput hay p99 production.

Chạy lại bằng source tải ở cuối bài:

python3 labs/engine.py postgres-index-selectivity

So sánh trước và sau khi thêm index

“Nếu common không dùng index, em vẫn muốn thử ép một lần,” Ben nói.

“Được. Giữ nó làm nhánh đối chiếu,” Tom đáp. “Đừng biến nút thử nghiệm thành cấu hình toàn hệ thống. Và kiểm tra kết quả tính tổng có còn giống nhau.”

Trước khi tạo index trên status, cả hai bộ lọc dùng Seq Scan. Có primary key trên id, nhưng primary key ấy không trực tiếp giải quyết điều kiện status = ....

Sau khi lấy baseline, lab tạo index và cập nhật statistics:

CREATE INDEX events_status_idx ON events(status);
ANALYZE events;

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON, TIMING OFF)
SELECT sum(length(payload))
FROM events
WHERE status = 'rare';

Đổi rare thành common rồi chạy lại. Đọc plan, không chỉ nhìn tổng thời gian:

Hit và read được cộng để thể hiện số lần truy cập shared buffer ở node gốc, không phải dung lượng dữ liệu riêng biệt hay số lần đọc SSD. Số đo lấy từ report đã xuất bản; raw log và SQL lưu riêng.

Với rare, index đưa truy vấn tới một phần nhỏ hơn của heap trong lần chạy này. Với common, quét bảng vẫn là kế hoạch được chọn, dù index đã tồn tại và vừa được ANALYZE.

Để kiểm tra đây không phải chỉ là “planner chưa biết index”, lab có một nhánh đối chiếu thêm:

SET enable_seqscan = off;

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON, TIMING OFF)
SELECT sum(length(payload))
FROM events
WHERE status = 'common';

Cấu hình này chỉ nằm trong session lab, dùng để xem một đường truy cập khác; không phải lời khuyên tắt Seq Scan trên production. Trong lần chạy đã xuất bản, nhánh ép dùng Bitmap Heap Scan + Bitmap Index Scan. Nó vẫn phải xử lý 59.400 dòng và vẫn đụng gần hết các heap page.

Số thời gian kể được đến đâu?

Ben nhìn các lần đo rồi ngẩng lên: “Nhánh ép index có trung vị nhỏ hơn. Thế mình không thể viết Seq Scan nhanh hơn ở đây.”

Tom nhìn lại bảng. “Đúng. Anh cũng không được lấy câu giải thích về cost thay cho kết quả đo. Mẫu này giúp mình đọc plan; nó chưa phân xử được chênh lệch nhỏ về tốc độ.”

Lần đầu tiên Ben sửa một kết luận trong bản nháp trước khi Tom phải hỏi.

Hai nhánh common có median gần nhau trong phép đo này. Nếu một mẫu của nhánh ép index nhanh hơn mẫu Seq Scan, điều đó không đủ chứng minh nên ép index cho hệ thống thật. Mỗi phương án có năm mẫu warm chạy nối tiếp; không có tráo thứ tự ngẫu nhiên, không kiểm soát noisy neighbor và không có khoảng tin cậy để khẳng định chênh lệch nhỏ.

Vì vậy, kết luận mạnh của lab là plan đã khác theo độ chọn lọc, và kết quả tính toán không thay đổi. Nó không chứng minh một ngưỡng selectivity phổ quát, cũng không chứng minh Seq Scan luôn nhanh hơn index khi trả nhiều dòng.

Các file EXPLAIN JSON đầy đủ nằm trong thư mục artifacts của lần chạy, trong đó giữ cả estimated rows, actual rows, node con và buffer counters. Nếu cần điều tra một truy vấn của bạn, đó là những chi tiết đáng so sánh hơn một con số thời gian đứng một mình.

Sửa thế nào còn tùy vấn đề đang gặp

“Em sẽ không mở ticket ‘xóa Seq Scan’ nữa,” Ben nói. “Em ghi truy vấn nào chậm, lấy những cột nào và nhóm dữ liệu nào.”

Tom gật đầu. “Thế mình mới chọn được thứ cần đổi.”

Trong bài này, chỉ phép thêm index và phép ép đường plan được chạy. INCLUDE, partial index và điều chỉnh statistics nâng cao là các hướng cần một thí nghiệm khác. Tham khảo index-only scan, partial index và planner statistics trước khi áp dụng.

Index của lab chiếm 434,176 byte (khoảng 0,41 MiB); đó mới là phần dung lượng. Bài này chưa benchmark insert/update/delete trước và sau khi thêm index, nên không gán một tỷ lệ “ghi chậm hơn X%”. Chi phí ghi là thứ phải đo với workload thực, không suy ra từ biểu đồ đọc này.

Đừng bỏ qua phần dữ liệu thay đổi theo thời gian

Ben lưu plan để làm mốc so sánh. Tom hỏi một câu cuối: “Nếu trạng thái hiếm hôm nay thành phổ biến sau đợt nhập dữ liệu, em có giữ nguyên kết luận không?” Cậu bổ sung phân bố dữ liệu vào ghi chú của lần đo.

Một index hợp lý khi status='pending' chỉ chiếm 1% có thể cho câu chuyện khác khi pending tăng thành 70% do downstream hỏng. Cùng một SQL, khác phân bố dữ liệu, khác phần việc còn lại.

Ngược lại, một Seq Scan trên bảng nhỏ nằm sẵn trong RAM có thể rất rẻ. Thấy từ “scan” rồi lập tức tối ưu có khi chỉ biến một truy vấn đơn giản thành một bộ index phải bảo trì lâu dài.

Nếu heap phình ra vì giữ nhiều phiên bản cũ, số page cần đi qua cũng là một phần của vấn đề. Bài snapshot và VACUUM cho thấy vì sao COUNT(*) vẫn như cũ mà file bảng lại lớn lên. Đây là chỗ hai cuộc điều tra nối với nhau: chọn plan và tình trạng storage không tồn tại độc lập.

Lần tới thấy Seq Scan, kiểm tra theo thứ tự này

Lần này Ben tự cầm bút ở bảng trắng. Cậu không ghi tên index đầu tiên; cậu ghi câu hỏi truy vấn cần trả lời và phần việc còn lại sau khi lọc.

• SQL có thật sự dùng cột, kiểu dữ liệu và biểu thức mà index phục vụ được không?
• Actual rows khớp bao nhiêu phần dữ liệu? Estimated rows lệch ở đâu?
• Truy vấn cần lấy cột nào, có phải tới heap không?
• Plan đọc bao nhiêu buffer, có sort, spill hoặc vòng lặp lồng nhau nào đắt hơn scan không?
• Statistics có phù hợp dữ liệu hiện tại không?
• Nếu thử một plan khác, kết quả có giống nhau và phép đo có đủ đại diện không?

Có index là bạn đã cung cấp thêm một lựa chọn. Database vẫn phải quyết định có nên chọn nó cho câu hỏi cụ thể đang được hỏi hay không.

Trang sổ Ben mang sang ngày mai

Ben không ép plan trên dịch vụ Fortex. Cậu giữ thử nghiệm làm bằng chứng để chọn hướng tối ưu theo query và workload thật. Trang thứ hai trong sổ có một dòng gạch chân: “Một đường truy cập mới là một lựa chọn. Muốn chọn phải đếm công việc.”

Ngay lúc đóng laptop, cậu thấy một transaction báo cáo vẫn mở. Chỉ đọc thôi, Ben nghĩ. Rồi cậu nhớ mình vừa viết chữ phải đếm, và gọi Tom lại.

Chương trước: Xóa 90% dữ liệu, vì sao file MySQL vẫn không nhỏ lại? · Chương tiếp: Một transaction chỉ đọc có thể giữ PostgreSQL phình lên thế nào? · Bắt đầu hành trình