關於索引的一道經典面試題(體現效率所在))

問題：
從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)  

公平起見執行一下清空緩衝的語句：

 

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) 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條記錄。查詢結果記錄多的原因是結果集數量太大。