索引合并 和 组合索引、复合索引、联合索引

索引合并 和 组合索引、复合索引、联合索引

2023-05-07 17:04:00

https://book.douban.com/annotation/32043301/

https://www.cnblogs.com/wl-blog/p/15186874.html

索引合并(Index Merge)

分别为 col1 和 col2 设置索引，MySQL在检索条件中自动使用 col1 和 col2

组合索引(Concatenated Index)

为 (col1, col2) 设置索引

两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效

1，创建索引

对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加 索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

2，复合索引

比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;

如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。 因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

3，索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

4，使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

5，排序的索引问题

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

6，like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。

---

https://www.cnblogs.com/BigBender/p/15428681.html

https://blog.csdn.net/onlyor/article/details/126504323

如果在explain中看到有索引合并，需要检查查询和表的结构，查看是否最优

explain SELECT * FROM student WHERE height>155 AND weight=55;

Mysql索引合并

MySQL在一般情况下执行一个查询时最多只会用到单个二级索引(辅助索引)，但特殊情况下一个查询中使用到多个二级索引，这种使用到多个索引来完成一次查询的执行方法称之为：索引合并。MySQL具体的索引合并算法有三种：Intersection合并、Union合并、Sort-Union合并。

Intersection合并

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `sex` varchar(1) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  `weight` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_height` (`height`),
  KEY `idx_weight` (`weight`)
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

/*Data for the table `student` */

insert  into `student`(`id`,`name`,`sex`,`age`,`height`,`weight`) values 
(1,'zhangsan','M',23,155,44),
(2,'lisi','F',45,155,44),
(3,'wangwu','F',12,155,55),
(4,'liming','F',36,177,55),
(5,'xiali','M',43,177,55),
(6,'caocao','F',35,177,55),
(8,'zhangfei','M',12,155,44);

假设这个查询使用Intersection合并的方式执行的话，那这个过程就是这样的：

1） 从idx_height二级索引对应的B+树中取出 height=155 的相关记录。

2） 从idx_weight二级索引对应的B+树中取出 weight=55 的相关记录。

3） 二级索引是由索引列 + 主键构成，两个结果集相交，得到符合记录id的集合。

4）按照上一步生成的id值列表进行回表操作，也就是从聚簇索引中把指定id值的完整用户记录取出来，返回给用户。

为啥不直接使用idx_height或者idx_weight只根据某个搜索条件去读取一个二级索引，然后回表后再过滤另外一个搜索条件呢？需要分析两种查询执行方式之间需要的成本代价。

1）只读取一个二级索引的成本：

按照某个搜索条件读取一个二级索引，根据从该二级索引得到的主键值进行回表操作，然后再过滤其他的搜索条件

2）读取多个二级索引之后取交集成本：

按照不同的搜索条件分别读取不同的二级索引，将从多个二级索引得到的主键值取交集，然后进行回表操作。

虽然读取多个二级索引比读取一个二级索引消耗性能，但是大部分情况下读取二级索引的操作是顺序I/O，而回表操作是随机I/O，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

MySQL在某些特定的情况下才可能会使用到Intersection索引合并，哪些情况呢？

一、等值匹配

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

而下边的查询就不能进行Intersection索引合并：

SELECT * FROM student WHERE height>155 AND weight=55;

是因为对height进行了范围匹配。

二、主键列可以是范围匹配

比方说下边这个查询可能用到主键和u_idx_day_status进行Intersection索引合并的操作：

SELECT * FROM student WHERE id>3 height>155;

对于InnoDB的二级索引来说，记录先是按照索引列进行排序，如果该二级索引是一个联合索引，那么会按照联合索引中的各个列依次排序。而二级索引的用户记录是由索引列 + 主键构成的，二级索引列的值相同的记录可能会有好多条，这些索引列的值相同的记录又是按照主键的值进行排序的。

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

Intersection索引合并会把从多个二级索引中查询出的主键值求交集，如果从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的，那么求交集的过程就很容易。但是如果从各个二级索引中查询出的结果集并不是按照主键排序的话，那就要先把结果集中的主键值排序完再来做上边的那个过程，就比较耗时了。

按照有序的主键值去回表取记录有个专有名词，叫：Rowid Ordered Retrieval，简称ROR。

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

SELECT * FROM student WHERE id>3 height=155;

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

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

联合索引替代Intersection索引合并，如下SQL

SELECT * FROM student WHERE height=155 AND weight=55;

这个查询之所以可能使用Intersection索引合并的方式执行，因为idx_height和idx_weight是两个单独的B+树索引，要是把这两个列搞一个联合索引，那直接使用这个联合索引就把事情搞定了，何必用啥索引合并呢，使用这个联合索引进行查询简直是又快又好，既不用多读一棵B+树，也不用合并结果。

Union合并

我们在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来，我们说这些不同的搜索条件之间是OR关系。有时候OR关系的不同搜索条件会使用到不同的索引，比方说这样：

SELECT * FROM student WHERE height=155 OR weight=55;

Intersection是交集的意思，这适用于使用不同索引的搜索条件之间使用AND连接起来的情况；Union是并集的意思，适用于使用不同索引的搜索条件之间使用OR连接起来的情况。与Intersection索引合并类似，MySQL在某些特定的情况下才可能会使用到Union索引合并：

一、等值匹配

分析同Intersection合并

二、主键列可以是范围匹配

分析同Intersection合并

三、使用Intersection索引合并的搜索条件

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

SELECT * FROM student WHERE age=45 AND weight=44 OR (height=155 AND weight=55);

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

先按照搜索条件height=155 AND weight= 55从索引idx_height和idx_weight中使用Intersection索引合并的方式得到一个主键集合。

再按照搜索条件 age=45 AND weight=44 从索引idx_age和idx_weight中得到另一个主键集合。

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

Sort-Union合并

Union索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到，比方说下边这个查询就无法使用到Union索引合并：

SELECT * FROM student WHERE height>155 OR weight<55;

这是因为根据height>155或weight<55从索引中获取的二级索引记录的主键值也不是排好序的，所以我们可以这样：

先根据height>155条件从二级索引中获取记录，并按照记录的主键值进行排序

再根据weight<55条件从二级索引中获取记录，并按照记录的主键值进行排序

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

上述这种先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并，很显然，这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。