Tối ưu hóa truy vấn SQL: 15 kỹ thuật để cải thiện hiệu năng

6. Tối ưu mệnh đề WHERE

Mệnh đề WHERE rất quan trọng trong truy vấn SQL vì cho phép lọc dữ liệu theo điều kiện cụ thể, đảm bảo chỉ trả về các bản ghi liên quan. Nó cải thiện hiệu quả truy vấn bằng cách giảm lượng dữ liệu được xử lý, điều này rất quan trọng khi làm việc với tập dữ liệu lớn. 

Vì vậy, một mệnh đề WHERE đúng đắn có thể là đồng minh mạnh mẽ khi tối ưu hiệu năng truy vấn SQL. Dưới đây là một số cách tận dụng mệnh đề này:

• Thêm điều kiện lọc phù hợp càng sớm càng tốt. Đôi khi, có mệnh đề WHERE là tốt nhưng chưa đủ. Cần lưu ý vị trí đặt mệnh đề. Loại bỏ càng nhiều hàng càng sớm trong mệnh đề WHERE có thể giúp tối ưu truy vấn.
• Tránh dùng hàm trên các cột trong mệnh đề WHERE. Khi áp dụng hàm lên một cột, cơ sở dữ liệu phải áp dụng hàm đó cho mọi hàng trước khi có thể lọc kết quả. Điều này ngăn cản việc sử dụng chỉ mục hiệu quả.

Ví dụ, thay vì: 

SELECT * 
FROM employees WHERE 
YEAR(hire_date) = 2020;

Chúng ta nên dùng: 

SELECT * 
FROM employees 
WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';

• Dùng toán tử phù hợp. Hãy chọn các toán tử hiệu quả nhất phù hợp nhu cầu. Ví dụ, = thường nhanh hơn LIKE, và sử dụng khoảng ngày cụ thể nhanh hơn dùng các hàm như MONTH(order_date).

Vì vậy, ví dụ, thay vì thực hiện truy vấn này:

SELECT * 
FROM orders 
WHERE MONTH(order_date) = 12 AND YEAR(order_date) = 2023;

Chúng ta có thể thực hiện như sau: 

SELECT * 
FROM orders 
WHERE order_date >= '2023-12-01' AND order_date < '2024-01-01';

7. Tối ưu truy vấn con (subquery)

Trong một số trường hợp, khi viết truy vấn, chúng ta muốn linh hoạt lọc, tổng hợp hoặc join dữ liệu ngay trong cùng một truy vấn thay vì chạy nhiều truy vấn riêng lẻ. 

Trong những trường hợp đó, chúng ta có thể dùng subquery. Subquery trong SQL là các truy vấn lồng bên trong một truy vấn khác, thường nằm trong các câu lệnh SELECT, INSERT, UPDATE hoặc DELETE. 

Subquery có thể mạnh và nhanh, nhưng cũng có thể gây vấn đề hiệu năng nếu không dùng cẩn thận. Nguyên tắc là nên giảm thiểu việc dùng subquery và tuân theo một số thực hành tốt:

• Thay thế subquery bằng join khi có thể. Join nhìn chung nhanh và hiệu quả hơn subquery.
• Thay vào đó, dùng common table expressions (CTE).  CTE giúp tách mã của chúng ta thành vài truy vấn nhỏ thay vì một truy vấn lớn, dễ đọc hơn nhiều.

WITH SalesCTE AS ( 
             SELECT salesperson_id, SUM(sales_amount) AS total_sales 
             FROM sales GROUP BY salesperson_id ) 

SELECT salesperson_id, total_sales 
FROM SalesCTE WHERE total_sales > 5000;

• Dùng subquery không tương quan (uncorrelated). Subquery không tương quan độc lập với truy vấn ngoài và có thể được thực thi một lần, trong khi subquery tương quan sẽ chạy cho mỗi hàng của truy vấn ngoài.

8. Dùng EXISTS thay cho IN đối với subquery

Khi làm việc với subquery, chúng ta thường cần kiểm tra một giá trị có tồn tại trong một tập kết quả không. Ta có thể làm điều này bằng IN hoặc EXISTS, nhưng EXISTS thường hiệu quả hơn, đặc biệt với tập dữ liệu lớn.

Mệnh đề IN sẽ đọc toàn bộ kết quả của subquery vào bộ nhớ trước khi so sánh. Ngược lại, EXISTS dừng xử lý subquery ngay khi tìm thấy một khớp. 

Ví dụ cách sử dụng mệnh đề này:

SELECT * 
FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id AND c.country = 'USA');

9. Hạn chế sử dụng DISTINCT

Hãy tưởng tượng chúng ta đang làm một phân tích để gửi ưu đãi khuyến mãi tới khách hàng từ các thành phố duy nhất. Cơ sở dữ liệu có nhiều đơn hàng từ cùng khách hàng. Điều đầu tiên nảy ra trong đầu là dùng mệnh đề DISTINCT.

Hàm này hữu ích trong một số trường hợp nhưng có thể tốn tài nguyên, đặc biệt với tập dữ liệu lớn. Có vài lựa chọn thay thế cho DISTINCT:

• Xác định và loại bỏ dữ liệu trùng lặp trong quy trình làm sạch dữ liệu. Điều này ngăn trùng lặp xâm nhập vào cơ sở dữ liệu ngay từ đầu.
• Dùng GROUP BY thay cho DISTINCT khi có thể. GROUP BY có thể hiệu quả hơn, đặc biệt khi kết hợp với các hàm tổng hợp. 

Vì vậy, thay vì thực hiện:

SELECT DISTINCT city FROM customers;

Chúng ta có thể dùng:

SELECT city FROM customers GROUP BY city;

• Dùng hàm cửa sổ. Các hàm như ROW_NUMBER có thể giúp xác định và loại bỏ trùng lặp mà không cần dùng DISTINCT.

10. Tận dụng các tính năng đặc thù của hệ quản trị

Khi làm việc với dữ liệu, chúng ta tương tác bằng SQL thông qua Hệ quản trị cơ sở dữ liệu (DBMS). DBMS xử lý các lệnh SQL, quản lý cơ sở dữ liệu và đảm bảo tính toàn vẹn, bảo mật dữ liệu. Các hệ thống cơ sở dữ liệu khác nhau cung cấp những tính năng độc đáo có thể giúp tối ưu truy vấn. 

Database hint là các hướng dẫn đặc biệt chúng ta có thể thêm vào truy vấn để thực thi hiệu quả hơn. Chúng hữu ích nhưng cần sử dụng thận trọng. 

Ví dụ, trong MySQL, hint USE INDEX có thể buộc sử dụng một chỉ mục cụ thể:

 SELECT * FROM employees USE INDEX (idx_salary) WHERE salary > 50000;

Trong SQL Server, hint OPTION (LOOP JOIN) chỉ định phương thức join: 

SELECT * 
FROM orders 
INNER JOIN customers ON orders.customer_id = customers.id OPTION (LOOP JOIN); 

Các hint này ghi đè tối ưu hóa truy vấn mặc định, cải thiện hiệu năng trong các kịch bản cụ thể.

Mặt khác, partitioning và sharding là hai kỹ thuật để phân phối dữ liệu trên đám mây. 

• Với partitioning, chúng ta chia một bảng lớn thành nhiều bảng nhỏ, mỗi bảng có khóa phân vùng riêng. Khóa phân vùng thường dựa trên dấu thời gian tạo hàng hoặc giá trị số nguyên. Khi thực thi truy vấn trên bảng này, máy chủ sẽ tự động định tuyến đến bảng phân vùng phù hợp với truy vấn. 
• Sharding khá giống, ngoại trừ việc thay vì tách một bảng lớn thành các bảng nhỏ, nó tách một cơ sở dữ liệu lớn thành các cơ sở dữ liệu nhỏ hơn. Mỗi cơ sở dữ liệu này nằm trên một máy chủ khác nhau. Thay vì khóa phân vùng, khóa sharding định tuyến truy vấn chạy trên cơ sở dữ liệu thích hợp. Sharding tăng tốc độ xử lý vì tải được chia đều cho các máy chủ khác nhau. 

11. Giám sát và tối ưu thống kê của cơ sở dữ liệu

Giữ cho thống kê của cơ sở dữ liệu luôn cập nhật là quan trọng để bộ tối ưu truy vấn có thể đưa ra quyết định chính xác, sáng suốt về cách thực thi truy vấn hiệu quả nhất. 

Thống kê mô tả phân bố dữ liệu trong một bảng (ví dụ: số lượng hàng, tần suất các giá trị và độ phân tán của giá trị qua các cột), và bộ tối ưu dựa vào thông tin này để ước lượng chi phí thực thi truy vấn. Nếu thống kê lỗi thời, bộ tối ưu có thể chọn kế hoạch thực thi kém hiệu quả, như dùng sai chỉ mục hoặc chọn quét toàn bảng thay vì quét theo chỉ mục hiệu quả hơn, dẫn đến hiệu năng truy vấn kém.

Nhiều cơ sở dữ liệu hỗ trợ cập nhật tự động để duy trì thống kê chính xác. Chẳng hạn, trong SQL Server, cấu hình mặc định sẽ tự động cập nhật thống kê khi có lượng dữ liệu thay đổi đáng kể. Tương tự, PostgreSQL có tính năng auto-analyze, cập nhật thống kê sau khi vượt một ngưỡng thay đổi dữ liệu nhất định. 

Tuy nhiên, chúng ta có thể cập nhật thủ công trong trường hợp cập nhật tự động chưa đủ hoặc cần can thiệp thủ công. Trong SQL Server, có thể dùng lệnh UPDATE STATISTICS để làm mới thống kê cho một bảng hoặc chỉ mục cụ thể, trong khi ở PostgreSQL, có thể chạy lệnh ANALYZE để cập nhật thống kê cho một hoặc nhiều bảng. 

-- Update statistics for all tables in the current database
ANALYZE;

-- Update statistics for a specific table
ANALYZE my_table;

12. Sử dụng stored procedure

Stored procedure là tập lệnh SQL được lưu trong cơ sở dữ liệu để chúng ta không phải viết đi viết lại cùng một SQL. Có thể hình dung nó như một kịch bản tái sử dụng. 

Khi cần thực hiện một tác vụ nhất định, như cập nhật bản ghi hoặc tính toán giá trị, chúng ta chỉ cần gọi stored procedure. Nó có thể nhận đầu vào, thực hiện công việc như truy vấn hoặc sửa đổi dữ liệu và thậm chí trả về kết quả. Stored procedure giúp tăng tốc vì SQL được biên dịch sẵn, khiến mã của bạn gọn gàng và dễ quản lý hơn. 

Chúng ta có thể tạo stored procedure trong PostgreSQL như sau:

CREATE OR REPLACE PROCEDURE insert_employee(
    emp_id INT,
    emp_first_name VARCHAR,
    emp_last_name VARCHAR
)
LANGUAGE plpgsql
AS $
BEGIN
    -- Insert a new employee into the employees table
    INSERT INTO employees (employee_id, first_name, last_name)
    VALUES (emp_id, emp_first_name, emp_last_name);
END;
$;

-- call the procedure
CALL insert_employee(101, 'John', 'Doe');

13. Tránh sắp xếp và nhóm không cần thiết

Là người làm dữ liệu, chúng ta thích dữ liệu được sắp xếp và nhóm để rút ra thông tin dễ dàng hơn. Ta thường dùng ORDER BY và GROUP BY trong các truy vấn SQL.

Tuy nhiên, cả hai mệnh đề này đều tốn tài nguyên tính toán, đặc biệt khi xử lý tập dữ liệu lớn. Khi sắp xếp hoặc tổng hợp dữ liệu, bộ máy cơ sở dữ liệu thường phải quét toàn bộ dữ liệu rồi sắp xếp, xác định nhóm và/hoặc áp dụng các hàm tổng hợp, thường dùng các thuật toán tiêu tốn tài nguyên. 

Để tối ưu truy vấn, chúng ta có thể làm theo một số gợi ý:

• Giảm thiểu sắp xếp. Chỉ dùng ORDER BY khi cần thiết. Nếu việc sắp xếp không quan trọng, loại bỏ mệnh đề này có thể giúp giảm đáng kể thời gian xử lý. 
• Dùng chỉ mục. Khi có thể, hãy đảm bảo các cột tham gia ORDER BY và GROUP BY được lập chỉ mục. 
• Đẩy việc sắp xếp lên tầng ứng dụng. Nếu có thể, hãy xử lý sắp xếp ở tầng ứng dụng thay vì trong cơ sở dữ liệu. 
• Tiền tổng hợp dữ liệu. Với các truy vấn phức tạp liên quan đến GROUP BY, chúng ta có thể tổng hợp dữ liệu từ sớm hoặc trong materialized view, để cơ sở dữ liệu không phải tính đi tính lại cùng các phép tổng hợp.

14. Dùng UNION ALL thay vì UNION

Khi muốn kết hợp kết quả từ nhiều truy vấn thành một danh sách, chúng ta có thể dùng các mệnh đề UNION và UNION ALL. Cả hai đều kết hợp kết quả của hai hoặc nhiều câu lệnh SELECT khi chúng có cùng tên cột. Tuy nhiên, chúng không giống nhau và sự khác biệt khiến chúng phù hợp cho các trường hợp sử dụng khác nhau.

Mệnh đề UNION loại bỏ các hàng trùng lặp, điều này đòi hỏi thời gian xử lý nhiều hơn. 

Hình: Union trong SQL. Nguồn ảnh: DataCamp SQL-Join cheat sheet.

Ngược lại, UNION ALL kết hợp kết quả nhưng giữ tất cả các hàng, bao gồm cả trùng lặp. Vì vậy, nếu không cần loại bỏ trùng lặp, chúng ta nên dùng UNION ALL để có hiệu năng tốt hơn.

Hình: UNION ALL trong SQL. Nguồn ảnh: DataCamp SQL-Join cheat sheet.

-- Potentially slower
SELECT product_id FROM products WHERE category = 'Electronics'
UNION
SELECT product_id FROM products WHERE category = 'Books';

-- Potentially faster
SELECT product_id FROM products WHERE category = 'Electronics'
UNION ALL
SELECT product_id FROM products WHERE category = 'Books';

15. Chia nhỏ các truy vấn phức tạp

Làm việc với các tập dữ liệu lớn đồng nghĩa với việc chúng ta sẽ thường xuyên gặp các truy vấn phức tạp khó hiểu và khó tối ưu. Chúng ta có thể xử lý chúng bằng cách chia nhỏ thành các truy vấn nhỏ, đơn giản hơn. Bằng cách này, ta dễ dàng xác định các nút thắt hiệu năng và áp dụng kỹ thuật tối ưu.

Một trong những chiến lược được dùng thường xuyên để chia nhỏ truy vấn là materialized view. Đây là kết quả truy vấn được tính sẵn và lưu trữ, có thể truy cập nhanh thay vì phải tính lại mỗi lần tham chiếu. Khi dữ liệu nền thay đổi, materialized view phải được làm mới thủ công hoặc tự động.

Ví dụ cách tạo và truy vấn một materialized view:

-- Create a materialized view
CREATE MATERIALIZED VIEW daily_sales AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM order_items
GROUP BY product_id;

-- Query the materialized view
SELECT * FROM daily_sales;

Kết luận

Trong bài viết này, chúng ta đã khám phá các chiến lược và thực hành tốt để tối ưu truy vấn SQL, từ lập chỉ mục và join đến subquery và các tính năng đặc thù của hệ quản trị. Áp dụng những kỹ thuật này, bạn có thể cải thiện đáng kể hiệu năng truy vấn và giúp cơ sở dữ liệu vận hành hiệu quả hơn.

Hãy nhớ rằng tối ưu truy vấn SQL là một quá trình liên tục. Khi dữ liệu tăng và ứng dụng phát triển, bạn sẽ cần liên tục giám sát và tối ưu các truy vấn để đảm bảo chúng chạy ở hiệu năng tối ưu.

Để nâng cao hiểu biết về SQL, chúng tôi khuyến khích bạn khám phá các tài nguyên sau trên DataCamp:

• SQL Basics Cheat Sheet
• Improving Query Performance in SQL Server
• Improving Query Performance in PostgreSQL