SQL调优实战总结
前言
作为开发人员,我们免不了与sql打交道。有些sql可能在业务的最开始,执行是毫无问题的。但是随着业务量的提升以及业务复杂度的加深,可能之前的sql就会逐渐展现出疲惫之势了。这时就会面临sql调优。
那么调优到底如何调?不同的人有不同的姿势。可能大部分人首先想到的就是加索引。
没错,加索引是一种比较典型,也是一种比较廉价的手段。
但是,索引怎么加?加在哪里?加之后是否会对已有的其他sql产生影响?这些问题都是需要考虑的。
同时也应该意识到,索引是一把双刃剑,就像硬币的两面,加快查询速度的同时,也会拖慢我们插入删除的速度。
当然,除了加索引之外,在创造sql时,也可以借鉴一些成熟的经验总结,去预防一些问题。如遵循最左匹配的原则、select时指明具体字段、不建议使用函数、超过3张表不建议join等等。
但是实际上,不同业务场景面对的问题是不尽相同的。同一条sql在数据量、数据分布不同的情况下,其执行结果可能是截然不同的。当sql无法持续支撑业务继续发展时,我们就需要结合实际进行调优。
方法论
对于sql调优的整个过程,我有一套自己的方法论,可以大致分为以下四个阶段:
1.白盒分析
此阶段我们可以根据自己已有的知识积累和经验,猜想sql可能慢的原因。
2.执行计划解读
通过explain结果,解读并模拟出mysql服务器对sql的真实执行过程。
3.瓶颈点确定
白盒分析+执行计划解读之后,基本可以确定sql可能慢的原因。
4.对症下药
找到瓶颈点后,逐个击破。
接下来,我就通过两个实例(单表查询和连接查询)与大家一起交流下调优的一些心得。
以下的调优过程主要是站在sql层面进行优化。并非站在整个系统方面,如分表、切换存储媒介等。
实战一
主人公sql为单表查询,其执行时间可达到2.3s。
select
很多字段
from
t_voucher_header
where
period_year = 2020
and period_month = 10
and user_je_source_name = 'xxx'
and `status` in (10, 30, 50)
and employee_number != '1000'
and can_auto_push = 1
and is_delete = 0
and batch_id > xxx
limit
200
sql背景
全表数据量在700w+,batch_id为主键字段。
已存在的相关索引为:
联合索引1:user_je_source_name、voucher_category、record_type、company_code、status
联合索引2:period_year、period_month、user_je_source_name、voucher_category、is_delete、company_code、status
联合索引3:period_year、period_month、user_je_source_name、status
白盒分析
对于单表查询比较简单,主要有两点可以考量。
1.单表查询,是否用到了索引?
2.单表查询,是否用对了索引?
执行计划解读
以上我们可以读到这些信息:
1.索引使用长度较低,仅仅使用了联合索引的第一个字段来加快查找速度。
2.Using index condition是在5.6之后引入的新特性,索引条件下推。出现情况是在搜索条件中虽出现了索引列,却并不能用到(前缀匹配嘛)。但是由于索引中包含了搜索条件,因此可利用索引进行过滤。
以本sql看,查询条件为period_year、period_month、user_je_source