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系列经典文章