如何从仅含生效、失效日期字段的表中提取指定日期间隔内的数据

核心判断逻辑 #

要识别查询区间[query_start, query_end]内可用的商品，本质是判断商品的有效生命周期和查询区间存在重叠，满足两个条件即可：

• 商品激活时间 <= 查询区间结束时间
• 商品失效时间 >= 查询区间开始时间（失效时间为空时默认商品仍在生效，满足该条件）

查询语句示例 #

假设表名为store_item_schedule，以MySQL为例，优化后的查询语句如下：

SELECT 
    `Store ID`, 
    `Item ID`, 
    item_active_Date, 
    item_inactive_date
FROM store_item_schedule
WHERE 
    -- 商品激活时间早等于查询结束时间
    item_active_Date <= '2021-11-25'
    -- 商品失效时间晚等于查询开始时间，或未失效
    AND (item_inactive_date >= '2021-06-15' OR item_inactive_date IS NULL);

注：其他数据库语法差异很小，仅需调整时间函数、引号规则即可，核心判断逻辑通用。

千万级数据性能优化方案 #

1. 建覆盖索引避免回表 #

建立联合覆盖索引，所有查询需要的字段都在索引中，不需要扫描原表，性能提升非常明显：

-- MySQL 写法
CREATE INDEX idx_date_cover ON store_item_schedule (item_active_Date, item_inactive_date, `Store ID`, `Item ID`);

-- PostgreSQL/SQL Server 写法（支持INCLUDE语法）
CREATE INDEX idx_date_cover ON store_item_schedule (item_active_Date, item_inactive_date) INCLUDE (`Store ID`, `Item ID`);

2. 避免索引列函数操作 #

上面的查询语句没有对索引字段item_inactive_date使用COALESCE/IFNULL这类函数，而是拆分为OR条件，完全可以命中索引，不会出现索引失效的问题。

3. 分区表优化 #

如果数据量持续增长，可以按item_active_Date建立范围分区，查询时会自动跳过激活时间晚于查询结束时间的分区，大幅减少扫描的数据量。

4. 冷数据归档 #

对已失效超过1年以上不会再参与查询的冷数据，定期归档到离线存储表，降低业务主表的数据量，查询性能会进一步提升。

内容的提问来源于stack exchange，提问作者Dhananjayan