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”.