MySQL có thể giữ bao nhiêu bản ghi?

Question

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

Nội dung chính Show

Khi một truy vấn con tương quan là tốt
Tối ưu hóa hợp nhất chỉ mục
CHỌN và CẬP NHẬT trên cùng một bảng
MySQL có thể xử lý 100 triệu bản ghi không?
MySQL có thể lưu trữ bao nhiêu hàng?
MySQL có thể xử lý hàng tỷ hàng không?
MySQL có thể xử lý dữ liệu lớn không?

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
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

1 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 (

SELECT * FROM sakila.film
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

2). Điều này cảm thấy tự nhiên khi viết với một truy vấn con, như sau

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
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

3 và thay thế chúng và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. 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
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

Bây giờ, truy vấn con yêu cầu

SELECT * FROM sakila.film
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

6 từ bảng

SELECT * FROM sakila.film
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

7 bên ngoài và không thể thực hiện trước.

SELECT * FROM sakila.film
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

8 hiển thị kết quả là

SELECT * FROM sakila.film
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

9 (bạn có thể sử 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 |
+----+--------------------+------------+--------+------------------------+

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
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

8, MySQL sẽ quét bảng của bảng

SELECT * FROM sakila.film
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

7 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ộ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 |
+----+--------------------+------------+--------+------------------------+

3

mysql> 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 |
+----+--------------------+------------+--------+------------------------+

3

MySQL đã 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ào

mysql> 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> 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 so 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 |
+----+--------------------+------------+--------+------------------------+

9 là

SELECT * FROM sakila.film
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

9 trong một truy vấn và

mysql> SELECT film.* FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id)
    -> WHERE actor_id = 1;

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ý

Truy vấn thứ hai không nói "Sử dụng ở đâu" trong cột

mysql> SELECT film.* FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id)
    -> WHERE actor_id = 1;

2 cho bảng

SELECT * FROM sakila.film
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

7. Điều đó không quan trọng, mặc dù. dù sao thì mệnh đề

mysql> SELECT film.* FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id)
    -> WHERE actor_id = 1;

4 của truy vấn thứ hai cũng giống như mệnh đề

SELECT * FROM sakila.film
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

0

Truy vấn thứ hai cho biết “Không tồn tại” trong cột

mysql> SELECT film.* FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id)
    -> WHERE actor_id = 1;

2 của bả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 |
+----+--------------------+------------+--------+------------------------+

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ục

mysql> SELECT film.* FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id)
    -> WHERE actor_id = 1;

9 của bả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 |
+----+--------------------+------------+--------+------------------------+

9. Điều này tương đương với truy vấn con tương quan

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

0

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

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ớp

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

2 truy vấn con

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 |
+----+--------------------+------------+--------+------------------------+

7

425 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> 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

4 hoặc

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

5 để loại bỏ các bản sao

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

9

185 QPS

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

6 truy vấn con

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

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ổ sung

Nếu bạn nghĩ rằng bất kỳ truy vấn riêng lẻ nào bên trong

mysql> 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

1 sẽ được hưởng lợi từ

mysql> 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

3 hoặc nếu bạn biết chúng sẽ tuân theo mệnh đề

mysql> 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

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ủa

mysql> 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

1. Ví dụ: nếu bạn

mysql> 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

1 hai bảng lớn cùng nhau và

mysql> 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

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 đặt

mysql> 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

8 trên mỗi truy vấn bên trong

mysql> 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

1

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
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

0, MySQL thường chọn cách quét bảng. Ví dụ: bả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 |
+----+--------------------+------------+--------+------------------------+

9 có chỉ mục trên

SELECT * FROM sakila.film
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

6 và một chỉ mục trên

SELECT * FROM sakila.film
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

3, nhưng không phải là lựa chọn tốt cho cả hai điều kiện

SELECT * FROM sakila.film
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

0 trong truy vấn 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);

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

1 của hai truy vấn

-- 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> SELECT film.film_id FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id);

6, giao cho điều kiện

mysql> SELECT film.film_id FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id);

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ột

mysql> SELECT film.* FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id)
    -> WHERE actor_id = 1;

2

-- 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
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

0 phức tạp, vì vậy bạn có thể thấy các phép toán lồng nhau trong cột

mysql> SELECT film.* FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id)
    -> WHERE actor_id = 1;

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ế

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> SELECT DISTINCT film.film_id FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id);

1 hoặc chỉ quay lại chiến thuật

mysql> 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

1 cũ

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> SELECT DISTINCT film.film_id FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id);

4 hoặc

mysql> SELECT film.* FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id)
    -> WHERE actor_id = 1;

4 đặt các cột bằng nhau

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> SELECT DISTINCT film.film_id FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id);

7 và chúng tôi muốn chạy truy vấn sau

-- 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> SELECT DISTINCT film.film_id FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id);

8, nhưng mệnh đề

SELECT * FROM sakila.film
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

0 của truy vấn không chỉ định cột

mysql> SELECT DISTINCT film.film_id FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id);

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 * FROM sakila.film
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

0, như minh họa trong

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
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

0, 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. )

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
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

8 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". ”

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> SELECT film_id FROM sakila.film
    ->    WHERE EXISTS(SELECT * FROM sakila.film_actor
    ->    WHERE film.film_id = film_actor.film_id);

4 và

mysql> SELECT film_id FROM sakila.film
    ->    WHERE EXISTS(SELECT * FROM sakila.film_actor
    ->    WHERE film.film_id = film_actor.film_id);

5. Đây là một ví dụ

-- 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> SELECT film_id FROM sakila.film
    ->    WHERE EXISTS(SELECT * FROM sakila.film_actor
    ->    WHERE film.film_id = film_actor.film_id);

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ơn

SELECT * FROM sakila.film
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

3. 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ỏ

mysql> SELECT film_id FROM sakila.film
    ->    WHERE EXISTS(SELECT * FROM sakila.film_actor
    ->    WHERE film.film_id = film_actor.film_id);

4 và viết lại truy vấn bằng

mysql> 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

3, như sau

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

MySQL có thể xử lý 100 triệu bản ghi không?

Nếu bạn thực sự cần quyền truy cập trong SQL vào các điểm dữ liệu riêng lẻ, hãy đảm bảo bạn giảm kích thước của mỗi hàng xuống số lượng trường tối thiểu và kiểu dữ liệu nhỏ nhất có thể. MySQL lớn nhất mà cá nhân tôi từng quản lý là ~100 triệu hàng .

MySQL có thể lưu trữ bao nhiêu hàng?

Giới hạn kích thước hàng tối đa của MySQL là 65.535 byte được thể hiện trong các ví dụ về InnoDB và MyISAM sau đây. Giới hạn được thực thi bất kể công cụ lưu trữ nào, mặc dù công cụ lưu trữ có thể có khả năng hỗ trợ các hàng lớn hơn.

MySQL có thể xử lý hàng tỷ hàng không?

Vâng, nó có thể xử lý hàng tỷ bản ghi . Nếu bạn lập chỉ mục đúng cách cho các bảng, chúng vừa với bộ nhớ và các truy vấn của bạn được viết đúng cách thì đó không phải là vấn đề.

MySQL có thể xử lý dữ liệu lớn không?

MySQL không được thiết kế để chạy các truy vấn phức tạp đối với khối lượng dữ liệu lớn (yêu cầu xử lý rất nhiều dữ liệu trên quy mô lớn). Trình tối ưu hóa MySQL khá hạn chế, thực hiện một truy vấn tại một thời điểm bằng một luồng duy nhất.

programming mysql

MySQL có thể giữ bao nhiêu bản ghi?

Khi một truy vấn con tương quan là tốt

Tối ưu hóa hợp nhất chỉ mục

CHỌN và CẬP NHẬT trên cùng một bảng

MySQL có thể xử lý 100 triệu bản ghi không?

MySQL có thể lưu trữ bao nhiêu hàng?

MySQL có thể xử lý hàng tỷ hàng không?

MySQL có thể xử lý dữ liệu lớn không?

Bài Viết Liên Quan

Quảng Cáo

Có thể bạn quan tâm

Toplist được quan tâm

Quảng cáo

Xem Nhiều

Quảng cáo

Chúng tôi

Điều khoản

Trợ giúp

Mạng xã hội