MySQL 调优的（实战思路）

我是怎么做 MySQL 调优的（实战思路）

这不是“某几个参数调一调”的问题，而是一整套从 架构 → SQL → 索引 → 配置 → 系统 的排查流程。
你可以把这篇当成自己的 MySQL 调优 Checklist。

---

一、调优前的共识：先度量，再优化

调优最怕两件事：

1. 拍脑袋调参数：改了一堆配置，QPS 没上去，反而更慢；
2. 只盯一个点：只会改 innodb_buffer_pool_size，其他全靠缘分。

所以第一步永远是：

1. 确认问题是什么

   • 慢？慢在哪？是“偶尔很慢”还是“永远都不快”？
   • CPU 打满？IO 打满？连接数飙高？锁等待多？

2. 收集基础信息

   • 版本：SELECT VERSION();
   • 业务类型：读多写少？写多读少？混合？
   • 数据量、QPS、TPS。

3. 看监控 / 日志

   • 慢查询日志（slow log）；
   • CPU / IO / QPS 曲线；
   • SHOW GLOBAL STATUS 中的一些关键指标。

原则：先找到“最痛的那块”，再下手。

---

二、第一刀：从 SQL 入手（慢查询 & 执行计划）

绝大多数 MySQL 性能问题，都可以归结为：SQL 写得不行 + 索引没设计好。

2.1 开启慢查询日志

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;   -- 超过 1 秒的算慢查询
SET GLOBAL log_queries_not_using_indexes = 1;  -- 也可以先开一阵

然后看：

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

线下或运维平台中，可以用工具分析 slow log：

• 比如 mysqldumpslow、pt-query-digest 等；
• 找出 次数最多 / 最慢 / 总耗时最高 的那些 SQL。

2.2 对慢 SQL 用 EXPLAIN 做执行计划分析

EXPLAIN SELECT ...;

重点看：

• type：访问类型（ALL、index、range、ref、const 等）；
  • ALL = 全表扫描，基本就是优化对象；
• key：实际使用的索引；
• rows：预估扫描行数；
• Extra：Using filesort、Using temporary、Using index 等。

2.3 改写 SQL + 补索引

常见优化方式：

1. 为 高频 WHERE 条件 / JOIN 字段 / ORDER BY 字段 建索引；

2. 把“函数/计算写在左边”的写法改掉：

   -- ❌ 错误示例：索引容易失效
   WHERE DATE(create_time) = '2025-01-01';
   
   -- ✅ 正确示例：使用范围
   WHERE create_time >= '2025-01-01 00:00:00'
     AND create_time <  '2025-01-02 00:00:00';
   

3. 优先使用 联合索引 + 最左前缀：

   -- 经常这么查
   WHERE user_id = ? AND status = ? AND create_time > ?;
   
   -- 就建一个
   CREATE INDEX idx_user_status_time ON t_order(user_id, status, create_time);
   

4. 避免在高并发场景下使用：

   • SELECT *（只取必要字段）；
   • 大范围 IN；
   • OR 拼字段（可考虑拆成 UNION）；
   • 复杂嵌套子查询（可改 JOIN 或分步查询）。

实战经验：先搞定 Top N 慢 SQL，系统性能往往就上一个台阶。

---

三、第二刀：索引与表结构设计

3.1 索引设计思路

1. 区分“高频查询条件”和“偶尔查查”；
2. 给经常出现在 WHERE / JOIN / ORDER BY / GROUP BY 的列设计索引；
3. 尽量用 联合索引代替多个单列索引；
4. 注意索引列的选择性（区分度）：
   • 性别、是否删除（0/1）这类字段不要单独建索引；
   • 可以放在联合索引里。

3.2 合理选择主键与存储引擎

InnoDB：

• 强烈建议：自增整型主键 + InnoDB；
• 避免使用 UUID/业务字符串作为聚簇索引主键；
• 避免过宽的主键，所有二级索引都要跟着变胖。

表结构方面：

• 避免大字段（TEXT/BLOB）频繁参与查询，可拆分表；
• 合理拆分“冷热字段”，避免每次查询都扫一大坨无关数据。

---

四、第三刀：InnoDB 关键参数调优

在 SQL & 索引搞定后，如果数据库仍吃紧，可以看配置。

4.1 innodb_buffer_pool_size（最关键的内存参数）

• 作用：InnoDB 用来缓存数据页 + 索引页；
• 一般建议：物理内存的 50%～70% 左右（线下压测决定）；
• 过小：大量读盘，性能抖；
• 过大：挤压 OS 缓存/其他进程，可能换页。

innodb_buffer_pool_size = 8G   # 视机器内存而定

4.2 innodb_log_file_size / log_buffer_size

• redo log 的大小和值：
  • 太小：频繁 flush，增加 IO；
  • 太大：崩溃恢复时间变长。
• 典型值可从几百 MB 到几 GB，需结合写入量、硬件调整。

innodb_log_file_size = 1G
innodb_log_buffer_size = 64M

4.3 innodb_flush_log_at_trx_commit

控制事务提交时 redo log 的刷盘策略：

• 1：每次事务提交都刷盘（最安全，最耗 IO）；
• 2：每次提交写 OS 缓存，每秒刷盘一次；
• 0：每秒写一次 + 刷盘。

业务取舍：

• 金融/强一致：倾向 1；
• 对少量数据丢失能接受，可以考虑 2，性能更好。

4.4 连接数相关

max_connections
thread_cache_size
wait_timeout
interactive_timeout

• max_connections 不是越大越好：
  • 太大容易让机器被打爆；
  • 一般配合连接池使用，几百~一两千视情况。

---

五、第四刀：操作系统 & 硬件层面

当你确认：

• SQL 已经相对合理；
• 索引也设计得不错；
• 配置也调过；

此时数据库仍然吃紧，就要看系统层面。

5.1 磁盘 IO

• 用 iostat、vmstat、iotop 看：
  • 磁盘队列长度、IO 等待；
• 如果 IO 明显成为瓶颈：
  • 换 SSD；
  • RAID 级别调整；
  • 分库分表、冷热分离。

5.2 CPU

• 大量复杂 SQL、函数计算、排序、JOIN 会吃 CPU；
• 通过慢查询 + EXPLAIN 优化 SQL，减少 CPU 压力；
• 必要时升级机器配置或做读写分离、分库。

5.3 网络

• 主从复制延迟大、跨机房访问慢；
• 尽量让数据库靠近应用部署（同一可用区/机房）。

---

六、第五刀：架构级优化（分库分表 & 读写分离）

当单实例再怎么调也扛不住时，就是架构层面问题了。

6.1 读写分离

典型做法：

• 主库负责写、从库负责读；
• 中间加一个数据访问层（或中间件）做路由；
• 部分请求强制走主库（写后立刻读）。

适合场景：

• 读远多于写；
• 允许短暂读到旧数据的场景。

6.2 分库分表

如果单表行数上亿，索引高度、统计信息、缓存命中都会出问题。

常见拆分维度：

• 按业务维度拆库（用户库、订单库、日志库）；
• 按 hash/范围拆分大表：
  • 用户 ID 取模；
  • 按时间分表（按月/按日）。

拆分后要注意：

• 跨分片 JOIN 不再简单支持，需要应用层处理；
• 全局唯一主键（雪花算法、号段发号等）；
• 分布式事务（能避则避，多用最终一致方案）。

---

七、监控与排查工具清单

调优离不开“观察工具”：

1. 慢查询日志：慢 SQL 排查入口；
2. EXPLAIN / EXPLAIN ANALYZE：看执行计划、实际耗时；
3. SHOW PROCESSLIST：看当前连接、是否有锁等待；
4. SHOW ENGINE INNODB STATUS\G：看死锁、事务等待；
5. information_schema / performance_schema / sys 库：
   • 统计哪些 SQL 最耗费资源。
6. 操作系统工具：top、iostat、vmstat、sar 等。

---

八、一套实战调优流程（总结版）

给你一套可直接复用的步骤：

1. 确认问题类型

   • 慢：是个别 SQL 慢，还是所有操作都慢？
   • 卡：CPU 高？IO 高？锁等待多？

2. 开慢日志 + 抓 Top SQL

   • 找出最慢/最频繁/总时长最高的 SQL；
   • 优先优化前 10% 的“重灾区”。

3. 抓执行计划（EXPLAIN）

   • 看是否使用索引；
   • type 是否为 range/ref/const 而不是 ALL；
   • 是否大量 Using filesort、Using temporary。

4. 设计/调整索引 & 改写 SQL

   • 减少全表扫描、回表、排序临时表；
   • 利用覆盖索引和联合索引。

5. 调整 InnoDB 核心参数

   • innodb_buffer_pool_size、innodb_log_file_size、innodb_flush_log_at_trx_commit 等；
   • 压测不同参数组合的效果。

6. 观察硬件资源

   • 磁盘 IO 是否打满；
   • CPU 是否经常 100%；
   • 内存是否频繁换页。

7. 必要时考虑架构改造

   • 加从库做读写分离；
   • 对热点库/表做拆分。

---

九、小结

MySQL 调优本质上是一个“找瓶颈 → 定位层级 → 有针对性优化”的过程：

1. SQL 与索引永远是第一优先级；
2. InnoDB 参数是在“SQL/索引合理之后”的加分项；
3. 再往上，是硬件和架构层面的扩展。

一句话：

不要指望靠改几个参数就“神奇提速”，调优一定是数据驱动 + 全链路思考。
养成“看慢日志 + EXPLAIN + 监控”的习惯，你的 MySQL 水平会非常快地上来。