關於索引必須知道的知識

哆啦罐發表於2021-01-03

mysql索引的各種概念

在學習索引的時候,常常會看到回表、覆蓋索引、索引下推、頁分裂等等概念,本篇就常見概念進行介紹和總結,希望能幫助大家快速掌握這些“高大上”的概念。

索引基於B+樹,要想更好地理解這些概念建議先了解談談MySQL索引底層實現之資料結構資料結構之B+樹

回表

根據葉子節點的內容, 索引型別分為主鍵索引和非主鍵索引。(mysql索引的資料結構是B+樹,對這方面知識看不懂的可以參考)

  • 主鍵索引的葉子節點存的是整行資料。 在InnoDB裡, 主鍵索引也被稱為聚簇索引(clustered index)。
  • 非主鍵索引的葉子節點內容是索引欄位值+主鍵的值。 在InnoDB裡, 非主鍵索引也被稱為二級索引(secondary index) 。

基於主鍵索引和普通索引的查詢有什麼區別?

普通索引查詢方式,則需要先搜尋索引樹,得到主鍵值,再到主鍵索引樹按主鍵值搜尋一次,這個過程稱為回表

也就是說,基於非主鍵索引的查詢需要多掃描一棵索引樹。 因此, 我們在應用中應該儘量使用主鍵查詢。

索引維護——頁分裂

什麼是頁分裂?

如果R5所在的資料頁已經滿了, 根據B+樹的演算法, 這時候需要申請一個新的資料頁,然後挪動部分資料過去。這個過程稱為頁分裂。 當然有分裂就有合併。當相鄰兩個頁由於刪除了資料,利用率很低之後, 會將資料頁做合併。 合併的過程, 可以認為是分裂過程的逆過程

頁分裂的壞處

除了效能外, 頁分裂操作還影響資料頁的利用率。原本放在一個頁的資料, 現在分到兩個頁中,整體空間利用率降低大約50%。

如何避免頁分裂?

使用自增主鍵。每次插入一條新記錄, 都是追加操作,都不涉及到挪動其他記錄,也不會觸發葉子節點的分裂。

自增主鍵的其他好處

由於每個非主鍵索引的葉子節點上都是主鍵的值。,如果用身份證號做主鍵, 那麼每個二級索引的葉子節點佔用約20個位元組, 而如果用整型做主鍵, 則只要4個位元組, 如果是長整型(bigint)則是8個位元組。顯然, 主鍵長度越小, 普通索引的葉子節點就越小, 普通索引佔用的空間也就越小。

有沒有什麼場景適合用業務欄位直接做主鍵的呢?

比如,有些業務的場景需求是這樣的:

  1. 只有一個索引
  2. 該索引必須是唯一索引

由於沒有其他索引, 所以也就不用考慮其他索引的葉子節點大小的問題。這時候我們就要優先考慮上一段提到的“儘量使用主鍵查詢”原則, 直接將這個索引設定為主鍵,可以避免每次查詢需要搜尋兩棵樹。

覆蓋索引

非聚集索引的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。

字首索引對覆蓋索引的影響

使用字首索引就不能使用覆蓋索引對查詢效能進行優化了。因為索引只包含了字串的部分資料。

遇到字首的區分度不夠好的情況時,我們要怎麼辦?

  1. 使用倒序儲存:
    • 不會消耗額外的欄位,但是每次索引一般不止4個字元,索引樹需要多的儲存空間
    • 每次寫和讀的時候,都需要額外呼叫一次reverse函式
    • 查詢時有字首索引的問題:會增加額外的記錄掃描次數
    • 不支援範圍查詢
  2. 使用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)先做判斷, 直接過濾掉不滿足條件的記錄, 減少回表次數。

本文記錄比較零散,如果有模稜兩可或者不對的地方歡迎大家指正。

相關文章