【线上SQL优化复盘】

最新推荐文章于 2024-08-09 16:35:51 发布

爱抄中间件代码的路人丙

最新推荐文章于 2024-08-09 16:35:51 发布

阅读量1k

点赞数 25

分类专栏： MySQL索引优化记录-生产案列工作日常文章标签： sql mysql 性能优化

本文链接：https://blog.csdn.net/whatjeans/article/details/139596004

版权

工作日常同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

MySQL索引优化记录-生产案列

1 篇文章 0 订阅

订阅专栏

MySQL 线上性能优化实战（无八股、纯线上问题记录解决以及心得）

最近终于有时间可以来跟大家分享一下之前参与的一个项目上线遇到的性能问题：大部分问题都由于慢SQL导致
阅读本篇文章，你将获得以下收获：

SQL实际优化案列
除了常见八股索引失效之外：排序字段编码不一致导致索引失效（即使使用use、force index关键字也无法解决）
深入理解Innodb引擎中B+树索引
理解随机IO、以及顺序IO
了解MySQL优化器是如何选择最优索引的

背景：项目测试上线，系统非常不稳定，小程序、app端、PC端都非常的卡顿、进入系统后也非常的卡顿（当时遇到2个难题）
后面运维排查了一下，发现数据库所在的服务器的磁盘IO非常高（而且数据库会偶发性崩掉），后面重启后开启了慢日志查询，把大部分慢sql都打印出来了,除此之外，在连续的几天的观测发现，system服务的内存一直涨（即使流量高峰过去了，内存监控都没有落下来，而且还中途还出现过一次部分集群节点OOM killed重启）
难题1：慢查询SQL优化
难题2：system服务POD内存泄漏导致OOM Killed(这个是笔者发现的)

笔者在其中主要负责慢SQL的优化工作、system服务内存泄漏（OOM killed）问题排查以及保证xx模块上线的稳定

1、 SQL实际优化案列

1.1 索引失效、force index 失效均失效场景

原sql如下

> select
> 		su.realname as name,
> 		su.user_type,
>		sii.total_period as hours
> 		from study_user_record_statistic sii 
> 		join sys_user su on sii.user_id = su.id
> 		where su.org_code = 'xxxxxxx'
> 		order by sii.total_period desc

以上sql，通过explain 分析发现，study_user_record_statistic 表查询的时候，走的全表扫描；sys_user 表查询的时候，走的org_code_IDX

表数据情况：
sys_user 100w左右
study_user_record_statistic 85w左右
未优化之前，sql查询耗时：15s左右，explain 分析是因为study_user_record_statistic 未使用user_id索引导致全表扫描

当时推测造成索引失效的情况如下：

（1）mysql误判：索引user_id的cordinoritty 值未更新，与表中数据的区分度差别太大等导致sql索引优化器更倾向于走全表
后面低峰期的时候，更新了cordinoritty 且统计了一下user_id在study_user_record_statistic 表中的区分度还是很高的，排除这种可能
（2）使用原sql添加force index(user_id_IDX)，explain 发现study_user_record_statistic 任然全表扫描，且key关键字为null
当时很纳闷，难道force index 失效了，不应该哇，但是在extra栏，看到一个以前没见过的标识，后面才发现study_user_record_statistic 中user_id的排序编码和sys_user 的id排序编码不一致
后面推测应该是数据工程师做数据迁移的时候造成的

解决方法也就明了：将关联字段的排序规则设置一致，explain 分析之后，sql使用了索引，查询平均时间降到了0.4~0.6s左右