MySQL開發規範和原則大全

標籤：

一、 表設計

1. 庫名、表名、欄位名必須使用小寫字母，“_”分割。
2. 庫名、表名、欄位名必須不超過12個字元。
3. 庫名、表名、欄位名見名知意,建議使用名詞而不是動詞。
4. 建議使用InnoDB儲存引擎。
5. 儲存精確浮點數必須使用DECIMAL替代FLOAT和DOUBLE。
6. 建議使用UNSIGNED儲存非負數值。
7. 建議使用INT UNSIGNED儲存IPV4。
8. 整形定義中不添加長度，比如使用INT，而不是INT(4)。
9. 使用短資料類型，比如取值範圍為0-80時，使用TINYINT UNSIGNED。
10. 不建議使用ENUM類型，使用TINYINT來代替。
11. 儘可能不使用TEXT、BLOB類型。
12. VARCHAR(N)，N表示的是字元數不是位元組數，比如VARCHAR(255)，可以最大可儲存255個漢字，需要根據實際的寬度來選擇N。
13. VARCHAR(N)，N儘可能小，因為MySQL一個表中所有的VARCHAR欄位最大長度是65535個位元組，進行排序和建立暫存資料表一類的記憶體操作時，會使用N的長度申請記憶體。
14. 表字元集選擇UTF8。
15. 使用VARBINARY儲存變長字串。
16. 儲存年使用YEAR類型。
17. 儲存日期使用DATE類型。
18. 儲存時間（精確到秒）建議使用TIMESTAMP類型，因為TIMESTAMP使用4位元組，DATETIME使用8個位元組。
19. 建議欄位定義為NOT NULL。
20. 將過大欄位拆分到其他表中。
21. 禁止在資料庫中使用VARBINARY、BLOB儲存圖片、檔案等。
22. 表結構變更需要通知DBA審核。

二、 索引

1. 非唯一索引必須按照“idx_欄位名稱_欄位名稱[_欄位名]”進行命名。
2. 唯一索引必須按照“uniq_欄位名稱_欄位名稱[_欄位名]”進行命名。
3. 索引名稱必須使用小寫。
4. 索引中的欄位數建議不超過5個。
5. 單張表的索引數量控制在5個以內。
6. 唯一鍵由3個以下欄位組成，並且欄位都是整形時，使用唯一鍵作為主鍵。
7. 沒有唯一鍵或者唯一鍵不符合5中的條件時，使用自增（或者通過發號器擷取）id作為主鍵。
8. 唯一鍵不和主鍵重複。
9. 索引欄位的順序需要考慮欄位值去重之後的個數，個數多的放在前面。
10. ORDER BY，GROUP BY，DISTINCT的欄位需要添加在索引的後面。
11. 使用EXPLAIN判斷SQL語句是否合理使用索引，盡量避免extra列出現：Using File Sort，Using Temporary。
12. UPDATE、DELETE語句需要根據WHERE條件添加索引。
13. 不建議使用%首碼模糊查詢，例如LIKE “%weibo”。
14. 對長度過長的VARCHAR欄位建立索引時，添加crc32或者MD5 Hash欄位，對Hash欄位建立索引。
15. 合理建立聯合索引（避免冗餘），(a,b,c) 相當於 (a) 、(a,b) 、(a,b,c)。
16. 合理利用覆蓋索引。
17. SQL變更需要確認索引是否需要變更並通知DBA。

三、 SQL語句

1. 使用prepared statement，可以提供效能並且避免SQL注入。
2. SQL語句中IN包含的值不應過多。
3. UPDATE、DELETE語句不使用LIMIT。
4. WHERE條件中必須使用合適的類型，避免MySQL進行隱式類型轉化。
5. SELECT語句只擷取需要的欄位。
6. SELECT、INSERT語句必須顯式的指明欄位名稱，不使用SELECT *，不使用INSERT INTO table()。
7. 使 用SELECT column_name1, column_name2 FROM table WHERE [condition]而不是SELECT column_name1 FROM table WHERE [condition]和SELECT column_name2 FROM table WHERE [condition]。
8. WHERE條件中的非等值條件（IN、BETWEEN、<、<=、>、>=）會導致後面的條件使用不了索引。
9. 避免在SQL語句進行數學運算或者函數運算，容易將商務邏輯和DB耦合在一起。
10. INSERT語句使用batch提交（INSERT INTO table VALUES(),(),()……），values的個數不應過多。
11. 避免使用預存程序、觸發器、函數等，容易將商務邏輯和DB耦合在一起，並且MySQL的預存程序、觸發器、函數中存在一定的bug。
12. 避免使用JOIN。
13. 使用合理的SQL語句減少與資料庫的互動次數。
14. 不使用ORDER BY RAND()，使用其他方法替換。
15. 建議使用合理的分頁方式以提高分頁的效率。
16. 統計表中記錄數時使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1)。
17. 禁止在從庫上執行後台管理和統計類型功能的QUERY。

四、 散表

1. 每張表資料量建議控制在5000w以下。
2. 可以結合使用hash、range、lookup table進行散表。
3. 散表如果使用md5（或者類似的hash演算法）進行散表，表名尾碼使用16進位，比如user_ff。
4. 推薦使用CRC32求餘（或者類似的算術演算法）進行散表，表名尾碼使用數字，數字必須從0開始並等寬，比如散100張表，尾碼從00-99。
5. 使用時間散表，表名尾碼必須使用特定格式，比如按日散表user_20110209、按月散表user_201102。

五、 其他

1. 大量匯入、匯出資料需要DBA進行審查，並在執行過程中觀察服務。
2. 批次更新資料，如update,delete 操作，需要DBA進行審查，並在執行過程中觀察服務。
3. 產品出現非資料庫平台營運導致的問題和故障時，如前端被抓站，請及時通知DBA，便於維護服務穩定。
4. 業務部門程式出現bug等影響資料庫服務的問題,請及時通知DBA，便於維護服務穩定。
5. 業務部門推廣活動，請提前通知DBA進行服務和訪問評估。
6. 如果出現業務部門人為誤操作導致資料丟失，需要恢複資料，請在第一時間通知DBA，並提供準確時間，誤動作陳述式等重要線索。

———————————————————————————————————————————————--------------------------------------------------------

FAQ

1-1.庫名、表名、欄位名必須使用小寫字母，“_”分割。

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

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

c) 欄位名顯示區分大小寫，但實際使用不區分，即不可以建立兩個名字一樣但大小寫不一樣的欄位。

d) 為了統一規範， 庫名、表名、欄位名使用小寫字母。

1-2.庫名、表名、欄位名必須不超過12個字元。

庫名、表名、欄位名支援最多64個字元，但為了統一規範、易於辨識以及減少傳輸量，必須不超過12字元。

1-3.庫名、表名、欄位名見名知意,建議使用名詞而不是動詞。

a) 使用者評論可用表名usercomment或者comment。

b) 庫表是一種客觀存在的事物，一種對象，所以建議使用名詞。

1-4.建議使用InnoDB儲存引擎。

a) 5.5以後的預設引擘，支援事務，行級鎖，更好的恢複性，高並發下效能更好，對多核，大記憶體，ssd等硬體支援更好。

b) 具體比較可見附件的官方白皮書。

1-5.儲存精確浮點數必須使用DECIMAL替代FLOAT和DOUBLE。

a) mysql中的數實值型別（不包括整型）：
IEEE754浮點數： float （單精確度） ， double 或 real （雙精確度）
定點數： decimal 或 numeric

單精確度浮點數的有效數字二進位是24位，按十進位來說，是8位；雙精確度浮點數的有效數字二進位是53位，按十進位來說，是16 位
一個實數的有效數字超過8位，用單精確度浮點數來表示的話，就會產生誤差！同樣，如果一個實數的有效數字超過16位，用雙精確度浮點數來表示，也會產生誤差
b) IEEE754標準的電腦浮點數，在內部是用二進位表示的，但在將一個十進位數轉換為二進位浮點數時，也會造成誤差，原因是不是所有的數都能轉換成有限長度的位元。
即一個二進位可以準確轉換成十進位，但一個帶小數的十進位不一定能夠準確地用二進位來表示。

執行個體：
drop table if exists t;

create table t(value float(10,2));

insert into t values(131072.67),(131072.68);

select value from t;

+———–+

| value |

+———–+

| 131072.67 |

| 131072.69 |

+———–+

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

同樣的位元組數，儲存的數值範圍更大。如tinyint 有符號為 -128-127，無符號為0-255

1-7. 如何使用INT UNSIGNED儲存ip？

使用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

1-8. INT[M]，M值代表什麼含義？

注意數實值型別括弧後面的數字只是表示寬度而跟儲存範圍沒有關係，比如INT(3)預設顯示3位，空格補齊，超出時正常顯示，python、java用戶端等不具備這個功能。

1-10.不建議使用ENUM、SET類型，使用TINYINT來代替。

MySQL開發規範和原則大全