上周排查一个线上问题用户反馈昨天的订单一条都没查到但数据库里明明躺着两千多条。最后定位下来不是数据丢了也不是接口挂了而是那个查询条件把时间段写成了 2024-05-20 00:00:00 AND 2024-05-20 00:00:00——起止时间一模一样区间宽度为零。这种一看就想笑、笑完又想哭的错误在时间字段查询这件事上几乎每天都在发生。围绕时间字段做指定时间段的查询看起来是最基础的 SQL 操作实际上它牵扯的东西比想象中多得多区间的开闭语义、日期与时间戳的类型差异、时区偏移、函数包字段导致的索引失效、Java 侧格式化时的线程安全、深分页下的性能塌方。这篇文章不打算写成教科书我想把这几年在订单、日志、监控、报表这几类场景里踩过的时间坑系统性地捋一遍把每一步为什么这么写讲透。无论你是刚开始写 CRUD 的新人还是带团队做数据平台的老手下面这些内容应该都能直接拿去对照自己的代码。1. 时间段到底是什么先把区间语义对齐再动手写SQL很多人写时间查询是手感驱动——看到需求说查5月20日的数据条件里就顺手敲个BETWEEN 2024-05-20 AND 2024-05-20。跑出来结果是空的还以为是没数据。问题的根子不在语法在于时间段这个词本身就有歧义业务方说的和数据库理解的往往不是一回事。1.1 左闭右开是工程上最省心的选择先明确一个术语。所谓时间段数学上是区间工程上必须指定端点是否包含区间写法含义是否包含起点是否包含终点[start, end]闭区间是是[start, end)左闭右开是否(start, end)开区间否否我推荐绝大多数场景用[start, end)也就是左闭右开。理由有三条都是实打实踩出来的。第一条它能天然避开最后一秒的精度问题。假设你存的是DATETIME(3)毫秒精度想让5月20日全天完整覆盖写 2024-05-20 23:59:59会漏掉23:59:59.500这条记录。你可能想改写成 2024-05-20 23:59:59.999但字段精度一旦提升到微秒这个魔数又不够看了。而写成 2024-05-21 00:00:00无论精度怎么变都不会漏。第二条连续区间拼接时不会重叠。做日报、月报的时候相邻两天的区间如果是闭区间23:59:59这个点会被两天的报表各统计一次数字对不上。左闭右开则严丝合缝前一天的终点就是后一天的起点不重不漏。第三条它跟 Java 里java.time的很多 API 语义一致像LocalDate.plusDays(1).atStartOfDay()这种写法能直接算出上界代码读起来也顺。提示如果业务方明确要求包含终点比如按自然日统计且数据精确到秒那就退化为闭区间但务必在接口文档里写明end 参数含当秒让调用方知道边界行为。1.2 日期字段和日期时间字段处理逻辑完全不同数据库里的时间字段大致分三档写法差别很大。第一档是纯日期DATE只到天。这时候做范围查询其实是字符串或整数比较BETWEEN 2024-05-01 AND 2024-05-20是安全的因为不存在时分秒端点语义清晰。第二档是日期时间DATETIME / TIMESTAMP精确到秒、毫秒甚至微秒。这才是坑最多的地方必须用 起点 AND 终点1天的写法。第三档是 Unix 时间戳BIGINT秒或者毫秒。这类字段看起来最土实际上最好处理没有格式、没有时区、没有精度歧义直接比大小。代价是可读性差你拿1716134400000是看不出这是哪天的调试时得先转换。我个人的经验是日志、埋点、监控这类高频写入且只用于范围过滤的数据用毫秒时间戳最省心业务单据用 DATETIME方便人工查库和导出。混用是最糟糕的同一个库里两种都有写查询的人分分钟搞错。1.3 时区那个让你数据凭空少8小时的隐藏变量这是最隐蔽的一类 bug而且往往在测试环境发现不了。MySQL 的TIMESTAMP类型在存储时会把值转成 UTC读取时再按会话时区转回来DATETIME则原样存储不做任何转换。如果你的 JDBC 连接串里serverTimezone没配对Java 里2024-05-20 10:00:00写进去、读出来可能变成2024-05-20 02:00:00整整差 8 小时——跨天查询就此全线崩盘。排查这类问题有个笨但有效的办法写一条SELECT NOW(), session.time_zone, global.time_zone;看数据库自己认为现在几点再跟应用服务器的时间对一下。三边对不上别急着改业务代码先把时区配置统一了。我的建议是能不用TIMESTAMP就别用老老实实DATETIME时区转换全部放到应用层做。这样数据库的行为是可预测的出问题时责任边界也清楚。2. 主流数据库的时间区间写法对照别把MySQL的习惯带到Oracle写 SQL 的人如果不是多数据库背景很容易把一家的语法硬套到另一家。MySQL 的宽容度特别高很多写法看起来能跑到了 PostgreSQL 或 Oracle 直接报错。这一节把常见写法对照着列一遍。2.1 MySQLBETWEEN 能用但要小心隐式转换最直白的写法SELECT id, order_no, created_at FROM t_order WHERE created_at 2024-05-20 00:00:00 AND created_at 2024-05-21 00:00:00;这是我最推荐的形态。created_at字段前面不加任何函数字符串常量会被 MySQL 隐式转成DATETIME再比较索引能正常走范围扫描。要警惕的是BETWEEN。它等价于 AND 两端都闭用在纯 DATE 字段上没问题用在 DATETIME 上就要想清楚终点给什么值-- 危险会漏掉 2024-05-20 当天 00:00:00 之后的所有数据 -- 因为两端都是 00:00:00区间宽度为零 WHERE created_at BETWEEN 2024-05-20 00:00:00 AND 2024-05-20 00:00:00 -- 可用但不够优雅依赖秒级精度毫秒数据会漏 WHERE created_at BETWEEN 2024-05-20 00:00:00 AND 2024-05-20 23:59:59还有一个常见的坏习惯用DATE_FORMAT把字段截断到天再比较-- 别这么写 WHERE DATE_FORMAT(created_at, %Y-%m-%d) 2024-05-20它在语义上没问题但DATE_FORMAT(created_at, ...)是个函数表达式MySQL 无法用它去走created_at上的索引只能全表扫描。数据量上万还能忍上千万就是灾难。2.2 OracleTO_DATE、TRUNC 与字符串比较的顺序Oracle 对类型的要求严格得多字符串和日期之间不会那么随意地隐式转换。正确姿势是显式转换SELECT id, order_no, created_at FROM t_order WHERE created_at TO_DATE(2024-05-20 00:00:00, YYYY-MM-DD HH24:MI:SS) AND created_at TO_DATE(2024-05-21 00:00:00, YYYY-MM-DD HH24:MI:SS);如果需要按天聚合或者按天做等值判断Oracle 里有TRUNC-- 按天等于但同样会破坏 created_at 上的普通索引 WHERE TRUNC(created_at) DATE 2024-05-20跟 MySQL 一样TRUNC(created_at)会让常规 B-Tree 索引失效。真要在 Oracle 里做这种查询标准做法是建函数索引CREATE INDEX idx_order_trunc_created ON t_order(TRUNC(created_at));。这是一条很实用的经验——当你不得不在字段上套函数时去建对应的函数索引而不是每次都全表扫。另外提醒一句Oracle 的DATE类型自带时分秒只是显示时不带很多人误以为它只存日期。这也是为什么WHERE created_at DATE 2024-05-20往往查不到数据的真正原因——它等价于跟2024-05-20 00:00:00比相等。2.3 参数化查询把拼接字符串这条路彻底堵死不管哪家数据库时间条件都不该用字符串拼接。除了 SQL 注入风险还有一个纯技术原因手工拼出来的时间字符串格式五花八门2024/5/20、2024-05-20、20240520都有人写一旦跟数据库期望的格式对不上要么报错要么被悄悄转成一个错误的值。Java 里用PreparedStatement或者 MyBatis 的#{}让驱动去处理类型转换// JDBC 原生写法 String sql SELECT id, order_no FROM t_order WHERE created_at ? AND created_at ?; try (PreparedStatement ps conn.prepareStatement(sql)) { ps.setTimestamp(1, Timestamp.valueOf(start)); ps.setTimestamp(2, Timestamp.valueOf(end)); try (ResultSet rs ps.executeQuery()) { // 处理结果 } }setTimestamp会带上时区信息交给驱动处理比你自己拼字符串可靠一个数量级。这个习惯值不值得养成我用一句话总结只要参数是用户传进来的就永远用占位符没有例外。3. 索引为什么没生效时间区间查询的执行计划拆解同样一条时间范围查询有人跑 20 毫秒有人跑 20 秒。差别通常不在 SQL 写得漂不漂亮而在索引有没有被用上。这一节不讲索引原理教科书讲怎么看出来它没用上、以及为什么没用上。3.1 先学会看 EXPLAIN别靠猜MySQL 里执行计划就是照妖镜EXPLAIN SELECT id, order_no, created_at FROM t_order WHERE created_at 2024-05-20 00:00:00 AND created_at 2024-05-21 00:00:00;重点看三列列名期望值含义typerange或ref访问类型出现ALL就是全表扫key索引名非 NULL实际用到的索引rows尽量小预估扫描行数如果type是ALL、key是NULL基本可以断定索引没用上。接下来的任务是找出为什么。3.2 函数包字段、隐式类型转换、前导通配符三大元凶我把这些年遇到的索引失效原因归了归类时间查询相关的九成出在前两条。原因一字段上套了函数。WHERE DATE_FORMAT(created_at, %Y-%m-%d) 2024-05-20、WHERE YEAR(created_at) 2024、WHERE DATE_ADD(created_at, INTERVAL 1 DAY) NOW()全是这一类。B-Tree 索引是按字段原始值排序的你把排序依据改了索引就没法用来定位了。原因二隐式类型转换。这条最阴险。假设created_at是字符串类型有些老系统真这么干你写WHERE created_at 20240520000000数字MySQL 会把字段转成数字再比索引直接废掉。反过来字段是 DATETIME你传了个格式不对的字符串也可能触发转换。原因三范围条件太多。联合索引(user_id, created_at)里如果既对user_id做范围查询、又对created_at做范围查询索引只能用到第一个范围列。这是 B-Tree 索引的固有特性不是配置问题。正确做法是让等值条件排在联合索引前面范围条件放最后。排查的顺序我一般是这样的先EXPLAIN确认key是否为空再看 SQL 里字段有没有被函数包住再对一遍字段类型和参数类型是否一致最后看联合索引的列顺序。3.3 慢查询日志定位那些你没意识到的区间有些区间查询问题不在 SQL 本身而在区间开得太大。比如有人把默认查询范围设成了一年用户打开页面就触发一次全表扫描。这种问题在开发环境数据少时完全看不出来。打开慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;跑一段时间后用mysqldumpslow或者pt-query-digest汇总按总耗时排序而不是按单次耗时排序。你会发现真正拖垮数据库的往往不是某条特别慢的语句而是某条中速语句被调用了十万次。我在一个订单系统里就这么干过一次单个时间区间查询耗时 0.8 秒没超过 1 秒的慢查询阈值所以一直没被记录。但报表页每刷新一次要塞 20 个这样的查询累计 16 秒。把它挑出来加索引之后整体响应从 18 秒降到 1.2 秒。这个案例我一直拿来讲慢查询日志的阈值别卡得太死或者干脆按平均耗时排序来找高频中速语句。4. Java 侧的时间处理从参数接收到结果序列化的全链路SQL 只是时间链路的一半。请求参数从 HTTP 进来是字符串进入 Service 要变成时间对象传给 DAO 要变成正确的类型查回来要格式化给前端。任何一环出错最终用户看到的就是数据不对。4.1 Java 时间类型选型别再新建 Date 了老代码里满是java.util.Date和SimpleDateFormat这套 API 有两个硬伤第一SimpleDateFormat不是线程安全的。它在内部维护了一个可变的Calendar多线程下会串数据。我见过线上因为共用一个静态SimpleDateFormat实例导致时间格式化结果随机错乱的案例排查了两天才定位到。第二Date的语义含糊。它既表示日期又表示时刻getYear()返回的是年份减 1900getMonth()从 0 开始用一次骂一次。新代码一律用java.timeDateTimeFormatter FMT DateTimeFormatter.ofPattern(yyyy-MM-dd HH:mm:ss); // 用户传 2024-05-20解析成当天起点 LocalDate day LocalDate.parse(2024-05-20); LocalDateTime start day.atStartOfDay(); LocalDateTime end day.plusDays(1).atStartOfDay(); // 格式化回字符串 String startStr start.format(FMT);DateTimeFormatter是不可变对象天生线程安全可以放心当静态常量用。LocalDate.plusDays(1)计算次日零点自动处理月末、年末、闰年比自己写if (month 12)靠谱得多。如果参数带时区跨区域业务用ZonedDateTime或者OffsetDateTime明确指定ZoneId.of(Asia/Shanghai)别依赖系统默认时区。4.2 MyBatis 与 MyBatis-Plus 里怎么拼时间条件MyBatis 里最常规的写法是用if做动态条件select idselectByRange resultTypeOrder SELECT id, order_no, created_at FROM t_order WHERE 1 1 if teststart ! null AND created_at gt; #{start} /if if testend ! null AND created_at lt; #{end} /if /select注意和在 XML 里要转义成gt;和lt;或者包在![CDATA[ ]]里否则解析器会报错。MyBatis-Plus 的QueryWrapper更贴近 Java 思维LambdaQueryWrapperOrder wrapper new LambdaQueryWrapper(); wrapper.ge(Order::getCreatedAt, start) .lt(Order::getCreatedAt, end) .orderByDesc(Order::getCreatedAt);ge和lt就是和跟前面讲的左闭右开语义正好对上。要留意的是lt传进来的end必须已经算好是次日零点别在业务代码里传个23:59:59然后指望框架帮你补。顺带提一个高频错误用 MyBatis 的if teststart ! null判断时如果参数是字符串而不是时间对象空字符串会通过! null检查然后被拼进 SQL变成一个非法的时间常量直接报错。稳妥的写法是判断类型后再判断内容或者干脆在 Controller 层用DateTimeFormat注解把字符串直接绑定成LocalDateTime。4.3 返回给前端的时间格式统一在序列化层做查出来的created_at默认会被序列化成 ISO 格式或者时间戳数组前端拿到一脸懵。我的做法是在配置里统一指定Bean public Jackson2ObjectMapperBuilderCustomizer jacksonCustomizer() { return builder - { DateTimeFormatter fmt DateTimeFormatter.ofPattern(yyyy-MM-dd HH:mm:ss); builder.serializerByType(LocalDateTime.class, new LocalDateTimeSerializer(fmt)); builder.deserializerByType(LocalDateTime.class, new LocalDateTimeDeserializer(fmt)); }; }这样做的好处是入参出参格式一致前端不需要额外处理。踩过的坑是一旦全局配了格式所有接口都得遵守特殊接口想用别的格式只能单独加注解。所以配置之前最好跟前端对一次别各写各的。5. 分页、聚合与统计时间区间查询的三种进阶形态基础的等值范围查询写熟了之后真实的业务需求往往更复杂。这一节挑三个最常见的进阶场景。5.1 大区间分页深分页为什么会越来越慢用户查最近一年的订单还要翻到第 500 页。SQL 大概长这样SELECT id, order_no, created_at FROM t_order WHERE created_at 2023-05-20 AND created_at 2024-05-20 ORDER BY created_at DESC LIMIT 10000, 20;LIMIT 10000, 20的含义是先按顺序取出 10020 行再丢掉前 10000 行。翻到后面页扫描量线性增长速度越来越慢。改善的办法是游标分页用上一页最后一条记录的时间作为下一页的起点SELECT id, order_no, created_at FROM t_order WHERE created_at 2023-05-20 AND created_at 2024-05-20 AND created_at 2024-03-15 08:30:12 -- 上一页最后一条的时间 ORDER BY created_at DESC LIMIT 20;这样每次查询都只扫 20 行跟页码无关。代价是不能跳页只能上一页下一页。订单流水、操作日志这种按时间倒序浏览的场景游标分页完美适配。但如果是跳到第 87 页看某个具体订单这种需求游标分页就不合适了得换个思路。还有一个小窍门如果时间字段上有索引且区分度高可以考虑把ORDER BY created_at换成ORDER BY id前提是主键自增且与时间正相关MySQL 优化器有时能因此选择更优的路径。5.2 按天聚合时间桶怎么切才不会缺数据报表需求经常是按天统计订单量SELECT DATE(created_at) AS day, COUNT(*) AS cnt FROM t_order WHERE created_at 2024-05-01 AND created_at 2024-06-01 GROUP BY DATE(created_at) ORDER BY day;这段 SQL 本身没问题但有两个隐忧。其一DATE(created_at)无法用索引聚合必然全扫。如果表很大而查询频率又高正确做法是建一张按天预聚合的统计表定时任务每天凌晨跑一次报表直接读结果。用空间换时间这是报表场景的通行做法。其二没有数据的那一天不会出现在结果里。5月1日到5月31日如果5月10日零订单结果集里就没有 5 月 10 日这一行。前端画折线图时这一天会被直接跳过看起来像是5月9日直接连到5月11日。解决办法是在应用层补全日期序列查出来的结果作为 Map再按完整日期列表去填缺的补 0。MapLocalDate, Long cntMap result.stream() .collect(Collectors.toMap(DayCount::getDay, DayCount::getCnt)); ListDayCount full new ArrayList(); for (LocalDate d startDate; d.isBefore(endDate); d d.plusDays(1)) { full.add(new DayCount(d, cntMap.getOrDefault(d, 0L))); }这个补零动作看起来琐碎但缺了它报表就是错的。5.3 子查询与更新语句里的时间条件有一种需求是把最近 7 天的订单标记为待归档。MySQL 里直接写UPDATE t_order SET status ARCHIVED WHERE created_at DATE_SUB(NOW(), INTERVAL 7 DAY) AND status DONE;看着挺正常但如果你写成下面这样就要小心了-- 谨慎更新和子查询用的是同一张表 UPDATE t_order SET status ARCHIVED WHERE id IN ( SELECT id FROM t_order WHERE created_at DATE_SUB(NOW(), INTERVAL 7 DAY) );MySQL 不允许在UPDATE的子查询里直接引用被更新的同一张表会报You cant specify target table for update in FROM clause。绕过的办法是套一层派生表UPDATE t_order SET status ARCHIVED WHERE id IN ( SELECT id FROM ( SELECT id FROM t_order WHERE created_at DATE_SUB(NOW(), INTERVAL 7 DAY) ) AS tmp );这里的tmp是必须的很多人生成 SQL 时把它漏掉然后对着报错发懵。原理是派生表会被物化成临时表从而切断同一张表的直接引用关系。批量归档这类操作还要注意事务大小。一次更新几十万行会长时间持有锁阻塞正常读写。稳妥做法是分批发每次取 1000 条的时间上界作为下批的起点循环执行中间留一点间隔。6. 一套可复用的排查清单下次再遇到时间查询问题直接照着走前面五节讲的是原理和方法这一节把它压缩成一份能贴在显示器旁边的清单。我按发现问题到验证修复的顺序排遇到问题从头往下走就行。6.1 五步定位法第一步确认区间语义。业务方要的是某一天还是某个精确时刻到某个精确时刻起点和终点是否包含把答案写成一句话再翻译成 SQL。第二步把时间范围打印出来。在 Service 层加一行日志输出实际传给 DAO 的start和end。我遇到过太多次代码写的是对的但传进来的参数是错的比如前端传了个空字符串被解析成了 1970 年。第三步把 SQL 拿到数据库里手工跑。用打印出来的具体值替换占位符在客户端执行。这一步能立刻区分SQL 逻辑问题和应用层参数问题。第四步EXPLAIN 看执行计划。key为空、type为ALL说明索引没用上回到 3.2 节找原因。第五步对比修复前后耗时。别只看感觉快了用真实数据量测。测试环境数据少时索引用不用得上差别不大这种假阳性最坑人。6.2 常见问题对照表现象最可能的原因处理方向查某天数据为空区间起止相同或终点取 00:00:00终点改为次日零点用少了最后几毫秒的数据终点写成23:59:59改为次日零点并用相邻两天数据重复用了闭区间拼接统一改成左闭右开数据整体偏移 8 小时时区配置不一致检查serverTimezone与服务器时区查询慢、EXPLAIN 显示全表扫字段套了函数或类型不匹配去掉函数、对齐类型联合索引只用到前半段范围条件排在等值条件之前调整索引列顺序翻页越翻越慢深分页LIMIT offset改游标分页或按主键过滤更新语句报子查询错误子查询引用了被更新表套一层派生表6.3 几个容易忽略的实操细节最后一节分享几个小技巧都是文档里不太写、但实际很好用的。用半开区间做昨天的通用公式。无论什么数据库昨天 [今天零点减一天, 今天零点)。Java 里就是LocalDate.now().minusDays(1).atStartOfDay()和LocalDate.now().atStartOfDay()端点全靠plusDays/minusDays推绝不手写时分秒。给时间字段加索引时要考虑查询模式。如果经常是按用户时间段查就建(user_id, created_at)如果经常是全表按时间段扫单独建(created_at)即可。索引不是越多越好每个索引都会拖慢写入一张表上超过五六个索引就该重新审视了。测试用例一定要覆盖边界。数据里至少埋三条区间起点前 1 毫秒、正好等于起点、正好等于终点。跑一遍如果三条都符合预期这段代码基本就稳了。我所在的团队把这三条做成了单测模板新写的查询方法直接套省了无数回归时间。别在生产库上直接试 SQL。大区间查询可能瞬间把 CPU 拉满。要试就在从库或者测试库试并且先看一眼EXPLAIN里的rows预估几十万行以上就别贸然执行了。慢查询日志记得定期分析别只开不看。开了阈值不等于问题解决了每周花半小时用pt-query-digest过一遍把 Top 10 里跟时间查询相关的挑出来优化比事后救火轻松太多。时间字段的查询说简单也简单一行WHERE就完事说复杂也复杂从区间语义到索引到序列化一路都是能翻车的地方。我自己的体会是凡是跟时间相关的代码都按最坏情况会发生来写边界多想一步类型多确认一次日志多打一行。这几年的线上事故里真正因为算法复杂、架构精妙而出的问题其实很少绝大多数都是这种看起来毫不起眼的边界细节。下次你写时间范围查询时不妨把区间先写成[start, end)再回头看看是不是少了很多麻烦。
上周排查一个线上问题用户反馈昨天的订单一条都没查到但数据库里明明躺着两千多条。最后定位下来不是数据丢了也不是接口挂了而是那个查询条件把时间段写成了 2024-05-20 00:00:00 AND 2024-05-20 00:00:00——起止时间一模一样区间宽度为零。这种一看就想笑、笑完又想哭的错误在时间字段查询这件事上几乎每天都在发生。围绕时间字段做指定时间段的查询看起来是最基础的 SQL 操作实际上它牵扯的东西比想象中多得多区间的开闭语义、日期与时间戳的类型差异、时区偏移、函数包字段导致的索引失效、Java 侧格式化时的线程安全、深分页下的性能塌方。这篇文章不打算写成教科书我想把这几年在订单、日志、监控、报表这几类场景里踩过的时间坑系统性地捋一遍把每一步为什么这么写讲透。无论你是刚开始写 CRUD 的新人还是带团队做数据平台的老手下面这些内容应该都能直接拿去对照自己的代码。1. 时间段到底是什么先把区间语义对齐再动手写SQL很多人写时间查询是手感驱动——看到需求说查5月20日的数据条件里就顺手敲个BETWEEN 2024-05-20 AND 2024-05-20。跑出来结果是空的还以为是没数据。问题的根子不在语法在于时间段这个词本身就有歧义业务方说的和数据库理解的往往不是一回事。1.1 左闭右开是工程上最省心的选择先明确一个术语。所谓时间段数学上是区间工程上必须指定端点是否包含区间写法含义是否包含起点是否包含终点[start, end]闭区间是是[start, end)左闭右开是否(start, end)开区间否否我推荐绝大多数场景用[start, end)也就是左闭右开。理由有三条都是实打实踩出来的。第一条它能天然避开最后一秒的精度问题。假设你存的是DATETIME(3)毫秒精度想让5月20日全天完整覆盖写 2024-05-20 23:59:59会漏掉23:59:59.500这条记录。你可能想改写成 2024-05-20 23:59:59.999但字段精度一旦提升到微秒这个魔数又不够看了。而写成 2024-05-21 00:00:00无论精度怎么变都不会漏。第二条连续区间拼接时不会重叠。做日报、月报的时候相邻两天的区间如果是闭区间23:59:59这个点会被两天的报表各统计一次数字对不上。左闭右开则严丝合缝前一天的终点就是后一天的起点不重不漏。第三条它跟 Java 里java.time的很多 API 语义一致像LocalDate.plusDays(1).atStartOfDay()这种写法能直接算出上界代码读起来也顺。提示如果业务方明确要求包含终点比如按自然日统计且数据精确到秒那就退化为闭区间但务必在接口文档里写明end 参数含当秒让调用方知道边界行为。1.2 日期字段和日期时间字段处理逻辑完全不同数据库里的时间字段大致分三档写法差别很大。第一档是纯日期DATE只到天。这时候做范围查询其实是字符串或整数比较BETWEEN 2024-05-01 AND 2024-05-20是安全的因为不存在时分秒端点语义清晰。第二档是日期时间DATETIME / TIMESTAMP精确到秒、毫秒甚至微秒。这才是坑最多的地方必须用 起点 AND 终点1天的写法。第三档是 Unix 时间戳BIGINT秒或者毫秒。这类字段看起来最土实际上最好处理没有格式、没有时区、没有精度歧义直接比大小。代价是可读性差你拿1716134400000是看不出这是哪天的调试时得先转换。我个人的经验是日志、埋点、监控这类高频写入且只用于范围过滤的数据用毫秒时间戳最省心业务单据用 DATETIME方便人工查库和导出。混用是最糟糕的同一个库里两种都有写查询的人分分钟搞错。1.3 时区那个让你数据凭空少8小时的隐藏变量这是最隐蔽的一类 bug而且往往在测试环境发现不了。MySQL 的TIMESTAMP类型在存储时会把值转成 UTC读取时再按会话时区转回来DATETIME则原样存储不做任何转换。如果你的 JDBC 连接串里serverTimezone没配对Java 里2024-05-20 10:00:00写进去、读出来可能变成2024-05-20 02:00:00整整差 8 小时——跨天查询就此全线崩盘。排查这类问题有个笨但有效的办法写一条SELECT NOW(), session.time_zone, global.time_zone;看数据库自己认为现在几点再跟应用服务器的时间对一下。三边对不上别急着改业务代码先把时区配置统一了。我的建议是能不用TIMESTAMP就别用老老实实DATETIME时区转换全部放到应用层做。这样数据库的行为是可预测的出问题时责任边界也清楚。2. 主流数据库的时间区间写法对照别把MySQL的习惯带到Oracle写 SQL 的人如果不是多数据库背景很容易把一家的语法硬套到另一家。MySQL 的宽容度特别高很多写法看起来能跑到了 PostgreSQL 或 Oracle 直接报错。这一节把常见写法对照着列一遍。2.1 MySQLBETWEEN 能用但要小心隐式转换最直白的写法SELECT id, order_no, created_at FROM t_order WHERE created_at 2024-05-20 00:00:00 AND created_at 2024-05-21 00:00:00;这是我最推荐的形态。created_at字段前面不加任何函数字符串常量会被 MySQL 隐式转成DATETIME再比较索引能正常走范围扫描。要警惕的是BETWEEN。它等价于 AND 两端都闭用在纯 DATE 字段上没问题用在 DATETIME 上就要想清楚终点给什么值-- 危险会漏掉 2024-05-20 当天 00:00:00 之后的所有数据 -- 因为两端都是 00:00:00区间宽度为零 WHERE created_at BETWEEN 2024-05-20 00:00:00 AND 2024-05-20 00:00:00 -- 可用但不够优雅依赖秒级精度毫秒数据会漏 WHERE created_at BETWEEN 2024-05-20 00:00:00 AND 2024-05-20 23:59:59还有一个常见的坏习惯用DATE_FORMAT把字段截断到天再比较-- 别这么写 WHERE DATE_FORMAT(created_at, %Y-%m-%d) 2024-05-20它在语义上没问题但DATE_FORMAT(created_at, ...)是个函数表达式MySQL 无法用它去走created_at上的索引只能全表扫描。数据量上万还能忍上千万就是灾难。2.2 OracleTO_DATE、TRUNC 与字符串比较的顺序Oracle 对类型的要求严格得多字符串和日期之间不会那么随意地隐式转换。正确姿势是显式转换SELECT id, order_no, created_at FROM t_order WHERE created_at TO_DATE(2024-05-20 00:00:00, YYYY-MM-DD HH24:MI:SS) AND created_at TO_DATE(2024-05-21 00:00:00, YYYY-MM-DD HH24:MI:SS);如果需要按天聚合或者按天做等值判断Oracle 里有TRUNC-- 按天等于但同样会破坏 created_at 上的普通索引 WHERE TRUNC(created_at) DATE 2024-05-20跟 MySQL 一样TRUNC(created_at)会让常规 B-Tree 索引失效。真要在 Oracle 里做这种查询标准做法是建函数索引CREATE INDEX idx_order_trunc_created ON t_order(TRUNC(created_at));。这是一条很实用的经验——当你不得不在字段上套函数时去建对应的函数索引而不是每次都全表扫。另外提醒一句Oracle 的DATE类型自带时分秒只是显示时不带很多人误以为它只存日期。这也是为什么WHERE created_at DATE 2024-05-20往往查不到数据的真正原因——它等价于跟2024-05-20 00:00:00比相等。2.3 参数化查询把拼接字符串这条路彻底堵死不管哪家数据库时间条件都不该用字符串拼接。除了 SQL 注入风险还有一个纯技术原因手工拼出来的时间字符串格式五花八门2024/5/20、2024-05-20、20240520都有人写一旦跟数据库期望的格式对不上要么报错要么被悄悄转成一个错误的值。Java 里用PreparedStatement或者 MyBatis 的#{}让驱动去处理类型转换// JDBC 原生写法 String sql SELECT id, order_no FROM t_order WHERE created_at ? AND created_at ?; try (PreparedStatement ps conn.prepareStatement(sql)) { ps.setTimestamp(1, Timestamp.valueOf(start)); ps.setTimestamp(2, Timestamp.valueOf(end)); try (ResultSet rs ps.executeQuery()) { // 处理结果 } }setTimestamp会带上时区信息交给驱动处理比你自己拼字符串可靠一个数量级。这个习惯值不值得养成我用一句话总结只要参数是用户传进来的就永远用占位符没有例外。3. 索引为什么没生效时间区间查询的执行计划拆解同样一条时间范围查询有人跑 20 毫秒有人跑 20 秒。差别通常不在 SQL 写得漂不漂亮而在索引有没有被用上。这一节不讲索引原理教科书讲怎么看出来它没用上、以及为什么没用上。3.1 先学会看 EXPLAIN别靠猜MySQL 里执行计划就是照妖镜EXPLAIN SELECT id, order_no, created_at FROM t_order WHERE created_at 2024-05-20 00:00:00 AND created_at 2024-05-21 00:00:00;重点看三列列名期望值含义typerange或ref访问类型出现ALL就是全表扫key索引名非 NULL实际用到的索引rows尽量小预估扫描行数如果type是ALL、key是NULL基本可以断定索引没用上。接下来的任务是找出为什么。3.2 函数包字段、隐式类型转换、前导通配符三大元凶我把这些年遇到的索引失效原因归了归类时间查询相关的九成出在前两条。原因一字段上套了函数。WHERE DATE_FORMAT(created_at, %Y-%m-%d) 2024-05-20、WHERE YEAR(created_at) 2024、WHERE DATE_ADD(created_at, INTERVAL 1 DAY) NOW()全是这一类。B-Tree 索引是按字段原始值排序的你把排序依据改了索引就没法用来定位了。原因二隐式类型转换。这条最阴险。假设created_at是字符串类型有些老系统真这么干你写WHERE created_at 20240520000000数字MySQL 会把字段转成数字再比索引直接废掉。反过来字段是 DATETIME你传了个格式不对的字符串也可能触发转换。原因三范围条件太多。联合索引(user_id, created_at)里如果既对user_id做范围查询、又对created_at做范围查询索引只能用到第一个范围列。这是 B-Tree 索引的固有特性不是配置问题。正确做法是让等值条件排在联合索引前面范围条件放最后。排查的顺序我一般是这样的先EXPLAIN确认key是否为空再看 SQL 里字段有没有被函数包住再对一遍字段类型和参数类型是否一致最后看联合索引的列顺序。3.3 慢查询日志定位那些你没意识到的区间有些区间查询问题不在 SQL 本身而在区间开得太大。比如有人把默认查询范围设成了一年用户打开页面就触发一次全表扫描。这种问题在开发环境数据少时完全看不出来。打开慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;跑一段时间后用mysqldumpslow或者pt-query-digest汇总按总耗时排序而不是按单次耗时排序。你会发现真正拖垮数据库的往往不是某条特别慢的语句而是某条中速语句被调用了十万次。我在一个订单系统里就这么干过一次单个时间区间查询耗时 0.8 秒没超过 1 秒的慢查询阈值所以一直没被记录。但报表页每刷新一次要塞 20 个这样的查询累计 16 秒。把它挑出来加索引之后整体响应从 18 秒降到 1.2 秒。这个案例我一直拿来讲慢查询日志的阈值别卡得太死或者干脆按平均耗时排序来找高频中速语句。4. Java 侧的时间处理从参数接收到结果序列化的全链路SQL 只是时间链路的一半。请求参数从 HTTP 进来是字符串进入 Service 要变成时间对象传给 DAO 要变成正确的类型查回来要格式化给前端。任何一环出错最终用户看到的就是数据不对。4.1 Java 时间类型选型别再新建 Date 了老代码里满是java.util.Date和SimpleDateFormat这套 API 有两个硬伤第一SimpleDateFormat不是线程安全的。它在内部维护了一个可变的Calendar多线程下会串数据。我见过线上因为共用一个静态SimpleDateFormat实例导致时间格式化结果随机错乱的案例排查了两天才定位到。第二Date的语义含糊。它既表示日期又表示时刻getYear()返回的是年份减 1900getMonth()从 0 开始用一次骂一次。新代码一律用java.timeDateTimeFormatter FMT DateTimeFormatter.ofPattern(yyyy-MM-dd HH:mm:ss); // 用户传 2024-05-20解析成当天起点 LocalDate day LocalDate.parse(2024-05-20); LocalDateTime start day.atStartOfDay(); LocalDateTime end day.plusDays(1).atStartOfDay(); // 格式化回字符串 String startStr start.format(FMT);DateTimeFormatter是不可变对象天生线程安全可以放心当静态常量用。LocalDate.plusDays(1)计算次日零点自动处理月末、年末、闰年比自己写if (month 12)靠谱得多。如果参数带时区跨区域业务用ZonedDateTime或者OffsetDateTime明确指定ZoneId.of(Asia/Shanghai)别依赖系统默认时区。4.2 MyBatis 与 MyBatis-Plus 里怎么拼时间条件MyBatis 里最常规的写法是用if做动态条件select idselectByRange resultTypeOrder SELECT id, order_no, created_at FROM t_order WHERE 1 1 if teststart ! null AND created_at gt; #{start} /if if testend ! null AND created_at lt; #{end} /if /select注意和在 XML 里要转义成gt;和lt;或者包在![CDATA[ ]]里否则解析器会报错。MyBatis-Plus 的QueryWrapper更贴近 Java 思维LambdaQueryWrapperOrder wrapper new LambdaQueryWrapper(); wrapper.ge(Order::getCreatedAt, start) .lt(Order::getCreatedAt, end) .orderByDesc(Order::getCreatedAt);ge和lt就是和跟前面讲的左闭右开语义正好对上。要留意的是lt传进来的end必须已经算好是次日零点别在业务代码里传个23:59:59然后指望框架帮你补。顺带提一个高频错误用 MyBatis 的if teststart ! null判断时如果参数是字符串而不是时间对象空字符串会通过! null检查然后被拼进 SQL变成一个非法的时间常量直接报错。稳妥的写法是判断类型后再判断内容或者干脆在 Controller 层用DateTimeFormat注解把字符串直接绑定成LocalDateTime。4.3 返回给前端的时间格式统一在序列化层做查出来的created_at默认会被序列化成 ISO 格式或者时间戳数组前端拿到一脸懵。我的做法是在配置里统一指定Bean public Jackson2ObjectMapperBuilderCustomizer jacksonCustomizer() { return builder - { DateTimeFormatter fmt DateTimeFormatter.ofPattern(yyyy-MM-dd HH:mm:ss); builder.serializerByType(LocalDateTime.class, new LocalDateTimeSerializer(fmt)); builder.deserializerByType(LocalDateTime.class, new LocalDateTimeDeserializer(fmt)); }; }这样做的好处是入参出参格式一致前端不需要额外处理。踩过的坑是一旦全局配了格式所有接口都得遵守特殊接口想用别的格式只能单独加注解。所以配置之前最好跟前端对一次别各写各的。5. 分页、聚合与统计时间区间查询的三种进阶形态基础的等值范围查询写熟了之后真实的业务需求往往更复杂。这一节挑三个最常见的进阶场景。5.1 大区间分页深分页为什么会越来越慢用户查最近一年的订单还要翻到第 500 页。SQL 大概长这样SELECT id, order_no, created_at FROM t_order WHERE created_at 2023-05-20 AND created_at 2024-05-20 ORDER BY created_at DESC LIMIT 10000, 20;LIMIT 10000, 20的含义是先按顺序取出 10020 行再丢掉前 10000 行。翻到后面页扫描量线性增长速度越来越慢。改善的办法是游标分页用上一页最后一条记录的时间作为下一页的起点SELECT id, order_no, created_at FROM t_order WHERE created_at 2023-05-20 AND created_at 2024-05-20 AND created_at 2024-03-15 08:30:12 -- 上一页最后一条的时间 ORDER BY created_at DESC LIMIT 20;这样每次查询都只扫 20 行跟页码无关。代价是不能跳页只能上一页下一页。订单流水、操作日志这种按时间倒序浏览的场景游标分页完美适配。但如果是跳到第 87 页看某个具体订单这种需求游标分页就不合适了得换个思路。还有一个小窍门如果时间字段上有索引且区分度高可以考虑把ORDER BY created_at换成ORDER BY id前提是主键自增且与时间正相关MySQL 优化器有时能因此选择更优的路径。5.2 按天聚合时间桶怎么切才不会缺数据报表需求经常是按天统计订单量SELECT DATE(created_at) AS day, COUNT(*) AS cnt FROM t_order WHERE created_at 2024-05-01 AND created_at 2024-06-01 GROUP BY DATE(created_at) ORDER BY day;这段 SQL 本身没问题但有两个隐忧。其一DATE(created_at)无法用索引聚合必然全扫。如果表很大而查询频率又高正确做法是建一张按天预聚合的统计表定时任务每天凌晨跑一次报表直接读结果。用空间换时间这是报表场景的通行做法。其二没有数据的那一天不会出现在结果里。5月1日到5月31日如果5月10日零订单结果集里就没有 5 月 10 日这一行。前端画折线图时这一天会被直接跳过看起来像是5月9日直接连到5月11日。解决办法是在应用层补全日期序列查出来的结果作为 Map再按完整日期列表去填缺的补 0。MapLocalDate, Long cntMap result.stream() .collect(Collectors.toMap(DayCount::getDay, DayCount::getCnt)); ListDayCount full new ArrayList(); for (LocalDate d startDate; d.isBefore(endDate); d d.plusDays(1)) { full.add(new DayCount(d, cntMap.getOrDefault(d, 0L))); }这个补零动作看起来琐碎但缺了它报表就是错的。5.3 子查询与更新语句里的时间条件有一种需求是把最近 7 天的订单标记为待归档。MySQL 里直接写UPDATE t_order SET status ARCHIVED WHERE created_at DATE_SUB(NOW(), INTERVAL 7 DAY) AND status DONE;看着挺正常但如果你写成下面这样就要小心了-- 谨慎更新和子查询用的是同一张表 UPDATE t_order SET status ARCHIVED WHERE id IN ( SELECT id FROM t_order WHERE created_at DATE_SUB(NOW(), INTERVAL 7 DAY) );MySQL 不允许在UPDATE的子查询里直接引用被更新的同一张表会报You cant specify target table for update in FROM clause。绕过的办法是套一层派生表UPDATE t_order SET status ARCHIVED WHERE id IN ( SELECT id FROM ( SELECT id FROM t_order WHERE created_at DATE_SUB(NOW(), INTERVAL 7 DAY) ) AS tmp );这里的tmp是必须的很多人生成 SQL 时把它漏掉然后对着报错发懵。原理是派生表会被物化成临时表从而切断同一张表的直接引用关系。批量归档这类操作还要注意事务大小。一次更新几十万行会长时间持有锁阻塞正常读写。稳妥做法是分批发每次取 1000 条的时间上界作为下批的起点循环执行中间留一点间隔。6. 一套可复用的排查清单下次再遇到时间查询问题直接照着走前面五节讲的是原理和方法这一节把它压缩成一份能贴在显示器旁边的清单。我按发现问题到验证修复的顺序排遇到问题从头往下走就行。6.1 五步定位法第一步确认区间语义。业务方要的是某一天还是某个精确时刻到某个精确时刻起点和终点是否包含把答案写成一句话再翻译成 SQL。第二步把时间范围打印出来。在 Service 层加一行日志输出实际传给 DAO 的start和end。我遇到过太多次代码写的是对的但传进来的参数是错的比如前端传了个空字符串被解析成了 1970 年。第三步把 SQL 拿到数据库里手工跑。用打印出来的具体值替换占位符在客户端执行。这一步能立刻区分SQL 逻辑问题和应用层参数问题。第四步EXPLAIN 看执行计划。key为空、type为ALL说明索引没用上回到 3.2 节找原因。第五步对比修复前后耗时。别只看感觉快了用真实数据量测。测试环境数据少时索引用不用得上差别不大这种假阳性最坑人。6.2 常见问题对照表现象最可能的原因处理方向查某天数据为空区间起止相同或终点取 00:00:00终点改为次日零点用少了最后几毫秒的数据终点写成23:59:59改为次日零点并用相邻两天数据重复用了闭区间拼接统一改成左闭右开数据整体偏移 8 小时时区配置不一致检查serverTimezone与服务器时区查询慢、EXPLAIN 显示全表扫字段套了函数或类型不匹配去掉函数、对齐类型联合索引只用到前半段范围条件排在等值条件之前调整索引列顺序翻页越翻越慢深分页LIMIT offset改游标分页或按主键过滤更新语句报子查询错误子查询引用了被更新表套一层派生表6.3 几个容易忽略的实操细节最后一节分享几个小技巧都是文档里不太写、但实际很好用的。用半开区间做昨天的通用公式。无论什么数据库昨天 [今天零点减一天, 今天零点)。Java 里就是LocalDate.now().minusDays(1).atStartOfDay()和LocalDate.now().atStartOfDay()端点全靠plusDays/minusDays推绝不手写时分秒。给时间字段加索引时要考虑查询模式。如果经常是按用户时间段查就建(user_id, created_at)如果经常是全表按时间段扫单独建(created_at)即可。索引不是越多越好每个索引都会拖慢写入一张表上超过五六个索引就该重新审视了。测试用例一定要覆盖边界。数据里至少埋三条区间起点前 1 毫秒、正好等于起点、正好等于终点。跑一遍如果三条都符合预期这段代码基本就稳了。我所在的团队把这三条做成了单测模板新写的查询方法直接套省了无数回归时间。别在生产库上直接试 SQL。大区间查询可能瞬间把 CPU 拉满。要试就在从库或者测试库试并且先看一眼EXPLAIN里的rows预估几十万行以上就别贸然执行了。慢查询日志记得定期分析别只开不看。开了阈值不等于问题解决了每周花半小时用pt-query-digest过一遍把 Top 10 里跟时间查询相关的挑出来优化比事后救火轻松太多。时间字段的查询说简单也简单一行WHERE就完事说复杂也复杂从区间语义到索引到序列化一路都是能翻车的地方。我自己的体会是凡是跟时间相关的代码都按最坏情况会发生来写边界多想一步类型多确认一次日志多打一行。这几年的线上事故里真正因为算法复杂、架构精妙而出的问题其实很少绝大多数都是这种看起来毫不起眼的边界细节。下次你写时间范围查询时不妨把区间先写成[start, end)再回头看看是不是少了很多麻烦。
