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存儲大小寫敏感的變長字元串

>  禁止在資料庫中存儲明文密碼

>  使用預編譯語句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