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


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


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)


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

 

 

 

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页