科普文：软件架构数据库系列之【MySQL前世今生及其体系结构概叙】

01Byte空间

已于 2024-08-29 17:35:22 修改

阅读量2.5k

点赞数 44

分类专栏：业务场景软件架构 MySQL 文章标签：数据库 mysql mysql前世今生

于 2024-08-29 16:11:50 首次发布

本文链接：https://blog.csdn.net/Rookie_CEO/article/details/141675790

版权

软件架构同时被 3 个专栏收录

1000 篇文章

订阅专栏

业务场景

708 篇文章

订阅专栏

MySQL

176 篇文章

订阅专栏

一、基本概念

OLTP（On-Line transaction processing）

OLTP：联机事务处理，主要对数据库增删改查。

OLTP 主要用来记录某类业务事件的发生；数据会以增删改的方式在数据库中进行数据的更新处理操作，要求实时性高、稳定性强、确保数据及时更新成功。

OLAP（On-Line Analytical Processing）

OLAP：联机分析处理，主要对数据库查询。

当数据积累到一定的程度，我们需要对过去发生的事情做一个总结分析时，就需要把过去一段时间内产生的数据拿出来进行统计分析，从中获取我们想要的信息，为公司做决策提供支持，这时候就是在做 OLAP 。

数据库（Database）

数据库（DB）是一个按照数据结构来组织、存储和管理数据的仓库，通俗点，就是存储数据的仓库。

数据库是一个电子化的文件夹，存储了一些特定的数据。在MySQL中，数据库是在文件系统中创建的，具有特定的结构，用于存储表和索引等数据库对象。

数据库实例（Instance）

数据库实例是一个运行的服务器，它处理数据库操作。实例通过后台进程（如mysqld）运行，并管理缓存和后台进程，如SQL接口、解析器、优化器、行为和事务管理等。每个实例在内存中都有自己的一组后台线程和专用的系统表。

简单来说，数据库是一个单独的文件集合，而数据库实例是管理和操作这些文件的一个服务进程。

数据库服务（Database service）

mysql数据库服务 = mysql数据库实例

数据库服务是指提供数据库访问和管理功能的系统或软件。它通常包括数据库服务器、网络连接、存储设备等组件，用于存储、检索和管理数据。数据库服务可以由多个客户端应用程序共享，并提供数据的一致性、安全性和可靠性。常见的数据库服务包括MySQL、Oracle、Microsoft SQL Server等。

数据库管理系统（DBMS）

【数据库管理系统(DBMS：DataBase Management System)】

数据库管理系统（DBMS）是建立、操作和管理数据库的大型软件，通俗地讲，就是操作数据库的工具。用于建立、使用和维护数据库的各种对象的一种系统。它提供了数据定义语言（DDL）和数据操作语言（DML），以便用户定义数据库的结构和维护数据库中的数据。DBMS还提供了数据安全性、完整性和并发控制等机制，确保数据的一致性和可靠性‌。

四者之间的关系

数据库管理系统(DBMS) --包含--》数据库服务 --包含--》数据库实例 --包含--》数据库；

在这里面DBMS数据库管理系统的概念最大。

误用导致混淆： 人们通常用数据库这个术语来代表他们使用的数据库软件。这是不正确的，它是引起混淆的根源。确切地说，数据库软件应称为DBMS（数据库管理系统）。数据库是通过DBMS创建和操纵的容器，你并不直接访问数据库。你使用的是DBMS，它替你访问和操纵数据库。很多时候我们是将“数据库”代替了DBMS（数据库管理系统）

数据库、模式和表

模式（schema）

模式（schema）：关于数据库和表的布局及特性的信息。
表具有一些特性，这些特性定义了数据在表中如何存储，如可以存储什么样的数据，数据如何分解，各部分信息如何命名，等等。描述表的这些特性信息就是所谓的模式，模式可以用来描述数据库中特定的表以及整个数据库（和其中表的关系）
注意：有时，模式用作数据库的同义词。遗憾的是，模式的含义通常在上下文中并不是很清晰。在这里，模式指的是上面给出的定义。

表（table）

表（table）：某种特定类型数据的结构化清单。
在你将资料放入自己的文件柜时，并不是随便将它们扔进某个抽屉就完事了，而是在文件柜中创建文件，然后将相关的资料放入特定的文件中。
在数据库领域中，这种文件称为表。表是一种结构化的文件，可用来存储某种特定类型的数据。表可以保存顾客清单、产品目录，或者其他信息清单。
关键的一点在于，存储在表中的数据是同一种类型的数据或一个清单。决不应该将顾客的清单与订单的清单存储在同一个数据库表中。这样做将使以后的检索和访问很困难。应该创建两个表，每个清单一个表。
数据库中的每个表都有一个名字，用来标识自己。此名字是唯一的，这表示数据库中没有其他表具有相同的名字。

在MySQL中，你可以有多个数据库，但通常只有一个数据库实例。然而，有些配置可能允许你运行多个实例，每个实例连接到不同的数据库或数据库的不同部分。

以下是如何在MySQL中创建新数据库的示例SQL命令：CREATE DATABASE mydatabase;这将创建一个名为mydatabase的新数据库（其实这不是数据库，而是模式）。

要连接到特定数据库，可以使用USE语句：USE mydatabase; 或者 mysql -u username -p mydatabase。

之后，你可以执行数据库操作，如创建表、插入数据等。

记住，实际的数据库实例（如mysqld服务）通常是在数据库服务器软件（如MySQL或MariaDB）安装过程中设置的，并且通常是在系统启动时自动启动的。

关系数据库管理系统（RDBMS）

关系数据库管理系统（Relational Database Management System：RDBMS）是指包括相互联系的逻辑组织和存取这些数据的一套程序 (数据库管理系统软件)。关系数据库管理系统就是管理关系数据库，并将数据逻辑组织的系统。

常用的关系数据库管理系统产品是甲骨文的Oracle和MySQL、IBM的DB2、微软的SQL Server、以及国产数据库基石PG（ PostgreSQL）。

MySQL（RDBMS）关系数据库管理系统

MySQL是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，中间被Sun收购出了MySQL5.1，现在被甲骨文Oracle收购，在甲骨文手里出了 MySQL5.5、MySQL5.6、MySQL5.7、MySQL8.0、8.1、8.2、8.3、8.4、9.0。其中MySQL5.5是被吐槽最多的数据库（原班人马MySQL之父Monty 在其被oracle收购后推出了MariaDB ）。

MySQL是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的RDBMS (Relational Database Management System，关系数据库管理系统)应用软件之一。

MySQL是一种关系型数据库管理系统，主要适用于OLTP的业务场景，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL作为网站数据库。

二、MySQL的前世今生

MySQL主要分支

‌‌MySQL的主要分支包括Drizzle,MariaDB,Percona Server,以及Oracle MySQL。

‌Drizzle‌是一个真正的MySQL分支，它进行了大量的重大更改，包括SQL语法的变化，设计目标之一是提供高可用性解决方案。Drizzle使用C++编写，旨在适应多核服务器、高性能服务器和云计算环境。
‌MariaDB‌是由MySQL的创始人Monty Widenius在离开Sun Microsystems后创立的。MariaDB的目标是社区开发，提供了MySQL的所有功能，并添加了许多新特性。它是MySQL的超集，因此可以在不修改现有系统的情况下运行。
‌Percona Server‌是由Percona公司发布的，该公司自称是“数据库性能专家”。Percona Server旨在与MySQL向后兼容，提供性能和功能的显著提升，特别是在高负载下的InnoDB性能和提供性能诊断工具方面。
PolarDB是阿里巴巴自主研发的下一代关系型分布式云原生数据库，目前兼容三种数据库引擎：MySQL、PostgreSQL、Oracle（语法兼容）。计算能力最高可扩展至1000核以上，存储容量最高可达 100T。经过阿里巴巴双十一活动的最佳实践，让用户既享受到开源的灵活性与价格，又享受到商业数据库的高性能和安全性。
Oracle MySQL‌是Oracle公司收购Sun Microsystems后获得的MySQL官方版本。Oracle对MySQL进行了持续的开发和维护，提供了官方的支持和更新。

这些分支提供了不同的功能和性能优化，适用于各种使用场景和需求。例如，Drizzle适合需要高性能和高可用性的环境，MariaDB适合需要更多特性和社区支持的场景，Percona Server则专注于性能优化和诊断工具，而Oracle MySQL则提供了官方的支持和更新。

选择合适的MySQL分支取决于用户的具体需求和技术栈‌

对于MySQL的历史，相信很多人早已耳熟能详，这里就不要赘述。下面仅从产品特性的角度梳理其发展过程中的里程碑事件。

1995年，MySQL 1.0发布，仅供内部使用。

1996年，MySQL 3.11.1发布，直接跳过了MySQL 2.x版本。

1999年，MySQL AB公司成立。同年，发布MySQL 3.23，该版本集成了Berkeley DB存储引擎。该引擎由Sleepycat公司开发，支持事务。在集成该引擎的过程中，对源码进行了改造，为后续可插拔式存储引擎架构奠定了基础。

2000年，ISAM升级为MyISAM存储引擎。同年，MySQL基于GPL协议开放源码。

2002年，MySQL 4.0发布，集成了后来大名鼎鼎的InnoDB存储引擎。该引擎由Innobase公司开发，支持事务，支持行级锁，适用于OLTP等高并发场景。

2005年，MySQL 5.0发布，开始支持游标，存储过程，触发器，视图，XA事务等特性。同年，Oracle收购Innobase公司。

2008年，Sun以10亿美金收购MySQL AB。同年，发布MySQL 5.1，其开始支持定时器（Event scheduler），分区，基于行的复制等特性。

2009年，Oracle以74亿美金收购Sun公司。

2010年，MySQL 5.5发布，其包括如下重要特性及更新。

InnoDB代替MyISAM成为MySQL默认的存储引擎。
多核扩展，能更充分地使用多核CPU。
InnoDB的性能提升，包括支持索引的快速创建，表压缩，I/O子系统的性能提升，PURGE操作从主线程中剥离出来，Buffer Pool可拆分为多个Instances。
半同步复制。
引入utf8mb4字符集，可用来存储emoji表情。
引入metadata locks（元数据锁）。
分区表的增强，新增两个分区类型：RANGE COLUMNS和LIST COLUMNS。
MySQL企业版引入线程池。
可配置IO读写线程的数量（innodb_read_io_threads，innodb_write_io_threads）。在此之前，其数量为1，且不可配置。
引入innodb_io_capacity选项，用于控制脏页刷新的数量。

2013年，MySQL5.6发布，其包括如下重要特性及更新。

GTID复制。
无损复制。
延迟复制。
基于库级别的并行复制。
mysqlbinlog可远程备份binlog。
对TIME, DATETIME和TIMESTAMP进行了重构，可支持小数秒。DATETIME的空间需求也从之前的8个字节减少到5个字节。
Online DDL。ALTER操作不再阻塞DML。
可传输表空间（transportable tablespaces）。
统计信息的持久化。避免主从之间或数据库重启后，同一个SQL的执行计划有差异。
全文索引。
InnoDB Memcached plugin。
EXPLAIN可用来查看DELETE，INSERT，REPLACE，UPDATE等DML操作的执行计划，在此之前，只支持SELECT操作。
分区表的增强，包括最大可用分区数增加至8192，支持分区和非分区表之间的数据交换，操作时显式指定分区。
Redo Log总大小的限制从之前的4G扩展至512G。
Undo Log可保存在独立表空间中，因其是随机IO，更适合放到SSD中。但仍然不支持空间的自动回收。（PG表膨胀、数据和undo放一起）
可dump和load Buffer pool的状态，避免数据库重启后需要较长的预热时间。
InnoDB内部的性能提升，包括拆分kernel mutex，引入独立的刷新线程，可设置多个purge线程。
优化器性能提升，引入了ICP，MRR，BKA等特性，针对子查询进行了优化。

可以说，MySQL5.6是MySQL历史上一个里程碑式的版本，这也是目前生产上应用得最广泛的版本。

2015年，MySQL5.7发布，其包括如下重要特性及更新。

组复制
InnoDB Cluster
多源复制
增强半同步（AFTER_SYNC）
基于WRITESET的并行复制。
在线开启GTID复制。
在线设置复制过滤规则。
在线修改Buffer pool的大小。
在同一长度编码字节内，修改VARCHAR的大小只需修改表的元数据，无需创建临时表。
可设置NUMA架构的内存分配策略（innodb_numa_interleave）。
透明页压缩（Transparent Page Compression）。
UNDO表空间的自动回收。
查询优化器的重构和增强。
可查看当前正在执行的SQL的执行计划（EXPLAIN FOR CONNECTION）。
引入了查询改写插件（Query Rewrite Plugin），可在服务端对查询进行改写。
EXPLAIN FORMAT=JSON会显示成本信息，这样可直观的比较两种执行计划的优劣。
引入了虚拟列，类似于Oracle中的函数索引。
新实例不再默认创建test数据库及匿名用户。
引入ALTER USER命令，可用来修改用户密码，密码的过期策略，及锁定用户等。
mysql.user表中存储密码的字段从password修改为authentication_string。
表空间加密。
优化了Performance Schema，其内存使用减少。
Performance Schema引入了众多instrumentation。常用的有Memory usage instrumentation，可用来查看MySQL的内存使用情况，Metadata Locking Instrumentation，可用来查看MDL的持有情况，Stage Progress instrumentation，可用来查看Online DDL的进度。
同一触发事件（INSERT，DELETE，UPDATE），同一触发时间（BEFORE，AFTER），允许创建多个触发器。在此之前，只允许创建一个触发器。
InnoDB原生支持分区表，在此之前，是通过ha_partition接口来实现的。
分区表支持可传输表空间特性。
集成了SYS数据库，简化了MySQL的管理及异常问题的定位。
原生支持JSON类型，并引入了众多JSON函数。
引入了新的逻辑备份工具-mysqlpump，支持表级别的多线程备份。
引入了新的客户端工具-mysqlsh，其支持三种语言：JavaScript, Python and SQL。两种API：X DevAPI，AdminAPI，其中，前者可将MySQL作为文档型数据库进行操作，后者用于管理InnoDB Cluster。
mysql_install_db被mysqld --initialize代替，用来进行实例的初始化。
原生支持systemd。
引入了super_read_only选项。
可设置SELECT操作的超时时长（max_execution_time）。
可通过SHUTDOWN命令关闭MySQL实例。
引入了innodb_deadlock_detect选项，在高并发场景下，可使用该选项来关闭死锁检测。
引入了Optimizer Hints，可在语句级别控制优化器的行为，如是否开启ICP，MRR等，在此之前，只有Index Hints。
GIS的增强，包括使用Boost.Geometry替代之前的GIS算法，InnoDB开始支持空间索引。

2018年，MySQL8.0发布，其包括如下重要特性及更新。

引入了原生的，基于InnoDB的数据字典。数据字典表位于mysql库中，对用户不可见，同mysql库的其它系统表一样，保存在数据目录下的mysql.ibd文件中。不再置于mysql目录下。
Atomic DDL。
重构了INFORMATION_SCHEMA，其中，部分表已重构为基于数据字典的视图，在此之前，其为临时表。
PERFORMANCE_SCHEMA查询性能提升，其已内置多个索引。
不可见索引（Invisible index）。
降序索引。
直方图。
公用表表达式（Common table expressions）。
窗口函数（Window functions）。
角色（Role）。
资源组（Resource Groups），可用来控制线程的优先级及其能使用的资源，目前，能被管理的资源只有CPU。
引入了innodb_dedicated_server选项，可基于数据库的内存来动态设置innodb_buffer_pool_size，innodb_log_file_size和innodb_flush_method。
快速加列（ALGORITHM=INSTANT）。
JSON字段的部分更新（JSON Partial Updates）。
自增主键的持久化。
可持久化全局变量（SET PERSIST）。
默认字符集由latin1修改为utf8mb4。
默认开启UNDO表空间，且支持在线调整数量（innodb_undo_tablespaces）。在MySQL5.7中，默认不开启，若要开启，只能初始化时设置。
备份锁。
Redo Log的优化，包括允许多个用户线程并发写入log buffer，可动态修改innodb_log_buffer_size的大小。
默认的认证插件由mysql_native_password更改为caching_sha2_password。
默认的内存临时表由MEMORY引擎更改为TempTable引擎，相比于前者，后者支持以变长方式存储VARCHAR，VARBINARY等变长字段。从MySQL8.0.13开始，TempTable引擎支持BLOB字段。
Grant不再隐式创建用户。
SELECT ... FOR SHARE和SELECT ... FOR UPDATE语句中引入NOWAIT和SKIP LOCKED选项，解决电商场景热点行问题。
正则表达式的增强，新增了4个相关函数，REGEXP_INSTR()，REGEXP_LIKE()，REGEXP_REPLACE()，REGEXP_SUBSTR()。
查询优化器在制定执行计划时，会考虑数据是否在Buffer Pool中。而在此之前，是假设数据都在磁盘中。
ha_partition接口从代码层移除，如果要使用分区表，只能使用InnoDB存储引擎。
引入了更多细粒度的权限来替代SUPER权限，现在授予SUPER权限会提示warning。
GROUP BY语句不再隐式排序。
MySQL5.7 引入的表空间加密特性可对Redo Log和Undo Log进行加密。
information_schema中的innodb_locks和innodb_lock_waits表被移除，取而代之的是performance_schema中的data_locks和data_lock_waits表。
引入performance_schema.variables_info表，记录了参数的来源及修改情况。
增加了对于客户端报错信息的统计（performance_schema.events_errors_summary_xxx）。
可统计查询的响应时间分布（call sys.ps_statement_avg_latency_histogram()）。
支持直接修改列名（ALTER TABLE ... RENAME COLUMN old_name TO new_name）。
用户密码可设置重试策略（Reuse Policy）。
移除PASSWORD()函数。这就意味着无法通过“SET PASSWORD ... = PASSWORD('auth_string') ”命令修改用户密码。
代码层移除Query Cache模块，故Query Cache相关的变量和操作均不再支持。
BLOB, TEXT, GEOMETRY和JSON字段允许设置默认值。
可通过RESTART命令重启MySQL实例。

2024年4月30日发布了 MySQL 8.4 版本(LTS)，该版本是创新版的第一个长期支持版本。

MySQL :: Download MySQL Community Server

MySQL :: MySQL 8.4 LTS (Long Term Support) in Depth (in Hebrew)

MySQL 8.1‌ 主要进行了bug修复和安全性增强，作为MySQL 8.0版本的维护更新。
‌MySQL 8.2‌ 到 ‌MySQL 8.4‌ 陆续发布了多个更新版本，主要集中于性能优化、安全性的进一步提升以及新功能的添加。例如，引入了窗口函数支持、改进的JSON支持、全球化与字符集支持、性能与可扩展性的提升等。
‌MySQL 9.0‌ 作为下一个主要版本，预计将带来更多的创新和改进，可能包括新的数据类型、查询优化、安全性的进一步提升以及其他尚未公布的新特性。

需要注意的是，上面提到的发布，一般指的是GA版本。

这里补充一点，对于 MySQL 8.x 系列，8.4.x 将作为 LTS 长期支持版本，而从 9.x 开始，9.7.x 将作为 LTS 长期支持版本。

根据官方资料介绍，MySQL 5.7 最多还能发布两个版本就 EOL 了，生命周期于今年 2023 年 10 月份到期,2015 发布至今 8 个年头，长达 8 年的长期支持就到此结束。MySQL 8.0 是 2018 年发布，还能坚挺两年，标准支持延长到 2025 年 4 月，去年显示应该是到今年 4 月份到期，应该是做了调整，在之前的基础上延长了两年，延伸支持的期限没有改变，仍然是 2026 年 4 月。

MySQL8.4新特性

MySQL :: MySQL 8.4 Reference Manual :: 1.4 What Is New in MySQL 8.4 since MySQL 8.0

MySQL :: MySQL Cluster Manager 8.4 Release Notes :: Changes in MySQL Cluster Manager 8.4.0 (2024-04-30, LTS Release)

MySQL 8.4 现已正式发布，这是一个具有重大意义的版本，因为它被指定为长期支持（LTS）版本。LTS 软件的引入意味着 MySQL 8.0.34+ 将成为一个仅修复错误的版本。

创新版本可能每季度发布一次，新的长期支持版本大约每两年发布一次。8.4 版本将持续到 2026 年初。但请记住，将它们纳入主流长期支持版本需要长达两年的时间！

8.0 到 8.4，过去六年都发生了哪些变化呢？

·MySQL Native 密码早已过时，默认情况下也不再加载。不过，它仍然可以加载。这是一个安全问题，建议尽快升级！
·在 Linux 上，Innodb_flush_method 已从 fsync 改为 ODIRECT。innopdb_log_buffer_size 从 16 MiB 变为 64 MiB。总共有20个innodb参数有变更。
·克隆插件（Clone PlugIn）允许我们在不同的点版本之间切换的容忍度更高。克隆非常方便，这是一个值得欢迎的变化。
·GTID 已得到扩展，允许处理事务组。这应该会有所帮助。
·mysqldump 现在可以为旧版本生成输出结果。这对那些将数据转移到 8.0.23 之前或 8.0.23 至 8.1（包括 8.0.23）的系统的人来说非常方便。有多少人会把数据从 8.4 转储到更早的版本？这在需要时会很方便。
·运行 ANALYZE TABLE 时，直方图会自动更新。
·可以授予新的 FLUSH_PRIVILEGES 权限。
·在复制命令中使用 MASTER 和 SLAVE 这两个术语，最终可能会被 SOURCE 和 REPLICA 所取代。
·143 个 bug 得到了修复。

8.4 功能变更
1 MySQL 密码认证
认证插件：默认情况下， mysql_native_password 插件被禁用，如果需要启用该插件，需要在启动时指定 --mysql-native-password=ON 或者在my.cnf 文件里面配置 mysql_native_password=ON 。

2 InnoDB 相关默认参数变化：有 20 个 InnoDB 变量的默认值已被修改

innodb_io_capacity: 默认值改成了10000，之前是200。目前绝大部分磁盘都是 SSD ,200 这个值的确少了点。

innodb_buffer_pool_instances: 调整取值的算法，取min(innodb_buffer_pool_size / innodb_buffer_pool_chunk_size *1/2, 逻辑 CPU * 1/4)

innodb_adaptive_hash_index: 默认改为 OFF ，之前是ON ，AHI 是 DDL 不稳定因素之一，这次终于默认关闭了。开启也带来不了多少性能提升。

其他的各位读者朋友可以看官方文档或者下面的截图：

3 复制相关
主从复制参数 SOURCE_RETRY_COUNT 默认值变更为 10，也即主从复制关系异常时，从库尝试连接主库10次。每次间隔的时间由 SOURCE_CONNECT_RETRY 决定，默认 60s 。

在 MySQL 的主从复制配置中，SOURCE_RETRY_COUNT 和 SOURCE_CONNECT_RETRY 决定复制过程中从服务器尝试重新连接到主服务器的行为。

SOURCE_RETRY_COUNT：从服务器在复制过程中遇到错误（如网络问题或访问冲突）时，尝试重新访问主库的重试次数。默认情况下，如果从服务器在读取二进制日志文件时遇到错误，它会停止复制并等待下一次 poll 操作（通常是几秒到几十秒，取决于 REPLICATE_RECONNECT 参数的设置）。如果设置了 SOURCE_RETRY_COUNT，从服务器将在这个次数范围内尝试重新读取错误发生的位置，而不是立即停止复制。

SOURCE_CONNECT_RETRY：从服务器尝试重新连接到主服务器的间隔时间。当从服务器由于某些原因与主服务器的连接断开时（比如主服务器重启或网络中断），从服务器会等待 SOURCE_CONNECT_RETRY 指定的秒数后尝试重新建立连接。如果连接失败，从服务器将继续在每隔 SOURCE_CONNECT_RETRY 秒尝试重连，直到成功为止。

之前没有注意改参数，这次补习一下。

4 master/slave 关键字变化
前几年为了政治正确官方决定去掉关键字 master ，slave ，现在终于在 8.4 版本里面去掉了，使用者需要做如下替换:

master ---> source
slave ---> replica
这里要辛苦运维开发同学做版本兼容。凡是涉及这两个词的状态参数，命令语句都要修改。不过 8.4 版本应用到是生产版本还有好多年，习惯命令行的朋友还不用着急修改。

5 SQL_AFTER_GTIDS 兼容 MTA.
以前，当复制启用并行复制 MTA功能并且用户尝试 SQL_AFTER_GTIDS，"START REPLICA" 会触发警告 ER_MTS_FEATURE_IS_NOT_SUPPORTED，并且复制会被切换到单线程模式。8.4 版本，"START REPLICA" 语句的 "SQL_AFTER_GTIDS" 选项兼容 MTA 。

6 GTID组复制参数相关
group_replication_consistency 系统变量的默认值从EVENTUAL 改为 BEFORE_ON_PRIMARY_FAILOVER。

group_replication_exit_state_action 系统变量的默认值改为 OFFLINE_MODE。

group_replication_set_as_primary() 函数在选择新主成员时，将等待正在进行的DDL结束。

7 Clone plugin
克隆功能对版本的要求进一步放宽，允许在同一大版本的不同小版本之间进行克隆。换句话说，只有主要版本号和次要版本号必须匹配，而以前点版本号也必须匹配。

例如，克隆功能现在允许将 8.4.0 克隆到 8.4.14 以及将 8.0.51 克隆到 8.0.37。对于 8.0，之前的限制仍然适用于 8.0.37 之前的版本，因此不允许将 8.0.36 等克隆到 8.0.42，反之亦然。（WL＃15989）

8 直方图自动更新功能
MySQL 8.4.0 支持自动更新直方图。当为给定的直方图启用此功能时，你可以通过为 ANALYZE TABLE 语句包含 AUTO UPDATE 选项来启用此功能。每当对指定的表运行 ANALYZE TABLE 时，它会被更新。

可以使用 MANUAL UPDATE 选项禁用自动更新某个表的直方图功能。如果没有指定这两个选项中的任何一个，则默认为 MANUAL UPDATE（无自动更新）。

9 Thread pool plugin 连接信息
增加连接池相关的表 tp_connections 记录每个连接池的元数据。

tp_thread_state ，tp_thread_group_state 增加了更细粒度的字段显示连接池的状态。

10 废弃 expire_logs_days
废弃参数 expire_logs_days 并使用 binlog_expire_logs_seconds 代替。

11 过时的复制选项和变量
在 MySQL 早期版本中，一些与MySQL复制相关的选项和变量已被弃用，并且已从MySQL 8.4中移除。现在尝试使用这些选项和变量将导致服务器抛出语法错误。这些选项和变量列举如下：

--slave-rows-search-algorithms：当应用更新或删除操作寻找表行时，复制应用程序使用的算法现在始终是HASH_SCAN,INDEX_SCAN，用户不再能够配置此项。

--log_bin_use_v1_events：这允许运行MySQL 5.7及更新版本的源服务器复制到早期版本的MySQL，这些早期版本的MySQL不再被支持或维护。

--relay-log-info-file, --relay-log-info-repository, --master-info-file, --master-info-repository：使用文件作为sql 应用线程的元数据的方式已被支持 crash-safe 的表所取代。

三、MySQL体系架构

1.MySQL是典型的C/S架构

客户端：mysql、mysql-jdbc

服务端：mysqld

客户端服务进程mysql和服务端服务进程mysqld

基于TCP协议的C/S架构

2.MySQL体系架构

MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层。

2.1 网络连接层

客户端连接器（Client Connectors）：提供与MySQL服务器建立的支持。目前几乎支持所有主流的服务器编程技术，例如常见的Java、C、Python、.NET等，它们通过各自的API技术与MySQL建立连接。

2.2 服务层（MySQL Server）

服务层是MySQL Server的核心，主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分。 - 连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。 - 系统管理和控制工具（Management Services & Utilities）：例如备份恢复、安全管理、集群管理等 - SQL接口（SQL Interface）：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。 - 解析器（Parser）：负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步检查解析树是否合法。 - 查询优化器（Optimizer）：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互。

select uid,name from user where gender=1; 选取--》投影--》联接策略 1）select先根据where语句进行选取，并不是查询出全部数据再过滤 2）select查询根据uid和name进行属性投影，并不是取出所有字段 3）将前面选取和投影联接起来最终生成查询结果

缓存（Cache&Buffer）：缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

2.3 存储引擎层（Pluggable Storage Engines）

存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异。现在有很多种存储引擎，各有各的特点，最常见的是MyISAM和InnoDB。

2.4 系统文件层（File System）

该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

日志文件
- 错误日志（Error log）默认开启，show variables like '%log_error%'
- 通用查询日志（General query log）记录一般查询语句，show variables like '%general%';
- 二进制日志（binary log）记录了对MySQL数据库执行的更改操作，并且记录了语句的发生时间、执行时长；但是它不记录select、show等不修改数据库的SQL。主要用于数据库恢复和主从复制。 show variables like '%log_bin%'; //是否开启 show variables like '%binlog%'; //参数查看 show binary logs;//查看日志文件
- 慢查询日志（Slow query log）记录所有执行时间超时的查询SQL，默认是10秒。 show variables like '%slow_query%'; //是否开启 show variables like '%long_query_time%'; //时长
配置文件 用于存放MySQL所有的配置信息文件，比如my.cnf、my.ini等。
数据文件
- db.opt 文件：记录这个库的默认使用的字符集和校验规则。
- frm 文件：存储与表相关的元数据（meta）信息，包括表结构的定义信息等，每一张表都会有一个frm 文件。
- MYD 文件：MyISAM 存储引擎专用，存放 MyISAM 表的数据（data)，每一张表都会有一个MYD 文件。
- MYI 文件：MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息，每一张 MyISAM 表对应一个 .MYI 文件。
- ibd文件和 IBDATA 文件：存放 InnoDB 的数据文件（包括索引）。InnoDB 存储引擎有两种表空间方式：独享表空间和共享表空间。独享表空间使用 .ibd 文件来存放数据，且每一张InnoDB 表对应一个 .ibd 文件。共享表空间使用 .ibdata 文件，所有表共同使用一个（或多个，自行配置）.ibdata 文件。
- ibdata1 文件：系统表空间数据文件，存储表元数据、Undo日志等。
- ib_logfile0、ib_logfile1 文件：Redo log 日志文件。
pid 文件 pid 文件是 mysqld 应用程序在 Unix/Linux 环境下的一个进程文件，和许多其他 Unix/Linux 服务端程序一样，它存放着自己的进程 id。
socket 文件 socket 文件也是在 Unix/Linux 环境下才有的，用户在 Unix/Linux 环境下客户端连接可以不通过TCP/IP 网络而直接使用 Unix Socket 来连接 MySQL。

3.MySQL运行机制

如上所示MySQL逻辑架构。

mysql运行机制涵盖MySQL体系结构中的4层结构：网络连接层、服务层、存储引擎层、系统文件层，这里只做一个概要描述，暂不深入说明。

3.1 建立连接（Connectors&Connection Pool）

通过C/S客户端/服务器通信协议与MySQL建立连接。

MySQL 客户端与服务端的通信方式是 “ 半双工 ”。

对于每一个 MySQL 的连接，时刻都有一个线程状态来标识这个连接正在做什么。

通讯机制：

全双工：能同时发送和接收数据，例如平时打电话。
半双工：指的某一时刻，要么发送数据，要么接收数据，不能同时。例如早期对讲机
单工：只能发送数据或只能接收数据。

例如单行道线程状态： show full processlist; //查看用户正在运行的线程信息，root用户能查看所有线程，其他用户只能看自己的

id：线程ID，可以使用kill xx；
user：启动这个线程的用户
Host：发送请求的客户端的IP和端口号
db：当前命令在哪个库执行
Command：该线程正在执行的操作命令
- Create DB：正在创建库操作
- Drop DB：正在删除库操作
- Execute：正在执行一个PreparedStatement
- Close Stmt：正在关闭一个PreparedStatement
- Query：正在执行一个语句
- Sleep：正在等待客户端发送语句
- Quit：正在退出
- Shutdown：正在关闭服务器
Time：表示该线程处于当前状态的时间，单位是秒
State：线程状态
- Updating：正在搜索匹配记录，进行修改
- Sleeping：正在等待客户端发送新请求
- Starting：正在执行请求处理
- Checking table：正在检查数据表
- Closing table : 正在将表中数据刷新到磁盘中
- Locked：被其他查询锁住了记录
- Sending Data：正在处理Select查询，同时将结果发送给客户端
Info：一般记录线程执行的语句，默认显示前100个字符。

想查看完整的使用show full processlist;

3.2 缓存（Cache&Buffer）

这是MySQL的一个可优化查询的地方，如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成“解析树”。

缓存Select查询的结果和SQL语句（query cache：mysql4.0引入、mysql5.7禁用、mysql8.0移除；）（PolarDB MySQL在2020年推出了Fast Query Cache（查询结果缓存），且在PolarDB MySQL各个大版本（5.6 5.7 8.0）中均已支持。Fast Query Cache做了大量无锁的设计和自适应优化，能够支持高并发，能够使业务享受缓存命中带来的性能提升而无需担心有不适配场景使业务受影响。后面有机会再详细说明）
执行Select查询时，先查询缓存，判断是否存在可用的记录集，要求是否完全相同（包括参数值），这样才会匹配缓存数据命中。
即使开启查询缓存，以下SQL也不能缓存
- 查询语句使用SQL_NO_CACHE
- 查询的结果大于query_cache_limit设置
- 查询中有一些不确定的参数，比如now()
show variables like '%query_cache%'; //查看查询缓存是否启用，空间大小，限制等
show status like 'Qcache%'; //查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等

3.3 解析器（Parser）

将客户端发送的SQL进行语法解析，生成"解析树"。预处理器根据一些MySQL规则进一步检查“解析树”是否合法，例如这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义，最后生成新的“解析树”。

3.4 查询优化器（Optimizer）

根据“解析树”生成最优的执行计划。

MySQL使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）。

等价变换策略
- 5=5 and a>5 改成 a > 5
- a < b and a=5 改成b>5 and a=5
- 基于联合索引，调整条件位置等
优化count、min、max等函数
- InnoDB引擎min函数只需要找索引最左边
- InnoDB引擎max函数只需要找索引最右边
- MyISAM引擎count(*)，不需要计算，直接返回
提前终止查询
- 使用了limit查询，获取limit所需的数据，就不在继续遍历后面数据
in的优化
- MySQL对in查询，会先进行排序，再采用二分法查找数据。比如where id in (2,1,3)，变成 in (1,2,3)

3.5 查询执行引擎负责执行 SQL 语句

此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存（Cache&Buffer）中，以后若有相同的 SQL 语句执行则直接返回结果。

如果开启了查询缓存，先将查询结果做缓存操作
返回结果过多，采用增量模式返回

4.MySQL存储引擎

存储引擎在MySQL的体系架构中位于第三层，负责MySQL中的数据的存储和提取，是与文件打交道的子系统，它是根据MySQL提供的文件访问层抽象接口定制的一种文件访问机制，这种机制就叫作存储引擎。使用show engines命令，就可以查看当前数据库支持的引擎信息。

InnoDB：支持事务，具有提交，回滚和崩溃恢复能力，事务安全。（MySQL5.5开始的默认引擎）
MyISAM：不支持事务和外键，访问速度快（MySQL5.5之前的默认引擎，8.0已经完全丢弃MyiSAM引擎，将系统表也改成innoDB引擎）
Memory：利用内存创建表，访问速度非常快，因为数据在内存，而且默认使用Hash索引，但是一旦关闭，数据就会丢失
Archive：归档类型引擎，仅能支持insert和select语句
Csv：以CSV文件进行数据存储，由于文件限制，所有列必须强制指定not null，另外CSV引擎也不支持索引和分区，适合做数据交换的中间表
BlackHole: 黑洞，只进不出，进来消失，所有插入数据都不会保存
Federated：可以访问远端MySQL数据库中的表。一个本地表，不保存数据，访问远程表内容
MRG_MyISAM：一组MyISAM表的组合，这些MyISAM表必须结构相同，Merge表本身没有数据，对Merge操作可以对一组MyISAM表进行操作。

InnoDB和MyISAM对比

InnoDB和MyISAM是使用MySQL时最常用的两种引擎类型，来看下它们的区别

事务和外键 InnoDB支持事务和外键，具有安全性和完整性，适合大量insert或update操作 MyISAM不支持事务和外键，它提供高速存储和检索，适合大量的select查询操作
锁机制 InnoDB支持行级锁，锁定指定记录。基于索引来加锁实现。 MyISAM支持表级锁，锁定整张表。
索引结构 InnoDB使用聚集索引（聚簇索引），索引和记录在一起存储，既缓存索引，也缓存记录。 MyISAM使用非聚集索引（非聚簇索引），索引和记录分开。
并发处理能力 MyISAM使用表锁，会导致写操作并发率低，读之间并不阻塞，读写阻塞。 InnoDB读写阻塞可以与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发
存储文件 InnoDB表对应两个文件，一个.frm表结构文件，一个.ibd数据文件。InnoDB表最大支持64TB； MyISAM表对应三个文件，一个.frm表结构文件，一个MYD表数据文件，一个.MYI索引文件。从MySQL5.0开始默认限制是256TB。

适用场景： MyISAM

不需要事务支持（不支持）
并发相对较低（锁定机制问题）
数据修改相对较少，以读为主
数据一致性要求不高

InnoDB

需要事务支持（具有较好的事务特性）
行级锁定对高并发有很好的适应能力
数据更新较为频繁的场景
数据一致性要求较高
硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO

总结：两种引擎该如何选择？

是否需要事务？有，InnoDB
是否存在并发修改？有，InnoDB
是否追求快速查询，且数据修改少？是，MyISAM
在绝大多数情况下，推荐使用InnoDB

5.MySQL后台线程

MySQL后台线程

提到mysql后台线程，一般都是和innodb引擎有关，这里就暂不说明，后面在Innodb章节里面再详细说明。

分为4个线程
Master Thread
核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中,保持数据的一致性还包括脏页的刷新、合并插入缓存、undo页的回收
IO Thread
在InnoDB存储引擎中大量使用了AIO来处理IO请求,这样可以极大地提高数据库的性能，而I0Thread主要负责这些IO请求的回调。

Purge Thread
主要用于回收事务已经提交了的undolog，在事务提交之后，undolog可能不用了，就用它来回收。
Page Cleaner Thread
协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞。

MySQL线程池状态

Threads_cached：目前空闲的数据库连接数。
Threads_connected：当前数据库存活的数据库连接数。
Threads_created：MySQL-Server运行至今，累计创建的连接数。
Threads_running：目前正在执行的数据库连接数

对于4个字段很容易理解，额外要说明的一点是Threads_cached这个字段，从名称上来看，似乎跟缓存有关系，其实也没错，因为这里是有一个数据库内部的优化机制。当一个客户端连接断开后，对于数据库连接却不会立马销毁，而是会先放入到一个缓存连接池当中。这样就能在下次新连接到来时，省去了创建线程、分配栈空间等一系列动作，但这个值不会是无限大的，一般都在32左右。

MySQL线程池参数

thread_handling = one-thread-per-connection代表不采用线程池

MySQL线程池后面再详细说明。

线程参数

线程状态

mysql线程和操作系统线程

6号线程即是我当前操作的线程。

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2024-08-29 15:44:39 0x4d88 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 52 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2 srv_active, 0 srv_shutdown, 591468 srv_idle
srv_master_thread log flush and writes: 591470
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3
OS WAIT ARRAY INFO: signal count 3
RW-shared spins 0, rounds 4, OS waits 2
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 4.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 5916931
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283747593713456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
696 OS file reads, 56 OS file writes, 7 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 2816552529
Log flushed up to   2816552529
Pages flushed up to 2816552529
Last checkpoint at  2816552520
0 pending log flushes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137297920
Dictionary memory allocated 134957
Buffer pool size   8192
Free buffers       7493
Database pages     699
Old database pages 278
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 664, created 35, written 39
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 699, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=21820, Main thread ID=12916, state: sleeping
Number of rows inserted 17, updated 0, deleted 0, read 26
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)