如何确定InnoDB表的最后更改时间? [英] How can I determine when an InnoDB table was last changed?

问题描述

过去，我已经成功地使用基础表的上次更新时间作为缓存键的一部分，将数据库查询的(大量)处理结果存储在memcached中.对于MyISAM表，最后更改的时间在SHOW TABLE STATUS中可用.不幸的是，对于InnoDB表，通常是NULL.

I've had success in the past storing the (heavily) processed results of a database query in memcached, using the last update time of the underlying tables(s) as part of the cache key. For MyISAM tables, that last changed time is available in SHOW TABLE STATUS. Unfortunately, that's usually NULL for InnoDB tables.

在MySQL 4.1中，InnoDB在其SHOW TABLE STATUS行中的ctime通常是其实际的上次更新时间，但对于MySQL 5.1而言似乎并非如此.

In MySQL 4.1, the ctime for an InnoDB in its SHOW TABLE STATUS line was usually its actual last update time, but that doesn't seem to be true for MySQL 5.1.

表中有一个DATETIME字段，但是它仅显示何时修改了一行-它不能显示不再存在的行的删除时间！所以，我真的不能使用MAX(update_time).

There is a DATETIME field in the table, but it only shows when a row has been modified - it cannot show the deletion time of a row that's not there anymore! So, I really cannot use MAX(update_time).

这是真正棘手的部分.我有许多从中读取的副本.我可以找出实际应用更改时不依赖于表的状态吗?

Here's the really tricky part. I have a number of replicas that I do reads from. Can I figure out the state of the table that doesn't rely on when the changes have actually been applied?

经过一段时间的研究，我的结论是，不可能像我所希望的那样便宜地获得这些信息.我可能要缓存数据，直到我期望表更改的时间(每天更新一次)为止，然后让查询缓存在可能的地方提供帮助.

My conclusion after working on this for a while is that it's not going to be possible to get this information as cheaply as I'd like. I'm probably going to cache data until the time that I expect the table to change (it's updated once a day), and let the query cache help out where it can.

推荐答案

这是 MySQL错误14374 ， 15438 和基础我有两个建议(除了修补MySQL外).

I have two suggestions (other than patching MySQL).

1. 如果每个文件使用一个表(innodb_file_per_table)，请统计基础文件.您可以编写一个MySQL函数/扩展来做到这一点.由于数据库缓存，这可能会稍微滞后.
2. 您可以在更新，删除和插入触发器后使用该触发器，以使自己的元数据表与您关注的每个表的最后更新时间保持一致.

1. If you're using one table per file (innodb_file_per_table), stat the underlying file. You could write a MySQL function/extension to do this. This may lag slightly, due to database caching.
2. You can use after update, delete, and insert triggers to keep your own metadata table with the last update times for each table you're concerned with.

我个人建议第二个，因为第二个更易移植，并且不依赖于实现细节(例如innodb_file_per_table).

I'd personally suggest the second, as its much more portable and doesn't depend on implementation details (such as innodb_file_per_table).

这篇关于如何确定InnoDB表的最后更改时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！