数据库里最不起眼、但又几乎天天要用到的操作就是根据时间字段查询指定时间段的数据。订单流水、操作日志、战绩记录、物流轨迹、设备上报、各类查询类站点的检索入口只要数据带了时间戳就绕不开这个动作。看着简单——一个 WHERE 加两个参数谁都会写可真到了千万级数据的表上你就会发现有的语句 30 毫秒返回有的语句能把数据库 CPU 拉到 100%。这篇文章不聊教科书定义只讲我在实际项目里反复踩过、又反复修过的那些细节时间字段到底该选哪种类型、区间边界怎么定、为什么加了格式化函数索引就废了、Java 侧传参的时区坑藏在哪、慢查询日志怎么读、大表跨月统计怎么扛。不管你是刚写第一行 SQL 的新手还是已经在维护线上库的老手下面这些东西都能直接拿去用。1. 时间字段的存储选型选错了后面全是补丁1.1 三种主流存法各自的适用边界时间段查询的第一步不是写 SQL而是回头看你的时间字段是怎么存进去的。这一点决定了后面所有查询的写法、性能和精度。我见过太多项目字段类型选错了最后靠一堆格式化函数、转换代码、补偿逻辑硬撑代码越写越厚性能越跑越差。所以先把三种主流存法摆清楚。第一种是DATETIMEMySQL 里的经典选择格式是YYYY-MM-DD HH:MM:SS可加小数位表示更高精度DATETIME(3)是毫秒。它存的是字面值不随会话时区变化你存进去什么读出来就是什么。绝大多数业务表尤其是订单、账单、流水这类要求记录就是记录的场景我都会优先用 DATETIME。第二种是TIMESTAMP它内部按 UTC 存储读写时按会话时区转换还带自动更新能力ON UPDATE CURRENT_TIMESTAMP。它的范围只到 2038 年这是个硬伤做长期归档的表不要用它。第三种是BIGINT 存时间戳或存yyyyMMddHHmmss这样的数值好处是排序直观、跨库迁移无歧义、索引体积小坏处是人看不懂、SQL 里没法直接用日期函数、报表和 BI 工具接起来别扭。提示如果一张表需要长期存在比如日志、账单要保留 10 年以上不要用 TIMESTAMP2038 年不是传说是确定会到的。选择逻辑其实很简单业务语义上的时刻用 DATETIME只关心先后顺序、且追求极致写入性能的日志类数据可以上 BIGINT 时间戳需要自动记录更新时间的辅助字段TIMESTAMP 也能用但别当主时间字段用。最怕的是同一张表里混着两种类型一会儿 DATETIME 一会儿时间戳查询的时候两边都转索引全废代码里还得写两套解析。1.2 边界该开还是该闭左闭右开几乎永远是对的接下来是边界。这是时间段查询里翻车率最高的地方比索引问题还隐蔽因为它不报错只是数据对不上。假设你要查2024 年 3 月 1 日到 3 月 31 日的数据。很多人的第一反应是SELECT * FROM orders WHERE create_time BETWEEN 2024-03-01 00:00:00 AND 2024-03-31 23:59:59;这条语句在秒级精度下勉强能用但它有两个隐患。第一漏掉 23:59:59.001 到 23:59:59.999 之间的数据——如果字段是DATETIME(3)这些记录就永远查不出来而且用户不会知道。第二逻辑上不干净每次都要去算月末最后一秒遇到闰年、月末天数变化写死的字符串迟早出错。我的固定写法是左闭右开SELECT * FROM orders WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-04-01 00:00:00;右边界直接用下个月的 1 号零点不用减一秒、不用管月末是 28 天还是 31 天。这个写法在秒级、毫秒级、微秒级精度下都成立永远不会漏数据也不会重复计算边界那一秒。参数生成在 Java 侧也极其简单start传当月起点的LocalDateTimeend传下月起点的LocalDateTime中间不需要任何加减运算。1.3 精度与毫秒一个被忽视的重复统计来源再说精度。字段是DATETIME秒级还是DATETIME(3)毫秒级对统计结果的影响完全不一样。如果字段是秒级但前端传上来的时间是毫秒级的2024-03-01 00:00:00.000MySQL 在比较时会做隐式转换通常不会有问题但如果字段是毫秒级你却用秒级边界去卡就会漏掉边界秒内的非零毫秒记录。真正麻烦的是统计类查询。比如按天统计订单量如果字段是毫秒级你用BETWEEN 当天零点 AND 当天 23:59:59会把23:59:59.500的记录漏掉日汇总就少了。稳妥的做法还是左闭右开或者干脆按日期函数分组SELECT DATE(create_time) AS d, COUNT(*), SUM(amount) FROM orders WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-04-01 00:00:00 GROUP BY DATE(create_time);这里有个反直觉的点WHERE 里用函数会导致索引失效但 GROUP BY 里的DATE()不影响索引使用因为 WHERE 已经先用范围把数据圈出来了前提是范围条件本身没被函数包住这个下一章细讲。区分清楚这两处能帮你少走很多弯路。1.4 时区代码不报错数据却对不上时区是最阴的一类问题。数据库层面TIMESTAMP类型跟会话时区绑定应用连接池里如果没显式设置时区就可能出现写入时间比预期早 8 小时或晚 8 小时的现象DATETIME不转换所以相对安全。应用层面Java 用new Date()或者LocalDateTime.now()拿到的是系统默认时区的时间如果容器镜像用的是 UTC那你查今天的数据时边界就和业务人员理解的今天不一致了。我踩过最典型的一次服务器时区是 UTC业务方说查昨天一整天的数据代码里用LocalDate.now()拿日期结果拿的是 UTC 的昨天跟东八区的昨天差了 8 小时报表少了一整个下午的数据。解决办法是所有时间边界的计算统一显式指定业务时区比如LocalDate.now(ZoneId.of(Asia/Shanghai))绝不依赖默认值。数据库连接串里也把时区参数写死别让驱动自己猜。注意一旦发现某些时间段查出来是空的、换一台机器查又是对的先查时区再查字段类型最后才怀疑索引。2. 索引失效的元凶为什么你的时间段查询越查越慢2.1 把时间字段包进函数等于亲手扔掉索引这是时间段查询最经典、也最容易被低级格式化写法带偏的坑。很多教程里会写SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-03-15;这条语句在功能上没毛病但create_time上如果建了索引这个索引在这条查询里完全用不上。原因很直白B 树索引是按create_time的原始值排序的一旦你把它包进DATE_FORMAT优化器就必须对每一行求一次函数值再比较索引的有序性被破坏只能全表扫描。数据量小的时候你感觉不到几百万行上去就是几秒和几十毫秒的差距。换成范围写法问题立刻消失SELECT * FROM orders WHERE create_time 2024-03-15 00:00:00 AND create_time 2024-03-16 00:00:00;同样的道理适用于DATE(create_time) 2024-03-15、YEAR(create_time) 2024、MONTH(create_time) 3、create_time 0 ...、CAST(create_time AS DATE) ...。判断标准就一条等号或比较符左边是不是一个干净的列名。是索引大概率能用不是基本没戏。提示字符串类型的日期字段比如varchar存2024-03-15 10:00:00做范围比较是可以用索引的因为它按字典序排格式统一时字典序等于时间序。但前提是格式必须严格统一一旦混入2024-3-5 9:00这种缺零格式排序就乱了范围查询结果直接错。2.2 用 EXPLAIN 读懂范围扫描到底走没走索引写完 SQL 别急着上线用EXPLAIN看一眼。时间段查询我重点看这几列列名关注点理想值type访问类型range最差不能是ALLkey实际使用的索引时间相关的联合索引不是 NULLkey_len用到索引的字节数越接近索引定义总长越好rows预估扫描行数跟实际返回量级接近filtered过滤后剩余比例越高越好接近 100 说明条件有效Extra附加信息出现Using filesort、Using temporary要警惕如果type是ALL说明在扫全表如果是index说明在扫整个索引树虽然比全表快一点但依然不是范围扫描。真正健康的时间段查询应该看到type: rangekey指向你建的时间索引。还有一种隐蔽情况type是range、key也对但rows特别大。这通常意味着范围划得太宽或者索引选择性太差。比如你在一张只有 3 天数据的表上用一整年的范围去查优化器会认为走索引还不如直接全表扫索性放弃索引——这是优化器的成本判断不是索引没用。这种情况下适当收窄范围或者补一个更高选择性的条件比如租户 ID、状态效果立竿见影。2.3 联合索引的最左前缀范围字段后面别再放等值字段时间段查询几乎从不是单独出现的它总跟着几个等值条件租户、状态、类型、逻辑删除标记。这时候索引怎么建就有讲究了。假设查询是WHERE tenant_id ? AND status ? AND create_time ? AND create_time ?。正确的联合索引顺序是CREATE INDEX idx_tenant_status_time ON orders (tenant_id, status, create_time);等值条件在前范围条件在最后。原因是B 树在遇到范围条件后后面的列就无法继续用于索引查找了。如果你把顺序写成(create_time, tenant_id, status)那么create_time走了范围扫描之后tenant_id和status就只能靠回表后再过滤效率差一大截。还有一个常被忽略的点范围和排序不能同时吃到索引。如果查询里既有create_time范围又要ORDER BY amount DESC那排序基本躲不开 filesort。想避免的话要么把排序字段放到索引里但要接受索引膨胀要么在业务上接受最近 N 天按时间倒序这种更简单的需求——注意ORDER BY create_time DESC是可以用上索引的因为方向和范围字段一致。注意ORDER BY create_time DESC在 MySQL 8.0 之前对联合索引的方向要求比较苛刻建索引时用DESC关键字能缓解8.0 之后有降序索引支持情况好一些但依然建议把 EXPLAIN 里的Using filesort作为优化信号。2.4 逻辑删除字段混进来会发生什么带逻辑删除的表几乎所有查询都会被自动追加一个deleted 0条件MyBatis-Plus 就是这么干的。这个条件出现在时间段查询里对索引的影响取决于你的索引结构。如果索引是(create_time)那deleted只能回表过滤索引还是能用的只是过滤后行数变多。如果索引是(deleted, create_time)并且查询里deleted 0的选择性很高比如 99% 的数据都是 0那这个前导列的区分度几乎为零反而会让优化器犹豫——它可能觉得不如只用create_time部分。我一般的做法是高选择性的等值条件放前面低选择性的比如布尔型状态、逻辑删除不单独作为前导列除非业务上绝大多数查询都带这个条件且数据分布极端倾斜。另外提醒一句逻辑删除的数据如果长期堆积会让表体积膨胀时间段范围扫描要跳过的墓碑行越来越多性能是缓慢劣化的。定期归档或者物理清理比加索引更有效。3. 落地实现从 SQL 到 Java 代码的完整链路3.1 各数据库方言差异速查时间段查询的骨架一样但不同数据库的写法有差异迁移或者多库共存时容易踩坑。MySQL 和 PostgreSQL 都支持 AND PostgreSQL 还多了daterange类型和操作符配合 GiST 索引处理区间非常舒服但写法冷门团队里不一定有人熟我一般还是用标准写法。Oracle 里要注意DATE类型精度只到秒毫秒要TIMESTAMP日期字面量要写TO_DATE(2024-03-01 00:00:00, YYYY-MM-DD HH24:MI:SS)或者用TIMESTAMP 2024-03-01 00:00:00这种 ANSI 字面量。SQL Server 里是 AND 照旧但要注意datetime和datetime2的精度差异后者才是毫秒级。Oracle 做时间段聚合统计的写法跟 MySQL 差别主要在类型转换上SELECT TRUNC(create_time) AS d, COUNT(*) AS cnt, SUM(amount) AS total FROM orders WHERE create_time TIMESTAMP 2024-03-01 00:00:00 AND create_time TIMESTAMP 2024-04-01 00:00:00 GROUP BY TRUNC(create_time) ORDER BY d;这里的TRUNC(create_time)等价于 MySQL 的DATE()放在 GROUP BY 里没问题但千万别把它放进 WHERE理由和上一章讲的一样。3.2 Java 侧的时间参数怎么算、怎么传Java 8 之后时间处理统一用java.time别再碰java.util.Date和SimpleDateFormat——后者不是线程安全的多线程下格式化出错误时间是常态很多时间段查询偶尔查不到数据的bug就出在这。我常用的一段边界计算工具逻辑是这样的// 指定业务时区避免默认时区带来的偏移 ZoneId zone ZoneId.of(Asia/Shanghai); // 查询某一天 [day, day1) LocalDate day LocalDate.of(2024, 3, 15); LocalDateTime start day.atStartOfDay(); LocalDateTime end day.plusDays(1).atStartOfDay(); // 查询某一月 [monthStart, nextMonthStart) YearMonth month YearMonth.of(2024, 3); LocalDateTime monthStart month.atDay(1).atStartOfDay(); LocalDateTime nextMonthStart month.plusMonths(1).atDay(1).atStartOfDay(); // 格式化成数据库需要的字符串只有打印或拼 SQL 时才需要 DateTimeFormatter fmt DateTimeFormatter.ofPattern(yyyy-MM-dd HH:mm:ss); String startStr start.format(fmt);关键点是加一天、加一个月这些运算全部交给LocalDate去做不要用plusHours(24)或plusSeconds(86400)硬算。因为有些地区有夏令时一天不一定是 24 小时用日期维度加逻辑永远正确。传给数据库时用PreparedStatement的setObject或者 MyBatis 的参数占位符让驱动去做类型转换别自己拼字符串。拼字符串除了有注入风险还会因为格式不一致导致隐式转换和索引失效。3.3 MyBatis / MyBatis-Plus 里的正确姿势MyBatis 的 XML 里时间段条件是这样写的select idlistByRange resultTypeOrder SELECT id, order_no, amount, create_time FROM orders WHERE create_time gt; #{start} AND create_time lt; #{end} if teststatus ! null AND status #{status} /if /select注意和在 XML 里必须转义这是新手最常见的编译报错来源之一。参数start、end直接用LocalDateTime类型MyBatis 3.4.5 以上对java.time有原生支持不需要额外注册 TypeHandler。MyBatis-Plus 的 LambdaQueryWrapper 写起来更短LambdaQueryWrapperOrder qw new LambdaQueryWrapper(); qw.ge(Order::getCreateTime, start) .lt(Order::getCreateTime, end) .eq(status ! null, Order::getStatus, status) .orderByDesc(Order::getCreateTime);这里有两个细节值得说。一eq(boolean condition, ...)这种带条件的重载非常好用能省掉一堆if但要确认你的项目里逻辑删除配置开了没有开了的话框架会自动追加deleted 0条件数就变多了。二不要用apply(date_format(create_time,%Y-%m-%d) {0}, day)这种写法虽然它看起来灵活但直接把函数带进了 WHERE前面讲的索引失效问题原封不动地回来了。真要按天筛选就老老实实算[当天, 次日)的范围。3.4 时间段 分页 排序 关联查询的组合拳真实业务里时间段查询很少是单表。典型场景是查某段时间内下过单且有退款记录的用户这就涉及 EXISTS 或 IN 子查询。SELECT o.user_id, COUNT(*) AS cnt, SUM(o.amount) AS total FROM orders o WHERE o.create_time #{start} AND o.create_time #{end} AND EXISTS ( SELECT 1 FROM refunds r WHERE r.order_id o.id AND r.create_time #{start} AND r.create_time #{end} ) GROUP BY o.user_id ORDER BY cnt DESC LIMIT #{offset}, #{size};关于EXISTS和IN的取舍我的经验是子查询表大、外层表小用 EXISTS外层表大、子查询结果集小用 IN。MySQL 在新版本里对IN子查询做了物化优化性能差距没以前那么夸张但EXISTS在关联字段有索引时更稳。IN报错的常见原因我也遇到过几次列表太长触发max_allowed_packet、子查询返回多列、类型不匹配字符串 IN 数字导致全表扫描这几种都要留意。分页方面深分页是时间段查询的另一个性能黑洞。LIMIT 100000, 20这种写法会先扫过 10 万行再丢弃。优化思路是基于游标的分页用上一页最后一条的create_time和id作为下一页起点SELECT id, order_no, create_time FROM orders WHERE create_time #{start} AND create_time #{end} AND (create_time #{lastTime} OR (create_time #{lastTime} AND id #{lastId})) ORDER BY create_time, id LIMIT 20;这套写法在时间字段有索引时非常高效因为每次都是从索引的某个位置继续往下扫不用回头。代价是不能跳页只适合下一页式的交互。如果业务必须支持跳页那就在前端限制最大页数别让用户翻到第 5000 页。3.5 定时任务里传时间参数的坑有一个场景特别容易出空指针定时任务比如 Quartz、Spring Task在凌晨跑统计时间参数从配置或上一轮任务结果里取。如果配置没读到、上一轮结果为空参数就是 null直接扔进 SQL 或者塞进 Wrapper要么报空指针要么查询变成无条件全表扫描。我的处理方式是任务入口第一件事就是校验时间参数为空就按默认策略补齐比如取昨天并且把补齐后的实际参数打进日志。日志里能看到本次执行实际查询区间是 X 到 Y排查问题时省掉大量猜测。另外定时任务里的时间边界建议由任务自己算不要从外部传入减少不确定性。4. 故障实录时间段查询的排查套路4.1 先用慢查询日志定位再动手优化优化之前别猜。MySQL 打开慢查询日志设置一个合理的阈值比如 1 秒跑一段时间后看哪些时间段查询进了榜。重点看三个数字Query_time总耗时、Lock_time锁等待、Rows_examined扫描行数。如果是Rows_examined巨大但返回行数很少八成是索引没用上如果Query_time高但Rows_examined不高可能是锁竞争或者磁盘 IO。拿到慢 SQL 之后用EXPLAIN看执行计划再决定是加索引、改写法还是改数据结构。顺序不能反——先加索引再观察往往加了十个索引问题还在。4.2 常见问题速查表现象可能原因处理方式边界数据时有时无用了BETWEEN 00:00:00 AND 23:59:59改左闭右开右边界取下期起点查询突然变慢WHERE 里对时间字段用了函数去掉函数改纯范围比较昨天数据少了 8 小时时区不统一默认时区是 UTC显式指定业务时区分页越翻越慢深分页 LIMIT offset 很大改游标分页用时间ID 定位加了索引还是全表扫索引顺序不对范围字段在前等值字段在前范围字段放最后定时任务报空指针时间参数未校验为 null入口补默认值并打日志统计结果对不上明细字段精度与边界精度不一致统一毫秒/秒精度或按天分组视图查得比原表还慢视图嵌套子查询谓词无法下推直接查基表或改物化视图关于视图那一条多说一句。很多人觉得把复杂查询包成视图就能提速这是个误解。普通视图只是保存了 SQL 定义执行时展开成子查询优化器能不能把外层的时间范围条件下推到内层取决于具体实现很多时候推不下去结果就是内层先扫全表再过滤。视图是便利工具不是性能工具。真需要预计算考虑物化视图Oracle 有MySQL 需要自己建汇总表或者定时任务落汇总表。4.3 大表跨月统计怎么扛数据量上到千万级、上亿级之后时间段统计的优化就得换个思路了。分索引只能解决单点查询扛不住大范围聚合。我常用的三板斧是这样的。第一按时间分区。MySQL 的 RANGE 分区按TO_DAYS(create_time)切查询带时间范围时能触发分区裁剪只扫相关分区效果非常直观。缺点是分区键必须进主键或唯一键表结构要提前设计中途改代价大。第二冷热分离加归档。把 3 个月前的数据迁到历史表或归档库主表保持小快灵。归档任务每周跑一次按时间批量搬配合INSERT INTO ... SELECT ... WHERE create_time ?注意 MySQL 里UPDATE/DELETE的子查询不能直接引用被更新的表需要包一层派生表绕开限制。第三预聚合汇总表。如果业务需求是固定的按天、按小时的金额和单量直接建一张汇总表每小时算一次查询时读汇总表毫秒级返回。明细查询和统计查询走两条路互不干扰。这是我认为性价比最高的方案能用空间换时间的场景基本都值得。提示预聚合表一定要有重算机制。一旦某次任务失败或数据修正必须有办法按时间段重新跑一遍覆盖写入不然汇总和明细会永久性对不上。5. 几个我固化成习惯的做法写到这里还有几个零散但很值钱的经验单独拎出来。时间字段的索引我基本都会建但绝不建多。一张订单表时间相关索引超过两个就是浪费。写入时的索引维护成本是实打实的尤其高频写入的表每多一个索引插入就慢一分。所有时间边界的计算代码必须集中在一个工具类里。分散在各个 Service 里的时间计算三个月后就是一场灾难。集中之后改时区、改精度、改边界策略都是一处生效。上线前必做的一件事把新写的时间段 SQL 拿真实数据量跑一遍 EXPLAIN。测试环境几万行看不出问题生产几百万行立刻暴露。这个习惯帮我拦下过至少五次线上事故。统计类查询的时间范围永远比用户看到的宽一天。因为跨时区、跨天边界的问题宽一天再在应用层裁剪比卡死在边界上安全得多。最后一点是关于字符串日期的。如果你的表里已经存了varchar格式的时间短期内没法改结构那就在应用层严格保证写入格式统一补零、固定分隔符并在查询时坚持左闭右开的纯字符串范围比较别用STR_TO_DATE去转换——转了就索引失效。这是权宜之计长期还是建议迁到DATETIME越早迁成本越低。