我们在执行一条SQL语句的时候，如果我们想要知道这条SQL语句查询了哪些表，有没有使用索引，获取数据的时候遍历了多少行数据，我们可以通过EXPLAIN命令来查看这些执行信息，这些执行信息统称为执行计划。
1.使用 EXPLAIN 查看执行计划
使用方式 explain sql。
explain.png
下面对图中的每一个字段进行说明。
-
id：每个执行计划都有一个id，如果是联合查询，这里还将会有多个id。
-
select_type：表示 select 的查询类型，常见的有 SIMPLE（普通查询，即没有联合查询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子查询）等。
-
table：当前执行计划查询的表，如果给表起了别名，这里显示的就是别名信息。
-
partitions：访问的分区表信息。
-
type：表示从表中查询到行所执行的方式，这个是SQL优化中一个很重要的指标，结果值从好到差依次为：system>const>eq_ref>ref>range>index>ALL。
-
system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据
const.png
- eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一主键索引作为关联条件。
eq_ref.png
- ref：非唯一索引扫描或者唯一索引的最左匹配原则扫描。
ref.png
- range：索引范围扫描，比如：>，<，between等操作。
range.png
- index：索引全表扫描，此时遍历整个索引树。
index.png
- ALL：表示全表扫描，需要遍历全表找到需要的行。
ALL.png
-
possible_keys：可能使用到的索引。
-
key：实际使用的索引。
-
ref：关联id信息。
-
rows：查找到记录扫描的行数。
-
filtered：查找所需要的记录占总扫描数的比例。
-
Extra：额外信息。
Extra通常会包含以下值：
示例表结构：
CREATE TABLE `user` ( `id` int(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `age` int(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_unique_name` (`name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
name字段创建了一个普通索引，age没有创建索引。
- Using where：表示where条件中有非索引字段作为 条件查询。
using_where.png
- Using index：表示select语句使用了覆盖索引，即在索引树中就可以找到需要查询的数据而不需要回表。
using_index.png
- Using where,Using index：表示查找的列被索引覆盖，且where条件字段有索引，且为模糊查询。
using_where_index.png
- null：表示查询的列没有被索引覆盖，如果有where条件的话，则所有的where条件必须有索引，且非模糊查询。
null.png
- Using index condition：表示查询列没有被索引覆盖，如果有查询条件where，则查询条件字段有索引，且为模糊查询。
using_index_condition.png
- Using temporary：表示使用了临时表存储数据，比如查询语句中使用了group by，distinct等。
using_temporary.png
- Using filesort：表示使用了文件做排序。
using_filesort.png
2.使用Show Profile 分析 SQL 执行性能
Profile可以分析执行线程的状态和时间，还支持进一步选择ALL，CPU，MEMORY，BLOCK IO，CONTEXT SWITCHES等类型来查询SQL语句在不同系统资源上所消耗的时间。相关命令如下：
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type参数：
ALL：显示所有开销信息
BLOCK IO：阻塞的输入输出次数
CONTEXT SWITCHES：上下文切换相关开销信息
CPU：显示CPU的相关开销信息
IPC：接收和发送消息的相关开销信息
MEMORY ：显示内存相关的开销，目前无用
PAGE FAULTS ：显示页面错误相关开销信息
SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置(行数)
SWAPS：显示swap交换次数的相关开销信息
通过 select @@have_profiling 查询数据库是否支持Show Profile功能（MySQL在5.0.37版本之后才支持该功能）：
have_profiling.png
通过 select @@profiling 查询是否开启了该功能：
profiling.png
如果没有开启该功能可以设置profiling的值为1开启session级别的profiling：
set profiling = 1;
Show Profiles 只显示最近发给服务器的 SQL 语句，默认情况下是记录最近已执行的 15 条记录，我们可以重新设置 profiling_history_size 增大该存储记录，最大值为 100。
set profiling_history_size = 20;
show variables like 'profiling_history_size';
profiling_history_size.png
show profiling.png
通过show profiles命令可以拿到我们刚刚执行的SQL语句的Query_ID，我们可以再通过 show profile for query Query_ID语句就能查到对应的SQL语句在执行过程中线程的每个状态所消耗的时间了。
query_id.png
- starting：开始
- checking permissions：校验权限
- Opening tables：打开表
- init：初始化
- System lock：系统锁定
- optimizing：优化
- statistics：统计
- preparing：准备
- executing：执行
- Sending data：读取数据并返回给客户端
- end：结束
- query end：查询结束
- closing tables：关闭表
- freeing items：释放资源
- cleaning up：清理
结束！
网友评论