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

问题：
从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条记录。查询结果记录多的原因是结果集数量太大。