根據多年經驗整理的《互連網MySQL開發規範》

寫在前面：無規矩不成方圓。對於剛加入互連網的朋友們，肯定會接觸到MySQL，MySQL作為互連網最流行的關係型資料庫產品，它有它擅長的地方，也有它不足的短板，針對它的特性，結合互連網大多應用的特點，筆者根據自己多年互連網公司的MySQL DBA經驗，現總結出互連網MySQL的一些開發規範，僅供參考。 一、基礎規範(1) 使用INNODB儲存引擎(2) 表字元集使用UTF8(3) 所有表都需要添加註釋(4) 單表資料量建議控制在5000W以內(5) 不在資料庫中儲存圖⽚、檔案等大資料(6) 禁止線上上做資料庫壓力測試(7) 禁⽌從測試、開發環境直連資料庫 二、命名規範(1) 庫名表名欄位名必須有固定的命名長度，12個字元以內(2) 庫名、表名、欄位名禁⽌止超過32個字元。須見名之意(3) 庫名、表名、欄位名禁⽌止使⽤用MySQL保留字(4) 臨時庫、表名必須以tmp為首碼，並以⽇日期為尾碼(5) 備份庫、表必須以bak為首碼，並以日期為尾碼 三、庫、表、欄位開發設計規範(1) 禁⽌使用分區表(2) 拆分大欄位和訪問頻率低的欄位，分離冷熱資料(3) 用HASH進⾏散表，表名尾碼使⽤⼗進位數，下標從0開始(4) 按日期時間分表需符合YYYY[MM][DD][HH]格式(5) 採用合適的分庫分表策略。例如千庫十表、十庫百表等(6) 儘可能不使用TEXT、BLOB類型(7) 用DECIMAL代替FLOAT和DOUBLE儲存精確浮點數(8) 越簡單越好：將字元轉化為數字、使用TINYINT來代替ENUM類型(9) 所有欄位均定義為NOT NULL(10) 使用UNSIGNED儲存非負整數(11) INT類型固定佔用4位元組儲存(12) 使用timestamp儲存時間(13) 使用INT UNSIGNED儲存IPV4(14) 使用VARBINARY儲存大小寫敏感的變長字串(15) 禁止在資料庫中儲存純文字密碼，把密碼加密後儲存(16) 用好數實值型別欄位Tinyint (1Byte)smallint (2Byte)mediumint (3Byte)int (4Byte)bigint (8Byte) 類型 位元組 最小值 最大值 (帶符號的/無符號的) (帶符號的/無符號的) TINYINT 1 -128 127 無符號 0 255 SMALLINT 2 -32768 32767 無符號 0 65535 MEDIUMINT 3 -8388608 8388607 無符號 0 16777215 INT 4 -2147483648 2147483647 無符號 0 4294967295 BIGINT 8 -9223372036854775808 9223372036854775807 無符號 0 18446744073709551615 如果數值欄位沒有那麼大，就不要用 bigint (17) 儲存ip最好用int儲存而非char(15)(18) 不允許使用ENUM(19) 避免使用NULL欄位NULL欄位很難查詢最佳化，NULL欄位的索引需要額外空間，NULL欄位的複合索引無效(20) 少用text/blob，varchar的效能會比text高很多，實在避免不了blob，請拆表(21) 資料庫中不允許儲存大檔案，或者照片，可以將大對象放到磁碟上，資料庫中儲存它的路徑 四、索引規範1、索引的數量要控制：(1) 單張表中索引數量不超過5個(2) 單個索引中的欄位數不超過5個(3) 對字串使⽤用首碼索引，首碼索引長度不超過8個字元(4) 建議優先考慮首碼索引，必要時可添加偽列並建立索引2、主鍵準則(1) 表必須有主鍵(2) 不使用更新頻繁的列作為主鍵(3) 盡量不選擇字串列作為主鍵(4) 不使用UUID MD5 HASH這些作為主鍵(數值太離散了)(5) 預設使⽤非空的唯一鍵作為主鍵(6) 建議選擇自增或發號器3、重要的SQL必須被索引，比如：(1) UPDATE、DELETE語句的WHERE條件列(2) ORDER BY、GROUP BY、DISTINCT的欄位4、多表JOIN的欄位注意以下：(1) 區分度最大的欄位放在前面(2) 核⼼SQL優先考慮覆蓋索引(3) 避免冗餘和重複索引(4) 索引要綜合評估資料密度和分布以及考慮查詢和更新比例5、索引禁忌(1) 不在低基數列上建立索引，例如“性別”(2) 不在索引列進行數學運算和函數運算6、盡量不使用外鍵(1) 外鍵用來保護參照完整性，可在業務端實現(2) 對父表和子表的操作會相互影響，降低可用性7、索引命名：非唯一索引必須以 idx_欄位1_欄位2命名，唯一所以必須以uniq_欄位1_欄位2命名，索引名稱必須全部小寫8、建立的唯一索引必須不能和主鍵重複9、索引欄位的預設值不能為NULL，要改為其他的default或者空。NULL非常影響索引的查詢效率10、反覆查看與表相關的SQL，符合最左首碼的特點建立索引。多條欄位重複的語句，要修改語句條件欄位的順序，為其建立一條聯合索引，減少索引數量11、能使用唯一索引就要使用唯一索引，提高查詢效率12、研發要經常使用explain，如果發現索引選擇性差，必須讓他們學會使用hint 五、SQL規範(1) sql語句儘可能簡單大的sql想辦法拆成小的sql語句(充分利用QUERY CACHE和充分利用多核CPU)(2) 事務要簡單，整個事務的時間長度不要太長 (3) 避免使用觸發器、函數、預存程序(4) 降低業務耦合度，為sacle out、sharding留有餘地(5) 避免在資料庫中進⾏數學運算(MySQL不擅長數學運算和邏輯判斷)(4) 不要用select *，查詢哪幾個欄位就select 這幾個欄位(5) sql中使用到OR的改寫為用 IN() (or的效率沒有in的效率高)(6) in裡面數位個數建議控制在1000以內(7) limit分頁注意效率。Limit越大，效率越低。可以改寫limit，比如例子改寫：select id fromtlimit 10000, 10; => select id from t where id > 10000 limit10;(9) 使用union all替代union(10) 避免使⽤大表的JOIN(11) 使用group by 分組、自動排序(12) 對資料的更新要打散後批次更新，不要一次更新太多資料(13) 減少與資料庫的互動次數(13) 注意使用效能分析工具Sql explain / showprofile / mysqlsla(14) SQL語句要求所有研發，SQL關鍵字全部是大寫，每個詞只允許有一個空格(15) SQL語句不可以出現隱式轉換，比如 select id from 表 where id='1'(16) IN條件裡面的資料數量要少，我記得應該是500個以內，要學會使用exist代替in，exist在一些情境查詢會比in快(17) 能不用NOT IN就不用NOTIN，坑太多了。。會把空和NULL給查出來(18) 在SQL語句中，禁止使用首碼是%的like(19) 不使用負向查詢，如not in/like(19) 關於分頁查詢：程式裡建議合理使用分頁來提高效率limit，offset較大要配合子查詢使用(20) 禁止在資料庫中跑大查詢(21) 使⽤先行編譯語句，只傳參數，比傳遞SQL語句更高效；一次解析，多次使用；降低SQL注入機率(22) 禁止使⽤order by rand()(23) 禁⽌單條SQL語句同時更新多個表 六、流程規範(1) 所有的建表操作需要提前告知該表涉及的查詢sql；(2) 所有的建表需要確定建立哪些索引後才可以建表上線；(3) 所有的改表結構、加索引操作都需要將涉及到所改表的查詢sql發出來告知DBA等相關人員；(4) 在建新表加欄位之前，要求研發至少要提前3天郵件出來，給dba們評估、最佳化和審核的時間(5)大量匯入、匯出資料必須提前通知DBA協助觀察(6) 禁⽌線上上從庫執行後台管理和統計類查詢(7) 禁⽌有super許可權的應用程式帳號存在(8) 推廣活動或上線新功能必須提前通知DBA進⾏行流量評估(9) 不在業務高峰期批次更新、查詢資料庫免費領取兄弟連IT教育原創linux營運工程師視頻/細說linux教程，詳情諮詢官網客服：http://www.lampbrother.net/linux/學PHP、Linux、HTML5、UI、Android等視頻教程（課件+筆記+視頻）！聯絡Q2430675018 歡迎加入linux交流群 478068715

