哭..我以為我很懂MySQL索引

mysql影片教學專欄介紹真實的索引.

#相關免費學習推薦：mysql影片教學

一、什麼是索引？

  在關聯式資料庫中，索引是一種單獨的、物理的對資料庫表中一列或多列的值進行排序的一種儲存結構，它是某個表中一列或若干列值的集合和對應的指向表中物理標識這些值的資料頁的邏輯指標清單。索引的作用相當於圖書的目錄，可以依照目錄中的頁碼快速找到所需的內容。

  當表中有大量記錄時，若要對錶進行查詢，第一種搜索資訊方式是全表搜索，是將所有記錄一一取出，和查詢條件進行一一對比，然後返回滿足條件的記錄，這樣做會消耗大量資料庫系統時間，並造成大量磁碟I/O操作；第二種就是在表中建立索引，然後在索引中找到符合查詢條件的索引值，最後透過儲存在索引中的ROWID（相當於頁碼）快速找到表中對應的記錄。

  MySQL5.5以後InnoDB儲引擎所使用的索引資料結構主要用：B Tree；這篇文章帶大家以B Tree前世今生為主線來聊一聊；

**Mark**

：

B Tree可以對，>=，BETWEEN，IN ，以及不以通配符開始的LIKE使用索引。 （MySQL5.5後）

  這些事實或許會顛覆你的一些認知，例如在你讀過的其他文章或書中。以上這些都屬於“範圍查詢”，都是不走索引的！

  沒錯，早先5.5以前優化器是不會選擇透過索引搜尋的，優化器認為這樣取出的行多與全表掃描的行，因為還要回表查一次嘛，可能會涉及I/O的行數更多，被優化器放棄。

  經過演算法（B Tree）優化後，支援對部分範圍類型的掃描（得利與B Tree資料結構的有序性）。此做法同時也違反了最左前綴原則，導致範圍查詢後的條件無法用到聯合索引，我們在後面詳細說明。

二、索引的優缺點

1、優點

1. #索引大大減少了伺服器需要掃描的資料量
2. 索引可以幫助伺服器避免排序和臨時表
3. 索引可以將隨機I/O變成順序I/O

#2、缺點

1. 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存索引檔。
2. 建立索引會佔用磁碟空間的索引檔案。一般情況這個問題不算嚴重，但如果你在一個大表上建立了多種組合索引，且伴隨大量資料量插入，索引檔案大小也會快速膨脹。
3. 如果某個資料列包含許多重複的內容，為它建立索引就沒有太大的實際效果。
4. 對於非常小的表，大部分情況下簡單的全表掃描更有效率；

#  因此應該只為最經常查詢和最經常排序的資料列建立索引。 （MySQL裡同一個資料表裡的索引總數限制為16個）

  資料庫存在的意義之一就是是解決資料儲存和快速尋找的。那麼資料庫的資料存在哪？沒錯，是磁碟，磁碟的優點是啥？便宜！缺點呢？相比記憶體存取速度慢。

  那你知道MySQL索引主要使用的資料結構麼？

  B 樹！你脫口而出。

  那 B 樹 是什麼樣的資料結構？ MySQL索引又是為什麼選擇了B 樹呢？

  其實最終選用B 樹是經歷了漫長的演化：

二元排序樹 → 二元平衡樹 → B-Tree（B樹） → B Tree（B 樹）

#  有小夥伴問我“B樹跟B-樹有什麼區別” ？這裡普及一下，MySQL資料結構只有B-Tree（B樹）和B Tree（B 樹），多只是讀法不同罷了，「B-Tree」 一般統稱為B樹，你叫他B-樹也行~ ~

  還有小夥伴提到的紅黑樹，是程式語言中的儲存結構，不是MySQL的；如Java的HashMap就是用的鍊錶加紅黑樹。

  好了，今天就帶著大家一起看一下演化成 B 樹 的過程吧。

三、B Tree索引的前世今生

1、二元排序樹

  理解B 樹之前，簡單說一下二元排序樹，對於一個節點，它的左子樹的孩子節點值都要小於它本身，它的右子樹的孩子節點值都要大於它本身，如果所有節點都滿足這個條件，那麼它就是二元排序樹。 （此處可以串列二分查找的知識點）

上圖是一顆二元排序樹，你可以嘗試利用它的特點，體驗查找9的過程：

• 9比10小，去它的左子樹（節點3）找
• 9比3大，去節點3的右子樹（節點4）找
• 9比4大，去節點4的右子樹（節點9）查找
• 節點9與9相等，查找成功

一共比較了4次，那你有沒有想過上述結構的最佳化方式？

2、AVL樹(自平衡二元尋找樹)

上圖是AVL樹，節點個數與值皆與二元排序樹一摸一樣

再來看一下找9的過程：

• 9比4大，去它的右子樹查找
• 9比10小，去它的左子樹查找
• 節點9與9相等，查找成功

  一共比較了3次，同樣的資料量比二元排序樹少了一次，為什麼呢？因為AVL樹高度要比二元排序樹小，高度越高意味著比較的次數越多；不要小看優化的這一次，假如是200w條數據，比較次數會明顯地不同。

  你可以想像一棵 100 萬節點的平衡二元樹，樹高 20。一次查詢可能需要存取 20 個資料區塊。在機械硬碟時代，從磁碟隨機讀取一個資料區塊需要 10 ms 左右的尋址時間。也就是說，對於一個 100 萬行的表，如果使用二元樹來存儲，單獨訪問一個行可能需要 20 個 10 ms 的時間，這個查詢可真夠慢的！

3、B樹（Balanced Tree）多路平衡查找樹多叉的

B樹是一種多路自平衡搜尋樹，它類似普通的二元樹，但B書允許每個節點有更多的子節點。 B樹示意圖如下：

B樹的特性：

1. 所有鍵值分佈在整個樹中
2. 任何關鍵字出現且只出現在一個節點中
3. 搜尋有可能在非葉子節點結束
4. 在關鍵字全集內做一次查找，效能逼近二分查找演算法

  為了提升效率，要盡量減少磁碟I/O的次數。實際過程中，磁碟並不是每次嚴格按需讀取，而是每次都會預讀。

  磁碟讀取完需要的資料後，會依序再多讀一部分資料到記憶體中，而這樣做的理論依據是電腦科學中註明的局部性原理：

• 由於磁碟順序讀取的效率很高(不需要尋址時間，只需很少的旋轉時間)，因此對於具有局部性的程式來說，預讀可以提高I/O效率.預讀的長度一般為頁(page)的整倍數。
• MySQL(預設使用InnoDB引擎)，將記錄依照頁的方式管理，每頁大小預設為16K(可以修改)。

B-Tree借助電腦磁碟預讀機制:

  每次新建節點的時候，都是申請一個頁的空間，所以每查找一個節點只需要一次I/ O;因為實際應用當中，節點深度會很少，所以查找效率很高.

  那麼最終版的B 樹是如何做的呢？

4、B Tree (B 樹是B樹的變體，也是一種多路搜尋樹)

從圖中也可以看到，B 樹與B樹的不同在於：

1. 所有關鍵字儲存在葉子節點，非葉子節點不儲存真正的data，因此可以快速定位到葉子結點。
2. 為所有葉子節點增加了一個鏈指針，意味著所有的值都是按順序存儲的，並且每一個葉子頁到根的距離相同，很適合查找範圍數據。

**因此，B Tree可以對，>=，BETWEEN，IN，以及不以萬用字元開始的LIKE使用索引。 **

B 樹的優點：

比較的次數均衡，減少了I/O次數，提高了查找速度，查找也更穩定。

• B 樹的磁碟讀寫代價更低
• B 樹的查詢效率更加穩定

  要知道的是，你每次建立表，系統會為你自動建立一個基於ID的聚集索引（上述B 樹），儲存全部資料；你每次增加索引，資料庫就會為你建立一個附加索引（上述B 樹），索引選取的欄位數量就是每個節點儲存資料索引的個數，注意該索引不會儲存全部資料。

四、為什麼MySQL索引選擇了 B 樹 而不是 B樹？

1. B 樹更適合外部儲存(一般指磁碟儲存),由於內節點(非葉子節點)不儲存data，所以一個節點可以儲存更多的內節點，每個節點能索引的範圍更大更精確。也就是說使用B 樹單次磁碟I/O的資訊量相比較B樹更大，I/O效率更高。
2. mysql是關係型資料庫，經常會按照區間來存取某個索引列，B 樹的葉子節點間按順序建立了鏈指針，加強了區間訪問性，所以B 樹對索引列上的區間範圍查詢很友善。而B樹每個節點的key和data在一起，無法進行區間查找。

五、程式設計師，你應該知道的索引知識點

1、回表查詢

例如你創建了name，age索引name_age_index，查詢數據時使用了

select * from table where name ='陈哈哈' and age = 26;
1复制代码

  由於附加索引中只有name 和age，因此命中索引後，資料庫還必須回去聚集索引中查找其他數據，這就是回表，這也是你背的那條：少用select * 的原因。

2、索引覆蓋

結合回表會更好理解，例如上述name_age_index索引，有查詢

select name， age from table where name ='陈哈哈' and age = 26;
1复制代码

  此時select的欄位name，age在索引name_age_index中都能取得到，所以不需要回表，滿足索引覆蓋，直接回傳索引中的數據，效率高。是DBA同學優化時的首選優化方式。

3、最左前綴原則

  B 樹的節點儲存索引順序是從左向右存儲，在匹配的時候自然也要滿足從左向右匹配；通常我們在建立聯合索引的時候，也就是對多個欄位建立索引，相信建立過索引的同學會發現，無論是Oracle或MySQL 都會讓我們選擇索引的順序，例如我們想要在a,b,c三個欄位上建立一個聯合索引，我們可以選擇自己想要的優先級，a、b、c,或是b、a、c 或是c、a、b等順序。 為什麼資料庫會讓我們選擇欄位的順序呢？不都是三個欄位的聯合索引麼？這裡就引出了資料庫索引最左的前綴原理。

  在我們開發中常常會遇到明明這個欄位建立了聯合索引，但SQL查詢該欄位時卻不會使用索引的問題。例如索引abc_index:(a,b,c)是a,b,c三個欄位的聯合索引，下列sql執行時都無法命中索引abc_index的；

select * from table where c = '1';

select * from table where b ='1' and c ='2';
123复制代码

以下三種情況卻會走索引:

select * from table where a = '1';

select * from table where a = '1' and b = '2';

select * from table where a = '1' and b = '2'  and c='3';
12345复制代码

從上面兩個例子大家是否闊以看出點眉目？

  是的，索引abc_index:(a,b,c)，只會在(a)、(a,b)、(a,b,c) 三種類型的查詢中使用。其實這裡說的有一點歧義，其實(a,c)也會走，但只走a字段索引，不會走c字段。

  另外還有一個特殊情況說明下，下面這種類型的也只會有 a與b 走索引，c不會走。

select * from table where a = '1' and b > '2'  and c='3';
1复制代码

  像上面這種類型的sql語句，在a、b走完索引後，c已經是無序了，所以c就沒法走索引，優化器會認為還不如全表掃描c字段來的快。

**最左前綴：顧名思義，就是最左優先，上例中我們創建了a_b_c多列索引,相當於創建了(a)單列索引，(a,b)組合索引以及(a,b,c)組合索引。 **

  因此，在建立多列索引時，要根據業務需求，where子句中使用最頻繁的一列放在最左邊。

4、索引下推優化

或索引name_age_index，有如下sql

select * from table where name like '陈%' and age > 26；
1复制代码

該語句有兩種執行可能：

##命中name_age_index聯合索引，查詢所有滿足name以"陳"開頭的數據， 然後回表查詢所有滿足的行。
• 命中name_age_index聯合索引，查詢所有滿足name以"陳"開頭的數據，然後順便篩選age>20的索引，再回表查詢全行數據。

顯然第2種方式回表查詢的行數較少，I/O次數也會減少，這就是索引下推。所以不是所有like都不會命中索引。

六、使用索引時的注意事項

1、索引不會包含有null值的欄位

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

2、使用短索引

  对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3、索引列排序

  查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4、like语句操作

  一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%陈%” 不会使用索引而like “陈%”可以使用索引。

5、不要在列上进行运算

这将导致索引失效而进行全表扫描，例如

SELECT * FROM table_name WHERE YEAR(column_name)<h2 data-id="heading-21">6、不使用not in和操作</h2><p>这不属于支持的范围查询条件，不会使用索引。</p><h1 data-id="heading-22">我的体会</h1><p>  曾经，我一度以为我很懂MySQL。</p><p>  刚入职那年，我还是个孩子，记得第一个需求是做个统计接口，查询近两小时每隔5分钟为一时间段的网站访问量，JSONArray中一共返回24个值，当时菜啊，写了个接口循环二十四遍，发送24条SQL去查（捂脸），由于那个接口，被技术经理嘲讽~~表示他写的SQL比我吃的米都多。虽然我们山东人基本不吃米饭，但我还是羞愧不已。。<br>然后经理通过调用一个dateTime函数分组查询处理一下，就ok了，效率是我的几十倍吧。从那时起，我就定下目标，深入MySQL学习，万一日后有机会嘲讽回去？</p><p>  筒子们，MySQL路漫漫，其修远兮。永远不要眼高手低，一起加油，希望本文能对你有所帮助。</p>

以上是哭..我以為我很懂MySQL索引的詳細內容。更多資訊請關注PHP中文網其他相關文章！