Mysql学习笔记—MySQL索引优化分析
1.EXPLAIN:输出 SELECT 执行的详细信息
- MySQL 提供了一个
EXPLAIN
命令,它可以对 SELECT 语句进行分析,并输出 SELECT 执行的详细信息,供开发人员有针对性的优化。如下所示
:EXPLAIN SELECT * from user WHERE id < 3;
2.select_type:表示查询的类型,常用的值如下:
1. 最常见的查询类型是SIMPLE
,表示我们的查询没有子查询也没用到UNION
查询。
SIMPLE
: 表示查询语句不包含子查询或unionPRIMARY
:表示此查询是最外层的查询UNION
:表示此查询是UNION的第二个或后续的查询DEPENDENT UNION
:UNION中的第二个或后续的查询语句,使用了外面查询结果UNION RESULT
:UNION的结果SUBQUERY
:SELECT子查询语句DEPENDENT SUBQUERY
:SELECT子查询语句依赖外层查询的结果。
3.type:表示存储引擎查询数据时采用的方式。
1. 比较重要的一个属性
,通过它可以判断出查询是全表扫描还是基于索引的部分扫描。
2. 常用属性值如下,从上至下效率依次增强
。
-
ALL
:表示全表扫描,找到我们想要的值,性能最差,有很大的优化空间。
-
index
:表示基于索引的全表扫描,先扫描索引再扫描全表数据(索引是有序的)
。
2-1. age没有索引,所以type是ALL
-
range
:表示使用索引范围查询。使用>、>=、<、<=、in等等范围性查找的东西。
-
ref
:表示使用非唯一索引进行单值(查询出来就一条数据)
查询。4-1. 先创建一个age的非唯一索引
4-2. age=18,type是ALL,age=22,type是ref,原因是表中age=18的有很多条数据,age=22的只有一条数据
-
eq_ref
:一般情况下出现在多表join查询,表示前面表的每一个记录,都只能匹配后面表的一行结果。
-
const
:表示使用主键或唯一索引做等值查询,常量查询。
-
NULL
:表示不用访问表,速度最快。
4.possible_keys:显示的是能够使用到的索引名称
表示查询时能够使用到的索引
。注意并不一定会真正使用,显示的是索引名称。
5.key:显示的是真正使用到的索引名称
表示查询时真正使用到的索引
,显示的是索引名称。
6.rows:查询到结果需要扫描多少行记录
MySQL查询优化器会根据统计信息,估算SQL要查询到结果需要扫描多少行记录
。原则上rows是越少效率越高,可以直观的了解到SQL效率高低。
7.key_len:表示查询使用了索引的字节数量
表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。key_len的计算规则如下:
1. 字符串类型
字符串长度跟字符集有关
:latin1=1、gbk=2、utf8=3、utf8mb4=4char(n)
:n*字符集长度varchar(n)
:n * 字符集长度 + 2字节
2. 数值类型
TINYINT
:1个字节SMALLINT
:2个字节MEDIUMINT
:3个字节INT、FLOAT
:4个字节BIGINT、DOUBLE
:8个字节
3. 时间类型
DATE
:3个字节TIMESTAMP
:4个字节DATETIME
:8个字节
4. 字段属性
NULL属性
占用1个字节,如果一个字段设置了NOT NULL,则没有此项。
8.Extra:额外的信息
Extra表示很多额外的信息,各种操作会在Extra提示相关信息,常见几种如下:
-
Using where
:表示查询需要通过索引回表查询数据。
-
Using index
:表示查询需要通过索引,索引就可以满足所需数据,覆盖索引。 -
Using filesort
:表示查询出来的结果需要额外排序,数据量小在内存,大的话在磁盘,因此有Using filesort建议优化。 -
Using temprorary
:查询使用到了临时表,一般出现于去重、分组等操作。
9.回表查询
- 在之前介绍过,InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录,InnoDB必须要有,且只有一个。
- 辅助索引的叶子节点存储的是主键值和索引字段值,通过辅助索引无法直接定位行记录,通常情况下,需要扫码两遍索引树。先通过辅助索引定位主键值,然后再通过聚簇索引定位行记录,这就叫做回表查询,它的性能比扫一遍索引树低。
总结:通过索引查询主键值,然后再去聚簇索引查询记录信息
10.覆盖索引
- 在MySQL官网,类似的说法出现在explain查询计划优化章节,即explain的输出结果Extra字段为Using index时,能够触发索引覆盖。
- 不管是SQL-Server官网,还是MySQL官网,都表达了:只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快,这就叫做索引覆盖。
- 实现索引覆盖最常见的方法就是:将被查询的字段,建立到组合索引。