一、基礎(chǔ)規(guī)范

使用InnoDB存儲(chǔ)引擎
支持事務(wù)、行級(jí)鎖、并發(fā)性能更好、CPU及內(nèi)存緩存頁(yè)優(yōu)化使得資源利用率更高

推薦使用utf8mb4字符集
無需轉(zhuǎn)碼，無亂碼風(fēng)險(xiǎn), 支持emoji表情以及部分不常見漢字

表、字段必須加注釋
方便他人理解字段意思，在后期維護(hù)中非常非常有用，不用去瞎猜這個(gè)字段是干嘛的。

不在數(shù)據(jù)庫(kù)做計(jì)算
禁止使用存儲(chǔ)過程、視圖、觸發(fā)器、Event。
在并發(fā)量大的情況下，這些功能很可能將數(shù)據(jù)庫(kù)拖跨，業(yè)務(wù)邏輯放到服務(wù)層具備更好的擴(kuò)展性，能夠輕易實(shí)現(xiàn)“增機(jī)器就加性能”

禁止存儲(chǔ)文件
文件存儲(chǔ)在文件系統(tǒng)，數(shù)據(jù)庫(kù)里存URI

控制單表數(shù)據(jù)量
單表記錄控制在千萬(wàn)級(jí)

二、命名規(guī)范

庫(kù)名、表名、字段名：小寫，下劃線風(fēng)格
非唯一索引名idxxxx，唯一索引名uniqxxx

表必須有主鍵，例如自增主鍵
a）主鍵遞增，數(shù)據(jù)行寫入可以提高插入性能
b）主鍵要選擇較短的數(shù)據(jù)類型，Innodb引擎普通索引都會(huì)保存主鍵的值，較短的數(shù)據(jù)類型可以有效的減少索引的磁盤空間，提高索引的緩存效率
c）保證實(shí)體的完整性，唯一性

不要使用外鍵，如果有外鍵約束，用應(yīng)用程序控制
外鍵會(huì)導(dǎo)致表與表之間耦合，update與delete操作都會(huì)涉及相關(guān)聯(lián)的表，十分影響sql 的性能，甚至?xí)斐伤梨i。高并發(fā)情況下容易造成數(shù)據(jù)庫(kù)性能下降，大數(shù)據(jù)高并發(fā)業(yè)務(wù)場(chǎng)景數(shù)據(jù)庫(kù)使用以性能優(yōu)先

三、字段設(shè)計(jì)規(guī)范

把字段定義為NOT NULL并且提供默認(rèn)值
a）null的列使索引/索引統(tǒng)計(jì)/值比較都更加復(fù)雜，對(duì)MySQL來說更難優(yōu)化
b）null 這種類型MySQL內(nèi)部需要進(jìn)行特殊處理，增加數(shù)據(jù)庫(kù)處理記錄的復(fù)雜性；同等條件下，表中有較多空字段的時(shí)候，數(shù)據(jù)庫(kù)的處理性能會(huì)降低很多
c）null值需要更多的存儲(chǔ)空間，無論是表還是索引中每行中的null的列都需要額外的空間來標(biāo)識(shí)
d）對(duì)null 的處理時(shí)候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in這些操作符號(hào)。如：where name!=’zhangsan’，如果存在name為null值的記錄，查詢結(jié)果就不會(huì)包含name為null值的記錄

不要使用TEXT、BLOB類型
會(huì)浪費(fèi)更多的磁盤和內(nèi)存空間，非必要的大量的大字段查詢會(huì)淘汰掉熱數(shù)據(jù)，導(dǎo)致內(nèi)存命中率急劇降低，影響數(shù)據(jù)庫(kù)性能,如果必須要使用則獨(dú)立出來一張表，用主鍵來對(duì)應(yīng)，避免影響其它字段索引效率

不要使用小數(shù)存儲(chǔ)貨幣
建議使用整數(shù)，小數(shù)容易導(dǎo)致錢對(duì)不上

必須使用varchar存儲(chǔ)手機(jī)號(hào)
手機(jī)號(hào)會(huì)去做數(shù)學(xué)運(yùn)算么？

為提高效率可以犧牲范式設(shè)計(jì)，冗余數(shù)據(jù)
a)不是頻繁修改的字段
b)不是 varchar 超長(zhǎng)字段，更不能是 text 字段

四、索引設(shè)計(jì)規(guī)范

禁止在更新十分頻繁、區(qū)分度不高的屬性上建立索引
a）更新會(huì)變更B+樹，更新頻繁的字段建立索引會(huì)大大降低數(shù)據(jù)庫(kù)性能
b）“性別”這種區(qū)分度不大的屬性，建立索引是沒有什么意義的

建立組合索引，必須把區(qū)分度高的字段放在最左邊
因?yàn)槭褂脝巫侄尾樵儠r(shí)會(huì)使用組合索引的左邊字段而不使用右邊的字段，如果 where a=? and b=? ， a 列的幾乎接近于唯一值，那么只需要單建 idx_a 索引即可

頁(yè)面搜索嚴(yán)禁左模糊或者全模糊
索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引, 如果需要請(qǐng)走搜索引擎來解決

五、SQL使用規(guī)范

禁止使用SELECT *，只獲取必要的字段，需要顯示說明列屬性
a）消耗cpu，io，內(nèi)存，帶寬
b）不能有效的利用覆蓋索引
c）使用SELECT *容易在增加或者刪除字段后出現(xiàn)程序BUG, 不具有擴(kuò)展性

使用INSERT INTO t_xxx VALUES(xxx)，必須顯示指定插入的列屬性
容易在增加或者刪除字段后出現(xiàn)程序BUG

務(wù)必請(qǐng)使用“同類型”進(jìn)行比較，否則可能全表掃面
SELECT name FROM t_user WHERE phone=1888888888 會(huì)導(dǎo)致全表掃描.

禁止在WHERE條件的上使用函數(shù)或者計(jì)算
解讀：SELECT naem FROM tuser WHERE date(createdatatime)='2017-12-29' 會(huì)導(dǎo)致全表掃描
推薦的寫法是：SELECT name FROM tuser WHERE createdatatime>= '2017-12-29' and create_datatime < '2017-12-30'

禁止負(fù)向查詢，以及%開頭的模糊查詢
a）負(fù)向查詢條件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，會(huì)導(dǎo)致全表掃描
b）%開頭的模糊查詢，會(huì)導(dǎo)致全表掃描

不要大表使用JOIN查詢，禁止大表使用子查詢
會(huì)產(chǎn)生臨時(shí)表，消耗較多內(nèi)存與CPU，極大影響數(shù)據(jù)庫(kù)性能

OR改寫為IN()或者UNION
原因很簡(jiǎn)單or不會(huì)走索引

簡(jiǎn)單的事務(wù)
事務(wù)就像程序中的鎖一樣粒度盡可能要小

不要一次更新大量數(shù)據(jù)
數(shù)據(jù)更新會(huì)對(duì)行或者表加鎖，應(yīng)該分為多次更新

總結(jié)

以上是生活随笔為你收集整理的MySQL优化之推荐使用规范的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。