如何优化SQL查询中的ORDER BY COUNT(column)以避免Using temporary和Using filesort？

Question

我想要统计某个id和对应的数量并排序，sql如下：

SELECT
    t.label_name AS topic,
    t.label_id,
    COUNT( work_order_id) AS requestWorkOrderNumber
FROM
    biz_work_order_label t
INNER JOIN biz_work_order t1 ON
    (t1.id = t.work_order_id)
WHERE
    t.deleted = 0
    AND t1.deleted = 0
    AND (t.label_type = 8
        AND t1.problem_location_code LIKE '%440000%'
        AND t1.reporting_time BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59'
        AND t1.nature IN ('ts', 'jb'))
GROUP BY
    t.label_name,
    t.label_id
ORDER BY
    requestWorkOrderNumber DESC
LIMIT 10

explain的结果：

数据量大约400万，查询这个sql需要5s多，label_name、label_id、work_order_id都加过索引，没有效果，应该是count函数导致索引失效了。
应该如何优化呢？

Answer 1

在biz_work_order_label 表上面的work_order_id增加索引，biz_work_order_label 自动会变成被驱动表，会提升效率，现在只需200ms就可以查出来

Answer 2

t1主表索引

CREATE INDEX idx_t1_query 
ON biz_work_order (
    deleted,
    problem_location_code,
    reporting_time,
    nature,
    id
);

t（label表）

CREATE INDEX idx_t_query
ON biz_work_order_label (
    deleted,
    label_type,
    work_order_id,
    label_id,
    label_name
);

优化后的SQL语句

先过滤 t1，再 JOIN，work_order_id 是 JOIN 字段 ，label_type 是过滤字段

SELECT
    t.label_name AS topic,
    t.label_id,
    COUNT(*) AS requestWorkOrderNumber
FROM
(
    SELECT id
    FROM biz_work_order
    WHERE
        deleted = 0
        AND problem_location_code LIKE '440000%' 
        AND reporting_time BETWEEN '2024-01-01' AND '2024-12-31'
        AND nature IN ('ts', 'jb')
) t1
JOIN biz_work_order_label t
    ON t.work_order_id = t1.id
WHERE
    t.deleted = 0
    AND t.label_type = 8
GROUP BY
    t.label_name,
    t.label_id
ORDER BY
    requestWorkOrderNumber DESC
LIMIT 10;