筛选时间区间这件事我在项目里踩过的坑比很多人想象的多得多。刚入行那会儿我觉得这有什么难的无非就是一个where加上两个时间条件写出来能跑就行。后来做订单系统、做日志系统、做设备运行记录系统才发现同样是根据时间字段查询指定时间段的数据写得好和写得烂在数据量小的时候完全看不出差别一旦单表到了几百万上千万行性能差距能拉开几十倍。这篇文章我想把这件事从头到尾捋一遍它到底在解决什么问题哪些时间字段类型是常见坑SQL 怎么写边界才不出错后端从参数接收到 ORM 拼 SQL 再到分页排序该怎么处理最后再把我实际遇到过的典型故障现场还原出来。不管你是刚接触后端的新人还是已经写了几年代码但对时间查询仍然心里没底的老手这篇应该都能找到能直接抄的部分。1. 为什么时间段查询看着简单实际上坑最多1.1 这个需求到底难在哪查询指定时间段的数据这句话翻译成技术语言其实是三件事叠在一起第一确定时间字段是什么类型、以什么精度存储第二确定边界的开闭规则也就是开始时间和结束时间到底含不含第三确定用什么手段让它在大数据量下还能跑得动。绝大多数人只做了第一件的表面工作第二件事靠差不多第三件事完全没考虑。我先说一个非常典型的场景。一个订单列表页用户选择2024-03-01 到 2024-03-31前端传过来的可能是2024-03-01 00:00:00和2024-03-31 00:00:00也可能是日期字符串2024-03-01和2024-03-31。如果是后面这种后端直接拿它去和DATETIME字段比较那整个 3 月 31 号这一天 00:00:00 之后产生的数据全都会被漏掉因为2024-03-31 10:22:00显然大于2024-03-31 00:00:00。用户会投诉说我明明选了 31 号怎么没有数据你查日志发现 SQL 没报错、接口没异常问题就出在这一天的精度差上。这类问题不是技术难度问题是严谨度问题。时间段查询真正的难点从来不是把 SQL 写出来而是把所有边界、时区、精度、性能的组合情况都想到并且处理掉。1.2 时间字段的三种典型存储形态在聊 SQL 之前得先统一认知数据库里时间字段的存储方式直接决定了查询怎么写。我见过的项目里主要有三种。第一种是原生的日期时间类型MySQL 里常见的是DATETIME和TIMESTAMPPostgreSQL 是timestamp和timestamptzOracle 是DATE和TIMESTAMP。这种写法可读性最好直接用 SQL 比较就行但要注意TIMESTAMP范围只到 2038 年而且受会话时区影响。第二种是用 BIGINT 存毫秒时间戳或者秒时间戳。这种在日志、埋点、消息系统里很常见好处是跨时区不歧义、精度高、比较运算快坏处是直接看数据库根本看不懂必须心里换算成时间。第三种是用字符串存yyyy-MM-dd HH:mm:ss。这种最坑我强烈建议能改就改。字符串比较虽然字典序在标准格式下恰好等价于时间先后但一旦格式不统一比如有的存2024-3-1有的存2024-03-01比较结果就完全是错的。下面这张表是我自己对三种形态的取舍总结可以直接拿去和团队讨论存储形态可读性跨时区索引效率推荐场景DATETIME高需约定时区高业务主数据、订单、表单TIMESTAMP中自动换算高创建/更新时间注意 2038BIGINT 时间戳低无歧义极高日志、埋点、海量事件流字符串高无低仅历史遗留不建议新项目用选型上没有绝对对错核心是团队内统一并且在接口层做一次明确的转换不要让前端传什么就直接往 SQL 里塞什么。1.3 边界问题左闭右开还是全闭这是我在代码评审里最常挑的一个点。时间段查询的边界数学上无非四种组合左闭右闭、左闭右开、左开右闭、左开右开。业务上几乎所有人想要的都是从开始时间到结束时间之间的数据但表达成代码时经常写错。我的建议是统一采用左闭右开time start AND time end。这里的end不是用户选择的结束日而是结束日的下一天零点。比如用户选 3 月 1 日到 3 月 31 日你真正传进 SQL 的应该是start 2024-03-01 00:00:00、end 2024-04-01 00:00:00然后条件写成time start AND time end。为什么推荐左闭右开而不是BETWEEN因为BETWEEN是闭区间等价于 AND 当字段精度是秒的时候 2024-03-31 00:00:00只包含到 31 号零点整这一秒还是漏了后面 23 小时 59 分 59 秒。只有把结束边界改成次日零点并用才在数学上无歧义地覆盖整段。这个规则一旦定下来跨天、跨月、跨年都自动正确不需要为每个月天数不同单独处理。提示定下左闭右开的规则后前后端、测试用例、文档都要同步否则前端按全闭逻辑理解又会重新引入偏差。2. 数据库层面的时间段查询怎么写才对2.1 BETWEEN、 AND 到底选哪个先把结论放前面能用 AND 就用它BETWEEN只在你非常确定字段精度和业务语义时使用。原因前面说过了BETWEEN的闭区间在秒级、毫秒级精度下天然容易漏数据而且很多人写BETWEEN时会顺手写成日期字符串触发隐式类型转换。来看一段实际的 SQL 对比-- 错误示范结束日是用户选择的日期精度丢失 SELECT * FROM orders WHERE create_time BETWEEN 2024-03-01 AND 2024-03-31; -- 正确示范结束边界用次日零点左闭右开 SELECT id, order_no, create_time, amount FROM orders WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-04-01 00:00:00;第二段不仅语义正确而且因为两个边界都是范围查询配合create_time上的索引可以高效地定位到一段连续的索引区间。第一段在某些 MySQL 版本下会因为2024-03-31被隐式转换成2024-03-31 00:00:00你会少查一整天。2.2 索引为什么会失效函数包裹字段的代价我见过太多这样写的 SQL-- 函数包裹字段索引直接失效 SELECT * FROM orders WHERE DATE(create_time) 2024-03-15; -- 或者用年月函数 SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m) 2024-03;DATE(create_time)和DATE_FORMAT(...)都把字段包在了函数里MySQL 没法用create_time索引去范围定位只能对每一行求值再比较也就是全表扫描。数据量小的时候你没感觉几百万行的时候就是秒级甚至分钟级的等待。正确的写法还是还原成范围-- 查某一天 SELECT * FROM orders WHERE create_time 2024-03-15 00:00:00 AND create_time 2024-03-16 00:00:00; -- 查某个月 SELECT * FROM orders WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-04-01 00:00:00;这里的原则很简单让字段裸奔把计算放到常量上。你要查哪一天、哪个月就在应用层算好边界值再传进去不要让数据库在字段上做函数运算。判断索引有没有生效最直接的就是看执行计划EXPLAIN SELECT * FROM orders WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-04-01 00:00:00;重点看type是不是range、key是不是用到了create_time索引、rows预估行数是多少。如果type是ALL那基本就是全表扫描了必须改。2.3 联合索引里时间字段该放哪一位单列时间索引好办麻烦的是联合索引。比如订单表经常按用户 时间段查索引是(user_id, create_time)还是(create_time, user_id)结果差别很大。如果查询几乎总是带user_id再叠加时间范围那(user_id, create_time)是最优的先用user_id等值定位再在剩下的区间里用create_time做范围约减。反过来如果索引是(create_time, user_id)时间范围一出现后面的user_id就用不上最左前缀了效果差很多。但如果查询有两种形态一种只按时间一种按用户加时间那就得权衡。我一般的做法是主查询带上用户维度就建(user_id, create_time)只按时间的报表类查询单独走另一个索引或者走读库、走离线。另外提醒一句联合索引里范围条件字段后面的列一般无法再用于索引快速过滤只能作为覆盖索引的一部分。这个规律记住能帮你少走很多弯路。2.4 慢查询日志定位时间段查询性能问题的第一手资料当线上出现某个时间段查询特别慢的反馈时不要凭猜先看慢查询日志。MySQL 默认long_query_time是 10 秒这个阈值对现代业务太宽了我会调成 1 秒甚至更低把慢查询都记下来-- 查看当前慢查询配置 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 开启慢查询日志按需生产环境注意磁盘 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;记下来之后用mysqldumpslow或者 pt-query-digest 做聚合看哪条时间段查询出现次数最多、平均耗时最长。经验上时间段查询慢九成是三个原因没索引、索引被函数破坏、查的时间范围太宽导致回表太多。逐个排查就行。3. 后端接口与 ORM 层的时间段查询落地3.1 参数接收与格式校验接口层我建议收两种形式的参数要么前后端约定统一传标准格式字符串yyyy-MM-dd HH:mm:ss要么传开始时间和结束时间两个字段并明确标注含义。坚决不要收一个关键词猜时间这种模糊接口。用 Java 举例接收时用LocalDateTime或LocalDate搭配DateTimeFormat、JsonFormat显式指定格式避免依赖默认行为public class TimeRangeQuery { DateTimeFormat(pattern yyyy-MM-dd HH:mm:ss) private LocalDateTime startTime; DateTimeFormat(pattern yyyy-MM-dd HH:mm:ss) private LocalDateTime endTime; // 省略 getter/setter }这里有个老生常谈但每年都有人踩的坑SimpleDateFormat不是线程安全的曾经有项目在静态字段里共享一个SimpleDateFormat高并发下解析出莫名其妙的时间。现在都用DateTimeFormatter它是不可变且线程安全的LocalDate、LocalDateTime也是不可变的能省掉一大堆并发麻烦。校验上至少要拦三件事开始时间不能晚于结束时间否则 SQL 结果恒空属于静默错误时间范围不能过大比如一次查三年这种请求要拒绝或者转为异步导出格式必须合法不合法直接返回参数错误不要让它进到 SQL 里。3.2 MyBatis 与 MyBatis-Plus 的动态条件用 MyBatis 写时间段条件标准姿势是if判断加两个独立条件不要用BETWEEN一把梭select idselectByTimeRange resultMapOrderMap SELECT id, order_no, create_time, amount FROM orders where if teststartTime ! null AND create_time gt; #{startTime} /if if testendTime ! null AND create_time lt; #{endTime} /if if teststatus ! null AND status #{status} /if /where ORDER BY create_time DESC LIMIT #{offset}, #{size} /select这样写的好处是开始、结束、状态都可以独立缺省组合灵活而且每个条件都是对字段裸比较不会破坏索引。注意 XML 里、要转义成lt;、gt;或者用 CDATA 包起来否则会解析报错。MyBatis-Plus 的QueryWrapper写时间段更简洁QueryWrapperOrder wrapper new QueryWrapper(); wrapper.ge(startTime ! null, create_time, startTime) .lt(endTime ! null, create_time, endTime) .eq(status ! null, status, status) .orderByDesc(create_time);ge是大于等于、lt是小于前面的布尔参数控制该条件是否加入正好对上某个条件可能为空的场景。这里唯一的注意点是字段名要用数据库列名而不是实体属性名混用会导致列找不到。3.3 分页与排序深分页是时间段查询的老对手时间段查询加上分页深分页问题就来了。假设按时间倒序翻到第 10000 页SQL 是SELECT * FROM orders WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-04-01 00:00:00 ORDER BY create_time DESC LIMIT 100000, 20;MySQL 要把满足条件的 100020 行都读出来再丢掉前 100000 行越翻到后面越慢。优化思路是用上一页最后一条的时间做游标SELECT * FROM orders WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-04-01 00:00:00 AND create_time 2024-03-20 15:30:00 -- 上一页最后一条的时间 ORDER BY create_time DESC LIMIT 20;这个方案依赖时间字段单调且唯一性可控如果同一时间大量重复需要加一个二级排序键比如 id一起做游标否则可能漏行或者重复。排序上还有个小细节ORDER BY create_time DESC如果能配合索引的有序性就可以避免额外排序。索引(create_time)本身就是有序的倒序扫描通常也能用上。但如果排序字段和索引不一致就会触发 filesort你可以用EXPLAIN里的Extra字段看到Using filesort这时候就要考虑调整索引或者排序字段。3.4 时区最容易在跨地域部署时爆发的坑时区问题在单机、单一地区部署时几乎不会暴露一旦服务器、数据库、客户端不在一个时区时间段查询就会莫名其妙偏移几个小时。核心原因是TIMESTAMP存储的是 UTC 时间戳读的时候会按当前会话时区转换而DATETIME存的是字面值不随时区变。我的处理原则是应用、数据库、连接串三处的时区保持一致并且在 JDBC 连接串里显式指定例如serverTimezoneAsia/Shanghai业务时间统一用LocalDateTime不带时区在大陆业务里已经够用如果系统跨时区就统一用 UTC 存、展示层再转本地。注意不要混用TIMESTAMP和DATETIME来存同一类业务时间否则某一天你会发现两列数据差了几个小时排查起来非常头疼。4. 高频故障现场与排查实录4.1 时间段查不到数据一张速查表我把选了时间段却没有返回预期数据这个最常见故障的原因整理成了速查表按出现频率从高到低排列现象可能原因排查方法结束日一整天数据缺失结束边界精度问题用了日期字符串或全闭区间检查 SQL 结束边界是否为次日零点、是否用整段时间都查不到时间字段类型与传入类型不一致隐式转换失败打印实际 SQL核对字段类型少几个小时时区不一致对比应用、数据库、连接串时区数据明显偏多开始边界写反或用了或运算检查、方向偶发查不到缓存未失效、读到旧数据检查缓存 TTL 和更新逻辑只有某些用户查不到数据本身不在该时间段换真实数据核对这张表我在团队里贴过很多次遇到类似反馈先按表过一遍基本能定位到八成问题。4.2 慢而且越来越慢性能排查路径性能类问题我会按这个顺序排先确认有没有索引再看执行计划的type和rows再看返回的数据量是不是过大最后看是不是被函数包裹了字段。-- 第一步字段有没有索引 SHOW INDEX FROM orders; -- 第二步执行计划看是否走索引 EXPLAIN SELECT id, order_no FROM orders WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-04-01 00:00:00; -- 第三步看实际返回行数和耗时 SELECT COUNT(*) FROM orders WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-04-01 00:00:00;如果count就几百万行那根本不是 SQL 的问题是业务需要限制时间范围或者走离线统计。我遇到过前端默认给用户开一个全部时间选项用户一点就查询整张表直接把数据库连接池打满。后来改成默认展示最近 7 天并提供导出功能承接大范围查询问题就解决了。4.3 跨天、跨月、跨年这些边界必须专门测边界我强烈建议用测试用例覆盖尤其是下面几种月末 31 号的查询、闰年 2 月 29 号、跨年 12 月 31 号到次年 1 月 1 号、夏令时切换日虽然大陆不涉及但海外业务会碰到、开始时间等于结束时间、开始时间晚于结束时间。用例写起来很简单比如验证 3 月整月查询用start 2024-03-01 00:00:00、end 2024-04-01 00:00:00插入 3 月 31 日 23:59:59 和 4 月 1 日 00:00:00 两条边界数据断言前者命中、后者不命中。这个测试能挡住绝大多数边界回归。4.4 定时任务里的时间段查询空指针和重复执行的坑时间段查询另一个高频出现的地方是定时任务。比如每天凌晨跑一次统计昨天一整天的数据任务里会构造start 昨天 00:00:00、end 今天 00:00:00。这里有两个坑。第一个坑是任务启动时缓存了时间参数第二天执行还在用第一天的时间导致统计了旧数据。解决办法是每次执行时重新计算时间边界不要放在静态字段或类加载时初始化。第二个坑是空指针。如果start或end因为某种原因没算出来直接进到 SQL 拼接里就会出问题。我一般会在进入查询前加一道断言LocalDate today LocalDate.now(); LocalDateTime start today.minusDays(1).atStartOfDay(); LocalDateTime end today.atStartOfDay(); Objects.requireNonNull(start, 统计开始时间不能为空); Objects.requireNonNull(end, 统计结束时间不能为空);同时在任务层面加去重锁或者记录执行日志避免任务重试或集群多节点同时跑导致数据重复计算。5. 一些实战经验与参数取舍建议5.1 默认时间段到底给多大这是个产品和技术都要一起定的问题。我踩过的坑是默认给全部时间结果列表页第一屏就把数据库拖慢。后来统一改成默认最近 7 天或最近 30 天根据业务数据产生速度调整订单、交易类默认 7 天通知、日志类默认 1 天报表类默认 30 天。用户需要更长范围时再手动选择并且对范围做上限限制。经验值是单次查询预估命中行数超过几十万时就应该考虑换方案比如强制缩短范围、转异步导出、或者走预聚合表。5.2 大数据量下的分段查询思路当数据量真的需要查很久时我常用的两种方案。一种是分段拉取把时间段切成若干小段每段单独查询再合并好处是每段都能用上索引、内存占用可控缺点是并发控制麻烦。另一种是用时间游标顺序扫描适合导出、同步这类场景从最早时间开始每次取一批用上一批的最后一个时间继续往下走。-- 时间游标式分页 SELECT * FROM orders WHERE create_time :lastTime ORDER BY create_time ASC LIMIT 1000;取回之后把本批最后一条的create_time作为下一批的lastTime直到某批返回空为止。这种方式不依赖offset深翻页性能稳定是我在数据同步和导出里用得最多的做法。5.3 时间格式化展示层的事不要塞给数据库最后再说一个经常被忽略的点。很多人喜欢在 SQL 里做格式化比如DATE_FORMAT(create_time, %Y-%m-%d %H:%i:%s)觉得省事。这样做有两个坏处一是前面说的破坏索引二是把展示逻辑耦合进了数据访问层接口一旦要换个格式就得改 SQL。更合理的做法是查询只负责取出原始时间字段格式化交给应用层或前端。Java 里用DateTimeFormatter.ofPattern(yyyy-MM-dd HH:mm:ss)把LocalDateTime转成字符串前端再按需展示。这样职责清晰索引也能正常走。需要注意的是有些特殊格式比如不同地区不同的日期展示方式还是放在前端更合适后端给的应该是标准格式或者时间戳避免为每个客户端定制。5.4 几个长期有效的操作习惯最后分享几个我坚持了很多年、也确实省过很多事的习惯。第一所有时间段接口在日志里打印实际使用的开始和结束边界值出问题时不用猜。第二任何涉及时间比较的字段建索引前先在真实数据量级上跑一遍EXPLAIN。第三边界测试用例固定保留每次改动这块逻辑都跑一遍。第四团队内把左闭右开、结束边界取次日零点写进开发规范减少口头约定带来的偏差。我在实际排查中发现时间段查询出的问题八成都不是数据结构多复杂而是边界、时区、索引这老三样。把这三样守住剩下的就是按业务需要做取舍了。要我说这套东西真正的价值不在代码多聪明而在于每次上线都能少一次为什么少了一天数据的深夜排查。 时间段查询避坑指南：SQL边界、索引优化与时区处理
筛选时间区间这件事我在项目里踩过的坑比很多人想象的多得多。刚入行那会儿我觉得这有什么难的无非就是一个where加上两个时间条件写出来能跑就行。后来做订单系统、做日志系统、做设备运行记录系统才发现同样是根据时间字段查询指定时间段的数据写得好和写得烂在数据量小的时候完全看不出差别一旦单表到了几百万上千万行性能差距能拉开几十倍。这篇文章我想把这件事从头到尾捋一遍它到底在解决什么问题哪些时间字段类型是常见坑SQL 怎么写边界才不出错后端从参数接收到 ORM 拼 SQL 再到分页排序该怎么处理最后再把我实际遇到过的典型故障现场还原出来。不管你是刚接触后端的新人还是已经写了几年代码但对时间查询仍然心里没底的老手这篇应该都能找到能直接抄的部分。1. 为什么时间段查询看着简单实际上坑最多1.1 这个需求到底难在哪查询指定时间段的数据这句话翻译成技术语言其实是三件事叠在一起第一确定时间字段是什么类型、以什么精度存储第二确定边界的开闭规则也就是开始时间和结束时间到底含不含第三确定用什么手段让它在大数据量下还能跑得动。绝大多数人只做了第一件的表面工作第二件事靠差不多第三件事完全没考虑。我先说一个非常典型的场景。一个订单列表页用户选择2024-03-01 到 2024-03-31前端传过来的可能是2024-03-01 00:00:00和2024-03-31 00:00:00也可能是日期字符串2024-03-01和2024-03-31。如果是后面这种后端直接拿它去和DATETIME字段比较那整个 3 月 31 号这一天 00:00:00 之后产生的数据全都会被漏掉因为2024-03-31 10:22:00显然大于2024-03-31 00:00:00。用户会投诉说我明明选了 31 号怎么没有数据你查日志发现 SQL 没报错、接口没异常问题就出在这一天的精度差上。这类问题不是技术难度问题是严谨度问题。时间段查询真正的难点从来不是把 SQL 写出来而是把所有边界、时区、精度、性能的组合情况都想到并且处理掉。1.2 时间字段的三种典型存储形态在聊 SQL 之前得先统一认知数据库里时间字段的存储方式直接决定了查询怎么写。我见过的项目里主要有三种。第一种是原生的日期时间类型MySQL 里常见的是DATETIME和TIMESTAMPPostgreSQL 是timestamp和timestamptzOracle 是DATE和TIMESTAMP。这种写法可读性最好直接用 SQL 比较就行但要注意TIMESTAMP范围只到 2038 年而且受会话时区影响。第二种是用 BIGINT 存毫秒时间戳或者秒时间戳。这种在日志、埋点、消息系统里很常见好处是跨时区不歧义、精度高、比较运算快坏处是直接看数据库根本看不懂必须心里换算成时间。第三种是用字符串存yyyy-MM-dd HH:mm:ss。这种最坑我强烈建议能改就改。字符串比较虽然字典序在标准格式下恰好等价于时间先后但一旦格式不统一比如有的存2024-3-1有的存2024-03-01比较结果就完全是错的。下面这张表是我自己对三种形态的取舍总结可以直接拿去和团队讨论存储形态可读性跨时区索引效率推荐场景DATETIME高需约定时区高业务主数据、订单、表单TIMESTAMP中自动换算高创建/更新时间注意 2038BIGINT 时间戳低无歧义极高日志、埋点、海量事件流字符串高无低仅历史遗留不建议新项目用选型上没有绝对对错核心是团队内统一并且在接口层做一次明确的转换不要让前端传什么就直接往 SQL 里塞什么。1.3 边界问题左闭右开还是全闭这是我在代码评审里最常挑的一个点。时间段查询的边界数学上无非四种组合左闭右闭、左闭右开、左开右闭、左开右开。业务上几乎所有人想要的都是从开始时间到结束时间之间的数据但表达成代码时经常写错。我的建议是统一采用左闭右开time start AND time end。这里的end不是用户选择的结束日而是结束日的下一天零点。比如用户选 3 月 1 日到 3 月 31 日你真正传进 SQL 的应该是start 2024-03-01 00:00:00、end 2024-04-01 00:00:00然后条件写成time start AND time end。为什么推荐左闭右开而不是BETWEEN因为BETWEEN是闭区间等价于 AND 当字段精度是秒的时候 2024-03-31 00:00:00只包含到 31 号零点整这一秒还是漏了后面 23 小时 59 分 59 秒。只有把结束边界改成次日零点并用才在数学上无歧义地覆盖整段。这个规则一旦定下来跨天、跨月、跨年都自动正确不需要为每个月天数不同单独处理。提示定下左闭右开的规则后前后端、测试用例、文档都要同步否则前端按全闭逻辑理解又会重新引入偏差。2. 数据库层面的时间段查询怎么写才对2.1 BETWEEN、 AND 到底选哪个先把结论放前面能用 AND 就用它BETWEEN只在你非常确定字段精度和业务语义时使用。原因前面说过了BETWEEN的闭区间在秒级、毫秒级精度下天然容易漏数据而且很多人写BETWEEN时会顺手写成日期字符串触发隐式类型转换。来看一段实际的 SQL 对比-- 错误示范结束日是用户选择的日期精度丢失 SELECT * FROM orders WHERE create_time BETWEEN 2024-03-01 AND 2024-03-31; -- 正确示范结束边界用次日零点左闭右开 SELECT id, order_no, create_time, amount FROM orders WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-04-01 00:00:00;第二段不仅语义正确而且因为两个边界都是范围查询配合create_time上的索引可以高效地定位到一段连续的索引区间。第一段在某些 MySQL 版本下会因为2024-03-31被隐式转换成2024-03-31 00:00:00你会少查一整天。2.2 索引为什么会失效函数包裹字段的代价我见过太多这样写的 SQL-- 函数包裹字段索引直接失效 SELECT * FROM orders WHERE DATE(create_time) 2024-03-15; -- 或者用年月函数 SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m) 2024-03;DATE(create_time)和DATE_FORMAT(...)都把字段包在了函数里MySQL 没法用create_time索引去范围定位只能对每一行求值再比较也就是全表扫描。数据量小的时候你没感觉几百万行的时候就是秒级甚至分钟级的等待。正确的写法还是还原成范围-- 查某一天 SELECT * FROM orders WHERE create_time 2024-03-15 00:00:00 AND create_time 2024-03-16 00:00:00; -- 查某个月 SELECT * FROM orders WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-04-01 00:00:00;这里的原则很简单让字段裸奔把计算放到常量上。你要查哪一天、哪个月就在应用层算好边界值再传进去不要让数据库在字段上做函数运算。判断索引有没有生效最直接的就是看执行计划EXPLAIN SELECT * FROM orders WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-04-01 00:00:00;重点看type是不是range、key是不是用到了create_time索引、rows预估行数是多少。如果type是ALL那基本就是全表扫描了必须改。2.3 联合索引里时间字段该放哪一位单列时间索引好办麻烦的是联合索引。比如订单表经常按用户 时间段查索引是(user_id, create_time)还是(create_time, user_id)结果差别很大。如果查询几乎总是带user_id再叠加时间范围那(user_id, create_time)是最优的先用user_id等值定位再在剩下的区间里用create_time做范围约减。反过来如果索引是(create_time, user_id)时间范围一出现后面的user_id就用不上最左前缀了效果差很多。但如果查询有两种形态一种只按时间一种按用户加时间那就得权衡。我一般的做法是主查询带上用户维度就建(user_id, create_time)只按时间的报表类查询单独走另一个索引或者走读库、走离线。另外提醒一句联合索引里范围条件字段后面的列一般无法再用于索引快速过滤只能作为覆盖索引的一部分。这个规律记住能帮你少走很多弯路。2.4 慢查询日志定位时间段查询性能问题的第一手资料当线上出现某个时间段查询特别慢的反馈时不要凭猜先看慢查询日志。MySQL 默认long_query_time是 10 秒这个阈值对现代业务太宽了我会调成 1 秒甚至更低把慢查询都记下来-- 查看当前慢查询配置 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 开启慢查询日志按需生产环境注意磁盘 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;记下来之后用mysqldumpslow或者 pt-query-digest 做聚合看哪条时间段查询出现次数最多、平均耗时最长。经验上时间段查询慢九成是三个原因没索引、索引被函数破坏、查的时间范围太宽导致回表太多。逐个排查就行。3. 后端接口与 ORM 层的时间段查询落地3.1 参数接收与格式校验接口层我建议收两种形式的参数要么前后端约定统一传标准格式字符串yyyy-MM-dd HH:mm:ss要么传开始时间和结束时间两个字段并明确标注含义。坚决不要收一个关键词猜时间这种模糊接口。用 Java 举例接收时用LocalDateTime或LocalDate搭配DateTimeFormat、JsonFormat显式指定格式避免依赖默认行为public class TimeRangeQuery { DateTimeFormat(pattern yyyy-MM-dd HH:mm:ss) private LocalDateTime startTime; DateTimeFormat(pattern yyyy-MM-dd HH:mm:ss) private LocalDateTime endTime; // 省略 getter/setter }这里有个老生常谈但每年都有人踩的坑SimpleDateFormat不是线程安全的曾经有项目在静态字段里共享一个SimpleDateFormat高并发下解析出莫名其妙的时间。现在都用DateTimeFormatter它是不可变且线程安全的LocalDate、LocalDateTime也是不可变的能省掉一大堆并发麻烦。校验上至少要拦三件事开始时间不能晚于结束时间否则 SQL 结果恒空属于静默错误时间范围不能过大比如一次查三年这种请求要拒绝或者转为异步导出格式必须合法不合法直接返回参数错误不要让它进到 SQL 里。3.2 MyBatis 与 MyBatis-Plus 的动态条件用 MyBatis 写时间段条件标准姿势是if判断加两个独立条件不要用BETWEEN一把梭select idselectByTimeRange resultMapOrderMap SELECT id, order_no, create_time, amount FROM orders where if teststartTime ! null AND create_time gt; #{startTime} /if if testendTime ! null AND create_time lt; #{endTime} /if if teststatus ! null AND status #{status} /if /where ORDER BY create_time DESC LIMIT #{offset}, #{size} /select这样写的好处是开始、结束、状态都可以独立缺省组合灵活而且每个条件都是对字段裸比较不会破坏索引。注意 XML 里、要转义成lt;、gt;或者用 CDATA 包起来否则会解析报错。MyBatis-Plus 的QueryWrapper写时间段更简洁QueryWrapperOrder wrapper new QueryWrapper(); wrapper.ge(startTime ! null, create_time, startTime) .lt(endTime ! null, create_time, endTime) .eq(status ! null, status, status) .orderByDesc(create_time);ge是大于等于、lt是小于前面的布尔参数控制该条件是否加入正好对上某个条件可能为空的场景。这里唯一的注意点是字段名要用数据库列名而不是实体属性名混用会导致列找不到。3.3 分页与排序深分页是时间段查询的老对手时间段查询加上分页深分页问题就来了。假设按时间倒序翻到第 10000 页SQL 是SELECT * FROM orders WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-04-01 00:00:00 ORDER BY create_time DESC LIMIT 100000, 20;MySQL 要把满足条件的 100020 行都读出来再丢掉前 100000 行越翻到后面越慢。优化思路是用上一页最后一条的时间做游标SELECT * FROM orders WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-04-01 00:00:00 AND create_time 2024-03-20 15:30:00 -- 上一页最后一条的时间 ORDER BY create_time DESC LIMIT 20;这个方案依赖时间字段单调且唯一性可控如果同一时间大量重复需要加一个二级排序键比如 id一起做游标否则可能漏行或者重复。排序上还有个小细节ORDER BY create_time DESC如果能配合索引的有序性就可以避免额外排序。索引(create_time)本身就是有序的倒序扫描通常也能用上。但如果排序字段和索引不一致就会触发 filesort你可以用EXPLAIN里的Extra字段看到Using filesort这时候就要考虑调整索引或者排序字段。3.4 时区最容易在跨地域部署时爆发的坑时区问题在单机、单一地区部署时几乎不会暴露一旦服务器、数据库、客户端不在一个时区时间段查询就会莫名其妙偏移几个小时。核心原因是TIMESTAMP存储的是 UTC 时间戳读的时候会按当前会话时区转换而DATETIME存的是字面值不随时区变。我的处理原则是应用、数据库、连接串三处的时区保持一致并且在 JDBC 连接串里显式指定例如serverTimezoneAsia/Shanghai业务时间统一用LocalDateTime不带时区在大陆业务里已经够用如果系统跨时区就统一用 UTC 存、展示层再转本地。注意不要混用TIMESTAMP和DATETIME来存同一类业务时间否则某一天你会发现两列数据差了几个小时排查起来非常头疼。4. 高频故障现场与排查实录4.1 时间段查不到数据一张速查表我把选了时间段却没有返回预期数据这个最常见故障的原因整理成了速查表按出现频率从高到低排列现象可能原因排查方法结束日一整天数据缺失结束边界精度问题用了日期字符串或全闭区间检查 SQL 结束边界是否为次日零点、是否用整段时间都查不到时间字段类型与传入类型不一致隐式转换失败打印实际 SQL核对字段类型少几个小时时区不一致对比应用、数据库、连接串时区数据明显偏多开始边界写反或用了或运算检查、方向偶发查不到缓存未失效、读到旧数据检查缓存 TTL 和更新逻辑只有某些用户查不到数据本身不在该时间段换真实数据核对这张表我在团队里贴过很多次遇到类似反馈先按表过一遍基本能定位到八成问题。4.2 慢而且越来越慢性能排查路径性能类问题我会按这个顺序排先确认有没有索引再看执行计划的type和rows再看返回的数据量是不是过大最后看是不是被函数包裹了字段。-- 第一步字段有没有索引 SHOW INDEX FROM orders; -- 第二步执行计划看是否走索引 EXPLAIN SELECT id, order_no FROM orders WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-04-01 00:00:00; -- 第三步看实际返回行数和耗时 SELECT COUNT(*) FROM orders WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-04-01 00:00:00;如果count就几百万行那根本不是 SQL 的问题是业务需要限制时间范围或者走离线统计。我遇到过前端默认给用户开一个全部时间选项用户一点就查询整张表直接把数据库连接池打满。后来改成默认展示最近 7 天并提供导出功能承接大范围查询问题就解决了。4.3 跨天、跨月、跨年这些边界必须专门测边界我强烈建议用测试用例覆盖尤其是下面几种月末 31 号的查询、闰年 2 月 29 号、跨年 12 月 31 号到次年 1 月 1 号、夏令时切换日虽然大陆不涉及但海外业务会碰到、开始时间等于结束时间、开始时间晚于结束时间。用例写起来很简单比如验证 3 月整月查询用start 2024-03-01 00:00:00、end 2024-04-01 00:00:00插入 3 月 31 日 23:59:59 和 4 月 1 日 00:00:00 两条边界数据断言前者命中、后者不命中。这个测试能挡住绝大多数边界回归。4.4 定时任务里的时间段查询空指针和重复执行的坑时间段查询另一个高频出现的地方是定时任务。比如每天凌晨跑一次统计昨天一整天的数据任务里会构造start 昨天 00:00:00、end 今天 00:00:00。这里有两个坑。第一个坑是任务启动时缓存了时间参数第二天执行还在用第一天的时间导致统计了旧数据。解决办法是每次执行时重新计算时间边界不要放在静态字段或类加载时初始化。第二个坑是空指针。如果start或end因为某种原因没算出来直接进到 SQL 拼接里就会出问题。我一般会在进入查询前加一道断言LocalDate today LocalDate.now(); LocalDateTime start today.minusDays(1).atStartOfDay(); LocalDateTime end today.atStartOfDay(); Objects.requireNonNull(start, 统计开始时间不能为空); Objects.requireNonNull(end, 统计结束时间不能为空);同时在任务层面加去重锁或者记录执行日志避免任务重试或集群多节点同时跑导致数据重复计算。5. 一些实战经验与参数取舍建议5.1 默认时间段到底给多大这是个产品和技术都要一起定的问题。我踩过的坑是默认给全部时间结果列表页第一屏就把数据库拖慢。后来统一改成默认最近 7 天或最近 30 天根据业务数据产生速度调整订单、交易类默认 7 天通知、日志类默认 1 天报表类默认 30 天。用户需要更长范围时再手动选择并且对范围做上限限制。经验值是单次查询预估命中行数超过几十万时就应该考虑换方案比如强制缩短范围、转异步导出、或者走预聚合表。5.2 大数据量下的分段查询思路当数据量真的需要查很久时我常用的两种方案。一种是分段拉取把时间段切成若干小段每段单独查询再合并好处是每段都能用上索引、内存占用可控缺点是并发控制麻烦。另一种是用时间游标顺序扫描适合导出、同步这类场景从最早时间开始每次取一批用上一批的最后一个时间继续往下走。-- 时间游标式分页 SELECT * FROM orders WHERE create_time :lastTime ORDER BY create_time ASC LIMIT 1000;取回之后把本批最后一条的create_time作为下一批的lastTime直到某批返回空为止。这种方式不依赖offset深翻页性能稳定是我在数据同步和导出里用得最多的做法。5.3 时间格式化展示层的事不要塞给数据库最后再说一个经常被忽略的点。很多人喜欢在 SQL 里做格式化比如DATE_FORMAT(create_time, %Y-%m-%d %H:%i:%s)觉得省事。这样做有两个坏处一是前面说的破坏索引二是把展示逻辑耦合进了数据访问层接口一旦要换个格式就得改 SQL。更合理的做法是查询只负责取出原始时间字段格式化交给应用层或前端。Java 里用DateTimeFormatter.ofPattern(yyyy-MM-dd HH:mm:ss)把LocalDateTime转成字符串前端再按需展示。这样职责清晰索引也能正常走。需要注意的是有些特殊格式比如不同地区不同的日期展示方式还是放在前端更合适后端给的应该是标准格式或者时间戳避免为每个客户端定制。5.4 几个长期有效的操作习惯最后分享几个我坚持了很多年、也确实省过很多事的习惯。第一所有时间段接口在日志里打印实际使用的开始和结束边界值出问题时不用猜。第二任何涉及时间比较的字段建索引前先在真实数据量级上跑一遍EXPLAIN。第三边界测试用例固定保留每次改动这块逻辑都跑一遍。第四团队内把左闭右开、结束边界取次日零点写进开发规范减少口头约定带来的偏差。我在实际排查中发现时间段查询出的问题八成都不是数据结构多复杂而是边界、时区、索引这老三样。把这三样守住剩下的就是按业务需要做取舍了。要我说这套东西真正的价值不在代码多聪明而在于每次上线都能少一次为什么少了一天数据的深夜排查。