原標題：史上最全的數(shù)據(jù)庫建表/SQL/索引規(guī)范

?

?

因為工作崗位的原因，負責(zé)制定了關(guān)于后端組數(shù)據(jù)庫的規(guī)約規(guī)范，作為所有產(chǎn)品線的規(guī)范，歷經(jīng)幾版的修改，最終形成下邊的文本，規(guī)范在整個后端執(zhí)行也有大半年的時間，對于整個團隊在開發(fā)階段就減少不恰當?shù)慕ū碚Z句、錯誤SQL、錯誤的索引有積極的意義，故分享出來給大家參考。

下邊分為建表規(guī)約、SQL規(guī)約、索引規(guī)約三個部分，每部分的每一條都有強制、建議兩個級別，大家在參考時，根據(jù)自己公司的情況來權(quán)衡。

建表規(guī)約

【強制】（1） 存儲引擎必須使用InnoDB

解讀：InnoDB支持事物、行級鎖、并發(fā)性能更好，CPU及內(nèi)存緩存頁優(yōu)化使得資源利用率更高。

【強制】（2）每張表必須設(shè)置一個主鍵ID，且這個主鍵ID使用自增主鍵（在滿足需要的情況下盡量短），除非在分庫分表環(huán)境下。

解讀：由于InnoDB組織數(shù)據(jù)的方式?jīng)Q定了需要有一個主鍵，而且若是這個主鍵ID是單調(diào)遞增的可以有效提高插入的性能，避免過多的頁分裂、減少表碎片提高空間的使用率。而在分庫分表環(huán)境下，則需要統(tǒng)一來分配各個表中的主鍵值，從而避免整個邏輯表中主鍵重復(fù)。

【強制】（3）必須使用utf8mb4字符集

解讀：在Mysql中的UTF-8并非“真正的UTF-8”，而utf8mb4”才是真正的“UTF-8”。

【強制】（4） 數(shù)據(jù)庫表、表字段必須加入中文注釋

解讀：大家都別懶

【強制】（5） 庫名、表名、字段名均小寫，下劃線風(fēng)格，不超過32個字符，必須見名知意，禁止拼音英文混用。

解讀：約定

【強制】（6）單表列數(shù)目必須小于30，若超過則應(yīng)該考慮將表拆分

解讀：單表列數(shù)太多使得Mysql服務(wù)器處理InnoDB返回數(shù)據(jù)之間的映射成本太高

【強制】（7）禁止使用外鍵，如果有外鍵完整性約束，需要應(yīng)用程序控制

解讀：外鍵會導(dǎo)致表與表之間耦合，UPDATE與DELETE操作都會涉及相關(guān)聯(lián)的表，十分影響SQL的性能，甚至?xí)斐伤梨i。

【強制】（8）必須把字段定義為NOT NULL并且提供默認值

解讀：a、NULL的列使索引/索引統(tǒng)計/值比較都更加復(fù)雜，對MySQL來說更難優(yōu)化 b、NULL這種類型Msql內(nèi)部需要進行特殊處理，增加數(shù)據(jù)庫處理記錄的復(fù)雜性；同等條件下，表中有較多空字段的時候，數(shù)據(jù)庫的處理性能會降低很多 c、NULL值需要更多的存儲空，無論是表還是索引中每行中的NULL的列都需要額外的空間來標識

【強制】（9）禁用保留字，如DESC、RANGE、MARCH等，請參考Mysql官方保留字。【強制】（10）如果存儲的字符串長度幾乎相等，使用CHAR定長字符串類型。

解讀：能夠減少空間碎片，節(jié)省存儲空間。更多SQL技巧可搜索公號：SQL數(shù)據(jù)庫開發(fā)

【建議】（11）在一些場景下，考慮使用TIMESTAMP代替DATETIME。

解讀：a、這兩種類型的都能表達"yyyy-MM-dd HH:mm:ss"格式的時間，TIMESTAMP只需要占用4個字節(jié)的長度，可以存儲的范圍為(1970-2038)年，在各個時區(qū)，所展示的時間是不一樣的；b、而DATETIME類型占用8個字節(jié)，對時區(qū)不敏感，可以存儲的范圍為(1001-9999)年。

【建議】（12）當心自動生成的Schema，建議所有的Schema手動編寫。

解讀：對于一些數(shù)據(jù)庫客戶端不要太過信任。

SQL規(guī)約【建議】 (1) 為了充分利用緩存，不允許使用自定義函數(shù)、存儲函數(shù)、用戶變量。

解讀：如果查詢中包含任何用戶自定義函數(shù)、存儲函數(shù)、用戶變量、臨時表、Mysql庫中的系統(tǒng)表，其查詢結(jié)果都不會被緩存。比如函數(shù)NOW或者CURRENT_DATE會因為不同的查詢時間，返回不同的查詢結(jié)果。

【強制】（2）在查詢中指定所需的列，而不是直接使用“ *”返回所有的列

解讀：a）讀取不需要的列會增加CPU、IO、NET消耗 b）不能有效的利用覆蓋索引

【強制】（3）不允許使用屬性隱式轉(zhuǎn)換

【建議】（4）在WHERE條件的屬性上使用函數(shù)或者表達式

解讀：Mysql無法自動解析這種表達式，無法使用到索引。

【強制】（5）禁止使用外鍵與級聯(lián)，一切外鍵概念必須在應(yīng)用層解決。

解讀：外鍵與級聯(lián)更新適用于單機低并發(fā)，不適合分布式、高并發(fā)集群;級聯(lián)更新是強阻塞，存在數(shù)據(jù)庫更新風(fēng)暴的風(fēng)險；外鍵影響數(shù)據(jù)庫的插入速度。

【建議】（6）應(yīng)盡量避免在WHERE子句中使用or作為連接條件

解讀：根據(jù)情況可以選擇使用UNION ALL來代替OR

【強制】（7）不允許使用%開頭的模糊查詢

解讀：根據(jù)索引的最左前綴原理，%開頭的模糊查詢無法使用索引，可以使用ES來做檢索。

索引規(guī)約【建議】（1）避免在更新比較頻繁、區(qū)分度不高的列上單獨建立索引

解讀：區(qū)分度不高的列單獨創(chuàng)建索引的優(yōu)化效果很小，但是較為頻繁的更新則會讓索引的維護成本更高

【強制】（2） JOIN的表不允許超過五個。需要JOIN的字段，數(shù)據(jù)類型必須絕對一致; 多表關(guān)聯(lián)查詢時，保證被關(guān)聯(lián)的字段需要有索引。

解讀：太多表的JOIN會讓Mysql的優(yōu)化器更難權(quán)衡出一個“最佳”的執(zhí)行計劃（可能性為表數(shù)量的階乘），同時要注意關(guān)聯(lián)字段的類型、長度、字符編碼等等是否一致。

【強制】（3）在一個聯(lián)合索引中，若第一列索引區(qū)分度等于1，那么則不需要建立聯(lián)合索引。

解讀：索引通過第一列就能夠完全定位的數(shù)據(jù)，所以聯(lián)合索引的后邊部分是不需要的。

【強制】（4）建立聯(lián)合索引時，必須將區(qū)分度更高的字段放在左邊

解讀：區(qū)分度更高的列放在左邊，能夠在一開始就有效的過濾掉無用數(shù)據(jù)。提高索引的效率，相應(yīng)我們在Mapper中編寫SQL的WHERE條件中有多個條件時，需要先看看當前表是否有現(xiàn)成的聯(lián)合索引直接使用，注意各個條件的順序盡量和索引的順序一致。

【建議】（5）利用覆蓋索引來進行查詢操作，避免回表

解讀：覆蓋查詢即是查詢只需要通過索引即可拿到所需DATA，而不再需要再次回表查詢，所以效率相對很高。我們在使用EXPLAIN的結(jié)果，extra列會出現(xiàn)："using index"。這里也要強調(diào)一下不要使用“SELECT * ”，否則幾乎不可能使用到覆蓋索引。

【建議】（6）在較長VARCHAR字段,例如VARCHAR(100)上建立索引時，應(yīng)指定索引長度，沒必要對全字段建立索引，根據(jù)實際文本區(qū)分度決定索引長度即可。

解讀：索引的長度與區(qū)分度是一對矛盾體，一般對字符串類型數(shù)據(jù)，若長度為20的索引，區(qū)分度會高達90%以上，則可以考慮創(chuàng)建長度例為20的索引，而非全字段索引。例如可以使用SELECT COUNT(DISTINCT LEFT(lesson_code, 20)) / COUNT(*) FROM lesson;來確定lesson_code字段字符長度為20時文本區(qū)分度。

【建議】（7）如果有ORDER BY的場景，請注意利用索引的有序性。ORDER BY最后的字段是聯(lián)合索引的一部分，并且放在索引組合順序的最后，避免出現(xiàn)file_sort的情況，影響查詢性能。

解讀：1、假設(shè)有查詢條件為WHERE a=? and b=? ORDER BY c； 存在索引：a_b_c，則此時可以利用索引排序。2、反例：在查詢條件中包含了范圍查詢，那么索引有序性無法利用，如:WHERE a>10 ORDER BY b; 索引a_b無法排序。

【建議】（8）在where中索引的列不能某個表達式的一部分，也不能是函數(shù)的參數(shù)。

解讀：即是某列上已經(jīng)添加了索引，但是若此列成為表達式的一部分、或者是函數(shù)的參數(shù)，Mysql無法將此列單獨解析出來，索引也不會生效。

【建議】 （9）我們在where條件中使用范圍查詢時，索引最多用于一個范圍條件，超過一個則后邊的不走索引。

解讀：Mysql能夠使用多個范圍條件里邊的最左邊的第一個范圍查詢，但是后邊的范圍查詢則無法使用。

【建議】 （10）在多個表進行外連接時，表之間的關(guān)聯(lián)字段類型必須完全一致

解讀：當兩個表進行Join時，字段類型若沒有完全一致，則加索引也不會生效，這里的完全一致包括但不限于字段類型、字段長度、字符集、collection等等

參考

《High.Performance.MySQL.3rd.Edition》 《阿里巴巴java開發(fā)手冊》

來源：juejin.im/post/6871969929365553165

- END -

愛數(shù)據(jù)福利大放送

回復(fù)【s30】即可領(lǐng)取

《深入淺出SQL》（中文版）

?返回搜狐，查看更多

責(zé)任編輯：