MySQL中between查询时间会索引失效吗？

Question

在MySQL中使用 `BETWEEN` 查询时间范围时，是否会导致索引失效是开发者常遇到的性能疑点。例如，对 `DATETIME` 或 `TIMESTAMP` 类型字段建立索引后，执行如 `WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59'` 的查询，理论上应走索引。但实际中若数据类型不匹配、函数包裹（如 `DATE(create_time)`）、或使用了不支持索引的隐式转换，就可能导致索引失效。此外，统计信息不准或查询优化器认为全表扫描更快时，也可能放弃使用索引。因此，需通过 `EXPLAIN` 检查执行计划，确保索引有效利用。

Answer 1

1. 索引与BETWEEN查询的基本原理

在MySQL中，BETWEEN 是一个用于匹配闭区间范围的SQL操作符，常用于时间字段如 DATETIME 或 TIMESTAMP 的筛选。当对这些字段建立B+树索引后，理论上使用 BETWEEN 查询应能高效利用索引进行范围扫描（range scan）。

例如：

SELECT * FROM orders 
WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59';

若 create_time 字段上有索引，该语句预期会走索引。但实际执行是否使用索引，取决于多个因素，包括数据类型、表达式使用方式和优化器决策等。

2. 导致索引失效的常见原因分析

• 函数包裹字段：如使用 WHERE DATE(create_time) BETWEEN ...，MySQL无法直接利用索引，因为函数改变了原始列值。
• 隐式类型转换：当比较字符串与日期类型不一致时，如字段为 DATETIME，而传入参数为非标准格式字符串，可能导致全表扫描。
• 索引列参与计算或表达式：如 WHERE create_time + INTERVAL 1 DAY BETWEEN ...，破坏了索引结构。
• 统计信息过期：InnoDB的统计信息未更新，导致优化器误判选择性，放弃索引。
• 高选择性场景下全表扫描更优：当查询覆盖大部分数据行时，优化器可能认为全表扫描成本更低。

3. 验证索引使用情况：EXPLAIN 执行计划解析

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	orders	NULL	range	idx_create_time	idx_create_time	5	NULL	12500	100.00	Using where
1	SIMPLE	orders	NULL	ALL	NULL	NULL	NULL	NULL	1000000	0.10	Using where

第一行显示使用了 range 类型访问方式，并命中 idx_create_time 索引；第二行则为全表扫描（ALL），表明索引未被使用。重点关注 key 和 type 字段。

4. 正确使用BETWEEN避免索引失效的最佳实践

1. 确保字段无函数封装，保持裸列出现在条件左侧。
2. 使用与列类型匹配的字面量，如 '2023-01-01 00:00:00' 对应 DATETIME。
3. 避免跨时区处理逻辑嵌入SQL，应在应用层完成时间标准化。
4. 定期运行 ANALYZE TABLE orders; 更新统计信息。
5. 考虑使用复合索引提升覆盖查询效率。
6. 使用 FORCE INDEX(idx_create_time) 在极端情况下强制索引（慎用）。
7. 监控慢查询日志，识别潜在的索引滥用问题。
8. 测试不同时间粒度下的查询性能差异，如按天、月分区的影响。
9. 使用 gtid_next 等高级特性前评估其对查询优化的干扰。
10. 结合 optimizer_trace 深入分析优化器决策路径。

5. 可视化：查询优化流程图（Mermaid）

graph TD
    A[开始查询] --> B{WHERE 条件是否包含函数?}
    B -- 是 --> C[索引失效, 走全表扫描]
    B -- 否 --> D{字段类型与值匹配?}
    D -- 否 --> E[触发隐式转换, 可能失效]
    D -- 是 --> F{优化器评估成本}
    F --> G[选择最低成本执行路径]
    G --> H[使用索引 range scan]
    G --> I[执行全表扫描]
    H --> J[返回结果]
    I --> J

6. 进阶调优建议与监控策略

对于拥有百万级以上数据量的表，仅依赖 BETWEEN 并不足以保障性能。可引入以下机制：

• 分区表设计：按时间范围进行RANGE分区，减少单次扫描的数据量。
• 索引覆盖优化：将常用查询字段纳入联合索引，避免回表。
• 使用虚拟列+索引：对需要函数处理的场景，创建存储式虚拟列并建索引。
• 启用Performance Schema：追踪索引使用频率与查询延迟分布。
• 结合pt-query-digest工具：分析生产环境中真实SQL执行模式。