mysql 垃圾_手工重现Mysql插入的"2.6亿"垃圾数据

# 手工重现Mysql插入的"2.6亿"垃圾数据

> [SHOW PROCESSLIST Syntax](https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html)

> [Examining Thread Information](https://dev.mysql.com/doc/refman/5.7/en/thread-information.html)

本文是昨天 [Mysql插入2.6亿条垃圾数据后会发生什么?](https://chenyongjun.vip/articles/42) 的续篇，将模拟生产环境情况，加深对问题的理解，探索更多解决方式，同时也更多的了解下mysql `kill`命令。

## 重现准备

基于`Mysql 5.7.22`，在一个事务内，利用存储过程往测试表中插入1千万记录，在此过程中进行`kill mysql thread`、`drop table` 等操作。

测试表和过程对应的SQL可见本文最后的附录。

## 开始重现

### 执行存储过程,插入数据

执行存储过程之后，事务中处理的记录数高速增长。


### 对表执行DDL操作

在上述事务 **inserting** 期间，对表执行 `truncate`、`drop`，通过`show processlist`结果如下：

`drop table test`


`truncate table test`


上面两个操作的结果都是: *Waiting for table metadata lock*

> 如果你想了解什么是 Metadata lock，可以查阅官网文档 [Metadata Locking](https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html)

### kill mysql thread

在插入850W记录后，表容量扩展到1G，CPU和内存被`mysqld`基本耗尽.

现在，kill 下图中 mysql thread **12**，事务进入 **ROLLING BACK** 状态。同时，在`PROCESSLIT`中会发现该线程已消失。


在 *rollback* 期间，对表做任何DDL操作其实和 *inserting* 期间是一样的。

### restart mysql

在上述 *rollback* 期间，停止Mysql，再重启。Mysql会继续事务的回滚(ps:其实不用想，这是肯定的，否则无法保障数据一致性需求)

## 理解mysql kill

> 参考 [KILL Syntax](https://dev.mysql.com/doc/refman/5.7/en/kill.html)

### kill的使用

每一个连接到 [mysqld](https://dev.mysql.com/doc/refman/5.7/en/mysqld.html) 的 connection都将使用独立的 *thread* 来运行。可以使用 **kill** 命令来杀死thread.

通过 `show processlist` 查看当前线程，下图中的ID字段即 *mysql thread id*.

该命令与查询 *processlist* 表的效果一样


**kill** 提供了两个选项：

* kill connection：将关闭连接，默认使用该选项。eg: `kill 1` 和 `kill connection 1`效果一样

* kill query：中断当前连接正在执行的SQL，但是连接不会关闭，可以继续使用, eg: `kill query 1`

### kill的原理

使用kill命令处理mysql thread时，将会为这个thread设置一个**kill flag**。在大多数情况下，线程可能需要一些时间才能被killed，因为mysql会以特定的间隔来检查设置了 **kill flag** 的thread并将其kill.

关于kill flag在各种操作期间的检查情况，可以参考 [KILL Syntax](https://dev.mysql.com/doc/refman/5.7/en/kill.html)

## 小结

本文虽然重现了问题的过程，但其实并未解决问题。后续将继续分享以下几个知识点，作为本次事件的续篇。

* kill mysql thread后事务的处理机制？

* InnoDB redo/undo机制以实现事务并保障数据的一致性

* redo/undo buffer是如何耗尽内存的？

* mysql 常见锁的排查流程

## 附录

### 测试表

包含主键和3个普通字段,将使用mysql `uuid() `赋值

```mysql

CREATE TABLE test.test (

id INT(11) NOT NULL AUTO_INCREMENT,

uid CHAR(36) DEFAULT NULL,

uid2 CHAR(36) DEFAULT NULL,

uid3 CHAR(36) DEFAULT NULL,

PRIMARY KEY (id)

)

ENGINE = INNODB;

```

### 测试的存储过程

存储过程运行1千万次，在运行过程中将中断执行。

```mysql

CREATE DEFINER = 'test'@'%'

PROCEDURE test.test()

BEGIN

DECLARE i INT;

START TRANSACTION;

SET i = 1;

REPEAT

INSERT INTO test (uid, uid2, uid3)

VALUES (UUID(), UUID(), UUID());

SET i = i + 1;

UNTIL i >= 10000000

END REPEAT;

COMMIT ;

END

```

扫码或搜索 codercyj 关注微信公众号, 结伴学习, 一起努力