数据库原理与开发技术 课件 8.1 MySQL的技术架构

第8章MySQL后台技术与Python编程本章主要内容：（1）MySQL的技术架构：四层架构、存储引擎、系统数据库、

日志、性能优化（2）掌握MySQL存储过程的编程，包括游标对象Cursor的使用（3）掌握MySQL触发器的编程（4）掌握Python对MySQL数据库的插入、修改、删除、查询操作8.1MySQL技术架构

MySQL的技术构架主要指数据库的物理结构和逻辑结构在技术上的实现方式。MySQL提供了多种存储引擎，并把不同的存储引擎作为插件可供用户选择。由于不同的存储引擎具有不同的物理结构和逻辑结构的处理方式，因此，MySQL的架构具备灵活性，MySQL的架构和行为会随着存储引擎的改变而改变。以Win10和MySQL8.0为例，MySQL安装成功后，其参数配置存储在文本文件“my.ini”中，该文件默认位于MySQL8.0的数据目录（DataDirectory）下：C:\ProgramData\MySQL\MySQLServer8.0\my.ini8.1MySQL技术架构my.ini8.1.1MySQL技术架构概述

MySQL基架主要分为四层，分别是网络连接层、服务层、存储引擎层和物理层。这四层大致包括如下九大模块组件，其结构如图8-1所示。（1）MySQL向外提供的交互接口（Connectors）。

（2）管理服务组件和工具组件（ManagementService&Utilities）。

（3）连接池组件（ConnectionPool）。

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

（5）查询分析器组件（Parser）。

（6）优化器组件（Optimizer）。

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

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

（9）物理文件（FileSystem）。8.1.1MySQL技术架构概述1.网络连接层2.服务层3.存储引擎层4.物理层8.1.2MySQL存储引擎存储引擎是数据存储、更新、查询，及建立索引等技术的实现方式。一般的数据库系统只有一种存储引擎，例如，Orcale、SQLServer等。

MySQL提供了多种数据库引擎，各种引擎的性能不相同，用户可以根据业务需要进行选择。MySQL之所以有多种存储引擎，是由于MySQL的开源性决定的。

从MySQL5.0开始，支持的存储引擎主要有：MyISAM、InnoDB、Memory、Marge、Archive等。

进入MySQLWorkbench，输入ShowEngines语句可以查看系统所支持的引擎类型，如图8-2所示。主要引擎及性能如下。8.1.2MySQL存储引擎8.1.2MySQL存储引擎

1.InnoDB存储引擎InnoDB

是事务数据库的首选引擎，它支持事务的ACID特性、支持行锁定、支持外键。InnoDB不创建目录，使用InnoDB创建基本表时，MySQL会在MySQL数据目录下自动创建一个名为*.ibd的10M大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的重做日志文件（redo），默认是2组，也可以设置3组。MySQL5.5之后，InnoDB为默认的存储引擎。InnoDB的优点是提供了良好的事务、并发控制和故障恢复功能，缺点是数据读写效率较低、占用空间较大。8.1.2MySQL存储引擎

2.MyISAM存储引擎MyISAM是MySQL常见的存储引擎，MySQL5.5之前的默认引擎。使用MyISAM创建表结构后，会生成3个文件。文件的名字为表名，扩展名为文件类型，其中*.frm为表的结构定义、*.myd（MYData）为表的数据存储文件、*.myi（MYIndex）为表的索引文件。

MyISAM的优点是占用空间小、数据处理速度快，缺点是不支持事务的完整性和并发性。8.1.2MySQL存储引擎3.Memory存储引擎Memory是MySQL中的特殊存储引擎。它使用存储在内存中的数据来建立基本表，且数据全部在内存中。

Memory引擎建立的每个基本表对应于磁盘上的一个文件，但该文件只保存表结构的定义，不保存数据，其数据全部放在内存中。

Memory引擎的优点是数据处理速度非常快，缺点是表的生命周期短，基本上一次性使用，其对内存容量要求高。因此实际中很少使用。

8.1.3MySQL中的系统数据库MySQL安装成功后自带的数据库，称为系统数据库，用户创建的数据库，称为用户数据库。每个系统数据库都有各自特殊的功能，其表现在，在MySQL用户数据库运行期间，系统数据库会自动记录一些必要的信息，以便于数据管理和查询优化。用户不能直接修改系统数据库中的内容，但可以查看。

下面以MySQL8.0版本为例，介绍4个系统数据库的主要内容和功能。

8.1.3MySQL中的系统数据库1.information_schema这个数据库记录了MySQL服务器所有数据库的信息。例如，数据库的名、数据库的表、访问权限、基本表的数据类型，数据库索引信息等。

8.1.3MySQL中的系统数据库1.information_schema其中，表global_variables记录了所有全局变量的取值情况，表hosts记录了当前所有用户电脑连接MySQL数据库的连接情况。

8.1.3MySQL中的系统数据库2.performance_schema这个数据库记录了数据库服务器性能参数，可用于监控服务器在一个较低级别的运行过程中的资源消耗、资源等待等情况。

8.1.3MySQL中的系统数据库3.sys这个数据库中所有的数据源来自：performance_schema。目标是把performance_schema的复杂度降低，让数据库管理员能更好的阅读这个库里的内容，让数据库管理员更快的了解DB的运行情况。例如：（1）谁使用了最多的资源？基于IP或是用户？

（2）大部分连接来自哪里及发送的SQL情况？

（3）机器执行多的SQL语句是什么样？

（4）哪个文件产生了最多的IO，它的IO模式是怎么样的？

（5）哪个表的IO最多？

（6）哪张表被访问过最多？

8.1.3MySQL中的系统数据库3.sys（7）哪些语句延迟比较严重？

（8）哪些SQL语句使用了磁盘临时表？

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

（10）每个库占用多少Bufferpool？

（11）每个连接分配多少内存？

（12）MySQL内部现在有多个线程在运行？以上这些问题，sys数据库都有记录。例如，该数据库中，有一张host_summary表（主机概要表），记录了如下信息：

8.1.3MySQL中的系统数据库

Host：

监听连接过的主机

Statements：

当前主机执行的语句总数

statement_latency：

语句等待时间

statement_avg_latency：执行语句平均延迟时间 table_scans：

表扫描次数

file_ios：

I/O事件请求的次数 file_io_latency：

文件I/O延迟 current_connections：

当前连接数

total_connections：

总连接数

unique_users：

连接过来的、不同的用户账号数

current_memory：

当前账户分配的内存

total_memory_allocated：该主机内存总数

8.1.3MySQL中的系统数据库4.mysql这是MySQL的核心数据库，类似于SQLServer中的master表，主要负责存储数据库的用户、权限设置、关键字等MySQL自己需要使用的控制和管理信息。

在该系统数据库中，存储账户权限信息的表有：user、db、tables_priv、columns_priv等。 user表是mysql中最重要的一个权限表，它记录了允许连接到服务器的账号信息。db表存储了用户对某个数据库的操作权限，tables_priv表存储了对表设置操作权限，columns_priv表存储了对表的列设置权限。8.1.4MySQL日志

MySQL日志记录了MySQL数据库日常操作和错误信息，为MySQL管理和优化提供必要的决策依据。当数据库遇到意外故障时，可以通过日志查看文件出错的原因，并且可以通过日志文件进行数据恢复。8.1.4MySQL日志

MySQL日志主要分为5类：（1）错误日志（errorlog）：记录MySQL服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息。

（2）查询日志（generallog）：记录建立的客户端连接和执行的语句。

（3）慢查询日志（slowlog）：记录执行时间超过指定时间的查询语句。

（4）二进制日志（binlog）：记录所有更改数据的语句，可用于数据复制。

（5）事务日志（redolog、undolog）：redolog是重做日志，记录事务执行后的状态，防止在发生故障的时间点，尚有脏页未写入磁盘。undolog是回滚日志，提供回滚操作。8.1.5MySQL数据备份与恢复（含数据迁移）

1.数据库备份进入

MySQLWorkbanch，点击“DataExport”（数据导出），选择要备份的数据库，再输入或选择要备份的路径，例如d:\stock_backup，如图8-11（a）、（b）所示。8.1.5MySQL数据备份与恢复（含数据迁移）

2.数据库恢复为便于数据库的转储和分析，利用备份的数据库文件，可以进行数据库恢复。

进入

MySQLWorkbanch，点击“DataImport”（数据导入），选择已备份数据库的文件夹，系统会自动识别备份的数据库名，点击：开始导入，如图8-13（a）所示。8.1.5MySQL数据备份与恢复（含数据迁移）

2.数据库恢复8.1.5MySQL数据备份与恢复（含数据迁移）

3.数据库迁移数据库迁移，就是把数据从一个系统移动到另一个系统上。