Hướng dẫn mysql not exists vs not in - mysql không tồn tại so với không trong

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ế.

Cái nào tốt hơn không có hoặc không tồn tại?

Sự khác biệt giữa không và không tồn tại là gì?

Sự khác biệt giữa tồn tại không tồn tại và không phải trong SQL là gì?

Cái nào nhanh hơn không tồn tại hoặc không tồn tại?