如何优化GROUP BY语句性能？3000万行数据聚合慢问题求助

问题背景 #

现有一个基于3000万行合并数据的SQL查询，不带GROUP BY时执行时间仅1.5-2秒，但添加GROUP BY后耗时长达5分钟。由于数据量过大，无法在代码层处理聚合（内存不足），需优化GROUP BY语句的执行性能。

原查询语句 #

select game_id,
       count(user_id),
       count(distinct user_id),
       provider_id,
       aggregator,
       country,
       sum(sum_bets),
       sum(sum_wins),
       currency,
       any_value(date)
from (select transactions.game_id                                            as game_id,
             transactions.user_id                                            as user_id,
             games.provider_id                                               as provider_id,
             games.aggregator                                                as aggregator,
             users.country                                                   as country,
             transactions.currency                                           as currency,
             IF(transactions.type_id IN (2, 16), transactions.amount, 0)     as sum_bets,
             IF(transactions.type_id IN (3, 18, 20), transactions.amount, 0) as sum_wins,
             transactions.date_create                                        as date
      from `transactions`
               inner join `games` on transactions.game_id = games.id
               inner join `users` on transactions.user_id = users.id
      where transactions.status_id = 1
        and transactions.type_id in (2, 3, 16, 18, 20)
        and transactions.game_id is not null
        and users.country is not null
      union all
      select transactions_bonus.game_id                                                    as game_id,
             transactions_bonus.user_id                                                    as user_id,
             games.provider_id                                                             as provider_id,
             games.aggregator                                                              as aggregator,
             users.country                                                                 as country,
             transactions_bonus.currency                                                   as currency,
             IF(transactions_bonus.type IN (5, 6), transactions_bonus.amount_bonus, 0)     as sum_bets,
             IF(transactions_bonus.type IN (4, 7, 10), transactions_bonus.amount_bonus, 0) as sum_wins,
             transactions_bonus.date                                                       as date
      from `transactions_bonus`
               inner join `games` on transactions_bonus.game_id = games.id
               inner join `users` on transactions_bonus.user_id = users.id
      where transactions_bonus.status in (2, 3, 4, 5, 6)
        and transactions_bonus.type in (4, 5, 6, 7, 10)
        and transactions_bonus.game_id is not null
        and users.country is not null) as popular_games
group by game_id, provider_id, aggregator, country, currency

涉及表结构 #

表transactions #

-- auto-generated definition
create table transactions
(
    id                  bigint unsigned auto_increment
        primary key,
    string_id           varchar(36)                            null,
    user_id             bigint unsigned                        not null,
    game_id             smallint unsigned                      null,
    bonus_id            smallint unsigned                      null,
    jackpot_id          bigint unsigned                        null,
    prize_id            bigint unsigned                        null,
    tournament_id       bigint unsigned                        null,
    amount              int                                    not null,
    commission          int                                    null,
    aggregator_amount   int unsigned default '0'               not null,
    aggregator_currency varchar(16) collate utf8_bin           null,
    aggregator_fee      int unsigned default '0'               not null,
    currency            varchar(3)                             not null,
    status_id           tinyint unsigned                       not null,
    balance             int                                    null,
    gateway_method      varchar(64)                            null,
    account             varchar(128) collate utf8_bin          null comment 'Аккаунт в платежной системе (номер карты, телефон, кошелек)',
    ip                  varchar(39)                            null,
    comment             varchar(128)                           null,
    date_create         datetime     default CURRENT_TIMESTAMP not null,
    date_complete       datetime                               null,
    date_cancel         datetime                               null,
    type_id             tinyint unsigned                       not null,
    manager_id          int                                    null,
    event_id            int                                    null,
    constraint transactions_ibfk_1
        foreign key (user_id) references users (id)
            on update cascade,
    constraint transactions_ibfk_2
        foreign key (game_id) references games (id)
            on update cascade,
    constraint transactions_ibfk_3
        foreign key (jackpot_id) references jackpots (id)
            on update cascade,
    constraint transactions_ibfk_4
        foreign key (tournament_id) references tournaments (id)
            on update cascade,
    constraint transactions_prize_id_foreign
        foreign key (prize_id) references prizes (id)
            on update cascade
)
    engine = InnoDB;

create index date_create
    on transactions (date_create);

create index game_id
    on transactions (game_id);

create index jackpot_id
    on transactions (jackpot_id);

create index string_id
    on transactions (string_id);

create index tournament_id
    on transactions (tournament_id);

create index type_id
    on transactions (type_id);

create index user_id
    on transactions (user_id);

表transactions_bonus #

-- auto-generated definition
create table transactions_bonus
(
    id                     bigint unsigned auto_increment
        primary key,
    account                tinyint unsigned default '1'               not null comment 'Номер счета. 1 - бонусный счет казино, 2 - бонусный счет спорта',
    type                   tinyint unsigned                           not null,
    user_id                bigint unsigned                            not null,
    admin_id               bigint unsigned                            null,
    transaction_id         bigint unsigned                            null,
    bonus_id               bigint unsigned                            null,
    tournament_id          bigint unsigned                            null,
    prize_id               bigint unsigned                            null,
    game_id                smallint unsigned                          null,
    amount_bonus           int                                        not null,
    amount_deposit         int                                        null,
    currency               varchar(3)                                 not null,
    wager                  float                                      not null,
    max_bet                int unsigned                               null,
    max_transfer           int unsigned     default '0'               not null comment 'Максимальная сумма перевода с бонусного счета на основной',
    balance                int                                        not null,
    status                 tinyint unsigned                           not null,
    comment                text                                       null,
    date                   timestamp        default CURRENT_TIMESTAMP not null,
    date_cancel            timestamp                                  null,
    date_wagering_complete timestamp                                  null,
    constraint transactions_bonus_ibfk_1
        foreign key (admin_id) references users (id)
            on update cascade,
    constraint transactions_bonus_ibfk_3
        foreign key (tournament_id) references tournaments (id)
            on update cascade,
    constraint transactions_bonus_ibfk_4
        foreign key (user_id) references users (id)
            on update cascade,
    constraint transactions_bonus_ibfk_5
        foreign key (game_id) references games (id)
            on update cascade,
    constraint transactions_bonus_ibfk_6
        foreign key (prize_id) references prizes (id)
            on update cascade,
    constraint transactions_bonus_ibfk_7
        foreign key (bonus_id) references bonuses (id)
            on update cascade,
    constraint transactions_bonus_ibfk_8
        foreign key (transaction_id) references transactions (id)
            on update cascade
)
    engine = InnoDB;

create index account
    on transactions_bonus (account);

create index admin_id
    on transactions_bonus (admin_id);

create index bonus_id
    on transactions_bonus (bonus_id);

create index game_id
    on transactions_bonus (game_id);

create index status
    on transactions_bonus (status);

create index tournament_id
    on transactions_bonus (tournament_id);

create index transaction_id_2
    on transactions_bonus (transaction_id);

create index type
    on transactions_bonus (type);

create index user_id
    on transactions_bonus (user_id);

表games #

-- auto-generated definition
create table games
(
    id                     smallint unsigned auto_increment
        primary key,
    string_id              varchar(96) collate utf8_bin                     not null,
    provider_id            tinyint unsigned                                 not null,
    aggregator             tinyint unsigned                                 not null,
    name                   varchar(255)                                     not null,
    game_type              tinyint unsigned                                 not null,
    source                 tinyint unsigned                                 not null,
    technology             tinyint unsigned                                 not null,
    device_type            tinyint unsigned                                 not null,
    status                 tinyint unsigned       default '0'               not null,
    block_kyc              tinyint unsigned       default '0'               not null comment 'Блокировка для не верифицированных пользователей',
    block_jackpots         tinyint unsigned       default '0'               not null comment 'Не участвует в джекпотах',
    is_blocked_tournaments tinyint unsigned       default '0'               not null,
    is_wagering            tinyint                default 0                 null,
    is_bonuses             tinyint unsigned       default '0'               null,
    is_jackpots            tinyint unsigned       default '0'               not null,
    freespins              tinyint unsigned       default '0'               not null comment 'Участвует в фриспинах',
    has_demo               tinyint(1)             default 0                 not null,
    multiplier             decimal(8, 2) unsigned default 0.00              not null,
    image                  varchar(128)                                     null,
    sorting                int unsigned           default '0'               not null,
    created_at             timestamp              default CURRENT_TIMESTAMP null,
    updated_at             timestamp                                        null,
    blocked                smallint unsigned      default '0'               null,
    constraint string_id
        unique (string_id, aggregator),
    constraint games_ibfk_1
        foreign key (provider_id) references games_providers (id)
)
    engine = InnoDB;

create index provider_id
    on games (provider_id);

表users #

-- auto-generated definition
create table users
(
    id                       bigint unsigned auto_increment
        primary key,
    name                     varchar(255) charset utf8                         null,
    surname                  varchar(255)                                      null,
    email                    varchar(255)                                      null,
    phone                    varchar(12)                                       null,
    password                 varchar(255)                                      not null,
    user_group               tinyint(1)                                        not null,
    status                   tinyint(1)              default 0                 not null,
    network                  varchar(32)                                       null,
    network_uid              varchar(32)                                       null,
    login                    varchar(255)                                      not null,
    ip_registration          varchar(39)                                       not null,
    ip_auth                  varchar(39)                                       null,
    country                  varchar(3) charset utf8                           null,
    country_already_changed  tinyint unsigned        default '0'               not null comment 'Страну можно изменить только один раз',
    birthday_already_changed tinyint unsigned        default '0'               not null comment 'Дату рождения можно установить один раз',
    city                     varchar(255)                                      null,
    postcode                 varchar(15)                                       null,
    date_birth               date                                              null,
    date_registration        timestamp               default CURRENT_TIMESTAMP not null,
    date_auth                timestamp                                         null,
    date_last_activity       timestamp                                         null,
    comment                  varchar(265)                                      null,
    affiliate_id             varchar(32) charset utf8                          null,
    affiliate_user_id        varchar(255)                                      null,
    affiliate_company_id     varchar(32)                                       null,
    affiliate_payload        varchar(192) charset utf8                         null,
    affiliate_link_type      tinyint unsigned        default '0'               not null comment 'Тип партнерской ссылки, по которой перешел юзер',
    favorite_bets            tinyint unsigned        default '0'               not null comment 'Любимые ставки игрока',
    timezone                 varchar(64) charset utf8                          null,
    region                   varchar(255)                                      null,
    address                  varchar(265)                                      null,
    kyc_status               tinyint unsigned        default '1'               not null,
    role_id                  tinyint                                           null,
    sms_autentification      tinyint unsigned        default '0'               not null,
    remember_token           varchar(100)                                      null,
    created_at               timestamp               default CURRENT_TIMESTAMP not null,
    updated_at               timestamp                                         null,
    kyc_expire               date                                              null,
    sex                      varchar(1) charset utf8 default 'm'               null,
    login_attempt            int                     default 0                 not null,
    language                 varchar(2) charset utf8 default 'en'              not null,
    session_uuid             varchar(36)                                       null,
    session_token            varchar(64)                                       null,
    avatar                   varchar(265)                                      null,
    referral_user_id         bigint unsigned                                   null,
    level                    tinyint unsigned        default '0'               not null,
    points                   int unsigned            default '0'               not null,
    shop_points              int unsigned            default '0'               not null,
    last_points_used         timestamp                                         null,
    max_withdrawal_amount    int unsigned                                      null,
    promocode_error_attempts int unsigned            default '0'               null,
    promocode_block_to       datetime                                          null,
    bonus_blocked            tinyint unsigned        default '0'               not null comment 'Заблокирован для участия в бонусах',
    bonus_blocked_date       timestamp                                         null,
    blocked                  tinyint unsigned        default '0'               not null comment 'Блокировка Sumsub',
    service_status           varchar(32)                                       null comment 'Статус сервиса SumSub',
    constraint login
        unique (login),
    constraint network
        unique (network, network_uid),
    constraint users_email_unique
        unique (email),
    constraint users_ibfk_1
        foreign key (referral_user_id) references users (id)
            on update cascade
)
    engine = InnoDB
    collate = utf8mb4_unicode_ci;

create index date_last_activity
    on users (date_last_activity);

create index referral_user_id
    on users (referral_user_id);

优化方案 #

1. 提前聚合子查询，减少外层分组数据量 #

原查询先合并3000万行数据再分组，性能瓶颈在于大数据集的排序和聚合。改为对两个子查询分别先按分组字段聚合，再通过UNION ALL合并，最后外层统一聚合（若两个子查询的分组键完全一致，外层聚合可直接合并统计值）。

修改后的查询语句：

SELECT 
    game_id,
    SUM(total_user_count) AS total_user_count,
    SUM(distinct_user_count) AS distinct_user_count,
    provider_id,
    aggregator,
    country,
    SUM(total_bets) AS sum_bets,
    SUM(total_wins) AS sum_wins,
    currency,
    ANY_VALUE(date) AS date
FROM (
    -- 先聚合transactions表数据
    SELECT 
        t.game_id,
        COUNT(t.user_id) AS total_user_count,
        COUNT(DISTINCT t.user_id) AS distinct_user_count,
        g.provider_id,
        g.aggregator,
        u.country,
        t.currency,
        SUM(IF(t.type_id IN (2, 16), t.amount, 0)) AS total_bets,
        SUM(IF(t.type_id IN (3, 18, 20), t.amount, 0)) AS total_wins,
        ANY_VALUE(t.date_create) AS date
    FROM `transactions` t
    INNER JOIN `games` g ON t.game_id = g.id
    INNER JOIN `users` u ON t.user_id = u.id
    WHERE t.status_id = 1
      AND t.type_id IN (2, 3, 16, 18, 20)
      AND t.game_id IS NOT NULL
      AND u.country IS NOT NULL
    GROUP BY t.game_id, g.provider_id, g.aggregator, u.country, t.currency

    UNION ALL

    -- 先聚合transactions_bonus表数据
    SELECT 
        tb.game_id,
        COUNT(tb.user_id) AS total_user_count,
        COUNT(DISTINCT tb.user_id) AS distinct_user_count,
        g.provider_id,
        g.aggregator,
        u.country,
        tb.currency,
        SUM(IF(tb.type IN (5, 6), tb.amount_bonus, 0)) AS total_bets,
        SUM(IF(tb.type IN (4, 7, 10), tb.amount_bonus, 0)) AS total_wins,
        ANY_VALUE(tb.date) AS date
    FROM `transactions_bonus` tb
    INNER JOIN `games` g ON tb.game_id = g.id
    INNER JOIN `users` u ON tb.user_id = u.id
    WHERE tb.status IN (2, 3, 4, 5, 6)