This site uses cookies to deliver our services. By using this site, you acknowledge that you have read and understand our Cookie and Privacy policy. Your use of Kontext website is subject to this policy. Allow Cookies and Dismiss

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

603 views 0 comments last modified about 9 years ago Raymond Tang

SQL Server

In this page

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

Related pages

Querying Teradata and SQL Server - Tutorial 1: The SELECT Statement

28937 views   7 comments last modified about 4 years ago

SELECT is one of the most commonly used statements. In this tutorial, I will cover the following items: Two of the principal query clauses—FROM and SELECT Data Types Built-in functions CASE expressions and variations like ISNULL and COALESCE. * The functio...

View detail

Querying Teradata and SQL Server - Tutorial 2 Filtering and Sorting

2902 views   2 comments last modified about 4 years ago

Tutorial –1 For Teradata developers, if you have no SQL Server installed, please go to the following link to download the SQL Server 2014 Expression Edition. ...

View detail

Querying Teradata and SQL Server - Tutorial 0: Preparation

2685 views   0 comments last modified about 4 years ago

In this serial, I will compare Teradata SQL with T-SQL with samples provided. This is mainly prepared for SQL Server DBAs, Developers and other users to help them quickly master the common used SQLs in Teradata platform. Similar to T-SQL, Teradata SQL implements most part of the ANSI SQL with ext...

View detail

[SQL Server 2012] 使用自带功能简化SQL

749 views   0 comments last modified about 6 years ago

在低版本SQL Server中,对于一些常用的查询功能,我们可能需要运用复杂的逻辑,如临时表,游标等等来实现,这样可能会导致代码难维护,同时产生性能问题。本文将简述用自带的功能去简单的实现这些操作。 注意:一些功能仅在SQL Server 2012以及以上版本支持。 1.查询分页 (OFFSET, FETCH) 在很多Web应用程序中,我们需要实现分页功能,在过去,我们往往通过top语句实现或者通过ROW_NUMBER;在2012中加入了FETECH以及OFFSET标准SQL中支持的功能。下面的例子是查询第11到20行数据: ...

View detail

将远程 Sql Server 服务器上的数据库备份到本地的方法

551 views   0 comments last modified about 10 years ago

首先,必须知道数据库服务器的地址(IP),连接用户名和密码、其次,可以按照下面几种方式进行:

View detail

SQL Server 附加没有日志文件ldf的数据库mdf的方法

696 views   0 comments last modified about 9 years ago

Keywords: how to attach database without log file

View detail

Add comment

Please login first to add comments.  Log in New user?  Register

Comments (0)

No comments yet.