白日夢的MySQL專題（第38篇文章）8分鐘回顧MySQL的索引

公眾號首發-推薦閱讀原文-格式更好看

一、導讀

在MySQL中，不僅為主鍵建立的聚簇索引選用的資料結構是B+Tree，像輔助索引，二級索引、覆蓋索引、聯合索引等等其實都是B+Tree。

二、聚簇索引

MySQL預設為 int 型別的主鍵建立一個聚簇索引。這棵B+Tree是如何設計、如何長高可以參考上一篇文章。

https://mp.weixin.qq.com/s/TwcNEzEWg0PpN0Ra_kiTag

這顆B+Tree之所以叫做聚簇索引是因為它的葉子節點中儲存的是完整的資料行，也就是說你拿著id從這棵樹的根索引上檢索，一直到葉子節點並且定位到特定的資料頁後，你是可以去除完整的資料行來的！（所有列都有）

三、二級索引

二級索引也被大家稱為輔助索引，其實每個索引都是對應一棵獨立的B+Tree，而且他們都有這個特性：後面的資料頁中的索引值均比它前面的資料頁中的索引值大，並且都會通過頁分裂的機制保證這個特性一致成立。

不同的是不同索引的葉子節點中儲存的資料是不一樣的！對於二級索引來說它的葉子節點中儲存的不再是完整的資料行，而是id值。

比如表裡面有 id、age、name、addr四列，且name列是二級索引。然後你的SQL是這樣的

select * from table
where name = 'tom';

那就會先掃描name列這顆B+Tree，找到name=‘tom’所在的葉子節點，葉子節點中儲存的只有name = ‘tom’的這行資料在表中的id值。於是再拿著這個id值去聚簇索引中重新查詢，這個動作我們稱為：“回表”

你可以像下面這樣建立二級索引

CREATE INDEX [index name] ON [table name]([column name]);

或者

ALTER TABLE [table name] ADD INDEX [index name]([column name]);

四、聯合索引

4.1、什麼是聯合索引

聯合索引也叫複合索引，說白了就是多個欄位一起組合成一個索引。

像下面這樣使用 id + title 組合在一起構成一個聯合索引

CREATE TABLE `text` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=3691 DEFAULT CHARSET=utf8

# 或者通過這種方式新增聯合索引
alter table text add INDEX `t3_index_title_content` (`title`,`content`);

如果我們像上圖那樣建立了索引，我們只要保證 id+title 兩者結合起來全域性唯一就ok
建立聯合索引同樣是需要進行排序的，排序的規則就是按照聯合索引所有列組成的字串的之間的先後順序進行排序,，如a比b優先。

4.2、左字首原則

使用聯合索引進行查詢時一定要遵循左字首原則。

什麼是左字首原則呢？

就是說想讓索引生效的話，一定要新增上第一個索引，只使用第二個索引進行查詢的話會導致索引失效。

比如上面建立的聯合索引，假如我們的查詢條件是 where id = '1' 或者 where id = '1' and title = '唐詩宋詞' 索引都會不失效。

但是如果我們不使用第一個索引id，像這樣 where title = '唐詩' ，結果就是導致索引失效。

問：如果我不遵循做字首原則，一定不能使用聚簇索引嗎？

回答：不是的！可以看下面的例子：

# t3表中有3個索引，如下：
# id:聚簇索引
# x1:唯一的二級索引
# x1_x3_x2:聯合索引
explain select * from t3 where  x2 = 'fdc1a9f7d94ece2b68b7d3e3be1b0f3b';

可以看到，x2列沒有單獨的索引。但是sql的執行計劃選擇去聯合索引樹中掃全表，也不會去聚簇索引中全表掃描。

這裡只需要大概看懂這個執行計劃就ok，下一講詳細講！

4.3、聯合索引的分組&排序

還是使用這個例子：

CREATE TABLE `text` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=3691 DEFAULT CHARSET=utf8

demo1: 當我們像下面這樣寫sql時，就會先按照id進行排序。當id相同時再按照title進行排序。

select * form text order by id, title;

demo2: 當我們像下面這樣寫sql時，就會先將id相同的劃分為一組，再將title相同的劃分為一組。

select id,title form text group by id, title;

demo3: ASC和DESC混用, 其實大家都知道底層使用B+樹，本身就是有序的。要是不加限制的話，預設就是ASC。反而是混著使用就使得索引失效。

select * form text order by id ASC, title DESC;

另外補充一點：如果你的group by xxx列，這一列沒有索引時，mysql會 Using temporary 也就是中間表來實現你的分組操作，效率是很低的！而如果有索引的話，直接走索引就可以實現 group by。

五、覆蓋索引

覆蓋索引其實和二級索引沒啥區別，只不過是查詢方式不同而讓它省去了回表的操作而已。

還是這個例子：

比如表結構是這樣的

CREATE TABLE `text` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`title`,`content`)
) ENGINE=InnoDB AUTO_INCREMENT=3691 DEFAULT CHARSET=utf8

然後你的SQL是這樣的

select content from table
where title = 'all in';

你會發現，其實select中期望得到的內容已經全部存在於輔助索引中了，所以不需要再使用id進行回表操作也能得到正確的返回值。

這其實在一定程度上也說明了別總是動不動就select *，能走覆蓋索引儘量使用覆蓋索引。哪怕是不得不進行一次回表操作也儘量使用limit、where條件限制一下！

六、倒排索引

InnoDB中是存在倒排索引和全文檢索的概念的！

MySQL的inverted index同B+Tree索引一樣。另外會使用一張輔助表來儲存單詞和document之間的對映關係。

比如它的倒排索引表長下面這樣：

Number	Text	Documents
1	old	1,4
2	hot	2,5

解讀上表：old這個單詞在document1和doc4中出現過。單詞hot在doc2、doc5中出現過

full inverted index關聯資料長下面這樣

Number	Text	Documents
1	code	(1,4), (2,5)
2	review	(3,5),(5,8)

解讀上表：單詞code在doc1的第4個單詞的位置上出現了。同理單詞review也類似。

但是一般我們一說到全文檢索或者是倒排索引往往都會直觀的想到：Elasticsearch 這款NoSQL

因為InnoDB儲存引擎的全文檢索是存在限制的：

每張表只能有一個全文檢索的索引
由多列組合而成的全文檢索的索引列必須使用相同的字符集
不支援沒有單詞界定符的語言，如：中文、日語、韓語