EXPLAIN 和 COUNT 返回两个不同的值

2021-12-30 00:00:00 sql count mysql sql-execution-plan

我在做:

explain select * from calibration;

它说 52133456345632 行

it says 52133456345632 rows

当我这样做时:

select count(*) from calibration;

我收到了 52134563456961

i am getting 52134563456961

有人能解释一下这里发生了什么吗?

can someone explain whats going on here?

推荐答案

表统计信息(由 EXPLAIN 使用)基于系统缓存的值，可能不准确.

Table statistics (used by EXPLAIN) are based on system-cached values that may not be accurate.

http://dev.mysql.com/doc/refman/5.1/en/using-explain.html 说:

对于 InnoDB 表，这个数字是一个估计值，可能并不总是准确的.

For InnoDB tables, this number is an estimate, and may not always be exact.

因此，'count()' 版本的查询将是准确的，因为它确实会计算"现有行.解释"版本不一定计算您的行数，但可能会使用估计/缓存.解释并不打算在代码或生产中实际使用 - 它只是帮助分析查询的工具.

So the 'count()' version of the query will be accurate, as it will really 'count' existing rows. The 'explain' version does not necessarily count your rows, but might use an estimation/cache. Explain is not intended to be actually used in code or production - it is just a tool to help analysing your queries.

相关文章