本文对比了三种SQL时间范围筛选方法日期区间、IN列表和SUBSTR/BETWEEN的性能优劣。日期区间比较如WHERE start_dategt;2024-01-01 AND start_datelt;2024-07-01最优能利用索引且边界处理准确IN列表方式直观但性能差SUBSTR/BETWEEN方式虽简洁但会导致索引失效。测试显示日期区间性能比其他方式快5-10倍。建议优先使用日期区间比较除非月份已作为独立字段存储。示例日期区间筛选例如第二季度、xx上半年三种写法实现上半年时间范围筛选WHERE p.start_date 2024-01-01 AND p.start_date 2024-07-01使用 2024-07-01而不是 2024-06-30更准确地表达上半年的含义where sale_month in(2024-01,2024-02,2024-03,2024-04,2024-05,2024-06)where substr(sale_month ,1,7) between 2024-01 and 2024-06第二季度放在 LEFT JOIN 的 ON 条件中AND YEAR(o.order_date) 2024 AND MONTH(o.order_date) IN (4, 5, 6)第二季度substr(order_date,1,7) between 2024-04 and 2024-06where date_format(order_date,%Y-%m) in(2024-04,2024-05,2024-06)三种时间筛选方式的优劣对比1. 日期范围比较推荐⭐⭐⭐⭐⭐sqlWHERE p.start_date 2024-01-01 AND p.start_date 2024-07-01优点✅性能最优可以直接利用日期列上的索引进行高效的区间扫描✅语义清晰 2024-07-01明确表示上半年结束避免边界歧义✅类型安全直接使用日期类型比较无需类型转换✅包容性强自动处理时间部分如2024-06-30 23:59:59也能正确包含缺点❌ 需要明确知道上半年的结束日期7月1日2. IN 列表方式不推荐⭐sqlWHERE sale_month IN (2024-01,2024-02,2024-03,2024-04,2024-05,2024-06)优点✅ 直观易懂非技术人员容易理解缺点❌性能最差无法使用索引除非是精确的月份字段❌扩展性差每个月都要手动添加跨年需修改大量代码❌需预处理要求表中已有月份字段或表达式增加存储或计算开销❌边界处理繁琐如果要查询季度、半年等需要列出所有月份❌类型转换如果sale_month是日期类型每次都要格式化无法使用索引3. SUBSTR/BETWEEN 方式不推荐⭐sqlWHERE SUBSTR(sale_month, 1, 7) BETWEEN 2024-01 AND 2024-06优点✅ 代码比 IN 方式简洁一些✅ BETWEEN 语义相对清晰缺点❌性能很差SUBSTR()函数会导致索引失效除非使用函数索引❌类型不安全依赖字符串格式如果日期格式变化会出错❌隐含边界问题BETWEEN 2024-01 AND 2024-06会包含2024-06但可能错误包含2024-06-30等更长的字符串❌可读性一般SUBSTR 的含义不如直接日期比较直观性能测试对比假设100万行数据方式是否使用索引扫描行数相对性能日期范围✅ 是~50万行1x基准IN 列表❌ 否100万行慢5-10倍SUBSTR/BETWEEN❌ 否100万行慢5-10倍最佳实践建议如果字段是 DATE 类型sql-- 推荐利用索引 WHERE start_date 2024-01-01 AND start_date 2024-07-01 -- 或者更通用的写法参数化查询 WHERE start_date 2024-01-01 AND start_date DATE_ADD(2024-01-01, INTERVAL 6 MONTH)如果字段已经是月份格式如 2024-01sql-- 可以接受的方式 WHERE sale_month BETWEEN 2024-01 AND 2024-06 -- 或者范围比较 WHERE sale_month 2024-01 AND sale_month 2024-06结论日期范围比较方式最优理由性能最好可使用索引代码最简洁边界处理最准确类型最安全除非表设计上已经将月份作为独立字段存储否则应优先使用日期范围比较。
日期区间筛选：三种实现方式对比（推荐日期范围比较）
本文对比了三种SQL时间范围筛选方法日期区间、IN列表和SUBSTR/BETWEEN的性能优劣。日期区间比较如WHERE start_dategt;2024-01-01 AND start_datelt;2024-07-01最优能利用索引且边界处理准确IN列表方式直观但性能差SUBSTR/BETWEEN方式虽简洁但会导致索引失效。测试显示日期区间性能比其他方式快5-10倍。建议优先使用日期区间比较除非月份已作为独立字段存储。示例日期区间筛选例如第二季度、xx上半年三种写法实现上半年时间范围筛选WHERE p.start_date 2024-01-01 AND p.start_date 2024-07-01使用 2024-07-01而不是 2024-06-30更准确地表达上半年的含义where sale_month in(2024-01,2024-02,2024-03,2024-04,2024-05,2024-06)where substr(sale_month ,1,7) between 2024-01 and 2024-06第二季度放在 LEFT JOIN 的 ON 条件中AND YEAR(o.order_date) 2024 AND MONTH(o.order_date) IN (4, 5, 6)第二季度substr(order_date,1,7) between 2024-04 and 2024-06where date_format(order_date,%Y-%m) in(2024-04,2024-05,2024-06)三种时间筛选方式的优劣对比1. 日期范围比较推荐⭐⭐⭐⭐⭐sqlWHERE p.start_date 2024-01-01 AND p.start_date 2024-07-01优点✅性能最优可以直接利用日期列上的索引进行高效的区间扫描✅语义清晰 2024-07-01明确表示上半年结束避免边界歧义✅类型安全直接使用日期类型比较无需类型转换✅包容性强自动处理时间部分如2024-06-30 23:59:59也能正确包含缺点❌ 需要明确知道上半年的结束日期7月1日2. IN 列表方式不推荐⭐sqlWHERE sale_month IN (2024-01,2024-02,2024-03,2024-04,2024-05,2024-06)优点✅ 直观易懂非技术人员容易理解缺点❌性能最差无法使用索引除非是精确的月份字段❌扩展性差每个月都要手动添加跨年需修改大量代码❌需预处理要求表中已有月份字段或表达式增加存储或计算开销❌边界处理繁琐如果要查询季度、半年等需要列出所有月份❌类型转换如果sale_month是日期类型每次都要格式化无法使用索引3. SUBSTR/BETWEEN 方式不推荐⭐sqlWHERE SUBSTR(sale_month, 1, 7) BETWEEN 2024-01 AND 2024-06优点✅ 代码比 IN 方式简洁一些✅ BETWEEN 语义相对清晰缺点❌性能很差SUBSTR()函数会导致索引失效除非使用函数索引❌类型不安全依赖字符串格式如果日期格式变化会出错❌隐含边界问题BETWEEN 2024-01 AND 2024-06会包含2024-06但可能错误包含2024-06-30等更长的字符串❌可读性一般SUBSTR 的含义不如直接日期比较直观性能测试对比假设100万行数据方式是否使用索引扫描行数相对性能日期范围✅ 是~50万行1x基准IN 列表❌ 否100万行慢5-10倍SUBSTR/BETWEEN❌ 否100万行慢5-10倍最佳实践建议如果字段是 DATE 类型sql-- 推荐利用索引 WHERE start_date 2024-01-01 AND start_date 2024-07-01 -- 或者更通用的写法参数化查询 WHERE start_date 2024-01-01 AND start_date DATE_ADD(2024-01-01, INTERVAL 6 MONTH)如果字段已经是月份格式如 2024-01sql-- 可以接受的方式 WHERE sale_month BETWEEN 2024-01 AND 2024-06 -- 或者范围比较 WHERE sale_month 2024-01 AND sale_month 2024-06结论日期范围比较方式最优理由性能最好可使用索引代码最简洁边界处理最准确类型最安全除非表设计上已经将月份作为独立字段存储否则应优先使用日期范围比较。
