关于索引的一道经典面试题 - 问题: 从100万条记录中的到 成绩最高的记录 问题分析: 要从一张表中找到成绩最高的记录并不难,有很多种办法,最简单的就是利用TOP 1 select top 1 * from student order by score desc 但是这种方法有一点小问题,就是如果成绩最高的有两个人的话只能查出其中的一个 上一篇:SQL Server 索引中include的魅力(具有包含性列的索引) 下一篇:偶遇with ties http://www.fengfly.com http://www.fengfly.com/html/ERP/xinxianquan/http://www.fengfly.com/html/JAVA/kaifagongju/ANT/ 问题: 从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,1) not null, pid varchar(18) not null, md varchar(11) not 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条记录。查询结果记录多的原因是结果集数量太大。
资源出自网络,为收集整理而来,仅供学习和研究使用。如有侵犯你的版权,请指出,