ORATEA

如何进行SQL优化(2全表 or 索引)

六月 19, 2008 – 10:08 上午

二，解决问题
1，全表 OR 索引
网上关于全表扫描和索引扫描之间的比较的文章数不胜数。不过对于大多数人来说，很难让他相信全表扫描有的时候会比索引扫描快。他们觉得使用索引就是为了提高SQL的效率，那么既然这样用了索引肯定要比全比扫描要好。
网上的建议一般是，如果扫描的记录数量占全表的总记录数量的10%以下使用索引扫描要好，否则就应该是用全表扫描。这个观点是有问题的，确切的是如果扫描的块的个数占所有块的个数的10%以下使用索引要好一些。这点在HWM下有大量空闲块的表中会体现的比较明显的。
索引之所以会提高数据访问效率，主要是两个原因，第一是索引的树型结构，第二是索引本身比较小。而索引比较小这点常常被忽略，而一味强调索引的树型结构。关于索引的扫描方式主要有，索引唯一扫描，索引范围扫描，索引全扫描，索引快速全扫描，索引跳跃扫描等。其中索引快速全扫描提高访问效率的重要原因就是，一般来说索引都是比表要小的。
以前我是做OLTP系统的，也经常帮同事优化一些SQL。那个时候80%的SQL问题都是由于索引引起的，不恰当的索引还不如没有索引。对于B+索引来说，仅仅那些经常访问的，选择性高的列才适合建索引。选择性低的列不太适合做索引，尤其是索引的前导列。
在9i中虽然推出了索引跳跃扫描，并不是说索引的前导列可以是那些选择性低的列了。其实索引跳跃扫描也不是高效的访问方式，只不过有时候可能会比全表扫描要好些。所以我们在建立B+索引的时候，还是得小心谨慎，选择正确的恰当的列来做索引。关于位图索引，我在正式的生产环境从来都没有使用过，位图在并发更新的时候有这严重的性能问题。位图索引可能仅仅适合OLAP系统吧。
使用了CBO之后，我们优化时候的工作真的少了很多。只要及时分析对象上的统计信息，主要包括表和索引的统计信息，列上的直方图信息。CBO都能正确的为我们的SQL选择正确的单表访问计划。说到统计信息了，就不得不说常常被忽略的直方图。使用CBO优化器后，单表选择有问题的时候大部分都是因为索引列的数据分布不平衡，有着严重的倾斜。在分析直方图之前，CBO无法得知在列上的不平衡，CBO总是傻傻的认为列的分布是平均的，列值的选择性都是相同的，所以CBO经常会做出错误的选择。上面说的主要是全表扫描和索引扫描之间的问题。那么如果有多个索引呢，oracle会怎么选择呢。在RBO的时候有一系列的规则的，都是些很傻瓜的规则，我这也不说，毕竟RBO已经是过时的东西了。在CBO中一切都用COST来说话，CBO会衡量使用各个索引的代价，然后选择一个代价最小的访问方式。大部分时候都是会选择尽量包含多个条件列的最小的索引。
解决方案：

（1）准确的统计信息，表的统计信息，索引的索引信息，索引列的统计信息
（2）简单的校验

例a，全表扫描比索引扫描高效的情况

SQL> create table sunwg(id number,name char(50));

表已创建。

SQL> insert into sunwg select rownum,rownum from dba_objects where rownum<1000
已创建10000行。

SQL> commit;

提交完成。

SQL> insert into sunwg select 1,1 from dba_objects where rownum<10001;

已创建10000行。

SQL> commit;

提交完成。

SQL> create index ind_sunwg on sunwg(id);

索引已创建。

SQL> set autot traceonly

索引扫描

SQL> select /*+index(t ind_sunwg)*/* from sunwg t where t.id = 1;

已选择10001行。
执行计划
———————————————————-
Plan hash value: 569973584

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
—————————————————————————
| 0 | SELECT STATEMENT | | 9820 | 623K| 109 (1)
| 1 | TABLE ACCESS BY INDEX ROWID| SUNWG | 9820 | 623K| 109 (1)
|* 2 | INDEX RANGE SCAN | IND_SUNWG | 9820 | | 24 (0)
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 - access(”T”.”ID”=1)

Note
—–
- dynamic sampling used for this STATEMENT

统计信息
———————————————————-
0 recursive calls
0 db block gets
1437 consistent gets
0 physical reads
0 redo size
677125 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10001 rows processed

全表扫描

SQL> select /*+full(t)*/* from sunwg t where t.id = 1;

已选择10001行。
执行计划
———————————————————-
Plan hash value: 856969577

—————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
—————————————————————
| 0 | SELECT STATEMENT | | 9820 | 623K| 42 (3)
|* 1 | TABLE ACCESS FULL| SUNWG | 9820 | 623K| 42 (3)
—————————————————————

Predicate Information (identified by operation id):
—————————————————

1 - filter(”T”.”ID”=1)

Note
—–
- dynamic sampling used for this statement
统计信息
———————————————————-
0 recursive calls
0 db block gets
851 consistent gets
0 physical reads
0 redo size
135120 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10001 rows processed

例b，索引跳跃扫描并不是高效的例子

SQL> drop table sunwg purge;

表已删除。

SQL> create table sunwg as select * from dba_objects;

表已创建。

SQL> create index ind_sunwg on sunwg(owner,object_name);

索引已创建。

SQL> analyze table sunwg compute statistics for table for all indexes for all indexed columns;

表已分析。

索引跳跃扫描

SQL> select * from sunwg where object_name = ‘OBJ’;

执行计划
———————————————————-
Plan hash value: 2063640765

—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
—————————————————————————-
| 0 | SELECT STATEMENT | | 2 | 194 | 28 (0
| 1 | TABLE ACCESS BY INDEX ROWID| SUNWG | 2 | 194 | 28 (0
|* 2 | INDEX SKIP SCAN | IND_SUNWG | 2 | | 26 (0
—————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 - access(”OBJECT_NAME”=’OBJ’)
filter(”OBJECT_NAME”=’OBJ’)

统计信息
———————————————————-
0 recursive calls
0 db block gets
27 consistent gets
0 physical reads
0 redo size
1200 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

全表扫描

SQL> select /*+full(sunwg)*/* from sunwg where object_name = ‘O
执行计划
———————————————————-
Plan hash value: 856969577

—————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
—————————————————————
| 0 | SELECT STATEMENT | | 2 | 194 | 161 (2)
|* 1 | TABLE ACCESS FULL| SUNWG | 2 | 194 | 161 (2)
—————————————————————

Predicate Information (identified by operation id):
—————————————————

1 - filter(”OBJECT_NAME”=’OBJ’)
统计信息
———————————————————-
0 recursive calls
0 db block gets
699 consistent gets
0 physical reads
0 redo size
1196 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

删除低效索引，重建索引

SQL> drop index ind_sunwg;

索引已删除。

SQL> create index ind_sunwg_new on sunwg(object_name,owner);

索引已创建。

SQL> analyze table sunwg compute statistics for table for all indexes for all indexed columns;

表已分析。

SQL> set autot traceonly
SQL> select * from sunwg where object_name = ‘OBJ’;

执行计划
———————————————————-
Plan hash value: 1944008724

———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
———————————————————————————-
| 0 | SELECT STATEMENT | | 2 | 194 | 3 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| SUNWG | 2 | 194 | 3 (0)|
|* 2 | INDEX RANGE SCAN | IND_SUNWG_NEW | 2 | | 2 (0)|
———————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 - access(”OBJECT_NAME”=’OBJ’)

统计信息
———————————————————-
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1200 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

建立恰当的索引之后，避免了索引跳跃扫描，逻辑读从27降到了4。这并不是说索引跳跃扫描不好，在索引建立的有问题的情况下，索引跳跃扫描会避免全表扫描巨大消耗。不过这只是治标不治本的，真正的问题在于那些低效率的索引上。
所以当我们遇到索引跳跃扫描的时候，不要仅仅为了避免了全表扫描而高兴，应该多想想是不是我的索引列选择或者索引列的顺序有问题。索引跳跃扫描是索引建立有问题的信号，我们要重视起来。