閱讀460 返回首頁    go 阿裏雲 go 技術社區[雲棲]

MySQL 5.7怎麼爬出臨時表空間的坑

如何確認臨時表是由哪個用戶連接創建的？

上次我們介紹了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 的第三部分數值在兩個版本中的表現不一樣。

在5.7.16版本上，雖然也是單調遞增，但並不是順序的，而是有跳躍，跳躍規則未知；
在5.7.18版本上，在保持單調遞增的基礎上，每次值都是順序增長的，未跳躍，這個規則看起來更合理些。

好了，現在我們知道隻要根據當前的用戶連接ID，就能找到該會話裏創建的所有臨時表。想要釋放這些臨時表，隻需要查詢 I_S.INNODB_TEMP_TABLE_INFO 表的 NAME 列值所有包含當前用戶連接ID的記錄，殺掉對應的用戶連接ID即可（注意：這會釋放該用戶連接創建的所有臨時表）。

原文發布時間為：2017-09-18
原文作者：周日葉師傅擼北馬
本文來自雲棲社區合作夥伴“老葉茶館”，了解相關信息可以關注“老葉茶館”微信公眾號

最後更新：2017-09-20 12:03:00

  上一篇：go  SAP HANA Express Edition (HXE)-虛擬機模板安裝使用步驟
  下一篇：go  MySQL 8.0.2複製新特性搶鮮看