首頁 >資料庫 >mysql教程 >最詳細的MySQ設計與開發規格【推薦收藏】

最詳細的MySQ設計與開發規格【推薦收藏】

藏色散人
藏色散人轉載
2022-01-18 16:16:362173瀏覽

以下這篇文章為大家整理最詳細的MySQL 設計與開發規範,希望對大家有幫助。

資料庫物件命名規範

資料庫物件

資料庫物件是資料庫的組成部分,常見的有以下幾種:表(Table )、索引(Index)、檢視(View)、圖表(Diagram)、缺省值(Default)、規則(Rule)、觸發器(Trigger)、預存程序(Stored Procedure)、 使用者(User)等。命名規範是指資料庫物件如資料庫(SCHEMA)、表(TABLE)、索引(INDEX)、約束(CONSTRAINTS)等的命名約定。 【推薦:mysql影片教學

資料庫物件全域命名規格

1、命名使用有意義的英文詞彙,詞彙中間以下劃線分隔

2、命名只能使用英文字母、數字、底線,以英文字母開頭

#3、避免用MySQL的保留字如:backup、call、group等

4、所有資料庫物件使用小寫字母,實際上MySQL中是可以設定大小寫是否敏感的,為了確保統一性,我們這邊規範全部小寫表示。

資料庫命名規格

1、資料庫命名盡量不超過30個字元。

2、資料庫命名一般為專案名稱 代表函式庫意義的簡寫,例如IM專案的工作流程資料庫,可以是 im_flow。

3、資料庫建立時必須新增預設字元集和校對規則子句。預設字元集為UTF8(已遷移dumbo的使用utf8mb4)

4、命名應使用小寫。

表命名規範

1、常規表表名以t_開頭,t代表table的意思,命名規則即t 模組(包含模組意義的簡寫)表(包含表格意義的簡寫),例如使用者模組的教育資訊表:t_user_eduinfo。

2、臨時表(RD、QA或DBA同學用於資料暫存處理的表格),命名規則:temp前綴模組表日期後綴:temp_user_eduinfo_20210719

#3 、備份表(用於保存和歸檔歷史資料或作為災備恢復的資料)命名規則,bak前綴模組表日期後綴:bak_user_eduinfo_20210719

4、同一個模組的表盡可能使用相同的前綴,表名稱盡可能表達含義

5、多個單字以下劃線_ 分隔

6、常規表表名盡量不超過30個字符,temp表和bak表視情況而定,也盡量簡短為宜,命名應使用小寫

字段命名規範

1、字段命名需要表示其實際含義的英文單字或簡寫,單字之間用底線_ 進行連接,如service_ip、service_port

2、各表之間相同意義的欄位必須同名,例如a表和b表都有創建時間,應該統一為create_time,不一致會很混亂。

3、多個單字以下劃線_ 分隔

4、字段名盡量不超過30個字符,命名應該使用小寫

索引命名規範

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個字符,命名應該使用小寫,組合索引的字段不宜太多,不然也不利於查詢效率的提升。

5、多單字組成的列名,取盡可能代表意義的縮寫,如test_contactmember_idfriend_id上的組合索引:idx_mid_fid

6、理解組合索引最左前綴原則,避免重複建設索引,如果建立了(a,b,c),相當於建立了(a), (a,b), (a,b ,c)。

視圖命名規範

1、視圖名稱以v開頭,表示view,完整結構是v 視圖內容意義縮寫。

2、若檢視只來源單一資料表,則為v 表名。如果視圖由幾個表格關聯產生就用v 底線(_)連接幾個表名,視圖名盡量不超過30個字元。如超過30個字元則取簡寫。

3、如無特殊需要,嚴禁開發人員創建視圖。

4、命名應使用小寫。

預存程序命名規範

1、預存程序名稱以sp開頭,表示預存程序(storage procedure)。之後多個單字以下劃線(_)進行連接。在儲存過程命名中應體現其功能。儲存過程名盡量不能超過30個字元。

2、預存程序中的輸入參數以i_開頭,輸出參數以o_開頭。

3、命名應使用小寫。

create procedure sp_multi_param(in i_id bigint,in i_name varchar(32),out o_memo varchar(100))

函數命名規格

1、函數名稱以func開始,表示function。之後多個單字以下劃線(_)進行連接,函數命名中應反映其功能。函數名盡量不超過30個字元。

2、命名應使用小寫。

create function func_format_date(ctime datetime)

觸發器命名規範

1、觸發器以trig開頭,表示trigger 觸發器。

2、基本部分,描述觸發器所加的表,觸發器名稱盡量不超過30個字元。

3、字尾(_i,_u,_d),表示觸發條件的觸發方式(insert,update或delete)。

4、命名應使用小寫。

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_表名,後面緊跟著該外鍵所在的表名和對應的主表名(不含t_)。子表名和父表名用底線(_)分隔。如下:

ALTER TABLE t_user ADD CONSTRAINT fk_user_dept FOREIGN KEY(depno) REFERENCES t_dept (id);

3、非空約束:如無特殊需要,建議所有欄位預設為非空(not null),不同資料型別必須給予預設值(default)。

1 `id` int(11) NOT NULL,2 `name` varchar(30) DEFAULT '',3 `deptId` int(11) DEFAULT ,4 `salary` float DEFAULT NULL,

4、出於效能考慮,如無特殊需要,建議不使用外鍵。參照完整性由程式碼控制。這個也是我們普遍的做法,從程式角度進行完整性控制,但是如果不注意,也會產生髒數據。

5、命名應使用小寫。

使用者命名規格

1、生產使用的使用者命名格式為code_應用

2、只讀使用者命名規則為read_應用

資料庫物件設計規格

儲存引擎的選擇

1、如無特殊需求,必須使用innodb儲存引擎。

可以透過 show variables likedefault_storage_engine‘ 來查看目前預設引擎。主要有MyISAMInnoDB,從5.5版本開始預設使用 InnoDB 引擎。點這裡進行刷題。

基本的差異為:MyISAM類型不支援事務處理等高階處理,而InnoDB類型支援。 MyISAM類型的表強調的是性能,其執行速度比InnoDB類型更快,但是不提供事務支持,而InnoDB提供事務支持以及外部鍵等高級資料庫功能。

字元集的選擇

1、如無特殊要求,必須使用utf8utf8mb4

在國內,選擇對中文和各語言支援都非常完善的utf8格式是最好的方式,MySQL在5.5之後增加utf8mb4編碼,mb4就是most bytes 4的意思,專門用來相容四位元組的unicode

所以utf8mb4utf8的超集,除了將編碼改為utf8mb4外不需要做其他轉換。當然,為了節省空間,一般情況下使用utf8也就夠了。

可以使用下列腳本來檢視資料庫的編碼格式

1 SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';2 -- 或3 SHOW VARIABLES Like '%char%';

表設計規格

1、不同應用程式間所對應的資料庫表之間的關聯應盡可能減少,不允許使用外鍵對錶之間進行關聯,確保組件對應的表之間的獨立性,為系統或表結構的重構提供可能性。目前業界的做法一般 由程序控制參照完整性。

2、表格設計的角度不應該針對整個系統進行資料庫設計,而應該根據系統架構中元件劃分,針對每個元件所處理的業務進行資料庫設計。

3、表格必須要有PK,主鍵的優勢是唯一識別、有效引用、高效檢索,所以一般情況下盡量有主鍵欄位。

4、一個欄位只表示一個意義。

5、表格不應該有重複列。

6、禁止使用複雜資料類型(數組,自訂等),Json類型的使用視情況而定。

7、需要join的欄位(連接鍵),資料型別必須保持絕對一致,避免隱含轉換。例如關聯的欄位都是int型別。

8、設計應至少滿足第三範式,盡量減少資料冗餘。一些特殊場景允許反範式化設計,但在專案評審時需要對冗餘欄位的設計給予解釋。

9、TEXT欄位作為大體量文字存儲,必須放在獨立的表中 , 用PK與主表關聯。如無特殊需要,禁止使用TEXTBLOB欄位。

10、需要定期刪除(或轉移)過期資料的表,透過分錶解決,我們的做法是按照2/8法則將操作頻率較低的歷史資料遷移到歷史表中,依照時間或則曾Id做切割點。

11、單表字段數不要太多,建議最多不要大於50個。過度的寬表對效能也是很大的影響。

12、MySQL在處理大表時,效能就開始明顯降低,所以建議單表物理大小限制在16GB,表中資料行數控制在2000W內。

業界的規則是超過2000W效能開始明顯降低。但這個數值是彈性的,你可以根據實際情況來測試來判斷,例如阿里的標準就是500W,百度的確是2000W。實際上是否寬表,單行資料所佔用的空間都有起到作用的。

13、如果資料量或資料成長在前期規劃時就較大,那麼在設計評審時就應加入分錶策略,後續會有專門的文章來分析資料拆分的做法:垂直拆分(垂直分庫與垂直分錶)、水平分割(分庫分錶與庫內分錶);

#14、無特殊需求,嚴禁使用分區表

欄位設計規格

1、INT:如無特殊需要,存放整數數字使用UNSIGNED INT型,整數欄位後的數字代表顯示長度。例如id int(11) NOT NULL

2、DATETIME:所有需要精確到時間(時分秒)的欄位都使用DATETIME,不要使用TIMESTAMP型別。

對於TIMESTAMP,它把寫入的時間從目前時區轉換為UTC(世界標準時間)進行儲存。查詢時,將其轉換為客戶端當前時區進行傳回。而對於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’。

但是特殊情況,對於跨時區的業務,TIMESTAMP更為合適。

3、VARCHAR:所有動態長度字串全部使用VARCHAR類型,類似於狀態等有限類別的欄位,也使用可以比較明顯表示出實際意義的字串,而不應該使用INT之類的數字來代替;VARCHAR(N)

N表示的是字元數而不是位元組數。例如VARCHAR(255),可以最大可儲存255個字元(字元包括英文字母,漢字,特殊字元等)。但N應盡可能小,因為MySQL一個表中所有的VARCHAR字段最大長度是65535個字節,且儲存字元數目由所選字元集決定。

如UTF8儲存一個字元最大要3個字節,那麼varchar在存放佔用3個位元組長度的字元時不應超過21845個字元。同時,在進行排序和建立臨時表一類的記憶體操作時,會使用N的長度申請記憶體。 (如無特殊需要,原則上單一varchar型欄位不允許超過255個字元)

4、TEXT:僅當字元數量可能超過20000個的時候,才可以使用TEXT類型來存放字元類資料,因為所有MySQL資料庫都會使用UTF8字元集。

所有使用TEXT類型的欄位必須和原始表進行分拆,與原始表主鍵單獨組成另外一個表進行存放,與大文本欄位的隔離,目的是。如無特殊需要,不使用MEDIUMTEXTTEXTLONGTEXT類型

##5、對於精確浮點型資料存儲,需要使用

DECIMAL,嚴禁使用FLOATDOUBLE

6、如無特殊需要,盡量不使用

BLOB類型

#7、如無特殊需要,欄位建議使用

NOT NULL屬性,可用預設值取代NULL

8、自增欄位類型必須是整數且必須為

UNSIGNED,建議類型為INTBIGINT,且自增欄位必須是主鍵或主鍵的一部分。

索引設計規格

1、索引區分

#索引必須建立在索引選擇性(區分度)較高的欄位上,選擇性的計算方式為:  

selecttivity = count(distinct c_name)/count(*); 如果區分度結果小於0.2,則不建議在此列上建立索引,否則大機率會拖慢SQL執行

2、遵循最左字首

#####

对于确定需要组成组合索引的多个字段,设计时建议将选择性高的字段靠前放。使用时,组合索引的首字段,必须在where条件中,且需要按照最左前缀规则去匹配。

3、禁止使用外键,可以在程序级别来约束完整性

4、Text类型字段如果需要创建索引,必须使用前缀索引

5、单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表,应尽量少建索引,索引建立的原则理论上是多读少写的场景。

6、ORDER BYGROUP BYDISTINCT的字段需要添加在索引的后面,形成覆盖索引

7、正确理解和计算索引字段的区分度,文中有计算规则,区分度高的索引,可以快速得定位数据,区分度太低,无法有效的利用索引,可能需要扫描大量数据页,和不使用索引没什么差别。

8、正确理解和计算前缀索引的字段长度,文中有判断规则,合适的长度要保证高的区分度和最恰当的索引存储容量,只有达到最佳状态,才是保证高效率的索引。

9、联合索引注意最左匹配原则:必须按照从左到右的顺序匹配,MySQL会一直向右匹配索引直到遇到范围查询(>、<、betweenlike)然后停止匹配。

如:depno=1 and empname>'' and job=1 如果建立(<code>depno,empname,job)顺序的索引,job是用不到索引的。

10、应需而取策略,查询记录的时候,不要一上来就使用*,只取需要的数据,可能的话尽量只利用索引覆盖,可以减少回表操作,提升效率。

11、正确判断是否使用联合索引(上面联合索引的使用那一小节有说明判断规则),也可以进一步分析到索引下推(IPC),减少回表操作,提升效率。

12、避免索引失效的原则:禁止对索引字段使用函数、运算符操作,会使索引失效。这是实际上就是需要保证索引所对应字段的”干净度“。

13、避免非必要的类型转换,字符串字段使用数值进行比较的时候会导致索引无效。

14、模糊查询’%value%’会使索引无效,变为全表扫描,因为无法判断扫描的区间,但是’value%’是可以有效利用索引。

15、索引覆盖排序字段,这样可以减少排序步骤,提升查询效率

16、尽量的扩展索引,非必要不新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

举例子:比如一个品牌表,建立的的索引如下,一个主键索引,一个唯一索引

1 PRIMARY KEY (`id`),2 UNIQUE KEY `uni_brand_define` (`app_id`,`define_id`)

当你同事业务代码中的检索语句如下的时候,应该立即警告了,即没有覆盖索引,也没按照最左前缀原则:

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应该是有序并且无意义的,由开发人员自定义,尽可能简短,并且是自增序列。

2、表中除PK以外,还存在唯一性约束的,可以在数据库中创建以“uk_”作为前缀的唯一约束索引。

3、PK字段不允许更新。

4、禁止创建外键约束,外键约束由程序控制。

5、如无特殊需要,所有字段必须添加非空约束,即not null

6、如无特殊需要,所有字段必须有默认值。

SQL使用规范

select 检索的规范性

1、尽量避免使用select *,join语句使用select *可能导致只需要访问索引即可完成的查询需要回表取数。

一种是可能取出很多不需要的数据,对于宽表来说,这是灾难;一种是尽可能避免回表,因为取一些根本不需要的数据而回表导致性能低下,是很不合算。

2、严禁使用 select * from t_name ,而不加任何where条件,道理一样,这样会变成全表全字段扫描。

3、MySQL中的text类型字段存储:

3.1、不与其他普通字段存放在一起,因为读取效率低,也会影响其他轻量字段存取效率。

3.2、如果不需要text类型字段,又使用了select *,会让该执行消耗大量io,效率也很低下

4、在取出欄位上可以使用相關函數,但應盡可能避免出現now() , rand() , sysdate() 等不確定結果的函數,在Where條件中的過濾條件欄位上嚴禁使用任何函數,包括資料類型轉換函數。大量的計算和轉換會造成效率低下,這個在索引那邊也描述過了。

5、分頁查詢語句全部都需要帶有排序條件, 否則很容易引起亂序

6、用in()/union替換or ,效率會好一些,並注意in的個數小於300

7、嚴禁使用%前綴進行模糊前綴查詢:如:select a,b,c from t_name where a like '%name'; 可以使用%模糊後綴查詢如:select a,b from t_name where a like 'name%';

#8、避免使用子查詢,可以把子查詢優化為join操作

通常子查詢在in子句中,且子查詢中為簡單SQL(不包含uniongroup byorder bylimit子句)時,才可以把子查詢轉換成關聯查詢進行最佳化。

子查詢效能差的原因:

· 子查詢的結果集無法使用索引,通常子查詢的結果集就會儲存到暫存表中,不論是記憶體臨時表或磁碟暫存表都不會存在索引,所以查詢效能會受到一定的影響;

· 特別是對於傳回結果集比較大的子查詢,其對查詢效能的影響也就越大;

· 由於子查詢會產生大量的臨時表也沒有索引,所以會消耗過多的CPU和IO資源,產生大量的慢查詢。

運算的規範

1、禁止使用不含欄位清單的INSERT語句

如: insert into values ('a','b','c');  應使用insert into t_name(c1,c2,c3) values ('a','b','c');

2、大批量寫入操作(UPDATEDELETEINSERT),需要分批多次進行動作

· 大批量操作可能會造成嚴重的主從延遲,特別是主從模式下,大批量操作可能會造成嚴重的主從延遲,因為需要slave#masterbinlog中讀取日誌來進行資料同步。

· binlog日誌為row格式時會產生大量的日誌

以上是最詳細的MySQ設計與開發規格【推薦收藏】的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:learnku.com。如有侵權,請聯絡admin@php.cn刪除