MySQL -通过连接优化选择大表

Question

我做了一些调查，在类似的问题上完全找不到任何帮助。基本上，我有两个表，一个存储用户帖子，另一个保存表a“市场”中记录的适度性。

表模式A(市场)

-- has 15 million records
create table marketPlace(
    ID          bigint auto_increment primary key,
    USER_ID     bigint                            not null,
    TITLE       varchar(60)                       not null,
    DESCRIPTION varchar(1000) collate utf8mb4_bin not null,
    COUNTRY     varchar(2)                        not null comment 'Country iso_code.',
    VIEWS       bigint default 0                  not null,
    DATE        bigint                            not null
);
create index COUNTRY on marketPlace (COUNTRY);
create index DATE on marketPlace (DATE);
create index ID_and_country_index on marketPlace (ID, COUNTRY);

表模式B(适度)

-- also has 15 million records
create table moderation(
    ID          bigint auto_increment primary key,
    adID        bigint not null comment 'The ID of the post being flagged',
    mode_flags  json   not null comment 'Flags added by approved moderators',
    flag_status bigint as (json_unquote(json_extract(`mode_flags`, '$.mod.flag'))) stored comment 'Post moderation status',
    date        bigint not null
);
create index composite_index on moderation (adID, flag_status);
create index flag_status on moderation (flag_status);
create index modID on moderation (modID);

正在使用下面的select和join查询，该查询耗时近2分钟。

SELECT
    market_place.COUNTRY
    ,moderation.flag_status

FROM marketPlace market_place
    #use index (COUNTRY)

     INNER JOIN (
        SELECT adID, flag_status
        FROM moderation #use INDEX(composite_index)
    )moderation ON(moderation.flag_status = 1 AND market_place.ID = moderation.adID)
WHERE market_place.COUNTRY='GB'
LIMIT 25;

正如您在上面的查询中所看到的，我也尝试过强制MySQL使用索引，并且在120秒钟左右之后仍然会得到结果。我确实在查询上运行了一个快速的explain，并显示索引正在被使用，但不知怎么的，返回几个记录仍然需要很长的时间。

解释结果：

#   id  select_type  table           partitions     type        possible_keys                       key         key_len    ref                              rows        filtered    Extra
1   1   SIMPLE       market_place    NULL           ref         PRIMARY,COUNTRY                     COUNTRY     10         const                            7143700     100         Using index
2   1   SIMPLE       moderation      NULL           eq_ref      adID,flag_status,composite_index    adID        8          main_database.market_place.ID    1           50          Using where

是否有优化和加速上述查询的方法？任何帮助都会受到感谢。谢谢。

Answer 1

各国都有标准的2字母缩写。使用它们，而不是去查找它们。这将消除联接，并可能使查询更快。

如果您需要从任何一个表中的其他列，那么状态！！

这可能有助于：

INDEX(flag_status,  adID)

ON与WHERE的约定是将定义关系的条件放在ON中，并在WHERE中添加“筛选”子句。(对于INNER JOIN，它们是等价的，但它可以帮助人类做出区分。)

使用COUNTRY作为PRIMARY KEY，而不是4倍大的人工BIGINT！