使用LEFT JOIN提升性能

Question

我有一个MySQL查询，其中有5或6个LEFT JOIN。正如预期的那样，这相当慢。考虑到我只期望得到大约100个结果，也许对我来说运行许多单独的SQL查询并手动将它们拼接起来更有意义。我猜测这需要很长时间，因为使用了多个左连接创建了一个巨大的表。是这样吗？

我正在Rails 3中进行此操作。我知道创建活动记录很昂贵，但我认为这可能比有太多LEFT JOINS要快。我对数据库在幕后的工作知之甚少。非常感谢任何见解。

编辑：这是实际查询和表模式

查询：

SELECT people.* FROM people LEFT JOIN person_organization_relationships ON person_organization_relationships.person_id = people.id AND person_organization_relationships.stop_person IS NULL LEFT JOIN person_redirect_relationships AS r_from_others ON r_from_others.parent_id = people.id AND r_from_others.stop_person IS NULL LEFT JOIN person_redirect_relationships AS r_to_others ON r_to_others.child_id = people.id AND r_to_others.stop_person IS NULL LEFT JOIN person_organization_relationships AS r_p_check ON r_p_check.person_id = r_from_others.child_id AND r_p_check.stop_person IS NULL LEFT JOIN organization_redirect_relationships AS r_o_check ON r_o_check.child_id = person_organization_relationships.organization_id AND r_o_check.stop_organization IS NULL LEFT JOIN person_organization_relationships AS rr_p_check ON rr_p_check.person_id = r_from_others.child_id AND rr_p_check.stop_person IS NULL LEFT JOIN organization_redirect_relationships AS rr_o_check ON rr_p_check.organization_id = rr_o_check.child_id AND rr_o_check.stop_organization IS NULL WHERE (((person_organization_relationships.organization_id = 1 OR r_o_check.parent_id = 1) AND r_to_others.parent_id IS NULL) OR (r_p_check.organization_id = 1 OR rr_o_check.parent_id = 1)) GROUP BY people.id

表模式：

  create_table "people", :force => true do |t|
    t.datetime "created_at"
    t.datetime "updated_at"
    t.boolean  "delta",             :default => true, :null => false
  end


  create_table "person_organization_relationships", :force => true do |t|
    t.integer  "person_id"
    t.integer  "organization_id"
    t.integer  "start_person"
    t.integer  "stop_person"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  add_index "person_organization_relationships", ["organization_id"], :name => "index_person_organization_relationships_on_organization_id"
  add_index "person_organization_relationships", ["person_id"], :name => "index_person_organization_relationships_on_person_id"
  add_index "person_organization_relationships", ["start_person"], :name => "index_person_organization_relationships_on_start_person"
  add_index "person_organization_relationships", ["stop_person"], :name => "index_person_organization_relationships_on_stop_person"

  create_table "person_redirect_relationships", :force => true do |t|
    t.integer  "parent_id"
    t.integer  "child_id"
    t.integer  "start_person"
    t.integer  "stop_person"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  add_index "person_redirect_relationships", ["child_id"], :name => "index_person_redirect_relationships_on_child_id"
  add_index "person_redirect_relationships", ["parent_id"], :name => "index_person_redirect_relationships_on_parent_id"
  add_index "person_redirect_relationships", ["start_person"], :name => "index_person_redirect_relationships_on_start_person"
  add_index "person_redirect_relationships", ["stop_person"], :name => "index_person_redirect_relationships_on_stop_person"


  create_table "organization_redirect_relationships", :force => true do |t|
    t.integer  "parent_id"
    t.integer  "child_id"
    t.integer  "start_organization"
    t.integer  "stop_organization"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  add_index "organization_redirect_relationships", ["child_id"], :name => "index_organization_redirect_relationships_on_child_id"
  add_index "organization_redirect_relationships", ["parent_id"], :name => "index_organization_redirect_relationships_on_parent_id"
  add_index "organization_redirect_relationships", ["start_organization"], :name => "index_organization_redirect_relationships_on_start_organization"
  add_index "organization_redirect_relationships", ["stop_organization"], :name => "index_organization_redirect_relationships_on_stop_organization"

---

这个查询没有返回任何结果。

+----+-------------+-----------------------------------+--------+----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+------------------------------------------------------------------------+------+---------------------------------+

| id | select_type | table | type | possible_keys
| key | key_len | ref
| rows | Extra |

+----+-------------+-----------------------------------+--------+----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+------------------------------------------------------------------------+------+---------------------------------+

| 1 | SIMPLE | person_details | ALL | index_person_details_on_current_p_id
| NULL | NULL | NULL
| 4938 | Using temporary; Using filesort |

| 1 | SIMPLE | people | eq_ref | PRIMARY
| PRIMARY | 4 | knolcano_development.person_details.current_p_id
| 1 | |

| 1 | SIMPLE | person_organization_relationships | ref | index_person_organization_relationships_on_person_id,index_person_organization_relationships_on_stop_person | index_person_organization_relationships_on_person_id | 5 | knolcano_development.person_details.current_p_id
| 1 | |

| 1 | SIMPLE | r_from_others | ref | index_person_redirect_relationships_on_parent_id,index_person_redirect_relationships_on_stop_person | index_person_redirect_relationships_on_stop_person | 5 | const
| 3 | |

| 1 | SIMPLE | r_to_others | ref | index_person_redirect_relationships_on_child_id,index_person_redirect_relationships_on_stop_person | index_person_redirect_relationships_on_child_id | 5 | knolcano_development.people.id
| 2 | |

| 1 | SIMPLE | r_p_check | ref | index_person_organization_relationships_on_person_id,index_person_organization_relationships_on_stop_person | index_person_organization_relationships_on_person_id | 5 | knolcano_development.r_from_others.child_id
| 1 | |

| 1 | SIMPLE | r_o_check | ref | index_organization_redirect_relationships_on_child_id,index_organization_redirect_relationships_on_stop_organization | index_organization_redirect_relationships_on_child_id | 5 | knolcano_development.person_organization_relationships.organization_id | 1 | |

| 1 | SIMPLE | rr_p_check | ref | index_person_organization_relationships_on_person_id,index_person_organization_relationships_on_stop_person | index_person_organization_relationships_on_person_id | 5 | knolcano_development.r_from_others.child_id
| 1 | |

| 1 | SIMPLE | rr_o_check | ref | index_organization_redirect_relationships_on_child_id,index_organization_redirect_relationships_on_stop_organization | index_organization_redirect_relationships_on_child_id | 5 | knolcano_development.rr_p_check.organization_id
| 1 | Using where |

+----+-------------+-----------------------------------+--------+----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+------------------------------------------------------------------------+------+---------------------------------+

9 rows in set (0.00 sec)

注：此SQL查询结果包含多个表格的信息，用于优化查询性能。

但是当我运行查询时，它花费了0.14秒。这算是很长的时间吗？我正在尝试确定在实现memcached之前是否有良好的查询。

- Cyrus

---

实际的查询和解释输出将是有用的信息。 - Joe Stefanelli

我同意@JoeStefanelli说的话。请展示EXPLAIN的查询和结果。 - Tadeck

Answer 1

有太多的JOIN可能是一个非常糟糕的想法，但您应该先展示您的查询。

首先，需要索引来加速查询。如果没有任何索引，则可能需要根据执行的查询创建一些索引。

如果您执行多个LEFT JOIN，则可以（可能）将它们分成不同的查询，这应该可以使应用程序运行得更快。

您可以参考MySQL关于优化的文档，特别是LEFT JOIN优化和使用索引的优化。这可能会给您提供额外的细节。

- Tadeck

---

好的回答，我只想强调一点，索引的不良使用是这种场景下性能问题最有可能的原因。 - Jan-Henk

Answer 2

可能有多种原因导致这种情况发生。查询性能差、索引不佳等等。解释、查询以及涉及到相关表的创建语句会在很大程度上有助于找出答案。

不过，我通常看到这种情况时，是由于索引不佳造成的。

- Jericon