前言 | 筆記歸檔
這周公司開發工作比較悠閒，工作幾乎壓在設計上游，於是整理了下公司開發的文件，包括專案架構、伺服器運維、規範、api對接、基本依賴資訊等。如下是包含其中的MySQL開發規範，根據社群很多的博文參考以及結合自身小團隊開發情況總結。
命名規範
物件名稱必須使用小寫，多單詞統一使用下劃線分割
命名的單詞必須做到顧名思義、簡潔，表名長度不要超過16個字元，欄位名稱長度不要超過32個字元
禁止使用保留字並且儘量少用含有關鍵詞來命名
臨時表必須以
tmp_開頭、以日期結尾，備份表必須以bak_開頭、以日期結尾
基礎規範
儘可能地使用
InnoDB作為表的儲存引擎在
MySQL 5.6以後，InnoDB被設定成預設的儲存引擎，支援事務和行級鎖。資料庫和資料表統一使用
UTF8MB4字元編碼UTF8MB4字元編碼支援中文儲存以及表情儲存，相容性槓槓的。所有的表和欄位必須新增註釋
這個是好習慣的問題，即使做到了顧名思義，以防萬一哪天健忘或理解錯誤，同時給後人留下後路，提高維護性。使用
comment設定註釋。儘量控制錶行數在500萬以內
資料量越多，則查詢的效率越低，同時會導致長時間佔用高記憶體以及磁碟
IO過高。資料量膨大建議採用分表、合理分割槽等方案。儘可能採用冷熱資料分離策略
在
MySQL中，資料表列數最大限制為4096列 ，每條元祖資料總和大小不能超過65535位元組，常用的欄位與基本不常用的欄位、細分不同業務的資料分開表設計儲存，減小表寬度，保證熱資料的記憶體快取命中率，降低CPU使用率以及降低IO流。禁止以圖片、檔案等二進位制資料
MySQL雖然支援對檔案物件的儲存，但是開發人員是不允許、不推薦這樣做的。檔案通常是很大的，轉成二進位制資料將是一串很長的字串，無疑佔用資料庫很大的儲存空間，在資料庫讀寫更是消耗記憶體和佔用大量的IO流，最終導致查詢的效率低下。一般檔案是存放於檔案伺服器，將檔案伺服器的路徑儲存於資料庫中。
行為與流程規範
禁止線上上做資料庫的壓力測試
對應的環境使用對應的資料庫比如測試環境一定要使用測試環境的資料庫
super許可權只能屬於DBA，不能賦予專案程式養成檢視
SQL執行效能的習慣，可以借用效能分析工具譬如：
EXPLAIN語句 |showprofile|mySQLsla等。禁止在業務高峰期批次更新、查詢資料
可以在流量比較低的凌晨跑批操作。
活動推廣、系統上線以及平臺上新務必對流量進行評估
防患於未然、否則可能造成資料庫伺服器流量瓶頸進而導致影響業務。
所有建表前都要確定欄位的型別、長度以及索引方可建表
確保表結構設計為最優是前期資料庫最大的最佳化
所有對錶的結構、資料的修改務必經過
DBA的審閱和同意
表設計規範
儘可能每張表的索引數量控制在5個以內
索引具有提高查詢的效率的好處也有降低寫操作效率的壞處，甚至會降低查詢到的效率。同時索引也是佔用記憶體空間的，因而應該合理控制索引的數量。
每一張
InnoDB表都必須含有一個主鍵InnoDB 是一種索引組織表：資料的儲存的邏輯順序和索引的順序是相同的。每個表都可以有多個索引，但是表的儲存順序只能有一種 InnoDB是按照主鍵索引的順序來組織表的。不要使用可能會更新的列作為主鍵，同時儘量不要使用
UUID、MD5、HASH等無序的字串作為主鍵。在沒有特別的情況下，要使用自增的整型或發號器作為主鍵。儘可能避免使用外來鍵約束
外來鍵可以保證資料的準確性、參照完整性，每次進行寫操作時都會走校驗資料知否正確的流程，將會有損寫操作的效能，資料的參照完整性建議在業務層實現。倘若字表的寫操作很少的情況下務必使用外來鍵約束。
設定資料表架構應考慮後期擴充套件型
體驗產品和架構師的交流和能力、對業務的熟悉度。
遵循正規化與冗餘平衡原則
第一正規化：具有原子性
第二正規化：主鍵列與非主鍵列遵循完全函式依賴關係
第三正規化：非主鍵列之間沒有傳遞函式依賴關係
合理的原則能夠體驗出資料庫的可操作性、穩定性以及效能
nice。正規化設計是資料結構的一種思想，但是我們應當靈活使用，一味追求三正規化無疑會影響程式的效能，適當的冗餘是可以提高查詢的效率的，前提要保證是主鍵的冗餘。控制每張表的欄位在20以內，否則業務分表
資料表的寬度與記憶體佔用的大小成正比，在進行讀寫操作時，資料庫程式將表結構與資料載入記憶體，當表寬度越長消耗的記憶體越多、越佔
IO流，導致操作的效率下降。將可能將欄位按照業務細分、冷熱的條件進行分表設計。
欄位設計規範
儘可能不要在表中建立顧名思義的擴充套件欄位
比如
ext、ext_1、extend_n，時間一長，好幾個這樣的欄位，即使每一個都有comment，也會降低SQL的可讀性，特別是在構建SQL語句的時候。優先設定佔儲存空間最小的型別和長度
合理設定欄位的型別和長度，可以節省
MySQL的表空間，是效能最佳化的姿勢之一。同時，索引列定義空間越大也會導致建立索引的所需空間也越大。應當嚴禁定義欄位，譬如IP應使用UNSUGNED或者INT結構型別，在PHP中可以使用long2ip與ip2long函式進行互轉性別應使用
CHAR(1)，即定長的字串型別… …
儘可能避免使用
TEXT、BLOB、ENUM資料型別MySQL 記憶體臨時表不支援
TEXT、BLOB這樣的大資料型別，如果查詢中包含這樣的資料，在排序等操作時，就不能使用記憶體臨時表，必須使用磁碟臨時表進行，毋庸置疑會降低查詢的效率。MySQL對索引欄位長度是有限制的，TEXT或BLOB型別只能使用字首索引。避免
ENUM資料型別在
MySQL中，儲存列舉型別的資料在庫中，欄位列中儲存的值實際為整數，特別容易導致開發者混亂，同時在查詢使用排序是基於數值整型的，雖然可以使用ORDER BY FIELD(),但是會導致索引失效，儘量避免這麼做。儘可能將所有的資料列定義為
NOT NULL型別NULL列比較特殊，需要額外的空間來儲存，同時會造成索引失效。使用
TIMESTAMP與INT替換DATETIME儲存時間很明顯，
TIMESTAMP與INT佔4位位元組，而DATETIME佔8位位元組。那麼儲存時間應該如何選擇TIMESTAMP與INT呢？TIMESTAMP的可讀性高而INT的靈活性高，因而經常需要使用計算操作的應當使用INT儲存，否則使用TIMESTAMP。金額相關的資料必須使用
DECIMAL資料型別談到錢這個東西呢，精確是非常重要的，即便要浪費儲存空間、笑?~
DECIMAL型別為精準浮點數，在計算時不會丟失精度，可以自定義其長度，可用於儲存比 bigint 更大的整型資料。表與表關聯的鍵名保持一致或以關聯表名的縮寫為字首
規範事項，保持規範、養成習慣，提高程式的可讀性。
固定長度的字串欄位務必使用
CHAR節省存空間、降低記憶體使用率、提高讀寫效能。
使用
UNSIGNED儲存非負整數節省存空間、降低記憶體使用率、提高讀寫效能。
禁止敏感資料以明文形式儲存
確保資訊的安全性，比如密碼、隱秘資料等。
索引規範
重要的
SQL語句必須帶上索引作為條件避免冗餘和重複索引
重複索引： 在相同的列上按照相同的順序建立的相同型別的索引。
冗餘索引： 兩個索引按照相同的順序覆蓋了相同的列。
在一張使用者表裡面，將使用者
id設定成主鍵的同時再設定成唯一索引，那就是重複索引，如果建立了索引(a,b)，再設定a索引，則a為冗餘索引，這兩種錯誤的操作都會降低讀寫的效能。務必不要在作為查詢條件很少、或者沒有關聯的欄位下建立索引
索引本身佔用儲存空間，過多設定會導致查詢效率降低。比如在成績表中將分數設定為索引，這是一種錯誤的做法。
禁止在索引列進行數學運算和函式運算
MySQL不擅長於運算，需要計算的應該移至程式碼業務層。總而言之，凡是計算都要移至程式碼業務層(MySQL不擅長於運算)。符合索引將區分度高的置前
將區分度高的索引置前可以縮短查詢的範圍，以至提高查詢的效率，特別是在
JOIN連表查詢，提高效率特別明顯。
SQL使用規範
危險的
SQL語句必須帶上索引作為條件，謹記謹記哪些是危險的
SQL語句呢，刪、改皆為危險的語句，一定要記住帶上WHERE。建議使用預編譯語句運算元據庫
先簡單瞭解下
SQL執行的流程，SQL先解析、預編譯處理再生成執行計劃，最後呼叫引擎的api方法返回執行的結果，使用預編譯的操作姿勢，在讀寫的時候可以省去預編譯的時間，終而提高執行效率。嚴禁使用
SELECT *查詢欄位要什麼
SELECT什麼，不能多，否則可能導致覆蓋索引失效，消耗更多的CPU和IO以網路頻寬資源。查詢語句務必帶上索引以提高查詢效率
必須避免資料型別隱式轉換
在
MySQL中，資料會存在隱式轉換，當該欄位發生轉換時，索引會造成失效。充分利用利用索引優勢
既然設定了索引就好好充分利用好索引，將查詢的效率提至最高。
禁止使用相同的賬號跨庫操作
各執其職，互不越權。
禁止使用帶有資料值卻不帶有欄位鍵名的
INSERT操作這是一種錯誤的做法，對於表的改動後會造成比較大的影響。
INSERT INTO user VALUES ('alicfeng',23); # 應該這樣操作 INSERT INTO user (`username`,`age`) VALUES ('alicfeng',23);儘可能使用
JOIN替代子查詢操作子查詢的結果集無法使用索引，通常子查詢的結果集會被儲存到臨時表中，不論是記憶體臨時表還是磁碟臨時表都不會存在索引，所以查詢效能會受到一定的影響。 特別是對於返回結果集比較大的子查詢，其對查詢效能的影響也就越大。 由於子查詢會產生大量的臨時表也沒有索引，所以會消耗過多的
CPU和IO資源，產生大量的慢查詢。儘可能避免使用
JOIN關聯過多的表一般情況下，建議
JOIN的表不要超過5個，JOIN多表查詢比較耗時時間，關聯的表越多越耗時間，防止執行超時或死鎖。合併操作、減少資料庫的互動
可以靈活地合併
SQL操作，降低IO消耗的同時也提高了執行效率，譬如UPDATE user SET username='alicfeng' FROM id=1995; UPDATE user SET age=23 FROM id=1995; # 合併操作成一條SQL UPDATE user SET username='alicfeng',age=23 FROM id=1995;儘可能使用
IN代替OR語句禁止使用
ORDER BY RAND()隨機排序語句會把表中所有符合條件的資料裝載到記憶體中，然後在記憶體中對所有資料根據隨機生成的值進行排序，並且可能會對每一行都生成一個隨機值，如果滿足條件的資料集非常大，就會消耗大量的
CPU和IO及記憶體資源。禁止在
WHERE語句中進行計算對列進行函式轉換或計算時會導致無法使用索引。
# 索引會失效 WHERE DATE(create_date)='20190308'; # 靈活使用[推薦] WHERE create_date>='20190308' AND create_date<'20190309';使用
UNION ALL而不是使用UNION在已知資料沒有重複或無須刪除重複行的前提下，因為
UNION需要重複值掃描，降低效率。大批次寫操作儘可能合理地分批次處理
大批次的操作應當合理平均分批次處理，防止死鎖影響業務，同時儘量將跑批這種大操作至於凌晨操作。
不在資料庫做運算，務必將運算置於業務層
MySQL不擅長數學運算和邏輯判斷。禁止使用索引做運算
索引會失效。
SQL語句簡單化使用事務儘量簡單化，同時控制事務執行的時間
時間長會導致長時間鎖表，造成死鎖，進而影響業務。
IN語句引數的個數儘量控制在1000以內注意
LIMIT分頁查詢效率，LIMIT越大效率越低在使用
LIMIT做分頁時，更改巧妙地處理查詢，譬如使用S1替換成S2，將有效地提高查詢的效率。# S1 SELECT `username` FROM `user` LIMIT 10000,20; # S2 SELECT `username` FROM `user` WHERE id>10000 LIMIT 20;編寫
SQL語句必須全部為大寫，每個詞必只允許只有一個空格符編寫規範，必須統一併遵循。
儘可能使用
EXIST|NOT EXIST替代IN | NOT IN禁止使用
LIKE新增%字首進行模糊查詢%前置會導致索引失效禁止一條語句同時對多個表進行寫操作
本作品採用《CC 協議》，轉載必須註明作者和本文連結