數據庫查詢性能優化之利器—索引（二）

在前面一篇文章中談到適當地建立索引能夠大幅度地提升SQL語句查詢速度，然而并不是在什么情況下都適合建立索引，下面來談一下什么情況下適合建立索引、建立什么樣的索引以及建立索引帶來的好處和壞處。

一.索引的概念

　　廣義的索引是指：將具有檢索意義的事項按照一定方式排列，以方便進行檢索。數據庫中的索引是指：將數據庫表中的一列或者多列按照一定的方式進行組織以方便對數據庫表中的內容進行查詢。

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

　　數據庫中的索引跟字典的目錄類似。數據庫文件中的內容是存儲在磁盤上的，當在數據庫中查找記錄時，如果能獲得該記錄在磁盤上存儲的位置，就能迅速找到該記錄，就跟查找漢字一樣，否則的話就只有對數據庫表中的所有內容進行掃描，直到找到符合條件的記錄為止，這樣顯然會消費很多的時間。因此適當地創建索引能夠加快查詢速度。比如，我有一張表

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

　　里面有若干條數據：

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

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

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

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

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

?二.索引的分類

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

　　根據索引包含的列的多少分為多列索引和單列索引，在創建索引的時候，可以選擇在某一列上創建索引，也可以選擇在多個列上創建索引。

?　? 還有一種叫做唯一索引，表示此索引的每一個索引項對應一個唯一的數據記錄。

　　根據聚簇索引和非聚簇索引的概念可以知道，在一張表上只會有一個聚簇索引，因為一張表中的記錄的物理順序規則只有一種，而可以有多個非聚簇索引。

三.索引的創建

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

　　創建索引的SQL語句格式為：

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

　　在創建索引時，unique和cluster|noncluster以及asc|desc是可選的，當沒有指定索引為唯一索引時，默認為非唯一索引；沒有指定索引為聚簇索引還是非聚簇索引時，則默認為非聚簇索引；沒有指定索引值的排序方式時，默認為asc升序。

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

　　當需要返回某個范圍內的值的時候，選擇創建聚簇索引是比較合適的，就跟拼音查找類似，可以找到讀音為"chen"的若干個漢字。

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

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

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

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

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

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