數(shù)據(jù)庫原理與開發(fā)技術(shù) 課件 8.1 MySQL的技術(shù)架構(gòu)

第8章MySQL后臺技術(shù)與Python編程本章主要內(nèi)容：（1）MySQL的技術(shù)架構(gòu)：四層架構(gòu)、存儲引擎、系統(tǒng)數(shù)據(jù)庫、

日志、性能優(yōu)化（2）掌握MySQL存儲過程的編程，包括游標(biāo)對象Cursor的使用（3）掌握MySQL觸發(fā)器的編程（4）掌握Python對MySQL數(shù)據(jù)庫的插入、修改、刪除、查詢操作8.1MySQL技術(shù)架構(gòu)

MySQL的技術(shù)構(gòu)架主要指數(shù)據(jù)庫的物理結(jié)構(gòu)和邏輯結(jié)構(gòu)在技術(shù)上的實現(xiàn)方式。MySQL提供了多種存儲引擎，并把不同的存儲引擎作為插件可供用戶選擇。由于不同的存儲引擎具有不同的物理結(jié)構(gòu)和邏輯結(jié)構(gòu)的處理方式，因此，MySQL的架構(gòu)具備靈活性，MySQL的架構(gòu)和行為會隨著存儲引擎的改變而改變。以Win10和MySQL8.0為例，MySQL安裝成功后，其參數(shù)配置存儲在文本文件“my.ini”中，該文件默認(rèn)位于MySQL8.0的數(shù)據(jù)目錄（DataDirectory）下：C:\ProgramData\MySQL\MySQLServer8.0\my.ini8.1MySQL技術(shù)架構(gòu)my.ini8.1.1MySQL技術(shù)架構(gòu)概述

MySQL基架主要分為四層，分別是網(wǎng)絡(luò)連接層、服務(wù)層、存儲引擎層和物理層。這四層大致包括如下九大模塊組件，其結(jié)構(gòu)如圖8-1所示。（1）MySQL向外提供的交互接口（Connectors）。

（2）管理服務(wù)組件和工具組件（ManagementService&Utilities）。

（3）連接池組件（ConnectionPool）。

（4）SQL接口組件（SQLInterface）。

（5）查詢分析器組件（Parser）。

（6）優(yōu)化器組件（Optimizer）。

（7）緩存主件（Caches&Buffers）。

（8）插件式存儲引擎（PluggableStorageEngines）。

（9）物理文件（FileSystem）。8.1.1MySQL技術(shù)架構(gòu)概述1.網(wǎng)絡(luò)連接層2.服務(wù)層3.存儲引擎層4.物理層8.1.2MySQL存儲引擎存儲引擎是數(shù)據(jù)存儲、更新、查詢，及建立索引等技術(shù)的實現(xiàn)方式。一般的數(shù)據(jù)庫系統(tǒng)只有一種存儲引擎，例如，Orcale、SQLServer等。

MySQL提供了多種數(shù)據(jù)庫引擎，各種引擎的性能不相同，用戶可以根據(jù)業(yè)務(wù)需要進(jìn)行選擇。MySQL之所以有多種存儲引擎，是由于MySQL的開源性決定的。

從MySQL5.0開始，支持的存儲引擎主要有：MyISAM、InnoDB、Memory、Marge、Archive等。

進(jìn)入MySQLWorkbench，輸入ShowEngines語句可以查看系統(tǒng)所支持的引擎類型，如圖8-2所示。主要引擎及性能如下。8.1.2MySQL存儲引擎8.1.2MySQL存儲引擎

1.InnoDB存儲引擎InnoDB

是事務(wù)數(shù)據(jù)庫的首選引擎，它支持事務(wù)的ACID特性、支持行鎖定、支持外鍵。InnoDB不創(chuàng)建目錄，使用InnoDB創(chuàng)建基本表時，MySQL會在MySQL數(shù)據(jù)目錄下自動創(chuàng)建一個名為*.ibd的10M大小的自動擴(kuò)展數(shù)據(jù)文件，以及兩個名為ib_logfile0和ib_logfile1的重做日志文件（redo），默認(rèn)是2組，也可以設(shè)置3組。MySQL5.5之后，InnoDB為默認(rèn)的存儲引擎。InnoDB的優(yōu)點(diǎn)是提供了良好的事務(wù)、并發(fā)控制和故障恢復(fù)功能，缺點(diǎn)是數(shù)據(jù)讀寫效率較低、占用空間較大。8.1.2MySQL存儲引擎

2.MyISAM存儲引擎MyISAM是MySQL常見的存儲引擎，MySQL5.5之前的默認(rèn)引擎。使用MyISAM創(chuàng)建表結(jié)構(gòu)后，會生成3個文件。文件的名字為表名，擴(kuò)展名為文件類型，其中*.frm為表的結(jié)構(gòu)定義、*.myd（MYData）為表的數(shù)據(jù)存儲文件、*.myi（MYIndex）為表的索引文件。

MyISAM的優(yōu)點(diǎn)是占用空間小、數(shù)據(jù)處理速度快，缺點(diǎn)是不支持事務(wù)的完整性和并發(fā)性。8.1.2MySQL存儲引擎3.Memory存儲引擎Memory是MySQL中的特殊存儲引擎。它使用存儲在內(nèi)存中的數(shù)據(jù)來建立基本表，且數(shù)據(jù)全部在內(nèi)存中。

Memory引擎建立的每個基本表對應(yīng)于磁盤上的一個文件，但該文件只保存表結(jié)構(gòu)的定義，不保存數(shù)據(jù)，其數(shù)據(jù)全部放在內(nèi)存中。

Memory引擎的優(yōu)點(diǎn)是數(shù)據(jù)處理速度非?？?，缺點(diǎn)是表的生命周期短，基本上一次性使用，其對內(nèi)存容量要求高。因此實際中很少使用。

8.1.3MySQL中的系統(tǒng)數(shù)據(jù)庫MySQL安裝成功后自帶的數(shù)據(jù)庫，稱為系統(tǒng)數(shù)據(jù)庫，用戶創(chuàng)建的數(shù)據(jù)庫，稱為用戶數(shù)據(jù)庫。每個系統(tǒng)數(shù)據(jù)庫都有各自特殊的功能，其表現(xiàn)在，在MySQL用戶數(shù)據(jù)庫運(yùn)行期間，系統(tǒng)數(shù)據(jù)庫會自動記錄一些必要的信息，以便于數(shù)據(jù)管理和查詢優(yōu)化。用戶不能直接修改系統(tǒng)數(shù)據(jù)庫中的內(nèi)容，但可以查看。

下面以MySQL8.0版本為例，介紹4個系統(tǒng)數(shù)據(jù)庫的主要內(nèi)容和功能。

8.1.3MySQL中的系統(tǒng)數(shù)據(jù)庫1.information_schema這個數(shù)據(jù)庫記錄了MySQL服務(wù)器所有數(shù)據(jù)庫的信息。例如，數(shù)據(jù)庫的名、數(shù)據(jù)庫的表、訪問權(quán)限、基本表的數(shù)據(jù)類型，數(shù)據(jù)庫索引信息等。

8.1.3MySQL中的系統(tǒng)數(shù)據(jù)庫1.information_schema其中，表global_variables記錄了所有全局變量的取值情況，表hosts記錄了當(dāng)前所有用戶電腦連接MySQL數(shù)據(jù)庫的連接情況。

8.1.3MySQL中的系統(tǒng)數(shù)據(jù)庫2.performance_schema這個數(shù)據(jù)庫記錄了數(shù)據(jù)庫服務(wù)器性能參數(shù)，可用于監(jiān)控服務(wù)器在一個較低級別的運(yùn)行過程中的資源消耗、資源等待等情況。

8.1.3MySQL中的系統(tǒng)數(shù)據(jù)庫3.sys這個數(shù)據(jù)庫中所有的數(shù)據(jù)源來自：performance_schema。目標(biāo)是把performance_schema的復(fù)雜度降低，讓數(shù)據(jù)庫管理員能更好的閱讀這個庫里的內(nèi)容，讓數(shù)據(jù)庫管理員更快的了解DB的運(yùn)行情況。例如：（1）誰使用了最多的資源？基于IP或是用戶？

（2）大部分連接來自哪里及發(fā)送的SQL情況？

（3）機(jī)器執(zhí)行多的SQL語句是什么樣？

（4）哪個文件產(chǎn)生了最多的IO，它的IO模式是怎么樣的？

（5）哪個表的IO最多？

（6）哪張表被訪問過最多？

8.1.3MySQL中的系統(tǒng)數(shù)據(jù)庫3.sys（7）哪些語句延遲比較嚴(yán)重？

（8）哪些SQL語句使用了磁盤臨時表？

（9）哪張表占用了最多的bufferpool？

（10）每個庫占用多少Bufferpool？

（11）每個連接分配多少內(nèi)存？

（12）MySQL內(nèi)部現(xiàn)在有多個線程在運(yùn)行？以上這些問題，sys數(shù)據(jù)庫都有記錄。例如，該數(shù)據(jù)庫中，有一張host_summary表（主機(jī)概要表），記錄了如下信息：

8.1.3MySQL中的系統(tǒng)數(shù)據(jù)庫

Host：

監(jiān)聽連接過的主機(jī)

Statements：

當(dāng)前主機(jī)執(zhí)行的語句總數(shù)

statement_latency：

語句等待時間

statement_avg_latency：執(zhí)行語句平均延遲時間 table_scans：

表掃描次數(shù)

file_ios：

I/O事件請求的次數(shù) file_io_latency：

文件I/O延遲 current_connections：

當(dāng)前連接數(shù)

total_connections：

總連接數(shù)

unique_users：

連接過來的、不同的用戶賬號數(shù)

current_memory：

當(dāng)前賬戶分配的內(nèi)存

total_memory_allocated：該主機(jī)內(nèi)存總數(shù)

8.1.3MySQL中的系統(tǒng)數(shù)據(jù)庫4.mysql這是MySQL的核心數(shù)據(jù)庫，類似于SQLServer中的master表，主要負(fù)責(zé)存儲數(shù)據(jù)庫的用戶、權(quán)限設(shè)置、關(guān)鍵字等MySQL自己需要使用的控制和管理信息。

在該系統(tǒng)數(shù)據(jù)庫中，存儲賬戶權(quán)限信息的表有：user、db、tables_priv、columns_priv等。 user表是mysql中最重要的一個權(quán)限表，它記錄了允許連接到服務(wù)器的賬號信息。db表存儲了用戶對某個數(shù)據(jù)庫的操作權(quán)限，tables_priv表存儲了對表設(shè)置操作權(quán)限，columns_priv表存儲了對表的列設(shè)置權(quán)限。8.1.4MySQL日志

MySQL日志記錄了MySQL數(shù)據(jù)庫日常操作和錯誤信息，為MySQL管理和優(yōu)化提供必要的決策依據(jù)。當(dāng)數(shù)據(jù)庫遇到意外故障時，可以通過日志查看文件出錯的原因，并且可以通過日志文件進(jìn)行數(shù)據(jù)恢復(fù)。8.1.4MySQL日志

MySQL日志主要分為5類：（1）錯誤日志（errorlog）：記錄MySQL服務(wù)的啟停時正確和錯誤的信息，還記錄啟動、停止、運(yùn)行過程中的錯誤信息。

（2）查詢?nèi)罩荆╣enerallog）：記錄建立的客戶端連接和執(zhí)行的語句。

（3）慢查詢?nèi)罩荆╯lowlog）：記錄執(zhí)行時間超過指定時間的查詢語句。

（4）二進(jìn)制日志（binlog）：記錄所有更改數(shù)據(jù)的語句，可用于數(shù)據(jù)復(fù)制。

（5）事務(wù)日志（redolog、undolog）：redolog是重做日志，記錄事務(wù)執(zhí)行后的狀態(tài)，防止在發(fā)生故障的時間點(diǎn)，尚有臟頁未寫入磁盤。undolog是回滾日志，提供回滾操作。8.1.5MySQL數(shù)據(jù)備份與恢復(fù)（含數(shù)據(jù)遷移）

1.數(shù)據(jù)庫備份進(jìn)入

MySQLWorkbanch，點(diǎn)擊“DataExport”（數(shù)據(jù)導(dǎo)出），選擇要備份的數(shù)據(jù)庫，再輸入或選擇要備份的路徑，例如d:\stock_backup，如圖8-11（a）、（b）所示。8.1.5MySQL數(shù)據(jù)備份與恢復(fù)（含數(shù)據(jù)遷移）

2.數(shù)據(jù)庫恢復(fù)為便于數(shù)據(jù)庫的轉(zhuǎn)儲和分析，利用備份的數(shù)據(jù)庫文件，可以進(jìn)行數(shù)據(jù)庫恢復(fù)。

進(jìn)入

MySQLWorkbanch，點(diǎn)擊“DataImport”（數(shù)據(jù)導(dǎo)入），選擇已備份數(shù)據(jù)庫的文件夾，系統(tǒng)會自動識別備份的數(shù)據(jù)庫名，點(diǎn)擊：開始導(dǎo)入，如圖8-13（a）所示。8.1.5MySQL數(shù)據(jù)備份與恢復(fù)（含數(shù)據(jù)遷移）

2.數(shù)據(jù)庫恢復(fù)8.1.5MySQL數(shù)據(jù)備份與恢復(fù)（含數(shù)據(jù)遷移）

3.數(shù)據(jù)庫遷移數(shù)據(jù)庫遷移，就是把數(shù)據(jù)從一個系統(tǒng)移動到另一個系統(tǒng)上。