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

賜我白日夢 發表於 2021-06-20
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的執行計劃選擇去聯合索引樹中掃全表,也不會去聚簇索引中全表掃描。

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

這裡只需要大概看懂這個執行計劃就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儲存引擎的全文檢索是存在限制的:

  • 每張表只能有一個全文檢索的索引
  • 由多列組合而成的全文檢索的索引列必須使用相同的字符集
  • 不支援沒有單詞界定符的語言,如:中文、日語、韓語
  1. MySQL的修仙之路,圖文談談如何學MySQL、如何進階!(已釋出)
  2. 面前突擊!33道資料庫高頻面試題,你值得擁有!(已釋出)
  3. 大家常說的基數是什麼?(已釋出)
  4. 講講什麼是慢查!如何監控?如何排查?(已釋出)
  5. 對NotNull欄位插入Null值有啥現象?(已釋出)
  6. 能談談 date、datetime、time、timestamp、year的區別嗎?(已釋出)
  7. 瞭解資料庫的查詢快取和BufferPool嗎?談談看!(已釋出)
  8. 你知道資料庫緩衝池中的LRU-List嗎?(已釋出)
  9. 談談資料庫緩衝池中的Free-List?(已釋出)
  10. 談談資料庫緩衝池中的Flush-List?(已釋出)
  11. 瞭解髒頁刷回磁碟的時機嗎?(已釋出)
  12. 用十一張圖講清楚,當你CRUD時BufferPool中發生了什麼!以及BufferPool的優化!(已釋出)
  13. 聽說過表空間沒?什麼是表空間?什麼是資料表?(已釋出)
  14. 談談MySQL的:資料區、資料段、資料頁、資料頁究竟長什麼樣?瞭解資料頁分裂嗎?談談看!(已釋出)
  15. 談談MySQL的行記錄是什麼?長啥樣?(已釋出)
  16. 瞭解MySQL的行溢位機制嗎?(已釋出)
  17. 說說fsync這個系統呼叫吧! (已釋出)
  18. 簡述undo log、truncate、以及undo log如何幫你回滾事物! (已釋出)
  19. 我勸!這位年輕人不講MVCC,耗子尾汁! (已釋出)
  20. MySQL的崩潰恢復到底是怎麼回事? (已釋出)
  21. MySQL的binlog有啥用?誰寫的?在哪裡?怎麼配置 (已釋出)
  22. MySQL的bin log的寫入機制 (已釋出)
  23. 刪庫後!除了跑路還能幹什麼?(已釋出)
  24. 自導自演的面試現場,趣學資料庫的10種檔案(已釋出)
  25. 大型面試現場:一條update sql執行都經歷什麼?(已釋出)
  26. 大型翻車現場:如何實現記錄存在的話就更新,如果記錄不存在的話就插入。(已釋出)
  27. 視訊+圖文串講:MySQL 行鎖、間隙鎖、Next-Key-Lock、以及實現記錄存在的話就更新,如果記錄不存在的話就插入如何保證併發安全(已釋出)
  28. 自導自演的面試現場:說說char 和 varchar的區別你瞭解多少?。(已釋出)
  29. 自導自演的面試現場之--你竟然不瞭解MySQL的組提交?。(已釋出)
  30. 全網最清楚的:MySQL的insert buffer和change buffer 串講(已釋出)
  31. Double Write並不難理解
  32. 簡述MySQL的三大正規化
  33. 盤點各種登陸資料庫的方式
  34. 面試現場:join聯表的注意點、有哪些聯表查詢方式、真題...
    35.心裡有點樹
    36.心裡有點B樹
    37.聚簇索引到底是怎麼回事?是如何長高的?
    38.8分鐘回顧MySQL的索引