当前位置：首页 > article >正文

复杂sql分析以及索引合并

article 2024/11/14 0:25:52

复杂sql分析

简单的sql语句我们很简单的就可以分析出来它的执行计划，但是复杂的sql呢。例如

SELECT	*	FROM	single_table	WHERE
       	(key1	>	'xyz'	AND	key2	=	748	)	OR
       	(key1	<	'abc'	AND	key1	>	'lmn')	OR
       	(key1	LIKE	'%suf'	AND	key1	>	'zzz'	
        AND	
        (key2	<	8000	OR	common_field	=	'abc'))	;

这条语句很难分析出来，遇到这样的语句怎么办呢，怎么分析它的范围匹配区间呢？

首先我们在为某个索引确定范围区间的时候只需要把用不到相关索引的搜索条件替换成true。

之所以把用不到索引的搜索条件替换为true，是因为我们呢不打算使用这些条件进行在该索引上进行过滤，所以不管索引的记录满不满足这些条件，我们都把他们选取出来，带到之后徽标的时候再使用他们过滤。

例如select * from table where key1 >100 and key2 > 10;

如果假设为用key1上的索引就可以简化为:select * from table where key1 > 100 and true;

如果假设为用key2上的索引就可以简化为:select * from table where true and key2 > 10;

根据小学的知识：a and b 只有a和b同时为true的时候结果才为true，只有所以key1>100 and true 可以简化为key1 > 100，因为最后的结果由key1 > 100决定。

所以如果假设为用key1上的索引就可以简化为:select * from table where key1 > 100；

根据上面的方法就可以简化上面的复杂sql为：

我们假设使用key1上的索引。

①key1 < 'abc' AND key1 > 'lmn'，永远都不会满足这个条件，所以为false

②key1 like ‘%suf' 因为%在前面所以这个条件是用不到索引的

SELECT	*	FROM	single_table	WHERE
       	(key1	>	'xyz'	AND	true	)	OR
       	(false)	OR
       	(true	AND	key1	>	'zzz'	
        AND	
        (true	OR	true))	;

false or 条件根据小学的知识，最后的结果取决与条件为true还是false，所以简化为：条件

SELECT	*	FROM	single_table	WHERE
       	key1	>	'xyz'	OR  key1	>	'zzz';

这就变成了简单的sql。可以很好的进行分析

继续简化因为条件为or，所以最后的结果取并集，所以就是最小的值key1 > 'xyz'

我们假设使用key2上的索引。

SELECT	*	FROM	single_table	WHERE
       	key2 = 748  or true	;

true or 任何条件的结果：true

所以使用key2 需要扫描key2二级索引的全部记录，然后回表，这效率很低甚至不如全表扫描，所以优化器最后选择key1上的索引。

索引合并

mysql在一般的情况下执行一个查询最多会用到单个二级索引，但是也有些特上述情况可能用到多个二级索引，mysql中把这种使用到多个索引来完成一次查询的执行方法称之为：index_merge

explain的type = index_merge

索引合并有三种算法

Intersection合并

intersection就是交集的意思，就是某个查询可以使用多个二级索引，将多个二级索引中查到的结果取交集，例如

select * from table where key1 = 'a' and key2 = 'b';

假设使用intersection算法就是：

①从key1二级索引的B+树中取出key1=’a'的记录

②从key2 二级索引的B+树中取出key2=’a'的记录

③二级索引都是由二级索引列+id值组成，所以我们可以计算出两个结果集中id值的交集

④根据交集的id值进行回表操作，把结果返回给用户。

这⾥有同学会思考：为啥不直接使⽤idx_key1或者idx_key3只根据某个搜索条件去读取⼀个⼆级索引，然后回表后再过滤另外⼀个搜索条件呢？这⾥要分析⼀下两种查询执⾏⽅式之间需要的成本代价。

只读取⼀个⼆级索引的成本：

按照某个搜索条件读取⼀个⼆级索引

根据从该⼆级索引得到的主键值进⾏回表操作，然后再过滤其他的搜索条件

读取多个⼆级索引之后取交集成本：

按照不同的搜索条件分别读取不同的⼆级索引

将从多个⼆级索引得到的主键值取交集，然后进⾏回表操作

虽然读取多个⼆级索引⽐读取⼀个⼆级索引消耗性能，但是读取⼆级索引的操作是顺序I/O，⽽回表操作是随机I/O，所以如果只读取⼀个⼆级索引时需要回表的记录数特别多，⽽读取多个⼆级索引之后取交集的记录数⾮常少，当节省的因为回表⽽造成的性能损耗⽐访问多个⼆级索引带来的性能损耗更⾼时，读取多个⼆级索引后取交集⽐只读取⼀个⼆级索引的成本更低。

MySQL在某些特定的情况下才可能会使⽤到Intersection索引合并：

情况⼀：⼆级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

⽐⽅说下边这个查询可能⽤到idx_key1和idx_key_part这两个⼆级索引进⾏Intersection索引合并的操作：

SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

⽽下边这两个查询就不能进⾏Intersection索引合并：

SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';

第⼀个查询是因为对key1进⾏了范围匹配，第⼆个查询是因为联合索引idx_key_part中的key_part2列并没有出现在搜索条件中，所以这两个查询不能进⾏ Intersection索引合并。

情况⼆：主键列可以是范围匹配

⽐⽅说下边这个查询可能⽤到主键和idx_key1进⾏Intersection索引合并的操作：

SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

为啥呢？凭啥呀？突然冒出这么两个规定让⼤家⼀脸懵逼，下边我们慢慢品⼀品这⾥头的⽞机。这话还得从InnoDB的索引结构说起，你要是记不清麻烦再回头看看。对于InnoDB的⼆级索引来说，记录先是按照索引列进⾏排序，如果该⼆级索引是⼀个联合索引，那么会按照联合索引中的各个列依次排序。⽽⼆级索引的⽤户记录是由索引列 + 主键构成的，⼆级索引列的值相同的记录可能会有好多条，这些索引列的值相同的记录⼜是按照主键的值进⾏排序的。

所以重点来了，之所以在⼆级索引列都是等值匹配的情况下才可能使⽤Intersection索引合并，是因为只有在这种情况下根据⼆级索引查询出的结果集是按照主键值排序的。

so？还是没看懂根据⼆级索引查询出的结果集是按照主键值排序的对使⽤Intersection索引合并有啥好处？⼩伙⼦，别忘了Intersection索引合并会把从多个⼆级索引中查询出的主键值求交集，如果从各个⼆级索引中查询的到的结果集本身就是已经按照主键排好序的，那么求交集的过程就很easy啦。假设某个查询使⽤ Intersection索引合并的⽅式从idx_key1和idx_key2这两个⼆级索引中获取到的主键值分别是：

从idx_key1中获取到已经排好序的主键值：1、3、5

从idx_key2中获取到已经排好序的主键值：2、3、4

那么求交集的过程就是这样：逐个取出这两个结果集中最⼩的主键值，如果两个值相等，则加⼊最后的交集结果中，否则丢弃当前较⼩的主键值，再取该丢弃的主键值所在结果集的后⼀个主键值来⽐较，直到某个结果集中的主键值⽤完了，如果还是觉得不太明⽩那继续往下看：

先取出这两个结果集中较⼩的主键值做⽐较，因为1 < 2，所以把idx_key1的结果集的主键值1丢弃，取出后边的3来⽐较。

因为3 > 2，所以把idx_key2的结果集的主键值2丢弃，取出后边的3来⽐较。

因为3 = 3，所以把3加⼊到最后的交集结果中，继续两个结果集后边的主键值来⽐较。

后边的主键值也不相等，所以最后的交集结果中只包含主键值3。

别看我们写的啰嗦，这个过程其实可快了，时间复杂度是O(n)，但是如果从各个⼆级索引中查询出的结果集并不是按照主键排序的话，那就要先把结果集中的主键值排序完再来做上边的那个过程，就⽐较耗时了。

另外，不仅是多个⼆级索引之间可以采⽤Intersection索引合并，索引合并也可以有聚簇索引参加，也就是我们上边写的情况⼆：在搜索条件中有主键的范围匹配的情况下也可以使⽤Intersection索引合并索引合并。为啥主键这就可以范围匹配了？还是得回到应⽤场景⾥，⽐如看下边这个查询：

SELECT * FROM single_table WHERE key1 = 'a' AND id > 100;

假设这个查询可以采⽤Intersection索引合并，我们理所当然的以为这个查询会分别按照id > 100这个条件从聚簇索引中获取⼀些记录，在通过key1 = 'a'这个条件从idx_key1⼆级索引中获取⼀些记录，然后再求交集，其实这样就把问题复杂化了，没必要从聚簇索引中获取⼀次记录。别忘了⼆级索引的记录中都带有主键值的，所以可以在从idx_key1中获取到的主键值上直接运⽤条件id > 100过滤就⾏了，这样多简单。所以涉及主键的搜索条件只不过是为了从别的⼆级索引得到的结果集中过滤记录罢了，是不是等值匹配不重要。

当然，上边说的情况⼀和情况⼆只是发⽣Intersection索引合并的必要条件，不是充分条件。也就是说即使情况⼀、情况⼆成⽴，也不⼀定发⽣Intersection索引合并，这得看优化器的⼼情。优化器只有在单独根据搜索条件从某个⼆级索引中获取的记录数太多，导致回表开销太⼤，⽽通过Intersection索引合并后需要回表的记录数⼤⼤减少时才会使⽤Intersection索引合并。

Union合并

union是并集的意思，适用于不通索引的搜索条件之间使用or连接起来的情况。

情况⼀：⼆级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。

比如说下边这个查询可能⽤到idx_key1和idx_key_part这两个二级索引进行Union索引合并的操作：

SELECT * FROM single_table WHERE key1 = 'a' OR ( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

而下边这两个查询就不能进行Union索引合并：

SELECT * FROM single_table WHERE key1 > 'a' OR (key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

SELECT * FROM single_table WHERE key1 = 'a' OR key_part1 = 'a';

第⼀个查询是因为对key1进行了范围匹配，第⼆个查询是因为联合索引idx_key_part中的key_part2列并没有出现在搜索条件中，所以这两个查询不能进行 Union索引合并。

情况⼆：主键列可以是范围匹配

情况三：使用Intersection索引合并的搜索条件

这种情况其实也挺好理解，就是搜索条件的某些部分使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取交集，比方说这个查询：

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');

优化器可能采用这样的方式来执行这个查询：

先按照搜索条件key1 = 'a' AND key3 = 'b'从索引idx_key1和idx_key3中使⽤Intersection索引合并的方式得到⼀个主键集合。

再按照搜索条件key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'从联合索引idx_key_part中得到另⼀个主键集合。

采用Union索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回给⽤户。

当然，查询条件符合了这些情况也不⼀定就会采用Union索引合并，也得看优化器的心情。优化器只有在单独根据搜索条件从某个⼆级索引中获取的记录数比较少，通过Union索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并。

Sort-Union合并

Union索引合并的使⽤条件太苛刻，必须保证各个⼆级索引列在进⾏等值匹配的条件下才可能被⽤到，⽐⽅说下边这个查询就⽆法使⽤到Union索引合并：

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

这是因为根据key1 < 'a'从idx_key1索引中获取的⼆级索引记录的主键值不是排好序的，根据key3 > 'z'从idx_key3索引中获取的⼆级索引记录的主键值也不是排好序的，但是key1 < 'a'和key3 > 'z'这两个条件⼜特别让我们动⼼，所以我们可以这样：

先根据key1 < 'a'条件从idx_key1⼆级索引总获取记录，并按照记录的主键值进⾏排序

再根据key3 > 'z'条件从idx_key3⼆级索引总获取记录，并按照记录的主键值进⾏排序

因为上述的两个⼆级索引主键值都是排好序的，剩下的操作和Union索引合并⽅式就⼀样了。

我们把上述这种先按照⼆级索引记录的主键值进⾏排序，之后按照Union索引合并⽅式执⾏的⽅式称之为Sort-Union索引合并，很显然，这种Sort-Union索引合并⽐单纯的Union索引合并多了⼀步对⼆级索引记录的主键值排序的过程。

为啥有Sort-Union索引合并，就没有Sort-Intersection索引合并么？是的，的确没有Sort-Intersection索引合并这么⼀说， SortUnion的适⽤场景是单独根据搜索条件从某个⼆级索引中获取的记录数⽐较少，这样即使对这些⼆级索引记录按照主键值进⾏排序的成本也不会太⾼⽽Intersection索引合并的适⽤场景是单独根据搜索条件从某个⼆级索引中获取的记录数太多，导致回表开销太⼤，合并后可以明显降低回表开销，但是如果加⼊Sort-Intersection后，就需要为⼤量的⼆级索引记录按照主键值进⾏排序，这个成本可能⽐回表查询都⾼了，所以也就没有引⼊Sort-Intersection。