从500 m行的MySQL表中获取所选行的AVG值？

Question

我在MySQL 8.x中有一张有5亿条记录的表。我获得特定结果集的常规查询是200 My，但是如果我试图获得一个AVG值，性能就会下降到30s+。

结构：

KW_ID | DATE | SERP | MERCHANT_ID | ARTICLE_ID

-- auto-generated definition
create table merchants_keyword_serps
(
    KW_ID       mediumint unsigned                        null,
    MERCHANT_ID tinyint unsigned                          null,
    ARTICLE_ID  char(10)                                  null,
    SERP        tinyint unsigned                          null,
    DATE        date                                      null,
    constraint `unique`
        unique (MERCHANT_ID, ARTICLE_ID, KW_ID, DATE),
    constraint fk_serps_kwd_t
        foreign key (MERCHANT_ID, ARTICLE_ID) references merchants_product_catalog (MERCHANT_ID, ARTICLE_ID)
            on delete cascade,
    constraint keywords
        foreign key (KW_ID) references merchants_keywords (ID)
            on delete cascade
);

create index merchants_keyword_serps_SERP_index
    on merchants_keyword_serps (SERP);

create index mks_date
    on merchants_keyword_serps (DATE);

目标，获得20220122和MERCHANT_ID =2的SERP：

select
       mcs.SERP
FROM merchants_keyword_serps mcs
WHERE date = 20220120
AND mcs.MERCHANT_ID = 2;

此外，还可以为所有商店提供AVG：

select
       mcs.SERP,
       (
        SELECT AVG(SERP)
           FROM merchants_keyword_serps mcs2
           WHERE mcs2.date = 20220120
           AND mcs2.KW_ID = mcs.KW_ID
           AND mcs2.ARTICLE_ID = mcs.ARTICLE_ID) AS SERP_AVG
from  merchants_keyword_serps mcs
WHERE
    date = 20220120
    AND mcs.MERCHANT_ID = 2;

预期的结果将是为所有具有相同KW_ID、DATE、ARTICLE_ID的商店增加一个具有平均SERP值的列。

有什么办法可以用不同的方法来加快速度吗？索引都设置好了，我相信，因为标准查询在unter 200 in中运行得非常快。

Answer 1

KW_ID从哪里来？请提供SHOW CREATE TABLE merchants_keyword_serps。

在约会中使用20220120是自找麻烦。(我还没有发现任何问题。)

加上以下内容：

INDEX(merchant_id, date)
INDEX(kw_id, article_id, date, serp)

并放弃这些，因为它们将是多余的：

INDEX(merchant_id)
INDEX(kw_id)