1、MySQL基礎(chǔ)規(guī)范開(kāi)發(fā)規(guī)范數(shù)據(jù)研發(fā)與運(yùn)營(yíng)部 / 王偉目 錄 CONTENTS一、基本規(guī)范二、命名規(guī)范三、庫(kù)表設(shè)計(jì)規(guī)范四、索引設(shè)計(jì)規(guī)范五、字段設(shè)計(jì)規(guī)范六、SQL語(yǔ)句設(shè)計(jì)規(guī)范七、行為規(guī)范引言引言線(xiàn)上的數(shù)據(jù)庫(kù)故障，很大一部分來(lái)自對(duì)數(shù)據(jù)庫(kù)的使用操作不規(guī)范，在開(kāi)發(fā)線(xiàn)上的數(shù)據(jù)庫(kù)故障，很大一部分來(lái)自對(duì)數(shù)據(jù)庫(kù)的使用操作不規(guī)范，在開(kāi)發(fā)過(guò)程中遵循一定的規(guī)范，可以有效地減低數(shù)據(jù)庫(kù)故障率、提高數(shù)據(jù)庫(kù)的穩(wěn)過(guò)程中遵循一定的規(guī)范，可以有效地減低數(shù)據(jù)庫(kù)故障率、提高數(shù)據(jù)庫(kù)的穩(wěn)定性、高效性、安全性。定性、高效性、安全性。1 1、當(dāng)前線(xiàn)上、當(dāng)前線(xiàn)上MySQLMySQL的版本有的版本有5.5.14/5.5.38/5.6.205.5.

2、14/5.5.38/5.6.20，默認(rèn)采用，默認(rèn)采用MySQL5.5.38MySQL5.5.38的版本。的版本。2 2、DDL/DMLDDL/DML操作申請(qǐng)必須走操作申請(qǐng)必須走ITSVITSV流程；授權(quán)默認(rèn)授增刪改查的權(quán)限，需要流程；授權(quán)默認(rèn)授增刪改查的權(quán)限，需要申請(qǐng)人直屬領(lǐng)導(dǎo)審批即可，其他特殊權(quán)限至少需要申請(qǐng)人所屬部門(mén)總監(jiān)以申請(qǐng)人直屬領(lǐng)導(dǎo)審批即可，其他特殊權(quán)限至少需要申請(qǐng)人所屬部門(mén)總監(jiān)以上領(lǐng)導(dǎo)審批，上領(lǐng)導(dǎo)審批，然后發(fā)郵件到然后發(fā)郵件到即可。即可。3 3、線(xiàn)上做大的變動(dòng)，特別是對(duì)、線(xiàn)上做大的變動(dòng)，特別是對(duì)MySQLMySQL數(shù)據(jù)庫(kù)調(diào)整較大的，在上線(xiàn)前后要通數(shù)據(jù)庫(kù)調(diào)整較大的，在上線(xiàn)前后要通知知D

3、BADBA觀察數(shù)據(jù)庫(kù)整體情況，以防訪(fǎng)問(wèn)量陡增造成數(shù)據(jù)庫(kù)性能急劇下降給業(yè)觀察數(shù)據(jù)庫(kù)整體情況，以防訪(fǎng)問(wèn)量陡增造成數(shù)據(jù)庫(kù)性能急劇下降給業(yè)務(wù)帶來(lái)影響。務(wù)帶來(lái)影響。1 1、基本規(guī)范、基本規(guī)范1 1、使用、使用INNODBINNODB存儲(chǔ)引擎存儲(chǔ)引擎 5.5 5.5以后的默認(rèn)引擘，支持事務(wù)，行級(jí)鎖，更好的恢復(fù)性，高并發(fā)下性以后的默認(rèn)引擘，支持事務(wù)，行級(jí)鎖，更好的恢復(fù)性，高并發(fā)下性能更好，對(duì)多核，大內(nèi)存，能更好，對(duì)多核，大內(nèi)存，ssdssd等硬件支持更好。等硬件支持更好。2 2、MySQLMySQL字符集使用字符集使用UTF8UTF8 統(tǒng)一統(tǒng)一，不會(huì)有轉(zhuǎn)換產(chǎn)生亂碼風(fēng)險(xiǎn)。，不會(huì)有轉(zhuǎn)換產(chǎn)生亂碼風(fēng)險(xiǎn)。3 3、所有

4、表都需要添加注釋?zhuān)瑔伪頂?shù)據(jù)量建議控制在、所有表都需要添加注釋?zhuān)瑔伪頂?shù)據(jù)量建議控制在10001000萬(wàn)以?xún)?nèi)。萬(wàn)以?xún)?nèi)。4 4、不在數(shù)據(jù)庫(kù)中存儲(chǔ)圖片、文件等大數(shù)據(jù)。、不在數(shù)據(jù)庫(kù)中存儲(chǔ)圖片、文件等大數(shù)據(jù)。5 5、禁止在線(xiàn)上做數(shù)據(jù)庫(kù)壓力測(cè)試。、禁止在線(xiàn)上做數(shù)據(jù)庫(kù)壓力測(cè)試。6 6、禁止從測(cè)試、開(kāi)發(fā)環(huán)境直連線(xiàn)上數(shù)據(jù)庫(kù)。、禁止從測(cè)試、開(kāi)發(fā)環(huán)境直連線(xiàn)上數(shù)據(jù)庫(kù)。2 2、基本規(guī)范、基本規(guī)范1 1、庫(kù)名、表名、字段名必須使用小寫(xiě)字母并采用下劃線(xiàn)、庫(kù)名、表名、字段名必須使用小寫(xiě)字母并采用下劃線(xiàn)分割，禁止使用中分割，禁止使用中橫線(xiàn)。橫線(xiàn)。2 2、庫(kù)名、表名、字段名禁止超過(guò)、庫(kù)名、表名、字段名禁止超過(guò)3232個(gè)字符，須見(jiàn)名

5、知意，統(tǒng)一規(guī)范個(gè)字符，須見(jiàn)名知意，統(tǒng)一規(guī)范、易于辨識(shí)。易于辨識(shí)。3 3、庫(kù)名、表名、字段名禁止使用庫(kù)名、表名、字段名禁止使用MySQLMySQL保留關(guān)鍵字。保留關(guān)鍵字。4 4、臨時(shí)庫(kù)、臨時(shí)表名必須以臨時(shí)庫(kù)、臨時(shí)表名必須以tmptmp為前綴并以日期為后綴。為前綴并以日期為后綴。5 5、備份庫(kù)、備份表名必須以備份庫(kù)、備份表名必須以bakbak為前綴并以日期為為前綴并以日期為后綴后綴3 3、庫(kù)表設(shè)計(jì)規(guī)范、庫(kù)表設(shè)計(jì)規(guī)范1 1、不建議使用分區(qū)表、不建議使用分區(qū)表MySQLMySQL的分區(qū)表實(shí)際性能不是很好，且管理維護(hù)成本較高。的分區(qū)表實(shí)際性能不是很好，且管理維護(hù)成本較高。2 2、拆分大字段和訪(fǎng)問(wèn)頻率低的

6、字段，分離冷熱數(shù)據(jù)。、拆分大字段和訪(fǎng)問(wèn)頻率低的字段，分離冷熱數(shù)據(jù)。3 3、用、用HASHHASH進(jìn)行散表，表名后綴使用十進(jìn)制數(shù)，下標(biāo)從進(jìn)行散表，表名后綴使用十進(jìn)制數(shù)，下標(biāo)從0 0開(kāi)始。開(kāi)始。首次分表盡量多的分，避免二次分表，二次分表的難度和成本較高。首次分表盡量多的分，避免二次分表，二次分表的難度和成本較高。4 4、按日期時(shí)間分表需符合、按日期時(shí)間分表需符合YYYYMMDDHHYYYYMMDDHH格式。格式。5 5、采用合適的分庫(kù)分表策略。、采用合適的分庫(kù)分表策略。6 6、單表字段數(shù)控制在、單表字段數(shù)控制在2020個(gè)個(gè)以?xún)?nèi)以?xún)?nèi)4 4、索引設(shè)計(jì)規(guī)范、索引設(shè)計(jì)規(guī)范 索引是一把雙刃劍，它可以提高查詢(xún)

7、效率但也會(huì)降低插入和更新的速度并占用磁盤(pán)空間1 1、單張表中索引數(shù)量不超過(guò)、單張表中索引數(shù)量不超過(guò)5 5個(gè)。個(gè)。2 2、單個(gè)索引中的字段數(shù)不超過(guò)、單個(gè)索引中的字段數(shù)不超過(guò)5 5個(gè)。個(gè)。3 3、innodbinnodb主鍵推薦使用自增列，主鍵不應(yīng)該被修改，字符串不應(yīng)該做主鍵，主鍵推薦使用自增列，主鍵不應(yīng)該被修改，字符串不應(yīng)該做主鍵，如果不指定主鍵，如果不指定主鍵，innodbinnodb會(huì)使用唯一且非空值索引代替。會(huì)使用唯一且非空值索引代替。4 4、如果是復(fù)合索引，區(qū)分度最大的字段放在索引前面。、如果是復(fù)合索引，區(qū)分度最大的字段放在索引前面。5 5、核心、核心SQLSQL優(yōu)先考慮覆蓋索引。優(yōu)先考

8、慮覆蓋索引。6 6、區(qū)分度最大的字段放在索引前面。、區(qū)分度最大的字段放在索引前面。7 7、避免冗余或重復(fù)索引：、避免冗余或重復(fù)索引：合理創(chuàng)建聯(lián)合索引（避免冗余），合理創(chuàng)建聯(lián)合索引（避免冗余），index(index(a,b,ca,b,c) )相當(dāng)于相當(dāng)于index(a)index(a)、index(index(a,ba,b) )、index(index(a,b,ca,b,c) )。4 4、索引設(shè)計(jì)規(guī)范、索引設(shè)計(jì)規(guī)范8 8、不在低基數(shù)列上建立索引，例如、不在低基數(shù)列上建立索引，例如性別性別。9 9、不在索引列進(jìn)行數(shù)學(xué)運(yùn)算和函數(shù)運(yùn)算。、不在索引列進(jìn)行數(shù)學(xué)運(yùn)算和函數(shù)運(yùn)算。1010、盡量不要使用外鍵。

9、、盡量不要使用外鍵。對(duì)父表和子表的操作會(huì)相互影響，降低可用性。對(duì)父表和子表的操作會(huì)相互影響，降低可用性。1111、不使用、不使用% %前導(dǎo)的查詢(xún)，如前導(dǎo)的查詢(xún)，如like“%xxxlike“%xxx”，無(wú)法使用索引。，無(wú)法使用索引。1212、不使用反向查詢(xún)，如、不使用反向查詢(xún)，如not in / not likenot in / not like無(wú)法使用索引，導(dǎo)致全表掃描全表掃描。無(wú)法使用索引，導(dǎo)致全表掃描全表掃描。1313、索引不是越多越好，按實(shí)際需要進(jìn)行創(chuàng)建。、索引不是越多越好，按實(shí)際需要進(jìn)行創(chuàng)建。在線(xiàn)上創(chuàng)建表的時(shí)候，一定要考慮好表的索引，這樣避免線(xiàn)上MySQL性能問(wèn)題帶來(lái)的故障以及降低后

10、續(xù)維護(hù)成本。沒(méi)有索引的建表語(yǔ)句DBA不予通過(guò)審核。5 5、字段設(shè)計(jì)規(guī)范、字段設(shè)計(jì)規(guī)范1 1、禁止使用、禁止使用TEXTTEXT、BLOBBLOB類(lèi)型。類(lèi)型。2 2、將字符轉(zhuǎn)化為數(shù)字。、將字符轉(zhuǎn)化為數(shù)字。3 3、使用、使用TINYINTTINYINT來(lái)代替來(lái)代替ENUMENUM類(lèi)型類(lèi)型4 4、字段長(zhǎng)度盡量按實(shí)際需要進(jìn)行分配，不要隨意分配一個(gè)很大的容量。、字段長(zhǎng)度盡量按實(shí)際需要進(jìn)行分配，不要隨意分配一個(gè)很大的容量。5 5、如果可能的話(huà)所有字段均定義為、如果可能的話(huà)所有字段均定義為not nullnot null。6 6、使用、使用UNSIGNEDUNSIGNED存儲(chǔ)非負(fù)整數(shù)。存儲(chǔ)非負(fù)整數(shù)。同樣的字

11、節(jié)數(shù)，存儲(chǔ)的數(shù)值范圍更大。如同樣的字節(jié)數(shù)，存儲(chǔ)的數(shù)值范圍更大。如tinyinttinyint有符號(hào)為有符號(hào)為-128-127-128-127，無(wú)符號(hào)為無(wú)符號(hào)為0-2550-255。7 7、INTINT類(lèi)型固定占用類(lèi)型固定占用4 4個(gè)字節(jié)存儲(chǔ)。個(gè)字節(jié)存儲(chǔ)。8 8、使用、使用TIMESTAMPTIMESTAMP存儲(chǔ)時(shí)間。存儲(chǔ)時(shí)間。因?yàn)橐驗(yàn)門(mén)IMESTAMPTIMESTAMP使用使用4 4字節(jié)，字節(jié)，DATETIMEDATETIME使用使用8 8個(gè)字節(jié)個(gè)字節(jié), ,同時(shí)同時(shí)TIMESTAMPTIMESTAMP具有具有自動(dòng)賦值以及自動(dòng)更新的特性。自動(dòng)賦值以及自動(dòng)更新的特性。9 9、禁止在數(shù)據(jù)庫(kù)中存儲(chǔ)明文

12、密碼。、禁止在數(shù)據(jù)庫(kù)中存儲(chǔ)明文密碼。6 6、SQLSQL語(yǔ)句設(shè)計(jì)規(guī)范語(yǔ)句設(shè)計(jì)規(guī)范1 1、盡量避免相同語(yǔ)句由于書(shū)寫(xiě)格式的不同，而導(dǎo)致多次語(yǔ)法解析。、盡量避免相同語(yǔ)句由于書(shū)寫(xiě)格式的不同，而導(dǎo)致多次語(yǔ)法解析。2 2、避免隱式轉(zhuǎn)換，會(huì)導(dǎo)致索引失效。、避免隱式轉(zhuǎn)換，會(huì)導(dǎo)致索引失效。3 3、禁止使用存儲(chǔ)過(guò)程、觸發(fā)器、視圖等，讓數(shù)據(jù)庫(kù)做最擅長(zhǎng)的事，降、禁止使用存儲(chǔ)過(guò)程、觸發(fā)器、視圖等，讓數(shù)據(jù)庫(kù)做最擅長(zhǎng)的事，降低業(yè)務(wù)耦合度。低業(yè)務(wù)耦合度。4 4、避免使用大表的、避免使用大表的joinjoin。5 5、避免在數(shù)據(jù)庫(kù)中進(jìn)行數(shù)學(xué)運(yùn)算，容易將業(yè)務(wù)邏輯和、避免在數(shù)據(jù)庫(kù)中進(jìn)行數(shù)學(xué)運(yùn)算，容易將業(yè)務(wù)邏輯和DBDB耦合在一起，

13、耦合在一起，MySQLMySQL不擅長(zhǎng)數(shù)學(xué)運(yùn)算和邏輯判斷，無(wú)法使用索引不擅長(zhǎng)數(shù)學(xué)運(yùn)算和邏輯判斷，無(wú)法使用索引6 6、減少與數(shù)據(jù)庫(kù)的交互次數(shù)、減少與數(shù)據(jù)庫(kù)的交互次數(shù)。7 7、拒絕大、拒絕大SQLSQL，拆分成小，拆分成小SQLSQL，充分利用，充分利用query cachequery cache，充分利用多核，充分利用多核CUPCUP。8 8、使用、使用inin代替代替oror，inin的值不超過(guò)的值不超過(guò)10001000個(gè)。個(gè)。6 6、SQLSQL語(yǔ)句設(shè)計(jì)規(guī)范語(yǔ)句設(shè)計(jì)規(guī)范9 9、禁止使用、禁止使用order byorder by因?yàn)橐驗(yàn)镺RDER BY ORDER BY 會(huì)將數(shù)據(jù)從磁盤(pán)中讀取，

14、進(jìn)行排序，會(huì)消耗大量的會(huì)將數(shù)據(jù)從磁盤(pán)中讀取，進(jìn)行排序，會(huì)消耗大量的IOIO和和CPUCPU，可以在程序中進(jìn)行相應(yīng)的排序。，可以在程序中進(jìn)行相應(yīng)的排序。1010、使用、使用union allunion all而不是而不是unionunion。1111、禁止單條、禁止單條SQLSQL語(yǔ)句同時(shí)更新多個(gè)表。語(yǔ)句同時(shí)更新多個(gè)表。1212、不使用、不使用select select * *，避免消耗，避免消耗cpucpu和硬盤(pán)和硬盤(pán)IOIO、消耗網(wǎng)絡(luò)帶寬。、消耗網(wǎng)絡(luò)帶寬。1313、禁止使用、禁止使用testtest庫(kù)。庫(kù)。7 7、行為行為規(guī)范規(guī)范1 1、批量導(dǎo)入、導(dǎo)出數(shù)據(jù)必須提前通知、批量導(dǎo)入、導(dǎo)出數(shù)據(jù)必須

15、提前通知DBADBA協(xié)助觀察；協(xié)助觀察；2 2、批量更新數(shù)據(jù)，如、批量更新數(shù)據(jù)，如update,deleteupdate,delete操作，需要操作，需要DBADBA進(jìn)行審查，并在執(zhí)進(jìn)行審查，并在執(zhí)行過(guò)程中觀察服務(wù)負(fù)載等各種行過(guò)程中觀察服務(wù)負(fù)載等各種狀況，狀況，進(jìn)行進(jìn)行DDL/DMLDDL/DML操作時(shí)，需要研發(fā)在場(chǎng)執(zhí)操作時(shí)，需要研發(fā)在場(chǎng)執(zhí)行，特別是執(zhí)行更新、刪除數(shù)據(jù)庫(kù)和刪除庫(kù)表行，特別是執(zhí)行更新、刪除數(shù)據(jù)庫(kù)和刪除庫(kù)表時(shí)時(shí)；3 3、禁止在從庫(kù)上執(zhí)行后臺(tái)管理和統(tǒng)計(jì)、禁止在從庫(kù)上執(zhí)行后臺(tái)管理和統(tǒng)計(jì)類(lèi)復(fù)雜的類(lèi)復(fù)雜的功能查詢(xún)；功能查詢(xún)；4 4、禁止有、禁止有supersuper權(quán)限的應(yīng)用程序賬號(hào)存在；

16、權(quán)限的應(yīng)用程序賬號(hào)存在；5 5、產(chǎn)品出現(xiàn)非數(shù)據(jù)庫(kù)導(dǎo)致的故障時(shí)及時(shí)通知、產(chǎn)品出現(xiàn)非數(shù)據(jù)庫(kù)導(dǎo)致的故障時(shí)及時(shí)通知DBADBA協(xié)助排查；協(xié)助排查；6 6、促銷(xiāo)活動(dòng)或上線(xiàn)新功能必須提前通知、促銷(xiāo)活動(dòng)或上線(xiàn)新功能必須提前通知DBADBA進(jìn)行流量評(píng)估；進(jìn)行流量評(píng)估；7 7、如果需要數(shù)據(jù)庫(kù)數(shù)據(jù)，請(qǐng)及時(shí)、如果需要數(shù)據(jù)庫(kù)數(shù)據(jù)，請(qǐng)及時(shí)聯(lián)系聯(lián)系DBADBA進(jìn)行恢復(fù)進(jìn)行恢復(fù)；8 8、對(duì)單表的多次、對(duì)單表的多次alteralter操作必須合并為一次操作；操作必須合并為一次操作；9 9、不在、不在MySQLMySQL數(shù)據(jù)庫(kù)中存放業(yè)務(wù)邏輯，禁止使用存儲(chǔ)過(guò)程；數(shù)據(jù)庫(kù)中存放業(yè)務(wù)邏輯，禁止使用存儲(chǔ)過(guò)程；7 7、行為行為規(guī)范規(guī)范1010、重大項(xiàng)目的數(shù)據(jù)庫(kù)方案選型和設(shè)計(jì)必須提前通知、重大項(xiàng)目的數(shù)據(jù)庫(kù)方案選型和設(shè)計(jì)必須提前通知DBADBA參與；參與；1111、對(duì)特別重要的庫(kù)表，提前與、對(duì)特別重要的庫(kù)表，提