Đây là bài mở đầu một sê-ri mới: Cơ sở dữ liệu cho lập trình viên — đo xem cơ sở dữ liệu thật sự làm gì với truy vấn của bạn, bằng PostgreSQL chạy trong container dùng một lần. Cùng tinh thần "đo thật" của sê-ri hệ điều hành trước, nhưng công cụ đo giờ là EXPLAIN ANALYZE, pg_stat và đồng hồ \timing. Bắt đầu bằng câu hỏi nền tảng nhất: khi bạn tìm một hàng, cơ sở dữ liệu tìm bằng cách nào, và một chỉ mục (index) thật sự nhanh hơn bao nhiêu?
Hai cách tìm một hàng
Khi bạn viết SELECT * FROM nguoi_dung WHERE email = '...', cơ sở dữ liệu có hai cách cơ bản để tìm.
Quét tuần tự (Sequential Scan): đọc lần lượt mọi trang của bảng từ đầu tới cuối, kiểm tra từng hàng xem có khớp điều kiện không. Không cần chuẩn bị gì, nhưng chi phí tỉ lệ với kích thước bảng — bảng gấp đôi thì quét lâu gấp đôi. Với một hàng cần tìm trong một triệu hàng, đây là đọc cả triệu để lấy một.
Quét chỉ mục (Index Scan): nếu cột email có một chỉ mục, chỉ mục đó là một cây B-tree sắp xếp sẵn theo giá trị email. Cơ sở dữ liệu đi từ gốc cây xuống lá — qua chỉ vài trang — là tới thẳng vị trí hàng cần, rồi nhảy vào bảng lấy hàng đó. Chi phí tỉ lệ với logarit kích thước bảng: bảng gấp đôi chỉ tốn thêm một chút. Khác biệt giữa "đọc theo kích thước" và "đọc theo log kích thước" là khác biệt giữa 14 mili giây và 0,03 mili giây, như tôi đo dưới đây.
Đo: 14 ms so với 0,03 ms
Tôi tạo một bảng nguoi_dung một triệu hàng trong PostgreSQL, rồi tìm đúng một email. Đầu tiên chưa có chỉ mục trên email, dùng EXPLAIN (ANALYZE, BUFFERS) để xem kế hoạch thật:
Seq Scan on nguoi_dung (actual time=... rows=1)
Rows Removed by Filter: 999999
Buffers: shared hit=7353
Execution Time: 14.0 ms
Cơ sở dữ liệu quét tuần tự: đọc 7353 trang (toàn bộ 57MB của bảng), lọc bỏ 999.999 hàng để giữ lại một, mất 14 mili giây (nó còn huy động 2 tiến trình song song mà vẫn tốn ngần ấy). Giờ tạo chỉ mục CREATE INDEX idx_email ON nguoi_dung(email) và chạy đúng truy vấn đó:
Index Scan using idx_email (actual time=... rows=1)
Index Cond: (email = '...')
Buffers: shared hit=1 read=3
Execution Time: 0.034 ms
Giờ nó dùng chỉ mục: chỉ đọc 4 trang (đi từ gốc B-tree xuống lá rồi vào bảng lấy hàng), mất 0,034 mili giây. So với quét tuần tự: nhanh hơn khoảng 400 lần, và đọc ít hơn khoảng 1800 lần (4 trang thay vì 7353). Chỉ mục tốn 39MB để lưu, nhưng đổi lại biến một phép tìm "đọc cả bảng" thành "đọc vài trang". Đây là lý do chỉ mục là công cụ tối ưu cơ sở dữ liệu quan trọng nhất.
Một lần tôi đo hớ: đừng đọc "cost" như thời gian
Khi mới nhìn output EXPLAIN, tôi định đo mức nhanh lên bằng một con số bắt mắt: cost. Kế hoạch quét tuần tự ghi cost=...13561, kế hoạch chỉ mục ghi cost=...8.44. Tôi lấy 13561 chia 8.44 và định viết "chỉ mục nhanh hơn khoảng 1600 lần". Con số nằm ngay đầu mỗi dòng kế hoạch, trông như thước đo chính thức.
Nhưng con số đo thật — actual time / Execution Time — lại cho 14ms so với 0,034ms ≈ 400 lần, không phải 1600. Hai con số vênh nhau bốn lần, và theo kỷ luật đo lường, hai số mâu thuẫn nghĩa là tôi đang đọc nhầm đại lượng.
Sự thật: cost không phải thời gian. Nó là một con số ước lượng mà trình lập kế hoạch (planner) tự tính theo một đơn vị trừu tượng — đại khái "chi phí tương đối" ghép từ số trang phải đọc và số hàng phải xử lý, với các hệ số cấu hình. Planner dùng cost để so sánh và chọn giữa các kế hoạch khả dĩ, chứ nó không hứa cost bằng bao nhiêu mili giây. Tỉ lệ cost (1600) và tỉ lệ thời gian thật (400) khác nhau vì chúng đo hai thứ khác nhau: một cái là dự đoán tương đối, một cái là đồng hồ. Cái đáng tin để nói "nhanh bao nhiêu" luôn là actual time và Execution Time của EXPLAIN ANALYZE — đồng hồ thật, đo trên lần chạy thật.
Bài học đo lường, cũng là kim chỉ nam của cả sê-ri này: tin đồng hồ hơn ước lượng. cost hữu ích để hiểu vì sao planner chọn kế hoạch này thay kế hoạch kia, nhưng đừng đọc nó như thời gian chạy. Và một hệ quả kèm theo: để planner ước lượng đúng, bảng phải có thống kê mới — luôn chạy ANALYZE sau khi nạp nhiều dữ liệu, trước khi trách planner "chọn sai kế hoạch".
Vì sao điều này quan trọng khi lập trình
Hệ quả đầu tiên: một chỉ mục đặt đúng chỗ là cách tăng tốc truy vấn lớn nhất bạn có. Phần lớn truy vấn chậm trong ứng dụng thật là quét tuần tự trên bảng lớn ở cột lẽ ra nên có chỉ mục — một dòng CREATE INDEX biến 14ms thành 0,03ms. Khi một endpoint chậm dần lúc dữ liệu lớn lên, nghi ngờ đầu tiên là một truy vấn đang quét cả bảng: chạy EXPLAIN ANALYZE trên nó, thấy Seq Scan trên bảng lớn với Rows Removed by Filter khổng lồ là bắt được thủ phạm. Điều đáng nói là truy vấn đó vẫn chạy đúng — nó chỉ chậm; nên lỗi kiểu này không lộ ra khi bảng còn nhỏ lúc phát triển, mà chỉ hiện hình khi dữ liệu thật lớn lên trên máy chủ, đúng lúc khó sửa nhất.
Hệ quả thứ hai: EXPLAIN ANALYZE là công cụ chẩn đoán đầu tiên, không phải phỏng đoán. Đừng đoán truy vấn dùng chỉ mục hay không — hỏi thẳng cơ sở dữ liệu. EXPLAIN (không ANALYZE) cho kế hoạch và ước lượng mà không chạy; EXPLAIN ANALYZE chạy thật và cho actual time cùng số hàng thật; thêm BUFFERS cho biết đọc bao nhiêu trang (và bao nhiêu từ RAM hit so với từ đĩa read). Đọc ba thứ đó là thấy chính xác cơ sở dữ liệu làm gì, thay vì suy diễn.
Hệ quả thứ ba là bài học đo lường mang sang cả sê-ri. Con số mang theo: quét tuần tự đọc cả bảng (7353 trang, 14ms cho 1 triệu hàng), quét chỉ mục đọc vài trang (4 trang, 0,03ms) — nhanh ~400 lần; nhưng con số nói lên điều đó là actual time của EXPLAIN ANALYZE, không phải cost (một ước lượng trừu tượng của planner). Cơ sở dữ liệu, cũng như hệ điều hành, sẵn sàng cho bạn nhiều con số; biết con số nào là đồng hồ và con số nào là ước lượng là điều kiện đầu tiên để tối ưu đúng chỗ.
Thử ba mươi giây
Với một truy vấn bất kỳ trong cơ sở dữ liệu của bạn, chạy EXPLAIN (ANALYZE, BUFFERS) trước nó và đọc ba thứ: dòng đầu là Seq Scan hay Index Scan (quét cả bảng hay đi thẳng), Execution Time ở cuối (thời gian thật, tính bằng mili giây), và Buffers: shared hit/read (đọc bao nhiêu trang, từ RAM hay đĩa). Nếu bạn thấy Seq Scan trên một bảng lớn với Rows Removed by Filter rất cao, đó là một chỉ mục đang thiếu — thử thêm CREATE INDEX trên cột trong WHERE rồi chạy lại, và so Execution Time trước với sau. Đừng nhìn cost để kết luận nhanh chậm; nhìn Execution Time — đó là đồng hồ.
