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

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

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

一.索引的概念

　　廣義的索引是指：将具有檢索意義的事項按照一定方式排列，以友善進行檢索。資料庫中的索引是指：将資料庫表中的一列或者多列按照一定的方式進行組織以友善對資料庫表中的内容進行查詢。

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

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

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

　　裡面有若幹條資料：

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

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

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

 二.索引的分類

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

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

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

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

三.索引的建立

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

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

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

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

　　當需要傳回某個範圍内的值的時候，選擇建立聚簇索引是比較合适的，就跟拼音查找類似，可以找到讀音為"chen"的若幹個漢字。

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

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

　　此時則适合在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)都不能夠使用該做引。

本文轉載自海 子部落格園部落格，原文連結：http://www.cnblogs.com/dolphin0520/archive/2012/09/03/2659755.html如需轉載自行聯系原作者