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

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

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

3、inux的操作系統(tǒng)要開啟系統(tǒng)日志 l Mysql數(shù)據(jù)庫如果使用了innodb引擎必須打開二進制日志l Mysql必須打開慢日志，并且將慢日志閥值設(shè)置為1-2秒之間 l 對Mysql的錯誤日志等要進行定期查看，定期清理和定期備份管理2.6數(shù)據(jù)庫架構(gòu)規(guī)范【強制】2.6.1 mysql數(shù)據(jù)庫高可用l MYSQL 采用復制+第三方心跳軟件或者第三方集群架構(gòu)(經(jīng)測試后)實現(xiàn)近7X24小時高可用性要求，數(shù)據(jù)庫架構(gòu)必須能夠?qū)崿F(xiàn)故障自動遷移，滿足業(yè)務7X24小時持續(xù)服務要求3設(shè)計規(guī)范3.1命名規(guī)范【強制】3.1.1表的命名表的名稱在T_后增加一個或者多個后綴，表名、字段名必須使用小寫字母或數(shù)字；禁止出現(xiàn)數(shù)字開

4、頭，禁止兩個下劃線中間只出現(xiàn)數(shù)字。數(shù)據(jù)庫字段名的修改代價很大，因為無法進行預發(fā)布，所以字段名稱需要慎重考慮。3.1.2視圖命名：V_相關(guān)表名（或者根據(jù)需要另取名字）3.1.3存儲過程命名：PRO_存儲過程名（用英文表達存儲過程意義）3.1.4函數(shù)命名：FUN_函數(shù)名稱（用英文表達函數(shù)作用）3.1.5觸發(fā)器命名：TRI_觸發(fā)器名稱（用英文表達觸發(fā)器作用）3.1.6索引命名：I_表名_字段名(如果存在多字段索引，取每字段前三個字符加下劃線組合，如：在 hyid,hyname,hymobile上建立聯(lián)合索引，命名：I_表名_hyi_hyn_hym,如果前三個截取字符相同，就從字段名稱中不同的字符開始

5、取三個字符加下劃線組合，如：在 zhyid,zhyname,zhymobile上建立聯(lián)合索引，命名：I_表_id_nam_mob)3.1.7唯一索引命名：UI_表名_字段名(如果存在多字段唯一索引，取每字段前三個字符加下劃線組合，如：在 hyid,hyname,hymobile上建立唯一索引，命名：UI_表名_hyi_hyn_hym,如果前三個截取字符相同，就從字段名稱中不同的字符開始取三個字符加下劃線組合，如：在 zhyid,zhyname,zhymobile上建立唯一索引，命名：UI_表_id_nam_mob)3.1.8主鍵命名：PK_表名_字段名(如果存在多字段主鍵，取每字段前三個字符加

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

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

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

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

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

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

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

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

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

15、mysqldump方式進行每日備份或根據(jù)需要進行INNODB增量備份l 數(shù)據(jù)庫備份采取每天業(yè)務負載低峰時段的凌晨3-5點進行備份l 數(shù)據(jù)庫備份用戶使用專有備份用戶，權(quán)限僅僅為可以進行備份和恢復l 數(shù)據(jù)庫備份必須保留兩個以上存儲點，避免因誤操作或磁盤損壞丟失備份文件l 數(shù)據(jù)備份設(shè)置保留周期，周期以外的數(shù)據(jù)庫備份文件視其需要進行刻盤歸檔處理l 數(shù)據(jù)庫備份采取每月定期驗證備份文件有效性的驗證測試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 恢復策略l 數(shù)據(jù)庫恢復采取專有數(shù)據(jù)庫恢復賬號進行l(wèi) 數(shù)據(jù)庫恢復要先制定失敗回退計劃并保證其可行l(wèi) 數(shù)據(jù)庫如果是對已有業(yè)務數(shù)據(jù)庫換進行時，必須先對現(xiàn)有數(shù)據(jù)庫環(huán)境進行備份l 數(shù)據(jù)庫恢復必須進行測試驗證l 數(shù)據(jù)庫恢復后要進行全面的數(shù)據(jù)和業(yè)務驗證l 數(shù)據(jù)庫恢復成功后，必須立即及逆行那個一次全面的數(shù)據(jù)庫備份6開發(fā)測試與上線安全規(guī)范l 開發(fā)測試期間，數(shù)據(jù)庫由DBA維護，所有開發(fā)賬號由DBA統(tǒng)一建立，開發(fā)人員擁有數(shù)據(jù)庫的增刪改查以及庫結(jié)構(gòu)表結(jié)構(gòu)變更權(quán)限l 測試完畢正式上線前，由DBA統(tǒng)一進行數(shù)據(jù)庫結(jié)構(gòu)及數(shù)據(jù)上線部署，所有數(shù)據(jù)庫權(quán)限統(tǒng)一收回并按照實際需求進行授權(quán)部署l

21、生產(chǎn)數(shù)據(jù)庫中應用程序賬號只賦予最小增刪改查權(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ù)庫對象建立刪除修改都需要經(jīng)過DBA操作7 監(jiān)控l 【強制】所有生產(chǎn)庫必須加入監(jiān)控系統(tǒng)，不加入監(jiān)控系統(tǒng)不得上線生產(chǎn)l 【強制】MYSQL監(jiān)控項有CPU,內(nèi)存，I/O，MYSQL進程，MYSQL復制狀態(tài)，MYSQL復制延遲，MYSQL端口，MYSQL鎖等待l 【強制】MONGODB監(jiān)控項有CPU,內(nèi)存，I/O，MONGODB進程，MYSQL復制集狀態(tài)，MONGODB端口8連接池使用規(guī)范l 建議在性能測試階段進行連接池最優(yōu)設(shè)置l 連接池必須開啟

22、autoconnect設(shè)置l 最小連接數(shù)設(shè)置為50-100l 最大連接數(shù)設(shè)置為500-1000l 連接池idletime設(shè)置為10000-200009 數(shù)據(jù)庫重要級別劃分l 目前根據(jù)數(shù)據(jù)庫存儲數(shù)據(jù)的安全重要程度暫時將數(shù)據(jù)庫從高到低劃分為三個級別(A級,B級,C級)A級數(shù)據(jù)庫B級數(shù)據(jù)庫C級數(shù)據(jù)庫備份恢復增量備份，數(shù)據(jù)庫恢復要求達到任意指定時間點恢復，備份至少保留本地和異地兩份增量備份或全庫邏輯備份，備份至少保留本地和異地兩份全庫邏輯備份，備份至少保留本地和異地兩份高可用7X24小時高可用7X24小時高可用非7X24小時高可用，允許服務一小時以內(nèi)中斷審計重要數(shù)據(jù)增刪改查需要審計不需要不需要數(shù)據(jù)存儲每表空間獨立數(shù)據(jù)文件，數(shù)據(jù)與索引獨立存儲每表空間獨立數(shù)據(jù)文件，數(shù)據(jù)與索引獨立存儲不要求備份恢復測試間隔每月每季度半年各級別數(shù)據(jù)類型指導財務交易類數(shù)據(jù)運營類數(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