MYSQL 临时表 - 如何查看活动表

Question

我们公司有一个简单的“类crm”软件。一些复杂的查询需要花费很长时间，这些查询每天都会使用。因此，我正在测试一些修改，使用临时表来替换我们需要的所有复杂联接和子查询。

到目前为止，进展非常顺利，速度提高了90%以上。

由于它是一个Web应用程序（CodeIgniter + MySQL），我计划将其放在“生产测试”环境中，以便50%的用户可以帮助我测试。我想监视活动的表，如果可能的话，还要监视每个连接。

我的问题是 - 有没有办法查看MySQL实例中所有处于活动状态的TEMPORARY TABLES？也许查看它的数据？

我读到了一些关于PLUGIN或类似东西的文章，但是那篇文章太混乱了，我无法理解。

非常感谢，抱歉我的英语不是我的母语。

- ddutra

---

适用于系统管理员和桌面支持专业人员的问答社区，应发布在http://www.serverfault.com上。 - Mchl

Answer 1

临时表 = temporary，它在查询之后会被删除。

除了记录所有的查询并逐个执行以检查哪个查询需要tmp_table之外，没有直接的解决方案。

系统变量如Created_tmp_tables可能会提供一些思路。

mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+

- ajreal

Answer 2

临时表的一个问题是跟踪使用情况，还有创建、索引和删除它们的开销 - 特别是在 Web 应用程序中，临时表的生命周期通常与 HTTP 请求的生命周期一样长。

对于预编译结果（即物化视图），我设置了一个传统的表，添加了字段，这些字段引用 MySQL 连接 ID / Web 会话 ID / 源查询 + 生成时间，具体取决于数据的 TTL 和是否共享。

除了获取表的详细使用情况跟踪外，这使得更容易进行查询调优，当然，模式也更好地记录下来了。

- symcbean

Answer 3

MySQL INNODB从v5.7提供了一些关于临时表的信息，可以通过INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INF来获取，但它只显示服务信息，没有关于表结构或名称的详细信息。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;
+----------+--------------+--------+------------+
| TABLE_ID | NAME         | N_COLS | SPACE      |
+----------+--------------+--------+------------+
|     1066 | #sql569_1c_4 |      4 | 4243767290 |
+----------+--------------+--------+------------+
1 row in set (0.00 sec)

mysql> 

- Nick Ovchinnikov

Answer 4

当你的数据集变得更大时，临时表就无法帮助你了。最终，它们如何帮助呢？在计算结果后，数据必须被复制到这些表中，为什么不使用memcached缓存结果呢？

此外，我见过一些相当大（数十GB）并且在单台机器上运行的数据库，查询速度非常快。问题在于你是否正确配置了数据库服务器（软件和硬件）。你可能正在经历暂时的加速，但不能保证它会永久有效。我会优化应用程序、查询、所需的软件和硬件来运行应用程序，然后使用memcache缓存结果，除非你需要查询结果的最新热备份。

- N.B.

---

不同意。当您需要从许多表中更新数据时，临时表对于复杂查询非常有用。 - M H

@michaelhanon，你的反对意见并没有提到或引用任何事实。这只是你个人的观点。你可以持不同意见，但这并不意味着你是正确的。 - N.B.

Answer 5

我认为在视图临时表中没有意义，因为它们只在连接期间存在，关闭连接后就会消失。但是关于临时表有一个重要的事实。如果您在脚本中使用持久连接，则临时表将在此连接的生命周期内存在，并且使用此连接的客户端将可以访问相同的临时表。此时，临时表将占用系统资源。为了避免这种情况，您应该在使用此临时表的必要查询运行后手动删除临时表。

- Andrej