返回

MySQL 左联接过滤查询执行计划之谜: 重新排序与优化策略

mysql

MySQL查询中的左联接和过滤的执行计划差异

引言

MySQL查询优化是一门艺术，涉及理解查询执行计划，以确保最佳性能。本文将深入探讨一个包含左联接和过滤条件的MySQL查询，分析其执行计划中的差异，并提供针对此类查询优化的一些见解。

查询示例

让我们考虑以下查询：

SELECT m.title, p_cast.person_name
FROM movie m
LEFT JOIN (
    SELECT *
    FROM movie_cast m_cast
    JOIN person p_cast ON m_cast.person_id = p_cast.person_id
    WHERE p_cast.person_name = 'Brad Pitt'
) ON m.movie_id = m_cast.movie_id;

预期执行计划

根据左联接语义，预期执行顺序如下：

  1. 将movie表与子查询结果左联接，该子查询返回Brad Pitt参与的所有电影的电影ID和演员姓名。
  2. 过滤联接结果，仅保留电影ID与子查询中返回的电影ID相匹配的电影。
  3. 返回匹配电影的标题和Brad Pitt的名字，如果不存在匹配，则返回movie表的标题和NULL值。

观察到的执行计划差异

然而，在检查查询执行计划时，我们发现了一个令人惊讶的差异。执行计划表明以下步骤：

  1. movie表与movie_cast表进行内联接。
  2. 过滤联接结果，仅保留电影ID与movie_cast表中返回的电影ID相匹配的电影。
  3. 再次将过滤后的结果与person表左联接，过滤条件是person_name = 'Brad Pitt'。

解释差异

这种差异是由MySQL优化器重新排序查询造成的。优化器确定，将movie和movie_cast表进行内联接，然后再与person表进行左联接，更有效率。这种重新排序不会改变查询的最终结果。

左联接的实现

MySQL中左联接通过NOT NULL约束实现。当movie表与movie_cast表进行内联接时，movie表中的所有电影都包含在结果中，即使它们在movie_cast表中没有匹配的条目。此后，当结果与person表进行左联接时，NOT NULL约束确保movie表中的所有电影都保留在结果中，即使它们没有与Brad Pitt匹配的条目。因此，左联接的行为如预期的那样，它保留了所有电影，而不管它们是否包含Brad Pitt。

结论

执行计划中观察到的差异是由于MySQL优化器重新排序查询造成的，这不会改变查询的最终结果。左联接的行为如预期的那样，保留了movie表中的所有电影，而不管它们是否包含Brad Pitt。

常见问题解答

  1. 为什么优化器会重新排序查询？
    • 优化器尝试找到最有效率的查询执行计划，可能涉及重新排序联接顺序或使用不同的算法。
  2. 左联接与内联接有何不同？
    • 左联接保留所有左表行，即使它们在右表中没有匹配项，而内联接仅保留具有匹配项的行。
  3. NOT NULL约束在左联接中的作用是什么？
    • NOT NULL约束确保左表中的所有行都保留在左联接结果中，即使它们在右表中没有匹配项。
  4. 如何优化带有左联接的查询？
    • 考虑使用索引以提高联接性能。
    • 如果可能，避免在子查询中使用左联接，因为这可能导致嵌套循环联接。
  5. 如何解决左联接执行计划中的差异？
    • 重新排序查询以匹配预期的执行顺序。
    • 联系数据库供应商以获取支持或升级到更新版本的数据库，该版本可能使用更优化的执行计划。