MySQL 數(shù)據(jù)庫鐵律(小結(jié))

熱門標(biāo)簽：銅陵防封電銷卡 怎么在地圖標(biāo)注位置生成圖片 美國反騷擾電話機器人 電銷卡外呼系統(tǒng)供應(yīng)商 400電話可以免費申請嗎 真人語音電話機器人 福建外呼系統(tǒng)定制化 悟空科技電話機器人 騰訊地圖標(biāo)注提升

好的數(shù)據(jù)庫規(guī)范有助于減少軟件實現(xiàn)的復(fù)雜度，降低溝通成本，本鐵律主要涵蓋了建庫建表、建索引、寫 SQL、ORM 映射等方面的處理約定。

1.建庫鐵律

-	鐵律	Level	備注
字符集	使用 utf-8。如果存儲的是表情則選用 utf8mb4 進行存儲。	強制
排序規(guī)則	使用 utf8_general_ci	強制

2.建表鐵律

-	鐵律	Level	備注
注釋	一定要有字段注釋。	強制
編碼	使用 utf-8。如果存儲的是表情則選用 utf8mb4 進行存儲。	強制
是否概念的字段	必須用 is_xx 命名，數(shù)據(jù)類型是 unsigned tinyint（1是0否）例如 is_deleted（1刪除0未刪除）。	強制	任何字段如果非負數(shù)必須unsigned
表名、字段名	只能使用小寫字母、下劃線或者數(shù)字；禁止以下劃線或者數(shù)字開頭；禁止兩個下劃線之間只出現(xiàn)數(shù)字；禁用保留字；表名禁止使用復(fù)數(shù)名詞。	強制
庫名、表名的命名	庫名盡量與應(yīng)用名稱一致，表名最好用 業(yè)務(wù)名稱_表的作用 命名。	強制
索引命名	主鍵索引用 pk_字段名；唯一索引用 uk_字段名；普通索引用 idx_字段名。	強制	pk_ 即 primary key；uk_即 unique key；idx_即 index
小數(shù)類型	數(shù)據(jù)類型是 decimal，禁止使用 float 和 double，float 和 double 存在精度損失，如果存儲的數(shù)據(jù)范圍超過 decimal 的范圍，建議將數(shù)據(jù)拆成整數(shù)和小數(shù)并分開存儲。	強制
varchar類型	varchar是可變長字符串，不預(yù)先分配存儲空間，長度不要超過5000個字符，如果長度大于5000應(yīng)用text（獨立出一張表來，用主鍵來對應(yīng)，避免影響其他字段的索引效率）。	強制
表名必備三字段	id（數(shù)據(jù)類型是 unsigned bigint，單表遞增，步長為1），gmt_create、gmt_modified（主動創(chuàng)建時間、被動更新時間，數(shù)據(jù)類型都是 datetime）。	強制
字段冗余	字段允許適當(dāng)冗余，但必須考慮數(shù)據(jù)一致，冗余字段應(yīng)具備1）不頻繁修改；2）不是varchar超長字段，更不能是text字段。	推薦
分庫分表	單表行數(shù)超過500萬行或者單表容量超過2GB時，才推薦分庫分表。	推薦

設(shè)置合適的字符存儲長度，不但可以節(jié)約數(shù)據(jù)庫表空間和索引存儲，更重要的是能夠提升檢索速度。

3.建索引鐵律

-	鐵律	Level	備注
唯一索引	業(yè)務(wù)上具有唯一特性的字段，即使是多個字段的組合，也必須建成唯一索引。雖然唯一索引影響了 insert 速度，這個損耗可以忽略，但是明顯提高了查詢速度；另外，即使在應(yīng)用層做了非常完善的校驗控制，只要沒有唯一索引，根據(jù)墨菲定律，必然有臟數(shù)據(jù)產(chǎn)生。	強制
join	超過三個表禁止 join，需要 join 的字段，數(shù)據(jù)類型必須一致；當(dāng)多表關(guān)聯(lián)查詢時，保證被關(guān)聯(lián)的字段需要有索引；即使雙表 join 也要注意表索引、SQL 性能。	強制
varchar字段上建立索引	必須指定索引長度，沒必要對全字段建立索引，根據(jù)實際文本區(qū)分度決定索引長度即可。索引長度與區(qū)分度是一對矛盾體，一般對字符串類型數(shù)據(jù)，長度為 20 的索引，區(qū)分度會高達 90% 以上，可以使用 count(distinct left(列名, 索引長度))/count(*) 的區(qū)分度來確定。	強制
頁面搜索禁止模糊	頁面搜索禁止左模糊或者全模糊，如果有需要請走搜索引擎來解決。禁止原因：索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引。	強制
order by	如果有 order by 的場景，請注意索引的有序性。order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現(xiàn) file_sort 的情況，影響查詢性能。正例：where a=? and b=? order by c; 索引應(yīng)建為 a_b_c；反例：索引中有范圍查找，那么索引有序性無法利用，如 where a>10 order by b; 索引 a_b 無法排序。	推薦

4.寫SQL鐵律

-	鐵律	Level	備注
count(*)	不要使用 count(列名) 或 count(常量) 來替代 count(*)，count(*) 是 SQL92 定義的標(biāo)準(zhǔn)統(tǒng)計行數(shù)的語法，跟數(shù)據(jù)庫無關(guān)，跟 NULL 和非 NULL 無關(guān)。count(*) 會統(tǒng)計值為 NULL 的行，而 count(列名) 不會統(tǒng)計此列為 NULL 的行。	強制
count(distinct col)	計算該列除 NULL 外的不重復(fù)行數(shù)。注意，count(distinct col1, col2)，如果其中一列全為 NULL，那么即使另一列有不同的值，也返回為 0。	強制
sum(col)	當(dāng)一列的值全為 NULL 時，count(col) 的返回結(jié)果為 0，但 sum(col) 的返回結(jié)果為 NULL，因此使用 sum() 時需要注意 NPE 問題?？捎萌缦路绞奖苊?NPE 問題：select if(isnull(sum(g)), 0, sum(g)) from table;	強制
isnull	使用 isnull() 來判斷是否為 NULL 值。NULL 與任何值的比較都為 NULL。	強制
分頁查詢邏輯	若 count 為 0 應(yīng)直接返回，避免執(zhí)行后面的分頁語句。	強制
外鍵與級聯(lián)	禁止使用外鍵與級聯(lián)，一切外鍵概念必須在應(yīng)用層解決。原因：外鍵與級聯(lián)不適合分布式、高并發(fā)集群，級聯(lián)更新是強阻塞，存在數(shù)據(jù)庫更新風(fēng)暴的風(fēng)險，外鍵影響數(shù)據(jù)庫的插入速度。	強制
存儲過程	禁止使用存儲過程，存儲過程難以調(diào)試和擴展，更沒有移植性。	強制
數(shù)據(jù)訂正	數(shù)據(jù)訂正（特別是刪除、修改記錄操作）時要先 select，避免出現(xiàn)誤刪除，確認(rèn)無誤后才能執(zhí)行更新語句。	強制
in	in 操作能避免就避免，如果實在避免不了，in 后面的集合元素數(shù)量要控制在 1000 個以內(nèi)。	推薦
truncate table	禁止使用 truncate table，truncate table 比 delete 速度快，且使用的系統(tǒng)和日志資源少，但是 truncate 無事務(wù)且不觸發(fā) trigger，有可能造成事故，故不要在開發(fā)代碼中使用此語句。	參考

5.ORM映射鐵律

-	鐵律	Level	備注
表查詢	禁止使用 * 作為查詢的字段列表，需要哪些字段必須明確。	強制
POJO	POJO 類的布爾屬性不能加 is，而數(shù)據(jù)庫字段必須加 is，要求在 resultMap 中進行字段與屬性之間的映射。	強制
返回參數(shù)	禁止用 resultClass 作為返回參數(shù)，即使所有類屬性名與數(shù)據(jù)庫字段一一對應(yīng)，也需要定義；反過來，每一個表也必然有一個屬性與之對應(yīng)。原因：配置映射關(guān)系，使字段與 DO 類結(jié)耦，方便維護。	強制
返回參數(shù)	禁止直接使用 HashMap、HashTable 作為查詢結(jié)果集的輸出。原因：屬性值的類型不可控。	強制
sql.xml 配置參數(shù)	sql.xml 配置參數(shù)使用 #{}, #param#，不要使用 ${}，${} 容易出現(xiàn)SQL注入。	強制
queryForList	禁止使用 Mybatis 自帶的 queryForList(String statementName, int start, int size)。原因：其實現(xiàn)方式是在數(shù)據(jù)庫取到 statementName 對應(yīng)的 SQL 語句的所有記錄，再通過 subList 取 start, size 的子集合。	強制
更新時間	更新數(shù)據(jù)庫表記錄時，必須同時更新記錄對應(yīng)的修改時間。	強制
更新數(shù)據(jù)庫表記錄	不要寫一個大而全的數(shù)據(jù)更新接口（傳入為 POJO 類）。執(zhí)行 SQL 時，不要更新無改動的字段，原因：容易出錯、效率低、增加 binlog 存儲。	推薦
@Transactional	@Transactional 事務(wù)不要濫用。事務(wù)會影響數(shù)據(jù)庫的 QPS。另外，使用事務(wù)的地方需要考慮各方面的回滾方案，包括緩存回滾、搜索引擎回滾、消息補償、統(tǒng)計修正等。	參考
Mybatis 動態(tài)sql標(biāo)簽	isEqual> 中的 compareValue 是與屬性值對比的常量，一般是數(shù)字，表示相等時執(zhí)行相應(yīng)的 SQL 語句； isNotEmpty> 表示不為空且不為 null 時執(zhí)行； isNotNull> 表示不為 null 時執(zhí)行。	參考

以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。