MySQL 数据库 7（Day55）

主题：InnoDB 存储层次（行/页/区/段/表空间）与 .ibd 文件组成、共享表空间 vs 独立表空间、不停服在线迁移表（传输表空间）、Undo Log 的产生保留与在线自动收缩、索引介绍（六大类型/聚簇与回表/最左匹配）与索引实验数据准备

---

结构总览

MySQL 数据库 7
  ├─ 温故知新：数据存储层次（应用内存 / OS Cache / 磁盘）
  ├─ 表空间与 .ibd 文件组成：行→页(16KB)→区(64页≈1MB)→段→表空间
  ├─ 共享表空间 vs 独立表空间（innodb_file_per_table）
  ├─ 在线迁移表：FOR EXPORT → 复制 .ibd/.cfg → DISCARD → IMPORT
  ├─ Undo Log：回滚 + MVCC、旧版本保留条件、在线自动收缩
  └─ 索引介绍与实验准备：索引类型、B+树定位、EXPLAIN 对比

关键要点

数据存储层次： MySQL 数据从应用程序内存 → 操作系统缓存（OS Cache）→ 磁盘逐层流转。查询时 InnoDB 先查缓冲池（Buffer Pool），未命中再经 OS Cache 读磁盘数据页；修改通常先改内存中的数据页，通过 Redo Log 保证可恢复性，之后再刷脏页落盘。

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';  -- 缓冲池大小
SHOW VARIABLES LIKE 'datadir';                  -- 数据目录
SHOW VARIABLES LIKE 'innodb_log%';              -- 日志相关配置

排错：不要以为 UPDATE 后数据立即写入 .ibd 文件（观察文件修改时间不可靠）；加内存不会让所有查询永久变快——还要看缓冲池命中率、是否走索引、磁盘与锁等待。

表空间层次结构与 .ibd 文件组成：

行 Row（一条记录）
  ↓ 多行记录组成
页 Page：默认 16 KB，一次磁盘 I/O 的基本单位
  ↓ 64 个连续页组成
区 Extent：16KB × 64 = 1 MB
  ↓ 多个区组成
段 Segment（管理数据段或索引段）
  ↓ 多个段及管理信息组成
表空间 Tablespace → 以 .ibd 文件等形式保存

一页能存多少行不固定——由行记录大小、变长字段和页内管理信息共同决定，行越小存得越多。Compact 行格式（MySQL 5.0 引入）保存记录头信息、变长字段长度、空值信息和实际列数据，比旧格式更省空间。

SHOW VARIABLES LIKE 'innodb_page_size';   -- 默认 16384（16KB）
SHOW TABLE STATUS LIKE 't_space';         -- 表的引擎/行数/空间
SHOW CREATE TABLE t_space;

排错：「每页固定存 7992 行」是错误理解；「一个区只属于一张表」也不对——区是空间分配单位，由段和表空间统一管理；禁止直接删除 .ibd 文件删表，必须用 DROP TABLE / TRUNCATE TABLE 保持数据字典一致。

共享表空间 vs 独立表空间：

对比项目	共享表空间	独立表空间
存储方式	多张表共用系统表空间（如 ibdata1）	每表一个 .ibd 文件
文件管理	集中管理	可按表管理
删表后空间	通常不归还操作系统	删表/重建可释放文件空间
表迁移	不便单独迁移	支持按表导出导入
配置	innodb_file_per_table=OFF	innodb_file_per_table=ON（现代默认）

关键认知：

SHOW VARIABLES LIKE 'innodb_file_per_table';
 
-- 查看表的数据/索引空间与空闲碎片
SELECT table_schema, table_name, engine, table_rows,
       data_length, index_length, data_free
FROM information_schema.tables
WHERE table_schema = 'tablespace_demo';

⚠️ 修改 innodb_file_per_table 只影响新建表，已有表要靠 ALTER TABLE ... ENGINE=InnoDB 重建迁移。DELETE 只删逻辑记录，.ibd 文件不会自动缩小——空间留给该表复用，要归还操作系统需重建表。

在线迁移表（不停服，传输表空间）： 前提是源端与目标端表结构完全一致且都是 InnoDB。流程：源端锁表导出 → 复制文件 → 目标端丢弃表空间 → 放入文件 → 导入。

-- ① 源端：刷新表并获取元数据锁（生成 .cfg + 锁定写入）
FLUSH TABLES user_account FOR EXPORT;
 
-- ② shell：复制 .ibd 和 .cfg 到目标机器
-- cp /var/lib/mysql/source_db/user_account.{ibd,cfg} /backup/
-- scp 后放到目标库目录并 chown mysql:mysql
 
-- ③ 源端：复制完成后解锁
UNLOCK TABLES;
 
-- ④ 目标端：建好结构一致的空表后，丢弃本地表空间
ALTER TABLE user_account DISCARD TABLESPACE;
 
-- ⑤ 文件就位后导入表空间并验证
ALTER TABLE user_account IMPORT TABLESPACE;
SELECT * FROM user_account;

排错：IMPORT 报表空间不存在/文件错误 → 确认已先 DISCARD、检查文件路径属主权限；只拷了 .ibd 没拷 .cfg → 传统传输表空间两者都要；迁移期间源端仍有写入 → 必须在 FOR EXPORT 之后才能拷贝。结构不一致是最常见失败原因，用 SHOW CREATE TABLE 逐一比对字段顺序/类型/索引/字符集。

Undo Log 的产生、保留与在线自动收缩： Undo 记录修改前的数据版本，两大用途——①事务回滚恢复原值；②MVCC 让其他事务按隔离级别读到旧版本。事务提交后 Undo 不一定立即删除：只要还有活动事务需要读旧版本，就必须保留；没有读者时才具备回收条件，空间达到阈值后由后台执行自动截断收缩。

SHOW VARIABLES LIKE 'innodb_undo%';                 -- Undo 配置
SELECT * FROM information_schema.innodb_trx;        -- 当前事务（重点看开始时间）
SELECT * FROM performance_schema.data_lock_waits;   -- 锁等待
SHOW PROCESSLIST;

[mysqld]
innodb_undo_tablespaces=2          # 独立 Undo 表空间个数
innodb_undo_log_truncate=ON        # 开启在线自动收缩
innodb_max_undo_log_size=1073741824  # 超过 1GB 触发截断

排错：提交后 Undo 空间没变小 → 查长事务/未提交事务（长事务是 Undo 膨胀头号原因）；Undo 文件大就直接删文件是绝对禁止的——由 InnoDB 管理回收；开启 truncate 后也不会立即收缩，要满足大小阈值、版本清理和后台任务条件。

索引介绍： 索引（Index，也称 Key）是加速查找的数据结构。无索引时从第一行逐行比对（全表扫描 Full Table Scan）；有索引时先在索引结构中定位，再按定位信息取数据，大幅减少扫描范围。

索引类型	特点
主键索引	主键约束自动创建，唯一 + 非空；InnoDB 中即聚簇索引
唯一索引	值不重复，允许 NULL
普通索引	无唯一性要求，纯加速
联合索引	多字段组成，如 (name, age)，遵循最左匹配
全文索引	文本搜索场景
空间索引	空间数据类型

InnoDB 主键索引是聚簇索引（数据按主键组织）；二级（普通）索引叶子节点只存索引字段 + 主键值，查完整记录需按主键回表到聚簇索引再取一次。

CREATE INDEX idx_user_info_name ON user_info (name);      -- 普通
CREATE UNIQUE INDEX uk_user_name ON user_info (name);     -- 唯一
CREATE INDEX idx_city_age ON user_info (city, age);       -- 联合
DROP INDEX idx_city_age ON user_info;                     -- 删除
 
EXPLAIN SELECT * FROM user_info WHERE name = 'egon';      -- 看是否走索引
SHOW INDEX FROM user_info;

最左匹配示例（联合索引 (name, age, city)）：WHERE name=.. ✅、WHERE name=.. AND age=.. ✅、只有 WHERE age=.. ❌（缺最左列）。其他失效场景：对索引字段做函数运算、前置模糊 LIKE '%egon'（LIKE 'egon%' 可以走）、字段类型不一致引发隐式转换。索引不是越多越好——占空间且增加 INSERT/UPDATE/DELETE 维护成本。

索引实验数据准备要点： 数据量不能太小（否则优化器认为全表扫描更快）；条件字段要有足够重复值才能观察过滤效果；用 EXPLAIN 看优化器是否选了索引（关注 type/key/rows/Extra）；建索引前后 SQL 保持一致。

当日总结

• 数据在应用内存 → OS Cache → Buffer Pool → 磁盘间流转；性能取决于内存命中率与磁盘 I/O 次数
• InnoDB 层次：行 → 页（16KB，I/O 基本单位）→ 区（64 连续页 ≈ 1MB）→ 段 → 表空间（.ibd 文件）
• 页容纳行数随记录大小变化；区是分配单位，不专属某张表；禁止直接删 .ibd 删表
• 共享表空间多表共用以至难迁移；独立表空间（innodb_file_per_table=ON）每表一个文件，便于管理与迁移
• DELETE 不缩文件；改 file_per_table 只影响新表；回收空间靠 ALTER TABLE 重建
• 在线迁移五步：FLUSH … FOR EXPORT → 拷 .ibd+.cfg → UNLOCK → DISCARD → IMPORT，前提两端表结构一致
• Undo 服务回滚 + MVCC；提交后旧版本被活动事务依赖就不能回收；长事务导致 Undo 膨胀；innodb_undo_log_truncate=ON 自动收缩
• 索引六类：主键/唯一/普通/联合/全文/空间；二级索引叶子存主键需回表；联合索引最左匹配；函数运算/前置 %/隐式转换使索引失效
• EXPLAIN 对比建索引前后执行计划是验证手段；小表优化器可能放弃索引

相关页面

• MySQL 概念页 — 本课核心概念页（表空间细化/在线迁移/Undo 收缩/索引分类）
• MySQL 数据库 6（Day54） — 上一课（授权体系/InnoDB 逻辑架构：Buffer Pool/Redo/Undo/双写缓冲），本课物理存储结构的直接前置
• MySQL 数据库 5（Day53） — 批量插入 300 万条（本课索引实验数据准备的同类手法）
• 数据库导学（Day48） — 索引与慢查询优化路线图