Tại sao truy vấn của bạn chậm (Và tại sao thêm RAM không giải quyết được vấn đề)
Tôi từng dành cả buổi chiều nhìn chằm chằm vào một truy vấn mất 18,4 giây chỉ để lấy 452 hàng. Phản xạ đầu tiên của tôi là: ‘Máy chủ cần thêm RAM.’ Tất nhiên, gấp đôi bộ nhớ chẳng giải quyết được gì. Cơ sở dữ liệu vẫn chậm chạp, và tôi thì hoàn toàn mù tịt. Thật dễ dàng khi coi cơ sở dữ liệu như một chiếc hộp ma thuật: đưa SQL vào và nhận dữ liệu ra. Nhưng khi hiệu suất tụt dốc, bạn phải xem chính xác cách engine đang xử lý yêu cầu của mình.
Đây là lúc Kế hoạch thực thi (Execution Plan) xuất hiện. Hãy coi nó như chỉ dẫn GPS cho dữ liệu của bạn. Nếu không đọc được các chỉ dẫn này, bạn chỉ đang đoán mò xem nên thêm index nào hay viết lại lệnh join ra sao. Sau nhiều năm debug PostgreSQL và MySQL, tôi thấy rằng 90% truy vấn chậm có thể được giải quyết bằng cách hiểu một vài chỉ số cụ thể trong kết quả EXPLAIN.
Chính xác thì Kế hoạch thực thi là gì?
Trước khi chạy lệnh, hãy tìm hiểu những gì xảy ra đằng sau hậu trường. Khi bạn gửi một truy vấn, Optimizer (Bộ tối ưu hóa) sẽ đánh giá các bảng, số lượng hàng (số liệu thống kê) và các index hiện có. Sau đó, nó tính toán ‘chi phí’ (cost) của các lộ trình khác nhau. Nó nên quét toàn bộ bảng? Nên sử dụng index? Nên join Bảng A với Bảng B trước, hay ngược lại?
Kế hoạch thực thi là quyết định cuối cùng. Đó là một cấu trúc cây của các hoạt động, trong đó mỗi nút đại diện cho một bước cụ thể, như ‘Index Scan’ hoặc ‘Hash Join’. Dữ liệu chảy từ các lá của cây lên gốc. Hiểu được luồng chảy này là chìa khóa để phát hiện các ‘rò rỉ’ về hiệu suất.
PostgreSQL: Hiểu rõ sự thật với EXPLAIN ANALYZE
Trong PostgreSQL, lệnh EXPLAIN cơ bản chỉ cho thấy những gì bộ tối ưu hóa nghĩ là sẽ xảy ra. Tôi hiếm khi sử dụng nó một mình. Thay vào đó, tôi dựa vào EXPLAIN ANALYZE. Lệnh này thực sự thực thi truy vấn và cung cấp thời gian thực tế.
-- Lệnh "tủ" của tôi để debug hiệu suất
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.email = '[email protected]';Khi bạn xem xét kết quả, hãy ưu tiên ba chỉ số sau:
- Actual Time: Đây là phép đo thực tế tính bằng mili giây. Nếu ‘Actual Time’ cao hơn đáng kể so với ước tính ‘Cost’, có khả năng số liệu thống kê bảng của bạn đã cũ.
- Buffers: Chỉ số này cho biết bao nhiêu dữ liệu được đọc từ shared buffer cache so với từ đĩa. Số lượng ‘Read’ cao cho thấy bạn đang truy cập đĩa quá nhiều.
- Seq Scan vs. Index Scan: ‘Seq Scan’ (Quét tuần tự) trên một bảng có 1 triệu hàng có nghĩa là cơ sở dữ liệu đang đọc từng khối dữ liệu trên đĩa. Đây thường là đối tượng đầu tiên cần xem xét để thêm index.
MySQL: Trực quan hóa logic của bộ tối ưu hóa
MySQL 8.0 mang lại cải tiến lớn với EXPLAIN ANALYZE. Nó cung cấp đầu ra dạng cây dễ đọc hơn nhiều so với định dạng bảng cũ. Nó cho bạn thấy chính xác thời gian được tiêu tốn ở đâu trong quá trình thực thi.
-- Cách tiếp cận hiện đại trong MySQL 8.0
EXPLAIN ANALYZE
SELECT u.name, count(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;Nếu bạn đang làm việc trên phiên bản cũ hơn, cột ‘type’ là người bạn tốt nhất. Hãy tìm các giá trị cụ thể này:
- system/const: Tuyệt vời. Truy vấn tìm thấy một hàng ngay lập tức.
- eq_ref/ref: Tốt. Engine đang sử dụng index để tìm hàng hiệu quả.
- ALL: Full Table Scan. Đây là hành động tương đương với việc tìm kiếm từng trang trong thư viện chỉ để tìm một câu trích dẫn cụ thể. Nó gần như luôn luôn là một điểm nghẽn.
Các điểm nghẽn thường gặp và dấu hiệu cảnh báo
Khi xem xét một kế hoạch, hãy tìm các mẫu báo hiệu rắc rối. Một vấn đề phổ biến là **Nested Loop** trên hai bảng lớn. Hãy tưởng tượng Bảng A và Bảng B đều có 100.000 hàng. Một vòng lặp lồng nhau có thể cố gắng thực hiện 10 tỷ lần so sánh. Thường thì **Hash Join** sẽ nhanh hơn trong trường hợp này. Tuy nhiên, bộ tối ưu hóa có thể chọn vòng lặp chậm hơn nếu nó đánh giá sai rằng một bảng là nhỏ do số liệu thống kê lỗi thời.
Một dấu hiệu cảnh báo khác là ‘Using temporary’ hoặc ‘Using filesort’ trong MySQL. Điều này xảy ra khi cơ sở dữ liệu không thể sử dụng index để sắp xếp mệnh đề ORDER BY hoặc GROUP BY của bạn. Nó buộc phải đưa dữ liệu vào bộ nhớ hoặc đĩa để sắp xếp thủ công. Đối với các tập dữ liệu lớn, điều này có thể biến một truy vấn 50ms thành một truy vấn 5 giây.
Mẹo quy trình nhập dữ liệu
Các vấn đề về hiệu suất thường xuất hiện trong quá trình di chuyển dữ liệu (migration). Khi tôi cần chuyển đổi tệp CSV sang JSON để kiểm thử hoặc nhập liệu, tôi sử dụng toolcraft.app/vi/tools/data/csv-to-json. Nó chạy hoàn toàn trong trình duyệt, giữ cho dữ liệu của bạn riêng tư. Đây là một cách tuyệt vời để nhanh chóng chuẩn bị dữ liệu giả nhằm xác minh rằng các kế hoạch thực thi của bạn có khả năng mở rộng như mong đợi trước khi đưa vào môi trường production.
Thực hành thực tế: Sửa một truy vấn chậm
Giả sử việc tìm kiếm sản phẩm đang mất 2,5 giây. Bạn chạy EXPLAIN và thấy đoạn kết quả này:
-- Đoạn kết quả PostgreSQL
Filter: (price > 150.00)
Rows Removed by Filter: 842,000
Sequential Scan on products (cost=0.00..28000.00 rows=1200 width=64)Chỉ số ‘Rows Removed by Filter’ chính là bằng chứng xác thực nhất. PostgreSQL đã phải đọc 842.000 hàng chỉ để tìm ra 1.200 hàng bạn cần. Đây là ứng cử viên hoàn hảo cho một Composite Index trên (category_id, price). Sau khi thêm, kế hoạch sẽ chuyển sang Index Scan, và thời gian thực thi có thể sẽ giảm xuống dưới 10ms.
Bốn quy tắc để có hiệu suất tốt hơn
- Chạy hai lần: Lần chạy đầu tiên đọc từ đĩa; lần thứ hai đọc từ buffer cache. Luôn sử dụng hiệu suất ở trạng thái ‘warm’ (đã có cache) làm cơ sở đối chiếu.
- Làm mới thống kê: Trong PostgreSQL, hãy chạy
ANALYZE;sau các đợt cập nhật lớn. Trong MySQL, sử dụngANALYZE TABLE;. Bộ tối ưu hóa chỉ thông minh bằng lượng siêu dữ liệu (metadata) mà nó có. - Ngừng sử dụng SELECT *: Việc lấy các cột không cần thiết có thể ngăn chặn ‘Index Only Scans’. Nếu tất cả dữ liệu yêu cầu đều nằm trong index, cơ sở dữ liệu thậm chí không cần chạm vào heap của bảng chính.
- Cảnh giác với sai lệch kiểu dữ liệu (Type Mismatch): Nếu cột của bạn là
VARCHARnhưng bạn truy vấn vớiWHERE id = 123(một số nguyên), cơ sở dữ liệu có thể bỏ qua index để chạy hàm chuyển đổi trên từng hàng một.
Kết luận
Làm chủ các kế hoạch thực thi đã thay đổi cách tôi viết code. Tôi không còn hy vọng một truy vấn sẽ nhanh; tôi có thể chứng minh điều đó. Hãy biến EXPLAIN thành một phần tiêu chuẩn trong quy trình phát triển local của bạn. Bất kỳ truy vấn nào có nhiều lệnh join hoặc filter trên các bảng lớn đều xứng đáng được kiểm tra nhanh. Có thể mất một chút thời gian để làm quen với các thuật ngữ chuyên môn, nhưng lợi ích về hiệu suất là tức thì. Người dùng của bạn—và CPU máy chủ của bạn—sẽ nhận thấy sự khác biệt.
