MySQL EXPLAIN语句返回行数不准确问题咨询

你的测试现象完全符合MySQL InnoDB存储引擎的索引统计逻辑，以下是具体原因：

1. EXPLAIN中rows字段的本质 #

EXPLAIN输出的rows值是查询优化器基于索引统计数据估算的匹配行数，并非实际执行查询后的精确计数。InnoDB的索引统计信息是通过采样数据页生成的，而非全量扫描索引或表。

2. 大数量匹配时返回近似值 #

当company_id=60这类匹配行数极大的场景，InnoDB的统计采样机制（由innodb_stats_sample_pages参数控制默认采样页数）无法覆盖所有数据，只能通过采样结果估算整体匹配行数，因此会出现估算值（24347253）与实际值（23475317）的误差。这种误差是采样统计的正常现象，数据量越大、分布越不均匀，误差可能越明显。

3. 小数量匹配时返回准确值 #

对于company_id=1这类匹配行数极少的场景，对应的索引键值统计信息足够精确——InnoDB在统计时会记录低频键的精确计数，因此优化器能直接返回准确的估算值（181），与实际结果一致。

4. 无匹配行时返回1 #

当查询的company_id=2在索引中完全不存在时，InnoDB的统计数据里没有该键值的任何记录。此时查询优化器会使用默认最小估算值1，这是优化器的设计逻辑：它不会直接估算为0，避免因“认为无匹配”而跳过某些潜在的优化路径，同时这也是统计信息缺失时的兜底行为。

测试案例验证 #

以下是你的测试语句及结果，对应上述分析：

大数量匹配场景

mysql> select count(*) from contacts where company_id=60;
+----------+
| count(*) |
+----------+
| 23475317 |
+----------+
1 row in set (14.55 sec)

mysql> explain select count(*) from contacts where company_id=60;
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+----------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys           | key                     | key_len | ref   | rows     | filtered | Extra       |
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+----------+----------+-------------+
|  1 | SIMPLE      | contacts | NULL       | ref  | contacts_company_id_idx | contacts_company_id_idx | 5       | const | 24347253 |   100.00 | Using index |
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+----------+----------+-------------+
1 row in set, 1 warning (0.25 sec)

小数量匹配场景

mysql> select count(*) from contacts where company_id=1;
+----------+
| count(*) |
+----------+
|      181 |
+----------+
1 row in set (0.37 sec)

mysql> explain select count(*) from contacts where company_id=1;
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | contacts | NULL       | ref  | contacts_company_id_idx | contacts_company_id_idx | 5       | const |  181 |   100.00 | Using index |
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.31 sec)

无匹配行场景

mysql> select count(*) from contacts where company_id=2;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.34 sec)

mysql> explain select count(*) from contacts where company_id=2;
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | contacts | NULL       | ref  | contacts_company_id_idx | contacts_company_id_idx | 5       | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.25 sec)

内容的提问来源于stack exchange，提问作者Sidharth Samant