數(shù)據(jù)庫查詢性能優(yōu)化之利器—索引（二）

在前面一篇文章中談到適當(dāng)?shù)亟⑺饕軌虼蠓鹊靥嵘齋QL語句查詢速度，然而并不是在什么情況下都適合建立索引，下面來談一下什么情況下適合建立索引、建立什么樣的索引以及建立索引帶來的好處和壞處。

一.索引的概念

　　廣義的索引是指：將具有檢索意義的事項(xiàng)按照一定方式排列，以方便進(jìn)行檢索。數(shù)據(jù)庫中的索引是指：將數(shù)據(jù)庫表中的一列或者多列按照一定的方式進(jìn)行組織以方便對(duì)數(shù)據(jù)庫表中的內(nèi)容進(jìn)行查詢。

　　字典是廣義的索引最好的例子，比如我們?cè)谧值渲胁檎?陳"字，有兩種查找方式：拼音查找和偏旁查找。拼音查找的前提是知道這個(gè)字的大概讀音是"chen"，然后去目錄的拼音頁列表查找到拼音為"chen"的漢字所在的頁碼，比如找到拼音為"chen"的漢字所在的起始頁碼為100，然后就會(huì)直接把字典翻到100頁，如果該漢字恰好在100頁，則查找到該漢字了，如果不在100頁，那么繼續(xù)往后面翻幾頁就會(huì)找到該漢字了。進(jìn)行偏旁查找不需要知道該漢字的讀音，只需要先在目錄的偏旁列表中找到" 阝"對(duì)應(yīng)的頁碼比如說12，然后去12頁找到"陳"字所在的具體頁碼比如說101，最后只需要直接把字典翻到101頁就可以看到"陳"字了。試想一下如果字典沒有這個(gè)目錄，要想在字典中找到某個(gè)漢字的話是很難的，最直接的辦法可能就是從字典的正文第一頁逐頁查找直到找到該漢字為止，這個(gè)過程所耗費(fèi)的時(shí)間是可想而知的，可能找一上午都找不到要找的漢字。

　　數(shù)據(jù)庫中的索引跟字典的目錄類似。數(shù)據(jù)庫文件中的內(nèi)容是存儲(chǔ)在磁盤上的，當(dāng)在數(shù)據(jù)庫中查找記錄時(shí)，如果能獲得該記錄在磁盤上存儲(chǔ)的位置，就能迅速找到該記錄，就跟查找漢字一樣，否則的話就只有對(duì)數(shù)據(jù)庫表中的所有內(nèi)容進(jìn)行掃描，直到找到符合條件的記錄為止，這樣顯然會(huì)消費(fèi)很多的時(shí)間。因此適當(dāng)?shù)貏?chuàng)建索引能夠加快查詢速度。比如，我有一張表

　　peple( id integer(PK), name varchar(40) , age tinyint , tel varchar(20)?)

　　里面有若干條數(shù)據(jù)：

1?Tom?20?85443452?Mark?28?67893533?Jim?18?139456734564?Jack?20?86754565?Jemyy?12?6789456

　　然后執(zhí)行語句 select * from people where name='Jemyy';

　　會(huì)對(duì)所有的數(shù)據(jù)記錄進(jìn)行掃描逐一進(jìn)行條件匹配，直到找到符合條件的記錄。如果在name列上建立了索引，與字典的目錄進(jìn)行類比，假如把name列按照字母升序進(jìn)行排列，然后可能就得到一個(gè)目錄：

Jack?第四條記錄的存儲(chǔ)位置
Jemyy?第五條記錄的存儲(chǔ)位置
Jim?第三條記錄的存儲(chǔ)位置
Mark?第二條記錄的存儲(chǔ)位置
Tom?第一條記錄的存儲(chǔ)位置

　　查找的時(shí)候先找到Jemyy得到該記錄的存儲(chǔ)位置，然后根據(jù)存儲(chǔ)位置獲取該記錄的內(nèi)容。上面過程只是類比字典的查找對(duì)索引的一個(gè)理解，實(shí)際當(dāng)中SQL利用索引進(jìn)行查找的過程可能跟上面略有不同（沒有研究過實(shí)際的SQL利用索引進(jìn)行查找的過程，這個(gè)估計(jì)得需要讀SQL引擎的源碼），但是原理是類似的。

?二.索引的分類

　　根據(jù)索引項(xiàng)與表中記錄的物理順序是否一致索引可以分為聚簇索引和非聚簇索引。索引項(xiàng)的順序與表中記錄的物理順序一致的索引稱作為聚簇索引，不一致的索引則稱為非聚簇索引。比如字典的拼音查找目錄就是聚簇索引，而偏旁查找目錄則是非聚簇索引。

　　根據(jù)索引包含的列的多少分為多列索引和單列索引，在創(chuàng)建索引的時(shí)候，可以選擇在某一列上創(chuàng)建索引，也可以選擇在多個(gè)列上創(chuàng)建索引。

?　? 還有一種叫做唯一索引，表示此索引的每一個(gè)索引項(xiàng)對(duì)應(yīng)一個(gè)唯一的數(shù)據(jù)記錄。

　　根據(jù)聚簇索引和非聚簇索引的概念可以知道，在一張表上只會(huì)有一個(gè)聚簇索引，因?yàn)橐粡埍碇械挠涗浀奈锢眄樞蛞?guī)則只有一種，而可以有多個(gè)非聚簇索引。

三.索引的創(chuàng)建

　　在創(chuàng)建索引之前，要考慮好是否適合在某些列上創(chuàng)建索引，創(chuàng)建聚簇索引還是非聚簇索引，是創(chuàng)建單列索引還是多列索引，亦或是唯一索引。

　　創(chuàng)建索引的SQL語句格式為：

create?[unique][cluster|noncluster]?index?indexname?on?tablename([column?asc|desc,column?asc|desc..)

　　在創(chuàng)建索引時(shí)，unique和cluster|noncluster以及asc|desc是可選的，當(dāng)沒有指定索引為唯一索引時(shí)，默認(rèn)為非唯一索引；沒有指定索引為聚簇索引還是非聚簇索引時(shí)，則默認(rèn)為非聚簇索引；沒有指定索引值的排序方式時(shí)，默認(rèn)為asc升序。

　　一般情況下來說，在經(jīng)常需要進(jìn)行搜索、進(jìn)行外連接以及排序的列上比較適合建立索引，而很少使用到或者需要經(jīng)常被修改的列上則不適合建立索引。建立索引雖然能夠加快查詢速度，但是同時(shí)也為數(shù)據(jù)庫的維護(hù)帶來了不便。由于要對(duì)索引進(jìn)行存儲(chǔ)，所以建立索引帶來了額外的空間消耗；并且建立索引對(duì)數(shù)據(jù)庫的修改造成了很大的不便，當(dāng)要往數(shù)據(jù)庫里插入數(shù)據(jù)或者修改數(shù)據(jù)時(shí)，索引也會(huì)隨之自動(dòng)進(jìn)行修改，這個(gè)時(shí)候會(huì)帶來很大的時(shí)間消耗。因此如果在一開始沒考慮好，比如在需要經(jīng)常修改的列上建立索引，后期的維護(hù)是很麻煩的。

　　當(dāng)需要返回某個(gè)范圍內(nèi)的值的時(shí)候，選擇創(chuàng)建聚簇索引是比較合適的，就跟拼音查找類似，可以找到讀音為"chen"的若干個(gè)漢字。

　　而對(duì)于多列索引和單列索引的選擇則一般需要根據(jù)where子句的判斷條件來選擇。比如：

select?*?from?peple?where?name='jack'

　　此時(shí)則適合在name列上建立單列索引，而

select?*?from?peple?where?name='jack'?and?age>20

　　此時(shí)則適合在name和age列上創(chuàng)建多列索引，這樣更能夠加快查詢速度。索引的使用具有最左前綴匹配原則，當(dāng)判別條件中存在索引的引導(dǎo)列時(shí)會(huì)使用該索引。假設(shè)people表有四個(gè)索引nameindex(name)，ageindex(age)，telindex(tel)，mutilindex(name,age,tel)。

　　select * from peple where name='jack' 會(huì)選擇使用nameindex索引，而select * from peple where name='jack' and age>20則會(huì)選擇使用mutilindex，注意一次查詢只能使用上面4個(gè)索引中的一個(gè)索引。對(duì)于mutilindex，若判別條件為(name),(name,age),(name,age,tel),(name,tel)等都可以使用該索引，而(age,tel),(tel)都不能夠使用該做引。