时间戳

首页 > 解决方案 > 在 JOIN 上优化 GROUP BY

mysql - 在 JOIN 上优化 GROUP BY

问题描述

SELECT 
 h1.Date AS Date,
 h1.Currency AS Currency1,
 h2.Currency AS Currency2,
 h1.Account AS Account,
 SUM(h1.Size) AS Size1,
 SUM(h2.Size) AS Size2,
 h1.MaturityDate AS MaturityDate
FROM
 htable h1 JOIN htable h2 ON 
 h1.AssetClass = 'FX' AND
 h2.AssetClass = 'FX' AND
 h1.Date = h2.Date AND
 (SUBSTR(h1.Ticker, 7, 3) <> h1.Currency) AND
 (SUBSTR(h2.Ticker, 7, 3) = h2.Currency) AND
 h1.SecID = h2.SecID
GROUP BY h1.Date, h1.Currency, h2.Currency, h1.Account, h1.MaturityDate
HAVING SUM(h1.Size) <> 0 AND SUM(h2.Size) <> 0)

当查询特定的Date时，上面没有 GROUP BY 子句的查询很快。

问题是当我添加 GROUP BY 时，它变得非常慢，即使我在 HAVING 子句中添加了一个特定的日期。

我已经为Date、AssetClass、SecID、Currency、MaturityDate列添加了htable索引，还为（Date、Account、Currency、MaturityDate）的组合添加了索引。

这是在 MySQL 中完成的。您对如何加快查询有任何提示（我计划将查询用于视图定义）？谢谢！

标签： mysqlsql

解决方案

我能看到的第一个可以提高查询性能的索引是：

htable (AssetClass, Date, SecID) -- if Date is more selective than SecID

或者

htable (AssetClass, SecID, Date) -- if SecId is more selective than Date

现在，如果您想更进一步，可以通过以下方式为 的虚拟列建立索引SUBSTR(h2.Ticker, 7, 3)：

alter table htable add ticker_currency varchar(3) 
  generated always as (SUBSTR(h2.Ticker, 7, 3)) virtual;

然后，添加索引：

htable (AssetClass, SecID, Date, ticker_currency)

推荐阅读