第八章

數據庫應用程序開發Database西北工業大學NorthwesternPolytechnicalUniversity數據庫目錄8.1

實戰目標與準備8.2數據庫應用軟件開發概述8.3

JDBC編程實戰8.4ODBC編程實戰8.5數據庫連接池應用實戰8.6ADO.NET——Window窗口程序實戰8.7ORM技術——基于Django框架的Web程序實戰8.8其他數據庫訪問方法實戰8.1實戰目標與準備實戰目標本章目標是掌握數據庫應用程序中的數據庫訪問技術，期望讀者能獨立開發一個基本的數據庫應用程序，特別是其中的數據庫訪問部分。實戰準備本機的應用程序開發，需要根據自己選擇則的編程語言提前安裝好以下軟件：JDBC：JAVA開發環境ODBC：C++或者C#開發環境或者Python開發環境其他訪問數據庫方式：C++或者Python開發環境8.2數據庫應用軟件開發概述8.3JDBC類名路徑備注驅動程序類java.sql.Driver由各數據庫廠商提供驅動程序管理類java.sql.DriverManager作用于應用程序與驅動程序之間數據庫連接類java.sql.Connection用于建立與指定數據庫的連接靜態SQL語句執行類java.sql.Statement用于執行靜態SQL語句并返回結果動態SQL語句執行類java.sql.PreparedStatement用于執行含參SQL語句并返回結果存儲過程語句執行類java.sql.CallableStatement用于執行存儲過程語句并返回結果結果集處理類java.sql.ResultSet用于檢索結果集中的數據JDBC常用類JDBC開發涉及到的所有類都包含在java.sql包中不同的JDBC版本接口名和使用略有差異8.3JDBCJDBC操作數據庫的基本工作流程步驟1：加載驅動程序步驟2：建立與數據庫的連接定義連接的URL利用生成的URL建立與數據庫的連接步驟3：執行SQL語句創建語句執行類對象執行SQL語句，可以通過executeQuery、executeUpdate、execute三種方式執行。步驟4：處理結果集步驟5：釋放資源8.3JDBCConnectionconn=null;//mysql8.0Stringdriver="com.mysql.cj.jdbc.Driver";//驅動Stringurl="jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=UTC";//URL//mysql5.7//Stringdriver="com.mysql.jdbc.Driver";//StringURL="jdbc:mysql://localhost:3306/student";

Stringuser="root";//用戶名Stringpasswd="*****";//密碼

try{Class.forName(driver);conn=DriverManager.getConnection(url,user,passwd);}catch(Exceptione){ e.printStackTrace();}8.3JDBCtry{ stat=conn.createStatement(); ret=stat.executeQuery("select*froms");

while(ret.next()) { for(inti=1;i<=4;i++) System.out.print(ret.getString(i)+"");

System.out.println(""); } conn.close();}catch(SQLExceptione){ e.printStackTrace();}8.4ODBCODBC的概念微軟開發的用于連接各種關系型數據庫的函數庫，以動態鏈接庫的形式提供給程序使用，其目的是給出統一的編程接口，從而簡化數據庫應用程序的編寫。ODBC數據庫應用程序驅動程序管理器(DLL)SQLServer驅動程序Oracle驅動程序Foxpro驅動程序(DLL)Sybase驅動程序DB2驅動程序SQLServer數據源Oracle數據源Foxpro數據源Sybase數據源DB2數據源DBDBDBDBDB8.4ODBCODBC數據源的配置手工配置：控制面板-〉管理工具->ODBC數據源(64位)或32位（需要事先安裝該MySQL的ODBC驅動）8.4ODBC——C++調用ODBCCdatabasedatabase;CrecordsetrecordSet;if(!database.IsOpen()){database.Open(NULL,FALSE,FALSE,_T("ODBC;DSN=student_mysql64;UID=root;PWD=123456"));recordSet.m_pDatabase=&database;}CStringstrSQL;strSQL.Format("select*froms");recordSet.Open(CRecordset::forwardOnly,strSQL);for(inti=0;i<recordSet.GetRecordCount();i++){CStringtemp;recordSet.GetFieldValue("score_id",temp);recordSet.GetFieldValue("student_name",temp);recordSet.MoveNext();}recordSet.Close();database.Close();8.4ODBC—Python調用ODBCimportmysql.pyodbc8.5數據庫連接池應用實戰數據庫連接:昂貴（反復建立和釋放與數據庫的物理連接）數據庫連接池：負責分配、管理和釋放數據庫連接。需要使用時去連接池中取出一條空閑的連接，而不是新建一條連接。常見連接池：Java：

Druid、C3P0

HikariCP等Python:DBUtils.PooledDB等8.5數據庫連接池應用實戰8.6ADO.NET——Window窗口程序實戰ADO.NET的體系結構8.6ADO.NET——Window窗口程序實戰ADO.NET中幾個重要概念Connection對象：主要用于建立與數據源的活動連接Command對象：主要用來執行SQL語句DataAdaper對象：主要用來通過Dataset處理數據DataReader對象：主要用來讀取數據（快速不緩沖的順序訪問）5.DataSet:離線處理的核心，用來存儲數據6.DataTable:DataSet中以表的形式存儲數據8.6ADO.NET——Window窗口程序實戰8.7ORM技術—基于Django框架的Web程序實戰ORM（Object/RelationalMapping）即對象關系映射，是一種數據持久化技術。它在對象模型和關系型數據庫直接建立起對應關系。Dept.javadept-deptNo-deptName-locationDEPT(table)DEPTnumber(2)DNAMEVARCHAR(20)LOCVARCHAR(20)ORM類表8.7ORM技術—基于Django框架的Web程序實戰Django的MVT架構MVC(Model-View-Control)8.7ORM技術—基于Django框架的Web程序實戰model層：數據庫的每張表有一個繼承自models類的對應子類view層：new_book:Book類的實例，該類基類方法save()可以插入數據8.8其他數據庫訪問技術—libmysql例如：mysql提供lib：libmysql.dll

mysql_init() //初始化數據結構mysql_library_init()//初始化數據庫mysql_real_connect()//連接數據庫mysql_query() //查詢

mysql_store_result() //存儲結果mysql_fetch_row() //逐行處理mysql_free_result() //釋放資源mysql_close() //關閉連接直接利用DBMS提供的動態鏈接庫8.8其他數據庫訪問技術—嵌入式SQL什么是嵌入式SQL(EmbeddedSQL)?

SQL語句嵌入到其他高級語言中，這時高級語言被稱為(宿)主語言，這種方式下使用的SQL稱為嵌入式SQL。(靜態SQL/動態SQL)如何區分主語言與SQL語句？前綴：EXECSQL結束標志：隨主語言的不同而不同如：C語言中使用SQL：EXECSQL<SQL語句>;例：EXECSQLDROPTABLEStudent;常見的幾個數據庫系統預編譯器（命令）PostgreSQL:ecpg(.pgc->.c)SQLServer:nsqlprep.exe（.sqc->.c）Oralce:proc/cc++8.8其他數據庫訪問技術—嵌入式SQL例：帶有嵌入式SQL的一小段C程序:查詢并打印學生成績。EXECSQLINCLUDESQLCA;

/*(1)定義SQL通信區*/EXECSQLBEGINDECLARESECTION;/*(2)說明主變量*/

CHARSno(5);

CHARCno(3);

INTgrade;EXECSQLENDDECLARESECTION;main()

{

EXECSQLDECLAREC1CURSORFOR

/*(3)游標操作(定義游標)*/

SELECTSno,Cno,gradeFROMSC;

/*從SC表查詢Sno,Cno,Grade*/

EXECSQLOPENC1;

/*(4)游標操作（打開游標）*/

for(;;) {

EXECSQLFETCHC1INTO:Sno,:Cno,:Grade;

/*(5)游標操作（將當前數據放入主變量并推進游標指針）*/

if(sqlca.sqlcode<>SUCCESS)

/*(6)利用SQLCA中的狀態信息決定何時退出循環*/

break;printf("Sno:%s,Cno:%s,Grade:%d",Sno,Cno,Grade);/*打印查詢結果*/ }

EXECSQLCLOSEC1;

/*(7)游標操作（關閉游標）*/

EXECSQLDEALLOCATEC1；/*(8)游標操作（釋放游標）*/}合作QQ：243001978第九章

數據庫應用軟件測試Database西北工業大學NorthwesternPolytechnicalUniversity數據庫目錄9.1

實戰目標與準備9.2數據庫應用軟件測試概述9.3

數據庫設計驗證9.4功能測試9.5性能測試9.6負載測試與壓力測試9.7安全性測試9.1實戰目標與準備實戰目標本章目標是掌握數據庫應用程序的基本測試內容、方法以及工具等。實戰準備JMeter5.4：性能測試工具官網下載地址：/9.2數據庫應用軟件測試概述數據庫應用系統數據庫管理系統9.3數據庫設計驗證Student(Sno,Sdept,Mname,Cname,Grade)1.數據冗余太大：浪費大量的存儲空間例：每一個系主任的姓名重復出現2.修改復雜數據冗余，更新數據時，維護數據完整性代價大。例：某系更換系主任，系統必須修改與該系學生有關的每個元組3.插入異常該插的數據無法插入到表中。例：如果一個系剛成立，尚無學生，我們就無法把這個系及其系主任的信息存入數據庫。⒋刪除異常不該刪除的數據被刪掉了例：如果某個系的學生全部畢業了，我們在刪除該系學生信息的同時，把這個系及其系主任的信息也丟掉了9.3數據庫設計驗證數據庫設計步驟、成果與驗證觀點9.4功能測試功能測試通過測試驗證軟件的每個功能是否都按照用戶的需求進行了實現并且能正常使用。測試重點—用戶需求—WebApp,移動App測試方法

白盒測試+黑盒測試9.5性能測試性能測試：驗證系統的主要功能或組件的實際性能是否與用戶的性能需求一致。

數據庫應用軟件的性能測試：關注該軟件整體或者其中某些組件在規定時間內，響應用戶或系統輸入的能力，通常用請求響應時間，吞吐量等度量性能優劣。常見工具：LoadRunner、Jmeter、LocustDBMS基準性能測試：Sysbench、TPCC、TPCH等9.5性能測試基于Jmeter的測試示例：9.6負載和壓力測試負載測試（LoadTest）通過逐漸增加負載評估系統或者組件性能的測試方法，目的是觀察系統在各種不同的負載情況下是否都能夠正常工作。而性能測試通常是直接指定一個特定的負載進行測試。壓力測試（StressTest）評估系統或者組件處于或超過預期負載時的運行情況。重點關注系統在峰值負載或超出最大負載情況下的處理能力，在壓力級別逐漸增加時，系統性能應該是按照預期緩慢下降，但是不應發生系統崩潰的現象。9.7安全性測試SQL注入是指通過把SQL命令插入到Web表單提交或頁面請求等的查詢字符串中。select*frommemberwhereUID='"&request("ID")&"'andPasswd='"&request("Pwd")"'select*frommemberwhereUID='Admin‘--'AndPasswd=''假設猜測系統有Admin的用戶，構造以上SQL語句，由于SQL語言中“--”是注釋符號，其后的字符都會被當作注解，因此該攻擊者無需輸入密碼可直接進入系統9.7安全性測試針對SQL注入的測試構造專門的注入字符或者語句單獨測試-例如給可輸入的用戶名構造zhang'--借助漏洞掃描工具測試-掃描之后，進行對應的升級數據庫等動作合作QQ：243001978第十章

數據庫中的事務與鎖Database西北工業大學NorthwesternPolytechnicalUniversity數據庫目錄10.1

實戰目標與準備10.2事務概述與常用命令10.3

MySQL中ACID特性驗證10.4并發導致的數據不一致問題重現實戰10.5MySQL并發控制——鎖10.6封鎖協議與MySQL的隔離級別10.7基于隔離級別與鎖解決數據不一致問題10.8MySQL的死鎖10.9MySQL并發控制——MVCC10.1實戰目標與準備實戰目標本章的目標是在掌握了數據庫和基本表的創建和管理的基礎上，學習如何通過一定的工具或SQL命令來對基本表中的數據進行增、刪、改、查等基本操作。熟練掌握對基本表進行數據插入、修改和刪除的SQL語句。熟練掌握數據查詢的SQL語句（簡單查詢和復合查詢）。掌握對視圖的數據操作，并了解其與基本表數據操作的關系。掌握SQL語句查詢性能分析的基本知識。了解TPC-C基準數據庫10.2事務概述與常用命令1.

事務的概念一個數據庫操作序列一個不可分割的工作單位（要么全做，要么全不做）恢復和并發控制的基本單位在關系數據庫中的事務和程序：一個事務可以是一條SQL語句，一組SQL語句或整個程序。一個程序通常包含多個事務。10.2事務概述與常用命令2.

事務的特性(ACID)原子性（Atomicity）一致性（Consistency）隔離性（Isolation）持續性（Durability）要么全做，要么全不做事務執行的結果必須是使數據庫從一個一致性狀態變到另一個一致性狀態一個事務的執行不能被其他事務干擾事務一旦提交，它對數據庫中數據的改變就應該是永久性的。10.2事務概述與常用命令commit10.2事務概述與常用命令rollback10.2事務概述與常用命令starttransaction;insertintotvalues(1);insertintotvalues(2);savepoints1;insertintotvalues(3);insertintotvalues(4);savepoints2;insertintotvalues(5);insertintotvalues(6);select*fromt;rollbacktosavepoints2;select*fromt;savepoint10.2事務概述與常用命令功能例子查看當前活動事務select*frominformation_schema.innodb_trx;設置/查看是否自動提交set@@autocommit=0;setglobal@@autocommit=0;setautocommit=0;setglobalautocommit=0;showglobal/sessionvariableslike'autocommit’;(省略global/session修飾符時，默認為session范圍)設置/查看全局隔離級別setglobaltransactionisolationlevelrepeatableread;showglobalvariableslike'transaction_isolation';設置/查看當前連接(session)隔離級別settransactionisolationlevelrepeatableread;setsessiontransactionisolationlevelrepeatableread;showvariableslike'transaction_isolation’;showsessionvariableslike'transaction_isolation’;select@@transaction_isolation;10.3MySQL中ACID特性驗證事務的原子性與一致性示例10.3MySQL中ACID特性驗證事務的隔離性與持久性示例10.4并發導致的數據不一致問題重現實戰第一類丟失更新問題10.4并發導致的數據不一致問題重現實戰第二類丟失更新問題10.4并發導致的數據不一致問題重現實戰讀臟數據問題10.4并發導致的數據不一致問題重現實戰不可重復讀10.4并發導致的數據不一致問題重現實戰幻讀10.5MySQL并發控制——鎖10.5MySQL并發控制——鎖幾張與鎖相關的系統表：10.5MySQL并發控制——鎖InnoDB的支持的鎖鎖類別描述SharedandExclusiveLocks共享鎖/排他鎖IntentionLocks意向共享鎖/意向排他鎖RecordLocks行鎖GapLocks間隙鎖Next-KeyLocks臨鍵鎖InsertIntentionLocks插入意向鎖AUTO-INCLocks自增鎖PredicateLocksforSpatialIndexes空間索引使用，本處暫不討論10.5MySQL并發控制——鎖InnoDB：間隙鎖create

tablet(

idint(11)

not

null,

cint(11),

dint(11),

primary

key

(id),

keyc(c)

)

ENGINE

=

InnoDB;

insert

intotvalues(0,0,0),(5,5,5),(15,15,15)；(-∞,0)(0,5)(5,15)(15,+∞)0515間隙鎖沖突：是“往這個間隙中插入一個記錄”。select*fromtwherec=5forupdate;限制插入c值=5的左右兩個間隙鎖范圍內的值：[0,15]10.5MySQL并發控制——鎖InnoDB：間隙鎖insert

intot(id,c,d)values(0,0,0),(5,5,5),(15,15,15)；begin;

select*fromtwherec=5forupdate;primarykey(id),keyc(c))ENGINE

=

InnoDB;

T110.5MySQL并發控制——鎖InnoDB：間隙鎖10.5MySQL并發控制——鎖InnoDB：間隙鎖10.5MySQL并發控制——鎖查看鎖的方式：showengineinnodbstatus;MySQL8.0的系統表：innodb_trx,data_locks,innodb_lock_waits.

select*frominformation_schema.innodb_trxselect*fromperformance_schema.data_locks

select*fromsys.innodb_lock_waits;information_schema.innodb_trx表（事務信息表，trx_id）10.5MySQL并發控制——鎖查看鎖的方式：sys.innodb_lock_waits表performance_schema.data_locks表LOCK_TYPE:record/tableLOCK_MODE:S[,GAP],X[,GAP],IS[,GAP],IX[,GAP],AUTO_INC,UNKNOWNLOCK_STATUS:GRANTED/WAITINGLOCK_DATA:與鎖關聯的數據（表鎖時，該值為NULL）。10.6封鎖協議與MySQL的隔離級別10.6封鎖協議與MySQL的隔離級別封鎖協議-各級封鎖區別：什么操作需要申請封鎖以及何時釋放鎖-封鎖協議級別越高，一致性程度越高10.6封鎖協議與MySQL的隔離級別隔離級別讀未提交：ReadUncommitted讀已提交：ReadCommitted可重復讀：RepeatableRead可串行化：Serializable默認隔離級別：MySQL是可重復讀；KingBase、SQLServer、Oracle等為讀已提交注意：事務隔離級別并非越高越好MySQL常用:setsessiontransactionisolationlevelreaduncommittedselect@@transaction_isolation10.7基于隔離級別與鎖解決數據不一致問題10.8MySQL的死鎖10.9MySQL并發控制——MVCC多版本并發控制(MVCC):保留數據的多個版本。寫數據時，舊版本的數據并不刪除，寫一個新的版本，這樣并發的讀操作還能讀到舊版本的數據。讀寫不阻塞，并發度高。傳統封鎖方式MVCC在T1準備寫A的時候，

為A生成一個新版本（A’），T2事務不用等待，可繼續在A’上執行。T2準備提交時，確認事務T1是否已經完成。如果T1已經完成了，T2就可以放心地提交。如果T1還沒有完成，那么T2必須等待直到T1完成。10.9MySQL并發控制——MVCCMySQL中的InnoDB中實現MVCC寫新數據時，把舊數據移動到一個單獨的地方，如回滾段中其他讀數據時，從回滾段中把舊版本數據讀出來MySQLInnodb中每個表都包含三個隱藏字段rowid,trx_id和roll_pointer，MVCC主要使用了trx_id和roll_pointer的數據版本信息。10.9MySQL并發控制——MVCC合作QQ：243001978第十一章

數據庫的恢復技術Database西北工業大學NorthwesternPolytechnicalUniversity數據庫目錄11.1

實戰目標與準備11.2數據庫中的恢復技術概述11.3

MySQL中基于數據轉儲的備份與恢復實戰11.4MySQL中基于日志的手動恢復實戰11.5MySQL中的Redo與Undo日志11.1實戰目標與準備實戰目標本章的目標是在掌握了數據庫和基本表的創建和管理的基礎上，學習如何通過一定的工具或SQL命令來對基本表中的數據進行增、刪、改、查等基本操作。熟練掌握對基本表進行數據插入、修改和刪除的SQL語句。熟練掌握數據查詢的SQL語句（簡單查詢和復合查詢）。掌握對視圖的數據操作，并了解其與基本表數據操作的關系。掌握SQL語句查詢性能分析的基本知識。了解TPC-C基準數據庫11.2數據庫中的恢復技術概述故障是不可避免的：系統故障：計算機軟、硬件故障人為故障：操作員的失誤、惡意的破壞等。故障的影響：運行中事務非正常中斷，影響數據庫中數據的正確性。破壞數據庫，全部或部分丟失數據。數據庫的恢復

把數據庫從錯誤狀態恢復到某一已知的正確狀態(亦稱為一致狀態或完整狀態。恢復技術是衡量系統優劣的重要指標11.2數據庫中的恢復技術概述數據轉儲是指數據庫管理員定期將全部或者部分數據存儲到備份介質上，發生故障時從備份介質恢復的過程。通常DBA可以根據具體業務場景、涉及數據量大小等因素，設計合適的定時全量備份、增量備份等不同備份策略，例如每周進行全量備份，每日進行增量備份。日志系統：記錄事務對數據庫的更新操作，主要用于事務故障恢復和系統故障恢復。11.3MySQL中基于數據轉儲的備份與恢復實戰MySQL中的基于數據轉儲的數據備份恢復可以通過多種方式進行。官方提供的mysqldump、mysqlbackup等第三方工具，如xtrabackup，mydumper等。11.4MySQL中基于日志的手動恢復實戰日志類型日志標識默認說明一般查詢日志log不開啟記錄所有的查詢，占空間影響性能，默認不開(general-log)錯誤日志log-err開啟記錄mysql服務的錯誤慢查詢日志log-slow-queries開啟記錄執行時間超過long_query_time設定時間閾值(秒)的SQL語句，mysqldumpslow。二進制日志log-bin開啟Mysql的server層變更日志，主要用于記錄修改數據或有可能引起數據改變的mysql語句，可用于數據復制或者恢復。滾動文件，由.index文件管理。中繼日志relaylog不開啟主從復制時使用的日志事務日志innodb_log開啟InnoDB特有的事務日志redo和undo日志，幫助提高事務的效率。存儲引擎在修改表的數據時只需要修改其內存拷貝，再把修改行為記錄到持久在硬盤上的事務日志中，而不用每次都將修改的數據本身持久到磁盤。MySQL中日志文件類型：my.ini或者f11.4MySQL中基于日志的手動恢復實戰二進制日志：Binlog文件：用于進行數據恢復的日志（主從同步或者恢復）--顯示binlog的基本信息showvariableslike'log_bin‘;--確認binlog是否開啟showmasterlogs;--顯示所有的binlog文件showmasterstatus;--顯示最新的binlog的最后位置showbinlogevents;

--顯示所有的binlog--顯示指定binlogshowbinlogeventsin'THINKPAD_LINING-bin.000019’;--顯示指定binlog中某個位置之后的logshowbinlogeventsin'THINKPAD_LINING-bin.000019'from40080;showbinlogeventsin'THINKPAD_LINING-bin.000019'from40080limit10;--刷新binlog，生成一個新的binlog文件flushlogs;Binlog文件：用于進行事務恢復的日志Row:不記錄sql語句上下文相關信息，僅保存哪條記錄被修改。Statement：每一條會修改數據的sql都會記錄在binlog中。Mixedlevel:

是以上兩種的混合使用，一般的語句修改使用statement格式保存binlog，如一些函數;statement無法完成主從復制的操作，則采用row格式保存binlog.MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日志形式。11.4MySQL中基于日志的手動恢復實戰11.4MySQL中基于日志的手動恢復實戰Binlog文件：用于binlog進行事務恢復創建新的binlog日志文件執行常規SQL數據操作（含創建，增刪改操作）從日志中找回待恢復之前的SQL語句，導出為test000022.sql1.mysqlbinlog.exemysql-bin.000022>test_000022.txt2.在txt日志中查找待恢復（如DROPTABLE）日志的位置(該語句的at2413)3.導出binlog日志中'DROPTABLE'之前的SQL語句mysqlbinlogmysql-bin.000022-ddb1--skip-gtids--stop-position=2413>test000022.sql在mysql中執行以上SQL文件flushlogs;showmasterstatus;假設：最新：mysql-bin.000022sourceC:\ProgramData\MySQL\MySQLServer8.0\Data\test000022.sql11.4MySQL中基于日志的手動恢復實戰Binlog文件：(輸出為txt文件）Window的日志默認路徑：C:\ProgramData\MySQL\MySQLServer8.0\Data11.5MySQL中的Redo與Undo日志MySQL的InnoDB存儲引擎架構（引自MySQL官網）11.5MySQL中的Redo與Undo日志redolog：物理日志，提升事務持久化處理的性能。只記錄事務對數據頁做了哪些修改。在磁盤上由名為ib_logfile0和ib_logfile1。包含兩部分：先寫內存

->

后續某個時間一次性寫多條到磁盤。內存中的日志緩沖（redologbuffer，易失）磁盤上的日志文件（redologfile，穩定）undolog：邏輯日志，用于保證數據的原子性。保存事務發生之前的數據的一個版本，可以用于回滾。是MVCC(多版本控制)實現的關鍵。/doc/dev/mysql-server/8.0.11/PAGE_INNODB_REDO_LOG.html合作QQ：243001978第十二章

面向云數據庫的運維DatabaseSystem西北工業大學NorthwesternPolytechnicalUniversity數據庫系統李寧目錄12.1數據庫的運維任務12.2.云數據庫管理12.3.云DBA的智能運維數據庫的運維任務環境部署與維護備份與恢復數據庫的重組與重構12.1數據庫的運維任務監控與診斷目錄12.1數據庫的運維任務12.2.云數據庫管理12.3.云DBA的智能運維12.2云數據庫管理云數據庫實例整體管理備份管理參數管理任務中心回收站單個云數據庫實例管理查看監控指標、登錄、轉按需（按需與包月可以互轉）、創建只讀購買相同配置、磁盤擴容、規格變更、創建備份、參數修改、重置密碼、重啟實例與退訂12.2云數據庫管理DAS數據管理服務庫管理SQL操作導入導出結構方案目錄12.1數據庫的運維任務12.2.云數據庫管理12.3.云DBA的智能運維12.3云DBA的智能運維SQL診斷日報診斷12.3云DBA的智能運維SQL分析慢SQL合作QQ：243001978第十三章

數據庫應用開發綜合實戰案例DatabaseSystem西北工業大學NorthwesternPolytechnicalUniversity數據庫系統李寧目錄1.選題背景2.需求分析3.數據庫設計4.數據庫實施5.數據庫應用程序設計6.數據庫應用程序開發7.數據庫應用系統運行1.選題背景源自真實場景：數據庫實驗教學的痛點學生提交大量SQL作業增大教師批閱工作量SQL語句寫法靈活多樣導致批閱難度較大作業批閱結果可能也無法及時反饋給學生開發一個高效的SQL-OJ（OnlineJudge）系統以提高數據庫課程的教學效率和效果。目錄1.選題背景2.需求分析3.數據庫設計4.數據庫實施5.數據庫應用程序設計6.數據庫應用程序開發7.數據庫應用系統運行2.需求調研——方法

調查組織機構/用戶調查各個部門業務活動明確新系統的各種要求確定新系統的邊界調查的方法跟班作業開調查會專人介紹調查表查閱記錄其他信息需求+處理需求+非功能需求2.需求調研案例——MYSQL-OJ數據需求用戶信息題庫與題目信息試卷信息練習或考試信息答題信息功能需求教師：班級管理、班內成員管理、題庫/題目/試卷/考試管理、統計信息學生：注冊、個人信息管理、考試答題功能、儀表盤管理員：學校管理、教師用戶管理非功能需求安全性需求:權限控制性能需求：支持1000人同時在線答題，題目3秒內提交到系統分權限、管數據、定功能需求調研結果的分析和表達方法數據流圖數據字典數據項數據結構數據流數據存儲處理過程2.需求調研——結果表達信息需求處理需求完整性需求安全性需求性能需求其他2.需求調研案例——MYSQL-OJ數據流圖2.需求調研案例——MYSQL-OJ數據字典：以該系統服務100所學校，10年為例數據處理數據流數據存儲目錄1.選題背景2.需求分析3.數據庫設計4.數據庫實施5.數據庫應用程序設計6.數據庫應用程序開發7.數據庫應用系統運行3.數據庫設計概念結構設計：ER圖設計邏輯結構設計:ER圖轉換成關系模式安全性與完整性設計：加密、防注入、約束、觸發器等物理結構設計：存儲與存取，重點是索引物理數據庫3.1概念結構設計數據抽象，局部視圖設計視圖集成DFDDD總E-R圖邏輯結構設計返回用戶，征求意見到滿意為止需求分析分E-R圖

1)抽象數據+設計局部視圖

2)集成局部視圖，得到全體的概念結構3.1概念設計案例—MYSQL-OJ

語義描述與數據抽象用戶相關實體：學校、班級、用戶（管理員、教師、學生）考試相關實體：題庫、題目、試卷、考試或者練習活動根據現實世界與應用需求分析，實體之間語義描述如下：一個學校擁有多個班級，一個班級只能屬于某個學校；一個班級有多名學生，一個學生只能屬于一個班級。系統可以有多個管理員，任意一個管理員可以增刪改所有學校、教師的基本信息；每位教師管理所負責班級的學生信息。一位教師可以創建多個題庫（題庫指一個事先準備好的數據庫，可包含多張表以及部分數據），每個題庫只能被一位老師創建。基于一個題庫可以出多道題目，每個題目只能屬于某一個題庫。每位教師可以出若干道題目。一套試卷可以由任意多道題目組成，相同題目可出現在多套試卷中，且相同題目在不同試卷中分值可以不同。每位教師可給所負責的一個或多個班級，將任意一套可訪問的試卷發布為一次考練活動。學生可以作答自己可見的未過期考練活動。3.1概念設計案例—MYSQL-OJ

實體屬性描述3.1概念設計案例—MYSQL-OJ系統總ER圖（12個實體+14個聯系）分ER圖3.2邏輯結構設計—ER圖轉換

邏輯結構設計：ER圖轉換為邏輯結構基本轉換實體與屬性轉換1對1聯系：合并到任意1端[強制參與]或單獨轉成新關系1對多轉換：合并到N端或單獨成新關系多對多轉換：聯系轉換成單獨成新關系特殊轉換：復合屬性、弱實體、ISA等設計優化范式理論判斷與分解基于應用特征分析的分解與合并外模式設計3.2邏輯設計案例ER圖中的實體轉換ER圖中的聯系轉換3.2邏輯模式優化案例1.模式優化：以Exam、Exam_class為例分析。其他關系均滿足BCNF。1)Exam(考試ID,考試名,考試描述,開始時間,結束時間,活躍狀態,是否公布答案,教師ID,試卷ID)：該關系的碼是考試ID，不存在非主屬性和主屬性（考試ID）對碼的部分函數依賴和傳遞函數依賴，因此滿足BCNF。2）Exam_class(考試ID(FK),班級ID(FK),發布時間)：假設允許把某次考試給某個班級發布多次，則碼中需要加入發布時間。由此該關系為全碼，無非主屬性，滿足BCNF。2.其他優化（反范式化設計、分解或合并等）

題目名：該系統中教師身份登錄時，以班級為單位進行作答統計信息，在訪問題

目的作答記錄表時，會頻繁使用題目名稱，因此在該表中設計如下的冗余題目名。

coding_exam_ques_answer_rec：考試題目記錄ID,學生ID,題目ID,考試ID,

提交答案,答案正確性,最新提交時間,提交次數,本題得分,題目名。3.外模式設計

創建一個教師信息視圖：Teacher，僅包含教師ID和教師姓名即可。3.3安全性完整性設計安全性權限控制（管理員、教師、學生各自的權限設計）數據加密（用戶的密碼）防SQL注入等（ORM框架等）完整性完整性約束實體完整性示例：

User_Student表的student_id為主鍵。參照完整性示例：

User_Student表的class_id為外鍵。用戶自定義完整性：User_User表的priority取值：0:學生,1:教師,2:管理員。觸發器T1：為了避免統計功能中的實時統計給系統帶來過大壓力，設計了觸發器：在完成某個學生的判卷后更新Coding_ExamAnswerRec表的成績字段時，自動更新單獨設計的統計信息表Coding_Stat中相關信息。3.4物理結構設計1.確定數據庫物理存儲結構確定數據庫物理結構的內容1.確定數據的存放位置和存儲結構2.確定系統配置影響數據存放位置和存儲結構的因素1.硬件環境2.應用需求3.存取時間4.存儲空間利用率5.維護代價例如：將表和索引放在不同的磁盤（機械盤和SSD盤）；日志和數據分開；分區數據根據應用情況將易變部分與穩定部分、存取頻率較高部分與存取頻率較低部分分開存放，確定熱點數據，以提高系統性能。3.4物理結構設計2.確定數據庫物理存取方法哪些屬性列建立索引（啟發式規則）查詢條件聚集函數連接條件建立什么樣的索引聚集索引（主鍵索引）/二級索引（非主鍵索引）B+樹索引/Hash索引唯一索引單一索引/復合索引全文索引3.4物理設計案例—MYSQL-OJ數據存儲：

本例中，在數據存儲方面，為了提高系統的高可用性，在云數據庫實例創建時，選擇了主備架構，該架構可以定期備份主庫的數據，防止數據丟失。其他采用默認。數據存取

結合系統具體的應用功能需求，列出系統中可能相對頻繁執行的查詢需求，然后逐一考慮索引設計。系統中每個人僅能看到自己或者自己關聯班級的相關信息，學生ID、教師ID、班級ID經常出現在連接條件中。答題功能中，頻繁使用考試ID、試卷ID、題目ID等屬性，但因為這些字段都有主鍵索引，所以無需額外創建索引。學生儀表盤的統計功能中，需統計該學生所有考試的總提交次數。該功能將對coding_exam_ques_answer_rec表的提交次數做求和計算，該表數據量較大，因此按照索引設計的啟發式規則，對頻繁使用聚集函數的字段設計復合索引（學生ID，提交次數）。3.4數據庫設計產出成果物最終形成共13張表(含視圖）3.4數據庫設計產出成果物最終形成共13張表(含視圖）3.4數據庫設計產出成果物目錄1.選題背景2.需求分析3.數據庫設計4.數據庫實施5.數據庫應用程序設計6.數據庫應用程序開發7.數據庫應用系統運行4.數據庫實施創建數據庫表手動創建DDL語句基于建模工具生成DDL語句加載數據測試數據：造數腳本或者造數工具

（華為云數據庫、存儲過程、python\sh腳本等）生產數據：如果是已有庫中有數據

（數據導入導出、數據遷移工具等，如kettle等）4.數據庫實施造數腳本程序例子：python+MYSQL：loaddataLOADDATAINFILE'C:/ProgramData/MySQL/MySQLServer8.0/Uploads/userinfo.csv'INTOTABLEuserinfofieldsterminatedby',';目錄1.選題背景2.需求分析3.數據庫設計4.數據庫實施5.數據庫應用程序設計6.數據庫應用程序開發7.數據庫應用系統運行5.數據庫應用程序設計功能設計

——確定功能模塊可以用思維導圖、模塊圖等形式等表示5.數據庫應用程序設計功能設計

——分模塊詳細描述5.數據庫應用程序設計功能設計

——分模塊詳細描述（簡潔版）簡要說明每個界面的功能5.數據庫應用程序設計功能設計

——分模塊詳細描述（詳細版）1.概要