记录一次truncate导致MySQL夯住的故障

目录

环境信息：   

故障描述：   

处理过程：

原理分析：

    show processlist结果中的system lock含义：

     truncate原理：

1. TRUNCATE 的执行流程

2、TRUNCATE 表导致数据库夯住的原因

3、 TRUNCATE 表导致数据库夯住的解决方案

4、 kill TRUNCATE 语句失败后，主从数据不一致的原因：

5、为什么TRUNCATE TABLE users 会影响其他表的SQL

6、为什么 KILL 语句无法立刻终止 TRUNCATE

TRUNCATE 与 DELETE 的区别

环境信息：   

    mysql 5.7一主两从的架构。

故障描述：   

    truncate一张3000万行的大表，数据量约为45G，同时库中执行很多其他dml和select for update，主库夯死， 应用反馈没法打开，下游的接口不能查询，数据库活跃会话数在80左右。

    通过information_schema查询事务、锁、等待的SQL被阻塞住，迟迟没有结果。通过show processlist结果显示，数据库中truncate的SQL处于system lock状态，对其他表的查询、dml的SQL部分处于Opening tables状态以及部分在正常执行。期间从information_schema.innodb_locks和information_schema.innodb_lock_waits两个视图，偶尔成功，但均没有查询到任何锁等待和锁信息。

处理过程：

    1、通过show processlist拉取truncate会话信息，kill truncate语句后，通过show processlist发现truncate语句处于killed状态，没有被清理干净。查询information_schema.innodb_trx视图发现truncate语句仍然在执行。

    2、推测是有元数据锁等待，通过show processlist拉取所有应用会话，kill所有应用会话，发现数据库中的所有会话均为killed状态，数据库仍然夯住。

    3、断开一个从库的同步做数据保护，停止应用，通过stop命令重启数据库实例失败，最终通过kill方式关闭数据库，重启数据库实例后库中所有应用会话均被清理干净，主库恢复正常，应用打开，开始恢复对外服务。

    4、数据库恢复后，检查truncate过的表，主库中该表数据行数为0，所有数据均被清理干净。而未断开同步的从库该表数据仍然为三千多万行，此时可以确认主从数据不一致。

    5、经应用校验，主库数据无问题，最终以主库数据为准，重做两台从库的备机。

本次故障原因分析：

原理分析：

    show processlist结果中的system lock含义：

• system lock 表示 TRUNCATE TABLE 正在等待释放相关资源（如 MDL 锁）。

• 由于需要truncate的表可能有 活跃事务 在使用，导致 TRUNCATE 无法立即获取独占 MDL 锁，从而进入 system lock 状态。

     truncate原理：

     RUNCATE TABLE 是 DDL（数据定义语言） 语句，而不是普通的 DELETE 语句。它在 MySQL 内部的执行方式 和 DELETE 不同，主要依赖 表元数据重置 及 物理数据页回收，因此执行速度更快，同时影响锁机制、事务处理和 Binlog 记录方式。

    MDL 锁不会自动超时，如果 TRUNCATE TABLE 语句 等待其他事务结束的时间过长，大量 SQL 都会排队等待，导致数据库响应变慢甚至无法对外提供服务。

1. TRUNCATE 的执行流程

当执行 TRUNCATE TABLE table_name; 时，MySQL 主要做了以下几步：

（1）获取表的元数据锁（MDL）

• TRUNCATE TABLE 是 DDL 语句，执行时需要 获取 MDL EXCLUSIVE（排他元数据锁），以防止其他并发操作（如 SELECT、INSERT、UPDATE）。

• 如果有并发事务或查询未提交，TRUNCATE 可能会被 阻塞，一直等到其他事务释放锁。

（2）重建表（适用于 InnoDB 引擎）

在 InnoDB 存储引擎下，TRUNCATE 不会逐行删除数据，而是 重建表结构：

1. 删除原表的表空间文件（.ibd）：

   • TRUNCATE 直接 删除 users.ibd 文件（如果 innodb_file_per_table=ON）。

   • 释放所有数据页，表的行数归零。

2. 重新创建一个空的新表：

   • 生成一个新的 ibd 文件，表的 AUTO_INCREMENT 计数器也会被重置（除非 innodb_autoinc_persistent=ON）。

3. 更新数据字典：

   • InnoDB 更新 information_schema 的数据字典，重新分配新的表 ID。

   注意：

• TRUNCATE 不能回滚，因为它直接删除表空间文件，而不像 DELETE 那样记录事务日志。

• AUTO_INCREMENT 计数器被重置，除非 innodb_autoinc_persistent=ON。

（3）删除 & 重新创建表的相关统计信息

• MySQL 重新计算表的统计信息，并 清空 innodb_buffer_pool 缓存 中的该表相关数据。

• 这可能会导致 TRUNCATE 后的第一次查询变慢，因为 需要重新生成索引统计信息。

2、TRUNCATE 表导致数据库夯住的原因

    truncate语句需要获取排他元数据锁，dml语句也需要获取共享元数据锁。

可能的原因：

• 有长事务未提交，占用了 MDL 锁。

• 有其他 DDL 语句在执行，与 TRUNCATE 发生冲突。

• Binlog 复制未完成，导致 TRUNCATE 进入 system lock。

 3、 TRUNCATE 表导致数据库夯住的解决方案

   1、通过FLUSH TABLES tablename; 命令先释放 table_cache 资源。

   2、通过SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_NAME = 'tablename';检查对应表是否有元数据锁。

   3、确认没有元数据锁后再执行 TRUNCATE

   4、如果释放表元数据锁资源后执行truncate数据库还是夯住，kill该会话

   5、如果kill会话不生效，重启数据库实例

 4、 kill TRUNCATE 语句失败后，主从数据不一致的原因：

    主库 TRUNCATE 执行过程中被 kill，该truncate语句不会被回滚，只能继续执行完成，但因为kill语句，Binlog 未写入完整，从库未同步truncate语句，从中相应表并未清理。

  5、为什么TRUNCATE TABLE users 会影响其他表的SQL

    虽然 TRUNCATE TABLE users; 只影响 users 表，但由于 MDL 锁争用 和 InnoDB 内部机制，会间接影响 其他表上的操作。

1.1. TRUNCATE TABLE 触发 MDL EXCLUSIVE 锁

• MDL（Metadata Lock）

  • TRUNCATE TABLE users; 需要获取 users 表的 MDL EXCLUSIVE 锁。

  • 该锁会等到 所有涉及 users 表的事务结束后 才能执行。

• 问题：

  • 如果有未提交的事务在访问 users 表（如 SELECT * FROM users），那么 TRUNCATE 会被阻塞，无法立即执行。

  • 这会导致新到来的 SELECT users、INSERT users、UPDATE users 排队等待，造成 Opening tables 状态。

1.2. TRUNCATE TABLE 触发 flush & purge 影响 InnoDB

• TRUNCATE 不是简单的 DELETE，它会触发 表重建：

  • TRUNCATE TABLE users; 重新创建 users.ibd 文件。

  • 触发 InnoDB 的 flush & purge 机制，可能导致 InnoDB 短暂冻结（stalling）。

• 问题：

  • 如果 flush & purge 耗时较长，那么 整个 InnoDB 层会短暂变慢，导致 其他表的 SQL 也可能变慢或进入 Opening tables 状态。

1.3. Opening tables 可能与 table_cache 相关

• MySQL 需要在 table_cache 中找到表的描述信息（.frm、.ibd 等）：

  • 如果 某个线程持有 MDL EXCLUSIVE 锁（如 TRUNCATE users），而 table_cache 需要访问 users 的元数据，就可能导致等待。

• 问题：

  • 由于 table_cache 竞争，其他表的 SQL 也可能进入 Opening tables 状态。

  • 但 未受影响的表（未被 table_cache 竞争影响的表）仍能正常运行，所以只有部分 SQL 卡住，部分仍可执行。

6、为什么 KILL 语句无法立刻终止 TRUNCATE

（1）DDL 操作不受事务管理

• TRUNCATE TABLE 不会记录 undo log，所以它 无法回滚。

• 一旦 TRUNCATE 语句开始执行，它会 删除表数据并重建表空间，这部分操作不能简单通过 KILL 终止并回滚。

• 即使 KILL 了会话，MySQL 仍需要等待 TRUNCATE 彻底完成，以保证表的完整性。

（2）DDL 操作涉及元数据变更

• TRUNCATE 需要获取 元数据锁（MDL EXCLUSIVE），以阻止其他会话对表进行并发访问。

• TRUNCATE 的执行流程通常如下：

  1. 获取 MDL 排他锁，阻止其他事务访问该表。

  2. 删除表的 .ibd 文件（如果 innodb_file_per_table=ON）。

  3. 重新创建一个新的空表。

  4. 释放 MDL 锁。

• 如果在执行 TRUNCATE 期间 KILL 进程，MySQL 需要等待所有涉及的元数据修改完成，否则可能导致表结构损坏。

TRUNCATE 与 DELETE 的区别

 总结：

• 如果你想快速清空表数据，且不需要事务回滚，TRUNCATE 是更好的选择。

• 如果只想删除部分数据，或者希望事务支持，DELETE 更合适。