Oracle 之索引，妙不可言（三）——BTREE索引下

BTREE 索引的三大特点

• 高度(level)较低
  • select * from t where object_id=xxx;
    • * 表示查询出表的所有列
    • 索引IO次数：1、找数据块的表。2、找表对应的列。3、找（索引块）列对应的ID。4、根据数据块检索表所有列。
    • 优化：select object_id from t where object_id=xxx;
    • 优化IO后次数：1、找数据块的表。2、找表对应的列。3、找（索引块）列对应的ID，定位到数据块具体列。
  • 分区索引设计
  • 索引树的高度一般都比较低
  • 空间大小，存储数据量
  • 索引是 INDEX RANGE SCAN
• 存储列值
  • cound(*)优化
    • 索引列有空值，索引不起作用
      • 设置索引列为主键
      • 指定索引列非空属性
      • where 条件添加索引列 is not null
    • 如果表只有一个字段，索引比表还大（多了rowid），则用cound(*)就不高效。
    • 索引是 INDEX FAST FULL SCAN
  • sum/avg优化
    • 函数用不到索引（因为列允许为空）
    • select sum(object_id) from t where object_id is notnull;
      • 索引列非空后，sum/avg可用索引
  • max/min优化
    • 无关表的数据量
    • 优化写法
      • select max,min from (select max(object_id) max from t) a,(select min(object_id) min from t) b;
  • 索引与回表(TABLE ACCESS BY INDEX ROWID)
    • 写语句时尽量用到列名，可提升性能
    • 组合索引的应用，可削减回表动作
    • 聚合因子决定回表查询速度
      • 通过字典判断聚合因子
        • select index_name,blevel,leaf_blocks,num_rows,distinct_keys,clustering_factor from user_ind_statistics where table_name in(...);
      • 聚合因子低
        • 表和索引的排列顺序相似度高
          • 查询速度快
      • 聚合因子高
        • 表和索引的排列顺序相似度低
  • 索引列存储的值及rowid组成
• 结构有序
  • order by 排序
    • 对索引列进行 order by 这样性能就更低了，不用回表
  • distinct 去重
    • distinct是有排序的
    • 加上distinct是一种影响性能的查询方式
    • 建索引可削除distinct的排序，对语句进行优化，但收效不明显
    • 强制引用索引：/*+index(表名)*/
  • 多表合并：在这两种情况下索引是无法消除排序的
    • UNION
      • 合并后没有重复记录，类似 distinct
    • UNION ALL
      • Index fast full scan
      • 需要排序，因为是简单的合并
  • 索引是顺序取出表数据，并有充插入索引块中

此文章为个人的笔记，如有写的不对或你对这方面还有什么高见，评论区见。