MySQL查找未使用的表

Question

我正在处理一个数据库，里面有很多未使用的表格，我正在清理它。我正在尝试找到一种方法，可以让我100%确定某个表格不再被使用。

经过一些谷歌搜索，我仍然找不到一个好的方法。我只能使用以下命令告诉最后的写入表格（INSERT、UPDATE等）：

SHOW TABLE STATUS 或在mysql数据目录上运行ls -lt（可以通过运行SHOW VARIABLES LIKE 'datadir';来找到该目录）

你有其他建议吗？

谢谢。

- Jean Paul Galea

---

我认为唯一百分之百确定的方法是解决应用程序的所有表依赖关系。即使一个表在过去三周内没有被查询，它仍然可能偶尔被应用程序需要，不是吗？ - Pekka

SHOW TABLE STATUS不能告诉你想知道的所有信息。当然，如果你看到一个表在最近一小时被修改过，你并不知道它是从哪里被修改的，但你知道这个表正在被使用。如果它已经一年没有被修改过了，那么这就是表没有被使用的很好的证明。 - VoteyDisciple

@Pekka - 我在代码库上运行了grep，但仍然可能有脚本在使用这些表。 - Jean Paul Galea

@VoteyDiscip - 不幸的是，它并不适用于所有引擎，比如InnoDB。 - Jean Paul Galea

Answer 1

我知道这是一个老问题，但似乎没有一个合适的答案，我通过自己的搜索找到了这里。根据Mark Leith关于未使用表和索引的博客文章，应该能够像这样做：

SELECT
    t.*
FROM performance_schema.table_io_waits_summary_by_table t
WHERE
    t.COUNT_STAR = 0
    AND t.OBJECT_SCHEMA = '<your-schema-name-goes-here>'
    AND t.OBJECT_TYPE = 'TABLE';

MySQL官方文档提供了有关此主题的更多详细信息。

当然，这需要您启用性能模式，并且统计数据没有被清除或截断了一段时间。

- mrdr

Answer 2

尝试使用 INFORMATION_SCHEMA.TABLES。其中有一列名为 UPDATE_TIME。检查该字段中的日期。如果是 NULL，则自表格创建以来从未更新过。

例如：未在最近10天内更新的表格列表。

SELECT table_schema, table_name, create_time, update_time
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql')
  AND engine IS NOT NULL
  AND ((update_time < (now() - INTERVAL 10 DAY)) OR update_time IS NULL);

来试试看吧!!!

- RolandoMySQLDBA

---

不适用于InnoDB，请参见http://dev.mysql.com/doc/refman/5.5/en/show-table-status.html （show table status使用information_schema.tables）。 - KCD

@KCD，你对InnoDB的说法是正确的。如果你启用了innodb_file_per_table，你需要从操作系统中检查.ibd文件的时间戳。 - RolandoMySQLDBA

考虑到自MySQL 5.5版本以来InnoDB是默认的存储引擎，因此这个答案有些误导性。 - s1d

Answer 3

尝试这个http://forums.mysql.com/read.php?20,298759,299185#msg-299185

打开“常规日志”并扫描它（以编程方式！）可以告诉您在日志打开期间谁正在访问哪些表。注意：该文件增长速度很快。

- Yasen Zhelev

Answer 4

我很惊讶MySQL在识别未使用的表方面支持如此之少。正如在这个问题的评论中所指出的，至少有一种有前途的方法被证明可以用于MyISAM表，但不能用于InnoDB表。

然而，我可能已经找到了一种可靠的方法来识别未使用的InnoDB表，至少在MySQL 5.7.26版本中是这样的。这种方法利用了MySQL的file_summary_by_instance表，该表汇总了有关文件名的I/O操作信息。

登录mysql并执行以下操作：

mysql> SELECT * FROM performance_schema.file_summary_by_instance\G

这将打印所有数据库中所有文件的诸如COUNT_READ之类的值。您可以通过各种方式缩小搜索范围。例如，要显示数据库文件/var/lib/mysql/production/user.ibd的所有值，

mysql> SELECT * FROM performance_schema.file_summary_by_instance where FILE_NAME = '/var/lib/mysql/production/user.ibd'\G

你也可以只选择你感兴趣的值，例如：

mysql> SELECT FILE_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_MISC FROM performance_schema.file_summary_by_instance where FILE_NAME = '/var/lib/mysql/production/user.ibd'\G

以上的查询可以让您监视 MySQL 实例使用的所有文件中各种类型的使用计数。但是，如果要确定某个文件是否正在使用，您可能想要重置这些计数。幸运的是， TRUNCATE TABLE 可用于文件 I/O 摘要表。它将摘要列重置为零，而不是删除行。 因此，您可以，

mysql> truncate table file_summary_by_instance;

然后观察哪些文件的计数会增加。

注意：由于某些表可能只在应用程序启动时被读取，您可能需要重新启动应用程序（甚至服务器）并查看是否增加了访问计数。

有关file_summary_by_instance表的更多详细信息，请参见https://dev.mysql.com/doc/refman/5.7/en/file-summary-tables.html

- CODE-REaD

Answer 5

嗯，这个问题早就被问过了，但我希望这个答案能够帮助到未来的某个人。

SHOW TABLE STATUS where `Rows` = 0;

- Meyanbe Olorunfemi