数据库管理-第211期 记一次简单的SQL性能优化（20240623）

数据库管理211期 2024-06-23

数据库管理-第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=:3where id=:4and 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=:3where sn=:5and id=:4;

这样可以通过sn列上的索引筛选出更少的行再与id列进行匹配，好处是无需调整索引，但性能不一定是最好的。

方案2

在方案1的基础上删除sn和id列上的索引，并重新创建包含sn、id列且sn为引导列的复合索引：

createindex 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性能优化。
老规矩，知道写了些啥。