SQL语言艺术(三)战术部署：建立索引

这章讲解为关键数据提供最高效访问路径的基本策略：建立索引

打破索引的神话

数据库表的访问突然变慢了？加索引吧。我们听过太多类似的索引能提高访问速度的话了，索引当然不是包治百病的灵丹妙药，先来看看索引带来的问题吧

1.索引是一种尽快访问特定数据的技术，但无法对所有数据实现快速存取

索引是一种以原子粒度访问数据的手段，而不是为了检索大量数据的。程序的输入值，定义的数据子集的大小是建立索引的基础。如果索引策略与数据检索需求严重不符，反而会降低查询性能

2.索引开销相当大

包括磁盘空间与处理开销，有时索引的数据量远远超过数据表本身的大小，维护一个索引的开销，可能会比维护一个表的开销还要大。而且每当在表中插入或删除记录时，该表的索引都必须进行调整，而对需要频繁更新的字段加索引，也会增大索引的维护成本。如果在未建索引的表中插入数据需要100个时间单位，每增加一个索引会增加100到250个单位。在大规模并发访问的环境中，过多索引会造成资源竞争和加锁

为哪些字段加索引？

仅管有这些缺点，索引仍然是数据库中极重要的组成部分。我们该如何进行取舍呢？

匹配某索引键的记录所占的平均百分比反映了索引的可选择性，百分比越低，索引的可选择性就越高

对于OLTP(联机事务处理)应用来说，如果表都符合范式的要求，需要加索引的字段应该很少。主键会自动加上索引，具有唯一性的字段可能在实现完整性约束的时候被加上索引，对于接近唯一性的字段应考虑加上索引。

有些数据库产品还允许为函数的处理结果建立索引，即函数索引。这类功能应该慎用，只做为程序代码无法修改时的备选方案，函数索引暗示设计存在问题，数据模型甚至连基本的数据原子性问题都没有解决

外键增加索引也要谨慎，外键加索引能减少并发更新主表时对子表进行一致性检查所花的时间，减少子表加锁的时间。但如果主表是只读或者很少被修改的，此时增加索引有害无益

OLTP应用中，太多索引往往意味着设计不够稳定，我们应该根据设计的初衷判断大量增加索引的合理性

哪些因素影响索引效果？

索引键相关记录的物理位置是否邻近很重要，记录的排列顺序与索引键相同，可以提高范围(range)查询的速度

索引通常使用树结构来实现，以避免对表进行大量插入，更新及删除操作后，索引快速退化。函数会导致索引失效，如果在where子句中需要对字段使用函数，说明表中原子性的数据可能不符合业务需要，这时连1NF都不符合。当然很多时候我们可能通过适当的转化，来避免对where中的字段使用函数，比如：

where trunc(created_date) = to_date('10-MAR-2010', 'DD-MON-YYYY')

以上SQL会导致created_date上的索引失效，如果改成以下的形式，则可以避免这个问题

where created_date >= to_date('10-MAR-2010', 'DD-MON-YYYY') and created_date < to_date('11-MAR-2010', 'DD-MON-YYYY')

有时where条件中某类型的字段要与另一类型的常数做比较，引起DBMS进行隐式的数据类型转换，也会造成索引失效，为了避免隐患，一定要显式地用转换函数进行转换

即使查询使用了索引也不代表访问是高效的，最有效的索引访问类型是唯一性索引。在非唯一性索引的情况下会发生范围搜索，找到索引后，还必须根据索引提供的地址访问每个数据块，符合要求的记录是聚焦在同一磁盘区域还是散布在不同地方，对性能影响较大

总之，索引不是万灵药，充分理解要处理的数据，做出合理的判断，才能获得高效方案