資料庫物件命名規範
1、資料庫物件
資料庫物件是資料庫的組成部份，常見的有以下幾種：
表（Table ）、索引（Index）、檢視（View）、圖表（Diagram）、缺省值（Default）、規則（Rule）、觸發器（Trigger）、儲存過程（Stored Procedure）、 使用者（User）等。命名規範是指資料庫物件如資料庫（SCHEMA）、表（TABLE）、索引（INDEX）、約束（CONSTRAINTS）
等的命名約定。
2、資料庫物件全域命名規範
1、命名使用具有意義的英文詞匯，詞匯中間以底線分隔
2、命名只能使用英文字母、數位、底線，以英文字母開頭
3、避免用MySQL的保留字如：backup、call、group等
4、所有資料庫物件使用小寫字母，實際上MySQL中是可以設定大小寫是否敏感的，為了保證統一性，我們這邊規範全部小寫表示。
3、資料庫命名規範
1、資料庫命名盡量不超過30個字元。
2、資料庫命名一般為計畫名稱+代表庫含義的簡寫，比如IM計畫的工作流資料庫，可以是 im_flow。
3、資料庫建立時必須添加預設字元集和校對規則子句。預設字元集為UTF8（已遷移dumbo的使用utf8mb4）
4、命名應使用小寫。
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表視情況而定，也盡量簡短為宜，命名應使用小寫。
5、欄位命名規範
1、欄位命名需要表示其實際含義的英文單詞或簡寫，單詞之間用底線 _ 進行連線，如 service_ip、service_port。
2、各表之間相同意義的欄位必須同名，比如a表和b表都有建立時間，應該統一為create_time，不一致會很混亂。
3、多個單詞以底線 _ 分隔
4、欄位名盡量不超過30個字元，命名應該使用小寫
6、索引命名規範
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)。
7、檢視命名規範
1、檢視名以v開頭，表示view，完整結構是v+檢視內容含義縮寫。
2、如果檢視只來源單個表，則為v+表名。如果檢視由幾個表關聯產生就用v+底線（_）連線幾個表名，檢視名盡量不超過30個字元。如超過30個字元則取簡寫。
3、如無特殊需要，嚴禁開發人員建立檢視。
4、命名應使用小寫。
8、儲存過程命名規範
1、儲存過程名以sp開頭，表示儲存過程（storage procedure）。之後多個單詞以底線（_）進行連線。儲存過程命名中應體現其功能。儲存過程名盡量不能超過30個字元。
2、儲存過程中的輸入參數以i_開頭，輸出參數以o_開頭。
3、命名應使用小寫。
createprocedure sp_multi_param(in i_id bigint,in i_name varchar(32),out o_memo varchar(100))
9、函式命名規範
1、函式名以func開始，表示function。之後多個單詞以底線（_）進行連線，函式命名中應體現其功能。函式名盡量不超過30個字元。
2、命名應使用小寫。
createfunction func_format_date(ctime datetime)
10、觸發器命名規範
1、觸發器以trig開頭，表示trigger 觸發器。
2、基本部份，描述觸發器所加的表，觸發器名盡量不超過30個字元。
3、字尾（_i,_u,_d）,表示觸發條件的觸發方式（insert,update或delete）。
4、命名應使用小寫。
DROPTRIGGERIFEXISTS trig_attach_log_d;
CREATETRIGGER trig_attach_log_d AFTERDELETEON t_dept FOREACHROW;
11、 約束命名規範
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)。
`id` int(11) NOT NULL,
`name` varchar(30) DEFAULT '',
`deptId` int(11) DEFAULT 0,
`salary` float DEFAULT NULL,
4、出於效能考慮，如無特殊需要，建議不使用外來鍵。參照完整性由程式碼控制。這個也是我們普遍的做法，從程式角度進行完整性控制，但是如果不註意，也會產生臟數據。
5、命名應使用小寫。
12、 使用者命名規範
1、生產使用的使用者命名格式為 code_套用
2、唯讀使用者命名規則為 read_套用
資料庫物件設計規範
13、 儲存引擎的選擇
1、如無特殊需求，必須使用innodb儲存引擎。
可以透過
show variables like 'default_storage_engine'
來檢視當前預設引擎。主要有MyISAM 和 InnoDB，從5.5版本開始預設使用 InnoDB 引擎。
基本的差別為：MyISAM型別不支持事務處理等高級處理，而InnoDB型別支持。MyISAM型別的表強調的是效能，其執行速度比InnoDB型別更快，但是不提供事務支持，而InnoDB提供事務支持以及外部鍵等高級資料庫功能。
14、 字元集的選擇
1、如無特殊要求，必須使用utf8或utf8mb4。
在國內，選擇對中文和各語言支持都非常完善的utf8格式是最好的方式，MySQL在5.5之後增加utf8mb4編碼，mb4就是most bytes 4的意思，專門用來相容四字節的unicode。
所以utf8mb4是utf8的超集，除了將編碼改為utf8mb4外不需要做其他轉換。當然，為了節省空間，一般情況下使用utf8也就夠了。
可以使用如下指令碼來檢視資料庫的編碼格式
SHOWVARIABLESWHERE Variable_name LIKE'character_set_%'OR Variable_name LIKE'collation%';
-- 或
SHOWVARIABLESLike'%char%';
15、 表設計規範
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、無特殊需求，嚴禁使用分區表
16、 欄位設計規範
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，並且自增欄位必須是主鍵或者主鍵的一部份。
17、 索引設計規範
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 KEY `uni_brand_define` (`app_id`,`define_id`)
當你同事業務程式碼中的檢索語句如下的時候，應該立即警告了，即沒有覆蓋索引，也沒按照最左字首原則：
select brand_id,brand_name from ds_brand_system wherestatus=? and define_id=? and app_id=?
建議改成如下：
select brand_id,brand_name from ds_brand_system where app_id=? and define_id=? andstatus=?
18、 約束設計規範
1、PK應該是有序並且無意義的，由開發人員自訂，盡可能簡短，並且是自增序列。
2、表中除PK以外,還存在唯一性約束的,可以在資料庫中建立以「uk_」作為字首的唯一約束索引。
3、PK欄位不允許更新。
4、禁止建立外來鍵約束，外來鍵約束由程式控制。
5、如無特殊需要，所有欄位必須添加非空約束，即not null。
6、如無特殊需要，所有欄位必須有預設值。
SQL使用規範
19、 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從句)時，才可以把子查詢轉化為關聯查詢進行最佳化。
20、 子查詢效能差的原因：
子查詢的結果集無法使用索引，通常子查詢的結果集會被儲存到臨時表中，不論是記憶體臨時表還是磁盤臨時表都不會存在索引，所以查詢效能 會受到一定的影響；
特別是對於返回結果集比較大的子查詢，其對查詢效能的影響也就越大；
由於子查詢會產生大量的臨時表也沒有索引，所以會消耗過多的CPU和IO資源，產生大量的慢查詢。
21、 操作的規範性
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格式時會產生大量的日誌
程式上的約束
後續我們團隊的目標是研發評審工具對開發同學送出的建庫、建表、刷數據、查詢的語句進行分析，看看是否符合應有的規範。如果不符合，駁回修改。
作者：翁智華
出處：cnblogs.com/wzh2010/
>>
END
精品資料，超贊福利，免費領
微信掃碼/長按辨識 添加【技術交流群】
群內每天分享精品學習資料
最近開發整理了一個用於速刷面試題的小程式；其中收錄了上千道常見面試題及答案(包含基礎、並行、JVM、MySQL、Redis、Spring、SpringMVC、SpringBoot、SpringCloud、訊息佇列等多個型別)，歡迎您的使用。
👇👇
👇點選"閱讀原文"，獲取更多資料（持續更新中）
