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

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

防偽碼：勤勞一日，可得一夜安眠；勤勞一生，可得幸福長眠

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

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

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

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

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按哪個索引進行數(shù)據(jù)查詢，但一般不需要人工指定，SQL Server將會根據(jù)所創(chuàng)建的索引，自動優(yōu)化查詢。其實，使用索引可加快數(shù)據(jù)檢索速度，但為每個列都建立檢索沒有必要。因為檢索自身也需要維護，并占用一定資源。

案例二：驗證索引的作用

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

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

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

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

如下圖所示：

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

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

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

5、打開“sql server profiler ”工具進行跟蹤，如下圖所示：

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

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

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

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

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

注意：選擇benet數(shù)據(jù)庫中的學生表，然后點擊“開始分析”

索引類型為clusterd（聚集索引），索引列為“學號”。

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

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

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

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

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

首先我們先來了解一下索引的分類以及選擇索引列的注意事項：

我們掌握了學術(shù)性的理論后，將進行詳細的試驗操作來進一步鞏固：

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

目前tstudent表中沒有任何索引也沒有主鍵

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

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

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

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

為成績表創(chuàng)建組合索引，因為一個學生不能為一門學科錄入兩次成績，所以將成績表中的studentID和subjectID創(chuàng)建組合索引

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

創(chuàng)建一個表TS

create TABLE TS(

StudentID varchar(10)NOT NULL,

Sname varchar(10)DEFAULT NULL,

sex char(2)DEFAULT NULL,

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

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)建唯一性約束的時候就會創(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代表非聚集索引

二、視圖

在這里，一些舉例試驗就不再一一演示了，因為，在我看來，作為一名數(shù)據(jù)庫管理員，必須要掌握數(shù)據(jù)庫優(yōu)化這項技能。

最好掌握一些基本的通用語法，雖說視圖是個變量，隨時更新變化，用起來很方便簡潔，可直接在其基礎(chǔ)上直接

執(zhí)行：

例如

select * from 視圖名

where 條件=xxx

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

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