索引优化原则及Oracle中索引总结

共计 2380 个字符，预计需要花费 6 分钟才能阅读完成。

Oracle 索引建立原则

• 确定针对该表的操作是大量的查询操作还是大量的增删改操作。
• 尝试建立索引来帮助特定的查询。检查自己的 sql 语句，为那些频繁在 where 子句中出现的字段建立索引。
• where 语句中不得不对查询列采用函数查询，如 upper 函数，最好建立相应函数索引；
• 在 SQL 语句中经常进行 GROUP BY、ORDER BY 的字段上建立索引
• 用于联接的列（主健 / 外健）上建立索引；
• 在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定；
• 尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间，同时，复合索引也占磁盘空间。
• 对于小型的表，建立索引可能会影响性能
• 在不同值较少的字段上不必要建立索引，如性别字段；
• 应该避免对具有较少值的字段进行索引。？？？
• 避免选择大型数据类型的列作为索引。
• 缺省情况下建立的是非簇集索引，但在以下情况下最好考虑簇集索引，如：含有有限数目（不是很少）唯一的列；进行大范围的查询；
• 充分的利用索引可以减少表扫描 I / 0 的次数，有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中
• 避免在有大量并发 DML 运算的表中使用 Bitmap 索引；
• 经常被更新，或者一个表虽然很大，但是如果多数查询返回结果都超过表中总行数的 4%，那么一般认为也是不宜建立索引的。
• 经常查询的记录数目少于表中所有记录总数的 5% 时就应当创建索引
• 存储索引的表空间最好单独设定
• 随着数据的变化，索引的效率会下降，因此应定期重建索引

Oracle 位图索引  B- 树索引

B- 树索引在 Oracle 中是一个通用的索引，在创建索引时它就是默认的索引类型。最多可以包括 32 列。

    创建语句：create index indexName on tableName(columnName);

    特点：

        1. 索引不存储 null 值。更准确的说，单列索引不存储 null 值，复合索引不存储全为 Null 的值。索引不能存储 Null，所以对这列采用 is null 条件时，因为索引上根本没 Null 值，不能利用到索引，只能全表扫描。

        2. 不适合键值较少的列。与数据块有关。

        3. 前导模糊查询不能利用索引 (like ‘%XX’ 或者 like ‘%XX%’)。这样会导致全表扫描。

位图索引 Oracle 为每个唯一键创建一个位图，然后把与键值所关联的 ROWID 保存为位图。最多可以包括 30 列。

    创建语句：create bitmap index indexName on tableName(columnName);

    特点：

        1. 相对于 B *Tree 索引, 占用的空间非常小, 创建和使用非常快。位图索引由于只存储键值的起止 Rowid 和位图, 占用的空间非常少。

        2. 不适合键值较多的列。

        3. 不适合 update、insert、delete 频繁的列。

        4. 可以存储 null 值。B*Tree 索引由于不记录空值, 当基于 is null 的查询时, 会使用全表扫描, 而对位图索引列进行 is null 查询时, 则可以使用索引。

        5. 当 select count(XX) 时, 可以直接访问索引中一个位图就快速得出统计数据。

        6. 当根据键值做 and,or 或 in(x,y,..) 查询时, 直接用索引的位图进行或运算, 快速得出结果行数据。

    位图索引有很多限制：

        基于规则的优化器不会考虑位图索引

        当执行 ATLER TABLE 语句，并修改包含有位图索引的列时，会使位图索引失效

        位图索引在索引块中储存了索引键的值；然而，他们并不能用户任何类型的完整性检查

        位图索引不能被申明为唯一索引

索引不会被命中的情况

1、查询谓词没有使用索引的主要边界，可能会导致不走索引。

    查询：SELECT * FROM T WHERE Y=XXX;

    假如 T 表上有一个包含 Y 值的组合索引，但是优化器会认为需要一行行的扫描会更有效，这个时候，优化器可能会选择 TABLE ACCESS FULL，

    查询换成：SELECT Y FROM T WHERE Y = XXX，优化器会直接去索引中找到 Y 的值，因为从 B 树中就可以找相应的值。

2、如果在 B 树索引中有一个空值的时候，优化器可能不会走索引。

    查询：那么查询诸如 SELECT COUNT(*) FROM T 

    有两种方式可以让索引有效，一种是 SELECT COUNT(*) FROM T WHERE XXX IS NOT NULL 或者是不能为空。

3、在索引字段上使用函数，导致不会走索引。

    查询：SELECT * FROM T WHERE FUN(Y) = XXX。

    如果在 T 表上有一个索引 Y 时，索引也不会被用到，因为你要查询的列中所有的行都需要被计算一遍，

    可以在这个表上建立一个基于函数的索引，比如 CREATE INDEX IDXFUNT ON T(FUN(Y)); 这种方式，等于 Oracle 会建立一个存储所有函数计算结果的值，再进行查询的时候就不需要进行计算了。

4、索引不适用于隐式转换的情况。

    查询：SELECT * FROM T WHERE Y = 5 

    在 Y 上面有一个索引，但是 Y 列是 VARCHAR2 的，那么 Oracle 会将上面的 5 进行一个隐式的转换成 SELECT * FROM T WHERE TO_NUMBER(Y) = 5, 这个时候也是有可能用不到索引的。

5、如果表只有几个数据块大小，而且可以被 Oracle 一次性抓取，那么就没有使用索引的必要了，因为抓取索引还需要去根据 rowid 从数据块中获取相应的元素值，因此在表特别小的情况下，索引没有用到是情理当中的事情。

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-03/141538.htm