mysql - 左连接运行速度比内连接快得多

Question

我一直在尝试优化查询的性能，而我已经设法做到了这一点。使用左连接从 36.7 秒减少到 3.3 秒，但我不太明白为什么左连接与普通连接相比让我的性能有了如此大的飞跃。

两个查询 repo 的结果集是一样的。

这里是带有 EXPLAIN... 的查询

正常加入:36.7 秒

SELECT t1.entityId, SUM(t2.gbp) AS amount
FROM transactionsV2 t1
JOIN
(
    SELECT
    t.uniqueId,
    ROUND((CASE WHEN t.currency != "GBP" THEN t.amount/er.exchange_rate ELSE t.amount END), 2) AS gbp
    FROM transactionsV2 t
    JOIN total_control.exchange_rates er 
    ON t.currency = er.currency
    ) t2
ON t1.uniqueId = t2.uniqueId
WHERE t1.paymentType IN ("DB", "3D")
AND t1.processing_time >= '2019-04-01 00:00:00'
AND t1.processing_time <= '2019-04-20 23:59:59'
AND t1.status = 1
GROUP BY t1.entityId

左连接:3.3 秒

SELECT t1.entityId, SUM(t2.gbp) AS amount
FROM transactionsV2 t1
LEFT JOIN
(
    SELECT
    t.uniqueId,
    ROUND((CASE WHEN t.currency != "GBP" THEN t.amount/er.exchange_rate ELSE t.amount END), 2) AS gbp
    FROM transactionsV2 t
    JOIN total_control.exchange_rates er 
    ON t.currency = er.currency
    ) t2
ON t1.uniqueId = t2.uniqueId
WHERE t1.paymentType IN ("DB", "3D")
AND t1.processing_time >= '2019-04-01 00:00:00'
AND t1.processing_time <= '2019-04-20 23:59:59'
AND t1.status = 1
GROUP BY t1.entityId

Answer 1

你能将其表示为相关子查询吗？

SELECT t.entityId,
       (SELECT  SUM(CASE WHEN t2.currency <> 'GBP' THEN t2.amount/er.exchange_rate ELSE t.amount END)j
SUM(t2.gbp) AS amount
        FROM transactionsV2 t2 JOIN
             total_control.exchange_rates er 
             ON t.currency = er.currency
        WHERE t2.uniqueid = t.uniqueid
FROM transactionsV2 t
WHERE t.paymentType IN ('DB', '3D') AND
      t.processing_time >= '2019-04-01' AND
      t.processing_time < '2019-04-21' AND
      t.status = 1;

如果是这样，您可以向基础表添加索引以提高性能。但是，我怀疑交易表真的是一个 View 。