数据库索引:联合索引基本知识

来源:互联网 发布:windows系统电视优缺点 编辑:程序博客网 时间:2024/05/16 09:46

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.联合索引的好处

  1. 避免回表
    在执行计划中,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列的单列索引。

  2. 两个单列查询返回行较多,同时查返回行较少,联合索引更高效。
    如果select * from user where id=2 和select * from user where name=’tom’ 各自返回的行数比较多,而select * from user where id=2 and name=’tom’返回的行数比较少,那么这个时候使用联合索引更加高效。


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

  1. 等值查询中,查询条件a返回的条目比较多,查询条件b返回的条目比较多,而同时查询a、b返回的条目比较少,那么适合建立联合索引;

  2. 对于有等值查询的列和范围查询的列,等值查询的列建在前、范围查询的列建在后比较实用;

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


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

  • Insert:索引越多插入明显慢得多,这是因为记录必须与索引同时更新,而要维护索引那种有序排列的结构,就必须把新增的索引键值插入到特定的位置,而不是随机排放,这里就涉及到重 组数据的动作,如果索引块存不下,如果索引块存不下,则还要涉及到扩展索引块的动作,这都需要很大的开销。
    相对于有序插入,无序插入时索引的影响更加惊人,因为有序插入时,由于插入的数据有一定的顺序,可以在准备工作后快速扩展新块和批量重组,而对于无序操作,批量则不可能。

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

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


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

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

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

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


转载:http://www.cnblogs.com/gudi/p/4058411.html

0 0
原创粉丝点击