MySQL性能分析

MySQL常见性能瓶颈

• CPU：CPU的饱和一般发生在IO时
• IO：IO瓶颈发生在装入数据远大于内存容量时
• 服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态

Explain

是什么（查看执行计划）

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

能干嘛

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

怎么玩

• 查看语句：
  ecplain 要查看的sql语句（横表）
  或
  ecplain 要查看的sql语句\G（竖表）

• 执行包含的信息：
  

各字段解释：

这是一个实际的例子，我们可以看到使用ecplain 要查看的sql语句后出现如下结果：

• id – 查看表的读取顺序

  id相同 执行顺序由上至下；

  id不同 id值越大优先级越高；

• select_type – 显示区别联合查询、子查询、普通查询等

  SIMPLE – 简单的select查询 不包含子查询或union；

  PRIMARY – 查询中若包含任何复杂的子部分，最外层查询则被标记为；

  SUBQUERY – 在select或where包含的子查询；

  DERIVED – 在from列表包含的子查询被标记为DERIVED（衍生），mysql会递归执行这些子查询，把结果放在临时表里；

  UNION – 若第二个select出现在union后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为：DERIVED；

  UNION RESULT – 从union表获取结果的select；

• table – 显示表名

• type – 显示查询用了何种类型

  system – 表只有一行记录（等于系统表），这是const类型得特例，平时不会出现，可以忽略不记。

  const – 表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如果将主键置于where列表中，mysql就能将该查询转换到一个常量。

  eq_ref – 唯一性扫描索引，对于每个索引键，表中刚好只有一条记录与之匹配。常用于主键或唯一索引扫描。

  ref – 非唯一性扫描索引，返回匹配某个单独值的所有行。上面eq_ref的多值情况。如where age=1

  range – 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在where语句中出现了between、<、>、in等的查询。这种范围扫描查询比全表扫描要好。

  index – Full Index Scan，index与ALL区别为index类型只遍历索引树。都是读全表，但是index是从索引中读取，all是从硬盘中读，而且索引文件通常比数据文件小。

  all – Full Table Scan，遍历全表来找到匹配的行。

  从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL

  细节：

  1. type 是 ALL，当数据到达百万以上一定要优化。
  2. 一般来说，如果要优化得保证查询至少达到range级别，最好达到ref。

• possible_keys和key

  possible_key – 显示可能应用在这张表中的索引，一个或多个。理论上可能被使用的索引，但不一定被查询实际使用。

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

  四种情况：

  1. possible_key有值，key有值：正常，有的时候前者有多个值但后者只有一个也正常。
  2. possible_key有值，key无值：索引失效了，出现问题了。
  3. possible_key无值，key有值：条件查询没有用到索引或没有条件查询，但查询的列刚好顺序、数量和索引一致。
  4. possible_key无值，key无值：正常，就是你没建索引。

• key_len – 表示索引中使用的字节数，可通过该列计算【查询中使用的索引的长度】，在查询结果一样的情况下，该值越小越好。key_len显示的值为索引字段的最大可能长度，而非实际使用长度，即通过表定义计算而得，不是通过表内检索而得。

  假设你建立复合索引(col1,col2)，如果【通过col1条件查询】和【通过col1和col2条件查询】的结果一样，那么前者比较好，因为只需要用一个字段，key_len的值会比较小，上面也说过是通过表定义的长度来决定key_len的值。

• ref – 显示key中索引参照的值。

  库名.表名.字段名 – 表示索引参照的值是哪个库的哪个表的哪个字段；

  const – 表示索引参照的值是常量，一般是where id=1这样；

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

• Extra – 十分重要的额外信息。

  Using filesort – 说明mysql完全或部分没有按照你所建的索引排序，比较需要优化了。mysql无法利用索引完成的排序操作称为“文件排序”；

  **Using temporary **-- 使用了临时表保存中间结果，mysql对查询结果排序时使用临时表。这也比较需要优化，因为临时表的创建和删除都是比较费性能的，常见于order by和group by；

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

  Using where – 使用了where过滤；

  Using join buffer – 使用了连接缓存，如果总是出现这个字段，可以去配置文件中适当调大这个值；

  Impossible where – where子句的值总是false，不能用来获取任何元组；

  Select tables optimized away – 在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化；

  distinct – 优化distinct操作，在找到第一组匹配的元组后马上停止找相同值的动作。

注意：

1. group by等排序时，如果有用到索引，最好严格按照索引的顺序来，比如，存在复合索引(col1,col2)，排序时如果跳过col1，直接使用col2排序，会导致出现Using filesort、Using temporary等比较严重的问题。
2. 覆盖索引，select后面的列名完全与建立的索引顺序、数量一致。这样可以直接使用索引读取数据，避免读取表的数据行。