复合索引疑问

Question

经常会对多列进行不确定查询
如表test(col1,col2,col3,col4,....col20)
可能会用到这些查询
select * from test where col1='值1'
select * from test where col1='值1' and col2='值2'
select * from test where col1='值1' and col2='值2' and col3='值3'
select * from test where col3='值3' and col4='值4'
select * from test where col4='值4' and col5='值5'
select * from test where col5='值5'
假设经常对这5列查询
现在疑问是
1：分别对5列建立索引还是建立一个包括5列的复合索引 依据是什么
2：复合索引对条件的位置有影响吗 如：index(col1,col2,col3)
  查询语句
  select * from test where col1='值1' and col2='值2' and col3='值3'
  与
  select * from test where col3='值3' and col2='值2' and col1='值1'
 有区别吗
希望大家不必要贴一些没有依据的资料
谢谢

Answer 1

COL1,COL2,COL3上建复合索引
COL3,COL4上建复合索引
COL4,COL5上建复合索引
COL5上建索引

Answer 2

如果记录数没有达到恐怖的千万级或亿级,对col1+col2+col3+col4+col5建立一个索引即可.

Answer 3

复合索引中的次序很重要，常用的的字段应该放前面，尽量保持where中字段的次序与复合索引的顺序一致 

Answer 4

不可以的，COL2单独的查询就用不到索引。
要么对所有可能的组合都加上索引，要么就是5列单独加索引。

Answer 5

SET STATISTICS IO OFF
GO
SET SHOWPLAN_TEXT OFF
GO
SET NOCOUNT ON
GO
IF OBJECT_ID('TB') IS NOT NULL
DROP TABLE TB
GO
CREATE TABLE TB(
ID INT IDENTITY(1,1)
,COL1 VARCHAR(50)
,COL2 VARCHAR(50)
,COL3 VARCHAR(50)
,COL4 VARCHAR(50)
,COL5 VARCHAR(50)
)
INSERT INTO TB
SELECT TOP 10000 NEWID(),NEWID(),NEWID(),NEWID(),NEWID()
FROM MASTER..SPT_VALUES T1,MASTER..SPT_VALUES T2
--为方便演示，加入一列已知列
INSERT INTO TB
SELECT 'A','B','C','D','E'
GO
--最不合适的索引，只能用到索引扫描或表扫描
CREATE INDEX INX1 ON TB(COL1,COL2,COL3,COL4,COL5)
GO
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM TB
WHERE COL2='B' AND COL4='D'
GO
SET SHOWPLAN_TEXT OFF
GO
--可用的索引，能用到索引查找
CREATE INDEX INX2 ON TB(COL2,COL3,COL4)
GO
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM TB
WHERE COL2='B' AND COL4='D'
GO
SET SHOWPLAN_TEXT OFF
GO
--最合适的索引和最合适的查询，只查询需要的列，索引键为查询条件，包含所有需要的列
CREATE INDEX INX3 ON TB(COL2,COL4) INCLUDE(ID)
GO
SET SHOWPLAN_TEXT ON
GO
SELECT ID FROM TB
WHERE COL2='B' AND COL4='D'
GO
SET SHOWPLAN_TEXT OFF
GO
/*
运行
SET STATISTICS IO ON
后
选中语句运行，查看相应IO结果再运行
SET STATISTICS IO OFF我的测试环境和测试结果只能代表部分情景，但索引的使用是全部适用的
*/

Answer 6

#1.SQL Server Profiler->New Trace...
#2.根据访问记录,对常用的查询建立索引

Answer 7

修正下，节约点COL1,COL2,COL3上建复合索引
COL3,COL4上建复合索引
COL5，COL4上建复合索引

Answer 8

关于问题：
1：分别对5列建立索引还是建立一个包括5列的复合索引 依据是什么
依据是查询2：复合索引对条件的位置有影响吗 如：index(col1,col2,col3)
  查询语句
  select * from test where col1='值1' and col2='值2' and col3='值3'
  与
  select * from test where col3='值3' and col2='值2' and col1='值1'
 有区别吗条件的位置没有区别
需要注意索引的位置是有区别的，如index(col1,col2,col3)可以覆盖
where col1='值1' and col2='值2' and col3='值3'
where col1='值1' and col2='值2' 
where col1='值1'
不能覆盖
where col2='值2' and col3='值3'
where col2='值2' 
where col3='值3'

Answer 9

其中
COL3,COL4上建复合索引
还是
COL4，COL3上建复合索引
需要考虑其他情况，比如COL4、COL3的重复率比较，COL4、COL3的字段长度等