第八章

數(shù)據(jù)庫應(yīng)用程序開發(fā)Database西北工業(yè)大學(xué)NorthwesternPolytechnicalUniversity數(shù)據(jù)庫目錄8.1

實戰(zhàn)目標(biāo)與準(zhǔn)備8.2數(shù)據(jù)庫應(yīng)用軟件開發(fā)概述8.3

JDBC編程實戰(zhàn)8.4ODBC編程實戰(zhàn)8.5數(shù)據(jù)庫連接池應(yīng)用實戰(zhàn)8.6ADO.NET——Window窗口程序?qū)崙?zhàn)8.7ORM技術(shù)——基于Django框架的Web程序?qū)崙?zhàn)8.8其他數(shù)據(jù)庫訪問方法實戰(zhàn)8.1實戰(zhàn)目標(biāo)與準(zhǔn)備實戰(zhàn)目標(biāo)本章目標(biāo)是掌握數(shù)據(jù)庫應(yīng)用程序中的數(shù)據(jù)庫訪問技術(shù)，期望讀者能獨立開發(fā)一個基本的數(shù)據(jù)庫應(yīng)用程序，特別是其中的數(shù)據(jù)庫訪問部分。實戰(zhàn)準(zhǔn)備本機(jī)的應(yīng)用程序開發(fā)，需要根據(jù)自己選擇則的編程語言提前安裝好以下軟件：JDBC：JAVA開發(fā)環(huán)境ODBC：C++或者C#開發(fā)環(huán)境或者Python開發(fā)環(huán)境其他訪問數(shù)據(jù)庫方式：C++或者Python開發(fā)環(huán)境8.2數(shù)據(jù)庫應(yīng)用軟件開發(fā)概述8.3JDBC類名路徑備注驅(qū)動程序類java.sql.Driver由各數(shù)據(jù)庫廠商提供驅(qū)動程序管理類java.sql.DriverManager作用于應(yīng)用程序與驅(qū)動程序之間數(shù)據(jù)庫連接類java.sql.Connection用于建立與指定數(shù)據(jù)庫的連接靜態(tài)SQL語句執(zhí)行類java.sql.Statement用于執(zhí)行靜態(tài)SQL語句并返回結(jié)果動態(tài)SQL語句執(zhí)行類java.sql.PreparedStatement用于執(zhí)行含參SQL語句并返回結(jié)果存儲過程語句執(zhí)行類java.sql.CallableStatement用于執(zhí)行存儲過程語句并返回結(jié)果結(jié)果集處理類java.sql.ResultSet用于檢索結(jié)果集中的數(shù)據(jù)JDBC常用類JDBC開發(fā)涉及到的所有類都包含在java.sql包中不同的JDBC版本接口名和使用略有差異8.3JDBCJDBC操作數(shù)據(jù)庫的基本工作流程步驟1：加載驅(qū)動程序步驟2：建立與數(shù)據(jù)庫的連接定義連接的URL利用生成的URL建立與數(shù)據(jù)庫的連接步驟3：執(zhí)行SQL語句創(chuàng)建語句執(zhí)行類對象執(zhí)行SQL語句，可以通過executeQuery、executeUpdate、execute三種方式執(zhí)行。步驟4：處理結(jié)果集步驟5：釋放資源8.3JDBCConnectionconn=null;//mysql8.0Stringdriver="com.mysql.cj.jdbc.Driver";//驅(qū)動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的概念微軟開發(fā)的用于連接各種關(guān)系型數(shù)據(jù)庫的函數(shù)庫，以動態(tài)鏈接庫的形式提供給程序使用，其目的是給出統(tǒng)一的編程接口，從而簡化數(shù)據(jù)庫應(yīng)用程序的編寫。ODBC數(shù)據(jù)庫應(yīng)用程序驅(qū)動程序管理器(DLL)SQLServer驅(qū)動程序Oracle驅(qū)動程序Foxpro驅(qū)動程序(DLL)Sybase驅(qū)動程序DB2驅(qū)動程序SQLServer數(shù)據(jù)源Oracle數(shù)據(jù)源Foxpro數(shù)據(jù)源Sybase數(shù)據(jù)源DB2數(shù)據(jù)源DBDBDBDBDB8.4ODBCODBC數(shù)據(jù)源的配置手工配置：控制面板-〉管理工具->ODBC數(shù)據(jù)源(64位)或32位（需要事先安裝該MySQL的ODBC驅(qū)動）8.4ODBC——C++調(diào)用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調(diào)用ODBCimportmysql.pyodbc8.5數(shù)據(jù)庫連接池應(yīng)用實戰(zhàn)數(shù)據(jù)庫連接:昂貴（反復(fù)建立和釋放與數(shù)據(jù)庫的物理連接）數(shù)據(jù)庫連接池：負(fù)責(zé)分配、管理和釋放數(shù)據(jù)庫連接。需要使用時去連接池中取出一條空閑的連接，而不是新建一條連接。常見連接池：Java：

Druid、C3P0

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

mysql_init() //初始化數(shù)據(jù)結(jié)構(gòu)mysql_library_init()//初始化數(shù)據(jù)庫mysql_real_connect()//連接數(shù)據(jù)庫mysql_query() //查詢

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

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

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

CHARSno(5);

CHARCno(3);

INTgrade;EXECSQLENDDECLARESECTION;main()

{

EXECSQLDECLAREC1CURSORFOR

/*(3)游標(biāo)操作(定義游標(biāo))*/

SELECTSno,Cno,gradeFROMSC;

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

EXECSQLOPENC1;

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

for(;;) {

EXECSQLFETCHC1INTO:Sno,:Cno,:Grade;

/*(5)游標(biāo)操作（將當(dāng)前數(shù)據(jù)放入主變量并推進(jìn)游標(biāo)指針）*/

if(sqlca.sqlcode<>SUCCESS)

/*(6)利用SQLCA中的狀態(tài)信息決定何時退出循環(huán)*/

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

EXECSQLCLOSEC1;

/*(7)游標(biāo)操作（關(guān)閉游標(biāo)）*/

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

數(shù)據(jù)庫應(yīng)用軟件測試Database西北工業(yè)大學(xué)NorthwesternPolytechnicalUniversity數(shù)據(jù)庫目錄9.1

實戰(zhàn)目標(biāo)與準(zhǔn)備9.2數(shù)據(jù)庫應(yīng)用軟件測試概述9.3

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

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

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

數(shù)據(jù)庫中的事務(wù)與鎖Database西北工業(yè)大學(xué)NorthwesternPolytechnicalUniversity數(shù)據(jù)庫目錄10.1

實戰(zhàn)目標(biāo)與準(zhǔn)備10.2事務(wù)概述與常用命令10.3

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

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

事務(wù)的特性(ACID)原子性（Atomicity）一致性（Consistency）隔離性（Isolation）持續(xù)性（Durability）要么全做，要么全不做事務(wù)執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫從一個一致性狀態(tài)變到另一個一致性狀態(tài)一個事務(wù)的執(zhí)行不能被其他事務(wù)干擾事務(wù)一旦提交，它對數(shù)據(jù)庫中數(shù)據(jù)的改變就應(yīng)該是永久性的。10.2事務(wù)概述與常用命令commit10.2事務(wù)概述與常用命令rollback10.2事務(wù)概述與常用命令starttransaction;insertintotvalues(1);insertintotvalues(2);savepoints1;insertintotvalues(3);insertintotvalues(4);savepoints2;insertintotvalues(5);insertintotvalues(6);select*fromt;rollbacktosavepoints2;select*fromt;savepoint10.2事務(wù)概述與常用命令功能例子查看當(dāng)前活動事務(wù)select*frominformation_schema.innodb_trx;設(shè)置/查看是否自動提交set@@autocommit=0;setglobal@@autocommit=0;setautocommit=0;setglobalautocommit=0;showglobal/sessionvariableslike'autocommit’;(省略global/session修飾符時，默認(rèn)為session范圍)設(shè)置/查看全局隔離級別setglobaltransactionisolationlevelrepeatableread;showglobalvariableslike'transaction_isolation';設(shè)置/查看當(dāng)前連接(session)隔離級別settransactionisolationlevelrepeatableread;setsessiontransactionisolationlevelrepeatableread;showvariableslike'transaction_isolation’;showsessionvariableslike'transaction_isolation’;select@@transaction_isolation;10.3MySQL中ACID特性驗證事務(wù)的原子性與一致性示例10.3MySQL中ACID特性驗證事務(wù)的隔離性與持久性示例10.4并發(fā)導(dǎo)致的數(shù)據(jù)不一致問題重現(xiàn)實戰(zhàn)第一類丟失更新問題10.4并發(fā)導(dǎo)致的數(shù)據(jù)不一致問題重現(xiàn)實戰(zhàn)第二類丟失更新問題10.4并發(fā)導(dǎo)致的數(shù)據(jù)不一致問題重現(xiàn)實戰(zhàn)讀臟數(shù)據(jù)問題10.4并發(fā)導(dǎo)致的數(shù)據(jù)不一致問題重現(xiàn)實戰(zhàn)不可重復(fù)讀10.4并發(fā)導(dǎo)致的數(shù)據(jù)不一致問題重現(xiàn)實戰(zhàn)幻讀10.5MySQL并發(fā)控制——鎖10.5MySQL并發(fā)控制——鎖幾張與鎖相關(guān)的系統(tǒng)表：10.5MySQL并發(fā)控制——鎖InnoDB的支持的鎖鎖類別描述SharedandExclusiveLocks共享鎖/排他鎖IntentionLocks意向共享鎖/意向排他鎖RecordLocks行鎖GapLocks間隙鎖Next-KeyLocks臨鍵鎖InsertIntentionLocks插入意向鎖AUTO-INCLocks自增鎖PredicateLocksforSpatialIndexes空間索引使用，本處暫不討論10.5MySQL并發(fā)控制——鎖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的左右兩個間隙鎖范圍內(nèi)的值：[0,15]10.5MySQL并發(fā)控制——鎖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并發(fā)控制——鎖InnoDB：間隙鎖10.5MySQL并發(fā)控制——鎖InnoDB：間隙鎖10.5MySQL并發(fā)控制——鎖查看鎖的方式：showengineinnodbstatus;MySQL8.0的系統(tǒng)表：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表（事務(wù)信息表，trx_id）10.5MySQL并發(fā)控制——鎖查看鎖的方式：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:與鎖關(guān)聯(lián)的數(shù)據(jù)（表鎖時，該值為NULL）。10.6封鎖協(xié)議與MySQL的隔離級別10.6封鎖協(xié)議與MySQL的隔離級別封鎖協(xié)議-各級封鎖區(qū)別：什么操作需要申請封鎖以及何時釋放鎖-封鎖協(xié)議級別越高，一致性程度越高10.6封鎖協(xié)議與MySQL的隔離級別隔離級別讀未提交：ReadUncommitted讀已提交：ReadCommitted可重復(fù)讀：RepeatableRead可串行化：Serializable默認(rèn)隔離級別：MySQL是可重復(fù)讀；KingBase、SQLServer、Oracle等為讀已提交注意：事務(wù)隔離級別并非越高越好MySQL常用:setsessiontransactionisolationlevelreaduncommittedselect@@transaction_isolation10.7基于隔離級別與鎖解決數(shù)據(jù)不一致問題10.8MySQL的死鎖10.9MySQL并發(fā)控制——MVCC多版本并發(fā)控制(MVCC):保留數(shù)據(jù)的多個版本。寫數(shù)據(jù)時，舊版本的數(shù)據(jù)并不刪除，寫一個新的版本，這樣并發(fā)的讀操作還能讀到舊版本的數(shù)據(jù)。讀寫不阻塞，并發(fā)度高。傳統(tǒng)封鎖方式MVCC在T1準(zhǔn)備寫A的時候，

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

數(shù)據(jù)庫的恢復(fù)技術(shù)Database西北工業(yè)大學(xué)NorthwesternPolytechnicalUniversity數(shù)據(jù)庫目錄11.1

實戰(zhàn)目標(biāo)與準(zhǔn)備11.2數(shù)據(jù)庫中的恢復(fù)技術(shù)概述11.3

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

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

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

->

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

面向云數(shù)據(jù)庫的運維DatabaseSystem西北工業(yè)大學(xué)NorthwesternPolytechnicalUniversity數(shù)據(jù)庫系統(tǒng)李寧目錄12.1數(shù)據(jù)庫的運維任務(wù)12.2.云數(shù)據(jù)庫管理12.3.云DBA的智能運維數(shù)據(jù)庫的運維任務(wù)環(huán)境部署與維護(hù)備份與恢復(fù)數(shù)據(jù)庫的重組與重構(gòu)12.1數(shù)據(jù)庫的運維任務(wù)監(jiān)控與診斷目錄12.1數(shù)據(jù)庫的運維任務(wù)12.2.云數(shù)據(jù)庫管理12.3.云DBA的智能運維12.2云數(shù)據(jù)庫管理云數(shù)據(jù)庫實例整體管理備份管理參數(shù)管理任務(wù)中心回收站單個云數(shù)據(jù)庫實例管理查看監(jiān)控指標(biāo)、登錄、轉(zhuǎn)按需（按需與包月可以互轉(zhuǎn)）、創(chuàng)建只讀購買相同配置、磁盤擴(kuò)容、規(guī)格變更、創(chuàng)建備份、參數(shù)修改、重置密碼、重啟實例與退訂12.2云數(shù)據(jù)庫管理DAS數(shù)據(jù)管理服務(wù)庫管理SQL操作導(dǎo)入導(dǎo)出結(jié)構(gòu)方案目錄12.1數(shù)據(jù)庫的運維任務(wù)12.2.云數(shù)據(jù)庫管理12.3.云DBA的智能運維12.3云DBA的智能運維SQL診斷日報診斷12.3云DBA的智能運維SQL分析慢SQL合作QQ：243001978第十三章

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

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

1)抽象數(shù)據(jù)+設(shè)計局部視圖

2)集成局部視圖，得到全體的概念結(jié)構(gòu)3.1概念設(shè)計案例—MYSQL-OJ

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

實體屬性描述3.1概念設(shè)計案例—MYSQL-OJ系統(tǒng)總ER圖（12個實體+14個聯(lián)系）分ER圖3.2邏輯結(jié)構(gòu)設(shè)計—ER圖轉(zhuǎn)換

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

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

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

coding_exam_ques_answer_rec：考試題目記錄ID,學(xué)生ID,題目ID,考試ID,

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

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

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

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

本例中，在數(shù)據(jù)存儲方面，為了提高系統(tǒng)的高可用性，在云數(shù)據(jù)庫實例創(chuàng)建時，選擇了主備架構(gòu)，該架構(gòu)可以定期備份主庫的數(shù)據(jù)，防止數(shù)據(jù)丟失。其他采用默認(rèn)。數(shù)據(jù)存取

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

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

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

——確定功能模塊可以用思維導(dǎo)圖、模塊圖等形式等表示5.數(shù)據(jù)庫應(yīng)用程序設(shè)計功能設(shè)計

——分模塊詳細(xì)描述5.數(shù)據(jù)庫應(yīng)用程序設(shè)計功能設(shè)計

——分模塊詳細(xì)描述（簡潔版）簡要說明每個界面的功能5.數(shù)據(jù)庫應(yīng)用程序設(shè)計功能設(shè)計

——分模塊詳細(xì)描述（詳細(xì)版）1.概要