索引

1.索引

• 索引说明

  • 索引是表的一个可选结构，在逻辑上和物理上都独立于表的数据，索引可以优化查询，当不能优化DML操作，Oracle会自动维护索引，频繁的DML操作反而会引起大量的索引维护，降低性能。
  • 若sql仅仅访问有索引的列，那么数据库将会只读取索引，而不读取表。

• 索引目的：主要为了较少IO，一般在以下情景中使用索引比较合理。

  • 大表，返回的行数 < 5%
  • 经常使用where子句查询的列
  • 离散度高的列
  • 更新键值的代价低

2.索引的使用

• 唯一索引（unique or non_unique）：唯一索引，键值不重复。

  -- 创建
  CREATE UNIQUE INDEX 索引名 ON 表名(列名); 
  -- 删除
  DROP INDEX 索引名; 
  

• 普通索引：键值可重复。

  -- 创建
  CREATE INDEX 索引名 ON 表名(列名); 
  -- 删除
  DROP INDEX 索引名; 
  

• 组合索引：绑定两列以上的索引

  -- 创建
  CREATE INDEX 索引名 ON 表名(列名1,列名2); 
  -- 删除
  DROP INDEX 索引名; 
  

• 函数索引：查询时必须用到该函数，才会用到。

  -- 创建
  CREATE INDEX 索引名 ON 表名(函数名(列名)); 
  -- 删除
  DROP INDEX 索引名; 
  

3.索引的问题

• 索引碎片的问题：由于对标做DML操作时，会导致索引表块的自动更改操作，尤其是delete操作，会引起index表的index_entries的逻辑删除，而仅当一个索引块中所有的index_entries都被删除了，才会把这个索引块删除，故delete、insert等操作容易引起索引碎片问题。

• 索引问题分析：当需要查看索引有没有必要进行处理时，一般通过以下步骤进行排查。

  • 1.对索引信息进行分析

    ANALYZE INDEX 索引名 VALIDATE STRUCTURE;
    

  • 2.查看指标

    SELECT NAME, HEIGHT, PCT_USED,DEL_LF_ROWS/LF_ROWS FROM INDEX_STATS WHERE NAME = 索引名;
    

  • 3.满足以下任一条件，则有进行处理的必要。

    • HEIGHT >= 4
    • PCT_USED < 50%
    • DEL_LF_ROWS/LF_ROWS > 0.2

• 索引问题处理：

  • 方式1 使用oracle整理碎片的功能

    ALTER INDEX 索引名 REBUILD ONLINE;
    

  • 方式2 重新建表，再将数据导入