1] Khóa chính của MySQL là một chỉ mục duy nhất. [được đặt tên là 'PRIMARY' và với yêu cầu bổ sung là tất cả các cột của nó phải có ràng buộc NOT NULL]. Chỉ mục duy nhất trên roomID là không cần thiết
Từ mô tả của bạn, tôi đoán rằng tất cả các truy vấn đều tương tự như 'CHỌN * TỪ phòng WHERE roomID=123' ?
2] Nếu tất cả các CHỌN [tôi cho rằng đó là hiệu suất CHỌN mà bạn quan tâm] được thực hiện bằng cách sử dụng 'WHERE roomID=. ' mệnh đề, thì hiệu suất sẽ ổn. Đó là bởi vì phần bù hàng trong tệp dữ liệu có thể được tra cứu thông qua chỉ mục chính, giúp truy cập ngẫu nhiên có thể, ví dụ: sẽ không có quá trình quét tệp dữ liệu quá nhiều. Điều đó có nghĩa là kích thước [số lượng bản ghi] không còn quan trọng. Ngoài ra, hiệu suất chủ yếu tỷ lệ thuận với số lần tìm kiếm đĩa do một truy vấn gây ra. Chỉ mục chính được giữ vĩnh viễn trong RAM trong bộ đệm chính, vì vậy nếu bạn đảm bảo rằng bộ đệm chính đủ lớn để chứa nó, điều đó sẽ không gây ra hoạt động của đĩa. Sau khi tra cứu trong chỉ mục chính, sẽ có một đĩa tìm kiếm để định vị từng hàng có liên quan và một đĩa bổ sung tìm kiếm từng trường văn bản của mỗi hàng [tệ nhất]. Có một chút mức sử dụng CPU để nối các cột TEXT với dữ liệu hàng chính và gửi kết quả tới ứng dụng khách, điều này sẽ rất tối thiểu
Tất nhiên, nếu các truy vấn không thuộc dạng được đề cập ở trên, thì tất cả các cược đều bị tắt ;-]. Bạn có thể sử dụng EXPLAIN SELECT để MySQL cho bạn biết tốc độ thực thi của một câu lệnh cụ thể. Ngoài ra còn có ĐIỂM CHUẨN để thử nghiệm
Cách tiếp cận “mọi thứ đều là tham gia vòng lặp lồng nhau” của MySQL để thực hiện truy vấn không lý tưởng để tối ưu hóa mọi loại truy vấn. May mắn thay, chỉ có một số trường hợp hạn chế mà trình tối ưu hóa truy vấn MySQL thực hiện công việc kém và thường có thể viết lại các truy vấn đó hiệu quả hơn
MySQL đôi khi tối ưu hóa các truy vấn phụ rất tệ. Những người vi phạm tồi tệ nhất là _______ 9 truy vấn phụ trong mệnh đề _______ 1_______0. Ví dụ: hãy tìm tất cả các bộ phim trong bảng
SELECT * FROM sakila.film WHERE1 của cơ sở dữ liệu mẫu Sakila có dàn diễn viên bao gồm nữ diễn viên Penelope Guiness [EXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
SELECT * FROM sakila.film WHERE2]. Điều này cảm thấy tự nhiên khi viết với một truy vấn con, như sauEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
mysql>SELECT * FROM sakila.film
->WHERE film_id IN[
->SELECT film_id FROM sakila.film_actor WHERE actor_id = 1];
Thật hấp dẫn khi nghĩ rằng MySQL sẽ thực hiện truy vấn này từ trong ra ngoài, bằng cách tìm danh sách các giá trị
SELECT * FROM sakila.film WHERE3 và thay thế chúng vào danh sáchEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
-- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];9. Chúng tôi đã nói một danh sách
-- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];9 thường rất nhanh, vì vậy bạn có thể mong đợi truy vấn được tối ưu hóa thành một thứ như thế này
-- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];
Thật không may, chính xác điều ngược lại xảy ra. MySQL cố gắng “trợ giúp” truy vấn con bằng cách đẩy một mối tương quan vào nó từ bảng bên ngoài, điều mà nó cho rằng sẽ cho phép truy vấn con tìm các hàng hiệu quả hơn. Nó viết lại truy vấn như sau
SELECT * FROM sakila.film WHEREEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
Bây giờ, truy vấn con yêu cầu
SELECT * FROM sakila.film WHERE6 từ bảngEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
SELECT * FROM sakila.film WHERE7 bên ngoài và không thể thực hiện trước.EXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
SELECT * FROM sakila.film WHERE8 hiển thị kết quả làEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
SELECT * FROM sakila.film WHERE9 [bạn có thể sử dụngEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
0 để xem chính xác cách viết lại truy vấn]mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
Theo kết quả đầu ra của
SELECT * FROM sakila.film WHERE8, MySQL sẽ quét bảng của bảngEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
SELECT * FROM sakila.film WHERE7 và thực hiện truy vấn con cho mỗi hàng mà nó tìm thấy. Điều này sẽ không gây ra hiệu suất đáng chú ý trên các bảng nhỏ, nhưng nếu bảng bên ngoài rất lớn, hiệu suất sẽ cực kỳ tệ. May mắn thay, thật dễ dàng để viết lại một truy vấn như mộtEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
3mysql>SELECT film.* FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id]
->WHERE actor_id = 1;
Một cách tối ưu hóa tốt khác là tạo danh sách
-- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];9 theo cách thủ công bằng cách thực hiện truy vấn con dưới dạng truy vấn riêng biệt với
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
5. Đôi khi điều này có thể nhanh hơn một mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
3MySQL đã bị chỉ trích kỹ lưỡng về loại kế hoạch thực hiện truy vấn con cụ thể này. Dù nhất định phải sửa nhưng phê bình thường lẫn lộn hai vấn đề khác nhau. thứ tự thực hiện và bộ nhớ đệm. Thực hiện truy vấn từ trong ra ngoài là một cách để tối ưu hóa nó; . Tự viết lại truy vấn cho phép bạn kiểm soát cả hai khía cạnh. Các phiên bản tương lai của MySQL sẽ có thể tối ưu hóa loại truy vấn này tốt hơn nhiều, mặc dù đây không phải là nhiệm vụ dễ dàng. Có những trường hợp xấu nhất rất tồi tệ đối với bất kỳ kế hoạch thực hiện nào, kể cả kế hoạch thực hiện từ trong ra ngoài mà một số người cho rằng sẽ đơn giản để tối ưu hóa
Khi một truy vấn con tương quan là tốt
MySQL không phải lúc nào cũng tối ưu hóa các truy vấn con tương quan kém. Nếu bạn nghe lời khuyên luôn luôn tránh chúng, đừng nghe. Thay vào đó, hãy so sánh và đưa ra quyết định của riêng bạn. Đôi khi một truy vấn con tương quan là một cách hoàn toàn hợp lý, hoặc thậm chí tối ưu, để có được kết quả. Hãy xem một ví dụ
mysql>EXPLAIN SELECT film_id, language_id FROM sakila.film
->WHERE NOT EXISTS[
->SELECT * FROM sakila.film_actor
->WHERE film_actor.film_id = film.film_id
->]\G
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: film.film_id rows: 2 Extra: Using where; Using index
Lời khuyên tiêu chuẩn cho truy vấn này là viết nó dưới dạng
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
7 thay vì sử dụng truy vấn con. Về lý thuyết, kế hoạch thực thi của MySQL về cơ bản sẽ giống nhau. Hãy xem nàomysql>EXPLAIN SELECT film.film_id, film.language_id
->FROM sakila.film
->LEFT OUTER JOIN sakila.film_actor USING[film_id]
->WHERE film_actor.film_id IS NULL\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Using where; Using index;Not exists
Các kế hoạch gần như giống hệt nhau, nhưng có một số khác biệt
Loại
mysql>
8 so vớiEXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+ | id | select_type | table | type | possible_keys | +----+--------------------+------------+--------+------------------------+ | 1 | PRIMARY | film | ALL | NULL | | 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id | +----+--------------------+------------+--------+------------------------+mysql>
9 làEXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+ | id | select_type | table | type | possible_keys | +----+--------------------+------------+--------+------------------------+ | 1 | PRIMARY | film | ALL | NULL | | 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id | +----+--------------------+------------+--------+------------------------+SELECT * FROM sakila.film WHERE
9 trong một truy vấn vàEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
mysql>
1 trong truy vấn kia. Sự khác biệt này chỉ phản ánh cú pháp, bởi vì truy vấn đầu tiên sử dụng truy vấn con và truy vấn thứ hai thì không. Nó không tạo ra nhiều khác biệt về hoạt động xử lýSELECT film.* FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id]
->WHERE actor_id = 1;
Truy vấn thứ hai không nói "Sử dụng ở đâu" trong cột
mysql>
2 cho bảngSELECT film.* FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id]
->WHERE actor_id = 1;
SELECT * FROM sakila.film WHERE
7. Điều đó không quan trọng, mặc dù. dù sao thì mệnh đềEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
mysql>
4 của truy vấn thứ hai cũng giống như mệnh đềSELECT film.* FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id]
->WHERE actor_id = 1;
SELECT * FROM sakila.film WHERE
0EXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
Truy vấn thứ hai cho biết “Không tồn tại” trong cột
mysql>
2 của bảngSELECT film.* FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id]
->WHERE actor_id = 1;
mysql>
9. Đây là một ví dụ về thuật toán kết thúc sớm mà chúng tôi đã đề cập trước đó trong chương này. Điều đó có nghĩa là MySQL đang sử dụng tối ưu hóa không tồn tại để tránh đọc nhiều hơn một hàng trong chỉ mụcEXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+ | id | select_type | table | type | possible_keys | +----+--------------------+------------+--------+------------------------+ | 1 | PRIMARY | film | ALL | NULL | | 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id | +----+--------------------+------------+--------+------------------------+mysql>
9 của bảngSELECT film.* FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id]
->WHERE actor_id = 1;
mysql>
9. Điều này tương đương với truy vấn con tương quanEXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+ | id | select_type | table | type | possible_keys | +----+--------------------+------------+--------+------------------------+ | 1 | PRIMARY | film | ALL | NULL | | 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id | +----+--------------------+------------+--------+------------------------+mysql>
0EXPLAIN SELECT film_id, language_id FROM sakila.film
->WHERE NOT EXISTS[
->SELECT * FROM sakila.film_actor
->WHERE film_actor.film_id = film.film_id
->]\G
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: film.film_id rows: 2 Extra: Using where; Using indexmysql>
1, bởi vì truy vấn này dừng xử lý hàng hiện tại ngay khi tìm thấy kết quả khớpEXPLAIN SELECT film_id, language_id FROM sakila.film
->WHERE NOT EXISTS[
->SELECT * FROM sakila.film_actor
->WHERE film_actor.film_id = film.film_id
->]\G
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: film.film_id rows: 2 Extra: Using where; Using index
Vì vậy, về lý thuyết, MySQL sẽ thực hiện các truy vấn gần như giống hệt nhau. Trên thực tế, điểm chuẩn là cách duy nhất để biết cách tiếp cận nào thực sự nhanh hơn. Chúng tôi đã đo điểm chuẩn cho cả hai truy vấn trên thiết lập tiêu chuẩn của chúng tôi. Kết quả được hiển thị trong
Bảng 4-1. NOT EXISTS so với LEFT OUTER JOIN
Truy vấn
Kết quả trong các truy vấn mỗi giây [QPS]
mysql>2 truy vấn conEXPLAIN SELECT film_id, language_id FROM sakila.film
->WHERE NOT EXISTS[
->SELECT * FROM sakila.film_actor
->WHERE film_actor.film_id = film.film_id
->]\G
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: film.film_id rows: 2 Extra: Using where; Using index
360QP
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
7425 QPS
Điểm chuẩn của chúng tôi nhận thấy rằng truy vấn con chậm hơn một chút
Tuy nhiên, điều này không phải lúc nào cũng đúng. Đôi khi một truy vấn con có thể nhanh hơn. Ví dụ: nó có thể hoạt động tốt khi bạn chỉ muốn xem các hàng từ một bảng khớp với các hàng trong bảng khác. Mặc dù điều đó nghe có vẻ mô tả một phép nối hoàn hảo, nhưng không phải lúc nào nó cũng giống như vậy. Tham gia sau, được thiết kế để tìm mọi bộ phim có diễn viên, sẽ trả về các bản sao vì một số phim có nhiều diễn viên
mysql>SELECT film.film_id FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id];
Chúng tôi cần sử dụng
mysql>4 hoặcEXPLAIN SELECT film_id, language_id FROM sakila.film
->WHERE NOT EXISTS[
->SELECT * FROM sakila.film_actor
->WHERE film_actor.film_id = film.film_id
->]\G
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: film.film_id rows: 2 Extra: Using where; Using index
mysql>5 để loại bỏ các bản saoEXPLAIN SELECT film_id, language_id FROM sakila.film
->WHERE NOT EXISTS[
->SELECT * FROM sakila.film_actor
->WHERE film_actor.film_id = film.film_id
->]\G
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: film.film_id rows: 2 Extra: Using where; Using index
mysql>SELECT DISTINCT film.film_id FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id];
Nhưng chúng ta thực sự đang cố gắng diễn đạt điều gì với truy vấn này và nó có hiển nhiên từ SQL không? . Đây là truy vấn được viết dưới dạng truy vấn phụ thay vì tham gia
mysql>SELECT film_id FROM sakila.film
->WHERE EXISTS[SELECT * FROM sakila.film_actor
->WHERE film.film_id = film_actor.film_id];
Một lần nữa, chúng tôi đã đo điểm chuẩn để xem chiến lược nào nhanh hơn. Kết quả được hiển thị trong
Bảng 4-2. EXISTS vs INNER THAM GIA
Truy vấn
Kết quả trong các truy vấn mỗi giây [QPS]
mysql>9EXPLAIN SELECT film_id, language_id FROM sakila.film
->WHERE NOT EXISTS[
->SELECT * FROM sakila.film_actor
->WHERE film_actor.film_id = film.film_id
->]\G
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: film.film_id rows: 2 Extra: Using where; Using index
185 QPS
mysql>6 truy vấn conEXPLAIN SELECT film_id, language_id FROM sakila.film
->WHERE NOT EXISTS[
->SELECT * FROM sakila.film_actor
->WHERE film_actor.film_id = film.film_id
->]\G
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: film.film_id rows: 2 Extra: Using where; Using index
325 QPS
Trong ví dụ này, truy vấn con thực hiện nhanh hơn nhiều so với phép nối
Chúng tôi đã đưa ra ví dụ dài này để minh họa hai điểm. bạn không nên chú ý đến lời khuyên phân loại về truy vấn phụ và bạn nên sử dụng điểm chuẩn để chứng minh giả định của mình về kế hoạch truy vấn và tốc độ thực hiện
MySQL đôi khi không thể "đẩy xuống" các điều kiện từ bên ngoài của
mysql>1 vào bên trong, nơi chúng có thể được sử dụng để giới hạn kết quả hoặc cho phép tối ưu hóa bổ sungEXPLAIN SELECT film.film_id, film.language_id
->FROM sakila.film
->LEFT OUTER JOIN sakila.film_actor USING[film_id]
->WHERE film_actor.film_id IS NULL\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Using where; Using index;Not exists
Nếu bạn nghĩ rằng bất kỳ truy vấn riêng lẻ nào bên trong
mysql>1 sẽ được hưởng lợi từEXPLAIN SELECT film.film_id, film.language_id
->FROM sakila.film
->LEFT OUTER JOIN sakila.film_actor USING[film_id]
->WHERE film_actor.film_id IS NULL\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Using where; Using index;Not exists
mysql>3 hoặc nếu bạn biết chúng sẽ tuân theo mệnh đềEXPLAIN SELECT film.film_id, film.language_id
->FROM sakila.film
->LEFT OUTER JOIN sakila.film_actor USING[film_id]
->WHERE film_actor.film_id IS NULL\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Using where; Using index;Not exists
mysql>4 khi được kết hợp với các truy vấn khác, thì bạn cần đặt các mệnh đề đó vào trong mỗi phần củaEXPLAIN SELECT film.film_id, film.language_id
->FROM sakila.film
->LEFT OUTER JOIN sakila.film_actor USING[film_id]
->WHERE film_actor.film_id IS NULL\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Using where; Using index;Not exists
mysql>1. Ví dụ: nếu bạnEXPLAIN SELECT film.film_id, film.language_id
->FROM sakila.film
->LEFT OUTER JOIN sakila.film_actor USING[film_id]
->WHERE film_actor.film_id IS NULL\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Using where; Using index;Not exists
mysql>1 hai bảng lớn cùng nhau vàEXPLAIN SELECT film.film_id, film.language_id
->FROM sakila.film
->LEFT OUTER JOIN sakila.film_actor USING[film_id]
->WHERE film_actor.film_id IS NULL\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Using where; Using index;Not exists
mysql>3 kết quả cho 20 hàng đầu tiên, MySQL sẽ lưu cả hai bảng lớn vào một bảng tạm thời và sau đó chỉ truy xuất 20 hàng từ đó. Bạn có thể tránh điều này bằng cách đặtEXPLAIN SELECT film.film_id, film.language_id
->FROM sakila.film
->LEFT OUTER JOIN sakila.film_actor USING[film_id]
->WHERE film_actor.film_id IS NULL\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Using where; Using index;Not exists
mysql>8 trên mỗi truy vấn bên trongEXPLAIN SELECT film.film_id, film.language_id
->FROM sakila.film
->LEFT OUTER JOIN sakila.film_actor USING[film_id]
->WHERE film_actor.film_id IS NULL\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Using where; Using index;Not exists
mysql>1EXPLAIN SELECT film.film_id, film.language_id
->FROM sakila.film
->LEFT OUTER JOIN sakila.film_actor USING[film_id]
->WHERE film_actor.film_id IS NULL\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Using where; Using index;Not exists
Tối ưu hóa hợp nhất chỉ mục
Các thuật toán hợp nhất chỉ mục, được giới thiệu trong MySQL 5. 0, hãy để MySQL sử dụng nhiều hơn một chỉ mục trên mỗi bảng trong một truy vấn. Các phiên bản trước của MySQL chỉ có thể sử dụng một chỉ mục duy nhất, vì vậy khi không có chỉ mục nào đủ tốt để giải quyết tất cả các hạn chế trong mệnh đề
SELECT * FROM sakila.film WHERE0, MySQL thường chọn cách quét bảng. Ví dụ: bảngEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
9 có chỉ mục trên SELECT * FROM sakila.film WHERE6 và một chỉ mục trênEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
SELECT * FROM sakila.film WHERE3, nhưng không phải là lựa chọn tốt cho cả hai điều kiệnEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
SELECT * FROM sakila.film WHERE0 trong truy vấn nàyEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
-- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];0
Trong các phiên bản MySQL cũ hơn, truy vấn đó sẽ tạo ra một bảng quét trừ khi bạn viết nó dưới dạng
mysql>1 của hai truy vấnEXPLAIN SELECT film.film_id, film.language_id
->FROM sakila.film
->LEFT OUTER JOIN sakila.film_actor USING[film_id]
->WHERE film_actor.film_id IS NULL\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Using where; Using index;Not exists
-- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];1
Trong MySQL5. 0 và mới hơn, tuy nhiên, truy vấn có thể sử dụng cả hai chỉ mục, quét chúng đồng thời và hợp nhất các kết quả. Có ba biến thể về thuật toán. hợp cho điều kiện
mysql>6, giao cho điều kiệnSELECT film.film_id FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id];
mysql>7 và hợp của giao cho kết hợp của cả hai. Truy vấn sau đây sử dụng kết hợp hai lần quét chỉ mục, như bạn có thể thấy bằng cách kiểm tra cộtSELECT film.film_id FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id];
mysql>2SELECT film.* FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id]
->WHERE actor_id = 1;
-- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];2
MySQL có thể sử dụng kỹ thuật này trên các mệnh đề
SELECT * FROM sakila.film WHERE0 phức tạp, vì vậy bạn có thể thấy các phép toán lồng nhau trong cộtEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
mysql>2 đối với một số truy vấn. Điều này thường hoạt động rất tốt, nhưng đôi khi các hoạt động đệm, sắp xếp và hợp nhất của thuật toán sử dụng nhiều tài nguyên CPU và bộ nhớ. Điều này đặc biệt đúng nếu không phải tất cả các chỉ mục đều có tính chọn lọc cao, vì vậy quá trình quét song song trả về rất nhiều hàng cho thao tác hợp nhất. Hãy nhớ rằng trình tối ưu hóa không tính đến chi phí này—nó chỉ tối ưu hóa số lần đọc trang ngẫu nhiên. Điều này có thể khiến truy vấn bị “đánh giá thấp”, thực tế có thể chạy chậm hơn so với quét bảng đơn giản. Việc sử dụng nhiều bộ nhớ và CPU cũng có xu hướng ảnh hưởng đến các truy vấn đồng thời, nhưng bạn sẽ không thấy ảnh hưởng này khi chạy truy vấn riêng lẻ. Đây là một lý do khác để thiết kế điểm chuẩn thực tếSELECT film.* FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id]
->WHERE actor_id = 1;
Nếu các truy vấn của bạn chạy chậm hơn do giới hạn của trình tối ưu hóa này, bạn có thể giải quyết vấn đề đó bằng cách vô hiệu hóa một số chỉ mục với
mysql>1 hoặc chỉ quay lại chiến thuậtSELECT DISTINCT film.film_id FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id];
mysql>1 cũEXPLAIN SELECT film.film_id, film.language_id
->FROM sakila.film
->LEFT OUTER JOIN sakila.film_actor USING[film_id]
->WHERE film_actor.film_id IS NULL\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Using where; Using index;Not exists
Tuyên truyền bình đẳng đôi khi có thể có chi phí bất ngờ. Ví dụ: hãy xem xét một danh sách
-- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];9 khổng lồ trên một cột mà trình tối ưu hóa biết sẽ bằng một số cột trên các bảng khác, do mệnh đề
mysql>4 hoặcSELECT DISTINCT film.film_id FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id];
mysql>4 đặt các cột bằng nhauSELECT film.* FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id]
->WHERE actor_id = 1;
Trình tối ưu hóa sẽ “chia sẻ” danh sách bằng cách sao chép nó vào các cột tương ứng trong tất cả các bảng có liên quan. Điều này thường hữu ích, vì nó cung cấp cho trình tối ưu hóa truy vấn và công cụ thực thi nhiều tùy chọn hơn về nơi thực sự thực hiện kiểm tra
-- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];9. Nhưng khi danh sách quá lớn, nó có thể dẫn đến việc thực thi và tối ưu hóa chậm hơn. Không có cách giải quyết tích hợp nào cho sự cố này tại thời điểm viết bài này—bạn sẽ phải thay đổi mã nguồn nếu đó là sự cố đối với bạn. [Đó không phải là vấn đề đối với hầu hết mọi người. ]
MySQL không thể thực hiện song song một truy vấn trên nhiều CPU. Đây là một tính năng được cung cấp bởi một số máy chủ cơ sở dữ liệu khác, nhưng không phải MySQL. Chúng tôi đề cập đến nó để bạn không mất nhiều thời gian tìm cách thực hiện truy vấn song song trên MySQL
MySQL không thể thực hiện phép nối băm thực tại thời điểm viết bài này—mọi thứ đều là phép nối vòng lặp lồng nhau. Tuy nhiên, bạn có thể mô phỏng phép nối băm bằng cách sử dụng chỉ mục băm. Nếu bạn không sử dụng công cụ lưu trữ Bộ nhớ, bạn cũng sẽ phải mô phỏng các chỉ mục băm. Chúng tôi đã chỉ cho bạn cách thực hiện việc này trong “Xây dựng chỉ mục băm của riêng bạn” trên
MySQL trước đây không thể thực hiện quét chỉ mục lỏng lẻo, quét các phạm vi không liền kề của một chỉ mục. Quét chỉ mục MySQL thường yêu cầu điểm bắt đầu xác định và điểm kết thúc xác định trong chỉ mục, ngay cả khi chỉ một vài hàng không liền kề ở giữa thực sự mong muốn cho truy vấn. MySQL sẽ quét toàn bộ phạm vi hàng trong các điểm cuối này
Một ví dụ sẽ giúp làm rõ điều này. Giả sử chúng tôi có một bảng có chỉ mục trên các cột
mysql>7 và chúng tôi muốn chạy truy vấn sauSELECT DISTINCT film.film_id FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id];
-- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];3
Vì chỉ mục bắt đầu bằng cột
mysql>8, nhưng mệnh đềSELECT DISTINCT film.film_id FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id];
SELECT * FROM sakila.film WHERE0 của truy vấn không chỉ định cộtEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
mysql>8, MySQL sẽ thực hiện quét bảng và loại bỏ các hàng không khớp bằng mệnh đềSELECT DISTINCT film.film_id FROM sakila.film
->INNER JOIN sakila.film_actor USING[film_id];
SELECT * FROM sakila.film WHERE0, như minh họa trongEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
Hình 4-5. MySQL quét toàn bộ bảng để tìm hàng
Thật dễ dàng để thấy rằng có một cách nhanh hơn để thực hiện truy vấn này. Cấu trúc của chỉ mục [chứ không phải API công cụ lưu trữ của MySQL] cho phép bạn tìm kiếm từ đầu từng dải giá trị, quét cho đến khi kết thúc dải, sau đó quay lại và nhảy tới đầu dải tiếp theo. cho thấy chiến lược đó sẽ như thế nào nếu MySQL có thể thực hiện được
Lưu ý sự vắng mặt của mệnh đề
SELECT * FROM sakila.film WHERE0, mệnh đề này không cần thiết vì chỉ riêng chỉ mục cho phép chúng ta bỏ qua các hàng không mong muốn. [Một lần nữa, MySQL chưa thể làm điều này. ]EXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
Hình 4-6. Quét chỉ mục lỏng lẻo, điều mà MySQL hiện không thể thực hiện, sẽ hiệu quả hơn
Phải thừa nhận rằng đây là một ví dụ đơn giản và chúng tôi có thể dễ dàng tối ưu hóa truy vấn mà chúng tôi đã hiển thị bằng cách thêm một chỉ mục khác. Tuy nhiên, có nhiều trường hợp thêm chỉ số khác cũng không giải quyết được vấn đề. Một ví dụ là truy vấn có điều kiện phạm vi trên cột đầu tiên của chỉ mục và điều kiện bằng trên cột thứ hai
Bắt đầu từ MySQL 5. 0, có thể quét chỉ mục lỏng lẻo trong một số trường hợp hạn chế nhất định, chẳng hạn như các truy vấn tìm giá trị tối đa và tối thiểu trong một truy vấn được nhóm
-- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];4
Thông tin “Sử dụng chỉ mục cho từng nhóm” trong kế hoạch
SELECT * FROM sakila.film WHERE8 này cho biết việc quét chỉ mục lỏng lẻo. Đây là một tối ưu hóa tốt cho mục đích đặc biệt này, nhưng nó không phải là quét chỉ mục lỏng lẻo cho mục đích chung. Nó có thể được gọi là "thăm dò chỉ số lỏng lẻo". ”EXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
Cho đến khi MySQL hỗ trợ quét chỉ mục lỏng lẻo cho mục đích chung, giải pháp thay thế là cung cấp một hằng số hoặc danh sách các hằng số cho các cột hàng đầu của chỉ mục. Chúng tôi đã trình bày một số ví dụ về cách đạt được hiệu suất tốt với các loại truy vấn này trong nghiên cứu điển hình về lập chỉ mục của chúng tôi trong chương trước
MySQL không tối ưu hóa tốt một số truy vấn
mysql>4 vàSELECT film_id FROM sakila.film
->WHERE EXISTS[SELECT * FROM sakila.film_actor
->WHERE film.film_id = film_actor.film_id];
mysql>5. Đây là một ví dụSELECT film_id FROM sakila.film
->WHERE EXISTS[SELECT * FROM sakila.film_actor
->WHERE film.film_id = film_actor.film_id];
-- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];5
Bởi vì không có chỉ mục trên
mysql>6, truy vấn này thực hiện quét bảng. Nếu MySQL quét khóa chính, về mặt lý thuyết, nó có thể dừng sau khi đọc hàng khớp đầu tiên, vì khóa chính tăng dần và bất kỳ hàng nào tiếp theo sẽ có giá trị lớn hơnSELECT film_id FROM sakila.film
->WHERE EXISTS[SELECT * FROM sakila.film_actor
->WHERE film.film_id = film_actor.film_id];
SELECT * FROM sakila.film WHERE3. Tuy nhiên, trong trường hợp này, MySQL sẽ quét toàn bộ bảng mà bạn có thể xác minh bằng cách lược tả truy vấn. Giải pháp thay thế là loại bỏEXISTS
[ SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id];
mysql>4 và viết lại truy vấn bằngSELECT film_id FROM sakila.film
->WHERE EXISTS[SELECT * FROM sakila.film_actor
->WHERE film.film_id = film_actor.film_id];
mysql>3, như sauEXPLAIN SELECT film.film_id, film.language_id
->FROM sakila.film
->LEFT OUTER JOIN sakila.film_actor USING[film_id]
->WHERE film_actor.film_id IS NULL\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Using where; Using index;Not exists
-- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];6
Chiến lược chung này thường hoạt động tốt khi MySQL chọn quét nhiều hàng hơn mức cần thiết. Nếu bạn là người theo chủ nghĩa thuần túy, bạn có thể phản đối rằng truy vấn này thiếu điểm quan trọng của SQL. Chúng tôi được cho là có thể cho máy chủ biết chúng tôi muốn gì và nó phải tìm ra cách lấy dữ liệu đó, trong khi đó, trong trường hợp này, chúng tôi đang cho MySQL biết cách thực hiện truy vấn và kết quả là, nó không . Đúng, nhưng đôi khi bạn phải thỏa hiệp với các nguyên tắc của mình để đạt hiệu suất cao
CHỌN và CẬP NHẬT trên cùng một bảng
MySQL không cho phép bạn
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
8 từ một bảng trong khi đồng thời chạy một -- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];01 trên đó. Đây thực sự không phải là một hạn chế của trình tối ưu hóa, nhưng biết cách MySQL thực thi các truy vấn có thể giúp bạn khắc phục nó. Đây là một ví dụ về truy vấn không được phép, mặc dù đó là SQL tiêu chuẩn. Truy vấn cập nhật từng hàng với số hàng tương tự trong bảng
-- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];7
Để khắc phục hạn chế này, bạn có thể sử dụng bảng dẫn xuất, vì MySQL hiện thực hóa nó dưới dạng bảng tạm thời. Điều này thực hiện hiệu quả hai truy vấn. một
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
8 bên trong truy vấn con và một bảng nhiều giá trị -- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];01 với các kết quả được nối của bảng và truy vấn con. Truy vấn con mở và đóng bảng trước khi truy vấn bên ngoài
-- SELECT GROUP_CONCAT[film_id] FROM sakila.film_actor WHERE actor_id = 1; -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980 SELECT * FROM sakila.film WHERE film_id IN[1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980];01 mở bảng, vì vậy truy vấn bây giờ sẽ thành công