为什么行通过“解释"返回?不等于count()?

2021-11-20 00:00:00 count mysql explain

    mysql> select count(*) from table where relation_title='xxxxxxxxx';
+----------+
| count(*) |
+----------+
|  1291958 |
+----------+

mysql> explain select *  from table where relation_title='xxxxxxxxx';
+----+-------------+---------+-
| id | select_type | rows    |
+----+-------------+---------+-
|  1 | SIMPLE      | 1274785 | 
+----+-------------+---------+-

我认为解释 select * from table where relationship_title='xxxxxxxxx';"按索引返回relation_title='xxxxxxxxx' 的行.但它比真正的数字小.

I think that "explain select * from table where relation_title='xxxxxxxxx';" returns the rows of relation_title='xxxxxxxxx' by index. But it's small than the true num.

推荐答案

它显示了它运行了多少行以获得您的结果.

It is showing how many rows it ran through to get your result.

错误数据的原因是 EXPLAIN 不准确，它根据存储的有关您的表的信息对您的数据进行猜测.

The reason for the wrong data is that EXPLAIN is not accurate, it makes guesses about your data based on information stored about your table.

这是非常有用的信息，例如在对许多表执行 JOINS 时，您希望确保不会为每一行的一行信息遍历整个连接表.

This is very useful information, for example when doing JOINS on many tables and you want to be sure that you aren't running through the entire joined table for one row of information for each row you have.

这是对 608 行表的测试.

Here's a test on a 608 row table.

SELECT COUNT(id) FROM table WHERE user_id = 1

结果:

COUNT(id) 512

这里是解释

EXPLAIN SELECT COUNT(id) FROM table WHERE user_id = 1

结果:

id rows 1 608

相关文章