《Mysql設計規范》- 資料庫命名規范 / 資料庫基本設計規范 / 索引設計規范 / 欄位設計規范 / SQL開發規范 / 資料庫操作行為規范

一、資料庫命名規范

1. 所有的資料庫物件名稱必須使用小寫字母并用下劃線表示，因為默認情況下，mysql 對大小寫敏感， mysql 資料庫本質上是 linux 系統下的一個檔案，而 linux 系統是大小寫敏感的，
2. 所有資料庫物件名稱禁止使用 mysql 保留關鍵字，
3. 資料庫物件的命名要能做到見名知意，并且最好不要超過 32 個字符，太長不方便使用，并且會在傳輸時增加網路開銷，
4. 臨時表必須以 tmp_ 為前綴并以日期為后綴，
5. 備份表必須以 bak_ 為前綴并以日期為后綴，
6. 所有存盤相同資料的列名和列型別必須一致，比如 user 表中的 id 和 order 表中的 user_id，

二、資料庫基本設計規范

1. 所有表必須使用 Innodb 存盤引擎，極少數特殊業務需求除外，Innodb 引擎是 5.6 之后的默認存盤引擎，mysql 5.5 之前使用 Myisam(默認存盤引擎)，Innodb 優點：支持事務，行級鎖，更好的恢復性，高并發下性能更好，
2. 資料庫和表的字符集統一使用 UTF-8，如果要存盤一些如表情符號的，還需使用 UTF-8 的拓展字符集，資料庫，表，欄位字符集一定要統一，統一字符集可以避免由于字符集轉換產生的亂碼，在 mysql 中 UTF-8 字符集，漢字占 3 位元組，ASCII 碼占 1 位元組，
3. 所有表和欄位都需要添加注釋，良好的注釋能大大減少時間成本，
4. 從一開始就進行資料字典的維護 ，即資料庫說明檔案，
5. 盡量控制單表資料量大小，建議控制在 500 萬以內，雖然 500 萬并不是 mysql 的資料庫限制，但是會給修改表結構，備份，恢復帶來很大困難，單表可存盤資料量大小取決于存盤設定和檔案系統，想減少單表資料量：歷史資料歸檔(常見于日志表)，分庫分表(常見于業務表)，磁區表，建議不要使用 mysql 磁區表，因為磁區表在物理上表現為多個檔案，在邏輯上表現為一個表，如果一定要磁區，請謹慎選擇磁區鍵，跨磁區查詢效率比查詢大資料量的單表查詢效率更低，建議采物理分表的方式管理大資料，但是對應用程式的開發要求和復雜度更高，
6. 盡量做到冷熱資料分離，減少表的寬度(欄位數) ，減少磁盤 IO，保證熱資料的記憶體快取命中率，更有效的利用快取，避免讀入無用的冷資料，這樣的話，就要對表的列進行拆分，將經常使用的列放到一個表中，可以避免過多的關聯操作，也可以提高查詢性能，
7. 禁止在表中建立預留欄位 ，預留欄位很難做到見名知義，預留欄位無法確定存盤的資料型別，后期如果修改欄位型別，會對全表鎖定，嚴重影響資料庫的并發性，對目前 mysql 來說，修改一個欄位的成本要遠遠大于增加一個欄位的成本，
8. 禁止在資料庫中存盤圖片，檔案等二級制資料，這類資料如果要存，就得使用 blog 或者 text 這樣的大欄位加以存盤，會影響資料庫的性能，檔案這種通常所占資料容量很大，會在短時間內造成資料庫檔案的快速增長，而資料庫在讀取資料時，會進行大量的隨機 IO 操作，如果資料檔案過大，IO 操作會非常耗時，從而影響資料庫性能，正確做法是將這類資料存盤在檔案服務器中，而資料庫只存盤地址資訊，
9. 禁止在線上做資料庫壓力測驗，會對正常業務造成影響，也會產生很多垃圾資料，建議建立專門的壓力測驗資料庫，進行測驗，然后對比測驗服務器和線上服務器的硬體環境，評估線上資料庫的性能，
10. 禁止從開發環境，測驗環境直連生產環境資料庫，

三、索引設計規范

Innodb 中主鍵實質上是一個索引，

1. 限制每張表上索引數量，建議單表不超過 5 個索引，索引并不是越多越好，可以提高查詢效率，但是會降低插入和更新的效率，甚至在一些情況下，還會降低查詢效率，因為 mysql 優化器在選擇如何優化查詢時，會根據統計資訊，對每一個可用索引來進行評估，以生成一個最好的執行計劃，如果同時有很多索引都可以用于查詢，就會增加 mysql 查詢優化器生成查詢計劃的時間，
2. 每個 Innodb 表都必須有一個主鍵，Innodb 是一種索引索引組織表，是指資料存盤的邏輯順序和索引的順序是相同，Innodb 是按照主鍵索引的順序來組織表的，因此，每個 Innodb 表都必須要有一個主鍵，如果我們沒有指定主鍵，那么 Innodb 會優先選擇表中第一個非空唯一索引來作為主鍵，如果沒有這個索引，那么 Innodb 會自動生成一個占 6 位元組的主鍵，而這個主鍵的性能并不是最好，
3. 不使用更新頻繁的列作為主鍵，不使用多列聯合主鍵，因為 Innodb 是一種索引索引組織表，如果主鍵上的值頻繁更新，就意味著資料存盤的邏輯順序頻繁變動，必然會帶來大量的 IO 操作，降低資料庫性能，
4. 不要使用 uuid，md5，hash，字串列作為主鍵，因為這種主鍵不能保證主鍵的值是順序增長的，如果后來的主鍵值在已有主鍵值的中間段，那么這個主鍵插入的時候，會將所有主鍵值大于它的列都向后移，
5. 最好選擇能保證值的順序為順序增長的列為主鍵，并且資料不能重復，建議用 mysql 自增 id 建立主鍵，
6. 避免建立冗余索引和重復索引，
7. 對于頻繁的查詢優先使用覆寫索引，就是包含了所有查詢欄位的索引，這樣可以避免 Innodb 表進行索引的二次查找，并可以把隨機 IO 變為順序 IO 提高查詢效率，
8. 盡量避免使用外鍵，mysql 和別的資料庫不同，會自動在外鍵上建立索引，會降低資料庫的寫性能，
9. 建議不使用外鍵約束，但是一定要在表與表之間的關聯鍵上建立索引，雖然外鍵是為了保證資料的完整性，但是最好在代碼中去保證，

四、欄位設計規范

1. 優先選擇符合存盤需要的最小的資料型別，盡量將字串轉化為數字型別存盤：如將 ip 存盤為數字：inet_aton(‘255.255.255.255’) = 4294967295 ，反之 inet_ntoa(4294967295) = ‘255.255.255.255’，
2. 對于非負整型資料，優先使用無符號整型來存盤，如：id,age，無符號相對于有符號，可以多出一倍的存盤空間，
3. mysql 中，varchar(n) 中 n 表示字符數而不是位元組數，
4. 避免使用 text，blog 來存盤欄位，這種型別只能使用前綴索引，如果非要使用，建議將這種資料分離到單獨的拓展表中，
5. 避免使用 enum 型別，列舉本身是一個字串型別，但是內部確是用正數型別來存盤的，所以最多可存盤 65535 種不同的值，修改的話必須使用 alter 陳述句，直接修改元資料，有操作風險；order by 效率低，必須轉換并無法使用索引，禁止使用數值作為 enum 值，因為 enum 本身是索引順序存盤的，會造成邏輯混淆，
6. 盡可能把所有列定義為 not null ，索引 null 列需要額外的空間來保存，占更多空間，進行比較和計算時，對 null 值作特別的處理，可能造成索引失效，
7. 禁止使用字串來存盤日期型資料，無法使用日期函式計算比較，字串存盤要占更多的記憶體空間，datetime(8位元組) 和 timestamp (本身是以 int 存盤，占 4 位元組,范圍: 1970-01-01 00:00:01到2038-01-19 03:14:07)
8. 財務相關資料，使用 decimal 型別 (精準浮點型別，在計算時不丟失精度)，

五、SQL開發規范

1. 建議使用預編譯陳述句(prepareStatment)進行資料庫操作，可以同步執行預編譯計劃，減少預編譯時間，可以有效避免動態 sql 帶來的 SQL 注入的問題，只傳引數，一次決議，多次使用，比傳遞 sql 陳述句更高效，
2. 避免資料型別的隱式轉換，一般出現在 where 從句中，會導致索引失效，如：select id,name from user where id = ‘12’;，
3. 充分利用已存在的索引，避免使用雙 % 的查詢條件，不走索引，一個 SQL 只能利用到復合索引中的一列進行范圍查詢，使用 left join 或 not exists 來優化 not in 操作，
4. 程式連接不同的資料庫使用不同的賬號，禁止跨庫查詢，為資料庫遷移和分庫分表留出余地，降低業務耦合度，避免權限過大而產生的安全風險，
5. 禁止使用 select * 來查詢，必須用欄位名，可能會消耗更多的 cpu 和 IO 以及網路資源，無法使用覆寫索引，可以減少表結構變更對已有程式的影響，
6. 禁止使用不含欄位串列的 insert 陳述句，可以減少表結構變更對已有程式的影響，
7. 禁止使用子查詢，雖然可使 sql 可讀性好，但是缺點遠遠大于優點，子查詢回傳的結果集無法使用索引，結果集會被存盤到一個臨時表中，結果集越大性能越低，把子查詢優化為 join 操作，但是并不是所有的都可以優化為join ，一般情況下，只有當子查詢是在 in 字句中，并且子查詢是一個簡單的 sql(不包含union，group by，order by，limit) 才能轉換為關聯查詢，
8. 避免 join 過多的表，每 join 一個表會占一部分記憶體(join_buffer_size)，會產生臨時表操作，影響查詢效率，mysql 最多允許關聯 61 個表，建議不超過 5 個，
9. 減少同資料庫的互動次數，資料庫更適合處理批量操作，合并多個相同的操作到一起，提高處理效率，
10. 使用 in 代替 or，in 的值不要超過 500 個，in 操作可以有效利用索引，
11. 禁止使用 order by rand() 進行隨機排序，會把表中所有符合條件的資料裝載到記憶體中進行排序，會消耗大量的 cpu 和 io 及記憶體資源，推薦在程式中獲取隨機值，
12. 禁止在 where 從句中對列進行函式轉換和計算，導致無法使用相關列上的索引，where date(create_time)=’20170901’ 寫成 where create_time >= ‘20170901’ and create_time < ‘20170902’，
13. 在明顯不會有重復值時使用 union all 而不是 union ，union 會把所有資料放在臨時表中后再進行去重操作，會多消耗記憶體，IO，網路資源，union all 不會再對結果集進行去重操作，
14. 拆分復雜的大 sql 為多個小 sql，目前 mysql 中一個 sql 只能使用一個cpu 計算，不支持多 cpu 并行計算，sql 拆分后可以通過并行執行來提高處理效率，

六、資料庫操作行為規范

主要面向手動操作資料庫的行為，

1. 超過 100 萬的批量寫操作，要分批多次進行操作，主從復制中：大批量操作可能會造成嚴重的主從延遲，因為當主庫執行完成后，才會在從庫執行， binlog 日志為 row 格式時會產生大量的日志，避免產生大量事務，產生阻塞，占滿可用連接，
2. 對大表資料結構的修改一定要謹慎，可能會造成嚴重的鎖表操作，尤其是生產環境，是不能忍受的，對于大表使用 pt-online-schema-change 修改表結構，首先會建立一個與原表結構相同的新表，然后在新表上進行表結構的修改，然后把原表中的資料復制到新表中，并且增加一些觸發器，以便把原表中即時新增的資料也復制到新表中，在行的所有資料復制完成之后，會在原表上增加一個很準的時間鎖，同時把新表命名為原表，把原表刪掉，實際上是把一個原子的 DDL 操作分解成多批次進行，避免大表修改產生的主從延遲問題，避免在對表欄位進行修改時進行鎖表，
3. 禁止為程式使用的賬號賦予 super 權限 ，當資料庫連接數達到最大限制時，允許 1 個有 super 權限的用戶連接，super 權限只能留給 DBA 處理問題的賬號使用，
4. 對于程式連接資料庫賬號，遵循權限最小原則，程式使用的資料庫賬號只能在一個 DB 下使用，不準跨庫，程式使用的賬號原則上不準有 drop 權限，