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. Để 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, Datapot sẽ không đi sâu vào phân tích cơ sở dữ liệu, mà là những thủ thuật đơn giản khi bạn tự học SQL có thể đ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 về tối ưu hóa

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 khi tự học SQL

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 ý:Khi tự học SQL, 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.

• Xem thêm: So sánh mệnh đề Where và Having trong SQL

Tip

1: Sử dụng Tên Cột thay vì * trong lệnh SELECT – Tips cần chú ý khi tự học SQL

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:

SELECT * FROM SH.Sales;

Truy vấn đã được cải thiện:

SELECT s.prod_id FROM SH.sales s;

Tip

2: Tránh đưa mệnh đề HAVING trong lệnh SELECT

Tip thứ hai trong khi tự học SQL về tối ưu hóa lệnh truy vấn là 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:

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:

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 – Tự học SQL

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:

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:

SELECT * FROM SH.sales s JOIN SH.customers c ON s.cust_id = c.cust_id WHERE c.cust_marital_status='single';

• Xem thêm: Phân tích RFM trong SQL

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:

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:

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. Do đó khi trong quá trình thực hành và tự học SQL, 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:

SELECT s.* FROM SH.sales s WHERE s.prod_id = 14 OR s.prod_id = 17;

Truy vấn đã cải thiện:

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:

SELECT s.prod_id FROM SH.sales s;

0

Truy vấn đã được cải thiện:

SELECT s.prod_id FROM SH.sales s;

1

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:

SELECT s.prod_id FROM SH.sales s;

2

Truy vấn đã cải thiện:

SELECT s.prod_id FROM SH.sales s;

3

Tip

8:Tránh sử dụng OR trong các mệnh đề thỏa mãn nhiều điều kiện

Trong trường hợp khi bạn tự học SQL, 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

SELECT s.prod_id FROM SH.sales s;

4

Truy vấn đã cải thiên

SELECT s.prod_id FROM SH.sales s;

5

• Xem thêm: SQL cheat sheet: Kim chỉ nam cho người bắt đầu

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

SELECT s.prod_id FROM SH.sales s;

6

Truy vấn đã cải thiện:

SELECT s.prod_id FROM SH.sales s;

7

Tip

10:Loại bỏ các phép toán thừa

Khi làm hoặc tự học SQL, 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: