MySQL開發規範

標籤：dba   select   排序   sort   判斷   資料對象   strong   客戶   sql資料庫   

本文檔是為協助研發與營運人員按照規範使用MySQL資料庫，提升研發寫SQL的水平。致力於提供一個安全，穩定，高效能的資料庫環境。

命名規範
1)庫名、表名、欄位名必須使用小寫字母，"_"分割。
2)庫名、表名、欄位名不超過12個字元。
3)庫名、表名、欄位名禁止使用MySQL保留字，見附件。
4)庫名、表名、欄位名見名知意，建議使用名詞而不是動詞。
5)資料對象、變數的命名都採用英文字元,禁止使用中文命名。
6)臨時庫、表名必須以tmp為首碼，並以日期為尾碼。
7)備份庫、表必須以bak為首碼，並以日期為尾碼。

基礎規範
1)所有表統一使用InnoDB儲存引擎。
2)表字元集選擇UTF8mb4。
3)所有表和列都需要添加註釋。
4)禁止在資料庫中儲存圖片、檔案。
5)禁止線上上做資料庫壓力測試,如有特殊需要，需提前報備。
6)禁止用戶端直接操作測試，生產資料庫。
7)研發保證應用與資料庫表結構版本的統一，並提供相應的資料庫復原策略。
8)表結構變更DBA審核未通過的，不允許上線。

欄位設計規範
1)每張表必須有整型主鍵。如：id bigint(20) UNSIGNED ， NOT NULL，不要自增。
2)禁止DEFAULT NULL，建議NOT NULL 設定預設值。
3)儲存精確浮點數必須使用DECIMAL替代FLOAT和DOUBLE，或者使用bigint（需要做轉換）。
4)建議使用UNSIGNED儲存非負數值。
5)不建議使用ENUM類型，使用TINYINT來代替。
6)建議使用INT UNSIGNED儲存IPV4。
7)禁止在資料庫中儲存純文字密碼。
8)整形定義中建議採用INT(10),而不是INT(1),INT(11)或其他。
9)儲存狀態，性別等，用TINYINT。
10)將過大欄位拆分到其他表中。儘可能不使用TEXT、BLOB類型。如果必須使用，業務表中的TEXT,BLOB中欄位，必須要拆分到單獨的表中儲存。
11)需要根據實際的寬度來選擇VARCHAR(N)類型的寬度。
12)N表示的是字元數不是位元組數. VARCHAR(N)，N儘可能小，進行排序和建立暫存資料表一類的記憶體操作時，會使用N的長度申請記憶體。
13)儲存年使用YEAR類型，儲存日期使用DATE類型。
14)13 儲存時間（精確到秒）建議使用TIMESTAMP類型，因為TIMESTAMP使用4位元組，DATETIME使用8個位元組。TIMESTAMP類型儲存的值不能比1970早或比2037晚。

一句話總結：
?能NOT NULL 就NOT NULL，char、varchar用NOT NULL DEFAULT ‘‘,tinyint、smallint、int用NOT NULL DEFAULT 0。
?char、varchar取值要吝嗇，根據實際需求給，比如人名一般不超過5個，varchar(5)，不要varchar(200)。int、tinyint這類，int(1)和int(13)都是一樣的，
?我們統一用int(10),tinyint取值範圍[-128,127],加了unsigned取值[0,255]，如果不需要儲存負數，整數型別的加unsigned。

索引規範
1)單張表的索引數量控制在5個以內。
2)複合索引中的欄位數建議不超過5個。
3)非唯一索引必須按照"idx_欄位名稱_欄位名稱[_欄位名]"進行命名。
4)唯一索引必須按照"uniq_欄位名稱_欄位名稱[_欄位名]"進行命名。
5)合理利用覆蓋索引。不使用更新頻繁的列做為索引。
6)對長字串考慮使用首碼索引，首碼索引長度不超過8個字元。
7)索引欄位的順序需要考慮欄位值去重之後的個數，個數多的放在前面。
8)使用EXPLAIN判斷SQL語句是否合理使用索引，盡量避免extra列出現：Using File Sort，UsingTemporary。
9)UPDATE、DELETE語句需要根據WHERE條件添加索引。
10)合理建立聯合索引（避免冗餘），(a,b,c) 相當於 (a) 、(a,b) 、(a,b,c)，但（a,c）只能用到部分索引。

索引禁忌
1)不在選擇性低的列上建立索引，例如"性別", "狀態"， "類型"。
2)不在索引列進行數學運算和函數運算。
3)盡量不使用外鍵。
4)高並發情境不建議使用唯一索引。
5)不使用前置查詢，如like "%ab",like "%ab%"。
6SQL語句規範
1)SQL語句中IN包含的值不應過多（不超過1000個）
2)UPDATE、DELETE語句不使用LIMIT。
3)WHERE條件中必須使用合適的類型，避免MySQL進行隱式類型轉化。
4)SELECT語句只擷取需要的欄位。
5)SELECT、INSERT語句必須顯式的指明欄位名稱，不使用SELECT *，不使用INSERT INTO table()。
6)WHERE條件中的非等值條件（IN、BETWEEN、<、<=、>、>=）會導致後面的條件使用不了索引。
7)避免在SQL語句進行數學運算或者函數運算，容易將商務邏輯和DB耦合在一起。
8)INSERT語句使用batch提交（INSERT INTO table VALUES(),(),()……），values的個數不應過多。
9)避免使用預存程序、觸發器、函數等，容易將商務邏輯和DB耦合在一起，並且MySQL的預存程序、觸發器、函數中存在一定的bug。
10)避免使用JOIN。
11)使用合理的SQL語句減少與資料庫的互動次數。
12)不使用ORDER BY RAND()，使用其他方法替換。
13)建議使用合理的分頁方式以提高分頁的效率。
14)統計表中記錄數時使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1)。

MySQL開發規範