數據庫

數據庫,主鍵為何不宜太長長長長長長長長?

回答星球水友提問:沈老師,我聽網上說,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則無所謂;

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

我還沒有學會寫個人說明!

可能是東半球最好的 Curl 學習指南,強烈建議收藏!

上一篇

單點登錄系統原理與實現,圖文并茂,附源碼

下一篇

你也可能喜歡

數據庫,主鍵為何不宜太長長長長長長長長?

長按儲存圖像,分享給朋友

ITPUB 每周精要將以郵件的形式發放至您的郵箱


微信掃一掃

微信掃一掃
30岁的男人干啥赚钱快赚钱多 彩票论坛赚钱 赛车快速 浙江体彩6 1开奖查询官网 一期一码最准期期中 怎样是公式算一肖 上市公司公开发行股 云南星悦麻将 时时彩开奖赛车微彩app 文化长城股票股吧 体育彩票大乐透玩法