去年的時候寫了一篇文章叫Java程式設計師的兩項通用能力，這篇文章我提到對於Java程式設計師而言很重要的兩項通用能力(從程式設計的角度出發)，一是熟練掌握並深入理解Java程式語言，二是資料庫。關於深入理解Java程式語言，後續我會有一個專門的wiki來講這方面的東西，今天主要講資料庫設計方面的。我個人認為資料庫設計是”網際網路軟體三高架構體系(高併發、高可用、高效能)”的根基
一、為什麼說資料庫設計是”網際網路軟體三高架構體系”的根基？
以我這四年多做的SaaS應用為例，都離不開一個共性，即資料的儲存和處理，而資料的儲存就涉及到資料庫。而資料記錄會儲存在對應的表中，而表又是資料庫中的一個組成部分(表由表名、表的欄位、表的記錄等構成)。自下而上看，一個良好的資料庫設計，是由良好的資料表設計決定的，而一個良好的資料表設計，它能夠有效地達到”三降”目的:
- (1)降低儲存成本；
- (2)降低計算成本；
- (2)降低維護成本。
而”三降”對於”網際網路三高架構體系”又顯得至關重要。所以我認為資料庫設計是”網際網路軟體三高架構體系”的根基，沒有這個根基，一切皆為空中樓閣。
二、資料庫設計需要考慮哪些方面？
1.業務層面
從業務層面出發，資料庫的設計必須圍繞業務層面進行思考，自頂而下。這裡的業務層面，主要是需求層面，弄清楚客戶的需求(與自己單幹做外包的原理一樣)，我在這篇文章提到過技術人對賺錢的思考與摸索
，其中一些文字，我用圖片表示:
我認為在做資料庫設計的思考，有以下這麼兩點，可以借鑑:
- 專案一定要調研很細，分清哪些能做，哪些不能做(不確定的不做，需求不明確的不做)；
- 調研需求前，列一個問題清單，面對面與客戶溝通時，把所有疑問拋給客戶(需求不明確或需求疑問得不到解答，直接慧導致很多無用功)。
2.架構層面
有人說，IT架構其實是計算、網路、儲存。其中資料庫就是儲存的主要代表。
資料庫架構也有四個原則(與網際網路三高體系有一定關係):
- 高可用；
- 高效能；
- 一致性；
- 擴充套件性。
從架構層面上看，常見的方案有如下:
- 主備架構(主庫提供讀寫服務，備庫冗餘做故障轉移)；
- 雙主架構(兩個主庫同時提供服務，負載均衡)；
- 主從架構(一主多從，讀寫分離)；
- 雙主+主從架構(具備雙主+主從的特點)。
但每種方案都需要考慮實際情況，能否落地是關鍵，不能落地的方案都是”空中樓閣”。這裡的實際情況指成本高低、與目前的業務是否匹配等。
3.規範層面
(1)阿里巴巴Java開發手冊終極版(1.3.0)
這裡我僅僅列舉三個，分別為建表規約、索引規約、SQL規約(這三個非常重要)。
a.建表規約
表達是與否概念的欄位，必須使用 is_xxx 的方式命名，資料型別是 unsigned tinyint（1 表示是，0 表示否）。
說明：任何欄位如果為非負數，必須是 unsigned。
注意：POJO 類中的任何布林型別的變數，都不要加 is 字首，所以，需要在設定從 is_xxx 到
Xxx 的對映關係。資料庫表示是與否的值，使用 tinyint 型別，堅持 is_xxx 的命名方式是為了明確其取值含義與取值範圍。
表名、欄位名必須使用小寫字母或數字，禁止出現數字開頭，禁止兩個下劃線中間只出現數字。資料庫欄位名的修改代價很大，因為無法進行預釋出，所以欄位名稱需要慎重考慮。
說明：MySQL 在 Windows 下不區分大小寫，但在 Linux 下預設是區分大小寫。因此，資料庫名、表名、
欄位名，都不允許出現任何大寫字母，避免節外生枝。
表名不使用複數名詞。
說明：表名應該僅僅表示表裡面的實體內容，不應該表示實體數量，對應於 DO 類名也是單數形式，符合表達習慣。
禁用保留字，如 desc、range、match、delayed 等，請參考 MySQL 官方保留字。
主鍵索引名為 pk_欄位名；唯一索引名為 uk_欄位名；普通索引名則為 idx_欄位名。
說明：pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的簡稱。
小數型別為 decimal，禁止使用 float 和 double。
說明：在儲存的時候，float 和 double 都存在精度損失的問題，很可能在比較值的時候，得到不正確的
結果。如果儲存的資料範圍超過 decimal的範圍，建議將資料拆成整數和小數並分開儲存。
如果儲存的字串長度幾乎相等，使用 char 定長字串型別。
varchar是可變長字串，不預先分配儲存空間，長度不要超過 5000，如果儲存長度大於此值，定義欄位型別為 text，獨立出來一張表，用主鍵來對應，避免影響其它欄位索引效率。
表必備三欄位：id, create_time, update_time。
說明：其中 id 必為主鍵，型別為 bigint unsigned、單表時自增、步長為 1。create_time, update_time
的型別均為 datetime 型別，前者現在時表示主動式建立，後者過去分詞表示被動式更新。
表的命名最好是遵循“業務名稱_表的作用”。
庫名與應用名稱儘量一致。
如果修改欄位含義或對欄位表示的狀態追加時，需要及時更新欄位註釋。
欄位允許適當冗餘，以提高查詢效能，但必須考慮資料一致。冗餘欄位應遵循：
- 1） 不是頻繁修改的欄位。
- 2） 不是唯一索引的欄位。
- 3） 不是 varchar 超長欄位，更不能是 text 欄位。
單錶行數超過 500 萬行或者單表容量超過 2GB，才推薦進行分庫分表。
說明：如果預計三年後的資料量根本達不到這個級別，請不要在建立表時就分庫分表。
合適的字元儲存長度，不但節約資料庫表空間、節約索引儲存，更重要的是提升檢索速度。
b.索引規約
業務上具有唯一特性的欄位，即使是組合欄位，也必須建成唯一索引。
說明：不要以為唯一索引影響了 insert 速度，這個速度損耗可以忽略，但提高查詢速度是明顯的；另外，
即使在應用層做了非常完善的校驗控制，只要沒有唯一索引，根據墨菲定律，必然有髒資料產生。
超過三個表禁止 join。需要 join 的欄位，資料型別保持絕對一致；多表關聯查詢時，保證被關聯的欄位需要有索引。
說明：即使雙表 join 也要注意表索引、SQL 效能。
在 varchar 欄位上建立索引時，必須指定索引長度，沒必要對全欄位建立索引，根據實際文字區分度決定索引長度。
說明：索引的長度與區分度是一對矛盾體，一般對字串型別資料，長度為 20 的索引，區分度會高達 90%以上，可以使用 count(distinct left(列名,索引長度))/count(*)的區分度來確定。
頁面搜尋嚴禁左模糊或者全模糊，如果需要請走搜尋引擎來解決。
說明：索引檔案具有 B-Tree 的最左字首匹配特性，如果左邊的值未確定，那麼無法使用此索引。
如果有 order by 的場景，請注意利用索引的有序性。order by 最後的欄位是組合索引的一部分，並且放在索引組合順序的最後，避免出現 file_sort 的情況，影響查詢效能。
利用覆蓋索引來進行查詢操作，避免回表。
說明：如果一本書需要知道第 11 章是什麼標題，會翻開第 11 章對應的那一頁嗎？目錄瀏覽一下就好，這
個目錄就是起到覆蓋索引的作用。
利用延遲關聯或者子查詢優化超多分頁場景。
說明：MySQL 並不是跳過 offset 行，而是取 offset+N 行，然後返回放棄前 offset 行，返回 N 行，那當
offset 特別大的時候，效率就非常的低下，要麼控制返回的總頁數，要麼對超過特定閾值的頁數進行 SQL
改寫。
SQL 效能優化的目標：至少要達到 range 級別，要求是 ref 級別，如果可以是 consts最好。
說明：
- 1） consts 單表中最多隻有一個匹配行（主鍵或者唯一索引），在優化階段即可讀取到資料。
- 2） ref 指的是使用普通的索引（normal index）。
- 3） range 對索引進行範圍檢索。
建組合索引的時候，區分度最高的在最左邊。
說明：存在非等號和等號混合判斷條件時，在建索引時，請把等號條件的列前置。如：where c>? and d=?
那麼即使 c 的區分度更高，也必須把 d 放在索引的最前列，即建立組合索引 idx_d_c。
防止因欄位型別不同造成的隱式轉換，導致索引失效。
建立索引時避免有如下極端誤解：
- 1） 索引寧濫勿缺。認為一個查詢就需要建一個索引。
- 2） 吝嗇索引的建立。認為索引會消耗空間、嚴重拖慢記錄的更新以及行的新增速度。
- 3） 抵制惟一索引。認為惟一索引一律需要在應用層通過“先查後插”方式解決。
c.SQL語句
不要使用 count(列名)或 count(常量)來替代 count()，count()是 SQL92 定義的標準統計行數的語法，跟資料庫無關，跟 NULL 和非 NULL 無關。
說明：count(*)會統計值為 NULL 的行，而 count(列名)不會統計此列為 NULL 值的行。
count(distinct col) 計算該列除 NULL 之外的不重複行數，注意 count(distinct col1,col2) 如果其中一列全為 NULL，那麼即使另一列有不同的值，也返回為 0。
當某一列的值全是 NULL 時，count(col)的返回結果為 0，但 sum(col)的返回結果為NULL，因此使用 sum()時需注意 NPE 問題。
使用 ISNULL()來判斷是否為 NULL 值。
說明：NULL 與任何值的直接比較都為 NULL。
1） NULL<>NULL 的返回結果是 NULL，而不是 false。
2） NULL=NULL 的返回結果是 NULL，而不是 true。
3） NULL<>1 的返回結果是 NULL，而不是 true。
程式碼中寫分頁查詢邏輯時，若 count 為 0 應直接返回，避免執行後面的分頁語句。
不得使用外來鍵與級聯，一切外來鍵概念必須在應用層解決。
說明：（概念解釋）學生表中的 student_id 是主鍵，那麼成績表中的 student_id 則為外來鍵。如果更新學
生表中的 student_id，同時觸發成績表中的 student_id 更新，即為級聯更新。外來鍵與級聯更新適用於單機
低併發，不適合分散式、高併發叢集；級聯更新是強阻塞，存在資料庫更新風暴的風險；外來鍵影響資料庫
的插入速度。
禁止使用儲存過程，儲存過程難以除錯和擴充套件，更沒有移植性。
資料訂正（特別是刪除或修改記錄操作）時，要先 select，避免出現誤刪除，確認無誤才能執行更新語句。
對於資料庫中表記錄的查詢和變更，只要涉及多個表，都需要在列名前加表的別名（或表名）進行限定。
說明：對多表進行查詢記錄、更新記錄、刪除記錄時，如果對操作列沒有限定表的別名（或表名），並且操作列在多個表中存在時，就會拋異常。
SQL 語句中表的別名前加 as，並且以 t1、t2、t3、…的順序依次命名。
說明：
- 1）別名可以是表的簡稱，或者是依照表在 SQL 語句中出現的順序，以 t1、t2、t3 的方式命名。
- 2）別名前加 as 使別名更容易識別。
in 操作能避免則避免，若實在避免不了，需要仔細評估 in 後邊的集合元素數量，控制在 1000 個之內。
因國際化需要，所有的字元儲存與表示，均採用 utf8 字符集，那麼字元計數方法需要注意。
說明：
SELECT LENGTH(“輕鬆工作”)； 返回為 12
SELECT CHARACTER_LENGTH(“輕鬆工作”)； 返回為 4
如果需要儲存表情，那麼選擇 utf8mb4 來進行儲存，注意它與 utf8 編碼的區別。
TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日誌資源少，但 TRUNCATE無事務且不觸發 trigger，有可能造成事故，故不建議在開發程式碼中使用此語句。
說明：TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同。
(2)各博友總結概要
a.沒有完美的資料庫設計，只有符合業務的資料庫設計
b.在遵守資料庫設計三大正規化的同時，也要考慮使用者體驗
c.資料庫設計時必須要做的兩件事情(需求分析、考慮需求可能的變更)
d.資料庫設計注意事項
- 使用者輸入不能作為主鍵；
- 凡是有業務意義的不能作為主鍵；
- 除非是確定非空欄位，其他允許為空(約束簡單原則)；
- 不能缺少時間或日期欄位，並預留備註欄位；
- 業務時效性；
- 資料採集(寧濫勿缺)；
- 預先計劃(考慮使用者誤操作，非物理刪除與物理刪除)。
e.欄位是否允許為空(結合業務思考)
f.表增加欄位不要採用drop table或create table，最好用alter(保留原來的資料)。
g.多表操作必須使用事務
h.資料庫命名
- 使用名詞作為表名；
- 相關表採用統一的字首。
i.欄位的命名
- 根據動詞被動形式+描述性字尾；
- 時間區分當前和未來；
- 使用第三人稱複數；
- 區分單數和複數。
j.充分利用工具
- Power Design；
- Navicat Data Modeler。
k.獲取資料模式手冊
l.做新的資料庫設計時，要記住過去的教訓，古為今鑑
m.在物理實踐之前進行邏輯實現
n.建立資料字典和ER圖表
o.有意義的欄位命名
p.標準化和資料驅動
q.標準化不能過頭
r.小心保留字
s.仔細選擇數字型別
t.避免使用觸發器
u.文字欄位留足量
v.採用檢視
w.用儲存過程讓系統做活
x.建立模式
y.報表技巧
z.檢查各種變化
(3)我對規範層面的感受
從外包公司到至今，越來越體會到規範化的重要意義。從資料庫設計到編碼，簡直是一部血淚史。當年在某創業公司的時候，除了寫程式碼隨意之外，對於資料庫設計更是隨意，為了功能更好實現，給資料庫對應的表不斷增加欄位，最終造成大量冗餘，資料庫欄位冗餘，對應的Java程式碼也是如此。而後到某教育Saas公司才明白資料表不可輕易加欄位，需要從需求層面考慮，而不是從自身功能實現難易考慮。