MySQL优化之explain执行计划结果解析

1.概念

MySQL执行语句的时候，MySQL优化器会考虑许多技术来有效地执行SQL查询中涉及的查找。优化程序选择执行最有效查询的操作集称为“查询执行计划”，也叫EXPLAIN计划。

2.结果解析

explain语句提供有关MySQL如何执行语句的信息。explain适用于select、delete、insert、replace和update语句。

explain解释了MySQL如何处理语句，包括有关如何连接表以及以何种顺序连接的信息。

explain的输出结果有两种格式。一种是默认的，像查询的表格一样；一种是Json。

我们可以通过查看explain输出的结果，来判断自SQL的性能，从而优化，达到想要的效果。

我们拿一条查询SQL来举个栗子。

select * from school.student where age = 18;

使用explain

explain select * from school.student where age = 18;

现在是没有加任何索引：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	student	NULL	ALL	NULL	NULL	NULL	NULL	4	25	Using where

如果要想输入json格式，explain format = json即可

explain format = json select * from school.student where age = 18;

JSON格式的结果就不放了，比较长，内容基本上和默认格式一样。

给age创建一个索引之后，再使用一下explain。结果如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	student	NULL	ref	student_age_index	student_age_index	4	const	2	100	NULL

我们来解释一下输出的内容

列	JSON名称	含义
id	select_id	编号
select_type	没有	SQL的类型
table	table_name	输出行的表
partitions	partitions	匹配的分区
type	access_type	联接类型
possible_keys	possible_keys	可能选择的索引
key	key	实际选择的索引
key_len	key_len	索引列类型字节长度
ref	ref	与索引比较的列
rows	rows	要检查的数据行数的估算值
filtered	filtered	按条件筛选的行的占比
Extra	没有

2.1.id

这个就是一个连续的编号。

2.2.select_type

查询的类型。

2.3.table

结果所在的表名。

2.4.partitions

查询结果所在的分区。

2.5.type

这一列比较重要。

联接类型（join类型）。描述如何联接表。性能最好到最差依次为：

system、const、eq ref、ref、fulltext、ref or null、index merge、quique subquery、index subquery、range、index、all

解释一下含义

type	解释
system	该表只有一行（=系统表），是const的一个特例。
const	该表最多有一个匹配行，从查询时开始时读取。该列可视为常量。利用主键查询，通常是const。 举个栗子：select * from student where id =1;
eq ref	对于上一个表中的每个行组合，从此表中读取一行。 当连接使用索引的所有部分，并且索引是主键或唯一的非空索引时，将使用它。
ref	对于上一个表中的每个行组合，将从此表中读取具有匹配索引值的所有行。 如果联接只使用键的最左边前缀，或者键不是主键或唯一索引（换句话说，如果联接无法基于键值选择一行），则使用ref。 如果使用的键只匹配几行，那么这是一种很好的连接类型。
fulltext	连接使用全文索引执行。
ref or null	类似于ref，MySQL会对包含值的行进行额外的搜索。最常用于解析子查询。is null也会用ref or null处理。 举个栗子：select * from student where address is null;
index merge	使用索引合并优化。输出行中的列包含所用索引的列表，并包含所用索引的最长关键部分的列表。
unique subquery	in类型的子查询，且子查询查的是唯一索引，用unique subquery代替er ref。 举个栗子：select * from student where class_id in (select id from class where grade=1);
index subquery	类似于unique subquery，子查询查的是非唯一索引。
range	仅检查给定范围内的行，并使用索引选择行。当使用任何 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE 或 IN（） 运算符将键列与常量进行比较时，将使用range
index	扫描索引树。有两种情况： 1.索引是查询的覆盖索引，并满足表中所需的所有数据； 2.从索引中读取的数据执行全表扫描，按索引顺序查找数据行。
all	全表扫描。这是最糟糕的情况。

2.6.possible_keys

执行SQL时，可能用到的索引。

2.7.key

该列指示MySQL实际决定使用的索引。

如果强制使用索引请使用：force index index_name 或use index index_name；
如果强制忽略使用索引：ignore index index_name。

2.8.key_len

该列指示MySQL使用的key的长度。

2.9.ref

该列显示将哪些列或常量与列中指定的索引进行比较。

2.10.rows

该列显示MySQL认为它必须检查才能执行检查的行数。

2.11.filtered

该列显示按表条件筛选的表行的预估百分比。最大为100，这意味着不会对行进行筛选。
从100开始减小的值表示筛选量增加。
举个栗子：如果总共1000行的数据，显示50，则要与其他表联接的行数为1000*50=500。

2.12.extra

该列包含有关MySQL如何解析查询的其他信息。