左关联.doc

通过调节索引 优化Oracle关联查询性能 2010-06-03 09:24 黄永兵 51CTO 字号：T | T 这是一个最常见的查询性能优化方法，有人用“百试不爽”来称赞这个查询优化方法，本文就一个真实的系统为例介绍一下如何通过索引优化查询性能，虽然案例使用的是Oracle数据库，但本法一样适用于所有关系数据库，当你遇到查询缓慢时，不妨首先用本办法优化一下，也许有意想不到的效果。 AD： 51CTO学院：IT精品课程在线看！ 【51CTO独家特稿】低碳指数：在这里为了方便计算和直观，我们以Intel至强X7500处理器的TDP为标准计算能耗（TDP 130W/h 2.167W/m 0.036W/s）。另外根据中国林业局的数据，一棵树一天吸收二氧化碳量为5.023kg，每一度电产生0.785公斤二氧化碳。 如果按照本文方法优化后数据库执行时间由191秒缩减到189秒，也就是单位时间少1%的能量消耗。那么在一天里将减少0.03kw电能消耗，约合0.023kg二氧化碳排放，按我们的计算是一天减少0.05棵树二氧化碳吸收量。 本文引用一套实验室信息管理系统（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使用的这三张表除了创建主键时自动创建的索引外，均未创建其它索引，下图是无索引时的执行计划。 图 1 无索引时的执行计划 从图1可以看出，表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字段加上索引，下面是对应的执行计划。 图 2 给comm_patient的i_age加上索引后的执行计划 从图2可以看出，表comm_patient的全表扫描消失了，取而代之的是索引唯一性扫描，成本从18一下子降低到1了，注意这里并未使用我们给i_age增加的索引，但却靠它触发了使用表主键对应的索引。但表lis_report仍然是全表扫描，由于where子句中引用了该表的d_checkdate字段，因此我们给该字段加上索引看看效果。 图 3 给lis_report的d_checkdate字段加上索引后的执行计划 从上图可以看出，表lis_report的全表扫描消失了，取而代之的是索引范围降序扫描（INDEX RANGE SCAN DESCENDING），成本也从191下降到189。注意这里的索引范围降序扫描的来历，因为我的where子句中引用d_checkdate是介于2008-11-01至2008-11-30的一个范围，这时引用的这种字段上建