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