mysql 開發標準規範

標籤：範圍   mysql索引   sam   主鍵重複   inet_ntoa   業務   statement   平台   一個   

一、表設計

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

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

3. 庫名、表名、欄位名見名知意,盡量使用名詞而不是動詞。

4. 優先使用InnoDB儲存引擎。

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

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

7. 使用INT UNSIGNED儲存IPV4。【FAQ】

8. 整形定義中不添加長度，比如使用INT，而不是INT[4]。【FAQ】

9. 使用短資料類型，比如取值範圍為0-80時，使用TINYINT UNSIGNED。

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

11. 儘可能不使用TEXT、BLOB類型。

12. VARCHAR(N)，N表示的是字元數不是位元組數，比如VARCHAR(255)，可以最大可儲存255個漢字，需要根據實際的寬度來選擇N。

13. VARCHAR(N)，N儘可能小，因為MySQL一個表中所有的VARCHAR欄位最大長度是65535個位元組，進行排序和建立暫存資料表一類的記憶體操作時，會使用N的長度申請記憶體。

14. VARCHAR(N)，N>5000時，使用BLOB類型。

15. 表字元集選擇UTF8。

16. 使用VARBINARY儲存變長字串。

17. 儲存年使用YEAR類型。

18. 儲存日期使用DATE類型。

19. 儲存時間（精確到秒）使用TIMESTAMP類型，因為TIMESTAMP使用4位元組，DATETIME使用8個位元組。【FAQ】

20. 欄位定義為NOT NULL。

21. 將過大欄位拆分到其他表中。

22. 不在資料庫中使用VARBINARY、BLOB儲存圖片、檔案等。

二、 索引

1. 非唯一索引按照“idx_欄位名稱_欄位名稱[_欄位名]”進行命名。

2. 唯一索引按照“uniq_欄位名稱_欄位名稱[_欄位名]”進行命名。

3. 索引名稱使用小寫。

4. 索引中的欄位數不超過5個。

5. 唯一鍵由3個以下欄位組成，並且欄位都是整形時，使用唯一鍵作為主鍵。

6. 沒有唯一鍵或者唯一鍵不符合5中的條件時，使用自增（或者通過發號器擷取）id作為主鍵。

7. 唯一鍵不和主鍵重複。

8. 索引欄位的順序需要考慮欄位值去重之後的個數，個數多的放在前面。

9. ORDER BY，GROUP BY，DISTINCT的欄位需要添加在索引的後面。

10. 單張表的索引數量控制在5個以內。#索引少走索引查詢快.

11. 使用EXPLAIN判斷SQL語句是否合理使用索引，盡量避免extra列出現：Using File Sort，Using Temporary。【FAQ】

12. UPDATE、DELETE語句需要根據WHERE條件添加索引。#注意要是不加條件可能全部執行,災難性的,一定要避免,或者用技術手段阻止這樣的執行方式.

13. 不建議使用%首碼模糊查詢，例如LIKE “%weibo”。#相當於全表做匹配,查詢會較慢.

14. 對長度大於50的VARCHAR欄位建立索引時，使用其他方法。【FAQ】

15. 合理建立聯合索引（避免冗餘），(a,b,c) 相當於 (a) 、(a,b) 、(a,b,c)。

16. 合理利用覆蓋索引。【FAQ】

17. 在合理情況下使用FORCE INDEX。

18. SQL變更需要確認索引是否需要變更並通知DBA。

三、 SQL語句

1. 使用prepared statement，可以提供效能並且避免SQL注入。#參考文檔:http://www.cnblogs.com/liuhongfeng/p/4175765.html

2. SQL語句中IN包含的值不超過500。

3. UPDATE、DELETE語句不使用LIMIT。#沒理解,用limit豈不是更快.有限制.

4. WHERE條件中使用合適的類型，避免MySQL進行隱式類型轉化。【FAQ】

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的個數不超過500。

11. 避免使用預存程序、觸發器、函數等，容易將商務邏輯和DB耦合在一起，並且MySQL的預存程序、觸發器、函數中存在一定的bug。#沒理解

12. 避免使用JOIN。

13. 使用合理的SQL語句減少與資料庫的互動次數。【FAQ】

14. 不使用ORDER BY RAND()，使用其他方法替換。【FAQ】

15. 使用合理的分頁方式以提高分頁的效率。【FAQ】

16. 統計表中記錄數時使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1)。

17. 禁止在從庫上執行後台管理和統計類型功能的QUERY。

四、 散表

1. 每張表資料量控制在5000w以下。

2. 可以結合使用hash、range、lookup table進行散表。

3. Hash散表，表名尾碼使用16進位，比如user_ff。

4. 使用時間散表，表名尾碼使用日期，比如按日散表user_20110209、按月散表user_201102。

五、 其他

1. 大量匯入、匯出資料需要DBA進行審查，並在執行過程中觀察服務。

2. 批次更新資料，如update,delete 操作，需要DBA進行審查，並在執行過程中觀察服務。#避免影響其他資料.

3. 產品出現非資料庫平台營運導致的問題和故障時，如前端被抓站，請及時通知DBA，便於維護服務穩定。

4. 業務部門程式出現bug等影響資料庫服務的問題,請及時通知DBA，便於維護服務穩定。

5. 業務部門推廣活動，請提前通知DBA進行服務和訪問評估。

6. 如果出現業務部門人為誤操作導致資料丟失，需要恢複資料，請在第一時間通知DBA，並提供準確時間，誤動作陳述式等重要線索。

6.FAQ

1. 如何使用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

2. INT[M]，M值代表什麼含義？

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

3. 為什麼建議使用TIMESTAMP來儲存時間而不是DATETIME？

DATETIME和TIMESTAMP都是精確到秒，優先選擇TIMESTAMP，因為TIMESTAMP只有4個位元組，而DATETIME 8個位元組。同時TIMESTAMP具有自動賦值以及自動更新的特性。

4. 如何使用TIMESTAMP的自動賦值屬性？

a) 將目前時間作為ts的預設值：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP。

b) 當行更新時，更新ts的值：ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP。

c) 可以將1和2結合起來：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。

5.如何對長度大於50的VARCHAR欄位建立索引？

下面的表增加一列url_crc32，然後對url_crc32建立索引，減少索引欄位的長度，提高效率。

? CREATE TABLE url(

?? url VARCHAR(255) NOT NULL DEFAULT 0, url_crc32 INT UNSIGNED NOT NULL DEFAULT 0, ?? index idx_url(url_crc32)

6. 為什麼需要避免MySQL進行隱式類型轉化？

因為MySQL進行隱士類型轉化之後，可能會將索引欄位類型轉化成=號右邊值的類型，導致使用不到索引，原因和避免在索引欄位中使用函數是類似的。

7. 為什麼避免使用複雜的SQL？

拒絕使用複雜的SQL，將大的SQL拆分成多條簡單SQL分步執行。原因：簡單的SQL容易使用到MySQL的query cache；減少鎖表時間特別是MyISAM；可以使用多核cpu。

8. 為什麼不建議使用SELECT *?

增加很多不必要的消耗（cpu、io、記憶體、網路頻寬）；增加了使用覆蓋索引的可能性；當表結構發生改變時，前段也需要更新。

9. InnoDB儲存引擎為什麼避免使用COUNT(*)?

InnoDB表避免使用COUNT(*)操作，計數統計即時要求較強可以使用memcache或者redis，非即時統計可以使用單獨統計表，定時更新。

10. MySQL中如何進行分頁？

假如有類似下面分頁語句： SELECT * FROM table ORDER BY TIME DESC LIMIT 10000,10; 這種分頁方式會導致大量的io，因為MySQL使用的是提前讀取策略。 推薦分頁方式： SELECT * FROM table WHERE TIME

11. 為什麼不能使用ORDER BY rand()？

因為ORDER BY rand()會將資料從磁碟中讀取，進行排序，會消耗大量的IO和CPU，可以在程式中擷取一個rand值，然後通過在從資料庫中擷取對應的值。

12. 如何減少與資料庫的互動次數？

使用下面的語句來減少和db的互動次數： INSERT ... ON DUPLICATE KEY UPDATE REPLACE INSERT IGNORE INSERT INTO values(),()#即資料盡量一次插入,不要分開多次插入,減少互動次數.

13. 如何結合使用多個緯度進行散表散庫？

例如微博message，先按照crc32(message_id)將message散到16個庫中，然後針對每個庫中的表，

一天產生一張新表。

14. VARCHAR中會產生額外儲存嗎？

VARCHAR(M)，如果M<256時會使用一個位元組來儲存長度，如果M>=256則使用兩個位元組來儲存長度。

15. 為什麼MySQL的效能依賴於索引？

MySQL的查詢速度依賴良好的索引設計，因此索引對於高效能至關重要。合理的索引會加快查詢速度（包括UPDATE和DELETE的速度，MySQL會將包含該行的page載入到記憶體中，然後進行UPDATE或者DELETE操作），不合理的索引會降低速度。 MySQL索引尋找類似於新華字典的拼音和部首尋找，當拼音和部首索引不存在時，只能通過一頁一頁的翻頁來尋找。當MySQL查詢不能使用索引時，MySQL會進行全表掃描，會消耗大量的IO。

16. 為什麼一張表中不能存在過多的索引？

InnoDB的secondary index使用b+tree來儲存，因此在UPDATE、DELETE、INSERT的時候需要對b+tree進行調整，過多的索引會減慢更新的速度。

17. 什麼是覆蓋索引？

InnoDB儲存引擎中，secondary index（非主鍵索引）中沒有直接儲存行地址，儲存主索引值。如果使用者需要查詢secondary index中所不包含的資料列時，需要先通過secondary index尋找到主索引值，然後再通過主鍵查詢到其他資料列，因此需要查詢兩次。 覆蓋索引的概念就是查詢可以通過在一個索引中完成，覆蓋索引效率會比較高，主鍵查詢是天然的覆蓋索引。 合理的建立索引以及合理的使用查詢語句，當使用到覆蓋索引時可以獲得效能提升。 比如SELECT email,uid FROM user_email WHERE uid=xx，如果uid不是主鍵，適當時候可以將索引添加為index(uid,email)，以獲得效能提升。#相當於就是都走索引操作效率會變高.

18. EXPLAIN語句

EXPLAIN語句（在MySQL用戶端中執行）可以獲得MySQL如何執行SELECT語句的資訊。通過對SELECT語句執行EXPLAIN，可以知曉MySQL執行該SELECT語句時是否使用了索引、全表掃描、暫存資料表、排序等資訊。盡量避免MySQL進行全表掃描、使用暫存資料表、排序等

#參考文檔http://blog.csdn.net/solmyr_biti/article/details/54293492

mysql 開發標準規範