含ORDER BY和GROUP BY的SQL查询执行过慢问题咨询

现有查询的问题分析 #

1. 逻辑可靠性问题：你当前的写法是内层查询先全量排序符合条件的记录，外层再GROUP BY分组，本质是想取每个(CODIGO_BOMBA, CODIGO_BICO)分组下最新DATAHORA对应的ENCERRANTE_FINAL，但这种写法结果不可靠：如果数据库开启ONLY_FULL_GROUP_BY模式会直接报错，就算没开，拿到的ENCERRANTE_FINAL也不一定是排序后的第一条值，完全依赖数据库底层实现。
2. 性能问题核心：
   • 写法冗余：需要先查询所有符合条件的记录排序，再做分组，大量数据下会产生非常多无用计算，还可能触发磁盘临时表排序。
   • 缺少匹配索引：没有对应过滤、排序、查询字段的联合索引，查询时需要全表扫描+回表取数，速度自然很慢。

优化方案 #

1. SQL写法优化

优先选窗口函数写法（MySQL8.0+、MariaDB10.2+支持，逻辑清晰可靠）

SET @CODIGO_EMPRESA = 1;
SET @CODIGO_FILIAL = 2;
SET @DATA_INICIAL = '2021-06-16 21:59:17';
SET @DATA_FINAL = '2021-09-20 21:59:16';
SET @SITUACAO_SUP_SANG = 0;

SELECT CODIGO_BOMBA, CODIGO_BICO, ENCERRANTE_FINAL
FROM (
    SELECT 
        CODIGO_BOMBA, CODIGO_BICO, ENCERRANTE_FINAL,
        ROW_NUMBER() OVER (PARTITION BY CODIGO_BOMBA, CODIGO_BICO ORDER BY DATAHORA DESC, ENCERRANTE_INICIAL DESC) AS rn
    FROM MOVIMENTO_COMB_ABASTECIDAS 
    WHERE
        CODIGO_EMPRESA = @CODIGO_EMPRESA
        AND CODIGO_FILIAL = @CODIGO_FILIAL
        AND DATAHORA < @DATA_INICIAL
) t WHERE rn = 1;

低版本数据库兼容写法

SELECT m.CODIGO_BOMBA, m.CODIGO_BICO, m.ENCERRANTE_FINAL
FROM MOVIMENTO_COMB_ABASTECIDAS m
INNER JOIN (
    SELECT 
        CODIGO_BOMBA, CODIGO_BICO, 
        MAX(CONCAT(DATAHORA, LPAD(ENCERRANTE_INICIAL, 20, '0'))) AS sort_key
    FROM MOVIMENTO_COMB_ABASTECIDAS
    WHERE
        CODIGO_EMPRESA = @CODIGO_EMPRESA
        AND CODIGO_FILIAL = @CODIGO_FILIAL
        AND DATAHORA < @DATA_INICIAL
    GROUP BY CODIGO_BOMBA, CODIGO_BICO
) t ON m.CODIGO_BOMBA = t.CODIGO_BOMBA 
    AND m.CODIGO_BICO = t.CODIGO_BICO
    AND CONCAT(m.DATAHORA, LPAD(m.ENCERRANTE_INICIAL, 20, '0')) = t.sort_key
WHERE
    m.CODIGO_EMPRESA = @CODIGO_EMPRESA
    AND m.CODIGO_FILIAL = @CODIGO_FILIAL
    AND m.DATAHORA < @DATA_INICIAL;

2. 索引优化（核心提速手段）

建联合覆盖索引，查询时不需要回表，直接从索引取数，性能提升最明显：

CREATE INDEX idx_mov_emp_fil_dt_bomba_bico ON MOVIMENTO_COMB_ABASTECIDAS (
    CODIGO_EMPRESA, CODIGO_FILIAL, DATAHORA DESC, ENCERRANTE_INICIAL DESC, CODIGO_BOMBA, CODIGO_BICO, ENCERRANTE_FINAL
);

索引顺序说明：前两个是WHERE等值过滤条件放最前，接下来是排序字段和查询字段，完全匹配查询逻辑，避免数据库额外排序和回表操作。

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