MySQL có thể xử lý bao nhiêu lần ghi mỗi giây?

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 kẻ phạm tội tồi tệ nhất là truy vấn con

-- 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 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. 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 rằng 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

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

40 để xem chính xác cách viết lại truy vấn)

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

4

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 từng 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

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

43

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

9

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

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

45. Đôi khi điều này có thể nhanh hơn một

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

43

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ụ

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

3

Lời khuyên tiêu chuẩn cho truy vấn này là viết nó dưới dạng

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

47 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

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

5

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

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

  48 so với

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

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

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

  91 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

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

  92 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 đề

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

  94 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

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

  92 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);

  49. Đâ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

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

  99 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);

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

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

  30

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

  31, 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)

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

32 truy vấn con

360QP

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

47

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

2

Chúng tôi cần sử dụng

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

34 hoặc

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

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

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

5

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

9

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)

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

39

185 QPS

truy vấn con

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

36

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

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

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

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

51 sẽ có lợi từ một

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

53, hoặc nếu bạn biết chúng sẽ tuân theo 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);

54 sau khi được kết hợp với các truy vấn khác, thì bạn cần đặt những mệnh đề đó bên trong mỗi phần 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);

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

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

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

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

53 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ảng đó. Bạn có thể tránh điều này bằng cách đặt

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

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

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

51

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

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

49 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 lần quét bảng trừ khi bạn viết nó dưới dạng

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

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

26, giao cho điều kiện

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

27 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

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

92

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

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

92 đố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 * FROM sakila.film
    -> WHERE film_id IN(
    ->    SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);

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

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

51 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 lớ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);

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

54 hoặc

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

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

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

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

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

58, 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 (nhưng 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 mỗi 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 thấy 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 * FROM sakila.film
    -> WHERE film_id IN(
    ->    SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);

94 và

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

95. Đâ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 * FROM sakila.film
    -> WHERE film_id IN(
    ->    SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);

96, 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à mọi hàng 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. Cách giải quyết là loại bỏ

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

94 và viết lại truy vấn bằng một

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

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

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

48 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

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

48 bên trong truy vấn con và 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 nhiều bảng 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

-- 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 bên ngoài mở bảng, vì vậy truy vấn bây giờ sẽ thành công

MySQL có thể xử lý bao nhiêu lần đọc mỗi giây?

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

MySQL có thể xử lý bao nhiêu dữ liệu?

MySQL có thể xử lý 1 tỷ bản ghi không?