QPS提升10倍的sql優(yōu)化

本次慢sql優(yōu)化是大促準(zhǔn)備時的一個優(yōu)化，優(yōu)化4c16g單實例mysql支持QPS從437到4610，今天發(fā)文時618大促已經(jīng)順利結(jié)束，該mysql庫和應(yīng)用在整個大促期間運行也非常穩(wěn)定。本文復(fù)盤一下當(dāng)時的sql優(yōu)化過程

1. 問題背景

大促準(zhǔn)備期間發(fā)現(xiàn)4c16G的單實例mysql數(shù)據(jù)庫，每逢流量高峰都會有cpu 100%的問題，集中在0點和12點。

但也存在相近大小的流量cpu利用率相差很大的情況：從圖中可見在5.12日0點查詢437QPS時cpu利用率達(dá)到100%，而5.12日15:02分時 625QPS時CPU利用率不到20%

可見應(yīng)該是查詢語句有差異造成CPU利用率高，而此時并沒有慢sql出現(xiàn)。

2. 問題分析

2.1 分析應(yīng)用請求及日志

通過應(yīng)用監(jiān)控看到0點時流量大，很多路由排班表的本地緩存沒有命中，導(dǎo)致查詢較多。所以想到是否可以通過提高緩存命中率，減少sql查詢，以降低CPU利用率。調(diào)整緩存大小，和緩存的有效期。經(jīng)過測試驗證仍然沒有解決問題

2.2 分析sql

雖然沒有慢sql出現(xiàn)，但還是分析了下sql。經(jīng)分析sql 查詢是不是用了索引，發(fā)現(xiàn)查詢字段也是“走了idx_road_site索引”的（注意這里是引號，其實索引并未完全生效）

表結(jié)構(gòu)及索引如下

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 '創(chuàng)建時間', `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連接數(shù)指標(biāo)

前兩步都沒定位到原因，繼續(xù)分析mysql其他監(jiān)控指標(biāo)。

從上圖mysql監(jiān)控發(fā)現(xiàn)0點時連接數(shù)突增，所以分析是不是有沒有提前創(chuàng)建數(shù)據(jù)庫連接。修改應(yīng)用連接池配置,單應(yīng)用最少空閑連接為50，應(yīng)用有4個實例，這樣整個數(shù)據(jù)庫連接數(shù)在4*50=200個以上，大于圖中突增后的總連接數(shù)100

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

但是驗證后仍然沒有解決問題，就犯難了。但是思考原因可能就上面這三點，卻沒有解決問題。所以又回過來繼續(xù)從新分析檢查，同時也做好了升級CPU為8核再試的心理準(zhǔn)備。

2.4 sql優(yōu)化--誤入歧途--意外暴露問題

再次分析查詢語句，懷疑是不是排序的字段沒有走索引，所以將sql做了如下調(diào)整，并分析了執(zhí)行計劃

#應(yīng)用中sql select * from road_schedule where site_id = '19275xxx' and road_id = '02xx' order by ts desc limit 1; #認(rèn)為的按id排序更好的sql select * from road_schedule where site_id = '19275xxx' and road_id = '02xx' order by id desc limit 1;

從執(zhí)行計劃看按ts排序 Extra 信息為 Using index condition; Using filesort 猜測按文件排序是不是影響查詢的原因

按id排序的執(zhí)行計劃如下，Extra信息為 Using where

對比兩個執(zhí)行計劃又都用到了idx_road_site索引，所以猜測按id排序肯定會快一點

事與愿違，慢sql出現(xiàn)了

從優(yōu)化建議可以看出按id排序時，優(yōu)先使用了主鍵索引，并沒有使用idx_road_site索引，所以造成了慢sql。但同時原始sql也顯而易見的展現(xiàn)在了眼前，發(fā)現(xiàn)組合索引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修復(fù)驗證

上一步已經(jīng)定位到原因，修復(fù)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);

經(jīng)驗證完成，完美解決CPu利用率在0點高的問題。在0點時4c16g數(shù)據(jù)庫實例輕松支持1420QPS 的查詢，CPU利用率在20%以下

后又觀測到4c16g支持4610QPS都沒有問題，至此不但優(yōu)化了SQL，還節(jié)約了實例升級帶來的機器成本。

3. 總結(jié)

總結(jié)本次優(yōu)化經(jīng)歷

?慢sql 往往是影響數(shù)據(jù)庫性能的大瓶頸，sql寫好了不但可以優(yōu)化性能，還能節(jié)約機器成本，降本增效。

?最好能看到sql語句執(zhí)行的第一現(xiàn)場，本次主要是由于查看代碼時沒有及時注意到索引字段的傳參類型不對這一細(xì)節(jié)，造成花了很多時間分析問題

?雖然整個問題分析過程比較曲折，但問題分析的方向應(yīng)該還是對的，過程中學(xué)到不少知識。

?表結(jié)構(gòu)的設(shè)計也有一些歷史遺留原因，site_id 字段在表中定義為整型可能比較符合業(yè)務(wù)含義。表字段定義和業(yè)務(wù)含義一致，寫sql也不容易犯錯

歡迎大家評論交流！

審核編輯 黃宇