扣绩效篇 | 终于踩到了SQL慢查询的坑！

架构师修行之路

发布于 2021-06-09 17:29:03

6440

发布于 2021-06-09 17:29:03

文章被收录于专栏：架构师

场景一

step1. 问题发现

将应用发布到生产环境后，前端页面请求后台API返回数据，发现至少需要6s。查看到慢SQL

step2. 复现慢SQL

执行SQL

select count(*) from sync_block_data
where unix_timestamp(sync_dt) >= 1539101010
AND unix_timestamp(sync_dt) <= 1539705810

查看耗时

step3. 优化慢SQL sync_dt的类型为datetime类型。换另外一种sql写法，直接通过比较日期而不是通过时间戳进行比较。将sql中的时间戳转化为日期，分别为2018-10-10 00:03:30和2018-10-17 00:03:30

执行SQL

select count(*) from sync_block_data
where sync_dt >= "2018-10-10 00:03:30"
AND sync_dt <= "2018-10-17 00:03:30"

查看耗时

一共耗时419毫秒，和慢查询相比速度提升六倍多

查看执行计划

explain select count(*) from sync_block_data
where sync_dt >= "2018-10-10 00:03:30"
AND sync_dt <= "2018-10-17 00:03:30"

执行计划结果

访问页面，优化完成后请求时间平均为900毫秒

执行计划中慢查询和快查询唯一的区别就是type不一样：慢查询中type为index，快查询中type为range。

step4. 优化慢SQL

这条sql的业务逻辑为统计出最近七天该表的数据量，可以去掉右边的小于等于 执行SQL

select count(*) from sync_block_data
where sync_dt >= "2018-10-10 00:03:30"

查看耗时

一共耗时275毫秒，又将查询时间减少了一半

查看执行计划

explain select count(*) from sync_block_data
where sync_dt >= "2018-10-10 00:03:30"

执行计划结果

type仍是range。但是通过少比较一次将查询速度提高一倍

step5. 继续优化慢SQL

新建一个bigint类型字段sync_dt_long存储sync_dt的毫秒值，并在sync_dt_long字段上建立索引,测试环境下

优化step3慢查询SQL

select count(*) from copy_sync_block_data
where sync_dt >="2018-10-10 13:15:02"

耗时为34毫秒

优化step4慢查询三SQL

select count(*) from copy_sync_block_data
where sync_dt_long >= 1539148502916

耗时为22毫秒测试环境中速度提升10毫秒左右

优化慢查询三sql测试小结：在InnoDB存储引擎下，比较bigint的效率高于datetime 完成三步优化以后生产环境中请求耗时：

速度又快了200毫秒左右。通过给查询的数据加10s缓存，响应速度最快平均为20ms

explain使用介绍

通过explain，可以查看sql语句的执行情况（比如查询的表，使用的索引以及mysql在表中找到所需行的方式等）

用explain查询mysql查询计划的输出参数有

重点关注type，type类型的不同竟然导致性能差六倍！！！

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref。