创建排序索引需要很长时间

Question

我有一个 SQL（MariaDB，版本：10.3.22-MariaDB-0+deb10u1）：

SELECT
    v.employee_id AS id,
    REPLACE(
        FORMAT(
         COALESCE((
            SUM(COALESCE((COALESCE(v.amount, 0) / COALESCE(ash.value, a.standard, 0)), 0))
            /
            (
               SUM(COALESCE(v.worktime, 0)) 
               +
               (
                  SELECT SUM(COALESCE(t.break, 0)) 
                  + COALESCE(SUM(t.assigned_task1), 0) 
                  + COALESCE(SUM(t.assigned_task2), 0) 
                  + COALESCE(SUM(t.assigned_task3), 0) 
                  + COALESCE(SUM(t.assigned_task4), 0) 
                  + COALESCE(SUM(t.assigned_task5), 0) 
                  FROM times t 
                  WHERE t.employee_id = v.employee_id 
                  AND t.day BETWEEN '2022-02-03' AND '2022-02-03' 
               )
            ) 
         ), 0) * 100 
       , 0 
     ), ',', '') AS p_real 
FROM volumes v 
INNER JOIN activities a 
ON 
   v.activity_id = a.id 
   AND a.is_deleted = 0 
   AND a.id IN (-1, 145, 218, 227, 286) /* There are much more IDs in real SQL. */
LEFT JOIN activity_standard_histories ash 
ON ash.activity_id = a.id AND ash.created = (
    SELECT created 
    FROM activity_standard_histories 
    WHERE 
        DATE(activity_standard_histories.created) <= v.day 
        AND activity_standard_histories.activity_id = a.id 
    ORDER BY activity_standard_histories.created DESC 
    LIMIT 1
)
WHERE 
    v.employee_id IN ('552','553','554','2113','2991') /* There are much more employee_ids in real SQL. */
    AND v.day BETWEEN '2022-02-03' AND '2022-02-03' 
GROUP BY v.employee_id

volumes 表有 486.2Mb 大小，并且一直在增长。

这个查询执行了很长时间。我注意到的：

1. 查询EXPLAIN：

   

2. 此查询的状态长时间为“正在创建排序索引”。

表格索引：

卷

活动

activity_standard_histories

次

我看过很多资料：这个， 这个，这个，这个等等。但是，我不确定我到底应该申请什么。也许它可能是一些额外的复杂索引，但我不确定是哪一个，因为每个表已经有某些索引。