MySQL規(guī)范

規(guī)范在整個(gè)后端執(zhí)行也有大半年的時(shí)間，對(duì)于整個(gè)團(tuán)隊(duì)在開發(fā)階段就減少不恰當(dāng)?shù)慕ū碚Z句、錯(cuò)誤 SQL、錯(cuò)誤的索引有積極的意義，故分享出來給大家參考。

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

建表規(guī)約

【強(qiáng)制】：①存儲(chǔ)引擎必須使用 InnoDB

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

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

解讀：由于 InnoDB 組織數(shù)據(jù)的方式?jīng)Q定了需要有一個(gè)主鍵，而且若是這個(gè)主鍵 ID 是單調(diào)遞增的可以有效提高插入的性能，避免過多的頁分裂、減少表碎片提高空間的使用率。

而在分庫分表環(huán)境下，則需要統(tǒng)一來分配各個(gè)表中的主鍵值，從而避免整個(gè)邏輯表中主鍵重復(fù)。

【強(qiáng)制】：③必須使用 utf8mb4 字符集

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

【強(qiáng)制】：④數(shù)據(jù)庫表、表字段必須加入中文注釋

解讀：大家都別懶。

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

解讀：約定。

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

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

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

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

【強(qiáng)制】：⑧必須把字段定義為 NOT NULL 并且提供默認(rèn)值

解讀：

NULL 的列使索引/索引統(tǒng)計(jì)/值比較都更加復(fù)雜，對(duì) MySQL 來說更難優(yōu)化。

NULL 這種類型 MySQL 內(nèi)部需要進(jìn)行特殊處理，增加數(shù)據(jù)庫處理記錄的復(fù)雜性；同等條件下，表中有較多空字段的時(shí)候，數(shù)據(jù)庫的處理性能會(huì)降低很多。

NULL 值需要更多的存儲(chǔ)空，無論是表還是索引中每行中的 NULL 的列都需要額外的空間來標(biāo)識(shí)。

【強(qiáng)制】：⑨禁用保留字，如 DESC、RANGE、MARCH 等

解讀：請(qǐng)參考 MySQL 官方保留字。

【強(qiáng)制】：⑩如果存儲(chǔ)的字符串長度幾乎相等，使用 CHAR 定長字符串類型

解讀：能夠減少空間碎片，節(jié)省存儲(chǔ)空間。

【建議】：?在一些場景下，考慮使用 TIMESTAMP 代替 DATETIME

解讀：

這兩種類型的都能表達(dá)"yyyy-MM-dd HHss"格式的時(shí)間，TIMESTAMP 只需要占用 4 個(gè)字節(jié)的長度，可以存儲(chǔ)的范圍為（1970-2038）年，在各個(gè)時(shí)區(qū)，所展示的時(shí)間是不一樣的。

而 DATETIME 類型占用 8 個(gè)字節(jié)，對(duì)時(shí)區(qū)不敏感，可以存儲(chǔ)的范圍為（1001-9999）年。

【建議】：?當(dāng)心自動(dòng)生成的 Schema，建議所有的 Schema 手動(dòng)編寫

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

SQL 規(guī)約

【建議】：①為了充分利用緩存，不允許使用自定義函數(shù)、存儲(chǔ)函數(shù)、用戶變量

解讀：如果查詢中包含任何用戶自定義函數(shù)、存儲(chǔ)函數(shù)、用戶變量、臨時(shí)表、MySQL 庫中的系統(tǒng)表，其查詢結(jié)果都不會(huì)被緩存。

比如函數(shù) NOW() 或者 CURRENT_DATE() 會(huì)因?yàn)椴煌牟樵儠r(shí)間，返回不同的查詢結(jié)果。

【強(qiáng)制】：②在查詢中指定所需的列，而不是直接使用“ *”返回所有的列 解讀：

讀取不需要的列會(huì)增加 CPU、IO、NET 消耗。

不能有效的利用覆蓋索引。

【強(qiáng)制】：③不允許使用屬性隱式轉(zhuǎn)換

解讀：假設(shè)我們?cè)?a target="_blank">手機(jī)號(hào)列上添加了索引，然后執(zhí)行下面的 SQL 會(huì)發(fā)生什么？

explain SELECT user_name FROM parent WHERE phone=13812345678；很明顯就是索引不生效，會(huì)全表掃描。

【建議】：④在 WHERE 條件的屬性上使用函數(shù)或者表達(dá)式

解讀：MySQL 無法自動(dòng)解析這種表達(dá)式，無法使用到索引。 【強(qiáng)制】：⑤禁止使用外鍵與級(jí)聯(lián)，一切外鍵概念必須在應(yīng)用層解決

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

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

解讀：根據(jù)情況可以選擇使用 UNION ALL 來代替 OR。 【強(qiáng)制】：⑦不允許使用 % 開頭的模糊查詢

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

索引規(guī)約

【建議】：①避免在更新比較頻繁、區(qū)分度不高的列上單獨(dú)建立索引

解讀：區(qū)分度不高的列單獨(dú)創(chuàng)建索引的優(yōu)化效果很小，但是較為頻繁的更新則會(huì)讓索引的維護(hù)成本更高。 【強(qiáng)制】：②JOIN 的表不允許超過五個(gè)。需要 JOIN 的字段，數(shù)據(jù)類型必須絕對(duì)一致; 多表關(guān)聯(lián)查詢時(shí)，保證被關(guān)聯(lián)的字段需要有索引

解讀：太多表的 JOIN 會(huì)讓 MySQL 的優(yōu)化器更難權(quán)衡出一個(gè)“最佳”的執(zhí)行計(jì)劃（可能性為表數(shù)量的階乘），同時(shí)要注意關(guān)聯(lián)字段的類型、長度、字符編碼等等是否一致。 【強(qiáng)制】：③在一個(gè)聯(lián)合索引中，若第一列索引區(qū)分度等于 1，那么則不需要建立聯(lián)合索引

解讀：索引通過第一列就能夠完全定位的數(shù)據(jù)，所以聯(lián)合索引的后邊部分是不需要的。 【強(qiáng)制】：④建立聯(lián)合索引時(shí)，必須將區(qū)分度更高的字段放在左邊

解讀：區(qū)分度更高的列放在左邊，能夠在一開始就有效的過濾掉無用數(shù)據(jù)。提高索引的效率，相應(yīng)我們?cè)?Mapper 中編寫 SQL 的 WHERE 條件中有多個(gè)條件時(shí)，需要先看看當(dāng)前表是否有現(xiàn)成的聯(lián)合索引直接使用，注意各個(gè)條件的順序盡量和索引的順序一致。 【建議】：⑤利用覆蓋索引來進(jìn)行查詢操作，避免回表

解讀：覆蓋查詢即是查詢只需要通過索引即可拿到所需 DATA，而不再需要再次回表查詢，所以效率相對(duì)很高。

我們?cè)谑褂?EXPLAIN 的結(jié)果，extra 列會(huì)出現(xiàn)："using index"。這里也要強(qiáng)調(diào)一下不要使用“SELECT * ”，否則幾乎不可能使用到覆蓋索引。

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

解讀：索引的長度與區(qū)分度是一對(duì)矛盾體，一般對(duì)字符串類型數(shù)據(jù)，若長度為 20 的索引，區(qū)分度會(huì)高達(dá) 90% 以上，則可以考慮創(chuàng)建長度例為 20 的索引，而非全字段索引。

例如可以使用 SELECT COUNT(DISTINCT LEFT(lesson_code, 20))/COUNT(*) FROM lesson；來確定 lesson_code 字段字符長度為 20 時(shí)文本區(qū)分度。

【建議】：⑦如果有 ORDER BY 的場景，請(qǐng)注意利用索引的有序性

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

解讀：

假設(shè)有查詢條件為 WHERE a=? and b=? ORDER BY c；存在索引：a_b_c，則此時(shí)可以利用索引排序。

反例：在查詢條件中包含了范圍查詢，那么索引有序性無法利用，如：WHERE a>10 ORDER BY b；索引 a_b 無法排序。

【建議】：⑧在 Where 中索引的列不能某個(gè)表達(dá)式的一部分，也不能是函數(shù)的參數(shù)

解讀：即是某列上已經(jīng)添加了索引，但是若此列成為表達(dá)式的一部分、或者是函數(shù)的參數(shù)，MySQL 無法將此列單獨(dú)解析出來，索引也不會(huì)生效。 【建議】：⑨我們?cè)?Where 條件中使用范圍查詢時(shí)，索引最多用于一個(gè)范圍條件，超過一個(gè)則后邊的不走索引

解讀：MySQL 能夠使用多個(gè)范圍條件里邊的最左邊的第一個(gè)范圍查詢，但是后邊的范圍查詢則無法使用。 【建議】：⑩在多個(gè)表進(jìn)行外連接時(shí)，表之間的關(guān)聯(lián)字段類型必須完全一致

解讀：當(dāng)兩個(gè)表進(jìn)行 Join 時(shí)，字段類型若沒有完全一致，則加索引也不會(huì)生效，這里的完全一致包括但不限于字段類型、字段長度、字符集、Collection 等等。

參考資料：

《High.Performance.MySQL.3rd.Edition》

《阿里巴巴java開發(fā)手冊(cè)》

責(zé)任編輯：xj

原文標(biāo)題：CTO 要我把這份 MySQL 規(guī)范貼在工位上！

文章出處：【微信公眾號(hào)：數(shù)據(jù)分析與開發(fā)】歡迎添加關(guān)注！文章轉(zhuǎn)載請(qǐng)注明出處。