一．B树索引
0. B树索引中不存在非唯一的条目。
1)在非唯一索引中，ORACLE会把rowid作为一个额外的列追加到键上，使得键唯一。Exp：create index I on T( x , y ) ,从概念上讲就是Create unique index I on T(x , y , rowid)。ORACLE会首先按索引键值排序，然后再按照rowid升序排序。
2)在唯一索引中，数据只按索引键值排序。
1． 大多数情况下，B树索引的高度都是2或者3，所以一般情况下，在索引中找到一个键只需要2或3次I/O。
2． B树索引所有叶子块都应该在同一层上，并且叶子节点实际上都是双向链表，这样在进行索引区间扫描(index range scan)的时候，只需通过叶子节点的向前或者向后就可以了，无需再对索引结构进行导航。
3． 适当对B树索引存在重复值的列进行压缩, 可以增加缓存命中率，使I/O数减少，因为相关的条目可能都存在在一个块中。（Exp:create index I on T(userid , username) username=’steven’这个值可能就会对应于多个rowid放在同一个索引块中）；但是带来的负面作用是使索引结构复杂化，维护索引更耗时，查询索引占用CPU更多的时间。（压缩适合用于多列索引中）
4． B树索引的反向键索引主要用于缓解索引右侧缓冲区忙等待。适合用于类似于sequence产生的PK主键上，因为这些列不太会需要使用区间扫描，也就是不会用到max(PK),min(PK),between and或者where PK < 200等查询
5． 如果在查询中会有order by colum1 asc,column2 desc, 试着在创建索引时create index I on T(colum1asc,column2 desc) , 因为ORACLE INDEX默认是DESC排序，在索引中排序总比在磁盘中排序好得多。
6． 适合B树索引使用的2种情况：
1)访问表中占很小比例的行
2)根本不访问表，所需查询的数据全部在索引中
3)一种特殊的用法，使用索引访问表的全部行，这样的做法是为了使查询最初的响应时间很短，不是针对总吞吐量进行的优化。
7． 索引是按索引键顺序存储，索引会按键的有序顺序进行访问。索引指向的块则随机存储在堆中。
8． 建议：在thin表中使用B树索引查询<2-3%的列，fat表中<20-25%的列。
二．函数索引
1. 函数索引的好处: Exp: function(column1) 如果表中有1000行，即便column1列上建有索引，索引在此时也不被使用，function会执行1000次。
2． 建立函数索引会使插入/更新性能稍有下降，但是查询速度有了极大的提高。另外如果更新的时候没有涉及到建立函数索引的这个列，那就不会产生额外的开销。
3． 部分行建立索引：
1)某一列只有很少的基数，例如只有Y和N
2)通常只会查询where column1 = ‘N’
3)并且值为N的行占很小的比例
4)在此列上建立索引，由于很大的一部分索引空间是浪费的(等于Y的)，不会使用到的，所以我们可以在值为’N’的那些行上面建立索引。
5)Create index I on T(decode(column1 , ‘N’, ‘N’))
6)利用的是B树索引特性，如果列值为null,在索引中就没有相应的条目。
4．使用函数索引来保证复杂的约束：(某个条件成立时，X,Y,Z必须唯一)
1)如果项目表，项目有2种状态，ACTIVE和INACTIVE
2)希望ACTIVE的项目必须有唯一的项目名，INACTIVE的项目没有此要求
3)此时项目状态这列上我们不适合建立唯一键
4)但是我们可以建立一个唯一索引
5)Create unique index I on T(decode(STATUS , ‘ACTIVE’ , NAME))
6)利用的还是B树索引特性，如果列值为null,在索引中就没有相应的条目。
三．索引的一些常见问题
1．B树索引特性，如果列值为null,在索引中就没有相应的条目。
1)Create table T (x int , y int)
2)Create unique index I T(x , y)
3)Insert into T values(1 , 1)
4)Insert into T values(null , 1)
5)Insert into T values(1 , null)
6)Insert into T values(null , null)
7)此时索引中只有三行.(null , null)不在索引行中
8)Insert into T values(null , null) --成功插入
9)Insert into T values(1 , null) --报错，违反index唯一性
10)所以我们可以看到ORACLE中null <> null (null代表N/A)
11)Where x is null --这个查询无法使用索引，因为（null,null）不在索引中，如果ORACLE使用索引就会得到错误的答案
12)同样能够得出结论，如果在一个允许null的列上面建立索引，x is null也不会使用索引
13)可以使用索引的条件Create table T (x int , y int not null);
2.外键建立索引是需要的
3.索引跳跃式扫描
1)Create index I T(x , y)
2)Select * from T where x=5; --此时优化器可能不会使用索引
3)Select x , y from T where x=5; --可能使用索引，因为所需要的内容都在索引中
4)索引跳跃式扫描—skip scan
5)如果y只有2个基数，Y和N ,oracle会采用index
6)Select * from T where x=5; --会经过如下处理
7)Select * from T where x=5 and y=’Y’
8)Union all
9)Select * from T where x=5 and y=’N’;
4.如果索引建立在一个允许null的列上面, select * from T就会使用全表扫描，不使用索引，因为null的行并不在索引行中，所以不会使用index统计数目。
5.select * from T where x=5 等价于select * from T where to_number(x)=’5’,由于存在隐式转换，所以x上的索引不会得到使用
6.where trunc(date) = trunk(sysdate) 可以转换成 date >=trunc(sysdate) and date<trunk(sysdate+1)
7.定期分析表
如果发现Oracle 在有索引的情况下，没有使用索引，这并不是Oracle 的优化器出错。在有些情况下，Oracle 确实会选择全表扫描（Full Table Scan）,而非索引扫描（Index Scan）。这些情况通常可能是：
1. 表未做statistics, 或者 statistics 陈旧，导致 Oracle 判断失误。
最常见的例子，是以下这句sql 语句：
select count(*) from T;
在未作statistics 之前，它使用全表扫描，需要读取6000多个数据块（一个数据块是8k）, 做了statistics 之后，使用的是 INDEX (FAST FULL SCAN) ，只需要读取450个数据块。但是，statistics 做得不好，也会导致Oracle 不使用索引。
8.有些情况下单列索引不如复合索引有效率！
甲、有一种情况是显而易见的，那就是，当sql 语句所查询的列，全部都出现在复合索引中时，此时由于 Oracle只需要查询索引块即可获得所有数据，当然比使用多个单列索引要快得多。（此时，这种优化方式被称为Index only access path）。需要注意的是：where 子句中的这个字段，必须是复合索引的第一个字段
乙、还有一种情况就是where coid>=130000 and issuedate >= to_date ('2001-07-20', 'yyyy-mm-dd') 。此时复合索引I_mytabs_test ( coid, issuedate)的效果远远好于两个单列索引：I_mytabs1(coid), I_mytabs2(issuedate)。
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2015-6-17 12:29:24 |
赞(
操作
