如何让子查询更快

Question

对于作者概述，我们正在寻找一个查询，该查询将显示所有作者，包括他们最好的书。此查询的问题是速度不够。只有大约 1500 位作者，生成概述的查询目前需要 20 秒。

主要问题似乎是生成每个人对所有书籍的平均评分。 通过选择以下查询，它仍然相当快。

select
    person.id as pers_id,
    person.firstname,
    person.suffix,
person.lastname,
    thriller.title,
    year(thriller.orig_pubdate) as year,
    thriller.id as thrill_id,
    count(user_rating.id) as nr,
    AVG(user_rating.rating) as avgrating
from 
    thriller 
inner join 
    thriller_form 
    on thriller_form.thriller_id = thriller.id
inner join 
    thriller_person 
    on thriller_person.thriller_id = thriller.id 
    and thriller_person.person_type_id = 1 
inner join 
    person 
    on person.id = thriller_person.person_id
left outer join
    user_rating
    on user_rating.thriller_id = thriller.id 
    and user_rating.rating_type_id = 1
where thriller.id in
    (select top 1 B.id from thriller as B
    inner join thriller_person as C on B.id=C.thriller_id
    and person.id=C.person_id)
group by
    person.firstname,
    person.suffix,
    person.lastname,
    thriller.title,
    year(thriller.orig_pubdate),
    thriller.id,
    person.id
order by
    person.lastname

但是，如果我们通过选择具有平均评分的书籍来使子查询变得更复杂一点，则需要整整 20 秒才能生成结果集。 查询将如下所示：

select
    person.id as pers_id,
    person.firstname,
    person.suffix,
    person.lastname,
    thriller.title,
    year(thriller.orig_pubdate) as year,
    thriller.id as thrill_id,
    count(user_rating.id) as nr,
    AVG(user_rating.rating) as avgrating
from 
    thriller 
inner join 
    thriller_form 
    on thriller_form.thriller_id = thriller.id
inner join 
    thriller_person 
    on thriller_person.thriller_id = thriller.id 
    and thriller_person.person_type_id = 1 
inner join 
    person 
    on person.id = thriller_person.person_id
left outer join
    user_rating
    on user_rating.thriller_id = thriller.id 
    and user_rating.rating_type_id = 1
where thriller.id in
    (select top 1 B.id from thriller as B
    inner join thriller_person as C on B.id=C.thriller_id
    and person.id=C.person_id
    inner join user_rating as D on B.id=D.thriller_id
    group by B.id
    order by AVG(D.rating))
group by
    person.firstname,
    person.suffix,
    person.lastname,
    thriller.title,
    year(thriller.orig_pubdate),
    thriller.id,
    person.id
    order by
    person.lastname

有人对加快此查询有好的建议吗？

Answer 1

计算平均值需要进行表扫描，因为您必须对值进行求和，然后除以（相关）行数。这反过来意味着您需要进行大量的重新扫描；那很慢。你能计算一次平均值并存储它们吗？这将使您的查询使用这些预先计算的值。 （是的，它对数据进行非规范化，但对性能进行非规范化通常是必要的；性能和最少数据之间需要进行权衡。）

使用临时表作为平均值的存储可能是合适的。