MySQL 5.7怎么爬出临时表空间的坑

导读

如何确认临时表是由哪个用户连接创建的？

上次我们介绍了，这次我们来介绍如何确认是哪个用户连接创建的临时表，以及如何释放临时表。

首先，我们查看当前的连接ID：

yejr@imysql.com>SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| ? ? ? ? ? ?2470 |
+-----------------+

在当前会话中创建临时表：

yejr@imysql.com [test]>create temporary table tmp1
 ?select * from I_S.global_status;

备注：上面的I_S是information_schema的简写，下同。

立即查看临时表信息：

yejr@imysql.com [test]>select TABLE_ID, NAME from
 I_S.innodb_temp_table_info;
+----------+-----------------------+
| TABLE_ID | NAME ? ? ? ? ? ? ? ? ?|
+----------+-----------------------+
| ? ? ?505 | #sql17ab5_4000003a6_4 |
+----------+-----------------------+

我们观察到NAME列的值是#sql17ab5_4000003a6_4，它由3部分构成：

第1部分，由“#sql”字符串开始，并加上随机值；

第2部分，一串”疑似”16进制字符；

第3部分，单调递增的数值；

这其中的第2部分，我们注意到是“疑似”16进制，我们把“3a6“从16进制转成10进制试试看：

yejr@imysql.com [test]>select conv('3a6', 16, 10);
+---------------------+
| conv('3a6', 16, 10) |
+---------------------+
| 934 ?? ? ? ? ? ? ? ?|
+---------------------+

可以看到，正好和当前的连接ID是一样的，这证实了我们的设想。

我手上有两个MySQL 5.7版本，下面是多次、不定时创建临时表的整个观察过程记录。

首先是Linux系统下的5.7.18版本：

Server version: ? ?5.7.18-log MySQL Community Server (GPL)
yejr@imysql.com[test]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| ? ? ? ? ? ?1737 |
+-----------------+
yejr@imysql.com[test]> select conv(1737, 10 ,16);
+--------------------+
| conv(1737, 10 ,16) |
+--------------------+
| 6C9 ? ? ? ? ? ? ? ?|
+--------------------+
yejr@imysql.com[test]> select TABLE_ID, NAME from
 I_S.innodb_temp_table_info where NAME like ‘%6C9%’;

+----------+----------------+
| TABLE_ID | NAME ? ? ? ? ? |
+----------+----------------+
| ? ? ?121 | #sql7e95_6c9_5 |
| ? ? ?120 | #sql7e95_6c9_4 |
| ? ? ?119 | #sql7e95_6c9_3 |
| ? ? ?118 | #sql7e95_6c9_2 |
+----------+----------------+

以及Mac系统下的MySQL 5.7.16版本：

Server version: ? ?5.7.16-log MySQL Community Server (GPL)
yejr@imysql.com[test]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| ? ? ? ? ? ?934 ?|
+-----------------+
yejr@imysql.com[test]> select conv(934, 10 ,16);
+--------------------+
| conv(1737, 10 ,16) |
+--------------------+
| 3A6 ? ? ? ? ? ? ? ?|
+--------------------+
yejr@imysql.com[test]> select TABLE_ID, NAME from
 I_S.innodb_temp_table_info where NAME like ‘%3A6%’;
+----------+-------------------------+
| TABLE_ID | NAME ? ? ? ? ? ? ? ? ? ?|
+----------+-------------------------+
| ? ? ?518 | #sql17ab5_31000003a6_31 |
| ? ? ?517 | #sql17ab5_29000003a6_29 |
| ? ? ?516 | #sql17ab5_26000003a6_26 |
| ? ? ?515 | #sql17ab5_23000003a6_23 |
| ? ? ?514 | #sql17ab5_1e000003a6_1e |
| ? ? ?513 | #sql17ab5_1b000003a6_1b |
| ? ? ?512 | #sql17ab5_18000003a6_18 |
| ? ? ?511 | #sql17ab5_16000003a6_16 |
| ? ? ?510 | #sql17ab5_14000003a6_14 |
| ? ? ?509 | #sql17ab5_12000003a6_12 |
| ? ? ?508 | #sql17ab5_10000003a6_10 |
| ? ? ?507 | #sql17ab5_d000003a6_d ? |
| ? ? ?506 | #sql17ab5_a000003a6_a ? |
| ? ? ?505 | #sql17ab5_4000003a6_4 ? |
+----------+-------------------------+

从这个结果能看到临时表的NAME的第三部分数值在两个版本中的表现不一样。

好了mssql 临时表，现在我们知道只要根据当前的用户连接ID，就能找到该会话里创建的所有临时表。想要释放这些临时表，只需要查询 I_S.INNODB_TEMP_TABLE_INFO 表的 NAME 列值所有包含当前用户连接ID的记录，杀掉对应的用户连接ID即可（注意：这会释放该用户连接创建的所有临时表）。

（编辑：开发网_开封站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!