SQL Server 执行计划分析
数据准备:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 -- 创建数据库 2 CREATE TABLE [dbo].[T_table]( 3 [ID] [uniqueidentifier] NOT NULL, 4 [CODE] [int] NOT NULL, 5 [DESC] [varchar](100) NOT NULL 6 ) ON [PRIMARY] 7 8 -- 添加20000条数据 9 Declare @Loop Int 10 Declare @ID UniqueIdentifier 11 Declare @Desc Varchar (100) 12 13 Set @Loop = 1 14 Set @Desc = '' 15 16 WHILE @Loop <= 200000 17 BEGIN 18 Set @ID = NewID() 19 Set @Desc = ' PerformanceIssue - ' + Convert( Varchar(10),@Loop ) 20 Insert Into T_table Values (@ID, @Loop, @Desc) 21 Set @Loop = @Loop + 1 22 END
对比分析表的扫描方式:
1. 查询全部记录:
set statistics profile on; select * from T_table where code < 200
执行计划如下(采用 Table Scan, 系统开销 2.00069 ):
2. 创建索引:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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)