【索引】联合索引的基本知识

  0.预备                                                                                                           

假设我们有表 user (id,name)列

  1.联合索引是个什么东西                                                                                  

　　我们知道，对于表的单列(如id)数据，是可以建立索引的，对于多列(id和name组合,或者,name和id组合)，也可以建立索引。联合索引，也称之为组合索引。

      先来看单列索引的逻辑结构。

      

　　由此得出索引的三个特征：索引有序+高度较低+存储列值

　　联合索引也满足这三个特征，但这里的逻辑图就不画了，而是以更直观的方式来展现其查找逻辑，这里应注意，联合索引的列有前后，以id列在前、name列在后为例。

     建联合索引前的情况

    

    建联合索引后的情况(先按ID排序+再按Name排序)

　 

 

  2.建立联合索引的语法                                                                                      

 create index indexName on  tableName(column1,column2,...,columnN)，如create idx_un_userid_username on user(id,name)

 联合索引可以建立多列(列数大于2)的索引,建议列数最多不要越过3列，超过3列，应重新设计表。

  3.联合索引的好处                                                                                            

   A:避免回表

      在执行计划中，table access by index rowid代表是回表动作。

      如在user的id列建有索引，select id from user这个不用回表，直接从索引中读取id的值，而select id,name from user中，不能返回除id列其他的值，所以必须要回表。

      如果建有了id和name列的联合索引，则可以避免回表。

      另外，建立了id和name的联合索引(id列在在前)，则select id from user可以避免回表，而不用单独建立id列的单列索引。

   B:两个单列查询返回行较多，同时查返回行较少，联合索引更高效。

     如果select * from user where id=2 和select * from user where name='tom' 各自返回的行数比较多，而select * from user where id=2 and name='tom'返回的行数比较少，那么这个时候使用联合索引更加高效。

  4.什么时候该用联合索引以及如何设计组合索引更高效                              

   A:等值查询中，查询条件a返回的条目比较多，查询条件b返回的条目比较多，而同时查询a、b返回的条目比较少，那么适合建立联合索引；

   B:对于有等值查询的列和范围查询的列，等值查询的列建在前、范围查询的列建在后比较实用；

   C:如第3点A中的另外说到，如果联合索引列的前置列与索引单列一致，那么单列查询可以用到索引，这样就避免了再建单列索引，因此联合索引的前置列应尽量与单列一致；

  5.联合索引在DML时候的性能分析                                                               

   Insert：索引越多插入明显慢得多，这是因为记录必须与索引同时更新，而要维护索引那种有序排列的结构，就必须把新增的索引键值插入到特定的位置，而不是随机排放，这里就涉及到重                 组数据的动作，如果索引块存不下，如果索引块存不下，则还要涉及到扩展索引块的动作，这都需要很大的开销。

             相对于有序插入，无序插入时索引的影响更加惊人，因为有序插入时，由于插入的数据有一定的顺序，可以在准备工作后快速扩展新块和批量重组，而对于无序操作，批量则不可能。

  Delete：删除影响所有的索引，在海量数据库定位删除少量记录时，这个条件列是索引列显然是必要的，但过多的索引还是会影响明显，因为其他列的索引也要更新。在经常要删除大量记录的               时候，危害加剧。另外，delete删除索引后，索引块中的相关需要删除记录只是被打上了一个删除标志而已，并没有真正删除。

  Update:更新的影响最小，如果是更新整条记录则与delete类似，如果是修改某列时，则不会触及到其他索引列的维护。

  6.使用联合索引需要注意的地方                                                                      

   A:超过3个列的联合索引不合适，否则虽然减少了回表动作，但索引块过多，查询时就要遍历更多的索引块了；

   B:建索引动作应谨慎，因为建索引的过程会产生锁，不是行级锁，而是锁住整个表，任何该表的DML操作都将被阻止，在生产环境中的繁忙时段建索引是一件非常危险的事情；

   C:对于某段时间内，海量数据表有频繁的更新，这时可以先删除索引，插入数据，再重新建立索引来达到高效的目的。