Các lệnh SQL có thể dùng để truy xuất dữ liệu từ bất cứ cơ sở dữ liệu nào. Nếu bạn đã từng truy xuất thông tin từ cơ sở dữ liệu, rất có thể bạn đã gặp những lệnh truy vấn chậm. Đôi khi, thời gian phản hồi chậm là do hệ thống quá tải, và trong một số trường hợp là do người dùng không viết những lệnh truy vấn hiệu quả, và đây mới là nguyên nhân phổ biến hơn. Để có hiệu năng tốt hơn, chúng ta cần những câu lệnh tối ưu, nhanh hơn, và hiệu quả hơn. Bài viết này trình bày cách tối ưu hóa các lệnh truy vấn SQL để có kết quả tốt hơn. Chủ đề tối ưu hóa truy vấn là rất rộng nhưng chúng tôi sẽ cố gắng đề cập đến những điểm quan trọng nhất. Trong bài viết này, INDA sẽ không đi sâu vào phân tích cơ sở dữ liệu, mà là những mẹo và thủ thuật đơn giản để điều chỉnh truy vấn, có thể được áp dụng nhằm cải thiện hiệu suất ngay lập tức.
Mục lục
Giới thiệu
Tối ưu hóa truy vấn là một kĩ năng quan trọng đối với các Data Analyst, nguời khai thác dữ liệu từ CSDL với tần suất cao. Để cải thiện hiệu quả của các truy vấn, các lập trình viên và Data Analyst cần hiểu được công cụ tối ưu hóa truy vấn và và cách công cụ này chọn một lối truy cập và chuẩn bị kế hoạch tiến hành truy vấn. Việc điều chỉnh truy vấn có liên quan tới những kiến thức về các kĩ thuật như công cụ tối ưu theo chi phí và theo kinh nghiệm, cộng với những công cụ giúp phân tích kế hoạch thực thi truy vấn (execution plan) do CSDL cung cấp. Cách tốt nhất để điều chỉnh hiệu suất là tìm cách viết các truy vấn theo nhiều cách khác nhau, từ đó so sánh kế hoạch thực thi của mỗi truy vấn.
Các bí quyết chung để tối ưu hóa truy vấn
Mỗi tip dưới đây đã được kiểm tra bằng cách chạy cả hai truy vấn gốc và truy vấn đã chỉnh sửa khi truy vấn dữ liệu từ cơ sở dữ liệu 11g Oracle mẫu, đặc biệt trên lược đồ Sales (Sales Schema). Tôi đã ghi lại thời gian trung bình của mỗi truy vấn để thể hiện sự tối ưu khi xây dựng các truy vấn hiệu quả hơn.
Lưu ý:
Các kỹ thuật tối ưu truy vấn có sự khác biệt nhau giữa các hệ quản trị CSDL khác nhau, phụ thuộc vào Database Engine của từng hệ quản trị CSDL. Độc giả nên lưu tâm điều này.
Tip #1: Sử dụng Tên Cột thay vì * trong lệnh SELECT
Nếu bạn chỉ đang chọn một số ít cột từ bảng, không cần sử dụng lệnh SELECT *. Dù cách viết này dễ, nhưng lại gây tốn nhiều thời gian hơn để cơ sở dữ liệu có thể hoàn thành truy vấn. Bằng cách chỉ chọn những cột cần thiết, bạn đang giảm bớt kích cỡ của bảng kết quả, giảm lưu lượng mạng, và làm tăng hiệu năng của truy vấn.
Ví dụ:
Truy vấn gốc:
| 1 | SELECT * FROM SH.Sales; |
Truy vấn đã được cải thiện:
| 1 | SELECT s.prod_id FROM SH.sales s; |
Tip #2: Tránh đưa mệnh đề HAVING trong lệnh SELECT
Mệnh đề HAVING có tác dụng lọc sau khi các hàng đã được chọn và được sử dụng như một filter. Mệnh đề này không có nhiều tác dụng với lệnh SELECT. Cách hoạt động của nó là quét qua bảng kết quả cuối cùng để phân tích và bỏ những hàng không đáp ứng điều kiện của mệnh đề HAVING.
Ví dụ:
Truy vấn gốc:
| 1234567 | SELECT s.cust_id,count(s.cust_id) FROM SH.sales s GROUP BY s.cust_id HAVING s.cust_id != '1660' AND s.cust_id != '2'; |
Truy vấn đã được cải thiện:
| 123456789 | SELECT s.cust_id,count(cust_id) FROM SH.sales s WHERE s.cust_id != '1660' AND s.cust_id !='2' GROUP BY s.cust_id; |
Tip #3: Loại bỏ các mệnh đề DISTINCT không cần thiết
Khi xem xét ví dụ dưới đây, từ khóa DISTINCT trong truy vấn gốc là không cần thiết vì tên bảng đã chứa p.ID là khóa chính của bảng – đây là một phần của kết quả. Việc đưa mệnh dề DISTINCT sẽ khiến CSDL thực hiện thêm một phép so sánh để loại bỏ các dòng trùng nhau trong tập kết quả.
Ví dụ:
Truy vấn gốc:
| 1234567 | SELECT DISTINCT * FROM SH.sales s JOIN SH.customers c ON s.cust_id= c.cust_id WHERE c.cust_marital_status = 'single'; |
Truy vấn đã được cải thiện:
| 1234567 | SELECT * FROM SH.sales s JOIN SH.customers c ON s.cust_id = c.cust_id WHERE c.cust_marital_status='single'; |
Tip #4:Un-nest các truy vấn nội bộ (Sub-query)
Viết lại các sub-query sẽ giúp truy vấn chạy hiệu quả và tối ưu hơn. Nhìn chung, việc unnest các sub-query luôn được tiến hành với sub-query tương tác với tối đa một bảng từ mệnh đề FROM, được sử dụng trong các mệnh đề ANY, ALL, và EXISTS. Một sub-query không tương quan, hay một sub-query với nhiều hơn một bảng từ mệnh đề FROM, sẽ được làm phẳng nếu dựa theo ngữ cảnh của truy vấn đó.
Ví dụ:
Truy vấn gốc:
| 12345678910111213 | SELECT * FROM SH.products p WHERE p.prod_id = (SELECT s.prod_id FROM SH.sales s WHERE s.cust_id = 100996 AND s.quantity_sold = 1 ); |
Truy vấn đã được cải thiện:
| 123456789 | SELECT p.* FROM SH.products p, sales s WHERE p.prod_id = s.prod_id AND s.cust_id = 100996 AND s.quantity_sold = 1; |
Tip #5:Cân nhắc sử dụng mệnh đề IN khi truy vấn một cột đã được đánh index
Mệnh đề IN có thể được khai thác cho các lệnh truy vấn sử dụng bảng đã được đánh index, và đồng thời, công cụ tối ưu hóa có thể sắp xếp danh sách IN để khớp với thứ tự phân loại của chỉ số, dẫn tới lệnh truy vấn hiệu quả hơn. Cần chú ý rằng danh sách IN chỉ được chứa các hàm, hoặc các giá trị là hằng số trong việc thực hiện khối truy vấn, ví dụ như các tham chiếu ngoài.
Ví dụ:
Truy vấn gốc:
| 1234567 | SELECT s.* FROM SH.sales s WHERE s.prod_id = 14 OR s.prod_id = 17; |
Truy vấn đã cải thiện:
| 12345 | SELECT s.* FROM SH.sales s WHERE s.prod_id IN (14, 17) |
Tip #6:Sử dụng EXISTS thay cho DISTINCT khi kết nối các bảng có một hoặc nhiều liên kết.
Từ khóa DISTINCT có tác dụng chọn tất cả cột trong bảng, phân tích và lọc ra các cột bị trùng lặp. Thay vào đó, nếu bạn sử dụng sub-query với từ khóa EXISTS, bạn có thể tránh việc phải trả lại toàn bộ một bảng.
Ví dụ:
Truy vấn gốc:
| 12345 | SELECT DISTINCT c.country_id, c.country_name FROM SH.countries c,SH.customers e WHERE e.country_id = c.country_id; |
Truy vấn đã được cải thiện:
| 1234567 | SELECT c.country_id, c.country_name FROM SH.countries c WHERE EXISTS (SELECT 'X' FROM SH.customers e WHERE e.country_id = c.country_id); |
Tip #7: Sử dụng UNION ALL thay cho UNION
Mệnh đề UNION ALL nhanh hơn UNION bỏi vì mệnh đề UNION ALL không tính tới các điểm trùng lập và mệnh đề UNION lại tìm các điểm trùng lặp trong bảng khi chọn hàng, dù có điểm trùng lặp hay không hãy ưu tiên sử dụng UNION ALL khi chúng ta biết chắc chắn mỗi dòng trong kết quả sẽ là duy nhất hoặc có thể chấp nhận việc trùng lặp.
Ví dụ:
Truy vấn gốc:
| 123456789 | SELECT cust_id FROM SH.sales UNION SELECT cust_id FROM customers; |
Truy vấn đã cải thiện:
| 123456789 | SELECT cust_id FROM SH.sales UNION ALL SELECT cust_id FROM customers; |
Tip #8:Tránh sử dụng OR trong các mệnh đề thỏa mãn nhiều điều kiện
Mỗi khi bạn đặt ‘OR’ trong condition kết nối, truy vấn sẽ chậm đi ít nhất một hoặc hai thừa số
Ví dụ:
Truy vấn gốc
| 1234567 | SELECT * FROM SH.costs c INNER JOIN SH.products p ON c.unit_price = p.prod_min_price OR c.unit_price = p.prod_list_price; |
Truy vấn đã cải thiên
| 1234567891011121314151617 | SELECT * FROM SH.costs c INNER JOIN SH.products p ON c.unit_price = p.prod_min_price UNION ALL SELECT * FROM SH.costs c INNER JOIN SH.products p ON c.unit_price = p.prod_list_price; |
Tip #9:Tránh đặt hàm số bên phải của toán tử so sánh
Các hàm số hay phương pháp rất thường được sử dụng với truy vấn SQL tương ứng. Viết lại truy vấn bằng cách bỏ các hàm tổng hợp sẽ tăng hiệu suất đáng kế, đặc biệt với các cột đã được đánh index (chỉ mục).
Ví dụ:
Truy vấn gốc
| 1234567 | SELECT * FROM SH.sales WHERE EXTRACT (YEAR FROM TO_DATE (time_id, ‘DDMON-RR’)) = 2001 AND EXTRACT (MONTH FROM TO_DATE (time_id, ‘DD-MON-RR’)) =12; |
Truy vấn đã cải thiện:
| 123 | SELECT * FROM SH.sales WHERE TRUNC (time_id) BETWEEN TRUNC(TO_DATE(‘12/01/2001’, ’mm/dd/yyyy’)) AND TRUNC (TO_DATE (‘12/30/2001’,’mm/dd/yyyy’)); |
Tip #10:Loại bỏ các phép toán thừa
Sẽ có lúc bạn thực hiện các phép toán trong một statement của SQL. Các phép toán có thể làm giảm hiệu suất đáng kể nếu cách viết không phù hợp. Mỗi lần truy vấn tìm thấy một hàng, nó sẽ thực hiện lại phép toán. Vì vậy, việc loại bỏ phép toán không cần thiết sẽ giúp truy vấn chạy nhanh hơn rất nhiều.
Ví dụ:
Truy vấn gốc:
| 12345 | SELECT * FROM SH.sales s WHERE s.cust_id + 10000 < 35000; |
Truy vấn đã cải thiện:
| 12345 | SELECT * FROM SH.sales s WHERE s.cust_id < 25000; |
Kết luận
Tối ưu hóa truy vấn là một công việc phổ biến dành cho các quản trị viên cơ sở dữ liệu và người thiết kế ứng dụng để điều chỉnh hiệu quả chung của hệ thống cơ sở dữ liệu. Mục đích của bài viết này là đưa ra các trường hợp trong SQL để cung cấp một tư liệu tham khảo nhanh chóng, dễ hiểu trong giai đoạn phát triển và bảo trì các truy vấn của cơ sở dữ liệu. Ngay cả khi bạn có hạ tầng tốt, hiệu suất có thể suy giảm đáng kể do có các truy vấn thiếu hiệu quả. Tối ưu hóa truy vấn sẽ có ảnh hưởng rất lớn tới hiệu quả của DBMS, và hoạt động này sẽ tiếp tục phát triển với những chiến lược mới, phức tạp hơn dựa trên sự phát triển của các Database Engine mới, tối ưu hơn. Vì vậy, chúng ta nên cố gắng làm theo các tip nói trên để truy vấn trở nên hiệu quả hơn. Chúng ta có thể tối ưu hóa mà không tốn quá nhiều công sức nếu thường xuyên thực hiện và tuân theo các nguyên tắc. Trọng tâm chính là tối ưu hóa truy vấn để đạt được hiệu suất cao nhất.
Nguồn: Internet
Chúng tôi chuyên cung cấp những khoá học về Phân tích dữ liệu như Power BI, SQL, Data Model, Data Warehouse,… đăng ký ngay để nhận được tư vấn chi tiết khoá học dành riêng cho bạn nhé!
SQL Level 2: Advanced SQL (for Data Engineer) – Lập trình dữ liệu nâng cao
DWH / ETL – Tổng hợp, chuẩn hóa và Xây dựng kho dữ liệu (Cơ bản)
Data Modeling: Thiết kế mô hình dữ liệu trong doanh nghiệp