索引覆盖、索引失效以及查看索引执行计划

Myth丶恋晨 2024-03-30 12:10 64阅读 0赞

在讲述这三个主题之前，我们首先对索引的分类进行概述：

1.按照创建方式

索引包括自动创建的索引和主动创建的索引：自动的创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建 对应列的索引。（即外键索引，唯一索引以及外键索引）如果一张表中不存在这些数据，则会自动创建一个普通索引（这个索引是我们不能看见的），而其他我们我们手动创建的索引同样也是普通索引

而我们在手动创建的索引中，可以使用其中的两个列创建复合索引，如下：

![d3cdc7c071334a3eae6d74e95127a445.png][]

2.按照应用场景来分：

分为聚集索引（主键索引）和非聚集索引（其他索引）

为什么主键索引被称为聚集索引？

mysql在储存主键时，会储存以主键为索引的所有数据信息，而非聚集索引只会储存索引列和主键信息（因为通过索引列找到数据然后通过主键查看所有信息）

我们通过查看索引执行计划中间穿插对索引覆盖进行讲解（利用实例）

**查看索引执行计划：**

![05ff46b807d94c0fb51fc73981fa076c.png][]

以下全部详细解析explain各个属性含义：

各属性含义：

**id**: **查询的序列号**

.**select\_type**: 查**询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询** SIMPLE：**查询中不包含子查询或者UNION，也就是单独的一条SQL 查询中若包含任何复杂的子部分**，最外层查询则被标记为：**PRIMARY 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY**

3**.table:** 输出的行所引用的表 type: 访问类型 从左至右，性能由差到好

![329eb026a43a4ea3924acb170533cb0d.png][]

3.1.ALL: 扫描全表 index: 扫描全部索引树

3.2.index: 扫描全部索引树

3.3.range: 扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常 见于between、等的查询

3.4.ref: **使用非唯一索引或非唯一索引前缀进行的查找，不是主键或不是唯一索引**

3.5.eq\_ref：**唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯 一索引扫描**

3.6.const, system: 单表中最多有一个匹配行，查询起来非常迅速，例如根据主键或唯一索引查 询。system是const类型的特例，当查询的表只有一行的情况下， 使用system。

3.7.NULL: 不用访问表或者索引，直接就能得到结果，如:

![9f2a035f258f4ffaa8466d444580ec99.png][]

**possible\_keys:**（在sql语句中可能执行哪些索引） 表示查询时可能使用的索引。如果是空的，没有相关的索引。这时要提高性能，可 通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引

**key:** （在sql语句中执行了哪些索引）显示MySQL实际决定使用的索引。如果没有索引被选择，是NULL key\_len: 使用到索引字段的长度 注：key\_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key\_len是根据表定义计 算而得，不是通过表内检索出的。

**ref:** 显示哪个字段或常数与key一起被使用

**rows:** 这个数表示mysql要遍历多少数据才能找到，表示MySQL根据表统计信息及索引选用情况， 估算的找到所需的记录所需要读取的行数，在innodb上可能是不准确的

**Extra:** 执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。

Using index：表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引 表就完成了这个查询，这个叫**覆盖索引。（也就是说所查询的数据都在索引表中，不需要再去主表中去查询）**

Using where：表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取 所有需要的数据，则会出现 Using where。

**索引失效**

最直观的体现是在key和possible keys两列中本来应该显示使用了索引，但是事实显示并没有调用索引，索引失效的情况包括如下：  
1.最左原则：类似于字典的目录，这就是一个典型的复合索引

2.判断不等：每个都要判断

3.类型转换：与原类型不符

4.like '%xxx'：第一个字符都不能确定，怎么去索引中比较呢？

5.索引列运算 age + 1：改了原来的值

6.is null 或 is not null ： 全表扫描了

[d3cdc7c071334a3eae6d74e95127a445.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/bfff58f2c4874913bbe1f605ac2b8dfa.png
[05ff46b807d94c0fb51fc73981fa076c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/256bd1c917f14b3f851a5ca82017f6fe.png
[329eb026a43a4ea3924acb170533cb0d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/a50db263ac6c4c808f3962861f68e52f.png
[9f2a035f258f4ffaa8466d444580ec99.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/8941da7ef52e499ba44fc660118248ec.png