Không, họ không giống nhau. Chức năng trong dịch chuyển thành một loạt hoặc các câu lệnh. Thông thường, điều này hướng mọi người lên khi họ sử dụng một trình điều khiển con trong hàm và truy vấn đó trả về ít nhất một giá trị null. Ví dụ. Col Not In[ Select Foo From Bar ]
. Điều này so sánh
Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
0 cho mỗi hàng. Nếu một trong các giá trị của foo là null, bạn sẽ nhận được Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
1 và toàn bộ câu lệnh trả về sai dẫn đến không có hàng.Tồn tại chỉ đơn giản là xác định nếu bất kỳ hàng nào trong truy vấn con được trả về [không tồn tại là không có hàng nào có thể được trả về]. Mệnh đề chọn hoàn toàn bị bỏ qua.
Example:
Create Table Test [ Id int not null ]
Insert Test [ 1 ]
Insert Test [ 2 ]
Insert Test [ 3 ]
Create Table Bar [ Foo int null ]
Insert Bar [ Null ]
Insert Bar [ 1 ]
Insert Bar [ 2 ]
Những điều sau đây sẽ dẫn đến không có hàng vì chúng tôi đang thực hiện việc so sánh
Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
2 một cách hiệu quảSelect Id
From Test
Where Id Not In[ Select Foo From Bar ]
Tuy nhiên, sau đây sẽ trả về một hàng
Select Id
From Test
Where Not Exists[ Select 1 From Bar Where Foo = Id ]
Trong truy vấn trên, tôi sẽ nhận được id = 3.
Khi bạn muốn thực hiện một thao tác khác biệt giữa hai bảng, bạn có một lựa chọn:
Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
3 với một truy vấn con tương quan hoặc Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
4. Cái sau được cho là đơn giản hơn để viết và làm cho ý định của truy vấn rõ ràng hơn. Và các hệ thống cơ sở dữ liệu hiện đại sẽ tối ưu hóa hai truy vấn thành các kế hoạch thực hiện tương tự, xử lý mối tương quan giữa các truy vấn bên ngoài và bên trong [tôi nói là hiện đại vì khi tôi làm việc với Oracle 7.3 vào giữa những năm 90, tôi đã học được ].Có một sự khác biệt chính giữa hai cấu trúc: nếu trình điều khiển con trả về
Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
5 trong kết quả của nó thì điều kiện Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
4 sẽ thất bại, bởi vì NULL không bằng nhau không bằng bất kỳ giá trị nào khác. Nhưng nếu bạn bảo vệ chống lại điều đó, chúng nên tương đương - thực sự, một số nguồn sẽ cho bạn biết rằng Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
4 nhanh hơn và do đó được ưa thích.Bài đăng này là về một trường hợp mà nó chậm hơn đáng kể và Nulls sẽ đổ lỗi.
Xem xét hai bảng sau, có thể được sử dụng để theo dõi dữ liệu ClickStream. Vì chúng tôi theo dõi cả người dùng ẩn danh và người dùng đã đăng ký,
Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
8 là không thể. Tuy nhiên, khi người dùng không phải là NULL, chỉ số thứ cấp có tính điểm cao.create table USERS
[
ID integer auto_increment primary key,
...
]
create table EVENTS
[
ID integer auto_increment primary key,
TYPE smallint not null,
USER_ID integer
...
]
create index EVENTS_USER_IDX on EVENTS[USER_ID];
Ok, bây giờ chúng ta hãy sử dụng các bảng sau: từ một bộ người dùng nhỏ, chúng tôi muốn tìm những bảng chưa có sự kiện cụ thể. Sử dụng
Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
4 và đảm bảo rằng các null không xuất hiện trong kết quả bên trong, truy vấn trông như thế này:select ID
from USERS
where ID in [1, 7, 2431, 87142, 32768]
and ID not in
[
select USER_ID
from EVENTS
where TYPE = 7
and USER_ID is not null
];
Đối với bộ dữ liệu thử nghiệm của tôi, bảng
Select Id
From Test
Where Not Exists[ Select 1 From Bar Where Foo = Id ]
0 có 100.000 hàng và bảng Select Id
From Test
Where Not Exists[ Select 1 From Bar Where Foo = Id ]
1 có 10.000.000, trong đó khoảng 75% có null Select Id
From Test
Where Not Exists[ Select 1 From Bar Where Foo = Id ]
2. Tôi đang chạy trên máy tính xách tay của mình, có bộ xử lý Core i7, RAM 12 GB và SSD.Và tôi liên tục nhận được thời gian dài khoảng 2 phút, đó là Wow Wow.
Hãy thay thế
Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
4 bằng Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
3 và phụ tương quan:select ID
from USERS
where ID in [1, 7, 2431, 87142, 32768]
and not exists
[
select 1
from EVENTS
where USER_ID = USERS.ID
and TYPE = 7
];
Phiên bản này chạy trong 0,01 giây, đó là những gì tôi mong đợi.
Thời gian để so sánh các kế hoạch thực thi. Kế hoạch đầu tiên là từ truy vấn
Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
4, thứ hai là từ Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
3.+----+--------------------+--------+------------+----------------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+--------+------------+----------------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| 1 | PRIMARY | USERS | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 5 | 100.00 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | EVENTS | NULL | index_subquery | EVENTS_USER_IDX | EVENTS_USER_IDX | 5 | func | 195 | 10.00 | Using where |
+----+--------------------+--------+------------+----------------+-----------------+-----------------+---------+------+------+----------+--------------------------+
+----+--------------------+--------+------------+-------+-----------------+-----------------+---------+------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+--------+------------+-------+-----------------+-----------------+---------+------------------+------+----------+--------------------------+
| 1 | PRIMARY | USERS | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 5 | 100.00 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | EVENTS | NULL | ref | EVENTS_USER_IDX | EVENTS_USER_IDX | 5 | example.USERS.ID | 97 | 10.00 | Using where |
+----+--------------------+--------+------------+-------+-----------------+-----------------+---------+------------------+------+----------+--------------------------+
Gần như giống hệt nhau: Cả hai chọn các hàng từ bảng
Select Id
From Test
Where Not Exists[ Select 1 From Bar Where Foo = Id ]
0, sau đó sử dụng tham gia vòng lặp lồng nhau [Subquery phụ thuộc & RQUO;] để lấy các hàng từ bảng Select Id
From Test
Where Not Exists[ Select 1 From Bar Where Foo = Id ]
1. Cả hai tuyên bố sử dụng Select Id
From Test
Where Not Exists[ Select 1 From Bar Where Foo = Id ]
9 để chọn các hàng trong truy vấn con. Và họ ước tính số lượng hàng tương tự ở mỗi bước.Nhưng nhìn kỹ hơn vào các loại tham gia. Phiên bản
Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
4 sử dụng index_subquery, trong khi phiên bản Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
3 sử dụng ref. Ngoài ra, hãy xem cột create table USERS
[
ID integer auto_increment primary key,
...
]
create table EVENTS
[
ID integer auto_increment primary key,
TYPE smallint not null,
USER_ID integer
...
]
create index EVENTS_USER_IDX on EVENTS[USER_ID];
2: Phiên bản Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
3 sử dụng tham chiếu rõ ràng đến cột bên ngoài, trong khi Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
4 sử dụng hàm. Những gì đang xảy ra ở đây?Loại tham gia
create table USERS
[
ID integer auto_increment primary key,
...
]
create table EVENTS
[
ID integer auto_increment primary key,
TYPE smallint not null,
USER_ID integer
...
]
create index EVENTS_USER_IDX on EVENTS[USER_ID];
5 chỉ ra rằng MySQL sẽ quét chỉ mục để tìm các hàng có liên quan cho truy vấn con. Có thể đó là vấn đề? Tôi không nghĩ vậy, bởi vì Select Id
From Test
Where Not Exists[ Select 1 From Bar Where Foo = Id ]
9 là hẹp hẹp: nó chỉ có một cột, vì vậy động cơ không phải đọc nhiều khối để tìm các hàng tương ứng với ID từ truy vấn bên ngoài [thực sự, tôi Đã thử một loạt các truy vấn để thực hiện chỉ số này, và tất cả chạy trong vài phần trăm giây].Để biết thêm thông tin, tôi đã chuyển sang kế hoạch thực hiện mở rộng trên mạng. Để xem kế hoạch này, tiền tố truy vấn với
create table USERS
[
ID integer auto_increment primary key,
...
]
create table EVENTS
[
ID integer auto_increment primary key,
TYPE smallint not null,
USER_ID integer
...
]
create index EVENTS_USER_IDX on EVENTS[USER_ID];
7 và theo dõi nó với create table USERS
[
ID integer auto_increment primary key,
...
]
create table EVENTS
[
ID integer auto_increment primary key,
TYPE smallint not null,
USER_ID integer
...
]
create index EVENTS_USER_IDX on EVENTS[USER_ID];
8. Đây là những gì bạn nhận được từ truy vấn Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
4 [được định dạng lại cho sự rõ ràng]:/* select#1 */ select `example`.`USERS`.`ID` AS `ID`
from `example`.`USERS`
where [[`example`.`USERS`.`ID` in [1,7,2431,87142,32768]]
and [not[
[`example`.`USERS`.`ID`,
[
[
[`example`.`USERS`.`ID`] in EVENTS on EVENTS_USER_IDX checking NULL where [[`example`.`EVENTS`.`TYPE` = 7] and [`example`.`EVENTS`.`USER_ID` is not null]] having
[`example`.`EVENTS`.`USER_ID`]]]]]]]
Tôi đã không thể tìm thấy một lời giải thích về ____ ____40, nhưng đây là những gì tôi nghĩ đang xảy ra: Trình tối ưu hóa tin rằng nó đang thực hiện một truy vấn
select ID
from USERS
where ID in [1, 7, 2431, 87142, 32768]
and ID not in
[
select USER_ID
from EVENTS
where TYPE = 7
and USER_ID is not null
];
1 có thể có Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
5 trong kết quả; Nó không xem xét kiểm tra null trong mệnh đề select ID
from USERS
where ID in [1, 7, 2431, 87142, 32768]
and ID not in
[
select USER_ID
from EVENTS
where TYPE = 7
and USER_ID is not null
];
3 khi đưa ra quyết định này. Do đó, nó sẽ kiểm tra 7,5 triệu hàng trong đó Select Id
From Test
Where Not Exists[ Select 1 From Bar Where Foo = Id ]
2 là null, cùng với vài tá hàng nơi nó phù hợp với các giá trị từ truy vấn bên ngoài. Và bằng cách kiểm tra, tôi có nghĩa là nó sẽ đọc hàng bảng và chỉ sau đó áp dụng điều kiện select ID
from USERS
where ID in [1, 7, 2431, 87142, 32768]
and ID not in
[
select USER_ID
from EVENTS
where TYPE = 7
and USER_ID is not null
];
5. Hơn nữa, dựa trên thời gian để chạy truy vấn, tôi nghĩ rằng nó đang làm điều này cho mỗi một trong số các giá trị ứng cử viên trong truy vấn bên ngoài.Vì vậy, điểm mấu chốt: Bất cứ khi nào bạn nghĩ đến việc sử dụng trình điều khiển
select ID
from USERS
where ID in [1, 7, 2431, 87142, 32768]
and ID not in
[
select USER_ID
from EVENTS
where TYPE = 7
and USER_ID is not null
];
1 hoặc Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
4 trên cột vô hiệu, hãy suy nghĩ lại và sử dụng select ID
from USERS
where ID in [1, 7, 2431, 87142, 32768]
and ID not in
[
select USER_ID
from EVENTS
where TYPE = 7
and USER_ID is not null
];
8 hoặc Select Id
From Test
Where Id Not In[ Select Foo From Bar ]
3 thay thế.