1. 首頁 > 生活百科 > >

「MySQL高級篇」MySQL索引原理，設計原則( 三 )

生活百科


索引分類在InnoDB中，表都是根據主鍵順序以索引的形式存放的，這種存儲方式的表稱為索引組織表 。又因為前面我們提到的，InnoDB使用了B+樹索引模型，所以數據都是存儲在B+樹中的 。?
每一個索引在InnoDB里面對應一棵B+樹 。假設 ， 我們有一個主鍵列為ID的表，表中有字段k，并且在k上有索引 。這個表的建表語句是：
mysql> create table T(id int primary key,k int not null,name varchar(16),index (k))engine=InnoDB;表中R1~R5的(ID,k)值分別為(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，兩棵樹的示例示意圖如下：

「MySQL高級篇」MySQL索引原理，設計原則

文章插圖
從圖中不難看出，根據葉子節點的內容，索引類型分為主鍵索引和非主鍵索引 。
主鍵索引數據表的主鍵列使用的就是主鍵索引 ， 且會默認創建，這也是為什么 ， 我們還沒學索引的時候，老師經常跟我們說根據主鍵查會快一點 ， 原來主鍵本身就建好了索引 。主鍵索引的葉子節點存的是整行數據 。在InnoDB里，主鍵索引也被稱為聚簇索引（clustered index） 。
輔助索引輔助索引的葉子節點內容是主鍵的值 。在InnoDB里，輔助索引也被稱為二級索引（secondary index） 。?
如下圖：
  • 主鍵索引存放了整行數據
  • 輔助索引只存放了自己本身 ， 以及id主鍵用于回表查詢
?
「MySQL高級篇」MySQL索引原理，設計原則

文章插圖
?
根據上面的索引結構，我們來討論一個問題：基于主鍵索引和輔助索引的查詢有什么區別？?
  • 如果語句是select * from T where ID=500，即主鍵查詢方式 ， 則只需要搜索ID這棵B+樹；
  • 如果語句是select * from T where k=5，即普通索引查詢方式，則需要先搜索k索引樹 ， 得到ID的值為500，再到ID索引樹搜索一次 。這個過程稱為回表 。
也就是說，基于輔助索引的查詢需要多掃描一棵索引樹 。因此，我們在應用中應當盡量使用主鍵查詢 。?
除非說，我們所要查詢的數據 ， 剛好就是我們索引樹上存在的，此時我們稱之為覆蓋索引--即索引列中包含了我們要查詢的所有數據 。同時 ， 二級索引又分為了如下幾種(先簡單略過即可，后續我們再慢慢了解)：?
  • 唯一索引(Unique Key) ：唯一索引也是一種約束 。唯一索引的屬性列不能出現重復的數據 ， 但是允許數據為 NULL，一張表允許創建多個唯一索引 。建立唯一索引的目的大部分時候都是為了該屬性列的數據的唯一性 ， 而不是為了查詢效率 。
  • 普通索引(Index) ：普通索引的唯一作用就是為了快速查詢數據，一張表允許創建多個普通索引，并允許數據重復和 NULL 。
  • 前綴索引(Prefix) ：前綴索引只適用于字符串類型的數據 。前綴索引是對文本的前幾個字符創建索引，相比普通索引建立的數據更小，因為只取前幾個字符 。
  • 全文索引(Full Text) ：全文索引主要是為了檢索大文本數據中的關鍵字的信息，是目前搜索引擎數據庫使用的一種技術 。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引
擴展--索引下推所謂下推，顧名思義，其實是推遲我們的回表操作，MySQL不會輕而易舉讓我們去回表 ， 因為很浪費 。什么意思呢？來看下邊這個例子 。
我們建立了一個復合索引(name，status) ， 索引中也是按這個字段來存儲的，類似圖中這樣：
復合索引樹(只存儲索引列和主鍵用于回表)
namestatusid(主鍵)小米101小米212我們執行這樣一條語句：
SELECT * FROM tb_seller WHERE name like '小米%' and status ='1' ;
  1. 首先我們在復合索引樹上 ， 找到了第一個以小米開頭的name -- 小米1
  2. 此時我們不著急回表(回到主鍵索引樹搜索的過程，我們稱為回表)，而是先在復合索引樹判斷status是否=1 ， 此時status=0，我們直接就不回表了 ， 直接繼續找下一個以小米開頭的name
  1. 找到第二個-- 小米2，判斷status=1，則根據id=2去主鍵索引樹上找，得到所有的數據
這種先在自身索引樹上判斷是否滿足其他的where條件，不滿足則直接pass掉，不進行回表的操作，就叫做索引下推 。
最左前綴原則所謂最左前綴，可以想象成一個爬樓梯的過程，假設我們有一個復合索引：name，status，address，那這個樓梯由低到高依次順序是：name，status ， address，最左前綴，要求我們不能出現跳躍樓梯的情況 ， 否則會導致我們的索引失效：?

推薦閱讀