mysql - 使用 order by 的 SQL 查询运行速度非常慢

Question

我有两张 table 。 上传 和利润。

上传:

╔════╦══════════════╦══════════════════╗
║ id ║  name        ║ more columns...  ║
╠════╬══════════════╬══════════════════╣
║  1 ║ Jeff Atwood  ║ ................ ║
║  2 ║ Geoff Dalgas ║ ................ ║
║  3 ║ Jarrod Dixon ║ ................ ║
║  4 ║ Joel Spolsky ║ ................ ║
╚════╩══════════════╩══════════════════╝

利润:

╔══════════╦══════════════╦══════════════════╗
║ uploadid ║    amount    ║ more columns...  ║
╠══════════╬══════════════╬══════════════════╣
║    1     ║     4.0      ║ ................ ║
║    1     ║     7.2      ║ ................ ║
║    3     ║     6.3      ║ ................ ║
║    4     ║     2.5      ║ ................ ║
╚══════════╩══════════════╩══════════════════╝

如您所见，uploads.id => profits.uploadid

我想显示 uploads 表中的一些行，再显示一列，告诉我有多少“利润”。

结果示例:

╔════╦══════════════╦════════════════╦══════════════════╗
║ id ║  name        ║  profitsCount  ║ more columns...  ║
╠════╬══════════════╬════════════════╬══════════════════╣
║  1 ║ Jeff Atwood  ║       2        ║ ................ ║
║  2 ║ Geoff Dalgas ║       0        ║ ................ ║
║  3 ║ Jarrod Dixon ║       1        ║ ................ ║
║  4 ║ Joel Spolsky ║       1        ║ ................ ║
╚════╩══════════════╩════════════════╩══════════════════╝

注意:在真实表中，uploads.id 和 profits.uploadid 列是 varchar 而不是 int，我在这里使用 int 是为了更清楚。

问题是，当我使用大型表(数千行)运行查询时，它会花费很多时间

我的查询:

SELECT          `uploads`.* ,COUNT(`profits`.`uploadid`) AS `numProfits`
FROM            `uploads`
LEFT JOIN       `profits`
ON              `uploads`.`id` = `profits`.`uploadid`
GROUP BY        `uploads`.`id`
ORDER BY        `numProfits`
DESC
LIMIT           30

Answer 1

这是您的查询:

SELECT u.* ,COUNT(p.uploadid) AS numProfits
FROM uploads LEFT JOIN
     profits p
     ON u.id = p.uploadid
GROUP BY u.id
ORDER BY numProfits DESC
LIMIT 30;

第一个改进:在 profits(uploadid) 上创建一个索引。这可能会解决您的问题。您可能能够通过以下方式获得更好的性能:

select u.*,
       (select count(*) from profits p where u.id = p.uploadid) as numProfits
from uploads u
order by numProfits desc
limit 30;

这消除了对文件排序聚合的需要。我更喜欢带有显式聚合的第一个版本，但子查询可以更好地工作。

您还可以在子查询中尝试聚合:

select u.*, numProfits
from uploads u join
     (select uploadid, count(*) as numProfits
      from profits p
      group by uploadid
      order by numProfits desc
      limit 30
     ) p
     on u.id = p.uploadid;
order by numProfits desc;

编辑:

对于最后一个解决方案，要选择没有利润的行，请使用 left join 和 coalesce():

select u.*, coalesce(numProfits, 0) as numProfits
from uploads u left join
     (select uploadid, count(*) as numProfits
      from profits p
      group by uploadid
      order by numProfits desc
      limit 30
     ) p
     on u.id = p.uploadid;
order by numProfits desc;