100-为什么数据库运行越来越慢? 了解一下<SQL诊所>服务

老虎刘

发布于 2023-09-01 13:20:48

2120

发布于 2023-09-01 13:20:48

很多数据库会随着时间的增长越来越慢, 今天通过一个小案例说明一下. 文章结尾可能有你需要的东西.

最近在给客户的某个oracle数据库做优化的时候, 发现一个TOP SQL, 执行时间长,消耗CPU和存储资源多. 原SQL经过脱敏后, 长成这个样子:

select id,owner from T1 a 
where exists 
(select 1 from
    (select 1 as x_id,sysdate-2 as x_date from dual      
     union all
     ......
     union all
     select 100      ,sysdate-1           from dual     
    ) x 
where x.x_id=a.id 
and a.created>=x.x_date  
);

其中union all部分大概由100多个 select .. from dual组成, T1表记录数有几千万,最终满足条件返回的记录数只有几十条. T1表的id字段上有索引.

经过分析, 这个SQL的关键点不在x部分的形式, 为了方便测试, 我们把x部分用一个100条记录的小表代替,简化如下:

select id,owner from T1 a
where exists 
(select 1 
 from t100 x 
 where x.id=a.id  
  and a.created>=x.created
 );

下面通过T1表从小到大, 模拟生产数据库SQL执行效率逐渐下降的情况.

生产系统业务上线初期:

假设T1表记录数有10万条(T1替换为表名T10w), 执行情况如下:

执行时间很快, 只需要0.02秒, 主要消耗在T1(T10w)表的全表扫描上. 不会引起大家的注意.

业务上线一段时间后:

随着系统运行时间的增长, T1表增长到了 100w(表名T1m)条记录,执行效率也接近10倍的下降,靠着强大的磁盘性能, 执行时间也只需要0.19秒, 仍在可以忍受范围:

业务上线较长时间:

当T1表记录数达到2000w(表名tbig)时, 执行时间来到了18.86秒,加上执行频率较高,系统已经不堪重负, 而且这个数据库的最终业务用户的使用体验也会大幅下降:

如何解决这个问题?

对于DBA:

一方面可能会把这个SQL告知开发, 让其优化SQL, 另一方面可能要给系统分配更多的CPU,内存和存储资源,避免业务用户的投诉和系统宕机的可能. 如果客户有信创需求, 说不定还会把这个库迁移到分布式数据库: 把大表数据打散到多台服务器处理, 也算是一种解决办法.

对于开发人员:

如果开发人员对数据库了解不够深入, 就不知道如何优化这个SQL , 只能把控制T1表的大小作为优化方案, 那就要牺牲用户的历史数据保留时间. 但是系统资源的使用和SQL执行时间还是不如人意.

老虎刘的优化方法:

从SQL优化的角度来看, 这个问题可以通过改写SQL来解决.

对于数据库来说,SQL的写法非常重要,实现相同业务逻辑, 可以有不同的SQL写法, 执行效率也是差别很大, 数据量越大就越明显.

数据库的优化器会把一些常见的SQL写法在内部做一些查询转换,这就考验数据库的核心算法了. 上面这个SQL的写法 , 强大如oracle数据库也没能拿出一个让人满意的执行计划.

我的优化方法是: 把原SQL的exists子查询改成内连接.

还是用2000万数据量的大表(tbig)测试,改写后SQL的执行时间只需要0.01秒, 效率一下子提升接近2万倍(下面显示的0.01秒是sqlplus显示的最小值, 实际可能还不到10毫秒). 这样一个SQL, 即使每天执行几百万次, 对数据库来说也是完全没有任何压力. 而且对业务用户来说, 会体验飞一般的感觉.

(注意: 如果T100表的id字段有重复值, 那么上面的改写就不等价了, 需要再增加一些简单处理, 才能在保证逻辑等价的情况下实现相同的优化效果, 请有兴趣的读者自己再思考一下, 可以加微信交流. 微信号: ora_service )

数据库性能优化方法主要有下面几种:

对于主机/存储/操作系统/网络的优化, 持证上岗的DBA和系统管理员们的经验都很丰富, 不在讨论范围. 下面主要是SQL相关,因为数据库的主要任务就是执行SQL.

调参数 : 对于优化器相关参数, 一般不建议全局调整, 我看到有些大师建议把_optimizer_null_aware_antijoin这个隐含参数关闭, 说是有bug. 我有一些客户就被这个参数折磨的苦不堪言. 其实随着版本不断升级, 很多bug都会逐步修复, 可能有少量极特殊sql受bug影响, 单独处理就好了.
保证统计信息的准确: 这个东西很重要, 大部分DBA都被这个问题困扰,有时还需要开发配合.
索引和SQL写法: 这两个东西很多时候是需要配合的,也是SQL优化的核心.
设计: 到了生产, 设计上的问题就很难搞了, 比如字段类型的选择(90%以上的开发人员会用varchar2来保存日期类型, 这是非常不规范的行为)/范式化与反范式化(违反第一范式,将数据通过分隔符保存在一个字段,会带来严重的性能隐患)/业务逻辑的实现方法(在大表上频繁模糊查询,并发修改相同记录导致行锁等)/ 绑定变量的使用 ....
hint调整执行计划: 任何数据库的优化器都不是完美的, oracle提供的hint是对优化器不足的一个弥补, 这个技能也是必备的.特别是生产系统可以在不改SQL代码的情况下解决性能问题. 用hint优化复杂sql,对技术要求比较高.
大表分区
......

上面很多内容大部分可以通过开发规范来了解和约束. 几年前我在某移动一个开发规范上补充了一些内容, 这个版本我自己不是太满意, 但是也在江湖上流传甚广; 去年我又在某银行的开发中心, 从0开始, 花了10几个人天搞了一个全新的开发规范, 分OLTP和OLAP两部分, 内容更广, 还包括很多新版本的新特性. 说是开发规范, 其实就是把开发需要了解的知识点列出来, 很多知识点对其他数据库也都是适用的.

下面是本文的重点, 也是专业人士发挥特长实现自身价值的时刻:

如果DBA或开发人员有SQL优化或相关需求, 可以到老虎刘的<SQL诊所>咨询, 本人会用25年以上的专业经验为大家答疑解惑. 有偿服务, 根据问题复杂程度收费几十到几千不等, 问题不解决不收费. 服务范围暂定如下: