优化基于PARTITION OVER DENSE_RANK的大表SQL查询性能

针对你的900万行数据查询慢的问题，可以从索引优化和查询语句简化两方面入手，大幅提升执行速度：

一、创建针对性复合索引 #

当前查询的核心逻辑是：过滤未处理（is_processed=0）的行，按employee_id分区，再按next_processing_time、id排序计算排名，最后取排名最靠前的20条。现有索引完全无法匹配这个逻辑，导致全表扫描和大量排序操作。

创建以下覆盖索引，直接匹配查询的过滤、分区、排序及返回字段需求：

CREATE INDEX idx_unprocessed_employee_queue ON UserListCheckQueue(
  is_processed,
  employee_id,
  next_processing_time,
  id
);

索引设计逻辑： #

1. 先通过is_processed过滤出所有未处理的行，直接缩小数据范围；
2. 按employee_id分组（对应窗口函数的PARTITION BY），让同一员工的任务集中在一起；
3. 接着按next_processing_time和id排序，完全匹配窗口函数内的排序规则；
4. 最后包含id字段，实现覆盖索引，查询时无需回表读取原数据，直接从索引获取结果。

二、简化查询语句 #

原窗口函数中的ORDER BY is_processed ASC完全多余——因为WHERE is_processed=0已经过滤出所有该值的行，排序这个字段不会改变结果，反而增加计算开销。修改后的查询语句：

SELECT
  id,
  DENSE_RANK() OVER (
    PARTITION BY employee_id 
    ORDER BY next_processing_time ASC, id ASC
  ) AS job_rank
FROM test.UserListCheckQueue
WHERE is_processed = 0
ORDER BY job_rank
LIMIT 20;

优化效果说明 #

创建索引并修改语句后，数据库会直接利用索引的有序性：

• 无需全表扫描，直接定位到所有未处理的行；
• 窗口函数计算排名时，不需要额外排序，直接按索引顺序计算；
• 覆盖索引避免了回表操作，进一步减少IO开销。
  原本20秒的查询，优化后通常能降到毫秒级。

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