數(shù)據(jù)庫管理規(guī)范[研究分析]

1、技術(shù) l 類別1 數(shù)據(jù)庫管理規(guī)范數(shù)據(jù)庫管理規(guī)范 版本號：版本號：V1.0V1.0 信息科信息科 20172017 年年 4 4 月月 技術(shù) l 類別2 數(shù)據(jù)庫管理規(guī)范數(shù)據(jù)庫管理規(guī)范 1 規(guī)范說明規(guī)范說明 .2 2 維護管理安全規(guī)范維護管理安全規(guī)范 .2 3 設(shè)計規(guī)范設(shè)計規(guī)范 .3 4. SQL 編寫規(guī)范編寫規(guī)范 .6 5. 數(shù)據(jù)庫備份與恢復(fù)策略規(guī)范數(shù)據(jù)庫備份與恢復(fù)策略規(guī)范 .7 6 開發(fā)測試與上線安全規(guī)范開發(fā)測試與上線安全規(guī)范 .9 7 監(jiān)控監(jiān)控 .10 8 連接池使用規(guī)范連接池使用規(guī)范 .10 9 數(shù)據(jù)庫重要級別劃分數(shù)據(jù)庫重要級別劃分 .10 10 數(shù)據(jù)庫安裝規(guī)范數(shù)據(jù)庫安裝規(guī)范 .11 11

2、 業(yè)務(wù)上線數(shù)據(jù)庫部分規(guī)范業(yè)務(wù)上線數(shù)據(jù)庫部分規(guī)范 .12 技術(shù) l 類別3 1 規(guī)范說明規(guī)范說明 本規(guī)范是針對數(shù)據(jù)庫使用，維護管理，設(shè)計開發(fā)等。 2 維護管理安全規(guī)范維護管理安全規(guī)范 2.1 數(shù)據(jù)庫版本使用規(guī)范數(shù)據(jù)庫版本使用規(guī)范【強制】 MYSQL 使用 5.6.39 社區(qū)版本 2.3 數(shù)據(jù)庫賬戶安全與管理規(guī)范數(shù)據(jù)庫賬戶安全與管理規(guī)范 2.3.1 密碼安全密碼安全【強制】 密碼不少于 8 位，應(yīng)包含數(shù)字，字母，字符 密碼不應(yīng)該和用戶名一致 刪除數(shù)據(jù)庫默認空用戶賬戶 必須修改 root 密碼 2.4 賬戶安全賬戶安全管理管理【強制】 為應(yīng)用程序設(shè)立獨立訪問賬戶 禁止開發(fā)工程師通過應(yīng)用帳號登錄生產(chǎn)數(shù)

3、據(jù)庫 禁止 QA 申請線上賬號與使用賬號登陸線上環(huán)境進行測試 數(shù)據(jù)庫只有 root 用戶具有最高管理權(quán)限 備份數(shù)據(jù)庫用戶要單獨建立，并專屬備份恢復(fù)操作 數(shù)據(jù)庫安裝后刪除或鎖閉不需要使用的數(shù)據(jù)庫賬號 數(shù)據(jù)庫管理員賬戶可以鎖定和解鎖其他用戶 數(shù)據(jù)庫管理賬戶在空閑 5 分鐘以上會自動退出 數(shù)據(jù)庫管理權(quán)限賬號不能超過 2 個 技術(shù) l 類別4 2.5 數(shù)據(jù)庫日志管理規(guī)范數(shù)據(jù)庫日志管理規(guī)范【強制】 windows、linux 下的日志管理 Windows 系統(tǒng)應(yīng)開啟日志功能，記錄系統(tǒng)和系統(tǒng)中各個進程的相關(guān)信息 linux 的操作系統(tǒng)要開啟系統(tǒng)日志 Mysql 數(shù)據(jù)庫如果使用了 innodb 引擎必須打開

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

5、禁 止出現(xiàn)數(shù)字開頭，禁止兩個下劃線中間只出現(xiàn)數(shù)字。數(shù)據(jù)庫字段名的修改代價很大，因為 無法進行預(yù)發(fā)布，所以字段名稱需要慎重考慮。 3.1.23.1.2 視圖命名視圖命名：V_相關(guān)表名（或者根據(jù)需要另取名字） 3.1.33.1.3 存儲過程命名存儲過程命名：PRO_存儲過程名（用英文表達存儲過程意義） 3.1.43.1.4 函數(shù)命名函數(shù)命名：FUN_函數(shù)名稱（用英文表達函數(shù)作用） 3.1.53.1.5 觸發(fā)器命名觸發(fā)器命名：TRI_觸發(fā)器名稱（用英文表達觸發(fā)器作用） 3.1.63.1.6 索引命名索引命名：I_表名_字段名(如果存在多字段索引，取每字段前三個字符加下劃 技術(shù) l 類別5 線組合，如

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

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

8、.103.1.10 SequenceSequence 命名命名：SEQ_表名_列名（或者根據(jù)需要另取名字） 3.23.2 設(shè)計原則設(shè)計原則 3.2.13.2.1 表的設(shè)計表的設(shè)計 【強制】每個表，都必須要有主鍵。主鍵是每行數(shù)據(jù)的唯一標識，保證主鍵不可隨意 更新修改，在不知道是否需要主鍵的時候，請加上主鍵，它會為你的程序以及將來查 找數(shù)據(jù)中的錯誤等等，提供一定的幫助； 【強制】所有的 MySQL 數(shù)據(jù)庫除歷史原因外，都必須采用 UTF8 編碼 【建議】一個表的某列與另一表有關(guān)聯(lián)關(guān)系的時候， 請在應(yīng)用程序維護外鍵關(guān)系，如 果在數(shù)據(jù)庫建立外鍵約束請遵循以下幾點： 盡量少使用外鍵，在高并發(fā)下數(shù)據(jù)庫會增

9、加較大開銷； 不要以數(shù)據(jù)操作不方便為理由而不建外鍵。加上外鍵以后，一些數(shù)據(jù)操作變得有些麻 煩，但是這正是對數(shù)據(jù)一致性的保護 以缺省的方式建立外鍵（即用 delete restrict 方式） ，以達到保護數(shù)據(jù)一致性的目的； 【強制】外鍵一定要建立索引。 技術(shù) l 類別6 【建議】單表行數(shù)超過 500 萬行或者單表容量超過 2GB，才推薦進行分庫分表 3.2.23.2.2 列的設(shè)計列的設(shè)計 【強制】字段命名必須用英文來定義，如果太長可用縮寫； 【強制】相應(yīng)類型數(shù)據(jù)存入相應(yīng)類型字段(如：數(shù)字=NUMBER，日期 =DATE，定長字符=CHAR，變長字符=VARCHAR2 等）否則會自動進行不 必要

10、的類型轉(zhuǎn)換，降低性能，破壞數(shù)據(jù)完整性 【強制】表達是與否概念的字段，必須使用 is_xxx 的方式命名，數(shù)據(jù) 類型是 unsigned tinyint（ 1 表示是，0 表示否） 【強制】小數(shù)類型為 decimal，禁止使用 float 和 double 【建議】varchar 是可變長字符串，不預(yù)先分配存儲空間，長度不要超 過 5000，如果存儲長度大于此值，定義字段類型為 text，獨立出來一 張表，用主鍵來對應(yīng)，避免影響其它字段索引效率 【建議】字段的寬度要在一定時間內(nèi)足夠用，但也不要過寬，占用過多 的存儲空間； 【建議】除非必要，否則盡量不加冗余列。所謂冗余列，是指能通過其 他列計算出

11、來的列，或者是與某列表達同一含義的列，或者是從其他表 復(fù)制過來的列等等。冗余列需要應(yīng)用程序來維護一致性，相關(guān)列的值改 變的時候，冗余列也需要隨之修改，而這一規(guī)則未必所有人都知道，就 有可能因此發(fā)生不一致的情況。如果是應(yīng)用的特殊需要，或者是為了優(yōu) 化某些邏輯很復(fù)雜的查詢等操作，可以加冗余列； 3.2.33.2.3 索引的設(shè)計索引的設(shè)計 【強制】業(yè)務(wù)上具有唯一特性的字段，即使是組合字段，也必須建成唯一索 引 【強制】超過三個表禁止 join。需要 join 的字段，數(shù)據(jù)類型保持絕對一致； 技術(shù) l 類別7 多表關(guān)聯(lián) 【強制】查詢時，保證被關(guān)聯(lián)的字段需要有索引 【強制】在 varchar 字段上建立

12、索引時，必須指定索引長度，沒必要對全字 段建立索引， 【強制】頁面搜索嚴禁左模糊或者全模糊，如果需要請走搜索引擎來解決 【強制】如果有 order by 的場景，請注意利用索引的有序性。order by 最 后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現(xiàn) file_sort 的情況，影響查詢性能。 正例：where a=? and b=? order by c; 索引：a_b_c 【強制】建組合索引的時候，區(qū)分度最高的在最左邊 【建議】性能測試階段收集 90%SQL 后，統(tǒng)一設(shè)計建立索引 【建議】根據(jù)實際文本區(qū)分度決定索引長度。 說明：索引的長度與區(qū)分度是一對矛盾體，一般對字

13、符串類型數(shù)據(jù)，長度 為 20 的索引，區(qū)分度會高達 90%以上，可以使用 count(distinct left(列名, 索引長度)/count(*)的區(qū)分度來確定。 4. SQL 編寫規(guī)范編寫規(guī)范 【強制】禁止開發(fā)人員在 SQL 中添加 Hint，Hint 只能由 DBA 審核后添加。 【強制】禁止使用悲觀鎖定，即讀鎖 select for update。 【強制】禁止在開發(fā)代碼中使用 DDL 語句，比如 truncate，alter table 等。 【強制】禁止使用與系統(tǒng)環(huán)境相關(guān)的 mysql inside 函數(shù)，列如 UUID()，USER()， sysdate()這些函數(shù)，這樣可能會

14、導(dǎo)致主從同步不一致。 【強制】禁止在充當讀角色的 mysqld 上執(zhí)行寫操作（寫操作指 update,delete,insert),因 技術(shù) l 類別8 為可能導(dǎo)致同步中斷或者數(shù)據(jù)不一致。 【強制】不要使用 count(列名)或 count(常量)來替代 count()，count()就是 SQL92 定 義的標準統(tǒng)計行數(shù)的語法，跟數(shù)據(jù)庫無關(guān)，跟 NULL 和非 NULL 無關(guān)。 【強制】說明：count(*)會統(tǒng)計值為 NULL 的行，而 count(列名)不會統(tǒng)計此列為 NULL 值的行 【強制】使用 ISNULL()來判斷是否為 NULL 值。注意：NULL 與任何值的直接比較都為 NU

15、LL 【強制】禁止使用存儲過程，存儲過程難以調(diào)試和擴展，更沒有移植性 【強制】在表查詢中，一律不要使用 * 作為查詢的字段列表，需要哪些字段必須明確 寫明 【強制】表連接操作中，作為連接條件的字段的數(shù)據(jù)類型嚴格一致。 【強制】如果表連接字段數(shù)據(jù)類型不一致，在 SQL 中用顯示用類型轉(zhuǎn)換，具體情況咨 詢 DBA。 示例 正確用法 1：select col1, col2 from tbl1, tbl2 where tbl1.col3 = tbl2.col4; 其中“tbl1.col3”與“tbl2.col4”數(shù)據(jù)類型嚴格一致。 【建議】必須正確開啟事務(wù)并且顯式關(guān)閉事務(wù) 【建議】使用域名連接數(shù)據(jù)庫。

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

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

18、=/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 ; then mkdir $BackupPath fi echo - $LogFile echo $(date +%y-%m-%d %H:%M:%S)

19、 $LogFile echo - $LogFile #Delete Old File if -f $OldFile ; then rm -f $OldFile $LogFile 2 then echo $NewFile The Backup File is exists,Cant Backup! else /usr/local/mysql/bin/mysqldump -single-transaction -A -master-data=2 -opt -all- databases $DumpFile tar czvf $NewFile $DumpFile $LogFile 2 find /d

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

21、DBA 統(tǒng)一進行數(shù)據(jù)庫結(jié)構(gòu)及數(shù)據(jù)上線部署，所有數(shù)據(jù)庫權(quán)限 統(tǒng)一收回并按照實際需求進行授權(quán)部署 技術(shù) l 類別11 生產(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)控監(jiān)控 【強制】所有生產(chǎn)庫必須加入監(jiān)控系統(tǒng)，不加入監(jiān)控系統(tǒng)不得上線生產(chǎn) 【強制】MYSQL 監(jiān)控項有 CPU,內(nèi)存，I/O，MYSQL 進程，MYSQL 復(fù)制狀態(tài)，MYSQL 復(fù) 制延遲，MYSQL 端口，MYSQL 鎖等待 【強制】MONGODB 監(jiān)控項有 CPU,內(nèi)存，

22、I/O，MONGODB 進程，MYSQL 復(fù)制集狀態(tài)， MONGODB 端口 8 連接池使用規(guī)范連接池使用規(guī)范 建議在性能測試階段進行連接池最優(yōu)設(shè)置 連接池必須開啟 autoconnect 設(shè)置 最小連接數(shù)設(shè)置為 50-100 最大連接數(shù)設(shè)置為 500-1000 連接池 idletime 設(shè)置為 10000-20000 9 數(shù)據(jù)庫重要級別劃分數(shù)據(jù)庫重要級別劃分 目前根據(jù)數(shù)據(jù)庫存儲數(shù)據(jù)的安全重要程度暫時將數(shù)據(jù)庫從高到低劃分為三個級別(A 級, B 級,C 級) A 級數(shù)據(jù)庫B 級數(shù)據(jù)庫C 級數(shù)據(jù)庫 備份恢復(fù)增量備份，數(shù)據(jù)庫 恢復(fù)要求達到任意 指定時間點恢復(fù)， 備份至少保留本地 和異地兩份 增量備

23、份或全庫邏 輯備份，備份至少 保留本地和異地兩 份 全庫邏輯備份，備 份至少保留本地和 異地兩份 高可用7X24 小時高可用7X24 小時高可用非 7X24 小時高可用， 技術(shù) l 類別12 允許服務(wù)一小時以 內(nèi)中斷 審計重要數(shù)據(jù)增刪改查 需要審計 不需要不需要 數(shù)據(jù)存儲每表空間獨立數(shù)據(jù) 文件，數(shù)據(jù)與索引 獨立存儲 每表空間獨立數(shù)據(jù) 文件，數(shù)據(jù)與索引 獨立存儲 不要求 備份恢復(fù)測試間隔每月每季度半年 各級別數(shù)據(jù)類型指 導(dǎo) 財務(wù)交易類數(shù)據(jù)運營類數(shù)據(jù)日志記錄類數(shù)據(jù) 10 數(shù)據(jù)庫安裝規(guī)范數(shù)據(jù)庫安裝規(guī)范 數(shù)據(jù)庫安裝一律采用解壓安裝方式，具體步驟如下： mkdir -p /data/mysql/data mkdir -p /data/mysql/logs groupadd mysql