这个 MySQL 问题困扰了我一个月，现在终于把他解决了

柔情只为你懂 2024-05-23 22:07 73阅读 0赞

问题1

首先，姜老师先来问 一个问题： 如何获得 MySQL 数据库中最近5分钟更新过的表 ？

别看这个问题简单，然而大部分同学并不一定能答上来。

给同学们3分钟的思考时间。

时间到！

这个问题的本质是涉及到对于 MySQL 元数据字典表的了解。

关于 MySQL 的元数据字典表有两张，一张是mysql数据库下的表 innodb\_table\_stats，另一张是 information\_schema 数据库下的表 TABLES。

有经验的 DBA 知道元数据字典表 innodb\_table\_stats 是 InnoDB 存储引擎，而元数据字典表 TABLES 在 5.7 版本中是 Memory 引擎。

![efdd6869fa48667b4a477d9ccb2caf1c.jpeg][]

编辑切换为居中

添加图片注释，不超过 140 字（可选）

点击图片可放大

因此，通常查询表 innodb\_table\_stats 的速度会快很多，并且该表也有 last\_update 字段，可用于查询最近5分钟发生修改的表（假设我们只需知道 InnoDB 的表），如：

SELECT \* FROM innodb\_table\_stats WHERE last\_update > DATE\_SUB(NOW(), INTERVAL 5 MINUTES) AND last\_update =< NOW()

然而，很可惜，查询元数据字典表 innodb\_table\_stats 是错的！

很简单，看下面这个简单的例子：

![8d0211e6e2651f63200aba6699e0575e.jpeg][]

编辑切换为居中

添加图片注释，不超过 140 字（可选）

点击图片可放大

可以看到对表 t 进行变更后，元数据字典表中的 last\_update 值并没有发生变化。

这是因为对于元数据字典表 innodb\_table\_stats 的更新是有条件的，只有当表中的超过 10% 的记录发生变更时，才会触发更新！

另外，虽然文档中说可以通过命令 FLUSH TABLE tbl\_name 触发手动重新统计表的元数据信息，但是根据姜老师的测试发现，貌似也没有触发。

当然，这不是关键，因为我们要获取的最近 5 分钟内发生变更过的数据，即通过 1 条 SQL 取得所有发生变更的表名。

所以，我们的目标不得不转向 information\_schema 数据库下的表 TABLES。

虽然该表的存储引擎为 Memroy ，但他是实时更新的。如：

![bf8a75d958807897f9783ee5867ef619.jpeg][]

编辑切换为居中

添加图片注释，不超过 140 字（可选）

点击图片可放大

可以看到插入记录后，表 t 的最后修改时间从15:17:59 变为了 17:24:04，是最新变更的时间。

所以，查询最近5分钟发生变化的表，可以通过下面的命令：

SELECT \* FROM information\_schema.TABLES WHERE (update\_time > DATE\_SUB(NOW(), INTERVAL 5 MINUTES) AND update\_time =< NOW())

问题2

接着的问题是，为什么表 TABLES 可以实时更新？实时更新的代价不是会很大么？

再给同学们3分钟的思考时间。

![9d1f63baf5e25924951f9bf6e314b288.gif][]

编辑

添加图片注释，不超过 140 字（可选）

时间到。

这是因为表 innodb\_table\_stats 需要持久化到磁盘，每次表变更就更新元数据字典表的话，会导致开销增大。

而表 TABLES 是元数据字典表内存数据结构的一种映射，并通过 Memroy 引擎绑定将最后的数据显示出来而已。

InnoDB 存储引擎中对于表的元数据字典结构定义为 dict\_table\_t，其大致定义如下所示：

struct dict\_table\_t \{ table\_name\_t name; time\_t update\_time; ... \}

问题3

然而，在使用表 TABLES 的过程中，我们发现在某台 MySQL 实例上发生了一个”诡异“的现象：

表 TABLES 中某些记录的 update\_time 字段会诡异地从非 NULL 值更新为 NULL 值

上述现象导致统计 5 分钟内发生变更表的遗漏，最终产生了另一个服务的错误。

但是，小伙伴通过源码阅读发现这个现象是一个正常的现象！

因为 InnoDB 存储引擎层面存储表的元数据字典信息 dict\_table\_t 在内存中其实一个 LRU 的数据结构：

struct dict\_sys\_t\{ UT\_LIST\_BASE\_NODE\_T(dict\_table\_t) table\_LRU; ... \} struct dict\_table\_t \{ table\_name\_t name; UT\_LIST\_NODE\_T(dict\_table\_t) table\_LRU; time\_t update\_time; ... \}

而 InnoDB 存储引擎的 Master 后台线程会定期进行扫描，确保这个 LRU 链表中元字典数据表的数量不要超过参数 table\_definition\_cache

![8074e7333f9fa885434ede559f404925.jpeg][]

编辑切换为居中

添加图片注释，不超过 140 字（可选）

而参数 table\_definition\_cache 设置的值为 4000，这意味着若 5 分钟内有超过 4000 张表发生过打开，又或有超过 4000 张表发生修改，那么其中某些表就会被从 LRU 链表中移除。

待下次读取表的元数据字典信息时， 会重新分配和初始化表的元数据字典对象，而这时 update\_time 就会显示为 NULL，从而导致统计出错。

分析完原因后，要解决上述问题就很简单了，只需要调大参数 table\_definition\_cache 即可。

但参数调大后，意味着 MySQL 的内存使用率会增大。

虽然 dict\_table\_t 结构本身只占用不到 700 字节，但这个结构中还有列名信息，索引元数据字典信息等：

struct dict\_table\_t \{ table\_name\_t name; UT\_LIST\_NODE\_T(dict\_table\_t) table\_LRU; time\_t update\_time; const char\* col\_names; UT\_LIST\_BASE\_NODE\_T(dict\_index\_t) indexes; ... \}

如果表的列很多很长，表上的索引数量也较多，那么数据库实例占用的内存会更大。

所以同学们线上一定要预留足够的内存空间，否则可能会产生 OOM 的问题。

[efdd6869fa48667b4a477d9ccb2caf1c.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/52ffc16a15fd47e7b7c1858e1c2e4c83.jpeg
[8d0211e6e2651f63200aba6699e0575e.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/38c21025be604df1ac83e6e10c4ec43a.jpeg
[bf8a75d958807897f9783ee5867ef619.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/86bcc534b56a45f48026ad20486983ff.jpeg
[9d1f63baf5e25924951f9bf6e314b288.gif]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/5585bdeaff8b439798201f358db33969.gif
[8074e7333f9fa885434ede559f404925.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/e5d8eef4f33a44cd8581b1b5c4cd67f7.jpeg