为关系型数据库添加索引是一种最基础的优化方式
基础虽基础, 但其实并不简单, 记得几年前去一家国内大公司的云计算部门面试
提到索引的时候面试官说”加索引都是最基础的”, 我不赞同这种说法, 基础但并不简单
考虑下面这种情况
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毫秒, 则使用索引反而会消耗更多时间