MySQL 查询优化之 Index Merge

Index Merge Intersection 访问算法

Index Merge Union 访问算法

Index Merge Sort-Union 访问算法

参考文档

索引合并访问方法可以在查询中对一个表使用多个索引,对它们同时范围扫描，并且合并结果(intersects/unions/unions-of-intersections)。 此访问方法合并来自单个表的索引扫描; 它不会将扫描合并到多个表中。

使用索引合并的示例查询：

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

【注意】
索引合并优化算法有以下已知的缺陷：

  • 如果您的查询具有深度AND或OR嵌套的复杂WHERE子句，并且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)
  • 索引合并不适用于全文索引。

在EXPLAIN输出中，索引合并方法在type列中显示为index_merge。 在这种情况下，key列包含使用的索引列表，key_len包含这些索引的最长键部分列表。

Index Merge访问方法有几种算法，它们显示在EXPLAIN输出的Extra字段中：

  • Using intersect(...)

  • Using union(...)

  • Using sort_union(...)

Index Merge方法根据合并算法的不同分成了三种：Intersect，Union，Sort_union。它们显示在EXPLAIN输出的Extra字段中。Intersect和Union都需要使用的索引是ROR的，也就是ROWID ORDERED，即针对不同的索引扫描出来的数据必须是同时按照ROWID排序的，这里的ROWID其实也就是InnoDB的主键（如果不定义主键，InnoDB会隐式添加ROWID列作为主键）。只有每个索引是ROR的，才能进行归并排序，你懂的。 当然你可能会有疑惑，查记录后内部进行一次sort不一样么，何必必须要ROR呢，不错，所以有了Sort-union。Sort-union就是每个非ROR的索引排序后再进行Merge。MySQL至于为什么没有Sort-Intersect，就不清楚了，但是MariaDB从5.3版本开始就支持了。

1. Index Merge Intersection 访问算法

index intersect merge是多个索引条件扫描得到的结果进行交集运算。显然在多个索引提交之间是 AND 运算时，才会出现 index intersect merge。 下面两种where条件或者它们的组合时会进行 index intersect merge:

  • 条件使用到复合索引中的所有字段或者左前缀字段(对单字段索引也适用)

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

  • 主键上的任何范围条件

    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;

示例1

mysql> show index from employees;

+-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table     | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| employees |          0 | PRIMARY        |            1 | emp_no      | A         |      298936 |     NULL | NULL   |      | BTREE      |         |               |

| employees |          1 | idx_name       |            1 | last_name   | A         |        1651 |     NULL | NULL   |      | BTREE      |         |               |

| employees |          1 | idx_first_name |            1 | first_name  | A         |        1251 |     NULL | NULL   |      | BTREE      |         |               |

+-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3 rows in set (0.00 sec)

mysql> explain select * from employees.employees e where e.first_name='Tzu' and e.last_name='Terkki';

+----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+-------------------------------------------------------+

| id | select_type | table | partitions | type        | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                                                 |

+----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+-------------------------------------------------------+

|  1 | SIMPLE      | e     | NULL       | index_merge | idx_name,idx_first_name | idx_name,idx_first_name | 66,58   | NULL |    1 |   100.00 | Using intersect(idx_name,idx_first_name); Using where |

+----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+-------------------------------------------------------+

1 row in set, 1 warning (0.00 sec)

示例2

mysql> show index from employees;

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| employees |          0 | PRIMARY  |            1 | emp_no      | A         |      298936 |     NULL | NULL   |      | BTREE      |         |               |

| employees |          1 | idx_name |            1 | last_name   | A         |        1651 |     NULL | NULL   |      | BTREE      |         |               |

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2 rows in set (0.00 sec)

mysql> explain select * from employees.employees e where e.emp_no>10011 and e.last_name = 'Terkki';

+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+------------------------------------------------+

| id | select_type | table | partitions | type        | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                                          |

+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+------------------------------------------------+

|  1 | SIMPLE      | e     | NULL       | index_merge | PRIMARY,idx_name | idx_name,PRIMARY | 70,4    | NULL |   90 |   100.00 | Using intersect(idx_name,PRIMARY); Using where |

+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+------------------------------------------------+

1 row in set, 1 warning (0.00 sec)

索引合并Intersection访问算法对所有使用的索引执行同时扫描，并产生从合并索引扫描中接收到的行序列的交集。

如果查询中使用的所有列都被使用的索引覆盖，则不会检索完整的表行（EXPLAIN输出包含在这种情况下在Extra字段中 Using index）。 例如：

mysql> explain select count(*) from employees.employees e where e.first_name='Tzu' and e.last_name='Terkki';

+----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------+

| id | select_type | table | partitions | type        | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                                                              |

+----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------+

|  1 | SIMPLE      | e     | NULL       | index_merge | idx_name,idx_first_name | idx_name,idx_first_name | 66,58   | NULL |    1 |   100.00 | Using intersect(idx_name,idx_first_name); Using where; Using index |

+----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------+

mysql> explain select e.emp_no,e.first_name,e.last_name from employees.employees e where e.first_name='Tzu' and e.last_name='Terkki';

+----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------+

| id | select_type | table | partitions | type        | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                                                              |

+----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------+

|  1 | SIMPLE      | e     | NULL       | index_merge | idx_name,idx_first_name | idx_name,idx_first_name | 66,58   | NULL |    1 |   100.00 | Using intersect(idx_name,idx_first_name); Using where; Using index |

+----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------+

1 row in set, 1 warning (0.00 sec)

如果使用的索引未涵盖查询中使用的所有列，则仅在满足所有使用的键的范围条件时才检索完整行。

如果其中一个合并条件是InnoDB表的主键上的条件，则它不用于行检索，而是用于过滤掉使用其他条件检索的行。

2. Index Merge Union 访问算法

index uion merge就是多个索引条件扫描，对得到的结果进行并集运算，显然是多个条件之间进行的是 OR 运算。

下面几种类型的 where 条件，以及他们的组合可能会使用到index union merge算法：

  • 条件使用到复合索引中的所有字段或者左前缀字段(对单字段索引也适用)

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

  • InnoDB表的主键上的任何范围条件

  • 任何符合 index intersect merge 的where条件

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;

示例3

mysql> explain select e.emp_no,e.first_name,e.last_name from employees.employees e where e.first_name='Tzu' or e.last_name='Terkki';

+----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+---------------------------------------------------+

| id | select_type | table | partitions | type        | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                                             |

+----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+---------------------------------------------------+

|  1 | SIMPLE      | e     | NULL       | index_merge | idx_name,idx_first_name | idx_first_name,idx_name | 58,66   | NULL |  416 |   100.00 | Using union(idx_first_name,idx_name); Using where |

+----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+---------------------------------------------------+

1 row in set, 1 warning (0.00 sec)

示例4

mysql> explain select * from employees.employees e where e.emp_no>10011 or e.last_name = 'Terkki';

+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+--------+----------+--------------------------------------------+

| id | select_type | table | partitions | type        | possible_keys    | key              | key_len | ref  | rows   | filtered | Extra                                      |

+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+--------+----------+--------------------------------------------+

|  1 | SIMPLE      | e     | NULL       | index_merge | PRIMARY,idx_name | PRIMARY,idx_name | 4,66    | NULL | 149648 |   100.00 | Using union(PRIMARY,idx_name); Using where |

+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+--------+----------+--------------------------------------------+

1 row in set, 1 warning (0.00 sec)

3. Index Merge Sort-Union 访问算法

当WHERE子句转换为OR组合的多个范围条件时，此访问算法适用，但Index Merge union 算法不适用。

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_col = 30;

sort-union算法和union算法之间的区别在于sort-union算法必须首先获取所有行的行ID，然后在返回任何行之前对它们进行排序。

mysql> show index from t1;

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| t1    |          1 | idx_i1_i2 |            1 | i1          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |

| t1    |          1 | idx_i1_i2 |            2 | i2          | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |

| t1    |          1 | idx_i2    |            1 | i2          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3 rows in set (0.00 sec)

mysql> explain select * from t1 force index (idx_i1_i2,idx_i2) where i1>1 or i2>5;

+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------+

| id | select_type | table | partitions | type        | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                                           |

+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------+

|  1 | SIMPLE      | t1    | NULL       | index_merge | idx_i1_i2,idx_i2 | idx_i1_i2,idx_i2 | 4,4     | NULL |   21 |   100.00 | Using sort_union(idx_i1_i2,idx_i2); Using where |

+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------+

1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 force index (idx_i1_i2,idx_i2)where (i1>4 or i2=4) and d = '2001-01-01';

+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------+

| id | select_type | table | partitions | type        | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                                           |

+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------+

|  1 | SIMPLE      | t1    | NULL       | index_merge | idx_i1_i2,idx_i2 | idx_i1_i2,idx_i2 | 4,4     | NULL |   10 |    10.00 | Using sort_union(idx_i1_i2,idx_i2); Using where |

+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+-------------------------------------------------+

1 row in set, 1 warning (0.00 sec)

4. 参考文档

http://www.ywnds.com/?p=14468
https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html

最新文章

  1. Jquery_类选择器笔记
  2. OpenStack学习参考
  3. JDBC连接池。。。转载
  4. BroadcastReceiver接收系统广播消息
  5. 最大联通子数组之和（dfs，记忆化搜索，状态压缩）
  6. WPF命令参数CommandParameter
  7. 站点建设10个最好的响应的HTML5滑块插件
  8. JS的异步回调函数
  9. 需要注意的subList方法！和substring是不一样的！从源码解释他们的不同。
  10. 深度残差网(deep residual networks)的训练过程
  11. Python 常用命令
  12. Java框架spring 学习笔记（九）：Spring的bean管理（@Required、@Component、@Autowired、@Resource注解）
  13. 【Beta阶段】测试报告
  14. Maven的下载与安装（Windows版本）
  15. App推荐 | Google Tasks
  16. TabLayout基本属性全解
  17. RGB与CMYK以及加色与减色
  18. mq刷盘方式
  19. 【BZOJ】【2752】【HAOI2012】高速公路(Road)
  20. BZOJ1233 [Usaco2009Open]干草堆tower 和 BZOJ3549 [ONTAK2010]Tower

热门文章

  1. File upload in ASP.NET Core web API
  2. 初学Java web（转）
  3. typescript学习笔记（三）---接口
  4. [BZOJ2251/BJWC2010]外星联络
  5. 洛谷 P2231 [HNOI2002]跳蚤
  6. Hive_Hive的数据模型_内部表
  7. JAVA常用知识总结（十）——Maven
  8. nopCommerce - asp.net开源商城
  9. (转)Quirks模式与standards模式区别
  10. xml和TreeView