MySQL 8.0.46 故障场景与源码

1. lock_t
    记录「谁（trx_id）持有了哪个索引、哪一页、行位图上的哪一把锁」。
2. 右侧 4×4 锁兼容矩阵：同一行上 X 与 S 互斥，IX / IS 仅与表级意向锁兼容。
3. 下方四种精确模式（REC_NOT_GAP / GAP / ORDINARY / INSERT_INTENTION）决定锁覆盖的是记录、间隙、二者还是插入意图。
4. 这是死锁与间隙锁冲突的判定依据。

1. RR
    隔离级别下加的是 next-key 锁（记录 + 前间隙），图中红色区间禁止插入——这是「范围 UPDATE / DELETE 踩死锁、并发插入被堵」的根源。
2. RC
    下退化成只锁记录本身，间隙不锁。
3. 线上多数读多写少业务可用 RC 规避大量间隙冲突。遇到唯一索引也会有间隙锁。

4. RC ，遇到唯一键冲突时，也会有S Next-Key Lock 此为gap锁, 会阻塞插入意向锁 insert intention lock。

5. RC，Insert 更新遇到唯一索引时，会产生S gap ，gap锁为范围锁，扩大了锁定范围，会增加死锁概率。

6. RC，唯一键如果插入相同数据，语句会被阻塞，语句锁超退出之后，会获得[前数据，插入数据]和[插入数据，后数据]2个范围的S Gap，阻塞Gap之间的插入操作。

7. 为了唯一键插入的原子性（防止其他事务插入相同数据），unique check 的时候会给所有的相同的record 和下一个record 加上next-key lock. 导致后续insert record 虽然没有冲突, 但是还是会被Block 住, 进而有可能造成死锁的问题.[对于insert操作来说，若发生唯一约束冲突，则需要对冲突的唯一索引加next-key共享读锁。而且还需要对该唯一索引的下一条记录也加next-key共享读锁。]

8. 插入意向锁的属性：不会阻塞其他任何锁，只会被gap lock阻塞。

9. 对于唯一键不要插入相同数据，因为会放大锁的范围。

锁名称解释：

• Record lock/lock_mode X locks rec but not gap  都是行锁

• lock mode S/lock mode X 都是gap锁

• Next-Key Lock （Record lock+gap lock）行锁+gap锁

1. 事务改动先写入 log buffer（内存缓冲区），此时尚未落盘。
2. log_writer
    线程把 log buffer 刷到环形 redo 日志文件。
3. 事务提交时，log_flusher 按 innodb_flush_log_at_trx_commit 决定落盘时机。
4. =1
    每次提交都 fsync 到磁盘（最安全、最慢）；=2 只写 OS 缓存；=0 每秒刷一次。
5. 双 1 配置
   （redo=1 + sync_binlog=1）保证崩溃不丢数据，但写吞吐下降，需配合足够大的 redo 容量。

1. 每行改动在 undo 里挂一条版本记录，靠 ROLL_PTR 串成版本链。
2. ReadView
    携带创建者、活跃事务上限等字段，决定当前事务能看到哪条版本。
3. RR
    在事务开始时建一次视图（可重复读），RC每条语句重建（读已提交）。
4. 未提交事务
   的 undo 不能被 purge 回收，是大事务 IO 飙升的根源（见故障三）。

1. 二级索引
   叶子只存「索引列 + 主键值」，查询非索引列要回表到聚簇索引（图中虚线为随机 IO）。
2. 当优化器估算回表代价大于全表扫描时，就会放弃索引（故障四根源）。
3. 覆盖索引
   （索引含所有查询列）能消除回表，是最直接的优化手段。

图7 · 死锁：wait-for 成环 → DeadlockChecker 选 victim 回滚并抛 1213

-- 1) 开启全局死锁日志 

SET GLOBAL innodb_print_all_deadlocks = ON;  

-- 2) 查看最近一次死锁（关注 LATEST DETECTED DEADLOCK） 

SHOW ENGINE INNODB STATUS\G  

-- 3) 实时锁等待（MySQL 8.0 推荐 performance_schema） 

SELECT * FROM performance_schema.data_lock_waits\G SELECT * FROM performance_schema.data_locks\G  

-- 4) 定位未提交/长事务 

SELECT trx_id, trx_state, trx_started, trx_weight, trx_query FROM information_schema.innodb_trx ORDER BY trx_started ASC\G

-- 1) 在线调大合理等待阈值（仅减少正常长事务被误杀，不解决死锁） 

SET GLOBAL innodb_lock_wait_timeout = 60;  

-- 2) 开启全量死锁日志（定位冲突 SQL 顺序，性能影响可忽略） 

SET PERSIST innodb_print_all_deadlocks = ON;  

-- 3) 应用层伪代码：捕获 1213 后指数退避重试 

-- try { execute(tx); } catch (DeadlockException e) { 

--     if (retryCount < 3) sleep((1<<retryCount)*50ms); retry(); -- } 

-- 4) 业务约定：所有「订单+库存」写操作统一为 先订单(id ASC) → 后库存(id ASC)

图8 · 连接生命周期与 THD 状态机：半开连接如何拖垮连接数

-- 1) 连接状态分布 

SHOW PROCESSLIST; 

SELECT COMMAND, COUNT(*) FROM information_schema.processlist GROUP BY COMMAND;  

-- 2) 超时参数（默认 28800s = 8h，过长） 

SHOW VARIABLES LIKE 'wait_timeout'; 

SHOW VARIABLES LIKE 'interactive_timeout';  

-- 3) 连接水位 

SHOW STATUS LIKE 'Threads_connected'; SHOW STATUS LIKE 'Max_used_connections';  

-- 4) 紧急回收超长 Sleep（确认来源后再 KILL） 

SELECT CONCAT('KILL ', id, ';') AS kill_sql FROM information_schema.processlist WHERE COMMAND='Sleep' AND TIME > 1800;

-- 1) 缩短闲置超时，快速回收半开/僵死连接（在线 + 写入 my.cnf） 

SET PERSIST wait_timeout = 600; 

SET PERSIST interactive_timeout = 600;  

-- 2) 提高连接水位冗余（按峰值*1.3 估算，写入 my.cnf） 

-- max_connections = 800  

-- 3) 业务连接池（以 HikariCP 为例，application.yml / properties）

-- maximumPoolSize: 50 

-- minimumIdle: 5 

-- idleTimeout: 300000        # 5min 无活动回收 

-- maxLifetime: 1800000       # 30min 强制换新，避免服务端已超时 -- connectionTestQuery: SELECT 1 

-- keepaliveTime: 120000      # 探活

图9 · 大事务 undo 堆积与 purge 滞后：单事务 vs 拆批提交对比

-- 1) 定位执行中的长事务（按开始时间排序） 

SELECT trx_id, trx_state, trx_started, trx_weight,        trx_rows_modified, trx_query FROM information_schema.innodb_trx ORDER BY trx_started ASC\G  

-- 2) 观察 undo 堆积（关注 History list length） 

SHOW ENGINE INNODB STATUS\G  

-- 3) 锁等待实时视图 

SELECT * FROM performance_schema.data_lock_waits\G SELECT waiting_trx_id, blocking_trx_id, wait_age FROM sys.innodb_lock_waits\G

-- 1) 提升 purge 并发与限流（写入 my.cnf） 

-- innodb_purge_threads = 8 

-- innodb_purge_batch_size = 1000 

-- innodb_max_purge_lag = 100000        

# 超过则限流写入，防止 history list 失控  

-- 2) 在线开启自动统计更新，避免大批量后统计失真 

SET GLOBAL innodb_stats_auto_recalc = ON;  

-- 3) 大批量操作的推荐分批模板（应用层循环执行） 

-- WHILE 有数据: 

--   UPDATE ... WHERE id BETWEEN ? AND ? LIMIT 2000;  -- 按主键切片 

--   COMMIT;  -- 每批独立提交，立即释放 undo 与锁 

--   SLEEP(0.2);  -- 错峰，给 purge 喘息

图10 · 优化器代价模型：为何「有索引却走全表扫描」

-- 1) 执行计划（关注 key / type） 

EXPLAIN SELECT ... ;  

-- 2) 索引基数（Cardinality 偏低=选择性差） 

SHOW INDEX FROM `table_name`;  

-- 3) 优化器追踪代价比较 

SET optimizer_trace='enabled=on'; 

SELECT ... ; 

SELECT * FROM information_schema.optimizer_trace\G 

SET optimizer_trace='enabled=off';  

-- 4) 刷新统计信息后复测 

ANALYZE TABLE `table_name`;

-- 1) 刷新统计信息（运维手动触发，或开启自动） 

ANALYZE TABLE order_item; 

SET GLOBAL innodb_stats_auto_recalc = ON;  

-- 2) 加大持久化统计采样页，让 Cardinality 更准（写入 my.cnf） 

-- innodb_stats_persistent_sample_pages = 200  

-- 3) 临时止血：核心慢查强制走正确索引（长期仍需重构索引） 

-- SELECT ... FROM order_item FORCE INDEX(idx_cover) WHERE ...  

-- 4) 调整 IN() 代价估算精度（默认 200，超过改 equality dive 估算） -- eq_range_index_dive_limit = 200  

-- 5) 推荐重建为覆盖索引，消除回表随机 IO 

-- ALTER TABLE order_item ADD INDEX idx_cover (status, create_time, order_id, amount);

图11 · 主从复制全链路：binlog → relay log → coordinator → 并行 worker

-- 1) 8.0 复制状态（REPLICA 体系，非 SLAVE） 

SHOW REPLICA STATUS\G 

-- 关注：Seconds_Behind_Source、Replica_SQL_Running_State、 --       Relay_Log_Space、Last_SQL_Error  

-- 2) 主库 binlog 位点 

SHOW BINARY LOG STATUS; 

SHOW MASTER STATUS;        

-- 兼容写法  

-- 3) 从库 worker 回放状态 

SELECT * FROM performance_schema.replication_applier_status_by_worker\G

-- 1) 主库：开启 WRITESET 依赖追踪（写入 my.cnf，需重启生效） 

-- binlog_transaction_dependency_tracking = WRITESET 

-- transaction_write_set_extraction = XXHASH64  

-- 2) 从库：LOGICAL_CLOCK 并行 + worker 数（在线设置） 

STOP REPLICA SQL_THREAD; 

SET GLOBAL replica_parallel_type = LOGICAL_CLOCK; 

SET GLOBAL replica_parallel_workers = 8; 

START REPLICA SQL_THREAD;  

-- 3) 从库只读，避免长查询阻塞 applier（写入 my.cnf） 

-- super_read_only = ON  

-- 4) 监控延迟告警 

-- 关注 Seconds_Behind_Source，>30s 即告警

图12 · 排序与临时表：内存阈值、落盘流程与 filesort 归并代价

-- 1) 执行计划（关注 Extra） 

EXPLAIN SELECT ... ;  

-- 2) 临时表落盘次数 

SHOW STATUS LIKE 'Created_tmp_disk_tables'; SHOW STATUS LIKE 'Created_tmp_tables'; SHOW STATUS LIKE 'Sort_merge_passes';  

-- 3) 用 digest 找出最耗临时表/排序的 SQL 

SELECT DIGEST_TEXT, COUNT_STAR,        SUM_CREATED_TMP_DISK_TABLES, SUM_SORT_MERGE_PASSES FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC LIMIT 10;

-- 1) 调大内存临时表上限（两者取小值生效，写入 my.cnf） 

-- tmp_table_size = 256M 

-- max_heap_table_size = 256M  

-- 2) 内部临时表优先用内存引擎（8.0 默认 TempTable，必要时调大其内存池） 

-- internal_tmp_mem_storage_engine = TempTable 

-- temptable_max_ram = 1G  

-- 3) 推荐：为 ORDER BY / GROUP BY 建联合索引，消除 filesort -- ALTER TABLE ticket ADD INDEX idx_owner_upd (owner, update_time);  

-- 4) 延迟关联：先取主键再回表，缩小排序行宽 

-- SELECT t.* FROM ticket t JOIN ( 

  SELECT id FROM ticket WHERE owner=123 ORDER BY update_time DESC LIMIT 20 

) x USING(id);