建索引(尤其是主键)时请注意索引列顺序

7/4/2008来源:SQL技巧人气:6662

在创建主键或其它索引时,SQL Server总是自动将字段的顺序设置为升序排列;升序是默认设置,是为了保持与 SQL Server 早期版本的兼容性。建索引时索引列的顺序应该按照常用查询中的排序方式排序。

我们做个试验创建一个表,其中主键上的聚集索引按照id倒叙排列,然后分别倒叙顺序select数据,比较select的时间:

+测试代码

if object_id('test_indexorder','U') is not null

begin

truncate table test_indexorder

drop table test_indexorder

end

go

create table test_indexorder

(

id int identity(1,1) not null,

name varchar(20) not null,

content varchar(50) not null,

co1 varchar(50),

co2 varchar(50),

co3 varchar(50),

co4 varchar(50),

co5 varchar(50),

constraint pk_testorder PRimary key clustered(

id desc

)

)

go

--insert 1000000 条数据

set nocount on;

declare @t datetime;

set @t = getdate();

DECLARE @cn int;

set @cn = 1000000;

while(@cn > 0)

begin

insert into test_indexorder(name,content,co1,co2,co3,co4,co5)

VALUES(

'name' + cast(@cn as varchar(10)),

cast(newid() as varchar(50)),

cast(newid() as varchar(50)),

cast(newid() as varchar(50)),

cast(newid() as varchar(50)),

cast(newid() as varchar(50)),

cast(newid() as varchar(50)));

set @cn = @cn -1;

end

print '插入时间(毫秒):';

print datediff(millisecond,@t,getdate());

set nocount off;

GO

checkpoint

dbcc freeproccache

dbcc dropcleanbuffers

GO

go

set nocount on;

declare @t datetime;

set @t = getdate();

with t_rn as (

select *,rn = ROW_NUMBER() OVER (ORDER BY id desc) FROM test_indexorder

)

SELECT id,name,content,co1,co2,co3,co4,co5 from t_rn WHERE rn between 19007 and 19057;

print '查询时间(毫秒)'

print datediff(millisecond,@t,getdate())

set @t = getdate();

with t_rn as (

select *,rn = ROW_NUMBER() OVER (ORDER BY id asc) FROM test_indexorder

)

SELECT id,name,content,co1,co2,co3,co4,co5 from t_rn WHERE rn between 17007 and 17057;

print '查询时间(毫秒)'

print datediff(millisecond,@t,getdate())

set nocount off;

以下是查询时间结果

查询时间(毫秒)

393

查询时间(毫秒)

606

按照和索引相同顺序从100万条数据中取50条时需要393毫秒,相反顺序时需要606毫秒。造成的性能影响还是挺大的。

结论:

在建索引时要考虑常用查询的排序方式,在建主键时要特别注意,因为sql server会自动按照升序来建,这时候如果您的查询多数用主键列倒叙排列,记得要修改一下默认的设置。