MySQL索引理解和應用

索引是什麼

索引是一種特殊的檔案(InnoDB資料表上的索引是表空間的一個組成部分)，它們包含著對資料表裡所有記錄的引用指標。索引的目的在於提高查詢效率，對相關列使用索引是提高SELECT操作效能的最佳途徑。在生活中也有非常多的索引案例，比如我們書籍的目錄，目錄裡包含了章節和頁碼的對映關係，有了目錄我們就可以方便快速的定位到我們想要閱讀的章節。

索引的種類

MySQL索引主要包含普通索引、唯一索引、主鍵索引、外來鍵索引、複合索引和全文索引這6大種類。

普通索引

普通索引(由關鍵字KEY或INDEX定義的索引)的唯一任務是加快對資料的訪問速度。因此，應該只為那些最經常出現在查詢條件(WHERE column = …)或排序條件(ORDER BY column)中的資料列建立索引。只要有可能，就應該選擇一個資料最整齊、最緊湊的資料列(如一個整數型別的資料列)來建立索引。

唯一索引

普通索引允許被索引的資料列包含重複的值。如果能確定某個資料列將只包含彼此各不相同的值，在為這個資料列建立索引的時候就應該用關鍵字UNIQUE把它定義為一個唯一索引。這麼做的好處在於：一是簡化了MySQL對這個索引的管理工作，這個索引也因此而變得更有效率；二是MySQL會在有新記錄插入資料表時，自動檢查新記錄的這個欄位的值是否已經在某個記錄的這個欄位裡出現過了；如果是，MySQL將拒絕插入那條新記錄。也就是說，唯一索引可以保證資料記錄的唯一性。

主鍵索引

必須為主鍵欄位建立一個索引，這個索引就是所謂的”主鍵索引”，不允許有空值。主索引與唯一索引的唯一區別是：前者在定義時使用的關鍵字是PRIMARY而不是UNIQUE。一個表只能有一個主鍵。

外來鍵索引

如果為某個外來鍵欄位定義了一個外來鍵約束條件，MySQL就會定義一個內部索引來幫助自己以最有效率的方式去管理和使用外來鍵約束條件。

複合索引

索引可以覆蓋多個資料列，如像INDEX(columnA, columnB)索引。這種索引的特點是MySQL可以有選擇地使用一個這樣的索引。如果查詢操作只需要用到columnA資料列上的一個索引，就可以使用複合索引INDEX(columnA, columnB)。不過，這種用法僅適用於在複合索引中排列在前的資料列組合。比如說，INDEX(A, B, C)可以當做A或(A, B)的索引來使用，但不能當做B、C或(B, C)的索引來使用。

全文索引

文字欄位上的普通索引只能加快對出現在欄位內容最前面的字串(也就是欄位內容開頭的字元)進行檢索操作。如果欄位裡存放的是由幾個、甚至是多個單詞構成的較大段文字，普通索引就沒什麼作用了。這類場合正是全文索引(full-text index)可以大顯身手的地方。在生成這種型別的索引時，MySQL將把在文字中出現的所有單詞建立為一份清單，查詢操作將根據這份清單去檢索有關的資料記錄。

索引的優點

• 大大加快資料的檢索速度，這也是建立索引的最主要的原因。
• 在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。
• 建立唯一性索引，保證資料庫表中每一行資料的唯一性。
• 加速表和表之間的連線，特別是在實現資料的參考完整性方面特別有意義。
• 透過使用索引，可以在查詢的過程中使用隱藏最佳化器，提高系統的效能。

索引的缺點

• 索引可以加快資料檢索操作，但會使資料修改操作變慢。每修改資料記錄，索引就必須重新整理一次。為了在某種程式上彌補這一缺陷，許多SQL命令都有一個DELAY_KEY_WRITE項。

• 索引還會在硬碟上佔用相當大的空間。因此應該只為最經常查詢和最經常排序的資料列建立索引。

建索引應該遵循的原則

從上面的索引分類和索引優缺點我們可以很明顯的看出，索引能夠為我們帶來很多好處，但這個好處建立的前提是規範的索引，因此這裡提煉一些實踐當中非常常用的索引規約。

儘量設定欄位的預設值為非null

只要列中包含有NULL值都將不會被包含在索引中，複合索引中只要有一列含有NULL值，那麼這一列對於此複合索引就是無效的。所以我們在資料庫設計時不要讓欄位的預設值為NULL。

使用短索引

對串列進行索引，如果可能應該指定一個字首長度。例如，如果有一個CHAR(255)的列，如果在前10個或20個字元內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作。更為重要的是，對於較短的鍵值，索引快取記憶體中的塊能容納更多的鍵值，因此，MySQL也可以在記憶體中容納更多的值。這增加了找到行而不用讀取索引中較多塊的可能性。

牢記最左字首匹配原則

在建立一個 n 列的索引時，實際是建立了MySQL可利用的n個索引。多列索引可以起到建立多個索引的作用，因為可利用索引中最左邊的列集來匹配行。這樣的列集稱為最左字首。mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。

選擇區分度高的列作為索引

區分度的公式是count(distinct col)/count(*)，表示欄位不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別欄位可能在大資料面前區分度就是0。

索引列不要參與計算

如果在索引列上進行運算查詢，這將導致索引失效而進行全表掃描，這也就失去了建立索引的意義了。同理NOT IN、<>、LIKE查詢操作也是無法應用到索引的，應該儘量避免使用, LIKE操作比較特殊，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。