SQL Server中的联合主键、聚集索引、非聚集索引

我们都知道在一个表中当需要2列以上才能确定记录的唯一性的时候，就需要用到联合主键，当建立联合主键以后，在查询数据的时候性能就会有很大的提升，不过并不是对联合主键的任何列单独查询的时候性能都会提升，但我们依然可以通过对联合主键中的首列除外的其他列建立非聚集索引来提高性能。

本文将对联合主键、聚集索引、非聚集索引对查询性能的影响举例说明。

步骤一，建立一个测试表，并且插入350万条以上的数据。

/*创建测试数据表*/
create table MyTestTable
(
id varchar(10)not null,
parent varchar(40) not null,
addtime datetime default(getdate()),
intcolumn int default(10),
bitcolumn bit default(1)
)
go
/*添加万条随机字符串测试数据耗时分钟*/
declare @count int=3557643
declare @i int =0
declare @id varchar(10),@parent varchar(40)
while(@i<@count)
begin
select @id=left(newid(),10)
if(@i % 20=0)
begin
select @parent=left(newid(),40)
end
insert MyTestTable(id,parent) values(@id,@parent)
select @i=@i+1
end
go

步骤二，不建立任何索引查询测试

/*未建立索引时的查询*/
declare @beginTime datetime =getdate()
declare @elapsedSecond int =0
select * from MyTestTable where parent='DD7D9F34-3A9C-43CA-836B-F2BABD78CE70' and id='103ACE5C-7'
select @elapsedSecond=DATEDIFF(MICROSECOND,@beginTime,GETDATE())
print '未建立索引时查找数据消耗微秒数'
print @elapsedSecond
select @beginTime=GETDATE()
select * from MyTestTable where parent='F535C18F-BD48-4D45-88DF-9653BB9B422D'
select @elapsedSecond=DATEDIFF(MICROSECOND,@beginTime,GETDATE())
print '未建立索引时查找第二列数据消耗微秒数'
print @elapsedSecond

执行结果如下：

--(1 row(s) affected)
--未建立索引时查找数据消耗微秒数
--530000
--(20 row(s) affected)
--未建立索引时查找第二列数据消耗微秒数
--500000

从执行结果我们可以看出，当没有索引的时候，SQL Server会遍历整个表，因此需要很长的时间。

步骤三，建立联合主键（会自动创建聚集索引）并查询测试

go
/*建立联合主键*/
alter table MyTestTable add constraint PK_id_parent primary key(id asc,parent asc)
/*建立索引后的查询*/
declare @beginTime datetime =getdate()
declare @elapsedSecond int =0
select * from MyTestTable where parent='DD7D9F34-3A9C-43CA-836B-F2BABD78CE70' and id='103ACE5C-7'
select @elapsedSecond=DATEDIFF(MICROSECOND,@beginTime,GETDATE())
print '建立索引时查找数据消耗微秒数'
print @elapsedSecond
select @beginTime=GETDATE()
select * from MyTestTable where parent='F535C18F-BD48-4D45-88DF-9653BB9B422D'
select @elapsedSecond=DATEDIFF(MICROSECOND,@beginTime,GETDATE())
print '建立索引后查找第二列数据消耗微秒数'
print @elapsedSecond
go

执行结果如下：

--(1 row(s) affected)
--建立索引时查找数据消耗微秒数
--0
--(20 row(s) affected)
--建立索引后查找第二列数据消耗微秒数
--500000

从上面看出，建立联合主键后，查询第一列或者同时查询两列（and关系）速度会非常的快，小于1微妙，但查询联合主键的第二列的时候却特别的慢，因为无法通过索引查询。

步骤四，给联合主键的第二列建立非聚集索引，并且测试

go
/*给第二列创建非聚集索引*/
create index index_parent on MyTestTable(parent asc)
declare @beginTime datetime =getdate()
declare @elapsedSecond int =0
select * from MyTestTable where parent='DD7D9F34-3A9C-43CA-836B-F2BABD78CE70' and id='103ACE5C-7'
select @elapsedSecond=DATEDIFF(MICROSECOND,@beginTime,GETDATE())
print '为第二列建立索引时查找数据消耗微秒数'
print @elapsedSecond
select @beginTime=GETDATE()
select * from MyTestTable where parent='9A75DC47-DDF7-4922-9179-E87B91FE3921'
select @elapsedSecond=DATEDIFF(MICROSECOND,@beginTime,GETDATE())
print '为第二列建立索引后查找第二列数据消耗微秒数'
print @elapsedSecond

执行结果如下：

--(1 row(s) affected)
--为第二列建立索引时查找数据消耗微秒数
--0
--(20 row(s) affected)
--为第二列建立索引后查找第二列数据消耗微秒数
--0

从执行结果可以看出，建立索引后，查询第二列的速度也非常的快了。

总结

一般情况下，对于一个表T，联合主键（A,B)，下列情况的查询时，SQL Server 可以从索引中查询，速度较快：

select * from T where A=Value and B=Value
select * from T where  B=Value and A=Value 
select * from T where A=Value

下面的查询不会经过索引，速度会比较的慢

select * from T where A=Value or B=Value
select * from T where B=Value