1. 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.
2. Những điều cần biết
Trước khi đi vào các tips tôi sẽ chia sẻ, các bạn cần nắm được một số khái niệm sau:
- Cơ sở dữ liệu là nơi lưu trữ dữ liệu.
- Những thao tác tới cơ sở dữ liệu gồm truy vấn (
select), thêm (insert), sửa (update), xoá (delete) dữ liệu. Khi dữ liệu nhỏ, tốc độ thực hiện gần như là tức thì, khi dữ liệu đủ lớn thì tốc độ thực hiện trở thành một trở ngại đối với dự án. Đôi khi bạn có thể mất hàng giờ để thực hiện một thao tác bất kỳ tới cơ sở dữ liệu.
Bài toán đặt ra lúc này là làm sao giảm thời gian thực hiện mà vẫn đáp ứng được dữ liệu mong muốn. Mặc dù hiện nay có rất nhiều phương án để giải quyết bài toán này như sử dụng cache, đưa dữ liệu lên RAM…
Trong phạm vi bài viết này, tôi sẽ đưa ra một số lưu ý khi thao tác tới cơ sở dữ liệu nhằm đạt được hiệu năng tốt nhất.
3. Bí kíp
#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:
SELECT * FROM SH.Sales;Truy vấn đã được cải thiện:
SELECT s.prod_id FROM SH.sales s;
#2: Tránh đưa mệnh đề HAVING trong lệnh SELECT
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;
#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:
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';
#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;
#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)
