主题
第五阶段：MySQL 架构与存储引擎
5.1 MySQL 整体架构
5.1.1 四层架构总览
MySQL Server 整体架构（分层视图）：
客户端
┌───────────┐ ┌───────────┐ ┌───────────┐ ┌───────────┐
│ mysql CLI │ │ JDBC/ODBC │ │ DBeaver │ │ App Code │
└─────┬─────┘ └─────┬─────┘ └─────┬─────┘ └─────┬─────┘
│ │ │ │
└──────┬──────┴──────┬──────┴──────┬──────┘
│ │ │
═════════════╪═════════════╪═════════════╪══════════════════
↓ ↓ ↓
┌──────────────────────────────────────────────────────────┐
│ 第一层：连接层 │
│ │
│ ┌─────────────┐ ┌─────────────┐ ┌──────────────────┐ │
│ │ 连接管理 │ │ 认证鉴权 │ │ 线程池（可选） │ │
│ │ TCP/Socket │ │ 用户名密码 │ │ Thread Pool │ │
│ │ 连接 │ │ 权限校验 │ │ 连接池 │ │
│ └─────────────┘ └─────────────┘ └──────────────────┘ │
└──────────────────────────┬───────────────────────────────┘
│
┌──────────────────────────┼───────────────────────────────┐
│ 第二层：服务层（SQL Layer） │
│ │
│ SQL 执行流程： │
│ ┌──────┐ ┌──────┐ ┌──────────┐ ┌──────┐ ┌──────┐ │
│ │解析器│→│预处理 │→│ 查询优化器│→│执行器│→│结果集│ │
│ │Parser│ │PreProc│ │Optimizer │ │Exec │ │Result│ │
│ └──────┘ └──────┘ └──────────┘ └──────┘ └──────┘ │
│ │
│ 其他组件： │
│ ┌──────────────┐ ┌────────────┐ ┌─────────────────────┐│
│ │ 查询缓存 │ │ 内置函数 │ │ 存储过程/视图/触发器││
│ │(8.0 已移除) │ │ 日期/数学 │ │ Procedures/Views ││
│ └──────────────┘ └────────────┘ └─────────────────────┘│
└──────────────────────────┬───────────────────────────────┘
│ Handler API
┌──────────────────────────┼───────────────────────────────┐
│ 第三层：引擎层（Storage Engine Layer） │
│ │
│ ┌────────┐ ┌────────┐ ┌────────┐ ┌────────┐ │
│ │ InnoDB │ │ MyISAM │ │ Memory │ │Archive │ ... │
│ │ ⭐ │ │ │ │ │ │ │ │
│ └───┬────┘ └───┬────┘ └───┬────┘ └───┬────┘ │
│ │ │ │ │ │
└──────┼───────────┼───────────┼───────────┼──────────────┘
│ │ │ │
┌──────┼───────────┼───────────┼───────────┼──────────────┐
│ ↓ ↓ ↓ ↓ │
│ 第四层：存储层 │
│ │
│ ┌──────────────────────────────────────────────────┐ │
│ │ 文件系统（磁盘 / SSD） │ │
│ │ │ │
│ │ .ibd 数据文件 .frm 表结构 redo log undo log │ │
│ │ binlog relay log slow log error log │ │
│ └──────────────────────────────────────────────────┘ │
└──────────────────────────────────────────────────────────┘5.1.2 SQL 执行全流程
一条 SQL 从发送到返回结果，经过以下完整路径：
SELECT * FROM users WHERE id = 1; 这条 SQL 的完整执行流程：
┌──────────┐
│ 1. 连接器 │ 验证用户名密码、获取权限
└────┬─────┘
↓
┌──────────┐
│ 2. 查询 │ MySQL 8.0 已移除此步骤
│ 缓存 │ (8.0 之前：命中缓存直接返回)
└────┬─────┘
↓
┌──────────┐ SQL 语句 → 语法树（AST）
│ 3. 解析器 │
│ Parser │ 词法分析：识别关键词 SELECT, FROM, WHERE
│ │ 语法分析：检查语法是否正确
└────┬─────┘ → 如果语法错误，报错 ERROR 1064
↓
┌──────────┐
│ 4. 预处理 │ 检查表名、列名是否存在
│PreProcess│ 检查权限
└────┬─────┘ → 如果表/列不存在，报错
↓
┌──────────┐ 确定最优执行方案
│ 5. 优化器 │
│Optimizer │ 选择使用哪个索引
│ │ 确定 JOIN 的连接顺序
│ │ 生成执行计划
└────┬─────┘
↓
┌──────────┐ 按照执行计划调用存储引擎接口
│ 6. 执行器 │
│ Executor │ 调用引擎的 read_row() 接口
│ │ 逐行判断是否满足条件
│ │ 将结果集返回客户端
└────┬─────┘
↓
┌──────────┐
│ 7. 存储 │ InnoDB 引擎
│ 引擎 │ 从 Buffer Pool 或磁盘读取数据
└──────────┘sql
-- 验证 SQL 执行流程
-- 查看连接信息
SHOW PROCESSLIST;
-- +----+------+-----------+------+---------+------+-------+------------------+
-- | Id | User | Host | db | Command | Time | State | Info |
-- +----+------+-----------+------+---------+------+-------+------------------+
-- | 5 | root | localhost | test | Query | 0 | init | SHOW PROCESSLIST |
-- +----+------+-----------+------+---------+------+-------+------------------+
-- 查看优化器选择的执行计划
EXPLAIN SELECT * FROM users WHERE id = 1;
-- 查看优化器的优化过程
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE id = 1;
-- MySQL 8.0+ 查看优化器的真实执行信息
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;5.2 InnoDB 存储引擎架构
5.2.1 InnoDB 架构全景
InnoDB 存储引擎内部架构：
┌─────────────────────────────────────────────────────────────┐
│ 内存区域（Memory） │
│ │
│ ┌───────────────────────────────────────────────────┐ │
│ │ Buffer Pool（缓冲池） │ │
│ │ │ │
│ │ ┌──────────┐ ┌──────────┐ ┌──────────────────┐ │ │
│ │ │ 数据页 │ │ 索引页 │ │ 自适应哈希索引 │ │ │
│ │ │ Data Page│ │Index Page│ │ Adaptive Hash │ │ │
│ │ └──────────┘ └──────────┘ └──────────────────┘ │ │
│ │ ┌──────────┐ ┌──────────┐ ┌──────────────────┐ │ │
│ │ │ Undo 页 │ │ 锁信息 │ │ 插入缓冲 │ │ │
│ │ │ Undo Page│ │Lock Info │ │ Change Buffer │ │ │
│ │ └──────────┘ └──────────┘ └──────────────────┘ │ │
│ │ │ │
│ │ 管理：LRU 链表（young/old 区域） + Free 链表 │ │
│ └───────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────┐ ┌──────────────────────────────┐ │
│ │ Log Buffer │ │ 额外内存池 │ │
│ │ 日志缓冲 │ │ 数据字典、锁、等待队列等 │ │
│ │ (redo log 缓冲) │ │ │ │
│ └────────┬─────────┘ └──────────────────────────────┘ │
│ │ │
└───────────┼────────────────────────────────────────────────┘
│ 刷盘
┌───────────┼────────────────────────────────────────────────┐
│ ↓ 磁盘区域（Disk） │
│ │
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────────┐ │
│ │ 系统表空间 │ │ 独立表空间 │ │ 通用表空间 │ │
│ │ ibdata1 │ │ *.ibd │ │ General TS │ │
│ │ │ │ (每表一个) │ │ │ │
│ │ • 数据字典 │ │ • 表数据 │ │ │ │
│ │ • Undo Log │ │ • 索引数据 │ │ │ │
│ │ • Change Buf │ │ │ │ │ │
│ └──────────────┘ └──────────────┘ └──────────────────┘ │
│ │
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────────┐ │
│ │ Redo Log │ │ Undo Log │ │ Temp Tablespace │ │
│ │ ib_logfile0 │ │ undo_001 │ │ ibtmp1 │ │
│ │ ib_logfile1 │ │ undo_002 │ │ (临时表空间) │ │
│ └──────────────┘ └──────────────┘ └──────────────────┘ │
└─────────────────────────────────────────────────────────────┘5.2.2 Buffer Pool 详解
Buffer Pool 是 InnoDB 最重要的内存结构，用于缓存数据页和索引页。
Buffer Pool 工作原理：
┌──────────────────────────────────────────────────────┐
│ Buffer Pool │
│ │
│ LRU 链表（改进版）： │
│ │
│ ←──────── 热数据（young 区，5/8）──────────→ │
│ ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌────┐ │
│ │ P1 │→│ P5 │→│ P3 │→│ P8 │→│ P2 │ 频繁访问 │
│ └────┘ └────┘ └────┘ └────┘ └────┘ │
│ midpoint │
│ ←──────── 冷数据（old 区，3/8）──────────→ │
│ ┌────┐ ┌────┐ ┌────┐ ┌────┐ │
│ │ P9 │→│ P4 │→│ P7 │→│ P6 │ 不常访问 │
│ └────┘ └────┘ └────┘ └────┘ │
│ ↓ │
│ 被淘汰 │
│ │
│ 新页面先进入 old 区头部 │
│ → 在 old 区存活超过 1 秒且再次被访问 │
│ → 移入 young 区头部 │
│ → 防止全表扫描污染 Buffer Pool │
└──────────────────────────────────────────────────────┘
脏页刷盘：
┌──────────┐ ┌──────────┐
│ Buffer │ flush │ 磁盘 │
│ Pool 中 │──────→│ .ibd │
│ 脏页 │ │ 文件 │
└──────────┘ └──────────┘
刷盘时机：
1. Redo Log 空间不足
2. Buffer Pool 空间不足
3. 后台线程定期刷盘
4. MySQL 正常关闭sql
-- ==================== Buffer Pool 相关配置 ====================
-- 查看 Buffer Pool 大小（默认 128MB，生产建议设为物理内存的 60-80%）
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- +-------------------------+-----------+
-- | Variable_name | Value |
-- +-------------------------+-----------+
-- | innodb_buffer_pool_size | 134217728 | ← 128MB
-- +-------------------------+-----------+
-- 动态调整（MySQL 5.7.5+）
SET GLOBAL innodb_buffer_pool_size = 1073741824; -- 1GB
-- 查看 Buffer Pool 使用状态
SHOW ENGINE INNODB STATUS\G
-- 关注 BUFFER POOL AND MEMORY 部分
-- 查看 Buffer Pool 命中率
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- Innodb_buffer_pool_read_requests ← 逻辑读（从 BP 读）
-- Innodb_buffer_pool_reads ← 物理读（从磁盘读）
-- 命中率 = 1 - (reads / read_requests) × 100%
-- 生产环境命中率应 > 99%
-- 查看 Buffer Pool 实例数（多实例减少锁竞争）
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';
-- BP >= 1GB 时建议设为 85.2.3 Change Buffer
Change Buffer（变更缓冲）：
适用场景：对非唯一二级索引的 INSERT/UPDATE/DELETE
┌─────────────────────────────────────────────────────┐
│ 没有 Change Buffer： │
│ │
│ INSERT → 读取二级索引页到 BP → 修改 → 脏页刷盘 │
│ ↑ 随机磁盘 IO（慢！） │
│ │
│ 有 Change Buffer： │
│ │
│ INSERT → 记录到 Change Buffer（内存） │
│ ↑ 无需磁盘 IO（快！） │
│ │
│ 后续读取该页时： │
│ 读取磁盘页 → merge Change Buffer 中的变更 → 完成 │
└─────────────────────────────────────────────────────┘
⚠️ 只适用于非唯一二级索引！
因为唯一索引必须读取磁盘判断唯一性，无法延迟。5.3 InnoDB 日志系统
5.3.1 日志系统全景
InnoDB 三大日志及其作用：
┌─────────────────────────────────────────────────────┐
│ │
│ ┌──────────┐ ┌──────────┐ ┌──────────────────┐ │
│ │ Redo Log │ │ Undo Log │ │ Binlog │ │
│ │ 重做日志 │ │ 回滚日志 │ │ 二进制日志 │ │
│ ├──────────┤ ├──────────┤ ├──────────────────┤ │
│ │ 保证 │ │ 保证 │ │ 用于 │ │
│ │ 持久性(D)│ │ 原子性(A)│ │ 主从复制 │ │
│ │ │ │ + MVCC │ │ 数据恢复 │ │
│ ├──────────┤ ├──────────┤ ├──────────────────┤ │
│ │ InnoDB │ │ InnoDB │ │ MySQL Server │ │
│ │ 引擎层 │ │ 引擎层 │ │ 服务层 │ │
│ ├──────────┤ ├──────────┤ ├──────────────────┤ │
│ │ 物理日志 │ │ 逻辑日志 │ │ 逻辑日志 │ │
│ │ 记录页面 │ │ 记录行 │ │ 记录 SQL/行变更 │ │
│ │ 的修改 │ │ 的旧值 │ │ │ │
│ ├──────────┤ ├──────────┤ ├──────────────────┤ │
│ │ 循环写入 │ │ 随事务 │ │ 追加写入 │ │
│ │ 固定大小 │ │ 创建/清理│ │ 无限增长 │ │
│ └──────────┘ └──────────┘ └──────────────────┘ │
└─────────────────────────────────────────────────────┘5.3.2 Redo Log（重做日志）
Redo Log 的作用 —— WAL（Write-Ahead Logging）机制：
核心思想：先写日志，再写磁盘
┌──────────────────────────────────────────────────────┐
│ UPDATE accounts SET balance = 800 WHERE id = 1; │
│ │
│ 步骤： │
│ 1. 从 Buffer Pool 中找到 id=1 的数据页 │
│ （不在则从磁盘加载到 BP） │
│ 2. 在 Buffer Pool 中修改数据页（内存中修改） │
│ 3. 生成 Redo Log 写入 Log Buffer │
│ 4. COMMIT 时 Redo Log 刷盘 ← 顺序写，速度很快 │
│ 5. 脏页异步刷盘 ← 随机写，后台慢慢做 │
│ │
│ 如果 Step 4 后宕机： │
│ → 重启后用 Redo Log 恢复未刷盘的脏页 ← 保证持久性！ │
└──────────────────────────────────────────────────────┘
Redo Log 循环写入：
┌──────────────────────────────────────────────┐
│ │
│ ib_logfile0 ib_logfile1 │
│ ┌───────────────┐ ┌───────────────┐ │
│ │ ██████████░░░ │ │ ░░░░░░░░░░░░░│ │
│ └───────────────┘ └───────────────┘ │
│ ↑ write pos ↑ checkpoint │
│ │
│ write pos: 当前写入位置（顺时针推进） │
│ checkpoint: 已刷盘到数据文件的位置 │
│ │
│ write pos 追上 checkpoint → 暂停写入， │
│ 等待 checkpoint 推进（刷脏页） │
└──────────────────────────────────────────────┘sql
-- ==================== Redo Log 配置 ====================
-- 查看 Redo Log 文件大小和数量
SHOW VARIABLES LIKE 'innodb_log_file_size';
-- +----------------------+-----------+
-- | Variable_name | Value |
-- +----------------------+-----------+
-- | innodb_log_file_size | 50331648 | ← 48MB（默认）
-- +----------------------+-----------+
SHOW VARIABLES LIKE 'innodb_log_files_in_group';
-- +--------------------------+-------+
-- | Variable_name | Value |
-- +--------------------------+-------+
-- | innodb_log_files_in_group| 2 | ← 默认 2 个文件
-- +--------------------------+-------+
-- 总 Redo Log 空间 = innodb_log_file_size × innodb_log_files_in_group
-- 建议生产设置：256MB~1GB per file
-- Redo Log 刷盘策略
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
-- +--------------------------------+-------+
-- | Variable_name | Value |
-- +--------------------------------+-------+
-- | innodb_flush_log_at_trx_commit | 1 | ← 默认
-- +--------------------------------+-------+
--
-- = 0：每秒写入 OS cache 并 fsync（可能丢 1 秒数据）
-- = 1：每次 COMMIT 都 fsync（最安全，默认，推荐）
-- = 2：每次 COMMIT 写入 OS cache，每秒 fsync（折中）5.3.3 Undo Log（回滚日志）
Undo Log 的两大作用：
1. 事务回滚（保证原子性）
┌──────────────────────────────────────────────┐
│ BEGIN; │
│ UPDATE SET balance = 800; → Undo 记录旧值 │
│ → Undo Log: (id=1, balance=1000) │
│ │
│ ROLLBACK; │
│ → 读取 Undo Log，恢复 balance = 1000 │
└──────────────────────────────────────────────┘
2. MVCC 版本链（多版本并发控制）
┌──────────────────────────────────────────────┐
│ 事务 A（快照读）： │
│ → 当前版本不可见？ │
│ → 沿 Undo Log 版本链找到可见的旧版本 │
│ → 返回历史版本数据 │
│ （详见第四阶段 MVCC 部分） │
└──────────────────────────────────────────────┘
Undo Log 类型：
┌─────────────────┬──────────────────────────┐
│ insert undo log │ INSERT 操作产生的 Undo │
│ │ 事务提交后可立即删除 │
│ │（INSERT 的行对其他事务不可见）│
├─────────────────┼──────────────────────────┤
│ update undo log │ UPDATE/DELETE 产生的 Undo │
│ │ 需等待所有快照读不再需要 │
│ │ 后才能被 purge 线程清理 │
└─────────────────┴──────────────────────────┘5.3.4 Binlog（二进制日志）
Binlog 的作用：
┌──────────────────────────────────────────────────┐
│ 1. 主从复制： │
│ ┌──────┐ binlog ┌──────┐ relay log ┌──────┐│
│ │Master│ ───────→│Slave │ ─────────→ │Slave ││
│ │主库 │ │从库 │ 回放执行 │数据 ││
│ └──────┘ └──────┘ └──────┘│
│ │
│ 2. 数据恢复： │
│ 全量备份 + Binlog 增量 → 恢复到任意时间点 │
└──────────────────────────────────────────────────┘
Binlog 三种格式：
┌─────────────┬──────────────────────────────────────┐
│ Statement │ 记录原始 SQL 语句 │
│ (默认 5.7) │ 优点：日志量小 │
│ │ 缺点：NOW() 等不确定函数可能主从不一致 │
├─────────────┼──────────────────────────────────────┤
│ Row │ 记录每行数据的变化（修改前后的值） │
│ (推荐) │ 优点：精确，不会主从不一致 │
│ │ 缺点：日志量大 │
├─────────────┼──────────────────────────────────────┤
│ Mixed │ 混合模式：普通 SQL 用 Statement， │
│ │ 不确定函数用 Row │
└─────────────┴──────────────────────────────────────┘sql
-- ==================== Binlog 配置 ====================
-- 查看是否开启 Binlog
SHOW VARIABLES LIKE 'log_bin';
-- +---------------+-------+
-- | Variable_name | Value |
-- +---------------+-------+
-- | log_bin | ON |
-- +---------------+-------+
-- 查看 Binlog 格式
SHOW VARIABLES LIKE 'binlog_format';
-- +---------------+-------+
-- | Variable_name | Value |
-- +---------------+-------+
-- | binlog_format | ROW | ← 推荐 ROW
-- +---------------+-------+
-- 查看所有 Binlog 文件
SHOW BINARY LOGS;
-- +------------------+-----------+
-- | Log_name | File_size |
-- +------------------+-----------+
-- | mysql-bin.000001 | 1073742 |
-- | mysql-bin.000002 | 524288 |
-- +------------------+-----------+
-- 查看 Binlog 事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 10;
-- 使用 mysqlbinlog 工具解析
-- mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001
-- Binlog 过期自动清理
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
-- 默认 2592000（30 天），8.0+ 使用此参数5.3.5 两阶段提交（2PC）
Redo Log 和 Binlog 的两阶段提交：
为什么需要？
→ 确保 Redo Log 和 Binlog 的一致性
→ 防止主从数据不一致
UPDATE accounts SET balance = 800 WHERE id = 1;
┌──────────────────────────────────────────────────────┐
│ │
│ 1. 执行器调用 InnoDB 修改数据 │
│ ↓ │
│ 2. InnoDB 修改 Buffer Pool 中的数据页 │
│ ↓ │
│ 3. InnoDB 写 Redo Log（prepare 状态） ← 第一阶段 │
│ ↓ │
│ 4. 执行器写 Binlog 到磁盘 │
│ ↓ │
│ 5. InnoDB 将 Redo Log 改为 commit 状态 ← 第二阶段 │
│ │
│ ┌─────────┐ ┌─────────┐ ┌─────────┐ │
│ │ Redo Log│ │ Binlog │ │ Redo Log│ │
│ │ prepare │ → │ 写入 │ → │ commit │ │
│ └─────────┘ └─────────┘ └─────────┘ │
│ │
│ 崩溃恢复规则： │
│ • Redo = prepare + Binlog 完整 → 提交 │
│ • Redo = prepare + Binlog 不完整 → 回滚 │
│ • Redo = commit → 提交 │
└──────────────────────────────────────────────────────┘5.4 存储引擎对比
5.4.1 InnoDB vs MyISAM
| 特性 | InnoDB | MyISAM |
|---|---|---|
| 事务 | ✅ 支持 | ❌ 不支持 |
| 行级锁 | ✅ 支持 | ❌ 只有表级锁 |
| 外键 | ✅ 支持 | ❌ 不支持 |
| MVCC | ✅ 支持 | ❌ 不支持 |
| 崩溃恢复 | ✅ Redo Log 自动恢复 | ❌ 需手动修复 |
| 全文索引 | ✅ 5.6+ 支持 | ✅ 支持 |
| COUNT(*) | 需遍历（MVCC 无法缓存） | 有计数器，O(1) |
| 主键 | 必须有（没有则自动生成） | 可以没有 |
| 索引实现 | 聚簇索引（数据和索引一起） | 非聚簇（数据和索引分离） |
| 存储文件 | .ibd（数据+索引） | .MYD（数据）+ .MYI（索引） |
| 适用场景 | 几乎所有场景（默认推荐） | 只读/极少写的报表统计 |
文件存储对比：
InnoDB（聚簇索引）：
┌─────────────────────────┐
│ table_name.ibd │
│ ┌─────────────────────┐│
│ │ 主键索引 B+ 树 ││
│ │ 叶子节点存完整数据 ││
│ └─────────────────────┘│
│ ┌─────────────────────┐│
│ │ 二级索引 B+ 树 ││
│ │ 叶子节点存主键值 ││
│ └─────────────────────┘│
└─────────────────────────┘
MyISAM（非聚簇）：
┌───────────────┐ ┌───────────────┐
│ table.MYD │ │ table.MYI │
│ ┌───────────┐ │ │ ┌───────────┐ │
│ │ 数据行 1 │ │ │ │索引 → 指针 │ │
│ │ 数据行 2 │ │ │ │索引 → 指针 │ │
│ │ 数据行 3 │ │ │ │索引 → 指针 │ │
│ └───────────┘ │ │ └───────────┘ │
└───────────────┘ └───────────────┘
数据和索引分开存储 索引指向数据文件中的偏移量5.4.2 其他存储引擎
| 存储引擎 | 特点 | 适用场景 |
|---|---|---|
| Memory | 数据存内存，重启丢失，表级锁 | 临时表、缓存、会话数据 |
| CSV | 数据以 CSV 格式存储，可直接编辑 | 数据交换、日志存储 |
| Archive | 只支持 INSERT 和 SELECT，高压缩 | 归档数据、日志存储 |
| Blackhole | 写入即丢弃，不存储数据 | Binlog 中继、测试 |
| NDB Cluster | 分布式存储引擎 | MySQL Cluster 集群 |
| Federated | 访问远程 MySQL 表 | 跨数据库查询 |
sql
-- ==================== 存储引擎操作 ====================
-- 查看支持的存储引擎
SHOW ENGINES;
-- 查看默认存储引擎
SHOW VARIABLES LIKE 'default_storage_engine';
-- +------------------------+--------+
-- | Variable_name | Value |
-- +------------------------+--------+
-- | default_storage_engine | InnoDB |
-- +------------------------+--------+
-- 查看某个表的存储引擎
SHOW TABLE STATUS LIKE 'accounts'\G
-- 或
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test';
-- 创建表时指定引擎
CREATE TABLE logs (
id INT AUTO_INCREMENT PRIMARY KEY,
message TEXT,
created_at DATETIME
) ENGINE=Archive;
-- 修改表的存储引擎（会重建表，耗时！）
ALTER TABLE old_table ENGINE=InnoDB;5.5 数据库设计
5.5.1 范式理论
数据库范式（Normal Form）— 从低到高：
┌─────────────────────────────────────────────────────┐
│ 1NF（第一范式）：列不可再分 │
│ │
│ ❌ 违反 1NF： │
│ ┌─────┬──────────────────┐ │
│ │ id │ 联系方式 │ │
│ ├─────┼──────────────────┤ │
│ │ 1 │ 手机:138, QQ:123 │ ← 一列存了多个值 │
│ └─────┴──────────────────┘ │
│ │
│ ✅ 满足 1NF： │
│ ┌─────┬─────────┬────────┐ │
│ │ id │ phone │ qq │ │
│ ├─────┼─────────┼────────┤ │
│ │ 1 │ 138xxx │ 123 │ ← 每列只存一个值 │
│ └─────┴─────────┴────────┘ │
└─────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────┐
│ 2NF（第二范式）：满足 1NF + 非主键列完全依赖主键 │
│ │
│ ❌ 违反 2NF（联合主键下的部分依赖）： │
│ ┌──────────┬───────────┬──────────┬───────┐ │
│ │ student │ course │ score │ dept │ │
│ │ (主键1) │ (主键2) │ │ │ │
│ ├──────────┼───────────┼──────────┼───────┤ │
│ │ 张三 │ 数学 │ 90 │ 计算机│ │
│ └──────────┴───────────┴──────────┴───────┘ │
│ dept 只依赖 student，不依赖 course → 部分依赖 │
│ │
│ ✅ 拆分为两张表： │
│ students(student, dept) │
│ scores(student, course, score) │
└─────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────┐
│ 3NF（第三范式）：满足 2NF + 非主键列不传递依赖主键 │
│ │
│ ❌ 违反 3NF（传递依赖）： │
│ ┌─────┬─────────┬───────────┐ │
│ │ id │ dept_id │ dept_name │ │
│ ├─────┼─────────┼───────────┤ │
│ │ 1 │ D01 │ 计算机 │ │
│ └─────┴─────────┴───────────┘ │
│ id → dept_id → dept_name（传递依赖） │
│ │
│ ✅ 拆分： │
│ employees(id, dept_id) │
│ departments(dept_id, dept_name) │
└─────────────────────────────────────────────────────┘5.5.2 反范式设计
反范式：适当增加冗余，用空间换时间
┌─────────────────────────────────────────────────────┐
│ 严格 3NF（需要 JOIN）： │
│ │
│ orders 表 users 表 │
│ ┌────┬─────┐ ┌────┬──────┐ │
│ │ id │ uid │ │ id │ name │ │
│ └────┴─────┘ └────┴──────┘ │
│ │
│ 查询订单+用户名： │
│ SELECT o.*, u.name FROM orders o │
│ JOIN users u ON o.uid = u.id; │
│ │
│ 反范式（冗余用户名，避免 JOIN）： │
│ │
│ orders 表 │
│ ┌────┬─────┬───────────┐ │
│ │ id │ uid │ user_name │ ← 冗余字段 │
│ └────┴─────┴───────────┘ │
│ │
│ 直接查询，无需 JOIN： │
│ SELECT * FROM orders WHERE id = 1; │
└─────────────────────────────────────────────────────┘
适用场景：
• 读远多于写
• 频繁 JOIN 影响性能
• 冗余字段变化频率低（如用户名很少改）
代价：
• 更新时需同步更新冗余字段
• 可能导致数据不一致5.5.3 E-R 模型与建模
E-R（Entity-Relationship）模型：
实体（Entity）：矩形表示
属性（Attribute）：椭圆表示
关系（Relationship）：菱形表示
电商数据库 E-R 示意：
┌──────┐ 1 ┌──────┐ N ┌──────┐
│ 用户 │───────│ 下单 │───────│ 订单 │
└──────┘ └──────┘ └──────┘
│
│ 1
│
┌──────┐
│ 包含 │
└──────┘
│
│ N
│
┌──────┐ N ┌──────┐
│ 订单 │───────│ 商品 │
│ 明细 │ └──────┘
└──────┘
┌──────┐
┌──────┐ │ 属于 │
│ 品类 │────│ 1:N │
└──────┘ └──────┘
关系类型：
1:1 一对一（用户 ↔ 用户详情）
1:N 一对多（用户 → 订单）
M:N 多对多（订单 ↔ 商品，通过中间表实现）5.5.4 命名规范与设计最佳实践
┌─────────────────────────────────────────────────────────────┐
│ 数据库设计最佳实践 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 命名规范： │
│ • 表名：小写 + 下划线，复数形式 (users, order_items) │
│ • 字段名：小写 + 下划线 (user_name, created_at) │
│ • 索引名：idx_表名_字段名 (idx_users_email) │
│ • 主键：id（自增 BIGINT） │
│ • 外键字段：关联表_id (user_id, order_id) │
│ • 布尔字段：is_ 开头 (is_active, is_deleted) │
│ • 时间字段：created_at, updated_at, deleted_at │
│ │
│ 字段类型选择： │
│ • 整数：优先 INT / BIGINT，避免 TINYINT 后期扩容 │
│ • 金额：DECIMAL(10,2)，绝不用 FLOAT/DOUBLE │
│ • 字符串：固定长度用 CHAR，变长用 VARCHAR │
│ • 时间：DATETIME（范围大） 或 TIMESTAMP（自动时区转换） │
│ • 布尔：TINYINT(1) │
│ • 大文本：TEXT（不参与索引） │
│ • JSON：MySQL 5.7+ 的 JSON 类型 │
│ │
│ 表设计原则： │
│ • 每张表必须有主键（推荐自增 BIGINT） │
│ • 字段尽量 NOT NULL，使用 DEFAULT 值 │
│ • 预留 created_at、updated_at 字段 │
│ • 软删除用 deleted_at（或 is_deleted）而非物理删除 │
│ • 大表预估数据量，提前规划分表策略 │
│ • 避免使用外键约束（应用层保证一致性） │
│ • 注释清晰：表注释 + 字段注释 │
│ │
└─────────────────────────────────────────────────────────────┘5.6 实战练习
实战 Demo 1：查看 MySQL 运行状态与配置
sql
-- ==================== 1. 查看 MySQL 版本与系统信息 ====================
SELECT VERSION();
SHOW VARIABLES LIKE 'version%';
-- ==================== 2. 查看 InnoDB 关键配置 ====================
-- Buffer Pool
SHOW VARIABLES LIKE 'innodb_buffer_pool%';
-- 关注：
-- innodb_buffer_pool_size → 缓冲池大小
-- innodb_buffer_pool_instances → 缓冲池实例数
-- Redo Log
SHOW VARIABLES LIKE 'innodb_log%';
-- 关注：
-- innodb_log_file_size → 单个 redo log 大小
-- innodb_log_files_in_group → redo log 文件数量
-- 刷盘策略
SHOW VARIABLES LIKE 'innodb_flush%';
-- 关注：
-- innodb_flush_log_at_trx_commit → 0/1/2
-- innodb_flush_method → 刷盘方式
-- ==================== 3. 查看 MySQL 运行状态 ====================
-- Buffer Pool 命中率
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- 计算命中率：
SELECT
(1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100
AS buffer_pool_hit_rate
FROM (
SELECT
VARIABLE_VALUE AS Innodb_buffer_pool_reads
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) a, (
SELECT
VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) b;
-- ==================== 4. 查看连接信息 ====================
SHOW STATUS LIKE 'Threads%';
-- Threads_connected: 当前连接数
-- Threads_running: 当前活跃线程
-- Threads_cached: 缓存的线程数
SHOW VARIABLES LIKE 'max_connections';
-- 最大连接数（默认 151）
-- ==================== 5. 查看 InnoDB 引擎状态 ====================
SHOW ENGINE INNODB STATUS\G
-- 关注以下部分：
-- SEMAPHORES → 锁等待
-- TRANSACTIONS → 活跃事务
-- FILE I/O → IO 状况
-- BUFFER POOL → 缓冲池状态
-- LOG → 日志状态
-- ROW OPERATIONS → 行操作统计实战 Demo 2：电商数据库设计
sql
-- ==================== 电商数据库完整设计 ====================
CREATE DATABASE IF NOT EXISTS ecommerce DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ecommerce;
-- ==================== 1. 用户表 ====================
CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
username VARCHAR(50) NOT NULL COMMENT '用户名',
email VARCHAR(100) NOT NULL COMMENT '邮箱',
phone VARCHAR(20) DEFAULT NULL COMMENT '手机号',
password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
nickname VARCHAR(50) DEFAULT '' COMMENT '昵称',
avatar_url VARCHAR(500) DEFAULT '' COMMENT '头像URL',
is_active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否激活: 0-否 1-是',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
deleted_at DATETIME DEFAULT NULL COMMENT '删除时间(软删除)',
UNIQUE KEY uk_username (username),
UNIQUE KEY uk_email (email),
KEY idx_phone (phone),
KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
-- ==================== 2. 用户地址表 ====================
CREATE TABLE user_addresses (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '地址ID',
user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
receiver_name VARCHAR(50) NOT NULL COMMENT '收货人姓名',
phone VARCHAR(20) NOT NULL COMMENT '收货人手机',
province VARCHAR(50) NOT NULL COMMENT '省份',
city VARCHAR(50) NOT NULL COMMENT '城市',
district VARCHAR(50) NOT NULL COMMENT '区/县',
detail_address VARCHAR(200) NOT NULL COMMENT '详细地址',
is_default TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否默认: 0-否 1-是',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户地址表';
-- ==================== 3. 商品分类表 ====================
CREATE TABLE categories (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '分类ID',
parent_id BIGINT UNSIGNED DEFAULT 0 COMMENT '父分类ID, 0表示顶级分类',
name VARCHAR(100) NOT NULL COMMENT '分类名称',
sort_order INT NOT NULL DEFAULT 0 COMMENT '排序值(越小越靠前)',
is_active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类表';
-- ==================== 4. 商品表 ====================
CREATE TABLE products (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '商品ID',
category_id BIGINT UNSIGNED NOT NULL COMMENT '分类ID',
name VARCHAR(200) NOT NULL COMMENT '商品名称',
description TEXT COMMENT '商品描述',
price DECIMAL(10,2) NOT NULL COMMENT '销售价格',
original_price DECIMAL(10,2) DEFAULT NULL COMMENT '原价',
stock INT NOT NULL DEFAULT 0 COMMENT '库存数量',
sales INT NOT NULL DEFAULT 0 COMMENT '销量',
main_image VARCHAR(500) NOT NULL COMMENT '主图URL',
images JSON DEFAULT NULL COMMENT '商品图片(JSON数组)',
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 0-下架 1-上架',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY idx_category_id (category_id),
KEY idx_status_created (status, created_at),
KEY idx_price (price)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';
-- ==================== 5. 订单表 ====================
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
order_no VARCHAR(32) NOT NULL COMMENT '订单编号',
user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
total_amount DECIMAL(10,2) NOT NULL COMMENT '订单总金额',
pay_amount DECIMAL(10,2) NOT NULL COMMENT '实付金额',
freight_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '运费',
status TINYINT NOT NULL DEFAULT 0 COMMENT '状态: 0-待支付 1-已支付 2-已发货 3-已完成 4-已取消 5-已退款',
payment_method TINYINT DEFAULT NULL COMMENT '支付方式: 1-微信 2-支付宝 3-银行卡',
payment_time DATETIME DEFAULT NULL COMMENT '支付时间',
shipping_time DATETIME DEFAULT NULL COMMENT '发货时间',
completion_time DATETIME DEFAULT NULL COMMENT '完成时间',
receiver_name VARCHAR(50) NOT NULL COMMENT '收货人',
receiver_phone VARCHAR(20) NOT NULL COMMENT '收货人手机',
receiver_address VARCHAR(300) NOT NULL COMMENT '收货地址',
remark VARCHAR(500) DEFAULT '' COMMENT '备注',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_order_no (order_no),
KEY idx_user_id_status (user_id, status),
KEY idx_created_at (created_at),
KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
-- ==================== 6. 订单明细表 ====================
CREATE TABLE order_items (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '明细ID',
order_id BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
product_id BIGINT UNSIGNED NOT NULL COMMENT '商品ID',
product_name VARCHAR(200) NOT NULL COMMENT '商品名称(快照)',
product_image VARCHAR(500) NOT NULL COMMENT '商品图片(快照)',
price DECIMAL(10,2) NOT NULL COMMENT '购买时单价(快照)',
quantity INT NOT NULL COMMENT '数量',
total_price DECIMAL(10,2) NOT NULL COMMENT '小计金额',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY idx_order_id (order_id),
KEY idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';
-- ==================== 7. 支付记录表 ====================
CREATE TABLE payments (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '支付ID',
order_id BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
payment_no VARCHAR(64) NOT NULL COMMENT '支付流水号',
amount DECIMAL(10,2) NOT NULL COMMENT '支付金额',
method TINYINT NOT NULL COMMENT '支付方式: 1-微信 2-支付宝',
status TINYINT NOT NULL DEFAULT 0 COMMENT '0-待支付 1-成功 2-失败 3-退款',
paid_at DATETIME DEFAULT NULL COMMENT '支付成功时间',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_payment_no (payment_no),
KEY idx_order_id (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付记录表';
-- ==================== 插入测试数据 ====================
-- 分类
INSERT INTO categories (parent_id, name, sort_order) VALUES
(0, '电子产品', 1),
(0, '图书', 2),
(1, '手机', 1),
(1, '电脑', 2),
(2, '技术书', 1),
(2, '文学书', 2);
-- 商品
INSERT INTO products (category_id, name, description, price, original_price, stock, main_image) VALUES
(3, 'iPhone 15 Pro', '最新款 iPhone', 8999.00, 9999.00, 100, '/images/iphone15.jpg'),
(3, '小米 14', '小米旗舰手机', 3999.00, 4299.00, 200, '/images/mi14.jpg'),
(4, 'MacBook Pro 14', 'M3 Pro 芯片', 14999.00, 16999.00, 50, '/images/mbp14.jpg'),
(5, '高性能MySQL（第4版）', 'MySQL 权威指南', 149.00, NULL, 500, '/images/hp-mysql.jpg'),
(5, 'MySQL是怎样运行的', '从根儿上理解MySQL', 89.00, 99.00, 300, '/images/mysql-run.jpg');
-- 查看表关系
SELECT
TABLE_NAME AS '表名',
TABLE_ROWS AS '估计行数',
ENGINE AS '引擎',
TABLE_COMMENT AS '说明'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'ecommerce'
ORDER BY TABLE_NAME;5.7 常见问题 QA
Q1：为什么 MySQL 8.0 移除了查询缓存？
A：查询缓存（Query Cache）在实际场景中弊大于利：
- 只要表有任何修改，相关缓存全部失效（写多读少场景命中率极低）
- 维护缓存的开销（加锁、失效检查）超过了收益
- 在高并发下查询缓存的锁竞争成为瓶颈
- 替代方案：使用应用层缓存（Redis）或 ProxySQL 缓存
Q2：InnoDB 为什么必须有主键？
A：InnoDB 是索引组织表（IOT），数据存储在聚簇索引的 B+ 树叶子节点中。主键就是聚簇索引的 key：
- 如果定义了 PRIMARY KEY → 使用 PRIMARY KEY
- 如果没有 PRIMARY KEY → 使用第一个 UNIQUE NOT NULL 索引
- 如果都没有 → InnoDB 自动生成一个隐藏的 6 字节
row_id（但你无法使用它） - 建议：总是显式定义主键，推荐自增 BIGINT
Q3：innodb_flush_log_at_trx_commit 设为 0、1、2 有什么区别？
A：
| 值 | 行为 | 安全性 | 性能 | 适用场景 |
|---|---|---|---|---|
| 0 | 每秒写 Log Buffer → OS cache 并 fsync | 可能丢 1 秒数据 | 最高 | 非核心数据 |
| 1 | 每次 COMMIT 都 fsync 到磁盘 | 不丢数据 | 最低 | 生产默认推荐 |
| 2 | 每次 COMMIT 写 OS cache，每秒 fsync | OS 崩溃可能丢 1 秒 | 中 | 可接受少量丢失 |
Q4：Binlog 和 Redo Log 的区别是什么？
A：
| 对比项 | Redo Log | Binlog |
|---|---|---|
| 层级 | InnoDB 引擎层 | MySQL Server 层 |
| 内容 | 物理日志（页面的修改） | 逻辑日志（SQL 或行变化） |
| 写入方式 | 循环写入，固定大小 | 追加写入，无限增长 |
| 用途 | 崩溃恢复 | 主从复制、数据恢复 |
| 事务相关 | 有，InnoDB 专用 | 有，所有引擎通用 |
Q5：Buffer Pool 设多大合适？
A：
- 单实例服务器：设为物理内存的 60%-80%
- 共享服务器：根据实际情况分配，留足 OS 和其他程序的内存
- 查看当前命中率：命中率 < 99% 说明 Buffer Pool 可能不够大
- 示例：16GB 内存的服务器，可设
innodb_buffer_pool_size = 10G
Q6：为什么推荐用自增主键？
A：
- 顺序写入：自增 ID 保证新数据总是追加到 B+ 树尾部，避免页分裂
- 索引效率：自增主键短小（BIGINT 8 字节），二级索引存储主键值，越小越省空间
- 不推荐 UUID：UUID 随机插入导致频繁页分裂，且 36 字节太长
- 分布式场景：可使用 Snowflake 算法生成趋势递增的分布式 ID
Q7：什么是页分裂和页合并？
A：
- 页分裂：当一个数据页满了，需要插入新记录时，InnoDB 将页分裂为两个，各存一半数据。随机插入（如 UUID 主键）频繁页分裂，性能差
- 页合并：当删除大量数据导致页利用率低于阈值（默认 50%），InnoDB 将相邻页合并
- 优化：使用自增主键避免频繁页分裂
Q8：如何选择 DATETIME 和 TIMESTAMP？
A：
| 对比项 | DATETIME | TIMESTAMP |
|---|---|---|
| 范围 | 1000-01-01 ~ 9999-12-31 | 1970-01-01 ~ 2038-01-19 |
| 存储 | 8 字节 | 4 字节 |
| 时区 | 存什么显示什么 | 自动转换为 UTC 存储 |
| 默认值 | 5.6+ 支持 CURRENT_TIMESTAMP | 支持 CURRENT_TIMESTAMP |
| 推荐 | 日期范围大、不需要时区转换 | 需要时区转换、存储空间敏感 |
Q9：生产环境中应该使用外键约束吗？
A：一般不推荐在生产环境使用外键约束：
- 外键检查有额外开销，影响写入性能
- 大表 DDL 变更时外键是阻碍
- 分库分表后无法使用外键
- 外键级联删除可能导致意外数据丢失
- 推荐：在应用层保证引用完整性，在 E-R 设计文档中标注关系
Q10：MySQL 一行数据最大能存多少？
A：
- InnoDB 行大小限制：约 65535 字节（不含 TEXT/BLOB）
- 单个页面 16KB，一行数据不能超过页面的一半（约 8KB）
- 超过限制的 VARCHAR/TEXT/BLOB 会存到溢出页（overflow page）
- 建议：大文本/大字段用 TEXT/BLOB 类型，让 InnoDB 自动管理溢出
5.8 命令速查表
┌───────────────────────────────────────────────────────────────┐
│ MySQL 架构与引擎 命令速查 │
├───────────────────────────────────────────────────────────────┤
│ 系统信息 │
│ SELECT VERSION() 查看 MySQL 版本 │
│ SHOW VARIABLES LIKE '...' 查看系统变量 │
│ SHOW STATUS LIKE '...' 查看运行状态 │
│ SHOW PROCESSLIST 查看当前连接 │
│ SHOW ENGINE INNODB STATUS\G 查看 InnoDB 状态 │
├───────────────────────────────────────────────────────────────┤
│ Buffer Pool │
│ innodb_buffer_pool_size 缓冲池大小 │
│ innodb_buffer_pool_instances 缓冲池实例数 │
│ Innodb_buffer_pool_read_requests 逻辑读次数 │
│ Innodb_buffer_pool_reads 物理读次数 │
├───────────────────────────────────────────────────────────────┤
│ 日志相关 │
│ innodb_log_file_size Redo Log 文件大小 │
│ innodb_flush_log_at_trx_commit Redo 刷盘策略 (0/1/2) │
│ log_bin Binlog 是否开启 │
│ binlog_format Binlog 格式 │
│ SHOW BINARY LOGS 查看 Binlog 文件列表 │
│ SHOW BINLOG EVENTS IN '...' 查看 Binlog 事件 │
│ binlog_expire_logs_seconds Binlog 过期时间 │
├───────────────────────────────────────────────────────────────┤
│ 存储引擎 │
│ SHOW ENGINES 查看支持的引擎 │
│ SHOW TABLE STATUS LIKE '...' 查看表的引擎信息 │
│ ALTER TABLE t ENGINE=InnoDB 修改表引擎 │
│ default_storage_engine 默认引擎 │
├───────────────────────────────────────────────────────────────┤
│ 数据库设计 │
│ SHOW CREATE TABLE t\G 查看建表语句 │
│ DESCRIBE t / DESC t 查看表结构 │
│ information_schema.TABLES 表元数据 │
│ information_schema.COLUMNS 列元数据 │
│ information_schema.STATISTICS 索引元数据 │
├───────────────────────────────────────────────────────────────┤
│ 配置文件 (my.cnf) 关键参数 │
│ [mysqld] │
│ innodb_buffer_pool_size = 10G 缓冲池（物理内存60-80%） │
│ innodb_log_file_size = 256M Redo Log 大小 │
│ innodb_flush_log_at_trx_commit = 1 安全刷盘 │
│ max_connections = 500 最大连接数 │
│ binlog_format = ROW Binlog 格式 │
│ character-set-server = utf8mb4 默认字符集 │
│ innodb_file_per_table = ON 独立表空间 │
└───────────────────────────────────────────────────────────────┘