为什么估算的行数在phpmyadmin结果中有很大不同? [英] Why is the estimated rows count very different in phpmyadmin results?

问题描述

在数据库上没有任何更改，我的表上的行数却大不相同.

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(*)的信息丰富的博客文章用于Pernoa的InnoDB表.

SHOW TABLE STATUS的MySQL手册页状态:

行数.一些存储引擎(例如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限制上的页面详细信息:

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

这篇关于为什么估算的行数在phpmyadmin结果中有很大不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！