（一）建表規(guī)約

1.【強(qiáng)制】在編寫建表語句時(shí)，需同時(shí)考慮回滾、以及必要的初始化語句；生產(chǎn)環(huán)境變更表或數(shù)據(jù)時(shí)須提供變更和回滾sql文件。

? ? ??說明： 以"項(xiàng)目名_版本號_Create.DDL"、"項(xiàng)目名_版本號_RollBack.DDL、"項(xiàng)目名_版本號_InitData.DML"，

? ? ? ? ? ? ? ? ? 分別表示創(chuàng)建、回滾、初始化數(shù)據(jù)的sql文件放在項(xiàng)目工程的對應(yīng)文件夾中。

2.【強(qiáng)制】數(shù)據(jù)庫版本統(tǒng)一為MySQL5.6，引擎統(tǒng)一使用InnoDB。

3.【強(qiáng)制】庫名、表名、字段命名，必須使用小寫字母或數(shù)字組成，庫名應(yīng)與應(yīng)用名稱一致、表或字段名以英文單詞加"_"

? ? ?? ? ? ? ? ??表示其用途/目標(biāo)/意義來命名，表名、字段名不超過32個(gè)字符、表字段數(shù)量不超過35個(gè)。

? ? ? ? ? ? ? ? ?正例：poseidon(庫名)、send_task(表名)，send_task_logs_201710(表名)、user_login_name(字段名)

4.【強(qiáng)制】主鍵索引名為 pk_字段名、唯一索引名為 uk_字段名、普通索引名則為 idx_字段名，索引數(shù)量不超過5個(gè)。

? ? ??說明：pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的簡稱

5.【強(qiáng)制】表達(dá)是與否概念的字段，必須使用 is_xxx 的方式命名，數(shù)據(jù)類型是 unsigned tinyint（ 1 表示是，0 表示否）。

? ? ??說明：任何字段如果為非負(fù)數(shù)，必須是 unsigned。

? ? ??正例：表達(dá)邏輯刪除的字段名 is_deleted，1 表示刪除，0 表示未刪除。

6.【參考】業(yè)務(wù)數(shù)據(jù)需要進(jìn)行簡單的歸類，但相對要支持靈活變化，應(yīng)使用數(shù)據(jù)字典表或通過統(tǒng)一服務(wù)的形式獲取，

? ? ? ? ? ? ? ? ?禁止通過distinct語句在業(yè)務(wù)數(shù)據(jù)表去重獲取,禁止將歸類寫死在業(yè)務(wù)處理類和方法中。

7.【強(qiáng)制】合適的字符存儲長度，不但節(jié)約數(shù)據(jù)庫表空間、節(jié)約索引存儲，更重要的是提升檢索速度。

? ? ?說明：設(shè)計(jì)時(shí)盡量使用少的空間存儲字段，如能用int的就不用char或者varchar、能用tinyint的就不用int 、

? ? ? 能用varchar(20)的就不用varchar(255)。

8.【推薦】關(guān)于char、varchar、text應(yīng)根據(jù)是否定長、最大支持長度、效率等因素合理選擇類型，建議為：

? ? ? ? ? ? ? 1)?經(jīng)常變化的字段用varchar

? ? ? ? ? ? ? 2)?知道固定長度的用char

? ? ? ? ? ? ? 3)?盡量用varchar

? ? ? ? ? ? ? 4)?超過255字節(jié)的只能用varchar或者text

? ? ? ? ? ? ? 5)?能用varchar的地方不用text

? ? ? ? ? ? ? 6) 如超長(如5000)定義字段類型為 text，獨(dú)立出來一張表，用主鍵來對應(yīng)，避免影響其它字段執(zhí)引效率。

9.【禁止】禁用保留字，如 desc、range、match、delayed 等，請參考?MySQL 官方保留字。

10.【禁止】小數(shù)類型為 decimal，禁止使用 float 和 double。

? ? ? ??說明：float 和 double 在存儲的時(shí)候，存在精度損失的問題，很可能在值的比較時(shí)，得到不

? ? ? ? ? ? ? ? ? ?正確的結(jié)果。如果存儲的數(shù)據(jù)范圍超過 decimal 的范圍，建議將數(shù)據(jù)拆成整數(shù)和小數(shù)分開存儲。

11.【強(qiáng)制】表必備三字段：id, create_time, modified_time。

? ? ? ? ?說明：其中 id 必為主鍵。create_time表示主動創(chuàng)建,?modified_time過去分詞表示被動更新。

12.【強(qiáng)制】時(shí)間格式統(tǒng)一用yyyy-MM-dd HH:mm:ss.fff，精準(zhǔn)到毫秒；存儲、傳輸統(tǒng)一用北京時(shí)間，

? ? ? ? ??如有其它時(shí)區(qū)時(shí)間(如下單、付款等時(shí)間）保留原始時(shí)間、轉(zhuǎn)換的原則為誰用誰轉(zhuǎn)換。

13.【推薦】字段允許適當(dāng)冗余，以提高查詢性能，但必須考慮數(shù)據(jù)一致。冗余字段應(yīng)遵循：

? ? ? ? ? ? ? ? ? ?1）不是頻繁修改的字段。

? ? ? ? ? ? ? ? ? ?2）不是 varchar 超長字段，更不能是 text 字段。

? ? ? ?正例：商品類目名稱使用頻率高，字段長度短，名稱基本一成不變，可在相關(guān)聯(lián)的表中冗余存

? ? ? ? ? ? ? ? ? 儲類目名稱，避免關(guān)聯(lián)查詢。

14.【推薦】單表行數(shù)超過 100 萬行或者單表容量超過 1GB，才推薦進(jìn)行分庫分表分區(qū)。

? ? ? ? ?說明：如果預(yù)計(jì)三年后的數(shù)據(jù)量根本達(dá)不到這個(gè)級別，請不要在創(chuàng)建表時(shí)就分庫分表分區(qū)。

15.【強(qiáng)制】表、字段注釋(COMMENT)需遵守以下注釋約束，如有修改字段含義或?qū)ψ侄伪硎镜臓顟B(tài)追加時(shí)，

? ? ? ? ? ? ? ? ? ? 需要及時(shí)更新字段注釋以及《數(shù)據(jù)庫設(shè)計(jì)說明書》，注釋格式參考：

? ? ? ? ? ? ? ? ? 1)? 表/視圖注釋格式為：用途|負(fù)責(zé)人|創(chuàng)建日期 ，如：記錄用戶身份證號碼|張三|2017-12-28

? ? ? ? ? ? ? ? ? 2)? 字段注釋格式為：用途|說明 ， 如：記錄用戶性別|1男，2女，3未知(李四 2017-12-28 新增)

? ? ? ? ? ? ? ? ? 3)? 關(guān)聯(lián)字段來源需要說明，來自哪張表的那個(gè)字段，如：取自eload_user.user_id

16.【禁止】禁止定義枚舉類型的表；因?yàn)樵谑褂胏anal內(nèi)核的產(chǎn)品無法拉到數(shù)據(jù)

17.【強(qiáng)制】字符類型的存儲禁止用null，用""代替；如有將字段和值拼接成字符串存儲的需要，統(tǒng)一先轉(zhuǎn)json格式存儲。

（二）索引規(guī)范

1.【推薦】ID、關(guān)聯(lián)字段、業(yè)務(wù)外鍵、create_time 字段推薦建索引，在選擇性高的字段創(chuàng)建索引，注意組合索引的順序，

? ? ? ? ? ? ? ? ?利用索引的最左原則，使用復(fù)合索引，而不是添加新的索引、避免冗余索引。

? ? ? ? ? ? ? ? ?反例：idx_a_b_c(a,b,c)、idx_a(a)、idx_a_b(a,b)

2.【推薦】業(yè)務(wù)上具有唯一特性的字段，且具有強(qiáng)唯一性要求。推薦建成唯一索引。

? ? ??說明： 不要以為唯一索引影響了 insert 速度，這個(gè)速度損耗可以忽略，但提高查找速度是明顯的；

? ? ? ? ? ? ? ? ? 另外，即使在應(yīng)用層做了非常完善的校驗(yàn)控制，只要沒有唯一索引，根據(jù)墨菲定律，必然有臟數(shù)據(jù)產(chǎn)生。

? ? ??示例：如身份證號碼，在業(yè)務(wù)層面已經(jīng)做了唯一性檢查，但在表設(shè)計(jì)時(shí)還是要加唯一索引約束。

3.【推薦】原則上超過三個(gè)表禁止 join，如超過三張以上表join須集體評審討論通過。需要 join 的字段，

? ? ? ? ? ? ? ? ?數(shù)據(jù)類型必須絕對一致；多表關(guān)聯(lián)查詢時(shí)，保證被關(guān)聯(lián)的字段需要有索引。

? ? ??說明：即使雙表 join 也要注意表索引、SQL 性能。

4.【推薦】在 varchar 字段上建立索引時(shí)，建議指定索引長度，沒必要對全字段建立索引，根據(jù)

? ? ? ? ? ? ? ? ?實(shí)際文本區(qū)分度決定索引長度即可。

? ? ? ?說明：索引的長度與區(qū)分度是一對矛盾體，一般對字符串類型數(shù)據(jù)，長度為 20 的索引，區(qū)分

? ? ? ? ? ? ? ? 度會高達(dá) 90%以上，可以使用 count(distinct left(列名, 索引長度))/count(*)的區(qū)分度來確定。

? ? ? ?示例：如varchar(1024)，索引長度為36時(shí)區(qū)分度就有?90%以上，就沒必須建立1024長度的索引。

5.【強(qiáng)制】頁面搜索嚴(yán)禁左模糊或者全模糊，如果需要請走搜索引擎來解決。

? ? ? ?說明：索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引。

6.【推薦】如果有 order by 的場景，請注意利用索引的有序性。order by 最后的字段是組合

? ? ? ? ? ? ? ? ?索引的一部分，并且放在索引組合順序的最后，避免出現(xiàn) file_sort 的情況，影響查詢性能。

? ? ? ? ? ? ? ? ?正例：where a=? and b=? order by c; 索引：a_b_c

? ? ? ? ? ? ? ? ?反例：索引中有范圍查找，那么索引有序性無法利用，如：WHERE a>10 ORDER BY b; 索引a_b 無法排序。

7. 【推薦】利用覆蓋索引來進(jìn)行查詢操作，避免回表。

? ? ? ?說明：如果索引包含所有滿足查詢需要的數(shù)據(jù)的索引成為覆蓋索引(Covering Index)，也就是平時(shí)所說的不需要回表操作

? ? ? ?說明：使用explain，可以通過輸出的extra列來判斷，對于一個(gè)索引覆蓋查詢，顯示為using index,

? ? ? ? ? ? ? ? ??MySQL查詢優(yōu)化器在執(zhí)行查詢前會決定是否有索引覆蓋查詢

8. 【推薦】利用延遲關(guān)聯(lián)或者子查詢優(yōu)化超多分頁場景。

? ? ??說明：MySQL 并不是跳過 offset 行，而是取 offset+N 行，然后返回放棄前 offset 行，返回

? ? ? ? ? ? ? ? ?N 行，當(dāng) offset 特別大的時(shí)候，效率會非常的低下。

? ? ??正例：通過使用覆蓋索引查詢返回需要的主鍵,再根據(jù)主鍵關(guān)聯(lián)原表獲得需要的數(shù)據(jù)。

9. 【推薦】SQL 性能優(yōu)化的目標(biāo)：至少要達(dá)到 range 級別，要求是 ref 級別，如果可以是 consts最好。

? ? ??說明： 以下列出的12種級別，等級依次降低：

? ? ? 1)?system：系統(tǒng)表，表只有一行記錄。這是const表連接類型的一個(gè)特例；

? ? ? 2)?const：?表中最多只有一行匹配的記錄。讀常量，由于只有一行記錄，優(yōu)化程序里該行記錄的字段值可以被當(dāng)作是一

? ? ? ? ? 個(gè)常量，僅在查詢開始時(shí)讀取一次。當(dāng)PRIMARY KEY或UNIQUE索引與常量進(jìn)行比較時(shí)會顯示const，速度非常快；

? ? ? 3)?eq_ref：類似const，const比較的是常量，eq_ref是同另一張表中的字段關(guān)聯(lián)比較，這是最好的連接類型，

? ? ? ? ? ? ? ? eq_ref出現(xiàn)在PRIMARY KEY或UNIQUE類型索引進(jìn)行”=”做比較時(shí)檢索字段，

? ? ? ? ? ? ? ? 比較的值可以是固定值或者是表達(dá)式，表達(dá)示中可以使用表里的字段；

? ? ? 4)?ref：查詢時(shí)的索引類型不是PRIMARY KEY或UNIQUE類型索引導(dǎo)致匹配到的行有多個(gè)，或者僅能用到索引的

? ? ? ? ? ? ? ? 左前綴而非全部時(shí)的訪問類型，ref可被用于基于索引字段進(jìn)行 - 或 <=> 操作；

? ? ? 5)?ref_or_null：與ref的唯一區(qū)別就是在使用索引引用的查詢之外再增加一個(gè)空值的查詢。這種連接類型類似ref，

? ? ? ? ? ? ? ? 不同的是MySQL會在檢索的時(shí)候額外的搜索包含NULL值的記錄，它經(jīng)常用于子查詢。

? ? ? 6)?index_merge：查詢中同時(shí)使用兩個(gè)(或更多)索引，然后對索引結(jié)果進(jìn)行合并(merge)，再讀取表數(shù)據(jù)；

? ? ? 7)?unique_subquery：?子查詢中的返回結(jié)果字段組合是主鍵或唯一約束。用于in比較操作符中的子查詢錦繡谷的

? ? ? ? ? ? ? ?“鍵值唯一”的訪問類型場景中，如value in (select primary_key from table where x = x)

? ? ? 8)?index_subquery：子查詢中的返回結(jié)果字段組合是一個(gè)索引(或索引組合)，但不是一個(gè)主鍵或唯一索引，

? ? ? ? ? ? ? ? ?這種連接類型類似unique_subquery。它用子查詢來代替IN，不過它用于在子查詢中沒有唯一索引的情況下。

? ? ? 9)?range：索引范圍掃描 。只有在給定范圍的記錄才會被取出來，利用索引來取得一條記錄，

? ? ? ? ? ? ? ? ?常見于between、<、>等的查詢

? ? ?10)?index：全索引掃描 。連接類型跟ALL一樣，不同的是它只掃描索引樹。它通常會比ALL快點(diǎn)

? ? ? ? ? ? ? ? ?因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小。MySQL在查詢的字段只是單獨(dú)的索引的一部分的情況下使用這種連接類型；

? ? ?11)?fulltext：用于full text索引中用純文本匹配的方法來檢索記錄；

? ? ?12)?all：全表掃描。

10. 【強(qiáng)制】建組合索引的時(shí)候，區(qū)分度最高的在最左邊。

? ? ? ? ?正例：如果 where a=? and b=? ，a 列的幾乎接近于唯一值，那么只需要單建 idx_a 索引即可。

? ? ? ? ?說明：存在非等號和等號混合判斷條件時(shí)，在建索引時(shí)，請把等號條件的列前置。如：where a>??

? ? ? ? ? ? ? ? ? ?and b=? 那么即使 a 的區(qū)分度更高，也必須把 b 放在索引的最前列。

11. 【禁止】以下情況不走索引，在編寫sql或建索引時(shí)需慎重考慮：

? ? ? ? ? ? ? ? ?1) 條件字段選擇性弱，查出的結(jié)果集較大，不走索引；

? ? ? ? ? ? ? ? ?2)?where條件等號兩邊字段類型不同，不走索引；

? ? ? ? ? ? ? ? ?3)?優(yōu)化器分析的統(tǒng)計(jì)信息陳舊也可能導(dǎo)致不走索引；

? ? ? ? ? ? ? ? ?4)?索引字段 is null 不走索引；

? ? ? ? ? ? ? ? ?5)?對于count(*)當(dāng)索引字段有not null約束時(shí)走索引，否則不走索引；

? ? ? ? ? ? ? ? ?6)?like 后面的字符當(dāng)首位為通配符時(shí)不走索引；

? ? ? ? ? ? ? ? ?7)?使用不等于操作符如：<>、!= 等不走索引；

? ? ? ? ? ? ? ? ?8)?索引字段前加了函數(shù)或參加了運(yùn)算不走索引；

（三）SQL語句

1.【強(qiáng)制】重要的業(yè)務(wù)數(shù)據(jù)、配置數(shù)據(jù)等在做刪除處理時(shí)，推薦使用刪除標(biāo)記做邏輯刪除、不使用delete做物理刪除；

? ? ? ? ? ? ? ? ?禁止update和delete語句后面沒有帶where條件。

2.【推薦】復(fù)雜的業(yè)務(wù)邏輯的判斷或操作，建議在業(yè)務(wù)代碼中分步驟拆分編寫，如果一條sql寫完，

? ? ? ? ? ? ? ? ?會造成該sql語句過于復(fù)雜龐大，可讀性可維護(hù)性下變差，性能一般也不會太高。

3.【強(qiáng)制】sql語句應(yīng)按照業(yè)務(wù)模塊等分文件統(tǒng)一寫在xml或公用類中，禁止sql語句與業(yè)務(wù)代碼混寫在一起。

4.【禁用】"<>"、"!="、"not in"、"exsits"和"not exists"等反邏輯運(yùn)算符原則上禁用。

? ? ??說明："<>"、"!="、"not in"可用"="和"in"代替；"exsits"、"not exists"可用"等連接"和"外連接"

5.【推薦】關(guān)于or、in、union all運(yùn)算符的選擇，建議同一字段，將or改寫為in，不同字段，將or改為union all

6.【強(qiáng)制】sql語句中的where子句中的每個(gè)條件的操作符兩邊類型應(yīng)該相等，禁止?jié)撛诘臄?shù)據(jù)類型做轉(zhuǎn)換。

7.【強(qiáng)制】不要使用 count(列名)或 count(常量)來替代 count(*)，count(*)是 SQL92 定義的

? ? ? ? ? ? ? ? ?標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語法，跟數(shù)據(jù)庫無關(guān)，跟 NULL 和非 NULL 無關(guān)。

? ? ?說明：count(*)會統(tǒng)計(jì)值為 NULL 的行，而 count(列名)不會統(tǒng)計(jì)此列為 NULL 值的行。

8.【強(qiáng)制】count(distinct col) 計(jì)算該列除 NULL 之外的不重復(fù)行數(shù)，注意 count(distinct

? ? ? ? ? ? ? ? ?col1, col2) 如果其中一列全為 NULL，那么即使另一列有不同的值，也返回為 0。

9.【強(qiáng)制】當(dāng)某一列的值全是 NULL 時(shí)，count(col)的返回結(jié)果為 0，但 sum(col)的返回結(jié)果為

? ? ? ? ? ? ? ? ?NULL，因此使用 sum()時(shí)需注意 NPE 問題。

? ? ??正例：可以使用如下方式來避免 sum 的 NPE 問題：SELECT IF(ISNULL(SUM(g)),0,SUM(g))??FROM table;

10. 【強(qiáng)制】使用 ISNULL()來判斷是否為 NULL 值。

? ? ? ??說明：? NULL 與任何值的直接比較都為 NULL。

? ? ? ? ? ? ? ? ? ? 1） NULL<>NULL 的返回結(jié)果是 NULL，而不是 false。

? ? ? ? ? ? ? ? ? ? 2） NULL=NULL 的返回結(jié)果是 NULL，而不是 true。

? ? ? ? ? ? ? ? ? ? 3） NULL<>1 的返回結(jié)果是 NULL，而不是 true。

11.【強(qiáng)制】在代碼中寫分頁查詢邏輯時(shí)，若 count 為 0 應(yīng)直接返回，避免執(zhí)行后面的分頁語句。

12.【禁止】不得使用外鍵與級聯(lián)，一切外鍵概念必須在應(yīng)用層解決。

? ? ? ?說明：以學(xué)生和成績的關(guān)系為例，學(xué)生表中的 student_id是主鍵，那么成績表中的 student_id

? ? ? ? ? ? ? ? ? 則為外鍵。如果更新學(xué)生表中的 student_id，同時(shí)觸發(fā)成績表中的 student_id 更新，即為

? ? ? ? ? ? ? ? ? 級聯(lián)更新。外鍵與級聯(lián)更新適用于單機(jī)低并發(fā)，不適合分布式、高并發(fā)集群；級聯(lián)更新是強(qiáng)阻

? ? ? ? ? ? ? ? ? 塞，存在數(shù)據(jù)庫更新風(fēng)暴的風(fēng)險(xiǎn)；外鍵影響數(shù)據(jù)庫的插入速度。

13.【禁止】禁止使用存儲過程、觸發(fā)器、游標(biāo)，這些難以調(diào)試和擴(kuò)展，更沒有移植性。

14.【強(qiáng)制】數(shù)據(jù)訂正（特別是刪除、修改記錄操作）時(shí)，要先確認(rèn)，避免出現(xiàn)誤刪除，確認(rèn)無誤才能執(zhí)行更新語句。?

? ? ? ??示例：如A、B兩個(gè)線程都讀取某條數(shù)據(jù)其中一個(gè)字段值（如1000）做加100操作，A線程將值更新為1100，

? ? ? ? B線程如果不加判斷，結(jié)果也會更新也為1100，正確的結(jié)果值應(yīng)為1200，可通過增加版本號等字段來控制。

15.【推薦】in 操作能避免則避免，若實(shí)在避免不了，需要仔細(xì)評估 in 后邊的集合元素?cái)?shù)量，控制在 1000 個(gè)之內(nèi)。

16.【強(qiáng)制】數(shù)據(jù)庫編碼格式、所字符存儲與表示，均以 utf-8 編碼，注意字符統(tǒng)計(jì)函數(shù)的區(qū)別。

? ? ? ??說明：?SELECT LENGTH("輕松工作")； 返回為 12

? ? ? ? ? ? ? ? ? ? SELECT CHARACTER_LENGTH("輕松工作")； 返回為 4