SQL查询优化

摘录于中联研发中心《性能优化设计规范2014》第六章

SQL查询优化

1.1. 自定义函数的使用

1）查询自定义函数返回字段值

避免在输入非唯一匹配的SQL查询中使用自定义的函数，因为这些函数中可能存在多条SQL访问，当匹配结果集很大时，由于对每行数据都要运行一遍函数中的SQL查询和PLSQL代码，所以性能较差。

例：记帐输入病人姓名，模糊查找时，其中涉及三个自定义函数：Zl_Patientsurety，Zl_Patiwarnscheme，zl_PatiDayCharge，这将导致性能较差。

2）在Where条件中使用自定义函数

如果查询条件中包含自定义函数，应该将能够使用到索引的条件，或者能够将数据过滤最少化的条件放到最后。例：

Select 病人id, 门诊号, 姓名, 性别, 年龄 From 病人挂号记录

Where 发生时间 Between Sysdate - Decode(急诊, 1, 1, 2) And Sysdate And Instr(',' || Zlpinyincode(姓名), ',' || 'ZYK') > 0

上面的SQL，将条件\"发生时间 Between Sysdate - Decode(急诊, 1, 1, 2)\"放到最后面，SQL执行产生的逻辑读降低了两个数量级。

1.2. 表与视图的外连接

表和视图进行外连接，很可能导致视图中的查询无法使用索引，应避免使用视图，改为直接连接视图中的表。

例：保险帐户这个视图，下面的SQL语句

Select b.当前状态 From 病人信息 A, 保险帐户 B

Where a.病人id = b.病人id(+) And a.病人id = 1152;

在RBO基于规则的优化器模式下，优化器没有使用数据库的查询重写功能，将导致\"医保病人档案表\"的全表扫描。

1.3. 避免Select *

为了避免隐式字段转换导致的资源消耗，除了对子查询的字段访问外，其余不得使用Select *。

1.4. 选择最优索引

使用能够最大程度筛选数据集的索引，当存在多个可用的索引时，要求通过+0（日期或数值型字段）和||''（字符型字段）来避免使用某些低效的索引。 相对而言，常见的低效索引如下：

因外键而存在的参照引用基础表的索引，相对于主键或唯一键索引，效率较低 门诊费用记录_IX_收费细目id，病案主页_IX_当前病区ID 主键或唯一键索引优于范围索引

\"门诊费用记录_UQ_No\"相对于\"门诊费用记录_IX_登记时间\"更高效； 选择性较低的索引

通过药品id访问库存记录时，\"药品库存_UQ_库房id Unique(库房id,药品id,批次,性质)\"，相对于\"药品库存_IX_药品id On 药品库存(药品id)\"，是低效的索引。

1.5. 避免阻止索引的使用

在Where子句中的条件字段上，以下情况会阻止使用该字段对应的索引： 1） 使用函数(例如：Upper，Trunc，Instr) 例：Trunc(A.发生时间) >Trunc(SysDate)，无法使用\"开始时间\"字段上的索引。 可以改写为：A.发生时间>Trunc(SysDate)； 类似的还有：To_char(开始时间,'yyyy-mm-dd') > '2013-12-31'， 可以改写为：开始时间>to_date('2013-12-31','yyyy-mm-dd') 2） 表达式运算 例如：开始时间-终止时间>3，无法使用\"开始时间\"字段上的索。引. 可以改为：开始时间>开始时间+3 3） 隐式数据类型转换 例如：标识号>3，如果\"标识号\"是字符型，则无法使用对应的索引。 可以改为：标识号>'3' 4） 使用IS NULL或IS NOT NULL判断 如果查询条件中使用了这种判断，则无法使用对应的索引(除非通过提示字

指定使用某个索引)，如果该字段为空或不为空的数据占比很少，在数据结构设计时，应该将这类数据填写为非空值，这样才能按索引进行查询。 5） 使用双向模糊匹配或右匹配 当使用\"名称 Like '%ZYK%'\"或\"名称 Like '%ZYK'\"这样的查询条件时，是

无法使用\"名称\"索引访问数据的，从业务层面考虑，是否确实有必要采用双向匹配，如果可以改为左匹配\"名称 Like 'ZYK%'\"，则可以使用对应的索引。

1.6. 减少不必要的排序

排序操作是一种消耗CPU和内存较多的操作，特别是在高并发环境下。 1） 如果不是因为程序逻辑所需，不要随意进行排序； 2） 只对必要的字段进行排序； 3） 如果有多层子查询，除非逻辑所需，一般只在最外层查询排序； 4） 高频SQL查询，如有可能，采用客户端程序或控件排序替代SQL排序。 5） 由于Oracle版本的差异，从10G开始，缺省情况不支持Group by来实现排序，查

询结果需要排序时，应指定Order by子句。

1.7. 慎用标量子查询

在Select子句的字段上使用的子查询称为标量子查询，这种查询将针对主查询返回的每一行结果进行嵌套查询，效率较低，而且会阻止数据库自动优化查询。 尽可能避免使用标量子查询，能够使用表间连接的，应该优先使用表间连接。 一般情况，要求满足以下条件时才使用标量子查询： 1） 存在Max,Sum等聚合函数查询，并且主查询比较复杂，如果用表间连接，会导

致再写一层汇总查询，导致SQL更加复杂； 2） 对于主查询的每一行数据，子查询不一定有数据，如果用表间连接，需要使用

外连接，而且可能由于Or等条件导致无法使用外连接。 例：

Select A.开嘱科室ID,A.审核状态,A.申请序号, (Select To_Char(Max(P.打印时间), 'YYYY-MM-DD HH24:MI') From 病人医嘱打印 P Where a.Id = p.医嘱id And p.打印标记 = 0) As 打印时间 From 病人医嘱记录 A,部门表 E,药品特性 C,……

1.8. 检查SQL执行计划

凡是涉及业务大表访问或者重点功能程序中的SQL语句，要求将主要业务逻辑条件组合成的SQL放到PLSQL中查看执行计划，确保在基于规则的优化器模式下(RBO)的执行计划中利用到高效的索引，并且采用了高效的执行计划（不要求检查CBO下的执行计划，因为这会受到数据量、数据库环境、硬件等各种因素的影响）。 不要认为执行速度不慢就忽略此步骤，因为开发所使用数据库环境的数据量与用户环境有差别，可能没有暴露问题，所以，要求检查执行计划。

1.9. 避免编写复杂SQL语句

复杂是指SQL文本庞大、逻辑规则复杂等情况，这类SQL在CBO模式产生执行计划时需要更复杂的成本评估过程，消耗更多的CPU解析资源，并且错误的选择低效执行计划的风险更大。

所以，应该尽量避免书写复杂的SQL，可以采用功能拆分，功能实现方式调整，逻辑简化，本地数据处理等思路来优化调整。

功能拆分方法，例如：

病人费用查询，10.34以前的程序，输入姓名后，显示并加载符合条件的病人列表的SQL文本比较庞大，针对这种情况，可以先用一个简单的SQL查询并返回符合\"姓名\"条件的列表，操作人员选择确定病人身份后，再根据身份唯一条件去查询那个病人列表的SQL。

基于业务理解和数据结构分析后，对SQL逻辑进行简化，例如： 原SQL：

Select LPad(b.床号, 10, ' ') As 床号, LPad(b.房间号, 10, ' ') 房间号, b.床位编制, a.姓名, Zlpinyincode(a.姓名, 0, 0, ', ', 1) As 简码, a.住院号, a.病人id, a.主页id

From 床位状况记录 B, (Select Nvl(c.姓名, b.姓名) || Decode(c.婴儿病区id, Null, '', '之子') 姓名, b.住院号, b.病人id, b.住院次数 As 主页id

From 病人信息 B, 病案主页 C, 在院病人 R

Where b.病人id = r.病人id And c.病人id = b.病人id And b.住院次数 = c.主页id And b.当前病区id = r.病区id And (r.病区id = :V001 Or c.婴儿病区id = :V002)) A

Where b.病人id = a.病人id(+) And b.病区id = :V003 And Not (b.状态 = '修缮' And b.病人id Is Null) Order By LPad(b.床号, 10, ' ')

由于数据结构的冗余设计，可以减少一些不必要的表访问，优化后的SQL如下：

Select LPad(b.床号, 10, ' ') As 床号, LPad(b.房间号, 10, ' ') 房间号, b.床位编制, c.姓名, Zlpinyincode(c.姓名, 0, 0, ', ', 1) As 简码, c.住院号, c.病人id, c.主页id From 床位状况记录 B, 病案主页 C

Where b.病区id = :V001 And (c. 当前病区id(+) = :V002 Or c.婴儿病区id(+) = :V003)

And b.病人id = c.病人id(+) And c.出院日期(+) is Null And Not (b.状态 = '修缮' And b.病人id Is Null) Order By LPad(b.床号, 10, ' ')

1.10. 编写高效SQL的常用技巧

SQL语句的优化技巧很多，下面列出一些常用的技巧。 1） 如果确认没重复数据，用Union All而不是用Union，可以减少排序和重复记录筛

选。

2） 将Nvl函数放到Sum(a.xx)的外面，用Nvl(Sum(a.xx),0)替代Sum(Nvl(a.xx,0))； 3） 能够放在Where子句的条件，不要放到Having子句，应先过滤数据再聚合； 4） Exists（Not Exists）与In（Not In）的选择

在8i时代，一般要求：子查询结果集小时用IN，外表小而子查询表大时用EXISTS。在9i之后，已经优化了IN和EXISTS，优化器有个查询转换器，SQL虽然写法不同，但是优化器会根据既定规则进行查询重写，重写为优化器认为效率最高的SQL，所以，可能SQL写法不同，但是最终的执行计划却是完全一样的。

虽然优化器很强大，但是查询转换是有一定的的，有时，EXISTS性能低下，无法进行相关查询转换，所以，建议遵守以下原则：

IN一般是用于非相关子查询，而EXISTS一般用于相关子查询。 例：

Select ID, 分类, 编码, 名称, 说明 From 临床路径目录 A, 临床路径病种 B

Where a.Id = b.路径id And (b.疾病id = 11 Or b.诊断id = 22) And

(a.通用 = 1 Or a.通用 = 2 And Exists (Select 1 From 临床路径科室 C Where a.Id = c.路径id And c.科室id = 33))；

5） 先Group by还是先表间连接

如果没有可过滤数据行的条件，先在子查询中进行Group by后再进行表间连接，先聚合数据，减少记录行数后，再连接，例：

Select 名称,人数

From (Select 部门id, Count(*) 人数From 部门人员 B Group By 部门id) b, 部门表 a Where b.部门id = a.Id Order By 人数 desc

如果有可过滤数据行的条件，则不要采用子查询分组的方式，应采用表间连接后再分组的方式，避免有些情况由于查询重写失效，无法将谓词推进到子查询，例：

Select a.名称, Count(b.人员id) As 人数 From 部门人员 B, 部门表 A

Where b.部门id = a.Id And a.编码 Like '44%' Group By a.名称 Order By 人数 Desc

6） 添加过滤条件减少表间连接的记录数

根据业务逻辑，在SQL中添加必要的过滤条件，使之在表间连接之前，先过滤数据，减少驱动表数据行数，从而减少连接行数。

优化前：B表与A表先连接，可能有1W行记录连接，产生结果后再按条件过滤。

From (Select ID From 诊疗项目目录 Where 类别 = 'Z' And 操作类型 = 7) A, 病人医嘱记录 B

Where a.Id = b.诊疗项目id And b.开嘱时间 Between Sysdate - 3 And Sysdate

优化后：添加条件\"b.诊疗类别='Z'\"过滤后，B表与A表可能只剩100行记录连接

From 诊疗项目目录 A, 病人医嘱记录 B

Where b.诊疗类别 = 'Z' And a.操作类型 = 7 And a.Id = b.诊疗项目id

And b.开嘱时间 Between Sysdate - 3 And Sysdate

7） 将Or展开或避免使用Or

例，方式一：

Select * From 收费价目 Where (Sysdate Between 执行日期 And 终止日期) Or (Sysdate >= 执行日期 And 终止日期 Is Null)

方式二：

Select * From 收费价目 Where Sysdate Between 执行日期 And 终止日期 Union

Select * From 收费价目 Where Sysdate >= 执行日期 And 终止日期 Is Null;

方式三：

Select * From 收费价目 Where Sysdate Between 执行日期 And Nvl(终止日期， To_Date('3000-01-01'， 'yyyy-mm-dd'))

执行结果对比：

方式一：3109 consistent gets

方式二：3 db block gets， 1328 consistent gets， 125 physical reads 方式三：2449 consistent gets

虽然方式二逻辑读减少，但物理读较多，最终，方式三最快。 8） 使用With子查询来减少重复的SQL

如果SQL查询语句中需要多次访问某张表，使用With语法可大大提高性能。例：

Select * from (With

Q1 As (Select 3 + 5 S From Dual), Q2 As (Select 3 * 5 M From Dual),

Q3 As (Select M * S as MS From Q1, Q2)

Select MS From Q3 union all Select M From Q2 union all Select S From Q1);

9） 使用RowID快速访问数据

如果可能，应使用RowID来访问数据，这是比索引更快的访问方式，例：

Cursor c_No Is

Select Rowid From 住院费用记录 A Where NO = No_In And 记录性质 = 记录性质_In;

当通过游标返回的结果重新查询\"住院费用记录\"表时，没有使用主键ID字段的索引，而是直接使用了Rowid。 需要注意的一个问题，如果存在并发操作，读取和使用RowID之间有较长的时间间隔，可能导致读取位置的数据在使用时已经被修改。

10） 使用Connect By来产生递增序列记录集

如果我们需要在SQL中构造多行数据，或者产生行号，没有必要使用Union All这种冗长的子查询联接方式来产生，可以使用如下方式： Select Rownum From Dual Connect By Rownum < 11；

11） 使用f_List2str函数来汇聚单列多行字符 该函数是在ZLTools中定义的，需要配合Collect函数来使用，范例：

Select 科室, f_List2str(Cast(Collect(人员) As t_Strlist)) 人员

From (Select a.名称 As 科室, c.姓名 As 人员 From 部门表 A, 部门人员 B, 人员表 C

Where a.Id = b.部门id And b.人员id = c.Id Order By 科室, 人员) Group By 科室

应避免使用wm_concat函数，因为这是Oracle未公开的函数，不建议使用。从Oracle 10.2.0.5开始，该函数返回的是LOB类型，需要进行To_Char转换，否则会报错。

12） 使用数据窗口分析函数来简化SQL

使用ROW_NUMBER()、RANK()、Min()、Max()等函数，并结合数据窗口函数Over(Partition By)，可以实现数据组内记录的访问和操作，这种方式可以避免编写复杂的Group by和子查询表间连接SQL。 例：查询每个部门年龄最小的人

Select 部门, 姓名, 出生日期

From (Select b.名称 As 部门, a.姓名, a.出生日期, Min(出生日期) Over(Partition By c.部门id) As 部门内最小 From 人员表 A, 部门表 B, 部门人员 C

Where a.Id = c.人员id And c.部门id = b.Id And c.缺省 = 1) Where 出生日期 = 部门内最小

例：查询条件中存在聚合函数的子查询时，改写为Over方式，避免了子查询。

……And c.医嘱id = a.Id And c.操作类型 In (11, 12) And c.操作人员 = [2] And c.操作时间 between [3] And [4] And c.操作时间 =

(Select Max(操作时间) From 病人医嘱状态 Where 医嘱id = a.id And 操作类型 In (11, 12))

改写为：

Select 医嘱id,操作时间

From (Select 医嘱id,操作时间, Row_Number() Over(Partition By 医嘱id Order By 操作时间 Desc) as Top From 病人医嘱状态 C

Where c.操作时间Between To_Date('2011-03-01', 'YYYY-MM-DD') And To_Date('2011-03-02', 'YYYY-MM-DD') and 操作类型 in(2,3)) Where Top = 1；

13） 分级汇总时使用ROLLUP来简化SQL

在Group By中，可以使用分析函数ROLLUP、CUBE来实现分级汇总。

如果是ROLLUP(A, B, C)，首先会对(A、B、C)进行Group By，然后对(A、B)，(A)进行Group By，最后对全表进行Group By。 如果是CUBE(A, B, C)，首先会对(A、B、C)进行Group By，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行Group By。 例：按分院、部门两级汇总人数，并求小计与合计。

Select Decode(Grouping_Id(a.站点), 1, '合计', Null) 合计, Decode(Grouping_Id(a.站点, a.名称), 1, '小计', Null) 小计, a.站点 As 分院, a.名称, Count(*) 人数 From 部门表 A, 部门人员 B, 人员表 C

Where a.Id = b.部门id And b.人员id = c.Id And b.缺省 = 1

Group By Rollup(a.站点, a.名称)