InnoDB 与性能调优

InnoDB 是 MySQL 默认的事务引擎，几乎所有的性能问题最终都会涉及到它——慢查询、锁等待、连接堆积、磁盘 IO 高、内存不够。理解 InnoDB 内部几个关键对象（Buffer Pool、Redo Log、Undo Log、事务和锁）之间的协作方式，才知道排查时看什么。

一、InnoDB 的几个核心对象

InnoDB 围绕"页"来组织读写。数据页和索引页缓存在 Buffer Pool 里，更新在内存里改页、再写 redo、最后由后台线程把脏页刷回磁盘。这个设计的重点是把随机磁盘写变成顺序写 redo + 后台批量刷脏页，提高写性能的同时用 redo 保证提交不丢。

几个对象放在一起看：

对象	干什么	如果出问题
Buffer Pool	缓存数据页和索引页	命中率低 → 物理读多 → 查询慢
Redo Log	记录已提交事务对页的修改→崩溃恢复	太小 → 刷脏页频繁；太大 → 崩溃恢复慢
Undo Log	存储数据旧版本 → 回滚 + MVCC	长事务 → undo 堆积 → 历史版本链变长
Change Buffer	缓存二级索引的修改 → 减少随机读	对写多读少的非唯一二级索引有帮助
Doublewrite Buffer	防止页写入中途断电导致部分写损坏	增加一些写开销，但保护数据安全

几个"日志"经常被混淆，它们不一样：

日志	所属层级	作用	怎么使用
redo log	InnoDB 引擎层	崩溃恢复：重放已提交事务的页变更	自动，不需要人工干预
undo log	InnoDB 引擎层	回滚 + 一致性读提供历史版本	自动，但长事务会拖累它
binlog	MySQL Server 层	记录已提交的逻辑变更 → 复制、时间点恢复	人工回放、备份恢复的组成部分

MVCC（多版本并发控制）依赖 undo log 来给不同事务提供正确的数据版本。一个普通的 SELECT 在 REPEATABLE-READ 隔离级别下，会根据事务的快照版本去 undo 里找对应版本的数据，而不是总是等当前行上的锁释放。但长事务会让旧版本一直不被清理，undo 表空间越堆越大，purge 线程处理不过来，查询也要跟着翻更深的版本链。

二、事务隔离级别

MySQL 8.0 默认是 REPEATABLE-READ：

SELECT @@transaction_isolation;

常见隔离级别：

级别	特点	常见场景
READ-COMMITTED	每次读到最新已提交数据，不可重复读	高并发 OLTP，对一致性读要求不高
REPEATABLE-READ	同一事务内多次读取看到相同版本	需要事务内数据一致性视图的场景
SERIALIZABLE	最严格，类似对每行 SELECT 加共享锁	极少使用，并发极差

改会话隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

隔离级别影响锁行为。REPEATABLE-READ 下的间隙锁（gap lock）是 MySQL 特有的，为了防止"当前读"时出现幻读。READ-COMMITTED 下间隙锁被禁用了，并发会好一些，但基于 binlog 的复制在某些场景下可能出现不一致（所以默认用 REPEATABLE-READ）。

三、查看事务和锁等待

看当前有哪些活跃事务，运行了多久：

SELECT
  trx_id,
  trx_state,
  trx_started,
  trx_mysql_thread_id,
  trx_query
FROM information_schema.innodb_trx\G

看到启动时间很久的事务，要确认是正常业务逻辑还是被遗忘的事务挂在那里——长事务持有锁、阻止 undo 清理、可能还在从库上拖慢复制。

看锁等待关系——谁在等谁：

SELECT
  r.trx_id AS waiting_trx_id,
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query AS waiting_query,
  b.trx_id AS blocking_trx_id,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id\G

看当前所有连接线程：

SHOW FULL PROCESSLIST;

终止一个线程：

KILL 12345;

KILL 的是 MySQL 线程 ID，不是 Linux PID。执行前确认杀的是阻塞源（blocking）而不是被阻塞的（waiting），否则杀错了只是让业务重试更乱，问题还在。KILL 后大事务回滚也需要时间，不会瞬间释放锁。

四、死锁

查看最近一次死锁信息：

SHOW ENGINE INNODB STATUS\G

在输出里搜索 LATEST DETECTED DEADLOCK，能看到两个事务分别在等对方持有的锁，以及各自的 SQL 语句。

死锁常见原因：

原因	为什么会导致死锁
多个事务以不同顺序更新相同的行	A 先锁 1 再锁 2，B 先锁 2 再锁 1 → 互相等
WHERE 条件没有索引	扫描范围大，锁住更多行，增加冲突概率
间隙锁冲突	REPEATABLE-READ 下的间隙锁可能互相阻塞
大事务	持有锁时间长，期间并发事务都要排队等

死锁不等于数据库坏了。InnoDB 会自动检测死锁并回滚其中一个事务（较小的事务优先），应用层收到死锁错误时做有限重试即可。频繁死锁才需要看 SQL 和事务顺序，核心思路是让多个业务路径以同样的顺序访问资源。

五、Buffer Pool

Buffer Pool 是 InnoDB 最重要的内存区域，缓存数据页和索引页：

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

看命中率情况：

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

两个指标的含义：

指标	含义
Innodb_buffer_pool_read_requests	逻辑读——所有从 Buffer Pool 读数据的请求数（包括命中和未命中）
Innodb_buffer_pool_reads	物理读——Buffer Pool 里没有，需要从磁盘读取的次数

命中率粗略估算：

1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests

这个命中率应该在 99% 以上才算健康。如果物理读比例偏高，说明 Buffer Pool 不够大——热数据放不进内存，频繁去磁盘取。

Buffer Pool 不是越大越好。单机只跑 MySQL 时可以给到物理内存的 70-80%；同机还有别的服务时要给 OS 和其他进程留空间。MySQL 8.0 支持在线调整：

SET GLOBAL innodb_buffer_pool_size = 17179869184;

在线调整有内存分配和释放的过程，生产上更适合放在低峰时段操作。

六、Redo 和刷盘策略

Redo Log 的两个关键配置：

SHOW VARIABLES WHERE Variable_name IN (
  'innodb_flush_log_at_trx_commit',
  'sync_binlog',
  'innodb_log_file_size'
);

参数	值	含义
innodb_flush_log_at_trx_commit=1	每次提交都刷 redo 到磁盘	最安全，但写的压力最大
sync_binlog=1	每次事务都同步 binlog	同上，确保 binlog 也落盘

双 1 配置优先保证数据安全——即使 MySQL 或机器突然宕机，已提交的事务也不会丢。写入量很大的场景（每秒几千事务以上），双 1 会对磁盘 IO 造成较大压力，可能需要高 IOPS 的磁盘来支撑。日志类、缓存类等对数据一致性要求稍低的库，才考虑按风险评估调低这两个参数。

innodb_log_file_size 决定了 redo log 文件的大小。太小 → 循环写频繁触发刷脏页 → 磁盘 IO 高；太大 → 崩溃恢复时需要扫描的 redo 更多 → 恢复时间长。

七、连接数和连接堆积

连接数相关参数：

SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';

看当前连接分布：

SELECT user, host, db, command, time, state
FROM information_schema.processlist
ORDER BY time DESC
LIMIT 20;

连接数打满不一定是"连接数设太小"，更常见的原因是：

现象	可能是什么问题
大量 Sleep	应用连接池空闲连接太多，开了但不用
大量 Query 且执行时间长	慢 SQL 堆积，新进来的请求也排队
State 里出现 Waiting for ... lock	锁等待，不是 SQL 慢是有人在阻塞
某个 host 连接暴涨	可能应用重试风暴、刚重启、或者连接泄漏

max_connections 调大只能让更多请求排队，不解决慢 SQL 和锁等待。连接释放不掉的根因不去掉，更大的连接数只是把数据库从"拒绝连接"推到"CPU/IO 被更多请求打满"。

八、临时处理 vs 后续根治

接口开始超时、慢日志里同一类 SQL 反复出现时，现场的临时处理和后续的根治要分开想：

临时处理（缓解症状）：

操作	适用情况	风险
补索引	WHERE/ORDER BY 模式固定	会造成一段时间内表被锁、写入变慢
更新统计信息	执行计划明显选错了索引	高峰期可能带来抖动
调大 Buffer Pool	命中率低且内存确实有余量	挤压 OS 和其他进程
限流或入口限制	SQL 已经拖垮连接数	业务会报错，比数据库完全不可用强
Kill 阻塞源	确认是异常长事务阻塞了大量请求	回滚耗时，业务可能重试

后续根治（回到 SQL 或代码里改）：

问题 SQL 形态	改进方向
SELECT * 取了大字段	明确字段，只取必要的列
深分页 LIMIT 100000,20	游标方式，基于上一页最大 ID
WHERE DATE(created_at)	改成范围条件
%keyword% 模糊搜索	用 Elasticsearch 等搜索引擎
单事务更新几十万行	拆批 + 控制提交间隔

九、性能排查的基本顺序

MySQL 出问题时，不是先调参数，是先定位瓶颈在哪：

步骤	看什么	命令
1. 系统资源	CPU、内存、磁盘 IO	top、iostat -x 1、free -h
2. 连接状态	有没有堆积、卡在什么状态	SHOW FULL PROCESSLIST
3. 慢 SQL	什么 SQL 执行时间长、扫描了多少行	slow log、sys.statement_analysis
4. 锁等待	谁在阻塞谁	innodb_trx、innodb_lock_waits
5. 缓冲命中	是不是物理读太多	SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'
6. 错误日志	有没有异常信息	tail error.log

磁盘 IO 这块容易忽略——MySQL 查询慢的时候，可能不是 SQL 的问题，是磁盘本身延迟高：

iostat -x 1

重点看 %util（利用率）和 await（平均等待时间）。如果 await 几十上百毫秒，SQL 跑着快不了。

MySQL 8.0 自带的 sys schema 能快速看一些汇总（需要 performance_schema 开启）：

-- 按总耗时排序的 SQL
SELECT * FROM sys.statement_analysis
ORDER BY total_latency DESC LIMIT 10\G

-- 全表扫描的 SQL
SELECT * FROM sys.statements_with_full_table_scans
LIMIT 10\G

-- 表级别的 IO 统计
SELECT * FROM sys.schema_table_statistics
ORDER BY total_latency DESC LIMIT 10\G

查完后要能落到具体动作——磁盘 await 高降写入、慢 SQL 扫太多补索引、锁等待 Kill 阻塞源、连接耗尽限流——每一个"数据库慢"的判断后面，都要跟一个具体的原因和处理方向。