優化獲取期初餘額的查詢

我有這樣的查詢

select org, `Balance` from oss_collection_history och2 
 where och2.`DateFrom` = 
   (select min(`DateFrom`) from oss_collection_history och3 where och3.org = och2.org)

它有效，但使用我的數據集大約需要 4 秒。

解釋輸出如下所示

id|select_type |table|partitions|type|possible_keys |key|key_len|ref |rows|filtered|Extra |
--|------------------|-----|----------|----|----------------------------------|---|-------|------------|----|--------|-----------|
1|初級 |och2 | |全部 | | | | |6651| 100|用在哪裡|
2|相關子查詢|och3 | |ref |Org,oss_collection_history_Org_IDX|Org|22 |ca1.och2.Org| 443| 100|使用索引|

我嘗試添加以下指標，但似乎並沒有提高性能

CREATE INDEX `oss_collection_history_Charges_IDX3` 
ON oss_collection_history (`DateFrom`, `org`);

CREATE INDEX `oss_collection_history_Charges_IDX4` 
ON oss_collection_history (`DateFrom`);

CREATE INDEX `oss_collection_history_Charges_IDX6` 
ON oss_collection_history (`DateFrom`, `org`, `Balance`);

我為您的查詢創建了一個最小、完整且可驗證的範例。

CREATE TABLE oss_collection_history
(
   org int
   , Balance int
   , DateFrom date
);

INSERT INTO oss_collection_history VALUES (1, 1, '2019-01-01');
INSERT INTO oss_collection_history VALUES (1, 2, '2019-01-02');
INSERT INTO oss_collection_history VALUES (2, 1, '2019-01-03');
INSERT INTO oss_collection_history VALUES (2, 2, '2019-01-04');

CREATE INDEX oss_collection_history_Charges_IDX2 
ON oss_collection_history (org, DateFrom, Balance);

此查詢使用索引返回單個row-per-org以及相關的期初餘額：

SELECT och1.org
   , och1.Balance
   , och1.DateFrom
FROM oss_collection_history och1
INNER JOIN (
   SELECT MIN(och2.DateFrom) AS MinDateFrom
       , och2.org
   FROM oss_collection_history och2 
   GROUP BY och2.org
) t ON och1.org = t.org AND och1.DateFrom = t.MinDateFrom;

結果：

╔══════╦══════════╦════════════╗
║ org ║ 餘額 ║ DateFrom ║
╠══════╬══════════╬════════════╣
║ 1 ║ 1 ║ 2019-01-01 ║
║ 2 ║ 1 ║ 2019-01-03 ║
╚══════╩══════════╩════════════╝

這個小提琴就在這裡。

引用自：https://dba.stackexchange.com/questions/257795