[[356998]]

MySQL數(shù)據(jù)是如何存儲的?

聚集索引

我們先建如下的一張表

CREATE TABLE `student` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '學號', 
  `name` varchar(10) NOT NULL COMMENT '學生姓名', 
  `age` int(11) NOT NULL COMMENT '學生年齡', 
  PRIMARY KEY (`id`), 
  KEY `idx_name` (`name`) 
) ENGINE=InnoDB; 

插入如下sql

insert into student (`name`, `age`) value('a', 10); 
insert into student (`name`, `age`) value('c', 12); 
insert into student (`name`, `age`) value('b', 9); 
insert into student (`name`, `age`) value('d', 15); 
insert into student (`name`, `age`) value('h', 17); 
insert into student (`name`, `age`) value('l', 13); 
insert into student (`name`, `age`) value('k', 12); 
insert into student (`name`, `age`) value('x', 9); 

數(shù)據(jù)如下

圖片mysql是按照頁來存儲數(shù)據(jù)的，每個頁的大小為16k。

在MySQL中可以通過執(zhí)行如下語句，看到一個頁的大小

show global status like 'innodb_page_size' 

結(jié)果為16384，即16kb

在InnoDB存儲引擎中，是以主鍵為索引來組織數(shù)據(jù)的。記錄在頁中按照主鍵從小到大的順序以單鏈表的形式連接在一起。

可能有小伙伴會問，如果建表的時候，沒有指定主鍵呢?

如果在創(chuàng)建表時沒有顯示的定義主鍵，則InnoDB存儲引擎會按如下方式選擇或創(chuàng)建主鍵。

1. 首先判斷表中是否有非空的唯一索引，如果有，則該列即為主鍵。如果有多個非空唯一索引時，InnoDB存儲引擎將選擇建表時第一個定義的非空唯一索引作為主鍵
2. 如果不符合上述條件，InnoDB存儲引擎自動創(chuàng)建一個6字節(jié)大小的指針作為索引

頁和頁之間以雙鏈表的形式連接在一起。并且下一個數(shù)據(jù)頁中用戶記錄的主鍵值必須大于上一個數(shù)據(jù)頁中用戶記錄的主鍵值

假設(shè)一個頁只能存放3條數(shù)據(jù)，則數(shù)據(jù)存儲結(jié)構(gòu)如下。

可以看到我們想查詢一個數(shù)據(jù)或者插入一條數(shù)據(jù)的時候，需要從最開始的頁開始，依次遍歷每個頁的鏈表，效率并不高。

我們可以給這頁做一個目錄，保存主鍵和頁號的映射關(guān)系，根據(jù)二分法就能快速找到數(shù)據(jù)所在的頁。但這樣做的前提是這個映射關(guān)系需要保存到連續(xù)的空間，如數(shù)組。如果這樣做會有如下幾個問題

1. 隨著數(shù)據(jù)的增多，目錄所需要的連續(xù)空間越來越大，并不現(xiàn)實
2. 當有一個頁的數(shù)據(jù)全被刪除了，則相應的目錄項也要刪除，它后面的目錄項都要向前移動，成本太高

我們可以把目錄數(shù)據(jù)放在和用戶數(shù)據(jù)類似的結(jié)構(gòu)中，如下所示。目錄項有2個列，主鍵和頁號。

數(shù)據(jù)很多時，一個目錄項肯定很多，畢竟一個頁的大小為16k，我們可以對數(shù)據(jù)建立多個目錄項目，在目錄項的基礎(chǔ)上再建目錄項，如下圖所示

圖片來自《MySQL 是怎樣運行的：從根兒上理解 MySQL》

這其實就是一顆B+樹，也是一個聚集索引，即數(shù)據(jù)和索引在一塊。葉子節(jié)點保存所有的列值

以 InnoDB 的一個整數(shù)字段索引為例，這個 N 差不多是 1200。這棵樹高是 4 的時候，就可以存 1200 的 3 次方個值，這已經(jīng)17 億了?？紤]到樹根的數(shù)據(jù)塊總是在內(nèi)存中的，一個 10 億行的表上一個整數(shù)字段的索引，查找一個值最多只需要訪問 3次磁盤。其實，樹的第二層也有很大概率在內(nèi)存中，那么訪問磁盤的平均次數(shù)就更少了。《MySQL實戰(zhàn)45講》

非聚集索引

聚集索引和非聚集索引非常類似，區(qū)別如下

聚集索引葉子節(jié)點的值為所有的列值非聚集索引葉子節(jié)點的值為索引列+主鍵

當我們查詢name為h的用戶信息時(學號，姓名，年齡)，因為name上建了索引，先從name非聚集索引上，找到對應的主鍵id，然后根據(jù)主鍵id從聚集索引上找到對應的記錄。

從非聚集索引上找到對應的主鍵值然后到聚集索引上查找對應記錄的過程為回表

聯(lián)合索引/索引覆蓋

假設(shè)teacher表定義如下，在name和age列上建立聯(lián)合索引

CREATE TABLE `teacher` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '教師編號', 
  `name` varchar(10) NOT NULL COMMENT '教師姓名', 
  `age` int(11) NOT NULL COMMENT '教師年齡', 
  `ismale` tinyint(3) NOT NULL COMMENT '是否男性', 
  PRIMARY KEY (`id`), 
  KEY `idx_name_age` (`name`, `age`) 
) ENGINE=InnoDB; 

插入如下sql

insert into teacher (`name`, `age`, `ismale`) value('aa', 10, 1); 
insert into teacher (`name`, `age`, `ismale`) value('dd', 12, 0); 
insert into teacher (`name`, `age`, `ismale`) value('cb', 9, 1); 
insert into teacher (`name`, `age`, `ismale`) value('cb', 15, 1); 
insert into teacher (`name`, `age`, `ismale`) value('bc', 17, 0); 
insert into teacher (`name`, `age`, `ismale`) value('bb', 15, 1); 
insert into teacher (`name`, `age`, `ismale`) value('dd', 15, 1); 
insert into teacher (`name`, `age`, `ismale`) value('dd', 12, 0); 

對name和age列建立聯(lián)合索引

目錄頁由name列，age列，頁號這三部分組成。目錄會先按照name列進行排序，當name列相同的時候才對age列進行排序。

數(shù)據(jù)頁由name列，age列，主鍵值這三部分組成。同樣的，數(shù)據(jù)頁會先按照name列進行排序，當name列相同的時候才對age列進行排序。

當執(zhí)行如下語句的時候，會有回表的過程

select * from student where name = 'aa'; 

當執(zhí)行如下語句的時候，沒有回表的過程

select name, age from student where name = 'aa'; 

為什么不需要回表呢?

因為idx_name_age索引的葉子節(jié)點存的值為主鍵值，name值和age值，所以從idx_name_age索引上就能獲取到所需要的列值，不需要回表，即索引覆蓋

仔細看一下聯(lián)合索引這個圖，你就基本上能明白為什么不滿足最左前綴原則的索引會失效?

索引下推

當執(zhí)行如下語句的時候

select * from student where name like '張%' and age = 10 and ismale = 1; 

在5.6版本之前的執(zhí)行過程如下，先從idx_name_age索引上找到對應的主鍵值，然后回表找到對應的行，判斷其他字段的值是否滿足條件

來自《MySQL實戰(zhàn)45講》

在5.6引入了索引下推優(yōu)化，可以在遍歷索引的過程中，對索引中包含的字段做判斷，直接過濾掉不滿足條件的數(shù)據(jù)，減少回表次數(shù)，如下圖

圖片來自《MySQL實戰(zhàn)45講》

最左前綴原則

加速查詢

主要針對組合索引，滿足如下2個條件即可滿足左前綴原則

需要查詢的列和組合索引的列順序一致

查詢不要跨列

構(gòu)造數(shù)據(jù)如下，其中在name，address，country上建了聯(lián)合索引

CREATE TABLE `people` ( 
  `name` varchar(50) NOT NULL, 
  `address` varchar(50) NOT NULL, 
  `country` varchar(50) NOT NULL, 
  KEY `idx_name_addr_country` (`name`,`address`,`country`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

舉幾個例子，下面涉及到一些explain相關(guān)的知識，后面單開一篇長文來做介紹

例子一

explain select * from people where name = "jack" 
and address = "beijing" and country = "china" 

type為ref，key_len為456=(50*3+2)*3，聯(lián)合索引的所有列都使用了

例子二

explain select * from people where name = "jack" 

type為ref，key_len為152=50*3+2，聯(lián)合索引只使用了name列

例子三

explain select * from people where address = "beijing" 

type為index，表明查詢的時候?qū)φ麄€索引進行了掃描，并沒有加速查找。

假設(shè)有如下的聯(lián)合索引 key idx_a_b_c(a,b,c)

sql	是否使用索引
where a = x and b = x and c = x	是
where a = x and b = x	是，部分索引
where a = x	是，部分索引
where b = x	否，不包含最左列name
where b = x and c = x	否，不包含最左列name

如果你仔細看了前面聯(lián)合索引是如何存儲的，那你一定能看懂是否使用索引的介紹

目錄頁是按照 a b c 列的順序依次遞增排序的。先按照a列排序，如果a列相同，再按照b列排序，如果b列相同，才按照c列排序

所以查詢列值a b c，則這個排序規(guī)則能用到，即會走索引。如果只查列值b，并不能用到這個排序規(guī)則，所以得遍歷所有的記錄

加速排序

最左前綴原則不僅用在查詢中，還能用在排序中。MySQL中，有兩種方式生成有序結(jié)果集：

1. 通過有序索引順序掃描直接返回有序數(shù)據(jù)
2. Filesort排序，對返回的數(shù)據(jù)進行排序

因為索引的結(jié)構(gòu)是B+樹，索引中的數(shù)據(jù)是按照一定順序進行排列的，所以在排序查詢中如果能利用索引，就能避免額外的排序操作。EXPLAIN分析查詢時，Extra顯示為Using index。

所有不是通過索引直接返回排序結(jié)果的操作都是Filesort排序，也就是說進行了額外的排序操作。EXPLAIN分析查詢時，Extra顯示為Using filesort，當出現(xiàn)Using filesort時對性能損耗較大，所以要盡量避免Using filesort

還是先舉2個例子，然后總結(jié)

explain select * from people order by name 

Extra列只有Using index，即根據(jù)索引順序進行掃描

explain select * from people order by address 

在這里插入圖片描述

Extra列有Using filesort

總結(jié)：假如說有如下聯(lián)合索引，key idx_a_b_c(a,b,c)

order by 能使用索引排序

order by a 
order by a,b 
order by a,b,c 
order by a desc, b desc, c desc 
where a = const order by b,c 
where a = const and b = const order by c 
where a = const and b > const order by b,c 

order by 不能使用索引進行排序

order by b 
order by c 
order by b, c 
order by a asc, b desc, c desc //排序不一致 
where g = const order by b,c //丟失a索引 
where a = const order by c //丟失b索引 
where a = const order by a,d //d不是索引的一部分 
where a in (...) order by b,c //范圍查詢 

這個原因就不用我解釋了把，相信你一定看懂了

聯(lián)合索引的好處

索引覆蓋，減少了很多回表的操作，提高了查詢的效率

索引下推，索引列越多，通過索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表，有如下sql:select * from table where col1=1 and col2=2 and col3=3,假設(shè)假設(shè)每個條件可以篩選出10%的數(shù)據(jù)，如果只有單值索引，那么通過該索引能篩選出1000W10%=100w條數(shù)據(jù)，然后再回表從100w條數(shù)據(jù)中找到符合col2=2 and col3= 3的數(shù)據(jù);如果是聯(lián)合索引，通過索引篩選出1000w*10%*10% *10%=1w，效率提升可想而知!

索引為什么會失效?

當別人問我索引在什么條件下會失效時，我能背出一大堆規(guī)則

1. 不要在索引列上進行運算或使用函數(shù)
2. 前導模糊查詢不會使用索引，例如 like %李
3. 負向條件索引不會使用索引，建議用in。負向條件有：!=、<>、not in、not exists、not like 等

索引是按照一定規(guī)則排好序的，如果對索引列使用函數(shù)，或者 like % 李，具體的值都不知道，它怎么在B+樹上加速查詢?

本文轉(zhuǎn)載自微信公眾號「Java識堂」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請聯(lián)系Java識堂公眾號。