为什么行通过“explain”返回不等于count（）？ [英] Why the rows returns by "explain" is not equal to count()?

问题描述

  mysql> select count（*）from table where relation_title ='xxxxxxxxx'; 
 + ---------- + 
 | count（*）| 
 + ---------- + 
 | 1291958 | 
 + ---------- + 
 
 mysql>解释select * from table where relation_title ='xxxxxxxxx'; 
 + ---- + ------------- + --------- +  -  
 | id | select_type |行| 
 + ---- + ------------- + --------- +  -  
 | 1 | SIMPLE | 1274785 | 
 + ---- + ------------- + --------- +  -  
  

我认为解释select * from table where relation_title ='xxxxxxxxx';通过索引返回relation_title ='xxxxxxxxx'的行。但它小于真正的num。

解决方案

它显示它运行通过多少行以获得你的结果。

错误数据的原因是EXPLAIN不准确，它根据存储在表格中的信息猜测您的数据。

这是非常有用的信息，例如，当对许多表执行JOINS时，并且希望确保您没有在每个行的一行信息中运行整个联接表。 / p>

这是对608行表的测试。

  SELECT COUNT id）FROM table WHERE user_id = 1 
  

结果：

$ b b

  COUNT（id）
 512 
  

这里是解释

  EXPLAIN SELECT COUNT（id）FROM table WHERE user_id = 1 
  

结果：

  $ b 1 608 
  

    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 | 
+----+-------------+---------+-

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.

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.

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.

Here's a test on a 608 row table.

SELECT COUNT(id) FROM table WHERE user_id = 1

Result:

COUNT(id)
512

And here's the explain

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

Result:

id  rows
1   608

这篇关于为什么行通过“explain”返回不等于count（）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！