数据库管理-第211期 记一次简单的SQL性能优化(20240623)
作者:胖头鱼的鱼缸(尹海文)
Oracle ACE Pro: Database(Oracle与MySQL)
PostgreSQL ACE Partner
10年数据库行业经验,现主要从事数据库服务工作
拥有OCM 11g/12c/19c、MySQL 8.0 OCP、Exadata、CDP等认证
墨天轮MVP、认证技术专家、年度墨力之星,ITPUB认证专家、专家百人团成员,OCM讲师,PolarDB开源社区技术顾问,HaloDB外聘技术顾问,OceanBase观察团成员,青学会MOP技术社区(青年数据库学习互助会)技术顾问
圈内拥有“总监”、“保安”、“国产数据库最大敌人”等称号,非著名社恐(社交恐怖分子)
公众号:胖头鱼的鱼缸;CSDN:胖头鱼的鱼缸(尹海文);墨天轮:胖头鱼的鱼缸;ITPUB:yhw1809。
除授权转载并标明出处外,均为“非法”抄袭
这是发生在本周五的一个很简单的SQL性能优化案例。而且这次没有发生在一体机上,而是自建的3节点X86集群上。因为本案例均涉及生产环境内容,所有内容均脱敏且无截图。
1 起因
业务方反馈说,在调整数据内容过后,一张未分区的表上的update语句执行会超过2分钟,而该语句在之前运行不会这么久。这个语句的大概内容是:
update xxx set a=:1,b=:2,c=:3 where id=:4 and sn=:5;
2 变化
首先,既然业务方说数据调整过后出现的问题,那么看看调整了哪些内容,其实很简单,就是增加了id列的类型,sn也扩展了,因此数据量从原来的60W扩展到了120W。但就查询你语句来说没有任何变化。
3 排查问题
首先从执行计划来看,索引是走的id对应的列上的索引,再检查表的索引,是id列和sn列上都建了单独的索引。
执行计划一开始显示索引仅扫描了1700+行就因超时被程序结束了,怀疑统计信息有问题,收集统计信息后,预估行变成了60W行。因此向业务方确认id和sn列中数据的唯一性情况,反馈为id为分类唯一性较少,sn唯一性较多,但是二者必须联查才能获取对应的唯一行。
4 优化
方案1
直接调整语句将唯一性较高的列放在where语句的第一位作为引导列,使用sn列上的单独索引:
update xxx set a=:1,b=:2,c=:3 where sn=:5 and id=:4;
这样可以通过sn列上的索引筛选出更少的行再与id列进行匹配,好处是无需调整索引,但性能不一定是最好的。
方案2
在方案1的基础上删除sn和id列上的索引,并重新创建包含sn、id列且sn为引导列的复合索引:
create index idx_xxx on xxx(sn,id);
这样可以更好的利用sn和id列的关系,充分利用索引,问题就是重建索引过程中可能影响语句运行。
方案3
基于方案2,其实还可以锦上添花,因为sn和id列其实是存在一定关联关系的,那么在Oracle中可以再增加收集一个多列统计信息:
var v1 varchar2(1000);
exec :v1:=dbms_stats.create_extended_stats('USERNAME','XXX','(SN,ID)');
exec dbms_stats.gather_table_stats('USERNAME','XXX',method_opt=> 'for columns (SN,ID)');
5 效果
最后完成方案2的优化操作后,该语句执行时间降低至10s内完成。
总结
本期内容是一次简单的通过调整引导列和使用复合索引的SQL性能优化。
老规矩,知道写了些啥。