MariaDB中查找边界值（bounding values）的最优方案探讨

针对你需要查询指定时间范围内外错误状态（出现/解决）的需求，以下是两种性能更优的改写方案，同时结合索引优化进一步提升效率：

一、核心优化思路 #

原方案通过3次独立查询+UNION实现，会触发3次索引扫描。优化目标是减少索引扫描次数，同时利用数据库的高效查询特性精准获取边界值。

二、方案1：使用窗口函数（推荐，适用于支持窗口函数的数据库） #

通过一次索引扫描，同时获取范围前的最后一条记录、范围内所有记录、范围后的第一条记录，避免多次IO开销：

WITH boundary_params AS (
    SELECT '2022-08-03' AS start_date, '2022-08-09' AS end_date, '目标错误码' AS target_code
),
all_relevant_records AS (
    SELECT 
        e.date,
        e.value,
        -- 标记是否在目标时间范围内
        CASE WHEN e.date BETWEEN bp.start_date AND bp.end_date THEN 1 ELSE 0 END AS in_range,
        -- 范围前记录的排序标识（取最新的一条）
        ROW_NUMBER() OVER (PARTITION BY CASE WHEN e.date < bp.start_date THEN 1 ELSE 0 END ORDER BY e.date DESC) AS pre_range_rank,
        -- 范围后记录的排序标识（取最早的一条）
        ROW_NUMBER() OVER (PARTITION BY CASE WHEN e.date > bp.end_date THEN 1 ELSE 0 END ORDER BY e.date ASC) AS post_range_rank
    FROM errors e
    CROSS JOIN boundary_params bp
    WHERE e.error_code = bp.target_code
      -- 仅扫描必要范围：目标区间 + 区间前后各一条
      AND (e.date <= bp.end_date 
           OR e.date = (SELECT MIN(date) FROM errors WHERE error_code = bp.target_code AND date > bp.end_date)
           OR e.date = (SELECT MAX(date) FROM errors WHERE error_code = bp.target_code AND date < bp.start_date))
)
SELECT date, value
FROM all_relevant_records
WHERE 
    in_range = 1
    -- 仅当范围内首条不是"出现"状态时，返回范围前的记录
    OR (pre_range_rank = 1 AND EXISTS (
        SELECT 1 FROM all_relevant_records 
        WHERE in_range = 1 
        ORDER BY date ASC LIMIT 1
        HAVING MIN(value) != 1
    ))
    -- 仅当范围内末条不是"解决"状态时，返回范围后的记录
    OR (post_range_rank = 1 AND EXISTS (
        SELECT 1 FROM all_relevant_records 
        WHERE in_range = 1 
        ORDER BY date DESC LIMIT 1
        HAVING MAX(value) != 0
    ))
ORDER BY date ASC;

优势： #

• 仅触发1次索引扫描，大幅降低IO开销
• 在SQL层面完成不必要边界记录的过滤，减少应用层处理逻辑
• 逻辑集中，便于后续维护

三、方案2：优化原UNION方案（兼容低版本数据库） #

如果你的数据库不支持窗口函数，可以对原方案做以下关键优化：

1. 用UNION ALL替代UNION：三个查询结果无重复数据，UNION ALL无需执行去重和排序，性能提升明显
2. 强制指定错误码过滤：结合复合索引快速定位目标错误的时间序列
3. 在SQL层面过滤不必要的边界记录

优化后的SQL：

-- 范围前的最后一条记录（仅当范围内首条不是"出现"状态时返回）
SELECT date, value FROM errors
WHERE error_code = '目标错误码'
  AND date < '2022-08-03'
ORDER BY date DESC
LIMIT 1
AND EXISTS (
    SELECT 1 FROM errors 
    WHERE error_code = '目标错误码'
      AND date = '2022-08-03' 
      AND value != 1
)

UNION ALL

-- 范围内的所有记录
SELECT date, value FROM errors
WHERE error_code = '目标错误码'
  AND date BETWEEN '2022-08-03' AND '2022-08-09'
ORDER BY date ASC

UNION ALL

-- 范围后的第一条记录（仅当范围内末条不是"解决"状态时返回）
SELECT date, value FROM errors
WHERE error_code = '目标错误码'
  AND date > '2022-08-09'
ORDER BY date ASC
LIMIT 1
AND EXISTS (
    SELECT 1 FROM errors 
    WHERE error_code = '目标错误码'
      AND date = '2022-08-09' 
      AND value != 0
)

四、索引优化建议 #

无论采用哪种方案，都建议建立复合覆盖索引，彻底避免回表查询：

CREATE INDEX idx_errors_code_date_value ON errors (error_code, date) INCLUDE (value);

• 复合索引(error_code, date)可快速定位指定错误的时间序列数据
• INCLUDE (value)实现覆盖索引，直接从索引中获取所需字段，无需访问主表

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