MySQL不会更新information_schema，除非我手动运行ANALYZE TABLE`myTable` [英] MySQL not updating information_schema, unless I manually run ANALYZE TABLE `myTable`
问题描述
我需要获取表(InnoDB)的最后一个id(主键)，为此，我执行以下查询:
I have the need to get last id (primary key) of a table (InnoDB), and to do so I perform the following query:
SELECT (SELECT `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = 'mySchema' AND `TABLE_NAME` = 'myTable') - 1;
，它将返回错误的AUTO_INCREMENT.问题是除非我运行以下查询，否则information_schema的TABLES表不会使用当前值更新:
which returns the wrong AUTO_INCREMENT. The problem is the TABLES table of information_schema is not updated with the current value, unless I run the following query:
ANALYZE TABLE `myTable`;
为什么MySQL不会自动更新information_schema，我该如何解决此问题?
运行MySQL Server 8.0.13 X64.
Why doesn't MySQL update information_schema automatically, and how could I fix this behavior?
Running MySQL Server 8.0.13 X64.
推荐答案
问:为什么MySQL不会自动更新information_schema，我该如何解决此问题?
Q: Why doesn't MySQL update information_schema automatically, and how could I fix this behavior?
A: InnoDB将auto_increment值保留在内存中，并且不会将其持久化到磁盘上.
A: InnoDB holds the auto_increment value in memory, and doesn't persist that to disk.
元数据查询的行为(例如SHOW TABLE STATUS)受innodb_stats_on_metadata和innodb_stats_persistent变量的设置影响.
Behavior of metadata queries (e.g. SHOW TABLE STATUS) is influenced by setting of innodb_stats_on_metadata and innodb_stats_persistent variables.
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata
每次查询元数据时都强制执行ANALYZE可能会降低性能.
Forcing an ANALYZE everytime we query metadata can be a drain on performance.
除了这些变量的设置，或者是通过手动执行ANALYZE TABLE来强制收集统计信息之外，我认为没有解决此问题的方法.
Other than the settings of those variables, or forcing statistics to be collected by manually executing the ANALYZE TABLE, I don't think there's a "fix" for the issue.
(我认为主要是因为我认为这不是需要解决的问题.)
(I think that mostly because I don't think it's a problem that needs to be fixed.)
要获得表中auto_increment列的最大值，规范模式为:
To get the highest value of an auto_increment column in a table, the normative pattern is:
SELECT MAX(`ai_col`) FROM `myschema`.`mytable`
让我感到困惑的是为什么我们需要检索此特定信息.我们将用它做什么?
What puzzles me is why we need to retrieve this particular piece of information. What are we going to use it for?
当然，我们不会在应用程序代码中使用该值来确定分配给刚插入的行的值.不能保证最大值不是来自其他会话插入的行.并且我们有LAST_INSERT_ID()机制来检索会话中刚刚插入的行的值.
Certainly, we aren't going to use that in application code to determine a value that was assigned to a row we just inserted. There's no guarantee that the highest value isn't from a row that was inserted by some other session. And we have LAST_INSERT_ID() mechanism to retrieve the value of a row our session just inserted.
如果我们使用ANALYZE TABLE来刷新统计信息，那么与随后的SELECT之间还有一小段时间...另一个会话可能会滑入另一个INSERT中，以便我们从收集中获得值统计信息在我们检索时可能已过时".
If we go with the ANALYZE TABLE to refresh statistics, there's still a small some time between that and a subsequent SELECT... another session could slip in another INSERT so that the value we get from the gather stats could be "out of date" by the time we retrieve it.
这篇关于MySQL不会更新information_schema，除非我手动运行ANALYZE TABLE`myTable`的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
