非常非常缓慢的查询

Question

我有一个查询：

SELECT 
    a.id,
    a.name, 
    count(b.id),
    count(c.id), 
    count(e.id), 
    count(f.id)
FROM 
    organizations a
LEFT JOIN vessels b ON a.id = b.organization_id
LEFT JOIN licences c ON a.id = c.organization_id
LEFT JOIN fleets e ON a.id = e.organization_id
LEFT JOIN users f ON a.id = f.organization_id
GROUP BY a.id;

在所有的表中都有一个适当的索引（在主键和“organization_id”上），在“organizations”表中有大约80行，在“fleets”表中有400行，在“vessels”表中有2900行，在“licenses”表中有3000行，在“users”表中有10行。

这个查询甚至不能成功，它被卡住在“拷贝到临时表”的过程中。

如何重新设计这个查询以使其工作（更快）？

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  a   index       PRIMARY 4       1   
1   SIMPLE  b   ref organisation_id organisation_id 4   fuel.a.id   70  Using index
1   SIMPLE  c   ref organisation_id organisation_id 4   fuel.a.id   15  Using index
1   SIMPLE  e   ref organisation_id organisation_id 4   fuel.a.id   5   
1   SIMPLE  f   ref organization_id organization_id 5   fuel.a.id   1   Using index

- Sander

---

EXPLAIN - h2ooooooo

首先，您可以使用explain来查看是什么导致了这个缓慢的问题；其次，您正在进行许多连接操作；第三，查看您拥有的索引或者需要创建一些索引。 - Risto Novik

你需要将 a.name 添加到你的 GROUP BY 子句中吗？ - BellevueBob

@Sander，你说“所有表都有适当的索引”，但是在连接fleets时没有使用索引？ - h2ooooooo

@h2ooooooo 它有索引，但奇怪的是它没有说明..但无论如何，下面的答案有效，所以我很高兴:D - Sander

Answer 1

您的连接彼此不依赖，这就是为什么临时表会扩大的原因。

一个简单的解决方法是：

SELECT 
    a.id,
    a.name, 
    (select count(*) from vessels b where a.id = b.organization_id group by b.organization_id),
    (select count(*) from licenses b where a.id = b.organization_id group by b.organization_id),
    (select count(*) from fleets b where a.id = b.organization_id group by b.organization_id),
    (select count(*) from users b where a.id = b.organization_id group by b.organization_id),
FROM 
    organizations a

如果您按照以下方式进行操作，速度会更快：

SELECT 
    a.id,
    a.name,
    v.total,
    w.total,        
    x.total,
    y.total
FROM 
    organizations a
LEFT JOIN (select b.organizantion_id, count(*) total from vessels b group by b.organization_id) v on v.organization_id=a.id
LEFT JOIN (select b.organizantion_id, count(*) total from licenses b group by b.organization_id) w on w.organization_id=a.id
LEFT JOIN (select b.organizantion_id, count(*) total from fleets b group by b.organization_id) x on x.organization_id=a.id
LEFT JOIN (select b.organizantion_id, count(*) total from users b group by b.organization_id) y on y.organization_id=a.id

- Chris

---

我已经重新构建了你的查询，链接在这里：http://pastebin.com/ueMpa7kt，速度非常快！谢谢！加加分给你！ - Sander