《MySQL技術(shù)內(nèi)幕 InnoDB存儲引擎》學(xué)習(xí)筆記

聚集索引（Clustered Index）

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

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

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

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

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

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

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

輔助索引(Secondary Index)

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

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

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

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

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

以Key為7fffffff為例，7是0111,0代表負(fù)數(shù)，真實(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)計(jì)

假設(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)計(jì)出結(jié)果，但輔助索引要遠(yuǎn)小于聚集索引，所以優(yōu)化器會選擇輔助索引來統(tǒng)計(jì)。執(zhí)行explain命令：

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

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

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

因?yàn)槁?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)合索引是指對表上的多個列進(jìn)行索引。

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

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

聯(lián)合索引也是一棵B+樹，其鍵值數(shù)量大于等于2。鍵值都是排序的，通過葉子節(jié)點(diǎn)可以邏輯上順序的讀出所有數(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)合索引的，因?yàn)槿~子節(jié)點(diǎn)的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);

當(dāng)執(zhí)行

select * from buy_log where user_id = 2；

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

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

時，優(yōu)化器會選擇key(userid, buy_date)，因?yàn)閎uy_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é)果會進(jìn)行一次filesort，即按照buy_date重新排下序。所以聯(lián)合索引的好處在于可以避免filesort排序。

到此這篇關(guān)于mysql聚集索引、輔助索引、覆蓋索引、聯(lián)合索引的使用的文章就介紹到這了,更多相關(guān)聚集索引、輔助索引、覆蓋索引、聯(lián)合索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論