MySQL大数据量BETWEEN范围查询性能优化方法

BETWEEN查询变慢，根本原因在于容易触发全表扫描。优化方案很清晰：建立合理的复合索引（等值条件字段放在左侧，范围字段置于右侧），避免使用函数或表达式导致索引失效，同时结合覆盖索引、分区、拆分查询等手段来提升性能。

为什么BETWEEN查询会变慢

老实说，BETWEEN本身并不慢，真正拖慢性能的是它容易引发全表扫描——当字段没有索引、索引被绕过、或者查询范围过大时，MySQL只能逐行遍历数据。例如SELECT * FROM logs WHERE created_at BETWEEN '2020-01-01' AND '2026-01-01'，如果created_at缺少索引，或者使用了DATE(created_at)这样的函数，执行计划中的type就会显示为ALL，rows接近总行数。说白了，就是数据库在硬扛。

必须建索引，但别只建单列索引

单列索引CREATE INDEX idx_created_at ON logs(created_at)能解决基础问题，但实际业务场景往往带有等值条件，比如WHERE status = 'success' AND created_at BETWEEN ? AND ?。此时单列索引的效率远不如复合索引。

• 等值字段（如status）必须放在复合索引最左侧，范围字段（如created_at）必须放在最右侧
• 正确写法：CREATE INDEX idx_status_created ON logs(status, created_at)
• 错误写法：CREATE INDEX idx_created_status ON logs(created_at, status)——status完全无法走索引
• 如果还有第三个高频等值字段（如user_id），顺序应为(user_id, status, created_at)，切记不能把范围字段插在中间

避免让索引失效的常见操作

BETWEEN字段一旦参与表达式或函数，索引立刻失效。MySQL不会自动“反向推导”时间范围，它只认字段原样出现。

• ❌ WHERE YEAR(created_at) = 2024 → 索引失效
• ✅ WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' → 推荐做法，比BETWEEN更可控（避免23:59:59手误）
• ❌ WHERE DATE(created_at) = '2024-06-01' → 强制类型转换，索引失效
• ✅ WHERE created_at >= '2024-06-01' AND created_at < '2024-06-02' → 精确到天且可走索引
• 隐式类型转换同样危险：比如created_at是DATETIME类型，但传入字符串'2024-06-01'未带时分秒，MySQL可能放弃索引进行全表转换

缩小扫描范围比优化SQL更重要

即使索引完美，查询一年数据与查询一天数据，I/O、内存、网络开销相差一个数量级。别指望一条SQL扛下所有压力。

• 用LIMIT限制返回行数，尤其是分页场景：... BETWEEN ? AND ? ORDER BY id LIMIT 100
• 避免SELECT *，只取所需字段；若常用字段都在索引里，直接建覆盖索引：CREATE INDEX idx_covering ON logs(status, created_at, user_id, message)
• 超大表（千万级以上）考虑按月分区：PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at))，让MySQL跳过无关分区
• 对极长周期查询（如“近五年日志”），前端拆成5条语句并行查询，后端合并结果，比单条大范围查询更稳定

最容易被忽略的是：复合索引中范围字段右侧的所有列，无论是否出现在WHERE中，都不可用于索引查找。哪怕你写了WHERE a = 1 AND b > 10 AND c = 'x'，只要索引是(a, b, c)，c就只是过滤条件，不参与索引定位——这点必须靠EXPLAIN里的key_len和Extra字段验证，不能凭感觉。