溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇“MySQL數(shù)據(jù)庫設計規(guī)范是什么”文章的知識點大部分人都不太理解，所以小編給大家總結了以下內(nèi)容，內(nèi)容詳細，步驟清晰，具有一定的借鑒價值，希望大家閱讀完這篇文章能有所收獲，下面我們一起來看看這篇“MySQL數(shù)據(jù)庫設計規(guī)范是什么”文章吧。
MySQL 數(shù)據(jù)庫與 Oracle、 SQL Server 等數(shù)據(jù)庫相比，有其內(nèi)核上的優(yōu)勢與劣勢。我們在使用 MySQL 數(shù)據(jù)庫的時候需要遵循一定規(guī)范，揚長避短。本規(guī)范旨在幫助或指導 RD、QA、OP 等技術人員做出適合線上業(yè)務的數(shù)據(jù)庫設計。在數(shù)據(jù)庫變更和處理流程、數(shù)據(jù)庫表設計、SQL 編寫等方面予以規(guī)范，從而為公司業(yè)務系統(tǒng)穩(wěn)定、健康地運行提供保障。
以下所有規(guī)范會按照【高?！?、【強制】、【建議】三個級別進行標注，遵守優(yōu)先級從高到低。
對于不滿足【高?！亢汀緩娭啤績蓚€級別的設計，DBA 會強制打回要求修改。
【強制】使用小寫，有助于提高打字速度，避免因大小寫敏感而導致的錯誤。
【強制】沒有空格，使用下劃線代替。
【強制】名稱中沒有數(shù)字，只有英文字母。
【強制】有效的可理解的名稱。
【強制】名稱應該是自我解釋的。
【強制】名稱不應超過 32 個字符。
【強制】避免使用前綴。
【強制】遵守以上全部一般命名規(guī)則。
【強制】使用單數(shù)。
【強制】庫的名稱格式：業(yè)務系統(tǒng)名稱_子系統(tǒng)名。
【強制】一般分庫名稱命名格式是庫通配名_編號，編號從 0 開始遞增，比如 northwind_001，以時間進行分庫的名稱格式是庫通配名_時間。
【強制】創(chuàng)建數(shù)據(jù)庫時必須顯式指定字符集，并且字符集只能是 utf8 或者 utf8mb4。創(chuàng)建數(shù)據(jù)庫 SQL 舉例：
create database db_name default character set utf8;
【強制】遵守以上全部一般命名規(guī)則。
【強制】使用單數(shù)。
【強制】相關模塊的表名與表名之間盡量體現(xiàn) join 的關系，如 user 表和 user_login 表。
【強制】創(chuàng)建表時必須顯式指定字符集為 utf8 或 utf8mb4。
【強制】創(chuàng)建表時必須顯式指定表存儲引擎類型，如無特殊需求，一律為 InnoDB。當需要使用除 InnoDB/MyISAM/Memory 以外的存儲引擎時，必須通過 DBA 審核才能在生產(chǎn)環(huán)境中使用。因為 InnoDB 表支持事務、行鎖、宕機恢復、MVCC 等關系型數(shù)據(jù)庫重要特性，為業(yè)界使用最多的 MySQL 存儲引擎。而這是其它大多數(shù)存儲引擎不具備的，因此首推 InnoDB。
【強制】建表必須有 comment。
【強制】關于主鍵：(1) 命名為 id，類型為 int 或 bigint，且為 auto_increment；(2) 標識表里每一行主體的字段不要設為主鍵，建議設為其它字段如 user_id，order_id等，并建立 unique key 索引。因為如果設為主鍵且主鍵值為隨機插入，則會導致 InnoDB 內(nèi)部 page 分裂和大量隨機 I/O，性能下降。
【建議】核心表（如用戶表，金錢相關的表）必須有行數(shù)據(jù)的創(chuàng)建時間字段 create_time 和最后更新時間字段 update_time，便于排查問題。
【建議】表中所有字段必須都是 NOT NULL 屬性，業(yè)務可以根據(jù)需要定義 DEFAULT 值。因為使用 NULL 值會存在每一行都會占用額外存儲空間、數(shù)據(jù)遷移容易出錯、聚合函數(shù)計算結果偏差等問題。
【建議】建議對表里的 blob、text 等大字段，垂直拆分到其它表里，僅在需要讀這些對象的時候才去 select。
【建議】反范式設計：把經(jīng)常需要 join 查詢的字段，在其它表里冗余一份。如 username 屬性在 user_account，user_login_log 等表里冗余一份，減少 join 查詢。
【強制】中間表用于保留中間結果集，名稱必須以 tmp_ 開頭。備份表用于備份或抓取源表快照，名稱必須以 bak_ 開頭。中間表和備份表定期清理。
【強制】對于超過 100W 行的大表進行 alter table，必須經(jīng)過 DBA 審核，并在業(yè)務低峰期執(zhí)行。因為 alter table 會產(chǎn)生表鎖，期間阻塞對于該表的所有寫入，對于業(yè)務可能會產(chǎn)生極大影響。
【強制】遵守以上全部一般命名規(guī)則。
【建議】盡可能選擇短的或一兩個單詞。
【強制】避免使用保留字作為字段名稱：order，date，name 是數(shù)據(jù)庫的保留字，避免使用它?？梢詾檫@些名稱添加前綴使其易于理解，如 user_name，signup_date 等。
【強制】避免使用與表名相同的字段名，這會在編寫查詢時造成混淆。
【強制】在數(shù)據(jù)庫模式上定義外鍵。
【強制】避免使用縮寫或基于首字母縮寫詞的名稱。
【強制】外鍵列必須具有表名及其主鍵，例如：blog_id 表示來自表博客的外鍵 id。
【建議】表中的自增列（auto_increment 屬性），推薦使用 bigint 類型。因為無符號 int 存儲范圍為 0~4,294,967,295（不到 43 億），溢出后會導致報錯。
【建議】業(yè)務中選擇性很少的狀態(tài) status、類型 type 等字段推薦使用 tinytint 或者 smallint 類型節(jié)省存儲空間。
【建議】業(yè)務中 IP 地址字段推薦使用 int 類型，不推薦用 char(15)。因為 int 只占 4 字節(jié)，可以用如下函數(shù)相互轉換，而 char(15) 占用至少 15 字節(jié)。
select inet_aton('192.168.2.12');
select inet_ntoa(3232236044);Java 保存字符串ip 轉 int 類型
public static long ipToLong(String addr)
{
String[] addrArray = addr.split("\\.");
long num = 0;
for (int i = 0; i < addrArray.length; i++)
{
int power = 3 - i;
num += ((Integer.parseInt(addrArray[i]) % 256 * Math.pow(256, power)));
}
return num;
}
public static String longToIp(long i){
return ((i >> 24) & 0xFF) + "." +
((i >> 16) & 0xFF) + "." +
((i >> 8) & 0xFF) + "." +
(i & 0xFF);
}【建議】不推薦使用 enum，set。 因為它們浪費空間，且枚舉值寫死了，變更不方便。推薦使用 tinyint 或 smallint。
【建議】不推薦使用 blob，text 等類型。它們都比較浪費硬盤和內(nèi)存空間。在加載表數(shù)據(jù)時，會讀取大字段到內(nèi)存里從而浪費內(nèi)存空間，影響系統(tǒng)性能。建議和 PM、RD 溝通，是否真的需要這么大字段。InnoDB 中當一行記錄超過 8098 字節(jié)時，會將該記錄中選取最長的一個字段將其 768 字節(jié)放在原始 page 里，該字段余下內(nèi)容放在 overflow-page 里。不幸的是在 compact 行格式下，原始 page 和 overflow-page 都會加載。
【建議】存儲金錢的字段，建議用 int 以分為單位存儲，最大數(shù)值約 4290 萬，程序端乘以 100 和除以 100 進行存取。因為 int 占用 4 字節(jié)，而 double 占用 8 字節(jié)，空間浪費。
【建議】文本數(shù)據(jù)盡量用 varchar 存儲。因為 varchar 是變長存儲，比 char 更省空間。MySQL server 層規(guī)定一行所有文本最多存 65535 字節(jié)，因此在 utf8 字符集下最多存 21844 個字符，超過會自動轉換為 mediumtext 字段。而 text 在 utf8 字符集下最多存 21844 個字符，mediumtext 最多存 2^24/3 個字符，longtext 最多存 2^32 個字符。一般建議用 varchar 類型，字符數(shù)不要超過 2700。
【建議】時間類型盡量選取 timestamp。因為 datetime 占用 8 字節(jié)，timestamp 僅占用 4 字節(jié)，但是范圍為 1970-01-01 00:00:01 到 2038-01-01 00:00:00。更為高階的方法，選用 int 來存儲時間，使用 SQL 函數(shù) unix_timestamp() 和 from_unixtime() 來進行轉換。
【強制】InnoDB 表必須主鍵為 id int/bigint auto_increment，且主鍵值禁止被更新。
【建議】主鍵的名稱以 pk_ 開頭，唯一鍵以 uk_ 開頭，普通索引以 ix_ 開頭，一律使用小寫格式，以表名/字段的名稱或縮寫作為后綴。
【強制】InnoDB 和 MyISAM 存儲引擎表，索引類型必須為 BTREE；MEMORY 表可以根據(jù)需要選擇 HASH 或者 BTREE 類型索引。
【強制】單個索引中每個索引記錄的長度不能超過 64KB。
【建議】單個表上的索引個數(shù)不能超過 7 個。
【建議】在建立索引時，多考慮建立聯(lián)合索引，并把區(qū)分度最高的字段放在最前面。如列 user_id 的區(qū)分度可由 select count(distinct user_id) 計算出來。
【建議】在多表 join 的 SQL 里，保證被驅動表的連接列上有索引，這樣 join 執(zhí)行效率最高。
【建議】建表或加索引時，保證表里互相不存在冗余索引。對于 MySQL 來說，如果表里已經(jīng)存在 key(a, b)，則 key(a) 為冗余索引，需要刪除。
【建議】如果選擇性超過 20%，那么全表掃描比使用索引性能更優(yōu)，即沒有設置索引的必要。
【強制】分區(qū)表的分區(qū)字段（partition-key）必須有索引，或者是組合索引的首列。
【強制】單個分區(qū)表中的分區(qū)（包括子分區(qū)）個數(shù)不能超過 1024。
【強制】上線前 RD 或者 DBA 必須指定分區(qū)表的創(chuàng)建、清理策略。
【強制】訪問分區(qū)表的 SQL 必須包含分區(qū)鍵。
【建議】單個分區(qū)文件不超過 2G，總大小不超過 50G。建議總分區(qū)數(shù)不超過 20 個。
【強制】對于分區(qū)表執(zhí)行 alter table 操作，必須在業(yè)務低峰期執(zhí)行。
【強制】采用分庫策略的，庫的數(shù)量不能超過 1024。
【強制】采用分表策略的，表的數(shù)量不能超過 4096。
【建議】單個分表不超過 500W 行，ibd 文件大小不超過 2G，這樣才能讓數(shù)據(jù)分布式變得性能更佳。
【建議】水平分表盡量用取模方式，日志、報表類數(shù)據(jù)建議采用日期進行分表。
【強制】數(shù)據(jù)庫本身庫、表、列所有字符集必須保持一致，為 utf8 或 utf8mb4。
【強制】前端程序字符集或者環(huán)境變量中的字符集，與數(shù)據(jù)庫、表的字符集必須一致，統(tǒng)一為 utf8。
【建議】新的代碼不要用 model，推薦使用手動拼 SQL + 綁定變量傳入?yún)?shù)的方式。因為 model 雖然可以使用面向對象的方式操作 db，但是其使用不當很容易造成生成的 SQL 非常復雜，且 model 層自己做的強制類型轉換性能較差，最終導致數(shù)據(jù)庫性能下降。
【建議】前端程序連接 MySQL 或者 Redis，必須要有連接超時和失敗重連機制，且失敗重試必須有間隔時間。
【建議】前端程序報錯里盡量能夠提示 MySQL 或 Redis 原生態(tài)的報錯信息，便于排查錯誤。
【建議】對于有連接池的前端程序，必須根據(jù)業(yè)務需要配置初始、最小、最大連接數(shù)，超時時間以及連接回收機制，否則會耗盡數(shù)據(jù)庫連接資源，造成線上事故。
【建議】對于 log 或 history 類型的表，隨時間增長容易越來越大，因此上線前 RD 或者 DBA 必須建立表數(shù)據(jù)清理或歸檔方案。
【建議】在應用程序設計階段，RD 必須考慮并規(guī)避數(shù)據(jù)庫中主從延遲對于業(yè)務的影響。盡量避免從庫短時延遲（20 秒以內(nèi)）對業(yè)務造成影響，建議強制一致性的讀開啟事務走主庫，或更新后過一段時間再去讀從庫。
【建議】多個并發(fā)業(yè)務邏輯訪問同一塊數(shù)據(jù)（InnoDB 表）時，會在數(shù)據(jù)庫端產(chǎn)生行鎖甚至表鎖導致并發(fā)下降，因此建議更新類 SQL 盡量基于主鍵去更新。
【建議】業(yè)務邏輯之間加鎖順序盡量保持一致，否則會導致死鎖。
【建議】對于單表讀寫比大于 10:1 的數(shù)據(jù)行或單個列，可以將熱點數(shù)據(jù)放在緩存里（如 Memcached 或 Redis），加快訪問速度，降低 MySQL 壓力。
一個較為規(guī)范的建表語句為：
create table user ( `id` bigint(11) not null auto_increment, `user_id` bigint(11) not null comment '用戶 ID', `username` varchar(45) not null comment '登錄名', `email` varchar(30) not null comment '郵箱', `nickname` varchar(45) not null comment '昵稱', `avatar` int(11) not null comment '頭像', `birthday` date not null comment '生日', `gender` tinyint(4) default '0' comment '性別', `intro` varchar(150) default null comment '簡介', `resume_url` varchar(300) not null comment '簡歷存放地址', `register_ip` int not null comment '用戶注冊時的源 IP', `review_status` tinyint not null comment '審核狀態(tài)，1-通過，2-審核中，3-未通過，4-尚未提交審核', `create_time` timestamp not null comment '記錄創(chuàng)建的時間', `update_time` timestamp not null comment '資料修改的時間', primary key (`id`), unique key `idx_user_id` (`user_id`), key `idx_username`(`username`), key `idx_create_time`(`create_time`, `review_status`) ) engine = InnoDB default charset = utf8 comment = '用戶基本信息';
【強制】select 語句必須指定具體字段名稱，禁止寫成 *。因為 select * 會將不該讀的數(shù)據(jù)也從 MySQL 里讀出來，造成網(wǎng)卡壓力。
【強制】insert 語句指定具體字段名稱，不要寫成 insert into t1 values(…)，道理同上。
【建議】insert into … values(xx),(xx),(xx)…，這里 xx 的值不要超過 5000 個。值過多雖然上線很快，但會引起主從同步延遲。
【建議】select 語句不要使用 union，推薦使用 union all，并且 union 子句個數(shù)限制在 5 個以內(nèi)。因為 union all 不需要去重，節(jié)省數(shù)據(jù)庫資源，提高性能。
【建議】in 值列表限制在 500 以內(nèi)。例如 select … where user_id in(…500 個以內(nèi)…)，這么做是為了減少底層掃描，減輕數(shù)據(jù)庫壓力從而加速查詢。
【建議】事務里批量更新數(shù)據(jù)需要控制數(shù)量，進行必要的 sleep，做到少量多次。
【強制】事務涉及的表必須全部是 InnoDB 表。否則一旦失敗不會全部回滾，且易造成主從庫同步終端。
【強制】寫入和事務發(fā)往主庫，只讀 SQL 發(fā)往從庫。
【強制】除靜態(tài)表或小表（100 行以內(nèi)），dml 語句必須有 where 條件，且使用索引查找。
【強制】生產(chǎn)環(huán)境禁止使用 hint，如 sql_no_cache，force index，ignore key，straight join 等。因為 hint 是用來強制 sql 按照某個執(zhí)行計劃來執(zhí)行，但隨著數(shù)據(jù)量變化我們無法保證自己當初的預判是正確的，因此我們要相信 MySQL 優(yōu)化器。
【強制】where 條件里等號左右字段類型必須一致，否則無法利用索引。
【建議】select|update|delete|replace 要有 where 子句，且 where 子句的條件必需使用索引查找。
【強制】生產(chǎn)數(shù)據(jù)庫中強烈不推薦大表上發(fā)生全表掃描，但對于 100 行以下的靜態(tài)表可以全表掃描。查詢數(shù)據(jù)量不要超過表行數(shù)的 25%，否則不會利用索引。
【強制】where 子句中禁止只使用全模糊的 like 條件進行查找，必須有其它等值或范圍查詢條件，否則無法利用索引。
【建議】索引列不要使用函數(shù)或表達式，否則無法利用索引。如 where length(name) = 'admin' 或 where user_id + 2 = 10023。
【建議】減少使用 or 語句，可將 or 語句優(yōu)化為 union，然后在各個 where 條件上建立索引。如 where a = 1 or b = 2 優(yōu)化為 where a = 1 … union … where b = 2, key(a), key(b)。
【建議】分頁查詢，當 limit 起點較高時，可先用過濾條件進行過濾。如 select a, b, c from t1 limit 10000, 20; 優(yōu)化為: select a, b, c from t1 where id > 10000 limit 20;。
【強制】禁止跨 DB 的 join 語句。因為這樣可以減少模塊間耦合，為數(shù)據(jù)庫拆分奠定堅實基礎。
【強制】禁止在業(yè)務的更新類 SQL 語句中使用 join，比如 update t1 join t2 …。
【建議】不建議使用子查詢，建議將子查詢 SQL 拆開結合程序多次查詢，或使用 join 來代替子查詢。
【建議】線上環(huán)境，多表 join 不要超過 3 個表。
【建議】多表連接查詢推薦使用別名，且 select 列表中要用別名引用字段，數(shù)據(jù)庫.表格式，如 select a from db1.table1 alias1 where …。
【建議】在多表 join 中，盡量選取結果集較小的表作為驅動表，來 join 其它表。
【建議】事務中 insert|update|delete|replace 語句操作的行數(shù)控制在 2000 以內(nèi)，以及 where 子句中 in 列表的傳參個數(shù)控制在 500 以內(nèi)。
【建議】批量操作數(shù)據(jù)時，需要控制事務處理間隔時間，進行必要的 sleep，一般建議值 5-10 秒。
【建議】對于有 auto_increment 屬性字段的表的插入操作，并發(fā)需要控制在 200 以內(nèi)。
【強制】程序設計必須考慮“數(shù)據(jù)庫事務隔離級別”帶來的影響，包括臟讀、不可重復讀和幻讀。線上建議事務隔離級別為 repeatable-read。
【建議】事務里包含 SQL 不超過 5 個（支付業(yè)務除外）。因為過長的事務會導致鎖數(shù)據(jù)較久，MySQL 內(nèi)部緩存、連接消耗過多等雪崩問題。
【建議】事務里更新語句盡量基于主鍵或 unique key，如 update … where id = XX;，否則會產(chǎn)生間隙鎖，內(nèi)部擴大鎖定范圍，導致系統(tǒng)性能下降，產(chǎn)生死鎖。
【建議】盡量把一些典型外部調用移出事務，如調用 Web Service，訪問文件存儲等，從而避免事務過長。
【建議】對于 MySQL 主從延遲嚴格敏感的 select 語句，請開啟事務強制訪問主庫。
【建議】減少使用 order by，和業(yè)務溝通能不排序就不排序，或將排序放到程序端去做。order by、group by、distinct 這些語句較為耗費 CPU，數(shù)據(jù)庫的 CPU 資源是極其寶貴的。
【建議】order by、group by、distinct 這些 SQL 盡量利用索引直接檢索出排序好的數(shù)據(jù)。如 where a = 1 order by 可以利用 key(a, b)。
【建議】包含了 order by、group by、distinct 這些查詢的語句，where 條件過濾出來的結果集請保持在 1000 行以內(nèi)，否則 SQL 會很慢。
【高?！拷?update|delete t1 … where a = XX limit XX; 這種帶 limit 的更新語句。因為會導致主從不一致，導致數(shù)據(jù)錯亂。建議加上 order by PK。
【高?！拷故褂藐P聯(lián)子查詢，如 update t1 set … where name in(select name from user where …);，效率極其低下。
【強制】禁用 procedure、function、trigger、views、event、外鍵約束。因為他們消耗數(shù)據(jù)庫資源，降低數(shù)據(jù)庫實例可擴展性。推薦都在程序端實現(xiàn)。
【強制】禁用 insert into … on duplicate key update … 在高并發(fā)環(huán)境下，會造成主從不一致。
【強制】禁止聯(lián)表更新語句，如 update t1, t2 where t1.id = t2.id …。
以上就是關于“MySQL數(shù)據(jù)庫設計規(guī)范是什么”這篇文章的內(nèi)容，相信大家都有了一定的了解，希望小編分享的內(nèi)容對大家有幫助，若想了解更多相關的知識內(nèi)容，請關注億速云行業(yè)資訊頻道。
免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權內(nèi)容。
