Oracle数据库索引优化技术关联查询性能调优

Oracle数据库索引优化技术关联查询性能调优

2010年06月21日 星期一 19:00

　数据库性能优化是无止境的，无论哪种优化技术只是一种手段，但最重要的不是技术，而是思想，掌握了索引优化技术仅仅刚入门，只有融会贯通，举一反三才能成为高手。

　　本文引用一套实验室信息管理系统（LIS）使用的数据库，假设我们要查询2008年11月做检验的患者记录，条件是大于80岁，姓周的患者，最终结果按检查日期进行倒序排列。要使用的表有三个：

　　◆lis_report：报告主表，我们要用到的字段包括i_checkno（检查号），d_checkdate（检查日期），i_patientid（患者ID）；

　　◆comm_patient：患者信息表，我们要用到的字段包括i_patientid（患者ID），s_name（患者姓名），s_code（患者住院号），i_age（患者年龄），i_dept（患者所在病区）；

　　◆lis_code_dept：病区信息表，我们要用到的字段包括i_id（病区ID，主键，与comm_patient中的i_dept关联），s_name（病区名）。

　　最终我们构造的SQL如下：

select a.i_checkno, a.d_checkdate, b.s_name, b.s_code, b.i_age, c.s_name from lis_report a inner join comm_patient b on a.i_patientid = b.i_patientid inner join lis_code_dept c on b.i_dept = c.i_id where a.d_checkdate > ' 2008-11-01' and a.d_checkdate < '2008-11-30' and b.i_age>=80 and b.s_name like '周%' order by a.d_checkdate desc

　　我们的SQL使用的这三张表除了创建主键时自动创建的索引外，均未创建其它索引，下图是无索引时的执行计划。

　　表comm_patient和lis_report都使用了全表扫描，comm_patient全表扫描的成本是18，lis_report全表扫描的成本是191，只有表lis_code_dept因关联时使用的是其主键，因此这里使用了主键索引，从而避免了全表扫描，它的成本是0。我们知道提高查询性能的目标之一就是消灭掉全表扫描，因此我们应该给表comm_patient和lis_report加上适当的索引，在SQL代码的where子句中，对comm_patient表，我们引用了i_age和s_name字段，对lis_report表，我们引用了d_checkdate字段，通常给这些条件中引用的字段加上索引会提高查询速度，我们先给comm_patient的i_gae字段加上索引，下面是对应的执行计划。

　　表comm_patient的全表扫描消失了，取而代之的是索引唯一性扫描，成本从18一下子降低到1了，注意这里并未使用我们给i_age增加的索引，但却靠它触发了使用表主键对应的索引。但表lis_report仍然是全表扫描，由于where子句中引用了该表的d_checkdate字段，因此我们给该字段加上索引看看效果。

　　表lis_report的全表扫描消失了，取而代之的是索引范围降序扫描（INDEX RANGE SCAN DESCENDING），成本也从191下降到189。注意这里的索引范围降序扫描的来历，因为我的where子句中引用d_checkdate是介于2008-11-01至2008-11-30的一个范围，这时引用的这种字段上建立的索引通常都是执行范围扫描，因为这种条件返回的值往往不止一行。使用降序扫描的原因是order by子句使用了降序排序，如果我们将SQL代码中的“order by a.d_checkdate desc”改为“order by a.d_checkdate”，则变为索引范围扫描（INDEX RANGE SCAN）。

　　至此我们全部消除了全表扫描，我们看到加上索引后，查询执行的成本开销也有所降低，因为数据库表中的记录数不大，因此效果不太明显，如果有上百万条记录则会更直观。

　　虽然索引能提高查询性能，但索引也不能滥用，一是因为索引会降低写入性能，二是索引过多给索引管理带来麻烦，有些索引根本就没有使用，这样的索引只会带来负面影响，基于这些弊端的考虑，在设计数据库结构时应综合考虑表的使用频率（使用次数越多越应重点考虑是否建立索引），表中字段的使用频率（字段使用次数越多越应建立索引），字段类型（数值型字段越应建立索引），值的唯一性（最应建立索引的字段），值的重复性（值重复度越高，建立索引的必要性越低），值是否可为空（允许为空的字段一般不建立索引），表中记录数（记录数很少时一般不宜建立索引），表是读操作多一些还是写操作多一些（读操作越多的表越应建立索引，写操作越多的表越应避免建立索引）等，创建索引的一般原则是：在大表的常用且值重复几率小的字段上创建索引。