Tôi có hai bảng có cùng một khóa chính, nhưng một bảng lớn hơn nhiều so với bản kia. Tôi muốn biết ID nào có một hàng trong bảng nhỏ hơn. [Trong ví dụ,
SELECT a.id,NOT ISNULL[b.id] AS exists
FROM a LEFT OUTER JOIN b
ON a.id=b.id;
4 là lớn và SELECT a.id,NOT ISNULL[b.id] AS exists
FROM a LEFT OUTER JOIN b
ON a.id=b.id;
5 là nhỏ]. Ngay bây giờ, tôi đang sử dụng tham gia bên ngoài với một trường hợp để xác định xem giá trị B có null hay không. Nó không hoạt động [luôn luôn nhận được 1]. Khắc phục điều này sẽ ổn, nhưng phải có một cách tốt hơn. Tôi nên làm như thế nào?SELECT a.id,
CASE b.id
WHEN NULL THEN 0
ELSE 1
END AS exists
FROM a LEFT OUTER JOIN b
ON a.id=b.id;
hỏi ngày 29 tháng 7 năm 2011 lúc 21:08Jul 29, 2011 at 21:08
JMilloyjmilloyjmilloy
7,44610 Huy hiệu vàng 50 Huy hiệu bạc85 Huy hiệu Đồng10 gold badges50 silver badges85 bronze badges
Điều này có cùng logic về những gì bạn đã thể hiện nhưng có mã ngắn hơn:
SELECT a.id,NOT ISNULL[b.id] AS exists
FROM a LEFT OUTER JOIN b
ON a.id=b.id;
Đã trả lời ngày 29 tháng 7 năm 2011 lúc 21:17Jul 29, 2011 at 21:17
DalendalenDalen
8.7164 Huy hiệu vàng46 Huy hiệu bạc52 Huy hiệu Đồng4 gold badges46 silver badges52 bronze badges
0
Không. Kiểm tra
SELECT a.id,NOT ISNULL[b.id] AS exists
FROM a LEFT OUTER JOIN b
ON a.id=b.id;
6 trong [các] cột khóa nước ngoài chính xác là cách bạn làm điều này.Tuy nhiên, không có gì bằng
SELECT a.id,NOT ISNULL[b.id] AS exists
FROM a LEFT OUTER JOIN b
ON a.id=b.id;
6 [đó không phải là một giá trị], đó là lý do tại sao SELECT a.id,NOT ISNULL[b.id] AS exists
FROM a LEFT OUTER JOIN b
ON a.id=b.id;
8 của bạn đi đến phần SELECT a.id,NOT ISNULL[b.id] AS exists
FROM a LEFT OUTER JOIN b
ON a.id=b.id;
9. Bạn cần sử dụng CASE WHEN b.id IS NULL THEN ...
0 để kiểm tra xem một cột là SELECT a.id,NOT ISNULL[b.id] AS exists
FROM a LEFT OUTER JOIN b
ON a.id=b.id;
6.CASE WHEN b.id IS NULL THEN ...
Đã trả lời ngày 29 tháng 7 năm 2011 lúc 21:13Jul 29, 2011 at 21:13
Dan Grossmandan GrossmanDan Grossman
51.2k10 Huy hiệu vàng109 Huy hiệu bạc98 Huy hiệu đồng10 gold badges109 silver badges98 bronze badges
0
Sự khác biệt lớn nhất không phải là tham gia vs không tồn tại, đó là [như đã viết],
CASE WHEN b.id IS NULL THEN ...
2.Trong ví dụ đầu tiên, bạn nhận được tất cả các cột từ cả
CASE WHEN b.id IS NULL THEN ...
3 và CASE WHEN b.id IS NULL THEN ...
4, trong khi trong ví dụ thứ hai, bạn chỉ nhận được các cột từ CASE WHEN b.id IS NULL THEN ...
3.Trong SQL Server, biến thể thứ hai nhanh hơn một chút trong một ví dụ rất đơn giản:
Tạo hai bảng mẫu:
CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
Chèn 10.000 hàng vào mỗi bảng:
INSERT INTO dbo.A DEFAULT VALUES;
GO 10000
INSERT INTO dbo.B DEFAULT VALUES;
GO 10000
Xóa mỗi hàng thứ 5 khỏi bảng thứ hai:
DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT[*] -- shows 10,000
FROM dbo.A;
SELECT COUNT[*] -- shows 8,000
FROM dbo.B;
Thực hiện hai biến thể câu lệnh
CASE WHEN b.id IS NULL THEN ...
6:SELECT *
FROM dbo.A
LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;
SELECT *
FROM dbo.A
WHERE NOT EXISTS [SELECT 1
FROM dbo.B
WHERE b.B_ID = a.A_ID];
Kế hoạch thực thi:
Biến thể thứ hai không cần thực hiện thao tác bộ lọc vì nó có thể sử dụng toán tử tham gia chống SEMI trái.
Phương pháp nào là tốt nhất để chọn các giá trị có trong một bảng nhưng thiếu trong một phương pháp khác?
This:
SELECT l.* FROM t_left l LEFT JOIN t_right r ON r.value = l.value WHERE r.value IS NULL, đây:
SELECT l.* FROM t_left l WHERE l.value NOT IN [ SELECT value FROM t_right r ]hoặc cái này:
SELECT l.* FROM t_left l WHERE NOT EXISTS [ SELECT NULL FROM t_right r WHERE r.value = l.value ]
Cuối cùng, đó là thời gian MySQL.MySQL time.
Như mọi khi, chúng tôi sẽ tạo các bảng mẫu:
Chi tiết tạo bảng
Bảng
CASE WHEN b.id IS NULL THEN ...
7 chứa 100.000 hàng với 10.000 giá trị riêng biệt.100,000 rows with 10,000 distinct values.Bảng
CASE WHEN b.id IS NULL THEN ...
8 chứa 1.000.000 hàng với 10.000 giá trị riêng biệt.1,000,000 rows with 10,000 distinct values.Có 10 hàng trong
CASE WHEN b.id IS NULL THEN ...
7 với các giá trị không có trong CASE WHEN b.id IS NULL THEN ...
8.10 rows in CASE WHEN b.id IS NULL THEN ...
7 with values not present in CASE WHEN b.id IS NULL THEN ...
8.Trong cả hai bảng, trường
CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
1 được lập chỉ mục.Tham gia trái / là null
SELECT a.id,NOT ISNULL[b.id] AS exists
FROM a LEFT OUTER JOIN b
ON a.id=b.id;
0Xem kết quả truy vấn và kế hoạch thực hiện
Truy vấn trả về kết quả chính xác trong 0,73 giây.0.73 seconds.
Nếu chúng ta xem xét phần bổ sung của kế hoạch thực hiện, chúng ta sẽ thấy một điều thú vị ở đó:Extra part of the execution plan, we will see an interesting thing there:
CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
2Nó có nghĩa là gì?
Tài liệu MySQL đề cập đến điều này: documentation mentions this:
3CREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
MySQL đã có thể thực hiện tối ưu hóa
4 trên truy vấn và không kiểm tra nhiều hàng hơn trong bảng này cho kết hợp hàng trước sau khi tìm thấy một hàng phù hợp với tiêu chíCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
4. Dưới đây là một ví dụ về loại truy vấn có thể được tối ưu hóa theo cách này: was able to do aCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
4 optimization on the query and does not examine more rows in this table for the previous row combination after it finds one row that matches theCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
4 criteria. Here is an example of the type of query that can be optimized this way:CREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
1SELECT a.id,NOT ISNULL[b.id] AS exists FROM a LEFT OUTER JOIN b ON a.id=b.id;
Giả sử rằng
6 được định nghĩa làCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
7. Trong trường hợp này, MySQL quétCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
8 và tìm kiếm các hàng trongCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
9 bằng cách sử dụng các giá trị củaCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
0. Nếu MySQL tìm thấy một hàng phù hợp trongINSERT INTO dbo.A DEFAULT VALUES; GO 10000 INSERT INTO dbo.B DEFAULT VALUES; GO 10000
9, nó sẽ biết rằngCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
6 không bao giờ có thể làCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
6 và không quét qua các hàng còn lại trongSELECT a.id,NOT ISNULL[b.id] AS exists FROM a LEFT OUTER JOIN b ON a.id=b.id;
9 có cùng giá trị ID. Nói cách khác, với mỗi hàng trongCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
8, MySQL chỉ cần thực hiện một lần tra cứu duy nhất trongCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
9, bất kể có bao nhiêu hàng thực sự khớp trongCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
9.MySQL scansCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
8 and looks up the rows inCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
9 using the values ofCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
0. If MySQL finds a matching row inINSERT INTO dbo.A DEFAULT VALUES; GO 10000 INSERT INTO dbo.B DEFAULT VALUES; GO 10000
9, it knows thatCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
6 can never beCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
6, and does not scan through the rest of the rows inSELECT a.id,NOT ISNULL[b.id] AS exists FROM a LEFT OUTER JOIN b ON a.id=b.id;
9 that have the same id value. In other words, for each row inCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
8, MySQL needs to do only a single lookup inCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
9, regardless of how many rows actually match inCREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
9.CREATE TABLE dbo.A [ A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; CREATE TABLE dbo.B [ B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY[1,1] ]; GO
Đây chính xác là trường hợp của chúng tôi.
MySQL, cũng như tất cả các hệ thống khác ngoại trừ SQL Server, có thể tối ưu hóa
8 để trả về INSERT INTO dbo.A DEFAULT VALUES;
GO 10000
INSERT INTO dbo.B DEFAULT VALUES;
GO 10000
9 vì sớm tìm thấy giá trị phù hợp và đó là hệ thống duy nhất quan tâm để ghi lại hành vi này., as well as all other systems except SQL Server, is able to optimize INSERT INTO dbo.A DEFAULT VALUES;
GO 10000
INSERT INTO dbo.B DEFAULT VALUES;
GO 10000
INSERT INTO dbo.A DEFAULT VALUES;
GO 10000
INSERT INTO dbo.B DEFAULT VALUES;
GO 10000
8 to return INSERT INTO dbo.A DEFAULT VALUES;
GO 10000
INSERT INTO dbo.B DEFAULT VALUES;
GO 10000
9 as soon the matching value is found, and it is the only system that cared to document this behavior.Tuy nhiên, giả định rằng
CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
6 nên được định nghĩa là CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
7, tuy nhiên, quá mạnh, vì thành công DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT[*] -- shows 10,000
FROM dbo.A;
SELECT COUNT[*] -- shows 8,000
FROM dbo.B;
2 về điều kiện bình đẳng ngụ ý rằng giá trị được tìm thấy là ____37.Vì MySQL không có khả năng sử dụng các thuật toán tham gia
DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT[*] -- shows 10,000
FROM dbo.A;
SELECT COUNT[*] -- shows 8,000
FROM dbo.B;
4 và DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT[*] -- shows 10,000
FROM dbo.A;
SELECT COUNT[*] -- shows 8,000
FROM dbo.B;
5, nên DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT[*] -- shows 10,000
FROM dbo.A;
SELECT COUNT[*] -- shows 8,000
FROM dbo.B;
6 duy nhất có khả năng là DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT[*] -- shows 10,000
FROM dbo.A;
SELECT COUNT[*] -- shows 8,000
FROM dbo.B;
7, đó chính xác là những gì chúng ta thấy trong kế hoạch truy vấn [mặc dù thực tế là MySQL không gọi nó là]. Tuy nhiên, hành vi này là những gì một DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT[*] -- shows 10,000
FROM dbo.A;
SELECT COUNT[*] -- shows 8,000
FROM dbo.B;
6 thực hiện: nó kiểm tra các giá trị từ bảng bên trái chỉ so với một trong mỗi giá trị riêng biệt trong bảng bên phải, bỏ qua các bản sao.MySQL is not capable of using DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT[*] -- shows 10,000
FROM dbo.A;
SELECT COUNT[*] -- shows 8,000
FROM dbo.B;
4 and DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT[*] -- shows 10,000
FROM dbo.A;
SELECT COUNT[*] -- shows 8,000
FROM dbo.B;
5 join algorithms, the only DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT[*] -- shows 10,000
FROM dbo.A;
SELECT COUNT[*] -- shows 8,000
FROM dbo.B;
6 it is capable of is the DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT[*] -- shows 10,000
FROM dbo.A;
SELECT COUNT[*] -- shows 8,000
FROM dbo.B;
7, which is exactly what we see in the query plan [despite the fact that MySQL doesn't call it that]. However, this behavior is what an DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT[*] -- shows 10,000
FROM dbo.A;
SELECT COUNT[*] -- shows 8,000
FROM dbo.B;
6 does: it checks the values from the left table against only one of each distinct values in the right table, skipping the duplicates.Không phải vào
SELECT a.id,NOT ISNULL[b.id] AS exists
FROM a LEFT OUTER JOIN b
ON a.id=b.id;
2Xem kết quả truy vấn và kế hoạch thực hiện
Truy vấn trả về kết quả chính xác trong 0,73 giây.
Nếu chúng ta xem xét phần bổ sung của kế hoạch thực hiện, chúng ta sẽ thấy một điều thú vị ở đó:
Nó có nghĩa là gì?
SELECT *
FROM dbo.A
LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;
SELECT *
FROM dbo.A
WHERE NOT EXISTS [SELECT 1
FROM dbo.B
WHERE b.B_ID = a.A_ID];
4Tài liệu MySQL đề cập đến điều này:
MySQL đã có thể thực hiện tối ưu hóa
CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
4 trên truy vấn và không kiểm tra nhiều hàng hơn trong bảng này cho kết hợp hàng trước sau khi tìm thấy một hàng phù hợp với tiêu chí CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
4. Dưới đây là một ví dụ về loại truy vấn có thể được tối ưu hóa theo cách này:MySQL applies SELECT *
FROM dbo.A
LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;
SELECT *
FROM dbo.A
WHERE NOT EXISTS [SELECT 1
FROM dbo.B
WHERE b.B_ID = a.A_ID];
6 optimization to the subquery: it uses the index scan over SELECT *
FROM dbo.A
LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;
SELECT *
FROM dbo.A
WHERE NOT EXISTS [SELECT 1
FROM dbo.B
WHERE b.B_ID = a.A_ID];
7 and returns as soon as it finds [or not finds] a row.Giả sử rằng
CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
6 được định nghĩa là CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
7. Trong trường hợp này, MySQL quét CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
8 và tìm kiếm các hàng trong CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
9 bằng cách sử dụng các giá trị của INSERT INTO dbo.A DEFAULT VALUES;
GO 10000
INSERT INTO dbo.B DEFAULT VALUES;
GO 10000
0. Nếu MySQL tìm thấy một hàng phù hợp trong CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
9, nó sẽ biết rằng CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
6 không bao giờ có thể là SELECT a.id,NOT ISNULL[b.id] AS exists
FROM a LEFT OUTER JOIN b
ON a.id=b.id;
6 và không quét qua các hàng còn lại trong CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
9 có cùng giá trị ID. Nói cách khác, với mỗi hàng trong CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
8, MySQL chỉ cần thực hiện một lần tra cứu duy nhất trong CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
9, bất kể có bao nhiêu hàng thực sự khớp trong CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
9.MySQL takes this into account.Đây chính xác là trường hợp của chúng tôi.
MySQL, cũng như tất cả các hệ thống khác ngoại trừ SQL Server, có thể tối ưu hóa INSERT INTO dbo.A DEFAULT VALUES;
GO 10000
INSERT INTO dbo.B DEFAULT VALUES;
GO 10000
8 để trả về INSERT INTO dbo.A DEFAULT VALUES;
GO 10000
INSERT INTO dbo.B DEFAULT VALUES;
GO 10000
9 vì sớm tìm thấy giá trị phù hợp và đó là hệ thống duy nhất quan tâm để ghi lại hành vi này.
INSERT INTO dbo.A DEFAULT VALUES;
GO 10000
INSERT INTO dbo.B DEFAULT VALUES;
GO 10000
INSERT INTO dbo.A DEFAULT VALUES;
GO 10000
INSERT INTO dbo.B DEFAULT VALUES;
GO 10000
SELECT a.id,NOT ISNULL[b.id] AS exists
FROM a LEFT OUTER JOIN b
ON a.id=b.id;
3Xem kết quả truy vấn và kế hoạch thực hiện
Truy vấn trả về kết quả chính xác trong 0,73 giây.
Nếu chúng ta xem xét phần bổ sung của kế hoạch thực hiện, chúng ta sẽ thấy một điều thú vị ở đó:MySQL is the only system that produces three different plans for three different methods.
Kế hoạch không khác nhau nhiều: MySQL biết tra cứu chỉ mục là gì và
SELECT *
FROM dbo.A
LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;
SELECT *
FROM dbo.A
WHERE NOT EXISTS [SELECT 1
FROM dbo.B
WHERE b.B_ID = a.A_ID];
6 là gì và nó kết hợp chúng với nhau.MySQL does know what an
index lookup is and what SELECT *
FROM dbo.A
LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;
SELECT *
FROM dbo.A
WHERE NOT EXISTS [SELECT 1
FROM dbo.B
WHERE b.B_ID = a.A_ID];
6 is and it does combine them together.SELECT *
FROM dbo.A
LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;
SELECT *
FROM dbo.A
WHERE NOT EXISTS [SELECT 1
FROM dbo.B
WHERE b.B_ID = a.A_ID];
6 trong MySQL được tối ưu hóa để nó trả về ngay khi tìm thấy giá trị đầu tiên. Vì vậy, truy vấn này trong thực tế là một DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT[*] -- shows 10,000
FROM dbo.A;
SELECT COUNT[*] -- shows 8,000
FROM dbo.B;
6 cũng như hai truy vấn đầu tiên.MySQL is optimized so that it returns as soon as the first value is found. So this query in fact is an DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT[*] -- shows 10,000
FROM dbo.A;
SELECT COUNT[*] -- shows 8,000
FROM dbo.B;
6 as well as first two queries are.Truy vấn này, tuy nhiên, kém hiệu quả hơn một chút so với hai lần trước: phải mất 0,92 giây.0.92 s.
Đây không phải là giảm nhiều hiệu suất, tuy nhiên, truy vấn mất thêm 27% thời gian.27% more time.
Thật khó để nói lý do chính xác cho điều này, vì sự sụt giảm này là tuyến tính và dường như không phụ thuộc vào phân phối dữ liệu, số lượng giá trị trong cả hai bảng, v.v., miễn là cả hai trường được lập chỉ mục. Vì có ba đoạn mã trong MySQL mà Essentialy thực hiện một công việc, nên có thể mã chịu trách nhiệm cho
SELECT *
FROM dbo.A
LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;
SELECT *
FROM dbo.A
WHERE NOT EXISTS [SELECT 1
FROM dbo.B
WHERE b.B_ID = a.A_ID];
6 làm cho một số kiểm tra thêm cần thêm thời gian.MySQL that essentialy do one job, it is possible that the code responsible for SELECT *
FROM dbo.A
LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;
SELECT *
FROM dbo.A
WHERE NOT EXISTS [SELECT 1
FROM dbo.B
WHERE b.B_ID = a.A_ID];
6 makes some kind of an extra check which takes extra time.Bản tóm tắt
MySQL có thể tối ưu hóa cả ba phương pháp để thực hiện một loại
7. can optimize all three methods to do a sort of DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT[*] -- shows 10,000
FROM dbo.A;
SELECT COUNT[*] -- shows 8,000
FROM dbo.B;
DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT[*] -- shows 10,000
FROM dbo.A;
SELECT COUNT[*] -- shows 8,000
FROM dbo.B;
7.Nó sẽ lấy từng giá trị từ
CASE WHEN b.id IS NULL THEN ...
7 và tìm nó trong chỉ mục trên SELECT l.* FROM t_left l LEFT JOIN t_right r ON r.value = l.value WHERE r.value IS NULL1. Trong trường hợp một chỉ số bị truy cập hoặc bỏ lỡ chỉ mục, vị từ tương ứng sẽ ngay lập tức trả về
INSERT INTO dbo.A DEFAULT VALUES;
GO 10000
INSERT INTO dbo.B DEFAULT VALUES;
GO 10000
9 hoặc SELECT l.* FROM t_left l WHERE l.value NOT IN [ SELECT value FROM t_right r ]4, và quyết định trả lại hàng từ
CASE WHEN b.id IS NULL THEN ...
7 hoặc không được thực hiện ngay lập tức mà không kiểm tra các hàng khác trong CASE WHEN b.id IS NULL THEN ...
8.Tuy nhiên, ba phương pháp này tạo ra ba kế hoạch khác nhau được thực hiện bởi ba đoạn mã khác nhau. Mã thực thi
SELECT *
FROM dbo.A
LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;
SELECT *
FROM dbo.A
WHERE NOT EXISTS [SELECT 1
FROM dbo.B
WHERE b.B_ID = a.A_ID];
6 vị ngữ kém hiệu quả hơn khoảng 30% so với mã thực thi SELECT l.* FROM t_left l WHERE l.value NOT IN [ SELECT value FROM t_right r ]8 và
CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
4 được tối ưu hóa để sử dụng phương thức CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
3.30% less efficient than those that execute SELECT l.* FROM t_left l WHERE l.value NOT IN [ SELECT value FROM t_right r ]8 and
CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
4 optimized to use CREATE TABLE dbo.A
[
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
CREATE TABLE dbo.B
[
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY[1,1]
];
GO
3 method.Đó là lý do tại sao cách tốt nhất để tìm kiếm các giá trị bị thiếu trong MySQL là sử dụng
INSERT INTO dbo.A DEFAULT VALUES;
GO 10000
INSERT INTO dbo.B DEFAULT VALUES;
GO 10000
8 hoặc SELECT *
FROM dbo.A
LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;
SELECT *
FROM dbo.A
WHERE NOT EXISTS [SELECT 1
FROM dbo.B
WHERE b.B_ID = a.A_ID];
8 thay vì SELECT l.* FROM t_left l WHERE NOT EXISTS [ SELECT NULL FROM t_right r WHERE r.value = l.value ]3.MySQL is using a
INSERT INTO dbo.A DEFAULT VALUES;
GO 10000
INSERT INTO dbo.B DEFAULT VALUES;
GO 10000
8 or SELECT *
FROM dbo.A
LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;
SELECT *
FROM dbo.A
WHERE NOT EXISTS [SELECT 1
FROM dbo.B
WHERE b.B_ID = a.A_ID];
8 rather than SELECT l.* FROM t_left l WHERE NOT EXISTS [ SELECT NULL FROM t_right r WHERE r.value = l.value ]3.