mysql中聚集索引、輔助索引、覆蓋索引、聯(lián)合索引怎么用

這篇文章主要介紹了mysql中聚集索引、輔助索引、覆蓋索引、聯(lián)合索引怎么用，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

聚集索引（Clustered Index）

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

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

創(chuàng)建表t，并以人為的方式讓每個頁只能存放兩個行記錄（不清楚怎么人為控制每頁只存放兩個行記錄）：

最后《MySQL技術(shù)內(nèi)幕》的作者通過分析工具得到這棵聚集索引樹的大致構(gòu)造如下：

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

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

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

輔助索引(Secondary Index)

輔助索引，也叫非聚集索引。和聚集索引相比，葉子節(jié)點中并不包含行記錄的全部數(shù)據(jù)。葉子節(jié)點除了包含鍵值以外，每個葉子節(jié)點的索引行還包含了一個書簽（bookmark），該書簽用來告訴InnoDB哪里可以找到與索引相對應(yīng)的行數(shù)據(jù)。

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

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

然后作者通過分析工作得到輔助索引和聚集索引的關(guān)系圖：

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

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

覆蓋索引（Covering index）

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

使用覆蓋索引有啥好處？

• 可以減少大量的IO操作

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

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

select a from where c = -2;

• 有助于統(tǒng)計

假設(shè)存在如下表：

  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;

如果在該表上執(zhí)行：

select count(*) from student

優(yōu)化器會怎么處理？

遍歷聚集索引和輔助索引都可以統(tǒng)計出結(jié)果，但輔助索引要遠小于聚集索引，所以優(yōu)化器會選擇輔助索引來統(tǒng)計。執(zhí)行explain命令：

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

還有，假設(shè)執(zhí)行以下sql：

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

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

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

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

優(yōu)化器會選擇這個聯(lián)合索引：

聯(lián)合索引

聯(lián)合索引是指對表上的多個列進行索引。

以下為創(chuàng)建聯(lián)合索引idx_a_b的示例：

聯(lián)合索引的內(nèi)部結(jié)構(gòu)：

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

基于上面的結(jié)構(gòu)，對于以下查詢顯然是可以使用（a，b）這個聯(lián)合索引的：

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

select * from table where a=xxx;

但是對于下面的sql是不能使用這個聯(lián)合索引的，因為葉子節(jié)點的b值，1,2,1,4,1,2顯然不是排序的。

select * from table where b=xxx

聯(lián)合索引的第二個好處是對第二個鍵值已經(jīng)做了排序。舉個例子：

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);

當執(zhí)行

select * from buy_log where user_id = 2；

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

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

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

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

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

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

感謝你能夠認真閱讀完這篇文章，希望小編分享的“mysql中聚集索引、輔助索引、覆蓋索引、聯(lián)合索引怎么用”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識等著你來學(xué)習(xí)!