如何设计索引

        一个表建多少索引合适

 

        有人说一个表的索引不能超过6个，这是不对的。衡量索引是否合理不能单纯的用一个数字来判断。在

 

一张表上创建多少索引，创建什么样的索引，并无一定的规律。不能说一张表上有6个索引，就不能再创建第

 

7个索引了。设计索引时应该从应用的角度出发，一切服从应用需要。

 

 

        大家都知道索引会增加维护的成本，影响DML语句的性能，但在一般的OLTP系统中，和DML操作相

 

比，SELECT操作所占的比例要少的多。如果少一个索引，可能导致某张大表经常进行全表扫描，增加的

 

CPU和I/O开销可能达到这个索引维护成本的上百倍。所以不能单独的凭一个数字来判断索引是否合理。

 

        分析索引是否合理，需要将SQL都找出来，按照 buffer get或者physical read 排序，分析排在前面对性

 

能影响较大的SQL，看WHERE 条件和连接条件，然后判断如何创建索引。

 

 

          设计索引时要统筹考虑 

 

        在设计时，必须为执行最为频繁，对系统性能影响较大的SQL设计成本开销最小的索引，而一些次要的

 

SQL，可以和其他SQL共用索引（建立复合索引来共用），这些索引可能并不是最优的，但能满足目前应用

 

的需求。要记住，最优的并不是最好的，最适合的才是最好的。

 

       尽可能的让一个索引为更多的SQL服务，设计合理的复合索引是十分关键的。

 

        适当的时候需要建立符合索引

 

         有这样一个SQL：

 

            select * from table where  A > xxx and B > xxx

 

        在A列上有一个索引，但该SQL在执行时发现通过A的索引筛选出来的有4000条数据，然后通过筛选出

 

最后的结果是10条。既然建一个复合索引（A+B），可以减少400倍的数据读取，为什么不呢？

 

        合并类似索引

 

          比如一个表上有A + B和 A+C  两个索引，设计一个A+B+C索引也许是不错的选择。

      

         比如有A + B+C和 A+C +B两个索引，设计一个A+B+C索引或者A+C +B索引即可，至于设计成 A+B+C

 

还是A+C +B取决于是A + B用的多，还是A+C作为筛选条件时用的多。

 

        比如A+ C+E和A+B+E => 可以设计成A+ C+B+E或其他的，要注意的是，具体字段顺序，要根据应用情

 

况取舍。

 

  

       尽可能将复合索引中选择性强的字段放前面，这样可以减少索引范围扫描成本。

 

        根据实际情况选择索引类别

 

        位图索引和B树索引的使用场合是不同的，位图索引存储的是字段值的位图，当修改索引的字段的值

 

时，会锁定和这个值相等的所有记录。因此，一般来说，某张表如果针对索引列更新、删除、插入比较频

 

繁，是不适合使用位图索引的，不然很容易出现死锁。一般来说OTLP中不适合使用位图索引。但如果不出现

 

死锁或锁增加的情况，在OTLP系统中使用位图索引也是可以的。

 

        要注意函数索引的使用

 

        函数索引和普通索引的维护开销是差不多的。如果系统中不可避免的在某列上使用函数，那么请放心的

使用函数索引吧。

    

           有时候在索引中增加部分额外的字段能起到很好的作用。 

 

          例如：

                SELECT A FROM TABLE WHERE B=：1

 

        对于这样的SQL，一般会创建B列的索引。但如果创建一个A+B的索引，可以避免对表的扫描。当然，

 

这里不是说碰到这样的情况就建立包含SELECT中的列的索引，应该根据实际情况来设计。尽信书不如无

书。

        使用索引时要注意的：

 

        第一：索引必须是有用的。

 

        不使用的索引会增加DML操作的成本，也可能导致错误的执行计划。所以必须清除无用的索引，但要注

意的是，除非有很大的把握，否则不要轻易删除索引。在删除索引时，要确认没有业务会使用到它。

 

        第二，确保表和索引的统计信息的一致性。

 

        如果索引的统计信息和表的统计信息不一致，可能会出现错误的执行计划。对索引分析后，需要对表重

新分析。确保表和索引的统计信息是一致的。

 

         第三，不要在索引列上使用函数。

 

        在索引列上使用函数，会导致索引失效。所以不要在索引列上使用函数。当然，如果确实要使用，可以

 

建立函数索引。

 

      另外，避免索引列上使用隐式的强制转换。比如，索引列A的字段类型为DATE。当和它比较的值是

TIMESTAMP类型时

 

            WHERE create_date > XXX 

 

    会将小类型向大类型转换。这时create_date 会被隐式强转成TIMESTAMP类型。变成

 

            WHERE TO_ TIMESTAMP (create_date)> XXX 

 

    导致不能使用索引。

    

        最后，索引时需要维护的。定期对索引进行评估和维护是十分必要的，关于索引的维护请参考这里：

       http://zhuyuehua.iteye.com/admin/blogs/1872448