您好,登錄后才能下訂單哦!
這篇文章將為大家詳細(xì)講解有關(guān)MySQL的規(guī)范是怎樣的,文章內(nèi)容質(zhì)量較高,因此小編分享給大家做個(gè)參考,希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。
下邊分為建表規(guī)約、SQL 規(guī)約、索引規(guī)約三個(gè)部分,每部分的每一條都有強(qiáng)制、建議兩個(gè)級別,大家在參考時(shí),根據(jù)自己公司的情況來權(quán)衡。
建表規(guī)約
【強(qiáng)制】:①存儲引擎必須使用 InnoDB
**解讀:**InnoDB 支持事物、行級鎖、并發(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)用程序控制
**解讀:**外鍵會導(dǎo)致表與表之間耦合,UPDATE 與 DELETE 操作都會涉及相關(guān)聯(lián)的表,十分影響 SQL 的性能,甚至?xí)斐伤梨i。
【強(qiáng)制】:⑧必須把字段定義為 NOT NULL 并且提供默認(rèn)值
解讀:
【強(qiáng)制】:⑨禁用保留字,如 DESC、RANGE、MARCH 等
**解讀:**請參考 MySQL 官方保留字。
【強(qiáng)制】:⑩如果存儲的字符串長度幾乎相等,使用 CHAR 定長字符串類型
解讀:能夠減少空間碎片,節(jié)省存儲空間。
【建議】:?在一些場景下,考慮使用 TIMESTAMP 代替 DATETIME
解讀:
【建議】:?當(dāng)心自動(dòng)生成的 Schema,建議所有的 Schema 手動(dòng)編寫
**解讀:**對于一些數(shù)據(jù)庫客戶端不要太過信任。
SQL 規(guī)約
【建議】:①為了充分利用緩存,不允許使用自定義函數(shù)、存儲函數(shù)、用戶變量
解讀:如果查詢中包含任何用戶自定義函數(shù)、存儲函數(shù)、用戶變量、臨時(shí)表、MySQL 庫中的系統(tǒng)表,其查詢結(jié)果都不會被緩存。
比如函數(shù) NOW() 或者 CURRENT_DATE() 會因?yàn)椴煌牟樵儠r(shí)間,返回不同的查詢結(jié)果。
【強(qiáng)制】:②在查詢中指定所需的列,而不是直接使用“ *”返回所有的列
解讀:
【強(qiáng)制】:③不允許使用屬性隱式轉(zhuǎn)換
**解讀:**假設(shè)我們在手機(jī)號列上添加了索引,然后執(zhí)行下面的 SQL 會發(fā)生什么?
explain SELECT user_name FROM parent WHERE phone=13812345678;很明顯就是索引不生效,會全表掃描。
【建議】:④在 WHERE 條件的屬性上使用函數(shù)或者表達(dá)式
**解讀:**MySQL 無法自動(dòng)解析這種表達(dá)式,無法使用到索引。
**【強(qiáng)制】:**⑤禁止使用外鍵與級聯(lián),一切外鍵概念必須在應(yīng)用層解決
**解讀:**外鍵與級聯(lián)更新適用于單機(jī)低并發(fā),不適合分布式、高并發(fā)集群;級聯(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)化效果很小,但是較為頻繁的更新則會讓索引的維護(hù)成本更高。
【強(qiáng)制】:②JOIN 的表不允許超過五個(gè)。需要 JOIN 的字段,數(shù)據(jù)類型必須絕對一致; 多表關(guān)聯(lián)查詢時(shí),保證被關(guān)聯(lián)的字段需要有索引
**解讀:**太多表的 JOIN 會讓 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)我們在 Mapper 中編寫 SQL 的 WHERE 條件中有多個(gè)條件時(shí),需要先看看當(dāng)前表是否有現(xiàn)成的聯(lián)合索引直接使用,注意各個(gè)條件的順序盡量和索引的順序一致。
【建議】:⑤利用覆蓋索引來進(jìn)行查詢操作,避免回表
**解讀:**覆蓋查詢即是查詢只需要通過索引即可拿到所需 DATA,而不再需要再次回表查詢,所以效率相對很高。
我們在使用 EXPLAIN 的結(jié)果,extra 列會出現(xiàn):”using index”。這里也要強(qiáng)調(diào)一下不要使用“SELECT * ”,否則幾乎不可能使用到覆蓋索引。
【建議】:⑥在較長 VARCHAR 字段,例如 VARCHAR(100) 上建立索引時(shí),應(yīng)指定索引長度,沒必要對全字段建立索引,根據(jù)實(shí)際文本區(qū)分度決定索引長度即可
**解讀:**索引的長度與區(qū)分度是一對矛盾體,一般對字符串類型數(shù)據(jù),若長度為 20 的索引,區(qū)分度會高達(dá) 90% 以上,則可以考慮創(chuàng)建長度例為 20 的索引,而非全字段索引。
例如可以使用 SELECT COUNT(DISTINCT LEFT(lesson_code, 20))/COUNT(*) FROM lesson;來確定 lesson_code 字段字符長度為 20 時(shí)文本區(qū)分度。
【建議】:⑦如果有 ORDER BY 的場景,請注意利用索引的有序性
ORDER BY 最后的字段是聯(lián)合索引的一部分,并且放在索引組合順序的最后,避免出現(xiàn) file_sort 的情況,影響查詢性能。
解讀:
【建議】:⑧在 Where 中索引的列不能某個(gè)表達(dá)式的一部分,也不能是函數(shù)的參數(shù)
**解讀:**即是某列上已經(jīng)添加了索引,但是若此列成為表達(dá)式的一部分、或者是函數(shù)的參數(shù),MySQL 無法將此列單獨(dú)解析出來,索引也不會生效。
【建議】:⑨我們在 Where 條件中使用范圍查詢時(shí),索引最多用于一個(gè)范圍條件,超過一個(gè)則后邊的不走索引
**解讀:**MySQL 能夠使用多個(gè)范圍條件里邊的最左邊的第一個(gè)范圍查詢,但是后邊的范圍查詢則無法使用。
【建議】:⑩在多個(gè)表進(jìn)行外連接時(shí),表之間的關(guān)聯(lián)字段類型必須完全一致
**解讀:**當(dāng)兩個(gè)表進(jìn)行 Join 時(shí),字段類型若沒有完全一致,則加索引也不會生效,這里的完全一致包括但不限于字段類型、字段長度、字符集、Collection 等等。
關(guān)于MySQL的規(guī)范是怎樣的就分享到這里了,希望以上內(nèi)容可以對大家有一定的幫助,可以學(xué)到更多知識。如果覺得文章不錯(cuò),可以把它分享出去讓更多的人看到。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。