上篇文章我们说了,使用索引的注意事项,前面我们总结了查询数据库的方式有const,ref,ref_or_null,range,index,all,而使用时候需要注意,当where语句后面全是索引查询,当where语句后面跟着非索引的时候,当用and连接,比如where key1 and 非索引 = ‘abc’,这时候会先二级索引查询索引b+树进行回表。若用where key1 or 非索引 = ‘abc’,这时候会直接全表查询。
mysql查询的情况,一般下会用到单个二级索引,但某些时刻也会在一个查询里使用到多个二级索引,设计innoDB的人吧这个称为 index merge。
顾名思义,这是交集的意思,就是将多少二级索引查询的数据取交集,
SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
假如用intersection合并的方执行的话,这里有两个b+树,从key1和key3的索引树叶子节点查询到他们的交集数据id,再用交集id回表查询需要的数据。
那为什么不查询一个索引的id,然后继续在聚簇索引树过滤剩下的记录呢?这里就要分析两个方式查询的成本代价。
虽然读取多个二级索引比读取一个二级索引消耗性能,但是读取二级索引的时候是顺序I/O,而回表操作是随机I/O,而且读取一个二级索引,回表的记录特别多,这样也会降低性能,而取交集之后,回表的记录更少,获取数据的成本更低。
Mysql在哪些情况下才会使用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';
第一个是因为范围查询了,第二个是因为联合索引只有一个等值匹配。
主键列可以范围匹配
SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';
比如说这个sql就可以用到intersection索引。
那上面两个条件为什么成立,回忆我们之前学的,二级索引节点都是排序好的,按主键排序,所以如果二级索引不是等值的情况下,如何进行主键排序,所以只有二级索引是等值的情况下,不是范围查询的,才可以使用intersection合并索引查询,因为只有这种情况下,查询出来的数据是按主键排序的。所以这时候吧排序好的id,回表查询本身的数据就效率很高。
假设用intersection方法从key1和key2两个二级索引获取到的主键分别是:
Key1获取到排序好的主键:1,3,5
Key2获取到排序好的主键:2,3,6
那mysql获取他们交集的过程就是,逐个比较两个结果集最小的值,小的那个就丢弃,相等则放入交集结果,继续比较后一位数,小的就丢弃,直到遇到相等放入结果集中。
这种效率非常高,过程非常快,时间复杂度是0(n),但如果二级索引查询出来的主键不是排序好的,所以就需要先排序,这样就比较耗时间。
情况二又是怎么回事呢?
SELECT * FROM single_table WHERE key1 = 'a' AND id > 100;
当上面这个sql需要查询的时候,我们会查询key1的二级索引树,然后回表查询过滤的id>100,其实这样是没必要的,为了提高效率,可以用intersection合并索引,别忘了二级索引的叶子节点是存着主键id的,直接排序获取就好。
当然以上两个情况即使成立,也未必就会intersection合并索引查询,还是要看回表查询的代价,如果sql优化器觉得回表的代价太大,回表查询数据量太多,自然就会用intersection合并索引查询。
Union在两个表取出并集可以用上,但这里不是在两个表使用,意思是sql语句取并集:
情况一,二级索引是等值匹配,联合索引必须每个列的值都等值匹配,不能只匹配部分列。
SELECT * FROM single_table WHERE key1 = 'a' OR ( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');
比方说上面的sql就可以用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';
这两个sql就不可以用union查询,原因是二级索引不能范围查询和联合索引必须所有列都在。
情况二:主键可以范围查询
情况三:使用intersection索引合并搜索条件
SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');
优化器可能会这样处理,
先按key_part1和key_part2和key_part3使用intersection索引合并的方式得到一个主键集合。
在按key1和key3使用intersection索引合并的方式得到一个主键集合。
最后按union索引并集的方法吧这两个集合合在一起。
union索引使用的条件太苛刻,
SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'
这时候如果由上面这种情况:
先通过key1查询索引树的集合,因为不是排序好的,需要把他进行排序。
再通过key3查询索引树大于z的集合,因为不是排序好的,需要把他进行排序。
之后再用union合并索引,把两个id集合合并在一起,这种方法就是sort-union。但是是没有sort-intersection的,因为sort-union适合从某个索引获取的记录数少,即使排序,成本也不会太高。而前者使用场景是单独根据搜索条件从二级索引中获取的记录太多,排序成本太高。
联合索引不可以代替intersection索引
SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
上面的sql会直接用intersection合并索引查询,那如果吧他们建立一个联合索引直接用联合索引idx_key1_key3查询不也能搞定吧,但如果建立联合索引而放弃普通的单个二级索引的话,当需要把key3列单独查询,就没有索引了。
总结:当二级索引两个列不是联合索引但是在一起查询的时候,有机会触发intersection合并查询和union查询。