基于需求分析的SQL优化

梁军科

摘要：sql优化是数据库优化中最后的环节，无论sql语句如何改写都必须实现它原有的需求。本文介绍了一种通过sql语句还原需求，并结合业务上给定的需求，以需求的分析为起点，估计优化策略，来实现sql优化的方法。对于企业来说，数据是业务运行的关键，需要后台数据库的支持。数据库的运行效率决定着业务运行的效率，数据库优化在其中起着关键的作用。在优化过程中，可以通过调整内存分布，增加索引，收集统计数据等方法把问题处理掉，但是总有一些情况无法按上述办法解决，最终都要进行sql的优化。

关键词：需求 优化 sql 资源

中图分类号：TP311 文献标识码：A 文章编号：1007-9416（2016）07-0095-01

sql优化的方法有多种多样，但所有sql都是要实现用户的目的要求，本文从需求分析的角度来总结一下。

系统设计之初，从设备选型到软件设计，库表结构设计就是根据用户的需求来构建，到达sql语句这一层次，已经分解成最小单元的需求。按说在sql调优这一步，已经没有获取需求，分析需求的问题了，需求的实现也已经由sql表现完毕，调优所做的只是选择一种更好的实现。但是有时候需求分析的几个方面仍然需要重新认真考虑。当前实现不一定完全对应这个需求，比如检查表有无记录，通过取得表记录数，如果记录数>0，则表示有记录。另一种实现是只要检查到表存在一条记录则表示有记录，两种实现都可以表达需求，但是取得所有记录的代价和只取一条记录的代价对大表来说完全不同。因此要对已有的需求再分析，明确真实需求，排除矛盾需求，进行需求取舍，需求合并，需求改造。根据分析，可能一条sql不是必要的，可以丢弃；定期执行的语句不用执行那么频繁；几条语句合并执行，减少了重复的操作；从全局访问改成局部访问；通过物化视图或缓存的方式预先准备好数据。虽然不是每次都能从系统的层面来考虑需求，可一旦能够实施，将会对优化带来明显的效果。

下一步对具体语句的优化，得到这条语句的需求，进行需求分解。需要关联数据库的操作和使用的资源，对分解后的每一个需求进行分析。实现的数据库操作尽可能减少，可以只访问部分数据的就不要全部访问，只能全部访问的考虑减少表的大小，增加访问的吞吐量，同时可考虑批量访问，一次访问更多数据。操作耗费的成本可以通过几个指标来估计：表访问次数，访问行数，小结果集驱动，返回行数，逻辑读，缓存，局部访问，全局访问，批量执行，排序操作，递归，物理读。执行计划中的这些操作都能估计出成本的大小。使用的资源方面，要减少资源的使用，存在瓶颈时增加资源，资源集中访问时分散资源，资源使用不合理时优化访问路径，调整资源结构。

减少资源使用的操作如下：

避免或减少多余的操作：避免排序，递归等耗资源的操作，可以加大日志大小，增加日志组，减少日志切换频率。个别情况下可关闭归档，关闭日志。避免表空间频繁扩展，降低定时扫描频率，消除多余任务，批量提交，批量执行。重新选择采样点和比率，只针对某些表，索引收集统计信息。

重用数据，避免重复操作：加大共享池，设置cursor_sharing或session_cached参数，加大数据缓冲区。缓存结果集。

合并操作：case when，decode分析把相似的条件合并为一条语句。

局部访问：尽可能采用局部扫描，可以为列增加索引使用局部索引扫描，转化为分区表变为分区扫描，使用NL连接。使每次的访问都只访问部分数据。

全局访问：只能进行全扫描的，尽可能使用全索引扫描，在适当的列增加索引，使索引能包含查询的所有列；统计功能多考虑分析函数，减少表访问次数；减少访问对象的大小，使用分区表，进行表压缩；减少逻辑读，可以增大块大小，减少全表扫描块数，增大db_file_multiblock_read_count增加单次扫描所读取的块数，降低高水位线，降低读取次数。

分散资源的操作如下：

使用临时表空间组，分散临时表空间的使用，缓存sequence，分散热块，调整块尺寸，使用反向键索引，减少资源等待和阻塞

优化资源使用的操作如下：

使用优化的访问路径：返回较多的数据多采用全表扫描，全索引扫描，使用hash连接和排序合并连接，返回较少记录多采用索引局部扫描，嵌套循环连接。

使用优化的存储结构：优化的算法需要有好的存储结构来配合，良好的聚合因子能避免索引回表，簇表，索引组织表能避免回表，降低高水位减少全扫描的块数，使用临时表空间组减少资源竞争，反向键索引减少热块竞争。在适当的列上加索引可能选择局部扫描，使用分区扫描减少块访问。

以上介绍了需求分析及其与数据库相关的操作和资源使用的情况。基于需求分析的sql优化方法，可以有两个相反的过程。在拿到需要优化的sql语句之后，按照从内到外的顺序，从最内部的子查询开始向外分析，判断出语句实现的什么需求。传统上分析出语句结构后就可以进行优化，弱化选取出来的字段，重点关注涉及的表和视图，表连接顺序，连接字段，过滤条件，结合执行计划的指标，表的统计信息及表结构，判断是否是优化的实现，从中发现原有语句的缺陷。基于需要的优化可以到此为止，也可以继续分析，进入相反的过程，忘掉原来语句的实现方法，把根据语句判断出的需求和业务上给定的需求结合起来，得到真正的需求。再从最初的需求入手，对需求从整体架构到细节进行分析，取舍，改造，看需求的达成有几种方法，分析它的利弊和适用场景，哪种最好。确定一种需求的实现，进行需求的分解，分成几步动作，每一步根据分析的结果采取一个优化的策略方法，选择数据库采用的操作，估计耗费的成本，最后对比原来语句的实现方法，确认给定需求下的最优化方法。然后看一看数据库是否支持这些方法，不能支持的有什么次优的方法，折衷的方式达成。

通过以上步骤，完成了基于需求的sql优化。任何语句都是用来实现需求的，所以此方法可以普遍使用。通过需求分析，可以在最细致的功能上考虑适用的操作和使用的资源，降低实现的复杂度，从而得到优化的访问策略，使用语句优化有规可循。