MySQL資料庫六大設計規範總結1

資料庫命名規範

所有資料庫物件名稱必須小寫，因為其對大小寫是敏感的。例如table跟Table是兩張不同的表

命名要做到見名識意，禁止使用MySQL保留關鍵字。

臨時表以tmp_開頭，備份表以bak_開頭並以時間戳結尾。

所有儲存相同資料的列名和列型別必須一致。

資料庫基本規範

所有表和欄位都需要新增註釋

    使用comment從句新增表和列的備註

    從一開始就進行資料字典的維護

資料庫和表的字元統一使用UTF8

    統一字符集可以避免由於字符集轉換產生的亂碼

    MySQL中UTF8字符集漢字佔3個位元組，ASCII碼佔1個位元組

所有表必須使用InnoDB儲存引擎

    5.6以後的預設引擎

    支援事務、行級鎖、更好的恢復性、高併發下效能更好

儘量控制單表資料量的大小，建議控制在500萬以內

    500萬並不是MySQL資料庫的限制，具體MySQL能儲存多少萬資料量呢？取決於儲存設定和檔案系統

    可以用歷史資料歸檔，分庫分表等手段來控制資料量大小

謹慎使用MySQL分割槽表

    分割槽表在物理上表現為多個檔案，在邏輯上表現為一個表

    謹慎選擇分割槽鍵，跨分割槽查詢效率可能更低

    建議採用物理分表的方式管理大資料

儘量做到冷熱資料分離，減小表的寬度

    減少磁碟IO，保證熱資料的記憶體快取命中率

    利用更有效的利用快取，避免讀入無用的冷資料

    經常一起使用的列放到一個表中

禁止在表中建立預留欄位

    預留欄位的命名很難做到見名識義

    預留欄位無法確認儲存的資料型別，所以無法選擇合適的型別

    對預留欄位型別的修改，會對錶進行鎖定

禁止在資料庫中儲存圖片、檔案等二進位制資料

禁止線上上做資料庫壓力測試

禁止從開發環境，測試環境直連生產環境資料庫

資料庫索引規範

限制每張表上的索引數量，建議單張表索引不超過5個

    索引並不是越多越好，其可以提高效率同樣也可以降低效率

    禁止給表中的每一列都建立單獨的索引

    每個InnoDB表必須有一個主鍵

常見索引列建議

    SELECT、UPDATE、DELETE語句的WHERE從句中的列

    包含在ORDER BY、GROUP BY、DISTINCT中的欄位

    多表JOIN的關聯列

如何選擇索引列的順序

    區分度最高的列放在聯合索引的最左側

    儘量把欄位長度小的列放在聯合索引的最左側

    使用最頻繁的列放到聯合索引的左側

避免建立冗餘索引和重複索引

    primary key(id)、index(id)、unique index(id)

    index(a,b,c)、index(a,b)、index(a)

對於頻繁的查詢優先考慮使用覆蓋索引

    覆蓋索引：就是包含了所有查詢欄位的索引

    避免InnoDB表進行索引的二次查詢

    可以把隨機IO變為順序IO加快查詢效率

儘量避免使用外來鍵

    不建議使用外來鍵約束，但一定在表與表之間的關聯鍵上建立索引

    外來鍵可用於保證資料的參照完整性，但建議在業務端實現

    外來鍵會影響父表和子表的寫操作從而降低效能