这个 MySQL 问题困扰了我一个月,现在终于把他解决了!
1
问题1
SELECT * FROM innodb_table_stats
WHERE last_update > DATE_SUB(NOW(), INTERVAL 5 MINUTES)
AND last_update =< NOW()
点击图片可放大
可以看到插入记录后,表 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
问题2
接着的问题是,为什么表 TABLES 可以实时更新?实时更新的代价不是会很大么?
再给同学们3分钟的思考时间。
时间到。
这是因为表 innodb_table_stats 需要持久化到磁盘,每次表变更就更新元数据字典表的话,会导致开销增大。
而表 TABLES 是元数据字典表内存数据结构的一种映射,并通过 Memroy 引擎绑定将最后的数据显示出来而已。
InnoDB 存储引擎中对于表的元数据字典结构定义为 dict_table_t,其大致定义如下所示:
struct dict_table_t {
table_name_t name;
time_t update_time;
...
}
3
问题3
然而,在使用表 TABLES 的过程中,我们发现在某台 MySQL 实例上发生了一个”诡异“的现象:
上述现象导致统计 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
而参数 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 的问题。
以上。
4
今日思考题
2. 参数 table_definition_cache 应该设置到多大才合理?是否有监控可以统计到被刷出 LRU 列表的次数?
3. 表 information_schema.TABLES 底层实现上,MySQL 8.0 和 5.7 有何不同?
4. 在 MySQL 8.0 版本中使用表 information_schema.TABLES ,需要注意哪个参数?否则可能会导致查询不及时呢?
5. 表最后更新的时间 last_update 是在 InnoDB 源码哪个函数中被修改的?
欢迎留言回答。
全部答对的小伙伴,可免费加入 IMG VIP 群,获得 VIP 成员1年资格以及本号技术文章所有思考题的答案哟~~~
想要付费加入 IMG VIP 群的小伙伴也可以私信姜老师,欢迎成为尊贵的 IMG VIP 用户
往期推荐