如何优化MySQL InnoDB中taskList表的特定优先级筛选查询？

问题概述 #

你有一个InnoDB表taskList，表结构与数据如下：

需要筛选出同时满足以下条件的记录：

• Category为system
• Date_time在2019-01-01至2019-07-01之间
• Priority是该子集内不小于2且最接近2的最小值（即取子集里≥2的最小Priority值，本例中符合前两个条件的子集里，目标Priority为5，对应ID1、2的记录）

你的原查询虽然能正常工作，但重复执行了过滤逻辑，存在优化空间。下面是几种更简洁且性能更优的实现方式：

方案1：使用窗口函数（MySQL 8.0+ 首选） #

如果你的MySQL版本是8.0及以上，窗口函数是最高效的方案——它只需扫描一次表，就能完成过滤和目标Priority的计算：

SELECT ID, TaskName, Category, Date_time, Priority
FROM (
    SELECT *,
           -- 在符合条件的子集内计算最小Priority
           MIN(Priority) OVER () AS target_priority
    FROM taskList
    WHERE Category = 'system'
      AND Date_time BETWEEN '2019-01-01' AND '2019-07-01'
      AND Priority >= 2
) AS filtered
WHERE Priority = target_priority
ORDER BY Date_time DESC;

这里的MIN(Priority) OVER ()会在内部过滤结果集中直接算出目标Priority，外层只需匹配该值即可，全程单遍扫描，性能比原查询提升明显。

方案2：用JOIN替代子查询（兼容低版本MySQL） #

如果你的MySQL版本低于8.0，不支持窗口函数，可以用JOIN避免重复执行过滤逻辑：

SELECT t.*
FROM taskList t
-- 先计算出目标Priority，再关联主表
JOIN (
    SELECT MIN(Priority) AS min_priority
    FROM taskList
    WHERE Category = 'system'
      AND Date_time BETWEEN '2019-01-01' AND '2019-07-01'
      AND Priority >= 2
) AS p ON t.Priority = p.min_priority
WHERE t.Category = 'system'
  AND t.Date_time BETWEEN '2019-01-01' AND '2019-07-01'
  AND t.Priority >= 2
ORDER BY t.Date_time DESC;

这种写法逻辑和原查询一致，但JOIN的形式更易被MySQL优化器处理，减少重复计算的开销。

关键性能优化：创建复合索引 #

不管采用哪种查询写法，想要真正提升性能，必须创建合适的复合索引，避免全表扫描：

CREATE INDEX idx_task_cat_dt_priority ON taskList (Category, Date_time, Priority);

这个复合索引可以直接帮数据库过滤出Category='system'和Date_time范围内的记录，同时直接获取Priority值，避免回表查询，查询速度会大幅提升。

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