MySQL 实战——互联网标配关系型数据库 / Practical MySQL for Internet Applications
📅 创建时间：2026-05-08 🏷️ 标签：#MySQL #RDBMS #InnoDB #主从复制 #分库分表 📚 前置知识：[[00-db-overview]]（数据库分类基础） 📚 相关知识：[[07-distributed-sql]]（TiDB 解决 MySQL 扩展瓶颈）
MySQL 定位速览
┌─────────────────────────────────────────────────────────────┐
│ MySQL 在数据库版图中的位置 │
├─────────────────────────────────────────────────────────────┤
│ │
│ GitHub Stars: 9.7K+（MySQL 本身），全球部署量最大 │
│ 定位： 互联网标配 RDBMS，"足够好"的务实选择 │
│ 适用场景： 读多写少的 Web 应用、内容管理、SaaS 后端 │
│ 不适合： 复杂分析（用 ClickHouse）、强事务金融（用 TiDB） │
│ │
└─────────────────────────────────────────────────────────────┘第1部分：存储引擎——InnoDB vs MyISAM
MySQL 最大的设计选择点之一是存储引擎。
┌─────────────────────────────────────────────────────────────┐
│ InnoDB vs MyISAM 对比 │
├─────────────────────────────────────────────────────────────┤
│ │
│ │ 维度 │ InnoDB │ MyISAM │
│ ├──────────────┼─────────────────────┼──────────────────────┤
│ │ 事务支持 │ ✅ ACID 完整 │ ❌ 不支持 │
│ │ 行级锁 │ ✅ │ ❌（表级锁） │
│ │ 外键约束 │ ✅ │ ❌ │
│ │ 崩溃恢复 │ ✅ 自动恢复 │ ❌ 需要手动修复 │
│ │ 并发写入 │ ✅ │ ❌ │
│ │ 全文本索引 │ ✅（MySQL 5.6+） │ ✅ │
│ │ 空间函数 GIS │ ✅ │ ❌ │
│ │ 适用场景 │ OLTP（在线事务） │ 读多写少（归档/日志） │
│ │
└─────────────────────────────────────────────────────────────┘
结论：MySQL 8.0+ 中，InnoDB 是几乎所有场景的唯一选择。
MyISAM 仅在极特殊场景（只读压缩表）有微弱优势。第2部分：InnoDB 索引原理
2.1 B+ 树结构
InnoDB 的所有索引都是 B+ 树（B-Tree 的变种）：
┌─────────────────────────────────────────────────────────────┐
│ InnoDB B+ 树索引结构 │
├─────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────┐ │
│ │ 根节点 │ │
│ │ (非叶子节点)│ │
│ └──────┬──────┘ │
│ ┌──────┴──────┐ │
│ ┌─────┴─────┐ ┌─────┴─────┐ │
│ │ 叶子节点 │ │ 叶子节点 │ │
│ │ (包含数据) │ │ (包含数据) │ │
│ │ [15,20) │ │ [20,30) │ │
│ │ ──────── │ │ ──────── │ │
│ │ 数据行 │ │ 数据行 │ │
│ │ 15│row1 │ │ 20│row3 │ │
│ │ 16│row2 │ │ 22│row4 │ │
│ │ 18│row5 │ │ 28│row6 │ │
│ └───────────┘ └───────────┘ │
│ │
│ B+ 树特征： │
│ • 叶子节点包含所有数据（聚簇索引）或指向数据（辅助索引） │
│ • 叶子节点之间用双向链表连接（范围查询极快） │
│ • 非叶子节点只存索引键（矮胖树，深度小） │
│ • 查找：O(log n)，百万级数据约 3-4 层 │
│ │
└─────────────────────────────────────────────────────────────┘2.2 聚簇索引 vs 辅助索引
┌─────────────────────────────────────────────────────────────┐
│ 聚簇索引 vs 辅助索引 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 聚簇索引（Clustered Index）： │
│ • 表的主键索引即聚簇索引 │
│ • 叶子节点存储完整的行数据 │
│ • 每个表只有一个聚簇索引 │
│ • 数据按主键顺序物理存储 │
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 主键 │ 姓名 │ 年龄 │ 工资 │ │ │
│ ├────────┼────────┼────────┼────────┤ │ │
│ │ 1 │ 张三 │ 25 │ 8000 │ ← 聚簇索引叶子节点 │ │
│ │ 5 │ 李四 │ 30 │ 12000 │ 包含完整行数据 │ │
│ │ 9 │ 王五 │ 28 │ 10000 │ │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 辅助索引（Secondary Index）： │
│ • 非主键列的索引 │
│ • 叶子节点存储索引列值 + 主键值 │
│ • 查找时需要"回表"（用主键再查聚簇索引） │
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 姓名 │ 主键（回表指针） │ │
│ ├────────┼──────────────┐ │ │
│ │ 张三 │ 1 │ ──→ 回到聚簇索引找 row1 │ │
│ │ 李四 │ 5 │ ──→ 回到聚簇索引找 row2 │ │
│ │ 王五 │ 9 │ ──→ 回到聚簇索引找 row3 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 覆盖索引（Covering Index）： │
│ • 如果查询的列都在辅助索引中，不需要回表 │
│ • 例：SELECT name FROM users WHERE name = '张三' │
│ • 仅需扫描辅助索引，无需访问聚簇索引 │
│ │
└─────────────────────────────────────────────────────────────┘第3部分：主从复制与读写分离
3.1 复制原理
┌─────────────────────────────────────────────────────────────┐
│ MySQL 主从复制架构 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 写入 复制 读取 │
│ ┌──────┐ binlog ┌──────┐ │
│ │ Master│ ───────────────▶ Relay Log ──────▶│ Slave │ ───▶ │
│ │ (写) │ (异步) │ (读) │ │
│ └──────┘ └──────┘ │
│ │
│ 复制流程： │
│ 1. Master 执行写事务，记录 binlog │
│ 2. Master 的 I/O Thread 将 binlog 发送给 Slave │
│ 3. Slave 的 I/O Thread 接收，写入 relay log │
│ 4. Slave 的 SQL Thread 重放 relay log │
│ │
│ 复制模式： │
│ • 异步复制（默认）：Master 不等 Slave 确认就返回 │
│ • 半同步复制：Master 等至少一个 Slave 确认写入 │
│ • GTID 复制：基于事务 ID，自动位点对齐 │
│ │
└─────────────────────────────────────────────────────────────┘3.2 读写分离实战
┌─────────────────────────────────────────────────────────────┐
│ 读写分离架构 │
├─────────────────────────────────────────────────────────────┤
│ │
│ ┌───────────────┐ │
│ │ 应用层 │ │
│ │ (读写分离) │ │
│ └───┬───────┬───┘ │
│ 写 │ │ 读 │
│ ┌──────┘ └──────┐ │
│ ▼ ▼ │
│ ┌────────────┐ ┌────────────┐ │
│ │ Master │ │ Slave 1 │ │
│ │ (写) │ │ (读 1) │ │
│ └────────────┘ └────────────┘ │
│ │ │ │
│ └──────────┬─────────────┘ │
│ ▼ │
│ ┌────────────┐ │
│ │ Slave 2 │ │
│ │ (读 2) │ │
│ └───────────┘ │
│ │
│ 常见中间件：ShardingSphere / MySQL Router / ProxySQL │
│ │
│ 注意：复制延迟导致读可能落后于写——"写后读"问题 │
│ 解决方案：强制走主库 / 延迟读 / GTID + 半同步 │
│ │
└─────────────────────────────────────────────────────────────┘第4部分：分库分表
当单机 MySQL 无法承载时，分库分表是扩展路径：
┌─────────────────────────────────────────────────────────────┐
│ 分库分表策略 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 垂直拆分（按业务）： │
│ ┌────────────┐ ┌────────────┐ ┌────────────┐ │
│ │ 用户库 │ │ 订单库 │ │ 商品库 │ │
│ │ (users) │ │ (orders) │ │ (products) │ │
│ └────────────┘ └────────────┘ └────────────┘ │
│ → 优点：改动小，改动按业务拆分 │
│ → 缺点：无法解决单表热点问题 │
│ │
│ 水平拆分（按数据）： │
│ ┌────────────────────┐ │
│ │ users 表 │ │
│ ├────────────────────┤ │
│ │ user_id % 4 = 0 → users_0 │
│ │ user_id % 4 = 1 → users_1 │
│ │ user_id % 4 = 2 → users_2 │
│ │ user_id % 4 = 3 → users_3 │
│ └────────────────────┘ │
│ → 优点：解决单表数据量过大的问题 │
│ → 缺点：跨表查询困难（需要中间件聚合） │
│ │
│ 常用中间件：ShardingSphere / MyCAT / Vitess │
│ │
│ 分片键选择原则： │
│ • 均匀分布（避免数据倾斜） │
│ • 常用于查询（热门条件） │
│ • 不频繁变更（变更需要数据迁移） │
│ │
└─────────────────────────────────────────────────────────────┘第5部分：实战 SQL 示例
5.1 索引设计与查询优化
sql
-- 创建复合索引：最左前缀原则
CREATE INDEX idx_user_status_created ON users(status, created_at);
-- 查询：可以利用索引（覆盖索引，无需回表）
SELECT id, status, created_at
FROM users
WHERE status = 'active'
AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;
-- 查询：部分使用索引（只用 status，无法用 created_at 排序）
SELECT id, status, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC; -- filesort，可能较慢
-- 查询：完全无法使用索引
SELECT id, status, created_at
FROM users
WHERE created_at > '2026-01-01'; -- 跳过最左列5.2 慢查询分析与优化
sql
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过 1 秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 查看当前连接
SHOW PROCESSLIST;
-- EXPLAIN 分析执行计划
EXPLAIN SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id, u.name
HAVING order_count > 5
ORDER BY order_count DESC
LIMIT 10;
-- EXPLAIN 输出解读：
-- type: const > eq_ref > ref > range > index > ALL（尽量避免 ALL）
-- key: 实际使用的索引
-- rows: 预计扫描行数（越少越好）
-- Extra: Using filesort / Using temporary（需要优化）5.3 事务与锁
sql
-- 事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- MySQL 默认
-- 悲观锁（行锁）
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 锁定行
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 乐观锁（版本号）
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 100 AND version = 5; -- 版本不匹配则更新 0 行
-- 死锁检测：MySQL 自动回滚代价最小的事务
SHOW ENGINE INNODB STATUS; -- 查看最近死锁详情第6部分：MySQL 在 LLM/Agent 应用中的角色
┌─────────────────────────────────────────────────────────────┐
│ MySQL 在 AI 应用中的定位 │
├─────────────────────────────────────────────────────────────┤
│ │
│ MySQL 在 AI 应用中通常扮演"元数据存储"的角色： │
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 任务表（Task）： │ │
│ │ id / status / agent_id / created_at / result_url │ │
│ └─────────────────────────────────────────────────────┘ │
│ │ │ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Agent 配置表： │ │
│ │ id / name / system_prompt / model / tools (JSON) │ │
│ └─────────────────────────────────────────────────────┘ │
│ │ │ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 用户表（含 API Key 引用，但 Key 存在 K-V 存储中） │ │
│ │ id / email / tier / api_key_ref / created_at │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 不适合放在 MySQL 中的数据： │
│ • 海量消息记录 → PostgreSQL（JSONB + 分区）或 ClickHouse │
│ • 向量嵌入数据 → pgvector / Milvus │
│ • 临时缓存 → Redis │
│ • 文件本体 → S3/MinIO │
│ │
└─────────────────────────────────────────────────────────────┘学习状态：🟡 开始学习