MySQL開發規范

 　　1.庫名、表名、字段名必須使用小寫字母,並采用下劃線分割。

　　a)MySQL有配置參數lower_case_table_names,不可動態更改,linux系統默認為 0,即庫表名以實際情況存儲,大小寫敏感。如果是1,以小寫存儲,大小寫不敏感。如果是2,以實際情況存儲,但以小寫比較。

　　b)如果大小寫混合使用,可能存在abc,Abc,ABC等多個表共存,容易導致混亂。

　　c)字段名顯式區分大小寫,但實際使用不區分,即不可以建立兩個名字一樣但大小寫不一樣的字段。

　　d)為了統一規范, 庫名、表名、字段名使用小寫字母。

　　2.庫名、表名、字段名禁止超過32個字符。

　　庫名、表名、字段名支持最多64個字符,但為了統一規范、易於辨識以及減少傳輸量,禁止超過32個字符。

　　3.使用INNODB存儲引擎。

　　INNODB引擎是MySQL5.5版本以後的默認引擘,支持事務、行級鎖,有更好的數據恢復能力、更好的並發性能,同時對多核、大內存、SSD等硬件支持更好,支持數據熱備份等,因此INNODB相比MyISAM有明顯優勢。

　　4.庫名、表名、字段名禁止使用MySQL保留字。

　　當庫名、表名、字段名等屬性含有保留字時,SQL語句必須用反引號引用屬性名稱,這將使得SQL語句書寫、SHELL腳本中變量的轉義等變得非常復雜。

　　5.禁止使用分區表。

　　分區表對分區鍵有嚴格要求;分區表在表變大後,執行DDL、SHARDING、單表恢復等都變得更加困難。因此禁止使用分區表,並建議業務端手動SHARDING。

　　6.建議使用UNSIGNED存儲非負數值。

　　同樣的字節數,非負存儲的數值范圍更大。如TINYINT有符號為 -128-127,無符號為0-255。

　　7.建議使用INT UNSIGNED存儲IPV4。

　　UNSINGED INT存儲IP地址占用4字節,CHAR(15)則占用15字節。另外,計算機處理整數類型比字符串類型快。使用INT UNSIGNED而不是CHAR(15)來存儲IPV4地址,通過MySQL函數inet_ntoa和inet_aton來進行轉化。IPv6地址目前沒有轉化函數,需要使用DECIMAL或兩個BIGINT來存儲。

　　例如:

　　SELECT INET_ATON('209.207.224.40'); 3520061480

　　SELECT INET_NTOA(3520061480); 209.207.224.40

　　8.強烈建議使用TINYINT來代替ENUM類型。

　　ENUM類型在需要修改或增加枚舉值時,需要在線DDL,成本較大;ENUM列值如果含有數字類型,可能會引起默認值混淆。

　　9.使用VARBINARY存儲大小寫敏感的變長字符串或二進制內容。

　　VARBINARY默認區分大小寫,沒有字符集概念,速度快。

　　10.INT類型固定占用4字節存儲,例如INT(4)僅代表顯示字符寬度為4位,不代表存儲長度。

　　數值類型括號後面的數字只是表示寬度而跟存儲范圍沒有關系,比如INT(3)默認顯示3位,空格補齊,超出時正常顯示,python、java客戶端等不具備這個功能。

　　11.區分使用DATETIME和TIMESTAMP。存儲年使用YEAR類型。存儲日期使用DATE類型。 存儲時間(精確到秒)建議使用TIMESTAMP類型。

　　DATETIME和TIMESTAMP都是精確到秒,優先選擇TIMESTAMP,因為TIMESTAMP只有4個字節,而DATETIME8個字節。同時TIMESTAMP具有自動賦值以及自動更新的特性。注意:在5.5和之前的版本中,如果一個表中有多個timestamp列,那麼最多只能有一列能具有自動更新功能。

　　如何使用TIMESTAMP的自動賦值屬性?

　　a)自動初始化,並自動更新: column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

　　b)只是自動初始化: column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP

　　c)自動更新,初始化的值為0: column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

　　d)初始化的值為0: column1 TIMESTAMP DEFAULT 0

　　12.所有字段均定義為NOT NULL。

　　a)對表的每一行,每個為NULL的列都需要額外的空間來標識。

　　b)B樹索引時不會存儲NULL值,所以如果索引字段可以為NULL,索引效率會下降。

　　c)建議用0、特殊值或空串代替NULL值。

　　13.將大字段、訪問頻率低的字段拆分到單獨的表中存儲,分離冷熱數據。

　　有利於有效利用緩存,防止讀入無用的冷數據,較少磁盤IO,同時保證熱數據常駐內存提高緩存命中率。

　　14.禁止在數據庫中存儲明文密碼。

　　采用加密字符串存儲密碼，並保證密碼不可解密，同時采用隨機字符串加鹽保證密碼安全。防止數據庫數據被公司內部人員或黑客獲取後，采用字典攻擊等方式暴力破解用戶密碼。

　　15.表必須有主鍵,推薦使用UNSIGNED自增列作為主鍵。

　　表沒有主鍵,INNODB會默認設置隱藏的主鍵列;沒有主鍵的表在定位數據行的時候非常困難,也會降低基於行復制的效率。

　　16.禁止冗余索引。

　　索引是雙刃劍,會增加維護負擔,增大IO壓力。(a,b,c)、(a,b),後者為冗余索引。可以利用前綴索引來達到加速目的,減輕維護負擔。

　　17.禁止重復索引。

　　primary key a;uniq index a;重復索引增加維護負擔、占用磁盤空間,同時沒有任何益處。

　　18.不在低基數列上建立索引,例如“性別”。

　　大部分場景下,低基數列上建立索引的精確查找,相對於不建立索引的全表掃描沒有任何優勢,而且增大了IO負擔。

　　19.合理使用覆蓋索引減少IO,避免排序。

　　覆蓋索引能從索引中獲取需要的所有字段,從而避免回表進行二次查找,節省IO。INNODB存儲引擎中, secondary index(非主鍵索引,又稱為輔助索引、二級索引)沒有直接存儲行地址,而是存儲主鍵值。如果用戶需要查詢secondary index中所不包含的數據列,則需要先通過secondary index查找到主鍵值,然後再通過主鍵查詢到其他數據列,因此需要查詢兩次。覆蓋索引則可以在一個索引中獲取所有需要的數據,因此效率較高。主鍵查詢是天然的覆蓋索引。例如SELECT email,uid FROM user_email WHERE uid=xx,如果uid 不是主鍵,適當時候可以將索引添加為index(uid,email),以獲得性能提升。

　　20.用IN代替OR。SQL語句中IN包含的值不應過多,應少於1000個。

　　IN是范圍查找,MySQL內部會對IN的列表值進行排序後查找,比OR效率更高。

　　21.表字符集使用UTF8,必要時可申請使用UTF8MB4字符集。

　　a)UTF8字符集存儲漢字占用3個字節,存儲英文字符占用一個字節。

　　b)UTF8統一而且通用,不會出現轉碼出現亂碼風險。

　　c)如果遇到EMOJ等表情符號的存儲需求,可申請使用UTF8MB4字符集。

　　22.用UNION ALL代替UNION。

　　UNION ALL不需要對結果集再進行排序。

　　23.禁止使用order by rand()。

　　order by rand()會為表增加一個偽列,然後用rand()函數為每一行數據計算出rand()值,然後基於該行排序, 這通常都會生成磁盤上的臨時表,因此效率非常低。建議先使用rand()函數獲得隨機的主鍵值,然後通過主鍵獲取數據。

　　24.建議使用合理的分頁方式以提高分頁效率。

　　第一種分頁寫法：

　　select *

　　from t

　　where thread_id = 771025

　　and deleted = 0

　　order by gmt_create asc limit 0, 15;

　　select * from t

　　where thread_id = 771025

　　and deleted = 0

　　order by gmt_create asc limit 0, 15;

　　原理：一次性根據過濾條件取出所有字段進行排序返回。

　　數據訪問開銷=索引IO+索引全部記錄結果對應的表數據IO

　　缺點：該種寫法越翻到後面執行效率越差，時間越長，尤其表數據量很大的時候。

　　適用場景：當中間結果集很小(10000行以下)或者查詢條件復雜(指涉及多個不同查詢字段或者多表連接)時適用。

　　第二種分頁寫法：

　　select t.* from (

　　select id from t

　　where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 0, 15) a, t

　　where a.id = t.id;

　　前提：假設t表主鍵是id列，且有覆蓋索引secondary key:(thread_id, deleted, gmt_create)

　　原理：先根據過濾條件利用覆蓋索引取出主鍵id進行排序，再進行join操作取出其他字段。

　　數據訪問開銷=索引IO+索引分頁後結果(例子中是15行)對應的表數據IO。

　　優點：每次翻頁消耗的資源和時間都基本相同，就像翻第一頁一樣。

　　適用場景：當查詢和排序字段(即where子句和order by子句涉及的字段)有對應覆蓋索引時，且中間結果集很大的情況時適用。

　　25.SELECT只獲取必要的字段,禁止使用SELECT *。

　　減少網絡帶寬消耗;

　　能有效利用覆蓋索引;

　　表結構變更對程序基本無影響。

　　26.SQL中避免出現now()、rand()、sysdate()、current_user()等不確定結果的函數。

　　語句級復制場景下,引起主從數據不一致;不確定值的函數,產生的SQL語句無法利用QUERY CACHE。

　　27.采用合適的分庫分表策略。例如千庫十表、十庫百表等。

　　采用合適的分庫分表策略,有利於業務發展後期快速對數據庫進行水平拆分,同時分庫可以有效利用MySQL的多線程復制特性。

　　28.減少與數據庫交互次數,盡量采用批量SQL語句。

　　使用下面的語句來減少和db的交互次數:

　　a)INSERT ... ON DUPLICATE KEY UPDATE

　　b)REPLACE INTO

　　c)INSERT IGNORE

　　d)INSERT INTO VALUES()

　　29.拆分復雜SQL為多個小SQL,避免大事務。

　　簡單的SQL容易使用到MySQL的QUERY CACHE;減少鎖表時