mysql - SELECT 中的子查询还是 JOIN 中的子查询？

Question

我有一个以下形式的 MYSQL 查询:

SELECT
    employee.name,
    totalpayments.totalpaid
FROM
    employee
    JOIN (
        SELECT
            paychecks.employee_id,
            SUM(paychecks.amount) totalpaid
        FROM
            paychecks
        GROUP BY
            paychecks.employee_id
         ) totalpayments on totalpayments.employee_id = employee.id

我最近发现这种形式的返回速度要快得多:

SELECT
    employee.name,
    (
        SELECT
            SUM(paychecks.amount)
        FROM
            paychecks
        WHERE
            paychecks.employee_id = employee.id
    ) totalpaid
FROM
    employee

令我惊讶的是速度会有差异，并且较低的查询会更快。我更喜欢上面的形式进行开发，因为我可以独立运行子查询。

有没有一种方法可以实现“两全其美”:快速返回结果并能够单独运行子查询？

Answer 1

很可能，相关子查询能够有效地利用索引，这就是它速度快的原因，即使该子查询必须执行多次。

对于使用内联 View 的第一个查询，这会导致 MySQL 创建一个派生表，对于大型集合，这实际上是一个 MyISAM 表。

在 MySQL 5.6.x 及更高版本中，优化器可能会选择在派生表上添加索引(如果这允许 ref 操作)以及 ref 的估计成本code> 操作低于嵌套循环扫描。

我建议您尝试使用EXPLAIN来查看访问计划。 (根据您的性能报告，我怀疑您运行的是 MySQL 5.5 或更早版本。)

<小时/>

如果 employees 中的某些行在 paychecks 中没有匹配的行，则这两个语句并不完全等效。

可以完全避免子查询来获得等效结果:

SELECT e.name
     , SUM(p.amount) AS total_paid
  FROM employee e 
  JOIN paychecks p
    ON p.employee_id = e.id
 GROUP BY e.id

(使用内部联接来获取与第一个查询等效的结果，使用 LEFT 外部联接来等效于第二个查询。将 SUM() 聚合包装在 IFNULL 中 函数(如果您希望在工资支票中找不到具有非空值 amount 的匹配行时返回零而不是 NULL 值。)