一、數據庫命名規范

A、對象名稱使用小寫字母、下劃線分割

• Windows默認情況下無法建立大寫庫名
• Linux大小寫敏感，MySQL數據文件（庫名、表名、表別名嚴格區分大小寫）
  • Linux查詢大小寫敏感
  • Windows查詢大小寫不敏感
  • MySQL 5.6，lower_case_table_names，0：表示區分大小寫、1：表示不區分大小寫
  • MySQL 5.7，lower_case_table_names，2：表示區分大小寫、1：表示不區分大小寫
• 統一命名使用全小寫字母和下劃線分割
• 列名與列表名在所有情況下忽略大小寫

B、對象名禁止使用MySQL關鍵字/保留字

• 使用關鍵字，建表時不會報錯，CURD時會提示SQL語句錯誤
  • 對于SQL語句中的關鍵字，寫SQL時需要單引號包圍，該要求難以統一，還是不使用關鍵字作為表字段
  • 關鍵字列表 -?9.3 Keywords and Reserved Words

C、對象命名做到見名知意，限定在32個字符范圍內

• 字符過長增加網絡傳輸開銷

D、臨時表，tmp前綴、日期作為后綴，方便篩選、倒庫、清理

E、備份表，bak前綴、日期作為后綴，方便篩選、倒庫、清理

• 大表，導出為SQL文件進行備份

F、存儲相同數據的列名和列類型必須一致

• 關聯列，類型不一致會導致隱式類型轉換，引起索引失效，降低查詢效率

二、基本設計規范

A、統一使用InnoDB存儲引擎（5.6之后為默認）

• TODO MySQL不同存儲引擎比較

B、統一使用utf8字符集

• 只存儲中文字符，可以使用GBK、GB2312
• 對于emoj需要使用utf8mb4
  • TODO utf8和utf8mb4之前還有爭論，關注討論結論
• 編碼轉換容易導致亂碼，以及索引失效

C、所有表和字段都需添加注釋

• 建庫時維護好數據字典

D、控制數據量大小，控制在500w行

• InnoDB未做最大行限制，受限于存儲設備和文件系統
• 分庫分表
  • TODO 分庫分表策略

E、謹慎使用MySQL分區表

• 分區表在物理上表現為多個文件，邏輯上表現為一個文件
  • 需要把多個物理文件分布于磁盤陣列，才能提高IO利用率
• 減少跨分區查詢
• 更傾向于使用物理分表方式管理海量數據

F、減小表寬度、盡量做到冷熱數據分離

• MySQL限制單表最多存儲4096列
• 每行數據大小不超過65535 BYTE = 64K
• 控制寬度，對列進行垂直拆分
• 減少IO，保留熱數據的內存緩存命中率
• 使用字段進行查詢，避免讀入無用的冷數據

G、禁止在表中建立預留字段

• 預留字段難以做到見名知意
• 預留字段難以確認存儲類型
  • 修改類型，會引起全表鎖定
• 修改表字段代價 >> 增加表字段

H、禁止在數據庫存儲圖片、文件等bin數據

I、禁止在線上庫做壓力測試

J、禁止從開發環境、測試環境直連生產環境數據庫

三、索引設計規范

A、不濫用、亂用索引

• 建議單表索引不超過5個
• 提高查詢效率，降低插入和更新效率
• MySQL優化器，會評估索引，生成最優執行計劃，索引過多，導致生成執行計劃時間過長，降低效率

B、InnoDB表，必須有一個主鍵 - B樹

• InnoDB使用主鍵按照順序組織表結構
• 如果沒有主鍵，會按照表順序，選擇第一個非空唯一索引組織表結構
• 如果沒有符合以上規則的，MySQL會自動生成6BYTE主鍵（性能不佳）、
• 不適用頻繁更新的列作為主鍵
• 不使用聯合索引作為主鍵
• 不使用UUID、MD5、HASH、字符串作為主鍵（無法保證順序增長）
• 建議選擇/使用自增ID列
• TODO 索引于B樹

C、常見索引列

• SELECT、UPDATE、DELETE語句中WHERE從句中的列
• 包含ORDER BY、GROUP BY、DISTINCT中的列
• 多表的JOIN關聯列

D、索引列順序

• 聯合索引從左向右使用
• 區分度最高的列（主鍵、唯一），置于聯合索引的最左側
• 字段長度小的列置于聯合索引的最左側
  • 元素占用空間小，內存頁中元素多，索引速度快
• 使用最頻繁的列，置于聯合索引的最左側
• 避免冗余和重復索引
• 對于頻繁的查詢，優先考慮使用覆蓋索引（所有的列都建立索引）
  • 避免InnoDB索引的二次查找
  • 把隨機IO變為順序IO加快查詢效率
• 避免使用外鍵
  • 外鍵用于保證數據的參照完整性，建議置于業務端實現
  • 外鍵影響父表和子表的寫操作，從而降低性能
  • 不建議使用外鍵約束，但一定在表與表之間的關聯鍵上建立索引

上述描述中有一條聯合索引最左側，使用數據區分度高的列。關于區分度，唯一值和總行數比值，區分度越高（接近1）。

SELECT COUNT(DISTINCT cat_1)/COUNT(*) AS cat_1_rate, COUNT(DISTINCT cat_6)/COUNT(*) AS cat_6_rate FROM OuterCooperationDB.product_core;+------------+------------+ | cat_1_rate | cat_6_rate | +------------+------------+ | 0.0002 | 0.0381 | +------------+------------+

四、字段設計規范

A、優先選擇符合存儲需要的最小數據類型

• 將字符串轉化為數字類型存儲（INET_AION、IP->Integer；INET_NTOA、Integer->IP）
• 對于非負整數，優先使用無符號整型存儲（id）
• VARCHAR(N)代表的是字符數，不是BYTE數
  • VARCHAR(255)，可以保存255中文字符，使用UTF8，實際占用765BYTE
• 過大長度消耗更多的內存

B、避免使用TEXT、BLOB數據類型

• TinyText、Text（64K）、MidumText、LongText
• 進行排序等查詢，無法使用內存臨時表，而必須使用磁盤臨時表
• 讀取數據時，需要二次查詢
• 非用不可的情況，可以把BLOB和TEXT拆分到單獨的拓展表中

C、避免使用ENUM數據類型

• 修改ENUM值需要使用ALTER語句
• 8 Reasons Why MySQL's ENUM Data Type Is Evil

D、盡可能把所有列定義為NOT NULL

• 索引NULL列，需要額外的空間，要占用更多的空間
• 進行比較和計算時，需要對NULL值做特別的處理

E、使用TIMESTAMP或DATETIME類型存儲時間

• 禁止使用字符串存儲日期型數據
  • 無法用日期函數進行計算和比較
  • 使用字符串存儲占用更多的空間
• TIMESTAMP（4BYTE），DATETIME（8BYTE）
  • TIMESTAMP范圍小，1970-01-01 00:00:01 - 2038-01-19 03:14:07
  • DATETIME范圍大

F、金額相關類型，使用decimal類型存儲

• float、double不精確
• decimal精確
  • decimal占用空間由寬度決定
  • 可以存儲比bigint更大的整數數據

五、SQL開發規范

A、建議使用預編譯語句進行數據庫操作

• 重復使用執行計劃、減少編譯所需時間
• 傳遞參數比傳遞SQL語句，減少網絡IO
• 避免動態SQL導致的注入問題

mysql> PREPARE stmt-> FROM 'SELECT SQRT(POW(?,2) + POW(?, 2)) AS hypotenuse'; mysql> SET @a = 3; mysql> SET @b = 4; mysql> EXECUTE stmt USING @a, @b; mysql> DEALLOCATE PREPARE stmt;

B、避免類型隱式轉換

• 常見于WHERE從句，列類型與參數類型不一致，可能出現隱式轉換
• 隱式轉換導致索引失效

C、充分利用表中已存在的索引

• 避免使用雙%的查詢條件，如a like %.log%
• 避免使用前置%的查詢條件；對于后置%，可以利用列上索引
• 一個SQL只能利用聯合索引中的一列進行范圍查詢
• 使用LEFT JOIN或NOT EXISTS來優化NOT IN查詢

D、程序連接不同數據庫，需使用不同賬號，禁止跨庫查詢

• 為數據庫遷移和分庫分表留出余地
• 減低業務耦合度
• 避免權限過大而產生的安全風險

E、禁止使用SELECT *進行查詢

• 避免消耗CPU和網絡IO
  • TEXT類型還會進行二次讀取
• 無法使用覆蓋索引
• 減少表結構變更對程序帶來的影響

F、禁止使用不含字段列表的INSERT語句

• 減少表結構變更對程序帶來的影響

G、避免使用子查詢

• 子查詢結果集無法使用索引，結果集數據量大則嚴重影響效率
• 子查詢會產生臨時表，消耗CPU和IO資源，引起慢查詢
• 子查詢可讀性更高，但是會影響性能，可以優化為JOIN操作

H、避免使用JOIN關聯太多的表

• 每JOIN一個表會多占用關聯內存（join_buffer_size）
• MySQL最多允許61個表，建議不超過5個

I、減少通數據庫交互次數

• 數據庫更適合批量操作
  • 提高SQL處理效率

J、使用IN代替OR語句

• IN不要超過500個
• IN操作可以有效利用索引

K、禁止使用ORDER BY RAND()進行隨機排序

• 會把所有符合條件的數據加載到內存中，進行排序
  • 通過業務代碼完成隨機數據獲取

L、禁止WHERE從句中對列進行函數轉換和計算

• 導致索引失效，WHERE DATE(createtime) = '20180925'
• 可以變通SQL，繼續使用列索引，WHERE createtime >= '20180925' AND createtime < '20180926'

M、（明顯不會有重復值時）使用UNION ALL而不是UNION進行結果集合并

• UNION會把所有數據放入臨時表進行去重操作

N、拆分復雜大SQL為多個小SQL進行查詢

• MySQL不支持一個SQL的多CPU并行計算
• SQL拆分后可以通過并行執行提高處理效率

六、數據庫操作行為規范（開發、運維）

A、對于超過百萬行的批量寫操作，需要分批多次進行操作

• binlog日志為row格式時會產生大量的日志
• 大批量寫操作，可能會導致嚴重的主從延遲
• 涉及事務，對大量數據進行鎖定，產生大量阻塞，導致可用鏈接被消耗

B、對于大表結構修改

• pt-online-schema-change工具進行修改
• TODO 原理
• 避免大表修改產生的主從延遲
• 避免對表字段修改時的鎖表

C、禁止為程序使用賬號賦予super權限（維護）

• 遵循最小權限原則
• 原則上不具有DROP權限

總結

以上是生活随笔為你收集整理的「MySQL」 - SQL设计规范的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。