InnoDB 与性能调优

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

索引结构、回表和 EXPLAIN 判断方式见 索引与执行计划。InnoDB 调优经常要看执行计划,否则容易把 SQL 访问路径问题误判成参数问题——比如接口超时后看到 Buffer Pool 命中率下降,但 EXPLAIN 里 type=ALL、rows=5000000,基本能先判断是 SQL 在扫大表,不是单纯调大 Buffer Pool 就能解决。

一、核心对象

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 后大事务回滚也需要时间,不会瞬间释放锁——别以为 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'
);

双 1 配置(innodb_flush_log_at_trx_commit=1 和 sync_binlog=1)优先保证数据安全——即使 MySQL 或机器突然宕机,已提交的事务也不会丢。innodb_flush_log_at_trx_commit=1 是每次提交都刷 redo 到磁盘(最安全但写的压力最大),sync_binlog=1 是每次事务都同步 binlog(确保 binlog 也落盘)。

写入量很大的场景(每秒几千事务以上),双 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 被更多请求打满"——问题没解决,反而更糟。

八、临时处理与根治

接口开始超时、慢日志里同一类 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 跑着快不了——这时候优化 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 阻塞源、连接耗尽限流。每一个"数据库慢"的判断后面,都要跟一个具体的原因和处理方向,不然排查就停留在"看着像没问题"的层面。