1.5万字+30张图盘点索引常见的11个知识点（下篇）

索引合并

索引合并（index merge）是从MySQL5.1开始引入的索引优化机制，在之前的MySQL版本中，一条sql多个查询条件只能使用一个索引，但是引入了索引合并机制之后，MySQL在某些特殊的情况下会扫描多个索引，然后将扫描结果进行合并

结果合并会为下面三种情况：

• 取交集（intersect）
• 取并集（union）
• 排序后取并集（sort-union）

为了不耽误演示，删除之前所有的索引，然后为name和age各自分别创建一个二级索引idx_name和idx_age

取交集（intersect）

当执行下面这条sql就会出现取交集的情况

select * from `user` where name = '赵六' and age= 22;

查看执行计划

type是​​index_merge​​​，并且possible_key和key都是​​idx_name​​​和​​idx_age​​​，说明使用了索引合并，并且Extra有​​Using intersect(idx_age,idx_name)​​，intersect就是交集的意思。

整个过程大致是这样的，分别根据​​idx_name​​​和​​idx_age​​​取出对应的主键id，之后将主键id取交集，那么这部分交集的id一定同时满足查询​​name = '赵六' and age= 22​​的查询条件（仔细想想），之后再根据交集的id回表

不过要想使用取交集的联合索引，需要满足各自索引查出来的主键id是排好序的，这是为了方便可以快速的取交集

比如下面这条sql就无法使用联合索引

select * from `user` where name = '赵六' and age > 22;

只能用name这个索引，因为​​age > 22​​查出来的id是无序的，前面在讲索引的时候有说过索引列的排序规则

由此可以看出，使用联合索引条件还是比较苛刻的。

取并集（union）

取并集就是将前面例子中的​​and​​​换成​​or​​

select * from `user` where name = '赵六' or age = 22;

前面执行的情况都一样，根据条件到各自的索引上去查，之后对查询的id取并集去重，之后再回表

同样地，取并集也要求各自索引查出来的主键id是排好序的，如果查询条件换成​​age > 22​​时就无法使用取并集的索引合并

select * from `user` where name = '赵六' or age > 22;

排序后取并集（sort-union）

虽然取并集要求各自索引查出来的主键id是排好序的，但是如果遇到没排好序的情况，mysql会自动对这种情况进行优化，会先对主键id排序，然后再取并集，这种情况就叫 排序后取并集（sort-union）。

比如上面提到的无法直接取并集的sql就符合排序后取并集（sort-union）这种情况

select * from `user` where name = '赵六' or age > 22;

mysql如何选择索引

在日常生产中，一个表可能会存在多个索引，那么mysql在执行sql的时候是如何去判断该走哪个索引，或者是全表扫描呢？

mysql在选择索引的时候会根据索引的使用成本来判断

一条sql执行的成本大致分为两块

• IO成本，因为这些页都是在磁盘的，要想去判断首先得加载到内存，MySQL规定加载一个页的成本为1.0
• CPU成本，除了IO成本之外，还有条件判断的成本，也就是CPU成本。比如前面举的例子，你得判断加载的数据​​name = '赵六'​​符不符合条件，MySQL规定每判断一条数据花费的成本为0.2

全表扫描成本计算

对于全表扫描来说，成本计算大致如下

mysql会对表进行数据统计，这个统计是大概，不是特别准，通过​​show table status like '表名'​​可以查看统计数据

比如这个表大致有多少条数据rows，以及聚簇索引所占的字节数data_length，由于默认是16kb，所以就可以计算出(data_length/1024/16)大概有多少个数据页。

所以全表扫描的成本就这么计算了

​​rows * 0.2 + data_length/1024/16 * 1.0​​

二级索引+回表成本计算

二级索引+回表成本计算比较复杂，他的成本数据依赖两部分扫描区间个数和回表次数

为了方便描述扫描区间，这里我再把上面的图拿上来

select * from `user` where name = '赵六';

对着图看！

查询条件​​name = '赵六'​​就会产生一个扫描区间，从id=4的赵六扫描到id=6的赵六

又比如假设查询条件为​​name > '赵六'​​，此时就会产生一个从id=7的刘七开始直到数据结束（id=9的王九）的扫描区间

又比如假设查询条件为​​name < '李四' and name > '赵六'​​，此时就会产生两个扫描区间，从id=2的张三到id=3的张三算一个，从id=7的刘七开始直到数据结束算另一个

所以扫描区间的意思就是符合查询条件的记录区间

二级索引计算成本的时候，mysq规定读取一个区间的成本跟读取一个页的IO成本是一样的，都是1.0

区间有了之后，就会根据统计数据估计在这些区间大致有多少条数据，因为要读写这些数据，那么读取成本大致就是 条数 * 0.2

所以走二级索引的成本就是 ​​区间个数 * 1.0 + 条数 * 0.2​​

之后这些数据需要回表（如果需要的话），mysql规定每次回表也跟读取一个页的IO成本是一样，也是1.0

回表的时候需要对从聚簇索引查出来的数据进行剩余查询条件的判断，就是CPU成本，大致为 条数 * 0.2

所以回表的成本大致为 ​​条数 * 1.0 + 条数 * 0.2​​

所以二级索引+回表的大致成本为 ​​区间个数 * 1.0 + 条数 * 0.2 + 条数 * 1.0 + 条数 * 0.2​​

当索引的成本和全表扫描的成本都计算完成之后，mysql会选择成本最低的索引来执行

mysql对上述成本计算结果还会微调，但是微调的值特别小，所以这里我就省略了，并且这里也只是大致介绍了成本计算的规则，实际情况会更复杂，比如连表查询等等，有感兴趣的小伙伴查阅相关的资料

小结

总的来说，这一节主要是让你明白一件事，mysql在选择索引的时候，会根据统计数据和成本计算的规则来计算使用每个索引的成本，然后选择使用最低成本的索引来执行查询

索引失效

在日常开发中，肯定或多或少都遇到过索引失效的问题，这里我总结一下几种常见的索引失效的场景

为了方便解释，这里我再把图拿过来

不符和最左前缀匹配原则

当不符和最左前缀匹配原则的时候会导致索引失效

比如​​like​​以%开头，索引失效或者是联合索引没走第一个索引列。

比如name和age的联合索引，当执行​​select * from user where name > '王五' and age > 22;​​​时，那么如果要走索引的话，此时就需要扫描整个索引，因为索引列是先以name字段排序，再以age字段排序的，对于age来说，在整个索引中来说是无序的，从图中也可以看出 18、23...9，无序，所以无法根据二分查找定位到​​age > 22​​是从哪个索引页开始的，

所以走索引的话要扫描整个索引，一个一个判断，最后还要回表，这就很耗性能，不如直接扫描聚簇索引，也就是全表扫描来的痛快。

索引列进行了计算

当对索引进行表达式计算或者使用函数时也会导致索引失效

这个主要是因为索引中保存的是索引字段是原始值，从上面画的图可以看出来，当经过函数计算后的值，也就没办法走索引了

隐式转换

当索引列发生了隐式转换可能会导致索引失效

举个例子，mysql规定，当字符串跟数字比较时，会把字符串先转成数字再比较，至于字符串怎么转成数字，mysql有自己的规则

比如说，当我执行了下面这条sql时就会发生隐式转换

select * from `user` where name = 9527;

name字段是个varchar类型，9527，没加引号，是数字，mysql根据规则会把​​name​​​字段的值先转换成数字，再与9527比较，此时由于​​name​​字段发生了转换，所以索引失效了

ALL说明没走索引，失效了。

但是假设现在对age创建一个索引，执行下面这条sql

select * from `user` where age = '22';

此时age索引就不会失效，主要是因为前面说的那句话：

当字符串跟数字比较时，会把字符串先转成数字再比较

于是​​'22'​​会被隐式转成数字，之后再跟age比较，此时age字段并没有发生隐式转换，所以不会失效。

所以说，隐式转换可能会导致索引失效。

mysql统计数据误差较大

mysql统计数据误差较大也可能会导致索引失效，因为前面也说了，mysql会根据统计数据来计算使用索引的成本，这样一旦统计数据误差较大，那么计算出来的成本误差就大，就可能出现实际走索引的成本小但是计算出来的是走索引的成本大，导致索引失效

当出现这种情况时，可以执行​​analyze table 表名​​这条sql，mysql就会重新统计这些数据，索引就可以重新生效了

索引建立原则

单个表索引数量不宜过多

• 从上面分析我们知道，每个索引都对应一颗B+树，并且叶子节点存储了索引列全量的数据，一旦索引数量多，那么就会占有大量磁盘空间
• 同时前面也提到，在查询之前会对索引成本进行计算，一旦索引多，计算的次数就多，也可能会浪费性能

经常出现在where后的字段应该建立索引

这个就不用说了，索引就是为了加快速度，如果没有合适索引，就会全表扫描，对于InnoDB来说，全表扫描就是从聚簇索引的第一个叶子节点开始，顺着链表一个一个判断数据服不服合查询条件

order by、group by后字段可建立索引

比如下面这条sql

select * from `user` where name = '赵六' order by age asc;

查询​​name = '赵六'​​​并且根据​​age​​排序，name和age联合索引

你可能记不清索树了，我把那个索引树拿过来

此时对着索引树你可以发现，当​​name = '赵六'​​​时，​​age​​​已经排好序了（前面介绍索引的说了排序规则），所以就可以使用​​age​​索引列进行排序。

频繁更新的字段不宜建索引

因为索引需要保证按照索引列的值进行排序，所以一旦索引字段数据频繁更新，那么为了保证索引的顺序，就得频繁挪动索引列在索引页中的位置

比如name和age联合索引

此时把id=9这条数据的name从​​王九​​​改成​​赵六​​，那么此时就把这条更改后的数据在索引页上移到王五和id=4的赵六之间，因为name相同时，得保证顺序性，同时要按照age排序，id=9的age为9，最小，那么排在最前。

所以频繁更新的字段建索引就会增加维护索引的成本。

选择区分度高的字段做索引

这个是因为，如果区分度低，那么索引效果不好。

举个例子，假设现在有个性别字段sex，非男即女，如果对sex建索引，假设男排在女之前，那么索引页的数据排列大致如下：

这里我画了6条数据，假设有10w条数据那么也是这继续排，男在前，女子在后。

此时如果走sex索引，查询sex=男的数据，假设男女数据对半，那么就扫描的记录就有5w，之后如果要回表，那么根据成本计算规则发现成本是巨大的，那么此时还不如直接全表扫描来的痛快。

所以要选择区分度高的字段做索引

总结

到这，本文就结束了，这里回顾一下本文讲的内容

首先主要是讲了聚簇索引和非聚簇索引，随后讲了MySQL对于一些常见查询的优化，比如覆盖索引，索引下推，都是为了减少回表次数，从而减少带来的性能消耗，再后面就提到MySQL是如何选择索引的，最后介绍了索引失效的场景和索引建立的原则。

最后希望本文对你有所帮助！

最后的最后，表数据sql如下

INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (1, '李四', 20, '杭州');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (2, '张三', 18, '北京');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (3, '张三', 23, '上海');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (4, '赵六', 22, '杭州');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (5, '王五', 19, '北京');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (6, '赵六', 24, '上海');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (7, '刘七', 20, '上海');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (8, '刘七', 22, '上海');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (9, '王九', 9, '杭州');

参考：

[1].《MySQL是怎样运行的》

文章转载自公众号：三友的java日记