12 phương pháp giúp tối ưu truy vấn SQL hiệu quả

27/09/2023 | 1442

Các ứng dụng cơ sở dữ liệu đã trở thành một thành phần quan trọng của nhiều doanh nghiệp trên thế giới dựa vào dữ liệu ngày nay. Với rất nhiều công ty chọn xử lý và lưu trữ dữ liệu của họ trên đám mây, việc tối ưu hóa các truy vấn đã trở nên quan trọng hơn bao giờ hết đối với lợi nhuận của công ty.

Vì vậy, trong bài viết này VietnamWorks inTECH sẽ giới thiệu đến các bạn 12 tip giúp tối ưu hóa việc truy vấn SQL

1. Hạn chế tối đa việc sử dụng ký tự đại diện

Việc sử dụng các ký tự đại diện , chẳng hạn như % và _, trong truy vấn SQL, có thể làm chậm hiệu suất truy vấn. Khi sử dụng ký tự đại diện, cơ sở dữ liệu phải quét toàn bộ bảng để tìm dữ liệu liên quan. Để tối ưu hóa các truy vấn SQL, điều quan trọng là giảm thiểu việc sử dụng các ký tự đại diện và chỉ sử dụng chúng khi thực sự cần thiết.

Ví dụ: hãy xem xét một truy vấn để xác định tất cả các khách hàng trong thành phố có họ bắt đầu bằng chữ cái “P”. Truy vấn sau đây sử dụng ký tự đại diện để tìm tất cả các record khớp với yêu cầu:

SELECT * FROM customers WHERE last_name_city LIKE 'P%';

Truy vấn vẫn sẽ hoạt động nhưng sẽ chậm hơn khi sử dụng index trên cột Last_name_city. Truy vấn có thể được cải thiện bằng cách thêm index vào cột Last_name_city và viết lại như sau:

SELECT * FROM customers WHERE last_name_city >= 'P' AND last_name < 'Q';

2. Tăng hiệu suất truy vấn bằng index

Các truy vấn SQL có thể nhanh hơn bằng cách sử dụng các index, cho phép cơ sở dữ liệu nhanh chóng tìm thấy các mục phù hợp với tiêu chí cụ thể. Lập index là quá trình mapping các giá trị của một hoặc nhiều cột từ một bảng tới một giá trị duy nhất, giúp dễ dàng tìm kiếm các hàng khớp với một giá trị hoặc phạm vi giá trị nhất định.

Để nâng cao các truy vấn SQL, bạn có thể tạo index trên các cột thường được sử dụng trong các mệnh đề WHERE, JOIN và ORDER BY. Tuy nhiên, việc tạo quá nhiều index có thể làm chậm các hoạt động sửa đổi dữ liệu như INSERT, UPDATE, và DELETE.

Cùng tìm hiểu cách cân bằng giữa hiệu suất đọc và hiệu suất ghi khi quyết định cột nào cần lập index và loại index nào nên sử dụng.

Sử dụng truy vấn sau để tìm tất cả các đơn đặt hàng được thực hiện bởi một khách hàng cụ thể:

SELECT * FROM orders WHERE customer_number = 2154;

Vì cơ sở dữ liệu phải tìm kiếm toàn bộ bảng để tìm các mục khớp với mã số khách hàng nên truy vấn này có thể mất nhiều thời gian nếu bảng “orders” chứa nhiều bản ghi. Bạn có thể tạo index trên cột customer_number để cải thiện truy vấn:

CREATE INDEX idx_orders_customer_number ON orders (customer_id);

Bây giờ, khi bạn chạy truy vấn, cơ sở dữ liệu có thể nhanh chóng xác định vị trí các hàng khớp với mã số khách hàng bằng cách sử dụng index, điều này có thể cải thiện hiệu suất truy vấn.

3. Sử dụng kiểu dữ liệu phù hợp

Việc sử dụng các kiểu dữ liệu thích hợp cho các cột trong cơ sở dữ liệu có thể cải thiện đáng kể hiệu năng truy vấn. Ví dụ: sử dụng kiểu dữ liệu số nguyên cho một cột chứa giá trị là số có thể khiến truy vấn chạy nhanh hơn so với sử dụng kiểu dữ liệu văn bản. Việc sử dụng đúng loại dữ liệu cũng đảm bảo tính toàn vẹn của dữ liệu và có thể ngăn ngừa lỗi chuyển đổi dữ liệu.

Ví dụ, chúng ta có một bảng trong đó mỗi hàng thể hiện chi tiết đơn đặt hàng của một cửa hàng bán lẻ. Bảng có các cột cho ID đơn hàng, ID khách hàng, ngày đặt hàng và tổng số đơn đặt hàng.

Cột tổng đơn hàng chứa các giá trị số. Nếu cột tổng thứ tự được lưu dưới dạng kiểu dữ liệu văn bản thì các truy vấn thực hiện tính toán trên tổng thứ tự sẽ chậm hơn nếu cột được lưu dưới dạng kiểu dữ liệu số.

4. Tránh sử dụng truy vấn con

Truy vấn con có thể làm chậm hiệu suất truy vấn, đặc biệt khi được sử dụng trong mệnh đề WHERE hoặc HAVING. Điều quan trọng là tránh các truy vấn con bất cứ khi nào có thể và thay vào đó hãy sử dụng JOINs hoặc các kỹ thuật khác.

Ví dụ: truy vấn tìm tất cả khách hàng đã đặt hàng trong 30 ngày qua. Truy vấn sau đây sử dụng truy vấn con để tìm tất cả ID đơn đặt hàng trong vòng 30 ngày qua:

SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date >= DATEADD(day, -30, GETDATE()));

Truy vấn này sẽ hoạt động nhưng sẽ chậm hơn truy vấn sử dụng JOIN để tìm dữ liệu liên quan. Truy vấn sau đây sử dụng JOIN để tìm tất cả khách hàng đã đặt hàng trong 30 ngày qua:

SELECT DISTINCT c.* FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_date >= DATEADD(day, -30, GETDATE());

Truy vấn này kết hợp bảng khách hàng với bảng đơn hàng và truy xuất tất cả thông tin khách hàng của những người đã đặt hàng trong 30 ngày qua. Truy vấn này sẽ nhanh hơn truy vấn trước vì nó tránh được việc sử dụng truy vấn con.

5. Sử dụng LIMIT hoặc TOP để giới hạn số hàng trả về

Mệnh đề LIMIT hoặc TOP phải được sử dụng để hạn chế số lượng hàng được trả về trong truy vấn SQL. Kết quả là sẽ có ít dữ liệu hơn để xử lý và trả về.

Ví dụ: truy vấn để tìm tất cả khách hàng đã đặt hàng trong 27 ngày qua. Nếu có nhiều khách hàng đã đặt hàng trong 27 ngày qua, truy vấn có thể trả về một số lượng lớn hàng. Điều này có thể được tối ưu hóa bằng LIMIT hoặc TOP. Truy vấn sau đây giới hạn số hàng được trả về là 10:

SELECT TOP 10 * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date >= DATEADD(day, -27, GETDATE()));

Truy vấn này sẽ chỉ trả về 10 hàng trên cùng phù hợp với tiêu chí, điều này sẽ cải thiện hiệu suất truy vấn.

6. Tránh sử dụng SELECT *

Việc sử dụng câu lệnh SELECT * có thể làm chậm hiệu suất truy vấn vì nó trả về tất cả các cột trong một bảng, bao gồm cả những cột không cần thiết cho truy vấn. Để tối ưu hóa các truy vấn SQL, điều quan trọng là chỉ chọn các cột cần thiết cho truy vấn.

Ví dụ: để tìm tất cả khách hàng đã đặt hàng trong 30 ngày qua. Truy vấn sau đây chọn tất cả các cột từ bảng khách hàng:

SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date >= DATEADD(day, -30, GETDATE()));

Để tối ưu hóa truy vấn, câu lệnh SELECT có thể được sửa đổi để chỉ chọn các cột cần thiết:

SELECT customer_id, first_name, last_name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date >= DATEADD(day, -30, GETDATE()));

Truy vấn này sẽ chỉ chọn các cột ID khách hàng, tên và họ, điều này sẽ cải thiện hiệu suất truy vấn.

7. Sử dụng EXISTS thay vì IN

Một giá trị được so sánh với danh sách các giá trị được truy vấn con trả về bằng toán tử IN. Tuy nhiên, việc sử dụng IN có thể làm chậm hiệu suất truy vấn vì nó yêu cầu cơ sở dữ liệu thực hiện quét toàn bộ bảng trên truy vấn con. Để tối ưu hóa các truy vấn SQL, bạn có thể sử dụng toán tử EXISTS thay vì IN.

Ví dụ: tìm tất cả khách hàng đã đặt hàng trong 30 ngày qua:

SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date >= DATEADD(day, -30, GETDATE()));

Truy vấn này sử dụng IN để so sánh ID khách hàng với danh sách ID khách hàng được truy vấn con trả về. Để tối ưu hóa truy vấn, bạn có thể sử dụng EXISTS thay vì IN:

SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.order_date >= DATEADD(day, -30, GETDATE()));

Truy vấn này sử dụng EXISTS để kiểm tra xem có khớp với các hàng trong bảng đơn hàng hay không. Điều này có thể cải thiện hiệu suất truy vấn bằng cách tránh quét toàn bộ bảng.

8. Sử dụng GROUP BY để nhóm dữ liệu

GROUP BY được sử dụng để nhóm các hàng dựa trên một hoặc nhiều cột. Điều này có thể giúp cho việc tóm tắt dữ liệu hoặc thực hiện các chức năng tổng hợp trên các nhóm dữ liệu. Tuy nhiên, việc sử dụng GROUP BY có thể làm chậm hiệu suất truy vấn nếu nó được sử dụng khi không cần thiết. Để tối ưu truy vấn SQL, bạn chỉ nên sử dụng GROUP BY khi thích hợp.

Ví dụ: tìm tổng số đơn đặt hàng của mỗi khách hàng:

SELECT customer_id, COUNT(*) as order_count FROM orders GROUP BY customer_id;

Truy vấn này sử dụng GROUP BY để nhóm các hàng theo ID khách hàng và đếm số lượng đơn đặt hàng của mỗi khách hàng. Để tối ưu hóa truy vấn, bạn có thể sử dụng truy vấn con để truy xuất thông tin khách hàng và kết hợp thông tin đó với bảng “orders”:

SELECT c.customer_id, c.first_name, c.last_name, o.order_count FROM customers c JOIN (SELECT customer_id, COUNT(*) as order_count FROM orders GROUP BY customer_id) o ON c.customer_id = o.customer_id;

Truy vấn này sử dụng truy vấn con để tính toán số lượng đơn đặt hàng của mỗi khách hàng, sau đó kết hợp kết quả với bảng khách hàng để lấy thông tin khách hàng. Điều này tránh việc sử dụng GROUP BY và có thể cải thiện hiệu suất truy vấn.

9. Sử dụng stored procedures

Stored procedures là các câu lệnh SQL được biên dịch sẵn được lưu trữ trong cơ sở dữ liệu. Chúng có thể được gọi từ một ứng dụng hoặc trực tiếp từ truy vấn SQL. Việc sử dụng stored procedure có thể cải thiện hiệu suất truy vấn bằng cách giảm lượng dữ liệu được gửi giữa cơ sở dữ liệu và ứng dụng, đồng thời bằng cách giảm lượng thời gian cần thiết để biên dịch và thực thi các câu lệnh SQL.

10. Tối ưu hóa thiết kế cơ sở dữ liệu

Tối ưu hóa thiết kế cơ sở dữ liệu cũng có thể cải thiện hiệu suất truy vấn. Điều này bao gồm việc đảm bảo rằng các bảng được chuẩn hóa đúng cách và các index được sử dụng hiệu quả. Ngoài ra, điều quan trọng là phải đảm bảo rằng cơ sở dữ liệu được điều chỉnh phù hợp với khối lượng công việc dự kiến và được cấu hình ở mức đồng thời (concurrency) thích hợp.

11. Sử dụng các công cụ tối ưu hóa truy vấn

Có nhiều công cụ tối ưu hóa truy vấn có sẵn có thể giúp xác định các vấn đề về hiệu suất trong truy vấn SQL. Những công cụ này có thể cung cấp các đề xuất để cải thiện hiệu suất truy vấn, chẳng hạn như tạo index, viết lại truy vấn hoặc tối ưu hóa thiết kế cơ sở dữ liệu. Một số công cụ tối ưu hóa truy vấn phổ biến bao gồm Microsoft SQL Server Query Optimizer, Oracle SQL Developer, và MySQL Query Optimizer.

12. Giám sát hiệu suất truy vấn

Giám sát hiệu suất truy vấn là một bước quan trọng trong việc tối ưu hóa các truy vấn SQL. Bằng cách theo dõi hiệu suất truy vấn, có thể xác định các vấn đề về hiệu suất và thực hiện các điều chỉnh phù hợp. Để theo dõi hiệu suất truy vấn, bạn có thể sử dụng một số công cụ có sẵn như SQL Server Profiler, Oracle Enterprise Manager và MySQL Enterprise Monitor.

Lời kết

Tối ưu hóa các truy vấn SQL để có hiệu suất nhanh hơn là một bước quan trọng nhằm đảm bảo các ứng dụng cơ sở dữ liệu chạy hiệu quả. Thông qua bài viết này, chúng ta có thể kết luận những điểm sau

- Lập index là kỹ thuật hiệu quả nhất để tăng hiệu suất của các truy vấn SQL nhưng hãy cân nhắc cẩn thận sự cân bằng giữa hiệu suất đọc và hiệu suất ghi khi quyết định cột nào cần lập index và loại index nào nên sử dụng.

- Tối ưu hóa các truy vấn SQL là một quá trình diễn ra liên tục, yêu cầu giám sát và điều chỉnh thường xuyên để đảm bảo cải thiện hiệu suất.

- Phải giảm thiểu việc sử dụng các hoạt động như JOIN, GROUP BY, IN và truy vấn con để tăng hiệu suất.

- Kiểm tra các truy vấn trên các tập dữ liệu thực tế để đảm bảo rằng việc tối ưu hóa mang lại hiệu quả như mong muốn.

VietnamWorks inTECH