切換語言為:簡體

分析常見SQL會建立幾個索引,如何入手?

  • 爱糖宝
  • 2024-08-28
  • 2053
  • 0
  • 0

最近一直在研究MySQL原始碼,有點走火入魔了,今天還是分享一篇跟MySQL有關的。

大家先猜猜以下SQL會建立幾個索引?

CREATE TABLE t1 (
  id int,
  a varchar(10),
  b varchar(10),
  index idx_b (b),
  UNIQUE KEY unique_a (a)
);

不要往下滑,先自己思考一個答案,併發到評論區。










正確答案是3個,我先分析原因,再給出三種證明方式。

原因分析

首先,以上SQL沒有定義主鍵,因此InnoDB會預設建立一個隱藏主鍵row_id,從而會建立一個主鍵索引,再加上idx_b、unique_a兩個輔助索引,總共3個索引。

是不是和你心裏想得一樣?

但是有同學應該聽說過:如果表裏面有唯一索引,那麼就會用唯一索引當做主鍵索引。

那現在不正好定義了一個唯一索引unique_a嗎?

對對對,但是以上SQL定義的unique_a真的唯一嗎?

比如,我完全可以往表裏插入兩條一模一樣的資料:

  分析常見SQL會建立幾個索引,如何入手?

大家應該能發現了,重點在於unique_a對應的a欄位是可以為null的,這就導致了a欄位其實是可以存多個null的,也就導致unique_a並不是真正唯一的。

從而導致unique_a並不能作為主鍵索引,因為作為主鍵索引的欄位不能為null且得唯一,因此只有a欄位是not null的時候unique_a纔會作為主鍵索引,而此時表中就只有兩個索引了。

所以,我提的這個問題的重點在於a欄位不是not null,所以InnoDB會額外建立一個主鍵索引,從而有三個索引,而一旦a欄位是not null,那麼InnoDB就不需要額外建立主鍵索引了,而是直接用unique_a作為主鍵索引,從而就只有兩個索引了。

接下來證明一下,證明的方式有三種,一種是查INNODB_SYS_INDEXES表、一種是看ibd檔案,一種是看原始碼,不過我們先看show index:

show index from t1;

a欄位不是not null

分析常見SQL會建立幾個索引,如何入手?

a欄位是not null

分析常見SQL會建立幾個索引,如何入手?

???都是兩個索引啊?這是表象, show index是不會把隱藏的主鍵索引查出來的。

INNODB_SYS_INDEXES證明

我們查INNODB_SYS_INDEXES:

select * from information_schema.INNODB_SYS_TABLES where name = 'my_db/t1';
select * from information_schema.INNODB_SYS_INDEXES where table_id = 170;

a欄位不是not null的結果為,3個索引:

  分析常見SQL會建立幾個索引,如何入手? 

a欄位是not null的結果為,2個索引:

  分析常見SQL會建立幾個索引,如何入手?

ibd檔案證明

再看ibd檔案:

a欄位不是not null:

  分析常見SQL會建立幾個索引,如何入手? 

圈起來的是頁號,3、4、5分別表示對應三個索引B+樹的根頁頁號。

a欄位是not null: 

分析常見SQL會建立幾個索引,如何入手? 

會發現確實只有兩個索引B+樹的根頁。

原始碼證明

不感興趣的,可以點贊出門了,謝謝觀看,也歡迎大家關注我的公眾號:IT周瑜,公眾號裡有更多面試題解析、原始碼解析、架構設計的乾貨文章。

最後一種證明方式就是原始碼了,不過原始碼太難,我只貼最關鍵的原始碼: 

分析常見SQL會建立幾個索引,如何入手? 

該原始碼的上下文邏輯為:如果發現表沒有明確的定義PK,那麼就會遍歷定義的每個索引,然後遍歷每個索引定義的欄位,如果發現欄位可以為null,就會直接break出欄位迴圈,接著遍歷下一個索引中的各個欄位。

因此,如果某個唯一索引的欄位都不能null,那麼這個索引就能作為primary_key。

最後,如果primary_key仍然為MAX_KEY,那就表示只能由InnoDB自己建立一個主鍵索引了,比如

  分析常見SQL會建立幾個索引,如何入手? 

如果primary_key_no等於-1表示沒有主鍵,否則表示有主鍵。

如果沒有主鍵就會呼叫create_clustered_index_when_no_primary(),會使用row id來建立一個索引 

分析常見SQL會建立幾個索引,如何入手?

而如果有主鍵,呼叫的就是create_index(),會直接對主鍵欄位建立索引。

  分析常見SQL會建立幾個索引,如何入手?

好啦,一入原始碼深似海,從此頭髮是路人。

0則評論

您的電子郵件等資訊不會被公開,以下所有項目均必填

OK! You can skip this field.