關於索引必須知道的知識
mysql索引的各種概念
在學習索引的時候，常常會看到回表、覆蓋索引、索引下推、頁分裂等等概念，本篇就常見概念進行介紹和總結，希望能幫助大家快速掌握這些「高大上」的概念。
索引基於B+樹，要想更好地理解這些概念建議先了解談談MySQL索引底層實現之數據結構和數據結構之B+樹
回表
根據葉子節點的內容， 索引類型分為主鍵索引和非主鍵索引。（mysql索引的數據結構是B+樹，對這方面知識看不懂的可以參考）
- 主鍵索引的葉子節點存的是整行數據。 在InnoDB里， 主鍵索引也被稱為聚簇索引（clustered index）。
- 非主鍵索引的葉子節點內容是索引欄位值+主鍵的值。 在InnoDB里， 非主鍵索引也被稱為二級索引（secondary index） 。
基於主鍵索引和普通索引的查詢有什麼區別？
普通索引查詢方式，則需要先搜索索引樹，得到主鍵值，再到主鍵索引樹按主鍵值搜索一次，這個過程稱為回表。
也就是說，基於非主鍵索引的查詢需要多掃描一棵索引樹。 因此， 我們在應用中應該盡量使用主鍵查詢。
索引維護——頁分裂
什麼是頁分裂？
如果R5所在的數據頁已經滿了， 根據B+樹的演算法， 這時候需要申請一個新的數據頁，然後挪動部分數據過去。這個過程稱為頁分裂。 當然有分裂就有合併。當相鄰兩個頁由於刪除了數據，利用率很低之後， 會將數據頁做合併。 合併的過程， 可以認為是分裂過程的逆過程
頁分裂的壞處
除了性能外， 頁分裂操作還影響數據頁的利用率。原本放在一個頁的數據， 現在分到兩個頁中，整體空間利用率降低大約50%。
如何避免頁分裂？
使用自增主鍵。每次插入一條新記錄， 都是追加操作，都不涉及到挪動其他記錄，也不會觸發葉子節點的分裂。
自增主鍵的其他好處
由於每個非主鍵索引的葉子節點上都是主鍵的值。，如果用身份證號做主鍵， 那麼每個二級索引的葉子節點佔用約20個位元組， 而如果用整型做主鍵， 則只要4個位元組， 如果是長整型（bigint）則是8個位元組。顯然， 主鍵長度越小， 普通索引的葉子節點就越小， 普通索引佔用的空間也就越小。
有沒有什麼場景適合用業務欄位直接做主鍵的呢？
比如，有些業務的場景需求是這樣的：
- 只有一個索引
- 該索引必須是唯一索引
由於沒有其他索引， 所以也就不用考慮其他索引的葉子節點大小的問題。這時候我們就要優先考慮上一段提到的「盡量使用主鍵查詢」原則， 直接將這個索引設置為主鍵，可以避免每次查詢需要搜索兩棵樹。
覆蓋索引
非聚集索引的B+樹節點存儲的是索引列和主鍵，假如想要拿到完整數據的話還得根據主鍵去主鍵索引樹回表，這樣性能不好，如果我們要查詢得到的數據就是索引列和主鍵中的數據，就不要回表。這樣只需要在一棵索引樹上就能獲取SQL所需的所有列數據無需回表的索引稱為覆蓋索引
由於覆蓋索引可以減少樹的搜索次數， 顯著提升查詢性能， 所以使用覆蓋索引是一個常用的性能優化手段。
最左匹配原則
B+樹這種索引結構， 可以利用索引的「最左前綴」來定位記錄。
索引樹排序規則：在對聯合索引建立索引樹時，會按照索引欄位的順序依次排序。以（name,age,address）這個聯合索引為例，首先按照name排序完，在name排序值相同時繼續按照age排序。
最左匹配：在mysql建立聯合索引時還會遵循最左前綴匹配的原則，即最左優先，在檢索數據時從聯合索引的最左邊開始匹配（左邊的匹配不上，後面也不會再去匹配了）。同時，索引只能用於查找key是否存在（相等），遇到範圍查詢 (>、<、between、like左匹配)等就不能進一步匹配了，後續退化為線性查找。因此，列的排列順序決定了可命中索引的列數
在建立聯合索引的時候， 如何安排索引內的欄位順序？
- 考慮索引的復用能力。 因為可以支援最左前綴， 所以當已經有了(a,b)這個聯合索引後， 一般就不需要單獨在a上建立索引了。 因此， 第一原則是， 如果通過調整順序， 可以少維護一個索引， 那麼這個順序往往就是需要優先考慮採用的。
- 考慮空間。不要無節制的創建索引。
前綴索引
對字元串的前幾個字元（具體是幾個字元在建立索引時指定）建立索引，這樣建立起來的索引佔用空間更小
對字元串建立普通索引和前綴索引的語句如下：
# 普通字元串索引
alter table SUser add index index1(email);
# 前綴索引，索引長度為6
alter table SUser add index index2(email(6));
前綴索引的優勢和損失
優勢：佔用的空間會更小
損失：會增加額外的記錄掃描次數
使用前綴索引，定義好長度， 就可以做到既節省空間，又不用額外增加太多的查詢成本。
當要給字元串創建前綴索引時如何確定應該使用多長的前綴？
前綴索引肯定會損失區分度，我們需要提前預設一個可以接受的損失比列，使用count計算出多種長度的損失比例，選擇低於損失比例的最短長度。
第一步：計算算出這個列上有多少個不同的值：
select count(distinct email) as L from SUser
第二步：計算不同長度去重後有多少數據：
select
count(distinct left(email,4)） as L4,
count(distinct left(email,5)） as L5,
count(distinct left(email,6)） as L6,
count(distinct left(email,7)） as L7,
from SUser;
第三步：選擇合適的長度
在返回的L4~L7中，找出不小於 L * 95%的值，假設這裡L6、L7都滿足，你就可以選擇前綴長度為6。
前綴索引對覆蓋索引的影響
使用前綴索引就不能使用覆蓋索引對查詢性能進行優化了。因為索引只包含了字元串的部分數據。
遇到前綴的區分度不夠好的情況時，我們要怎麼辦？
- 使用倒序存儲：
- 不會消耗額外的欄位，但是每次索引一般不止4個字元，索引樹需要多的存儲空間
- 每次寫和讀的時候，都需要額外調用一次reverse函數
- 查詢時有前綴索引的問題：會增加額外的記錄掃描次數
- 不支援範圍查找
- 使用hash欄位
- 需要額外添加一個hash欄位
- 每次需要額外調用一次crc32()函數
- 查詢性能相對倒序存儲更穩定一些
- 不支援範圍查找
- 不再是使用前綴索引的方式
索引下推
上一段我們說到滿足最左前綴原則的時候，最左前綴可以用於在索引中定位記錄。 這時，你可能要問，那些不符合最左前綴的部分， 會怎麼樣呢？
mysql> select * from tuser where name like '張%' and age=10 and ismale=1;
我們還是以市民表的聯合索引（name, age） 為例。 如果現在有一個需求： 檢索出表中「名字第一個字是張， 而且年齡是10歲的所有男孩」。 那麼， SQL語句是這麼寫的：
你已經知道了前綴索引規則， 所以這個語句在搜索索引樹的時候， 只能用 「張」， 找到第一個滿足條件的記錄ID3。 當然， 這還不錯， 總比全表掃描要好。然後呢？當然是判斷其他條件是否滿足。
在MySQL 5.6之前， 只能從ID3開始一個個回表。 到主鍵索引上找出數據行， 再對比欄位值。
而MySQL 5.6 引入的索引下推優化（indexcondition pushdown)， 可以在索引遍歷過程中， 對索引中包含的欄位(age)先做判斷， 直接過濾掉不滿足條件的記錄， 減少回表次數。
本文記錄比較零散，如果有模稜兩可或者不對的地方歡迎大家指正。
