MySQL-基本概念

数据库组成

数据

描述事物的符号记录为数据（Data），包括数字、文字、图形、图像、声音、档案记录等，以“记录”形式统一的格式进行储存

表

将不同的记录组织在一起，就形成了“表”，是用来存储具体数据的

数据库

表的集合，是存储数据的仓库，以一定的组织方式存储的互相有关的数据

数据库管理系统

实现对数据库资源有效组织、管理和存取的系统软件，具有以下功能：数据库的建立和维护功能、数据定义功能、数据操纵功能、运行管理功能、通信功能。

数据库系统

是一个人-机系统，由硬件、操作系统、数据库、DBMS、应用软件和数据库用户组成。用户可以通过 DBMS 操作数据库，也可以通过应用程序操作数据库

当今主流数据库介绍

MySQL

特点：开源、易用、高性能，适用于读密集型应用，支持分布式数据库和ACID事务。
适用场景：Web应用、内容管理系统（CMS）、中小型企业应用。
优势：成本低、社区支持强大，适合预算有限的项目。

PostgreSQL

特点：开源、功能丰富，支持复杂查询、JSON数据类型、GIS扩展（PostGIS），强调数据完整性和可靠性。
适用场景：地理信息系统（GIS）、金融系统、复杂数据分析。
优势：高度可扩展，支持自定义数据类型和函数。

Microsoft SQL Server

特点：商业数据库，与Windows生态系统深度集成，支持T-SQL语言，提供商业智能工具（SSIS、SSAS、SSRS）。
适用场景：企业级应用、Windows环境下的业务系统、云服务（Azure SQL Database）。
优势：高可用性（Always On）、安全性强。

Oracle Database

特点：企业级数据库，高性能、高可用性（RAC集群），支持PL/SQL编程，具备强大的数据仓库功能。
适用场景：大型企业系统、银行、电信等关键业务。
优势：严格的商业许可模式，适合高并发、大规模数据处理。

IBM Db2

特点：分布式数据库解决方案，支持多用户查询不同DBMS中的数据，适用于大型企业环境。
适用场景：金融、电信、制造业等需要高稳定性的行业。
优势：跨平台支持，适用于复杂业务逻辑。

SQLite

特点：轻量级、嵌入式数据库，遵守ACID原则，占用资源极少。
适用场景：移动应用、嵌入式设备、小型应用。
优势：无需服务器，单文件存储，适合低内存环境。

国产数据库（如达梦、金仓、PolarDB）

特点：近年来快速发展，部分产品在性能和稳定性上已达到国际水平，尤其在政府、金融、能源行业应用广泛。
适用场景：信创产业、核心系统国产化替代。
优势：政策支持，本地化服务优化

MySQL 数据库介绍

MySQL 是一款深受欢迎的开源关系型数据库，2006 年有 SUN 公司收购，2008 年 Oracle公司又收购了 SUN，因此是 Oracle 旗下的产品，遵循了 GPL 协议，可以免费试用与修改。

特点

性能卓越、服务稳定
开源、无版权限制、成本低
多线程、多用户
基于 C/S（客户端/服务器）架构
安全可靠

MySQL 商业版与社区版

MySQL 商业版是由 MySQL AB 公司负责开发与维护，需要付费才能使用。
MySQL 社区版是由分散在世界各地的 MySQL 开发者、爱好者一起开发与维护，可以免费使用。

两者区别：

商业版组织管理与测试环节更加严格，稳定性更好
商业版不遵守 GPL
商业版可获得 7*24 小时的服务器，如故障维护与打补丁等

MySQL 产品阵营

第一阵营：5.0-5.1 阵营，可以说是早期产品的延续。
第二阵营：5.4-5.7 阵营，更好地整合了 MySQL AB 公司、社区、第三方公司的存储引擎，从而提高性能。
第三阵营：6.0-7.1 阵营，就是 MySQL Cluster 版本，为适应新时代集群对数据库的需求
而开发的。

MySQL 架构原理

MySQL数据库采用的是一种叫B树的存储方式，根节点枝节点和叶子节点都是存储键和值，在生成b树时，先生成叶子节点，在生成枝节点，在生成根节点。

MySQL 数据库索引采用B+树算法，只有叶子节点存储数据，枝节点和根节点只存储索引的键，也就可以存储更多的索引，所有的是数据都存储在了叶子节点，叶子节点增大了，为了增加在叶子节点找到数据的速度，在每个叶子节点上增加了相邻叶之间的指针。

整体架构设计

MySQL 采用分层架构设计

tex

+-----------------------+
|       Connectors      |  (客户端连接器)
+-----------------------+
|        Services       |  (服务层)
+-----------------------+
|      SQL Interface    |  (SQL接口)
| Parser & Optimizer    |  (解析器和优化器)
|       Caches          |  (查询缓存)
+-----------------------+
|    Storage Engines    |  (存储引擎层)
+-----------------------+
|  File System & Logs   |  (文件系统和日志)
+-----------------------+

InnoDB 存储引擎核心组件

tex

+-------------------------------+
|          Buffer Pool          |  (缓冲池)
+-------------------------------+
|      Change Buffer            |  (变更缓冲)
+-------------------------------+
|      Adaptive Hash Index      |  (自适应哈希索引)
+-------------------------------+
|      Log Buffer               |  (日志缓冲)
+-------------------------------+
|      Doublewrite Buffer       |  (双写缓冲)
+-------------------------------+

事务处理流程

mermaid

graph LR
    A[开始事务] --> B[执行SQL]
    B --> C[生成Undo Log]
    C --> D[修改内存数据]
    D --> E[写入Redo Log Buffer]
    E --> F[准备提交]
    F --> G[写入Binlog]
    G --> H[提交Redo Log]
    H --> I[事务完成]

日志系统关系图

tex

+-------------------+       +-------------------+       +-------------------+
|     Redo Log      |       |     Undo Log      |       |     Binlog        |
| (物理日志)        |       | (逻辑日志)        |       | (逻辑日志)        |
| 崩溃恢复使用      |       | 事务回滚使用      |       | 复制/恢复使用     |
+-------------------+       +-------------------+       +-------------------+
        |                           |                           |
        v                           v                           v
+---------------------------------------------------------------------+
|                           存储引擎层                                |
+---------------------------------------------------------------------+

SQL语句类型

英文简称	中文名称	作用	主要命令
DDL	数据定义语言	创建、修改、删除数据库对象（表、索引等）	`CREATE`、`ALTER`、`DROP`、`TRUNCATE`
DQL	数据查询语言	从数据库查询数据	`SELECT`
DML	数据操作语言	增、删、改表中的数据	`INSERT`、`UPDATE`、`DELETE`、`MERGE`
DCL	数据控制语言	管理数据库权限	`GRANT`、`REVOKE`
TCL	事务控制语言	管理数据库事务	`COMMIT`、`ROLLBACK`、`SAVEPOINT`

SQL 语句执行过程

tex

客户端
   ↓
接收请求（连接管理器）
   ↓
查询缓存（MySQL 8.0 之后已废弃）
   ↓
SQL 解析（词法分析、语法分析）
   ↓
查询优化器（生成执行计划）
   ↓
执行器（根据执行计划调度存储引擎）
   ↓
存储引擎（比如 InnoDB，负责数据读写）
   ↓
返回结果

1. 连接器

客户端连接：应用程序通过连接器(Connector)建立与MySQL服务器的连接
认证授权：MySQL验证用户名、密码和主机权限
分配资源：服务器为连接分配线程、内存等资源
会话初始化：设置会话变量、字符集等环境参数

2. 查询缓存

检查SQL是否在缓存中存在
验证用户是否有权限访问缓存结果
如果命中则直接返回结果（跳过后续所有步骤）

CAUTION

注意：MySQL 8.0+已移除查询缓存，原因：

表数据任何修改都会使相关缓存失效
高并发下缓存管理开销大于收益
现代应用更推荐使用Redis等外部缓存

3. SQL 解析器

词法分析：将SQL语句分解为标记(tokens)
语法分析：检查SQL语法正确性，构建解析树
语义分析：验证表、列是否存在，检查权限

4. 查询优化器

优化器决定：
- 使用哪个索引？
- 执行顺序如何？
- 是走全表扫描还是范围查询？
生成一个最优的执行计划（不是最完美，但通常够用）

5. 执行器

将优化后的查询转换为可执行的操作序列
生成具体的执行计划树

6. 存储引擎层

存储引擎交互：
- InnoDB缓冲池(Buffer Pool)查找数据
- 必要时从磁盘读取数据页
数据处理：
- 执行过滤、排序、分组等操作
- 可能使用临时表处理复杂查询
锁管理：
- 对DML操作获取适当的锁
- 处理锁冲突

6. 结果返回阶段

格式化查询结果
通过网络协议返回给客户端
对于大结果集可能分批返回

7. 日志记录阶段

写入undo log：记录修改前的数据，用于回滚
写入redo log：确保事务持久性
binlog记录（如果启用）：用于复制和时间点恢复

8. 资源清理阶段

释放锁资源
清理临时表
更新统计信息（可能异步）
重置会话状态

MySQL 5.7 与 8.0+ 版本核心差异详解

架构与性能改进

数据字典重构

5.7：使用文件存储元数据（.frm文件）

8.0+：采用事务型数据字典（InnoDB存储）

sql

-- 8.0+可查询数据字典
SELECT * FROM information_schema.innodb_tables WHERE name LIKE '%user%';

原子DDL

5.7：DDL操作可能中断导致元数据不一致

8.0+：支持原子DDL（完全成功或完全回滚）

sql

-- 8.0+的原子操作示例
CREATE TABLE t1 (id INT) ENGINE=InnoDB;
DROP TABLE t1, t2;  -- 要么全部删除，要么全部保留

SQL语法差异

复制命令变更

操作	5.7语法	8.0+语法
查看主库状态	`SHOW MASTER STATUS`	`SHOW BINARY LOG STATUS`
启动复制	`START SLAVE`	`START REPLICA`
重置复制	`RESET SLAVE`	`RESET REPLICA`

窗口函数支持

sql

-- 8.0+专有功能
SELECT 
  name, 
  salary,
  RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as dept_rank
FROM employees;

安全增强

认证插件

5.7默认：mysql_native_password

8.0+默认：caching_sha2_password

sql

-- 8.0创建用户
CREATE USER 'app'@'%' IDENTIFIED WITH caching_sha2_password BY 'password';

-- 兼容旧客户端
CREATE USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

密码策略

sql

-- 8.0+密码复杂度要求
SET GLOBAL validate_password.policy = STRONG;
ALTER USER 'user'@'host' IDENTIFIED BY 'NewPass123!';

InnoDB引擎改进

自增计数器持久化

5.7：服务器重启可能重置自增值

8.0+：自增值持久化到redo日志

sql

-- 8.0+行为
INSERT INTO t1 VALUES(NULL);  -- id=1
INSERT INTO t1 VALUES(NULL);  -- id=2
-- 重启后
INSERT INTO t1 VALUES(NULL);  -- id=3 (5.7可能重新从1开始)

并行查询

sql

-- 8.0+并行扫描
SET SESSION innodb_parallel_read_threads = 4;
SELECT * FROM large_table WHERE conditions;

JSON功能增强

JSON路径表达式

sql

-- 8.0+ JSON操作
UPDATE products 
SET attributes = JSON_SET(attributes, '$.color', 'blue') 
WHERE id = 100;

生成列支持

sql

-- 8.0+ JSON生成列
CREATE TABLE orders (
  id INT PRIMARY KEY,
  info JSON,
  customer_name VARCHAR(100) AS (info->>'$.name') STORED
);

配置参数变化

重要参数变更

参数	5.7	8.0+
默认字符集	latin1	utf8mb4
事务隔离级别默认值	REPEATABLE-READ	REPEATABLE-READ (但优化了锁机制)
组复制配置	插件方式	内置支持

备份恢复差异

克隆插件

sql

-- 8.0+专属功能
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
CLONE LOCAL DATA DIRECTORY = '/backup/mysql_clone';

撤销日志压缩

sql

-- 8.0+减少备份大小
SET GLOBAL innodb_undo_log_encrypt=ON;
SET GLOBAL innodb_undo_log_compress=ON;

升级注意事项

兼容性检查：
sql
```
mysql_upgrade --check-version
```
回滚方案：
- 8.0→5.7不支持直接降级
- 需要逻辑备份恢复

驱动兼容性：

JDBC连接串需添加参数：

ini

jdbc:mysql://host:3306/db?useSSL=false&allowPublicKeyRetrieval=true

如需针对特定场景的详细迁移方案，请提供您的具体应用架构和使用场景。

MySQL-基本概念 ​

数据库组成 ​

数据 ​

表 ​

数据库 ​

数据库管理系统 ​

数据库系统 ​

当今主流数据库介绍 ​

MySQL ​

PostgreSQL ​

Microsoft SQL Server ​

Oracle Database ​

IBM Db2 ​

SQLite ​

国产数据库（如达梦、金仓、PolarDB） ​

MySQL 数据库介绍 ​

特点 ​

MySQL 商业版与社区版 ​

MySQL 产品阵营 ​

MySQL 架构原理 ​

整体架构设计 ​

InnoDB 存储引擎核心组件 ​

事务处理流程 ​

日志系统关系图 ​

SQL语句类型 ​

SQL 语句执行过程 ​

1. 连接器 ​

2. 查询缓存 ​

3. SQL 解析器 ​

4. 查询优化器 ​

5. 执行器 ​

6. 存储引擎层 ​

6. 结果返回阶段 ​

7. 日志记录阶段 ​

8. 资源清理阶段 ​

MySQL 5.7 与 8.0+ 版本核心差异详解 ​

架构与性能改进 ​

数据字典重构 ​

原子DDL ​

SQL语法差异 ​

复制命令变更 ​

窗口函数支持 ​

安全增强 ​

认证插件 ​

密码策略 ​

InnoDB引擎改进 ​

自增计数器持久化 ​

并行查询 ​

JSON功能增强 ​

JSON路径表达式 ​

生成列支持 ​

配置参数变化 ​

重要参数变更 ​

备份恢复差异 ​

克隆插件 ​

撤销日志压缩 ​

升级注意事项 ​