查找员工的最新活动在添加 ORDER BY 时很慢

Question

我正在Laravel开发一个遗留系统，我正在尝试提取employee所做的某些特定类型操作的最新操作。

不加ORDER BY时性能很好.添加它时，查询将从大约 130 毫秒变为 18 秒。actions表中大约有 150 万行。

如何解决性能问题？

我试图通过删除查询的所有其他部分来隔离问题，以便对您来说更具可读性：

SELECT
employees.id,
(
SELECT DATE_FORMAT(actions.date, '%Y-%m-%d')
FROM pivot
JOIN actions
ON pivot.actions_id = actions.id
WHERE employees.id = pivot.employee_id
AND (actions.type = 'meeting'
OR (actions.type = 'phone_call'
AND JSON_VALID(actions.data) = 1
AND actions.data->>'$.update_status' = 1))
LIMIT 1
) AS latest_action
FROM employees
ORDER BY latest_action DESC

我尝试使用LEFT JOIN和MAX()代替，但它似乎没有解决我的问题。

我刚刚添加了一个子查询，因为它是原始查询已经非常复杂了。但是，如果您有其他建议，我全听。

更新

EXPLAIN结果：

id  select_type         table       partitions  type    possible_keys                       key                 key_len ref                     rows    filtered    Extra
1   PRIMARY             employees   NULL        ALL     NULL                                NULL                NULL    NULL                    15217   10          Using where
2   DEPENDENT SUBQUERY  pivot       NULL        ref     actions_type_index,pivot_type_index pivot_type_index    4       dev.employees.id        104     11.11       Using index condition
2   DEPENDENT SUBQUERY  actions     NULL        eq_ref  PRIMARY,Logs                        PRIMARY             4       dev.pivot.actions_id    1       6.68        Using where

更新 2

这是索引。我认为索引employee_type对我的特定查询并不重要，但也许应该重新设计？

# pivot table
KEY `actions_type_index` (`actions_id`,`employee_type`),
KEY `pivot_type_index` (`employee_id`,`employee_type`)
# actions table
KEY `Logs` (`type`,`id`,`is_log`)
# I tried to add `date` index to `actions` table but the problem remains.
KEY `date_index` (`date`)

Answer 1

首先，您的查询非常不理想。 我会这样重写它：

SELECT
e.id,
DATE_FORMAT(vMAX(a.date), '%Y-%m-%d') AS latest_action
FROM employees e
LEFT JOIN pivot p ON p.employee_id = e.id
LEFT JOIN actions a ON p.actions_id = a.id AND (a.type = 'meeting'
OR (a.type = 'phone_call'
AND JSON_VALID(a.data) = 1
AND a.data->>'$.update_status' = 1))
GROUP BY e.id
ORDER BY latest_action DESC

显然，p.employee_id、p.actions_id、a.date上必须有索引。对a.type也会很好. 此外，最好将a.data->>'$.update_status'替换为一些带有索引的简单字段。