如何提高性能使最近的记录显示在列表中，最近的前五名最多

Question

我正在制作一个示例最近的屏幕，它将显示一个列表，其中id设置为主键。

我已经按预期完成了正确的查询，但数据量大的表可能会导致性能下降。

这是下面的示例查询：

SELECT  distinct H.id    -- (Primary Key),
H.partnerid as PartnerId,
H.partnername AS partner, H.accountname AS accountName,
H.accountid as AccountNo,
FROM  myschema.mytransactionstable H
INNER JOIN (
SELECT  S.accountid, S.partnerid, S.accountname,
max(S.transdate) AS maxDate
from  myschema.mytransactionstable S
group by  S.accountid, S.partnerid, S.accountname
) ms  ON H.accountid = ms.accountid
AND  H.partnerid = ms.partnerid
AND  H.accountname =ms.accountname
AND  H.transdate = maxDate
WHERE  H.accountid = ms.accountid
AND  H.partnerid = ms.partnerid
AND  H.accountname = ms.accountname
AND  H.transdate = maxDate
GROUP BY  H.partnerid,H.accountid, H.accountname
ORDER BY  H.id DESC
LIMIT  5

在我的例子中，有一些值在所选列中相似，但仅在id的中不同

以下是未执行上述查询的图像链接。它们都是尚未筛选的记录。样本结果查询点击此处

由于我只想通过它们的id获得最近的5个，但其他列可以包含类似的值accountname,accountid,partnerid。

我已经得到了正确的查询，但是，我想提高查询的性能。有什么改进查询的建议吗？

Answer 1

您可以尝试使用row_number()

select * from
(
select *,row_number() over(order by transdate desc) as rn
from myschema.mytransactionstable
)A where rn<=5

Answer 2

• 不要重复ON和WHERE子句。使用CCD_ 5来说明表(或子查询(是如何"；相关的"；；使用WHERE进行筛选(即保留哪些行(。可能在您的情况下，应该删除所有WHERE。

• 请提供SHOW CREATE TABLE

• 这个"复合"索引可能会有所帮助，因为它处理子查询和JOIN:

  INDEX(partnerid, accountid, accountname, transdate)
  

• 这也将避免对GROUP BY进行单独排序。

• 但是ORDER BY是不同的，所以它不能避免排序。

• 此可能在不更改结果集排序的情况下避免排序：ORDER BY partnerid, accountid, accountname, transdate DESC

• 如果您还有其他问题，请提供EXPLAIN SELECT ...和EXPLAIN FORMAT=JSON SELECT ...。

如果我们无法获得索引来处理WHERE、GROUP BY、和ORDER BY，则查询将在看到LIMIT 5之前生成所有行。如果索引确实有效，则外部查询将在5之后停止，这可能会节省大量费用。