不同类型表的索引应用准则[摘]

 

1.拥有数据量较少的小型表

所谓小型表，是指一次I/O可以将表的全部数据块读取到内存中去，即存储表数据的数据块小于DB_FILE_MULTIBLOCK_READ_COUNT参数的设定。由于DBMS提供了多块读的功能，所以如果一次性就可以读取某个表的全部数据块，那么全表扫描与通过索引扫描读取数据的效率几乎没有什么差别，所以即使没有创建索引也不会影响执行效率。

如果只从单一的小型表中读取数据，那么索引的有无不会对执行速度有任何影响。但是在该小型表以不同方式与其他表执行连接的情况下，索引的有无将会对执行速度有着非常大的影响。

即使再小的表也应该确保具有由主键所创建的索引，因为主键对表连接的执行计划和各种数据完整性约束有着非常大的影响。尤其是这种表一般在嵌套循环中都位于内循环，需要被多次执行，在反复执行过程中即使有非常微小的差异也会产生较大的影响。

有时候不妨尝试使用索引组织表（IOT）的结构形式来存储这种类型的表。

 

 

2.主要起参考作用的大中型表

这种类型的表主要用来存储商业活动中的行为、主体、目的等对象的数据，在数据模型中主要属于关键实体（Key Entity）类，最具有代表性的表就是“顾客”表。通过分析统计这种表的读取类型，就可以发现主要以小范围扫描为主，或者大部分分情况下都位于内循环中，并且通过主键来执行表连接。

由于该类型表中存储的数据具有较强的独立性，所以在大部分情况下只读取其中满足条件的一部分数据。例如，思考一下顾客表，在这里不考虑像CRM那样对顾客的信息进行多样化分析的情况，一般情况下，对于顾客来说主要是以顾客编号、姓名、身份证号码、企业注册号码、电话号码等内容作为查询条件来从表中读取相关数据。

现在分析一下顾客编号。尽管顾客编号比较相似但是互相之间没有任何关联，某些顾客的注册时间可能比较相似，但是互相之间仍然没有任何必然的联系。一般情况，在顾客表中不仅需要存储大量的数据，而且都是以随机读取的方式访问，同时几乎不存在大量的数据被循环插入的情况。

也就是说，在这种表中主要所使用的查询条件已经很明确了，同时其主要的特征是，插入数据的操作并不经常发生，访问以查询为主。对于这种类型的表，只要为各个索引分配合理的任务（也就是创建了战略性索引），即使需要创建大量的索引也应当果断地做出决定。针对这种表创建索引的最好方法是在没有任何索引的前提下彻底地完成表的创建工作，之后一次性地为表创建所有的索引。为了在索引块中紧密地存储索引行，在创建索引时可以考虑将参数PCTFREE设置为近似于0的值。

索引与表不同，索引行长变化的可能性非常小。创建表时如果没有显式指定PCTFREE参数，则系统将默认值设置为10。同样，创建索引时默认的数据块将保留10%的空闲空间，我们建议不要将这10%的空间浪费掉。如果索引使用的时间过长或者执行过大量的数据变更操作，那么有必要对索引执行重构。

 

 

3.管理具体业务行为的大中型表

         这种类型表的主要特征是在查询中经常位于循环的外侧，并且随着时间的推移数据量不断增加。例如“销售信息”表比较符合这种类型特征，该表负责存储业务执行内容的交易活动，表中的列及读取过程中所使用到的查询条件多种多样。由于数据量不断增加，所以哪怕在索引的构成上稍微有些问题，都会很大程度上导致数据查询范围的扩大。

         由于通常无法通过某个特定的列来缩减查询范围，所以在大部分情况下都是使用多列组合来创建多个复合索引。由于数据量在不断地增加，所以使用B-Tree索引的情况比使用位图索引的情况多。又因为使用非“=”比较的情况较多，所以随着组合索引中列序的不同，执行效率也有着很大的不同。

         在有些情况下，对这种类型的表也可以使用分区或者聚簇。随着表结构的不同创建索引的战略方案也有所不同。数据量不断增加也意味着我们不能忽视数据插入时的代价，过多的索引不仅会增加数据插入时的成本，而且在某种程度上也会增加对存储空间的需求。我们必须要做的事情是，通过构建最理想的索引，实现以尽可能少的索引满足尽可能多的数据读取要求。

         为这种类型的表穿件战略性索引时，首先，搜集正在使用的所有数据读取类型；其实，寻找能够满足所有读取要求的比较理想的索引组合。事实上，对于这种表只需要通过改善索引结构就可以获得非常好的效果，且可以在很大程度上提高执行效率。

         在设计战略性索引时，除了考虑现在，还要顾及将来新增的数据读取类型。当增加了新的读取类型时，开发人员不应该为了该新查询而随心所欲地创建索引。由于用户的某些失误会对整个系统造成严重的不良影响，所以建议在编写查询语句时养成观察执行计划的习惯。

         对于这种类型的表而言，应该花费时间和精力去做的事情就是针对反复读取和大范围数据查询的情况寻找解决方案，根据二八法则，这部分访问应当成为关注的重点。如果没能为这两种情况寻找到比较有效的解决方案，则系统的最优化也只能是纸上谈兵。

         根据具体业务需要，也许在最初没有为该类表创建索引或者只创建了部分索引，但随着数据的插入进而使得数据量不断增加，此时，为了提高处理速度而必须创建新的索引。对于这种在具有一些索引的前提下对表执行大量的插入、修改、删除操作需要付出非常大的代价，所以对于创建索引的时间也仍然需要进行战略性的规划。

 

 

4.存储用的大型表

         这里所指的存储用的大型表主要是用来存储和管理日志性的数据。该类型表的特征为不仅存储着大量的数据，而且数据量不断在增加。对这种表并没有过多的读取要求，主要是以存储日志性的数据为目的，并且在这些日志性的数据进行加工处理之后，将其作为档案来保存，在有需要时直接从该类表中读取所需的数据。

         由于这种类型表的数据量在不断增加，所以数据插入时的代价较大，由于该类表以存储日志性的数据为主要目的，所以数据修改操作几乎不执行，可以将表的PCTFREE参数设置为0，由于这种表使用主键也容易增加成本开销，所以在允许的情况下建议不要设置主键。在没有为表指定主键约束的情况下，当需要主键进行识别时可以通过为其创建唯一索引达到目的。

         通过为这种类型的表创建分区可以实现有效管理的目的。一般情况下，越是时间久的日志性数据对其进行读取的概率就越小。所以，可以将时间较久不经常使用的数据存储在独立的分区中对其进行单独管理，从而减轻该表的整体负担。