MySQL全面瓦解26：代碼評審中的MySQL（團隊規範）

資料庫對象命名規範

資料庫對象

資料庫對象是資料庫的組成部分，常見的有以下幾種：表（Table ）、索引（Index）、視圖（View）、圖表（Diagram）、預設值（Default）、規則（Rule）、觸發器（Trigger）、存儲過程（Stored Procedure）、使用者（User）等。命名規範是指資料庫對象如資料庫（SCHEMA）、表（TABLE）、索引（INDEX）、限制（CONSTRAINTS）等的命名約定。

資料庫對象全局命名規範

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

2、命名隻能使用英文字母、數字、下劃線，以英文字母開頭

3、避免用MySQL的保留字如：backup、call、group等，參考MySQL 5.7+的關鍵字和保留字。

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、命名應使用小寫。

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

函數命名規範

1、函數名以func開始，表示function。之後多個單詞以下劃線（_）進行連接配接，函數命名中應展現其功能。函數名盡量不超過30個字元。

2、命名應使用小寫。

1 create function func_format_date(ctime datetime)

觸發器命名規範

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

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

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

1 DROP TRIGGER IF EXISTS trig_attach_log_d;
2 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 0,
4 `salary` float DEFAULT NULL,

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也就夠了。

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

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與主表關聯。如無特殊需要，禁止使用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)的索引，那麼隻需要修改原來的索引即可。

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

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(不包含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格式時會産生大量的日志

程式上的限制

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

評審工具建設完畢，會盡量開源。

架構與思維·公衆号：撰稿者為bat、位元組的幾位高階研發/架構。不做廣告、不賣課、不要打賞，隻分享優質技術

碼字不易，歡迎關注，歡迎轉載

作者：翁智華

出處：https://www.cnblogs.com/wzh2010/

本文采用「CC BY 4.0」知識共享協定進行許可，轉載請注明作者及出處。