MySQL命名、設計及使用規範《MySQL命名、設計及使用規範》

標籤：產品   格式   負數   signed   欄位名   完成   引入   off   表的操作   

資料庫環境

• dev：開發環境，開發可讀寫，可修改表結構。開發人員可以修改表結構，可以隨意修改其中的資料但是需要保證不影響其他開發同事。
• qa：測試環境，開發可讀寫，開發人員可以通過工具修改表結構。
• sim：類比環境，開發可讀寫，發起上線請求時，會先在這個環境上進行預執行，這個環境也可供部署上線演練或壓力測試使用。
• real：生產資料庫從庫（准即時同步），唯讀環境，不允許修改資料，不允許修改表結構，供線上問題尋找，資料查詢等使用。
• online：線上環境，開發人員不允許直接線上上環境進行資料庫操作，如果需要操作必須找DBA進行操作並進行相應記錄，禁止進行壓力測試。

這些環境的機器，一定要做到許可權劃分明確，讀寫帳號分離，並且有辨識度，能區分具體業務。例如使用者名稱w_account，r_ account 分別代表讀、寫帳號，account是讀寫帳號。

命名規範

基本命名原則

• 使用有意義的英文詞彙，詞彙中間以底線分隔。（不要用拼音）
• 只能使用英文字母，數字，底線，並以英文字母開頭。
• 庫、表、欄位全部採用小寫，不要使用駝峰式命名。
• 避免用ORACLE、MySQL的保留字，如desc，關鍵字如index。
• 命名禁止超過32個字元，須見名之意，建議使用名詞不是動詞
• 資料庫，資料表一律使用首碼
  • 臨時庫、表名必須以tmp為首碼，並以日期為尾碼
  • 備份庫、表必須以bak為首碼，並以日期為尾碼

為什麼庫、表、欄位全部採用小寫？

在 MySQL 中，資料庫和表對就於那些目錄下的目錄和檔案。因而，作業系統的敏感性決定資料庫和表命名的大小寫敏感。

• Windows下是不區分大小寫。
• Linux下大小寫規則：
  • 資料庫名與表名是嚴格區分大小寫；
  • 表的別名是嚴格區分大小寫；
  • 列名與列的別名在所有的情況下均是忽略大小寫；
  • 變數名也是嚴格區分大小寫；

如果已經設定了駝峰式的命名如何解決？需要在MySQL的設定檔my.ini中增加 lower_case_table_names = 1即可。

表命名

• 同一個模組的表儘可能使用相同的首碼，表名稱儘可能表達含義。所有日誌表均以 log_ 開頭

欄位命名

• 表達其實際含義的英文單詞或簡寫。布爾意義的欄位以“is_”作為首碼，後接動詞過去分詞。
• 各表之間相同意義的欄位應同名。各表之間相同意義的欄位，以去掉模組首碼的表名_欄位名命名。
• 外鍵欄位用表名_欄位名表示其關聯關係。
• 表的主鍵一般都約定成為id，自增類型，是別的表的外鍵均使用xxx_id的方式來表明。

索引命名

• 非唯一索引必須按照“idx_欄位名稱_欄位名稱[_欄位名]”進行命名
• 唯一索引必須按照“uniq_欄位名稱_欄位名稱[_欄位名]”進行命名

約束命名

• 主鍵約束：pk_表名稱。
• 唯一約束：uk_表名稱_欄位名。（應用中需要同時有唯一性檢查邏輯。）

觸發器命名

• trg_表名_操作。

函數過程命名

• 採用動詞+名詞的形式表達其含義。

序列命名

• seq_表名

表設計規範

1、表引擎取決於實際應用情境；日誌及報表類表建議用myisam，與交易，審核，金額相關的表建議用innodb引擎。如無說明，建表時一律採用innodb引擎。myisam與innodb的區別

2、預設使用utf8mb4字元集，資料庫定序使用utf8mb4_general_ci，（由於資料庫定義使用了預設，資料表可以不再定義，但為保險起見，建議都寫上）。

為什麼字元集不選擇utf8，定序不使用utf8_general_ci？

採用utf8編碼的MySQL無法儲存佔位是4個位元組的Emoji表情。為了使後端的項目，全面支援用戶端輸入的Emoji表情，升級編碼為utf8mb4是最佳解決方案。對於JDBC串連串設定了characterEncoding為utf8或者做了上述配置仍舊無法正常插入emoji資料的情況，需要在代碼中指定串連的字元集為utf8mb4。

3、所有表、欄位均應用 comment 列屬性來描述此表、欄位所代表的真正含義，如枚舉值則建議將該欄位中使用的內容都定義出來。

4、如無說明，表中的第一個id欄位一定是主鍵且為自動成長，禁止在非事務內作為上下文作為條件進行資料傳遞。禁止使用varchar類型作為主鍵語句設計。

5、如無說明，表必須包含create_time和modify_time欄位，即表必須包含記錄建立時間和修改時間的欄位

6、如無說明，表必須包含is_del，用來標示資料是否被刪除，原則上資料庫資料不允許物理刪除。

7、用盡量少的儲存空間來存數一個欄位的資料

• 能用int的就不用char或者varchar
• 能用tinyint的就不用int
• 使用UNSIGNED儲存非負數值。
• 不建議使用ENUM、SET類型，使用TINYINT來代替
• 使用短資料類型，比如取值範圍為0-80時，使用TINYINT UNSIGNED
• 儲存精確浮點數必須使用DECIMAL替代FLOAT和DOUBLE
• 時間欄位，除特殊情況一律採用int來記錄unix_timestamp
  • 儲存年使用YEAR類型。
  • 儲存日期使用DATE類型。
  • 儲存時間（精確到秒）建議使用TIMESTAMP類型，因為TIMESTAMP使用4位元組，DATETIME使用8個位元組。
• 建議使用INT UNSIGNED儲存IPV4。
• 儘可能不使用TEXT、BLOB類型
• 禁止在資料庫中使用VARBINARY、BLOB儲存圖片、檔案等。建議使用其他方式儲存（TFS/SFS），MySQL只儲存指標資訊。
• 單條記錄大小禁止超過8k（列長度(中文)*3(UTF8)+列長度(英文)*1）

datetime與timestamp有什麼不同？

相同點：TIMESTAMP列的顯示格式與DATETIME列相同。顯示寬度固定在19字元，並且格式為YYYY-MM-DD HH:MM:SS。
不同點：

• TIMESTAMP
  • 4個位元組儲存，時間範圍：1970-01-01 08:00:01 ~ 2038-01-19 11:14:07
  • 值以UTC格式儲存，涉及時區轉化 ，儲存時對當前的時區進行轉換，檢索時再轉換回當前的時區。
• datetime
  • 8個位元組儲存，時間範圍：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
  • 實際格式儲存，與時區不轉換

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

• 將目前時間作為ts的預設值：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP。
• 當行更新時，更新ts的值：ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP。
• 可以將1和2結合起來：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。

如何使用INT UNSIGNED儲存ip？

使用INT UNSIGNED而不是char(15)來儲存ipv4地址，通過MySQL函數inet_ntoa和inet_aton來進行轉化。Ipv6地址目前沒有轉化函數，需要使用DECIMAL或者兩個bigINT來儲存。

8、如無備忘，所有欄位都設定NOT NULL，並設定預設值；

9、禁止在資料庫中儲存純文字密碼

10、如無備忘，所有的布爾值欄位，如is_hot、is_deleted，都必須設定一個預設值，並設為0；

11、如無備忘，排序欄位order_id在程式中預設使用降序排列；

12、整形定義中不添加長度，比如使用INT，而不是INT[4]

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

注意數實值型別括弧後面的數字只是表示寬度而跟儲存範圍沒有關係。很多人他們認為INT(4)和INT(10)其取值範圍分別是 (-9999到9999)和(-9999999999到9999999999)，這種理解是錯誤的。其實對整型中的 M值與 ZEROFILL 屬性結合使用時可以實現列值等寬。不管INT[M]中M值是多少，其取值範圍還是 (-2147483648到2147483647 有符號時)，(0到4294967295無符號時)。

顯示寬度並不限制可以在列內儲存的值的範圍，也不限制超過列的指定寬度的值的顯示。當結合可選擴充屬性ZEROFILL使用時預設補充的空格用零代替。例如：對於聲明為INT(5) ZEROFILL的列，值4檢索為00004。請注意如果在整數列儲存超過顯示寬度的一個值，當MySQL為複雜聯結產生暫存資料表時會遇到問題，因為在這些情況下MySQL相信資料適合原列寬度，如果為一個數值列指定ZEROFILL, MySQL自動為該列添加UNSIGNED屬性。

13、使用VARBINARY儲存大小寫敏感的變長字串

什麼時候用CHAR，什麼時候用VARCHAR？

CHAR和VARCHAR類型類似，但它們儲存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。CHAR和VARCHAR型別宣告的長度表示你想要儲存的最大字元數。例如，CHAR(30)可以佔用30個字元。

• CHAR列的長度固定為建立表時聲明的長度。長度可以為從0到255的任何值。當儲存CHAR值時，在它們的右邊填充空格以達到指定的長度。當檢索到CHAR值時，尾部的空格被刪除掉。在儲存或檢索過程中不進行大小寫轉換。
• VARCHAR列中的值為可變長字串。長度可以指定為0到65,535之間的值。(VARCHAR的最大有效長度由最大行大小和使用的字元集確定。整體最大長度是65,532位元組）。

同CHAR對比，VARCHAR值儲存時只儲存需要的字元數，另加一個位元組來記錄長度(如果列聲明的長度超過255，則使用兩個位元組)。VARCHAR值儲存時不進行填充。當值儲存和檢索時尾部的空格仍保留，符合標準SQL。

char適合儲存使用者密碼的MD5雜湊值，它的長度總是一樣的。對於經常改變的值，char也好於varchar,因為固定長度的行不容易產生片段，對於很短的列，char的效率也高於varchar。char(1)字串對於單一位元組字元集只會佔用一個位元組，但是varchar(1)則會佔用2個位元組，因為1個位元組用來儲存長度資訊。

索引設計規範

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

什麼是覆蓋索引？

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

索引的基本規範

1、索引數量控制，單張表中索引數量不超過5個，單個索引中的欄位數不超過5個。

• 綜合評估資料密度和分布
• 考慮查詢和更新比例

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

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

2、對字串使用首碼索引，首碼索引長度不超過8個字元，建議優先考慮首碼索引，必要時可添加偽列並建立索引。

• 不要索引blob/text等欄位,不要索引大型欄位,這樣做會讓索引佔用太多的儲存空間

什麼是首碼索引？

首碼索引說白了就是對文本的前幾個字元（具體是幾個字元在建立索引時指定）建立索引，這樣建立起來的索引更小，所以查詢更快。 首碼索引能有效減小索引檔案的大小，提高索引的速度。但是首碼索引也有它的壞處：MySQL 不能在 ORDER BY 或 GROUP BY 中使用首碼索引，也不能把它們用作覆蓋索引(Covering Index)。

建立首碼索引的文法：ALTER TABLE table_name ADD KEY(column_name(prefix_length));

3、主鍵準則

• 表必須有主鍵
• 不使用更新頻繁的列
• 盡量不選擇字串列
• 不使用UUID MD5 HASH
• 預設使用非空的唯一鍵
• 建議選擇自增或發號器

4、 重要的SQL必須被索引，核心SQL優先考慮覆蓋索索引

• UPDATE、DELETE語句的WHERE條件列
• ORDER BY、GROUP BY、DISTINCT的欄位
• 多表JOIN的欄位

5、區分度最大的欄位放在前面

• 選擇篩選性更優的欄位放在最前面，比如單號、userid等，type，status等篩選性一般不建議放在最前面
• 索引根據左首碼原則，當建立一個聯合索引(a,b,c)，則查詢條件裡面只有包含(a)或(a,b)或(a,b,c)的時候才能走索引,(a,c)作為條件的時候只能使用到a列索引,所以這個時候要確定a的返回列一定不能太多，不然語句設計就不合理,(b,c)則不能走索引
• 合理建立聯合索引（避免冗餘），(a,b,c) 相當於 (a) 、(a,b) 、(a,b,c)

6、索引禁忌

• 不在低基數列上建立索引，例如“性別”
• 不在索引列進行數學運算和函數運算
• 不要索引常用的小型表

7、 盡量不使用外鍵

• 外鍵用來保護參照完整性，可在業務端實現
• 對父表和子表的操作會相互影響，降低可用性
• INNODB本身對online DDL的限制

MYSQL 中索引的限制

• MYISAM 儲存引擎索引長度的總和不能超過 1000 位元組
• BLOB 和 TEXT 類型的列只能建立首碼索引
• MYSQL 目前不支援函數索引
• 使用不等於 (!= 或者 <>) 的時候, MYSQL 無法使用索引。
• 過濾欄位使用函數運算 (如 abs (column)) 後, MYSQL無法使用索引。
• join語句中join條件欄位類型不一致的時候MYSQL無法使用索引
• 使用 LIKE 操作的時候如果條件以萬用字元開始 (如 ‘%abc…’)時, MYSQL無法使用索引。
• 使用非等值查詢的時候, MYSQL 無法使用 Hash 索引。

語句設計規範

1、使用先行編譯語句

• 只傳參數，比傳遞SQL語句更高效
• 一次解析，多次使用
• 降低SQL注入機率

2、避免隱式轉換

• 會導致索引失效

3、充分利用首碼索引

• 必須是最左首碼
• 不可能同時用到兩個範圍條件
• 不使用%前置的查詢，如like “%ab”

4、不使用負向查詢，如not in/like

• 無法使用索引，導致全表掃描
• 全表掃描導致buffer pool利用率降低

5、避免使用預存程序、觸發器、UDF、events等

• 讓資料庫做最擅長的事
• 降低業務耦合度，為sacle out、sharding留有餘地
• 避開BUG

6、避免使用大表的JOIN

• MySQL最擅長的是單表的主鍵/二級索引查詢
• JOIN消耗較多記憶體，產生暫存資料表

7、避免在資料庫中進行數學運算

• MySQL不擅長數學運算和邏輯判斷
• 無法使用索引

7、減少與資料庫的互動次數

• INSERT … ON DUPLICATE KEY UPDATE
• REPLACE INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),()
• UPDATE … WHERE ID IN(10,20,50,…)

8、合理的使用分頁

• 限制分頁展示的頁數
• 只能點擊上一頁、下一頁
• 採用延遲關聯

如何正確的使用分頁？

假如有類似下面分頁語句：SELECT * FROM table  ORDER BY id LIMIT 10000, 10

由於MySQL裡對LIMIT OFFSET的處理方式是取出OFFSET+LIMIT的所有資料，然後去掉OFFSET，返回底部的LIMIT。所以，在OFFSET數值較大時，MySQL的查詢效能會非常低。可以使用id > n 的方式進行解決：

使用id > n 的方式有局限性，對於id不連續的問題，可以通過翻頁的時候同時傳入最後一個id方式來解決。

  

1234567

//輸出時，找出當前結果集中的最大最小id//下一頁http://example.com/page.php?last=100select * from table where id<100 order by id desc limit 10//上一頁http://example.com/page.php?first=110select * from table where id>110 order by id desc limit 10

這種方式比較大的缺點是，如果在瀏覽中有插入/刪除操作，翻頁不會更新，而總頁數可能仍然是根據新的count(*) 來計算，最終可能會產生某些記錄訪問不到。為了修補這個問題，可以繼續引入當前頁碼以及在上次翻頁以後是否有插入/刪除等影響總記錄數的操作並進行緩衝

其他變種方式：

  

1	select * from table where id >= (select id from table order by id limit #offset#, 1)

 

9、拒絕大SQL，拆分成小SQL

• 充分利用QUERY CACHE
• 充分利用多核CPU

10、使用in代替or，in的值不超過1000個

11、禁止使用order by rand()

12、使用EXPLAIN診斷，避免產生暫存資料表

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

13、用union all而不是union

union all與 union有什麼區別？

union和union all關鍵字都是將兩個結果集合并為一個，但這兩者從使用和效率上來說都有所不同。

union在進行錶鏈接後會篩選掉重複的記錄，所以在錶鏈接後會對所產生的結果集進行排序運算，重複資料刪除的記錄再返回結果。如：

  

123	select * from test_union1unionselect * from test_union2

這個SQL在運行時先取出兩個表的結果，再用排序空間進行排序重複資料刪除的記錄，最後返回結果集，如果表資料量大的話可能會導致用磁碟進行排序。

而union all只是簡單的將兩個結果合并後就返回。這樣，如果返回的兩個結果集中有重複的資料，那麼返回的結果集就會包含重複的資料了。

從效率上說，union all要比union快很多，所以，如果可以確認合并的兩個結果集中不包含重複的資料的話，那麼就使用union all，如下：

  

123	select * from test_union1union allselect * from test_union2

 

14、程式應有捕獲SQL異常的處理機制

15、禁止單條SQL語句同時更新多個表

16、不使用select * ，SELECT語句只擷取需要的欄位

• 消耗CPU和IO、消耗網路頻寬
• 無法使用覆蓋索引
• 減少表結構變更帶來的影響
• 因為大，select/join 可能產生暫存資料表

17、UPDATE、DELETE語句不使用LIMIT

18、INSERT語句必須顯式的指明欄位名稱，不使用INSERT INTO table()

19、INSERT語句使用batch提交（INSERT INTO table VALUES(),(),()……），values的個數不超過500

20、統計表中記錄數時使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1) 備忘：僅針對Myisam

21、資料更建立議使用二級索引先查詢出主鍵，再根據主鍵進行資料更新

22、禁止使用跨庫查詢

23、禁止使用子查詢，建議將子查詢轉換成關聯查詢

24、針對varchar類型欄位的程式處理，請驗證使用者輸入，不要超出其預設的長度；

分表規範

單表一到兩年內資料量超過500w或資料容量超過10G考慮分表，需提前考慮曆史資料移轉或應用自行刪除曆史資料，採用等量均衡分表或根據商務規則分表均可。要分表的資料表必須與DBA商量分表策略

• 用HASH進行散表，表名尾碼使用十進位數，下標從0開始
• 按日期時間分表需符合YYYY[MM][DD][HH]格式
• 採用合適的分庫分表策略。例如千庫十表、十庫百表等
• 禁止使用分區表，分區表對分區鍵有嚴格要，分區表在表變大後執行DDL、SHARDING、單表恢複等都變得更加困難。
• 拆分大欄位和訪問頻率低的欄位，分離冷熱資料

行為規範

• 大量匯入、匯出資料必須提前通知DBA協助觀察
• 禁止線上上從庫執行後台管理和統計類查詢
• 禁止有super許可權的應用程式帳號存在
• 產品出現非資料庫導致的故障時及時通知DBA協助排查
• 推廣活動或上線新功能必須提前通知DBA進行流量評估
• 資料庫資料丟失，及時聯絡DBA進行恢複
• 對單表的多次alter操作必須合并為一次操作
• 不在MySQL資料庫中存放商務邏輯
• 重大項目的資料庫方案選型和設計必須提前通知DBA參與
• 對特別重要的庫表，提前與DBA溝通確定維護和備份優先順序
• 不在業務高峰期批次更新、查詢資料庫其他規範
• 提交線上建表改表需求，必須詳細註明所有相關SQL語句

其他規範

日誌類資料不建議儲存在MySQL上，優先考慮Hbase或OceanBase，如需要儲存請找DBA評估使用壓縮表格儲存體。

MySQL命名、設計及使用規範《MySQL命名、設計及使用規範》