MySQL促销表时间范围查询性能优化与字段使用问题咨询

问题1：不使用updatedOnDt字段是否可以查询到所需的活跃促销数据？ #

不能，原因如下：

• 你需要的活跃促销包含三类场景：① 激活时间落在查询区间内的促销；② 激活时间早于查询起始时间，且至今未停用的促销；③ 激活时间早于查询起始时间、停用时间晚于查询结束时间的促销。
• 没有updatedOnDt字段的话，无法区分已经停用和仍在运行的促销，也无法判断停用时间是否落在查询区间之外，完全覆盖不了后两类场景，也无法排除在查询区间内已经被停用的促销。

问题2：如果updatedOnDt字段是必要的，如何优化上述查询的性能？ #

首先你当前的SQL存在逻辑错误和优先级问题：SQL中AND的运算优先级高于OR，你现有的写法相当于(createdOnDt BETWEEN :fromDate AND :toDate) OR (createdOnDt <= :fromDate AND updatedOnDt IS NULL AND clientId = 1957)，会把其他clientId下、创建时间落在查询区间的促销也查出来，不符合需求。
优化步骤如下：

1. 先修正SQL逻辑，把clientId的过滤条件提到最外层，时间条件用括号包裹：

SELECT p.*
FROM promotions p
WHERE p.clientId = :clientId
AND (
  (p.createdOnDt >= :fromDate AND p.createdOnDt <= :toDate)
  OR (p.createdOnDt <= :fromDate AND p.updatedOnDt IS NULL)
)

2. 创建匹配查询逻辑的联合索引：

CREATE INDEX idx_client_created_updated ON promotions (clientId, createdOnDt, updatedOnDt);

等值过滤的clientId放在索引最左前缀，后续放两个用于范围过滤的时间字段，即可避免全表扫描，直接通过索引过滤大部分数据。
3. 如果OR条件仍然导致索引失效，可以拆为UNION ALL合并两个子查询的结果，进一步提升性能：

SELECT p.* FROM promotions p
WHERE p.clientId = :clientId AND p.createdOnDt BETWEEN :fromDate AND :toDate
UNION ALL
SELECT p.* FROM promotions p
WHERE p.clientId = :clientId AND p.createdOnDt <= :fromDate AND p.updatedOnDt IS NULL

问题3：覆盖跨查询区间的活跃促销场景，索引无提升如何处理？ #

首先你需要先修正活跃促销的判断逻辑，覆盖你提到的场景，正确的通用判断规则为：促销的活跃区间与查询区间存在重叠，即p.createdOnDt <= :toDate AND (p.updatedOnDt >= :fromDate OR p.updatedOnDt IS NULL)，这个逻辑可以覆盖所有符合要求的活跃促销，包括你说的8月2日激活、8月6日停用，查询8月3日-8月5日的场景。
性能优化方案如下：

• 不要单独创建单列时间索引，直接使用问题2中提到的(clientId, createdOnDt, updatedOnDt)联合索引，查询时会先通过clientId过滤掉无关客户的所有数据，再通过时间字段做范围过滤，性能远高于单列索引。
• 可以把updatedOnDt为null的默认值设置为极大值（比如'9999-12-31 23:59:59'），去掉OR updatedOnDt IS NULL的判断，简化查询条件为p.createdOnDt <= :toDate AND p.updatedOnDt >= :fromDate，更利于索引优化器选择合适的索引路径。
• 如果数据量极大，还可以将查询拆为两个UNION ALL分支：一个查询未停用的促销，一个查询已停用但停用时间晚于查询起始时间的促销，两个分支都可以完美匹配联合索引，避免复杂条件导致的索引失效。

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