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:对于某段时间内，海量数据表有频繁的更新，这时可以先删除索引，插入数据，再重新建立索引来达到高效的目的。
例如在查询名字时
联合索引使用结论:
1):查询条件中出现联合索引第一列,或者全部,则能利用联合索引.
2):条件列中只要条件相连在一起,以本文例子来说就是:
last_name=’1′ and first_name=’1′
与
first_name=’1′ and last_name=’1′
,无论前后,都会利用上联合索引.
3):查询条件中没有出现联合索引的第一列,而出现联合索引的第二列,或者第三列,都不会利用联合索引查询.
单一列索引的应用结论:
1):只要条件列中出现索引列,无论在什么位置,都能利用索引查询.
两者的共同点:
1):要想利用索引,都要符合SARG标准.
2) :都是为了提高查询速度.
3):都需要额外的系统开销,磁盘空间.
补充说明: stmtText信息来产生,在查询语句前面加上:SET STATISTICS PROFILE on.可以通过运行它,来观察你的查询是否合理,这样才能真正做到优化.
本文主旨:讨论什么情况下能利用上索引.
索引:创建索引可以根据查询业务的不同分为两种:单一列的索引,联合索引. 顾名思义,单一列索引就是指在表的某一列上创建索引,联合索引是在多个列上联合创建索引.
优缺点比较:
1):索引所占用空间:单一列索引相对要小.
2):索引创建时间:单一列索引相对短.
3):索引对insert,update,delete的影响程序:单一列索引要相对低.
4):在多条件查询时,联合索引效率要高.
索引的使用范围:单一列索引可以出现在where 条件中的任何位置,而联合索引需要按一定的顺序来写.
本文所用测试软件环境如下:SQL05
DEMO:创建一个人员表,包含人员ID,姓名.在人员ID上创建一个聚集索引,在first_name和last_name上创建一个联合
索引.
create table person (id int, last_name varchar(30), first_name varchar(30))
create unique clustered index person_id on person (id)
create index person_name on person (last_name, first_name)
在上例中,id上创建了聚集索引,下面的查询都会用了聚集索引.
where id=1
where id>1
where id<1
where id between 1 and n
where id like ’1%’
where id in(1,2,3…)
说明: id 列出现在条件中的位置并不一定要求第一列,不受位置影响.
不过下面的查询方式则不会用上聚集索引.
where person_id +1=n
where person_id like ‘%5′
where person_id like ‘%5%’
where person_id abs(15)
联合索引列比起单一列索引最大的好处在于,对于多条件的查询它比起单一列索引更加精确.拿上面的人员表来说吧,如果
要查询一个人的全名,只知道first_name是很难马上找到这个人的全名的,如果知道first_name和last_name则会非常容易找到.
下面根据不同的条件与输出列顺序说明索引的应用.
第一种情况:–条件和输出列和索引列顺序相同
select last_name,first_name from person where last_name=’1′ and first_name=’1′
stmtText
Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),
SEEK:([bdg_web_vaction].[dbo].[person].[last_name]=[@1]
AND [bdg_web_vaction].[dbo].[person].[first_name]=[@2]) ORDERED FORWARD)
结果:利用person_name联合索引查找
第二种情况:–条件列与索引列顺序不同,但输出列相同
select last_name,first_name from person where first_name=’1′ and last_name=’1′
stmtText
Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),
SEEK:([bdg_web_vaction].[dbo].[person].[last_name]=[@2] AND [bdg_web_vaction].
[dbo].[person].[first_name]=[@1]) ORDERED FORWARD)
结果:利用person_name联合索引查找
第三种情况:–条件列与输出列与索引列的顺序都不相同
select first_name,last_name from person where first_name=’1′ and last_name=’1′
Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),
SEEK:([bdg_web_vaction].[dbo].[person].
[last_name]=[@2] AND [bdg_web_vaction].[dbo].[person].[first_name]=[@1]) ORDERED FORWARD)
结果:利用person_name联合索引查找
第四种情况:–条件列在first_name和last_name中间加入另外一个条件
SELECT id, first_name,last_name from person where first_name=’1′ AND id=1 and last_name=’1′
Clustered Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_id]),
SEEK:([bdg_web_vaction].[dbo].[person].[id]=CONVERT_IMPLICIT(int,[@2],0)),
WHERE:([bdg_web_vaction].[dbo].[person].[first_name]=[@1] AND [bdg_web_vaction].[dbo].[person].[las
结果:不能利用person_name联合索引查找
第五种情况:--在输出列中分开first_name和last_name
SELECT first_name,id,last_name from person where first_name='1' and last_name='1'
Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),
SEEK:([bdg_web_vaction].[dbo].[person].
[last_name]=[@2] AND [bdg_web_vaction].[dbo].[person].[first_name]=[@1])
ORDERED FORWARD)
结果:利用person_name联合索引查找
第六种情况:条件列没有出现联合索引的第一列
SELECT first_name,id,last_name from person where first_name=’1′
SELECT first_name,last_name from person where first_name=’1′
SELECT last_name ,first_name from person where first_name=’1′
Index Scan(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),
WHERE:([bdg_web_vaction].[dbo].[person].[first_name]=[@1]))
结果:不能利用person_name联合索引.
第七种情况:–条件列出现联合索引的第一列
SELECT first_name,id,last_name from person where last_name=’1′
SELECT first_name,last_name from person where last_name=’1′
SELECT last_name ,first_name from person where last_name=’1′
Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),
SEEK:([bdg_web_vaction].[dbo].[person].[last_name]=[@1]) ORDERED FORWARD)
结果:利用person_name联合索引查找
联合索引使用总结:
1):查询条件中出现联合索引第一列,或者全部,则能利用联合索引.
2):条件列中只要条件相连在一起,以本文例子来说就是:
last_name=’1′ and first_name=’1′
与
first_name=’1′ and last_name=’1′
,无论前后,都会利用上联合索引.
3):查询条件中没有出现联合索引的第一列,而出现联合索引的第二列,或者第三列,都不会利用联合索引查询.
单一列索引的应用总结:
1):只要条件列中出现索引列,无论在什么位置,都能利用索引查询.
两者的共同点:
1):要想利用索引,都要符合SARG标准.
2) :都是为了提高查询速度.
3):都需要额外的系统开销,磁盘空间.
补充说明: stmtText信息来产生,在查询语句前面加上:SET STATISTICS PROFILE on.可以通过运行它,来观察你的查询是否合理,这样才能真正做到优化.
总结:即使表上创建了索引,但如果查询语句写的不科学的话(不符合SARG标准),也于事无补,要根据表索引情况来优化查询语句,如没有合适的索引可用,则要创建相应索引.