您的位置:知识库 » 数据库

关于索引的一道经典面试题

作者: Hawkon  来源: 博客园  发布时间: 2010-03-30 11:17  阅读: 3842 次  推荐: 1   原文链接   [收藏]  

问题:
从100万条记录中的到 成绩最高的记录

问题分析:
要从一张表中找到成绩最高的记录并不难,有很多种办法,最简单的就是利用TOP 1

select top 1 * from student order by score desc

 

但是这种方法有一点小问题,就是如果成绩最高的有两个人的话只能查出其中的一个。

对上面的方法进行改进:

select top 1 with ties * from student order by score desc

 

或用子查询的方式:

select * from student where score =(
select max(score) from student)

select * from student where score = (select top 1 score from student order by score desc)


=================成功的分割线======================
但是这个题目成功与否不在于能否查询出成绩最高的记录,而在于用最快的速度查询出来。经过测试从100万条记录中找到成绩最高的记录如果使用第一个方法需要1秒多,
下面是测试的代码:
我这里创建的表是person,查询的是年龄最大的

代码
create table person
(
id 
int identity(1,1not null,
pid 
varchar(18not null,
md 
varchar(11not null,
age 
int
)
go

declare @pid varchar(15)
declare @age int
declare @mb varchar(11)
declare @count int
set @count = 0
--插入100万条随机的记录
while(@count < 1000000)
begin
    
--生成随机的PID
    select @pid=substring(cast(rand() as varchar(20)),3,6)+
        
substring(cast(rand() as varchar(20)),3,6)+substring(cast(rand() as varchar(20)),3,6)
    
--生成随机的MB
    select @mb=substring(cast(rand() as varchar(20)),3,6)+
        
substring(cast(rand() as varchar(20)),3,5)
    
--生成随机的AGE
    select @age = cast (rand() * 100 as int)
    
--将生成的随机数据插入表
    insert into person
        
values ( @pid@mb,@age)

    
set @count = @count + 1
end 

 


以上插入的语句需要执行20分钟以上,请耐心等待
完成后用下面的测试看执行的效率

代码

DECLARE @BD DATETIME
DECLARE @ED DATETIME
SET @BD = GETDATE()--执行之前记录时间
SELECT TOP 1 * FROM PERSON  ORDER BY AGE DESC
SET @ED = GETDATE()--执行之后记录时间
SELECT datediff(millisecond,@BD,@ED)--用毫秒的方法显示执行时间。

 


我的电脑执行的时候是在1100-1500之间
如果是用子查询的方式会更慢。大约在5000毫秒左右。

解决办法:
为person表的age这一列创建索引

 create nonclustered index ix_age
on person(age)

 

公平起见执行一下清空缓存的语句: (感谢 huyg的提醒)

DBCC FREEPROCCACHE --清空SQL缓存
DBCC DROPCLEANBUFFERS

 

创建之后同样运行上面的测试速度的语句,看到的毫秒数是266。
Oh Yeah!效率提高了无数倍。
使用子查询的方式再试试看:

DECLARE @BD DATETIME
DECLARE @ED DATETIME
SET @BD = GETDATE()

select * from person where age =(
select max(age) from person )
 
SET @ED = GETDATE()
SELECT datediff(millisecond,@BD,@ED)

 

我执行的时间是4186毫秒,效率略有提升。

但是这样查询出的结果是10192条记录。查询结果记录多的原因是结果集数量太大。

1
0
标签:索引

数据库热门文章

    数据库最新文章

      最新新闻

        热门新闻