为关系型数据库添加索引是一种最基础的优化方式

基础虽基础, 但其实并不简单, 记得几年前去一家国内大公司的云计算部门面试

提到索引的时候面试官说”加索引都是最基础的”, 我不赞同这种说法, 基础但并不简单

考虑下面这种情况

1 索引的原理是什么

2 我应该在哪些字段上加索引

3 我应该对每个字段使用独立的索引还是应该使用联合索引

4 加了索引之后的坏处是什么

5 哪些情况下没有索引反而要比有索引更快

下面我们进一步讨论这些问题

1 索引的原理是什么

索引的工作方式其实和view差不多, 每个索引都会构成一个单独的索引表, 这里我不想讨论B tree之类的问题

当你为字段A创建了一个索引之后,数据库会生成一个独立的索引表

2 我应该在哪些字段上加索引

根据问题1, 实际上如果你直接使用select A from Table 时,数据并不需要对主表进行查询,而是直接查询索引表,这样可以避免全表扫描

下面这个表只有一个聚簇索引(主键索引,一般来说数据是按照聚簇索引来物理排序的,磁盘上的存储位置是连续的)

不过我的表里数据只有100条左右,所以看不出耗时差别

EXPLAIN PLAN FOR  select * from INO_OUTAGES.OUTAGE;SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));EXPLAIN PLAN FOR  select ID from INO_OUTAGES.OUTAGE;SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));

检索所有列时数据库会进行全表扫描

只检索索引列时, 只扫描索引表

如果是既查询索引列,又有额外的非索引列, 实际上也会进行全表扫描

EXPLAIN PLAN FOR  select ID,DURATION,DESCRIPTION from INO_OUTAGES.OUTAGE;SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));

接下来看看谓词的情况

EXPLAIN PLAN FOR  select * from INO_OUTAGES.OUTAGE where id=2146;SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));

可以看到当谓词是索引列时, 没有进行全表扫描 ( 不过如果你使用id<2146这种范围查询,oracle还是会使用全表扫描的方式)

还有order by 之类的谓词需要注意

根据上面的测试结果, 只有当 select用到的查询列, where使用的谓词, order by的谓词全部均为索引列时, 数据库才会使用索引表进行查询

如果一个查询需要经常被调用(此时可以忽略索引带来的更新和空间消耗), 最好将select,where,order by等使用的列全部添加索引

但是我们还会有下面的疑问

3 我应该对每个字段使用独立的索引还是应该使用联合索引

当KEY, AREA_KEY, MANDATOR_KEY为联合索引,你尝试select这三列时,会得到一个INDEX FAST FULL SCAN

区别在于

索引快速全扫描的执行结果不一定是有序的。这是因为索引快速全扫描时Oracle是根据索引行在磁盘上的物理存储顺序来扫描，而不是根据索引行的逻辑顺序来扫描的，所以扫描结果才不一定有序, INDEX FAST FULL SCAN的性能要大大优于INDEX FULL SCAN

EXPLAIN PLAN FOR  select KEY, AREA_KEY, MANDATOR_KEY from INO_ONCONFIG.LOOKUP_OPTIONS;SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));

谓词方面复杂一些

第一个例子, 如果不使用联合索引中全部的3个列,会得到一个全表扫描

EXPLAIN PLAN FOR  select * from INO_ONCONFIG.LOOKUP_OPTIONS where AREA_KEY=’IFMS_SYMPTOM_CODE’ and MANDATOR_KEY=’PLATFORM’;SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));

第二个例子, 如果使用全部的三个索引列做谓词,会得到一个索引表扫描

EXPLAIN PLAN FOR  select * from INO_ONCONFIG.LOOKUP_OPTIONS where KEY >’1′ and AREA_KEY=’INVOICE_INTERVALS’ and MANDATOR_KEY=’PLATFORM’;SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));

上面这个例子如果连select列都为索引列的话你甚至可以只使用索引表,不用查询主表

EXPLAIN PLAN FOR  select AREA_KEY,MANDATOR_KEY from INO_ONCONFIG.LOOKUP_OPTIONS where KEY >’1′ and AREA_KEY=’INVOICE_INTERVALS’ and MANDATOR_KEY=’PLATFORM’;SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’));

4 加了索引之后的坏处是什么

增加了存储空间和插入及修改数据时造成的索引维护

5 哪些情况下没有索引反而要比有索引更快

这里有一种极端情况就是数据库所使用的硬盘随机读操作比较慢,而连续读比较快

假设谓词是 id in (1,10,13,45,234,676,233,234,67,89), 整个表的容量是100M

对于现代硬盘读取100M可能只需要1秒, 而使用索引时,由于先扫描索引表,然后通过索引产生10次随机硬盘读取, 如果每次平均随机读时间超过了100毫秒, 则使用索引反而会消耗更多时间