主頁(yè) > 知識(shí)庫(kù) > MySQL 數(shù)據(jù)庫(kù)鐵律(小結(jié))

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

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

1.建庫(kù)鐵律

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

2.建表鐵律

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

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

3.建索引鐵律

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

4.寫(xiě)SQL鐵律

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

5.ORM映射鐵律

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

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

您可能感興趣的文章:

Mysql數(shù)據(jù)庫(kù)清理binlog日志命令詳解
mysql如何比對(duì)兩個(gè)數(shù)據(jù)庫(kù)表結(jié)構(gòu)的方法
MySQL查看數(shù)據(jù)庫(kù)表容量大小的方法示例
利用MySQL系統(tǒng)數(shù)據(jù)庫(kù)做性能負(fù)載診斷的方法
mysql創(chuàng)建數(shù)據(jù)庫(kù),添加用戶,用戶授權(quán)實(shí)操方法

標(biāo)簽：湖南銅川崇左蘭州湘潭黃山仙桃衡水

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL 數(shù)據(jù)庫(kù)鐵律(小結(jié))》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。