MySQL不会自动更新information_schema，除非我手动运行ANALYZE TABLE `myTable`。

Question

我需要获取一张表（InnoDB）的最后一个id（主键），为此我执行以下查询：

SELECT (SELECT `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = 'mySchema' AND `TABLE_NAME` = 'myTable') - 1;

返回错误的AUTO_INCREMENT。问题在于信息模式中的TABLES表未随当前值更新，除非我运行以下查询：

ANALYZE TABLE `myTable`;

为什么MySQL不会自动更新information_schema信息，我该如何解决这个问题？
运行的是MySQL Server 8.0.13 X64版本。

- Oliver

---

我猜INNODB数据库把那个存储在别的地方。 - RiggsFolly

Answer 1

Q: MySQL为什么不自动更新information_schema，我该如何解决这个问题？

A: InnoDB将auto_increment值保存在内存中，并不将其持久化到磁盘。

元数据查询的行为（例如 SHOW TABLE STATUS ）受 innodb_stats_on_metadata 和 innodb_stats_persistent 变量设置的影响。

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata

每次查询元数据时强制执行ANALYZE可能会影响性能。

除了调整这些变量的设置或手动执行 ANALYZE TABLE 来收集统计信息之外，我认为没有“解决”此问题的方法。

（我认为这主要是因为我认为这不是需要修复的问题。）

---

获取表中auto_increment列的最高值的规范模式为：

 SELECT MAX(`ai_col`) FROM `myschema`.`mytable`

---

让我感到困惑的是为什么我们需要检索这个特定的信息。我们将用它来做什么呢？

当然，我们不会在应用程序代码中使用它来确定刚刚插入行分配的值。无法保证最高值不是由其他会话插入的行导致的。而且我们有LAST_INSERT_ID()机制来检索我们的会话刚刚插入的行的值。

如果我们使用ANALYZE TABLE刷新统计信息，那么在那之后和随后的SELECT之间仍然存在一些时间...另一个会话可能会插入另一个INSERT，因此我们从收集统计数据中获得的值可能在我们检索它时已经过时了。

- spencer7593

---

感谢您的好解释。我原以为MAX（id）会减慢查询速度，所以找到了另一种方法，但不幸的是，这种方法对InnoDB表无效。我的用例如下：我有一个包含空间坐标的表，在新插入时，我必须检查新位置是否在10米半径内。如果是，则丢弃该位置。您认为使用这种方法是错误的吗？谢谢！ - Oliver

拥有一个合适的索引可用应该可以使 SELECT MAX(mycol) FROM mytable 的性能合理。使用案例唯一的问题是有点模糊。我们需要检查我们要插入的新行的坐标是否与具有最高 ai_col 值的行上的坐标相差不到 10 英里？如果我们将不得不查询表以获取坐标，那么我们可以使用类似于 SELECT r.coordinate_col FROM mytable r JOIN ( SELECT MAX(t.id) AS max_id FROM mytable t ) s ON s.max_id = r.id 的东西来完成它。 - spencer7593

如果我们需要计算“直线距离”，也就是大圆距离，我们可以让查询更加复杂，将我们提议的行的坐标输入，进行GCD计算，并返回距离... - spencer7593

我会使用EXPLAIN来查看查询计划；我会选择与内联视图的JOIN而不是id = (subquery)模式。我们只想确保MySQL在启动距离计算之前将结果缩减为单个行，我们只需要在一行上执行此操作。我倾向于做这样的事情：SELECT ST_Distance_Sphere(POINT(?, ?), t.lnglat) <= 10 AS bool_ FROM location t JOIN ( SELECT MAX(r.id) AS max_id FROM location r ) s ON s.max_id = t.id。 - spencer7593

@YanKarin - 如果您使用 ORDER BY id DESC LIMIT 1，则子查询是不必要的。(类似于我的答案。)您的查询(以及我的变体)将根据是否在10个距离单位内返回 true/false (1/0)。(请注意您的单位是什么。) - Rick James

Answer 2

SELECT * FROM tbl ORDER BY insert_datetime DESC LIMIT 1;

使用以下语句可以获取“最新”插入行的所有数据。不需要处理AUTO_INCREMENT，不需要使用子查询，不需要ANALYZE、information_schema或其他额外的提取（一旦获得id，就不需要额外的提取），等等。

SELECT * FROM table_name ORDER BY column_name DESC LIMIT 1;

是的，您确实需要在用于确定“最新”数据的列上建立索引。是的，可以使用id，但不应该这样做。AUTO_INCREMENT值保证是唯一的，但没有其他保证。

- Rick James

Answer 3

这个帖子已经有5年了，但是需要解决spencer7593的评论，因为这仍然是一个问题。

"要获取表中自增列的最高值，规范的模式是：

SELECT MAX(ai_col) FROM myschema.mytable"

这经常用于获取插入到表中的最后一个标识值。这是一个如此常见的查询，应该在information_schema或全局变量中进行更新，例如@last_identity_insert。在向表和相关表中进行多次插入时，外键需要最后一个标识值。这只是一个例子。

同样，information_schema.table_rows需要保持最新。从大表中选择count(*)会对性能造成巨大的影响。从information_schema中选择当前值则不会。

"每次查询元数据时强制进行ANALYZE会对性能造成负担"

甚至不需要ANALYZE。当对表进行插入操作时，数据库知道受影响的行数。更新表的行数应该是一个简单的问题。

这是一个烦恼。其他主要的关系型数据库管理系统供应商没有这个问题。

- Joseph White