ava 王者修炼手册【Mysql 篇 - 大表操作】：千万级大表核心操作原理与实战方案

DonaldCen666
44 阅读16分钟

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

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

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

2001978.jpg

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

  • 数据模型与操作：

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

  • 版本差异（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 号
学生分数ROW_NUMBER () 排名
A951
B902
C903
D854

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

RANK ()

  • 分数相同则并列排名，但后续不同分数的排名会跳过并列的数量，因为要 “算上并列的人数”
  • 可以理解为：比赛中如果两个人并列第 2 名，下一个人直接是第 4 名（第 3 名空缺）
学生分数RANK () 排名背后逻辑
A951最高分，排第 1
B902和 C 分数相同，并列第 2
C902和 B 分数相同，并列第 2
D854前面有 2 个并列第 2，所以跳过 3，直接排 4

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

DENSE_RANK ()

  • 分数相同则并列排名，后续不同分数的排名连续不跳跃（只看 “不同分数的层级”）
  • 可以理解为：比赛中两个人并列第 2 名，下一个人还是第 3 名（不空缺）
学生分数DENSE_RANK () 排名背后逻辑
A951最高分，第 1 层级
B902和 C 同层级，并列第 2
C902和 B 同层级，并列第 2
D853下一个层级，排 3（连续不跳）

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

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

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

分数ROW_NUMBER()RANK()DENSE_RANK()
100111
95222
95322
95422
80553

聚合窗口函数是什么？

GROUP BY 我们之前经常接触~

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

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

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

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

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

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

而 GROUP BY会合并相同行

聚合窗口函数 VS Group By

特性聚合窗口函数（SUM/AVG OVER ()）GROUP BY 聚合
结果集压缩不压缩（保留所有原始行）压缩（按分组合并行）
每行数据保留原始数据 + 聚合结果仅保留分组字段 + 聚合结果
适用场景需同时看原始数据和聚合结果仅需分组聚合结果

JSON 功能大幅增强

旧版本问题是什么？

仅支持基础JSON 操作

如 JSON_EXTRACT、JSON_SET，

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

8.0版本改进了什么？

查询提取类函数

函数作用
JSON_VALUE提取 JSON 中的标量值（字符串 / 数字 / 布尔），返回 SQL 原生类型
JSON_QUERY提取 JSON 中的对象 / 数组，返回 JSON 类型
JSON_EXTRACT增强路径支持比如 嵌套.address.city、数组[0]，兼容 5.7 但优化性能
JSON_PRETTY格式化 JSON 输出，提升可读性
-- 准备测试表（复用前文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"}}',
  '["篮球", "游泳", "阅读"]'
);

-- 查询SQL
SELECT 
    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.hobby
FROM user_json u,
     JSON_TABLE(
         u.hobbies,  -- 目标JSON数组字段
         '$[*]'      -- 数组路径（$[*]表示所有元素）
         COLUMNS (
             hobby VARCHAR(20) PATH '$'  -- 提取数组元素作为hobby列
         )
     ) jt;

结果：

idusernamehobby
1张三篮球
1张三游泳
1张三阅读

整体架构

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 引擎，数据存内存，速度快，重启丢失
avatar
文章
阅读
粉丝