Skip to main content
 首页 » 编程设计

mysql之使用 LEFT JOIN 提高性能

2024年02月13日20freeliver54

我有一个 mysql 查询，有 5 或 6 个 LEFT JOINS。正如预期的那样，这非常慢。考虑到我只期望 ~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)

但是当我运行查询时，花了 0.14 秒。那是很长一段时间吗？在实现 memcached 之前，我试图弄清楚是否有好的查询。

请您参考如下方法:

这么多的 JOIN 可能是一个非常糟糕的主意，但您应该首先显示您的查询。

首先需要索引来加快查询速度。如果您没有，您可能应该创建一些(取决于您执行的查询)。

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

您可以引用MySQL's documentation on optimization ，具体来说LEFT JOIN optimization和 optimization using indexes 。这可能会为您提供更多详细信息。


  文章来源：
  https://www.itguest.com/post/hgiabj2b8.html
阅读延展