[[338708]]

最近涉及數據庫相關操作較多，公司現有規范也不是太全面，就根據網上各路大神的相關規范，整理了一些自用的規范用法，萬望指正。

數據庫環境

dev: 開發環境

開發可讀寫，可修改表結構。開發人員可以修改表結構，可以隨意修改其中的數據但是需要保證不影響其他開發同事。 

test: 測試環境

開發可讀寫，開發人員可以通過工具修改表結構。

online: 線上環境

開發人員不允許直接在線上環境進行數據庫操作，如果需要操作必須找DBA進行操作并進行相應記錄，禁止進行壓力測試。

重點的問題，各個環境的mysql服務器對應的用戶權限，一定要做到權限劃分明確，有辨識度，能具體區分業務場景等。

命名規范

基本命名規則

•  使用有意義的英文詞匯，詞匯中間以下劃線分隔。（不要用拼音）
•  只能使用英文字母，數字，下劃線，并以英文字母開頭。
•  庫、表、字段全部采用小寫，不要使用駝峰式命名。
•  避免用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_表名稱_字段名。（應用中需要同時有唯一性檢查邏輯。）

表設計規范

表引擎取決于實際應用場景；日志及報表類表建議用myisam，與交易，審核，金額相關的表建議用innodb引擎。如無說明，建表時一律采用innodb引擎

默認使用utf8mb4字符集，數據庫排序規則使用utf8mb4_general_ci，（由于數據庫定義使用了默認，數據表可以不再定義，但為保險起見，建議都寫上

為什么字符集不選擇utf8，排序規則不使用utf8_general_ci

采用utf8編碼的MySQL無法保存占位是4個字節的Emoji表情。為了使后端的項目，全面支持客戶端輸入的Emoji表情，升級編碼為utf8mb4是最佳解決方案。對于JDBC連接串設置了characterEncoding為utf8或者做了上述配置仍舊無法正常插入emoji數據的情況，需要在代碼中指定連接的字符集為utf8mb4。

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

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

如無說明，表必須包含create_time和modify_time字段，即表必須包含記錄創建時間和修改時間的字段

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

•  用盡量少的存儲空間來存數一個字段的數據
•  能用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格式保存，涉及時區轉化 ，存儲時對當前的時區進行轉換，檢索時再轉換回當前的時區。
•  datetime8個字節儲存，時間范圍：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來存儲。

•  如無備注，所有字段都設置NOT NULL，并設置默認值；
•  禁止在數據庫中存儲明文密碼
•  如無備注，所有的布爾值字段，如is_hot、is_deleted，都必須設置一個默認值，并設為0；
•  如無備注，排序字段order_id在程序中默認使用降序排列；
•  整形定義中不添加長度，比如使用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屬性。

•  使用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)，以獲得性能提升。

索引的基本規范

•  索引數量控制，單張表中索引數量不超過5個，單個索引中的字段數不超過5個。
•  綜合評估數據密度和分布
•  考慮查詢和更新比例

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

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

對字符串使用前綴索引，前綴索引長度不超過8個字符，建議優先考慮前綴索引，必要時可添加偽列并建立索引。

不要索引blob/text等字段,不要索引大型字段,這樣做會讓索引占用太多的存儲空間

什么是前綴索引？

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

建立前綴索引的語法：ALTER TABLE table_name ADD KEY(column_name(prefix_length));

主鍵準則

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

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

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

區分度最大的字段放在前面

• 選擇篩選性更優的字段放在最前面，比如單號、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)

索引禁忌

•  不在低基數列上建立索引，例如“性別”
•  不在索引列進行數學運算和函數運算
•  不要索引常用的小型表
•  盡量不使用外鍵
•  外鍵用來保護參照完整性，可在業務端實現
•  對父表和子表的操作會相互影響，降低可用性
•  INNODB本身對online DDL的限制

MYSQL 中索引的限制

•  MYISAM 存儲引擎索引長度的總和不能超過 1000 字節
•  BLOB 和 TEXT 類型的列只能創建前綴索引
•  MYSQL 目前不支持函數索引
•  使用不等于 (!= 或者 <>) 的時候, MYSQL 無法使用索引。
•  過濾字段使用函數運算 (如 abs (column)) 后, MYSQL無法使用索引。
•  join語句中join條件字段類型不一致的時候MYSQL無法使用索引
•  使用 LIKE 操作的時候如果條件以通配符開始 (如 ‘%abc…’)時, MYSQL無法使用索引。
•  使用非等值查詢的時候, MYSQL 無法使用 Hash 索引。

語句設計規范

使用預編譯語句

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

避免隱式轉換

•  會導致索引失效

充分利用前綴索引

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

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

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

避免使用存儲過程、觸發器、UDF、events等

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

避免使用大表的JOIN

•  MySQL最擅長的是單表的主鍵/二級索引查詢
•  JOIN消耗較多內存，產生臨時表

避免在數據庫中進行數學運算

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

減少與數據庫的交互次數

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

合理的使用分頁

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

如何正確的使用分頁？

假如有類似下面分頁語句：SELECT * FROM table ORDER BY id LIMIT 10000, 10由于MySQL里對LIMIT OFFSET的處理方式是取出OFFSET+LIMIT的所有數據，然后去掉OFFSET，返回底部的LIMIT。所以，在OFFSET數值較大時，MySQL的查詢性能會非常低。可以使用id > n 的方式進行解決：

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

http://example.com/page.php?last=100  
select * from table where id<100 order by id desc limit 10  
//上一頁 
 http://example.com/page.php?first=110  
select * from table where id>110 order by id desc limit 10 

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

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

•  拒絕大SQL，拆分成小SQL
•  充分利用QUERY CACHE
•  充分利用多核CPU
•  使用in代替or，in的值不超過1000個
•  禁止使用order by rand()
•  使用EXPLAIN診斷，避免生成臨時表

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

•  用union all而不是union

union all與 union有什么區別？

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

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

select * from test_union1  
union select * from test_union2 

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

而union all只是簡單的將兩個結果合并后就返回。這樣，如果返回的兩個結果集中有重復的數據，那么返回的結果集就會包含重復的數據了。

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

select * from test_union1 union all select * from test_union2 

•  程序應有捕獲SQL異常的處理機制
•  禁止單條SQL語句同時更新多個表
•  不使用select * ，SELECT語句只獲取需要的字段
•  消耗CPU和IO、消耗網絡帶寬
•  無法使用覆蓋索引
•  減少表結構變更帶來的影響
•  因為大，select/join 可能生成臨時表
•  UPDATE、DELETE語句不使用LIMIT
•  INSERT語句必須顯式的指明字段名稱，不使用INSERT INTO table()
•  INSERT語句使用batch提交（INSERT INTO table VALUES(),(),()……），values的個數不超過500
•  統計表中記錄數時使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1) 備注：僅針對Myisam
•  數據更新建議使用二級索引先查詢出主鍵，再根據主鍵進行數據更新
•  禁止使用跨庫查詢
•  禁止使用子查詢，建議將子查詢轉換成關聯查詢
•  針對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評估使用壓縮表存儲。