概述
好久不见了,近期一直在忙项目的事,才有时间写博客,近期频繁出现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有这么几个值:
- eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配
-
const: 类似于PRIMARY 只匹配到了一行
-
system: 表中只有一行记录
-
index: 遍历了整棵索引树
-
ref: 非唯一性索引扫描,返回匹配某个单独值的所有行
- ALL: 对全表进行了扫描,性能最慢
- 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系列经典文章