Tối ưu hóa câu lệnh SQL

NỘI DUNG BÀI VIẾT

Lập trình viên chúng ta mỗi ngày phải làm việc rẩt nhiều với các câu lệnh SQL do vậy hiểu được cách để tối ưu câu lệnh SQL cực kỳ quan trọng. Việc tối ưu câu lệnh SQL sẽ làm tăng hiệu năng của ứng dụng, giảm thời gian chờ đợi của người dùng cuối.

Sử dụng index

Index là một trong những yếu tố quan trọng nhất góp phần vào việc nâng cao hiệu suất của cơ sở dữ liệu. Index trong SQL tăng tốc độ của quá trình truy vấn dữ liệu bằng cách cung cấp phương pháp truy xuất nhanh chóng tới các dòng trong các bảng, tương tự như cách mà mục lục của một cuốn sách giúp bạn nhanh chóng tìm đến một trang bất kỳ mà bạn muốn trong cuốn sách đó.

Nên đánh index ở các cột sử dụng where, order by, group by.

Mặc dù sử dụng INDEX nhằm mục đích để nâng cao hiệu suất của Database, nhưng đôi khi bạn nên tránh dùng chúng trong 1 số trường hợp sau:

Không nên sử dụng trong các bảng nhỏ, ít bản ghi.
Không nên sử dụng Index trong bảng mà các hoạt động UPDATE, INSERT xảy ra thường xuyên với tần suất lớn.
Không nên sử dụng cho các cột mà chứa một số lượng lớn giá trị NULL.
Không nên dùng Index cho các cột mà thường xuyên bị sửa đổi.

Các nguyên nhân gây chậm truy vấn SQL

Không/thiếu sử dụng các lợi ích của Indexes.
Trả về các dữ liệu không cần thiết.
Locks or deadlocks bị cấm.
Các câu truy vấn được viết nghèo nàn.
Không/thiếu tận dụng được I/O striping.
Thiếu bộ nhớ.

Các phương pháp cải tiến

Thu hẹp giá trị trả về

việc thu hẹp giới hạn của giá trị trả về sẽ tiết kiệm bộ nhớ, I/O striping, dung lượng khi truyền từ server về client.

 SELECT * FROM table_1 LEFTJOIN table_2 WHERE table_1.id = table_2.gid;

sử dụng select * sẽ khiến SQL quét toàn bộ table,trả về dữ liệu trùng lặp tiêu tốn I/O.Truy vấn dưới đây cùng mục đích nhưng nhanh hơn.

 SELECT table_1.id,table_2.username,table_2.lucky FROM table_1 LEFTJOIN table_2 WHERE table_1.id = table_2.gid;

Không hạn chế Index

toán tử phủ định : Index không thể thực hiện với toán tử phủ định do đó các toán tử phía dưới sẽ làm chậm câu lệnh hãy hạn chế sử dụng.

"IS NULL", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE",
Code language: JavaScript (javascript)

toán tử so sánh 2 lần

SELECT userid, username FROM user WHERE user_amount <=3000

câu lệnh này sẽ khiến SQL so sánh 2 lần : user_amount< 3000 OR user_amount=3000 do đó làm chậm truy vấn.Hãy dùng câu lệnh dưới đây cho tình huống tương tự.

SELECT userid, username FROM user WHERE user_amount < 3001

Sử dụng Like không hợp lý sẽ làm chậm truy vấn:

 SELECT lname, fname, address FROM Customers WHERE fname LIKE ‘%V%’

câu lệnh này so khớp phần đầu của %V vì thế không thể dùng index mặc dù điều kiện tìm kiếm rõ hơn nhưng sẽ chậm hơn câu lệnh dưới đây.

 SELECT lname, fname, address FROM Customers WHERE fname LIKE ‘V%’

hạn chế sử dụng function lên column

 SELECT member_number, first_name, last_name
FROM members
WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21

sau khi tính toán bởi hàm DATEDIFF thì SQL không thể dùng Index cho column datofbirth　được nữa.sử dụng câu truy vấn như dưới đây sẽ nhanh hơn.

SELECT member_number, first_name, last_name
FROM members
WHERE dateofbirth < DATEADD(yy,-21,GETDATE())

không để SQL thực hiện thao tác thừa

UNION, UNION ALL mặc dù cả 2 câu truy vấn đều quét table như nhau nhưng ở câu truy vấn 1 tiêu tốn phần lớn thời gian cho thao tác 「Distinct sort」để loại bỏ những bản ghi trùng nhau (mặc dù không hề có những records như vậy). đây là 1 ví dụ khác :

 SELECT userId, userName, userPhone
FROM User
WHERE dept = 'ruby' or rank = '5' or division = '2'
Code language: JavaScript (javascript)

 SELECT userId, userName, userPhone FROM User
WHERE dept = 'ruby' UNION ALL
SELECT userId, userName, userPhone FROM User
WHERE rank = '5' UNION ALL
SELECT userId, userName, userPhone FROM User
WHERE division = '2'
Code language: JavaScript (javascript)

DISTINCT, ORDER BY Đừng sử dụng DISTINCT và ORDER BY trong câu lệnh SELECT trừ khi thật sự cần thiết, DISTINCT sẽ kiểm tra và loại bỏ các bản ghi trùng lặp ORDER BY sẽ sort các records hầu hết các trường hợp 2 thao tác này chiếm phần lớn thời gian truy vấn.
COUNT() vs IF EXISTS Khi xác định sự tồn tại của records trong bảng nên dùng IF EXISTS thay cho COUNT() hoặc COUNT(DISTINCT).

IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')
Code language: JavaScript (javascript)

Hàm COUNT sẽ tìm cách lấy tất cả phần tử, so sánh, đếm nên sẽ tốn tài nguyên và nhiều thao tác hơn.Hãy dùng

IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')Code language: JavaScript (javascript)

Tránh truy vấn tương quan (correlated subquery)

Truy vấn tương quan là dạng truy vấn con trong đó có sử dụng các giá trị từ truy vấn cha.

SELECT user_id, last_name FROM users 
    WHERE EXISTS (SELECT * FROM donationuser WHERE donationuser.user_id = users.user_id);

Sử dụng Stored Procedure thay cho những cây truy vấn phức tạp

Stored Procedure thực thi mã nhanh hơn và giảm tải băng thông.

Thực thi nhanh hơn: Stored Procedure sẽ được biên dịch và lưu vào bộ nhớ khi được tạo ra. Điều đó có nghĩa rằng nó sẽ thực thi nhanh hơn so với việc gửi từng đoạn lệnh SQL. Vì nếu bạn gửi từng đoạn lệnh nhiều lần thì SQL cũng sẽ phải biên dịch lại nhiều lần, rất mất thời gian so với việc biên dịch sẵn.

Giảm tải băng thông: Nếu bạn gửi nhiều câu lệnh SQL thông qua network đến SQL sẽ ảnh hưởng tới hiệu suất đường truyền. Thay vì gửi nhiều lần thì bạn có thể gom các câu lệnh SQL vào 1 Stored Procedure và chỉ phải gọi đến 1 lần duy nhất qua network.

Thêm nữa viết Stored Procedure sẽ thuận lợi cho việc phân quyền và bảo mật tốt hơn

Tổng kết

Trên đây là một số cách tối ưu hóa câu lệnh SQL, mong sẽ giúp ích cho bạn.

Xem thêm: