如何知道SQL SERVER索引的使用情况

如何知道SQL SERVER索引的使用情况

如何提高查询语句的效率？一个有效的手段是创建索引。那么怎么知道你创建的索引的使用情况呢？showplan_text是个不错的办法。下面贴点代码说一说吧。

 

 1CREATE TABLE [dbo].[MyTable](
 2    [ID] [int] IDENTITY(1,1) NOT NULL,
 3    [Column1] [int] NOT NULL,
 4    [Column2] [int] NOT NULL,
 5    [Column3] [int] NOT NULL,
 6 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
 7(
 8    [ID] ASC
 9)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
10) ON [PRIMARY]
11go
12CREATE NONCLUSTERED INDEX [IX_MyTable] ON [dbo].[MyTable] 
13(
14    [Column1] ASC,
15    [Column2] ASC,
16    [Column3] ASC
17)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
18
19go
20
21set showplan_text on
22go
23
24select * from MyTable
25where
26    Column1=1 and Column2=1 and Column3=1
27go
28
29set showplan_text off
30go
31
32drop table MyTable
33go

 

 表上所建的索引为

Column1 ASC

Column2 ASC

Column3 ASC

1.where 语句条件同索引

select * from MyTable where Column1=1 and Column2=1 and Column3=1

结果为

|--Index Seek(OBJECT:([Test].[dbo].[MyTable].[IX_MyTable]), SEEK:([Test].[dbo].[MyTable].[Column1]=CONVERT_IMPLICIT(int,[@1],0) AND [Test].[dbo].[MyTable].[Column2]=CONVERT_IMPLICIT(int,[@2],0) AND [Test].[dbo].[MyTable].[Column3]=CONVERT_IMPLICIT(int,[@3],0)) ORDERED FORWARD)

没有问题，索引完全发挥了作用。

2.where 语句条件与索引顺序不同

select * from MyTable where Column3=1 and Column2=1 and Column1=1

  |--Index Seek(OBJECT:([Test].[dbo].[MyTable].[IX_MyTable]), SEEK:([Test].[dbo].[MyTable].[Column1]=CONVERT_IMPLICIT(int,[@2],0) AND [Test].[dbo].[MyTable].[Column2]=CONVERT_IMPLICIT(int,[@3],0) AND [Test].[dbo].[MyTable].[Column3]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

实际执行经过优化，此时索引最佳。

3.where 语句条件比索引列少

select * from MyTable where Column1=1 and Column2=1

 |--Index Seek(OBJECT:([Test].[dbo].[MyTable].[IX_MyTable]), SEEK:([Test].[dbo].[MyTable].[Column1]=CONVERT_IMPLICIT(int,[@1],0) AND [Test].[dbo].[MyTable].[Column2]=CONVERT_IMPLICIT(int,[@2],0)) ORDERED FORWARD)

索引也同样发挥了作用。

4.where 语句条件比索引列少，起始列相同

select * from MyTable where Column1=1 and Column3=1

|--Index Seek(OBJECT:([Test].[dbo].[MyTable].[IX_MyTable]), SEEK:([Test].[dbo].[MyTable].[Column1]=CONVERT_IMPLICIT(int,[@1],0)),  WHERE:([Test].[dbo].[MyTable].[Column3]=CONVERT_IMPLICIT(int,[@2],0)) ORDERED FORWARD)

5.where 语句与索引顺序不一致，缺少第一列

select * from MyTable where Column3=1 and Column2=1

  |--Index Scan(OBJECT:([Test].[dbo].[MyTable].[IX_MyTable]),  WHERE:([Test].[dbo].[MyTable].[Column3]=CONVERT_IMPLICIT(int,[@1],0) AND [Test].[dbo].[MyTable].[Column2]=CONVERT_IMPLICIT(int,[@2],0))) 

Index Scan，而不是Index Seek，后者性能优于前者。

6.where 语句与索引顺序不一致，有第一列

select * from MyTable where Column3=1 and Column1=1

  |--Index Seek(OBJECT:([Test].[dbo].[MyTable].[IX_MyTable]), SEEK:([Test].[dbo].[MyTable].[Column1]=CONVERT_IMPLICIT(int,[@2],0)),  WHERE:([Test].[dbo].[MyTable].[Column3]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

结论：

与索引列完全一致的where条件（and连接，使用=），此时的sql语句最为高效。

实际执行的查询经过优化，最大限度地使用索引。

有索引第一列时，将是Index Seek，否则（有索引其他列）是Index Scan。

showplan_text显示的信息我还得再学习学习，如果谁能提供点资料真是感激不尽。欢迎指点！