explain详解，看这一篇就行了！一、前言随着用户量和数据量的不断增长，系统里面有SQL慢查询问题是无法避免的。一般

一、前言

随着用户量和数据量的不断增长，系统里面有SQL慢查询问题是无法避免的。一般情况下系统中如果出现慢查询，意味着接口响应慢、接口超时等问题。如果是高并发的场景，可能会出现系统数据库的连接被占满的情况，直接导致服务不可用。优化慢查询的方法有很多，我们今天先来讲讲索引优化，因为它是解决慢查询sql问题最有效的手段。

二、EXPLAIN介绍

我们要如何才能得知你写的sql的执行情况呢？MySQL提供了一个explain关键字，用于显示sql查询的执行计划（即数据库如何执行你的查询），在sql前面加上explain关键字，就能够看到sql的执行计划，通过执行计划，我们可以清楚的看到sql有没有走索引、索引执行顺序等。

explain官方介绍：

EXPLAIN可以使用于 SELECT， DELETE， INSERT， REPLACE，和 UPDATE语句。
当EXPLAIN与可解释的语句一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。也就是说，MySQL解释了它将如何处理该语句，包括有关如何连接表以及以何种顺序连接表的信息。
当EXPLAIN与非可解释的语句一起使用时，它将显示在命名连接中执行的语句的执行计划。

我们直接通过例子来看看使用explain关键字之后的效果：

下面是explain输出结果及字段解释：

列名	说明
id	查询的标识符（多表 JOIN 时，相同 id 表示同一执行层级）。
select_type	查询类型（如 SIMPLE, PRIMARY, SUBQUERY, DERIVED 等）。
table	访问的表名。
partitions	匹配的分区（如果表有分区）。
type	关键字段：访问类型（性能从优到差排序：`system` > `const` > `eq_ref` > `ref` > `range` > `index` > `ALL`）。
possible_keys	可能使用的索引。
key	实际使用的索引。
key_len	使用的索引长度（字节数）。
ref	与索引比较的列或常量。
rows	关键字段：预估需要扫描的行数。（只是一个预估值）
filtered	过滤后剩余行的百分比。
Extra	关键字段：附加信息（如 Using where, Using index, Using temporary 等）。

下面我们来对这个表进行详细说明：

id

表示查询中执行select子句或者操作表的顺序，id的值越大，代表优先级越高，越先被执行。如果id值一样，那执行顺序自上而下。

explain
    select comment_body
from comment
where article_id = (
    select article_id from article where user_id = (
        select user_id from user where user_name = 'zhangsan'));

三个表依次嵌套，发现最里层的子查询 id最大，最先执行。

select_type

主要是区分普通查询、联合查询、子查询等。

SIMPLE：简单的select查询，不包含子查询与union

PRIMARY：查询中包含复杂的子部分，最外层会被标记为primary

SUBQUERY：在select或者where列表中包含了子查询

DERIVED：在from列表中包含的子查询衍生表

UNION：若第二个select出现在union之后，则被标记为union

UNION RESESULT：从union表获取结果的select

table

这一行数据是哪个表的数据。查询的表名，并不一定是真实存在的表，有别名显示别名，也可能为临时表。

type

查询中使用了何种类型。

最佳类型到最差类型：system>const>eq_ref>ref>range>index>All

system

当表仅有一行记录时（系统表），数据量很少，往往不需要进行磁盘IO，速度非常快。需同时满足以下条件:

‌表中仅有一条记录‌：查询的表必须是单行数据表。
‌存储引擎统计精确‌：需使用MyISAM或Memory等具备精确统计信息的存储引擎。
‌直接访问系统表‌：部分系统变量查询（如@@version）可能直接命中系统表，无需扫描用户表。

此时我这张测试表里面只有一条数据。

const

单表操作的时候，查询使用了主键或者唯一索引。出现const的话就表示发生下面两种情况：

在整个查询过程中这个表最多只会有一条匹配的行，比如主键id=1肯定就只有一行。
只需读取一次表数据就能够取得所需的结果。

我这里通过主键索引dept_id直接定位到唯一的一行记录，因此type为const。

表结构：

create table user
(
    user_id   int auto_increment comment '用户唯一id--主键'
        primary key,
    user_name varchar(30) not null comment '用户名',
    constraint idx_user_name
        unique (user_name)
)
    charset = utf8mb3;

我这里是使用到了唯一索引。

eq_ref

type=eq_ref出现在以下场景：

触发条件：

多表连接查询‌：当执行JOIN操作时，被驱动表通过主键或唯一索引进行等值匹配访问。
索引要求‌：被驱动表必须使用主键或非空的唯一索引（所有索引列都参与等值比较）。
匹配方式‌：每个索引键值在被驱动表中最多只匹配一行记录。

这里的user表（id主键）和order表（user_id字段建立关系）。此时对user表的访问类型为eq_ref，因为：

u.user_id是主键
通过o.user_id与之精确等值匹配
每个user_id值在user表中只对应一条记录。

ref

常用于非主键和非唯一索引扫描。相比于req_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，可能会找到多条符合条件的行。

1.简单查询，user_info表中的age为普通索引，不是主键索引，也不是唯一索引。

range

范围扫描通常出现在in，between，>，<，>=等操作中。使用一个索引来检索给定范围的行。

index

全索引扫描，只遍历索引树，这个通常比all快，因为索引文件通常比数据文件比较小，也就是说虽然all和index都是读全表，但是index是从索引文件中读取的，而all是从硬盘中读取的数据。

index包括order by主键和select索引列两种情况：

select索引列

如图，type为index，extra字段值为Using index，这种情况就是索引覆盖。所需要的数据都在索引列，无需进行回表。

order by 主键

这种情况会按照索引顺序全表扫描，拿到的数据都是按照主键排好序的，不需要额外进行排序。

all

表示全表扫描，从硬盘中的数据文件中读取数据，若表数据量大（百万条数据）的时候就要考虑优化了

总结：一般来说至少要达到range级别，最好能达到ref级别。

possible_keys

本次查询中可能会选用到的索引。

key

本次查询中具体使用到的索引。

key_len

这个key_len列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

举个例子，上面movie_actor表的联合索引idx_movie_actor_id由movie_id和actor_id两个int列组成，每个int是4个字节：

通过这个结果中的key_len=4可以推断出查询使用了第一个列：movie_id列来执行索引查找。

key_len计算规则

字符串： char(n)和varchar(n)，n均代表字符数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节； char(n)：如果存汉字长度就是 3n 字节； varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串；
数值类型： tinyint：1字节 smallint：2字节 int：4字节 bigint：8字节
时间类型： date：3字节 timestamp：4字节 datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL；

ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：user.id）。

rows

估算要找到所需的记录，需要读取的行数，这只是个预估值，值越小越好。

filtered

该列是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

explain详解，看这一篇就行了！