【MySQL】第1节｜全面理解MySQL架构

快速安装MySQL

使用Docker快速安装mysql8

docker run  -d  \
--name mysql8 \
--privileged=true \
--restart=always \
-p 13306:3306 \
-v /home/mysql8/data:/var/lib/mysql \
-v /home/mysql8/config:/etc/mysql/conf.d  \
-v /home/mysql8/logs:/logs \
-e MYSQL_ROOT_PASSWORD=123456 \
-e TZ=Asia/Shanghai mysql:8.0.42 \
--lower_case_table_names=1

--name mysql8：给容器命名

--privileged=true：给予容器额外的权限。这意味着容器将拥有主机系统的所有权限

--restart=always：服务器启动时，自启动

-p 13306:3306：端口映射，第一个 13306 是映射出去的端口，第二个 3306 是这个容器的端口

-v /home/mysql8/data:/var/lib/mysql：文件挂载， 前为宿主机的目录位置，后为容器内文件对应位置

-e MYSQL_ROOT_PASSWORD=123456：环境变量设置，此处是设置 ROOT 用户登录密码

-e TZ=Asia/Shanghai mysql：此处是设置 MySQL 的时区。

--lower_case_table_names=1：让表名忽略大小写

使用最新的docker镜像默认开启了root用户的远程访问权限

MySQL 8.4新特性

MySQL 8.4 是 MySQL 8 系列的最新长期支持版本（LTS），相较于 MySQL 8.0，它在性能、安全性、复制机制、高可用性等方面进行了多项优化和改进。以下是 MySQL 8.4 的主要新特性：

---

1. 复制与高可用性增强

• GTID 复制优化：GTID（全局事务标识符）机制进一步优化，提升事务追踪的可靠性，减少主主同步中的数据不一致问题。
• 半同步复制改进：增强半同步复制（Semi-synchronous Replication），确保至少一个从节点确认事务后才返回成功，提高数据安全性。
• 主从复制参数调整：
  • SOURCE_RETRY_COUNT 默认值从 86400 调整为 10，减少重试次数。
  • SOURCE_CONNECT_RETRY 默认 60 秒，优化网络波动时的重连机制。
• MGR（组复制）改进：
  • group_replication_consistency 默认值从 EVENTUAL 改为 BEFORE_ON_PRIMARY_FAILOVER，提高一致性。
  • group_replication_exit_state_action 默认改为 OFFLINE_MODE，增强故障恢复能力。
• Clone 插件兼容性放宽：允许同一大版本（如 8.4.x）不同小版本间克隆，简化滚动升级。

---

2. InnoDB 存储引擎优化

• 默认参数调整：
  • innodb_io_capacity 默认值从 200 提升至 10000，适配 SSD 存储。
  • innodb_buffer_pool_instances 动态调整，优化内存管理。
  • innodb_adaptive_hash_index 默认关闭（OFF），减少 DDL 不稳定性。
• 动态资源分配：自动识别容器环境（如 Docker/Kubernetes），调整 CPU 和内存使用策略。
• 数据一致性增强：优化 innodb_undo_log_truncate 和 CHECK TABLE EXTENDED，提升 GIS 数据完整性。

---

3. 安全与认证改进

• 默认禁用 mysql_native_password：推荐使用 caching_sha2_password 或 LDAP/Kerberos 认证。
• Windows LDAP 认证增强：支持 SASL 和 GSSAPI/Kerberos，提升企业级身份管理效率。
• 权限管理细化：新增 FLUSH_PRIVILEGES 和 OPTIMIZE_LOCAL_TABLE 权限，减少 SUPER 权限滥用。

---

4. 查询优化与统计信息

• 子查询优化：支持更多量化比较谓词（如 >ANY、<ALL）转换为派生表连接，提升复杂查询性能 15%-20%。
• 直方图自动更新：支持 ANALYZE TABLE ... AUTO UPDATE，动态调整统计信息。
• JSON 功能增强：优化 JOIN 和 GROUP BY 操作中的 JSON 列处理。

---

5. 废弃与移除的功能

• 废弃 expire_logs_days：改用 binlog_expire_logs_seconds（默认 604800 秒，即 7 天）。
• 移除 binlog_transaction_dependency_tracking：统一采用 COMMIT_ORDER 模式提升复制性能。
• 移除 MASTER_* 和 SLAVE_* 关键字：替换为 SOURCE_* 和 REPLICA_*。

---

6. 其他改进

• 线程池优化：新增 tp_connections 表，细化连接池监控。
• 性能模式增强：优化 data_locks 和 data_lock_waits 表查询效率。
• 自动故障转移：通过 super_read_only 和 read_only 提升高可用性。

---

升级建议

• 兼容性评估：检查是否依赖废弃功能（如 expire_logs_days、MASTER_* 语法）。
• 性能测试：验证 InnoDB 自动资源分配和复制优化效果。
• 安全迁移：优先使用 caching_sha2_password 或 LDAP 认证。

MySQL 8.4 在性能、安全性和高可用性方面均有显著提升，适合需要长期稳定支持的企业环境。如需更详细的技术细节，可参考官方文档或相关社区文章。

MySQL 9.0新特性

MySQL 9.0 是 MySQL 8.0 之后的一个重要创新版本（Innovation Release），引入了多项新功能，主要聚焦于向量数据处理、JSON 增强、安全性改进以及性能优化。以下是 MySQL 9.0 的主要新特性：

---

1. 向量数据类型（VECTOR）支持

• 向量存储：新增 VECTOR 列类型，支持存储多维数据（4 字节浮点值），适用于机器学习、AI 知识库等场景。默认最大维度为 2048，最高支持 16383。

CREATE TABLE vectors (id INT, embedding VECTOR(512));

• 向量函数：
  • STRING_TO_VECTOR() / TO_VECTOR()：将字符串转为向量。
  • VECTOR_TO_STRING() / FROM_VECTOR()：将向量转为字符串。
  • VECTOR_DIM()：获取向量维度。
• 限制：
  • 不能用作主键/外键。
  • 仅支持向量间的相等比较，不支持与其他类型比较。

---

2. JSON 数据处理增强

• EXPLAIN ANALYZE 的 JSON 输出存储：支持将执行计划 JSON 存入变量，便于分析。

EXPLAIN ANALYZE FORMAT=JSON INTO @plan SELECT * FROM users;

• JSON 函数优化：提升 JSON 类型在 JOIN 和 GROUP BY 中的处理效率。

---

3. 安全性改进

• 移除 mysql_native_password：默认禁用该插件，强制使用更安全的 caching_sha2_password 或 LDAP/Kerberos 认证。
• Windows LDAP 认证增强：支持 SASL/GSSAPI，提升企业级集成能力。

---

4. 性能优化与监控

• Performance Schema 新增表：
  • variables_metadata：记录系统变量信息（名称、作用域、范围等）。
  • global_variable_attributes：存储全局变量属性。
• 预处理语句支持事件 DDL：允许动态生成 CREATE/ALTER/DROP EVENT 语句。

PREPARE stmt FROM 'CREATE EVENT cleanup ON SCHEDULE EVERY 1 DAY DO DELETE FROM logs';

---

5. 其他改进

• 隐式外键约束：简化外键语法，支持省略列名引用主键。

CREATE TABLE orders (user_id INT REFERENCES users);  -- 隐式引用 users(id)

• Windows 安装优化：修复安装向导问题，提升体验。
• 子查询优化：放宽 LIMIT 1 子查询的派生表转换限制。

---

废弃与移除

• 移除 ER_SUBQUERY_NO_1_ROW 错误：调整 IGNORE 子查询行为。
• 弃用 variables_info 表的 MIN_VALUE/MAX_VALUE 列：改用 variables_metadata。

---

争议与评价

• 向量支持“隐藏”发布：官方文档最初未提及，后更新说明，被质疑主要为 HeatWave 云服务铺路。
• 创新性争议：部分开发者认为更新缺乏突破性功能，对比 PostgreSQL 的向量插件略显保守。

---

升级建议

• 适用场景：适合需要向量计算、JSON 深度处理或前沿技术尝鲜的环境。
• 生产谨慎：创新版更新频繁，长期稳定需求建议仍选 LTS（如 MySQL 8.4）。

如需更详细的技术细节，可参考 MySQL 9.0 官方文档 或相关社区分析。

查询语句的执行流程

MySQL 中词法分析和语法分析的区别

在 MySQL 的 SQL 语句处理过程中，词法分析(Lexical Analysis)和语法分析(Syntax Analysis)是两个关键但不同的编译阶段，它们共同将 SQL 语句转换为 MySQL 可以理解的内部结构。

词法分析 (Lexical Analysis)

主要任务

• 将 SQL 语句拆分为一系列有意义的标记(tokens)
• 识别 SQL 语句中的关键字、标识符、运算符、常量等基本元素

具体工作

1. 去除空格、注释等无关内容
2. 识别并分类：
   • 关键字(SELECT, FROM, WHERE等)
   • 标识符(表名、列名)
   • 常量(字符串、数字)
   • 运算符(=, >, <等)
   • 分隔符(逗号, 分号等)

输出结果

• token流：一系列带有类型和值的标记

示例

对于SQL语句：

SELECT id, name FROM users WHERE age > 18;

词法分析后的token流可能类似于：

[KEYWORD:SELECT], [IDENTIFIER:id], [COMMA], [IDENTIFIER:name], 
[KEYWORD:FROM], [IDENTIFIER:users], [KEYWORD:WHERE], 
[IDENTIFIER:age], [OPERATOR:>], [NUMBER:18], [SEMICOLON]

语法分析 (Syntax Analysis)

主要任务

• 检查token序列是否符合SQL语法规则
• 构建抽象语法树(AST - Abstract Syntax Tree)

具体工作

1. 验证token的排列是否符合SQL语法
2. 构建语法树结构，表示SQL语句的层次关系
3. 检查基本的语法错误(如缺少关键字、括号不匹配等)

输出结果

• 抽象语法树(AST)：表示SQL语句结构的树形数据结构

示例

对于上面的SQL语句，语法分析后可能生成如下AST结构：

SELECT_STATEMENT
├── SELECT_LIST
│   ├── COLUMN_REFERENCE(id)
│   └── COLUMN_REFERENCE(name)
├── FROM_CLAUSE
│   └── TABLE_REFERENCE(users)
└── WHERE_CLAUSE└── BINARY_EXPRESSION(>)├── COLUMN_REFERENCE(age)└── LITERAL(18)

主要区别

特性	词法分析	语法分析
关注点	识别基本语言单元	验证语句结构是否正确
输入	原始SQL字符串	词法分析产生的token流
输出	token序列	抽象语法树(AST)
错误检测	拼写错误、非法字符等	语法结构错误、关键字顺序错误
复杂度	相对简单	相对复杂
类比	识别句子中的单词	分析句子的语法结构

MySQL中的实现

在MySQL中：

• 词法分析主要由sql_lex.cc等文件实现
• 语法分析主要由sql_yacc.yy(Yacc语法文件)定义，编译后生成解析器
• 两者紧密配合，共同完成SQL语句的解析工作

词法分析器会先扫描SQL语句生成token流，然后语法分析器根据MySQL的SQL语法规则验证这些token的组合是否合法，并构建出可以进一步优化的查询表示结构。

更新语句的执行流程

黄色：Server层

绿色：存储引擎层

实践环节

演示事务隔离性的SQL

表结构及数据

CREATE TABLE `trans` (`id` int NOT NULL,`val` int DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;INSERT INTO `trans` (`id`, `val`) VALUES (1, 1);
INSERT INTO `trans` (`id`, `val`) VALUES (2, 2);

当前数据：

id	val
1	1
2	2

打开Navicat查询窗口A

start transaction with consistent snapshot;
select * from trans;
commit;

打开Navicat查询窗口B

start transaction with consistent snapshot;
update trans set val = val +1 where id = 1;
select * from trans;
commit;

打开Navicat查询窗口C

update trans set val = val +1 where id = 1;
select * from trans;

执行顺序

##1.窗口A执行
start transaction with consistent snapshot;
##2.窗口B执行
start transaction with consistent snapshot;
##3.窗口C执行
update trans set val = val +1 where id = 1;
select * from trans;
##3.窗口C结果
1 2
2 2
##4.窗口B执行
update trans set val = val +1 where id = 1;
select * from trans;
##4.窗口B结果
1 3
2 2
##5.窗口A执行
select * from trans;
##5.窗口A结果
1 1
2 2
##6.窗口B执行
commit;
##7.窗口C执行
select * from trans;
##7.窗口C结果
1 3
2 2
##8.窗口A执行
commit;
select * from trans;
##8.窗口A结果
1 3
2 2

如何恢复误删的表

恢复MySQL表中误删的数据（10条原始数据+1条新数据）

macos安装dokcer版本的mysql 8.0.42 缺少 mysqlbinlog 这个命令，无法验证

-- 1. 确认binlog是否启用
SHOW VARIABLES LIKE 'log_bin';-- 2. 查看当前binlog文件
SHOW MASTER STATUS;-- 3. 找到清空前的位置点（假设清空操作发生在'TRUNCATE'或'DELETE'语句执行时）
-- 使用mysqlbinlog工具查看日志内容：
mysqlbinlog --base64-output=DECODE-ROWS -v /var/lib/mysql/mysql-bin.00000X-- 4. 恢复到清空前的状态（保留最新插入的数据）
-- 先导出当前唯一的数据（保险起见）
mysqldump -u root -p your_database your_table --where="id=最新记录的ID" > last_record.sql-- 5. 从binlog恢复到清空前的状态
mysqlbinlog --start-datetime="清空前的时间" --stop-datetime="清空时的时间" /var/lib/mysql/mysql-bin.00000X | mysql -u root -p-- 6. 重新插入最后那条数据
mysql -u root -p your_database < last_record.sql