Các bộ tối ưu hóa truy vấn cơ sở dữ liệu hứa hẹn sẽ tự động lựa chọn kế hoạch thực thi nhanh nhất cho các truy vấn của bạn, nhưng các kiểm thử mới tiết lộ rằng chúng thường không đạt được mục tiêu này. Mặc dù các hệ thống này hoạt động tốt trong các tình huống đơn giản, chúng thường gặp khó khăn với các mẫu dữ liệu thực tế, dẫn đến các vấn đề hiệu suất có thể khiến các nhà phát triển bất ngờ.
Thực Tế Đằng Sau Tối Ưu Hóa Truy Vấn
Kiểm thử hiệu suất gần đây trên các phân phối dữ liệu khác nhau cho thấy các bộ tối ưu hóa truy vấn liên tục chọn các kế hoạch không tối ưu. Việc kiểm thử bao gồm các truy vấn SELECT đơn giản với điều kiện phạm vi, tuy nhiên ngay cả những thao tác cơ bản này cũng tiết lộ khoảng cách đáng kể giữa những gì bộ tối ưu hóa chọn và những gì thực sự chạy nhanh nhất. Vấn đề trở nên rõ rệt hơn với các truy vấn phức tạp liên quan đến nhiều bảng và phép nối.
Vấn đề cốt lõi nằm ở cách các bộ tối ưu hóa đưa ra quyết định. Chúng dựa vào các tóm tắt thống kê về dữ liệu của bạn và các mô hình chi phí đơn giản hóa để ước tính hiệu suất. Tuy nhiên, những thống kê này nhất thiết là các biểu diễn không đầy đủ của các mẫu dữ liệu thực tế. Cơ sở dữ liệu thực tế chứa các tương quan, phân cụm và các mẫu phân phối không thể được nắm bắt đầy đủ trong các tóm tắt thống kê gọn gàng.
Các Mẫu Hiệu Suất Kế Hoạch Truy Vấn:
- Dữ liệu đồng nhất: Quét chỉ mục được chọn cho độ chọn lọc 1-5%, nhưng quét bitmap thực tế hoạt động tốt hơn
- Dữ liệu tuần hoàn: Bộ lập kế hoạch kiên trì với quét chỉ mục ngay cả ở độ chọn lọc thấp khi bitmap sẽ là tối ưu
- Dữ liệu tuyến tính: Chỉ các tập dữ liệu tuyến tính hoàn hảo mới tạo ra việc lựa chọn kế hoạch tối ưu một cách nhất quán
- Tác động bộ nhớ đệm nóng: Sự khác biệt hiệu suất giữa các phương pháp quét phần lớn biến mất với dữ liệu được lưu đệm
 |
|---|
| Biểu diễn trực quan về tần suất bộ tối ưu hóa truy vấn chọn kế hoạch thực thi tối ưu, làm nổi bật khoảng cách hiệu suất trong việc thực thi truy vấn |
Sự Thất Vọng Của Cộng Đồng Với Tính Dự Đoán Của Kế Hoạch
Các chuyên gia cơ sở dữ liệu ngày càng lên tiếng về tính không thể dự đoán của bộ tối ưu hóa. Một số cho rằng các bộ lập kế hoạch truy vấn hiện đại đã trở nên quá thông minh đến mức gây hại cho chính chúng, tạo ra các tình huống mà những thay đổi nhỏ trong dữ liệu hoặc cấu trúc truy vấn có thể thay đổi hiệu suất một cách đáng kể. Tính không thể dự đoán này trở nên đặc biệt có vấn đề trong môi trường sản xuất nơi hiệu suất ổn định quan trọng hơn tối ưu hóa lý thuyết.
Cộng đồng PostgreSQL đối mặt với những thách thức đặc biệt vì cơ sở dữ liệu này không cho phép người dùng ép buộc các kế hoạch thực thi hoặc chỉ mục cụ thể. Mặc dù triết lý thiết kế này nhằm coi các kế hoạch không tối ưu như các lỗi cần được sửa, nó để lại cho các nhà phát triển những lựa chọn hạn chế khi bộ tối ưu hóa đưa ra những lựa chọn tồi. Các tiện ích mở rộng như pg_hint_plan cung cấp một số giải pháp, nhưng chúng không phải là một phần của hệ thống cốt lõi.
Nó đang trở nên quá phức tạp, quá không thể dự đoán. Mọi truy vấn và biến số đều trở thành một bất ngờ trong sản xuất.
Hạn chế của PostgreSQL Plan Control:
- Không có khả năng gốc để buộc sử dụng các chỉ mục cụ thể hoặc kế hoạch thực thi
- Các cờ phạm vi phiên cung cấp kiểm soát thô nhưng có thể ảnh hưởng đến các truy vấn khác
- CTE được vật chất hóa cung cấp rào cản tối ưu hóa nhưng có thể ngăn chặn các tối ưu hóa mong muốn
- Tiện ích mở rộng bên thứ ba pg_hint_plan cung cấp khả năng buộc kế hoạch
- RDS Aurora PostgreSQL được báo cáo là hỗ trợ các kế hoạch truy vấn được quản lý
Phức Tạp Về Phần Cứng và Môi Trường
Các môi trường máy tính hiện đại thêm một lớp phức tạp khác vào tối ưu hóa truy vấn. Các mô hình chi phí được phát triển cho các hệ thống on-premise truyền thống có thể không phản ánh chính xác các đặc tính hiệu suất của các hệ thống lưu trữ đám mây, mảng NVMe , hoặc môi trường container hóa. Các tính năng như prefetching, có thể cải thiện hiệu suất một cách đáng kể cho các mẫu truy cập nhất định, không phải lúc nào cũng được tính đến đúng cách trong các tính toán chi phí của bộ tối ưu hóa.
Việc kiểm thử cho thấy rằng bitmap scan liên tục vượt trội hơn index scan nhờ khả năng prefetching, tuy nhiên các bộ tối ưu hóa thường chọn phương pháp index scan chậm hơn. Những tối ưu hóa cấp phần cứng này có thể thay đổi hoàn toàn bối cảnh hiệu suất theo những cách mà các mô hình chi phí truyền thống không nắm bắt được.
Thông số kỹ thuật môi trường kiểm thử:
- Phần cứng: Bộ xử lý AMD Ryzen 9300X với bộ nhớ RAID NVMe
- Điều kiện bộ nhớ đệm: Kiểm thử bộ nhớ đệm lạnh (không có dữ liệu trong page cache hoặc shared buffers)
- Loại truy vấn: Các truy vấn SELECT đơn giản với điều kiện WHERE phạm vi đơn lẻ
- Phân phối dữ liệu được kiểm thử: Đồng nhất, chu kỳ có nhiễu, tuyến tính với các mức độ nhiễu khác nhau
Các Phương Pháp Thay Thế và Giải Pháp Tạm Thời
Một số nhà phát triển ủng hộ các phương pháp truy vấn xác định hơn, đặc biệt đối với các truy vấn được tạo bởi ứng dụng nơi tính dự đoán được quan trọng hơn tối ưu hóa lý thuyết. Các kỹ thuật như sử dụng Common Table Expressions ( CTEs ) như các rào cản tối ưu hóa, điều chỉnh cài đặt planner cấp phiên, hoặc tái cấu trúc truy vấn có thể cung cấp nhiều quyền kiểm soát hơn đối với các kế hoạch thực thi.
Các hệ thống cơ sở dữ liệu cao cấp cung cấp xử lý truy vấn thích ứng, cho phép engine điều chỉnh phương pháp của mình trong quá trình thực thi dựa trên dữ liệu hiệu suất thực tế. Tuy nhiên, những tính năng này không có sẵn phổ biến và thêm vào sự phức tạp riêng của chúng vào quản lý cơ sở dữ liệu.
Thách thức cơ bản vẫn còn: tối ưu hóa truy vấn về cơ bản là một vấn đề nén có tổn thất. Các bộ tối ưu hóa phải đưa ra quyết định nhanh dựa trên thông tin hạn chế, và các kế hoạch hoàn hảo cho tất cả các tình huống có thể có thể không thể đạt được về mặt toán học trong giới hạn thời gian và tài nguyên hợp lý.
Tham khảo: How often is the query plan optimal?