Tối ưu hóa truy vấn SQL là quá trình viết các câu truy vấn một cách hợp lý để cải thiện hiệu suất của cơ sở dữ liệu. Trong quá trình phát triển, lượng dữ liệu được truy cập và kiểm thử thường khá nhỏ, nên các truy vấn trả về kết quả rất nhanh. Tuy nhiên, vấn đề bắt đầu phát sinh khi dự án chính thức chạy thực tế và dữ liệu bắt đầu đổ về với số lượng lớn. Lúc đó, tốc độ phản hồi của các truy vấn SQL sẽ chậm lại đáng kể, gây ra nhiều vấn đề về hiệu năng.Khi làm việc với dữ liệu lớn, ngay cả một thay đổi nhỏ cũng có thể ảnh hưởng rất lớn đến hiệu suất hệ thống.
Việc tối ưu hiệu suất SQL không hề đơn giản. Đôi khi chỉ cần chỉnh sửa một chút cũng có thể khiến tốc độ truy vấn thay đổi rõ rệt. Dưới đây là một số cách hiệu quả nhất để tối ưu hóa các truy vấn SQL.

Indexing: Đảm bảo tạo chỉ mục phù hợp để truy cập nhanh vào cơ sở dữ liệu.
Select query: Chỉ định rõ các cột trong câu lệnh SELECT thay vì dùng SELECT* để tránh tải dữ liệu không cần thiết.
Running queries: Vòng lặp trong cấu trúc truy vấn làm chậm quá trình xử lý, vì vậy nên tránh sử dụng.
Subqueries: Tránh sử dụng truy vấn con có liên kết (correlated subqueries) vì chúng xử lý từng dòng một, làm chậm truy vấn.
Operators: Tránh sử dụng hàm ở phía bên phải của toán tử so sánh (RHS) vì có thể làm mất hiệu quả của chỉ mục.
Fetching data: Luôn giới hạn số lượng dữ liệu cần truy xuất để giảm tải cho hệ thống.
Loading: Sử dụng bảng tạm thời (temporary table) để xử lý các tập dữ liệu lớn hiệu quả hơn.
Selecting Rows: Sử dụng mệnh đề WHERE thay vì HAVING để lọc dữ liệu ban đầu, giúp truy vấn chạy nhanh hơn.

Các kỹ thuật tối ưu (database) và ví dụ

1. Lập chỉ mục đúng cách (Proper Indexing)

Chỉ mục (Index) là gì?

Chỉ mục là cấu trúc dữ liệu đặc biệt được tạo trên một hoặc nhiều cột trong bảng cơ sở dữ liệu, giúp tăng tốc độ truy xuất dữ liệu mà không cần phải quét toàn bộ bảng.

Lợi ích của chỉ mục

Tăng tốc truy vấn SELECT: Khi câu truy vấn có điều kiện WHERE lọc theo cột được lập chỉ mục, hệ quản trị cơ sở dữ liệu (DBMS) sẽ dùng chỉ mục để nhanh chóng tìm ra các dòng phù hợp, thay vì phải quét toàn bộ bảng.
Tăng tốc sắp xếp (ORDER BY), nhóm (GROUP BY): Nếu các cột được sắp xếp trong chỉ mục, DBMS có thể tận dụng để trả về kết quả nhanh hơn.
Hỗ trợ tạo liên kết (JOIN) hiệu quả: Khi join theo cột lập chỉ mục, DBMS xử lý nhanh hơn.

Cách tạo chỉ mục ví dụ trong SQL:

-- Tạo chỉ mục thông thường trên cột customer_id<br>
CREATE INDEX idx_customer_id ON orders (customer_id); -- Tạo chỉ mục duy nhất trên cột email
CREATE UNIQUE INDEX uq_email ON customers (email);

Khi nào nên đánh index?

Dữ liệu lớn: Nếu bảng có nhiều dữ liệu (hàng triệu bản ghi trở lên), việc đánh index sẽ giúp truy vấn nhanh hơn rất nhiều.
Các cột thường xuyên được dùng trong điều kiện WHERE, JOIN, ORDER BY, GROUP BY cũng rất nên được lập chỉ mục.

Lưu ý khi đánh index

Không đánh quá nhiều index đơn cột: Mỗi index chiếm dung lượng và làm chậm thao tác ghi (INSERT, UPDATE, DELETE), nên cần cân nhắc kỹ lưỡng.
Index đa cột (Composite Index): Nên tạo các index kết hợp 2-4 cột, ưu tiên các cột có khả năng lọc dữ liệu cao ở vị trí đầu tiên trong index.

Ví dụ: Nếu query thường WHERE company_id = ? AND gender = ? thì ta nên tạo index (company_id, gender) thay vì tạo riêng biệt 2 index cho từng cột.
Thứ tự cột trong index quan trọng: Cột có độ phân tán giá trị cao (high cardinality) và lọc nhiều dữ liệu không phù hợp nên được đặt trước.

Các loại index thường gặp trong SQL server

Clustered Index:

Dữ liệu trong bảng được sắp xếp vật lý trên đĩa theo thứ tự của chỉ mục này.
Một bảng chỉ được có 1 clustered index vì thứ tự vật lý chỉ có thể sắp xếp theo một chiều.
Thường tạo trên cột có giá trị duy nhất, có thứ tự, ví dụ ID số, thời gian.
Tối ưu cho các truy vấn tìm kiếm theo phạm vi (range queries) hoặc truy xuất theo thứ tự.

CREATE CLUSTERED INDEX idx_customer_id ON Customers(customer_id);

Non-Clustered Index:

Chỉ là cấu trúc dữ liệu riêng biệt tham chiếu đến vị trí dữ liệu trong bảng.
Một bảng có thể có nhiều non-clustered index.
Tối ưu các truy vấn tìm kiếm theo các cột không phải khóa chính, hoặc các truy vấn WHERE phức tạp.

CREATE NONCLUSTERED INDEX idx_lastname ON Customers(last_name);

Unique Index

Đảm bảo các giá trị trong cột hoặc nhóm cột được lập chỉ mục là duy nhất (giống như ràng buộc UNIQUE).
Khi cần đảm bảo không có giá trị trùng lặp trên cột hoặc tổ hợp cột.

CREATE UNIQUE NONCLUSTERED INDEX idx_unique_email ON Customers(email);

Full-Text Index

Dùng để tìm kiếm văn bản phức tạp trong các cột kiểu text, ntext, varchar, nvarchar,...
Hỗ trợ các phép tìm kiếm như CONTAINS, FREETEXT.
Tối ưu các truy vấn tìm kiếm dữ liệu văn bản trong bảng lớn.

CREATE FULLTEXT INDEX ON TableName(ColumnName)
KEY INDEX PK_TableName
WITH STOPLIST = SYSTEM;

2. Sử dụng SELECT <column_list> thay vì SELECT *

Tại sao nên tránh SELECT *?

Tăng tải cho hệ thống: SELECT* sẽ trả về toàn bộ các cột trong bảng, bao gồm cả những cột không cần thiết. Nếu bảng có nhiều cột hoặc dữ liệu lớn, đặc biệt là các cột chứa dữ liệu kiểu text, hình ảnh, binary,... thì việc này gây tăng tải truyền tải và xử lý trên cả database và ứng dụng.
Làm giảm hiệu suất: Việc truyền thừa dữ liệu kéo dài thời gian thực thi và có thể gây nghẽn băng thông mạng.
Khó bảo trì và dễ xảy ra lỗi: Khi cấu trúc bảng thay đổi (thêm cột mới), ứng dụng dùng SELECT * có thể bị ảnh hưởng không lường trước được. Việc chỉ định rõ cột giúp kiểm soát dữ liệu trả về tốt hơn.
Khó tối ưu truy vấn: Các công cụ tối ưu hóa truy vấn (query optimizer) cũng sẽ có dữ liệu đầy đủ hơn để chọn cách thực thi hiệu quả nhất nếu biết rõ cột cần truy vấn.

3. Tránh chạy truy vấn SQL trong vòng lặp

Vấn đề khi chạy truy vấn trong vòng lặp:

Hiệu năng kém: Khi bạn chạy một truy vấn SQL lặp lại nhiều lần (ví dụ 1000 lần), hệ quản trị cơ sở dữ liệu phải xử lý từng truy vấn riêng lẻ, tạo kết nối, parse, lập kế hoạch thực thi và thực thi từng câu, gây tốn tài nguyên và làm chậm toàn bộ quá trình.
Tăng độ trễ: Thời gian tổng thực thi tăng lên rất nhiều so với việc chạy một truy vấn duy nhất xử lý nhiều bản ghi.
Tăng tải mạng: Mỗi lần gửi truy vấn tốn chi phí truyền thông giữa ứng dụng và database.

Giải pháp: Sử dụng bulk insert / bulk update (thao tác hàng loạt)

Bulk Insert: Thay vì gửi từng dòng dữ liệu một, ta gửi nhiều dòng trong một câu truy vấn duy nhất bằng cách sử dụng cú pháp VALUES với nhiều bộ giá trị.

Ví dụ minh họa:

Không hiệu quả (chạy truy vấn trong vòng lặp):

for ($i = 0; $i < 1000; $i++) { $query = "INSERT INTO TBL (A, B, C) VALUES ($a[$i], $b[$i], $c[$i])"; $mysqli->query($query); }

Query được gửi và thực thi 1000 lần, rất chậm và tốn kém.

Hiệu quả (bulk insert):

INSERT INTO TBL (A, B, C) VALUES (1, 2, 3),
(4, 5, 6),
(7, 8, 9),
... -- nhiều dòng cùng lúc
;

Chỉ gửi một câu truy vấn duy nhất, giúp giảm đáng kể chi phí xử lý và thời gian thực thi.

Một số lưu ý khi sử dụng bulk insert / bulk update:

Giới hạn kích thước câu truy vấn: Một số DBMS có giới hạn về kích thước tối đa của câu truy vấn (ví dụ MySQL mặc định khoảng 4MB). Nếu bạn chèn rất nhiều record, nên chia nhỏ batch.
Sử dụng transaction: Khi chèn hoặc cập nhật hàng loạt, dùng transaction để đảm bảo tính toàn vẹn dữ liệu.
Bulk Update: Với cập nhật hàng loạt nhiều dòng cùng một lúc phức tạp hơn, có thể dùng các kỹ thuật như UPDATE ... FROM ... JOIN, hoặc sử dụng tạm bảng (temporary table) để cập nhật dữ liệu hiệu quả.

4. Tránh sử dụng correlated subqueries (subquery phụ thuộc) — thay bằng JOIN

Vấn đề với correlated subqueries

Correlated subquery là subquery có sử dụng tham chiếu đến cột của truy vấn bên ngoài (outer query). SQL Server (hay các DBMS khác) sẽ phải thực thi subquery cho mỗi hàng trả về từ truy vấn bên ngoài. Điều này dẫn đến việc quét bảng phụ nhiều lần, làm giảm hiệu suất rất nhiều, đặc biệt với bảng dữ liệu lớn.

Ví dụ không hiệu quả (correlated subquery):

SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName
FROM Customer c;

Với mỗi dòng trong Customer, DBMS phải thực thi subquery truy vấn bảng Company lấy tên công ty tương ứng. Nếu Customer có 10,000 bản ghi, subquery cũng chạy 10,000 lần.

Cách tối ưu: Dùng JOIN

SELECT c.Name, c.City, co.CompanyName
FROM Customer c
LEFT JOIN Company co ON co.ID = c.CompanyID;

Dùng JOIN giúp lấy dữ liệu từ hai bảng trong một truy vấn duy nhất. DBMS sẽ tối ưu cách nối 2 bảng, tránh việc lặp lại truy vấn phụ. Truy vấn chạy nhanh hơn, tận dụng chỉ mục và các thuật toán join tối ưu.
Ngoài JOIN, bạn có thể dùng các kỹ thuật khác như CTE (Common Table Expressions), window functions để tối ưu query phức tạp.

5. Tránh dùng hàm trên cột trong điều kiện WHERE (hoặc bên phải phép toán)

Ví dụ câu truy vấn sau:

SELECT * FROM Customer
WHERE YEAR(AccountCreatedOn) = 2005 AND MONTH(AccountCreatedOn) = 6;

Cột AccountCreatedOn có thể đã được đánh index. Tuy nhiên, khi bạn gọi hàm YEAR() và MONTH() trên cột, giá trị thành phần của cột bị tính toán động theo từng dòng.
Kết quả là chỉ mục sẽ không được sử dụng vì DBMS không thể tận dụng chỉ mục trên giá trị gốc, mà nó phải quét toàn bộ bảng. Cách làm hiệu quả Thay vì gọi hàm trích xuất năm, tháng, bạn nên viết điều kiện dưới dạng so sánh phạm vi giá trị, tức là:

SELECT * FROM Customer
WHERE AccountCreatedOn BETWEEN '2005-06-01' AND '2005-06-30 23:59:59';

Điều kiện trên cho phép DBMS sử dụng được chỉ mục trên cột AccountCreatedOn. Truy vấn nhanh hơn, nhất là bảng chứa dữ liệu lớn.
Tránh làm biến đổi cột bằng hàm trong điều kiện lọc, thay vào đó tính toán phạm vi ở ngoài (ứng dụng hoặc trong truy vấn) rồi dùng so sánh trực tiếp.

6. Luôn giới hạn lượng dữ liệu lấy ra và nhắm đến kết quả chính xác

Lấy càng ít dữ liệu càng tốt, vì truyền ít dữ liệu giúp giảm tải mạng, giảm thời gian xử lý client và tăng tốc độ trả kết quả.
Lọc dữ liệu càng nhiều càng tốt ngay tại server thay vì lấy toàn bộ dữ liệu rồi lọc ở client.
Áp dụng:
- Chỉ chọn các cột cần thiết (không dùng SELECT *).
- Sử dụng điều kiện WHERE chặt chẽ, chính xác.
- Sử dụng LIMIT hoặc TOP để giới hạn số dòng trả về khi cần.
Ví dụ:

SELECT first_name, last_name FROM employees WHERE city = 'Hanoi' LIMIT 100;

7. Xóa chỉ mục trước khi tải dữ liệu lượng lớn (bulk load)

Khi tải hàng nghìn hoặc triệu dòng dữ liệu, các chỉ mục trên bảng đích sẽ làm cho việc chèn dữ liệu chậm hơn rất nhiều do chỉ mục phải được cập nhật liên tục.
Giải pháp:
1. Tạo bảng tạm (temporary table) không có chỉ mục để load dữ liệu nhanh.
2. DROP hoặc DISABLE các chỉ mục trên bảng chính trước khi chèn hàng loạt dữ liệu.
3. Di chuyển dữ liệu từ bảng tạm vào bảng chính (việc copy trên bảng có ít chỉ số hoặc không có chỉ số nhanh hơn).
4. Tạo lại hoặc bật lại các chỉ mục trên bảng chính sau khi chèn xong.

8. Sử dụng WHERE thay vì HAVING khi có thể

WHERE lọc dữ liệu trước khi bước nhóm (GROUP BY) diễn ra.
HAVING lọc dữ liệu sau khi nhóm đã được tạo, tức là làm việc với kết quả đã tổng hợp.
Do đó, nếu có thể lọc dữ liệu trước khi nhóm, bạn nên dùng WHERE để:
- Giảm số lượng bản ghi đưa vào quá trình nhóm.
- Giúp truy vấn nhanh hơn rất nhiều.
Ví dụ:

-- Nên dùng WHERE để lọc trước
SELECT department, COUNT(*)
FROM employees
WHERE salary > 1000
GROUP BY department;
-- Dùng HAVING chỉ để lọc trên kết quả nhóm, như tổng lương > 10000
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 10000;

Chúc bạn tối ưu thành công và đạt hiệu năng cao trong mọi truy vấn SQL!

Tối ưu hóa truy vấn SQL