我有一个大的查询需要优化。

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

表事务

-- 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);

表交易_奖金

-- 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);

桌上游戏

-- 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);

表用户

-- 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);

这两个合并的表合并了3000万行，如果没有GROUP BY语句，它在1.5 - 2秒内执行，如果有GROUP BY语句，它最多可以等待5分钟。
显然，我可以不使用GROUP BY来执行脚本，然后在代码中格式化和聚合它，但是如果我将它保存在数组或JSON中，那么这么多的记录将超出我的RAM限制。

1条答案

按热度按时间

lokaqttq1#

注意，count(user_id)计算的是包含user_id IS NOT NULL的 * 行 * 的数量，看起来它不会为NULL，所以您也可以使用COUNT(*)。
在旧版本中，ANY_VALUE(date)不起作用，但MIN(date)起作用。
有很多IN(...)列表;这使得实际上不可能形成任何有用的INDEXes。下面是一些可能有所帮助的：

transactions:  INDEX(status_id, type_id)

如果不是COUNT(DISTINCT user_id)，我建议在每个子查询中执行SUMs，然后添加小计。
innodb_buffer_pool_size的值是多少？RAM有多大？表有多大（GB）？如果不能充分缓存，那么I/O就是问题所在，我建议将BIGINTs（每个8字节）更改为更文明的数据类型。
“两个合并的表组合了3000万行”--是过滤前的30 M还是过滤后的30 M（WHERE）？
请提供EXPLAIN SELECT ...

赞(0）回复(0）举报 2022-12-26

mysql 如何提高GROUP BY语句的性能？

1条答案

相关问题

热门标签

最新问答