MySQL開發規範

標籤：資料庫規範

命名規範

>  庫名、表名、欄位名必須使用小寫字母並採用底線分割
>  庫名、表名、欄位名禁止超過32個字元，須見名知意
   * 庫名、表名、欄位名支援最多64個字元，統一規範、易於辨識以及減少傳輸量不要超過32
>  庫名、表名、欄位名禁止使用MySQL保留關鍵字
>  臨時庫、暫存資料表名必須以tmp為首碼並以日期為尾碼
>  備份庫、備份表名必須以bak為首碼並以日期為尾碼

基本規範

>  使用INNODB儲存引擎
   * 5.5以後的預設引擘，支援事務，行級鎖，更好的恢複性，高並發下效能更好，對多核，大記憶體，ssd等硬體支援更好
>  表字元集使用UTF8
   * 使用utf8字元集，如果是漢字，佔3個位元組，但ASCII碼字元還是1個位元組
   * 統一，不會有轉換產生亂碼風險
>  所有表都需要添加註釋
>  單表資料量建議控制在5000萬以內
>  不在資料庫中儲存圖片、檔案等大資料
>  禁止線上上做資料庫壓力測試
>  禁止從測試、開發環境直連資料庫

庫表設計規範

>  禁止使用分區表
   * MySQL的分區表實際效能不是很好，且管理維護成本較高
>  拆分大欄位和訪問頻率低的欄位，分離冷熱資料
>  用HASH進行散表，表名尾碼使用十進位數，下標從0開始
   * 首次分表盡量多的分，避免二次分表，二次分表的難度和成本較高
>  按日期時間分表需符合YYYY[MM][DD][HH]格式
>  採用合適的分庫分表策略，如千庫十個表、十庫百表等

索引設計規範

索引是一把雙刃劍，它可以提高查詢效率但也會降低插入和更新的速度並佔用磁碟空間
>  單張表中索引數量不超過5個
>  單個索引中的欄位數不超過5個
>  對字串使用首碼索引，首碼索引長度不超過10個字元
   * 如果有一個CHAR(200) 列，如果在前10個字元內，多數值是惟一的，那麼就不要對整個列進行索引。對前10 個字元進行索引能夠節省大量索引空間，也可能會使查詢更快
>  表必須有主鍵
>  不使用更新頻繁地列作為主鍵
>  盡量不選擇字串列作為主鍵
>  不使用UUID、MD5、HASH 作為主鍵
>  預設使用非空的唯一鍵
>  主鍵建議選擇自增或發號器
>  重要的SQL必須被索引
   * SELECT、UPDATE、DELETE語句的WHERE條件列
   * ORDER BY、GROUP BY、DISTINCT的欄位
   * 多表JOIN的欄位
>  區分度最大的欄位放在索引前面
>  核心SQL優先考慮覆蓋索引
   * select的資料列只用從索引中就能夠取得，不必讀取資料行，換句話說查詢列要被所建的索引覆蓋
>  避免冗餘或重複索引
   * 合理建立聯合索引（避免冗餘），index(a,b,c) 相當於 index(a) 、index(a,b) 、index(a,b,c)
>  索引不是越多越好，按實際需要進行建立
   * 每個額外的索引都要佔用額外的磁碟空間，並降低寫操作的效能
>  不在低基數列上建立索引，例如‘性別’
>  不在索引列進行數學運算和函數運算
>  盡量不要使用外鍵
   * 外鍵用來保護參照完整性，可在業務端實現
   * 對父表和子表的操作會相互影響，降低可用性
   * INNODB本身對Online DDL的限制
>  不使用%前置的查詢，如like “%xxx”
   * 無法使用索引
>  不使用反向查詢，如 not in /  not like
   * 無法使用索引，導致全表掃描
   * 全表掃描導致buffer pool利用降低

欄位設計規範

>  儘可能不要使用TEXT、BLOB類型
   * 刪除這種值會在資料表中留下很大的"空洞"
   * 可以考慮把BLOB或TEXT列分離到單獨的表中
>  用DECIMAL代替FLOAT和DOUBLE儲存精確浮點數
   * 浮點數相對於定點數的優點是在長度一定的情況下，浮點數能夠表示更大的資料範圍
浮點數的缺點是會引起精度問題
>  將字元轉化為數字
>  使用TINYINT來代替ENUM類型
>  欄位長度盡量按實際需要進行分配，不要隨意分配一個很大的容量
   * the best strategy is to allocate only as much space as you really need
   * VARCHAR(N)，N表示的是字元數不是位元組數，比如VARCHAR(255)，可以最大可儲存255個漢字，需要根據實際的寬度來選擇N
   * VARCHAR(N)，N儘可能小，因為MySQL一個表中所有的VARCHAR欄位最大長度是65535個位元組，進行排序和建立暫存資料表一類的記憶體操作時，會使用N的長度申請記憶體
>  如果可能的話所有欄位均定義為not null
>  使用UNSIGNED儲存非負整數
   * 同樣的位元組數，儲存的數值範圍更大。如tinyint 有符號為 -128-127，無符號為0-255
>  INT類型固定佔用4個位元組儲存
>  使用TIMESTAMP儲存時間
   * 因為TIMESTAMP使用4位元組，DATETIME使用8個位元組, 同時TIMESTAMP具有自動賦值以及自動更新的特性
>  使用INT UNSIGNED 儲存IPV4
>  使用VARBINARY儲存大小寫敏感的變長字串
>  禁止在資料庫中儲存純文字密碼

SQL設計規範

>  使用先行編譯語句prepared statement
   * 只傳參數，比傳遞SQL語句更高效
   * 一次解析，多次使用
   * 降低SQL注入機率
java 方法如下： protected boolean updateSalary(Connection conn,BigDecimal x,String ID) throws SQLException{ PreparedStatement pstmt = null; try { pstmt = conn.prepareStatement("UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?"); pstmt.setBigDecimal(1, x); pstmt.setString(2, ID); return true; } finally{ if (pstmt!=null){ pstmt.close(); } } 
}  
>  盡量避免相同語句由於書寫格式的不同，而導致多次文法分析
>  避免隱式轉換
   * 會導致索引失效，如 select userid from table where userid=’1234’
>  充分利用首碼索引
   * 必須是最左首碼
   * 不可能同時用到兩個範圍條件
>  避免使用預存程序、觸發器、EVENTS等
   * 讓資料庫做最擅長的事
   * 降低業務耦合度，為Scale Out、Sharding 留點餘地
   * 避開BUG
>  避免使用大表的join
   * MySQL最擅長的是單表的主鍵/二級索引查詢
   * Join消耗較多的記憶體，產生暫存資料表
>  避免在資料庫中進行數學運算
   * 容易將商務邏輯和DB耦合在一起
   * MySQL不擅長數學運算和邏輯判斷
   * 無法使用索引
>  減少與資料庫的互動次數
   * Insert … on duplicate key update
   * Replace into 、 insert ignore、insert into values(),(),()…
   * Update … where id in (1,2,3,4)
   * Alter table tbl_name add column col1, add column col2
>  拒絕大SQL，拆分成小SQL
   * 充分利用query cache
   * 充分利用多核CPU
>  使用in代替or，in的值不超過1000個
>  禁止使用order by rand()
   * 因為ORDER BY rand()會將資料從磁碟中讀取，進行排序，會消耗大量的IO和CPU，可以在程式中擷取一個rand

     值，然後通過在從資料庫中擷取對應的值
>  使用union all 而不是union
>  程式應有捕獲SQL異常的處理機制
>  禁止單條SQL語句同時更新多個表
>  不使用select *
   * 消耗cpu和IO、消耗網路頻寬
   * 無法使用覆蓋索引
   * 減少表結構變更帶來的影響       

行為規範

>  大量匯入、匯出資料必須提前通知DBA協助觀察
>  批次更新資料，如update,delete 操作，需要DBA進行審查，並在執行過程中觀察服務
>  禁止在從庫上執行後台管理和統計類的功能查詢
>  禁止有super許可權的應用程式帳號存在
>  產品出現非資料庫導致的故障時及時通知DBA協助排查
>  促銷活動或上線新功能必須提前通知DBA進行流量評估
>  資料庫資料丟失，及時聯絡DBA進行恢複
>  對單表的多次alter操作必須合并為一次操作
>  不在MySQL資料庫中存放商務邏輯
>  重大項目的資料庫方案選型和設計必須提前通知DBA參與
>  對特別重要的庫表，提前與DBA溝通確定維護和備份優先順序
>  不在業務高峰期批次更新、查詢資料庫
>  提交線上建表需求，必須詳細註明所有相關SQL

MySQL開發規範