10张图，搞懂索引为什么会失效？

今天药忘吃喽~ 2022-12-28 01:52 110阅读 0赞

![format_png][]

##    MySQL数据是如何存储的？ ##

### 聚集索引 ###

我们先建如下的一张表

CREATE TABLE `student` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
      `name` varchar(10) NOT NULL COMMENT '学生姓名',
      `age` int(11) NOT NULL COMMENT '学生年龄',
      PRIMARY KEY (`id`),
      KEY `idx_name` (`name`)
    ) ENGINE=InnoDB;

插入如下sql

insert into student (`name`, `age`) value('a', 10);
    insert into student (`name`, `age`) value('c', 12);
    insert into student (`name`, `age`) value('b', 9);
    insert into student (`name`, `age`) value('d', 15);
    insert into student (`name`, `age`) value('h', 17);
    insert into student (`name`, `age`) value('l', 13);
    insert into student (`name`, `age`) value('k', 12);
    insert into student (`name`, `age`) value('x', 9);

数据如下![format_png 1][]mysql是按照页来存储数据的，每个页的大小为16k。

在MySQL中可以通过执行如下语句，看到一个页的大小

show global status like 'innodb_page_size'

结果为16384，即16kb

**在InnoDB存储引擎中，是以主键为索引来组织数据的**。记录在页中按照主键从小到大的顺序以单链表的形式连接在一起。

可能有小伙伴会问，如果建表的时候，没有指定主键呢？

如果在创建表时没有显示的定义主键，则InnoDB存储引擎会按如下方式选择或创建主键。

1.  首先判断表中是否有非空的唯一索引，如果有，则该列即为主键。如果有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个定义的非空唯一索引作为主键
2.  如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针作为索引

**页和页之间以双链表的形式连接在一起**。并且下一个数据页中用户记录的主键值必须大于上一个数据页中用户记录的主键值

假设一个页只能存放3条数据，则数据存储结构如下。![format_png 2][]可以看到我们想查询一个数据或者插入一条数据的时候，需要从最开始的页开始，依次遍历每个页的链表，效率并不高。![format_png 3][]我们可以给这页做一个目录，保存主键和页号的映射关系，根据二分法就能快速找到数据所在的页。但这样做的前提是这个映射关系需要保存到连续的空间，如数组。如果这样做会有如下几个问题

1.  随着数据的增多，目录所需要的连续空间越来越大，并不现实
2.  当有一个页的数据全被删除了，则相应的目录项也要删除，它后面的目录项都要向前移动，成本太高

我们可以把目录数据放在和用户数据类似的结构中，如下所示。目录项有2个列，主键和页号。![format_png 4][]数据很多时，一个目录项肯定很多，毕竟一个页的大小为16k，我们可以对数据建立多个目录项目，在目录项的基础上再建目录项，如下图所示

![format_png 5][]图片来自《MySQL 是怎样运行的：从根儿上理解 MySQL》

这其实就是一颗B+树，也是一个聚集索引，即数据和索引在一块。叶子节点保存所有的列值

> 以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。《MySQL实战45讲》

### 非聚集索引 ###

![format_png 6][]聚集索引和非聚集索引非常类似，区别如下

聚集索引叶子节点的值为**所有的列值**非聚集索引叶子节点的值为**索引列+主键**

当我们查询name为h的用户信息时（学号，姓名，年龄），因为name上建了索引，先从name非聚集索引上，找到对应的主键id，然后根据主键id从聚集索引上找到对应的记录。

**从非聚集索引上找到对应的主键值然后到聚集索引上查找对应记录的过程为回表**

### 联合索引/索引覆盖 ###

假设teacher表定义如下，在name和age列上建立联合索引

CREATE TABLE `teacher` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '教师编号',
      `name` varchar(10) NOT NULL COMMENT '教师姓名',
      `age` int(11) NOT NULL COMMENT '教师年龄',
      `ismale` tinyint(3) NOT NULL COMMENT '是否男性',
      PRIMARY KEY (`id`),
      KEY `idx_name_age` (`name`, `age`)
    ) ENGINE=InnoDB;

插入如下sql

insert into teacher (`name`, `age`, `ismale`) value('aa', 10, 1);
    insert into teacher (`name`, `age`, `ismale`) value('dd', 12, 0);
    insert into teacher (`name`, `age`, `ismale`) value('cb', 9, 1);
    insert into teacher (`name`, `age`, `ismale`) value('cb', 15, 1);
    insert into teacher (`name`, `age`, `ismale`) value('bc', 17, 0);
    insert into teacher (`name`, `age`, `ismale`) value('bb', 15, 1);
    insert into teacher (`name`, `age`, `ismale`) value('dd', 15, 1);
    insert into teacher (`name`, `age`, `ismale`) value('dd', 12, 0);

![format_png 7][]对name和age列建立联合索引

目录页由name列，age列，页号这三部分组成。目录会先按照name列进行排序，当name列相同的时候才对age列进行排序。

数据页由name列，age列，主键值这三部分组成。同样的，数据页会先按照name列进行排序，当name列相同的时候才对age列进行排序。![format_png 8][]

当执行如下语句的时候，会有回表的过程

select * from student where name = 'aa';

当执行如下语句的时候，没有回表的过程

select name, age from student where name = 'aa';

**为什么不需要回表呢？**

因为idx\_name\_age索引的叶子节点存的值为主键值，name值和age值，所以从idx\_name\_age索引上就能获取到所需要的列值，不需要回表，即索引覆盖

**仔细看一下联合索引这个图，你就基本上能明白为什么不满足最左前缀原则的索引会失效？**

### 索引下推 ###

当执行如下语句的时候

select * from student where name like '张%' and age = 10 and ismale = 1;

在5.6版本之前的执行过程如下，先从idx\_name\_age索引上找到对应的主键值，然后回表找到对应的行，判断其他字段的值是否满足条件![format_png 9][]图片来自《MySQL实战45讲》

在5.6引入了索引下推优化，可以在遍历索引的过程中，对索引中包含的字段做判断，直接过滤掉不满足条件的数据，减少回表次数，如下图![format_png 10][]图片来自《MySQL实战45讲》

## 最左前缀原则 ##

### 加速查询 ###

主要针对组合索引，满足如下2个条件即可满足左前缀原则

1.  需要查询的列和组合索引的列顺序一致
2.  查询不要跨列

构造数据如下，其中在name，address，country上建了联合索引

CREATE TABLE `people` (
      `name` varchar(50) NOT NULL,
      `address` varchar(50) NOT NULL,
      `country` varchar(50) NOT NULL,
      KEY `idx_name_addr_country` (`name`,`address`,`country`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

举几个例子，下面涉及到一些explain相关的知识，后面单开一篇长文来做介绍

**例子一**

explain select * from people where name = "jack"
    and address = "beijing" and country = "china"

![format_png 11][] 在这里插入图片描述

type为ref，key\_len为456=（50\*3+2）\*3，联合索引的所有列都使用了

**例子二**

explain select * from people where name = "jack"

![format_png 12][]type为ref，key\_len为152=50\*3+2，联合索引只使用了name列

**例子三**

explain select * from people where address = "beijing"

![format_png 13][]type为index，表明查询的时候对整个索引进行了扫描，并没有加速查找。

假设有如下的联合索引 key idx\_a\_b\_c(a,b,c)

<table> 
 <thead> 
  <tr> 
   <th>sql</th> 
   <th>是否使用索引</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>where a = x and b = x and c = x</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>where a = x and b = x</td> 
   <td>是，部分索引</td> 
  </tr> 
  <tr> 
   <td>where a = x</td> 
   <td>是，部分索引</td> 
  </tr> 
  <tr> 
   <td>where b = x</td> 
   <td>否，不包含最左列name</td> 
  </tr> 
  <tr> 
   <td>where b = x and c = x</td> 
   <td>否，不包含最左列name</td> 
  </tr> 
 </tbody> 
</table>

如果你仔细看了前面联合索引是如何存储的，那你一定能看懂是否使用索引的介绍

**目录页是按照 a b c 列的顺序依次递增排序的。先按照a列排序，如果a列相同，再按照b列排序，如果b列相同，才按照c列排序**

所以查询列值a b c，则这个排序规则能用到，即会走索引。如果只查列值b，并不能用到这个排序规则，所以得遍历所有的记录

### 加速排序 ###

最左前缀原则不仅用在查询中，还能用在排序中。MySQL中，有两种方式生成有序结果集：

1.  通过有序索引顺序扫描直接返回有序数据
2.  Filesort排序，对返回的数据进行排序

因为索引的结构是B+树，索引中的数据是按照一定顺序进行排列的，所以在排序查询中如果能利用索引，就能避免额外的排序操作。EXPLAIN分析查询时，Extra显示为Using index。

所有不是通过索引直接返回排序结果的操作都是Filesort排序，也就是说进行了额外的排序操作。EXPLAIN分析查询时，Extra显示为Using filesort，当出现Using filesort时对性能损耗较大，所以要尽量避免Using filesort

还是先举2个例子，然后总结

explain select * from people order by name

![format_png 14][] 在这里插入图片描述

Extra列只有Using index，即根据索引顺序进行扫描

explain select * from people order by address

![format_png 15][] 在这里插入图片描述

Extra列有Using filesort

总结：假如说有如下联合索引，key idx\_a\_b\_c(a,b,c)

**order by 能使用索引排序**

order by a
    order by a,b
    order by a,b,c
    order by a desc, b desc, c desc
    where a = const order by b,c
    where a = const and b = const order by c
    where a = const and b > const order by b,c

**order by 不能使用索引进行排序**

order by b
    order by c
    order by b, c
    order by a asc, b desc, c desc //排序不一致
    where g = const order by b,c //丢失a索引
    where a = const order by c //丢失b索引
    where a = const order by a,d //d不是索引的一部分
    where a in (...) order by b,c //范围查询

这个原因就不用我解释了把，相信你一定看懂了

## 联合索引的好处 ##

**索引覆盖**，减少了很多回表的操作，提高了查询的效率

**索引下推**，索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select \* from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据；如果是联合索引，通过索引筛选出1000w\*10%\*10% \*10%=1w，效率提升可想而知！

## 索引为什么会失效？ ##

当别人问我索引在什么条件下会失效时，我能背出一大堆规则

1.  不要在索引列上进行运算或使用函数
2.  前导模糊查询不会使用索引，例如 like %李
3.  负向条件索引不会使用索引，建议用in。负向条件有：!=、<>、not in、not exists、not like 等

**索引是按照一定规则排好序的，如果对索引列使用函数，或者 like % 李，具体的值都不知道，它怎么在B+树上加速查询？**

推荐阅读：

*  [推荐一款神仙颜值的 Redis 客户端工具][Redis]
 *  [腾讯 Code Review 规范出炉！你还敢乱写代码？？][Code Review]
 *  [Elasticsearch官方已支持SQL查询，用起来贼方便！][Elasticsearch_SQL]
 *  [写代码有这16个好习惯，可以减少80%非业务的bug][16_80_bug]

**![format_png 16][]喜欢我可以给我设为星标哦![format_png 16][]**

![format_png 17][]

![format_png 18][]

**好文章，我****“在看”**

![format_png 19][]

[format_png]: /images/20221120/bd3adbe3f9c6482eb748850a80234088.png
[format_png 1]: /images/20221120/4b881e9f721145558faa76a4ea63f9a1.png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9PUW1QaWFFVW5oZDV1T2pMdkY5TUI5Rzh0OTJiNzFOdmpJWDRQSEEzV2liNW05WWNYcGhuanljWE5VOXZpY2VYY2tpYzFKVmZjekpGbGNtTEtCRWlhV1dDVkxnLzY0MA?x-oss-process=image/format,png
[format_png 3]: /images/20221120/f36109f98ad747d1999f6a0c63b48102.png
[format_png 4]: /images/20221120/5790a2d07810459db44030758ffb6fa8.png
[format_png 5]: /images/20221120/f4cd0d4231f840e895bfa54e37c475f5.png
[format_png 6]: /images/20221120/f0c937f606c2480ab59c1c0d486a043e.png
[format_png 7]: /images/20221120/64288721af01412c9cff25cbc40a3f91.png
[format_png 8]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9PUW1QaWFFVW5oZDV1T2pMdkY5TUI5Rzh0OTJiNzFOdmpEbkk4TFUxQ01sU1cyTmxIRGQ2b00zQlA3eDlEV2licDZtVmNESzRpYnE5WmFCaWIwdUtBOVU4dkEvNjQw?x-oss-process=image/format,png
[format_png 9]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9PUW1QaWFFVW5oZDV1T2pMdkY5TUI5Rzh0OTJiNzFOdmo4eUEzSWxMQlBDdDhDM3NpYUVOaWFXc3FIQ0I0WE9rdElkU0hWWFJnS0VQamJVZ0VraFlxY2hMQS82NDA?x-oss-process=image/format,png
[format_png 10]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9PUW1QaWFFVW5oZDV1T2pMdkY5TUI5Rzh0OTJiNzFOdmp5Q3BpYnRUYndEVTdJWFlPSENFOXJpYk9UTTFFN2JsR2VleGVhNDlSMDR5enlKS1FnYlZUMzZtQS82NDA?x-oss-process=image/format,png
[format_png 11]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9PUW1QaWFFVW5oZDV1T2pMdkY5TUI5Rzh0OTJiNzFOdmpNRWxEdTZIUVlpY01vc29EN0x4Q29iSnRsTmlhaWJyOVk4YmN0aWNjVFQ4TUdDM2FaaWFxejRzNFZldy82NDA?x-oss-process=image/format,png
[format_png 12]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9PUW1QaWFFVW5oZDV1T2pMdkY5TUI5Rzh0OTJiNzFOdmpXQkZKOFdoY0NEY3ExOXZNZlZaMUtwN0ZJZFFjVkZTb0xwVzRPbFN4ejkwc0RhQmVIZGRjaWFnLzY0MA?x-oss-process=image/format,png
[format_png 13]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9PUW1QaWFFVW5oZDV1T2pMdkY5TUI5Rzh0OTJiNzFOdmptdWJMdjFDeERiaWM2YzllWTVnU1ZucmtxSWMzMEtMdVFEOURqSHU5aHI3UFNMeXViTHJ6RzB3LzY0MA?x-oss-process=image/format,png
[format_png 14]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9PUW1QaWFFVW5oZDV1T2pMdkY5TUI5Rzh0OTJiNzFOdmpLNWIwbFhsYmRNdlZIMkJuN3JMRlRESkk2MEttbDNSYktuWnkwTVAxUEprWTNPUWNoS2FMa1EvNjQw?x-oss-process=image/format,png
[format_png 15]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9PUW1QaWFFVW5oZDV1T2pMdkY5TUI5Rzh0OTJiNzFOdmo5RE9mT3U4Z242aWJpYnVlZnVENW5oWGtWTVVRMURpYmljVXRMbFJ0c1Rva2ljMFNTaWFsMWNISk9VdUEvNjQw?x-oss-process=image/format,png
[Redis]: http://mp.weixin.qq.com/s?__biz=Mzg3MjA4MTExMw%3D%3D&chksm=cef60bbff98182a93228f6e01abd430019b48937585f922c80232d6bc6bf2161df20c07cfe6d&idx=1&mid=2247497737&scene=21&sn=0fcc5f02cf96290ccd469ebde557f16b#wechat_redirect
[Code Review]: http://mp.weixin.qq.com/s?__biz=Mzg3MjA4MTExMw%3D%3D&chksm=cef6044ff9818d59ef2db5670daa6873436205d96ebcd8ad9b94e724b3b8a13e26c5230030da&idx=2&mid=2247497721&scene=21&sn=779cab3c1ca27a4c4cb235edaca96f17#wechat_redirect
[Elasticsearch_SQL]: http://mp.weixin.qq.com/s?__biz=Mzg3MjA4MTExMw%3D%3D&chksm=cef6046cf9818d7aa9f4c60fd2a64aa7b31a5b5afb1b75299ff62d5676aebc82eba098842f3e&idx=1&mid=2247497690&scene=21&sn=03f9f27e4eccc45ef0bb1d9cb19f4df3#wechat_redirect
[16_80_bug]: http://mp.weixin.qq.com/s?__biz=Mzg3MjA4MTExMw%3D%3D&chksm=cef604ccf9818dda8885bb8889f3b84cc3363b97f69bcf065a8959f623f3ffc2f8d5fff0e16f&idx=2&mid=2247497594&scene=21&sn=f601bee0a2516b248c48e4e2513e93ce#wechat_redirect
[format_png 16]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9DMXVETURxam4xaWJIYkNVMnVTdHpnaWNraWNQVng4ZFR2WHNKeDBiU0JpY01mZnlWMGwyQTlaaEZMSjZ5TmY0c3lkRnBmQ3c4aFZwc3Y5QTNKTlkweVlpYmZBLzY0MA?x-oss-process=image/format,png
[format_png 17]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9DMXVETURxam4xaWJIYkNVMnVTdHpnaWNraWNQVng4ZFR2WEQzeEJ3OGdqTEQzRzFkd0NQWGZia3BVcllhV0hsekJETUdHcFZ2dWVpYWFBWXRBRUVrY1FpY0h3LzY0MA?x-oss-process=image/format,png
[format_png 18]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9DMXVETURxam4xOExQUWZaa0JUelI0MkxpYXBCaGhpYVV4SEJiclFubGt4elpxWkdmZ2lhcTRHN2dIYWR2UWliaFZxR3BlWU1OY2M5S2tkVkxpYktpYTJYSkhrZy82NDA?x-oss-process=image/format,png
[format_png 19]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi83UVJUdmtLMnFDNnNMbEFQY1BoZURMeWN1allyT3ZzWnZpYVNPRGJSYlF6UXZXOFZMMXlpYUdBOEZUUGJwT2JGdUppYlNkaWIzVDU1c0pPYTNRZlZDeWlhUGNBLzY0MA?x-oss-process=image/format,png