MySQL
MySQL 笔记
一条数据在 MySQL 里要过三关：写进去要可靠（事务刷盘）→ 查出来要快（慢 SQL 排查）→ 装不下要拆分（分库分表）。 每个环节都是：什么问题 → 什么方案 → 方案有什么坑。
flowchart LR
subgraph C1["第 1 章 · 写数据"]
A["事务刷盘<br/>可靠？"]
end
subgraph C2["第 2 章 · 查数据"]
B["慢 SQL 排查<br/>快？"]
end
subgraph C3["第 3~6 章 · 数据太多"]
D["分库分表落地<br/>装得下？"]
end
A --> B --> D
目录
第 1 关 · 可靠：写进去的数据不能丢
第 2 关 · 快：一条 SQL 变慢，先分清”一直慢”还是”偶尔慢”
第 3 关 · 大：装不下了，怎么拆
收尾 · 落到本项目
第 1 关 · 可靠
1. 事务提交为什么慢——刷盘参数（压测临时调整）
一句话概括
innodb_flush_log_at_trx_commit 和 sync_binlog 控制 MySQL 事务提交时把数据写进磁盘（fsync）的频率。fsync 是”落盘”，磁盘 IO 慢时很贵（本项目 MySQL 在容器里，一次 fsync 就要 10ms+），压测时为提升吞吐临时调松，测完必须恢复。
两个参数
| 参数 | 原来=1 | 改为 2/0 | 含义 |
|---|---|---|---|
innodb_flush_log_at_trx_commit |
每次事务提交都 fsync redo log | 提交时只写 OS 缓存，每秒批量刷盘 | 值1=最安全最慢；值2=快，崩溃最多丢 1 秒 |
sync_binlog |
每次提交都 fsync binlog | 交给 OS 调度刷盘 | 值1=每次提交都落盘；值0=快，不主动刷 |
原来组合是 (1,1)：每笔事务提交要 fsync 两次（redo + binlog）。
为什么压测时是瓶颈
1
2
trade 落单 COMMIT ──► merchant 冻结 COMMIT ──► TC 写 global/branch ×4
fsync ① fsync ② fsync ③④⑤⑥
下单链路每单有 6 次写（业务 COMMIT ×2 + Seata 4 次），每次 fsync 约 20ms：
1
6 次写 × 20ms ≈ 136ms 全花在刷盘上 → SQL 层成为压测瓶颈
调整后实测：
| 操作 | 调整前 | 调整后 |
|---|---|---|
| COMMIT | 18~21ms | 1ms |
| Seata 写操作 | 15~23ms | 2ms |
代价与恢复
代价：MySQL 崩溃时可能丢最近 1 秒的事务。压测造数据可以接受，生产环境不能这么配。
恢复命令（压测结束后执行并验证）：
1
2
3
SET GLOBAL innodb_flush_log_at_trx_commit = 1;
SET GLOBAL sync_binlog = 1;
SHOW VARIABLES WHERE Variable_name IN ('innodb_flush_log_at_trx_commit','sync_binlog');
坑：
SET GLOBAL只对运行时生效，重启后以配置文件（my.cnf）为准，确认配置文件里也是安全值。
什么时候可以放宽
| 场景 | 建议 |
|---|---|
| 生产环境 | 保持 (1,1)，牺牲吞吐保数据安全 |
| 压测造数/批量导入 | 可临时 (2,0)，大幅提升 TPS（每秒完成的事务数） |
面试引申：为什么 (1,1) 最安全？—— redo log（崩溃恢复）和 binlog（主从复制）是两套日志，各自 fsync 才算真正落盘；双 1 意味着任何一瞬崩溃，最多丢”正在提交的那一笔”。这也是分布式事务 XA 在 MySQL 上性能差的部分原因（多次同步点 = 多次刷盘）。
第 2 关 · 快
2. 慢 SQL 排查总纲：先分清”一直慢”还是”偶尔慢”
一句话概括
一直慢 = 执行计划的问题（SQL 本身）；偶尔慢 = 执行环境的问题（MySQL 内部/OS/网络）。
如何快速区分
把这条 SQL 连续执行 20 次：
| 现象 | 结论 |
|---|---|
3.2s, 3.1s, 3.3s, 3.0s, 3.2s...（时间稳定） |
一直慢 |
0.005s, 0.004s, 2.1s, 0.005s, 0.003s, 3.5s...（标准差差一个数量级） |
偶尔慢 |
| 维度 | 一直慢 | 偶尔慢 |
|---|---|---|
| 根因 | 执行计划存在根本缺陷 | 执行环境发生间歇性变化 |
| 诊断起点 | EXPLAIN + 索引分析 |
Buffer Pool + 锁 + 系统资源 |
| 复现难度 | 容易，随时可复现 | 困难，要命中”窗口期” |
| 典型解法 | 加索引、改 SQL、分表 | 错峰、调参数、资源隔离、改 DDL 策略 |
| 常见误区 | 加索引一定能解决 | 改 SQL 就能解决（大概率不是 SQL 的问题） |
2.1 一直慢的 SQL：执行计划三板斧
第一斧：看执行计划 EXPLAIN
是什么：不用真跑，让 MySQL 把”这条 SQL 打算怎么查”提前说出来——一眼定位慢在哪：没走索引？还是扫太多行？
怎么用：EXPLAIN 慢SQL;，只盯 4 列：
| 列 | 慢的信号 | 意思 |
|---|---|---|
type |
ALL 全表扫 / index 全索引扫 |
没有有效过滤 |
rows |
比预期结果大很多 | 过滤性差，扫了不该扫的行 |
key |
NULL 或走错索引 |
索引没用上 / 选错 |
Extra |
Using filesort / Using temporary |
排序/分组没吃上索引 |
例（本项目 cf_order 查某用户最近订单）：
1
2
3
4
EXPLAIN SELECT * FROM `cf_order`
WHERE user_id = '2094240707584725082' AND deleted = 0
ORDER BY create_time DESC LIMIT 20;
-- type=ALL rows≈1000000 key=NULL Extra=Using where; Using filesort
翻译：cf_order 没有 user_id 索引 → 只能全表扫约 100 万行，逐行比对 user_id/deleted，再全部内存排序后取前 20。
修复：补联合索引，过滤 + 排序一起落地：
1
2
ALTER TABLE `cf_order` ADD KEY `CF_ORDER_USER_ID_DELETED_CREATE_TIME_IDX` (`user_id`, `deleted`, `create_time`);
-- 修复后：type=ref rows=51430 key=CF_ORDER_USER_ID_DELETED_CREATE_TIME_IDX Extra=Backward index scan
第二斧：检查索引——”有索引还慢”的四种情况
- 回表太多：没有覆盖索引，查询列不在索引里，每行都要回聚簇索引。查 10 万行就回表 10 万次。 方案：建覆盖索引（把要查的列都塞进索引）。
- 索引过滤性差：字段区分度低于 20% 时放最左列意义不大，应放在联合索引靠后的位置。
- 隐式类型转换：
WHERE user_id = '123'（字符串），但user_id是整型 → MySQL 可能放弃索引。 - 函数操作：
WHERE DATE(created_at) = '2024-01-01'让索引失效（对列做函数）。 正例：1 2
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02' -- 范围写法，能用上索引
第三斧：检查数据量和写入模式
| 情况 | 影响 | 方案 |
|---|---|---|
| 数据量过大（几千万~上亿行） | B+ 树变深、回表路径变长 | 分库分表或归档（见第 3 章） |
| 大量随机 INSERT/UPDATE/DELETE | 索引页分裂、碎片化 | OPTIMIZE TABLE 定期重建 |
收尾反问：三板斧都无效？再反问一次——它真的是”一直慢”吗？是否存在有规律的快/慢交替（有 → 其实是偶尔慢）。
2.2 偶尔慢的 SQL：执行环境的七大原因
偶尔慢难在两点：难以复现 + 根因多元（OS、存储、网络、并发控制、缓存、优化器行为都可能）。
原因① Buffer Pool 被”冲凉”（最常被低估，优先级最高）
类比：查数据 = 找资料。Buffer Pool（内存）= 书桌，常查的热数据摊在桌上，随手拿很快。但桌子就那么大——某天跑批/大查询要铺开一大堆冷门资料，把桌上的热数据挤回书架（磁盘）了。之后你再查热数据，只能起身去书架翻，慢上千倍。
怎么发现：书桌上（内存）的命中率低于 95%，说明热资料老被挤走：
1
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'; -- 命中率 = 1 - reads / read_requests
怎么解决：跑批错峰、加 LIMIT 分批，别和核心查询挤；innodb_buffer_pool_size 给到可用内存 60%~80%；innodb_old_blocks_time=1000 让新读入的页先在角落待一会，别一来就把热数据挤走。
原因② 执行计划突变 / 参数嗅探
类比：导航第一次见多数车走 A 路就记住了；以后哪怕这趟该走更快的 B 路，它也一直带你绕 A 路。
原理：status=1 占 90% 时全表扫划算，status=2 只占 0.1% 时走索引划算——但 MySQL 可能拿第一次参数定的路线一直复用，参数一换路线就错了。
怎么发现：换不同参数值跑 EXPLAIN，对比路线（计划）是不是变了。
怎么解决：FORCE INDEX 人工固定路线（缺点：索引变了要跟着维护）；或建覆盖索引，让走哪条路都快。
原因③ 锁等待与 MDL 阻塞（”隐形杀手”）
类比：一个人攥着表不撒手（长事务一直没提交），你想改表结构（ALTER）只能等；他堵着，后面所有来查这张表的人也被堵死——整张表像被冻住。
怎么发现：SHOW PROCESSLIST 一片 Waiting for table metadata lock；再用 INNODB_TRX 揪出攥着锁的长事务（SHOW PROCESSLIST 看不出事务开了多久，只有 trx_started 才算数）：
1
2
3
4
5
6
7
SELECT trx_id, trx_state,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS run_seconds, -- 已跑秒数
trx_mysql_thread_id, -- 对应连接 ID，KILL 用这个
trx_query -- 当前正在跑的 SQL；NULL = 开着事务但空闲
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30
ORDER BY run_seconds DESC;
trx_query=NULL+run_seconds很大 = 代码里开了事务忘了提交，连接挂着 → MDL 读锁就攥在它手里（最常见的元凶）
怎么解决：确认是僵尸事务后 KILL <trx_mysql_thread_id>（强制回滚，先确认再动手）。
改表（DDL）要拿写锁、容易被长事务卡死，三个安全姿势：
- 错峰 +
lock_wait_timeout：低谷期改表；SET SESSION lock_wait_timeout = 5设最长等待（默认 1 年 = 无限等），等不到就快速失败，别让全表陪等 ALGORITHM=INSTANT（8.0 内置）：只改表头不动数据（如末尾加列），瞬时完成——像改门牌号不用拆楼1
ALTER TABLE `cf_order` ADD COLUMN remark VARCHAR(255), ALGORITHM=INSTANT;
- pt-osc（外部工具）：影子表分批搬好数据再 RENAME 瞬间换表，几乎不阻塞——老店营业中搬家，换招牌客人无感；大表专用
原因④ 排序/分组临时表落盘
类比：ORDER BY 要把结果理一遍。量小清单放桌上（内存临时表）秒出；量一大桌子放不下，只能搬去仓库整理（磁盘临时表），瞬间慢一个数量级。偶尔慢 = 结果集刚好时大时小，偶尔超线。
怎么发现：落盘占比 > 10% 说明搬仓库搬多了：
1
SHOW GLOBAL STATUS LIKE 'Created_tmp%'; -- Created_tmp_disk_tables / Created_tmp_tables
怎么解决：给排序列建索引，免去排序；或调大 tmp_table_size / max_heap_table_size（别贪大）。
原因⑤ 操作系统层抖动
类比：宿舍突然挤进来一堆人，你干什么都慢半拍——不是你的问题，是环境被抢了。
| 看什么 | 信号 | 解法 |
|---|---|---|
vmstat 的 si/so |
> 0 = 内存被换到磁盘（swap） | 加内存，保证为 0 |
iostat / iotop |
磁盘被日志、备份、监控采集抢 | 换盘 / 错峰 |
numactl |
CPU 和内存不在同一节点，跨节点取数慢 | numactl --interleave=all mysqld |
| 云上实例 | “吵闹的邻居”抢底层资源 | 升规格 / 独享实例 |
原因⑥ 网络抖动（DB 与应用不在一台机时）
怎么发现：对比”DB 端执行耗时” vs “应用端感知耗时”：DB 只要 5ms、应用却要 500ms → 问题在网络不在 SQL。ping / mtr / tcpdump 查延迟、丢包、重传。
原因⑦ Query Cache”回光返照”（8.0 之前的老坑）
为什么是坑：缓存结果对写操作极不友好：表一被写，它所有缓存立即清空且清空要加锁。写多场景：缓存刚建就被清 → 命中率趋近 0，清空加锁反而拖慢并发（曾有案例：关掉后性能反升 30%）。
怎么解决：8.0 已移除没这坑了；5.7 直接关掉：
1
SET GLOBAL query_cache_type = 0; -- 5.7 需重启才能真正彻底禁用
2.3 偶尔慢的排查顺序
按以下顺序排查效率最高：
| Step | 排查项 | 手段 | 应对 |
|---|---|---|---|
| 1 | Buffer Pool 被冲凉 | 查命中率（<99% 且有大查询/跑批） | 错峰、调 innodb_old_blocks_time |
| 2 | 锁等待 / MDL | performance_schema、SHOW PROCESSLIST |
找长事务、处理 DDL |
| 3 | 临时表落盘 | Created_tmp_disk_tables 占比 |
调参、优化排序分组 |
| 4 | 执行计划突变 | 不同参数对比 EXPLAIN |
固定计划、覆盖索引 |
| 5 | 系统层抖动 | Swap/NUMA/磁盘 IO/网络 | 系统调优、资源隔离 |
| 6 | 最后才回到 SQL 本身 | —— | 极小概率事件 |
核心观点：MySQL 性能问题，诊断比修复难 10 倍。排查不要一上来就问”这条 SQL 能不能优化”，先问：它真的每次都慢，还是只是偶尔让你看见了？
第 3 关 · 大
3. 分库分表：什么时候拆、拆成什么样
3.1 先回答：真的到瓶颈了吗？（拆了反而更糟）
面试官问”分库分表怎么做的”，想听的是——面临什么瓶颈 → 选了哪种分法 → 分了多少库多少表 → 为什么是这个数量。不是背定义。
| 硬性指标 | 阈值 | 说明 |
|---|---|---|
| 单表数据量 | 超 1000 万且持续增长 | 查询明显变慢、加索引压不住 |
| 单库 QPS | 5000 以上 | 连接池紧张、主从延迟拉大 |
| 磁盘 IO | util% 长期 > 80% |
iostat 观察 |
| DDL 变更 | 几亿行表加字段 | 主库阻塞几十分钟 |
代价清单（决定前必须想清楚）：跨库 JOIN 基本做不了、分布式事务引入、运维成本上升、跨片查询/分页变复杂。
更轻量的替代方案：分区表（PARTITION BY）、归档冷数据（历史数据搬入归档表）。能不分就不分。
3.2 四种拆分方式
| 拆分 | 做法 | 目的/场景 |
|---|---|---|
| 垂直分表 | 宽表拆窄表（40 字段订单表 → order_main + order_ext） | 减少单行大小，提升 Buffer Pool 命中率 |
| 垂直分库 | 按业务域拆库（订单库/用户库/商品库） | 业务解耦、独立扩容——与数据量无关、与架构有关 |
| 水平分表 | 同库大表拆成 order_0 ~ order_3 | 降低单表数据量 |
| 水平分库 | 数据分散到多个 MySQL 实例（4 实例 × 4 表 = 16 张分表） | 分摊连接数、IO、CPU 压力 |
3.3 库表数量：算出来的，不是拍脑袋的
分表数（按容量倒推）：
1
2
3
预计 3 年后总订单量：10 亿
期望单表上限：2000 万
分表数 = 10 亿 / 2000 万 = 50 → 取 2 的整数次幂 = 64 张
分库数（按 QPS 倒推）：
1
2
3
4
峰值 QPS：5 万
单库可扛：5000 QPS
分库数 = 5 万 / 5000 = 10 → 取 16 个库
最终方案：16 库 × 4 表/库 = 64 张分表
为什么取 2 的整数次幂：取模运算高效、路由计算简洁、哈希位运算天然支持：
- 分表序号 =
hash(分片键) % 64 - 分库序号 =
分表序号 / 4
3.4 中间件选型：JDBC 模式 vs Proxy 模式
| ShardingSphere-JDBC | ShardingSphere-Proxy | |
|---|---|---|
| 模式 | 客户端嵌入式（JDBC 增强层，改造数据源） | 代理模式（独立部署，应用当它是普通 MySQL） |
| 优点 | 无需额外部署、性能好、零额外网络开销 | 多语言友好、应用无感知 |
| 坑 | 多语言不友好；每个应用都要引入 | 多一层网络转发有性能损耗；代理本身单点需高可用 |
| 选型 | 纯 Java + 小团队 | 多语言需求 / 应用完全不感知分片 |
4. 分片键：选错全盘皆输
4.1 三个必须（选键三原则）
- 必须覆盖核心查询场景——最高频查询必须能直接定位数据。用户查订单 → 选
user_id；选order_id做分片键则”查用户订单”要广播全表。 - 必须分布均匀——自增/雪花 ID 天然均匀；选
create_time日期则同一天数据集中一个分表（热点更严重）。 - 必须有稳定业务边界、不频繁变更——分片键变更 = 数据跨表迁移 = 极高成本。
关键结论：分片键不一定是主键，但必须是大多数查询的过滤条件。
4.2 业务场景速查表
| 业务 | 推荐分片键 | 核心查询 | 理由 |
|---|---|---|---|
| 电商订单 | user_id |
查用户订单列表 | 用户维度查询最高频 |
| 社交 Feed | user_id |
查用户时间线 | 每个用户数据独立 |
| 即时通讯 | conversation_id |
查会话消息 | 会话维度、天然分散 |
| 物流轨迹 | tracking_no |
查运单轨迹 | 运单号唯一且高频 |
| IoT 设备 | device_id |
查设备数据 | 设备维度、量大分散 |
4.3 跨片查询：三种解法
场景：订单按 user_id 分片，但商家要查”本店铺全部订单”（条件是 merchant_id）。
| 解法 | 实现 | 坑/适用性 |
|---|---|---|
| 广播查询 | 查询发到全部 64 张分表，应用层合并 | 压力翻 64 倍；分页 = 噩梦（越翻越慢）；仅限低频管理后台 |
| 异构索引 | 用 ES 维护 merchant_id → order_id 映射，先查 ES 拿 order_id 再精确路由 |
生产最常用；需维护 ES↔MySQL 同步一致性 |
| 双写异构表 | 再按 merchant_id 维度存一份（MQ 异步同步） | 存储翻倍、两份数据需最终一致性；适合查询场景明确且数据量可控 |
收尾 · 落到本项目
5. 项目实战：CatFun 2 库 × 4 表（ShardingSphere-JDBC）
本项目（catfun-server）的订单域真实落地：2 个物理库 × 每库 4 张表 = 8 个分片，分片键
user_id。 源码：catfun-common/common-shardingsphere（ShardingSphereConfig、UserDbShardingAlgorithm、UserTableShardingAlgorithm）。
5.1 为什么这样设计
1
2
3
4
ds0 (catfun_trade_0) ── cf_order_0..3 / cf_order_item_0..3 / ... × 7 张分片表
ds1 (catfun_trade_1) ── 同上
广播表：cf_coupon（无 user_id，每库全量一份）
单表规则：未分片表默认落 ds0
- 分片键统一
user_id（7 张用户维表）：同一用户全部数据落在同一物理库 - 下单链路本地事务天然成立 → 无需引入分布式事务
- 绑定表（binding table）：
cf_order和cf_order_item都按user_id分片，且分片算法相同，所以同一个用户的订单和明细一定落在同一编号的物理表（如_0配_0）。join 时 ShardingSphere 知道这个对应关系，直接在_0和_0之间关联，不需要_0和_1、_0和_2逐一组合——避免笛卡尔积式跨分片关联 - 广播表：
cf_coupon这类无user_id的表每库全量复制一份，本地 join 可用 - 单表规则：未分片表默认落
ds0；分片表不带分片键的查询由 ShardingSphere 全路由广播
5.2 分片算法：为什么库用低位、表要高位移除一位
1
2
// 库分片：CRC32(user_id) % 2 → ds0 / ds1 （用哈希低位）
// 表分片：FLOOR(CRC32(user_id) / 2) % 4 → cf_order_0..3 （高位移除一位再取模）
坑（反直觉）：如果表分片也用 CRC32 % 4，由于 MOD(x, 2) 完全由 MOD(x, 4) 决定，会出现：
1
库 0 只有 _0/_2，库 1 只有 _1/_3 → 一半物理表永远为空
改为 FLOOR(H/2) % 4 后：哈希 bit0 决定库、bit1~bit2 决定表，8 个(库,表)组合全部可命中——这就是”分片策略要正交”。
5.3 为什么用 CRC32 而不是 String.hashCode()
MySQL 内置 CRC32() 函数与 Java 的 CRC32 结果完全一致，所以数据迁移脚本：
1
INSERT INTO new_table SELECT ... WHERE MOD(CRC32(user_id), N) = x
能与应用路由结果严格对齐，避免”应用路由到 A 分片、数据却落在 B 分片”的经典翻车。