數(shù)據(jù)庫管理規(guī)范

數(shù)據(jù)庫管理規(guī)范數(shù)據(jù)庫管理規(guī)范版本號:VI、0信息科2017年4月

數(shù)據(jù)庫管理規(guī)范TOC\o"1-5"\h\z\o"CurrentDocument"1規(guī)范說明 1.2維護(hù)管理安全規(guī)范 \o"CurrentDocument"3設(shè)計(jì)規(guī)范 2\o"CurrentDocument"4、SQL編寫規(guī)范 5\o"CurrentDocument"5、數(shù)據(jù)庫備份與恢復(fù)策略規(guī)范 6.\o"CurrentDocument"6開發(fā)測試與上線安全規(guī)范 工\o"CurrentDocument"7監(jiān)控 7\o"CurrentDocument"8連接池使用規(guī)范 8\o"CurrentDocument"9數(shù)據(jù)庫重要級別劃分 810數(shù)據(jù)庫安裝規(guī)范 \o"CurrentDocument"11業(yè)務(wù)上線數(shù)據(jù)庫部分規(guī)范 9規(guī)范說明本規(guī)范就是針對數(shù)據(jù)庫使用,維護(hù)管理,設(shè)計(jì)開發(fā)等。維護(hù)管理安全規(guī)范2、1數(shù)據(jù)庫版本使用規(guī)范【強(qiáng)制】MYSQL使用5、6、39社區(qū)版本2、3數(shù)據(jù)庫賬戶安全與管理規(guī)范2、3、1密碼安全【強(qiáng)制】密碼不少于8位,應(yīng)包含數(shù)字,字母,字符密碼不應(yīng)該與用戶名一致刪除數(shù)據(jù)庫默認(rèn)空用戶賬戶必須修改root密碼2、4賬戶安全管理【強(qiáng)制】為應(yīng)用程序設(shè)立獨(dú)立訪問賬戶禁止開發(fā)工程師通過應(yīng)用帳號登錄生產(chǎn)數(shù)據(jù)庫禁止QA申請線上賬號與使用賬號登陸線上環(huán)境進(jìn)行測試數(shù)據(jù)庫只有root用戶具有最高管理權(quán)限備份數(shù)據(jù)庫用戶要單獨(dú)建立,并專屬備份恢復(fù)操作數(shù)據(jù)庫安裝后刪除或鎖閉不需要使用的數(shù)據(jù)庫賬號數(shù)據(jù)庫管理員賬戶可以鎖定與解鎖其她用戶數(shù)據(jù)庫管理賬戶在空閑5分鐘以上會自動退出數(shù)據(jù)庫管理權(quán)限賬號不能超過2個(gè)2、5數(shù)據(jù)庫日志管理規(guī)范【強(qiáng)制】windows、linux下的日志管理Windows系統(tǒng)應(yīng)開啟日志功能記錄系統(tǒng)與系統(tǒng)中各個(gè)進(jìn)程的相關(guān)信息linux的操作系統(tǒng)要開啟系統(tǒng)日志Mysql數(shù)據(jù)庫如果使用了innodb引擎必須打開二進(jìn)制日志Mysql必須打開慢日志，并且將慢日志閥值設(shè)置為1-2秒之間對Mysql的錯(cuò)誤日志等要進(jìn)行定期查瞧，定期清理與定期備份管理2、6數(shù)據(jù)庫架構(gòu)規(guī)范【強(qiáng)制】2、6、1mysql數(shù)據(jù)庫高可用?MYSQL采用復(fù)制+第三方心跳軟件或者第三方集群架構(gòu)（經(jīng)測試后）實(shí)現(xiàn)近7X24小時(shí)高可用性要求，數(shù)據(jù)庫架構(gòu)必須能夠?qū)崿F(xiàn)故障自動遷移滿足業(yè)務(wù)7X24小時(shí)持續(xù)服務(wù)要求3設(shè)計(jì)規(guī)范3、1命名規(guī)范【強(qiáng)制】3、1、1表的命名表的名稱在T_后增加一個(gè)或者多個(gè)后綴，表名、字段名必須使用小寫字母或數(shù)字;禁止出現(xiàn)數(shù)字開頭,禁止兩個(gè)下劃線中間只出現(xiàn)數(shù)字。數(shù)據(jù)庫字段名的修改代價(jià)很大,因?yàn)闊o法進(jìn)行預(yù)發(fā)布,所以字段名稱需要慎重考慮。3、1、2視圖命名:V_相關(guān)表名（或者根據(jù)需要另取名字）3、1、3存儲過程命名:PRO_存儲過程名（用英文表達(dá)存儲過程意義）3、1、4函數(shù)命名:FUN_函數(shù)名稱（用英文表達(dá)函數(shù)作用）3、1、5觸發(fā)器命名:TRI_觸發(fā)器名稱（用英文表達(dá)觸發(fā)器作用）3、1、6索引命名：I_表名.字段名（如果存在多字段索引，取每字段前三個(gè)字符加下劃線組合,如:在hyid,hyname,hymobile上建立聯(lián)合索引，命名：I—表名_hyi_hyn_hym,如果前三個(gè)截取字符相同,就從字段名稱中不同的字符開始取三個(gè)字符加下劃線組合,如:在zhyid,zhyname,zhymobile上建立聯(lián)合索弓|,命名：I_表_id_nam_mob）3、1、7唯一索引命名:UI_表名.字段名（如果存在多字段唯一索引，取每字段前三個(gè)字符加下劃線組合，如：在hyid,hyname,hymobile上建立唯一索引，命名：UI_表名_hyi_hyn_hym,如果前三個(gè)截取字符相同，就從字段名稱中不同的字符開始取三個(gè)字符加下劃線組合,如:在zhyid,zhyname,zhymobile上建立唯一索引，命名：UI_表_id_nam_mob）3、1、8主鍵命名:PK_表名一字段名（如果存在多字段主鍵,取每字段前三個(gè)字符加下劃線組合，如：在hyid,hyname,hymobile上建立主鍵，命名：PK_表名_hyi_hyn_hym,如果前三個(gè)截取字符相同,就從字段名稱中不同的字符開始取三個(gè)字符加下劃線組合,如：在zhyid,zhyname,zhymobile上建立主鍵,命名：PK_表_id_nam_mob）3、1、9外鍵命名:FK_表名-主表名—字段名3.1.10Sequence命名:SEQ_表名一列名（或者根據(jù)需要另取名字）3.2設(shè)計(jì)原則3、2、1表的設(shè)計(jì)【強(qiáng)制】每個(gè)表，都必須要有主鍵。主鍵就是每行數(shù)據(jù)的唯一標(biāo)識,保證主鍵不可隨意更新修改,在不知道就是否需要主鍵的時(shí)候,請加上主鍵,它會為您的程序以及將來查找數(shù)據(jù)中的錯(cuò)誤等等,提供一定的幫助;【強(qiáng)制】所有的MySQL數(shù)據(jù)庫除歷史原因外,都必須采用UTF8編碼【建議】一個(gè)表的某列與另一表有關(guān)聯(lián)關(guān)系的時(shí)候，請?jiān)趹?yīng)用程序維護(hù)外鍵關(guān)系，如果在數(shù)據(jù)庫建立外鍵約束請遵循以下幾點(diǎn)：盡量少使用外鍵,在高并發(fā)下數(shù)據(jù)庫會增加較大開銷;不要以數(shù)據(jù)操作不方便為理由而不建外鍵。加上外鍵以后,一些數(shù)據(jù)操作變得有些麻煩,但就是這正就是對數(shù)據(jù)一致性的保護(hù)以缺省的方式建立外鍵（即用deleterestrict方式），以達(dá)到保護(hù)數(shù)據(jù)一致性的目的；【強(qiáng)制】外鍵一定要建立索引。【建議】單表行數(shù)超過500萬行或者單表容量超過2GB,才推薦進(jìn)行分庫分表3、2、2列的設(shè)計(jì)【強(qiáng)制】字段命名必須用英文來定義，如果太長可用縮寫；【強(qiáng)制】相應(yīng)類型數(shù)據(jù)存入相應(yīng)類型字段（如:數(shù)字=>NUMBER,日期,DATE,定長字符,CHAR,變長字符=>VARCHAR2等）否則會自動進(jìn)行不必要的類型轉(zhuǎn)換,降低性能,破壞數(shù)據(jù)完整性【強(qiáng)制】表達(dá)就是與否概念的字段,必須使用is_xxx的方式命名,數(shù)據(jù)類型就是unsignedtinyint（1表示就是,0表示否）【強(qiáng)制】小數(shù)類型為decimal,禁止使用float與double【建議】varchar就是可變長字符串，不預(yù)先分配存儲空間,長度不要超過5000,如果存儲長度大于此值,定義字段類型為text,獨(dú)立出來一張表,用主鍵來對應(yīng),避免影響其它字段索引效率【建議】字段的寬度要在一定時(shí)間內(nèi)足夠用，但也不要過寬，占用過多的存儲空間;【建議】除非必要,否則盡量不加冗余列。所謂冗余列，就是指能通過其她列計(jì)算出來的列,或者就是與某列表達(dá)同一含義的列,或者就是從其她表復(fù)制過來的列等等。冗余列需要應(yīng)用程序來維護(hù)一致性,相關(guān)列的值改變的時(shí)候,冗余列也需要隨之修改,而這一規(guī)則未必所有人都知道,就有可能因此發(fā)生不一致的情況。如果就是應(yīng)用的特殊需要,或者就是為了優(yōu)化某些邏輯很復(fù)雜的查詢等操作,可以加冗余列;3、2、3索引的設(shè)計(jì)【強(qiáng)制】業(yè)務(wù)上具有唯一特性的字段,即使就是組合字段,也必須建成唯一索引【強(qiáng)制】超過三個(gè)表禁止join。需要join的字段，數(shù)據(jù)類型保持絕對一致;多表關(guān)聯(lián)【強(qiáng)制】查詢時(shí)，保證被關(guān)聯(lián)的字段需要有索引【強(qiáng)制】在varchar字段上建立索引時(shí)，必須指定索引長度，沒必要對全字段建立索引,【強(qiáng)制】頁面搜索嚴(yán)禁左模糊或者全模糊，如果需要請走搜索引擎來解決【強(qiáng)制】如果有orderby的場景，請注意利用索引的有序性。orderby最后的字段就是組合索引的一部分,并且放在索引組合順序的最后,避免出現(xiàn)的情況,影響查詢性能。正例:wherea二？andb二？orderbyc;索弓|:2_5_?！緩?qiáng)制】建組合索引的時(shí)候，區(qū)分度最高的在最左邊【建議】性能測試階段收集90%SQL后,統(tǒng)一設(shè)計(jì)建立索引【建議】根據(jù)實(shí)際文本區(qū)分度決定索引長度。說明:索引的長度與區(qū)分度就是一對矛盾體,一般對字符串類型數(shù)據(jù),長度為20的索引，區(qū)分度會高達(dá)90%以上，可以使用count(distinctleft(列名,索引長度))/count(*)的區(qū)分度來確定。4、SQL編寫規(guī)范【強(qiáng)制】禁止開發(fā)人員在SQL中添加Hint,Hint只能由DBA審核后添加?！緩?qiáng)制】禁止使用悲觀鎖定，即讀鎖select…forupdate。【強(qiáng)制】禁止在開發(fā)代碼中使用DDL語句，比如truncate,altertable…等。【強(qiáng)制】禁止使用與系統(tǒng)環(huán)境相關(guān)的mysqlinside函數(shù)洌如UUID(),USER(),sysdate()這些函數(shù),這樣可能會導(dǎo)致主從同步不一致。【強(qiáng)制】禁止在充當(dāng)讀角色的mysqld上執(zhí)行寫操作(寫操作指update,delete,insert),因?yàn)榭赡軐?dǎo)致同步中斷或者數(shù)據(jù)不一致?！緩?qiáng)制】不要使用count(列名)或count(常量)來替代count(),count()就就是SQL92定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語法,跟數(shù)據(jù)庫無關(guān),跟NULL與非NULL無關(guān)。【強(qiáng)制】說明:count(*)會統(tǒng)計(jì)值為NULL的行，而count(列名)不會統(tǒng)計(jì)此列為NULL值的行【強(qiáng)制】使用ISNULL()來判斷就是否為NULL值。注意:NULL與任何值的直接比較都為NULL【強(qiáng)制】禁止使用存儲過程，存儲過程難以調(diào)試與擴(kuò)展，更沒有移植性【強(qiáng)制】在表查詢中,一律不要使用*作為查詢的字段列表，需要哪些字段必須明確寫明【強(qiáng)制】表連接操作中，作為連接條件的字段的數(shù)據(jù)類型嚴(yán)格一致?！緩?qiáng)制】如果表連接字段數(shù)據(jù)類型不一致，在SQL中用顯示用類型轉(zhuǎn)換,具體情況咨詢DBA。示例正確用法1:selectcol1,col2fromtbl1,tbl2wheretbl1、col3=tbl2、col4;其中“tbl1、col3”與“tbl2、col4”數(shù)據(jù)類型嚴(yán)格一致?！窘ㄗh】必須正確開啟事務(wù)并且顯式關(guān)閉事務(wù)【建議】使用域名連接數(shù)據(jù)庫。禁止線上應(yīng)用使用IP地址連接數(shù)據(jù)庫?！窘ㄗh】in操作能避免則避免，若實(shí)在避免不了，需要仔細(xì)評估in后邊的集合元素?cái)?shù)量,控制在1000個(gè)之內(nèi)。?【建議】禁止DML語句的where條件中包含恒真條件(如:1=1)5、數(shù)據(jù)庫備份與恢復(fù)策略規(guī)范5、1備份策略數(shù)據(jù)庫備份需要在本地,從庫,存儲至少三個(gè)地方保留,確保備份不丟失數(shù)據(jù)庫備份周期根據(jù)業(yè)務(wù)需求,默認(rèn)采取每日備份,保留7天,每周周一備份,保留30天,每月一號備份,保留100天三個(gè)維度進(jìn)行數(shù)據(jù)庫備份方式采取mysqldump方式進(jìn)行每日備份或根據(jù)需要進(jìn)行INNODB增量備份數(shù)據(jù)庫備份采取每天業(yè)務(wù)負(fù)載低峰時(shí)段的凌晨3-5點(diǎn)進(jìn)行備份數(shù)據(jù)庫備份用戶使用專有備份用戶,權(quán)限僅僅為可以進(jìn)行備份與恢復(fù)數(shù)據(jù)庫備份必須保留兩個(gè)以上存儲點(diǎn),避免因誤操作或磁盤損壞丟失備份文件數(shù)據(jù)備份設(shè)置保留周期,周期以外的數(shù)據(jù)庫備份文件視其需要進(jìn)行刻盤歸檔處理數(shù)據(jù)庫備份采取每月定期驗(yàn)證備份文件有效性的驗(yàn)證測試備份腳本如下:#!/bin/bash#ThisisaShellScriptForAutoDBBackupDBName=xgw-global-10-BackupPath=/data/mysqlbak/daybakLogcd/data/mysqlbak/daybakNewFile=$DBName$(date+%Y%m%d-%H-%M)、tar、gzDumpFile=$DBName$(date+%Y%m%d-%H-%M)、sql#OldFile=$DBName$(date+%Y%m%d-%H-%M--date='7daysago')、tar、gz#if[!-d$BackupPath];thenmkdir$BackupPathfiecho" ">>$LogFileecho$(date+"%y-%m-%d%H:%M:%S")>>$LogFileecho" ">>$LogFile#DeleteOldFileif[-f$OldFile];thenrm-f$OldFile>>$LogFile2>&1echo"[$OldOld!">>$LogFileelseecho"[$OldOldBackupFile!">>$LogFilefi#CreateNewFileif[-f$NewFile];thenecho"[$NewFile]TheBackupexists,Can'tBackup!"else/usr/local/mysql/bin/mysqldump--single-transaction-A--master-data=2--opt--all-databases>$DumpFiletarczvf$NewFile$DumpFile>>$LogFile2>&1echo"[$NewSuccess!">>$LogFilerm-rf$DumpFilefiecho" ">>$LogFilecp/data/mysqlbak/daybak/$NewFile/data/dbbak/sitebak/>>$LogFile2>&1find/data/dbbak/sitebak/*-mtime+7-execrm-rf{}\;find/data/mysqlbak/daybak/*-mtime+7-execrm-rf{}\;5、2恢復(fù)策略數(shù)據(jù)庫恢復(fù)采取專有數(shù)據(jù)庫恢復(fù)賬號進(jìn)行數(shù)據(jù)庫恢復(fù)要先制定失敗回退計(jì)劃并保證其可行數(shù)據(jù)庫如果就是對已有業(yè)務(wù)數(shù)據(jù)庫換進(jìn)行時(shí),必須先對現(xiàn)有數(shù)據(jù)庫環(huán)境進(jìn)行備份數(shù)據(jù)庫恢復(fù)必須進(jìn)行測試驗(yàn)證數(shù)據(jù)庫恢復(fù)后要進(jìn)行全面的數(shù)據(jù)與業(yè)務(wù)驗(yàn)證數(shù)據(jù)庫恢復(fù)成功后,必須立即及逆行那個(gè)一次全面的數(shù)據(jù)庫備份6開發(fā)測試與上線安全規(guī)范開發(fā)測試期間，數(shù)據(jù)庫由DBA維護(hù)，所有開發(fā)賬號由DBA統(tǒng)一建立,開發(fā)人員擁有數(shù)據(jù)庫的增刪改查以及庫結(jié)構(gòu)表結(jié)構(gòu)變更權(quán)限測試完畢正式上線前,由DBA統(tǒng)一進(jìn)行數(shù)據(jù)庫結(jié)構(gòu)及數(shù)據(jù)上線部署,所有數(shù)據(jù)庫權(quán)限統(tǒng)一收回并按照實(shí)際需求進(jìn)行授權(quán)部署生產(chǎn)數(shù)據(jù)庫中應(yīng)用程序賬號只賦予最小增刪改查權(quán)限,不賦予變更數(shù)據(jù)庫設(shè)置庫結(jié)構(gòu)以及表結(jié)構(gòu)權(quán)限，只有DBA具有最高權(quán)限生產(chǎn)數(shù)據(jù)庫任何庫結(jié)構(gòu)表結(jié)構(gòu)索引結(jié)構(gòu)以及數(shù)據(jù)庫對象建立刪除修改都需要經(jīng)過DBA操作7監(jiān)控【強(qiáng)制】所有生產(chǎn)庫必須加入監(jiān)控系統(tǒng)，不加入監(jiān)控系統(tǒng)不得上線生產(chǎn)【強(qiáng)制】MYSQL監(jiān)控項(xiàng)有CPU,內(nèi)存,I/O,MYSQL進(jìn)程,MYSQL復(fù)制狀態(tài),MYSQL復(fù)制延遲,MYSQL端口,MYSQL鎖等待【強(qiáng)制】MONGODB監(jiān)控項(xiàng)有CPU,內(nèi)存,I/O,MONGODB進(jìn)程,MYSQL復(fù)制集狀態(tài),MONGODB端口8連接池使用規(guī)范建議在性能測試階段進(jìn)行連接池最優(yōu)設(shè)置連接池必須開啟autoconnect設(shè)置最小連接數(shù)設(shè)置為50-100最大連接數(shù)設(shè)置為500-1000連接池idletime設(shè)置為10000-200009數(shù)據(jù)庫重要級別劃分?目前根據(jù)數(shù)據(jù)庫存儲數(shù)據(jù)的安全重要程度暫時(shí)將數(shù)據(jù)庫從高到低劃分為三個(gè)級別(A級,B級,C級)A級數(shù)據(jù)庫B級數(shù)據(jù)庫C級數(shù)據(jù)庫備份恢復(fù)增量備份,數(shù)據(jù)庫恢復(fù)要求達(dá)到任意指定時(shí)間點(diǎn)恢復(fù)，備份至少保留本地與異地兩份增量備份或全庫邏輯備份，備份至少保留本地與異地兩份全庫邏輯備份，備份至少保留本地與異地兩份高可用7X24小時(shí)高可用7X24小時(shí)高可用非7X24小時(shí)高可用，允許服務(wù)一小時(shí)以內(nèi)中斷審計(jì)重要數(shù)據(jù)增刪改查需要審計(jì)不需要不需要數(shù)據(jù)存儲每表空間獨(dú)立數(shù)據(jù)文件,數(shù)據(jù)與索引獨(dú)立存儲每表空間獨(dú)立數(shù)據(jù)文件,數(shù)據(jù)與索引獨(dú)立存儲不要求備份恢復(fù)測試間隔每月每季度半年各級別數(shù)據(jù)類型指導(dǎo)財(cái)務(wù)交易類數(shù)據(jù)運(yùn)營類數(shù)據(jù)日志記錄類數(shù)據(jù)數(shù)據(jù)庫安裝規(guī)范數(shù)據(jù)庫安裝一律采用解壓安裝方式,具體步驟如下:mkdir-p/data/mysql/datamkdir-p/data/mysql/logsgroupaddmys