MySQL进阶 (1)：explain执行计划和show profile性能分析

前言

分析一条查询语句，是否存在可优化的空间，在mysql中，常用2种命令来分析sql语句的执行计划，例如 explain、show profiles。两种方式可结合使用。

一、explain执行计划

MySQL explain执行计划用于分析SQL执行效率，用来辅助SQL优化。

下面是一条简单的sql语句，查询所有employees表的记录，利用explain命令，检查该条语句的执行计划参数，如下图，红圈里的title列

参数：id

含义：包含一组数字，表示查询中执行select子句或操作表的顺序

执行顺序：id相同，可以认为是一组，执行顺序由上至下，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

参数：select_type

含义：表示查询中每个select子句的类型（简单 、复杂）

类型等级：

• simple：查询中不包含子查询或者union
• primary：查询中若包含任何复杂的子部分，最外层查询则被标记为primary
• subquery：在select 或 where列表中包含了子查询，该子查询被标记为subquery
• derived：在from列表中包含的子查询被标记为derived（衍生）。若union包含在 from子句的子查询中，外层select将被标记为derived
• union：若第二个select出现在union之后，则被标记为union
• union result：从union表获取结果的select被标记为union result

参数：table

含义：所查询的表名

参数：partitions

含义：版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

参数：type

含义：在表中找到数据行的方式，又称“访问类型”。范围从const(最佳)到ALL(最差)。

范围

• system：是const类型的特殊情况，查询的表中只有一行（少量数据，往往无需进行磁盘IO）
• const：用主键或唯一索引时，连接部分是常量，例如：id=5，索引一次就能找到，速度快
• eq_ref：主键索引或唯一索引。只匹配到一行数据，且索引的所有组成部分都被用上了
• ref：非主键非唯一索引，例如前缀索引、联合索引最左原则，与eq_ref不同的是匹配到了多行
• range：索引范围扫描，常见于between、<、>、>=、in()等的查询
• index：全索引扫描（扫描整个索引树）
• ALL：全表扫描（扫描整张表）
• NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

扫描方式，由快到慢：system > const > eq_ref > ref > range > index > ALL

参数：possible_keys

含义：可能用到的索引。查询字段上若存在索引将被列出，但不一定被查询使用。

参数：key

含义：在查询中实际使用的索引，若没有使用索引，显示为NULL。

参数：key_len

含义：实际使用到的索引长度，显示索引字段的最大可能长度，并非实际使用长度（根据表定义计算而得的）

参数：ref

含义：当使用索引列等值查询时，与索引列进行等值匹配的对象信息

属性

• const：当使用常量等值查询
• 字段名：当关联查询时，会显示相应关联表的关联字段
• func：如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为func
• null：其他情况null

参数：rows

含义：MySQL根据表统计信息及索引选用情况，预估需要读取的记录条数（非结果集里的行数）

参数：Extra

含义：包含不适合在其他列中显示但十分重要的额外信息

属性类型

• distinct：在select部分使用了distinc关键字
• Using index：查询的列被索引覆盖，不需要回表，直接通过索引就可以获取查询的数据。
• Using where：意味着全表扫描或者走索引扫描，但是还有查询条件不在索引字段当中。也就是没有可用的索引查找
• Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。
• Using filesort：当查询中包含order by操作，且无法利用索引完成排序时，MySQL的查询优化器不得不选择相应的排序算法来实现
• Using index for group-by：同Using index，所需数据只需要读取索引，当查询中使用group by或distinct子句时，且分组字段也在索引中。
• Using where;Using index：查询的列被索引覆盖，where筛选条件是索引的前导列的一个范围(select a,b from test where a > 5)，或索引列的非前导列（select a,b from test where b=5)
• NULL：查询的列未被索引覆盖，且where筛选条件是索引的前导列，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引
• Using index condition：称为索引下推 Index Condition Pushdown(ICP)，MySQL 5.6中引入的一种新特性，是存储引擎层使用索引过滤数据的一种优化方式。原来存储引擎通过索引检索到数据，返回给MySQL服务器，服务器再判断数据是否符合条件将其过滤，使用ICP技术后，MySQL服务器会把这部分下推到引擎层，由存储引擎判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。ICP能减少引擎层访问基表的次数和MySQL服务器访问存储引擎的次数，减少io次数，提高查询语句性能

什么是前导列？（即最左匹配原则）

创建复合索引语句的第一列或连续的多列。例：(a, b, c)，则 (a)、(ab)、(abc)都是前导列。前导列必须使用等于或者in操作，最右边的列可以使用不等式。

索引下推 ICP

如果禁用ICP（set optimizer_switch='index_condition_pushdown=off'）

参数：filtered

含义：表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比。估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）

结论

我们一般主要关注possible_keys可能用到的索引，和key实际用到的索引，以及type访问类型，检查是否语句使用到了最佳的索引，另外还需要关注key_len索引长度，比如复合索引，根据最左原则，可能不会全匹配到复合索引的列，根据索引长度可以区分出是否全匹配，从而修改sql语句，保证其查询性能达到最佳。

二、show profile性能分析

分析sql语句，除了使用explain，还可以使用 show profile / show profiles

• show profile      单独分析最近一条执行的sql语句的详细资源占用信息
• show profiles    显示最近发给服务器的多条语句

★ 作用

显示当前会话过程中执行的sql语句的性能(profiling)信息，查出最近执行的SQL语句的运行状态，包括在运行过程中执行了哪些操作，占用了多少时间，利用proflie 功能，可分析一个SQL具体的执行代价是怎么样的，尤其是分析出其最大瓶颈在哪里。

!! 注意：目前profile 功能可提供除内存以外的其他资源消耗统计，例CPU、I/O、context、swap

 

 相关命令

-- 打开/关闭功能：如下两种方式，二选其一

set @@profiling=0;   -- 0.关闭  1.开启

set profiling=ON;    -- off.关闭  on.开启



-- 检查是否开启：当前session是否开启profiling功能，如下两种方式，二选其一

select @@profiling;

show variables like 'profiling';




-- 显示当前会话过程中执行的sql语句的性能(profiling)信息

show profiles;  -- 显示最近发给服务器的多条语句
 
show profile for query [id];  -- 单独分析最近一条执行的sql语句的详细资源占用信息
-- 注意：[id]值是show profiles 查出来的某条记录的Query_ID

如何使用：命令演示

第一步：检查当前session是否开启profile功能：

-- 以下两种方式，选择其一即可

select @@profiling;   -- 1.开启中  0.关闭中

show variables like ‘profiling’;   -- on.开启中   off.关闭中

第二步：开启profiling

-- 以下两种方式，选择其一即可

set @@profiling=0;  -- 1.开启中  0.关闭中

set profiling=ON;  -- on.开启中   off.关闭中

第三步：运行需要分析的sql语句，例如：

select * from titles where emp_no='10001';

-- 注意：也可以分析explain的语句，例如：explain select * from titles where ..

第四步：查看结果：

①  show profiles 显示最近发给服务器的多条语句，完整流程操作如下：

☛ 参数分析：

• Query_Id：标识运行这条语句的id（自增的，每运行一次，id+1）
• Duration：执行该sql语句所用的查询时间
• Query：sql语句

②  show profile  查看某个查询的详细时间耗费，完整流程操作如下：

语句中的 2 是show profiles分析多条语句中的结果集，Query_Id的值

查看cpu、IO等信息

 

作用范围

1. 仅在本会话内起作用，即无法分析本会话外的语句。开启分析功能后，所有本会话中的语句都被分析（甚至包括执行错误的语句），除了 show profile 和 show profiles 两句本身。
2. profiling是会话级的，当会话结束，与之相关的profiling信息也会随之消失。
3. profiling是针对进程而非线程，因此运行在服务器上的其他服务进程可能会影响分析结果.

结论

我们一般主要关注Duration参数，即sql语句运行的时间，来检测此条语句是否是慢查询