Oracle数据库性能测试过程中索引使用问题记录

lsekfe

　近日在执行Oracle数据库性能测试时，通过在使用JMeter发压前后分别生成数据库快照，进而生成AWR报告分析，发现某一SQL语句的执行时间较长，遂对此语句进行分析。　　通过AWR报告中Main Report-->SQL Statistics-->Elapsed Time per Exec(s)找到执行时间长的SQL语句（注意需根据Elapsed Time(s)/Executions和SQL Module判断此语句是否为发压过程中实际执行的查询语句），并记录其SQL Id。执行select * from table(dbms_xplan.display_awr(‘9p4xcbfjzx27z’))，其中“9p4xcbfjzx27z”为SQL Id，获得此SQL语句的执行计划，发现T表在查询时使用了全表扫描（TABLE ACCESS FULL），如图1所示。分析具体的SQL语句：select T.x, T.y from T where T.z !=‘A’，发现T表在y列和z列建立了一个联合索引，那么为什么执行这条SQL语句时仍然使用了全表扫描呢？这个问题可以从以下三个方面分析。

图1 在T表上执行查询操作时使用了全表扫描
　　一、单独引用联合索引里非第一位置的索引列作为条件查询时不走索引。T表中与z列有关的索引是与y列共同组成的联合索引，如图2所示。而y列做为联合索引中位于第一位置的索引，在此查询中并未应用，因此单独查询z列时并未使用此联合索引。建议将可选性高的字段和使用频繁的字段放在组合索引顺序的前面，因为联合索引的前缀性，只有左侧的索引字段得到应用，后面的索引才可能得到应用。举个例子，索引建立成IDX_TABLENAME(A,B,C)，当where条件中包含(A)或(A,B)或(A,B,C)时可以应用索引，条件为(B,C)时不能应用索引，条件为(A,C)时只有A字段可以应用索引。注：可选性也叫区分度，指的是字段中唯一值的数量多少，可以用选择率来衡量。选择率=字段中唯一值总行数/字段总行数，选择率越高代表可选性越高。图3为联合索引条件查询时使用和不使用第一位置列时的索引使用情况。

图2 T表的yz联合索引

图3 联合索引条件查询时(a)使用和(b)不使用第一位置列时的索引使用情况
　　二、不等于(<>、!=等)、not in等不走索引。若T表中的索引并不是组合索引，而是仅在z列创建索引，那么第一个问题将不存在，但负向查询的操作同样会导致索引失效。负向查询指的是NOT，!=，<>，!<，!>等操作符对条件字段进行操作的查询。这些负向操作相当于需遍历整个字段，因此无法用到索引。图4为条件查询时不使用和使用负向操作时的索引使用情况，为实验更加直观，只对z列创建索引。
图4 条件查询时(a)不使用和(b)使用负向操作时的索引使用情况
　　三、数据量分布会影响索引使用。根据应用的特点，当SQL返回的行数占整个表行数的比例<=5%时，建议建立索引。当查询的字段返回行数比例超过一定阈值，即可选性过低时，优化器认为全表扫描更省时间，不会使用该字段的索引。即若不考虑问题一和问题二，z列中A值的占比也将影响索引的使用。图5为条件查询时返回数据量占比高和返回数据量占比低时的索引使用情况，为实验更加直观，只对z列创建索引，z值为6占所有数据的98%，z值为7占所有数据的1%。可以看到(b)实验结果并未像预想的那样全表扫描，而是同样使用了索引，这说明以上三条的索引使用准则适用于大部分情况，但仍有少数情况需视数据库类型、版本、优化策略、数据等而定。

图5 条件查询时(a)返回数据量占比高和(b)返回数据量占比低时的索引使用情况
　　以上就是近期Oracle数据库性能测试过程中关于索引使用问题的分享，欢迎批评指正。