如何优化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字段验证，不能凭感觉。