自行联接表-性能 [英] Join table on itself - performance

问题描述

我希望获得以下加入的帮助. 我有一个表(约2000万行)，由以下部分组成:

I would like some help with the following join. I have one table (with about 20 million rows) that consists of:

MemberId(主键)| ID(主键)|交易日期|余额

我想在一个查询中为所有客户获得最新的余额. 我知道我可以做这样的事情(我只是从记忆中写出来的).但是这种方式非常慢.

I would like to get the latest Balance for all the customers in one query. I know I could do something like this (I just wrote it from my memory). But this way is terribly slow.

SELECT * 
FROM money 
WHERE money.Id = (SELECT MAX(Id) 
                  FROM money AS m 
                  WHERE m.MemberId = money.MemberId)

还有其他(更快/更智能)的选择吗?

Are there any other (faster/smarter) options?

推荐答案

在我所经历的所有优化教程和屏幕录像中，与子查询相比，连接总是受到青睐.使用子查询时，将为每个比较执行该子查询，其中与一次连接一样.

In all optimization tutorials and screencasts that I've endured through, joins are always favoured over subqueries. When using a sub-query the sub-query is executed for each comparison, where as with a join only once.

SELECT * 
FROM money m
INNER JOIN (
    SELECT memberId, MAX(id) AS maxid
    FROM money
    GROUP BY memberId
) mmax ON mmax.maxid = m.id AND mmax.memberId = m.memberId

这篇关于自行联接表-性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！