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

关于索引,可以参考我的这篇文章:http://hi.baidu.com/1987raymond/blog/item/a0f7b11062e1330a213f2e26.html

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

步骤一,建立一个测试表,并且插入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

info Last modified by Raymond at 6 years ago * This page is subject to Site terms.

More from Kontext

Azure SQL Database Automated Backup Strategy

local_offer Azure local_offer SQL Server

visibility 84
thumb_up 0
access_time 3 months ago

When designing the architecture of Kontext platform, Azure SQL Database is chosen as the storage for relational data. TDE and other advanced security features are always enabled to protect the database. Backup plans are also employed to ensure I can always restore the database for as point of tim...

open_in_new Azure

local_offer Java local_offer python local_offer SQL Server

visibility 1489
thumb_up 0
access_time 3 months ago

In my previous article  Connect to SQL Server via JayDeBeApi in Python , I showed examples of u...

open_in_new Python Programming

local_offer pyspark local_offer spark-2-x local_offer teradata local_offer SQL Server

visibility 1330
thumb_up 0
access_time 4 months ago

In my previous article about  Connect to SQL Server in Spark (PySpark) , I mentioned the ways t...

open_in_new Spark + PySpark

local_offer python local_offer SQL Server

visibility 1252
thumb_up 0
access_time 5 months ago

Python JayDeBeApi module allows you to connect from Python to databases using Java JDBC drivers.

open_in_new Code snippets

info About author

comment Comments (0)

comment Add comment

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

No comments yet.

Dark theme mode

Dark theme mode is available on Kontext.

Learn more arrow_forward

Kontext Column

Created for everyone to publish data, programming and cloud related articles. Follow three steps to create your columns.


Learn more arrow_forward