mysql explain 当中不靠谱的地方-CSDN博客

本文链接：https://blog.csdn.net/oneyearlater/article/details/7606965

本文探讨了在特定条件下数据库查询优化器的行为变化，尤其是在涉及大量数据时如何选择索引和进行全表扫描的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1) 扫描的记录数在10w左右附近的时候, 即使在issuedOn上面加了索引，也会显示有问题，如

select count(*) from table1 where a between '2002-04-22' and '2002-08-12';

+----+-------------+--------------------+-------+---------------+----------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+-------+---------------+----------+---------+------+--------+-------------+
| 1 | SIMPLE | table1 | range | a | a | 4 | NULL | 169138 | Using where |
+----+-------------+--------------------+-------+---------------+----------+---------+------+--------+-------------+

1 row in set (0.01 sec)

用了key，并且是range

select count(*) from table1 where a between '2002-04-22' and '2002-08-31';

+----+-------------+--------------------+------+---------------+------+---------+------+---------+-------------+

居然是全表扫描！！

实际结果是

select count(*) as t1 from table1 where issuedOn between '2002-04-22' and '2002-08-12';
+----------+
| t1 |
+----------+
| 96769 |
+----------+
1 row in set (0.08 sec)

mysql> select count(*) as t2 from table1 where issuedOn between '2002-04-22' and '2002-08-31';
+----------+
| t2 |
+----------+
| 113185 |
+----------+
1 row in set (0.08 sec)