Tối ưu query SQL: bắt đầu từ execution plan · Vì sao thêm index mà query vẫn chậm
Trang danh sách đơn hàng load mất vài phút. Phản xạ đầu tiên của mình: cột nào nằm trong WHERE thì đánh index cột đó, mỗi cột một cái. Query vẫn chậm y nguyên, còn insert vào bảng thì chậm thêm.
Nói thẳng là hồi đó mình tối ưu bằng cảm giác. Phải tới lúc chịu ngồi đọc execution plan mình mới thấy database thật ra đang làm gì với cái query đó. Giờ gặp query chậm mình đi theo đúng mấy bước dưới đây. Ví dụ dùng PostgreSQL, chỗ nào MySQL khác mình sẽ ghi chú.
Bước 1: Lấy đúng query, đúng tham số
Đừng tối ưu cái query mình đoán là chậm. Lấy từ chỗ có số liệu thật: slow query log của MySQL, extension pg_stat_statements của PostgreSQL, hoặc log query của app.
Tham số cũng phải thật. Cùng một query, khách có mười đơn với khách có năm mươi nghìn đơn có thể ra hai plan khác hẳn nhau. Mình từng test bằng customer_id = 1 trên máy local, thấy nhanh vèo, rồi kết luận query không có vấn đề gì. Sai bét.
Bước 2: Chạy EXPLAIN
Query ví dụ:
SELECT id, total_amount, created_at
FROM orders
WHERE customer_id = 42
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
Trên PostgreSQL mình chạy:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_amount, created_at
FROM orders
WHERE customer_id = 42
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
EXPLAIN trơn chỉ cho plan dự kiến. Thêm ANALYZE thì database chạy query thật và báo cả thời gian lẫn số dòng thực tế. MySQL có EXPLAIN, và từ 8.0.18 có EXPLAIN ANALYZE. SQL Server thì bật actual execution plan trong SSMS.
Cẩn thận chỗ này: EXPLAIN ANALYZE chạy query thật, nên với UPDATE hay DELETE là nó sửa data thật. Xem plan của câu lệnh ghi thì mình luôn bọc trong BEGIN ... ROLLBACK.
Bước 3: Nhìn vào đâu trong plan
Plan dưới đây mình dựng để minh hoạ cho query trên khi chưa có index phù hợp, các con số không lấy từ hệ thống thật:
Limit (actual time=2150.3..2150.4 rows=20 loops=1)
-> Sort (actual time=2150.3..2150.3 rows=20 loops=1)
Sort Key: created_at DESC
-> Seq Scan on orders (rows=1250) (actual time=0.02..2140.5 rows=1180 loops=1)
Filter: ((customer_id = 42) AND (status = 'paid'))
Rows Removed by Filter: 4998820
Plan đọc từ trong ra ngoài, tức từ dòng thụt sâu nhất đi lên. Mình tìm ba thứ.
Có quét cả bảng lớn không. Seq Scan trên PostgreSQL, type: ALL trên MySQL. Dòng đáng sợ nhất ở trên là Rows Removed by Filter: database đọc gần năm triệu dòng chỉ để giữ lại hơn một nghìn.
Số dòng ước tính có lệch xa thực tế không. Ở đây ước tính 1250, thực tế 1180, vậy là khớp. Nếu ước tính 10 mà thực tế 100.000 thì database đang quyết định dựa trên thống kê cũ. Chạy ANALYZE orders; cập nhật thống kê trước đã rồi tính tiếp.
Thời gian dồn vào bước nào. So actual time giữa các bước. Ở đây gần như toàn bộ nằm ở Seq Scan, bước Sort chẳng đáng là bao.
Hồi mới đọc plan mình hay dán mắt vào cost rồi cố hiểu con số đó nghĩa là gì. Giờ gần như bỏ qua. cost là đơn vị ước lượng nội bộ, chỉ có ích khi đem hai plan ra so với nhau.
Bước 4: Đánh index đúng thứ tự cột
Sai lầm đầu bài của mình là tạo ba index đơn cho ba cột. Database thường chỉ dùng hiệu quả được một cái, phần còn lại vẫn phải lọc và sort. Query này cần một composite index:
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);
Quy tắc mình hay dùng: cột so sánh bằng đứng trước, cột so sánh khoảng hoặc cột để sort đứng sau. Với index này, database nhảy thẳng tới nhóm đơn paid của khách 42, các đơn đã nằm sẵn theo thứ tự created_at, lấy 20 dòng đầu là xong.
Composite index đi từ trái sang phải. Index trên giúp được query lọc theo customer_id, hoặc customer_id cộng status. Còn query chỉ lọc theo status thì gần như không, giống danh bạ xếp theo họ rồi tên chẳng giúp gì khi bạn chỉ nhớ tên.
Bảng đang chạy trên prod thì trên PostgreSQL mình dùng CREATE INDEX CONCURRENTLY để không chặn ghi trong lúc tạo index. Chuyện migration trên bảng lớn mình viết kỹ hơn ở bài cuối.
Bước 5: Có index rồi mà vẫn Seq Scan?
Thường là do cách viết điều kiện. Mấy trường hợp mình gặp nhiều nhất:
- Bọc hàm quanh cột.
WHERE DATE(created_at) = '2024-05-01'bắt database tính hàm cho từng dòng. Viết lại thành khoảng:created_at >= '2024-05-01' AND created_at < '2024-05-02'. LIKEmở đầu bằng%.LIKE 'abc%'dùng được index B-tree thường,LIKE '%abc'thì chịu.- Lệch kiểu dữ liệu. Cột là chuỗi mà so với số, kiểu
WHERE phone = 912345678, MySQL phải ép kiểu từng dòng và bỏ qua index. - Điều kiện khớp quá nhiều dòng. Nếu 90% đơn là
paidthì quét cả bảng đúng là nhanh hơn, database chọn vậy là đúng. Cái này không cần sửa.
Bước 6: Đo lại, và nhớ tính phần ghi
Chạy lại EXPLAIN (ANALYZE, BUFFERS) với đúng tham số cũ. Mình muốn thấy Seq Scan thành Index Scan, dòng Rows Removed by Filter biến mất, và bước Sort cũng biến mất vì index đã có sẵn thứ tự.
Với plan minh hoạ ở trên, gần như toàn bộ hơn 2 giây nằm ở bước Seq Scan đọc gần năm triệu dòng. Có index rồi thì bước đó biến mất, database chỉ còn đọc đúng 20 entry trong index. Con số cụ thể tuỳ data và máy của bạn, nên cứ ghi lại thời gian trước và sau khi thêm index, dán vào mô tả PR là bằng chứng thuyết phục nhất.
Rồi tới phần mình từng bỏ qua: index không free. Mỗi INSERT, UPDATE, DELETE đều phải cập nhật mọi index liên quan. Ba cái index đơn mình thêm vội lúc đầu chẳng giúp query nào mà vẫn làm chậm phần ghi, nên việc cuối cùng là drop chúng đi. PostgreSQL có view pg_stat_user_indexes cho biết index nào chưa từng được dùng lần nào.
Làm hết rồi mà vẫn chậm
Thì có thể index không cứu được nữa. Query mà thật sự phải đọc rồi cộng vài triệu dòng, kiểu báo cáo doanh thu cả năm, thì nên tính sẵn vào bảng tổng hợp hoặc materialized view. Partitioning cho bảng rất lớn thì mình chưa làm đủ nhiều để dám viết, nên không đưa vào đây.
Tuần này thử xem: lấy query chậm nhất trong log, chạy EXPLAIN ANALYZE với tham số thật, tìm dòng Rows Removed by Filter to nhất. Nhiều khi chỉ nhìn vậy đã thấy vấn đề. Bài sau mình nói về loại lỗi khó chịu hơn: query không chậm, chỉ ra kết quả sai.
P.S. Plan dài vài chục dòng thì mình hay dán cho AI đọc cùng. Mình dùng skill postgres-best-practices trong PrimeKit, bộ skill mình tự làm, chuyển thể từ bộ hướng dẫn PostgreSQL của Supabase. Cái mình thích là nó cũng đi đúng thứ tự: đọc plan trước, đề xuất index thì ghi kèm chi phí ghi và chuyện lock, rồi bắt đo lại. Gõ /pk:postgres-best-practices trong Claude Code, dán query với output của EXPLAIN (ANALYZE, BUFFERS) vào là được. Nó không tự chạy gì trên database của bạn, tạo index vẫn phải qua migration và review như bình thường.
All rights reserved