Mysql执行计划

Mysql执行计划

explain工具查看执行计划

MySQL 5.6.3之前只能分析 SELECT语句， MySQL5.6.3以后可以分析select、update、delete、

insert。

都已经mysql 8.0+了。你还不会用explain看你的sql语句吗

create table tuser( id int primary key, name varchar(100), age int, sex char(1), address varchar(100) );alter table tuser add index idx_name_age(name(100),age); alter table tuser add index idx_sex(sex(1)); insert into tuser(id,name,age,sex,address) values (1,'zhangsan',20,'0','万里大厦');

介绍：

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句的执行计划进行分析, 并输出

SELECT 执行的详细信息, 以供开发人员针对性优化.

使用explain这个命令来查看一个SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有

没有做全表扫描，这都可以通过explain命令来查看。

可以通过explain命令深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到

的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 explain 就可以了, 例如:

参数说明

id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.

select_type: SELECT 查询的类型.

table: 查询的是哪个表

partitions: 匹配的分区，表被分区时才会用到。

type: join 类型

possible_keys: 此次查询中可能选用的索引

key: 此次查询中确切使用到的索引.

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

rows: 显示此查询一共扫描了多少行. 这个是一个估计值。

filtered: 表示此查询条件所过滤的数据的百分比

extra: 额外的信息

key_len: 8

ref: const

rows: 1

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

下面逐一解释：

id

每个单位查询的SELECT语句都会自动分配的一个唯一标识符，表示查询中操作表的顺序，有四种情况：

id相同：执行顺序由上到下

id不同：如果是子查询，id号会自增，id越大，优先级越高。

id相同的不同的同时存在

id列为null的就表示这是一个结果集，不需要使用它来进行查询。

select_type（重要）

单位查询的查询类型，比如：普通查询、联合查询(union、union all)、子查询等复杂查询。

simple

表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，

且只有一个

primary

一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。

且只有一个

union （用到了union关键字）

union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表

select_type都是union

subquery

除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery

table

显示的单位查询的表名，有如下几种情况：

如果查询使用了别名，那么这里显示的是别名

如果不涉及对数据表的操作，那么这显示为null

如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于

这个查询产生。

如果是尖括号括起来的<union M,N>，与类似，也是一个临时表，表示这个结果来自于union查询

的id为M,N的结果集

type（重要）

显示的是单位查询的连接类型或者理解为访问类型，访问性能依次从好到差：

system
const
eq_ref
ref 
fulltext
ref_or_null
unique_subquery 
index_subquery 
range 
index_merge 
index 
ALL
#注意事项
- 除了all之外，其他的type都可以使用到索引 
- 除了index_merge之外，其他的type只可以用到一个索引 
- 最少要使用到range级别

这里只介绍只个重要的，剩下的就自行了解了

const（重要）

使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据

库也叫做唯一索引扫描。

eq_ref（重要） 注意和ref的区别

多表关联

等值连接

等值连接的两个表的列是唯一索引列或者主键列

此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果.

并且查询的比较操作通常是 = , 查询效率较高.

ref（重要）

多表关联

等值连接

等值连接的两个表的列是非唯一索引列

针对非唯一性索引，使用等值（=）查询。或者是使用了最左前缀规则索引的查询。

不重要：

fulltext

全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代

价，优先选择使用全文索引

ref_or_null

与ref方法类似，只是增加了null值的比较。实际用的不多。

unique_subquery

用于where中的in形式子查询，子查询返回不重复值唯一值

index_subquery

用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询

去重。

range（重要）

索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

index_merge

表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方

排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如rang

index（重要）

select结果列中使用到了索引，type会显示为index。

全部索引扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可

以使用索引排序或者分组的查询

all（重要）

这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

possible_keys

此次查询中可能选用的索引，一个或多个

key

查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的

select_type这里只会出现一个。

key_len

用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查

询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的

列，这里不会计算进去。

留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。

另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到

key_len中。

ref

如果是使用的常数等值查询，这里会显示const

如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段

如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows

这里是执行计划中估算的扫描行数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值，主要原

因是InnoDB里面使用了MVCC并发机制）

**extra（重要）**有时候也是null,要结合type,key等相关字段一起分析

这个列包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多，有几十种

using index（重要）

查询时不需要回表查询，直接通过索引就可以获取查询的结果数据。

表示相应的SELECT查询中使用到了覆盖索引（Covering Index），避免访问表的数据行，效率

不错！

如果同时出现Using Where ，说明索引被用来执行查找索引键值

如果没有同时出现Using Where ，表明索引用来读取数据而非执行查找动作

using where（重要）

表示Mysql将对storage engine提取的结果进行过滤，过滤条件字段无索引；

**using index condition(重要) ** ：也叫ICP,索引下推，用于组合索引中，在索引中断的情况下，将失效索引字段带入存储引擎中查询，这里简单介绍，演示下，不在本篇幅详细讲解，很重要的知识点，建议读者自行百度掌握

1.测试联合索引的最左原则的时候, 发现了5.6版本后的新特性Index Condition Pushdown

2.含义就是存储引擎层根据索引尽可能的过滤数据,然后在返回给服务器层根据where其他条件进行过滤

3.比如我有这样的联合索引 KEY name_gid_age_index (name,gid,age) , 查询的时候where name=‘taoshihan’ and age=1 , 没有按顺序连续查条件, 后面那个age就用不到索引

4.这时就会出现下面的情况

`CREATE` `TABLE` ``index_test` (`` ```id` ``int``(10) unsigned ``NOT` `NULL` `AUTO_INCREMENT,`` `````name`````varchar``(100) ``NOT` `NULL` `DEFAULT` `''``,`` ```gid` ``int``(11) ``NOT` `NULL` `DEFAULT` `'0'``,`` ```age` ``int``(11) ``NOT` `NULL` `DEFAULT` `'0'``,`` ```score` ``int``(10) unsigned ``NOT` `NULL` `DEFAULT` `'0'``,`` ``PRIMARY` `KEY` `(`id`),`` ``KEY` ``name_gid_age_index` (```name```,`gid`,`age`),`` ``KEY` ``score_index` (`score`)``) ENGINE=InnoDB AUTO_INCREMENT=4 ``DEFAULT` `CHARSET=utf8`

using filesort（重要） 这种情况要优化，尽量依靠索引本人排好的顺序

排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中

说明MySQL会使用一个外部的索引排序，而不是按照索引顺序进行读取。

MySQL中无法利用索引完成的排序操作称为 文件排序

写在最后，感谢点赞关注收藏转发

欢迎关注我的微信公众号 【猿之村】

来聊聊Java面试
加我的微信进一步交流和学习，微信手动搜索
【codeyuanzhicunup】添加即可
如有相关技术问题欢迎留言探讨，公众号主要用于技术分享，包括常见面试题剖析、以及源码解读、微服务框架、技术热点等。