代码之家 › 专栏 › 技术社区 › user3720435

使用子查询加快查询速度

mysql

user3720435 · 技术社区 · 6 年前

下面的mysql查询从父表中选择行并获取子表的计数和和和。有办法提高性能吗?

select
    parent.id,
    parent.date,
    parent.name,
    (select count(child.id) 
     from child 
     where child.parentid = parent.id) as count,
    (select sum(child.amount) 
     from child 
     where child.parentid = parent.id) as sum
from 
    parent

3 回复 | 直到 6 年前

Yogesh Sharma 6 年前

也许 JOIN 是一种有效的方法:

select p.id, p.date, p.name, c.count, c.sum
from parent p left join (
        select parentid , count(*) as count, sum(amount) as sum
        from child
        group by parentid 
       ) c on c.parentid = p.id;

Lukasz Szozda 6 年前

一种方法是 OUTER APPLY/LATERAL JOIN 要避免复制子查询,请执行以下操作:

select
  parent.id,
  parent.date,
  parent.name,
  sub.cnt,
  sub.total
from parent
OUTER APPLY (select count(child.id) AS cnt, sum(child.amount) AS total
             from child where child.parentid = parent.id) sub

刚刚看到mysql标记:

SELECT parent.id,
  parent.date,
  parent.name,
  count(child.id) AS cnt,
  sum(child.amount) AS total
FROM parent
LEFT JOIN child 
  ON child.parentid = parent.id
GROUP BY parent.id, parent.date, parent.name;

llessurt 6 年前

有时可以归结为尝试改变查询和评估执行计划,但如果当前查询计划正在对每个父记录进行查找,则执行左联接然后分组可能会更快。

SELECT p.id, p.[date], p.name, COUNT(c.id) AS childCount, SUM(c.amount) AS childSum
FROM parent AS p
LEFT JOIN child AS c ON c.parentid = p.id
GROUP BY p.id, p.[date], p.name