mysql 索引合并

七年·

已于 2023-04-25 11:16:30 修改

阅读量821

点赞数

分类专栏： mysql 文章标签： mysql

于 2021-01-07 15:26:57 首次发布

本文链接：https://blog.csdn.net/qq_28603127/article/details/112286056

版权

mysql 专栏收录该内容

16 篇文章 1 订阅

订阅专栏

本文探讨了MySQL 5.1及以上版本如何在特定条件下利用索引合并技术（包括intersect、union和sort-union），优化查询性能。通过实例和执行计划分析，揭示了索引合并的适用场景及条件，如等值连接时的主键有序性。

摘要由CSDN通过智能技术生成

有没有听说过,单表select只能使用一个索引,这句话是不对的,5.1版本之后有些情况下会使用多个索引,进行索引合并查询.
索引合并对应于exlian中的type 为index_merge.如下图
在这里插入图片描述
后面的extra里面的using uninon是指的索引合并的类型.
索引合并分为三种类型:
intersection索引合并 union索引合并 sort-union索引合并

我准备好了数据,需要的话直接生成:

CREATE TABLE `index_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` varchar(10) DEFAULT NULL,
  `key2` varchar(10) DEFAULT NULL,
  `com_key1` varchar(10) DEFAULT NULL,
  `com_key2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_key1` (`key1`) USING BTREE,
  KEY `idx_key2` (`key2`) USING BTREE,
  KEY `idx_com` (`com_key1`,`com_key2`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;

随机字符串函数:

CREATE FUNCTION `rand_str_func2`(`n` int) RETURNS varchar(255) 
BEGIN
	#Routine body goes here...
  declare str varchar(255) default 'abcdefghijklmnopqrstuvwxyz';
  declare num int default 0;
  declare return_str varchar(255) default '';
  while num<n DO
     set return_str= concat(return_str,substr(str,floor(RAND()*26)+1,1));
     set num=num+1;
  end while;
  return return_str;
END

插入数据的存储过程:

CREATE  PROCEDURE `rand_insert`(in n int)
BEGIN
	#Routine body goes here...
   DECLARE num int default 0;
   a:REPEAT 
    insert into index_test values(0,rand_str_func(1),rand_str_func(1),rand_str_func(1),rand_str_func(1));
    set num=num+1;
   until num>=n end repeat a;
END

调用存储过程生成表数据:

call  rand_insert(10000);

intersect索引合并

select * from index_test where key1='s' and key2='e';

上面的sql,可以选择的执行方案有三种:
第一种是使用idx_key1索引进行主键查询,然后将查询到的主键值回表查询数据,再通过key2='e’的条件过滤数据后返回结果集.
第二种是使用idx_key2的索引进行主键查询,然后将查询到的主键值回表查询数据,再通过key1='s’的条件过滤数据后返回结果集.
第三种是使用idx_key1,idx_key2两个索引进行主键扫描,因为key1等值情况下,查到的主键(id)是有序的,key2等值情况下,查到的主键也是有序的,所以将两个索引查询出的主键直接取交集,然后直接根据交集回表查询数据返回结果集,这种方式就是intersect索引合并,如下图执行计划所示.
在这里插入图片描述

在这里插入图片描述

intersect索引合并是有条件的,并不是使用多索引进行过滤就可以执行intersect索引合并,条件是根据索引条件扫描出来的主键的顺序必须是有序的(普通二级索引索引值可以重复,当重复时会按照主键顺序排序,所以等值条件查询出来的主键是有序的).

union索引合并

select * from index_test where key1='s' or key2='e';

上面的这个sql能利用idx_key1或者idx_key2索引吗?答案显然是不能的,需要全表扫描,而之前大表or条件连接一般是通过

select * from index_test  where key1='s' 
union 
select * from index_test  where key2='e'

来实现优化的(两个select都可以走索引),但是现在有了union索引合并之后就不再需要我们写union语句了:
在这里插入图片描述
上面使用了union索引合并,union索引合并的原理是,通过key1='s’扫描idx_key1索引找出满足条件的主键,key2='e’扫描idx_key2索引找到满足条件的主键,再对主键取union并集,之后再回表查询即可.
union索引合并的条件也是根据索引条件扫描出来的主键的顺序必须是有序的.

sort-union索引合并

select * from index_test where key1>'z' or key2>'z';

union索引条件必须是索引等值下主键有序,上面的sql不满足union合并的条件,但是,如果我们在union的基础上再深入思考,如果根据索引扫描出来的主键没有顺序,我们可不可以在内存中对扫描出来的主键进行排序再取交集呢?所以有了sort-union索引合并,它仅比union索引合并多了一个对主键进行排序的过程.
在这里插入图片描述

总结

如上述,当where条件中有多个索引时,一定情况下会使用多个索引进行数据扫描,分别是
等值条件下主键有序的and连接的intersect索引合并.
等值条件下主键有序的or连接的union索引合并.
范围条件下的or连接的union-sort索引合并.
但是并不一定满足索引合并的条件下,就会使用索引合并,如下图所示:
在这里插入图片描述
满足了union-sort索引合并的条件却并未使用,原因在于是否使用索引,使用那个索引,是否使用索引合并是由mysql执行优化器选择的,执行优化器会分析各种情况下查询所需要的成本,最终会选择成本最小的执行方法.(上图sql我们就可以看出,key1是’a-z’,有大量的key1>a的数据,所以此时执行索引合并,不如直接使用全表扫描)