好的數據庫規范有助于減少軟件實現的復雜度，降低溝通成本，本鐵律主要涵蓋了建庫建表、建索引、寫 SQL、ORM 映射等方面的處理約定。
1.建庫鐵律
| - | 鐵律 | Level | 備注 |
|---|---|---|---|
| 字符集 | 使用 utf-8。如果存儲的是表情則選用 utf8mb4 進行存儲。 | 強制 | |
| 排序規則 | 使用 utf8_general_ci | 強制 |
2.建表鐵律
| - | 鐵律 | Level | 備注 |
|---|---|---|---|
| 注釋 | 一定要有字段注釋。 | 強制 | |
| 編碼 | 使用 utf-8。如果存儲的是表情則選用 utf8mb4 進行存儲。 | 強制 | |
| 是否概念的字段 | 必須用 is_xx 命名，數據類型是 unsigned tinyint（1是0否）例如 is_deleted（1刪除0未刪除）。 | 強制 | 任何字段如果非負數必須unsigned |
| 表名、字段名 | 只能使用小寫字母、下劃線或者數字；禁止以下劃線或者數字開頭；禁止兩個下劃線之間只出現數字；禁用保留字；表名禁止使用復數名詞。 | 強制 | |
| 庫名、表名的命名 | 庫名盡量與應用名稱一致，表名最好用 業務名稱_表的作用 命名。 | 強制 | |
| 索引命名 | 主鍵索引用 pk_字段名；唯一索引用 uk_字段名；普通索引用 idx_字段名。 | 強制 | pk_ 即 primary key；uk_即 unique key；idx_即 index |
| 小數類型 | 數據類型是 decimal，禁止使用 float 和 double，float 和 double 存在精度損失，如果存儲的數據范圍超過 decimal 的范圍，建議將數據拆成整數和小數并分開存儲。 | 強制 | |
| varchar類型 | varchar是可變長字符串，不預先分配存儲空間，長度不要超過5000個字符，如果長度大于5000應用text（獨立出一張表來，用主鍵來對應，避免影響其他字段的索引效率）。 | 強制 | |
| 表名必備三字段 | id（數據類型是 unsigned bigint，單表遞增，步長為1），gmt_create、gmt_modified（主動創建時間、被動更新時間，數據類型都是 datetime）。 | 強制 | |
| 字段冗余 | 字段允許適當冗余，但必須考慮數據一致，冗余字段應具備1）不頻繁修改；2）不是varchar超長字段，更不能是text字段。 | 推薦 | |
| 分庫分表 | 單表行數超過500萬行或者單表容量超過2GB時，才推薦分庫分表。 | 推薦 |
設置合適的字符存儲長度，不但可以節約數據庫表空間和索引存儲，更重要的是能夠提升檢索速度。
3.建索引鐵律
| - | 鐵律 | Level | 備注 |
|---|---|---|---|
| 唯一索引 | 業務上具有唯一特性的字段，即使是多個字段的組合，也必須建成唯一索引。雖然唯一索引影響了 insert 速度，這個損耗可以忽略，但是明顯提高了查詢速度；另外，即使在應用層做了非常完善的校驗控制，只要沒有唯一索引，根據墨菲定律，必然有臟數據產生。 | 強制 | |
| join | 超過三個表禁止 join，需要 join 的字段，數據類型必須一致；當多表關聯查詢時，保證被關聯的字段需要有索引；即使雙表 join 也要注意表索引、SQL 性能。 | 強制 | |
| varchar字段上建立索引 | 必須指定索引長度，沒必要對全字段建立索引，根據實際文本區分度決定索引長度即可。索引長度與區分度是一對矛盾體，一般對字符串類型數據，長度為 20 的索引，區分度會高達 90% 以上，可以使用 count(distinct left(列名, 索引長度))/count(*) 的區分度來確定。 | 強制 | |
| 頁面搜索禁止模糊 | 頁面搜索禁止左模糊或者全模糊，如果有需要請走搜索引擎來解決。禁止原因：索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引。 | 強制 | |
| order by | 如果有 order by 的場景，請注意索引的有序性。order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現 file_sort 的情況，影響查詢性能。正例：where a=? and b=? order by c; 索引應建為 a_b_c；反例：索引中有范圍查找，那么索引有序性無法利用，如 where a>10 order by b; 索引 a_b 無法排序。 | 推薦 |
4.寫SQL鐵律
| - | 鐵律 | Level | 備注 |
|---|---|---|---|
| count(*) | 不要使用 count(列名) 或 count(常量) 來替代 count(*)，count(*) 是 SQL92 定義的標準統計行數的語法，跟數據庫無關，跟 NULL 和非 NULL 無關。count(*) 會統計值為 NULL 的行，而 count(列名) 不會統計此列為 NULL 的行。 | 強制 | |
| count(distinct col) | 計算該列除 NULL 外的不重復行數。注意，count(distinct col1, col2)，如果其中一列全為 NULL，那么即使另一列有不同的值，也返回為 0。 | 強制 | |
| sum(col) | 當一列的值全為 NULL 時，count(col) 的返回結果為 0，但 sum(col) 的返回結果為 NULL，因此使用 sum() 時需要注意 NPE 問題?？捎萌缦路绞奖苊?NPE 問題：select if(isnull(sum(g)), 0, sum(g)) from table; | 強制 | |
| isnull | 使用 isnull() 來判斷是否為 NULL 值。NULL 與任何值的比較都為 NULL。 | 強制 | |
| 分頁查詢邏輯 | 若 count 為 0 應直接返回，避免執行后面的分頁語句。 | 強制 | |
| 外鍵與級聯 | 禁止使用外鍵與級聯，一切外鍵概念必須在應用層解決。原因：外鍵與級聯不適合分布式、高并發集群，級聯更新是強阻塞，存在數據庫更新風暴的風險，外鍵影響數據庫的插入速度。 | 強制 | |
| 存儲過程 | 禁止使用存儲過程，存儲過程難以調試和擴展，更沒有移植性。 | 強制 | |
| 數據訂正 | 數據訂正（特別是刪除、修改記錄操作）時要先 select，避免出現誤刪除，確認無誤后才能執行更新語句。 | 強制 | |
| in | in 操作能避免就避免，如果實在避免不了，in 后面的集合元素數量要控制在 1000 個以內。 | 推薦 | |
| truncate table | 禁止使用 truncate table，truncate table 比 delete 速度快，且使用的系統和日志資源少，但是 truncate 無事務且不觸發 trigger，有可能造成事故，故不要在開發代碼中使用此語句。 | 參考 |
5.ORM映射鐵律
| - | 鐵律 | Level | 備注 |
|---|---|---|---|
| 表查詢 | 禁止使用 * 作為查詢的字段列表，需要哪些字段必須明確。 | 強制 | |
| POJO | POJO 類的布爾屬性不能加 is，而數據庫字段必須加 is，要求在 resultMap 中進行字段與屬性之間的映射。 | 強制 | |
| 返回參數 | 禁止用 resultClass 作為返回參數，即使所有類屬性名與數據庫字段一一對應，也需要定義；反過來，每一個表也必然有一個屬性與之對應。原因：配置映射關系，使字段與 DO 類結耦，方便維護。 | 強制 | |
| 返回參數 | 禁止直接使用 HashMap、HashTable 作為查詢結果集的輸出。原因：屬性值的類型不可控。 | 強制 | |
| sql.xml 配置參數 | sql.xml 配置參數使用 #{}, #param#，不要使用 ${}，${} 容易出現SQL注入。 | 強制 | |
| queryForList | 禁止使用 Mybatis 自帶的 queryForList(String statementName, int start, int size)。原因：其實現方式是在數據庫取到 statementName 對應的 SQL 語句的所有記錄，再通過 subList 取 start, size 的子集合。 | 強制 | |
| 更新時間 | 更新數據庫表記錄時，必須同時更新記錄對應的修改時間。 | 強制 | |
| 更新數據庫表記錄 | 不要寫一個大而全的數據更新接口（傳入為 POJO 類）。執行 SQL 時，不要更新無改動的字段，原因：容易出錯、效率低、增加 binlog 存儲。 | 推薦 | |
| @Transactional | @Transactional 事務不要濫用。事務會影響數據庫的 QPS。另外，使用事務的地方需要考慮各方面的回滾方案，包括緩存回滾、搜索引擎回滾、消息補償、統計修正等。 | 參考 | |
| Mybatis 動態sql標簽 | < isEqual> 中的 compareValue 是與屬性值對比的常量，一般是數字，表示相等時執行相應的 SQL 語句；< isNotEmpty> 表示不為空且不為 null 時執行；< isNotNull> 表示不為 null 時執行。 | 參考 |
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持武林網。
新聞熱點
疑難解答
