MySQL性能分析explain

性能分析的前置知识

MySQL query optimizer 查询优化器

MySQL为优化SELECT查询语句定义了优化器,通过计算分析系统中收集的统计信息,为客户端请求查询提供MySQL认为的最优执行计划 (这里的优化是MySQL认为的最优检索方式,并不一定是DBA认为的最优解决方式,这部分是最耗时的工作)

客户端向MySQL发送一条Query请求,命令解析器完成请求分类,区别出SELECT请求转发给MySQL Query Optimizer,MySQL首先会对整条SQL进行优化 处理掉一些常量表达式的预算直接换成常量值.并对Query中的查询条件进行简化和转换,比如必掉一些无用或显而易见的条件 结构调整等. 然后分析Query中的Hint信息(如果存在),看显示Hint信息是否可以完全确定该Query执行计划.如果没有Hint信息或者Hint信息还不足完成确定的执行计划,会读取涉及对象的统计信息,根据Query进行写相应的计算分析,然后最终得到执行计划

MySQL常见的瓶颈

• CPU CPU在饱和的时候一般发生在数据装入内存或者从磁盘读取数据
• IO 磁盘IO瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件瓶颈 top free iostat vmstat 查看系统性能
• 自身的SQL语句复杂 存在很大优化的空间 我们开发工程师更多的会关注在SQL的优化上 索引的优化上

explain的使用

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

使用 EXPLAIN关键字 后跟 SQL语句

EXPLAIN SQL

查询的结果包含 id , select_type , table , partitions , type , possible_keys , key , key_len , ref , rows, filtered , Extra

EXPLAIN 返回展示的信息

1. 表读取顺序 通过id定位
2. 数据读取操作的类型 select_type
3. 哪些索引可以使用 possible_keys
4. 哪些索引被实际使用 key
5. 索引的引用关系 ref
6. 每张表有多少行被优化器查询 rows

下面对explain 返回数据逐一进行分析

id

SELECT 查询的序列号 包含一组数据 表示SELECT 子句或操作表的顺序

一共存在三种情况

• id相同, 执行顺序从上到下依次执行
• id不同, id大的先执行
• id相同和不同的同时存在 id大的先执行 id相同的从上到下依次执行

id相同的情况

EXPLAIN SELECT t2.* FROM t1,t2,t3 WHERE t1.id = t3.id AND t1.id = t2.id AND t1.name=''

可以看到 所有的 id 都是1,MySQL的查询表顺序从上到下 先t3 -> t1 -> t2

id不同情况

EXPLAIN SELECT t2.* FROM t2
WHERE t2.id = (SELECT t1.id FROM t1 WHERE id = 
(SELECT t3.id FROM t3 WHERE name = '1' ))

上述是一个嵌套子查询,我们查看explain的结果发现 id 各不相同,以id从大到小的顺序, 先查询t3表 通过select_type的值字面意思也知道是一个子查询 再查询t1表 也是子查询 最后查询t1表 PRIMARY主键查询

执行顺序就是 t3->t2->t1 符合我们SELECT 语句的语义.

总结 id越大越优先查询,id相同从上至下依次查询 此处需要注意的优化点的是我们使用MySQL最佳实践是小表驱动大表 小表的查询优先于大表的查询使得SQL效率更高

select_type 与 table

select_type 表示查询类型,主要用于区别普通查询 联合查询 子查询等复杂查询

1. SIMPLE 简单查询,查询中不包含子查询或者UNION操作
2. PRIMARY 查询中包含任何复杂的子查询 最外层查询被标记为 PRIMARY
3. SUBQUERY 在SELECT 或 WHERE 列表中包含了子查询
4. DERIVED 在FROM列表中包含子查询被标记为DERIVED(衍生),MySQL会递归执行这些字查询把结果放在临时表中
5. UNION 若第二个SELECT 出现UNION之后,则被标记为UNION,若UNION包含在FROM子句的子查询中外层SELECT将被标记为 DERIVED
6. UNION_RESULT 从UNION表中获取结果的SELECT

table 表示来自哪张表 需要注意的是 table 数据可能是 derived[数字] 表示这个表是临时表 后面的数字代表id 衍生于哪张表

例如 derived2 表示 衍生与 id=2 的表 t2

type介绍

type字面意思表示访问类型,是非常重要的一个指标,结果值从好到坏依次如下:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

比较常见的 system > const > eq_ref > range > index > ALL

一般来说,保证查询至少到range 最好到 ref

• system 表示只有一行记录,这是const类型的特例,平时不会出现,忽略不计

• const: 表示通过索引一次就找到了,const用于比较 primary key 或者 unique 索引.因为只匹配一行数据,查询速度很快,比如根据主键查询,MySQL将查询转换成常量

• eq_ref: 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配.常见于主键或者唯一索引扫描

  EXPLAIN SELECT * FROM t1,t2 WHERE t1.id = t2.id
  

• ref: 非唯一索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问.返回所以匹配单个值的行,可能找到多个符合条件的行,属于查询和扫描的混合体

  -- ref
  EXPLAIN SELECT * FROM test_index WHERE common_index = ''
  

  

• range: 只检索给定范围的行,使用一个索引来选择行. key列显示使用了哪个索引一般就是在你的where语句中出现了beween < > in 操作的时候.这种范围扫描比全表扫描好,因为只需要开始于索引的某一个点,结束另一点,不用扫描全部索引

• index: FULL INDEX SCAN, index 与 ALL区别在于index类型只遍历索引树,通常比ALL快.因为索引文件通常比数据文件小很多 (虽然 index 和 ALL 都是读全表,但是index是从索引中读取索引一般缓存在内存中,ALL是从硬盘中读取)

  EXPLAIN SELECT common_index FROM test_index
  

  

  此时 SELECT common_index 本身就是索引,没有需要查询其他字段 直接扫描索引表就行了

• ALL: 全表扫描 遍历全表逐一匹配

possible_keys 和 key

• possible_keys 显示可能应用在这张表的索引,一个或多个.查询的字段若可以使用到的索引将被列出,但不一定被实际查询使用
• key 实际使用的索引. 如果为NULL 表示没有使用索引,查询中若使用了覆盖索引,则该索引仅出现在key列表中

key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引长度.在不损失精度的情况下,长度越短越好.key_len显示的是索引字段的最大可能长度,并非实际使用长度,是根据表定义计算而得,不是通过表内检索出来的. 在创建索引的时候 可以指定索引的长度,在明确在某一个长度下就可以表示唯一的情况下,可以使用短索引,但是不建议这样操作

ref

显示索引的哪一个列被使用,如果可能的话是一个常数.哪些列或者常量被用于查询索引列上的值

rows

根据表统计信息,大概估算出找到所需记录而要读取的行数

Extra

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

• Using filesort

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

  -- Extra Using filesort
  EXPLAIN SELECT * FROM test_index ORDER BY without_index
  

  

• Using temporary

  使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表.常见于排序order by 和分组查询group by

  EXPLAIN SELECT * FROM test_index GROUP BY without_index
  

  

• Using Index

  表示相应的SELECT操作中使用了覆盖索引(Convering Index),避免访问了表的数据行,效率很高

  如果同时出现Using where 表示索引被用来执行索引键值的查找

  如果没有同时出现Using where,表示索引用来读取数据而非执行查询动作

• Using where

  表示使用了WHERE 过滤

• Using join filter

  使用了连接缓存

• impossible where

  where 子句的值总是返回false,不能用来获取任何元素

• select tables optimized away

  在么有GROUP BY 子句的情况下,基于索引优化MIN/MAX操作,或者对于MyISAM存储引擎优化COUNT(*)操作,不必等待执行阶段再进行计算,查询执行计划生成的阶段即完成优化

• distinct

  优化distinct操作,在找到第一个匹配的元素后即停止查找同样值的动作

覆盖索引 Convering Index

覆盖索引也称索引覆盖

• select 数据列只用从索引中就可以读取,不需要读取数据航.MySQL可以利用索引返回select列表中的字段,而不需要再去读数据文件. 查询列要被所建的索引覆盖

• 索引是一种高效查找行的方法,但是一般数据库也能使用索引找到 一个列的数据.但是他不必须读取整行.索引的叶子节点存储了索引的数据,直接从索引上就可以得到想要的数据,不需要再通过叶子节点上索引指向的文件地址再去读文件.因为索引已经包含了所有SELECT中需要查询的字段

  -- Covering Index
  EXPLAIN SELECT complex_index_a,complex_index_b FROM test_index WHERE complex_index_a = ''