Nguyên nhân và Phương pháp tối ưu truy vấn SQL

Dẫn nhập

Qua quá trình làm việc, cũng như tìm hiểu trên mạng, có một số kinh nghiệm về nguyên nhân và phương pháp tối ưu truy vấn SQL Server trong lập trình được sưu tầm, nó sẽ được nói đến trong bài viết này.

Để đọc hiểu bài này tốt nhất các bạn nên có kiến thức cơ bản về cơ sở dữ liệu và SQL Server.

Nguyên nhân và các phương pháp tối ưu truy vấn SQL Server

Những câu truy vấn chậm có thể phát sinh từ rất nhiều nguyên nhân bao gồm:

• Không/thiếu sử dụng các lợi ích của Indexes.
• Không/thiếu tận dụng được I/O striping.
• Các thống kê lỗi thời hoặc thiếu các thống kê hữu ích.
• Thiếu bộ nhớ vật lý.
• Kết nối mạng chậm.
• Các câu truy vấn Transact-SQL chuyển số lượng dữ liệu lớn từ server đến client.
• Locks or deadlocks bị cấm.
• Thực hiện các câu truy vấn OLTP and OLAP trên cùng 1 máy server.
• Trả về các dữ liệu không cần thiết.
• Các câu truy vấn được viết nghèo nàn.

Nên sử dụng mệnh đề WHERE trong câu lệnh SELECT

Bạn nên luôn luôn thêm vào mệnh đề WHERE trong câu lệnh SELECT để thu hẹp số dòng trả về. Nếu bạn không sử dụng mệnh đề WHERE trong câu SELECT thì SQL Server sẽ thực hiện việc quét toàn table và trả về mọi bản ghi trong table. Điều này sẽ gây lãng phí tài nguyên I/O khi trả về các dòng không cần thiết.

Không cần lo lắng khi sử dụng in-line hay block comments

Bạn không phải lo lắng khi có sử dụng in-line hay block comments trong code vì chúng sẽ không ảnh hưởng tới việc xử lý hiệu năng trong ứng dụng của bạn. Ngoài ra, những block comments còn làm rỏ hơn các đoạn code của bạn.

Tránh sử dụng SQL Server Cursors

Nếu có thể, bạn nên tránh sử dụng SQL Server Cursors. Chúng thường sử dụng nhiều tài nguyên SQL Server, giảm hiệu năng và tính khả mở của ứng dụng bạn. Nếu bạn buộc phải thực hiện các thao tác hàng theo hàng thì hãy cố tìm cách giải quyết khác để thực hiện. Một số lựa chọn thực hiện tác vụ ở client là sử dụng các bảng trong tempdb, các bảng dẫn xuất, câu truy vấn có tương quan với nhau, hay sử dụng lệnh CASE. Thường thì tất cả kỹ thuật non-cursor đều có thể sử dụng để thực hiện cùng các tác vụ như SQL Server cursor.

Sử dụng TSQL by Duration

Để xác định những câu truy vấn chậm chạp bạn có thể sử dụng vết ‘TSQL by Duration’ trong SQL Server Profiler để theo dõi thời gian xử lý của các câu truy vấn.

Chỉ sử dụng DISTINCT, ORDER BY khi cần thiết

Bạn hãy cẩn thận cân nhắc có cần mệnh đề DISTINCT và ORDER BY trong câu truy vấn hay không. Mệnh đề DISTINCT và ORDER BY làm chậm quá trình truy vấn dữ liệu. Một số người thường đưa DISTINCT vào câu truy vấn ngay cả khi nó không cần thiết. Đây là thói quen xấu cần nên bỏ.

Chỉ sử dụng UNION khi cần thiết

Khi bạn dùng câu lệnh UNION, bạn hãy nhớ rằng ngầm định nó thực hiện tương đương như kết quả từ câu SELECT DISTINCT. Do đó nếu bạn biết chắc rằng chẳng có 1 hàng nào trùng lắp được tạo ra từ kết quả của UNION thì bạn nên sử dụng câu lệnh UNION ALL thay thế.

Ghi rõ tên cột trong câu lệnh SELECT

Trong câu truy vấn bạn không nên trả về các cột không cần thiết, như câu SELECT *, nó sẽ trả về mọi cột trong bảng và ngoài ra nó còn cản trở việc sử dụng indexes.

Giới hạn thời gian câu truy vấn

Nếu người sử dụng thực hiện nhiều câu truy vấn đặc biệt trên SQL Server và bạn nhận thấy rằng các câu truy vấn được viết nghèo nàn này chiếm quá nhiều tài nguyên, bạn nên cân nhắc sử dụng tuỳ chọn cấu hình ‘query governor cost limit’ để giới hạn thời gian câu truy vấn thực hiện. Bạn có thể gán option này với giá trị là seconds và có 2 cách để xác định. Một là bạn thay đổi nó ở cấp độ server bằng việc sử dụng sp_configure “query governor cost limit’” hoặc có thể gán nó ở cấp độ kết nối (chỉ có kết nối này có ảnh hưởng) bằng cách sử dụng lệnh SET QUERY_GOVERNOR_COST_LIMIT.

Giới hạn số lượng records khi truy vấn

Nếu trong ứng dụng cho phép người sử dụng chạy các câu truy vấn nhưng bạn không thể ngăn cản được người sử dụng lấy về hàng trăm, thậm chí hàng ngàn records mà họ không cần thì bạn hãy xem xét việc sử dụng toán tử TOP trong câu lệnh SELECT, chẳng hạn SELECT TOP 100 fullname, address FROM Customers. Ngoài việc sử dụng TOP, SQL Server còn hổ trợ lệnh SET ROWCOUNT cũng nhằm hạn chế số records truy xuất từ câu SELECT nhưng lệnh SET ROWCOUNT này không hiệu quả bằng TOP.

Hạn chế sử dụng toán tử bên dưới

Bạn hãy cố gắng tránh sử dụng các toán tử sau trong mệnh đề WHERE: "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%abc'". Vì những toán tử này nó không sử dụng đặc tính index mà thay vì thế nó sẽ dò tìm toàn bảng gây ảnh hưởng đến tốc độ của câu truy vấn.

Ví dụ:

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

Thay vì thế, bạn nên sử dụng

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

Nếu bạn có sự chọn lựa giữa IN và EXISTS trong câu truy vấn, bạn hãy chọn EXISTS là tốt nhất. Tương tự như IN và BETWEEN, hãy chọn BETWEEN.

Sử dụng Index hint để tối ưu hoá truy vấn sử dụng index

Nếu bạn nhận thấy rằng SQL Server sử dụng Table Scan thay vì INDEX SEEK trong câu truy vấn có IN hay OR, ngay cả cột tìm kiếm đó đã tạo index. Bạn nên sử dụng Index hint để bắt buộc trình tối ưu hoá truy vấn sử dụng index.

Ví dụ:

SELECT lname, fname, address FROM Customers WHERE CusID in (1, 5, 10)

Câu truy vấn sau sẽ chạy nhanh hơn câu trước vì ép sử dụng index

SELECT lname, fname, address FROM Customers (INDEX = IX_CusID) WHERE CusID in (1, 5, 10)

Tránh sử dụng Function trực tiếp vào cột

Bạn nên tránh sử dụng Functions hoạt động trực tiếp vào cột vì index sẽ không được sử dụng như trong ví dụ sau:

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

Câu truy vấn sau sẽ sử dụng index

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

Lưu ý khi ràng buộc dữ liệu

Đừng cài đặt các đặc tính ràng buộc dữ liệu dư thừa trong database. Chẳng hạn, nếu bạn cài đặt các ràng buộc tham chiếu bằng khoá chính, khoá ngoại thì bạn đừng thêm vào các triggers có cùng mục đích. Tương tự như vậy, đối với constraints và defaults hay constraints và rules.

Sử dụng Store Procedure khi thao tác với dữ liệu hình ảnh

Nếu ứng dụng của bạn cần thêm giá trị nhị phân vào cột dữ liệu hình ảnh (rất ít ai thiết kế database lại lưu trữ dữ liệu hình ảnh). Bạn hãy sử dụng Stored Procedure để thay thế cho câu lệnh INSERT được nhúng trong ứng dụng. Có lựa chọn lựa này vì đầu tiên ứng dụng phải chuyển giá trị nhị phân sang chuổi ký tự trước khi nó gởi tới Server. Và khi Server nhận được chuổi ký tự nó lại chuyển ngược lại dạng nhị phân. Hãy sử dụng SP để tránh vấn đề này.

Nếu có thể, bạn nên tránh sử dụng các hàm chuyển đổi kiểu dữ liệu trong mệnh đề WHERE.

Kinh nghiệm về Transaction

Bạn nên đóng gói mã T_SQL của bạn trong 1 transaction nếu bạn định sửa đổi database để bảo đảm tính nhất quán của dữ liệu. Còn đối với code chỉ cho việc reports thì đừng nên đóng gói thành 1 transaction vì khi mở và đóng 1 transaction sẽ tốn thêm 1 ít chi phí.

Không nên sử dụng optimizer hint trong câu truy vấn

Bạn không nên sử dụng các optimizer hint trong câu truy vấn vì thường thì rất khó đoán trước được Query Optimizer làm những gì. Optimizer hints là những từ khóa đặc biệt dùng để ép Query Optimizer chạy theo các từ khoá này.

Sử dụng UNION ALL

Trong các câu truy vấn có 1/nhiều OR bạn có thể viết lại bằng cách kết hợp UNION ALL để tăng tốc độ truy vấn.

Ví dụ:

SELECT employeeID, firstname, lastname
FROM Employees
WHERE dept = 'prod' or city = 'Orlando' or division = 'food'

Câu truy vấn sau cùng mục đích nhưng có tốc độ nhanh hơn

SELECT employeeID, firstname, lastname FROM Employees WHERE dept = 'prod'
UNION ALL
SELECT employeeID, firstname, lastname FROM Employees WHERE city = 'Orlando'
UNION ALL
SELECT employeeID, firstname, lastname FROM Employees WHERE division = 'food'

Hạn chế sử dụng Views lồng nhau

Trong khi Views rất thuận tiện trong việc hạn chế người sử dụng xem dữ liệu nhưng về vấn đề hiêu năng, sử dụng Views không tốt cho lắm. Vì Views không giống như SP, nó không được tối ưu hoá trước và ngay cùng câu lệnh SELECT bỏ trong View thì chạy chậm hơn câu lệnh đó chạy trong Query Analyzer. SQL Server không cấm việc sử dụng các Views lồng nhau nhưng bạn nên tránh sử dụng các Views lồng nhau.

Kết hợp HAVING và WHERE

Nếu câu SELECT của bạn có sử dụng HAVING bạn nên có thêm việc sử dụng WHERE để hạn chế những hàng không cần thiết. Trình tự thực hiện câu truy vấn có cú pháp SELECT … WHERE … GROUP BY … HAVING …. Đầu tiên WHERE được sử dụng để chọn ra những dòng thích hợp mà cần phải group. Kế đến GROUP BY chia các rows thành các tập records được nhóm và cuối cùng HAVING mới được sử dụng.

Sử dụng biến table thay vì bảng tạm

Trong SQL Server 2000 cung cấp 1 kiểu dữ liệu mới gọi là ‘table’. Mục đích của nó là lưu trữ tạm thời tập các records. Nếu có thể, bạn hãy sử dụng biến table thay vì sử dụng bảng tạm.

Không nên sử dụng SELECT COUNT(*)

Nếu bạn cần xác định sự tồn tại của records trong bảng, đừng nên sử dụng SELECT COUNT(*) vì nó không hiệu quả và lãng phí tài nguyên thay vì thế bạn nên sử dụng IF EXISTS thì hiệu quả hơn. Ví dụ:

IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')

Hãy sử dụng IF EXISTS sẽ nhanh hơn:

IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

Sử dụng Store Procedure thay vì Function

Nếu được hãy sử dụng SP thay vì User defined Function trong 1 tập dữ liệu lớn mà bạn muốn trả về. Bởi vì 1 User defined Function phức tạp thì chiếm nhiều chi phí hơn là 1 SP có cùng chức năng.

Xem phiên bản SQL Server

Đôi khi bạn muốn tìm phiên bản service pack mà bạn đang chạy trên SQL Server, bạn hãy sử dụng lệnh sau trong ISQL/W hay Query Analyzer:

SELECT @@Version

Sử dụng bảng dẫn xuất (derived table) để thay thế bảng tạm hay bảng chính.

Ví dụ sau được copy từ Books Online:

SELECT Ord.OrderID, Ord.OrderDate, maxUnitPrice
FROM Northwind.dbo.Orders AS Ord INNER JOIN
( SELECT orderID, MAX(UnitPrice) AS maxUnitPrice
  FROM Northwind.dbo.[Order Details]
  GROUP BY OrderID ) AS OrdDet ON ordDet.orderID = Ord.orderID
ORDER BY Ord.OrderID DESC, Ord.OrderDate, maxUnitPrice

Câu truy vấn màu đỏ được gọi là derived table. Derived table chiếm không gian ít hơn bảng nguồn hay bảng tạm bởi vì nó đã được lọc bớt đi. Nếu bạn sử dụng bảng nguồn bằng INNER JOIN sẽ chậm hơn câu trên rất nhiều.

Kết luận

Đây là một bài viết được sưu tầm trên mạng, nhằm chia sẽ kinh nghiệm khi làm việc với SQL Server, nếu bạn nào thấy có gì sai sót hoặc cần bổ sung, có thể comment bên dưới.

Cảm ơn các bạn đã theo dõi bài viết. Hãy để lại bình luận hoặc góp ý của mình để phát triển bài viết tốt hơn. Đừng quên “Luyện tập – Thử thách – Không ngại khó”.

---

Thảo luận

Nếu bạn có bất kỳ khó khăn hay thắc mắc gì về khóa học, đừng ngần ngại đặt câu hỏi trong phần BÌNH LUẬN bên dưới hoặc trong mục HỎI & ĐÁP trên thư viện Howkteam.com để nhận được sự hỗ trợ từ cộng đồng.