本次慢 sql 优化是大促准备时的一个优化，优化 4c16g 单实例 mysql 支持 QPS 从 437 到 4610，今天发文时 618 大促已经顺利结束，该 mysql 库和应用在整个大促期间运行也非常稳定。本文复盘一下当时的 sql 优化过程

1. 问题背景

大促准备期间发现 4c16G 的单实例 mysql 数据库，每逢流量高峰都会有 cpu 100%的问题，集中在 0 点和 12 点。

但也存在相近大小的流量 cpu 利用率相差很大的情况：从图中可见在 5.12 日 0 点查询 437QPS 时 cpu 利用率达到 100%，而 5.12 日 15:02 分时 625QPS 时 CPU 利用率不到 20%

可见应该是查询语句有差异造成 CPU 利用率高，而此时并没有慢 sql 出现。

2. 问题分析

2.1 分析应用请求及日志

通过应用监控看到 0 点时流量大，很多路由排班表的本地缓存没有命中，导致查询较多。所以想到是否可以通过提高缓存命中率，减少 sql 查询，以降低 CPU 利用率。调整缓存大小，和缓存的有效期。经过测试验证仍然没有解决问题

2.2 分析 sql

虽然没有慢 sql 出现，但还是分析了下 sql。经分析 sql 查询是不是用了索引，发现查询字段也是“走了 idx_road_site 索引”的（注意这里是引号，其实索引并未完全生效）

表结构及索引如下

CREATE TABLE `road_schedule` (    `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',    `courier_id` VARCHAR(240) DEFAULT NULL COMMENT 'courier_id',    `courier_name` VARCHAR(240) DEFAULT NULL COMMENT 'courier_name',    `road_id` VARCHAR(240) DEFAULT NULL COMMENT 'road_id',    `site_id` VARCHAR(240) DEFAULT NULL COMMENT 'site_id',    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',    `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',    `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间戳',    PRIMARY KEY (`id`),    KEY `idx_road_site` (`road_id` , `site_id`))  ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8

代码 sql 如下：

@Select("select courier_id,courier_name,road_id,site_id from road_schedule where road_id = #{roadId} and site_id = #{siteId} order by id desc limit 1")RoadScheduleDto getRoadScheduleById(@Param("roadId")String roadId, @Param("siteId")Integer siteId);

2.3 分析 mysql 连接数指标

前两步都没定位到原因，继续分析 mysql 其他监控指标。

从上图 mysql 监控发现 0 点时连接数突增，所以分析是不是有没有提前创建数据库连接。修改应用连接池配置,单应用最少空闲连接为 50，应用有 4 个实例，这样整个数据库连接数在 4*50=200 个以上，大于图中突增后的总连接数 100

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driverspring.datasource.type=com.alibaba.druid.pool.DruidDataSourcespring.datasource.druid.initial-size=50spring.datasource.druid.min-idle=50spring.datasource.druid.max-active=200spring.datasource.druid.keep-alive=truespring.datasource.druid.validation-query=select 1spring.datasource.druid.filters=stat,log4j2

但是验证后仍然没有解决问题，就犯难了。但是思考原因可能就上面这三点，却没有解决问题。所以又回过来继续从新分析检查，同时也做好了升级 CPU 为 8 核再试的心理准备。

2.4 sql 优化--误入歧途--意外暴露问题

再次分析查询语句，怀疑是不是排序的字段没有走索引，所以将 sql 做了如下调整，并分析了执行计划

#应用中sqlselect * from road_schedule where site_id = '19275xxx' and road_id = '02xx'  order by ts desc limit 1;#认为的按id排序更好的sqlselect * from road_schedule where site_id = '19275xxx' and road_id = '02xx'  order by id desc limit 1;

从执行计划看按 ts 排序 Extra 信息为 Using index condition; Using filesort 猜测按文件排序是不是影响查询的原因

按 id 排序的执行计划如下，Extra 信息为 Using where

对比两个执行计划又都用到了 idx_road_site 索引，所以猜测按 id 排序肯定会快一点

事与愿违，慢 sql 出现了

从优化建议可以看出按 id 排序时，优先使用了主键索引，并没有使用 idx_road_site 索引，所以造成了慢 sql。但同时原始 sql 也显而易见的展现在了眼前，发现组合索引 idx_road_site 的第二个字段 site_id 和表中`site_id` VARCHAR(240) DEFAULT NULL COMMENT '站点id',字段类型并不一致

sql 中 site_id 传参为整型，表中字段为字符串类型，所以断定是字段类型不一致造成的索引失效

select courier_id,courier_name,road_id,site_id from road_schedule where road_id = 'xxx' and site_id = xxxxx order by id desc limit 1;

2.5 sql 修复验证

上一步已经定位到原因，修复 sql 如下，siteId 传参类型为字符串类型

@Select("select courier_id,courier_name,road_id,site_id from road_schedule where road_id = #{roadId} and site_id = #{siteId} order by ts desc limit 1")RoadScheduleDto getRoadScheduleById(@Param("roadId")String roadId, @Param("siteId")String siteId);

经验证完成，完美解决 CPu 利用率在 0 点高的问题。在 0 点时 4c16g 数据库实例轻松支持 1420QPS 的查询，CPU 利用率在 20%以下

后又观测到 4c16g 支持 4610QPS 都没有问题，至此不但优化了 SQL，还节约了实例升级带来的机器成本。

3. 总结

总结本次优化经历

•慢 sql 往往是影响数据库性能的大瓶颈，sql 写好了不但可以优化性能，还能节约机器成本，降本增效。

•最好能看到 sql 语句执行的第一现场，本次主要是由于查看代码时没有及时注意到索引字段的传参类型不对这一细节，造成花了很多时间分析问题

•虽然整个问题分析过程比较曲折，但问题分析的方向应该还是对的，过程中学到不少知识。

•表结构的设计也有一些历史遗留原因，site_id 字段在表中定义为整型可能比较符合业务含义。表字段定义和业务含义一致，写 sql 也不容易犯错

欢迎大家评论交流！