分享是最有效的學習方式。
部落格：https://blog.ktdaddy.com/
故事
會議室裡，小貓撓著頭，心裡暗暗叫苦著“哎，這程式碼都擼完了呀，改起來成本也太大了。”
原來就在剛才，組長找到了小貓，說程式碼review過程中發現有些資料表模型設計得不合理，要求小貓改掉。小貓大概是設計了一個配置表，為了省事兒，小貓直接把相關的配置設計成了text型別的儲存形式。
關於這種業務場景下使用text文字型別儲存，組長指出了以下缺點：
- 在記憶體中處理Text欄位時，由於需要處理大量資料，可能會導致記憶體使用過度，影響資料庫效能。
- Text欄位無法建立索引，這會導致資料庫在執行查詢時無法利用索引來加速搜尋。雖然可以透過全文索引來改善搜尋效能，但是卻會有諸多限制，例如只能用於InnoDB引擎，並且索引只能建立在不超過1000位元組的字首上。
- 目前剛設計的時候就用text型別，後期隨著資料量的增長以及業務需求的變化，可能就意味著要將text型別繼續擴大變成，LongText或MediumText型別，這樣的轉換既費時又可能需要額外的儲存空間。
“組長說的也有道理，但是為什麼現在才指出來，當時方案模型評審的時候咋提呢，哎，醉了醉了，現在業務邏輯都按照現有方案開發完了，才提出來.....”
“改？要重寫邏輯，不改？萬一今後真的出現上面組長說的這些問題，不得被噴死......”
彷徨過後，小貓終於下定了決心，改了吧，長痛不如短痛...反正如果不改的話，受罪的還是自己。於是加班是少不了了......
資料庫設計
在需求評審完畢之後，一般就是我們的技術方案的設計，在技術方案設計過程中，資料庫模型設計是一個非常重要的環節。資料庫模型的設計往往會影響後續業務邏輯的擴充或者直接影響著研發實際寫程式碼的工作量。甚至會影響幾代研發的維護成本。因此物理資料庫的設計應該是一個非常謹慎以及嚴苛的過程，我們需要步步為營。
那麼我們又當如何去進行資料庫的設計呢？接下來，老貓從我們日常開發中用到比較多的mysql資料庫的設計規範說起。如下。
表設計
關於表設計，咱們從以下幾個方面來看。
1、 表的命名：表命名非常重要，我們要儘量去做到見名知意。
2、 根據實際場景確定引擎，咱們一般業務都會涉及到事務、行級鎖等功能，所以日常開發中包括設計中咱們還是以InnoDB引擎為準。
表設計-命名規範
1、在給相關的表進行命名的時候，表名建議還是以小寫英文字母和0-9數字組成（如果不涉及分表等業務場景，其實很少表名中會帶有數字）以及下劃線組成。雖然mysql在windows下表名不區分大小寫，但是在linux下是區分大小寫的，因此表名最好為小寫。
2、命名需要分類區分對待，當然英文單詞的命名建議使用名詞而不是動詞，另外的話詞義應該要與業務、產品線想關聯。例如我們命名一些配置類表的時候習慣以config打頭，例如config_XXX。當命名臨時表的時候一般tmp打頭，一般為tmp_XXX，當備份表的時候那麼就是bak_XXX。
3、 表命名咱們要用英文，而不是拼音或者是拼音和英文混合。記得大學剛出來的時候，那時候老貓也用拼音命名過一些表，例如設計圖書管理系統的時候居然用上了shu_jia（書架）。現在想想好搓。雖然用到英文，上面提到用名詞，那咱們在用名詞的時候其實最好也是使用單數形式而非複數。例如員工表設計的時候，我們設計成employee而不是employees。
4、 上述還提及表名中包含數字，其實很多時候在我們分庫分表的時候會用到。這裡提及幾個散表命名方式。首先是hash取模散表，咱們表名字尾使用16進位制數，下標從0開始，或者咱們用md5進行散表那麼基友user_0,user_ff等等。當然如果用到時間散表，咱們按照年月分表的時候，咱們會命名成user_202404等諸如此類。
表設計-設計規範
1、表設計的時候一般使用Innodb引擎。當然Mysql存在兩種可選引擎還有一種是MyISAM。MyISAM速度快，但是不支援事務、外來鍵以及行級鎖。反觀Innodb速度稍遜一籌，但是可以支援事務、外來鍵（雖然微服務的場景下，外來鍵很少用了）、行級鎖等高階功能。
2、必須定義主鍵。說到主鍵，咱一般都是Id自增主鍵。有的時候咱們可能也會用到uuid或者Md5或者hash等字串作為主鍵，但是這些列並不能保證資料的順序增長。這裡還是要和大家聊聊這兩種主鍵的優缺點。方便大家後續在做表設計的時候進行取捨。老貓總結了一下，如下圖：
3、表設計必須包含建立時間以及修改時間，用於記錄建立時間和修改時間。
4、表設計的時候不要使用外來鍵，外來鍵影響高併發下的效能，另外的目前我們的大型專案中會涉及到分庫分表，如果遇到外來鍵的話，咱們的分庫分表將會難以實施。
5、慎用觸發器和儲存過程。當然現在咱們應該很少會用到了，老貓只有當年在學習的時候用到了儲存過程，後面實際工作的時候好像就再也沒有接觸過了。觸發器和儲存過程雖然可以減少開發量，另外封裝性也好，比較安全並且不存在SQL隱碼攻擊問題。但是其本身可移植性是非常差的，另外的話佔用伺服器的資源也比較多，一旦發生錯誤，咱們排查問題也比較困難。網際網路領域，我們現在更願意把業務邏輯放到程式碼側，變更會容易一些。
6、不要在建表的時候進行預留欄位，預留欄位命名很難做到見名知意，另外的話及時今後用到，在資料量大的情況下，如果型別不滿足需求，我們去變更型別的時候會導致鎖表。
7、單條記錄的大小不要超過8kb。那麼這又是為什麼呢？首先，咱們從索引角度來看，innodb的頁塊大小預設為16kb，由於innodb採用聚簇索引（B+樹結構）存放資料，每個頁塊中至少有兩行資料，否則就失去了B+樹的意義（如果每個頁中只有一條資料，整個樹就成了一條雙向連結串列）。由於每個頁塊中至少有兩行資料，可以得出一行資料的大小限制為8kb。其次，從硬碟扇區大小的角度來看，單條記錄的大小一般不應該超過硬碟的扇區大小，目前硬碟的扇區大小多為4kb（只有少數是16kb），如果單條記錄過大的話，查詢的時候就會跨越多個扇區，增加尋道時間，可能導致效能下降。
8、單表在設計過程中，咱們最好不要超過50個int欄位、20個char欄位、2個text欄位，另外的話單表列數也要儘量少於50，單表數量咱們也要儘量控制在500w一下，2Gb以內。如果過大的情況下，修改表結構、備份、恢復就有影響，所以當出現太大表的時候，咱們還是儘量要去分庫分表。
欄位設計
欄位設計主要涵蓋兩個方面，一個是欄位的命名，另外一個是欄位的資料型別。咱們接下來詳細看一下。
欄位設計-命名規範
1、和表設計的時候一樣，咱們在欄位命名的過程中也儘量不要使用拼音。
2、在設計欄位的時候，咱們要避免資料庫關鍵字，比如name、time、datetime、desc等等。如果真要用到name的時候，咱們最好加上其他元素以及下劃線進行組成，例如user_name、biz_name等等。
3、欄位表示列舉、狀態型別表示是或者否的時候，咱們最好用is打頭，例如is_member，型別用unsigned tinyint(1-是 0-否) default 0。
欄位設計-設計規範
1、當我們預知當前欄位比較重要，或者之後查詢的時候用到比較多的時候，我們肯定要加上索引，那麼這種欄位，咱們在進行設計的時候就必須定義成Not null，並且設定default值。例如name為非空的，那麼我們的定義可能是name not null default '' comment "命名"。
2、如果欄位涉及小數儲存的時候，我們的欄位型別最好使用bigdecimal型別，而不是float或者是double，float以及double都會存在精度丟失的問題。當然有較真的小夥伴也會說bigdecimal也是有範圍的，那麼如果超過範圍的話，應該怎麼辦？那麼這個時候，其實我們可以將其分開進行儲存，整數和小數拆開。
3、避免使用text或者blob型別儲存大圖片檔案等資訊，這種資訊建議直接儲存到檔案系統，資料庫裡面可以直接儲存對應的檔案系統連結即可。
4、字串型別的，咱們一般使用varchar型別，如果說儲存的字串差不多都是等長的，那麼我們可以將欄位設計成char定長字串型別。另外的，varhcar型別在進行設計的時候咱們要避免設計過長，因為varchar型別在儲存層面是根據實際長度儲存的，但是記憶體分配卻是根據指定長度進行的。所以如果欄位設計不合理會導致記憶體不合理佔用。
5、進行時間設計的時候，如果確定只要年月日，那麼咱們就將欄位設計成date型別。如果說要用到時間戳的話，那麼我們要用到datetime以及timestamp。但是我們要注意這兩者的區別。關於這兩者的區別，老貓再此不做展開，大家有興趣的可以自己查一下。
6、當多個表中都關聯一個欄位的時候，咱們應該要保證這兩個欄位的型別一致，以免在寫程式碼的時候帶來不必要的轉換麻煩。例如tenant_id這個欄位在A表中我們設計成int型別在另外一個地方又設計成了bigint，那麼我們對應的程式碼中可能一個就是int型別另外一個地方就是Long型別。這樣在實際編碼的過程中就要去轉換。
索引設計
聊到索引相信大家都不陌生，索引一般以索引檔案的形式儲存在磁碟上。我們一般所說的索引指的就是B+樹結構組織的索引。
接下來咱們簡單聊一下不同層面的索引的劃分，然後再來聊索引相關的設計規範。
索引的分類
根據儲存型別劃分
聚集索引：在資料庫表中物理順序和主鍵順序一致，即資料行按照主鍵的順序儲存。只要找到第一個索引值記錄，其餘的連續記錄在物理層儲存層面一樣是連續存放。為了使得表記錄和索引的排列順序一致，插入記錄會重新排序，因此修改資料比較慢。
非聚集索引：表記錄和索引的排列順序不一定一致，非聚集索引的葉子層並不和實際資料頁相重疊，而是採用葉子層包含一個指向表記錄的指標。非聚集索引層次多，不會造成資料重排。
關於資料庫的索引的詳細介紹，老貓在此不做展開。後續會有專門的文章和大家分享。
根據邏輯劃分
這塊大家日常應用的過程中應該還是比較常見的。咱們可以分成以下幾種型別。
- 主鍵索引：特殊的唯一索引，不允許有空值。
- 聯合索引：多個欄位上建立的索引，用來提升複合查詢的效率。
- 普通索引：屬於基本索引，沒有其他限制。
- 唯一索引：和普通索引相似，但是值必須唯一，可以用空值，常用來做冪等。
索引命名規範
咱們在給索引命名的時候需要均用英文小寫字母進行命名。
主鍵索引：一般命名用pk_欄位名稱（預設一般都是id索引，在建立表的時候一般就已經指定完成了）
普通索引：咱們命名的時候一般用idx_表名_欄位名稱或者idx_欄位名稱。
唯一索引：一般用uk_表名_欄位名稱或者uk_欄位名稱。
設計規範
1、不是所有的資料庫欄位都適合加索引的。我們在建立索引的時候需要評估欄位的區分度。應該儘量避免將索引建立在區分度低的欄位上。舉個例子，例如性別：男女。還有日常業務中用到的狀態值、或者status-是否標記等等。
2、應當避免在頻繁更新的欄位上建立索引。因為每次變更都會導致B+樹發生變更，頻繁的變更會導致資料庫的效能大大降低。
3、我們需要控制一張表中索引的數量，索引數量並不是越多越好，單表建議控制在5個以內，當然這個也要結合表欄位的總數來定並非絕對。索引建立過多會增加CPU以及IO的開銷。雖然索引可以提高查詢效率，但是同樣會降低插入以及更新的效率。
4、建立聯合索引的時候儘量避免冗餘。例如（a,b,c）聯合索引即相當於(a)、（a,b）、(a、b、c)。另外這裡其實要提到索引的最左匹配原則。當查詢的時候為(a)或者（a,b）或者（a,b,c）的時候才能走到索引。如果查詢是(a,c)那麼其實只能走到(a)索引，這個時候其實需要注意(a)的時候返回的資料量，如果過多的話，其實語句設計就是不合理的。如果查詢是（b,c）則不能走索引。
（面試官也比較喜歡問這類問題）
5、能使用唯一索引的場景，我們應該儘量去使用唯一索引。
6、如果一個欄位的型別是varchar並且此時我們需要去建立相關的索引，我們此時必須要指定相關索引的長度，因為在前文中我們也提到了varchar型別儲存的字串長度往往是不固定的，如果是固定長度的咱們一般用char。我們完全沒有必要對全欄位建立索引，我們只要根據欄位文字的區分度來建立索引即可。如下建立索引語句：
ALTER TABLE users ADD INDEX idx_email (email(10));
總結
當我們接到產品提的相關需求之後，我們就會開始進行相關的技術分析和設計，其中在設計階段就會涉及基本的業務模型的設計。最終就是進行資料模型的設計。此時就會遇到上述的一些資料庫設計的問題。
透過上述一些注意點，相信很多小夥伴應該知道資料表設計階段的一些注意點了。如果小夥伴們還有一些需要補充的，也歡迎大家在評論區留言。分享是一種美德，大家一起進步。