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

1、數(shù)據(jù)庫管理規(guī)范版本號(hào)：V1.0信息科2017年4月 數(shù)據(jù)庫管理規(guī)范1 規(guī)范說明22 維護(hù)管理安全規(guī)范23設(shè)計(jì)規(guī)范34. SQL編寫規(guī)范65. 數(shù)據(jù)庫備份與恢復(fù)策略規(guī)范76開發(fā)測(cè)試與上線安全規(guī)范97 監(jiān)控108連接池使用規(guī)范109 數(shù)據(jù)庫重要級(jí)別劃分1010 數(shù)據(jù)庫安裝規(guī)范1111 業(yè)務(wù)上線數(shù)據(jù)庫部分規(guī)范121 規(guī)范說明本規(guī)范是針對(duì)數(shù)據(jù)庫使用，維護(hù)管理，設(shè)計(jì)開發(fā)等。2 維護(hù)管理安全規(guī)范2.1數(shù)據(jù)庫版本使用規(guī)范【強(qiáng)制】MYSQL使用5.6.39社區(qū)版本2.3數(shù)據(jù)庫賬戶安全與管理規(guī)范2.3.1 密碼安全【強(qiáng)制】l 密碼不少于8位，應(yīng)包含數(shù)字，字母，字符 l 密碼不應(yīng)該和用戶名一致l 刪除數(shù)據(jù)庫默認(rèn)空

2、用戶賬戶l 必須修改root密碼2.4賬戶安全管理【強(qiáng)制】l 為應(yīng)用程序設(shè)立獨(dú)立訪問賬戶l 禁止開發(fā)工程師通過應(yīng)用帳號(hào)登錄生產(chǎn)數(shù)據(jù)庫l 禁止QA申請(qǐng)線上賬號(hào)與使用賬號(hào)登陸線上環(huán)境進(jìn)行測(cè)試l 數(shù)據(jù)庫只有root用戶具有最高管理權(quán)限l 備份數(shù)據(jù)庫用戶要單獨(dú)建立，并專屬備份恢復(fù)操作l 數(shù)據(jù)庫安裝后刪除或鎖閉不需要使用的數(shù)據(jù)庫賬號(hào)l 數(shù)據(jù)庫管理員賬戶可以鎖定和解鎖其他用戶 l 數(shù)據(jù)庫管理賬戶在空閑5分鐘以上會(huì)自動(dòng)退出 l 數(shù)據(jù)庫管理權(quán)限賬號(hào)不能超過2個(gè)2.5數(shù)據(jù)庫日志管理規(guī)范【強(qiáng)制】windows、linux 下的日志管理l Windows系統(tǒng)應(yīng)開啟日志功能，記錄系統(tǒng)和系統(tǒng)中各個(gè)進(jìn)程的相關(guān)信息 l l

3、inux的操作系統(tǒng)要開啟系統(tǒng)日志 l Mysql數(shù)據(jù)庫如果使用了innodb引擎必須打開二進(jìn)制日志l Mysql必須打開慢日志，并且將慢日志閥值設(shè)置為1-2秒之間 l 對(duì)Mysql的錯(cuò)誤日志等要進(jìn)行定期查看，定期清理和定期備份管理2.6數(shù)據(jù)庫架構(gòu)規(guī)范【強(qiáng)制】2.6.1 mysql數(shù)據(jù)庫高可用l MYSQL 采用復(fù)制+第三方心跳軟件或者第三方集群架構(gòu)(經(jīng)測(cè)試后)實(shí)現(xiàn)近7X24小時(shí)高可用性要求，數(shù)據(jù)庫架構(gòu)必須能夠?qū)崿F(xiàn)故障自動(dòng)遷移，滿足業(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ù)字開

4、頭，禁止兩個(gè)下劃線中間只出現(xiàn)數(shù)字。數(shù)據(jù)庫字段名的修改代價(jià)很大，因?yàn)闊o法進(jìn)行預(yù)發(fā)布，所以字段名稱需要慎重考慮。3.1.2視圖命名：V_相關(guān)表名（或者根據(jù)需要另取名字）3.1.3存儲(chǔ)過程命名：PRO_存儲(chǔ)過程名（用英文表達(dá)存儲(chǔ)過程意義）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è)截取字符相同，就從字段名稱中不同的字符開始

5、取三個(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è)字符加

6、下劃線組合，如：在 hyid,hyname,hymobile上建立主鍵，命名：PK_表名_hyi_hyn_hym,如果前三個(gè)截取字符相同，就從字段名稱中不同的字符開始取三個(gè)字符加下劃線組合，如：在 zhyid,zhyname,zhymobile上建立主鍵，命名：PK_表_id_nam_mob)3.1.9外鍵命名：FK_表名_主表名_字段名3.1.10 Sequence命名：SEQ_表名_列名（或者根據(jù)需要另取名字）3.2 設(shè)計(jì)原則3.2.1表的設(shè)計(jì)l 【強(qiáng)制】每個(gè)表，都必須要有主鍵。主鍵是每行數(shù)據(jù)的唯一標(biāo)識(shí)，保證主鍵不可隨意更新修改，在不知道是否需要主鍵的時(shí)候，請(qǐng)加上主鍵，它會(huì)為你的程序以及將

7、來查找數(shù)據(jù)中的錯(cuò)誤等等，提供一定的幫助；l 【強(qiáng)制】所有的MySQL數(shù)據(jù)庫除歷史原因外，都必須采用UTF8編碼l 【建議】一個(gè)表的某列與另一表有關(guān)聯(lián)關(guān)系的時(shí)候， 請(qǐng)?jiān)趹?yīng)用程序維護(hù)外鍵關(guān)系，如果在數(shù)據(jù)庫建立外鍵約束請(qǐng)遵循以下幾點(diǎn)：盡量少使用外鍵，在高并發(fā)下數(shù)據(jù)庫會(huì)增加較大開銷；不要以數(shù)據(jù)操作不方便為理由而不建外鍵。加上外鍵以后，一些數(shù)據(jù)操作變得有些麻煩，但是這正是對(duì)數(shù)據(jù)一致性的保護(hù)以缺省的方式建立外鍵（即用delete restrict方式），以達(dá)到保護(hù)數(shù)據(jù)一致性的目的；l 【強(qiáng)制】外鍵一定要建立索引。l 【建議】單表行數(shù)超過 500萬行或者單表容量超過 2GB，才推薦進(jìn)行分庫分表3.2.2列的

8、設(shè)計(jì)l 【強(qiáng)制】字段命名必須用英文來定義，如果太長(zhǎng)可用縮寫；l 【強(qiáng)制】相應(yīng)類型數(shù)據(jù)存入相應(yīng)類型字段(如：數(shù)字=>NUMBER，日期=>DATE，定長(zhǎng)字符=>CHAR，變長(zhǎng)字符=>VARCHAR2等）否則會(huì)自動(dòng)進(jìn)行不必要的類型轉(zhuǎn)換，降低性能，破壞數(shù)據(jù)完整性l 【強(qiáng)制】表達(dá)是與否概念的字段，必須使用 is_xxx的方式命名，數(shù)據(jù)類型是 unsigned tinyint（ 1表示是，0表示否）l 【強(qiáng)制】小數(shù)類型為 decimal，禁止使用 float和 doublel 【建議】varchar是可變長(zhǎng)字符串，不預(yù)先分配存儲(chǔ)空間，長(zhǎng)度不要超過 5000，如果存儲(chǔ)長(zhǎng)度大于此值，

9、定義字段類型為 text，獨(dú)立出來一張表，用主鍵來對(duì)應(yīng)，避免影響其它字段索引效率l 【建議】字段的寬度要在一定時(shí)間內(nèi)足夠用，但也不要過寬，占用過多的存儲(chǔ)空間；l 【建議】除非必要，否則盡量不加冗余列。所謂冗余列，是指能通過其他列計(jì)算出來的列，或者是與某列表達(dá)同一含義的列，或者是從其他表復(fù)制過來的列等等。冗余列需要應(yīng)用程序來維護(hù)一致性，相關(guān)列的值改變的時(shí)候，冗余列也需要隨之修改，而這一規(guī)則未必所有人都知道，就有可能因此發(fā)生不一致的情況。如果是應(yīng)用的特殊需要，或者是為了優(yōu)化某些邏輯很復(fù)雜的查詢等操作，可以加冗余列；3.2.3 索引的設(shè)計(jì)l 【強(qiáng)制】業(yè)務(wù)上具有唯一特性的字段，即使是組合字段，也必須建

10、成唯一索引l 【強(qiáng)制】超過三個(gè)表禁止 join。需要 join的字段，數(shù)據(jù)類型保持絕對(duì)一致；多表關(guān)聯(lián)l 【強(qiáng)制】查詢時(shí)，保證被關(guān)聯(lián)的字段需要有索引l 【強(qiáng)制】在 varchar字段上建立索引時(shí)，必須指定索引長(zhǎng)度，沒必要對(duì)全字段建立索引，l 【強(qiáng)制】頁面搜索嚴(yán)禁左模糊或者全模糊，如果需要請(qǐng)走搜索引擎來解決l 【強(qiáng)制】如果有 order by的場(chǎng)景，請(qǐng)注意利用索引的有序性。order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現(xiàn) file_sort的情況，影響查詢性能。 正例：where a=? and b=? order by c; 索引：a_b_cl 【強(qiáng)制】建組合

11、索引的時(shí)候，區(qū)分度最高的在最左邊l 【建議】性能測(cè)試階段收集90%SQL后，統(tǒng)一設(shè)計(jì)建立索引l 【建議】根據(jù)實(shí)際文本區(qū)分度決定索引長(zhǎng)度。 說明：索引的長(zhǎng)度與區(qū)分度是一對(duì)矛盾體，一般對(duì)字符串類型數(shù)據(jù)，長(zhǎng)度為 20的索引，區(qū)分度會(huì)高達(dá) 90%以上，可以使用 count(distinct left(列名, 索引長(zhǎng)度)/count(*)的區(qū)分度來確定。4. SQL編寫規(guī)范l 【強(qiáng)制】禁止開發(fā)人員在SQL中添加 Hint，Hint只能由DBA審核后添加。l 【強(qiáng)制】禁止使用悲觀鎖定，即讀鎖 select for update。l 【強(qiáng)制】禁止在開發(fā)代碼中使用DDL語句，比如 truncate，alter

12、 table 等。l 【強(qiáng)制】禁止使用與系統(tǒng)環(huán)境相關(guān)的mysql inside 函數(shù)，列如UUID()，USER()，sysdate()這些函數(shù)，這樣可能會(huì)導(dǎo)致主從同步不一致。l 【強(qiáng)制】禁止在充當(dāng)讀角色的mysqld上執(zhí)行寫操作（寫操作指update,delete,insert),因?yàn)榭赡軐?dǎo)致同步中斷或者數(shù)據(jù)不一致。l 【強(qiáng)制】不要使用 count(列名)或 count(常量)來替代 count()，count()就是 SQL92定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語法，跟數(shù)據(jù)庫無關(guān)，跟 NULL和非 NULL無關(guān)。 l 【強(qiáng)制】說明：count(*)會(huì)統(tǒng)計(jì)值為 NULL的行，而 count(列名)不會(huì)統(tǒng)計(jì)

13、此列為 NULL值的行l(wèi) 【強(qiáng)制】使用 ISNULL()來判斷是否為 NULL值。注意：NULL與任何值的直接比較都為 NULLl 【強(qiáng)制】禁止使用存儲(chǔ)過程，存儲(chǔ)過程難以調(diào)試和擴(kuò)展，更沒有移植性l 【強(qiáng)制】在表查詢中，一律不要使用 * 作為查詢的字段列表，需要哪些字段必須明確寫明l 【強(qiáng)制】表連接操作中，作為連接條件的字段的數(shù)據(jù)類型嚴(yán)格一致。l 【強(qiáng)制】如果表連接字段數(shù)據(jù)類型不一致，在SQL中用顯示用類型轉(zhuǎn)換，具體情況咨詢DBA。示例正確用法1：select col1, col2 from tbl1, tbl2where tbl1.col3 = tbl2.col4;其中“tbl1.col3”與

14、“tbl2.col4”數(shù)據(jù)類型嚴(yán)格一致。l 【建議】必須正確開啟事務(wù)并且顯式關(guān)閉事務(wù)l 【建議】使用域名連接數(shù)據(jù)庫。 禁止線上應(yīng)用使用IP地址連接數(shù)據(jù)庫。l 【建議】in操作能避免則避免，若實(shí)在避免不了，需要仔細(xì)評(píng)估 in后邊的集合元素?cái)?shù)量，控制在 1000個(gè)之內(nèi)。l 【建議】禁止DML語句的where條件中包含恒真條件（如：1=1） 5. 數(shù)據(jù)庫備份與恢復(fù)策略規(guī)范 5.1 備份策略l 數(shù)據(jù)庫備份需要在本地，從庫，存儲(chǔ)至少三個(gè)地方保留，確保備份不丟失l 數(shù)據(jù)庫備份周期根據(jù)業(yè)務(wù)需求，默認(rèn)采取每日備份，保留7天，每周周一備份，保留30天，每月一號(hào)備份，保留100天三個(gè)維度進(jìn)行l(wèi) 數(shù)據(jù)庫備份方式采取

15、mysqldump方式進(jìn)行每日備份或根據(jù)需要進(jìn)行INNODB增量備份l 數(shù)據(jù)庫備份采取每天業(yè)務(wù)負(fù)載低峰時(shí)段的凌晨3-5點(diǎn)進(jìn)行備份l 數(shù)據(jù)庫備份用戶使用專有備份用戶，權(quán)限僅僅為可以進(jìn)行備份和恢復(fù)l 數(shù)據(jù)庫備份必須保留兩個(gè)以上存儲(chǔ)點(diǎn)，避免因誤操作或磁盤損壞丟失備份文件l 數(shù)據(jù)備份設(shè)置保留周期，周期以外的數(shù)據(jù)庫備份文件視其需要進(jìn)行刻盤歸檔處理l 數(shù)據(jù)庫備份采取每月定期驗(yàn)證備份文件有效性的驗(yàn)證測(cè)試l 備份腳本如下：#!/bin/bash #This is a ShellScript For Auto DB Backup DBName=xgw-global-10- BackupPath=/data/my

16、sqlbak/daybakLogFile=/data/mysqlbak/daybak/db.logcd /data/mysqlbak/daybak NewFile=$DBName$(date +%Y%m%d-%H-%M).tar.gz DumpFile=$DBName$(date +%Y%m%d-%H-%M).sql #OldFile=$DBName$(date +%Y%m%d-%H-%M -date='7 days ago').tar.gz # if ! -d $BackupPath ; then mkdir $BackupPath fiecho "-"

17、>> $LogFile echo $(date +"%y-%m-%d %H:%M:%S") >> $LogFile echo "-" >> $LogFile #Delete Old Fileif -f $OldFile ; then rm -f $OldFile >> $LogFile 2>&1 echo "$OldFileDelete Old File Success!" >> $LogFile else echo "$OldFileNo Old B

18、ackup File!" >> $LogFilefi# Create NewFile if -f $NewFile ; then echo "$NewFile The Backup File is exists,Can't Backup! " else /usr/local/mysql/bin/mysqldump -single-transaction -A -master-data=2 -opt -all-databases > $DumpFile tar czvf $NewFile $DumpFile >> $LogFil

19、e 2>&1 echo "$NewFileBackup Success!" >> $LogFile rm -rf $DumpFile fiecho "-" >> $LogFilecp /data/mysqlbak/daybak/$NewFile /data/dbbak/sitebak/ >> $LogFile 2>&1find /data/dbbak/sitebak/* -mtime +7 -exec rm -rf ;find /data/mysqlbak/daybak/* -mtime +

20、7 -exec rm -rf ;5.2 恢復(fù)策略l 數(shù)據(jù)庫恢復(fù)采取專有數(shù)據(jù)庫恢復(fù)賬號(hào)進(jìn)行l(wèi) 數(shù)據(jù)庫恢復(fù)要先制定失敗回退計(jì)劃并保證其可行l(wèi) 數(shù)據(jù)庫如果是對(duì)已有業(yè)務(wù)數(shù)據(jù)庫換進(jìn)行時(shí)，必須先對(duì)現(xiàn)有數(shù)據(jù)庫環(huán)境進(jìn)行備份l 數(shù)據(jù)庫恢復(fù)必須進(jìn)行測(cè)試驗(yàn)證l 數(shù)據(jù)庫恢復(fù)后要進(jìn)行全面的數(shù)據(jù)和業(yè)務(wù)驗(yàn)證l 數(shù)據(jù)庫恢復(fù)成功后，必須立即及逆行那個(gè)一次全面的數(shù)據(jù)庫備份6開發(fā)測(cè)試與上線安全規(guī)范l 開發(fā)測(cè)試期間，數(shù)據(jù)庫由DBA維護(hù)，所有開發(fā)賬號(hào)由DBA統(tǒng)一建立，開發(fā)人員擁有數(shù)據(jù)庫的增刪改查以及庫結(jié)構(gòu)表結(jié)構(gòu)變更權(quán)限l 測(cè)試完畢正式上線前，由DBA統(tǒng)一進(jìn)行數(shù)據(jù)庫結(jié)構(gòu)及數(shù)據(jù)上線部署，所有數(shù)據(jù)庫權(quán)限統(tǒng)一收回并按照實(shí)際需求進(jìn)行授權(quán)部署l

21、生產(chǎn)數(shù)據(jù)庫中應(yīng)用程序賬號(hào)只賦予最小增刪改查權(quán)限，不賦予變更數(shù)據(jù)庫設(shè)置庫結(jié)構(gòu)以及表結(jié)構(gòu)權(quán)限，只有DBA具有最高權(quán)限l 生產(chǎn)數(shù)據(jù)庫任何庫結(jié)構(gòu)表結(jié)構(gòu)索引結(jié)構(gòu)以及數(shù)據(jù)庫對(duì)象建立刪除修改都需要經(jīng)過DBA操作7 監(jiān)控l 【強(qiáng)制】所有生產(chǎn)庫必須加入監(jiān)控系統(tǒng)，不加入監(jiān)控系統(tǒng)不得上線生產(chǎn)l 【強(qiáng)制】MYSQL監(jiān)控項(xiàng)有CPU,內(nèi)存，I/O，MYSQL進(jìn)程，MYSQL復(fù)制狀態(tài)，MYSQL復(fù)制延遲，MYSQL端口，MYSQL鎖等待l 【強(qiáng)制】MONGODB監(jiān)控項(xiàng)有CPU,內(nèi)存，I/O，MONGODB進(jìn)程，MYSQL復(fù)制集狀態(tài)，MONGODB端口8連接池使用規(guī)范l 建議在性能測(cè)試階段進(jìn)行連接池最優(yōu)設(shè)置l 連接池必須開啟

22、autoconnect設(shè)置l 最小連接數(shù)設(shè)置為50-100l 最大連接數(shù)設(shè)置為500-1000l 連接池idletime設(shè)置為10000-200009 數(shù)據(jù)庫重要級(jí)別劃分l 目前根據(jù)數(shù)據(jù)庫存儲(chǔ)數(shù)據(jù)的安全重要程度暫時(shí)將數(shù)據(jù)庫從高到低劃分為三個(gè)級(jí)別(A級(jí),B級(jí),C級(jí))A級(jí)數(shù)據(jù)庫B級(jí)數(shù)據(jù)庫C級(jí)數(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ù)存儲(chǔ)每表空間獨(dú)立數(shù)據(jù)文件，數(shù)據(jù)與索引獨(dú)立存儲(chǔ)每表空間獨(dú)立數(shù)據(jù)文件，數(shù)據(jù)與索引獨(dú)立存儲(chǔ)不要求備份恢復(fù)測(cè)試間隔每月每季度半年各級(jí)別數(shù)據(jù)類型指導(dǎo)財(cái)務(wù)交易類數(shù)據(jù)運(yùn)營(yíng)類數(shù)據(jù)日志記錄類數(shù)據(jù)10 數(shù)據(jù)庫安裝規(guī)范數(shù)據(jù)庫安裝一律采用解壓安裝方式，具體步驟如下：mkdir -p /data/mysql/datamkdir -p /data/mysql/logsgroupadd mysqluseradd -g mysql mysqlchown -R mysql:mysql /da