什么時候使用B+樹索引

• 并不是所有查詢條件下出現(xiàn)的列都需要添加索引。對于什么時候添加索引，我們通過經(jīng)驗判斷，訪問表中很少一部分行時候，使用B+樹索引才有意義。
• 對于性別字段，地區(qū)字段，類型字段，他們?nèi)≈捣秶苌?#xff0c;即選擇性低。如下sql

select * from moment where status = 1;

• 對于性別，狀態(tài)，可取值范圍局限性非常大。對于上述SQL得到的結(jié)果可能是該表50% 的數(shù)據(jù)（假設2中狀態(tài)），這時候，添加B+樹索引完全沒有必要。相反，如果某個字段取值范圍不固定，幾乎沒有重復，即高選擇性，此時使用B+樹索引是最合適的，例如nickName 昵稱字段，基本上一個應用中都不給你重復出現(xiàn)。
• 如上，當訪問選擇性高的字段并從表中取出很少一部分行時候，對這個字段添加B+樹索引是非常有必要的，但是如果出現(xiàn)了訪問字段是高選擇性的，但是取出的行數(shù)據(jù)占表中大部分數(shù)據(jù)時候，MySql數(shù)據(jù)庫可能就不會使用B+樹索引了，我們看如下一個案例。

show index from New_UserBaseInfo;

• 表New_userBaseInfo大約有450W條數(shù)據(jù)。encodePhone字段上有一個索引，這時候，我們查找 ‘89F5F342F1ABE260F4F3D728174CF379’ 這個加密手機號時候，得到如下執(zhí)行計劃：

explain select * from New_UserBaseInfo where encodePhone = '89F5F342F1ABE260F4F3D728174CF379'

• 可以看到 使用了idx_encodePhone這個索引，也符合我們前面提到的套選擇性，選取表中很少行的原色，但是入座執(zhí)行下面的這條語句：

explain select * from New_UserBaseInfo where encodePhone > '89F5F342F1ABE260F4F3D728174CF379'

• 可以看到possible_keys依然是idx_encodePhone，但是實際上優(yōu)化器使用的索引key是null， 而且type是ALL，說他他匹配到了對應的索引，但是他并沒有使用索引去查下，還是全表查詢。way？？？
• 因為這不符合我們之前說的規(guī)則，雖然encodePhone 這個字段的值是高選擇性，但是我們?nèi)〕龅男袛?shù)據(jù)中占了表中的一大部分數(shù)據(jù)。可以看到rows顯示的是44W+ 數(shù)據(jù)，包括99%的數(shù)據(jù)了。因此查詢優(yōu)化器并沒有使用索引
• 也許會有疑問，查找加密手機號大于 89F5F342F1ABE260F4F3D728174CF379 的字段，這種情況幾乎不太可能出現(xiàn)。的確如此，但是我們考慮New_UserBaseInfo上 的lastRegisterTime 字段（注冊時間），改字段日期類型，字段上有一個idx_lastRegisterTime 非唯一索引，看如下兩條查詢語句：

explain select * from New_UserBaseInfo where lastRegisterTime < '2019-06-24';

explain select * from New_UserBaseInfo where lastRegisterTime < '2019-06-25';

• 查找用戶注冊時間小于某個時間的sql語句。前后兩條SQL只相差1天時間，2條SQL語句的執(zhí)行計劃竟然不一樣。在第二條SQL執(zhí)行的時候，雖然同樣使用的idx_lastRegisterTime索引，但是優(yōu)化器卻沒有使用這個索引，而是對全表掃描。

• MySQL數(shù)據(jù)庫的查詢優(yōu)化器會通過explain的rows字段預估查詢可能的到的行，如果大于某個值，則B+樹會選擇全表掃描。這個值大概是在20% 左右數(shù)據(jù)總量的時候會觸發(fā)。即當我們?nèi)〕龅臄?shù)據(jù)占比超過全數(shù)據(jù)量的20% 的時候，優(yōu)化器不會使用索引，而是全表掃描。

• 但是rows中預估的數(shù)據(jù)并不是絕對準確的，可以看大優(yōu)化器判斷日期小于2019-06-24 的數(shù)據(jù)是：757912，但是實際值：462377

• 實際值少了大概38%，這可能對查詢優(yōu)化器的選擇產(chǎn)生一定的影響，如果對比強制使用索引和使用優(yōu)化器選擇的全表掃描來查詢注冊日期小于2019-06-25的數(shù)據(jù)，最終發(fā)現(xiàn)如下：

select * from New_UserBaseInfo force index(idx_lastRegisterTime)where lastRegisterTime < '2019-06-25';select * from New_UserBaseInfo where lastRegisterTime < '2019-06-25';

• 查詢時間分別是1.45s，5.8s，第一句SQL強制使用idx_lastRegisterTime 索引，所用的時間是4.15s，根據(jù)優(yōu)化器選擇的全表掃方式，執(zhí)行第二SQL確5.8s，因此優(yōu)化器的選擇并不完全是正確的，有時候需要自己去判斷。

順序讀，隨機讀與預讀取

• 之前介紹的規(guī)則中，索引使用原則，高選擇，取出表中少部分數(shù)據(jù)。但是為什么只能是少部分數(shù)據(jù)？這就和InnoDB的順序讀和隨機讀取有關(guān)系
  • 順序讀：是指定順序的讀取磁盤上的快（Block）；隨機讀（Random Read）是指訪問的快不是連續(xù)的，需要磁盤的磁頭不斷移動。當前傳統(tǒng)機械磁盤的瓶頸之一就是隨機讀取的速度較低。
  • 在網(wǎng)上找的資料：同時對比RAID（磁盤陣列）開啟write back 和write Through的性能差異。測試磁盤是由4塊15000轉(zhuǎn)的硬盤組成的RADI 10.測試文件大小2GB，塊大小64KB。
  • Write-through：CPU向cache寫入數(shù)據(jù)時，同時向memory(后端存儲)也寫一份，使cache和memory的數(shù)據(jù)保持一致
  • Write-back：cpu更新cache時，只是把更新的cache區(qū)標記一下，并不同步更新memory(后端存儲)。只是在cache區(qū)要被新進入的數(shù)據(jù)取代時，才更新memory(后端存儲)。

write backwrite Through

順序讀	193.76	65.333
隨機讀	82.117	16.218

• 可以看到，不管是否開啟RAID卡的Write Back功能，磁盤的隨機讀性能都遠遠小于順序讀的性能。而上表中也說明了Write Back相對于Write Through 的性能提升。
• 在數(shù)據(jù)庫中，順序讀是指根據(jù)索引的葉節(jié)點數(shù)據(jù)就能順序的讀取所需要的行數(shù)據(jù)。這個順序只是邏輯上的順序讀取，在物理磁盤上，行對應的數(shù)據(jù)可能還是隨機分布在磁盤上的不同地址。但是相對來說，物理磁盤上的數(shù)據(jù)還是比較順序的，因為B+樹的構(gòu)建是根據(jù)區(qū)來管理的，區(qū)是64個連續(xù)的頁。如根據(jù)主鍵進行讀取，或者通過輔助索引的葉節(jié)點就能讀取到數(shù)據(jù)。
• 隨機讀，一般指訪問輔助索引葉節(jié)點不能完全得到的結(jié)果，需要根據(jù)輔助索引葉節(jié)點中的主鍵去找時機行數(shù)據(jù)。一般說來，輔助索引和主鍵所在的數(shù)據(jù)段是不同的，因此訪問是隨機的方式
• 之前的sql lastRegisterTime < ‘2019-06-25’ 這條就是典型的隨機讀取。而正是因為讀取的方式是隨機的，并且隨機讀的性能會遠低于順序讀取，因此優(yōu)化器才會選擇全表掃描的方式，而不是走 idx_lastRegisterTime 這個索引。

預讀取

• InnoDB存儲引擎為了提高讀性能，引入了預讀取技術(shù)。預讀取是通過一次IO請求將多個頁面預讀取緩沖池中，并且估計預讀取的多個頁馬上會被訪問。傳統(tǒng)的IO請求每次只讀取一個頁，在傳統(tǒng)機械硬盤較低的IOPS下。預讀取技術(shù)可以大大提高讀取性能。

• InnoDB有兩個預讀取的方法，隨機預讀取（Random read ahead）和線性預讀取（linear read ahead）

  • 隨機預讀取：指定一個區(qū)（64個連續(xù)的頁）中的13個頁面也在緩沖區(qū)中，并且在LRU列表的前端（即頁是被頻繁訪問），則InnoDB存儲引擎會將這個區(qū)中神域的所有頁預讀到緩沖區(qū)。
  • 線性預讀取基于緩沖池中的頁的訪問模式，而不是數(shù)量。如果一個區(qū)中的24個頁都被順序訪問了，則InnoDB存儲引擎會讀取下一個區(qū)的所有頁。
  • LRU頁解析：Innodb為了加快對磁盤中數(shù)據(jù)的操作，在操作磁盤上的數(shù)據(jù)時，會先把數(shù)據(jù)存放到一塊名為Buffer Pool的內(nèi)存緩沖池中，但是內(nèi)存的大小遠小于磁盤的大小，因此需要一種機制來淘汰非熱點數(shù)據(jù)，保證內(nèi)存中存在的數(shù)據(jù)是較為頻繁訪問的數(shù)據(jù)。LRU是這種管理場景下最常用的算法，類似Redis中的LRU淘汰算法：
    • 新數(shù)據(jù)插入到鏈表頭部；
    • 每當緩存命中（即緩存數(shù)據(jù)被訪問），則將數(shù)據(jù)移到鏈表頭部；
    • 當鏈表滿的時候，將鏈表尾部的數(shù)據(jù)丟棄。

• InnoDB1.0.4 開始，縮進訪問的預讀取被取消了，而線性預讀取還是保留了，并且加入了innodb_read_ahead_threshold參數(shù)，改參數(shù)標識一個區(qū)中的多少個頁面被順序訪問時候，InnoDB存儲引擎才開啟預讀取，即預讀下一個區(qū)中所有頁。默認值是56，當一個區(qū)中56個頁都被訪問過，則預讀下一個區(qū)的所有項。

show VARIABLES like 'innodb_read_ahead_threshold'

• 固態(tài)硬盤的情況，固態(tài)硬盤沒有讀寫磁頭，讀取不需要旋轉(zhuǎn)，因此隨機讀取性能得到質(zhì)的提高。因為固態(tài)硬盤現(xiàn)在并沒有全面普及，所InnoDB存儲引擎中沒有見到對固態(tài)硬盤相關(guān)的一些優(yōu)化。

輔助索引的優(yōu)化

• 輔助索引的葉子節(jié)點包含主鍵，但是輔助索引的葉子節(jié)點不包含完整的行信息，因此，InnoDB存儲引擎總是會先從輔助索引的葉節(jié)點判斷是否能得到所需要的數(shù)據(jù)。用如下案例解釋：

drop table if EXISTS tcreate table t(a int not null, b varchar(20), PRIMARY key(a), key(b));insert into t select 1, 'k';insert into t select 2, 'do';insert into t select 3, 'dr';insert into t select 4, 'an'; select * from t;

• 如上我們插入的數(shù)據(jù)，我們執(zhí)行如下查詢語句：

• 順序如下：
  

• 加入我們插入的數(shù)據(jù)如下：

insert into t select 1, 'a';insert into t select 2, 'b';insert into t select 3, 'c';insert into t select 4, 'd'; select * from t;

• 查詢結(jié)果如下：
  

• 我們可以看到，他的排序規(guī)則是按照b的順序排列的，并不是根據(jù)主鍵a的順序排列，這也就是我們上面提到的，因為輔助索引中包含了主鍵的值，因此訪問b列上的輔助索引就能得到a的值，這樣就可以得到表中所有數(shù)據(jù)的值。 通常情況，一個輔助索引頁中，能存放的數(shù)據(jù)比主鍵索引頁上存放的數(shù)據(jù)多，因此優(yōu)化器選擇了輔助索引 ，如果我們解釋這句查詢語句得到如下結(jié)果：

• 可以看到，優(yōu)化器最終選擇b索引，如果想得到對列a的排序結(jié)果，還需要對他進行Order By 操作，這樣優(yōu)化器才會走主鍵，避免在查詢b列后又發(fā)生對a的排序操作。如下圖：
  

• 或者可以強制使用主鍵索引
  

聯(lián)合索引

• 聯(lián)合索引是指對表上的多個列做索引。之前說的情況，都對表上的某個列進行索引。聯(lián)合索引類似：

alter table t add key idx_a_b(a,b);

• 什么時候該使用聯(lián)合索引？在這個問題之前我們應該弄清楚聯(lián)合索引內(nèi)部的結(jié)構(gòu)，本質(zhì)上，聯(lián)合索引還是 B+樹，不同的是聯(lián)合索引的鍵值數(shù)量不是1個，二手大于等于2個。我們用簡單的兩個key的情況說明問題，如上a，b兩個key，我們用如下圖表示：

• 如上圖中看到多個key情況的B+樹，和我們之前討論的單個鍵值沒有什么區(qū)別，鍵值都是排序的，通過葉子節(jié)點可以邏輯上順序的讀出所有數(shù)據(jù)，就上面的例子來說：（1,1）（1,2）（2,1）（2,4）（3,1）（3,2）數(shù)據(jù)按照（a，b）的順序存放。
• 例如對于查詢 select * from table where a= xxx and b = xxx，這種情況顯然可以用（a，b）聯(lián)合索引。對應單個a的查詢 select * from table where a= xxx也可以使用（a，b）聯(lián)合索引。但是對于select * from table where b= xxx單個b的查詢不可以用這個B+樹索引。可以看到葉子節(jié)點上b的值 1,2，1,4,1，2，顯然不是按排序的，因此對于b列的查詢使用不到（a，b）的聯(lián)合索引。
• 聯(lián)合索引的第二個好處：可以對第二個鍵值進行排序，例如，很多情況，我們都只查詢某個用戶訂單信息，并按照時間排序，取出最近一段時間的購買記錄，這個時候使用聯(lián)合索引可以避免多一次的排序操作。因為索引本身的葉子節(jié)點已經(jīng)排序了。如下測試案例：

create table buy_log(userid int unsigned not null, buy_date date);insert into buy_log values (1,'2021-01-19'), (2,'2021-01-19'), (3,'2021-01-19'), (1,'2021-02-19'), (3,'2021-02-19'), (1,'2021-03-19'), (1,'2021-04-19');ALTER table buy_log add key(userid); alter table buy_log add key(userid, buy_date);

• 如上建立兩個索引，都包含userid字段，對userid進行查詢，看優(yōu)化器的選擇，如下：
  

• 如上，possible_keys中有兩個索引，分別是單個userid和userid，buy_date的聯(lián)合索引。優(yōu)化選擇的是userid，因為改葉節(jié)點包含單個鍵值，因此一個頁能存放的記錄更多，接著，看一下的查詢，我們假定要取出userid= 1的最近三次購買記錄，并分析使用單個索引和符合索引區(qū)別：
  

• 同樣的都可以用兩個索引，但是這次優(yōu)化器選擇了符合索引，因為這個聯(lián)合索引中buy_date已經(jīng)排序好了，如果我們強制使用userid的單個索引，會有如下結(jié)果：
  

• 如上extra信息中，看到Using filesort，filesort指排序，但是不是文件中完成，我們可以對比執(zhí)行：

• 如上看到增加了排序操作，但是如果使用userid， buy_date的聯(lián)合索引userid_2，就不會有這一次額外的操作，如下：

上一篇：數(shù)據(jù)結(jié)構(gòu)與索引-- B+樹索引
下一篇：mysql技術(shù)分享-- 視圖是什么

總結(jié)

以上是生活随笔為你收集整理的数据结构与索引-- mySql索引诡异事件的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。