SQL优化(一) 索引

对于sql的执行效率而言，有两个非常重要的因素，一个是索引，另外一个是关联(join)。绝大多数的sql性能问题和不当的索引和关联相关。本篇先说说索引。
一、 索引类型1. B* tree index，即普通索引
2. 位图索引
3. Function based index函数索引
其他索引还包括bitmap join index，应用索引等，很少使用。下面介绍这3种最常用索引。

[@more@]

二、 B*tree index
(一) 索引结构
1. 索引结构包括branch block和leaf block（leaf nodes）。最上面的branch block称为root block。Leaf block(叶子节点)包含index key和rowid信息。Root block到leaf block的层次称为索引高度(level，or height)
2. 根据索引读一行记录的过程是，从root block遍历到leaf block，再根据leaf block的index key找到rowid，再读出对应block找到相应的row。如果height是3,则需要3+1个block的io
3. 索引的所有leaf block高度相同，这说明不管索引值是什么，遍历索引的开销是一致的。大多数表的索引高度是2-3层，检索的开销是2-3个block的io。这说明对于各种不同量级的表，b*tree的效率都是很高的。下面是**系统中几张不同数量级的表的索引高度
Table_name index_name 记录数 BLEVEL
Rate_discount RATE_DISCOUNT_XDISC_ID 100961 2
PRODUCT PRODUCT_PK 37693660 2
CDR_BILLED_2012_06_01 CDR_BILLED_PK 146877600 3(二) b*tree index的适用/不适用场合
1. 当访问一个表的少部分记录时应该用B*tree索引。前面说过索引检索2-3 block的io，然后根据rowid读取表中的记录。这种情况下比全表扫描效率高很多。
'少部分'能否确切定义？不能。一个可参考的经验值是：
对于thin table，即每行字节数较少的表，2-3%
对于fat table，即每行字节数较多的表，20%以内
2. 如果表记录数很少，使用索引效率反而低。例如，只有几十条记录，所有数据在一个block内。则全表扫描只需1个block的io，而索引读可能需要几个block
3. 如果访问一个大表的较大部分记录，使用索引效率反而低。
4. 对于第3点，例外情况是如果索引键值已经包含了查询的要求。如index on t(a,b)
Select count(*) from t;
Select a from t;
这种情况下，索引可以看作是'瘦身'的table，oracle会使用index full scan代替table full scan，毕竟索引比table小。
(三) 调优例子：不走索引反而性能提高！
update product set no_bill=1 where parent_account_no = 48003823 and parent_subscr_no …;
由于parent_account_no上有索引，因此oracle会选择index range scan。但由于这个account_no的记录数约4000万，整个product表大约9500万，sql运行超过4小时还出不来。
update product set no_bill=1 where parent_account_no+0 = 48003823 and parent_subscr_no …;
强制不走parent_account_no上的索引，执行速度反而快了，时间小于2小时。
三、 位图索引
(一) 索引结构
1. 和普通索引相比，位图索引只有少量index entry
2. 每个index entry指向很多行，用一个bit表示表一行，0表示不匹配，1表示匹配
(二) 位图索引适用场合
1. 字段值low distinct cardinality ，即唯一值相对于总行数的比例低，例如一个字段只有T/F两个值。
2. 适合ad hoc query（数据仓库领域有一个概念叫Ad hoc queries，中文一般翻译为"即席查询"。即席查询是指那些用户在使用系统时，根据自己当时的需求定义的查询）。
这类查询在OLAP或报表系统中是很常见的，where字段有各种组合，如
select *from t
where ( ( gender = 'M' and location = 20 )
or ( gender = 'F' or location = 22 ))
and age_group = '18 and under';
select count(*) from t where age_group = '41 and over' and gender = 'F';
如果是普通索引需要建多种组合的复合索引以便不同查询使用，索引空间会很大。而位图索引多个索引可以很方便地进行AND/OR操作，只需在字段上各建一个位图索引即可

以下测试显示，bitmap index两个索引能进行AND操作，而普通索引则不会
使用普通索引只用到一个索引
create table test_table as select owner,object_type,object_name from dba_objects;
create index test_idx1 on test_table(owner);
create index test_idx2 on test_table(object_name);
select count(*) from test_table where owner='HSS' AND object_name='TEST_TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 3928831041

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 29 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| TEST_TABLE | 1 | 29 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TEST_IDX2 | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
使用位图索引两个索引的检索结果能进行AND操作
drop index test_idx1;
drop index test_idx2;
create bitmap index test_idx1 on test_table(owner);
create bitmap index test_idx2 on test_table(object_type);
select count(*) from test_table where owner='HSS' AND object_type='TEST_TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 1409243622

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | BITMAP CONVERSION COUNT | | 55 | 715 | 2 (0)| 00:00:01 |
| 3 | BITMAP AND | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE| TEST_IDX2 | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE| TEST_IDX1 | | | | |

(三) 什么时候不宜用bitmap index
位图索引适用在大量读的场合，但不适合大量写的环境，特别是并发写的环境。因为当一个index entry被修改时，这个index entry指向的所有行都会被锁，oracle无法锁住单独的bit，而是锁住整个bitmap index entry。因此一个update可能导致几百行被锁。因此位图索引在OLAP系统中较常见，而OLTP系统中几乎不用。
四、 函数索引
(一) 函数索引有以下特点:
1. 函数索引在index entry中保存的是函数的计算结果,固化函数计算结果，提升性能
select ename, hiredate
from emp
where my_soundex(ename) = my_soundex('Kings')
如果没有函数索引，假如有n行，会调用my_soundex函数n次。如果有，则只需1次
2. 使用方便，不需要改写现有表结构和程序
3. 性能上，对insert/update会有些负面影响，但对查询性能提高很多，需要进行权衡。通常来说insert一条记录只要1次，但查询可能会进行很多次，因而是值得建索引的。
4. 能在某些行上面建索引而忽略其他行，以节省空间。某些情景下可代替位图索引，比位图索引有更好的并发行，而且空间也小(二) 应用例子
1. 函数索引仅在某些行上建索引的例子。
场景：假设1个表的字段process_flag只有两个值，N表示新记录未处理，处理后变为Y。大多数记录为Y。主要操作是查询process_flag='N'的记录进行处理，然后将process_flag值改为'Y'
如果使用B*tree索引，索引空间大，BLEVEL高。如果使用位图索引,并发修改性能又差。这时可使用函数索引（只在值为N的记录上）：
create index processed_flag_idx
on big_table( case temporary when 'N' then 'N' end );
2. 用函数索引在某些值上实现完整性约束的例子
场景：project表（name,status）。对于status='ACTIVE'的project,name必须唯一。但status='INACTIVE'则可以有多条重复记录。
Create unique index active_projects_must_be_unique
On projects ( case when status = 'ACTIVE' then name end );

五、 未能走索引的几种情况

以下是使用索引不当所引起的不走索引的几种常见情况：
1. Index on t(x,y)但where 条件中只有y字段。通常情况会进行全表扫描。
2. select count(*) from t通常由于索引比table小，oracle会进行index full scan。但如果索引字段含有NULL值，则不会走索引，因为索引值不包含null，如果进行index full scan统计值就不准确了。
3. select * from t where f(index_column)=value 如果不是函数索引，where条件在索引字段上进行函数操作则不走索引
4. select * from t where indexed_column=5 字段类型需转换。例如indexed_column是字符但where条件中用了数字
5. oracle优化器认为全表扫描比走索引效率更高。这种情况下oracle选择全表扫描。如果开发人员觉得有必要走索引，可以使用hint强制走索引
6. 未及时对表进行analyze，statistics不准确。例如原先是小表，后来数据量大增。由于statistics仍是旧的，oracle优化器会选择不走索引

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18474/viewspace-1060727/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18474/viewspace-1060727/