SQL Server 执行计划分析

数据准备:

-- 创建数据库
CREATE TABLE [dbo].[T_table](
    [ID] [uniqueidentifier] NOT NULL,
    [CODE] [int] NOT NULL,
    [DESC] [varchar](100) NOT NULL
) ON [PRIMARY]

-- 添加20000条数据 
Declare @Loop Int
Declare @ID UniqueIdentifier
Declare @Desc Varchar (100)

Set @Loop = 1
Set @Desc = ''

WHILE @Loop <= 200000
BEGIN
   Set @ID = NewID()
   Set @Desc = ' PerformanceIssue - ' + Convert( Varchar(10),@Loop )
   Insert Into T_table Values (@ID, @Loop, @Desc)
   Set @Loop = @Loop + 1
END

对比分析表的扫描方式:

1. 查询全部记录:   

set statistics profile on;
select * from T_table where code < 200

执行计划如下(采用 Table Scan, 系统开销 2.00069 ):

2. 创建索引:

if (exists (select * from sys.indexes where name = 'UNC_ID'))
    drop index T_table.UNC_ID
go
create index UNC_ID
on
T_table(ID);

*执行查询:  select * from T_table where code < 200 结果如下( 没有使用索引字段, 结果同上)

*执行查询:  select * from T_table where ID = '76A70F2B-7893-49B6-A489-D3B99C202B47' (使用索引字段过滤, 先 Index Seek, 再Lookup)

*执行查询:  select ID from T_table where ID = '76A70F2B-7893-49B6-A489-D3B99C202B47' ( 只查询索引字段, 执行Index Seek)

*执行查询:  select ID from T_table ( 如果没有where条件, 执行 Index Scan)