Mysql - Index Merge Optimization

Phương pháp Index Merge được sử dụng để lấy các row và kết hợp chúng thành một. Việc kết hợp có thể thể hiện phép hợp, giao hoặc việc hợp các phép giao. Phương pháp tiếp cận này quét từ một bảng duy nhất, nó không kết hợp quét trên nhiều bảng.

Trong kết quả của EXPLAIN, phương pháp Index Merge se có type column là index_merge. Trong trường hợp này, key column chứa danh sách các index được sử dụng, và key_len chứa danh sách các phần quan trọng nhất với các index.

Ví dụ:

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key=30;

SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1=t1.some_col;

SELECT * FROM t1, t2
  WHERE t1.key1=1
  AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);

Phương pháp Index Merge sử dụng một số thuật toán (có thể thấy trong field Extra khi EXPLAIN):

• Sử dụng intersect(...)
• Sử dụng union(...)
• Sử dụng sort_union(...)

Thuật toán intersect

Thuật toán này được sử dụng khi một mệnh đề WHERE được chuyển sang một số điều kiện trên các key kết hợp bằng AND, và môi điều kiện là một trong số sau:

• Các inde này có đúng N phần key_part1= const1Và key_part2= const2... VÀ key_partN=constN
• Bất kỳ điều kiện nào sử dụng primary key của bảng InnoDB

VD:

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;

SELECT * FROM tbl_name
  WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

Thuật toán thực hiện quét đồng bộ trên tất cả các inde được sử dụng và tạo các điểm giao nhau giữa các hàng tuần tự theo việc quét các index được kết hợp.

Nếu tất cả các column sử dụng trong truy vấn đều được bao phủ bởi các index, thì toàn bộ row của bảng se không được lấy ra (Trong trường hợp này, kết quả của EPLAIN thì field Extra se là Using index). Chúng ta có 1 ví dụ về việc này:

SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;

Nếu các index được sử dụng không bao gồm các cột được sử dụng trong truy vấn, thì tất cả các row chỉ được lấy ra khi các điều kiện cho tất cả các key được sử dụng đều an toàn.

Nếu một trong nhưng điều kiện được merge là điều kiện có chứa primary key của bảng InnoDB, nó se không được sử dụng trong các row được lấy ra, nhưng được sử dụng để lọc trong các row được lấy ra theo một điều kiện khác.

Thuật toán union

Các tiêu chuẩn áp dụng cho thuật toán này cung tương tự như thuật toán intersect. Thuật toán này được sử dụng khi mệnh đề WHERE được chuyển đổi thành các điều kiện với các key khác nhau kết hợp bằng OR, và cùng thỏa man một trong số sau:

• Các index này có đúng N phần key_part1=const1 AND key_part2=const2 ... AND key_partN=constN
• Bất kỳ điều kiện nào sử dụng primary key của bảng InnoDB
• Có một điều kiện mà thuật toán intersect được áp dụng

Ví dụ:

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;

SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR
  (key3='foo' AND key4='bar') AND key5=5;

Thuật toán sort_union

Thuật toán được sử dụng khi mệnh đề WHERE được chuyển đổi thành các điều kiện kết hợp với nhau bằng OR, nhưng thuật toán union lại không được áp dụng.

Ví dụ:

SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_coữ=30;

Sự khác nhau giữa sort-union và union là thuật toán sort-union phải lấy IDs các row đầu tiên, sau đó mới sắp ếp chúng trước khi trả về một row bất kỳ.