我正在用Laravel开发一个遗留系统,并试图从employee所做的某些特定类型的操作中提取最新的操作。
当我不添加ORDER BY时,性能是很好的。当添加它时,查询将从大约130 ms到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`)发布于 2019-11-28 11:44:55
首先,您的查询是非常非最佳的。我会把它改写成这样:
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',并在其上加上索引。
https://stackoverflow.com/questions/59087494
复制相似问题