資料庫聚簇索引——not null條件對唯一鍵索引成為聚簇索引的影響

pidanhub發表於2024-05-27
資料庫索引Null

資料庫聚簇索引的規則如下：

  • 如果有主鍵，則主鍵是聚簇索引（當然主鍵也不一定是單個列的）；
  • 如果沒有主鍵，但是有not null修飾的唯一鍵索引，則這個索引是聚簇索引；
  • 都沒有，建立一個叫db_row_id的6位元組隱藏列為聚簇索引，這個索引程式設計師不可見（這個列存在一定衝突和效能問題，詳見大佬：https://www.cnblogs.com/frankcui/p/15226301.html）。

所以一般還是要定義主鍵。如果不定義主鍵，再想透過建立索引的方式提高效能，那無論建立多少個索引，本質上都是二級索引，都要進行回表（本來一個主鍵就可以解決的事情，現在仍舊有一個聚簇索引，還額外多了那個原來定義主鍵就可以建立的索引，反而佔用空間）。同樣的查詢，IO次數變多，這是不值當的。

那有沒有not null是否真的影響unique列成為聚簇索引？

看起來我們無法直接從explain中找到是否走聚集索引的資訊，官方文件中介紹到，extra欄位中大抵只是看是否覆蓋，而聚集索引是一個“索引即資料”的概念，它就是完整的表了，即便沒有覆蓋掉所有查詢的列，也是真正覆蓋了，所以使用聚集索引的extra欄位為null。官方文件：

https://www.cnblogs.com/kerrycode/p/9909093.html

Using index (JSON property: using_index)

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY.

從表中僅使用索引樹中的資訊就能獲取查詢語句的列的資訊, 而不必進行其他額外查詢（seek）去讀取實際的行記錄。當查詢的列是單個索引的部分的列時, 可以使用此策略。（簡單的翻譯就
是：使用索引來直接獲取列的資料，而不需回表）。對於具有使用者定義的聚集索引的 InnoDB 表, 即使從Extra列中沒有使用索引, 也可以使用該索引。如果type是index並且Key是主鍵, 則會出現這種情況。

翻譯翻譯就是你用主鍵的聚簇索引，可能看起來沒有覆蓋，但是實際上所有資料都在這裡了，預設就是覆蓋了。

那怎麼判斷是否走了聚簇索引呢？就算有唯一鍵的聚簇索引，用了還是不知道的。我有這樣一個思路：

如果唯一鍵可以為null時，按照理論不應該是聚簇索引，當查詢的列有的不在唯一鍵索引覆蓋範圍內則應該會有回表（回聚集索引去查），即可以從某些方面佐證發生回表，如索引下推。接下來的目的就是尋找這個證據。

3309036-20240527213243861-1279717842

準備兩張表，分別加上not null（按理論為聚簇索引）和default null（可以為空）修飾：

CREATE TABLE `test_unique` (
  `id` int NOT NULL,
  `col1` varchar(255),
  `col2` varchar(255) NOT NULL,
  UNIQUE KEY `uni` (`id`) USING BTREE COMMENT '測試沒有主鍵的情況下，唯一鍵是不是聚簇索引與not null的關係'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `test_unique` (
  `id` int DEFAULT NULL,
  `col1` varchar(255),
  `col2` varchar(255) NOT NULL,
  UNIQUE KEY `uni` (`id`) USING BTREE COMMENT '測試沒有主鍵的情況下，唯一鍵是不是聚簇索引與not null的關係'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

準備資料：

分別執行這樣一個語句：

explain select id, col1 from test_unique where id in (89,98);

結果：

區別就在於Using index condition，找到對這個值的解釋：https://www.cnblogs.com/echoppy/p/14247575.html

很幸運得到這個結果，我的mysql的版本支援索引下推，也從側面佐證了“當唯一鍵索引為null時，並不能成為聚簇索引，否則就直接查出所有的值，不發生下推”。到此證明結束。

另：

當我在試圖尋找佐證時，有一個現象引起了我的好奇：嘗試用這樣一個語句去判斷是否走索引：

explain select id, col1 from test_unique where id > 23;

注意到唯一鍵索引可以為空時，範圍查詢會使索引失效：

NOT NULL時，可以發現範圍查詢走了索引，此時的唯一鍵索引與主鍵無二。

小結

實驗本身的目的不在於試圖推翻現在的結論，而是對現有的結論多一些直觀的認識，並透過實踐的形式對感興趣的概念加深認識和理解。本文涉及的知識點有索引以及

相關文章