大家好，我是程序员强子。

天天和 MySQL 打交道，CRUD 写得飞起~ 但关于它的底层原理，很多同学还停留在表面认知~

就像 荣耀里想上巅峰 1800 以上，不单单每个英雄的各种进阶连招，还包括 意识 等等都要去深入学习~反复练

来看看接下来要练习的细节：

• 数据模型与操作：特殊场景类型选型海量数据操作规范(大表删字段，删数据，新增索引 等)

• 版本差异（8.0 vs 5.7）：架构升级 + SQL 增强

• 整体架构：三层架构核心分工（客户端层 / 服务器层 / 存储引擎层）

系好安全带，出发咯~

数据模型与操作

数据类型选型

DATETIME 和 TIMESTAMP 类型的区别是什么？

• 范围 DATETIME 支持 1000-01-01 00:00:00 到 9999-12-31 23:59:59；TIMESTAMP 支持 1970-01-01 00:00:01 到 2038-01-19 03:14:07

• 时区 DATETIME 存储原始字符串，不依赖时区；TIMESTAMP 存储 UTC 时间戳，查询时会根据数据库时区转换

• 自动更新 TIMESTAMP 可设置 ON UPDATE CURRENT_TIMESTAMP，更新行时自动刷新 DATETIME 需手动更新。记录 订单修改时间 用 TIMESTAMP（更新时自动刷新）；记录 用户生日，固定时间， 用 DATETIME

TEXT 类型最大可以存储多长的文本？

• TINYTEXT：255 字节（约 255 字符）

• TEXT：65535 字节（约 6.5 万字符）

• MEDIUMTEXT：16777215 字节（约 16MB）

• LONGTEXT：4294967295 字节（约 4GB）例：

存储文章摘要用 TEXT；存储完整小说或大段日志用 MEDIUMTEXT/LONGTEXT

在 MySQL 中存储金额数据，应该使用什么数据类型?

• 推荐 DECIMAL，避免 FLOAT/DOUBLE 的精度丢失

• 格式：DECIMAL(M, D)，M 总位数（含小数），D 小数位数。

• 商品价格用 DECIMAL(10,2)（支持最大 99999999.99 元），存储 "99.99" 时精确无误

varchar 和 char 有什么区别?

• 存储方式 char 是固定长度,定义多少存多少，不足补空格 varchar 是可变长度,按实际内容长度存储，加 1-2 字节记录长度

• 适用场景 char 适合短且固定长度（如手机号 char(11)）；varchar 适合长度不固定（如用户名 varchar(20)）。存储 "13800138000"，char(11) 占 11 字节；varchar(11) 占 12 字节（11 字节内容 + 1 字节长度标识）

MySQL 中 VARCHAR(100) 和 VARCHAR(10) 的区别是什么?

• 存储长度：实际都按内容长度 + 1-2 字节存储

• 限制：定义的长度是最大允许值（VARCHAR (10) 最多存 10 字符，超则截断）。

• 性能：大长度可能影响索引效率（索引树节点能存的条目更少）

MySQL 一张表最多可以有多少列?

• MySQL 理论上限 4096 列，但实际受行大小限制（默认行最大 65535 字节）

• 若用大字段（如 TEXT），列数会大幅减少。

• 若表中多列是 VARCHAR(2000)（每列约 2000 字节），可能最多只能建 30 列

大表数据操作

大表：千万级以上数据的表

大表删除字段/大表修改字段类型

常规操作会导致什么问题？

• 长时间业务阻塞： 执行操作期间，表会被锁住，所有对该表的 SELECT/INSERT/UPDATE/DELETE 操作全部阻塞

• 表重建引发的 IO/CPU 资源耗尽删除字段 / 修改字段类型属于 需重建表 的操作，数据库会创建临时表，将原表数据逐行复制到临时表，完成后替换原表

• 磁盘空间大量增加临时表需要占用与原表几乎相同的磁盘空间，若磁盘剩余空间不足，操作会失败并可能导致数据库宕机复制数据时产生的大量 redo/undo 日志会进一步占用磁盘空间

• 主从延迟大幅增加主库执行表结构变更耗时很长，从库同样要重建表，导致主从数据延迟从秒级变为小时级

底层原因是什么？

删除字段/修改字段类型属于 拷贝表（Copy Table） 类操作

需创建临时表，逐行复制原表数据到临时表，替换原表后删除临时表

解决方案有哪些？

• 分库分表：预先拆分大表，降低单表操作的影响范围；

• 影子表替换：创建新表→同步数据→切换读写→删除旧表，完全避免锁表

大表清除数据

如果使用 DELETE FROM 删除数据会有什么问题？（包括全量删除/部分删除）

• 全表 DELETE：耗时极长，执行期间表被锁，业务读写完全阻塞

• 事务日志暴涨：DELETE 是 DML 操作，逐行记录 redo/undo 日志，千万级数据会撑爆事务日志（ib_logfile），引发磁盘空间耗尽

• 索引维护开销大：删除数据时需逐行更新所有索引，IO 资源被完全占用；

• 主从延迟剧增：主库逐行删除的 binlog 会被从库重放，导致从库延迟从秒级变为小时级

全量删除解决方案：

• TRUNCATE TABLE ： 速度极快（千万级表仅需几秒），直接释放磁盘空间

• DROP TABLE + 重建表：速度最快（毫秒级完成），彻底释放所有磁盘空间

那按条件呢？ 有什么优化方案呢？

• 为删除条件字段建索引： 否则接下来的分批删除操作也会因全表扫描导致效率极低

• 分批删除可以通过 定时任务调用删除程序将大事务拆分为多个小事务，每次删除少量数据（如 1000-5000 行），避免长时间锁占用和日志暴涨

新增二级索引

过程是怎么样的？

• 准备阶段：获取 MDL（元数据锁）共享锁（Online DDL 模式），防止表结构被修改；

• 扫描阶段：全表扫描聚簇索引，逐行读取索引字段和主键；

• 构建阶段：将读取的数据插入新的 B + 树结构，处理节点分裂、平衡；

• 收尾阶段：短暂升级为 MDL 排他锁，更新数据字典，将新索引标记为可用。

资源消耗的根源是什么？

• IO 消耗：全表扫描需读取所有数据页（千万级表可能有数十万数据页），构建索引需写入新的索引页，随机 IO 变为顺序 IO 但总量极大；

• CPU 消耗：计算 B + 树节点的哈希值、平衡节点、排序索引键值（若索引需排序）；

比较好的解决方案 ，就是 选择业务低峰期操作

还有就是使用第三方工具：

• pt-online-schema-change（Percona）或 gh-ost（GitHub）

• 原理：创建影子表在影子表上建索引通过触发器同步原表的 DML 操作数据同步完成后替换原表

大表新增字段

MySQL 5.6 之前：新增字段属于 拷贝表操作，需全表拷贝数据到临时表

全程持有 MDL 排他锁，业务读写完全阻塞（千万级表可能阻塞数小时）；

MySQL 5.6+：新增 NULL 字段或 NOT NULL 带默认值 ，仅短暂锁表；

但新增 NOT NULL 无默认值仍需拷贝表，阻塞严重

所以字段最好避免新建 NOT NULL 无默认值字段

解决方案是什么？注意点是什么？

• 优先新增 NULL 字段或带默认值的 NOT NULL 字段

• 避免新增字段到表中间位置，新增字段到表中间（如 AFTER col1）会触发拷贝表操作

• 低峰期操作 + 资源监控

若需新增 NOT NULL 字段有什么方案？

1. 先新增 NULL 字段

2. 批量更新历史数据为默认值

3. 修改字段为 NOT NULL

大表操作的通用原则

1. 避开高峰：在业务低峰期（如凌晨）操作，降低影响；

2. 先小后大：先在测试环境（同数据量的镜像表）验证，再上生产；

3. 备份优先：操作前备份表（mysqldump 或物理备份），避免数据丢失；

4. 监控进度：用 show processlist 观察操作状态，发现异常及时 kill；

5. 优先工具：无停机窗口时，优先用 pt-online-schema-change 或 gh-ost（GitHub 开源工具），减少人工操作风险。

这些方案的核心是 规避全表锁，通过分批次、影子表、在线工具等方式，在不中断业务的前提下完成大表字段调整。

版本差异

核心架构与底层改进

彻底移除「查询缓存（Query Cache）」

旧版本存在什么问题？

5.7 及之前的查询缓存默认开启，但命中率极低

原因是 只要表被修改，缓存就会失效，频繁写场景下完全无用

且会占用额外内存、增加锁竞争（缓存更新需加锁）

8.0 版本改进了什么？

直接移除查询缓存相关代码

包括 query_cache_type、query_cache_size 等配置

避免无效资源消耗，简化架构

无需再纠结缓存配置，专注于索引优化和 SQL 本身；

若需缓存，需通过应用层 比如 Redis 实现

InnoDB 自增 ID 持久化

旧版本存在什么问题？

5.7 中 InnoDB 的自增计数器（AUTO_INCREMENT）存储在内存中

重启后会重新计算（从表中最大 ID+1 开始）

若重启前有未提交的 INSERT，可能导致自增 ID 回滚，引发主键冲突

比如：插入 ID=10 未提交，重启后 ID 从 9 开始，再次插入时 ID=9 冲突

8.0 版本改进了什么？

自增计数器持久化 到 InnoDB 系统表

表名： mysql.innodb_autoinc_lock_mode

重启后无需重新计算，确保自增 ID 连续且不重复

引入「数据字典（Data Dictionary）」

旧版本问题是什么？

5.7 及之前的元数据（表结构、索引信息、权限等）存储在磁盘文件

比如 .frm 表结构文件、mysql 数据库的 MyISAM 表中

分散且可靠性低，崩溃恢复时需逐一校验文件，效率低

8.0 版本改进了什么？

所有元数据统一存储在 InnoDB 引擎的系统表 中

比如 mysql.tables、mysql.columns

元数据操作通过事务保证原子性，崩溃恢复更快

且支持通过 SQL 直接查询元数据

InnoDB 支持「并行查询（Parallel Query）」

旧版本问题是什么？

5.7 中 InnoDB 的查询只能单线程执行

即使是全表扫描、大结果集排序等 CPU 密集型操作，也无法利用多核 CPU

8.0 版本改进了什么？

支持对「全表扫描、聚集索引扫描」的查询进行并行执行

默认关闭，需通过 innodb_parallel_read_threads 配置线程数，最大 16

将扫描任务拆分给多个线程，提升大表查询效率。

暂不支持索引扫描、JOIN 操作的并行化

仅针对纯扫描场景（如 SELECT COUNT(*) FROM big_table）

SQL 功能增强

新增「窗口函数（Window Functions）」

旧版本问题是什么？

5.7 中实现「排名、Top N、累计求和」等分析型需求时，需用复杂的子查询 + 变量

，代码难写且性能差

8.0 版本改进了什么？

• 排名函数：RANK()（跳跃排名）、DENSE_RANK()（连续排名）、ROW_NUMBER()（唯一排名）

• 聚合窗口函数：SUM() OVER ()、AVG() OVER ()（按窗口分组计算，不压缩结果集）；

• 偏移函数：LAG()（取前 N 行数据）、LEAD()（取后 N 行数据）。

这些排名函数有什么作用？

假设我们有 4 个学生的数学成绩：95 分（A）、90 分（B）、90 分（C）、85 分（D）

按分数降序排名，看看三个函数的结果差异

ROW_NUMBER ()

• 只看排序后的位置，每行生成一个唯一的序号，哪怕分数相同，序号也绝对不重复。

• 可以理解为：排队时不管身高一样不一样，按排队顺序挨个编 1、2、3、4 号

B 和 C 分数相同，但排名是 2 和 3（唯一，不并列）。

RANK ()

• 分数相同则并列排名，但后续不同分数的排名会跳过并列的数量，因为要 “算上并列的人数”

• 可以理解为：比赛中如果两个人并列第 2 名，下一个人直接是第 4 名（第 3 名空缺）

并列后排名 “跳跃”（2 之后不是 3，是 4）

DENSE_RANK ()

• 分数相同则并列排名，后续不同分数的排名连续不跳跃（只看 “不同分数的层级”）

• 可以理解为：比赛中两个人并列第 2 名，下一个人还是第 3 名（不空缺）

并列后排名 连续（2 之后是 3，不跳过）

再举一个更直观的例子（分数重复更多）

如果分数是：100、95、95、95、80，三个函数的排名结果：

聚合窗口函数是什么？

GROUP BY 我们之前经常接触~

作用是将数据按指定列分组，对每个分组执行聚合计算（如 SUM/AVG/COUNT），

最终只返回每个分组的聚合结果行

原始的明细数据会被压缩、丢失

而 聚合窗口函数 = 聚合函数（SUM/AVG 等） + 窗口子句（OVER ()）

在指定的数据窗口内聚合数据

但保留每一行的原始数据 （不合并行、不压缩结果集）

而 GROUP BY 会合并相同行

聚合窗口函数 VS Group By

JSON 功能大幅增强

旧版本问题是什么？

仅支持基础 JSON 操作

如 JSON_EXTRACT、JSON_SET，

但无法将 JSON 转为关系表，复杂 JSON 查询需嵌套函数

8.0 版本改进了什么？

查询提取类函数

-- 准备测试表（复用前文user_json表）CREATE TABLE user_json (  id INT PRIMARY KEY AUTO_INCREMENT,  user_info JSON,  hobbies JSON);INSERT INTO user_json (user_info, hobbies) VALUES(  '{"name": "张三", "age": 25, "address": {"city": "北京", "district": "朝阳区"}, "contact": {"phone": "13800138000"}}',  '["篮球", "游泳", "阅读"]');
-- 查询SQLSELECT     id,    JSON_VALUE(user_info, '$.name') AS name,  -- 提取标量（字符串）    JSON_VALUE(user_info, '$.age') AS age,    -- 提取标量（数字）    JSON_QUERY(user_info, '$.address') AS address_obj,  -- 提取对象（JSON类型）    JSON_EXTRACT(hobbies, '$[0]') AS first_hobby,       -- 提取数组元素    JSON_PRETTY(user_info) AS formatted_info            -- 格式化输出FROM user_json;

结果

JSON_TABLE： 将 JSON 数组拆分为关系型表

-- SQL：将hobbies数组转为每行一个爱好SELECT     u.id,    JSON_VALUE(u.user_info, '$.name') AS username,    jt.hobbyFROM user_json u,     JSON_TABLE(         u.hobbies,  -- 目标JSON数组字段         '$[*]'      -- 数组路径（$[*]表示所有元素）         COLUMNS (             hobby VARCHAR(20) PATH '
  -- 提取数组元素作为hobby列         )     ) jt;

结果：

整体架构

PS: 现在先混一下眼熟，这个是从宏观角度出发，后面每一个点都会深入讲解的~

客户端

核心负责连接管理和身份认证，不处理具体 SQL 逻辑

连接管理

• 建立、维护、关闭与服务器的**连接， **采用 TCP/IP 协议（默认 3306 端口）

• 频繁创建 / 销毁连接会消耗资源，实际应用中会用连接池，如 Java 的 HikariCP、Druid 等等

• 连接池配置 maximumPoolSize=100，意味着最多同时维持 100 个连接，超过的请求会排队等待，防止数据库过载

身份认证

• 身份验证（账号密码 / 插件验证）

• 权限校验（操作权限匹配）

• 安全防护（防越权 / 暴力破解）

• 审计追踪（登录行为记录）

服务器层

服务器层是 MySQL 的核心，负责接收 SQL 请求后

完成解析、优化、执行的全流程

不直接处理数据存储，而是交给存储引擎

SQL 解析

• 词法分析：拆分 SQL 关键字（如 SELECT、FROM）、表名、字段名、条件等

• 语法分析：检查 SQL 语法是否正确（如是否漏写 WHERE、括号是否匹配）

SQL 优化

• 选择索引

• 调整连接顺序

• 简化条件

• ...

执行器

根据优化器生成的执行计划，

调用存储引擎的接口 , 比如 读一行,读范围数据

执行实际的数据操作，并返回结果

存储引擎层

存储引擎层负责数据的实际存储、提取、锁管理等

基于 插件化设计，可根据需求选择不同引擎（如 InnoDB、MyISAM）

数据存储与提取

数据在磁盘（物理文件）和内存（缓存）中的存储形式

• InnoDB 以 **页 **为单位存储（默认 16KB / 页），数据和索引存在.ibd 文件中；

• 数据会先写入内存中的 缓冲池，后台线程再异步刷到磁盘的.ibd 文件，保证性能和可靠性

存储引擎插件化的意义

插件化允许针对不同业务场景选择最适合的引擎，避免 “一刀切”

• 日志表：业务场景是 写多读少，无需事务，用 MyISAM（插入速度快，不支持事务但开销小）；

• 订单表：业务场景是 需事务、高并发 ，用 InnoDB（支持事务和行锁）

• 临时统计表：用 Memory 引擎，数据存内存，速度快，重启丢失