如何在MariaDB中编写高效单查询实现目标参考编号筛选

环境与表结构 #

在Debian 11（bullseye）系统的MariaDB 10.5.15中，涉及的reference_log表结构及样本数据如下：

DROP TABLE IF EXISTS reference_log;

CREATE TABLE reference_log
(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  reference_number VARCHAR(20) NOT NULL,
  reference_date DATE NOT NULL,
  log_date DATE NOT NULL
) Engine=InnoDB;

INSERT INTO reference_log (
    id,
    reference_number,
    reference_date,
    log_date
) VALUES (
    1, 
    '123',
    '2024-04-01',
    '2024-04-14'
);

INSERT INTO reference_log (
    id,
    reference_number,
    reference_date,
    log_date
) VALUES (
    2, 
    '123',
    '2024-04-01',
    '2024-04-15'
);

INSERT INTO reference_log (
    id,
    reference_number,
    reference_date,
    log_date
) VALUES (
    3, 
    '123',
    '2024-04-01',
    '2024-05-01'
);

INSERT INTO reference_log (
    id,
    reference_number,
    reference_date,
    log_date
) VALUES (
    4, 
    '123',
    '2024-05-01',
    '2024-05-06'
);

数据特性 #

• 更早的id对应相同或更早的log_date（按log_date升序插入）
• 元组(reference_number, log_date)始终唯一
• 元组(reference_number, reference_date)存在大量重复，按reference_date升序插入
• 同一reference_number的log_date可能存在间隔
• reference_date始终小于等于log_date

需求说明 #

找出指定log_date下，满足以下条件的所有reference_number：其同reference_number的前一个实例（定义为相同reference_number且id更小的行中id最大的那一行）的reference_date比当前行的reference_date更早。

结合样本数据：

• 当log_date为2024-05-01时，无符合条件的行返回（前实例reference_date与当前行相同）
• 当log_date为2024-05-06时，返回1行（前实例reference_date更早）

现有尝试的问题 #

• 自连接查询逻辑可行，但因数据量（超200万行）过大，耗时超4分钟
• 逐个查询指定log_date的约7000行数据，效率低且无法直接在数据库中测试查询逻辑

高效解决方案 #

利用MariaDB 10.5支持的窗口函数LAG()，可以实现单次扫描完成查询，大幅提升效率：

SELECT reference_number
FROM (
    SELECT 
        reference_number,
        reference_date,
        -- 获取同reference_number的前一行（按id排序的前一个实例）的reference_date
        LAG(reference_date) OVER (PARTITION BY reference_number ORDER BY id) AS prev_reference_date
    FROM reference_log
    WHERE log_date = '2024-05-06' -- 替换为指定的log_date
) AS sub
WHERE prev_reference_date < reference_date;

优化建议：添加索引 #

为了进一步提升查询速度，创建覆盖索引，让数据库无需回表查询：

CREATE INDEX idx_logdate_refnum_id_refdate 
ON reference_log(log_date, reference_number, id, reference_date);

验证结果 #

• 当指定log_date为2024-05-06时，查询返回123，符合预期
• 当指定log_date为2024-05-01时，因prev_reference_date等于当前reference_date，无结果返回，符合预期

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