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

1、數(shù)據(jù)庫管理規(guī)范 版本號： V1.0 信息科 2017 年 4 月 數(shù)據(jù)庫管理規(guī)范 1規(guī)范說明3 2維護(hù)管理安全規(guī)范 3 3設(shè)計(jì)規(guī)范 4 4. SQL編寫規(guī)范7 5. 數(shù)據(jù)庫備份與恢復(fù)策略規(guī)范 8 6開發(fā)測試與上線安全規(guī)范 10 7監(jiān)控 10 8連接池使用規(guī)范 11 9數(shù)據(jù)庫重要級別劃分 11 10數(shù)據(jù)庫安裝規(guī)范 12 11業(yè)務(wù)上線數(shù)據(jù)庫部分規(guī)范 13 1規(guī)范說明 本規(guī)范是針對數(shù)據(jù)庫使用，維護(hù)管理，設(shè)計(jì)開發(fā)等。 2維護(hù)管理安全規(guī)范 【強(qiáng)制】 2.1數(shù)據(jù)庫版本使用規(guī)范 MYSQL使用5.6.39社區(qū)版本 2.3數(shù)據(jù)庫賬戶安全與管理規(guī)范 2.3.1密碼安全【強(qiáng)制】 密碼不少于8位，應(yīng)包含數(shù)字，字母，

2、字符 密碼不應(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)中

3、各個(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.1 mysql數(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è)后綴，表名、字段名必須使用

4、小寫字母或數(shù)字；禁 止出現(xiàn)數(shù)字開頭，禁止兩個(gè)下劃線中間只出現(xiàn)數(shù)字。數(shù)據(jù)庫字段名的修改代價(jià)很大，因?yàn)?無法進(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索引命名：1_表名_字段名（如果存在多字段索引，取每字段前三個(gè)字符加下劃 線組合，如：在 hyid,hyname,hymobile上建立聯(lián)合索引，命名： I_表名_hyi_hyn_hym,如 果前

5、三個(gè)截取字符相同，就從字段名稱中不同的字符開始取三個(gè)字符加下劃線組合，如： 在 zhyid,zh yn ame,zhymobile上建立聯(lián)合索弓丨，命名：1_ 表 _id_n am_mob） 3.1.7唯一索引命名：Ul_表名_字段名（如果存在多字段唯一索引，取每字段前三個(gè)字 符加下劃線組合，如：在hyid,hy name,hymobile上建立唯一索引，命名：Ul_表名 _hyi_hyn_hym,如果前三個(gè)截取字符相同，就從字段名稱中不同的字符開始取三個(gè)字符加下 劃線組合，女口：在 zhyid,zhy name,zhymobile上建立唯一索引，命名：Ul_表 _id _n am_mob）

6、3.1.8主鍵命名：pa名_字段名（如果存在多字段主鍵，取每字段前三個(gè)字符加下劃 線組合，如：在 hyid,hyname,hymobile上建立主鍵，命名：PK_表名_hyi_hyn_hym,如果 前三個(gè)截取字符相同，就從字段名稱中不同的字符開始取三個(gè)字符加下劃線組合，如：在 zhyid,zh yn ame,zhymobile上建立主鍵，命名：PK_表 _id _n am_mob） 3.1.9外鍵命名：FK_表名_主表名_字段名 3.1.10 Sequenee命名：SEQ_I名列名（或者根據(jù)需要另取名字） 3.2設(shè)計(jì)原則 3.2.1表的設(shè)計(jì) 【強(qiáng)制】每個(gè)表，都必須要有主鍵。主鍵是每行數(shù)據(jù)的唯一

7、標(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ù) 以缺省的方式建立外鍵（即用delete restrict 方式），以達(dá)到保護(hù)數(shù)據(jù)一致性的目的； 【強(qiáng)制】外鍵一定要建立

8、索引。 【建議】單表行數(shù)超過500萬行或者單表容量超過 2GB,才推薦進(jìn)行分庫分表 3.2.2列的設(shè)計(jì) 【強(qiáng)制】字段命名必須用英文來定義，如果太長可用縮寫; 【強(qiáng)制】 相應(yīng)類型數(shù)據(jù)存入相應(yīng)類型字段（如：數(shù)字=NUMBE,日期 =DATE定長字符=CHAR變長字符=VARCHAR等）否則會自動進(jìn)行不 必要的類型轉(zhuǎn)換，降低性能，破壞數(shù)據(jù)完整性 【強(qiáng)制】表達(dá)是與否概念的字段，必須使用is_xxx的方式命名，數(shù)據(jù)類 型是 unsigned tinyint （ 1 表示是，0表示否） 【強(qiáng)制】小數(shù)類型為decimal，禁止使用float 和double 【建議】varchar是可變長字符串，不預(yù)先分配存

9、儲空間，長度不要超過 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 索引的

10、設(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)制】如果有order by的場景，請注意利用索引的有序性。order by 最 后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現(xiàn) file_sort的情況，影響查詢性能。 正例： where a=? and b=

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

12、碼中使用DDL語句，比如truncate , alter table等。 【強(qiáng)制】禁止使用與系統(tǒng)環(huán)境相關(guān)的mysql in side函數(shù)，列如UUID(), USER(, sysdate() 這些函數(shù)，這樣可能會導(dǎo)致主從同步不一致。 【強(qiáng)制】禁止在充當(dāng)讀角色的mysqld上執(zhí)行寫操作(寫操作指update,delete,insert),因 為可能導(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ì)值為

13、 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: select coll, col2 from tbll, tbl2 where tbl1.col3 = tb

14、l2.col4; 其中“ tbl1.col3 ”與“ tbl2.col4 ”數(shù)據(jù)類型嚴(yán)格一致。 【建議】必須正確開啟事務(wù)并且顯式關(guān)閉事務(wù) 【建議】使用域名連接數(shù)據(jù)庫。禁止線上應(yīng)用使用IP地址連接數(shù)據(jù)庫。 【建議】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天，每月一號備份，保留 1

15、00天三個(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)證測試 備份腳本如下： #!/bi n/bash #This is a ShellScript For Auto DB Backup DBName=xgw-global-10- Bac

16、kupPath=/data/mysqlbak/daybak LogFile=/data/mysqlbak/daybak/db .log cd /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 ; the n mkdir $BackupPath fi echo $Lo

17、gFile echo $(date +%y-%m-%d %H:%M:%S) $LogFile echo $LogFile #Delete Old File if -f $OldFile ; the n rm -f $OldFile $LogFile 2 the n echo $NewFile The Backup File is exists,Ca nt Backup! else -opt /usr/local/mysql/b in/mysqldump-si ngle-tra nsactio n-A -master-data=2 -all-databases $DumpFile tar czv

18、f $NewFile $DumpFile $LogFile 2 find /data/mysqlbak/daybak/* -mtime +7 -exec rm -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)

19、表結(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，MO

20、NGODB進(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-20000 9數(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ù)，備份 至少保留本地和異 地兩份 增量備份或全庫邏 輯備份，備份至少保 留本地和異地兩份 全庫邏輯備

21、份，備份 至少保留本地和異 地兩份 咼可用 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ù) 10數(shù)據(jù)庫安裝規(guī)范 數(shù)據(jù)庫安裝一律采用解壓安裝方式，具體步驟如下： mkdir -p /data/mysql/data mkdir -p /data/mysql/logs groupadd mysql useradd -g mysql mysql chow n -R mysql:mysql /