MySQL准入規範及容量評估

一、資料庫設計

1、表結構設計

 -表中的自增列（auto_increment屬性）推薦使用bigint型別
  -首選使用非空的唯一鍵， 其次選擇自增列或發號器
    不使用更新頻繁的列，儘量不選擇字串列，不使用UUID MD5 HASH
  -業務中選擇性很少的狀態status、型別type等欄位推薦使用tinytint或者smallint型別
  -業務中IP地址欄位推薦使用int型別
  -業務活躍的大表中必須有行資料的建立時間欄位create_time和最後更新時間欄位update_time
  -表中所有欄位必須都是NOT NULL屬性，業務可以根據需要定義DEFAULT值
  -用decimal儲存精確浮點數(不要用浮點型別)
  -不推薦使用enum，set，blob，text等型別，對於大表必須將text、blob等型別欄位拆分或者獨立建表 

2、索引設計

 -避免冗餘索引 ：避免將同一個欄位都建立索引，索引的建立需要根據訪問的SQL語句來評估
  -一次查詢，一個表只能用到一個索引，不要對每個查詢條件的欄位都單獨建立索引
  -單張表索引數量不超過7，單個索引欄位數不超過5  
  -不在null列上加索引
  -不在低基數列上建立索引，例如“性別” 
  -複合索引欄位排序，區分度最大的欄位放在前面
  -核心SQL優先考慮覆蓋索引
  -對字串使用字首索引
  -字首長度不超過8個字元 ，必須是最左字首 

3、字符集及校驗集

 -資料庫和表的字符集必須一致，且所有表的字符集必須一致，只能是utf8；資料庫中所有表採用統一的校驗集
  -主、從資料庫的字符集必須一致
  -前端程式字符集或者環境變數中的字符集，與資料庫、表的字符集必須一致 

4、其他要求

 -不推薦使用外來鍵，臨時表，檢視，自定義函式，儲存過程以及觸發器
  -SSD硬碟上，單表資料行數不能超過5000萬或者儲存空間不得大於30GB
  -SAS硬碟上，單表資料行數不能超過2000萬或者儲存空間不得大於15GB
  -上線前DBA必須根據1年內的業務訪問量和資料增長量，給出庫、表的擴充套件方案 

二、SQL編寫

1、select

 -SELECT語句必須指定具體欄位名稱，禁止寫成“select *”
  -SELECT語句禁止使用UNION，推薦使用UNION ALL，並且UNION子句個數限制在5個以內 

2、DML

 -INSERT語句必須指定具體的欄位名稱，不要寫成INSERT VALUES(……)形式 
  -SQL語句在程式中傳入的引數值型別必須與欄位在資料庫中的型別相同 

3、多表聯合查詢

 -多表連線查詢推薦使用別名，且SELECT列表中要用別名引用欄位，資料庫.表格式，如“select a.cid  from iknow_qb. tblreply a where …”
  -生產系統中，單個查詢中不推薦將3張表以上（包括3張表）做連線
  -生產系統中，強烈不推薦使用外關聯，包括左外關聯，右外關聯和全外關聯
  -在多表連線的查詢中，驅動表須要選擇結果集較小的表
  -禁止寫成多層子查詢巢狀的SQL語句，推薦改寫成表順序連線的格式
  -儘量不要在INSERT|UPDATE|DELETE|REPLACE語句中進行多表連線操作 

4、事務

 -事務中INSERT|UPDATE|DELETE|REPLACE語句操作的行數控制在2000，以及WHERE子句中IN列表的傳參個數控制在2000
  -批次運算元據時，需要控制事務處理間隔時間，進行必要的sleep，具體值由DBA給出，並且程式必須有中斷處理能力
  -對於有auto_increment屬性欄位的表的插入操作，併發需要控制在200/s以內
  -SQL級別/事務級別/主從資料庫中的表儲存引擎型別要一致，儲存引擎混合使用會導致主從資料不一致或主從同步中斷
  -對於同步延遲不敏感的只讀查詢，必須放到從庫上執行；對於同步延遲敏感的只讀查詢，可以放到主庫上執行
  -前端程式中儘量不要使用set語句，包括set names、set sql_mode和set isolation_level等 

5、表掃描方式：

 -SELECT|UPDATE|DELETE|REPLACE要有WHERE子句，且WHERE子句的條件必需使用索引查詢
  -生產資料庫中強烈不推薦大表上發生全表掃描，但對於5000行以下的靜態表可以全表掃描
  -業務中大表全表掃描和全表匯出（dump）推薦放在備份庫或者線下讀庫中進行
  -WHERE 子句中禁止只使用全模糊的LIKE條件進行查詢(如like '%aj%')，必須有其他查詢條件
  -WHERE子句中的索引列或組合索引前導列上不能使用函式 

6、排序和分組

 -有distinct、order by和group by子句的查詢，中間結果集限制10000行以內
  -對於大結果集（中間結果集超過10000行）的排序、分組放到程式端實現 

7、其他要求

 -單個SQL語句的大小限制在5MB以內
  -生產資料庫中SQL語句的中間結果集和最終結果集必須限制在5MB以內
  -生產資料庫中SQL語句禁止使用提示，如force index，ignore index，straight_join，sql_no_cache等
  -禁止使用全文檢索功能
  -禁止使用事件（EVENT）功能
  -程式中不要使用或操作mysql庫和test庫，禁止建立test或以test開頭的庫
  -禁止在mysql中使用使用者自定義變數
  -線上資料庫中不要進行業務的實時統計或者彙總等計算操作，可匯出後利用其它工具或者線上下備份庫中完成
  -減少與資料庫的互動次數 
        INSERT ... ON DUPLICATE KEY UPDATE 
        REPLACE  INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),()
        UPDATE … WHERE ID IN(A,B,C,…)
  -不使用負向查詢，例如 not in，!= ，not like
  -不在索引列進行數學運算和函式運算 
  -不使用%前導的查詢，例如like “%abc”
  -避免大表資料型別間的隱式轉換(這個經常出效能問題)會導致索引失效，例如數字轉字串 

三、MySQL相關特點介紹

1、MySQL對SQL的處理特點

 -SQL請求處理只能使用一個核
  -沒有SQL編譯快取,SQL儲存過程都是硬解析
  -索引上不支援運算對比
  -大多情況下一個Query只能使用一個索引
  -不支援Hash jion(MariaDB目前支援)
  -基於執行緒的對外服務模型(連線數太高,效能下降嚴重)
  -子查詢支援較差，外層查詢一般走不了索引 

2、MySQL支援的儲存大小

 -單個表空間64T, 每個表只有一個表空間,也就是每個單表最大64T
  -Innodb Logfile 加起來不能超過512G
  -每行大小限制65535 byte 
  -每個表最多1027個欄位
  -每個表最多64個普通索引 

3、MySQL生產參考指標

 -單例項最好不要超過1T, 周邊LOG除外,最大不建議超過5T
  -一般的OLTP單表建議最大不要超過10G 
  -通常在有buffer命中的情況下:
        Select 可以達到3-6W/S
        Insert 在聚集索引連續的情況可以到2w-3W/S
        在聚集索引不連續的情況下有可能也就是200-300/S
        UPDATE資料在記憶體的情況下可以達到3K/S
        DELETE資料在記憶體的情況下可以達到1k/s,有可能更少
  -資料庫的瓶頸： IO能力 ,想辦法用順序IO,減少隨機IO 

四、建表稽核

建庫或者建表，需要提前找DBA評估建表語句，並填寫表及SQL稽核模板：

SQL稽核模板.doc

五、容量評估

1、容量評估概述
        所有的資料庫上線：新建叢集、新建資料庫、新建表，都需要提前進行容量評估，防止後續因容量問題而又對已上線的業務進行調整、擴容、遷移等操作，從而對線上業務造成影響。容量包括：訪問量(讀寫)、資料及增長量、磁碟空間容量. 

2、表容量
        表容量主要從表的 記錄數、平均長度、增長量、讀寫量、總大小量進行評估。一般對於OLTP的表，建議單表不要超過2000W行資料量，總大小15G以內。訪問量：單表讀寫量在1600/s以內。
        對於單表資料量上百萬的表，每行記錄長度不要過長，不要和text、blob等欄位型別放在同一個表中。(MySQL資料頁大小為16K，每行記錄越長，每個資料頁儲存的記錄數就越少，因此在對資料進行檢索時，會產生更多的IO) 

3、例項容量
        MySQL是基於執行緒的服務模型，因此在一些併發較高的場景下，單例項並不能充分利用伺服器的CPU資源，吞吐量反而會卡在mysql層，特別是對於mysql5.5版本。在mysql 5.6版本中 做了很大最佳化，而且percona 版本有thread pool ，可以充分應對高併發場景下CPU上下文切換消耗過高的問題。
        單例項QPS吞吐量一般控制在20000/s以內，寫入量還需考慮從庫延遲問題，對於mysql5.6版本可以考慮進行分庫後再分表，充分利用5.6版本基於庫級別的多執行緒複製，從而提高寫入的吞吐量。 

4、磁碟空間
        伺服器一般會承載多個資料庫例項，因此在各個例項上線前，需要對各個例項進行 資料量的評估，以及1-2年內 主要的幾個大表的增長量情況，對資料量的評估，儘量精確到每個欄位。對於增長量不是特別快的業務(半年就翻倍的情況)，建議1-2年的資料量，最終佔磁碟使用率的70%以內。同時，對於一些資料增長較快，可以考慮使用大的慢盤進行資料歸檔。