MySQL高级(归纳三)之常见瓶颈 和 性能分析工具（Explain）

MySQL高级篇三 性能瓶颈和性能分析工具

一、MySQL常见瓶颈

1.1、MYSQL Query Optimizer（MySQL自带优化）

MySQL底层有 Query Optimizer（底层有进行调优）

Mysql中有专门负责优化SELECT语句的优化器模块
主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计
简单的说就是：mysql认为最优的数据检索方式，但不见得是DBA工程师认为是最优的，这部分最耗费时间


当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQLQuery Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。

并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。

然后分析Query中的Hint信息(如果有)，看显示Hint信息是否可以完全确定该Query 的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

1.2、MySQL常见瓶颈(IO、CPU、锁、硬件)

1. CPU ==> SQL中对大量数据进行比较、关联、排序、分组
2. IO： 实例内存满足不了缓存数据或排序等需要，导致产生大量 物理 IO。
3. IO： 查询执行效率低，扫描过多数据行。
4. 锁: 不适宜的锁的设置，导致线程阻塞，性能下降。
5. 锁: 死锁，线程之间交叉调用资源，导致死锁，程序卡住。
6. 服务器硬件的性能瓶颈：top,free, iostat和vmstat来查看系统的性能状态

二、性能分析工具Explain

2.1、Explain是什么

介绍：

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

官网入口

2.2、Explain能干嘛

能知道下面的这些信息

1. 表的读取顺序
2. 哪些索引可以使用
3. 数据读取操作的操作类型
4. 哪些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被优化器查询

2.3、Explain的使用

语法：Explain + SQL语句

例如：

执行计划包含的信息：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

2.4、Explain字段说明

2.4.1、字段概论

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
表的执行顺序	区分sql的查询类型	数据库表	sql执行的效率等级	分析可能用到的索引	真正用到的索引	索引的长度(使用空间)	说明引用的表的列	影响的行数	包含不适合在其他列中显示但十分重要的额外信息

2.4.2、id说明（三种情况）

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

简单的说就是表的执行顺序

情况一：id相同，执行顺序由上至下

id相同，执行顺序由上至下
此例中 先执行where 后的第一条语句 t1.id = t2.id 通过 t1.id 关联 t2.id 。
而 t2.id 的结果建立在 t2.id=t3.id 的基础之上。

情况二：id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。

情况三：id相同不同，同时存在

id如果相同，可以认为是一组，从上往下顺序执行。
在所有组中，id值越大，优先级越高，越先执行
衍生表 ==> derived2 ==>derived + 2
(2 表示由 id =2 的查询衍生出来的表。type 肯定是 all ，因为衍生的表没有建立索引)。

2.4.3、select_type说明

意思是查询的类型

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

01 常见的有6种

02 具体分析

类型	说明
SIMPLE	简单的 select 查询,查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
DERIVED	在FROM列表中包含的子查询被标记为DERIVED(衍生) MySQL会递归执行这些子查询, 把结果放在临时表里。
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DEPENDENT SUBQUERY	在SELECT或WHERE列表中包含了子查询,子查询基于外层
UNCACHEABLE SUBQUREY	无法被缓存的子查询
UNION	若第二个SELECT出现在UNION之后，则被标记为UNION； 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT	从UNION表获取结果的SELECT

03 具体案例

2.4.4、table和说明

显示这一行的数据是关于哪张表的

2.4.5、type说明

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏

完整的顺序：

• system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range(尽量保证) > index > ALL

常见实际的顺序：

• system > const > eq_ref >ref > range > index> ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

ALL类型	All 是非常垃圾，表示全表扫描
index类型	index index类型只遍历索引树。 这通常比ALL快，因为索引文件通常比数据文件小。 （也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
ref类型	ref 非唯一性索引扫描，返回匹配某个单独值的所有行.（准确性高）
range类型	range 只检索给定范围的行,使用一个索引来选择行 （一般就是在你的where语句中出现了between、<、>、in等的查询）

2.4.6、possible_keys和key和 key_len说明

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

key：
01、用到的索引
02、实际使用的索引。如果为NULL，则没有使用索引
03、查询中若使用了覆盖索引，则该索引和查询的select字段重叠

key_len
01、表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
02、key_len字段能够帮你检查是否充分的利用上了索引

2.4.7、ref和rows说明

ref：

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

rows：

1. rows列显示MySQL认为它执行查询时必须检查的行数。
2. 越少越好

2.4.7、Extra说明

• Using filesort：表示出现了内部自排序，影响性能
• Using temporary：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。非常炸裂，更影响性能
• USING index：表示使用覆盖索引，效率很好。（表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
• Using where：表明使用了where过滤
• using join buffer：使用了连接缓存

USING index详细说明：
表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！

如果同时出现using where，表明索引被用来执行索引键值的查找;

如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找

三、Explain之sql执行顺序案例分析

案例：

执行顺序流程:

---

上一篇：MySQL高级(归纳二)之MySQL索引介绍分析

下一篇：MySQL高级(归纳四)之sql性能调优分析与详解