导读
如何确认临时表是由哪个线程创建的?
上次我们介绍了MySQL 5.7临时表空间特性及使用注意事项,这次我们来介绍如何确认是哪个线程创建的临时表,以及如何释放临时表。
首先,我们查看当前的线程ID
yejr@imysql.com [test]>SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 2470 |
+-----------------+
在当前会话中创建临时表
yejr@imysql.com [test]>create temporary table tmp1 select * from information_schema.global_status;
立即查看临时表信息
yejr@imysql.com [test]>select * from information_schema.innodb_temp_table_info;
+----------+-----------------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+-----------------------+--------+-------+----------------------+---------------+
| 505 | #sql17ab5_4000003a6_4 | 5 | 512 | FALSE | FALSE |
+----------+-----------------------+--------+-------+----------------------+---------------+
我们观察到 NAME 列的值是 #sql17ab5_4000003a6_4,它由3部分构成:
- 第1部分,由“#sql”字符串开始,并加上随机值;
- 第2部分,一串”疑似”16进制字符;
- 第3部分,单调递增的数值;
第2部分,我们注意到是“疑似”16进制,我们把“3a6“从16进制转成10进制试试看:
yejr@imysql.com [test]>select conv('9a6', 16, 10);
+---------------------+
| conv('9a6', 16, 10) |
+---------------------+
| 2470 |
+---------------------+
可以看到,正好和当前的线程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 * from information_schema.innodb_temp_table_info where NAME like ‘%6C9%’;;
+----------+----------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+----------------+--------+-------+----------------------+---------------+
| 121 | #sql7e95_6c9_5 | 5 | 190 | FALSE | FALSE |
| 120 | #sql7e95_6c9_4 | 5 | 190 | FALSE | FALSE |
| 119 | #sql7e95_6c9_3 | 5 | 190 | FALSE | FALSE |
| 118 | #sql7e95_6c9_2 | 5 | 190 | FALSE | FALSE |
+----------+----------------+--------+-------+----------------------+---------------+
以及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 * from information_schema.innodb_temp_table_info where NAME like ‘%3A6%’;;
+----------+-------------------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+-------------------------+--------+-------+----------------------+---------------+
| 518 | #sql17ab5_31000003a6_31 | 5 | 512 | FALSE | FALSE |
| 517 | #sql17ab5_29000003a6_29 | 20 | 512 | FALSE | FALSE |
| 516 | #sql17ab5_26000003a6_26 | 5 | 512 | FALSE | FALSE |
| 515 | #sql17ab5_23000003a6_23 | 5 | 512 | FALSE | FALSE |
| 514 | #sql17ab5_1e000003a6_1e | 5 | 512 | FALSE | FALSE |
| 513 | #sql17ab5_1b000003a6_1b | 5 | 512 | FALSE | FALSE |
| 512 | #sql17ab5_18000003a6_18 | 5 | 512 | FALSE | FALSE |
| 511 | #sql17ab5_16000003a6_16 | 5 | 512 | FALSE | FALSE |
| 510 | #sql17ab5_14000003a6_14 | 5 | 512 | FALSE | FALSE |
| 509 | #sql17ab5_12000003a6_12 | 5 | 512 | FALSE | FALSE |
| 508 | #sql17ab5_10000003a6_10 | 5 | 512 | FALSE | FALSE |
| 507 | #sql17ab5_d000003a6_d | 5 | 512 | FALSE | FALSE |
| 506 | #sql17ab5_a000003a6_a | 5 | 512 | FALSE | FALSE |
| 505 | #sql17ab5_4000003a6_4 | 5 | 512 | FALSE | FALSE |
+----------+-------------------------+--------+-------+----------------------+---------------+
从这个结果能看到临时表的 NAME 的第三部分数值在两个版本中的表现不一样。
• 在5.7.16版本上,虽然也是单调递增,但并不是顺序的,而是有跳跃,跳跃规则未知;
• 在5.7.18版本上,在保持单调递增的基础上,每次值都是顺序增长的,未跳跃。
好了,现在我们知道只要根据当前的线程ID,就能找到该线程ID里所创建的临时表,想要释放这些临时表,只需要查询 I_S.INNODB_TEMP_TABLE_INFO 表的 NAME 列值所有包含当前线程ID的记录,杀掉对应的线程ID即可。