MySql - Optimizing SELECT Statements

Tiếp tục ở phần trước link, ở bài này chúng ta sẽ tìm hiểu về

• Range Optimization

Range Optimization

Range sử dụng 1 index duy nhât để lấy một tập hợp con của table chứa một hoặc một số khoảng gía trị index. Nó có thể sử dụng Single-Part Indexes hoặc Multiple-Part Indexes.

Single-Part Indexes

Đối với Single-Part Indexes, khoảng gía trị index có thể được biểu diễn bởi các điều kiện trong mệnh đề WHERE.

Để xác định điều kiện giới hạn cho Single-Part Indexes, chúng ta tuân theo các quy tắc sau:

• Đối với index BTREE và HASH, so sánh với constant value khi sử dụng các toán tử = , <=>, IN(), IS NULL, hoặc IS NOT NULL.
• Ngoài ra, với index BTREE, so sánh với constant value khi sử dụng >, <, >=, <=, BETWEEN, !=, <>, hoặc sử dụng so sánh LIKE nếu đối số là một constant string không chưa các ký tự đặc biệt.
• Với tất cả các index, nhiều điều kiện giới hạn kết với với OR hoặc AND tạo thành một điều kiện giới hạn.

Constant value có thể hiểu là:

• Một hằng số từ chuỗi query
• Một cột của table const hoặc system
• Kết qủa của một subquery không tương quan

Ví dụ một số điều kiện trong mệnh đề WHERE:

SELECT * FROM t1
  WHERE key_col > 1
  AND key_col < 10;

SELECT * FROM t1
  WHERE key_col = 1
  OR key_col IN (15,18,20);

SELECT * FROM t1
  WHERE key_col LIKE 'ab%'
  OR key_col BETWEEN 'bar' AND 'foo';

Một số gía trị không phải hằng số có thể được chuyển sang hằng số trong khi tối ưu.

MySQL luôn cố gắng trích xuất các điều kiện phạm vi từ mệnh đề WHERE cho mỗi index. Trong quá trình trích xuất, những điều kiện không thể được sử dụng cho điều kiện phạm vi sẽ bị loại bỏ, điều kiện tạo ra các khoảng chồng chéo được kết hợp và điều kiện trống sẽ được loại bỏ.

Cùng xem truy vấn dưới đây, gồm key1 là một column được index, và nonkey không được index.

SELECT * FROM t1 WHERE
  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  (key1 < 'bar' AND nonkey = 4) OR
  (key1 < 'uux' AND key1 > 'z');

Qúa trình xử lý với key1 diễn ra như sau:

1. Bắt đầu với mệnh đề WHERE:

(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z')

2. Xóa nonkey = 4 vaf key1 LIKE '%b' bởi vì chúng không được sử dụng khi scan. Cách đúng để xóa chúng là dùng TRUE, để chúng ta không sót bất kỳ row phù hợp nào khi scan

(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')

3. Thu hep điều kiện thành true hoặc false

• (key1 LIKE 'abcde%' OR TRUE) is always true
• (key1 < 'uux' AND key1 > 'z') is always false

Đổi điều kiện với hằng số

(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)

Xóa hằng số true, false không cần thiết

(key1 < 'abc') OR (key1 < 'bar')

4. Kết hợp các khoảng chồng chéo thành một điều kiện cuối dùng

(key1 < 'bar')

Nói chung các điều kiện sử dụng cho một range scan ít hạn chế hơn mệnh đề WHERE. MySQL thực hiện kiểm tra bổ sung để lọc ra các row thỏa mãn các điều kiện nhưng không phải mệnh đề WHERE đầy đủ.

Các thuật toán trích xuất điều kiện phạm vi có thể xử lý các cấu trúc lồng nhau AND/OR với độ sâu tùy ý, và đầu ra của nó không phụ thuộc vào thứ tự xuất hiện của điều kiện trong mệnh đề WHERE

MySQL không hỗ trợ kết hợp nhiều phạm vi cho việc xử lý index. Để làm được việc giới hạn này, bạn có thể sử dụng UNION hoặc SELECT

Multiple-Part Indexes

Range condition trong Multiple-Part Indexes là một phần mở rộng của range condition trong single-part index. Xét ví dụ sau để hiểu hơn, định nghĩa 1 multiple-part index key1(key_part1, key_part2, key_part3).

key_part1  key_part2  key_part3
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

Điều kiện key_part1 = 1 được xác định như sau:

(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)

Kết quả điều kiện là bản ghi 4, 5, 6 Ngược lại, điều kiện key_part3 = 'abc' không định nghĩa một khoảng đơn. Các mô tả sau có thể cho chúng ta thấy chi tiết hơn cách làm việc của multiple-part indexes

• Đối với index HASH, mỗi khoảng chứa giá trị giống nhau đều có thể được sử dụng. Có nghĩa là các khoảng này được tạo ra chỉ bởi điều kiện sau:

  key_part1 cmp const1
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;

const1, const2 là các hằng số, cmp là một trong số các toán tử so sánh =, <=>, IS NULL, và các điều diện bao gồm tất cả các index part. Ví dụ với 3 part HASH index:

key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'

• Đối với index BTREE, một khoảng có thể được sử dụng cho nhiều điều kiện kết hợp với AND, các điều kiện này so sánh các part với một constant value bằng toán tử =, <=>, IS NULL, >, <, >=, <=, !=, <>, BETWEEN, hoặc LIKE pattern (pattern không chứa ký tự đặc biệt).

Cách tối ưu ở đây là cố gắng thêm vào các key để xác định khoảng miễn là sử dụng các toán tử so sánh = , <=> hoặc IS NULL. Riêng các toán tử >, <, <=, >=, !=, <>, BETWEEN hoặc LIKE, cách tối ưu là không thêm các key.