各廠使用的MySQL團隊開發規範,太詳細了,建議收藏!

語言: CN / TW / HK

關注公眾號:IT老哥,每天讀一篇乾貨技術文章,一年後你會發現一個不一樣的自己

  • 數據庫對象命名規範

  • 數據庫對象

  • 數據庫對象全局命名規範

  • 數據庫命名規範

  • 表命名規範

  • 字段命名規範

  • 索引命名規範

  • 視圖命名規範

  • 存儲過程命名規範

  • 函數命名規範

  • 觸發器命名規範

  • 約束命名規範

  • 用户命名規範

  • 數據庫對象設計規範

  • 存儲引擎的選擇

  • 字符集的選擇

  • 表設計規範

  • 字段設計規範

  • 索引設計規範

  • 約束設計規範

  • SQL使用規範

  • select 檢索的規範性

  • 操作的規範性

  • 程序上的約束

圖片

數據庫對象命名規範

數據庫對象

數據庫對象是數據庫的組成部分,常見的有以下幾種:表(Table )、索引(Index)、視圖(View)、圖表(Diagram)、缺省值(Default)、規則(Rule)、觸發器(Trigger)、存儲過程(Stored Procedure)、 用户(User)等。命名規範是指數據庫對象如數據庫(SCHEMA)、表(TABLE)、索引(INDEX)、約束(CONSTRAINTS)等的命名約定。

數據庫對象全局命名規範

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_contact表member_id和friend_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)。

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

5、命名應使用小寫。

用户命名規範

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

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

數據庫對象設計規範

存儲引擎的選擇

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

可以通過 show variables like 'default_storage_engine' 來查看當前默認引擎。主要有MyISAM 和 InnoDB,從5.5版本開始默認使用 InnoDB 引擎。

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

字符集的選擇

1、如無特殊要求,必須使用utf8或utf8mb4。

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

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

可以使用如下腳本來查看數據庫的編碼格式

``` SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';  SHOW VARIABLES Like '%char%';

```

表設計規範

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

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

3、表必須要有PK,主鍵的優勢是唯一標識、有效引用、高效檢索,所以一般情況下儘量有主鍵字段。

4、一個字段只表示一個含義。

5、表不應該有重複列。

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

7、需要join的字段(連接鍵),數據類型必須保持絕對一致,避免隱式轉換。比如關聯的字段都是int類型。

8、設計應至少滿足第三範式,儘量減少數據宂餘。一些特殊場景允許反範式化設計,但在項目評審時需要對宂餘字段的設計給出解釋。

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

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類型的字段必須和原表進行分拆,與原表主鍵單獨組成另外一個表進行存放,與大文本字段的隔離,目的是。如無特殊需要,不使用MEDIUMTEXT、TEXT、LONGTEXT類型

5、對於精確浮點型數據存儲,需要使用DECIMAL,嚴禁使用FLOAT和DOUBLE。

6、如無特殊需要,儘量不使用BLOB類型

7、如無特殊需要,字段建議使用NOT NULL屬性,可用默認值代替NULL

8、自增字段類型必須是整型且必須為UNSIGNED,推薦類型為INT或BIGINT,並且自增字段必須是主鍵或者主鍵的一部分。

索引設計規範

1、索引區分度

索引必須創建在索引選擇性(區分度)較高的列上,選擇性的計算方式為:  selecttivity = count(distinct c_name)/count(*) ; 如果區分度結果小於0.2,則不建議在此列上創建索引,否則大概率會拖慢SQL執行

2、遵循最左前綴

對於確定需要組成組合索引的多個字段,設計時建議將選擇性高的字段靠前放。使用時,組合索引的首字段,必須在where條件中,且需要按照最左前綴規則去匹配。

3、禁止使用外鍵,可以在程序級別來約束完整性

4、Text類型字段如果需要創建索引,必須使用前綴索引

5、單張表的索引數量理論上應控制在5個以內。經常有大批量插入、更新操作表,應儘量少建索引,索引建立的原則理論上是多讀少寫的場景。

6、ORDER BY,GROUP BY,DISTINCT的字段需要添加在索引的後面,形成覆蓋索引

7、正確理解和計算索引字段的區分度,文中有計算規則,區分度高的索引,可以快速得定位數據,區分度太低,無法有效的利用索引,可能需要掃描大量數據頁,和不使用索引沒什麼差別。

8、正確理解和計算前綴索引的字段長度,文中有判斷規則,合適的長度要保證高的區分度和最恰當的索引存儲容量,只有達到最佳狀態,才是保證高效率的索引。

9、聯合索引注意最左匹配原則:必須按照從左到右的順序匹配,MySQL會一直向右匹配索引直到遇到範圍查詢(>、<、between、like)然後停止匹配。

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

10、應需而取策略,查詢記錄的時候,不要一上來就使用*,只取需要的數據,可能的話儘量只利用索引覆蓋,可以減少回表操作,提升效率。

11、正確判斷是否使用聯合索引(上面聯合索引的使用那一小節有説明判斷規則),也可以進一步分析到索引下推(IPC),減少回表操作,提升效率。

12、避免索引失效的原則:禁止對索引字段使用函數、運算符操作,會使索引失效。這是實際上就是需要保證索引所對應字段的”乾淨度“。

13、避免非必要的類型轉換,字符串字段使用數值進行比較的時候會導致索引無效。

14、模糊查詢'%value%'會使索引無效,變為全表掃描,因為無法判斷掃描的區間,但是'value%'是可以有效利用索引。

15、索引覆蓋排序字段,這樣可以減少排序步驟,提升查詢效率

16、儘量的擴展索引,非必要不新建索引。比如表中已經有a的索引,現在要加(a,b)的索引,那麼只需要修改原來的索引即可。

舉例子:比如一個品牌表,建立的的索引如下,一個主鍵索引,一個唯一索引

`` PRIMARY KEY (id),    UNIQUE KEYuni_brand_define(app_id,define_id`)

```

當你同事業務代碼中的檢索語句如下的時候,應該立即警告了,即沒有覆蓋索引,也沒按照最左前綴原則:

``` select brand_id,brand_name from  ds_brand_system where status=?  and define_id=?  and app_id=?

```

建議改成如下:

``` 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(不包含union、group by、order by、limit從句)時,才可以把子查詢轉化為關聯查詢進行優化。

子查詢性能差的原因:

「·」 子查詢的結果集無法使用索引,通常子查詢的結果集會被存儲到臨時表中,不論是內存臨時表還是磁盤臨時表都不會存在索引,所以查詢性能 會受到一定的影響;

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

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

操作的規範性

1、禁止使用不含字段列表的INSERT語句

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

2、大批量寫操作(UPDATE、DELETE、INSERT),需要分批多次進行操作

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

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

程序上的約束

後續我們團隊的目標是研發評審工具對開發同學提交的建庫、建表、刷數據、查詢的語句進行分析,看看是否符合應有的規範。如果不符合,駁回修改。