mysql數(shù)據(jù)庫開發(fā)規(guī)范【推薦】

最近一段時間一邊在線上抓取SQL來優(yōu)化，一邊在整理這個開發(fā)規(guī)范，盡量減少新的問題SQL進(jìn)入生產(chǎn)庫。今天也是對公司的開發(fā)做了一次培訓(xùn)，PPT就不放上來了，里面有十來個生產(chǎn)SQL的案例。因為規(guī)范大部分還是具有通用性，所以也借鑒了像去哪兒和趕集的規(guī)范，但實際在撰寫本文的過程中，每一條規(guī)范的背后無不是在工作中有參照的反面例子的。如果時間可以的話，會抽出一部分或分析其原理，或用案例證明。

一. 命名規(guī)范

1.庫名、表名、字段名必須使用小寫字母，并采用下劃線分割

(1)MySQL有配置參數(shù)lower_case_table_names=1，即庫表名以小寫存儲，大小寫不敏感。如果是0，則庫表名以實際情況存儲，大小寫敏感；如果是2，以實際情況存儲，但以小寫比較。

(2)如果大小寫混合使用，可能存在abc，Abc，ABC等多個表共存，容易導(dǎo)致混亂。

(3)字段名顯示區(qū)分大小寫，但實際使⽤時不區(qū)分，即不可以建立兩個名字一樣但大小寫不一樣的字段。

(4)為了統(tǒng)一規(guī)范， 庫名、表名、字段名使用小寫字母。

2.庫名以 d 開頭，表名以 t 開頭，字段名以 f_ 開頭

(1)比如表 t_crm_relation，中間的 crm 代表業(yè)務(wù)模塊名

(2)視圖以view_開頭，事件以event_開頭，觸發(fā)器以trig_開頭，存儲過程以proc_開頭，函數(shù)以func_開頭

(3)普通索引以idx_col1_col2命名，唯一索引以uk_col1_col2命名（可去掉f_公共部分）。如 idx_companyid_corpid_contacttime(f_company_id,f_corp_id,f_contact_time)

3.庫名、表名、字段名禁止超過32個字符，需見名知意

庫名、表名、字段名支持最多64個字符，但為了統(tǒng)一規(guī)范、易于辨識以及減少傳輸量，禁止超過32個字符

4.臨時庫、表名須以tmp加日期為后綴

如 t_crm_relation_tmp0425。備份表也類似，形如 _bak20160425 。

5.按日期時間分表須符合_YYYY[MM][DD]格式

這也是為將來有可能分表做準(zhǔn)備的，比如t_crm_ec_record_201403，但像 t_crm_contact_at201506就打破了這種規(guī)范。
不具有時間特性的，直接以 t_tbname_001 這樣的方式命名。

二. 庫表基礎(chǔ)規(guī)范

1.使用Innodb存儲引擎

5.5版本開始mysql默認(rèn)存儲引擎就是InnoDB，5.7版本開始，系統(tǒng)表都放棄MyISAM了。

2.表字符集統(tǒng)一使用UTF8

(1)UTF8字符集存儲漢字占用3個字節(jié)，存儲英文字符占用一個字節(jié)

(2)校對字符集使用默認(rèn)的 utf8_general_ci

(3)連接的客戶端也使用utf8，建立連接時指定charset或SET NAMES UTF8;。（對于已經(jīng)在項目中長期使用latin1的，救不了了）

(4)如果遇到EMOJ等表情符號的存儲需求，可申請使用UTF8MB4字符集

3.所有表都要添加注釋

(1)盡量給字段也添加注釋

(2)類status型需指明主要值的含義，如”0-離線，1-在線”

4.控制單表字段數(shù)量

(1)單表字段數(shù)上限30左右，再多的話考慮垂直分表，一是冷熱數(shù)據(jù)分離，二是大字段分離，三是常在一起做條件和返回列的不分離。

(2)表字段控制少而精，可以提高IO效率，內(nèi)存緩存更多有效數(shù)據(jù)，從而提高響應(yīng)速度和并發(fā)能力，后續(xù) alter table 也更快。

5.所有表都必須要顯式指定主鍵

(1)主鍵盡量采用自增方式，InnoDB表實際是一棵索引組織表，順序存儲可以提高存取效率，充分利用磁盤空間。還有對一些復(fù)雜查詢可能需要自連接來優(yōu)化時需要用到。

(2)需要全局唯一主鍵時，使用外部發(fā)號器ticket server（建設(shè)中）

(3)如果沒有主鍵或唯一索引，update/delete是通過所有字段來定位操作的行，相當(dāng)于每行就是一次全表掃描

(4)少數(shù)情況可以使用聯(lián)合唯一主鍵，需與DBA協(xié)商

6.不強(qiáng)制使用外鍵參考

即使2個表的字段有明確的外鍵參考關(guān)系，也不使用 FOREIGN KEY ，因為新紀(jì)錄會去主鍵表做校驗，影響性能。

7.適度使用存儲過程、視圖，禁止使用觸發(fā)器、事件

(1)存儲過程（procedure）雖然可以簡化業(yè)務(wù)端代碼，在傳統(tǒng)企業(yè)寫復(fù)雜邏輯時可能會用到，而在互聯(lián)網(wǎng)企業(yè)變更是很頻繁的，在分庫分表的情況下要升級一個存儲過程相當(dāng)麻煩。又因為它是不記錄log的，所以也不方便debug性能問題。如果使用過程，一定考慮如果執(zhí)行失敗的情況。

(2)使用視圖一定程度上也是為了降低代碼里SQL的復(fù)雜度，但有時候為了視圖的通用性會損失性能（比如返回不必要的字段）。

(3)觸發(fā)器（trigger）也是同樣，但也不應(yīng)該通過它去約束數(shù)據(jù)的強(qiáng)一致性，mysql只支持“基于行的觸發(fā)”，也就是說，觸發(fā)器始終是針對一條記錄的，而不是針對整個sql語句的，如果變更的數(shù)據(jù)集非常大的話，效率會很低。掩蓋一條sql背后的工作，一旦出現(xiàn)問題將是災(zāi)難性的，但又很難快速分析和定位。再者需要ddl時無法使用pt-osc工具。放在transaction執(zhí)行。

(4)事件（event）也是一種偷懶的表現(xiàn)，目前已經(jīng)遇到數(shù)次由于定時任務(wù)執(zhí)行失敗影響業(yè)務(wù)的情況，而且mysql無法對它做失敗預(yù)警。建立專門的 job scheduler 平臺。

a.單表數(shù)據(jù)量控制在5000w以內(nèi)

b.數(shù)據(jù)庫中不允許存儲明文密碼

三. 字段規(guī)范

1.char、varchar、text等字符串類型定義

(1)對于長度基本固定的列，如果該列恰好更新又特別頻繁，適合char

(2)varchar雖然存儲變長字符串，但不可太小也不可太大。UTF8最多能存21844個漢字，或65532個英文

(3)varbinary(M)保存的是二進(jìn)制字符串，它保存的是字節(jié)而不是字符，所以沒有字符集的概念，M長度0-255（字節(jié)）。只用于排序或比較時大小寫敏感的類型，不包括密碼存儲

(4)TEXT類型與VARCHAR都類似，存儲可變長度，最大限制也是2^16，但是它20bytes以后的內(nèi)容是在數(shù)據(jù)頁以外的空間存儲（row_format=dynamic），對它的使用需要多一次尋址，沒有默認(rèn)值。

一般用于存放容量平均都很大、操作沒有其它字段那樣頻繁的值。

網(wǎng)上部分文章說要避免使用text和blob，要知道如果純用varchar可能會導(dǎo)致行溢出，效果差不多，但因為每行占用字節(jié)數(shù)過多，會導(dǎo)致buffer_pool能緩存的數(shù)據(jù)行、頁下降。另外text和blob上面一般不會去建索引，而是利用sphinx之類的第三方全文搜索引擎，如果確實要創(chuàng)建（前綴）索引，那就會影響性能。凡事看具體場景。

另外盡可能把text/blob拆到另一個表中

(5)BLOB可以看出varbinary的擴(kuò)展版本，內(nèi)容以二進(jìn)制字符串存儲，無字符集，區(qū)分大小寫，有一種經(jīng)常提但不用的場景：不要在數(shù)據(jù)庫里存儲圖片。

2.int、tinyint、decimal等數(shù)字類型定義

(1)使用tinyint來代替 enum和boolean
ENUM類型在需要修改或增加枚舉值時，需要在線DDL，成本較高；ENUM列值如果含有數(shù)字類型，可能會引起默認(rèn)值混淆
tinyint使用1個字節(jié)，一般用于status,type,flag的列

(2)建議使用 UNSIGNED 存儲非負(fù)數(shù)值
相比不使用 unsigned，可以擴(kuò)大一倍使用數(shù)值范圍

(3)int使用固定4個字節(jié)存儲，int(11)與int(4)只是顯示寬度的區(qū)別

(4)使用Decimal 代替float/double存儲精確浮點數(shù)
對于貨幣、金額這樣的類型，使用decimal，如 decimal(9,2)。float默認(rèn)只能能精確到6位有效數(shù)字

3.timestamp與datetime選擇

(1)datetime 和 timestamp類型所占的存儲空間不同，前者8個字節(jié)，后者4個字節(jié)，這樣造成的后果是兩者能表示的時間范圍不同。前者范圍為1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，后者范圍為 1970-01-01 08:00:01 到 2038-01-19 11:14:07 。所以 TIMESTAMP 支持的范圍比 DATATIME 要小。

(2)timestamp可以在insert/update行時，自動更新時間字段（如 f_set_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP），但一個表只能有一個這樣的定義。

(3)timestamp顯示與時區(qū)有關(guān)，內(nèi)部總是以 UTC 毫秒 來存的。還受到嚴(yán)格模式的限制

(4)優(yōu)先使用timestamp，datetime也沒問題

(5)where條件里不要對時間列上使用時間函數(shù)

4.建議字段都定義為NOT NULL

(1)如果是索引字段，一定要定義為not null 。因為null值會影響cordinate統(tǒng)計，影響優(yōu)化器對索引的選擇

(2)如果不能保證insert時一定有值過來，定義時使用default ‘' ，或 0

5.同一意義的字段定義必須相同

比如不同表中都有 f_user_id 字段，那么它的類型、字段長度要設(shè)計成一樣

四. 索引規(guī)范

1.任何新的select,update,delete上線，都要先explain，看索引使用情況

盡量避免extra列出現(xiàn)：Using File Sort，Using Temporary，rows超過1000的要謹(jǐn)慎上線。
explain解讀

(1)type：ALL, index, range, ref, eq_ref, const, system, NULL（從左到右，性能從差到好）

(2)possible_keys：指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用

(3)key：表示MySQL實際決定使用的鍵（索引）
如果沒有選擇索引，鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX

(4)ref：表示選擇 key 列上的索引，哪些列或常量被用于查找索引列上的值

(5)rows：根據(jù)表統(tǒng)計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)

(6)Extra

a.Using temporary：表示MySQL需要使用臨時表來存儲結(jié)果集，常見于排序和分組查詢
b.Using filesort：MySQL中無法利用索引完成的排序操作稱為“文件排序”

1.索引個數(shù)限制

(1)索引是雙刃劍，會增加維護(hù)負(fù)擔(dān)，增大IO壓力，索引占用空間是成倍增加的

(2)單張表的索引數(shù)量控制在5個以內(nèi)，或不超過表字段個數(shù)的20%。若單張表多個字段在查詢需求上都要單獨用到索引，需要經(jīng)過DBA評估。

2.避免冗余索引

(1.)InnoDB表是一棵索引組織表，主鍵是和數(shù)據(jù)放在一起的聚集索引，普通索引最終指向的是主鍵地址，所以把主鍵做最后一列是多余的。如f_crm_id作為主鍵，聯(lián)合索引(f_user_id,f_crm_id)上的f_crm_id就完全多余

(2)(a,b,c)、(a,b)，后者為冗余索引?？梢岳们熬Y索引來達(dá)到加速目的，減輕維護(hù)負(fù)擔(dān)

3.沒有特殊要求，使用自增id作為主鍵

(1.)主鍵是一種聚集索引，順序?qū)懭?。組合唯一索引作為主鍵的話，是隨機(jī)寫入，適合寫少讀多的表

(2)主鍵不允許更新

4.索引盡量建在選擇性高的列上

(1)不在低基數(shù)列上建立索引，例如性別、類型。但有一種情況，idx_feedbackid_type (f_feedback_id,f_type)，如果經(jīng)常用 f_type=1 比較，而且能過濾掉90%行，那這個組合索引就值得創(chuàng)建。有時候同樣的查詢語句，由于條件取值不同導(dǎo)致使用不同的索引，也是這個道理。

(2)索引選擇性計算方法（基數(shù) ÷ 數(shù)據(jù)行數(shù)）

Selectivity = Cardinality / Total Rows = select count(distinct col1)/count(*) from tbname，越接近1說明col1上使用索引的過濾效果越好

(3)走索引掃描行數(shù)超過30%時，改全表掃描

5.最左前綴原則

(1)mysql使用聯(lián)合索引時，從左向右匹配，遇到斷開或者范圍查詢時，無法用到后續(xù)的索引列
比如索引idx_c1_c2_c3 (c1,c2,c3)，相當(dāng)于創(chuàng)建了(c1)、(c1,c2)、(c1,c2,c3)三個索引，where條件包含上面三種情況的字段比較則可以用到索引，但像 where c1=a and c3=c 只能用到c1列的索引，像 c2=b and c3=c等情況就完全用不到這個索引

(2)遇到范圍查詢(>、、between、like)也會停止索引匹配，比如 c1=a and c2 > 2 and c3=c，只有c1,c2列上的比較能用到索引，(c1,c2,c3)排列的索引才可能會都用上

(3)where條件里面字段的順序與索引順序無關(guān)，mysql優(yōu)化器會自動調(diào)整順序

6.前綴索引

(1)對超過30個字符長度的列創(chuàng)建索引時，考慮使用前綴索引，如 idx_cs_guid2 (f_cs_guid(26))表示截取前26個字符做索引，既可以提高查找效率，也可以節(jié)省空間

(2)前綴索引也有它的缺點是，如果在該列上 ORDER BY 或 GROUP BY 時無法使用索引，也不能把它們用作覆蓋索引(Covering Index)

(3)如果在varbinary或blob這種以二進(jìn)制存儲的列上建立前綴索引，要考慮字符集，括號里表示的是字節(jié)數(shù)

7.合理使用覆蓋索引減少IO

INNODB存儲引擎中，secondary index(非主鍵索引，又稱為輔助索引、二級索引)沒有直接存儲行地址，而是存儲主鍵值。
如果用戶需要查詢secondary index中所不包含的數(shù)據(jù)列，則需要先通過secondary index查找到主鍵值，然后再通過主鍵查詢到其他數(shù)據(jù)列，因此需要查詢兩次。覆蓋索引則可以在一個索引中獲取所有需要的數(shù)據(jù)列，從而避免回表進(jìn)行二次查找，節(jié)省IO因此效率較高。

例如SELECT email，uid FROM user_email WHERE uid=xx，如果uid不是主鍵，適當(dāng)時候可以將索引添加為index(uid，email)，以獲得性能提升。

8.盡量不要在頻繁更新的列上創(chuàng)建索引

如不在定義了 ON UPDATE CURRENT_STAMP 的列上創(chuàng)建索引，維護(hù)成本太高（好在mysql有insert buffer，會合并索引的插入）

五. SQL設(shè)計

1.杜絕直接 SELECT * 讀取全部字段

即使需要所有字段，減少網(wǎng)絡(luò)帶寬消耗，能有效利用覆蓋索引，表結(jié)構(gòu)變更對程序基本無影響

2.能確定返回結(jié)果只有一條時，使用 limit 1

在保證數(shù)據(jù)不會有誤的前提下，能確定結(jié)果集數(shù)量時，多使用limit，盡快的返回結(jié)果。

3.小心隱式類型轉(zhuǎn)換

(1)轉(zhuǎn)換規(guī)則

a. 兩個參數(shù)至少有一個是 NULL 時，比較的結(jié)果也是 NULL，例外是使用 => 對兩個 NULL 做比較時會返回 1，這兩種情況都不需要做類型轉(zhuǎn)換

b. 兩個參數(shù)都是字符串，會按照字符串來比較，不做類型轉(zhuǎn)換

c. 兩個參數(shù)都是整數(shù)，按照整數(shù)來比較，不做類型轉(zhuǎn)換

d. 十六進(jìn)制的值和非數(shù)字做比較時，會被當(dāng)做二進(jìn)制串

e. 有一個參數(shù)是 TIMESTAMP 或 DATETIME，并且另外一個參數(shù)是常量，常量會被轉(zhuǎn)換為 timestamp

f. 有一個參數(shù)是 decimal 類型，如果另外一個參數(shù)是 decimal 或者整數(shù)，會將整數(shù)轉(zhuǎn)換為 decimal 后進(jìn)行比較，如果另外一個參數(shù)是浮點數(shù)，則會把 decimal 轉(zhuǎn)換為浮點數(shù)進(jìn)行比較

g. 所有其他情況下，兩個參數(shù)都會被轉(zhuǎn)換為浮點數(shù)再進(jìn)行比較。

(2)如果一個索引建立在string類型上，如果這個字段和一個int類型的值比較，符合第 g 條。如f_phone定義的類型是varchar，但where使用f_phone in (098890)，兩個參數(shù)都會被當(dāng)成成浮點型。發(fā)生這個隱式轉(zhuǎn)換并不是最糟的，最糟的是string轉(zhuǎn)換后的float，mysql無法使用索引，這才導(dǎo)致了性能問題。如果是 f_user_id = ‘1234567' 的情況，符合第 b 條,直接把數(shù)字當(dāng)字符串比較。

4.禁止在where條件列上使用函數(shù)

(1)會導(dǎo)致索引失效，如lower(email)，f_qq % 4?？煞诺接疫叺某Ａ可嫌嬎?br />

(2)返回小結(jié)果集不是很大的情況下，可以對返回列使用函數(shù)，簡化程序開發(fā)

5.使用like模糊匹配，%不要放首位

會導(dǎo)致索引失效，有這種搜索需求是，考慮其它方案，如sphinx全文搜索

6.涉及到復(fù)雜sql時，務(wù)必先參考已有索引設(shè)計，先explain

(1)簡單SQL拆分，不以代碼處理復(fù)雜為由。

(2)比如 OR 條件： f_phone='10000' or f_mobile='10000'，兩個字段各自有索引，但只能用到其中一個?？梢圆鸱殖?個sql，或者union all。

(3)先explain的好處是可以為了利用索引，增加更多查詢限制條件

7.使用join時，where條件盡量使用充分利用同一表上的索引

(1)如 select t1.a,t2.b * from t1,t2 and t1.a=t2.a and t1.b=123 and t2.c= 4 ，如果t1.c與t2.c字段相同，那么t1上的索引(b,c)就只用到b了。此時如果把where條件中的t2.c=4改成t1.c=4，那么可以用到完整的索引

(2)這種情況可能會在字段冗余設(shè)計（反范式）時出現(xiàn)

(3)正確選取inner join和left join

8.少用子查詢，改用join

小于5.6版本時，子查詢效率很低，不像Oracle那樣先計算子查詢后外層查詢。5.6版本開始得到優(yōu)化

9.考慮使用union all，少使用union，注意考慮去重

(1)union all不去重，而少了排序操作，速度相對比union要快，如果沒有去重的需求，優(yōu)先使用union all

(2)如果UNION結(jié)果中有使用limit，在2個子SQL可能有許多返回值的情況下，各自加上limit。如果還有order by，請找DBA。

10.IN的內(nèi)容盡量不超過200個

超過500個值使用批量的方式，否則一次執(zhí)行會影響數(shù)據(jù)庫的并發(fā)能力，因為單SQL只能且一直占用單CPU，而且可能導(dǎo)致主從復(fù)制延遲

11.拒絕大事務(wù)

比如在一個事務(wù)里進(jìn)行多個select，多個update，如果是高頻事務(wù)，會嚴(yán)重影響MySQL并發(fā)能力，因為事務(wù)持有的鎖等資源只在事務(wù)rollback/commit時才能釋放。但同時也要權(quán)衡數(shù)據(jù)寫入的一致性。

12.避免使用is null, is not null這樣的比較

13.order by .. limit

這種查詢更多的是通過索引去優(yōu)化，但order by的字段有講究，比如主鍵id與f_time都是順序遞增，那就可以考慮order by id而非 f_time 。

14.c1 a order by c2

與上面不同的是，order by之前有個范圍查詢，由前面的內(nèi)容可知，用不到類似(c1,c2)的索引，但是可以利用(c2,c1)索引。另外還可以改寫成join的方式實現(xiàn)。

15.分頁優(yōu)化

建議使用合理的分頁方式以提高分頁效率，大頁情況下不使用跳躍式分頁

假如有類似下面分頁語句:

SELECT FROM table1 ORDER BY ftime DESC LIMIT 10000,10;

這種分頁方式會導(dǎo)致大量的io，因為MySQL使用的是提前讀取策略。

推薦分頁方式：

SELECT FROM table1 WHERE ftime last_time ORDER BY ftime DESC LIMIT 10

即傳入上一次分頁的界值

SELECT * FROM table as t1 inner JOIN (SELECT id FROM table ORDER BY time LIMIT 10000，10) as t2 ON t1.id=t2.id

16.count計數(shù)

(1)首先count()、count(1)、count(col1)是有區(qū)別的，count()表示整個結(jié)果集有多少條記錄，count(1)表示結(jié)果集里以primary key統(tǒng)計數(shù)量，絕大多數(shù)情況下count()與count(1)效果一樣的，但count(col1)表示的是結(jié)果集里 col1 列 NOT null 的記錄數(shù)。優(yōu)先采用count()

(2)大數(shù)據(jù)量count是消耗資源的操作，甚至?xí)下麄€庫，查詢性能問題無法解決的，應(yīng)從產(chǎn)品設(shè)計上進(jìn)行重構(gòu)。例如當(dāng)頻繁需要count的查詢，考慮使用匯總表

(3)遇到distinct的情況，group by方式可能效率更高。

17.delete,update語句改成select再explain

select最多導(dǎo)致數(shù)據(jù)庫慢，寫操作才是鎖表的罪魁禍?zhǔn)?br />

18.減少與數(shù)據(jù)庫交互的次數(shù)，盡量采用批量SQL語句

(1)INSERT ... ON DUPLICATE KEY UPDATE ...，插入行后會導(dǎo)致在一個UNIQUE索引或PRIMARY KEY中出現(xiàn)重復(fù)值，則執(zhí)行舊行UPDATE，如果不重復(fù)則直接插入，影響1行。

(2)REPLACE INTO類似，但它是沖突時刪除舊行。INSERT IGNORE相反，保留舊行，丟棄要插入的新行。

(3)INSERT INTO VALUES(),(),()，合并插入。

19.杜絕危險SQL

(1)去掉where 1=1 這樣無意義或恒真的條件，如果遇到update/delete或遭到sql注入就恐怖了

(2)SQL中不允許出現(xiàn)DDL語句。一般也不給予create/alter這類權(quán)限，但阿里云RDS只區(qū)分讀寫用戶

六. 行為規(guī)范

(1)不允許在DBA不知情的情況下導(dǎo)現(xiàn)網(wǎng)數(shù)據(jù)

(2)大批量更新，如修復(fù)數(shù)據(jù)，避開高峰期，并通知DBA。直接執(zhí)行sql的由運(yùn)維或DBA同事操作

(3)及時處理已下線業(yè)務(wù)的SQL

(4)復(fù)雜sql上線審核

因為目前還沒有SQL審查機(jī)制，復(fù)雜sql如多表join,count,group by，主動上報DBA評估。

(5)重要項目的數(shù)據(jù)庫方案選型和設(shè)計必須提前通知DBA參與

總結(jié)

以上就是本文的全部內(nèi)容，希望對大家有所幫助。

參考：

MySQL prepare原理詳解

幾個比較重要的MySQL變量

MySQL主庫binlog(master-log)與從庫relay-log關(guān)系代碼詳解

感謝閱讀，希望朋友們對本站多多支持！