【相關學習推薦：mysql教學】
#最近涉及資料庫相關作業較多，公司現有規範也不是太全面，就根據網路上各路大神的相關規範，整理了一些自用的規範用法，萬望指正。
資料庫環境
dev: 開發環境
開發可讀寫，可修改表結構。開發人員可以修改表結構，可以隨意修改其中的資料但是需要保證不影響其他開發同事。
test: 測試環境
開發可讀寫，開發人員可以透過工具修改表格結構。
online: 線上環境
開發人員不允許直接在線上環境進行資料庫操作，如果需要操作必須找DBA進行操作並進行對應記錄，禁止進行壓力測試。
重點的問題，各個環境的mysql伺服器對應的使用者權限，一定要做到權限劃分明確，有辨識度，能具體區分業務場景等。
命名規格
基本命名規則
為什麼庫、表、字段全部採用小寫？
在 MySQL 中，資料庫和表格對就於那些目錄下的目錄和檔案。因而，作業系統的敏感度決定資料庫和表命名的大小寫敏感。
表命名
同一個模組的表盡可能使用相同的前綴，表名稱盡可能表達意義。所有日誌表均以 log_ 開頭
欄位命名
索引命名
約束命名
表格設計規格
##表引擎取決於實際應用場景；日誌及報表類表建議用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，用來標示資料是否被刪除，原則上資料庫資料不允許物理刪除。
datetime與timestamp有何不同？
相同點：
TIMESTAMP欄位的顯示格式與DATETIME欄位相同。顯示寬度固定在19字符，且格式為YYYY-MM-DD HH:MM:SS。
不同點：
TIMESTAMP
如何使用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來儲存。
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)，以獲得效能提升。
索引的基本規格
#為什麼一張表中不能存在過多的索引？
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));
主鍵準則
區分度最大的欄位放在前面
索引禁忌#
MYSQL 中索引的限制
MYISAM 儲存引擎索引長度的總和不能超過1000 位元組
BLOB 和TEXT 類型的欄位只能建立前綴索引
MYSQL 目前不支援函數索引
使用不等於(!= 或<>) 的時候, MYSQL 無法使用索引。
過濾欄位使用函數運算 (如 abs (column)) 後, MYSQL無法使用索引。
join語句中join條件字段類型不一致的時候MYSQL無法使用索引
使用 LIKE 操作的時候如果條件以通配符開始 (如 ‘�c…')時, MYSQL無法使用索引。
使用非等值查詢的時候, MYSQL 無法使用 Hash 索引。
語句設計規格
使用預編譯語句
#避免隱含轉換
會導致索引失效
充分利用前綴索引
不使用負向查詢，如not in/like
避免使用預存程序、觸發器、UDF、events等
避免使用大表的JOIN
#MySQL最擅長的是單表的主鍵/二級索引查詢
JOIN消耗較多內存，產生臨時表
避免在資料庫中進行數學運算
減少與資料庫的互動次數
#合理的使用分頁
限制分頁顯示的頁數只能點選上一頁、下一頁採用延遲關聯
如何正確的使用分頁？
假如有類似下面分頁語句：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)
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
分表规范
单表一到两年内数据量超过500w或数据容量超过10G考虑分表，需提前考虑历史数据迁移或应用自行删除历史数据，采用等量均衡分表或根据业务规则分表均可。要分表的数据表必须与DBA商量分表策略
行为规范
其他规范
日志类数据不建议存储在MySQL上，优先考虑Hbase或OceanBase，如需要存储请找DBA评估使用压缩表存储。
相关图文教程：mysql数据库图文教程
以上是史上最全MySQL使用規範分享的詳細內容。更多資訊請關注PHP中文網其他相關文章！
