解析Oracle数据扫描OracleSQL优化引导局部扫描方法（2）

发现只通过索引就能获取查询数据的机会

 

         如果能够有效的通过索引来获取数据，那么无疑会对查询性能提供很大的帮助。但是如果索引的选择性较差时，或者虽然索引的选择性很好，但是要查询的数据范围很大时（如：进行计数、求和等数据统计时），那么此时通过索引来查询数据时，就很可能对数据查询性能造成伤害。为什么会这样？这主要是由于通过索引来扫描数据的特点决定的。通过索引来获取数据时，在查询第一条满足条件的索引行时进行的是随机读取，之后因为索引的有序性会执行连续的扫描（类似局部范围扫描），但是如果使用索引回表查询数据时发生的确实随机数据读取。因此如果通过索引查询的数据范围很大，同时有需要回表查询数据时，就会发生大量的随机数据读取，而大量的随机读取会对查询性能造成很大影响，同时还有可能对整个系统的性能造成伤害。  www.2cto.com  

 

        因此应该想方设法避免通过这种方式来使用索引检索数据。那么很自然的就会想到，如果只通过使用索引就能获取需要的全部数据，那么无论处理的数据范围有多大，都会取得非常会的响应速度，因为执行引擎会利用索引进行局部范围扫描。这就是最为关键的要点，我们要能够发现通过索引扫描就能够获得全部查询信息的机会，并加以利用提升查询性能。

 

        是否能够引导执行引擎以这种方式运行SQL查询，索引的结构起到了至关重要的作用。即使查询条件中所涉及的列都创建了各自的索引，在执行时也不是所有的索引都能够被使用。优化器不会因为SQL中所涉及的列被分散在不同的索引中，就轻而易举生成只是用索引的执行计划。通常若想实现只通过索引来查询数据，就必须至少要满足如下3个条件之一：

 

Ø      SQL中所涉及的列全部被包含在一个组合索引中；

 

Ø      能够实现索引合并操作；

 

Ø      SQL中所涉及的全部列都包含在索引中，同时满足索引连接条件；

 

这里涉及了两个陌生的概念，“索引合并”与“索引连接”，下面我们简单解释一下这两个概念。

 

        索引合并是指SQL查询时，通过使用两个以上索引相互配合来查询数据的情况。通过上面查询条件类型的讨论可以知道，通常在存在索引的情况下执行SQL查询时，都会用一个优化器评估出的具有最好选择性的索引作为驱动查询条件，其他的索引只作为过滤查询条件，在过滤查询条件发挥作用时通常不会使用其上的索引，因为通常此时主要是内存中的查找。但是有时存在这样的情况，就是存在多个索引，同时如果结合利用这些索引来查询数据时会比只使用其中一个索引效果更好性能更高，此时如果能够合并使用这些索引那么查询性能无疑会得到较大提升。在Oracle10G之前可以通过Hints AND_EQUAL来实现索引合并，但是从Oracle10G开始该Hints已经被废弃，因为据Oracle声称从10G开始，Oracle的优化器可以正确识别出应用索引合并查询的场合。另外要注意如果SQL语句中的多个索引，离散度（选择性）比较相似，那么此时使用索引合并通常会起到比较好的效果。但是如果SQL中多个索引很明显存在一个离散度鹤立鸡群的索引时，此时还是采用传统的查询方式较好，即通过选择性最好的索引作为驱动查询条件，其他索引作为过滤查询条件。  www.2cto.com  

 

        索引连接是指在某个查询语句中所使用到的某个列表存在一个以上的索引时，此时会按照Hash连接的方式将这些索引连接起来。即通过Hash连接索引来读取数据，而不是通过读取索引在读取表的方式来查询数据。在Oracle中可以通过使用Hints INDEX_JOIN来实现索引连接，如下语句所示：

 

Select /* +INDEX_JOIN(t)*/a2,b3,c1

 

From T t

 

Where a1=’11’ and b1 like ‘AB%’ and c2>100

 

如果在T表之上存在如下索引，IDX1(a1,a2,a3);IDX2(b1,b2);IDX3(c1,c2,c3),IDX4(d1)，那么可见该语句查询条件中的所有字段都包含在索引中，此时就会通过使用Hash连接来执行索引连接获取数据。另外注意索引连接并不是仅在单表查询中才会发挥作用，在多表连接的查询中，只要满足索引连接的条件也会应用索引连接来查询数据。强调一下，索引连接并不是任何时候任何情况下都有效，建议只在非常特殊的情况下（通过你的经验可以识别的情况），通过Hints来指导优化器使用索引连接，其他情况下还是交由优化器自己来决定，而不建议过分迷信索引连接而随意使用。

 

     明晰了上述两个概念后，我们通过如下示例来说明如何识别通过索引访问查询数据的情况，以及如何驱动优化器执行这种数据扫描。

 

示例语句：

 

Select ord_date,sum(qty)

 

From order  www.2cto.com  

 

Where ord_date like ‘201110%’

 

Group by ord_date;

 

       如果在order表上的ord_date字段上有索引，那么查询执行时会通过ord_date来驱动查询执行并进行分组求和，但是很显然此时需要回表来进行数据求和，即要发生全部范围数据扫描并会出现随机读取，如果数据范围较大那么就会在成一定的性能损伤。但是如果我们现在将qty字段添加到ord_date字段的索引中，生成一个新的组合索引，那么此时就满足了只通过索引来检索数据，不但使用了局部范围扫描而且避免了可能的大量随机读取。

 

再看如下示例：

 

Select ord_dept,count(*)

 

From order

 

Where ord_date like ‘201110%’

 

Group by ord_dept;

 

        如果在order表上的ord_date和agent_cd字段上存在组合索引，那么该语句仍然无法实现只通过索引执行局部扫描来获取数据，而是通过ord_date like ‘201110%’驱动条件扫描ord_date和agent_cd字段上的组合索引获取数整体范围，然后回表随机读取数据行，之后执行分组计数统计获得查询结果。再来看一下下面的语句：

 

Select agent_cd,count(*)

 

From order  www.2cto.com  

 

Where ord_date like ‘201110%’

 

Group by agent_cd;

 

此时该语句就可以利用索引进行局部扫描来获取全部数据了，因为该语句中的全部字段都来自于同一个组合索引，满足上述三个条件中的第一个条件。

 

        综上所述通常在执行分组统计查询以及部分普通查询时，在其中都可蕴藏着之通过索引进行局部范围扫描来获取全部数据的机会，要能够及时发现它们，发现它们的判断原则就可以参考上述的3个条件。通常情况下最常用的方式，就是将在Where条件中没有使用到的列，添加到已存在的某个单一索引或某个组合索引中，形成新的组合索引，以便指导优化器制定出只通过访问索引，并利用局部范围数据扫描获取数据的执行计划来提升查询性能。

 

 

 

摘自 javacoffe的专栏