复合索引的创建与使用示例

回答问题时写的，这是针对复合索引的使用场景写的测试代码

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

我的测试环境和测试结果只能代表部分情景，但索引的使用是全部适用的

*/

以下是执行计划

StmtText

------------------------------------------------

SELECT * FROM TB

WHERE COL2='B' AND COL4='D'

StmtText

---------------------------------------------------------------------------------------------------------------------

  |--Table Scan(OBJECT:([TEST].[dbo].[TB]), WHERE:([TEST].[dbo].[TB].[COL2]=[@1] AND [TEST].[dbo].[TB].[COL4]=[@2]))

这是只创建了第一个索引，在COL1到COL5上顺序创建的，这样的索引，对于条件中包含COL1的查询可以用到，按顺序包含的越多，效果越好。如果是跨索引键的查询，如COL1和COL3，只会用到COL1。

StmtText

------------------------------------------------

SELECT * FROM TB

WHERE COL2='B' AND COL4='D'

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))

       |--Index Seek(OBJECT:([TEST].[dbo].[TB].[INX2]), SEEK:([TEST].[dbo].[TB].[COL2]='B'),  WHERE:([TEST].[dbo].[TB].[COL4]='D') ORDERED FORWARD)

       |--RID Lookup(OBJECT:([TEST].[dbo].[TB]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

这是创建了第二个索引，也就是COL2,COL3,COL4上的索引，这时会用到索引查找，但如上面所说，只会用到COL2上的索引。注意看计划中的SEEK:([TEST].[dbo].[TB].[COL2]='B')，这表示查找时只用到了COL2的条件，后面的WHERE:([TEST].[dbo].[TB].[COL4]='D')表示在索引查找到的小结果集中再进行COL4的筛选。

StmtText

-------------------------------------------------

SELECT ID FROM TB

WHERE COL2='B' AND COL4='D'

StmtText

-------------------------------------------------------------------------------------------------------------------------------------------

  |--Index Seek(OBJECT:([TEST].[dbo].[TB].[INX3]), SEEK:([TEST].[dbo].[TB].[COL2]=[@1] AND [TEST].[dbo].[TB].[COL4]=[@2]) ORDERED FORWARD)

这是最后的索引，查找时用到了全部条件，也没有再去扫描表，可以有效减少对表的阻塞。