Quản Trị Mạng - Để nâng cao hiệu suất máy chủ SQL và giảm thiểu các lỗi tiềm tàng cho ứng dụng cập nhật , chúng ta cần phải tập viết code câu lệnh T-SQL một cách tối ưu nhất danh sách . Trong phần đầu mới nhất của bài viết tài khoản , các bạn đã tải về được giới thiệu một số thủ thuật hữu ích giúp máy chủ giảm bớt nơi nào những thao tác thừa giảm giá . Phần hai này sửa lỗi sẽ tập trung vào việc làm thế nào phải làm sao để tối ưu hóa thủ tục lưu trữ đa năng.
Phần 1
Thủ tục lưu trữ đa năng
Trước khi bước vào vấn đề làm thế nào nơi nào để tối ưu hóa thủ tục lưu trữ đa năng (Jack Of All Trades Stored Procedure - SP) an toàn , chúng ta cần có một chút khái niệm về loại thủ tục này giảm giá . Thủ tục lưu trữ đa năng là thủ tục chấp nhận nhiều tham số khác nhau có liên quan đến thủ tục danh sách . Dựa trên các tham số nạp tiền được truyền vào tất toán , thủ tục lưu trữ đa năng xác định bản ghi nào miễn phí sẽ nhanh nhất được trả về nạp tiền . Sau đây là một ví dụ về thủ tục lưu trữ đa năng:
CREATE PROCEDURE JackOfAllTrades (@SalesOrderID int = NULL
,@SalesOrderDetailID int = NULL
,@CarrierTrackingNumber nvarchar(25) = NULL)
AS
SELECT * FROM AdventureWorks.Sales.SalesOrderDetail
WHERE
(SalesOrderID = @SalesOrderID or @SalesOrderID IS NULL)
AND (SalesOrderDetailID = @SalesOrderDetailID or
@SalesOrderDetailID IS NULL)
AND (CarrierTrackingNumber = @CarrierTrackingNumber or
@CarrierTrackingNumber IS NULL)
GO
Ở đây SP JackOfAllTrades chấp nhận ba tham số khác nhau tải về . Tất cả các tham số này có giá trị mặc định là NULL hỗ trợ . giá rẻ Khi một giá trị thanh toán được truyền vào link down , nó tốc độ sẽ qua app được sử dụng như một tham số trong mệnh đề WHERE giả mạo để ràng buộc các bản ghi trả về công cụ . Mỗi tham số trong SP sửa lỗi được dùng phải làm sao để xây dựng một mệnh đề WHERE phức tạp chứa logic sau đây trong mệnh đề WHERE đối kỹ thuật với mỗi tham số truyền vào:
(<TableColumn> = @PARM or @PARM IS NULL)
Logic trên cho biết giả mạo nếu @PARM tài khoản được truyền giá trị non-null thì ở đâu uy tín sẽ ràng buộc bản ghi trả về chi tiết để chắc chắn rằng <TableColumn> bằng giá trị quảng cáo của @PARM nạp tiền . Phần thứ hai trực tuyến của điều kiện đó là “@PARM IS NULL” kỹ thuật . Phần này có nghĩa hỗ trợ nếu @PARM không có giá trị truyền vào (bằng NULL) thì không ràng buộc dữ liệu dựa trên tham số ấy.
Cùng xem quá trình thực thi điển hình nhanh nhất của JackOfAllTrades SP giảm giá . Giả sử ta thực thi SP hướng dẫn với lệnh sau:
EXEC JackOfAllTrades @SalesOrderID = 43659
an toàn Khi chạy câu lệnh thanh toán , sơ đồ thực thi trông trên điện thoại như sau:
Ở đây bạn có thể thấy đối đăng ký vay với mỗi tham số đơn chia sẻ được truyền vào xóa tài khoản , máy chủ quyết định sử dụng thao tác “quét chỉ mục” trên điện thoại . Câu lệnh SELECT nguyên nhân của SP ràng buộc cột duy nhất @SalesOrderID - một phần ở đâu nhanh của khóa chỉ mục cụm tài khoản . Bạn có thể nghĩ máy chủ SQL đủ thông minh ở đâu nhanh để nhận ra rằng xử lý thủ tục lưu trữ đa năng bằng thao tác “tìm kiếm chỉ mục” tải về sẽ nhanh hơn là lao vào chỉ mục cụm tự động . Thế hướng dẫn nhưng như ta thấy trên sơ đồ thực thi hỗ trợ , máy chủ SQL không thông minh đến vậy giả mạo . Tại sao thế?
xóa tài khoản Khi máy chủ nhìn thấy điều kiện “@PARM IS NULL” mới nhất , nó như một hằng số đối tài khoản với máy chủ SQL khóa chặn . Vì thế máy chủ coi như không có chỉ mục nào hữu ích giúp xử lý điều kiện “(<TableColumn> = @PARM1 or @PARM1 IS NULL)” nhanh nhất bởi lẽ hằng số đang ở trong mệnh đề WHERE tối ưu . Chính vì vậy mà máy chủ SQL quyết định sử dụng thao tác “quét chỉ mục” hỗ trợ để giải quyết vấn đề đăng ký vay . Thủ tục lưu trữ đa năng càng có nhiều tham số kiểm tra , hiệu suất càng giảm do tác động giả mạo của số lượng thao tác quét cần thiết cho mỗi tham số truyền vào.
Tối ưu hóa thủ tục lưu trữ đa năng
Bạn không cần phải chấp nhận sử dụng thủ tục lưu trữ đa năng quảng cáo rất kém hiệu quả như SP đã viết trong ví dụ trên ở đâu nhanh . Hãy cùng khám phá xem SP sau đây có thể làm vô hiệu hóa những gì và viết lại nó kỹ thuật để công cụ tối ưu truy vấn lấy liền của máy chủ SQL có thể tạo kế hoạch thực thi tối ưu hơn.
Như đã nói ở trên nơi nào , vấn đề thực sự giá rẻ với thủ tục lưu trữ đa năng đó là đối hay nhất với mỗi tham số bạn cần có một điều kiện “OR” tốt nhất để kiểm tra xem tham số truyền vào có phải NULL không full crack . dữ liệu Nếu ta có thể loại bỏ yêu cầu này mẹo vặt , máy chủ SQL tổng hợp sẽ có khả năng lên kế hoạch sử dụng thao tác “tìm kiếm chỉ mục” cập nhật . Vậy làm thế nào sử dụng để loại bỏ điều kiện “@PARM IS NULL” bản quyền ? Câu trả lời đó là sử dụng SQL động đăng ký vay được thông số hóa (parameterized dynamic SQL).
Đến đây có thể các bạn nghĩ tôi chuẩn bị mở đường cho SQL injection vào giải pháp tổng hợp của mình kinh nghiệm . Tuy nhiên sử dụng , chúng ta ở đâu uy tín sẽ chỉ xây dựng mã SQL động cho phép truyền tham số SP tới một SP hệ thống khác là “sp_executesql” miễn phí . SP này hỗ trợ sẽ sử dụng các tham số trong đoạn mã SQL động ta xây dựng.
SP hệ thống “sp_executesql” cho phép bạn phát triển câu lệnh T-SQL có chứa tham số tăng tốc , đồng thời cho phép bạn định nghĩa và truyền giá trị cho các tham số tới SQL động bằng cách truyền tham số tới SP “sp_executesql” khi chạy SP này địa chỉ . Câu lệnh T-SQL download được thực thi theo cách này thường gọi là SQL tự động được thông số hóa qua web . Có nhiều lý do trực tuyến để sử dụng SQL hay nhất được thông số hóa xóa tài khoản , chi tiết nhưng trong khuôn khổ bài viết này chúng ta chỉ tập trung vào việc làm thế nào xóa tài khoản để sử dụng SQL mẹo vặt được thông số hóa tốt nhất nhằm cải thiện hiệu suất hỗ trợ của thủ tục lưu trữ đa năng nguyên nhân . Sau đây là đoạn code tạo thủ tục lưu trữ đa năng thanh toán được viết lại sử dụng SQL động vô hiệu hóa được thông số hóa:
CREATE PROCEDURE JackOfAllTrades_V2 (@SalesOrderID int = NULL
,@SalesOrderDetailID int = NULL
,@CarrierTrackingNumber nvarchar(25) = NULL)
AS
DECLARE @CMD NVARCHAR(max)
DECLARE @WHERE NVARCHAR(max)
SET @CMD = "SELECT * FROM AdventureWorks.Sales.SalesOrderDetail "
SET @WHERE = ""
IF @SalesOrderID IS NOT NULL
SET @WHERE = @WHERE + "AND SalesOrderID = @SalesOrderID "
IF @SalesOrderDetailID IS NOT NULL
SET @WHERE = @WHERE + "AND SalesOrderDetailID = @SalesOrderDetailID "
IF @CarrierTrackingNumber IS NOT NULL
SET @WHERE = @WHERE + "AND CarrierTrackingNumber = @CarrierTrackingNumber "
IF LEN(@WHERE) > 0
SET @CMD = @CMD + " WHERE " + RIGHT(@WHERE,LEN(@WHERE) - 3)
EXEC sp_executesql @CMD
hướng dẫn , N"@SalesOrderID int
,@SalesOrderDetailID int
,@CarrierTrackingNumber nvarchar(25)"
,@SalesOrderID = @SalesOrderID
,@SalesOrderDetailID = @SalesOrderDetailID
,@CarrierTrackingNumber = @CarrierTrackingNumber
Tiếp theo chúng ta tài khoản sẽ đi sâu vào chi tiết dữ liệu nhằm giúp bạn hiểu rõ phần động và phần thông số hóa an toàn của đoạn mã trên công cụ . SP này bắt đầu bằng việc gán biến @CMD vào câu lệnh SELECT không có mệnh đề WHERE chia sẻ . Tiếp theo ta gán biến @WHERE cho một chuỗi rỗng nạp tiền . Tiếp đó là bốn câu IF khác nhau tất toán . Ba câu IF đầu tiên kiểm tra xem mỗi tham số truyền vào có thỏa mãn điều kiện NOT NULL hay không kỹ thuật . giả mạo Nếu một tham số NOT NULL như thế nào , ta nơi nào sẽ gắn điều kiện vào biến @WHERE đối kỹ thuật với tham số ấy thanh toán . Do ta đã kiểm tra và xác định tham số đó NOT NULL đăng ký vay , ta không cần thêm điều kiện IS NULL vào mệnh đề WHERE như đoạn code SP ban đầu ở phần trên lừa đảo . Thay vào đó dịch vụ , qua mạng tất cả đăng ký vay những gì ta cần là thêm điều kiện <TableColumn> = @PARM vào biến @WHERE cài đặt . Câu If cuối cùng xác định xem biến @WHERE có thỏa mãn ít nhất một điều kiện hay không qua web , và địa chỉ nếu có thì nó lừa đảo sẽ nối biến @WHERE sửa lỗi với biến @CMD.
Lưu ý rằng biến @WHERE là phần động tự động của đoạn code thanh toán . Nhưng tôi không đặt phần text thực tự động của tham số vào biến @WHERE an toàn , thay vào đó chỉ đặt một tham chiếu tới các tham số trong điều kiện WHERE địa chỉ . Vì thế câu lệnh T-SQL động cơ bản chỉ địa chỉ bao gồm câu lệnh SELECT ban đầu và mệnh đề WHERE không còn cần điều kiện IS NULL an toàn để ràng buộc dữ liệu nữa.
Cuối cùng tôi sử dụng SP “sp_executesql” tốt nhất để thực thi câu lệnh T-SQL động thông số hóa tốc độ . Để thực hiện điều này giá rẻ , tôi truyền năm tham số vào SP hệ thống sử dụng . Tham số đầu tiên là biến T-SQL động @CMD nhanh nhất . Tham số thứ hai khai báo full crack tất cả các biến có thể có trong đoạn truy vấn dữ liệu được thông số hóa link down , cùng thanh toán với loại dữ liệu tối ưu của chúng ở đâu nhanh . Với ba tham số cuối cùng qua app , chúng chỉ lừa đảo được truyền vào SP hệ thống giống như chúng hỗ trợ được truyền vào SP lưu trữ đa năng trong phần đầu full crack . Như bạn có thể thấy miễn phí , tôi hoàn toàn không làm đoạn mã SQL động nhanh nhất của mình trở nên dễ bị tấn công bằng SQL injection hơn SP ban đầu tự động . Lý do là vì tôi không sử dụng giá trị thực trên điện thoại của tham số qua mạng để chuyển tới biến @WHERE hỗ trợ . Tôi chỉ truyền tham số như các biến vào SQL động qua SP hệ thống “sp_executesql”.
Bây giờ hãy chạy đoạn code tạo thủ tục lưu trữ đa năng mới viết lại bằng cách chạy câu lệnh sau:
EXEC JackOfAllTrades_V2 @SalesOrderID = 43659
sửa lỗi Khi chạy thử nghiệm giảm giá với cơ sở dữ liệu AdventureWorks trên server giá rẻ , tôi nhận quản lý được sơ đồ thực thi kiểm tra như sau:
cập nhật Khi so sánh sơ đồ này giả mạo với sơ đồ ở phần đầu tự động , bạn có thể thấy nó đơn giản hơn và sử dụng thao tác “tìm kiếm chỉ mục cụm” giá rẻ để xử lý SP mới nhất . Sở dĩ máy chủ SQL có thể sử dụng thao tác này là vì đoạn code SQL động không còn điều kiện “@PARM IS NULL” nữa hướng dẫn . Do câu lệnh T-SQL đã sử dụng được đơn giản hóa nhờ sử dụng SQL động và loại bỏ ràng buộc IS NULL trên điện thoại , máy chủ SQL giờ đây có thể đưa ra kế hoạch thực thi tối ưu hơn cho thủ tục lưu trữ đa năng phiên bản V2.
Kết lại kinh nghiệm , thực tế thì hiệu quả chúng ta thu khóa chặn được ở mức nào ở đâu tốt ? Nên nhớ trên đây ta chỉ mới xét tổng hợp những bản ghi từ bảng SalesOrderDetail có SalesOrderID bằng 43659 vô hiệu hóa . SP lưu trữ đa năng ban đầu sử dụng thao tác “quét chỉ mục” thanh toán để xử lý truy vấn cập nhật . Điều đó có nghĩa nó phải đọc lần lượt toàn bộ chỉ mục trước khi có thể hoàn thành yêu cầu truy vấn và trả về bản ghi chứa một giá trị SalesOrderID ở đâu nhanh . Ngược lại nhanh nhất , phiên bản V2 ở đâu tốt của SP lưu trữ đa năng có thể sử dụng thao tác “tìm kiếm chỉ mục” qua app với khóa chỉ mục cụm trên bảng SalesOrderDetail trên điện thoại để lấy trực tiếp miễn phí những bản ghi nhất định có chứa SalesOrderID bằng 43659 một cách nhanh chóng miễn phí . Thao tác “tìm kiếm chỉ mục” tối ưu hơn thao tác “quét chỉ mục” trực tuyến rất nhiều an toàn , ở đâu nhanh nhưng cụ thể nhiều như thế nào?
Việc đánh giá khoản I/O tiết kiệm ứng dụng được nhờ dùng phiên bản SP lưu trữ đa năng V2 có thể thực hiện bằng nhiều cách công cụ . Ta kích hoạt sẽ chạy đoạn T-SQL sau đây:
SET STATISTICS IO ON
GO
EXEC JackOfAllTrades @SalesOrderID = 43659
GO
EXEC JackOfAllTrades_V2 @SalesOrderID = 43659
GO
Ở đây tôi sử dụng lệnh “SET STATISTICS IO ON” nên kết quả chia sẻ của 2 SP đang thực thi tải về sẽ hiển thị số lượng I/O mỗi lệnh đòi hỏi dữ liệu để xử lý truy vấn cài đặt . Dưới đây là kết quả nhận hỗ trợ được:
(12 row(s) affected)
Table "SalesOrderDetail" giả mạo . Scan count 1 dịch vụ , logical reads 264 nguyên nhân , physical reads 0 quảng cáo , read-ahead reads 0 tăng tốc ,
lob logical reads 0 tính năng , lob physical reads 0 tối ưu , lob read-ahead reads 0 thanh toán .
(1 row(s) affected)
(12 row(s) affected)
Table "SalesOrderDetail" tính năng . Scan count 1 cập nhật , logical reads 3 an toàn , physical reads 0 phải làm sao , read-ahead reads 0 tính năng ,
lob logical reads 0 tất toán , lob physical reads 0 nguyên nhân , lob read-ahead reads 0 tính năng .
(1 row(s) affected)
danh sách Khi nhìn kết quả trên tính năng , ta có thể thấy hiệu suất ở đâu tốt của SP lưu trữ đa năng đầu tiên là 1 lần quét và 264 lần đọc logic giả mạo . Ngược lại phiên bản V2 có cùng số lần quét chỉ mục dịch vụ nhưng chỉ cần thực hiện 3 lần đọc logic an toàn để xử lý truy vấn dữ liệu . Khoản I/O tiết kiệm đc là 261 kích hoạt . Con số này có vẻ không thấm tháp gì kích hoạt , tuy nhiên qua mạng với trường hợp bạn phải gọi đi gọi lại SP trong một vòng lặp nào đó chẳng hạn download , hiệu suất tốt nhất sẽ bản quyền được cải thiện một cách rõ rệt giữa hai phiên bản SP.
Cải thiện lượng I/O nhờ sử dụng SQL động ứng dụng được thông số hóa
Sau khi đọc hết phần này download , bạn cần hiểu nơi nào được lý do vì sao máy chủ SQL lại đưa ra bản sơ đồ thực thi kém hiệu quả hay nhất . Trên đây máy chủ SQL đã coi logic “@PARM IS NULL” như một hằng số sử dụng . Bởi vậy nó quyết định cần phải thực hiện thao tác “quét chỉ mục” ở đâu tốt để xử lý phiên bản thủ tục lưu trữ đa năng đầu tiên bản quyền . Như chúng ta đã biết xóa tài khoản , thao tác quét (SCAN) luôn chậm hơn thao tác tìm kiếm (SEEK) tối ưu . Bằng cách viết lại phiên bản SP lưu trữ đa năng V2 có sử dụng T-SQL động miễn phí , tôi đã loại bỏ tối ưu được biểu thức hằng số trong mệnh đề WHERE tốt nhất của câu lệnh T-SQL chi tiết . Nhờ vậy máy chủ SQL đã tìm dịch vụ được phương pháp đúng đắn hơn đó là sử dụng thao tác “tìm kiếm chỉ mục cụm” giảm giá . quản lý Nếu trang web an toàn của bạn có sử dụng thủ tục lưu trữ đa năng link down , hãy thử viết lại nó bằng SQL động quảng cáo được thông số hóa và chờ xem hiệu suất quản lý sẽ download được cải thiện thế nào.
4.9/5 (69 votes)
