8.2.1.4 Index Merge Optimization 索引合并优化:

8.2.1.4 Index Merge Optimization 索引合并优化:

索引合并方法是用于检索记录 使用多个 范围扫描和合并它们的结果集到一起

mysql> show index from ClientInvestOrder;
+-------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ClientInvestOrder |          0 | PRIMARY                |            1 | sn          | A         |       11466 |     NULL | NULL   |      | BTREE      |         |               |
| ClientInvestOrder |          0 | orderNo                |            1 | orderNo     | A         |       11466 |     NULL | NULL   |      | BTREE      |         |               |
| ClientInvestOrder |          1 | ClientInvestOrder_idx1 |            1 | clientSn    | A         |        1263 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> explain select * from ClientInvestOrder  where clientSn=12804;
+----+-------------+-------------------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------+
| id | select_type | table             | partitions | type | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | ClientInvestOrder | NULL       | ref  | ClientInvestOrder_idx1 | ClientInvestOrder_idx1 | 4       | const |    7 |   100.00 | NULL  |
+----+-------------+-------------------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


mysql> explain select * from ClientInvestOrder  where productSn=747 and clientSn=12804;
+----+-------------+-------------------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
| id | select_type | table             | partitions | type | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | ClientInvestOrder | NULL       | ref  | ClientInvestOrder_idx1 | ClientInvestOrder_idx1 | 4       | const |    7 |    10.00 | Using where |
+----+-------------+-------------------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


mysql> create index ClientInvestOrder_idx2 on ClientInvestOrder(productSn);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from ClientInvestOrder  where productSn=747 and clientSn=12804;
+----+-------------+-------------------+------------+-------------+-----------------------------------------------+-----------------------------------------------+---------+------+------+----------+-----------------------------------------------------------------------------+
| id | select_type | table             | partitions | type        | possible_keys                                 | key                                           | key_len | ref  | rows | filtered | Extra                                                                       |
+----+-------------+-------------------+------------+-------------+-----------------------------------------------+-----------------------------------------------+---------+------+------+----------+-----------------------------------------------------------------------------+
|  1 | SIMPLE      | ClientInvestOrder | NULL       | index_merge | ClientInvestOrder_idx1,ClientInvestOrder_idx2 | ClientInvestOrder_idx1,ClientInvestOrder_idx2 | 4,4     | NULL |    1 |   100.00 | Using intersect(ClientInvestOrder_idx1,ClientInvestOrder_idx2); Using where |
+----+-------------+-------------------+------------+-------------+-----------------------------------------------+-----------------------------------------------+---------+------+------+----------+-----------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

合并可以产生unions,intersections 或者unions-of-intersections 。


这个访问方式合并索引扫描从一个单独的表,它不会合并扫描跨越多个表


在EXPALIN 输出, Index Merge 方法出现作为index_merge 在类型列, 在这种情况下,

key 列 包含了使用的索引的列, key_len 包含那些索引的最长索引部分的列表
Examples:

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

Index 合并方法有几个访问算法(查看EXPLAIN 额外的字段)

Using intersect(...)

Using union(...)

Using sort_union(...)


下面的章节将描述那些方法:

注意:

Index 合并优化算法有下面已知的缺陷:

1.如果你的查询是一个复杂的WHERE 子句有嵌套的AND/OR 

MYSQL 不知道选择合适的执行计划，尝试分散表达式使用下面的规律:

(x AND y) OR z = (x OR z) AND (y OR z)

(x OR y) AND z = (x AND z) OR (y AND z)

索引合并不适用于全文索引,我们计划在将来的版本中实现

在MySQL 5.6.6之前,如果一个range scan是可能的在一些索引上,

优化器不会考虑使用Index 合并 union 或者Index合并Sort-Union算法,比如,考虑下面的查询:

SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;


对于这个查询,两个计划是可能的:

1.index 合并scan 使用  (goodkey1 < 10 OR goodkey2 < 20) condition.

2.一个range scan 使用badkey < 30 condition.

然而, 优化器只考虑第2个计划

选择在不同的可能索引合并访问方法的变体

在不同的可能的Index Merge 访问方法和其他访问方法的区别是基于成各种变量选项的成本评估

8.2.1.4  索引合并交叉访问算法:


这个访问算法可以被利用当一个WHERE 子句被转换成多个范围条件在不同的keys 使用AND连接,

每个条件是下面中的一个:

在这种形式下, index有N部分(有就是说,所有的index部分是被覆盖的)


key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

任何范围条件覆盖一个InnoDB表的主键

例子:

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;
  
 Index 合并交叉算法执行并发的扫描在所有被使用的索引和

产生记录顺序的交集 它从合并的Index 扫描接收


如果所有的列 用于在查询是通过使用的索引覆盖,full table 记录 不是被检索的

(EXPLAIN 输出包含使用的索引在额外字段)这里有一个查询的例子:

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

如果 被使用的索引不覆盖所有的列在查询里,全部的记录是被接收只有当范围条件对于那些使用的索引被满足

如果其中一个合并条件是一个条件覆盖了一个InnoDB表的主键,

它不用于行检索,但是用于过滤检索的记录用于其他条件

8.2.1.4.2 The Index Merge Union Access Algorithm 索引合并联合访问算法