books

《MySQL技术精粹》

架构、高级特性、性能优化与集群实战

date: 2022-07-04
address: NC
author: 吴第广

第一章 MySQL 架构介绍

从 MySQL 的逻辑组成、数据库存储数据引擎，以及 MySQL 相关工具方面介绍 MySQL 的整体架构。

1.1 MySQL 架构

MySQL 服务器由 SQL 层和存储引擎层构成。

20220630124020

SQL 层各模块特点如下：

  1. 客户端通过连接/线程处理层来连接 MySQL 数据库，连接/线程处理层主要用来处理客户端的请求、身份验证和数据库安全性验证等。
  2. 查询缓存和查询分析器是 SQL 层的核心部分，其中主要涉及查询的解析、优化、缓存，以及所有内置的函数，存储过程，触发器，视图等。
  3. 优化器主要负责存储和获取所有存储在 MySQL 中的数据

1.1.1 MySQL 物理文件的组成

MySQL 物理文件包括日志文件、数据文件和其他文件

  1. 日志文件：记录数据库操作信息和错误信息，包括错误日志、二进制日志、查询日志、慢查询日志和InnoDB引擎在线Redo日志等
    • 错误日志（Error Log）：记录MySQL Server运行过程中遇到的所有严重的错误信息，以及MySQL每次启动和关闭的详细信息
    • 二进制日志（binlog）：记录了MySQL所有修改数据库的操作，然后以二进制的心事记录在日志文件中，其中还包括每条语句执行时间和消耗的资源，以及相关的事务信息
    • 查询日志（hostname.log）：记录所有的查询操作，体积比较大，开启后对性能有较大影响
    • 慢查询日志：记录所有 SQL 执行时间超过 long_query_time 变量的语句和达到 min_examined_row_limit 条距离的语句。可通过 mysqldumpslow 命令对慢日志进行分析
    • InnoDB引擎在线Redo日志：记录了InnoDB所做的所有物理变更和事务信息。通过Redo日志和Undo信息，InnoDB大大加强了事务的安全性
  2. 数据日志：MyISAM引擎用“.MYD”扩展名，InnoDB引擎用“.ibd”作为扩展名，CSV引擎用“.csv”扩展名
    • .frm 文件：无论是哪种存储引擎，创建表后一定会生成一个以表名命名的“.frm”文件。主要存放与表结构相关的数据信息，包括表结构的定义信息。
    • .MYD 文件：MyISAM 存储引擎建表时，每个MyISAM类型表都有有一个“.MYD” 文件与之对应，用于存放数据表的数据信息
    • .MYI 文件：MyISAM 存储引擎建表都会有一个“.MYD”文件和一个“.MYI”文件，可以被缓存的内容主要是源于“.MYI”文件，“.MYI”文件中主要用来存储表数据文件中任何索引的数据树
    • ibd 文件和 .ibdata：用来存储InnoDB存储引擎中的索引数据。InnoDB存储引擎的存储方式能够通过配置来决定是采用共享表空间还是独享表空间的存储方式存储数据
  3. 其他文件：如系统配置文件、pid文件、socket文件
    • 系统配置文件一般都在“etc/my.cnf”中
    • pid文件类似于 Unix/Linux 中的进程文件
    • MySQL 服务器启动后，socket文件自动生成，该文件用来连接客户端

1.1.2 MySQL 各逻辑块简介

MySQL 逻辑架构采用 SQL 层和存储引擎分离方式。真正实现了数据存储和逻辑业务分离，MySQL 的 SQL 层宏观上可以分为三层，事实上 SQL 层包含很多的子模块

  1. 初始模块：在数据库启动时，对整个数据库做的一些初始化操作，如各种系统环境变量的初始化，各种缓存、存储引擎初始化设计等。
  2. 核心 API：主要实现了数据库底层操作的优化功能，其中主要包括IO操作、格式化输出、高性能存储数据结果算法的优化，字符串处理，其中最重要的是内存管理
  3. 网络交互模块：MySQL底层相互交互的模块抽象出接口，对外提供可以接收和发送数据的APi接口，其他模块需要交互的时候，可以通过APi接口调用
  4. 服务器客户端交互协议模块：MySQL服务器采用C/S形式访问数据库，数据连接使用MySQL C/S交互协议模块，实现了客户端和服务器端交互过程中所需要的一些独特的协议
  5. 用户模块：用于控制用户登录连接的权限和用户的授权管理
  6. 访问控制模块：用于监控用户的每一个操作。根据用户模块中不同的用户授权，以及根据其数据库的各种约束来控制用户对数据的访问。用户模块和访问控制模块结合起来，就组成了MySQL数据库的权限管理功能。
  7. 连接管理（连接线程和线程管理）：负责监听MySQL Server的各种请求，根据不同的请求，转发到线程管理模块，每个客户请求都会被数据库自动分配一个独立的线程为其单独服务，而连接线程的主要工作就是负责MySQL Server与客户端通信，线程管理模块负责管理这些生成的线程
  8. 转发模块：客户端连接MySQL后会发送一些查询语句，在MySQL Server里面，连接线程接收到客户端的一个请求后，会直接将查询转发到各个对应的处理模块。转发模块主要就是根据查询语句语法分析，然后转发给不同的模块处理
  9. 缓存模块：将客户端查询的请求返回的结果集放到缓存中，与查询的一个HASH值对应。在查询基表发生任何数据变化后，MySQL会自动将其擦寻的缓存失效
  10. 优化器模块：将客户端发送的查询请求，在之前算法的基础上分析，计算出一个最优的查询策略，优化之后会提高查询访问的速度，最后根据其最优策略返回查询语句
  11. 表变更管理模块：负责完成DML和DDL的查询，如 curd，create table，alter table等语句的处理
  12. 表维护模块：用于检测表的状态，分析、优化表结构，以及修复表
  13. 系统状态管理模块：负责响应客户端系统状态查询，如 show status，show variables等
  14. 表管理器：维护系统生成的表文件，将各个表结构信息缓存起来，另外，该模块还管理表级别的锁
  15. 日志记录模块：负责整个数据库逻辑层的日志文件，其中包含错误日志，二进制日志，以及慢查询日志等
  16. 复制模块：分为Master模块和Slave模块两部分。Master模块主要负责复制环境中读取Master端的binary日志，以及Slave端的I/O线程交互等工作。Slave模块主要有两个线程，一个负责从Master请求和接收binary日志，并写入本地I/O线程；另一个从relay log读取日志事件，然后解析成可以在Slave端执行的命令，交给Slave端的SQL线程
  17. 存储引擎接口模块：MySQL实现了其数据库底层存储引擎的插件式管理，将各种数据处理高度抽象化

1.1.3 MySQL 各逻辑块协调工作

20220630151722

MySQL 启动 -> （初始化模块）从系统配置文件读取系统参数和命令参数初始化整个系统 -> 启动存储引擎（初始化结束） -> 连接管理模块监听并接收客户端的程序 -> 将连接请求转发给线程管理模块去请求一个连接线程

线程模块接到请求后 -> 调用用户模块进行授权检查 -> 检测线程池是否有空间连接线程（有则直接连接上，无则建立一个新的线程与客户端建立连接）

如果初始阶段开启日志功能，这时候日志模块将请求记录日志

Query类型的请求，会将控制权交给Query解析器，Query解析器会检查是否是select类型的查询；如果是，则启动查询缓存模块查询

如果解析结果是DML/DDL。则交给变更管理模块；如果是一些检查，修复类的擦汗寻，则交给表维护模块去处理；如果是一条没有被缓存的查询语句，则交给查询优化器模块。实际上表变更管理器又分为若干小的模块：insert处理器、delete处理器、update处理器、create处理器，以及alter处理器。查询优化器，表变更模块，表维护模块，复制模块，状态模块都是根据命令解析器的结果不同而分发给不同类型的模块。

当一条命令执行完成之后，控制权都会还给连接线程模块，在上面各个模块处理过程中，各个模块都依赖于整个MySQL的核心API模块，比如内存管理，文件I/O，字符串处理等。

1.2 MySQL存储引擎概述

MySQL 采用插件的方式，将存储引擎直接加载到正在运行的 MySQL 中

20220630153145

查看当前默认存储引擎

show engines;

20220630153532

创建表时指定存储引擎

create table books(
    id int primary key auto_increment,
    name varchar(20) not null
) engine=MyISAM default charset=gbk;

修改表的存储引擎

alter table books engine = innodb;

1.3 MySQL各种存储引擎特性

1.3.1 MyISAM

MyISAM 在磁盘上存储三个文件

1.3.2 InnoDB

牺牲了存储和查询的效率，支持事务，支持自动增长列，外键等

  1. 支持事务：4个隔离级别
  2. 自动增长列
  3. 外键约束

在物理存储方面，InnoDB有独特的存储方式，表结构数据也是存放在“.frm”文件中，但是表数据和索引数据时存放在一起的。InnoDB的存储表和索引有两种方式

  1. 使用共享表空间存储：所有表和索引数据存放在同一个表空间
  2. 使用多表空间存储：表结构存放在.frm文件中，表数据和索引存放在单独的.ibd文件中。如果是分区表，则每个分区对应单独的.ibd文件，文件名称是“表名+分区名”

1.3.3 Memory

Memory 存储引擎通过采用内存中的内容来创建表。每个Memory表实际上和一个磁盘文件关联起来。文件名采用“表名.frm”的格式。Memory类型的表访问速度非常快，因为数据来源于内存空间。Memory存储引擎默认使用hash索引，数据库发生故障时数据会丢失。

Memory表内存存储在内存中，如果一个内部表变的很大，服务器自动把它转换成为一个磁盘表

1.3.4 Merge

Merge 存储引擎是一组 MyISAM 表的组合，将一组结构相同的 MyISAM 表组合成一个逻辑单元，通常也叫做MRG—_MyISAM存储引擎。Merge表本身没有数据

1.4 MySQL 工具

管理 MySQL 服务器、对数据库进行访问控制、管理 MySQL 用户以及数据库备份和恢复工具等

1.4.1 MySQL 命令行实用程序

MySQL 服务器端实用工具程序：

  1. mysqld：SQL 后台程序（即MySQL服务器进程）
  2. mysql_safe：服务器启动脚本
  3. mysql.server：服务器启动脚本，调用mysql_safe
  4. mysqld_multi：服务器启动脚本，可以启动或停止系统上安装的多个服务器
  5. myisamchk：用来描述、检查、优化和维护 MyISAM表
  6. mysql.server：服务器启动脚本
  7. mysqlbug：MySQL 缺陷报告脚本，可以用来向MySQL邮件系统发送缺陷报告
  8. mysql_install_db：系统首次安装时执行一次

MySQL 客户端实用工具：

  1. myisampack：压缩 MyISAM 表
  2. mysql：交互式输入SQL语句
  3. mysqlaccess：检查访问主机名、用户名和数据库组合的权限的脚本
  4. mysqladmin：执行管理操作的客户端程序，如创建或删除数据库，重载授权表，将表刷新到磁盘上，以及重新打开日志文件。还可以用来检索版本、进程，以及服务器的状态信息
  5. mysqlbinlog：从二进制日志读取语句的工具，帮助系统从崩溃中恢复
  6. mysqlcheck：检查、修复、分析以及优化表的表维护客户程序
  7. mysqldump：将MySQL数据库转储到一个文件
  8. mysqlhotcopy：快速备份MyISAM表的工具
  9. mysql import：使用LOAD DATA INFILE 将文本文件导入相关表
  10. mysqlshow：显示数据库、表、列以及索引相关信息
  11. perror：显示系统或者MySQL错误代码含义

MySQL 客户端连接工具–mysql

mysql [options] [database]

MySQL 管理工具–mysqladmin

mysqladmin [options] command command

1.4.2 MySQL Workbench

下一代可视化数据库设计软件

数据库设计和模型建立。SQL开发。数据库管理。

1.5 本章小结

主要围绕 MYSQL 数据库的逻辑结构&存储引擎及其特性&MySQL数据库提供的常用工具

第二章 MySQL 权限与安全

不当的权限设置可能会导致各种各样的安全隐患

2.1 权限表

MySQL 服务器通过权限表控制用户对数据库的访问，权限表存放在 mysql 数据库中，由 mysql_install_db 脚本初始化。存储账户权限信息表主要有：user、db、host、tables_priv、columns_priv和proce_priv

2.1.1 user 表

记录允许连接到服务器的账号信息，里面的授权是全局的

用户列：

第三章 数据备份与还原

第四章 MySQL 的高级特性

第五章 MySQL 锁定机制

第六章 使用 MySQL Workbench 管理数据库

第七章 SQL 性能优化

第八章 MySQL 服务器性能优化

第九章 MySQL 性能监控

第十章 MySQL Replication

第十一章 MySQL Cluster 实战

企业中 MySQL 的高可用架构

This site is open source. Improve this page.