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、優點
- 索引大大減小了服務器需要掃描的數據量
- 索引可以幫助服務器避免排序和臨時表
- 索引可以將隨機I/O變成順序I/O
2、缺點
- 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和delete。因為更新表時，MySQL不僅要保存數據，還要保存索引文件。
- 建立索引會占用磁盤空間的索引文件。一般情況這個問題不算嚴重，但如果你在一個大表上創建了多種組合索引，且伴隨大量數據量插入，索引文件大小也會快速膨脹。
- 如果某個數據列包含許多重復的內容，為它建立索引就沒有太大的實際效果。
- 對于非常小的表，大部分情況下簡單的全表掃描更高效；
??因此應該只為最經常查詢和最經常排序的數據列建立索引。（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樹的特點：
- 所有鍵值分布在整個樹中
- 任何關鍵字出現且只出現在一個節點中
- 搜索有可能在非葉子節點結束
- 在關鍵字全集內做一次查找，性能逼近二分查找算法
??為了提升效率，要盡量減少磁盤I/O的次數。實際過程中，磁盤并不是每次嚴格按需讀取，而是每次都會預讀。
??磁盤讀取完需要的數據后，會按順序再多讀一部分數據到內存中，這樣做的理論依據是計算機科學中注明的局部性原理：
- 由于磁盤順序讀取的效率很高(不需要尋址時間，只需很少的旋轉時間)，因此對于具有局部性的程序來說，預讀可以提高I/O效率.預讀的長度一般為頁(page)的整倍數。
- MySQL(默認使用InnoDB引擎)，將記錄按照頁的方式進行管理，每頁大小默認為16K(可以修改)。
B-Tree借助計算機磁盤預讀機制:
??每次新建節點的時候，都是申請一個頁的空間，所以每查找一個節點只需要一次I/O;因為實際應用當中，節點深度會很少，所以查找效率很高.
??那么最終版的 B+樹 是如何做的呢？
4、B+ Tree (B+樹是B樹的變體，也是一種多路搜索樹)
從圖中也可以看到，B+樹與B樹的不同在于：
- 所有關鍵字存儲在葉子節點，非葉子節點不存儲真正的data，從而可以快速定位到葉子結點。
- 為所有葉子節點增加了一個鏈指針，意味著所有的值都是按順序存儲的，并且每一個葉子頁到根的距離相同，很適合查找范圍數據。
**因此，B+Tree可以對，>=，BETWEEN，IN，以及不以通配符開始的LIKE使用索引。**
B+樹的優點：
比較的次數均衡，減少了I/O次數，提高了查找速度，查找也更穩定。
- B+樹的磁盤讀寫代價更低
- B+樹的查詢效率更加穩定
??要知道的是，你每次創建表，系統會為你自動創建一個基于ID的聚集索引（上述B+樹），存儲全部數據；你每次增加索引，數據庫就會為你創建一個附加索引（上述B+樹），索引選取的字段個數就是每個節點存儲數據索引的個數，注意該索引并不存儲全部數據。
四、為什么MySQL索引選擇了 B+樹 而不是 B樹？
- B+樹更適合外部存儲(一般指磁盤存儲),由于內節點(非葉子節點)不存儲data，所以一個節點可以存儲更多的內節點，每個節點能索引的范圍更大更精確。也就是說使用B+樹單次磁盤I/O的信息量相比較B樹更大，I/O效率更高。
- 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)<2017; 1復制代碼
6、不使用not in和操作
這不屬于支持的范圍查詢條件，不會使用索引。
我的體會
??曾經，我一度以為我很懂MySQL。
??剛入職那年，我還是個孩子，記得第一個需求是做個統計接口，查詢近兩小時每隔5分鐘為一時間段的網站訪問量，JSONArray中一共返回24個值，當時菜啊，寫了個接口循環二十四遍，發送24條SQL去查（捂臉），由于那個接口，被技術經理嘲諷~~表示他寫的SQL比我吃的米都多。雖然我們山東人基本不吃米飯，但我還是羞愧不已。。
然后經理通過調用一個dateTime函數分組查詢處理一下，就ok了，效率是我的幾十倍吧。從那時起，我就定下目標，深入MySQL學習，萬一日后有機會嘲諷回去？
??筒子們，MySQL路漫漫，其修遠兮。永遠不要眼高手低，一起加油，希望本文能對你有所幫助。
