主页 / dba / 问题 / 257795
Accepted
Archimedes Trajano
Asked: 2020-01-21 09:25:17 +0800 CST

优化获取期初余额的查询

  • 772

我有这样的查询

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`);
mysql mysql-5.6

1 个回答

  1. Best Answer

    我为您的查询创建了一个最小、完整且可验证的示例。

    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 ║
╚══════╩══════════╩════════════╝

    这个小提琴就在这里。

    • 2

相关问题