By using this site, you acknowledge that you have read and understand our Cookie policy, Privacy policy and Terms .

我们都知道在一个表中当需要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

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

visibility 59
thumb_up 0
access_time 18 days ago

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

open_in_new View open_in_new Spark + PySpark

local_offer python local_offer SQL Server

visibility 171
thumb_up 0
access_time 2 months ago

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

open_in_new View open_in_new Code snippets

Spark Read from SQL Server Source using Windows/Kerberos Authentication

local_offer pyspark local_offer SQL Server local_offer spark-2-x

visibility 141
thumb_up 0
access_time 3 months ago

In this article, I am going to show you how to use JDBC Kerberos authentication to connect to SQL Server sources in Spark (PySpark). I will use  Kerberos connection with principal names and password directly that requires  ...

open_in_new View open_in_new Spark + PySpark

local_offer SQL Server local_offer hive

visibility 1359
thumb_up 0
access_time 12 months ago

In one of my previous post, I showed how to configure Apache Hive 3.0.0 in Windows 10. Apache Hive 3.0.0 Installation on Windows 10 Step by Step Guide ...

open_in_new View open_in_new Hadoop

info About author

Kontext dark theme mode

Dark theme mode

Dark theme mode is available on Kontext.

Learn more arrow_forward
Kontext Column

Kontext Column

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

Learn more arrow_forward
info Follow us on Twitter to get the latest article updates. Follow us