0
  • 聊天消息
  • 系统消息
  • 评论与回复
登录后你可以
  • 下载海量资料
  • 学习在线课程
  • 观看威廉希尔官方网站 视频
  • 写文章/发帖/加入社区
会员中心
创作中心

完善资料让更多小伙伴认识你,还能领取20积分哦,立即完善>

3天内不再提示

QPS提升10倍的sql优化

京东云 来源:jf_75140285 作者:jf_75140285 2024-08-21 11:12 次阅读

本次慢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出现。

wKgaombFWxCAYtW-AADyzYog65Q806.png


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其他监控指标。

wKgZombFWxOAbrwiAAEjWW8783c244.png


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

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

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

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

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

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

wKgaombFWxWAcXKOAAFrjfz_U9I331.png


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

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

wKgZombFWxaAImluAAFf9VTGl_4698.png


事与愿违,慢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;

wKgaombFWxeAVTgBAAUk3sCfShY546.png

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%以下

wKgZombFWxqAaGdFAAC79g-SpkE465.png


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

wKgaombFWxqAeMkxAADAA9mZFRQ074.png


3. 总结

总结本次优化经历

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

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

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

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


欢迎大家评论交流!

审核编辑 黄宇

声明:本文内容及配图由入驻作者撰写或者入驻合作网站授权转载。文章观点仅代表作者本人,不代表电子发烧友网立场。文章及其配图仅供工程师学习之用,如有内容侵权或者其他违规问题,请联系本站处理。 举报投诉
  • SQL
    SQL
    +关注

    关注

    1

    文章

    763

    浏览量

    44123
  • QPS
    QPS
    +关注

    关注

    0

    文章

    24

    浏览量

    8800
收藏 人收藏

    评论

    相关推荐

    数据库SQL优化

    数据库执行SQL都会先进行语义解析,然后将SQL分成一步一步可执行的计划,然后逐步执行。通过分析执行计划,我们可以清晰的看到数据库执行的操作,这对于数据库SQL优化具有重大意义。 1
    的头像 发表于 10-09 15:43 1045次阅读
    数据库<b class='flag-5'>SQL</b>的<b class='flag-5'>优化</b>

    MySQL的执行过程 SQL语句性能优化常用策略

    回顾 MySQL 的执行过程,帮助介绍如何进行 sql 优化
    的头像 发表于 12-12 10:26 658次阅读
    MySQL的执行过程 <b class='flag-5'>SQL</b>语句性能<b class='flag-5'>优化</b>常用策略

    2017双11威廉希尔官方网站 揭秘—TDDL/DRDS 的类 KV 查询优化实践

    能做到大幅度提升,是因为查询完全绕开了 SQL 在 MySQL Server 层的各项开销,查询链路被极致缩短,事实上,这样的优化思路对 TDDL/DRDS 也同样适用。TDDL/DRDS 目前
    发表于 12-29 14:29

    第三代DRDS分布式SQL引擎全新发布

    SQL 优化策略,一些重要的例如:对 Filter 的上拉、下压、推导等优化,确保 DRDS 可以准确的识别出 SQL 中可以下推的部分,这个能很大程度上
    发表于 05-28 20:35

    Spark SQL的工作原理和性能优化

    Spark SQL(九):工作原理和性能优化
    发表于 06-12 16:21

    30种SQL语句优化总结

    必须掌握的30种SQL语句优化
    发表于 04-21 11:38

    数据库设计及开发规范之sql性能优化

    数据库设计及开发规范,sql性能优化
    发表于 05-08 10:58

    SQL语句怎么优化

    SQL语句优化——结合书籍论坛小结
    发表于 06-14 14:46

    内存条配置优化SQL Server服务器性能

    内存条配置优化SQL Server服务器性能  Microsoft SQL Server 2000 的 内存管理组件消除了对 SQL Server 可用的内存进行手工管理的需要。
    发表于 01-11 11:00 1063次阅读

    基于SQL Server的中文分词系统设计及应用

    随着当前信息化威廉希尔官方网站 的发展,优化设计中文分析系统已成为必然趋势,有助于提升网络索引擎面对中文信息的检索能力,本篇中将分析基于SQL Server的中文分词系统设计需求,从而优化设计中文分
    发表于 11-14 17:46 2708次下载
    基于<b class='flag-5'>SQL</b> Server的中文分词系统设计及应用

    SQL后悔药,SQL性能优化SQL规范优雅

    每一个好习惯都是一笔财富,本文基于MySQL,分SQL后悔药, SQL性能优化SQL规范优雅三个方向,分享写SQL的21个好习惯,谢谢阅读
    的头像 发表于 11-14 09:54 1824次阅读

    SQL子查询优化是怎么回事

    子查询 (Subquery)的优化一直以来都是 SQL 查询优化中的难点之一。 关联子查询的基本执行方式类似于 Nested-Loop,但是这种执行方式的效率常常低到难以忍受。 当数据量稍大时,必须
    的头像 发表于 02-01 13:55 2045次阅读
    <b class='flag-5'>SQL</b>子查询<b class='flag-5'>优化</b>是怎么回事

    SQL优化技巧分享

    一、查询SQL尽量不要使用select *,而是具体字段
    的头像 发表于 09-06 10:24 1410次阅读

    一文终结SQL子查询优化

    子查询(Subquery)的优化一直以来都是 SQL 查询优化中的难点之一。关联子查询的基本执行方式类似于 Nested-Loop,但是这种执行方式的效率常常低到难以忍受。
    的头像 发表于 04-28 14:19 755次阅读
    一文终结<b class='flag-5'>SQL</b>子查询<b class='flag-5'>优化</b>

    Oracle长耗时SQL优化案例

    最近在生产客服平台,运营岗老师反馈,一个2w人的企业,在信息详情查询时,加载时间过长,越70s左右出结果,需要后台优化SQL
    的头像 发表于 05-19 15:02 1023次阅读