我們做個(gè)試驗(yàn)創(chuàng)建一個(gè)表，其中主鍵上的聚集索引按照id倒敘排列，然后分別倒敘順序select數(shù)據(jù)，比較select的時(shí)間：

＋測(cè)試代碼

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 條數(shù)據(jù)

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 '插入時(shí)間（毫秒）：';

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 '查詢(xún)時(shí)間(毫秒)'

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 '查詢(xún)時(shí)間(毫秒)'

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

set nocount off;

以下是查詢(xún)時(shí)間結(jié)果

查詢(xún)時(shí)間(毫秒)

393

查詢(xún)時(shí)間(毫秒)

606

按照和索引相同順序從100萬(wàn)條數(shù)據(jù)中取50條時(shí)需要393毫秒，相反順序時(shí)需要606毫秒。造成的性能影響還是挺大的。

結(jié)論：

在建索引時(shí)要考慮常用查詢(xún)的排序方式，在建主鍵時(shí)要特別注意，因?yàn)閟ql server會(huì)自動(dòng)按照升序來(lái)建，這時(shí)候如果您的查詢(xún)多數(shù)用主鍵列倒敘排列，記得要修改一下默認(rèn)的設(shè)置。