mysql聚集索引、輔助索引、覆蓋索引、聯合索引的使用

Posted on by

《MySQL技術內幕 InnoDB存儲引擎》學習筆記

聚集索引（Clustered Index）

聚集索引就是按照每張表的主鍵構造一棵B+樹，同時葉子節點中存放的即為整張表的行記錄數據。

舉個例子，直觀感受下聚集索引。

創建表t，並以人為的方式讓每個頁隻能存放兩個行記錄（不清楚怎麼人為控制每頁隻存放兩個行記錄）：

這裡寫圖片描述

最後《MySQL技術內幕》的作者通過分析工具得到這棵聚集索引樹的大致構造如下：

這裡寫圖片描述

聚集索引的葉子節點稱為數據頁，每個數據頁通過一個雙向鏈表來進行鏈接，而且數據頁按照主鍵的順序進行排列。

如圖所示，每個數據頁上存放的是完整的行記錄，而在非數據頁的索引頁中，存放的僅僅是鍵值及指向數據頁的偏移量，而不是一個完整的行記錄。

如果定義瞭主鍵，InnoDB會自動使用主鍵來創建聚集索引。如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替主鍵。如果沒有唯一的非空索引，InnoDB會隱式定義一個主鍵來作為聚集索引。

輔助索引(Secondary Index)

輔助索引，也叫非聚集索引。和聚集索引相比，葉子節點中並不包含行記錄的全部數據。葉子節點除瞭包含鍵值以外，每個葉子節點的索引行還包含瞭一個書簽（bookmark），該書簽用來告訴InnoDB哪裡可以找到與索引相對應的行數據。

還是以《MySQL技術內幕》中的例子，來直觀感受下輔助索引的模樣。

還是以上面的表t為例，在列c上創建非聚集索引：

這裡寫圖片描述

然後作者通過分析工作得到輔助索引和聚集索引的關系圖：

這裡寫圖片描述

可以看到輔助索引idx_c的葉子節點中包含瞭列c的值和主鍵的值。

以Key為7fffffff為例，7是0111,0代表負數，真實的值應該取反加1，是-1，這是列c的值。Pointer是80000001,8是1000，1代表正數，所以80000001代表1，是主鍵的值。

覆蓋索引（Covering index）

InnoDB存儲引擎支持覆蓋索引，即從輔助索引中就可以得到查詢的記錄，而不需要查詢聚集索引中的記錄。

使用覆蓋索引有啥好處？

  • 可以減少大量的IO操作

上圖中我們知道，如果要查詢輔助索引中不含有的字段，得先遍歷輔助索引，再遍歷聚集索引，而如果要查詢的字段值在輔助索引上就有，就不用再查聚集索引瞭，這顯然會減少IO操作。

比如上圖中，以下sql可以直接使用輔助索引，

select a from where c = -2;
  • 有助於統計

假設存在如下表：

  CREATE TABLE `student` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` varchar(255) NOT NULL,
  `school` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_school_age` (`school`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果在該表上執行：

select count(*) from student

優化器會怎麼處理？

遍歷聚集索引和輔助索引都可以統計出結果，但輔助索引要遠小於聚集索引，所以優化器會選擇輔助索引來統計。執行explain命令：

這裡寫圖片描述

key和Extra顯示使用瞭idx_name這個輔助索引。

還有，假設執行以下sql：

select * from student where age > 10 and age < 15

因為聯合索引idx_school_age的字段順序是先school再age，按照age做條件查詢，通常不走索引：

這裡寫圖片描述

但是，如果保持條件不變，查詢所有字段改為查詢條目數：

select count(*) from student where age > 10 and age < 15

優化器會選擇這個聯合索引：

這裡寫圖片描述

聯合索引

聯合索引是指對表上的多個列進行索引。

以下為創建聯合索引idx_a_b的示例：

這裡寫圖片描述

聯合索引的內部結構：

這裡寫圖片描述

聯合索引也是一棵B+樹，其鍵值數量大於等於2。鍵值都是排序的，通過葉子節點可以邏輯上順序的讀出所有數據。數據（1,1）（1,2）（2,1）（2,4）（3,1）（3,2）是按照（a，b）先比較a再比較b的順序排列。

基於上面的結構，對於以下查詢顯然是可以使用（a，b）這個聯合索引的：

select * from table where a=xxx and b=xxx ;

select * from table where a=xxx;

但是對於下面的sql是不能使用這個聯合索引的，因為葉子節點的b值，1,2,1,4,1,2顯然不是排序的。

select * from table where b=xxx

聯合索引的第二個好處是對第二個鍵值已經做瞭排序。舉個例子：

create table buy_log(
    userid int not null,
    buy_date DATE
)ENGINE=InnoDB;

insert into buy_log values(1, '2009-01-01');
insert into buy_log values(2, '2009-02-01');

alter table buy_log add key(userid);
alter table buy_log add key(userid, buy_date);

當執行

select * from buy_log where user_id = 2；

時，優化器會選擇key(userid)；但是當執行以下sql：

select * from buy_log where user_id = 2 order by buy_date desc;

時，優化器會選擇key(userid, buy_date)，因為buy_date是在userid排序的基礎上做的排序。

如果把key（userid,buy_date）刪除掉，再執行：

select * from buy_log where user_id = 2 order by buy_date desc;

優化器會選擇key（userid），但是對查詢出來的結果會進行一次filesort，即按照buy_date重新排下序。所以聯合索引的好處在於可以避免filesort排序。

到此這篇關於mysql聚集索引、輔助索引、覆蓋索引、聯合索引的使用的文章就介紹到這瞭,更多相關聚集索引、輔助索引、覆蓋索引、聯合索引內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：