Mysql bỏ qua mệnh đề where nếu tham số là null

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 thi

mysql

MySQL 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

tiên tri

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

PostgreSQL

Bộ 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ủ SQL

SQL 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))

7

Quá 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 Server

Mặ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))

9

Phươ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

Bạn có thể sử dụng Isnull trong mệnh đề WHERE không?

Làm cách nào để kiểm tra xem tham số có phải là NULL trong SQL không?

Tham số có thể chấp nhận giá trị NULL không?

Làm cách nào để kiểm tra xem điều kiện có phải là NULL trong mysql không?