mysql - 内部联接中的临时表与子查询

Question

两种SQL，返回相同的结果。我的第一个连接是在子查询上，第二个是最终查询是一个与之前我创建/填充它们的临时连接

SELECT COUNT(*) totalCollegiates, SUM(getFee(c.collegiate_id, dateS)) totalMoney
FROM collegiates c
LEFT JOIN (
    SELECT collegiate_id FROM collegiateRemittances r
    INNER JOIN remittances r1 USING(remittance_id)
    WHERE r1.type_id = 1 AND r1.name = remesa   
) hasRemittance ON hasRemittance.collegiate_id = c.collegiate_id
WHERE hasRemittance.collegiate_id IS NULL AND c.typePayment = 1 AND c.active = 1 AND c.exentFee = 0 AND c.approvedBoard = 1 AND IF(notCollegiate, c.collegiate_id NOT IN (notCollegiate), '1=1');



DROP TEMPORARY TABLE IF EXISTS hasRemittance;

CREATE TEMPORARY TABLE hasRemittance
    SELECT collegiate_id FROM collegiateRemittances r
    INNER JOIN remittances r1 USING(remittance_id)
    WHERE r1.type_id = 1 AND r1.name = remesa;

SELECT COUNT(*) totalCollegiates, SUM(getFee(c.collegiate_id, dateS)) totalMoney
FROM collegiates c
LEFT JOIN hasRemittance ON hasRemittance.collegiate_id = c.collegiate_id
WHERE hasRemittance.collegiate_id IS NULL AND c.typePayment = 1 AND c.active = 1 AND c.exentFee = 0 AND c.approvedBoard = 1 AND IF(notCollegiate, c.collegiate_id NOT IN (notCollegiate), '1=1');

对于几千条记录，哪个性能更好？

Answer 1

除了您的显式临时表版本是 3 个 sql 语句而不是 1 个之外，这两个公式是相同的。也就是说，来回服务器的开销使其变慢。但是……

由于隐式临时表位于 LEFT JOIN 中，因此可以通过以下两种方式之一评估该子查询...

• 旧版本的 MySQL 被“转储”并重新评估。因此很慢。
• 较新的版本会自动创建一个索引。因此很快。

与此同时，您可以通过添加合适的索引来加速显式临时表版本。它将是 PRIMARY KEY(collegiate_id)。如果 INNER JOIN 有可能产生重复项，则说 SELECT DISTINCT。

对于“几千”行，您通常不需要担心性能。

Oracle 拥有无数的选择。 MySQL 很少，默认的(通常)是最好的。因此请忽略讨论您可以在 MySQL 中使用的各种选项的答案。

有问题

AND  IF(notCollegiate,
        c.collegiate_id NOT IN (notCollegiate),
        '1=1')

我不知道notCollegiate在哪个表中。notCollegiate不能是一个列表，那为什么要使用IN呢？而是简单地使用 !=。最后，'1=1' 是一个 3 个字符的字符串；你真的想要那个吗？

为了性能(任一版本)

• remittances 需要 INDEX(type_id, name, remittance_id) 并且 remittance_id 特别是最后。
• collegiateRemittances 需要 INDEX(remittance_id)(除非它是 PK)。
• collegiates 需要 INDEX(typePayment, active, exentFee , approvedBoard) 任何顺序。

底线:比起如何制定查询，更需要担心索引。

哎呀。又一条皱纹。什么是 getFee()？如果它是存储函数，也许我们需要担心优化它？什么是 dateS？