为什么 phpmyadmin 结果中估计的行数有很大不同?

2021-11-20 00:00:00 mysql phpmyadmin

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

Without any changes on database I got very different rows count on my tables.

什么会导致这种情况?

Server version: 5.1.63-cll Engine: InnoDB

推荐答案

与 MyISAM 表不同，InnoDB 表不跟踪表包含的行数.

Unlike MyISAM tables, InnoDB tables don't keep track of how many rows the table contains.

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

Because of this, the only way to know the exact number of rows in an InnoDB table is to inspect each row in the table and accumulate a count. Therefore, on large InnoDB tables, performing a SELECT COUNT(*) FROM innodb_table query can be very slow because it does a full table scan to get the number of rows.

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

phpMyAdmin uses a query like SHOW TABLE STATUS to get an estimated count of the number of rows in the table from the engine (InnoDB). Since it's just an estimate, it varies each time you call it, sometimes the variations can be fairly large, and sometimes they are close.

这是一篇关于COUNT(*)Percona 用于 InnoDB 表.

SHOW TABLE STATUS 状态:

行数.一些存储引擎，例如 MyISAM，存储精确计数.对于其他存储引擎，比如 InnoDB，这个值是一个近似值，可能与实际值相差多达 40到 50%.在这种情况下，使用 SELECT COUNT(*) 来获得准确的计数.

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 限制上的页面进入了一些更多细节:

The page on InnoDB restrictions goes into some more detail:

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

SHOW TABLE STATUS does not give accurate statistics on InnoDB tables, except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization.

InnoDB 不保留表中行的内部计数，因为并发事务可能会看到"不同数量的行同时.为了处理 SELECT COUNT(*) FROM t 语句，InnoDB 扫描表的索引，如果索引不是，则需要一些时间完全在缓冲池中.如果你的桌子不经常更换，使用 MySQL 查询缓存是一个很好的解决方案.要快速计数，你必须使用你自己创建的柜台表，让你的应用程序根据插入更新它并删除它.如果一个近似的行数就足够了，可以使用 SHOW TABLE STATUS.请参阅第 14.3.14.1 节，InnoDB 性能调优技巧".

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

相关文章