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

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

1. 問題背景

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

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

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

2. 問題分析

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

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

2.2 分析sql

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

表結(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)建時(shí)間', `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新時(shí)間', `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '時(shí)間戳', 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點(diǎn)時(shí)連接數(shù)突增，所以分析是不是有沒有提前創(chuàng)建數(shù)據(jù)庫連接。修改應(yīng)用連接池配置,單應(yīng)用最少空閑連接為50，應(yīng)用有4個(gè)實(shí)例，這樣整個(gè)數(shù)據(jù)庫連接數(shù)在4*50=200個(gè)以上，大于圖中突增后的總連接數(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

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

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

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

#應(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í)行計(jì)劃看按ts排序 Extra 信息為 Using index condition; Using filesort 猜測(cè)按文件排序是不是影響查詢的原因

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

對(duì)比兩個(gè)執(zhí)行計(jì)劃又都用到了idx_road_site索引，所以猜測(cè)按id排序肯定會(huì)快一點(diǎn)

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

從優(yōu)化建議可以看出按id排序時(shí)，優(yōu)先使用了主鍵索引，并沒有使用idx_road_site索引，所以造成了慢sql。但同時(shí)原始sql也顯而易見的展現(xiàn)在了眼前，發(fā)現(xiàn)組合索引idx_road_site的第二個(gè)字段site_id 和表中`site_id` VARCHAR(240) DEFAULT NULL COMMENT '站點(diǎn)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ù)驗(yàn)證

上一步已經(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)驗(yàn)證完成，完美解決CPu利用率在0點(diǎn)高的問題。在0點(diǎn)時(shí)4c16g數(shù)據(jù)庫實(shí)例輕松支持1420QPS 的查詢，CPU利用率在20%以下

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

3. 總結(jié)

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

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

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

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

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

歡迎大家評(píng)論交流！

審核編輯 黃宇