Một trong những tính năng chính của cơ sở dữ liệu SQL là hỗ trợ cho các truy vấn đặc biệt. truy vấn mới có thể được thực hiện bất cứ lúc nào. Điều này chỉ có thể thực hiện được vì [công cụ lập kế hoạch truy vấn] hoạt động trong thời gian chạy; . Chi phí do tối ưu hóa thời gian chạy giới thiệu có thể được giảm thiểu bằng các tham số liên kết
Ý chính của bản tóm tắt đó là cơ sở dữ liệu được tối ưu hóa cho SQL động—vì vậy hãy sử dụng nó nếu bạn cần
Thay mặt tôi
Tôi kiếm sống từ việc đào tạo SQL, điều chỉnh và tư vấn SQL và cuốn sách của tôi “Giải thích về hiệu suất SQL”. Tìm hiểu thêm tại https. // winand. tại/
Tuy nhiên, có một phương pháp được sử dụng rộng rãi là tránh SQL động để ưu tiên sử dụng SQL tĩnh—thường là do quan niệm sai lầm “SQL động chậm”. Thực tiễn này có hại nhiều hơn có lợi nếu cơ sở dữ liệu sử dụng bộ đệm kế hoạch thực thi được chia sẻ như DB2, cơ sở dữ liệu Oracle hoặc SQL Server
Để minh họa, hãy tưởng tượng một ứng dụng truy vấn bảng EMPLOYEES
. Ứng dụng này cho phép tìm kiếm id công ty con, id nhân viên và họ [không phân biệt chữ hoa chữ thường] trong bất kỳ kết hợp nào. Vẫn có thể viết một truy vấn bao gồm tất cả các trường hợp bằng cách sử dụng logic “thông minh”
SELECT first_name, last_name, subsidiary_id, employee_id
FROM employees
WHERE [ subsidiary_id = :sub_id OR :sub_id IS NULL ]
AND [ employee_id = :emp_id OR :emp_id IS NULL ]
AND [ UPPER[last_name] = :name OR :name IS NULL ]
Truy vấn sử dụng các biến liên kết có tên để dễ đọc hơn. Tất cả các biểu thức bộ lọc có thể được mã hóa tĩnh trong câu lệnh. Bất cứ khi nào không cần bộ lọc, bạn chỉ cần sử dụng NULL
thay vì cụm từ tìm kiếm. nó vô hiệu hóa điều kiện thông qua logic OR
Đó là một câu lệnh SQL hoàn toàn hợp lý. Việc sử dụng NULL
thậm chí còn phù hợp với định nghĩa của nó theo logic ba giá trị của SQL. Tuy nhiên, nó là một trong những mô hình chống hiệu suất tồi tệ nhất trong tất cả
Cơ sở dữ liệu không thể tối ưu hóa kế hoạch thực hiện cho một bộ lọc cụ thể vì bất kỳ bộ lọc nào trong số chúng có thể bị hủy khi chạy. Cơ sở dữ liệu cần chuẩn bị cho trường hợp xấu nhất—nếu tất cả các bộ lọc bị tắt
----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 478 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 2 | 478 |
----------------------------------------------------
Predicate Information [identified by operation id]:
---------------------------------------------------
1 - filter[[:NAME IS NULL OR UPPER["LAST_NAME"]=:NAME]
AND [:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID]
AND [:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID]]
Kết quả là, cơ sở dữ liệu sử dụng quét toàn bộ bảng ngay cả khi có một chỉ mục cho mỗi cột
Không phải là cơ sở dữ liệu không thể giải quyết logic “thông minh”. Nó tạo kế hoạch thực thi chung do sử dụng các tham số liên kết để có thể lưu vào bộ đệm và sử dụng lại với các giá trị khác sau này. Nếu chúng ta không sử dụng các tham số liên kết mà viết các giá trị thực trong câu lệnh SQL, trình tối ưu hóa sẽ chọn chỉ mục thích hợp cho bộ lọc hoạt động
SELECT first_name, last_name, subsidiary_id, employee_id
FROM employees
WHERE[ subsidiary_id = NULL OR NULL IS NULL ]
AND[ employee_id = NULL OR NULL IS NULL ]
AND[ UPPER[last_name] = 'WINAND' OR 'WINAND' IS NULL ]
---------------------------------------------------------------
|Id | Operation | Name | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 |
|*2 | INDEX RANGE SCAN | EMP_UP_NAME | 1 | 1 |
---------------------------------------------------------------
Predicate Information [identified by operation id]:
---------------------------------------------------
2 - access[UPPER["LAST_NAME"]='WINAND']
Tuy nhiên, đây không phải là giải pháp. Nó chỉ chứng minh rằng cơ sở dữ liệu có thể giải quyết các điều kiện này
Cảnh báo
Việc sử dụng các giá trị theo nghĩa đen khiến ứng dụng của bạn dễ bị tấn công bằng SQL injection và có thể gây ra các vấn đề về hiệu suất do chi phí tối ưu hóa tăng lên
Giải pháp rõ ràng cho các truy vấn động là SQL động. Theo nguyên tắc KISS, chỉ cần nói với cơ sở dữ liệu những gì bạn cần ngay bây giờ—và không cần gì khác
SELECT first_name, last_name, subsidiary_id, employee_id
FROM employees
WHERE UPPER[last_name] = :name
Lưu ý rằng truy vấn sử dụng tham số liên kết
Mẹo
Sử dụng SQL động nếu bạn cần mệnh đề where
động
Vẫn sử dụng các tham số liên kết khi tạo SQL động—nếu không thì huyền thoại “SQL động chậm” trở thành sự thật
Vấn đề được mô tả trong phần này là phổ biến. Tất cả các cơ sở dữ liệu sử dụng bộ đệm kế hoạch thực thi được chia sẻ đều có một tính năng để đối phó với nó—thường gây ra các sự cố và lỗi mới
DB2
DB2 sử dụng bộ nhớ đệm kế hoạch thực thi dùng chung và hoàn toàn gặp phải sự cố được mô tả trong phần này
DB2 cho phép chỉ định phương pháp tái tối ưu hóa bằng cách sử dụng gợi ý
----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 478 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 2 | 478 |
----------------------------------------------------
Predicate Information [identified by operation id]:
---------------------------------------------------
1 - filter[[:NAME IS NULL OR UPPER["LAST_NAME"]=:NAME]
AND [:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID]
AND [:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID]]
0. Giá trị mặc định là ----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 478 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 2 | 478 |
----------------------------------------------------
Predicate Information [identified by operation id]:
---------------------------------------------------
1 - filter[[:NAME IS NULL OR UPPER["LAST_NAME"]=:NAME]
AND [:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID]
AND [:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID]]
1, tạo ra một kế hoạch thực hiện chung và gặp sự cố được mô tả ở trên. ----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 478 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 2 | 478 |
----------------------------------------------------
Predicate Information [identified by operation id]:
---------------------------------------------------
1 - filter[[:NAME IS NULL OR UPPER["LAST_NAME"]=:NAME]
AND [:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID]
AND [:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID]]
2 sẽ yêu cầu trình tối ưu hóa luôn theo dõi các biến liên kết thực tế để đưa ra kế hoạch tốt nhất cho mỗi lần thực hiện. Đó là tắt hiệu quả bộ nhớ đệm kế hoạch thực hiện cho câu lệnh đóTùy chọn cuối cùng là
----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 478 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 2 | 478 |
----------------------------------------------------
Predicate Information [identified by operation id]:
---------------------------------------------------
1 - filter[[:NAME IS NULL OR UPPER["LAST_NAME"]=:NAME]
AND [:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID]
AND [:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID]]
3 sẽ chỉ hiển thị các tham số liên kết cho lần thực hiện đầu tiên. Vấn đề với phương pháp này là hành vi không xác định của nó. các giá trị từ lần thực hiện đầu tiên ảnh hưởng đến tất cả các lần thực hiện. Kế hoạch thực hiện có thể thay đổi bất cứ khi nào cơ sở dữ liệu được khởi động lại hoặc, ít dự đoán hơn, kế hoạch được lưu trong bộ nhớ cache hết hạn và trình tối ưu hóa sẽ tạo lại nó bằng các giá trị khác nhau vào lần tiếp theo câu lệnh được thực thimysqlMySQL không gặp phải vấn đề cụ thể này vì nó không có bộ đệm kế hoạch thực thi nào cả. Một yêu cầu tính năng từ năm 2009 thảo luận về tác động của bộ nhớ đệm kế hoạch thực hiện. Có vẻ như trình tối ưu hóa của MySQL đủ đơn giản để bộ nhớ đệm kế hoạch thực hiện không thành công
Cơ sở dữ liệu Oracle sử dụng bộ đệm kế hoạch thực thi dùng chung [“vùng SQL”] và hoàn toàn gặp phải sự cố được mô tả trong phần này
Oracle đã giới thiệu cái gọi là nhìn trộm liên kết với bản phát hành 9i. Nhìn trộm liên kết cho phép trình tối ưu hóa sử dụng các giá trị liên kết thực tế của lần thực thi đầu tiên khi chuẩn bị kế hoạch thực hiện. Vấn đề với phương pháp này là hành vi không xác định của nó. các giá trị từ lần thực hiện đầu tiên ảnh hưởng đến tất cả các lần thực hiện. Kế hoạch thực hiện có thể thay đổi bất cứ khi nào cơ sở dữ liệu được khởi động lại hoặc, ít dự đoán hơn, kế hoạch được lưu trong bộ nhớ cache hết hạn và trình tối ưu hóa sẽ tạo lại nó bằng các giá trị khác nhau vào lần tiếp theo câu lệnh được thực thi
Bản phát hành 11g đã giới thiệu tính năng chia sẻ con trỏ thích ứng để cải thiện hơn nữa tình hình. Tính năng này cho phép cơ sở dữ liệu lưu trữ nhiều kế hoạch thực thi cho cùng một câu lệnh SQL. Hơn nữa, trình tối ưu hóa xem trước các tham số liên kết và lưu trữ tính chọn lọc ước tính của chúng cùng với kế hoạch thực hiện. Khi bộ đệm được truy cập sau đó, tính chọn lọc của các giá trị liên kết hiện tại phải nằm trong phạm vi chọn lọc của kế hoạch thực hiện được lưu trong bộ đệm để được sử dụng lại. Mặt khác, trình tối ưu hóa sẽ tạo một kế hoạch thực hiện mới và so sánh nó với các kế hoạch thực hiện đã được lưu trong bộ nhớ cache cho truy vấn này. Nếu đã có một kế hoạch thực hiện như vậy, cơ sở dữ liệu sẽ thay thế nó bằng một kế hoạch thực hiện mới cũng bao gồm các ước tính chọn lọc của các giá trị liên kết hiện tại. Nếu không, nó sẽ lưu vào bộ nhớ đệm một biến thể kế hoạch thực hiện mới cho truy vấn này — tất nhiên là cùng với các ước tính về tính chọn lọc
PostgreSQLBộ nhớ đệm kế hoạch truy vấn PostgreSQL chỉ hoạt động với các câu lệnh đang mở—điều đó miễn là bạn vẫn mở
----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 478 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 2 | 478 |
----------------------------------------------------
Predicate Information [identified by operation id]:
---------------------------------------------------
1 - filter[[:NAME IS NULL OR UPPER["LAST_NAME"]=:NAME]
AND [:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID]
AND [:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID]]
4. Sự cố được mô tả ở trên chỉ xảy ra khi sử dụng lại một điều khiển câu lệnh. Lưu ý rằng trình điều khiển JDBC của PostgresSQL chỉ kích hoạt bộ đệm sau lần thực thi thứ năm. Xem thêm. Lập kế hoạch với các giá trị ràng buộc thực tếMáy chủ SQLSQL Server sử dụng cái gọi là đánh hơi tham số. Đánh hơi tham số cho phép trình tối ưu hóa sử dụng các giá trị liên kết thực tế của lần thực thi đầu tiên trong quá trình phân tích cú pháp. Vấn đề với phương pháp này là hành vi không xác định của nó. các giá trị từ lần thực hiện đầu tiên ảnh hưởng đến tất cả các lần thực hiện. Kế hoạch thực hiện có thể thay đổi bất cứ khi nào cơ sở dữ liệu được khởi động lại hoặc, ít dự đoán hơn, kế hoạch được lưu trong bộ nhớ cache hết hạn và trình tối ưu hóa sẽ tạo lại nó bằng các giá trị khác nhau vào lần tiếp theo câu lệnh được thực thi
SQL Server 2005 đã thêm các gợi ý truy vấn mới để có thêm quyền kiểm soát đối với việc dò tham số và biên dịch lại. Gợi ý truy vấn
----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 478 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 2 | 478 |
----------------------------------------------------
Predicate Information [identified by operation id]:
---------------------------------------------------
1 - filter[[:NAME IS NULL OR UPPER["LAST_NAME"]=:NAME]
AND [:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID]
AND [:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID]]
5 bỏ qua bộ đệm kế hoạch cho câu lệnh đã chọn. ----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 478 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 2 | 478 |
----------------------------------------------------
Predicate Information [identified by operation id]:
---------------------------------------------------
1 - filter[[:NAME IS NULL OR UPPER["LAST_NAME"]=:NAME]
AND [:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID]
AND [:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID]]
6 cho phép đặc tả các giá trị tham số thực tế chỉ được sử dụng để tối ưu hóa. Cuối cùng, bạn có thể cung cấp toàn bộ kế hoạch thực hiện với gợi ý ----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 478 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 2 | 478 |
----------------------------------------------------
Predicate Information [identified by operation id]:
---------------------------------------------------
1 - filter[[:NAME IS NULL OR UPPER["LAST_NAME"]=:NAME]
AND [:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID]
AND [:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID]]
7Quá trình triển khai ban đầu của gợi ý
----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 478 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 2 | 478 |
----------------------------------------------------
Predicate Information [identified by operation id]:
---------------------------------------------------
1 - filter[[:NAME IS NULL OR UPPER["LAST_NAME"]=:NAME]
AND [:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID]
AND [:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID]]
8 có một lỗi nên nó không xem xét tất cả các biến liên kết. Việc triển khai mới được giới thiệu với SQL Server 2008 có một lỗi khác, khiến tình hình trở nên rất khó hiểu. Erland Sommarskog đã thu thập tất cả thông tin liên quan về tất cả các bản phát hành SQL ServerMặc dù các phương pháp heuristic có thể cải thiện vấn đề “logic thông minh” ở một mức độ nhất định, nhưng chúng thực sự được xây dựng để xử lý các vấn đề về tham số liên kết liên quan đến biểu đồ cột và biểu thức
----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 478 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 2 | 478 |
----------------------------------------------------
Predicate Information [identified by operation id]:
---------------------------------------------------
1 - filter[[:NAME IS NULL OR UPPER["LAST_NAME"]=:NAME]
AND [:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID]
AND [:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID]]
9Phương pháp đáng tin cậy nhất để đạt được kế hoạch thực thi tốt nhất là tránh các bộ lọc không cần thiết trong câu lệnh SQL