首页 文章

如何将IN条件转换为INNER JOIN条件 - 连接速度较慢

提问于
浏览 次
0

我有一个在INNER JOIN条件下非常慢的查询，但在WHERE IN子句中使用时更快：

Slower Inner join:

SELECT  * 
FROM cases
left join
(
    select tst.team_set_id 
    from team_sets_teams tst 
    INNER JOIN team_memberships team_memberships 
    ON tst.team_id = team_memberships.team_id
       AND team_memberships.user_id = '1'
       AND team_memberships.deleted=0 group by tst.team_set_id
) cases_tf 
ON cases_tf.team_set_id = cases.team_set_id
LEFT JOIN contacts_cases 
ON contacts_cases.case_id = cases.id 
   AND contacts_cases.deleted = 0
where cases.deleted=0 
ORDER BY cases.name LIMIT 0,20;

Faster where in:

SELECT  *
FROM cases 
LEFT JOIN contacts_cases 
ON contacts_cases.case_id = cases.id 
   AND contacts_cases.deleted = 0
where cases.deleted=0 
  and cases.team_set_id in  (
            select tst.team_set_id 
            from team_sets_teams tst
            INNER JOIN team_memberships team_memberships 
            ON tst.team_id = team_memberships.team_id
               AND team_memberships.user_id = '1'
               AND team_memberships.deleted=0 
           group by tst.team_set_id
)
ORDER BY cases.name LIMIT 0,20;

INNER JOIN和WHERE IN子句的解释计划如下：

Inner Join:

+----+-------------+------------------+------+--------------------------------------------+---------------------+---------+-----------------------------------+--------+----------------------------------------------+
| id | select_type | table            | type | possible_keys                              | key                 | key_len | ref                               | rows   | Extra                                        |
+----+-------------+------------------+------+--------------------------------------------+---------------------+---------+-----------------------------------+--------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>       | ALL  | NULL                                       | NULL                | NULL    | NULL                              |      4 | Using temporary; Using filesort              |
|  1 | PRIMARY     | cases            | ref  | idx_cases_tmst_id                          | idx_cases_tmst_id   | 109     | cases_tf.team_set_id              | 446976 | Using where                                  |
|  1 | PRIMARY     | contacts_cases   | ref  | idx_con_case_case                          | idx_con_case_case   | 111     | sugarcrm.cases.id                 |      1 |                                              |
|  2 | DERIVED     | team_memberships | ref  | idx_team_membership,idx_teammemb_team_user | idx_team_membership | 109     |                                   |      2 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | tst              | ref  | idx_ud_team_id                             | idx_ud_team_id      | 109     | sugarcrm.team_memberships.team_id |      1 | Using where                                  |
+----+-------------+------------------+------+--------------------------------------------+---------------------+---------+-----------------------------------+--------+----------------------------------------------+

While in condition:

------+-----------------------------------+------+----------------------------------------------+
| id | select_type        | table            | type  | possible_keys                              | key                 | key_len | ref                               | rows | Extra                                        |
+----+--------------------+------------------+-------+--------------------------------------------+---------------------+---------+-----------------------------------+------+----------------------------------------------+
|  1 | PRIMARY            | cases            | index | NULL                                       | idx_case_name       | 768     | NULL                              |   20 | Using where                                  |
|  1 | PRIMARY            | contacts_cases   | ref   | idx_con_case_case                          | idx_con_case_case   | 111     | sugarcrm.cases.id                 |    1 |                                              |
|  2 | DEPENDENT SUBQUERY | team_memberships | ref   | idx_team_membership,idx_teammemb_team_user | idx_team_membership | 109     | const                             |    2 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | tst              | ref   | idx_ud_team_id                             | idx_ud_team_id      | 109     | sugarcrm.team_memberships.team_id |    1 | Using where                                  |
+----+--------------------+------------------+-------+--------------------------------------------+---------------------+---------+-----------------------------------+------+----------------------------------------------+

虽然有索引，但我无法弄清楚是什么问题 . 请帮帮我 . 谢谢 . （这是在sugarcrm中的查询）

mysql performance join sugarcrm

1 回答

  • 1

    无法将 IN 条件转换为 INNER JOIN .

    具有 IN 条件的查询称为 semi-join .
    半连接从一个表返回行，这些表将与另一个表连接，但不执行完整的连接 .

    以下是使用IN运算符的半连接查询的简单示例：

    SELECT *
FROM table1
WHERE some-column IN (
   SELECT some-other-column
   FROM table2
   WHERE some-conditions
)

    上面的半连接可以转换为语义等效的查询
    （eqivalent - 表示 giving exactly same results ）
    使用EXISTS运算符和dependend子查询：

    SELECT *
FROM table1
WHERE EXISTS(
   SELECT 1
   FROM table2
   WHERE some-conditions
     AND table1.some-column = table2.some-other-column
)

    大多数领先的数据库对上述两个查询使用相同的计划，并且它们的速度相同，
    不幸的是，对于MySql来说并非总是如此 .

    连接和半连接是完全不同的查询，具有完全不同的执行计划，
    所以比较他们的速度就像比较苹果和洋葱 .

    您可以尝试将带有IN的第一个查询转换为带有EXIST的查询，但不能转换为连接 .

    回复于

相关问题