MySQL窗口函数DENSE_RANK未利用索引致查询性能低下求优化

问题分析 #

你的查询性能瓶颈在于窗口函数未利用索引排序，导致大表场景下出现using_filesort，额外排序开销极大。核心原因是原查询的索引无法完全覆盖窗口函数的分区、排序及查询列需求，迫使MySQL回表后再进行排序。

优化步骤 #

1. 创建最优覆盖索引

首先创建包含所有查询所需列的覆盖索引，让MySQL无需回表即可获取数据，同时直接利用索引的有序性避免额外排序：

ALTER TABLE orders ADD INDEX idx_dep_account_create_order (department_id, account_hash, create_time, order_hash);

该索引的顺序严格匹配查询逻辑：

• 先过滤department_id
• 再按account_hash分区
• 最后按create_time排序
• 包含order_hash确保无需回表读取主键数据

2. 优化窗口函数查询（强制使用覆盖索引）

修改原查询，强制使用上述覆盖索引，确保窗口函数直接利用索引的有序性：

INSERT INTO orders
    (department_id, order_hash, account_hash, order_position_number)
SELECT 
    department_id,
    order_hash,
    account_hash,
    DENSE_RANK() OVER(PARTITION BY account_hash ORDER BY create_time) - 1 AS order_position_number
FROM orders FORCE INDEX (idx_dep_account_create_order)
WHERE department_id = 1
ON DUPLICATE KEY UPDATE
    order_position_number = VALUES(order_position_number);

通过FORCE INDEX引导优化器选择覆盖索引，避免其误选其他低效索引。

3. 备选方案：用变量模拟序号（绕过窗口函数排序）

若窗口函数仍无法有效利用索引，可改用用户变量模拟序号，利用索引排序后的直接计算：

INSERT INTO orders
    (department_id, order_hash, account_hash, order_position_number)
SELECT 
    department_id,
    order_hash,
    account_hash,
    @rank := CASE 
        WHEN @prev_account = account_hash THEN @rank + 1 
        ELSE 0 
    END AS order_position_number
FROM (
    -- 先通过索引排序，确保数据按account_hash+create_time有序
    SELECT department_id, order_hash, account_hash, create_time
    FROM orders FORCE INDEX (idx_dep_account_create_order)
    WHERE department_id = 1
    ORDER BY account_hash, create_time
) t,
(SELECT @prev_account := '', @rank := 0) vars
ON DUPLICATE KEY UPDATE
    order_position_number = VALUES(order_position_number);

该方式直接利用索引的有序输出，通过变量累加序号，完全避免窗口函数的内部排序开销。

4. 大表分批次处理

针对超大规模表，一次性处理会导致锁表及内存压力，可按account_hash分批次处理：

SET @batch_size = 1000;
SET @last_account = '';

REPEAT
    INSERT INTO orders
        (department_id, order_hash, account_hash, order_position_number)
    SELECT 
        department_id,
        order_hash,
        account_hash,
        @rank := CASE 
            WHEN @prev_account = account_hash THEN @rank + 1 
            ELSE 0 
        END AS order_position_number
    FROM (
        SELECT department_id, order_hash, account_hash, create_time
        FROM orders FORCE INDEX (idx_dep_account_create_order)
        WHERE department_id = 1
          AND account_hash > @last_account
        ORDER BY account_hash, create_time
        LIMIT @batch_size
    ) t,
    (SELECT @prev_account := '', @rank := 0) vars
    ON DUPLICATE KEY UPDATE
        order_position_number = VALUES(order_position_number);

    SET @last_account = (SELECT MAX(account_hash) FROM orders WHERE department_id = 1 AND account_hash > @last_account LIMIT 1);
UNTIL @last_account IS NULL END REPEAT;

通过分批限制处理数据量，降低单次操作的锁竞争与内存占用。

验证优化效果 #

执行EXPLAIN ANALYZE查看优化后的查询：

• 应显示Using index（使用覆盖索引，无回表）
• 无Using filesort（排序由索引完成）

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