如何在不改SQL的情况下优化数据库

主题简介

在数据库运维中我们会遇到各种各样的问题,这些问题的根源可能很明显,也可能被某种表象掩盖而使我们认不清。所以运维面临的两大问题就是,第一我们没有看清本质,第二应用不允许修改。那么我们如何解决这两个问题,是每一个运维者都应该思考的。今晚的分享将会从三个方面来进行。如何准确定位问题,如何不修改应用进行优化,以及如何通过SQL审核防患未然

大家好。我是云和恩墨公司的专家罗海雄,主要专长于性能优化这个领域,包括数据库的优化,SQL优化等。今天给大家分享的主题是“如何在无法修改应用时进行数据库优化”。

我先从一个真实的案例说起。我们的一个客户,是一个汽车经销商,他们的财务系统出现了严重的性能问题。

这是他们的CPU压力的示意图。

如何在不改SQL的情况下优化数据库

红色的是CPU使用量,绿色的是CPU空闲,黑色的是IO等待。我们可以看到,在图的中间部分,也就是月底的时候,CPU使用量已经达到80-90%, 而CPU Idle 已经接近0了。

中间似乎还有一段CPU 空闲100%, 其实是由于数据库压力太大,主机自动重启了。 这种情况已经严重影响他们公司财务系统的月结工作。他们赶紧找到我们公司,我一看AWR, 发现大量SQL执行时间很长。

如何在不改SQL的情况下优化数据库

从表里可以看到,第一个SQL平均一次执行需要2700秒,后面还有几个SQL单次执行在2000秒以上,显然有很大问题。

发现明显有部分SQL写法上存在问题,正是这些SQL,导致数据库压力过大。

其中一条SQL是这个样子的:

如何在不改SQL的情况下优化数据库

几个表都是几千万条记录的大表,re.FSrcEntryId是个选择都很高的栏位,sie.FID是个主键, si.FId 也是主键。T_IM_SaleIssueEntry是外连接的驱动表,但是上面没有直接的条件。T_IM_SaleIssueBill 和 T_BOT_RELATIONENTRY都是被驱动表,上面有两个条件:

si.FTransactionTypeIDIN (:3, :4)re.FSrcEntryId IN (:5, :6)

这种情况下,由于被驱动表已经有了确定值,逻辑上来说,外连接和内连接是等价的,但是Oracle 10g 的优化器没有为这种情况做优化,导致在优化前,走了大表的全表扫描

如何在不改SQL的情况下优化数据库

这一类SQL不少。我们最早的建议就是修改部分写法存在问题的SQL, 从源头入手,尝试解决。比如说,把LEFT OUTER JOIN 改成普通JOIN, 逻辑上是一样的,就能走刚才的好的执行计划。

如何在不改SQL的情况下优化数据库

但是,应用开发厂商表示,由于这个版本比较老,他们已经没有专门的人员对代码进行维护,无法修改SQL。

这就是作为一个系统维护DBA,经常会碰到的问题。系统刚上线,一切很美好... 一两年后,由于数据量的积累,用户数增多,功能点使用增多等原因,会使负荷逐渐增加,从而出现性能问题。根据我们的经验,在这些性能问题里面,SQL的问题可能占了80%.但是,由于种种原因,可能就是没法修改SQL.

比如说:

-- 使用封装好的商业套件

-- 外包开发,开发商已经离场

-- 自行开发,但开放部门不愿意配合进行代码修改

这就到了今天的主题:“如何在无法修改应用时进行数据库优化

总的来说,可以从硬件和软件两个方面去解决:硬件方面,可以通过增加或者CPU,增加内存,做一定的改善。也可以通过升级成增加RAC, 也可以增加CPU的处理能力。存储方面,升级更好地存储,针对一些I/O要求比较高的系统,也一种优化的手段。

如何在不改SQL的情况下优化数据库

这是我们的一个客户,通过把存储介质升级到PCIE Flash卡,极大的改善了I/O性能问题,是整个系统的性能得到了极大的提升。

我们公司的zData产品,通过高速的PCIE Flash卡,结合超大带宽,超小延迟的高速IB网络, 也可以非常有效地提高I/O的性能,总体性能是传统存储的10倍以上。这样,在不修改任何SQL的情况下,就轻松解决了I/O的瓶颈,提高的系统的性能。当然,对于不差钱的单位/企业,使用Oracle的Exadata也是一种方法;很简便的、不修改应用就能明显提高系统性能的方法。动硬件往往涉及到预算。不增加开销的情况下,也可以通过数据库的层面做一些优化。

回到我一开始介绍的那个案例。

经过研究,我们发现,LEFT OUTER JOIN不能等价转换成普通JOIN是Oracle 10g的行为模式。在Oracle 11g中,优化器做了升级,能够识别并内部进行这种转换。而恰巧,用户用的是Oracle 11g的数据库,只是由于应用开发方的要求,把优化器模式设为了10.2.0.1.

最终,通过和应用开发方,使用方的多次沟通和测试,最终把优化器模式设为了11.2.0, 从而解决了最大的问题。

当然,LEFT OUTER JOIN只是其中一个问题,后来,在这个客户的数据库上,我们还有针对性的建立了100多个索引。最终,在没有修改任何SQL的情况下,彻底解决了用户的系统性能问题。

通常来说,数据库层面的优化包括

参数调整:内存参数,优化器参数等

表结构调整:索引, 并行度,分区

SQL执行计划调整:SQL Profile,SPM,SQL Patch

其它: Cache 表、统计信息、物化视图+查询重写、数据归档等等

合适的优化器参数,会使你系统索引的问题看起来很简单,但往往是最有效的方法之一。大家讨论的比较多,我就不深入了。分区以及数据归档也是一个常用的手段。实际上,数据都是有生命周期的。很多用户的数据库里面,存了很多已经不需要的数据,通过清理、归档这些数据,往往也能获得比较高的优化效果。

还有一些情况,SQL写的并没有问题,但由于种种原因,数据库经常走错执行计划;这时候,通过改写SQL, 增加Hint是一种常见解决方式。在无法修改SQL的情况下,也可以通过一些手段对SQL执行计划进行固定。

这些手段主要包括有:

SQL Profile(Oracle 10g以后)

SQL Plan Baseline Management(Oracle 11g以后)

SQL Patch

Outline

SQL profile在Oracle 10g引入:

通过为特定的SQL文本指定优化器的一些信息,从而引导优化器生成更为合理的SQL执行计划。达到不修改SQL文本就可以改变并指定执行计划的目的。

SQL-Profile 主要通过dbms_sqltune包进行控制。时间关系,今天就不为大家演示了。

SQL Plan Management在Oracle 11g引入:

通过为特定的SQL指定已知SQL执行计划,强制优化器选择已经指定的SQL执行计划,从而达到不修改SQL文本即可修改执行计划的目的。

可以指定多个可用执行计划供优化器选择。

可以和SQL Tuning Advisor一起用

可以自动收集运行库中SQL 作为已知执行计划。

也可以手工设置。

SQL Plan Management主要通过DBMS_SPM包进行控制,SQL Patch是一种强行给SQL加Hint的方法,主要通过sys.dbms_sqldiag_internal.i_create_patch进行。

如何在不改SQL的情况下优化数据库

不同的数据库优化方式对整个数据库影响面各有不同,在使用的时候,需要谨慎的程度也不一样。

经典问题分享

问题一

关于SQL Profile使用方法有什么好的推荐的书籍或者文档介绍之类的

关于 SQL profile,推荐老熊的两篇文章

http://www.laoxiong.net/sql-profiles-part.html

http://www.laoxiong.net/sql-profiles-partii.html

问题二

关于驱动表和被驱动表如何确定?

对于Nested loop来说,被驱动表需要有高选择度索引,驱动表的结果应该尽量小。 Hash Join没有被驱动表需要索引的问题,只剩下驱动表结果集小的需求。多数情况,能够迅速帮你把数据量减下来的表,适合当驱动表。

问题三

同一条sql的执行计划经常会变是什么问题?

统计信息以及绑定变量是变化的主因

Oracle的新特性 基数反馈和adaptive cursor 也会造成执行计划不稳定

问题四

咨询一下zdata分布式存储采用的是什么raid方式?

zdata分布式存储采用的是分散的mirror机制。每个块至少在两个主机的Flash上存放,同时有机制保证某个主机出现问题后,把相应的块重新分布到其他主机上。

问题五

很多文章都在说索引高度太大会增加io开销,在数据量日渐增多的情况下,如何降低索引高度呢?

分区是一个办法

不是特别建议通过重建的方法强行降blevel, 在反弹的时候容易造成索引分裂,影响系统性能

问题六

今天我们优化sql上午时候,表关联,使用了jion,每个表都走了索引,但是从执行计划上看消耗掉的cpu还是很多,没有明显的下降。想问一下。优化sql需要考虑到具体方面

虽然走索引,当时如果驱动表结果集较大,多次的索引扫描,同样会导致性能不好。

总的来说,访问越少的Block, 性能就越好。合适的条件尽可能放在执行计划链的前段,迅速把结果集圈定在最终需要的结果集里。

如果缺乏合适的过滤条件,那么,考虑用Hash Join代替nested loop. 当然,还得看实际的情况。

问题七

动态生成的sql如何进行优化

“动态生成的sql如何优化”这个问题有点宽泛。我猜测提问者想问的可能根据用户输入条件动态拼接而成的SQL如何优化。这个问题得具体分析。通常来说,尽量把条件限制在第一个表,适当的多建索引,以及针对用户实际行为进行针对性优化。

比如说,在我服务过的一个公司,系统里有个界面,操作人员可以根据需要选择不同的条件,这些条件组合来自于多个表。

后来,我们直接分析系统访问日志,发现80%的查询都是特定两个表的条件,分别位于from 列表的第一个和第三个表。而第二个表是个关系表。

于是,我采用了比较少见的处理方式,把第一个表和第三个表的关键字段组合起来,创建了一个新的表,并在这个表的相应字段创建索引,而这个表的数据通过trigger的方式进行同步。最终,相关的SQL性能提升数百倍,从30秒钟下降到100ms。系统总体CPU降低 50%以上。

问题八

什么时候数据库会走错误的执行计划啊?

Oracle的新特性 基数反馈和adaptive cursor 也会造成执行计划不稳定

问题九

trigger能保证事务吗?

可以

参考:http://mp.weixin.qq.com/s?__biz=MjM5MzExMTU2OQ==&mid=2650603949&idx=1&sn=831ace083a2f0ef70824f7494816cba3&chksm=be95aad389e223c51d5b0b7d7292589d85d4df5b1f423e014c10e989182fba0e209865a2cf3b&mpshare=1&scene=1&srcid=1017lLda72MWD8uB3W7Ubloz#wechat_redirect

讲师PPT分享:

链接: https://pan.baidu.com/s/1eRKuCAQ 密码: kf3t

上一篇:mysql大表在不停机的情况下增加字段该怎么处理


下一篇:JVM加载类的过程,双亲委派机制中的方法