資料庫，主鍵為何不宜太長長長長長長長長？

58沈劍發表於2019-09-30
資料庫

回答星球水友提問：

沈老師，我聽網上說，MySQL資料表，在資料量比較大的情況下，主鍵不宜過長，是不是這樣呢？這又是為什麼呢？
 
這個問題嘛，不能一概而論：
（1）如果是InnoDB儲存引擎，主鍵不宜過長；
（2）如果是MyISAM儲存引擎，影響不大；
 
先舉個簡單的栗子說明一下前序知識。
 
假設有資料表：

t(id PK, name KEY, sex, flag);

 
其中：
（1）id是主鍵;
（2）name建了普通索引;
 
假設表中有四條記錄：

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

 
如果儲存引擎是MyISAM，其索引與記錄的結構是這樣的：

資料庫，主鍵為何不宜太長長長長長長長長？

（1）有單獨的區域儲存記錄(record)；
（2）主鍵索引與普通索引結構相同，都儲存記錄的指標（暫且理解為指標）；
畫外音：
（1）主鍵索引與記錄不儲存在一起，因此它是非聚集索引(Unclustered Index)；
（2）MyISAM可以沒有PK；
 
MyISAM使用索引進行檢索時，會先從索引樹定位到記錄指標，再透過記錄指標定位到具體的記錄。
畫外音：不管主鍵索引，還普通索引，過程相同。
 
InnoDB則不同，其索引與記錄的結構是這樣的：

資料庫，主鍵為何不宜太長長長長長長長長？

（1）主鍵索引與記錄儲存在一起；
（2）普通索引儲存主鍵（這下不是指標了）；
畫外音：
（1）主鍵索引與記錄儲存在一起，所以才叫聚集索引(Clustered Index)；
（2）InnoDB一定會有聚集索引；
 
InnoDB透過主鍵索引查詢時，能夠直接定位到行記錄。
 

資料庫，主鍵為何不宜太長長長長長長長長？

但如果透過普通索引查詢時，會先查詢出主鍵，再從主鍵索引上二次遍歷索引樹。
 
迴歸正題，為什麼InnoDB的主鍵不宜過長呢？
 
假設有一個使用者中心場景，包含身份證號，身份證MD5，姓名，出生年月等業務屬性，這些屬性上均有查詢需求。

最容易想到的設計方式是：

  • 身份證作為主鍵

  • 其他屬性上建立索引

user(id_code PK,
id_md5(index),
name(index),
birthday(index));

 

資料庫，主鍵為何不宜太長長長長長長長長？

此時的索引樹與行記錄結構如上：

  • id_code聚集索引，關聯行記錄

  • 其他索引，儲存id_code屬性值

 
身份證號id_code是一個比較長的字串，每個索引都儲存這個值，在資料量大，記憶體珍貴的情況下，MySQL有限的緩衝區，儲存的索引與資料會減少，磁碟IO的機率會增加。
畫外音：同時，索引佔用的磁碟空間也會增加。
 
此時，應該新增一個無業務含義的id自增列：

  • 以id自增列為聚集索引，關聯行記錄

  • 其他索引，儲存id值

user(id PK auto inc,
id_code(index),
id_md5(index),
name(index),
birthday(index));

 

資料庫，主鍵為何不宜太長長長長長長長長？

如此一來，有限的緩衝區，能夠緩衝更多的索引與行資料，磁碟IO的頻率會降低，整體效能會增加。
 
總結
（1）MyISAM的索引與資料分開儲存，索引葉子儲存指標，主鍵索引與普通索引無太大區別；
（2）InnoDB的聚集索引和資料行統一儲存，聚集索引儲存資料行本身，普通索引儲存主鍵；
（3）InnoDB不建議使用太長欄位作為PK（此時可以加入一個自增鍵PK），MyISAM則無所謂；

希望解答了這位水友的疑問。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31556838/viewspace-2658871/，如需轉載，請註明出處，否則將追究法律責任。

相關文章