MySQL 數(shù)據(jù)庫鐵律(小結(jié))
更新時(shí)間：2019年09月09日 09:38:51 作者：郭朝
這篇文章主要介紹了MySQL 數(shù)據(jù)庫鐵律，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
好的數(shù)據(jù)庫規(guī)范有助于減少軟件實(shí)現(xiàn)的復(fù)雜度，降低溝通成本，本鐵律主要涵蓋了建庫建表、建索引、寫 SQL、ORM 映射等方面的處理約定。
1.建庫鐵律
| - | 鐵律 | 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 |
| 表名、字段名 | 只能使用小寫字母、下劃線或者數(shù)字；禁止以下劃線或者數(shù)字開頭；禁止兩個(gè)下劃線之間只出現(xiàn)數(shù)字；禁用保留字；表名禁止使用復(fù)數(shù)名詞。 | 強(qiáng)制 | |
| 庫名、表名的命名 | 庫名盡量與應(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ù)范圍超過 decimal 的范圍，建議將數(shù)據(jù)拆成整數(shù)和小數(shù)并分開存儲(chǔ)。 | 強(qiáng)制 | |
| varchar類型 | varchar是可變長字符串，不預(yù)先分配存儲(chǔ)空間，長度不要超過5000個(gè)字符，如果長度大于5000應(yīng)用text（獨(dú)立出一張表來，用主鍵來對應(yīng)，避免影響其他字段的索引效率）。 | 強(qiáng)制 | |
| 表名必備三字段 | id（數(shù)據(jù)類型是 unsigned bigint，單表遞增，步長為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超長字段，更不能是text字段。 | 推薦 | |
| 分庫分表 | 單表行數(shù)超過500萬行或者單表容量超過2GB時(shí)，才推薦分庫分表。 | 推薦 |
設(shè)置合適的字符存儲(chǔ)長度，不但可以節(jié)約數(shù)據(jù)庫表空間和索引存儲(chǔ)，更重要的是能夠提升檢索速度。
3.建索引鐵律
| - | 鐵律 | Level | 備注 |
|---|---|---|---|
| 唯一索引 | 業(yè)務(wù)上具有唯一特性的字段，即使是多個(gè)字段的組合，也必須建成唯一索引。雖然唯一索引影響了 insert 速度，這個(gè)損耗可以忽略，但是明顯提高了查詢速度；另外，即使在應(yīng)用層做了非常完善的校驗(yàn)控制，只要沒有唯一索引，根據(jù)墨菲定律，必然有臟數(shù)據(jù)產(chǎn)生。 | 強(qiáng)制 | |
| join | 超過三個(gè)表禁止 join，需要 join 的字段，數(shù)據(jù)類型必須一致；當(dāng)多表關(guān)聯(lián)查詢時(shí)，保證被關(guān)聯(lián)的字段需要有索引；即使雙表 join 也要注意表索引、SQL 性能。 | 強(qiáng)制 | |
| varchar字段上建立索引 | 必須指定索引長度，沒必要對全字段建立索引，根據(jù)實(shí)際文本區(qū)分度決定索引長度即可。索引長度與區(qū)分度是一對矛盾體，一般對字符串類型數(shù)據(jù)，長度為 20 的索引，區(qū)分度會(huì)高達(dá) 90% 以上，可以使用 count(distinct left(列名, 索引長度))/count(*) 的區(qū)分度來確定。 | 強(qiáng)制 | |
| 頁面搜索禁止模糊 | 頁面搜索禁止左模糊或者全模糊，如果有需要請走搜索引擎來解決。禁止原因：索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引。 | 強(qiáng)制 | |
| 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)計(jì)行數(shù)的語法，跟數(shù)據(jù)庫無關(guān)，跟 NULL 和非 NULL 無關(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 問題?？捎萌缦路绞奖苊?NPE 問題：select if(isnull(sum(g)), 0, sum(g)) from table; | 強(qiáng)制 | |
| isnull | 使用 isnull() 來判斷是否為 NULL 值。NULL 與任何值的比較都為 NULL。 | 強(qiáng)制 | |
| 分頁查詢邏輯 | 若 count 為 0 應(yīng)直接返回，避免執(zhí)行后面的分頁語句。 | 強(qiáng)制 | |
| 外鍵與級(jí)聯(lián) | 禁止使用外鍵與級(jí)聯(lián)，一切外鍵概念必須在應(yīng)用層解決。原因：外鍵與級(jí)聯(lián)不適合分布式、高并發(fā)集群，級(jí)聯(lián)更新是強(qiáng)阻塞，存在數(shù)據(jù)庫更新風(fēng)暴的風(fēng)險(xiǎn)，外鍵影響數(shù)據(jù)庫的插入速度。 | 強(qiáng)制 | |
| 存儲(chǔ)過程 | 禁止使用存儲(chǔ)過程，存儲(chǔ)過程難以調(diào)試和擴(kuò)展，更沒有移植性。 | 強(qiáng)制 | |
| 數(shù)據(jù)訂正 | 數(shù)據(jù)訂正（特別是刪除、修改記錄操作）時(shí)要先 select，避免出現(xiàn)誤刪除，確認(rèn)無誤后才能執(zhí)行更新語句。 | 強(qiáng)制 | |
| in | in 操作能避免就避免，如果實(shí)在避免不了，in 后面的集合元素?cái)?shù)量要控制在 1000 個(gè)以內(nèi)。 | 推薦 | |
| truncate table | 禁止使用 truncate table，truncate table 比 delete 速度快，且使用的系統(tǒng)和日志資源少，但是 truncate 無事務(wù)且不觸發(fā) trigger，有可能造成事故，故不要在開發(fā)代碼中使用此語句。 | 參考 |
5.ORM映射鐵律
| - | 鐵律 | Level | 備注 |
|---|---|---|---|
| 表查詢 | 禁止使用 * 作為查詢的字段列表，需要哪些字段必須明確。 | 強(qiáng)制 | |
| POJO | POJO 類的布爾屬性不能加 is，而數(shù)據(jù)庫字段必須加 is，要求在 resultMap 中進(jìn)行字段與屬性之間的映射。 | 強(qiáng)制 | |
| 返回參數(shù) | 禁止用 resultClass 作為返回參數(shù)，即使所有類屬性名與數(shù)據(jù)庫字段一一對應(yīng)，也需要定義；反過來，每一個(gè)表也必然有一個(gè)屬性與之對應(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ù)庫取到 statementName 對應(yīng)的 SQL 語句的所有記錄，再通過 subList 取 start, size 的子集合。 | 強(qiáng)制 | |
| 更新時(shí)間 | 更新數(shù)據(jù)庫表記錄時(shí)，必須同時(shí)更新記錄對應(yīng)的修改時(shí)間。 | 強(qiáng)制 | |
| 更新數(shù)據(jù)庫表記錄 | 不要寫一個(gè)大而全的數(shù)據(jù)更新接口（傳入為 POJO 類）。執(zhí)行 SQL 時(shí)，不要更新無改動(dòng)的字段，原因：容易出錯(cuò)、效率低、增加 binlog 存儲(chǔ)。 | 推薦 | |
| @Transactional | @Transactional 事務(wù)不要濫用。事務(wù)會(huì)影響數(shù)據(jù)庫的 QPS。另外，使用事務(wù)的地方需要考慮各方面的回滾方案，包括緩存回滾、搜索引擎回滾、消息補(bǔ)償、統(tǒng)計(jì)修正等。 | 參考 | |
| Mybatis 動(dòng)態(tài)sql標(biāo)簽 | < isEqual> 中的 compareValue 是與屬性值對比的常量，一般是數(shù)字，表示相等時(shí)執(zhí)行相應(yīng)的 SQL 語句；< isNotEmpty> 表示不為空且不為 null 時(shí)執(zhí)行；< isNotNull> 表示不為 null 時(shí)執(zhí)行。 | 參考 |
以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
相關(guān)文章
本篇文章小編為大家介紹，跟著hsp一步步學(xué)mysql優(yōu)化的方法。需要的朋友參考下2013-04-04
MySQL如何從數(shù)據(jù)庫中刪除表中所有數(shù)據(jù)
這篇文章主要介紹了MySQL如何從數(shù)據(jù)庫中刪除表中所有數(shù)據(jù)問題，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-06-06
前綴索引，并不是一個(gè)萬能藥，他的確可以幫助我們對一個(gè)寫過長的字段上建立索引。但也會(huì)導(dǎo)致排序（order by ，group by）查詢上都是無法使用前綴索引的2013-05-05
mysql查詢每小時(shí)數(shù)據(jù)和上小時(shí)數(shù)據(jù)的差值實(shí)現(xiàn)思路詳解
這篇文章主要介紹了mysql查詢每小時(shí)數(shù)據(jù)和上小時(shí)數(shù)據(jù)的差值,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-04-04
ORM 全稱是（Object Relational Mapping）表示對象關(guān)系映射； 通俗理解可以理解為編程語言的虛擬數(shù)據(jù)庫；這篇文章主要介紹了ORM模型框架操作mysql數(shù)據(jù)庫的方法,需要的朋友可以參考下
2021-07-07 
我們都知道，binlog可以說是MySQL中比較重要的日志了，在日常學(xué)習(xí)及運(yùn)維過程中，也經(jīng)常會(huì)遇到。不清楚你對binlog了解多少呢？本篇文章將從binlog作用、binlog相關(guān)參數(shù)、解析binlog內(nèi)容三個(gè)方面帶你了解binlog
2021-06-06 
這篇文章主要為大家詳細(xì)介紹了MySQL中B樹索引和B+樹索引的區(qū)別，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下，希望能夠給你帶來幫助
2022-03-03 
本文給大家分享ubuntu下apt-get安裝和徹底卸載mysql的方法，文中提到了安裝mysq命令查看mysql是否運(yùn)行的命令寫法，具體內(nèi)容詳情大家通過本文學(xué)習(xí)吧
2017-08-08 
解決創(chuàng)建主鍵報(bào)錯(cuò):Incorrect column specifier for
這篇文章主要介紹了解決創(chuàng)建主鍵報(bào)錯(cuò):Incorrect column specifier for column‘id‘問題,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
2024-08-08