我有一个大的查询需要优化。
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
。下面是一些可能有所帮助的:如果不是
COUNT(DISTINCT user_id)
,我建议在每个子查询中执行SUMs
,然后添加小计。innodb_buffer_pool_size
的值是多少?RAM有多大?表有多大(GB)?如果不能充分缓存,那么I/O就是问题所在,我建议将BIGINTs
(每个8字节)更改为更文明的数据类型。“两个合并的表组合了3000万行”--是过滤前的30 M还是过滤后的30 M(
WHERE
)?请提供
EXPLAIN SELECT ...