覆盖 MySQL 常见性能问题的排查思路：从 MySQL"抖一下"的根因（脏页刷盘）、表数据删了大小不变（空洞）、count(*) 慢、order by 慢查询，到单行查询也慢的隐藏问题。
目录
| 章节 | 说明 |
|---|---|
| MySQL 为什么会"抖" | 脏页刷盘（flush）的四种触发场景 |
| 表文件删不小的原因 | 数据空洞与 optimize table |
| count(*) 为什么慢 | InnoDB 的 count 实现与优化方案 |
| order by 的执行原理 | 全字段排序 vs rowid 排序 |
| 索引失效的常见场景 | 函数、隐式转换、隐式字符集转换 |
| 只查一行为什么慢 | 锁、一致性读等隐藏原因 |
| 查大量数据会不会 OOM | 全表扫描的内存机制 |
MySQL 为什么会"抖"
正常执行很快的 SQL 语句，偶尔突然变慢，是因为触发了脏页刷盘（flush）。
什么是脏页
- 内存中数据页 ≠ 磁盘数据页 → 脏页
- 内存数据页已刷到磁盘，两者一致 → 干净页
WAL 机制下，写操作先写 redo log 和内存，不立即写磁盘，所以内存中会积累脏页。
四种触发 flush 的场景
| 场景 | 描述 | 严重程度 |
|---|---|---|
| redo log 写满 | write pos 追上 checkpoint，必须先刷脏页才能继续写 | 🔴 严重，所有更新停顿 |
| 内存不足 | Buffer Pool 空间满，要淘汰数据页，若是脏页则先刷盘 | 🟡 较严重，影响当前查询 |
| 系统空闲 | MySQL 主动刷脏页，后台线程执行 | 🟢 正常，用户无感 |
| 关机/重启 | 将所有脏页刷盘，保证干净关闭 | 🟢 正常 |
如何控制 flush 对性能的影响
-- 告知 InnoDB 主机 IO 能力（单位：IOPS），控制刷脏页的速度
-- 过低会导致脏页积累太多，刷脏时影响更大
SET GLOBAL innodb_io_capacity = 4000; -- SSD 推荐 4000+，机械盘 200
另一个关键参数：innodb_flush_neighbors
= 1（机械盘推荐）：刷脏页时顺带刷相邻脏页，减少随机 I/O= 0（SSD 推荐）：只刷当前脏页，SSD 随机 I/O 能力强无需合并
表文件删不小的原因
数据空洞
删除数据后，InnoDB 并不会立即收缩文件，而是将删除的位置标记为可复用。
-
插入 5 条 → 删除 3 条 → 再插入 3 条 → 文件大小不变（3 个空洞被新数据复用）
-
但如果删除后不再插入： → 文件中存在大量空洞，占用磁盘空间
空洞的来源
| 操作 | 产生空洞的原因 |
|---|---|
DELETE |
记录标记为删除，位置保留为可复用 |
随机 INSERT |
页分裂，页中出现空洞 |
UPDATE 非索引字段 |
原位置更新，无空洞 |
UPDATE 索引字段 |
等同于先删后插，产生空洞 |
重建表（收缩空间）
-- 方法1：Online DDL（MySQL 5.6+，推荐）
-- 重建期间可以正常读写，最后切换到新表
ALTER TABLE t ENGINE=InnoDB;
-- 方法2：optimize table（底层也是重建）
OPTIMIZE TABLE t;
-- 方法3：gh-ost / pt-online-schema-change（大表生产环境推荐）
⚠️
ALTER TABLE在 5.6+ 是 Online DDL，但最后的"替换表"阶段仍需短暂 MDL 写锁，大表时需谨慎评估。
count(*) 为什么慢
MyISAM vs InnoDB
| 引擎 | count(*) 实现 | 速度 |
|---|---|---|
| MyISAM | 直接存储了行数，O(1) | 极快 |
| InnoDB | 遍历全表或索引，O(N) | 慢（MVCC 需要逐行判断） |
InnoDB 必须遍历的原因：MVCC 下，每个事务看到的"行数"不同，无法维护一个全局准确的计数。
四种 count 的性能对比
-- 性能从高到低：
count(*) -- MySQL 优化器专门处理，遍历最小的索引树，推荐
count(1) -- 与 count(*) 基本相同
count(字段) -- 需要判断该字段是否为 NULL，稍慢
count(主键) -- 遍历主键（聚簇索引），数据量大时不如 count(*) 走二级索引
结论：优先用
count(*)，MySQL 会自动选择代价最小的索引遍历。
优化方案
| 方案 | 适用场景 |
|---|---|
| 用 Redis 维护计数器 | 精确性要求不高，允许偶尔不一致 |
| 新建计数表（InnoDB 事务保证） | 要求精确，需要与业务操作保持事务一致性 |
| 业务层统计（如数据量有上限） | 数据量小，性能不是问题 |
order by 的执行原理
全字段排序
-- 假设有 city 索引，查询并按 name 排序
SELECT city, name, age FROM t WHERE city='上海' ORDER BY name LIMIT 1000;
-- 执行流程：
-- 1. 走 city 索引，找到满足条件的行
-- 2. 取出 city, name, age 放入 sort_buffer
-- 3. sort_buffer 中按 name 排序
-- 4. 返回前 1000 行
sort_buffer_size：排序缓冲区大小，超出则使用磁盘临时文件（外部排序，更慢）。
-- 查看当前排序模式
SHOW VARIABLES LIKE 'sort_buffer_size';
-- EXPLAIN 中 Extra 列 "Using filesort" 表示用了文件排序（不一定慢，但值得关注）
rowid 排序
当排序行数据过宽（max_length_for_sort_data 限制），MySQL 改用 rowid 排序：
- 只取排序字段和主键放入 sort_buffer
- 排序后，再按主键回表取完整数据
- 代价：多一次回表，随机 I/O 增加
利用索引消除排序
-- 建联合索引 (city, name)，city 等值后 name 已有序，ORDER BY name 无需额外排序
ALTER TABLE t ADD INDEX idx_city_name(city, name);
-- EXPLAIN 中 Extra 不再出现 "Using filesort"
索引失效的常见场景
场景 1：对索引列使用函数
-- ❌ 索引失效：对 tradeid 使用了函数，破坏了索引树的有序性
SELECT * FROM t WHERE MONTH(created_at) = 7;
-- ✅ 改写：把函数移到右边
SELECT * FROM t WHERE created_at >= '2024-07-01' AND created_at < '2024-08-01';
场景 2：隐式类型转换
-- tradeid 字段类型为 varchar，但传入了整型
-- ❌ 索引失效：MySQL 将 tradeid 转为数值比较（相当于在索引列加了函数）
SELECT * FROM t WHERE tradeid = 110717;
-- ✅ 保持类型一致
SELECT * FROM t WHERE tradeid = '110717';
规则：字符串和数字比较时，MySQL 会将字符串转为数字。即
WHERE varchar_col = 数字会导致索引失效。
场景 3：隐式字符集转换
两个表字段类型相同但字符集不同（如一个 utf8，一个 utf8mb4）时，join 连接条件上的索引失效。
-- 避免方式：建表时统一字符集（推荐全部用 utf8mb4）
-- 修复方式：显式转换，或 ALTER TABLE 统一字符集
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4;
只查一行为什么慢
场景 1：等 MDL 锁
-- 出现 "Waiting for table metadata lock"
-- 原因：有其他会话正在做 DDL 或 FLUSH TABLES
SHOW PROCESSLIST;
-- 找到阻塞的 session 后 kill
场景 2：等 flush
-- 出现 "Waiting for table flush"
-- 原因：某个线程对该表做了 flush，但 flush 被另一个查询阻塞
-- 找到阻塞 flush 的慢查询 kill 即可
场景 3：等行锁
-- 查询等待行锁
SELECT * FROM sys.innodb_lock_waits\G
-- 找到持有锁的事务 ID，kill 对应 session
场景 4：一致性读回溯版本链
-- RR 隔离级别下，有长事务 + 大量更新时
-- 本事务 read-view 很老 → 需要沿版本链回溯 N 个 undo log → 时间长
-- 解决：避免长事务 + 大量更新同一行
查大量数据会不会 OOM
全表扫描时，MySQL 不会把所有数据全部加载到内存。
全表扫描的内存机制
InnoDB 将数据以"边读边发"的方式返回给客户端：
InnoDB Buffer Pool → Server 层 net_buffer → 客户端
↑
net_buffer 满时发送，发送完再继续读
默认 16 KB（net_buffer_length）
- 结论：全表扫描不会 OOM，内存使用量是恒定的（约等于 net_buffer + 少量 Buffer Pool 页）
- 但会影响 Buffer Pool：大量页被扫描后，可能淘汰热点数据页，影响其他查询的缓存命中率
系统资源优化配置
Buffer Pool 相关
-- Buffer Pool 大小（物理内存的 50-75%，最重要的 InnoDB 配置）
innodb_buffer_pool_size = 8G
-- 多个 Buffer Pool 实例，减少并发竞争（大内存时）
innodb_buffer_pool_instances = 8
-- 查看 Buffer Pool 命中率（应尽量接近 100%）
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests 越小越好
连接数配置
-- 最大连接数（默认 151，需根据应用并发量调整）
max_connections = 500
-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
-- 连接超时（空闲连接 8 小时后断开）
wait_timeout = 28800
interactive_timeout = 28800
查询缓冲区
-- 排序缓冲区（每个连接独立分配，不宜过大）
sort_buffer_size = 4M
-- join 缓冲区（BNL 算法时使用，适当增大可减少多轮扫描）
join_buffer_size = 8M
-- 读取缓冲区
read_buffer_size = 2M
read_rnd_buffer_size = 8M
重要系统变量速查
-- 查看所有与 InnoDB 相关的状态
SHOW ENGINE INNODB STATUS\G
-- 查看 MySQL 版本
SELECT VERSION();
-- 查看所有系统变量
SHOW VARIABLES;
SHOW VARIABLES LIKE 'innodb%';
-- 查看所有状态变量
SHOW STATUS;
SHOW GLOBAL STATUS LIKE 'Com_%'; -- 各类 SQL 语句执行次数
参考资料
- 《MySQL 实战 45 讲》— 第 12 讲：为什么我的 MySQL 会"抖"一下？
- 《MySQL 实战 45 讲》— 第 13 讲：为什么表数据删掉一半，表文件大小不变？
- 《MySQL 实战 45 讲》— 第 14 讲：count(*) 这么慢，我该怎么办？
- 《MySQL 实战 45 讲》— 第 16 讲：order by 是怎么工作的？
- 《MySQL 实战 45 讲》— 第 18 讲：为什么这些 SQL 语句逻辑相同，性能却差异巨大？
- 《MySQL 实战 45 讲》— 第 19 讲：为什么我只查一行的语句，也执行这么慢？
- 《MySQL 必知必会》— 第 26 讲：如何充分利用系统资源？
评论 (0)
发表评论