跳到主要内容

创建日期：2026-09-08 ｜ 最近更新：2026-09-08 本篇为方法论/清单向，EXPLAIN 类证据沿用篇 3 的实测输出；配置项以 8.4 官方文档为准。

MySQL 精通篇 7：性能调优与生产要点

能写对 SQL 是「会」，能把服务调得快且稳是「精」。这篇是「从能跑到扛得住」的 checklist——慢查询怎么找、索引怎么补、大表分页怎么写、上线前要做什么。

1. 优化顺序（别一上来调配置）​

① SQL 写得好不好（大多数问题在这）→ EXPLAIN 看
② 索引齐不齐（篇 3）
③ 表结构/范式设计（篇 5）
④ 事务/锁使用（篇 4）
⑤ 最后才动 MySQL 配置/硬件/架构（缓存、读写分离）

80% 的慢查询是 SQL 或索引问题，调参数是最后手段。

2. 找慢查询：三件套​

① 慢查询日志​

# my.cnf
slow_query_log = 1
long_query_time = 1        # 超过 1 秒的 SQL 记下来
slow_query_log_file = /var/log/mysql/slow.log

定期 mysqldumpslow / 用工具看这些慢 SQL，逐一 EXPLAIN。

② EXPLAIN（篇 3 复习）​

看到 type: ALL + 大 rows → 命中篇 3 的「五宗罪」检查清单：函数包列 / 隐式转换 / %xx% / 复合索引没走最左 / OR。想看实际执行时间用 EXPLAIN ANALYZE（8.0.18+，真跑并给每步耗时）。

③ performance_schema / 连接状态​

SHOW FULL PROCESSLIST;      -- 看现在谁在跑什么（卡住/长事务一眼见）
SHOW ENGINE INNODB STATUS;  -- 死锁等诊断

3. 高频「写 SQL 就慢」反模式（背下来）​

反模式改法
SELECT * 大宽表只取需要的列（还能用覆盖索引，篇 3）
LIMIT 100000, 20 深分页用游标/键集分页：WHERE id > 上次最后id ORDER BY id LIMIT 20（见下）
循环里逐条查（N+1）一次 IN / JOIN 取回来
对列用函数改成范围条件（created_at >= ? AND < ?）
忘加索引就上线上线前 EXPLAIN 一遍热点 SQL
COUNT(*) 扫大表换成汇总表 / 近似值（非精确场景）

深分页为何慢、怎么治​

LIMIT 100000, 20 要先数 10 万行再丢——越翻越慢。键集分页（只往后翻时）更快：

-- ❌ 深翻页
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- ✅ 记住上一页最后一条 id
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

配合索引，每次都只扫 20 行。「上一页最后 id」来自上一条返回（客户端回传即可）。缺点是不能随意跳页——产品上可接受就用它。

4. 大表/长久的工程手段​

场景手段
表超大（千万行+）分区表；或按时间归档/分表（order_2025 之类，应用路由）
读多写少加读缓存（Redis）；主从读写分离：主库写、从库读
高写入批量 insert、减少索引数量、必要时削峰
历史数据冷热分离（热库 + 归档库）

读写分离注意：主从有复制延迟——刚写就读可能读到旧值（篇 4 的隔离直觉同样适用）。关键读走主库，或容忍短暂延迟。

5. 连接与并发（后端视角）​

  • 连接池必须有（HikariCP / mysql2 pool 等），别每条 SQL 新建连接；池大小别盲目设大（太大反而因锁/上下文切换变慢，经验 10~20 起步）；
  • 长事务/未提交连接是隐形杀手：占连接 + 持锁 → 设置合理 wait_timeout、代码里事务及时 COMMIT/ROLLBACK；
  • 写冲突/死锁：代码要有重试（死锁是被回滚的那个事务要重新执行，篇 4）。

6. 上线前 checklist（基础设施向）​

账号与安全：

CREATE USER 'app'@'%' IDENTIFIED BY '强密码';        -- MySQL 8 默认 caching_sha2_password
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'%';   -- 最小权限，别给 root/ALL
FLUSH PRIVILEGES;   -- 8.0 后一般不需要，保留习惯无害
  • 应用账号只给用得到的库和权限；别用 root 连业务；
  • 远程连接走 TLS 或内网/VPN；端口别裸暴露公网。

配置与备份：

  • innodb_buffer_pool_size：设为物理内存的 50~70%（InnoDB 缓存，最重要参数之一）；
  • 备份必须有且演练过：逻辑备份 mysqldump，物理/一致性用 mysqlbackup 或云快照；至少 binlog 开启便于时间点恢复；
  • 定期迁移演练：能不能恢复到 5 分钟前，得真的试一次。

字符集与时区：库表统一 utf8mb4；时间尽量统一存 UTC（应用层转本地）。

7. 一页「生产自检」速查​

□ 所有表 InnoDB + utf8mb4
□ 主键合理，外键列/热点 WHERE 列有索引
□ 热点 SQL 全 EXPLAIN 过，无 ALL 大 rows
□ 深分页用键集/游标，无 SELECT *
□ 事务短、有死锁重试、连接用池
□ 慢查询日志开启，定期扫
□ 应用账号最小权限，非 root
□ buffer pool 按内存配好
□ 备份 + 恢复演练做过，binlog 开启

8. 学完这套你能做什么​

  • 建一套规范的表结构（篇 5）并写对增删改查/关联统计（篇 1/2）；
  • 慢查询能用 EXPLAIN 定位并加索引解决（篇 3）；
  • 理解事务/隔离/锁，能解释并发数据问题（篇 4）；
  • 上线前的性能与安全要点心里有数（本篇）。

从「入门」到「精通」的最后一公里，永远是在真实数据和真实流量里练。把每篇的「动手」在你自己项目里跑一遍，比读十遍有用。

自测​

  1. 优化顺序第一步应该看什么？为什么别先调配置？
  2. LIMIT 100000,20 慢在哪？键集分页怎么写？
  3. 读写分离最大的坑是什么？
  4. 应用账号为什么别用 root？最小权限怎么做？
  5. 最重要的 InnoDB 参数是哪个，一般配多大？

关联​