文章目录

性能分析Mysql查询优化器（Mysql Query Optimizer）Mysql常见瓶颈EXPLAIN简介EXPLAIN是什么？EXPLAIN怎么使用？ EXPLAIN能干嘛？id列 了解表的读取顺序select_type列 数据读取操作的操作类型。table列 表的名字type列 访问类型详解及排列possible_keys列 哪些索引可能被使用key列 哪些索引被实际使用（判断索引是否失效）key_len列 索引中使用的字节数ref列 表之间的引用rows列 每张表有多少行被优化器查询Extra列 包含不适合在其他列中显示但十分重要的额外信息 EXPLAIN案例 文章说明

性能分析

要说sql有问题，需要拿出证据，因此需要性能分析

Mysql查询优化器（Mysql Query Optimizer）

Mysql中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划(它认为最优的数据检索方式，不见得是DBA认为是最优的，这部分最耗费时间，如果知道mysql底层是如何执行一条sql，可以帮助我们更好地优化sql)当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是 SELECT 转发给MysQL Query Optimizer，MysQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、进行结构调整。然后分析 Query 中的 Hint 信息(如果有)，看显示Hint信息是否可以完全确定该Query 的执行计划。如果没有 Hint 或Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析，然后再得出最后的执行计划。

Mysql常见瓶颈

CPU: CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候服务器硬件的性能瓶颈（机器性能本来就弱）: top,free,iostat和vmstat来查看系统的性能状态

EXPLAIN简介

EXPLAIN是什么？

EXPLAIN：SQL的执行计划，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的，进而分析查询语句或是表结构的性能瓶颈。

EXPLAIN怎么使用？

语法：explain关键字 + 所写的SQL。

【表格形式查看】

explain select * from tbl_emp;

【列形式查看】

mysql> explain select * from pms_category \G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: pms_categorypartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 1425filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)

EXPLAIN能干嘛？

通过上面的表格可以查看以下信息：

id列 了解表的读取顺序

值有以下三种情况：

id相同，执行顺序由上至下。id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。id相同和不同 同时存在。永远是id大的优先级最高，id相等的时候从上到下顺序执行。

总结：id相同上下顺序走，不同id大的先走

select_type列 数据读取操作的操作类型。

select_type：数据查询的类型，主要是用于区别，普通查询、联合查询、子查询等复杂查询。

【常见值】

SIMPLE：简单的SELECT查询，查询中不包含子查询或者UNION。PRIMARY：查询中如果包含任何复杂的子部分，最外层查询则被标记为PRIMARY。SUBQUERY：在SELECT或者WHERE子句中包含了子查询。DERIVED：在FROM子句中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表中。UNION：如果第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。UNION RESULT：从UNION表获取结果的SELECT。

table列 表的名字

标题已经通俗易懂了，哈哈

type列 访问类型详解及排列

【type列常见值】

system：一张表只有一行记录（如系统参数表，每个参数字段只存储一个值），这是const类型的特例（平时不会出现，这个也可以忽略不计）const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转化为一个常量。eq_ref：唯一性索引扫描，读取本表中和关联表表中的每行组合成的一行，查出来只有一条记录。除了system和const类型之外, 这是最好的连接类型。ref：非唯一性索引扫描，返回本表和关联表某个值匹配的所有行，查出来有多条记录。

【例子1】

【例子2】

range：只检索给定范围的行，一般就是在WHERE语句中出现了BETWEEN、< >、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引。index：Full Index Scan，全索引扫描，index和ALL的区别为index类型只遍历索引树，不用全表扫描。也就是说虽然ALL和index都是读全表，但是index是从索引中读的，ALL是从磁盘中读取的。**因为索引文件通常比数据文件小，所以Index往往比ALL快。ALL：Full Table Scan，没有用到索引，全表扫描。

从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL。除了ALL没有用到索引，其他级别都用到索引了，表示全表扫描。

**总结：**多看sql的type，如果是All，争取优化成其他的，一般来说，得保证查询至少达到range级别，最好达到ref。

possible_keys列 哪些索引可能被使用

possible_keys：显示可能应用在这张表中的索引，一个或者多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key列 哪些索引被实际使用（判断索引是否失效）

key：实际使用的索引。如果为NULL，则没有使用索引。

查询中如果使用了覆盖索引，则该索引仅仅出现在key列表中，不出现在possible_keys中。

key_len列 索引中使用的字节数

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

在不损失精度的情况下，长度越短越好。一般是精度要求更高（根据姓名和城市查询一个人比只根据姓名查询一个人精度更高），key_len越长。

key_len计算规则：/qq_34930488/article/details/102931490

ref列 表之间的引用

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

由key_len可知t1表的idx col1 col2被充分使用，cl1匹配t2表的col1，co2匹配了一个常量，即’ac’

rows列 每张表有多少行被优化器查询

rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数。

【没建索引之前】

【建索引之后】

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

Extra列也会有一系列的值，下面是这些值的解释：

Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为"文件内排序"。

总结：出现Using filesort尽快优化

Using temporary：使用了临时表（查询结束之后还要删除）保存中间结果，MySQL在対查询结果排序时使用了临时表。常见于排序order by和分组查询group by。临时表対系统性能损耗很大。

总结：出现Using temporary尽快优化

Using index：表示相应的SELECT操作中使用了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现Using where，表示索引被用来执行索引键值的查找；如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

覆盖索引：就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。 注意：如果要使用覆盖索引，不要写SELECT *，要写出具体的字段。

Using where：表明使用了WHERE过滤。Using join buffer：使用了连接缓存。如果join特别多，可以调大配置文件的buffer。impossible where：WHERE子句的值总是false，不能用来获取任何元组。select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算查询执行计划生成的阶段即完成优化。distinct：找到第一个匹配的元组后停止同样值的动作

EXPLAIN案例

文章说明

本文章为本人学习尚硅谷的学习笔记，文章中大部分内容来源于尚硅谷视频（点击学习尚硅谷相关课程），也有部分内容来自于自己的思考，发布文章是想帮助其他学习的人更方便地整理自己的笔记或者直接通过文章学习相关知识，如有侵权请联系删除，最后对尚硅谷的优质课程表示感谢。