MySQL 資料庫規範--設計篇

目錄

---

1.設計階段
2.開發階段(未開發，explain工具使用)
3.調優階段(未開發，pt_query_digest、show_slow_log、查詢最佳化等)

1.設計階段

1.1 資料庫表的設計正規化(三正規化&反正規化)

---

為什麼需要正規化

優點：程式設計相對簡單，資料量更小，更適合放入記憶體，更新更快，只需要更新少量的資料，
更少的冗餘意味著更少的需要group distinct 之類的操作。

第一正規化

資料表每一列都是不可分割的基本資料項。舉例一個人有多個手機號

第二正規化

資料表裡的所有資料都要和該資料表裡的主鍵有完全相依賴的關係，不能只依賴部分。
舉例：使用者名稱&使用者技能 是主鍵，使用者居住地 ，那麼使用者名稱為主鍵就可以了。

第三正規化

缺點：非鍵屬性都只和候選屬性相關，非間屬性之間沒有關係。舉例冠軍表中冠軍名&冠軍生日。

正規化的缺陷

查詢變得相當複雜，查詢時需要更多的連線join ，一些複合索引的列由於正規化化的需要被分割到不同的表中，導致索引策略不佳。

反正規化

優點：減少了連線，可以更好的利用索引進行篩選和排序，對查詢操作可以提高效能。
缺點：要在資料一致性與查詢之間找到平衡點，符合業務場景的設計才是好的設計

資料庫設計準則

設計的資料庫應該按照使用者可能的訪問路徑，訪問習慣進行設計，而不是嚴格按照資料正規化來設計

1.2 儲存引擎的選擇

---

儲存引擎分類
InnoDB:

1.災難恢復性好
2.支援4中級別的事務，預設事務的隔離級別是Repeatable Read，事務支援是透過MVCC多版本併發控制來提供的。
3.使用行級鎖，併發效能高。
4.使用此儲存引擎的表，資料的物理組織形式是簇表，資料按主鍵來組織，即主鍵索引和資料是在一起的，B+樹就是這樣的
5.實現緩衝管理，能快取索引也能快取資料。
6.支援外來鍵
7.支援熱備份

MyISAM:

1.配合鎖，實現作業系統下的複製備份，遷移
2.使用表記鎖併發性差
3.支援全文索引
4.主機當機後，表容易損壞，災難恢復性不佳
5.無事務支援
6.只快取索引，資料快取利用作業系統緩衝區實現的，引發過多系統呼叫，效能不佳。
7.資料緊湊儲存，可以獲得更快的索引和更快的全表掃描效能。

儲存引擎的選擇:

設計階段我們選用InnoDB儲存引擎作為資料的儲存模式,使用事務、且併發性高，支援外來鍵，支援外來鍵索引。

1.3 字符集選擇

---

字元編碼採用utf-8
字元校驗採用utf-8-cgi

1.4 命名約定

---

規範的必要性P187

1.年前bug就是因為沒有建立索引導致的一系列Bug，所以建立規範，刻不容緩。
2.命名沒有強制約定，但在一個應用中建議風格統一。

命名約定

1.命名有意義，一眼知道這張表是幹什麼用的
2.資料庫，表都用小寫
   資料庫形如：backend
   資料表形如：client_device_info(客戶端裝置資訊)，不要縮寫，字母全小寫
3.索引命名以idx_為字首
4.命名不要過長（應儘量少於25字元）
5.不要使用保留字
6.同一欄位在不同的表中也應是相同的型別和長度
7.同一資料庫下有不同的模組，可以考慮對錶名用不同的字首標識
8.備份表時加上時間標識

1.5 索引設計

---

直接參考

1.6 資料表設計與規劃

---

表設計

1.如果沒有特殊情況，建議選擇InooDB索引
2.每個表都應該有主鍵，可選擇自增自斷，或整形欄位。例外情況，一些應用會頻繁的基於某些自斷進行檢索，設計人員可能認為這些欄位／
  字組合更適合做主鍵，因為更自然、更高效。
3.(不做強制要求)儘量將欄位設定為NOT NULL。因為NULL值的儲存需要額外的空間，且會導致比較運算更為複雜，會使得最佳化器更難以
  最佳化sql。null 值雖然會導致比較運算更加複雜，但這比因此定義了not null帶來應用邏輯異要好。
4.使用更短小的列，比如整型列。整型列的執行速度往往更快。
5.儲存精確浮點數必須使用DECIMAL代替float和double。
6.建議使用unsigned型別儲存非負值
7.建議使用 int unsigned儲存ipv4
8.整型定義中不新增顯示長度的值，使用int，而不是int(4)
9.儘可能不要使用text,blob型別
10.varchar（n） n表示字元數而不是位元組數，比如varchar（255）最大可儲存255個漢字，需根據實際字元長度選擇n的值。
11.字符集建議選擇utf-8
12.儲存年時使用year型別
13.儲存日期時使用date型別
14.儲存時間時，建議使用timestamp型別，因為timestamp使用的是4位元組，datetime使用的是8位元組。
15.不要在資料庫中使用varbinary或blob儲存圖片及檔案，mysql 並不適合大量儲存這型別檔案
16.join 操作的欄位，在不同表中的型別及命名要一致
17.如果更改表結構會影響效能，需要我司後臺(有DBA儘可能找DBA)進行聯合評審。

資料表規劃

 檢視資料表大小的指令碼
 select sum(data_length+index_length) from information_schema.tables where table_schema = ‘app_backend’ and table_name = ‘client_device_info’;
其中data_length是記錄大總大小，index_length 為索引的大小，table_schema 是資料庫名
table_name 是資料表名。

1.7 慎用外來鍵

---

外來鍵的使用

1.外來鍵的優點：
外來鍵約束使得程式設計師更不容易將不一致性引入資料庫，而且設計合適外來鍵有助於以文件方式記錄表間關係。
2.外來鍵的缺點
但這些優點是以伺服器為執行必要的檢查而花費額外的開銷為代價的。伺服器進行額外的檢查會影響效能。
其次外來鍵對併發效能的影響很大，因每次修改資料都需要去另外一個表檢查資料，需要獲取額外的鎖（以確保事務完成之前，父表的記錄不
會被刪除）高併發環境下出現效能問題，更好的辦法是在應用層實現外來鍵約束。