第 37 章 MySQL踩坑：性能与慢查询篇

📖 本篇简介：本篇收拾的是"数据库突然变慢、CPU 打满、连接耗尽"这类线上事故级坑点——连接数打满、Buffer Pool 配错、没开慢查询日志、索引选择性差却不自知、临时表落磁盘。这些问题平时不显山露水，一到大促或突发流量就集体爆发。前置知识是你已经看过 EXPLAIN 执行计划、了解 Buffer Pool 和 redo log 的基本作用。学完之后，你应该有一套"慢查询来了怎么定位、怎么量度、怎么根治"的标准动作，而不是靠猜。本系列所有示例基于 MySQL 8.0 语法书写，本示例基于 MySQL 8.0 环境。

目录

• 坑 1：连接数打满新请求全被拒
• 坑 2：innodb_buffer_pool_size 配得不合理
• 坑 3：没开慢查询日志瞎优化
• 坑 4：type 全表扫却以为走了索引
• 坑 5：没定期统计信息过期导致选错索引
• 坑 6：临时表落磁盘
• 坑 7：sort_buffer 太小 filesort 慢
• 坑 8：慢查询 select 加了大字段网络传输慢
• 坑 9：磁盘用机械盘 IO 打满
• 坑 10：连接池配置不当
• 坑 11：没监控突发流量直接压垮
• 坑 12：大查询和小查询混跑
• 速查表：性能慢查坑点速查
• 注意事项：慢查询治理的标准流程
• 练习题：动手自测与参考

坑 1：连接数打满新请求全被拒

场景 / 错误示范：

-- 错误示范：应用日志突然全是 "Too many connections"
SHOW VARIABLES LIKE 'max_connections';   -- 500
SHOW STATUS LIKE 'Threads_connected';    -- 500，满了

-- 新请求全被拒，业务报 500，但其实数据库还活着
-- 往往是某个慢查询占着连接不释放，连接被一个个耗光

为什么是坑：连接是有限资源，每个连接占内存。一个慢 SQL 跑几十秒，就占一个连接几十秒。并发量一上来，连接池慢慢被慢查询占满，新请求拿不到连接。这不是连接数配小了，是慢查询在偷连接。光调大 max_connections 是治标不治本。

正确做法：

-- 正确做法一：先看谁占着连接
SELECT id, user, host, db, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;
-- 找到跑了几十秒的那条 SQL，先 KILL 掉

-- 正确做法二：调大连接数 + 调小 wait_timeout
SET GLOBAL max_connections = 1000;
SET GLOBAL wait_timeout = 60;   -- 空闲连接 60 秒就断

-- 正确做法三：应用层用连接池复用，别每次请求新建连接
-- 根本解决：把慢 SQL 优化掉，连接自然释放得快
KILL 12345;   -- 杀掉那条慢查询

加分点：监控 Threads_connected / max_connections 比值，超过 80% 就告警。连接池 max 别超过 MySQL max_connections 的一半，留余量给运维和其他业务。

坑 2：innodb_buffer_pool_size 配得不合理

场景 / 错误示范：

-- 错误示范一：32G 内存的机器，Buffer Pool 还是 128M
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';   -- 128M
-- 热点数据全在磁盘，IO 打满，QPS 上不去

-- 错误示范二：矫枉过正，Buffer Pool 直接给 30G
-- 操作系统和其他进程没内存用，开始 swap，数据库反而更慢
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

为什么是坑：Buffer Pool 是 InnoDB 性能的命门。配小了，每次查询都读磁盘；配太大了，操作系统 page cache 没空间，甚至触发 swap，数据库直接卡死。合理值是物理内存的 50%~70%，给 OS、连接、其他进程留够。

正确做法：

-- 正确做法：按物理内存比例配，32G 机器给 18G 左右
SET GLOBAL innodb_buffer_pool_size = 18 * 1024 * 1024 * 1024;

-- 写进配置
-- [mysqld]
-- innodb_buffer_pool_size = 18G
-- innodb_buffer_pool_instances = 8

-- 看命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';  -- 总逻辑读
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';          -- 物理读
-- 命中率 = 1 - reads / read_requests，应 > 99%

加分点：8.0 支持 online 调整 Buffer Pool，不用重启。但调整是按 chunk 进行的，尽量调成 innodb_buffer_pool_chunk_size 的整数倍，避免碎片。

坑 3：没开慢查询日志瞎优化

场景 / 错误示范：

-- 错误示范：用户说"数据库慢"，开发凭感觉猜哪条 SQL 慢
-- 实际上根本没开慢查询日志，不知道到底慢在哪
SHOW VARIABLES LIKE 'slow_query_log';   -- OFF
SHOW VARIABLES LIKE 'long_query_time'; -- 10
-- 10 秒以上才算慢，线上一秒的 SQL 根本没记录

为什么是坑：慢查询日志是定位慢 SQL 的唯一客观依据。没开它，优化就是凭感觉猜。long_query_time 默认 10 秒，意味着 1~9 秒的 SQL 都被忽略，而这些恰恰是拖垮系统的主力。先量度，再优化，别跳步。

正确做法：

-- 正确做法：开慢查询日志，阈值设 1 秒
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;        -- 超过 1 秒记录
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';

-- 没走索引的查询也记录（哪怕很快）
SET GLOBAL log_queries_not_using_indexes = ON;

-- 用 mysqldumpslow 或 pt-query-digest 分析慢日志
-- mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
-- 按时间排序，看最慢的前 10 条

# my.cnf 固定
[mysqld]
slow_query_log = ON
long_query_time = 1
log_queries_not_using_indexes = ON

加分点：log_queries_not_using_indexes=ON 在大库上会产生很多日志，适合灰度开一段时间找出漏网 SQL，别长期开。分析慢日志用 pt-query-digest，比 mysqldumpslow 好用得多。

坑 4：type 全表扫却以为走了索引

场景 / 错误示范：

-- 错误示范：开发说"这条 SQL 加了索引啊"，但 EXPLAIN 一看
EXPLAIN SELECT * FROM orders WHERE phone = 13800001111;
-- 结果：type=ALL，key=NULL，rows=几百万
-- 索引根本没用上！开发凭"我加了索引"的印象就上线

为什么是坑：加了索引不等于用上了索引。函数包裹、隐式转换、OR、低选择性、优化器选错，都会让索引失效。不看 EXPLAIN 就以为走了索引，是慢查询治理最常见的自欺欺人。

正确做法：

-- 正确做法：每条慢 SQL 必看 EXPLAIN，重点看这几列
EXPLAIN SELECT * FROM orders WHERE phone = '13800001111';
-- type：至少 range/ref，出现 ALL 就是全表扫
-- key：实际用的索引，NULL 就是没用索引
-- rows：预估扫描行数，越大越危险
-- Extra：出现 Using filesort / Using temporary 要警惕

-- 发现 type=ALL，回去翻索引篇的 12 个坑，对照找原因
-- 修完再 EXPLAIN 一遍，确认 type 上去了、key 是你建的索引

加分点：把 EXPLAIN 结果截图发到群里 review，别光看 SQL 文本。key 列才是真相，key_len 能看出联合索引用了几列。

坑 5：没定期统计信息过期导致选错索引

场景 / 错误示范：

-- 错误示范：一张表数据翻了 10 倍，但统计信息还是旧的
-- 优化器以为某个索引选择性很好，实际现在很烂，选错索引
ANALYZE TABLE orders;   -- 从来没人跑过

-- 现象：同一条 SQL，昨天还快，今天突然慢了
-- 数据量变了，但优化器还按老黄历选索引

为什么是坑：MySQL 优化器靠"统计信息"（每个列的基数、分布）估算走哪个索引代价小。统计信息过期，优化器就像拿着旧地图找路，会选错索引。8.0 会自动更新统计信息，但大表批量导入后还是建议手动 ANALYZE。

正确做法：

-- 正确做法：大批量导入、数据归档后，手动更新统计信息
ANALYZE TABLE orders;

-- 查看统计信息
SHOW INDEX FROM orders;
-- Cardinality 列是估算的唯一值数，和实际行数对比一下，差太多就 ANALYZE

-- 8.0 还可以持久化统计信息，避免重启后重新采样
-- [mysqld]
-- innodb_stats_persistent = ON

加分点：发现"同一条 SQL 时快时慢"，大概率是优化器选了不同执行计划。可以用 FORCE INDEX(idx_xxx) 临时钉死，根治还是靠更新统计信息 + 让索引设计本身无歧义。

坑 6：临时表落磁盘

场景 / 错误示范：

-- 错误示范：GROUP BY + ORDER BY 的列不在索引里，结果集又大
SELECT user_id, COUNT(*) FROM orders
WHERE create_time >= '2026-10-01'
GROUP BY user_id
ORDER BY COUNT(*) DESC;
-- EXPLAIN：Extra 出现 'Using temporary; Using filesort'
-- 临时表超过 tmp_table_size，落磁盘，性能暴跌

为什么是坑：MySQL 做复杂 GROUP BY/ORDER BY 时，先在内存里建临时表。内存临时表超过 tmp_table_size 或 max_heap_table_size，就转成 MyISAM 磁盘临时表，磁盘 IO 比内存慢几个数量级。结果集越大，落磁盘代价越高。

正确做法：

-- 正确做法一：让 GROUP BY 的列走索引，避免临时表
-- 建联合索引 (create_time, user_id)
ALTER TABLE orders ADD KEY idx_time_uid (create_time, user_id);
-- 这样 GROUP BY user_id 能利用索引有序性，不用建临时表

-- 正确做法二：调大临时表内存上限
SET GLOBAL tmp_table_size = 64 * 1024 * 1024;      -- 64M
SET GLOBAL max_heap_table_size = 64 * 1024 * 1024;

-- 监控落磁盘次数
SHOW STATUS LIKE 'Created_tmp_disk_tables';
SHOW STATUS LIKE 'Created_tmp_tables';
-- 前者/后者比值高，说明大量临时表落磁盘

加分点：临时表落磁盘是症状不是病，根源是 SQL 没走索引。调大内存参数只是缓解，根治还是改 SQL + 加索引。

坑 7：sort_buffer 太小 filesort 慢

场景 / 错误示范：

-- 错误示范：ORDER BY 的结果集很大，sort_buffer 只有 256K
SELECT * FROM orders
WHERE user_id = 100
ORDER BY create_time DESC
LIMIT 20;
-- Extra: Using filesort，数据超出 sort_buffer，分块排序再合并，慢

为什么是坑：filesort 是 MySQL 在内存或磁盘上对结果排序的过程。sort_buffer_size 决定它一次能在内存排多少。结果集超过这个大小，就分块写到磁盘临时文件，归并排序，性能掉得厉害。但 sort_buffer 是每个连接分配的，开太大也会爆内存。

正确做法：

-- 正确做法一：让 ORDER BY 走索引，避免 filesort
-- 建索引 (user_id, create_time)
ALTER TABLE orders ADD KEY idx_uid_time (user_id, create_time);
-- 之后 ORDER BY create_time 直接利用索引顺序，无 filesort

-- 正确做法二：sort_buffer 适度调大，2M~4M 足够
SET GLOBAL sort_buffer_size = 4 * 1024 * 1024;

-- 监控
SHOW STATUS LIKE 'Sort_merge_passes';   -- 合并次数，高了说明 sort_buffer 不够

# my.cnf
[mysqld]
sort_buffer_size = 4M

加分点：sort_buffer 是 per-connection 的，max_connections=1000 时，sort_buffer=4M 最坏情况吃 4G 内存。别贪大，根治还是让排序走索引。

坑 8：慢查询 select 加了大字段网络传输慢

场景 / 错误示范：

-- 错误示范：列表页查询带了 text/json 大字段
SELECT * FROM goods WHERE category_id = 5;
-- goods 表里有 detail TEXT 字段，一条几 KB
-- 查 1000 行就是几 MB 网络传输，接口看着"慢"，其实是传输慢

为什么是坑：SQL 在数据库里跑得快，不代表接口响应快。SELECT 把大字段拉回来，网络传输、应用序列化、JSON 编码都要时间。列表页根本不需要详情，却把详情也拉回来了，纯浪费。

正确做法：

-- 正确做法：列表只取展示字段，详情走单独接口
SELECT id, name, price, image_url FROM goods WHERE category_id = 5;
-- 详情接口再单独查 detail
SELECT detail FROM goods WHERE id = 123;

-- 大字段（TEXT/BLOB/JOSN）拆到扩展表，主表只放常用列
-- 这样主表行更短，Buffer Pool 能缓存更多行

加分点：设计表时把大字段拆出去，是"行内优化"的经典手段。主表行越短，同样的 Buffer Pool 能缓存越多数据，整体性能越好。

坑 9：磁盘用机械盘 IO 打满

场景 / 错误示范：

-- 错误示范：数据库装在机械硬盘上，随机 IO 一高就卡住
-- iostat 看 %util 接近 100%，CPU 不高但响应很慢
-- 典型场景： Buffer Pool 命中率不够，大量随机读打到机械盘

为什么是坑：机械盘随机 IO 能力约每秒 100~200 次，SSD 能到几万。MySQL 是典型的随机 IO 负载（回表、索引查找）。用机械盘，Buffer Pool 一旦没命中，延迟直接从亚毫秒跳到十几毫秒，高并发下排队雪崩。这不是软件能调优的，是硬件天花板。

正确做法：

-- 正确做法一：数据库必须用 SSD，别图省钱
-- 云数据库直接选 SSD 云盘类型

-- 正确做法二：软件层尽量让热点数据常驻内存
-- 把 Buffer Pool 调大，命中率做到 99%+，磁盘 IO 自然下来
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

-- 正确做法三：把 binlog、redo log、数据目录分到不同盘
-- 减少 IO 竞争

# 用 iostat 看磁盘是不是瓶颈
# iostat -xz 1
# 看 %util 接近 100、await 高，就是磁盘 IO 瓶颈

加分点：生产环境数据库上 SSD 是性价比最高的升级，比加 CPU 管用。云盘还要看 IOPS 规格，别选容量型大盘（IOPS 低）。

坑 10：连接池配置不当

场景 / 错误示范：

# 错误示范：HikariCP 连接池 maxPoolSize=500
# 一个应用实例开 500 个连接，三个实例就是 1500
# MySQL max_connections=1000，直接被打爆
spring:
  datasource:
    hikari:
      maximum-pool-size: 500   # 太大
      minimum-idle: 500          # 永远占满

为什么是坑：连接池不是越大越好。数据库端连接数有限，应用端每个实例开太多连接，叠加起来就超过 MySQL 上限。而且 MySQL 每个连接都吃内存，连接多了反而互相抢资源。HikariCP 官方建议：连接数 ≈ CPU 核数 × 2 + 有效磁盘数，远没你想的那么大。

正确做法：

# 正确做法：连接池大小按数据库总容量反推
# MySQL max_connections=1000，10 个应用实例 → 每个池 50 就够
spring:
  datasource:
    hikari:
      maximum-pool-size: 50
      minimum-idle: 10
      connection-timeout: 3000   # 拿不到连接 3 秒超时
      max-lifetime: 600000       # 连接 10 分钟回收，配合 wait_timeout

加分点：连接池排队比让 MySQL 接太多连接健康。拿不到连接就快速失败、快速重试，别让请求在线程池里干等。监控连接池活跃数和等待数，比监控 MySQL 连接数更早预警。

坑 11：没监控突发流量直接压垮

场景 / 错误示范：

-- 错误示范：大促当天流量翻 5 倍
-- 数据库 CPU 从 20% 飙到 100%，响应从 10ms 到 2s
-- 运维是用户投诉了才知道，没有任何提前告警
SHOW GLOBAL STATUS LIKE 'Threads_running';  -- 高并发时应该监控

为什么是坑：数据库性能问题不是突然发生的，是慢慢恶化的。没有监控，你只能在用户投诉时才知道。突发流量下，慢查询被放大 N 倍，连接池被打满，雪崩只是时间问题。监控和告警是保命的。

正确做法：

-- 正确做法：至少监控这几个指标
-- 1. Threads_running：正在跑的活跃线程，突增说明有慢查询
SHOW GLOBAL STATUS LIKE 'Threads_running';
-- 2. InnoDB 行锁等待数
SHOW STATUS LIKE 'InnoDB_row_lock_waits';
-- 3. Buffer Pool 命中率
-- 4. 主从延迟
-- 5. 慢查询数

-- 用 Prometheus + Grafana 或云厂商监控，设阈值告警
-- 比如 Threads_running > 50 持续 1 分钟就告警

加分点：大促前做压测，把数据库压到极限看拐点。线上配置永远按压测拐点的 60% 水位运行，留够余量。没压测过的容量规划都是猜。

坑 12：大查询和小查询混跑

场景 / 错误示范：

-- 错误示范：订单列表（毫秒级）和报表统计（秒级）打同一个库
-- 报表一条慢 SQL 占了大量 IO 和 Buffer Pool
-- 订单列表的热点页被挤出 Buffer Pool，也开始变慢
-- 两个本来互不相关的业务，互相拖累

为什么是坑：MySQL 是资源共享的，Buffer Pool、IOPS、CPU 都混在一起。一个大报表扫全表，会把热点数据挤出 Buffer Pool，导致本来很快的在线查询也变慢。这叫"邻居噪音"。在线业务和重报表抢同一个库，迟早出事。

正确做法：

-- 正确做法一：读写分离，报表走从库
-- 在线写走主库，报表和大查询走从库，互不影响

-- 正确做法二：报表类大查询走专门的分析库（数仓 / ClickHouse）
-- 别在业务 OLTP 库里跑 OLAP 查询

-- 正确做法三：必须在同一库，就用资源组（8.0 资源组）限制大查询
-- 或在代码层给报表 SQL 加超时，别让它跑半小时
SET SESSION max_execution_time = 30000;   -- 30 秒超时

加分点：在线库和分析库分离是架构基本功。哪怕只有一主一从，把报表、后台导出都赶到从库，主库的在线性能立刻稳一大截。

速查表：性能慢查坑点速查

| 坑号 | 坑点 | 监控指标 | 修法 |

|---|---|---|---|

| 1 | 连接打满 | Threads_connected | KILL 慢查询 + 优化 SQL |

| 2 | Buffer Pool 配错 | 命中率 < 99% | 物理内存 50%~70% |

| 3 | 没开慢日志 | slow_query_log=OFF | 开慢日志，阈值 1s |

| 4 | 误以为走索引 | EXPLAIN type=ALL | 看 EXPLAIN 修索引 |

| 5 | 统计信息过期 | Cardinality 偏差 | ANALYZE TABLE |

| 6 | 临时表落磁盘 | Created_tmp_disk_tables | GROUP BY 走索引 |

| 7 | filesort 慢 | Sort_merge_passes | ORDER BY 走索引 |

| 8 | SELECT 大字段 | 接口响应慢 | 列表只取必要列 |

| 9 | 机械盘 IO 满 | iostat %util | 上 SSD |

| 10 | 连接池过大 | 连接数超 MySQL 上限 | 池大小按总容量反推 |

| 11 | 没监控告警 | Threads_running | 上监控 + 压测 |

| 12 | 大小查询混跑 | 在线查询被拖慢 | 读写分离 / 报表走从库 |

注意事项：慢查询治理的标准流程

线上遇到"数据库慢"，按这个顺序走，别跳步：

1. 先量度：开慢查询日志（如果之前没开），用 pt-query-digest 找出 Top 慢 SQL，别凭感觉猜。

2. 看执行计划：对最慢的那条 EXPLAIN，确认 type、key、rows、Extra，定位是没走索引、还是回表多、还是 filesort。

3. 判断根因：是索引问题（回索引篇）、SQL 写法问题（回 SQL 写法篇）、还是配置/硬件问题（本篇）。

4. 改完验证：改完 EXPLAIN 再看一遍，实测响应时间，别改完就上线。

5. 回归观察：上线后看慢查询日志和监控，确认这条 SQL 不再出现，且没引入新问题。

慢查询治理是个长期活，不是一次性优化。建立慢查询日志周报，每周清一批，比大促前临时抱佛脚靠谱。

顺手备一段"线上救火四连"，遇到慢查询时按顺序执行：

-- 线上救火四连：按顺序执行，定位慢在哪一环
-- 第一连：看现在谁在跑、跑了多久
SELECT id, time, state, info FROM information_schema.processlist
WHERE command <> 'Sleep' ORDER BY time DESC LIMIT 10;

-- 第二连：看当前活跃线程数和连接数水位
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Threads_connected';

-- 第三连：看 InnoDB 行锁等待和 Buffer Pool 命中率
SHOW STATUS LIKE 'InnoDB_row_lock_waits';
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

-- 第四连：拿到慢 SQL 后 EXPLAIN，确认 type/key/rows
-- EXPLAIN <把那条慢 SQL 粘进来>;

这四步走完，基本能判断是连接打满、锁等待、IO 瓶颈还是执行计划选错，再对症下药。

练习题：动手自测与参考

练习 1：用户反馈"接口慢"，你怎么定位是不是数据库的问题？

<details>

<summary>参考答案</summary>

先看应用监控（接口耗时分布、GC），再上数据库看慢查询日志有没有这条 SQL、Threads_running 高不高。确认是数据库慢后 EXPLAIN 这条 SQL，按本篇流程定位。别一上来就怀疑数据库，应用层、网络、缓存都可能是瓶颈。

</details>

练习 2：EXPLAIN 看到 Extra 是 Using temporary; Using filesort，说明什么？怎么优化？

<details>

<summary>参考答案</summary>

说明 GROUP BY/ORDER BY 用了临时表和文件排序。优化：让 GROUP BY 的列走联合索引（利用索引有序性），避免建临时表；同时适当调大 tmp_table_size 和 sort_buffer_size 缓解。根治是改索引和 SQL。

</details>

练习 3：Buffer Pool 命中率怎么算？低于多少要警惕？

<details>

<summary>参考答案</summary>

命中率 = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests。用 SHOW GLOBAL STATUS 拿这两个值算。生产环境应长期 > 99%；低于 99% 说明磁盘读偏多，考虑调大 Buffer Pool 或检查热点数据是不是被冷数据挤出去了。

</details>

练习 4：一条 SQL 在测试库很快，上了生产突然变成慢查询，列出至少三种可能原因。

<details>

<summary>参考答案</summary>

①数据量不同：测试库几万行，生产几千万行，索引选择性变了，优化器选错执行计划；②统计信息过期：批量导入后没 ANALYZE TABLE，优化器按老数据选索引；③参数配置不同：生产 Buffer Pool、tmp_table_size 配置不对；④并发不同：生产有其他大查询抢资源；⑤数据分布不同：某个值的倾斜度在生产更严重。排查时先 EXPLAIN 对比测试和生产的执行计划是否一致。

</details>

下一篇预告：第 38 章 MySQL踩坑：主从复制与高可用篇