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 索引合并联合访问算法