CirroData技术课堂（一）： MySQL的索引合并优化

东方国信大数据 2023-11-13

115

全文约2109字，阅读约13分钟

通常情况下，MySQL在查询一张表时只会用到单个二级索引。但是MySQL有种叫做index merge的优化：在满足一定条件时，查询一张表可以使用多个二级索引。

MySQL将index merge optimzation分为3种：index merge intersection, index merge union, index merge sort union。本篇我们将逐个介绍3种优化的原理，使用条件等内容。

Index merge Interscetion

原理

如下图所示：

注释：

0：图中的表，在列key_part1,key_part2,key_part建立联合索引（BTREE二级索引）；在key列上建立二级索引（BTREE二级索引）；

1：表的主键为Id列；

2：使用的查询过滤条件为key_part1=20 and key_part2=30 and key_part3 =40 and key =24；

3：聚合索引中主键id=1024为MySQL需要回表扫描的记录行。

执行步骤

MySQL按照sql中不同的过滤条件，同时读取不同的二级索引，再对根据各个二级索引返回的主键列取交集，然后根据交集中的主键范围到聚合索引读取完整的表的行数据。

在不同索引确定的主键的交集比较小的情况下，这种查询操作可以减少IO。因为读取二级索引是顺序IO，而回表（根据二级索引访问聚合索引）是随机IO。

使用条件

使用index intersection 需要使用and连接连接以下类型的条件：

0：在二级索引上使用等值条件;

1：主键可以使用范围匹配;

当然具体sql在执行时，是否使用index merge，还要看sql优化器计算出来的cost。其他index merge方法后面不在赘述。

例句：

执行计划中Using intersect(idx_key1, primary)表示执行时使用主键id列和在key1上创建的二级索引idx_key1。

Index merge union

原理

如下图所示：

注释：

0：图中的表在key_part1, key_part2, key_part3上建立联合索引。在key列上建立二级索引。

1：图中表的主键为id列

2：图中sql使用的过滤条件为key_part1=20 and key_part2 = 30 and key_part3 = 40 or key = 24

执行步骤

MySQL按照sql中不同的过滤条件同时读取不同的二级索引，再对各个二级索引确定的主键列取并集，然后根据并集中的主键范围到聚合索引读取完整的表的行数据。

使用条件：

使用条件：使用index union需要使用or连接连接以下类型的条件：

0：二级索引使用等值条件;

1：主键可以是范围条件;

2：使用index intersection的条件。

例句：

执行计划Using union(idx_key3,idx_key1)表示同时使用了在key1列上创建的idx_key1二级索引和在key3列上创建的idx_key3二级索引。

Sort-union

原理

使用union的条件太苛刻，必须保证各个二级索引在进行等值匹配的条件下才可以用。其实多个二级索引进行范围过滤时也可用。步骤稍微有点不同。

注释：

0：图中表在key1列和key2列上分别建立二级索引

1：图中表的主键为id列

2：图中sql使用的过滤条件为 key1 >=45 or key2 >=35

执行步骤：

从上图可以看到，不同的二级索引在对主键做并集后，又根据主键排序。然后再去聚合索引查找相关记录行。

例句：

执行计划Using sort_union(idx_key1, idx_key2)表示同时使用了idx_key1列上的二级索引和idx_key2列上创建的二级索引。

总结

可以看到MySQL在查询一张表时可以使用多个二级索引。

除了以上描述的使用规则，index merge optimization的使用限制条件还包括：index merge只能是同一张表的多个索引merge，不能跨表。

优势

通过index merge optimization, mysql server有时可以进一步缩小需要回表的数据范围。所以提可以提高sql的执行效率。

和index merge optimazation相比，联合索引有联合索引使用的限制条件，比如联合索引的最左匹配原则等。。

劣势

同时，如果在同一张表上创建多个索引，表的insert，update等操作需要维护的索引比较多，因此有会降低DML语句的执行效率。

和联合索引相比，index merge optimization因为要扫描多个索引，并将多个索引的主键进行排序，取交集，取并集等操作，所以通常会比聚合索引慢。

所以，创建多个二级索引和将多个二级索引合并成一个联合索引，哪个合适，哪个效率更高，需要看具体的应用场景。

而且即使表上有创建了多个二级索引，也不一定在sql查询中使用index merge optimization。MySQL的优化器还要比较其它表访问方式的成本，看哪种访问方式的成本低。

mysql执行计划索引 mysql sql优化 mysql创建索引

文章转载自东方国信大数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

CirroData技术课堂（一） ： MySQL的索引合并优化

评论

CirroData技术课堂（一）： MySQL的索引合并优化