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)先做判斷, 直接過濾掉不滿足條件的記錄, 減少回表次數。
本文記錄比較零散,如果有模稜兩可或者不對的地方歡迎大家指正。