每一篇文章的开始照旧是一点废话,今天应该是来北京刚好满一年吧,端午和实验室的大佬们吹吹逼,已经感觉到自己被深深吊打了,知耻而后勇吧,反正不去写算法我挺开心的,深度瞎学,人工智障。最近也反思了一下我最开始找工作的时候没有理想的算法offer,大概是诗和远方总是在撩拨我那不安躁动的心,整个人都显得很浮躁,没有去认真的去钻研算法原理,也没有总结出一个好一点的方法论。那从现在立志去做一个高级的curdboy,为时不晚,哈哈。
本来这周是打算把hashmap的升级版currenthashmap写完的,顺便引入一下多线程,讲一讲各种锁,但是技术菜鸟的我,上周写了3个sql,3个都报了慢sql,是真的菜,甚至有报5s,被主动查杀的。感到有点羞愧,所以先补一补sql基础吧。
回归主题,本期主题是mysql性能调优。在正式开始之前我们先思考几个问题:
1。你的sql问题的表现形式是什么。响应时间很慢?偶然出现的mysql,死锁?
2。你想优化的目标是什么。响应时间,sql稳定性。
在分析sql之前先分析结果
当然我们今天想说的性能调优指的是降低sql的响应时间。要想降低响应时间,首先我们就需要理解服务器执行sql花费在了哪里。
![7830e6db67391c8dd0af9ceb98a098b3.png](https://i-blog.csdnimg.cn/blog_migrate/1a1321068f5d7938870334085633fbf1.jpeg)
值得优化的查询:
解决最主要的矛盾
优化的方向:
1.扫描最少的行
2.返回最少的列。
从实战出发
//先大概介绍一下业务背景,业务系统每天都会有50w条数据写到我们的数据库,我们加工处理完之后再传给下游
//pt是时间版本号,fileverison是文件版本号(会存在逻辑错误,或者各种修复数据,导致需要重刷业务数据)
//下游每次会使用10个线程,每次读取5000行
select
id
from xx
where is_delete = 0
and pt = #{pt}
and fileversion = #{version}
limit #{pageNo},#{pageSize}
场景的特点,查询的字段重复性高 limit到后面,扫描的数量多
版本最开始上线:盲目加索引。
出现问题:数据库慢查询,数据量900w左右,某一天的数据重跑了多次,数据量大概在400w左右。
问题定位:
![ea7723e37c8b575dd60c5d094769a7da.png](https://i-blog.csdnimg.cn/blog_migrate/f2f069a506bd3758db2eaa50f2537af9.jpeg)
新建的索引为非聚簇索引,每一次查询需要先查询辅助索引,再查询主键索引。
在limit 395000,5000 的时候整个过程需要访问 800w次,失败的尝试
Explain
![26ab1397466b47bad6bb1e2af4334cf4.png](https://i-blog.csdnimg.cn/blog_migrate/4fa352e87a56e567225516e53e416cea.jpeg)
type:ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右,性能从差到好)
Extra:using index using where
第二次尝试:添加pt和version联合索引:
![44cea8e8bd66a5221f0ff3ff810ce76d.png](https://i-blog.csdnimg.cn/blog_migrate/4952a8ebb67d9569a521a89bc0da7aae.jpeg)
联合索引,相当于B+树的叶子节点,再加了一层叶子节点。效果依旧不理想。
在limit 395000,5000 的时候整个过程需要访问 100w次,失败的尝试。
提到了联合索引,再提一个sql的优化。
第三次尝试:覆盖索引
索引原则:
一星:索引将相关的原则放在一起
二星:索引中的顺序和查找中的顺序放在一起
三星:如果索引中的列包含了查询中的所有列
覆盖索引
在limit 395000,5000 的时候整个过程需要访问 50w次,失败的尝试
SELECT
t1.*
FROM
ershou_ledger_receive_all t1
JOIN (select
id
from ershou_ledger_receive_all
where is_delete = 0
and pt = #{pt}
and fileversion = #{version}
limit #{pageNo},#{pageSize}
)t2 ON t1.id = t2.id
第四次尝试
在limit 395000,5000 的时候整个过程需要访问 5000次.查询次数缩小200倍,性能稳定,并且与数据量无关
select
fileversion
from ershou_ledger_receive_all
where pt = #{pt} and fileversion=#{fileversion}
order by id limit 1
SELECT
t1.*
FROM
ershou_ledger_receive_all t1
JOIN (select
id
from ershou_ledger_receive_all
where id >#{id}
limit 5000
)t2 ON t1.id = t2.id