Công cụ tối ưu hóa các câu sql

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. Hôm nay, tôi xin chia sẻ với các bạn một số tips để làm điều đó nhé.

Những điều cần biết

Trước khi đi vào các tips tôi sẽ chia sẻ, các bạn cần nắm được một số khái niệm sau:

• Cơ sở dữ liệu là nơi lưu trữ dữ liệu.
• Những thao tác tới cơ sở dữ liệu gồm truy vấn (select), thêm (insert), sửa (update), xoá (

  Select UserId, Username From Users Where Amount > 18 Or Amount = 18

• dữ liệu. Khi dữ liệu nhỏ, tốc độ thực hiện gần như là tức thì, khi dữ liệu đủ lớn thì tốc độ thực hiện trở thành một trở ngại đối với dự án. Đôi khi bạn có thể mất hàng giờ để thực hiện một thao tác bất kỳ tới cơ sở dữ liệu.

Bài toán đặt ra lúc này là làm sao giảm thời gian thực hiện mà vẫn đáp ứng được dữ liệu mong muốn. Mặc dù hiện nay có rất nhiều phương án để giải quyết bài toán này như sử dụng cache, đưa dữ liệu lên RAM...

Trong phạm vi bài viết này, tôi sẽ đưa ra một số lưu ý khi thao tác tới cơ sở dữ liệu nhằm đạt được hiệu năng tốt nhất.

Lưu ý: CSDL mình sử dụng là MsSql nhé.

Bí kíp tối ưu truy vấn cơ sở dữ liệu

1. Hạ tầng

Do cơ sở dữ liệu lưu trữ trên ổ cứng nên việc nâng cấp hạ tầng như chuyển ổ cứng thường thành SSD, kết nối mạng giữa server và client phải đảm bảo, tăng bộ nhớ, chia nhỏ thành nhiều CSDL rồi thực hiện tổng hợp lại... Cách thức này chỉ phù hợp trong một thời gian vì hạ tầng luôn bị giới hạn chưa kể chi phí cũng đội lên rất nhiều.

2. Giới hạn kết quả trả về

Chỉ trả về những trường được sử dụng (tránh sử dụng Select * ...). Hạn chế số bản ghi trả về.

3. Đánh chỉ mục (Index)

Chỉ mục là bảng tra cứu đặc biệt mà Database Search Engine có thể sử dụng để tăng thời gian và hiệu suất truy vấn dữ liệu. Một lưu ý nhỏ là: index làm tăng hiệu năng của lệnh SELECT nhưng lại làm giảm hiệu năng của lệnh INSERT, UPDATE và DELETE. Chỉ nên index những trường có kiểu dữ liệu số. Những kiểu dữ liệu khác nếu không phải là đặc biệt, hoặc ko phải tìm kiếm nhiều thì ko nên index.Ví dụ: bạn có bảng Users có trường Username là nvarchar(200). Trong ứng dụng việc truy vấn theo Username là rất nhiều nên bạn có thể đánh index cho trường Username đó.

Các trường được index không nên thao tác với một số phép toán phủ định như: “IS NULL”, “!=”, “NOT”, “NOT IN”, “NOT LIKE”... Vì vậy một trường được tạo ra và được xác định index thì nên là khác NULL hoặc có giá trị mặc định.Hạn chế sử dụng phép toán so sánh 2 lần như “>=”, “<=” với những trường đánh index (bản chất của phép toán này là phép toán OR).Ví dụ:

Select UserId, Username From Users Where Amount >= 18

Câu lệnh trên tương ứng với câu lệnh sau

Select UserId, Username From Users Where Amount > 18 Or Amount = 18

5. Sử dụng từ khoá Like phải hợp lý

Khi sử dụng LIKE, không nên sử dụng ký tự %, * đặt ở phía trước giá trị tìm kiếm.

Select UserId, Username, FirstName From Users Where FirstName Like '%Hello';

Bạn hãy thay thế bằng câu lệnh sau:

Select UserId, Username, FirstName From Users Where FirstName Like 'Hello%';

Hoặc trong trường hợp bắt buộc thì nên sử dụng FULL TEXT search như sau:

Select UserId, Username, FirstName From Users Where CONTAINS(FirstName, 'Hello');

6. Không nên sử dụng hàm thao tác trực tiếp đến các column

Chúng ta hãy xem ví dụ dưới đây - xác định danh sách user có tuổi lớn hơn 18:

Select UserId, Username, DateOfBirth From Users Where DATEDIFF(YY, DateOfBirth, GETDATE()) > 18

Hàm DATEDIFF đã tác động trực tiếp tới trường DateOfBirth. Chúng ta có thể thay thế thành câu lệnh như sau:

Select UserId, Username, DateOfBirth From Users Where DateOfBirth < DATEADD(YY, -18, GETDATE())

7. UNION vs UNION ALL

Khi sử dụng UNION, Mssql sẽ thực hiện sắp xếp, lọc và loại bỏ các bản ghi trùng. Sử dụng UNION không khác gì bạn đang sử dụng SELECT DISTINCT. Nếu chúng ta xác định việc hợp dữ liệu giữa các nguồn với nhau mà không có bản ghi trùng thì việc sử dụng UNION sẽ không hợp lý.

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 lname, fname, address FROM Customers WHERE fname LIKE ‘V%’

0

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 lname, fname, address FROM Customers WHERE fname LIKE ‘V%’

1

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ó”.

Tải xuống

Tài liệu

Nhằm phục vụ mục đích học tập Offline của cộng đồng, Kteam hỗ trợ tính năng lưu trữ nội dung bài học Nguyên nhân và Phương pháp tối ưu truy vấn SQL dưới dạng file PDF trong link bên dưới.

Ngoài ra, bạn cũng có thể tìm thấy các tài liệu được đóng góp từ cộng đồng ở mục TÀI LIỆU trên thư viện Howkteam.com

Đừng quên like và share để ủng hộ Kteam và tác giả nhé!

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ê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.