共計 5747 個字符，預(yù)計需要花費(fèi) 15 分鐘才能閱讀完成。
自動寫代碼機(jī)器人，免費(fèi)開通
這篇文章給大家分享的是有關(guān) MySQL 數(shù)據(jù)庫的使用規(guī)范的內(nèi)容。丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考。一起跟隨丸趣 TV 小編過來看看吧。
一個平臺或系統(tǒng)隨著時間的推移和用戶量的增多，數(shù)據(jù)庫操作往往會變慢；而在 Java 應(yīng)用開發(fā)中數(shù)據(jù)庫更是尤為重要，絕大多數(shù)情況下數(shù)據(jù)庫的性能決定了程序的性能，如若前期埋下的坑越多到后期數(shù)據(jù)庫就會成為整個系統(tǒng)的瓶頸；因此，更規(guī)范化的使用 MySQL 在開發(fā)中是不可或缺的。
一、MySQL 數(shù)據(jù)庫命名規(guī)范
1、數(shù)據(jù)庫所有表前綴均使用項目名稱首字母縮寫；
2、數(shù)據(jù)庫所有對象名稱均使用小寫字母，并且單詞之間通過下劃線分開；
3、數(shù)據(jù)庫所有對象名稱禁止使用 MySQL 保留字及關(guān)鍵字，涉及到關(guān)鍵字的 SQL 查詢需要將關(guān)鍵字用單引號括起來；
4、數(shù)據(jù)庫所有對象名稱不超過 32 個字符，并且命名要遵循見名知意原則；
5、數(shù)據(jù)庫臨時表必須以 pro_tmp_ 為前綴并且以日期 20190917 為后綴，備份表必須以 pro_bac 為前綴并以時間戳為后綴；(pro 為項目名稱首字母縮寫)
6、數(shù)據(jù)庫所有存儲相同數(shù)據(jù)的列名和列類型必須保持一致。
二、MySQL 數(shù)據(jù)庫基本設(shè)計規(guī)范
1、若無特殊說明，建表時一律采用 Innodb 存儲引擎。
選擇合適的引擎可以提高數(shù)據(jù)庫性能，如 InnoDB 和 MyISAM，InnoDB 和 MyISAM 是許多人在使用 MySQL 時最常用的兩個表類型，這兩個表類型各有優(yōu)劣，視具體應(yīng)用而定；
基本的差別為：MyISAM 類型不支持事務(wù)處理等高級處理，而 InnoDB 類型支持；MyISAM 類型的表強(qiáng)調(diào)的是性能，其執(zhí)行數(shù)度比 InnoDB 類型更快，但是不提供事務(wù)支持，而 InnoDB 提供事務(wù)支持以及外部鍵等高級數(shù)據(jù)庫功能；
因此，其支持事務(wù)處理、支持外鍵、支持崩潰修復(fù)能力和并發(fā)控制是我們建表時首選的存儲引擎。
2、數(shù)據(jù)庫和表的字符集統(tǒng)一使用 UTF8
數(shù)據(jù)庫和表的字符集統(tǒng)一使用 utf8，若是有字段需要存儲 emoji 表情之類的，則將表或字段設(shè)置成 utf8mb4；因為，utf8 號稱萬國碼，其無需轉(zhuǎn)碼、無亂碼風(fēng)險且節(jié)省空間，而 utf8mb4 又向下兼容 utf8。
3、設(shè)計數(shù)據(jù)庫時所有表和字段必須添加注釋
使用 Comment 從句添加表和列的備注，或直接在數(shù)據(jù)庫連接工具的注釋欄添加注釋，從項目開始就進(jìn)行數(shù)據(jù)字典的維護(hù)。
使用 Comment 從句添加注釋如：
-- 1、創(chuàng)建表：CREATE TABLE t1(id varchar2(32) primary key,name VARCHAR2(8) NOT NULL,age number);
-- 2、添加表注釋：Comment on table t1 is 個人信息
-- 3、添加字段注釋：comment on column t1.id is id
comment on column t1.nameis 姓名
comment on column t1.age is 年齡 使用數(shù)據(jù)庫連接工具添加注釋：
4、單個表的數(shù)據(jù)量大小控制在 500 萬以內(nèi)
盡量控制單表數(shù)據(jù)量的大小，建議控制在 500 萬以內(nèi)；500 萬并不是 MySQL 數(shù)據(jù)庫的極限，但數(shù)據(jù)量太多不利于對表結(jié)構(gòu)進(jìn)行修改、備份和恢復(fù)數(shù)據(jù)，適當(dāng)采用分庫分表等手段來控制單表數(shù)據(jù)量的大小。
5、使用 MySQL 分區(qū)表需謹(jǐn)慎
分區(qū)是將一個表的數(shù)據(jù)按照某種方式，比如按照時間上的月份，分成多個較小的，更容易管理的部分，但是邏輯上仍是一個表；分區(qū)表在物理上表現(xiàn)為多個文件，在邏輯上仍表現(xiàn)為同一個表，需要謹(jǐn)慎選擇分區(qū)鍵；跨分區(qū)查詢效率可能會更低，建議使用物理分區(qū)表等方式管理大數(shù)據(jù)。
6、盡量滿足冷熱數(shù)據(jù)分離，減小表等寬度
MySQL 限制每個表最多存儲 4096 列，并且每一行數(shù)據(jù)的大小不超過 65535 字節(jié)，為了減少磁盤 IO 線程的開銷，就要適當(dāng)控制表的寬度，因為表越寬，把表裝載進(jìn)內(nèi)存緩沖池時所占用的內(nèi)存也就越大，就會消耗更多的 IO 線程；除此之外，為了保證熱數(shù)據(jù)的內(nèi)存緩存命中率，更有效的利用緩存，避免讀入無用的冷數(shù)據(jù)，盡量把經(jīng)常使用到的列放到同一個表中，避免不必要的關(guān)聯(lián)操作。
7、建立預(yù)留字段需謹(jǐn)慎
部分友人在設(shè)計數(shù)據(jù)庫表時，不僅設(shè)計了當(dāng)前所需要的字段，而且還在其中留出幾個字段作為備用。比方說，我設(shè)計了一個人員表（Person），其中已經(jīng)添加了各種必要的字段，包括姓名（Name）、性別（Sex）、出生年月日（birthday）等等；
為了以防萬一，比如之后可能 Person 表會涉及到畢業(yè)院校、工作單位、是否婚配和相片等信息，于是就加入 5 個 varchar2 型的字段，分別叫做 Text1、Text2……Text5；這一手操作看似防范于未然，其實也并不見得，因為大量預(yù)留字段會浪費(fèi)空間、預(yù)留字段不能做到見名知意、預(yù)留字段無法確認(rèn)存儲的數(shù)據(jù)類型且修改其字段類型還可能會造成鎖表等問題。
針對此等情況可以參考以下兩點(diǎn)解決方案：
如果數(shù)量很少，而且信息的性質(zhì)與原表密切相關(guān)，那么就可以直接在原表上增加字段，并將相關(guān)的數(shù)據(jù)更新進(jìn)去；
如果數(shù)量較大，或者并非是原表對象至關(guān)重要的屬性，那么就可以新增一個表，然后通過鍵值連接起來；
8、數(shù)據(jù)庫中禁止存儲圖片、文件等大的二進(jìn)制數(shù)據(jù)
若往數(shù)據(jù)庫表中存儲文件，而文件通常很大，當(dāng)數(shù)據(jù)庫進(jìn)行讀取操作時，會進(jìn)行大量的隨機(jī) IO 操作，大文件使得 IO 操作很耗時耗性能，造成短時間內(nèi)數(shù)據(jù)量快速增長；所以，通常將圖片、文件存儲在文件服務(wù)器中，數(shù)據(jù)庫只用于存儲文件地址信息。
三、MySQL 數(shù)據(jù)庫字段設(shè)計規(guī)范
1、優(yōu)先選擇符合存儲需要的最小的數(shù)據(jù)類型。
主要是考慮索引的性能，因為列的字段越大，建立索引時所需要的空間也越大，這樣一頁中能存儲的索引節(jié)點(diǎn)的數(shù)量也就越少，在遍歷時需要的 IO 次數(shù)也就越多，索引的性能也就越差。
2、避免使用 TEXT、BLOB 數(shù)據(jù)類型
避免使用 TEXT 和 BLOB 數(shù)據(jù)類型，其中最常見的 TEXT 類型可以存儲 64K 數(shù)據(jù)，MySQL 內(nèi)存臨時表不支持 TEXT、BLOB 這樣的大數(shù)據(jù)類型，若查詢中包含這樣的數(shù)據(jù)，在執(zhí)行排序等操作時就不能使用內(nèi)存臨時表，必須使用磁盤臨時表執(zhí)行操作；
TEXT 和 BLOB 類型只能使用前綴索引 (當(dāng)索引是很長的字符序列時，這個索引將會很占內(nèi)存，而且會很慢，這時候就會用到前綴索引了；所謂的前綴索引就是去索引的前面幾個字母作為索引，但是要降低索引的重復(fù)率，所以我們還必須要判斷前綴索引的重復(fù)率；)，因為 MySQL 對索引字段長度是有限的，所以 TEXT 類型只能使用前綴索引，并且 TEXT 列上是不能有默認(rèn)值的；
若需要使用，建議把 BLOB 或 TEXT 列分離到單獨(dú)的的擴(kuò)展表中，且查詢時一定不要使用 select *，只需取出必要的列即可。
3、避免使用 ENUM 枚舉類型
修改 ENUM 值需要使用 ALTER 語句；
ENUM 類型的 ORDER BY 操作效率低；
禁止使用數(shù)值作為 ENUM 的枚舉值。
4、所有列的默認(rèn)值定義為 NOT NULL
數(shù)據(jù)庫所有為 NULL 的列需要額外的空間來存儲，因此會占用更多的空間；
數(shù)據(jù)庫在進(jìn)行比較和計算時需要對 NULL 值做特別處理。
5、使用 TIMESTAMP(4 字節(jié)) 或 DATETIME(8 字節(jié)) 類型存儲時間
TIMESTAMP 存儲的時間范圍為：1970-01-01 00:00:01 ~ 2038-01-19-03:14:07；
TIMESTAMP 占用 4 字節(jié)和 INT 相同，但可讀性比 INT 類型的高，若是超出 TIMESTAMP 取值范圍的則使用 DATETIME 類型存儲；
用字符串類型存儲時間的缺點(diǎn)：無法使用日期函數(shù)進(jìn)行比較計算、字符串存儲占有更多的空間。
6、財務(wù)相關(guān)的金額類數(shù)據(jù)必須使用 decimal 類型
精準(zhǔn)浮點(diǎn)：decimal
非精準(zhǔn)浮點(diǎn)：float、double
Decimal 類型為精準(zhǔn)浮點(diǎn)數(shù)，在計算時不會丟失精度；占有空間大小由定義的寬度決定，每 4 個字節(jié)可以存儲 9 位數(shù)字，且小數(shù)點(diǎn)也要占有一個字節(jié)；另外，Decimal 類型可用于存儲比 bigint 更大的數(shù)據(jù)類型。
四、MySQL 索引設(shè)計規(guī)范
1、每張表的索引數(shù)量不超過 5 個
索引可以增加查詢效率，但同樣也會降低插入和更新的效率，甚至有些情況下還會降低查詢效率，因此并不是越多越好，要控制其數(shù)量。
2、每個 Innodb 表必須有一個主鍵
Innodb 是一種索引組織表，其數(shù)據(jù)存儲的邏輯順序和索引的順序是相同的；
每張表可以有多個索引，但表的存儲順序只能有一種，Innodb 是按照主鍵索引的順序來組織表的，因此不要使用更新頻繁的列、UUID、MD5、HASH 和字符串列作為主鍵，這些列無法保證數(shù)據(jù)的順序增長，主鍵建議使用自增 ID 值。
3、盡量避免使用外鍵約束
不建議使用外鍵約束 (foreign key)，但一定要在表與表之間的關(guān)聯(lián)鍵上建立索引；
外鍵雖然可以保證數(shù)據(jù)的參照完整性，但外鍵也會影響父表和子表的寫操作從而降低性能，還會使得表更耦合，建議在業(yè)務(wù)端實現(xiàn)。
五、MySQL 數(shù)據(jù)庫 SQL 開發(fā)規(guī)范
1、建議使用預(yù)編譯語句進(jìn)行數(shù)據(jù)庫操作
預(yù)編譯語句可以重復(fù)使用，相同的 SQL 語句可以一次解析，多次使用，減少 SQL 編譯所需要的時間，提高處理效率；此外，還可以有效解決動態(tài) SQL 帶來的 SQL 注入問題。
2、避免數(shù)據(jù)類型的隱式轉(zhuǎn)換
隱式轉(zhuǎn)換如：SELECT 1 + 1；數(shù)值型 + 字符型 的隱式轉(zhuǎn)換有可能會導(dǎo)致索引失效，以及一些意想不到的結(jié)果等。
3、充分利用表中存在的索引
1）避免使用雙 % 號的查詢條件
如 WHERE first_name like %James%，若無前置 %，只有后置 %，則執(zhí)行 SQL 語句時會用到列上的索引，雙 % 號則不會使用列上的索引。
2）一條 SQL 語句只能使用復(fù)合索引中的一列進(jìn)行范圍查詢
例如有 weight、age、sex 三列的聯(lián)合索引，在查詢條件中有 weight 列的范圍查詢，則在 age 和 sex 列上的索引將不會被使用；因此，在定義聯(lián)合索引時，若某列需要用到范圍查詢，則將該列放到聯(lián)合索引的右側(cè)。
3）使用 not exists 代替 not in
因為 not in 在 SQL 語句中執(zhí)行時會導(dǎo)致索引失效。
4、杜絕使用 SELECT *，必須使用 SELECT 字段列表 查詢
因為使用 SELECT * 查詢會消耗更多的 CPU、IO 和網(wǎng)絡(luò)寬帶資源，并且查詢時無法使用覆蓋索引。
5、禁止使用不含字段列表的 INSERT 語句
如：INSERT into table_name values (1 , 2 , 3 改為帶字段列表的 INSERT 語句：INSERT into table_name( c1 , c2 , c3) values (1 , 2 , 3
6、避免使用子查詢，可以把子查詢優(yōu)化為 join 關(guān)聯(lián)操作
但是，通常子查詢在 in 子句中，且子查詢中為簡單 SQL(即不包含 union、group by、order by、limit 從句) 時，才可以把子查詢轉(zhuǎn)化為 join 關(guān)聯(lián)查詢進(jìn)行優(yōu)化；
子查詢性能差的原因：
子查詢的結(jié)果集無法使用索引，通常子查詢的結(jié)果集會被存儲到臨時表中，不論是內(nèi)存臨時表還是磁盤臨時表都不會存在索引，所以查詢性能會受到一定的影響；
由于子查詢會產(chǎn)生大量的臨時表也沒有索引，所以會消耗過多的 CPU 和 IO 資源，產(chǎn)生大量的慢查詢。
7、避免使用 JOIN 關(guān)聯(lián)太多表
1）在 Mysql 中，對于同一個 SQL 關(guān)聯(lián)（join）多個表，每個 join 就會多分配一個關(guān)聯(lián)緩存，如果在一個 SQL 中關(guān)聯(lián)的表越多，所占用的內(nèi)存也就越大；
2）如果程序中大量的使用了多表關(guān)聯(lián)的操作，同時 join_buffer_size(MySQL 允許關(guān)聯(lián)緩存的個數(shù)) 設(shè)置的也不合理的情況下，就容易造成服務(wù)器內(nèi)存溢出的情況，就會影響服務(wù)器數(shù)據(jù)庫性能的穩(wěn)定性；
3）此外，對于關(guān)聯(lián)操作來說，會產(chǎn)生臨時表影響查詢效率，而 Mysql 最多允許關(guān)聯(lián) 61 個表，建議不超過 5 個；
8、對同一列對象進(jìn)行 or 判斷時，使用 in 替代 or
in 的值只要涉及不超過 500 個，則 in 操作可以更有效的利用索引，or 大多數(shù)情況下很少能利用到索引。
9、禁止使用 order by rand() 進(jìn)行隨機(jī)排序
10、禁止在 WHERE 從句中對列進(jìn)行函數(shù)轉(zhuǎn)換和計算
因為在 WHERE 從句中對列進(jìn)行函數(shù)轉(zhuǎn)換或計算時會導(dǎo)致索引無法使用。
No 推薦：
where date(end_time)= 20190101推薦：
where end_time = 20190101 and end_time 2019010211、在明顯不會有重復(fù)值時使用 UNION ALL 而不是 UNION
1）UNION 會把兩個結(jié)果集的所有數(shù)據(jù)放到臨時表中后再進(jìn)行去重操作；
2）UNION ALL 不會再對結(jié)果集進(jìn)行去重操作；
12、把復(fù)雜、較長的 SQL 拆分為為多個小 SQL 執(zhí)行
1）大 SQL 在邏輯上比較復(fù)雜，是需要占用大量 CPU 進(jìn)行計算一條 SQL 語句；
2）在 MySQL 中，一條 SQL 語句只能使用一個 CPU 進(jìn)行計算；
3）SQL 拆分后可以通過并行執(zhí)行來提高處理效率。
六、MySQL 數(shù)據(jù)庫行為規(guī)范
1、超過 100 萬行數(shù)據(jù)的批量操作 (update delete insert)，分多次進(jìn)行
大批量操作可能回造成嚴(yán)重的主從延遲；
binlog 日志為 row 格式時會產(chǎn)生大量的日志；
避免產(chǎn)生大事物操作。
2、對于大表使用 pt-online-schema-change 修改表結(jié)構(gòu)
1）避免大表修改產(chǎn)生的主從延遲、避免在對表字段進(jìn)行修改時進(jìn)行鎖表；
2）pt-online-schema-change 它首先會建立一個與原表結(jié)構(gòu)相同的新表，并且在新表上進(jìn)行表結(jié)構(gòu)的修改，然后再把原表中的數(shù)據(jù)復(fù)制到新表中，并在原表中增加一些觸發(fā)器；然后，把原表中新增的數(shù)據(jù)也復(fù)制到新表中，在行所有數(shù)據(jù)復(fù)制完成之后，把新表命名成原表，并把原來的表刪除掉，其是把原來一個 DDL 操作，分解成多個小的批次執(zhí)行。
3、禁止給程序使用的賬號授予 super 權(quán)限
當(dāng)達(dá)到最大連接數(shù)限制時，還運(yùn)行 1 個有 super 權(quán)限的用戶連接 super 權(quán)限只能留給 DBA 處理問題的賬號使用。
4、對于程序連接數(shù)據(jù)庫賬號，遵循權(quán)限最小原則
程序使用數(shù)據(jù)庫賬號只能在一個數(shù)據(jù)庫下使用，且程序使用的賬號原則上不授予 drop 權(quán)限。
感謝各位的閱讀！關(guān)于 MySQL 數(shù)據(jù)庫的使用規(guī)范就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！
向 AI 問一下細(xì)節(jié)丸趣 TV 網(wǎng) – 提供最優(yōu)質(zhì)的資源集合！
