在 SELECT 中使用 SUM() 更好还是在 SUB-SELECT 中使用? [英] Using SUM() in the SELECT is better or in the SUB-SELECT?

问题描述

我有两个查询结果相同，现在我想知道哪个更优化?

I have two query with the same result, now I want to know which one is more optimized?

在 SELECT 中:

select t1.*, sum(t2.value) as total_votes
from table1 t1
left join table2 t2
on t1.id = t2.post_id

在 SUB-SELECT 中:

select t1.*, t3.total_votes
from table1 t1
left join (select post_id, sum(value) as total_votes from table2 t2 group by post_id) t3
on t1.id = t3.post_id

需要注意的是，我有两个表:table1(posts)，table2(votes).上面两个查询是计算每个帖子的总票数.

It should be noted, I have two table: table1(posts), table2(votes). And the two above query are for calculating total votes for each post.

那么，哪个更快、更优化?

So, which one is faster and more optimized?

推荐答案

想必，您打算第一个查询是:

Presumably, you intend for the first query to be:

select t1.*, sum(t2.value) as total_votes
from table1 t1 left join
     table2 t2
     on t1.id = t2.post_id
group by t1.id;

您的问题没有正确答案，除了检查查询和数据的性能.为什么?您有两个相互竞争的事情在进行:优化联接和优化聚合.

There is no right answer to your question, other than checking the performance on both queries and the data. Why? You have two competing things going on: optimizing the join and optimizing the aggregation.

第一个查询可以使用索引进行连接——这使得效率更高.但是，聚合可能有很多数据，这是一个问题.

The first query can use indexes for the join -- which makes that much more efficient. However, there may be lots of data for the aggregation, which is a problem.

第二个可以进行聚合——可能是在非常少的数据上(想象一下 t2 有一行)——但是 join 可能无法进行充分利用索引.其他数据库有更强大的连接和聚合算法.但在 MySQL 中，你确实需要测试.

The second can do the aggregation -- potentially on very little data (imagine that t2 has one row) -- but then the join may not be able to make full use of an index. Other databases have more powerful algorithms for joins and aggregations. But in MySQL, you really need to test.

第三个版本通常比这两个版本都更好:

There is a third version that can often work better than either of these:

select t1.*, 
       (select sum(t2.value)
        from table2 t2
        where t1.id = t2.post_id
       ) as total_votes
from table1 t1;

当您有 where 和/或 order by 子句仅包含对 table1 的引用时，这尤其有效.

This is particularly effective when you have where and/or order by clauses that only contain references to table1.

这篇关于在 SELECT 中使用 SUM() 更好还是在 SUB-SELECT 中使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！