mysql - 为什么 phpmyadmin 结果中估计的行数非常不同？

Question

在不对数据库进行任何更改的情况下，我的表中的行数非常不同。

什么会导致这种情况？

Server version: 5.1.63-cll
Engine: InnoDB

Answer 1

与 MyISAM 表不同，InnoDB 表不跟踪表包含多少行。

正因为如此，了解 InnoDB 表中行的确切数量的唯一方法是检查表中的每一行并累积一个计数。因此，在大型 InnoDB 表上，执行 SELECT COUNT(*) FROM innodb_table 查询可能会非常慢，因为它会进行全表扫描以获取行数。

phpMyAdmin 使用类似SHOW TABLE STATUS 的查询从引擎 (InnoDB) 中获取表中行数的估计计数。由于这只是一个估计值，每次调用时都会有所不同，有时变化可能相当大，有时则很接近。

这是一篇关于 COUNT(*) for InnoDB tables 的博文由 Percona 提供。

SHOW TABLE STATUS 的 MySQL 手册页状态:

The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

InnoDB restrictions上的页面进入更多细节:

SHOW TABLE STATUS 没有给出 InnoDB 表的准确统计信息，除了表保留的物理大小。行数只是 SQL 优化中使用的粗略估计。

InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. To process a SELECT COUNT(*) FROM t statement, InnoDB scans an index of the table, which takes some time if the index is not entirely in the buffer pool. If your table does not change often, using the MySQL query cache is a good solution. To get a fast count, you have to use a counter table you create yourself and let your application update it according to the inserts and deletes it does. If an approximate row count is sufficient, SHOW TABLE STATUS can be used. See Section 14.3.14.1, “InnoDB Performance Tuning Tips”.