Tối ưu hóa câu lệnh WHERE trong MySQL

Cập nhật: Lượt xem: 138 [ Mysql ]

Trong phần này, mình sẽ nói về việc tối ưu hóa có thể thực hiện khi xử lý mệnh đề WHERE. Các ví dụ trong bài viết sử dụng mệnh đề SELECT, tuy nhiên việc tối ưu này có thể áp dụng cho cả câu lệnh WHERE trong DELETE và UPDATE.

Tối ưu hóa câu lệnh WHERE trong MySQL

Bạn hay có thói quen viết các điều kiện giống như làm phép toán, gom các toán tử vào trong ngoặc để thực hiện nhanh hơn. Nhưng SQL thực hiện các tối ưu tương tự, vì vậy bạn cần phải tránh thực hiện điều này bằng cách biến đổi về phép toán dễ hiểu và dễ bảo trì hơn.

  • Phá ngoặc
((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)
  • Sắp xếp lại phép toán cho dễ hiểu hơn
 (a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5
  • Rút gọn điều kiện về ít hơn
 (b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6)
-> b=5 OR b=6
  • Biểu thức không đổi được sử dụng bởi các index và được đánh giá chỉ một lần. Ví dụ như table có INDEX(A, B, C, D)
(a<b AND b=c) AND a=5 Chỉ sử dụng được index cho 1 trong 2 phép toán (a<b AND b=c) hoặc a=5

Còn lại biểu thức tương đương b>5 AND b=c AND a=5 có thể sử dụng được tất cả các index cho cả biểu thức

  • COUNT() trên một bảng mà không sử dụng WHERE được trả về trực tiếp từ thông tin bảng trong bảng MyISAM và bảng MEMORY. Điều này cũng tương tự với bất kì biểu thức NOT NULL nào được sử dụng với 1 bảng. Hiểu đơn giản là COUNT() với bảng đơn thì nó tự động lấy trong bảng MYISAM có sẵn chứ không phải truy vấn lại toàn bộ dữ liệu trong bảng nữa.
  • HAVING được gộp với WHERE nếu bạn không sử dụng GROUP BY hoặc là các hàm aggregate (COUNT(), MIN(), ..).
  • Với mỗi bảng trong 1 câu lệnh join, 1 câu lệnh WHERE đơn giản hơn được khởi tạo để ước lượng cho bảng và bỏ qua các dòng càng sớm càng tốt
  • Tất cả các bảng 'Không đổi' được đọc đầu tiên trước các bảng còn lại trong truy vấn. Một bảng 'Không đổi' là 1 trong số các bảng sau:
  1. Bảng trống (không có dữ liệu) hoặc bảng có 1 row
  2. Một bảng với câu lệnh WHERE có điều kiện là PRIMARY KEY hoặc UNIQUE index. cái mà tất cả các index được so sánh với 1 biểu thức và được định nghĩa là NOT NULL. Ví dụ như:
SELECT * FROM t WHERE primary_key=1;
SELECT * FROM t1,t2
  WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
  • Thứ tự JOIN được chọn bằng cách thử tất cả các trường hợp có thể. Nếu tất cả các cột trong ORDER BY và GROUP BY ở cùng 1 bảng, thì bảng đó được ưu tiên JOIN trước
  • Nếu có 1 câu lệnh ORDER BY và 1 câu lệnh khác là GROUP BY hoặc có ORDER BY và GROUP BY chứa các cột ở các bảng khác với bảng đầu tiên ở hàng đợi join. 1 bảng tạm sẽ được tạo.
  • Mỗi chỉ mục (index) của bảng được truy vấn, thì chỉ mục tốt nhất sẽ được sử dụng cho dù chúng ta nghĩ rằng việc quét bảng hiệu quả hơn. Ở mỗi lần, việc quét được sử dụng dựa trên bất kì index nào được mở rộng hơn 30% bảng đó, nhưng số phần trăm đó không xác định được việc lựa chọn giữa việc sử dụng index hay quét bảng. Trình tối ưu hóa bây giờ phức tạp hơn và dựa trên ước tính của nó dựa trên các yếu tố bổ sung như kích thước bảng, số lượng hàng và kích thước khối I / O.
  • Trong một số trường hợp, MySQL có thể đọc thẳng các row từ index thậm chí là không động đến dữ liệu từ bảng. Nếu tất cả các cột được sử dụng từ index là số, chỉ có index tree (index tree) được sử dụng để giải quyết truy vấn.
  • Trước khi mỗi row được trả về, nếu không đúng trong mệnh đề HAVING thì sẽ được bỏ qua

Sau đây là 1 vài truy vấn cực nhanh:

SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
  WHERE key_part1=constant;

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;
  • MySQL thực hiện các truy vấn sau chỉ sử dụng index tree, giả sử rằng các cột được lập chỉ mục là số:
SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name
  WHERE key_part1=val1 AND key_part2=val2;

SELECT key_part2 FROM tbl_name GROUP BY key_part1;

  • Các câu lệnh dưới đây sử dụng việc index để trả về các dòng kèm sắp xếp mà không cần phải ORDER BY riêng:
SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... ;