SQL Database Performance Tuning for Developers (Part 2)

Bài đăng này đã không được cập nhật trong 4 năm

SQL Tuning: Select Sparingly

Một trong những mẹo tối ưu hóa SQL là tránh sử dụng SELECT *, thay vào đó, bạn nên lựa chọn các columns cụ thể mà bạn cần. Điều này nghe có vẻ đơn giản, nhưng bạn có thể thấy thấy lỗi này ở khắp mọi nơi. Hãy xem xét một table có hàng trăm columns và hàng triệu record, nếu ứng dụng của bạn chỉ thực sự cần một vài column, thì không có nghĩa lý gì lại truy vấn tất cả dữ liệu. Nó rất lãng phí tài nguyên. (Để biết thêm chi tiết, bạn có thể xem tại đây).

Lấy ví dụ:

SELECT * FROM Employees

so với việc:

SELECT FirstName, City, Country FROM Employees

Nếu bạn thực sự cần nhiều column, hãy liệt kê rõ ràng từng column. Đây không phải là một quy tắc, mà là một phương pháp để ngăn ngừa các lỗi hệ thống trong tương lai và điều chỉnh hiệu năng SQL. Ví dụ: nếu bạn đang sử dụng INSERT... SELECT... và table gốc đã được thay đổi, thông qua việc thêm một colum mới, bạn có thể gặp lỗi, ngay cả khi column đó không cần đến table đích, ví dụ:

INSERT INTO Employees SELECT * FROM OldEmployees

Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

Để tránh loại lỗi này từ SQL , bạn nên khai báo từng column riêng lẻ:

INSERT INTO Employees (FirstName, City, Country)
SELECT Name, CityName, CountryName
FROM OldEmployees

Tuy nhiên, lưu ý rằng có một số tình huống sử dụng SELECT * có thể lại là phù hợp. Ví dụ, với các bảng tạm, điều này dẫn chúng ta đến mục tiếp theo.

SQL Tuning: The Wise Use of Temporary Tables (#Temp)

Các bảng tạm thường làm tăng độ phức tạp của truy vấn. Nếu code của bạn có thể được viết theo cách đơn giản, thì nên tránh sử dụng các bảng tạm.

Nhưng nếu bạn có một quy trình được lưu trữ, với một số thao tác dữ liệu không thể xử lý bằng một truy vấn duy nhất, bạn có thể sử dụng các bảng tạm làm trung gian để giúp bạn tạo kết quả cuối cùng.

Khi bạn phải JOIN một table lớn và có các điều kiện trên table đó, bạn có thể tăng hiệu suất bằng cách chuyển dữ liệu của mình trong bảng tạm, sau đó thực hiện join vào đó. Bảng tạm của bạn sẽ có ít record hơn table gốc (lớn), vì vậy việc JOIN sẽ kết thúc nhanh hơn!

Quyết định không phải lúc nào cũng đơn giản, nhưng ví dụ này sẽ cho bạn thấy một trong những tình huống mà bạn có thể muốn sử dụng các bảng tạm:

SELECT * INTO #Temp FROM Customer WHERE RegionID = 5
SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

(Lưu ý: một số developer cũng tránh sử dụng SELECT INTO để tạo bảng tạm, câu lệnh này lock cơ sở dữ liệu tempdb, không cho phép người dùng khác tạo bảng tạm. May mắn thay, điều này đã được sửa trong version 7.0 trở lên.)

SELECT r.RegionName, t.Name FROM Region r 
JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t 
ON t.RegionID = r.RegionID

Tuy nhiên có một vấn đề với truy vấn thứ hai này. Như đã mô tả ở trên, chúng ta chỉ nên đưa vào các column mà chúng ta cần trong truy vấn con của mình (tức là không sử dụng SELECT *) :

SELECT r.RegionName, t.Name FROM Region r 
JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t 
ON t.RegionID = r.RegionID

Tất cả các đoạn SQL này sẽ trả về cùng một dữ liệu. Nhưng với các bảng tạm, chúng ta có thể tạo một index trong bảng tạm để cải thiện hiệu suất. Có một số thảo luận tốt ở đây về sự khác biệt giữa các bảng tạm và các truy vấn con.

Cuối cùng, khi bạn thực hiện xong với bảng tạm, hãy xóa nó để clear tài nguyên tempdb, thay vì chỉ chờ nó tự động bị xóa (sẽ xảy ra khi kết nối của bạn đến cơ sở dữ liệu bị chấm dứt):

DROP TABLE #temp

SQL Tuning: “Does My Record Exist?”

Kỹ thuật tối ưu hóa SQL này liên quan đến việc sử dụng EXISTS (). Nếu bạn muốn kiểm tra nếu một bản ghi tồn tại, hãy sử dụng EXISTS () thay vì COUNT (). Trong khi COUNT () quét toàn bộ table, đếm tất cả các mục phù hợp với điều kiện của bạn, thì EXISTS () sẽ thoát ngay khi thấy kết quả cần thiết. Điều này sẽ cung cấp cho bạn performance tốt hơn và code rõ ràng hơn.

IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0
    PRINT 'YES'

so với:

IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%')
    PRINT 'YES'

SQL Performance Tuning (in the Office)

Các quản trị viên cơ sở dữ liệu SQL (DBA) và developer thường xung đột đối với các vấn đề liên quan đến dữ liệu và không liên quan đến dữ liệu. Đây là một số lời khuyên (cho cả hai bên) về cách hòa hợp và làm việc cùng nhau hiệu quả.

Database Optimization đối với developer:

Nếu ứng dụng của bạn ngừng hoạt động đột ngột, nó có thể không phải là vấn đề về cơ sở dữ liệu. Ví dụ, có thể bạn có một vấn đề internet. Điều tra một chút trước khi bạn buộc tội một DBA!
Ngay cả khi bạn là một người lập mô hình dữ liệu SQL giỏi, hãy nhờ DBA giúp bạn với sơ đồ quan hệ của bạn. Họ có rất nhiều thứ để chia sẻ và cung cấp.
Các DBA không muốn thay đổi nhanh chóng. Điều này là tự nhiên: họ cần phân tích toàn bộ cơ sở dữ liệu và kiểm tra tác động của bất kỳ thay đổi nào từ mọi góc độ. Một thay đổi đơn giản trong một column có thể mất một tuần để thực hiện, nhưng điều đó bởi vì một lỗi có thể trở thành tổn thất lớn cho công ty. Hãy kiên nhẫn!
Đừng yêu cầu các DBA SQL thực hiện thay đổi dữ liệu trong môi trường production. Nếu bạn muốn truy cập vào cơ sở dữ liệu production, bạn phải chịu trách nhiệm cho tất cả các thay đổi của riêng bạn.