MySQL 聚簇索引一定是主鍵嗎

cheer發表於2020-06-05
MySql索引

資料庫表的索引從資料儲存方式上可以分為聚簇索引和非聚簇索引（又叫二級索引）兩種。

use myTest;                 

drop table if exists t1; 

CREATE TABLE `t1` (          
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(20) DEFAULT NULL,
  `b` int(20) DEFAULT NULL,
  `c` int(20) DEFAULT NULL,
  `d` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_a_b` (`a`,`b`),
  KEY `idx_c` (`c`)
) ENGINE=InnoDB CHARSET=utf8mb4 ;

- 什麼是聚簇索引

先說說聚簇索引，聚簇索引有兩個明顯特徵：

  1. 索引必須為唯一索引
  2. 葉子節點處儲存的是整行資料（這也是為何用主鍵（聚簇索引）進行查詢時，查詢速度會較快的原因（減少回表查詢））

這也是為何在新建資料表時，會推薦建立自增id作為主鍵，同時id自增確保業務層面上的無意義。==確保主鍵ID業務上無意義很重要，因為它確保你再已生成記錄的主鍵ID，是不會被update的==。如果是隨機主鍵或者頻繁更新主鍵的話,就會存在資料頁頻繁斷裂，B+樹不飽和的情況（原因：聚簇索引是按順序進行排序的）。而如果設定主鍵是自增，那麼每一次都是在聚集索引的最後增加，當一頁寫滿，就會自動開闢一個新頁，不會有聚集索引樹分裂這一步，效率會比隨機主鍵高很多。這也是很多建表規範要求主鍵自增的原因。

- 什麼是非聚簇索引

非聚簇索引有兩個明顯特徵：

  1. 索引值必須為可不唯一
  2. 葉子節點處儲存的索引行跟主鍵（key+pk cols）

輔助索引的葉子節點，儲存主鍵值，而不是整行資料，這樣的好處有：
1.減少儲存資料，降低了輔助索引的樹所佔的儲存空間
2.聚簇索引列不變的情況下，非聚簇索引列改變不影響輔助索引的原本結構

image.png

- 聚簇索引一定是主鍵嗎

有人會疑慮似乎聚簇索引一定會是主鍵，那如果資料表不建立主鍵的話是否就沒有聚簇索引了？
在 InnoDB 中，聚集索引不一定是主鍵，但是主鍵一定是聚集索引：原因是如果沒有定義主鍵，聚集索引可能是第一個不允許為 null 的唯一索引，如果也沒有這樣的唯一索引，InnoDB 會選擇內建 6 位元組長的 ROWID 作為隱含的聚集索引。
InnoDB 的資料是按照主鍵順序存放的，而聚集索引就是按照每張表的主鍵構造一顆 B+ 樹，它的葉子節點存放的是整行資料。
每張 InnoDB 表都有一個聚集索引，但是不一定有主鍵。

- 使用非聚簇索引查詢資料時，一定要回表查詢嗎？

回表查詢是由於非聚簇索引的B+Tree葉子節點只記錄了，PK cols與key。這就造成當查詢欄位非主鍵跟索引欄位時，索引樹無法直接提供所有資料列，從而要進行一次回表查詢，也就是拿著主鍵ID再進行一次查詢操作。
例如t1表

select * from t1 where c = 1; #sql_1
select id,c from t1 where c = 1; #sql_2

當查詢被非聚簇索引涵蓋列時，則不需要走回表操作。即sql_2不需要走回表查詢，sql_1需要。

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章