這篇文章主要介紹了MySQ設(shè)計與開發(fā)規(guī)范有哪些的相關(guān)知識,內(nèi)容詳細易懂,操作簡單快捷,具有一定借鑒價值,相信大家閱讀完這篇MySQ設(shè)計與開發(fā)規(guī)范有哪些文章都會有所收獲,下面我們一起來看看吧。
10年積累的網(wǎng)站設(shè)計、做網(wǎng)站經(jīng)驗,可以快速應(yīng)對客戶對網(wǎng)站的新想法和需求。提供各種問題對應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認識你,你也不認識我。但先制作網(wǎng)站后付款的網(wǎng)站建設(shè)流程,更有山西免費網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。
數(shù)據(jù)庫對象是數(shù)據(jù)庫的組成部分,常見的有以下幾種:表(Table )、索引(Index)、視圖(View)、圖表(Diagram)、缺省值(Default)、規(guī)則(Rule)、觸發(fā)器(Trigger)、存儲過程(Stored Procedure)、 用戶(User)等。命名規(guī)范是指數(shù)據(jù)庫對象如數(shù)據(jù)庫(SCHEMA)、表(TABLE)、索引(INDEX)、約束(CONSTRAINTS)等的命名約定。【推薦:MySQL視頻教程】
1、命名使用具有意義的英文詞匯,詞匯中間以下劃線分隔
2、命名只能使用英文字母、數(shù)字、下劃線,以英文字母開頭
3、避免用MySQL的保留字如:backup、call、group等
4、所有數(shù)據(jù)庫對象使用小寫字母,實際上MySQL中是可以設(shè)置大小寫是否敏感的,為了保證統(tǒng)一性,我們這邊規(guī)范全部小寫表示。
1、數(shù)據(jù)庫命名盡量不超過30個字符。
2、數(shù)據(jù)庫命名一般為項目名稱+代表庫含義的簡寫,比如IM項目的工作流數(shù)據(jù)庫,可以是 im_flow。
3、數(shù)據(jù)庫創(chuàng)建時必須添加默認字符集和校對規(guī)則子句。默認字符集為UTF8(已遷移dumbo的使用utf8mb4)
4、命名應(yīng)使用小寫。
1、常規(guī)表表名以t_開頭,t代表table的意思,命名規(guī)則即 t + 模塊(包含模塊含義的簡寫)+ 表(包含表含義的簡寫),比如用戶模塊的教育信息表:t_user_eduinfo。
2、臨時表(RD、QA或DBA同學(xué)用于數(shù)據(jù)臨時處理的表),命名規(guī)則:temp前綴+模塊+表+日期后綴:temp_user_eduinfo_20210719
3、備份表(用于保存和歸檔歷史數(shù)據(jù)或者作為災(zāi)備恢復(fù)的數(shù)據(jù))命名規(guī)則,bak前綴+模塊+表+日期后綴:bak_user_eduinfo_20210719
4、同一個模塊的表盡可能使用相同的前綴,表名稱盡可能表達含義
5、多個單詞以下劃線 _ 分隔
6、常規(guī)表表名盡量不超過30個字符,temp表和bak表視情況而定,也盡量簡短為宜,命名應(yīng)使用小寫
1、字段命名需要表示其實際含義的英文單詞或簡寫,單詞之間用下劃線 _ 進行連接,如 service_ip、service_port
。
2、各表之間相同意義的字段必須同名,比如a表和b表都有創(chuàng)建時間,應(yīng)該統(tǒng)一為create_time,不一致會很混亂。
3、多個單詞以下劃線 _ 分隔
4、字段名盡量不超過30個字符,命名應(yīng)該使用小寫
1、唯一索引使用uni + 字段名 來命名:create unique index uni_uid on t_user_basic(uid)
。
2、非唯一索引使用idx + 字段名 來命名:create index idx_uname_mobile on t_user_basic(uname,mobile)
。
3、多個單詞以下劃線 _ 分隔。
4、索引名盡量不超過50個字符,命名應(yīng)該使用小寫,組合索引的字段不宜太多,不然也不利于查詢效率的提升。
5、多單詞組成的列名,取盡可能代表意義的縮寫,如 test_contact
表member_id
和friend_id
上的組合索引:idx_mid_fid
。
6、理解組合索引最左前綴原則,避免重復(fù)建設(shè)索引,如果建立了(a,b,c),相當(dāng)于建立了(a), (a,b), (a,b,c)。
1、視圖名以v開頭,表示view,完整結(jié)構(gòu)是v+視圖內(nèi)容含義縮寫。
2、如果視圖只來源單個表,則為v+表名。如果視圖由幾個表關(guān)聯(lián)產(chǎn)生就用v+下劃線(_)連接幾個表名,視圖名盡量不超過30個字符。如超過30個字符則取簡寫。
3、如無特殊需要,嚴禁開發(fā)人員創(chuàng)建視圖。
4、命名應(yīng)使用小寫。
1、存儲過程名以sp開頭,表示存儲過程(storage procedure
)。之后多個單詞以下劃線(_)進行連接。存儲過程命名中應(yīng)體現(xiàn)其功能。存儲過程名盡量不能超過30個字符。
2、存儲過程中的輸入?yún)?shù)以i_開頭,輸出參數(shù)以o_開頭。
3、命名應(yīng)使用小寫。
create procedure sp_multi_param(in i_id bigint,in i_name varchar(32),out o_memo varchar(100))
1、函數(shù)名以func開始,表示function。之后多個單詞以下劃線(_)進行連接,函數(shù)命名中應(yīng)體現(xiàn)其功能。函數(shù)名盡量不超過30個字符。
2、命名應(yīng)使用小寫。
create function func_format_date(ctime datetime)
1、觸發(fā)器以trig
開頭,表示trigger
觸發(fā)器。
2、基本部分,描述觸發(fā)器所加的表,觸發(fā)器名盡量不超過30個字符。
3、后綴(_i,_u,_d),表示觸發(fā)條件的觸發(fā)方式(insert,update或delete)。
4、命名應(yīng)使用小寫。
DROP TRIGGER IF EXISTS trig_attach_log_d;CREATE TRIGGER trig_attach_log_d AFTER DELETE ON t_dept FOR EACH ROW;
1、唯一約束:uk_表名稱_字段名。uk是UNIQUE KEY的縮寫。比如給一個部門的部門名稱加上唯一約束,來保證不重名,如下:
ALTER TABLE t_dept ADD CONSTRAINT un_name UNIQUE(name);
2、外鍵約束:fk_表名,后面緊跟該外鍵所在的表名和對應(yīng)的主表名(不含t_)。子表名和父表名用下劃線(_)分隔。如下:
ALTER TABLE t_user ADD CONSTRAINT fk_user_dept FOREIGN KEY(depno) REFERENCES t_dept (id);
3、非空約束:如無特殊需要,建議所有字段默認非空(not null),不同數(shù)據(jù)類型必須給出默認值(default)。
1 `id` int(11) NOT NULL,2 `name` varchar(30) DEFAULT '',3 `deptId` int(11) DEFAULT ,4 `salary` float DEFAULT NULL,
4、出于性能考慮,如無特殊需要,建議不使用外鍵。參照完整性由代碼控制。這個也是我們普遍的做法,從程序角度進行完整性控制,但是如果不注意,也會產(chǎn)生臟數(shù)據(jù)。
5、命名應(yīng)使用小寫。
1、 生產(chǎn)使用的用戶命名格式為 code_應(yīng)用
2、 只讀用戶命名規(guī)則為 read_應(yīng)用
1、如無特殊需求,必須使用innodb存儲引擎。
可以通過 show variables like
‘default_storage_engine
‘ 來查看當(dāng)前默認引擎。主要有MyISAM
和 InnoDB
,從5.5版本開始默認使用 InnoDB 引擎。點擊這里進行刷題。
基本的差別為:MyISAM
類型不支持事務(wù)處理等高級處理,而InnoDB
類型支持。MyISAM
類型的表強調(diào)的是性能,其執(zhí)行速度比InnoDB
類型更快,但是不提供事務(wù)支持,而InnoDB
提供事務(wù)支持以及外部鍵等高級數(shù)據(jù)庫功能。
1、如無特殊要求,必須使用utf8
或utf8mb4
。
在國內(nèi),選擇對中文和各語言支持都非常完善的utf8
格式是最好的方式,MySQL在5.5之后增加utf8mb4
編碼,mb4
就是most bytes 4
的意思,專門用來兼容四字節(jié)的unicode
。
所以utf8mb4
是utf8
的超集,除了將編碼改為utf8mb4
外不需要做其他轉(zhuǎn)換。當(dāng)然,為了節(jié)省空間,一般情況下使用utf8
也就夠了。
可以使用如下腳本來查看數(shù)據(jù)庫的編碼格式
1 SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';2 -- 或3 SHOW VARIABLES Like '%char%';
1、不同應(yīng)用間所對應(yīng)的數(shù)據(jù)庫表之間的關(guān)聯(lián)應(yīng)盡可能減少,不允許使用外鍵對表之間進行關(guān)聯(lián),確保組件對應(yīng)的表之間的獨立性,為系統(tǒng)或表結(jié)構(gòu)的重構(gòu)提供可能性。目前業(yè)內(nèi)的做法一般 由程序控制參照完整性。
2、表設(shè)計的角度不應(yīng)該針對整個系統(tǒng)進行數(shù)據(jù)庫設(shè)計,而應(yīng)該根據(jù)系統(tǒng)架構(gòu)中組件劃分,針對每個組件所處理的業(yè)務(wù)進行數(shù)據(jù)庫設(shè)計。
3、表必須要有PK,主鍵的優(yōu)勢是唯一標識、有效引用、高效檢索,所以一般情況下盡量有主鍵字段。
4、一個字段只表示一個含義。
5、表不應(yīng)該有重復(fù)列。
6、禁止使用復(fù)雜數(shù)據(jù)類型(數(shù)組,自定義等),Json
類型的使用視情況而定。
7、需要join
的字段(連接鍵),數(shù)據(jù)類型必須保持絕對一致,避免隱式轉(zhuǎn)換。比如關(guān)聯(lián)的字段都是int類型。
8、設(shè)計應(yīng)至少滿足第三范式,盡量減少數(shù)據(jù)冗余。一些特殊場景允許反范式化設(shè)計,但在項目評審時需要對冗余字段的設(shè)計給出解釋。
9、TEXT
字段作為大體量文本存儲,必須放在獨立的表中 , 用PK與主表關(guān)聯(lián)。如無特殊需要,禁止使用TEXT
、BLOB
字段。
10、需要定期刪除(或者轉(zhuǎn)移)過期數(shù)據(jù)的表,通過分表解決,我們的做法是按照2/8法則將操作頻率較低的歷史數(shù)據(jù)遷移到歷史表中,按照時間或者則曾Id做切割點。
11、單表字段數(shù)不要太多,建議最多不要大于50個。過度的寬表對性能也是很大的影響。
12、MySQL在處理大表時,性能就開始明顯降低,所以建議單表物理大小限制在16GB,表中數(shù)據(jù)行數(shù)控制在2000W內(nèi)。
業(yè)內(nèi)的規(guī)則是超過2000W性能開始明顯降低。但是這個值是靈活的,你可以根據(jù)實際情況進行測試來判斷,比如阿里的標準就是500W,百度的確是2000W。實際上是否寬表,單行數(shù)據(jù)所占用的空間都有起到作用的。
13、如果數(shù)據(jù)量或數(shù)據(jù)增長在前期規(guī)劃時就較大,那么在設(shè)計評審時就應(yīng)加入分表策略,后續(xù)會有專門的文章來分析數(shù)據(jù)拆分的做法:垂直拆分(垂直分庫和垂直分表)、水平拆分(分庫分表和庫內(nèi)分表);
14、無特殊需求,嚴禁使用分區(qū)表
1、INT
:如無特殊需要,存放整型數(shù)字使用UNSIGNED INT
型,整型字段后的數(shù)字代表顯示長度。比如 id
int(11) NOT NULL
2、DATETIME
:所有需要精確到時間(時分秒)的字段均使用DATETIME
,不要使用TIMESTAMP
類型。
對于TIMESTAMP
,它把寫入的時間從當(dāng)前時區(qū)轉(zhuǎn)化為UTC(世界標準時間)進行存儲。查詢時,將其又轉(zhuǎn)化為客戶端當(dāng)前時區(qū)進行返回。而對于DATETIME
,不做任何改變,基本上是原樣輸入和輸出。
另外DATETIME
存儲的范圍也比較大:
timestamp
所能存儲的時間范圍為:’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’。
datetime
所能存儲的時間范圍為:’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。
但是特殊情況,對于跨時區(qū)的業(yè)務(wù),TIMESTAMP
更為合適。
3、VARCHAR
:所有動態(tài)長度字符串 全部使用VARCHAR
類型,類似于狀態(tài)等有限類別的字段,也使用可以比較明顯表示出實際意義的字符串,而不應(yīng)該使用INT之類的數(shù)字來代替;VARCHAR(N)
,
N表示的是字符數(shù)而不是字節(jié)數(shù)。比如VARCHAR(255)
,可以最大可存儲255個字符(字符包括英文字母,漢字,特殊字符等)。但N應(yīng)盡可能小,因為MySQL一個表中所有的VARCHAR
字段最大長度是65535個字節(jié),且存儲字符個數(shù)由所選字符集決定。
如UTF8存儲一個字符最大要3個字節(jié),那么varchar在存放占用3個字節(jié)長度的字符時不應(yīng)超過21845個字符。同時,在進行排序和創(chuàng)建臨時表一類的內(nèi)存操作時,會使用N的長度申請內(nèi)存。(如無特殊需要,原則上單個varchar
型字段不允許超過255個字符)
4、TEXT
:僅僅當(dāng)字符數(shù)量可能超過20000個的時候,才可以使用TEXT類型來存放字符類數(shù)據(jù),因為所有MySQL數(shù)據(jù)庫都會使用UTF8字符集。
所有使用TEXT
類型的字段必須和原表進行分拆,與原表主鍵單獨組成另外一個表進行存放,與大文本字段的隔離,目的是。如無特殊需要,不使用MEDIUMTEXT
、TEXT
、LONGTEXT
類型
5、對于精確浮點型數(shù)據(jù)存儲,需要使用DECIMAL
,嚴禁使用FLOAT
和DOUBLE
。
6、如無特殊需要,盡量不使用BLOB
類型
7、如無特殊需要,字段建議使用NOT NULL
屬性,可用默認值代替NULL
8、自增字段類型必須是整型且必須為UNSIGNED
,推薦類型為INT
或BIGINT
,并且自增字段必須是主鍵或者主鍵的一部分。
1、索引區(qū)分度
索引必須創(chuàng)建在索引選擇性(區(qū)分度)較高的列上,選擇性的計算方式為: selecttivity = count(distinct c_name)/count(*)
; 如果區(qū)分度結(jié)果小于0.2,則不建議在此列上創(chuàng)建索引,否則大概率會拖慢SQL執(zhí)行
2、遵循最左前綴
對于確定需要組成組合索引的多個字段,設(shè)計時建議將選擇性高的字段靠前放。使用時,組合索引的首字段,必須在where
條件中,且需要按照最左前綴規(guī)則去匹配。
3、禁止使用外鍵,可以在程序級別來約束完整性
4、Text類型字段如果需要創(chuàng)建索引,必須使用前綴索引
5、單張表的索引數(shù)量理論上應(yīng)控制在5個以內(nèi)。經(jīng)常有大批量插入、更新操作表,應(yīng)盡量少建索引,索引建立的原則理論上是多讀少寫的場景。
6、ORDER BY
,GROUP BY
,DISTINCT
的字段需要添加在索引的后面,形成覆蓋索引
7、正確理解和計算索引字段的區(qū)分度,文中有計算規(guī)則,區(qū)分度高的索引,可以快速得定位數(shù)據(jù),區(qū)分度太低,無法有效的利用索引,可能需要掃描大量數(shù)據(jù)頁,和不使用索引沒什么差別。
8、正確理解和計算前綴索引的字段長度,文中有判斷規(guī)則,合適的長度要保證高的區(qū)分度和最恰當(dāng)?shù)乃饕鎯θ萘浚挥羞_到最佳狀態(tài),才是保證高效率的索引。
9、聯(lián)合索引注意最左匹配原則:必須按照從左到右的順序匹配,MySQL會一直向右匹配索引直到遇到范圍查詢(>、<、between
、like
)然后停止匹配。
如:depno=1 and empname>'' and job=1
如果建立(depno
,empname
,job
)順序的索引,job是用不到索引的。
10、應(yīng)需而取策略,查詢記錄的時候,不要一上來就使用*,只取需要的數(shù)據(jù),可能的話盡量只利用索引覆蓋,可以減少回表操作,提升效率。
11、正確判斷是否使用聯(lián)合索引(上面聯(lián)合索引的使用那一小節(jié)有說明判斷規(guī)則),也可以進一步分析到索引下推(IPC),減少回表操作,提升效率。
12、避免索引失效的原則:禁止對索引字段使用函數(shù)、運算符操作,會使索引失效。這是實際上就是需要保證索引所對應(yīng)字段的”干凈度“。
13、避免非必要的類型轉(zhuǎn)換,字符串字段使用數(shù)值進行比較的時候會導(dǎo)致索引無效。
14、模糊查詢’%value%’會使索引無效,變?yōu)槿頀呙?,因為無法判斷掃描的區(qū)間,但是’value%’是可以有效利用索引。
15、索引覆蓋排序字段,這樣可以減少排序步驟,提升查詢效率
16、盡量的擴展索引,非必要不新建索引。比如表中已經(jīng)有a的索引,現(xiàn)在要加(a,b)的索引,那么只需要修改原來的索引即可。
舉例子:比如一個品牌表,建立的的索引如下,一個主鍵索引,一個唯一索引
1 PRIMARY KEY (`id`),2 UNIQUE KEY `uni_brand_define` (`app_id`,`define_id`)
當(dāng)你同事業(yè)務(wù)代碼中的檢索語句如下的時候,應(yīng)該立即警告了,即沒有覆蓋索引,也沒按照最左前綴原則:
1 select brand_id,brand_name from ds_brand_system where status=? and define_id=? and app_id=?
建議改成如下:
1 select brand_id,brand_name from ds_brand_system where app_id=? and define_id=? and status=?
1、PK應(yīng)該是有序并且無意義的,由開發(fā)人員自定義,盡可能簡短,并且是自增序列。
2、表中除PK以外,還存在唯一性約束的,可以在數(shù)據(jù)庫中創(chuàng)建以“uk_”作為前綴的唯一約束索引。
3、PK字段不允許更新。
4、禁止創(chuàng)建外鍵約束,外鍵約束由程序控制。
5、如無特殊需要,所有字段必須添加非空約束,即not null
。
6、如無特殊需要,所有字段必須有默認值。
1、盡量避免使用select *
,join語句使用select *
可能導(dǎo)致只需要訪問索引即可完成的查詢需要回表取數(shù)。
一種是可能取出很多不需要的數(shù)據(jù),對于寬表來說,這是災(zāi)難;一種是盡可能避免回表,因為取一些根本不需要的數(shù)據(jù)而回表導(dǎo)致性能低下,是很不合算。
2、嚴禁使用 select * from t_name
,而不加任何where
條件,道理一樣,這樣會變成全表全字段掃描。
3、MySQL中的text
類型字段存儲:
3.1、不與其他普通字段存放在一起,因為讀取效率低,也會影響其他輕量字段存取效率。
3.2、如果不需要text
類型字段,又使用了select *
,會讓該執(zhí)行消耗大量io,效率也很低下
4、在取出字段上可以使用相關(guān)函數(shù),但應(yīng)盡可能避免出現(xiàn) now()
, rand()
, sysdate()
等不確定結(jié)果的函數(shù),在Where條件中的過濾條件字段上嚴禁使用任何函數(shù),包括數(shù)據(jù)類型轉(zhuǎn)換函數(shù)。大量的計算和轉(zhuǎn)換會造成效率低下,這個在索引那邊也描述過了。
5、分頁查詢語句全部都需要帶有排序條件 , 否則很容易引起亂序
6、用in()/union
替換or
,效率會好一些,并注意in的個數(shù)小于300
7、嚴禁使用%前綴進行模糊前綴查詢:如:select a,b,c from t_name where a like ‘%name’
; 可以使用%模糊后綴查詢?nèi)纾?code>select a,b from t_name where a like ‘name%’;
8、避免使用子查詢,可以把子查詢優(yōu)化為join
操作
通常子查詢在in子句中,且子查詢中為簡單SQL(不包含union
、group by
、order by
、limit
從句)時,才可以把子查詢轉(zhuǎn)化為關(guān)聯(lián)查詢進行優(yōu)化。
子查詢性能差的原因:
·子查詢的結(jié)果集無法使用索引,通常子查詢的結(jié)果集就會被存儲到臨時表中,不論是內(nèi)存臨時表還是磁盤臨時表都不會存在索引,所以查詢性能 會受到一定的影響;
·特別是對于返回結(jié)果集比較大的子查詢,其對查詢性能的影響也就越大;
·由于子查詢會產(chǎn)生大量的臨時表也沒有索引,所以會消耗過多的CPU和IO資源,產(chǎn)生大量的慢查詢。
1、禁止使用不含字段列表的INSERT語句
如:insert into values ('a','b','c')
; 應(yīng)使用 insert into t_name(c1,c2,c3) values ('a','b','c');
。
2、大批量寫操作(UPDATE
、DELETE
、INSERT
),需要分批多次進行操作
·大批量操作可能會造成嚴重的主從延遲,特別是主從模式下,大批量操作可能會造成嚴重的主從延遲,因為需要slave
從master
的binlog
中讀取日志來進行數(shù)據(jù)同步。
·binlog
日志為row
格式時會產(chǎn)生大量的日志
關(guān)于“MySQ設(shè)計與開發(fā)規(guī)范有哪些”這篇文章的內(nèi)容就介紹到這里,感謝各位的閱讀!相信大家對“MySQ設(shè)計與開發(fā)規(guī)范有哪些”知識都有一定的了解,大家如果還想學(xué)習(xí)更多知識,歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。
當(dāng)前名稱:MySQ設(shè)計與開發(fā)規(guī)范有哪些
分享鏈接:http://bm7419.com/article20/phohco.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供品牌網(wǎng)站制作、域名注冊、建站公司、網(wǎng)站策劃、網(wǎng)站導(dǎo)航、網(wǎng)站排名
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)