MYSQL 深入探索系列六 SQL执行计划

概述

       好久不见了，近期一直在忙项目的事，才有时间写博客，近期频繁出现sql问题，今天正好不忙咱们看看千万级别的表到底该如何优化sql。

案例

        近期有个小伙伴生产环境收到了告警，有个6千万的日志表，查询耗时大概120秒，之前都好好的，条件也很简单（操作人和被操作人），我们查看sql的执行计划，发现它走的主键索引，operatorName和userName建立了联合索引按说应该走这个索引的呀。

       sql 如下，条件很简单。

SELECT * from recover_log where operatorName="" and userName="" ORDER BY id LIMIT 10

      我们看下他的执行计划：

mysql> EXPLAIN SELECT * from recover_log where operatorName="" and userName="" ORDER BY id LIMIT 10;
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | recover_log | NULL       | index | NULL          | PRIMARY | 8       | NULL |   10 |        1 | Using where |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set

      上面的执行计划中，直接采用的主键索引，那为什么这么慢呢？按说应该用到联合索引的呀，我们通过force index强制走联合索引后sql耗时20ms。

       其实这个问题的本质是mysql发现数据即便走联合索引数据也会很多，而且查询的列有的必须再次回表查询，导致直接放弃了联合索引走了主键索引，而恰巧呢条件中的数据并非真实存在，导致对整个表进行了扫描。

        

可能有的同学不怎么看执行计划，我们简单过一下重点关注这几个字段 type possible_keys key  extra 。

type有这么几个值： 

  1.  eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配

  2.  const: 类似于PRIMARY 只匹配到了一行

  3.  system: 表中只有一行记录

  4.  index: 遍历了整棵索引树

  5.  ref: 非唯一性索引扫描，返回匹配某个单独值的所有行

  6.  ALL: 对全表进行了扫描，性能最慢
  7. range: 对索引数进行了范围扫描

possible_keys字段是该条sql在执行中可能用到的索引。

key字段是该条sql执行中真正用的索引。

extra这个字段是一些附加信息，但是也很重要，常见有以下几种值：

  • Using index：使用索引覆盖情况，也就是说索引中的数据直接返回的。
  • Using index condition：发生了索引下推的情况，也就是回表操作，如果符合的数据量不大还好，如果量很大会造成大量的回表操作，导致sql耗时严重。
  • Using filesort：可能在磁盘文件中进行了排序或内存中进行的排序，性能也是很慢，尽可能不让extra出现此值。
  • Using where：全表扫描的时候或者用索引扫描的时候通过where条件返回了部分数据。
  • Using temporary：用临时表保存结果，一般GROUP BY 操作会出现此值，性能也较慢，比如：
mysql> explain select * from sign_job_info_copy1 group by mssp_id;
+----+-------------+---------------------+------+---------------+------+---------+------+----------+---------------------------------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows     | Extra                           |
+----+-------------+---------------------+------+---------------+------+---------+------+----------+---------------------------------+
|  1 | SIMPLE      | sign_job_info_copy1 | ALL  | NULL          | NULL | NULL    | NULL | 12305505 | Using temporary; Using filesort |
+----+-------------+---------------------+------+---------------+------+---------+------+----------+---------------------------------+
1 row in set (0.01 sec)

总结

       mysql的执行计划有时候不一定是最优的，我们还需要利用explain多多了解执行计划，根据执行情况分析出sql的执行慢的原因。

       关注执行计划的时候不能只看某个值，应该多个字段联合起来分析sql，好了这期就先简单到这了。

MYSQL系列经典文章