MySQL WHERE查询性能差异疑问：两种条件为何扫描行数差异巨大？

核心逻辑：索引是否能被有效利用 #

两个查询的性能差异本质是索引是否可用，这和MySQL的B-tree索引运行机制直接相关：

第一个查询：高效利用索引 #

查询语句：

EXPLAIN SELECT * FROM sensor WHERE fecha >= '2022-07-16 10:00:00';

• 这个查询直接对fecha字段做范围比较，没有对字段做任何函数转换。MySQL的B-tree索引是按字段原始值有序存储的，因此可以快速定位到fecha大于等于'2022-07-16 10:00:00'的起始位置，然后只扫描后续匹配的32行数据，无需遍历全表。

第二个查询：索引失效导致全表扫描 #

查询语句：

EXPLAIN SELECT * FROM sensor WHERE DATE(fecha) = '2022-07-16' AND HOUR(fecha) >= '10:00:00';

• 这里对fecha字段使用了DATE()和HOUR()函数，属于对索引字段进行函数运算。这种情况下，MySQL无法直接利用fecha上的索引——因为函数运算会改变字段原本的有序性，索引里存储的是原始时间值，而非函数计算后的结果。
• MySQL只能对全表的95084条数据逐一计算DATE(fecha)和HOUR(fecha)的结果，再和条件对比，因此扫描行数等于总数据量，性能大幅下降。

优化建议 #

如果要实现第二个查询的逻辑且保持高效，需要把条件改写成不使用函数的形式，让索引能正常发挥作用：

EXPLAIN SELECT * FROM sensor WHERE fecha >= '2022-07-16 10:00:00' AND fecha < '2022-07-17 00:00:00';

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