數(shù)據(jù)庫(kù)優(yōu)化工程師必看 第一部分（索引、視圖）

數(shù)據(jù)庫(kù)優(yōu)化工程師必看 第一部分（索引、視圖）

防偽碼：勤勞一日，可得一夜安眠；勤勞一生，可得幸福長(zhǎng)眠

       在本章技術(shù)詳解之前，先分享一個(gè)今天晚上和一個(gè)做IT的學(xué)弟的溝通總結(jié)：

我們從八點(diǎn)多聊到十點(diǎn) ，他主要做Linux高級(jí)運(yùn)維，大部分時(shí)間還是做數(shù)據(jù)庫(kù)的一些工作， 按理說(shuō)還是我的小師弟 。哈哈。 他說(shuō)， 剛畢業(yè)從一家外資高新技術(shù)企業(yè)跳糟到一家國(guó)企，工資漲了小2000，現(xiàn)在稅后也是8500。 但是，他非常后悔。我說(shuō)以前的公司該是多么強(qiáng)大，讓你寧可每個(gè)月少掙好幾千也想回到以前的公司， 他說(shuō)老表，工資不是衡量一個(gè)人價(jià)值的唯一標(biāo)準(zhǔn)， 給你舉例說(shuō)明你就知道這個(gè)公司有多厲害了 ，這家公司叫青牛(北京)技術(shù)有限公司，主要做融合網(wǎng)絡(luò)的 最重要的是大多數(shù)的員工，自主的要求無(wú)償加班，回憶起我以前剛畢業(yè)在那家軟件公司上班的情景，總之效率特別高，例如經(jīng)理下達(dá)一個(gè)命令到技術(shù)部，要求中午下班之前完成，六七個(gè)部門的員工幫我一起完成，而我現(xiàn)在在xx國(guó)企，想做個(gè)備份，不知道秘鑰號(hào)碼，問(wèn)經(jīng)理、測(cè)試工程師、問(wèn)遍了，哎， 等我知道了秘鑰號(hào)，本來(lái)上午十點(diǎn)可以做完的事情。 結(jié)果，下午兩點(diǎn)才剛剛開(kāi)始 ，如果給我一次重新選擇的機(jī)會(huì)，我一定選擇擁有強(qiáng)大戰(zhàn)斗力和文化底蘊(yùn)的公司，我在青牛仿佛看到了未來(lái)的第二個(gè)阿里巴巴集團(tuán)。我其實(shí)總結(jié)一下 就是：眼光決定格局 選擇決定未來(lái) 用我恩師的話說(shuō)：公司文化的力量很大程度上決定他能走多遠(yuǎn)飛多高！

   現(xiàn)在是凌晨0:54分，我們正式開(kāi)始索引和視圖的詳解，希望就像博客昵稱“一盞燭光“那樣，去幫助更多的人解決實(shí)際問(wèn)題，謝謝各位的支持。我將持續(xù)更新更多原創(chuàng)技術(shù)文檔。

實(shí)驗(yàn)案例一：創(chuàng)建數(shù)據(jù)庫(kù)并使用索引查詢學(xué)生考試成績(jī)(多種表格在T-SQL查詢語(yǔ)句 第二部分 此處略)

select Student.StudentName,Subject.SubjectName,Result.ExamDate,Result.StudentResult

from Subject,Student,Result  with(INDEX=aaa)  注：INDEX=aaa，即索引=索引名

where Result.SubjectId=Subject.SubjectId and Result.StudentNo=Student.StudentNo and Result.StudentResult between 80 and 90

注：INDEX=aaa，即索引=索引名。雖然可以指定SQL Server按哪個(gè)索引進(jìn)行數(shù)據(jù)查詢，但一般不需要人工指定，SQL Server將會(huì)根據(jù)所創(chuàng)建的索引，自動(dòng)優(yōu)化查詢。其實(shí)，使用索引可加快數(shù)據(jù)檢索速度，但為每個(gè)列都建立檢索沒(méi)有必要。因?yàn)闄z索自身也需要維護(hù)，并占用一定資源。

案例二：驗(yàn)證索引的作用

1、 首先創(chuàng)建一個(gè)數(shù)據(jù)量大的表，名稱為“學(xué)生表”，分別有三列，學(xué)號(hào)，姓名和班級(jí)，如下圖所示，學(xué)號(hào)為自動(dòng)編號(hào)，班級(jí)為默認(rèn)值“一班”。

2、 向表中插入大量數(shù)據(jù)，數(shù)據(jù)越多，驗(yàn)證索引的效果越好。

使用語(yǔ)句完成：While 1>0  Insert into 學(xué)生表(姓名)  values(‘楊文’)

上面語(yǔ)句是一個(gè)死循環(huán)，除非強(qiáng)制結(jié)束，如果1大于0就會(huì)一直向表中插入姓名

如下圖所示：

3、等待5分鐘左右，打開(kāi)表的屬性，查看表的行數(shù)1030550，當(dāng)前為如下圖所示：

我們可以右擊，選擇前1000行，效果如下：

4、使用語(yǔ)句查詢第900000行的數(shù)據(jù)，Select * from 學(xué)生表 Where 學(xué)號(hào)=900000

5、打開(kāi)“sql server profiler ”工具進(jìn)行跟蹤，如下圖所示：

打開(kāi)“sql server profiler ”工具查看跟蹤的信息，發(fā)現(xiàn)查詢時(shí)間很長(zhǎng)，cpu工作了359毫秒，reads：讀了8630次，writes：寫(xiě)了9次，duration：總計(jì)花費(fèi)649毫秒完成查詢。

6、為了下面分析文件更準(zhǔn)確，多執(zhí)行幾次Select * from 學(xué)生表 Where 學(xué)號(hào)=900000

然后把跟蹤的結(jié)果保存在桌面上：

注：這里選擇第一項(xiàng) ，跟蹤文件。然后保存至桌面，效果如下：

7、 打開(kāi)“數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)”，添加跟蹤文件，進(jìn)行分析，發(fā)現(xiàn)索引建議，需要建立索引。

注意：選擇benet數(shù)據(jù)庫(kù)中的學(xué)生表，然后點(diǎn)擊“開(kāi)始分析”

索引類型為clusterd（聚集索引），索引列為“學(xué)號(hào)”。

8、 按照“數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)”的索引建議建立聚集索引，并且選擇“唯一”

9、 再次執(zhí)行Select * from 學(xué)生表Where 學(xué)號(hào)=900000

10、 打開(kāi)sql server profiler查看跟蹤的時(shí)間，發(fā)現(xiàn)查詢時(shí)間大幅提升，說(shuō)明索引可以提高查詢速度。

發(fā)現(xiàn)總計(jì)時(shí)間為1毫秒，幾乎忽略不計(jì)，以至于幾乎不花時(shí)間立即查詢

案例四：分別練習(xí)創(chuàng)建各種索引

首先我們先來(lái)了解一下索引的分類以及選擇索引列的注意事項(xiàng)：

我們掌握了學(xué)術(shù)性的理論后，將進(jìn)行詳細(xì)的試驗(yàn)操作來(lái)進(jìn)一步鞏固：

1、 創(chuàng)建聚集索引

目前tstudent表中沒(méi)有任何索引也沒(méi)有主鍵

為tstudent表創(chuàng)建聚集索引

選中studentID,單擊左上側(cè)的主鍵按鈕

為Tstuden表的studentID創(chuàng)建主鍵就同時(shí)創(chuàng)建了聚集索引

2、創(chuàng)建組合索引

為成績(jī)表創(chuàng)建組合索引，因?yàn)橐粋€(gè)學(xué)生不能為一門學(xué)科錄入兩次成績(jī)，所以將成績(jī)表中的studentID和subjectID創(chuàng)建組合索引

3、用命令創(chuàng)建聚集索引

創(chuàng)建一個(gè)表TS

create TABLE TS(

StudentID varchar(10)NOT NULL,

Sname varchar(10)DEFAULT NULL,

sex char(2)DEFAULT NULL,

cardID varchar(20)DEFAULT NULL,     注意：實(shí)際工作中建議從簡(jiǎn)從快，保證質(zhì)量，這些語(yǔ)法可拓展練習(xí)

Birthday datetime DEFAULT NULL,

Email varchar(40)DEFAULT NULL,

Class varchar(20)DEFAULT NULL,

enterTime datetime DEFAULTNULL

)

Go

用命令創(chuàng)建聚集索引

create clustered index CL_studentID

on TS(studentID)

創(chuàng)建聚集索引不一定創(chuàng)建主鍵，如下圖所示：

4、創(chuàng)建唯一索引

創(chuàng)建唯一性約束的時(shí)候就會(huì)創(chuàng)建唯一性索引，不能有重復(fù)值

為Tstudent表創(chuàng)建唯一非聚集索引

create unique nonclustered index U_cardID on TStudent(cardID)

5、創(chuàng)建非聚集索引---可以有重復(fù)值

為Tstudent表的姓名列創(chuàng)建非聚集索引

使用命令查看表上的索引

Select * from sys.sysindexes where id=(select object_id from sys.all_objects where

name='Tstudent')

Indid中1代表聚集索引

Indid中2代表唯一非聚集索引

Indidz中3代表非聚集索引

二、視圖

在這里，一些舉例試驗(yàn)就不再一一演示了，因?yàn)椋谖铱磥?lái)，作為一名數(shù)據(jù)庫(kù)管理員，必須要掌握數(shù)據(jù)庫(kù)優(yōu)化這項(xiàng)技能。

最好掌握一些基本的通用語(yǔ)法，雖說(shuō)視圖是個(gè)變量，隨時(shí)更新變化，用起來(lái)很方便簡(jiǎn)潔，可直接在其基礎(chǔ)上直接

執(zhí)行：

例如

select * from 視圖名

where 條件=xxx

很方便，但是視圖畢竟有局限性，在性能和修改限制方面有待提高。

本文出自 “一盞燭光” 博客，轉(zhuǎn)載請(qǐng)與作者聯(lián)系！