《收获，不止Oracle》读书笔记（一）：索引优化

最近在补数据库盲点之一：索引的优化，这里做下记录。我看的是《收获，不止Oracel》这本书，说实话，是因为豆瓣读书上评分很高，才选了这本，但是，好像不太适合我的感觉，行文风格有点啰嗦了，看来还是直接切入要害更适合我。

索引优化

索引的三大特点：

1. 索引树的高度一般都比较低；
2. 索引由索引列存储的值和rowid组成；
3. 索引本身是有序的。

插一句，Oracle中追踪索引执行性能的方法：

• set autotrace traceonly（不显示语句返回结果，只跟踪）
• alter session set statistic_level=all;（需要SQL执行返回结果之后才跟踪索引）

高度较低

• 用好分区索引（index_partition）：分区索引相对于普通索引，高度变化不大（即IO次数变化不大），但是索引数目由于分区会显著增加，如果搜索用不到分区条件，性能将明显下降；反之，需要使用分区字段的条件，能显著提升性能。

存储列值

• COUNT（*）优化：索引列不能存在NULL，注意：列定义的时候申明为可以为空（没有使用not null关键字），则oracle查询不会主动冒险去使用该索引，只会保守地采取全表遍历，所以需要显式地添加条件非空。

  • eg：create index idx1_object_id on table(object_id); – 创建索引
  • select count(*) from table where object_id is not null; –显式指明为非空
  • 当表只有一个字段（即只有一列）时，采用索引会比表还大（多了rowid），所以此时不宜采用索引。

• SUM/AVG优化：同COUNT（*）类似，需要指明为非空或列属性为非空

• MAX/MIN优化：

  • 不需要显式申明为非空，就可以使用索引
  • 单个最值的查询，会直接使用INDEX FULL SCAN(MAX/MIN)（即直接读取索引树最底层的最右边块里的最后一行/最左边的块里的第一行，逻辑读仅为2），速度比INDEX FAST FULL SCAN快，而且无论表中记录如何增大，查询性能也不会太慢
  • 两个最值同时查询（即max、min在一条sql语句中同时查询），就不会采用INDEX FULL SCAN(MAX/MIN)，转而采用全表扫描TABLE ACCESS FULL；此时需要显示申明为非空，则可以初步提升为使用索引查询的INDEX FAST FULL SCAN，但仍然不会使用INDEX FULL SCAN(MAX/MIN)，需要进一步改写提升：
    
    • 误区：两个最值分开查询，可以达到优化，但是此时查询到的最值并不对应（max、min先后执行，数据库的记录已经变化）
    • 优化版【两次INDEX FULL SCAN（MAX/MIN），逻辑读为4】： select max,min from (select max(object_id) max from table) a,(select min(object_id) min from table) b);
  • 查询速度（从快到慢）：INDEX FULL SCAN(MAX/MIN) > INDEX FAST FULL SCAN > TABLE ACCESS SCAN

• 索引回表（TABLE ACCESS BY INDEX ROWID）：指索引列没有查询所需的全部列，则需要根据index中的rowid定位到表中对应数据块来获取数据。

  • 创建组合索引来消除索引回表（组合索引不宜列过多，一般，最好维持组合列<=3）
  • 聚合因子决定了索引回表的速度：索引列与原表中对应列的顺序越一致（相似度越高），则聚合因子越低，速度越快
    
    • 聚合因子：通俗说，就是描述索引列（有序）与原表中对应列（无序）的排列顺序的相似程度
  • 所以，选取表中读取频率远高于其他列的列创建索引，可以保证表的排列顺序与索引顺序相似度较高，即聚合因子较小，索引回表速度更快。

索引有序

• ORDER BY（按照某列排序）：sql执行会有排序过程，如果对某列创建索引，且尽量避免产生回表操作，查询操作时，能消除排序，提升性能。

• 插一句，sql语句的执行效率的评判一般重点看cpu的使用率和执行时间两个指标：Cost（%CPU）、Time，逻辑读（consistent gets）指标在90%的情况下，也可以作为参考，逻辑读越少性能越快，但不适用于排序算法。

• DISTINCT（消除重复）：sql执行会有排序操作（HASH UNIQUE），创建索引，只能提升一点点性能，收效不大。

• 索引全扫（INDEX FULL SCAN）和索引快速全扫（INDEX FAST FULL SCAN）：

  • 索引快速全扫比索引全扫速度快
  • 索引全扫一次只读一个块，能保证有序；而索引快速全扫一次读取多个块，即减少逻辑读，不能保证有序；
  • 当sql执行需要排序时，由Oracle来判断选取那种扫描方式性能更优

• UNION（合并）：

  • UNION合并后没有重复记录，故有排序过程（SORT UNIQUE）；UNION ALL只是简单的合并，并没有筛选出重复记录
  • UNION是针对两个不同的结果集的筛选，分别创建各自的索引，并不能消除排序。
  • 日常常见优化：将UNION改成UNION ALL，因为大部分情况下，两个表根本不可能有重复。

主外键设计

• 外键（外键所在表的外键列取值必须在主表中的主键列有记录）上建立索引，能提升查询性能，避免锁的竞争
• 外键的删除，采用级联删除，即在外键约束上添加 on delete cascade，能在删除主键记录时自动删除外键记录，但为了数据的安全，不推荐使用

组合索引

• 等值查询时，组合索引无论哪一列在前，性能都一样
• 组合索引的两列，一列是等值查询，一列是范围查询，则等值查询在前，性能更好
• 有时候范围查询（>=、<=）改用in（a，b），效率会更高，因为后者是两个等值查询：==a、==b
• 如果单列的查询列与组合索引的前置匹配，可以直接使用组合索引优化查询性能

索引的危害

• 索引越多，插入数据越慢（同时需要维护索引的有序）
• 先把索引删除，无索引插入完毕，再建索引，往往比维护索引来插入性能更优
• 索引过多对insert负面影响最大；对delete有好有坏（分情况）；对update的影响最小

• 建立索引，需要排序，很耗CPU

• Oracle可以监控跟踪的索引，返回其一段时间内是否被使用过，有助于删除长期不用的索引

  • alter index 索引名 monitoring usage；–对需要跟踪的索引进行监控（取消监：nomonitoring）
  • select * from v$object_usage ; --观察v$object_usage