MySQL索引以及查詢最佳化的詳細介紹

這篇文章帶給大家的內容是關於MySQL索引以及查詢優化的詳細介紹，有一定的參考價值，有需要的朋友可以參考一下，希望對你有幫助。

文章《MySQL查詢分析》講述了使用MySQL慢查詢和explain指令來定位mysql效能瓶頸的方法，定位出效能瓶頸的sql語句後，則需要對低效的sql語句進行最佳化.本文主要討論MySQL索引原理及常用的sql查詢優化。

一個簡單的比較測試

前面的案例中，c2c_zwdb.t_file_count表只有一個自增id，FFileName欄位未加索引的sql執行情況如下：

在上圖中，type=all，key=null，rows=33777。此sql未使用索引，是一個效率非常低的全表掃描。如果加上聯合查詢和其他一些限制條件，資料庫會瘋狂的消耗內存，並且會影響前端程式的執行。

這時為FFileName欄位新增索引：

alter table c2c_zwdb.t_file_count add index index_title(FFileName);

再次執行上述查詢語句，其比較很明顯：

在該圖中，type=ref，key=索引名（index_title），rows=1。該sql使用了索引index_title，且是一個常數掃描，根據索引只掃描了一行。

比起未加索引的情況，加上了索引後，查詢效率比較非常明顯。

MySQL索引

透過上面的比較測試可以看出，索引是快速搜尋的關鍵。 MySQL索引的建立對於MySQL的高效運作是很重要的。對於少量的數據，沒有合適的索引影響不是很大，但是，當隨著數據量的增加，效能會急劇下降。如果對多列進行索引(組合索引)，列的順序非常重要，MySQL只能對索引最左邊的前綴進行有效的尋找。

下面介紹幾個常見的MySQL索引類型。

索引分單列索引和組合索引。單列索引，即一個索引只包含單一資料列，一個表格可以有多個單列索引，但這不是組合索引。組合索引，即一個索引包含多個欄位。

1、MySQL索引類型

(1) 主鍵索引PRIMARY KEY

它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時建立主鍵索引。

當然也可以用 ALTER 指令。記住：一個表只能有一個主鍵。

(2) 唯一索引 UNIQUE

唯一索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。可以在建立表格的時候指定，也可以修改表格結構，如：

ALTER TABLE table_name ADD UNIQUE (column)

#(3) 普通索引INDEX

這是最基本的索引，它沒有任何限制。可以在建立表格的時候指定，也可以修改表格結構，如：

ALTER TABLE table_name ADD INDEX index_name (column)

(4) 組合索引INDEX

組合索引，即一個索引包含多個欄位。可以在建立表格的時候指定，也可以修改表格結構，如：

ALTER TABLE table_name ADD INDEX index_name(column1, column2 , column3)

(5) 全文索引FULLTEXT

全文索引（也稱為全文檢索）是目前搜尋引擎使用的關鍵技術。它能夠利用分詞技術等多種演算法智慧分析出文字文字中關鍵字詞的頻率及重要性，然後依照一定的演算法規則聰明地篩選出我們想要的搜尋結果。

可以在建立表格的時候指定，也可以修改表格結構，如：

ALTER TABLE table_name ADD FULLTEXT (column)

2、索引結構及原理

mysql普遍使用B Tree做索引，但在實作上又根據叢集索引和非叢集索引而不同，本文暫不討論這點。

b 樹介紹

下面這張b 樹的圖片在很多地方可以看到，之所以在這裡也選取這張，是因為覺得這張圖片可以很好的詮釋索引的查找過程。

如上圖，是一顆b 樹。淺藍色的區塊我們稱之為一個磁碟區塊，可以看到每個磁碟區塊包含幾個資料項（深藍色所示）和指標（黃色所示），如磁碟區塊1包含資料項17和35，包含指標P1、P2、P3，P1表示小於17的磁碟區塊，P2表示在17和35之間的磁碟區塊，P3表示大於35的磁碟區塊。

真實的資料存在於葉子節點，分別為3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節點不儲存真實的數據，只儲存指引搜尋方向的數據項，如17、35並不真實存在於數據表中。

查找過程

在上圖中，如果要查找資料項29，那麼首先會把磁碟區塊1由磁碟載入到內存，此時發生一次IO ，在記憶體中用二分查找確定29在17和35之間，鎖定磁碟塊1的P2指針，內存時間因為非常短（相比磁碟的IO）可以忽略不計，通過磁碟塊1的P2指針的磁碟地址把磁碟區塊3由磁碟載入到內存，發生第二次IO，29在26和30之間，鎖定磁碟區塊3的P2指針，透過指針載入磁碟區塊8到內存，發生第三次IO，同時記憶體中做二分找找29，結束查詢，總計三次IO。真實的情況是，3層的b 樹可以表示上百萬的數據，如果上百萬的數據查找只需要三次IO，性能提高將是巨大的，如果沒有索引，每個數據項都要發生一次IO ，那麼總共需要百萬次的IO，顯然成本非常非常高。

性質

(1) 索引欄位要盡量的小。

透過上面b 樹的查找過程，或是透過真實的資料存在於葉子節點這個事實可知，IO次數取決於b 數的高度h。

假設目前資料表的資料量為N，每個磁碟區塊的資料項目的數量是m，則樹高h=㏒(m 1)N，當資料量N一定的情況下，m越大，h越小；

而m = 磁碟區塊的大小/資料項的大小，磁碟區塊的大小也就是一個資料頁的大小，是固定的；如果資料項佔的空間越小，資料項的數量m越多，樹的高度h越低。這就是為什麼每個資料項，也就是索引欄位要盡量的小，例如int佔4字節，要比bigint8位元組少一半。

(2) 索引的最左邊匹配特性。

當b 樹的資料項目是複合的資料結構，例如(name,age,sex)的時候，b 數是按照從左到右的順序來建立搜尋樹的，例如當(張三,20,F)這樣的資料來檢索的時候，b 樹會優先比較name來決定下一步的所搜方向，如果name相同再依序比較age和sex，最後得到檢索的資料；但當(20,F )這樣的沒有name的資料來的時候，b 樹就不知道下一步該查哪個節點，因為建立搜尋樹的時候name就是第一個比較因子，必須先根據name來搜尋才能知道下一步要去哪裡查詢。例如當(張三,F)這樣的資料來檢索時，b 樹可以用name來指定搜尋方向，但下一個欄位age的缺失，所以只能把名字等於張三的資料都找到，然後再匹配性別是F的數據了， 這個是非常重要的性質，即索引的最左匹配特性。

建立索引的幾大原則

(1) 最左前綴匹配原則

對於多列索引，總是從索引的最前面字段開始，接著往後，中間不能跳過。例如創建了多列索引(name,age,sex)，會先匹配name字段，再匹配age字段，再匹配sex字段的，中間不能跳過。 mysql會一直向右匹配直到遇到範圍查詢(>、

一般，在建立多列索引時，where子句中使用最頻繁的一列放在最左邊。

看一個補符合最左前綴匹配原則和符合該原則的對比範例。

實例：表c2c_db.t_credit_detail建有索引(Flistid,Fbank_listid)

#不符合最左前綴匹配原則的sql語句：

select * from t_credit_detail where Fbank_listid='201108010000199'G

該sql直接用了第二個索引字段Fbank_listid，跳過跳過了第一個索引欄位Flistid，不符合最左前綴匹配原則。用explain指令查看sql語句的執行計劃，如下圖：

從上圖可以看出，該sql未使用索引，是一個低效的全表掃描。

符合最左前綴匹配原則的sql語句：

select * from t_credit_detail where Flistid='2000000608201108010831508721' and Fbank_listid='201108010831508721' and Fbank_listid='20110801000199'G

該sql先使用了索引的第一個欄位Flistid，再使用索引的第二個欄位Fbank_listid，中間沒有跳過，符合最左字首匹配原則。用explain指令查看sql語句的執行計劃，如下圖：

#從上圖可以看出，該sql使用了索引，僅掃描了一行。

對比可知，符合最左前綴匹配原則的sql語句比不符合該原則的sql語句效率有極大提高，從全表掃描上升到了常數掃描。

(2) 盡量選擇區分度高的欄位作為索引。

例如，我們會選擇學號做索引，而不會選擇性別來做索引。

(3) =和in可以亂序

例如a = 1 and b = 2 and c = 3，建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以辨識的形式。

(4) 索引列不能參與計算，保持列「乾淨」

例如：Flistid 1>‘2000000608201108010831508721‘。原因很簡單，假如索引列參與計算的話，那每次檢索時，都會先將索引計算一次，再做比較，顯然成本太大。

(5) 盡量的擴充索引，不要新建索引。

例如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可。

索引的不足

雖然索引可以提高查詢效率，但索引也有自己的不足之處。

索引的額外開銷：

(1) 空間：索引需要佔用空間；

(2) 時間：查詢索引需要時間；

( 3) 維護：索引須要維護（資料變更時）；

不建議使用索引的情況：

(1) 資料量很小的表格

(2)空間緊張

常用優化總結

優化語句很多，需要注意的也很多，針對平時的情況總結一下幾點：

1、有索引但未被用到的情況（不建議）

(1) Like的參數以通配符開頭時

盡量避免Like的參數以通配符開頭，否則資料庫引擎會放棄使用索引而進行全表掃描。

以萬用字元開頭的sql語句，例如：select * from t_credit_detail where Flistid like '%0'G

這是全表掃描，沒有使用到索引，不建議使用。

不以通配符開頭的sql語句，例如：select * from t_credit_detail where Flistid like '2%'G

很明顯，這使用到了索引，是有範圍的查找了，比以通配符開頭的sql語句效率提高不少。

(2) where條件不符合最左前綴原則時

例子已在最左前綴匹配原則的內容中有舉例。

(3) 使用！ = 或 運算子時

盡量避免使用！ = 或 操作符，否則資料庫引擎會放棄使用索引而進行全表掃描。使用>或

select * from t_credit_detail where Flistid != '2000000608201108010831508721'G

#(4) 索引列參與計算

(4) 索引列參與計算計算# #應盡量避免在where 子句中對欄位進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。 select * from t_credit_detail where Flistid 1 > '2000000608201108010831508722'G

(5) 判斷欄位值#null值#null值

#應盡量避免在where子句中對欄位進行null值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如： 低效：select * from t_credit_detail where Flistid is null ;

可以在Flistid上設定預設值0，確保表中Flistid欄位沒有null值，然後這樣查詢： 高效：select * from t_credit_detail where Flistid =0;

#(6) 使用or來連接條件# #####應盡量避免在where子句中使用or來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如： 低效：select * from t_credit_detail where Flistid = '200000608201108010831508721' 或 Flistid = '10000200001';######可以用下面這樣的查詢代替上面的or 查詢： 高效：select ###from t_credit_detail where Flistid = '2000000608201108010831508721' union all select### from t_credit_detail where Flistid = '10000200001' ;###

2、避免select *

在解析的過程中，會將'*' 依序轉換成所有的列名，這個工作是透過查詢資料字典完成的，這意味著將耗費更多的時間。

所以，應該養成一個需要什麼就取什麼的好習慣。

3、order by 語句最佳化

任何在Order by語句的非索引項或有計算表達式都會降低查詢速度。

方法：1.重寫order by語句以使用索引；

  2.为所使用的列建立另外一个索引

  3.绝对避免在order by子句中使用表达式。

4、GROUP BY語句優化

提高GROUP BY 語句的效率, 可以透過將不需要的記錄在GROUP BY 之前過濾掉

低效率:

SELECT JOB , AVG(SAL)

FROM EMP

#GROUP by JOB

#HAVING JOB = 'PRESIDENT'

OR JOB = 'MANAGER'

高效：

SELECT JOB , AVG(SAL)

FROM EMP

WHERE JOB = 'PRESIDENT'

OR JOB = 'MANAGER'

GROUP by JOB

5、用exists 取代in

很多時候用exists 代替in 是一個好的選擇： select num from a where num in(select num from b) 用下面的語句替換： select num from a where exists(select 1 from b where num=a.num)

6、使用varchar/nvarchar 代替char/nchar

盡可能的使用varchar/nvarchar 代替char/nchar ，因為首先變長字段存儲空間小，可以節省存儲空間，其次對於查詢來說，在一個相對較小的欄位內搜尋效率顯然要高些。

7、能用DISTINCT的就不用GROUP BY

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

可改為：

## SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

8、能用UNION ALL就不要用UNION

UNION ALL不執行SELECT DISTINCT函數，這樣就會減少很多不必要的資源。

9、在Join表的時候使用相當類型的例，並將其索引

如果應用程式有很多JOIN 查詢，你應該確認兩個表中Join的欄位是被建過索引的。這樣，MySQL內部就會啟動為你最佳化Join的SQL語句的機制。

而且，這些被用來Join的字段，應該是相同的類型的。例如：如果你要把 DECIMAL 欄位和一個 INT 欄位Join在一起，MySQL就無法使用它們的索引。對於那些STRING類型，還需要有相同的字元集才行。 （兩個表的字元集有可能不一樣）

這篇文章到這裡就全部結束了，關於MySQL的更多知識大家可以關注php中文網的MySQL教程欄目！ ！ ！

以上是MySQL索引以及查詢最佳化的詳細介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！