我有查询慢的问题。
p
MariaDB: mariadb:10.3.25 ->InnoDB
我优化了大多数DB配置
结构create table customers
(
id bigint unsigned auto_increment
primary key,
email varchar(255) null,
full_name varchar(255) null,
country varchar(2) null,
first_name varchar(255) null,
second_name varchar(255) null,
company_name varchar(255) null,
gender char null,
birth_date date null,
state varchar(3) null,
null,
custom_field_1 varchar(255) null,
custom_field_2 varchar(255) null,
custom_field_3 varchar(255) null,
created_at timestamp null,
updated_at timestamp null,
deleted_at timestamp null
)
collate = utf8mb4_unicode_ci;
create table customer_daily_stats
(
date date not null,
campaign_id bigint not null,
customer_id bigint not null,
event_1 int unsigned default 0 not null,
event_2 int unsigned default 0 not null,
event_3 int unsigned default 0 not null,
event_4 int unsigned default 0 not null,
event_5 int unsigned default 0 not null,
constraint customer_daily_stats_date_customer_id_campaign_id_unique
unique (date, customer_id, campaign_id)
)
collate = utf8mb4_unicode_ci;
create index customer_daily_stats_customer_id_date_index
on customer_daily_stats (customer_id, date);
create index customer_daily_stats_campaign_id_index
on customer_daily_stats (campaign_id);
~ 1 - 500万行
customer_daily_stats~ 1 - 1亿行
查询select
customers.*,
IFNULL(
SUM(events_aggregation.event_1),
0
) as event_1,
IFNULL(
SUM(events_aggregation.event_2),
0
) as event_2,
IFNULL(
SUM(events_aggregation.event_3),
0
) as event_3,
IFNULL(
SUM(events_aggregation.event_4),
0
) as event_4
from
`customers`
left join customer_daily_stats as events_aggregation on `customers`.`id` = `events_aggregation`.`customer_id`
and `events_aggregation`.`date` between '2021-09-06' and '2022-07-06'
group by
`customers`.`id`;
问题主要思想是有可能获得任何日期的聚合。
问题是现在工作太慢了,我需要做加法聚合,这会降低性能。还有一个问题,我没有太多的磁盘空间(250G,大约80%已经使用了)。
我:
客户- ~ 1.5 m
- customer_daily_stats~ 50.000 查询速度
- ~ 5 s
问题
- 是否有任何方法来优化我的数据库或其他工具?
- 有没有DBs,帮助我提高性能?
更改索引。您当前有
unique (date, customer_id, campaign_id)
INDEX(customer_id, date)
INDEX(campaign_id)
Maybe改为:
PRIMARY KEY(customer_id, date, campaign_id)
INDEX(campaign_id)
但是…这是一个很大的但是。索引的重新排列可能会严重影响其他查询。我们真的需要看看
- 所有大查询
- 每个 的
EXPLAIN SELECT
你注意到范围是10个月加1天吗?这是因为BETWEEN
是"包含的"。
如果80%的磁盘已经被使用,那么您就陷入了困境。任何修复都需要超过20%的磁盘才能完成。
可以做的一件事(当您有足够的磁盘空间时)是将BIGINT
(8字节,可能是一个过大的范围)和INT UNSIGNED
(4字节,最大40亿)缩小为更小的int类型。
我困惑。这些似乎是相互矛盾的;请澄清:
customer_daily_stats ~ 1 - 1亿行
customer_daily_stats ~ 50.000
更多帮助分析的东西:
innodb_buffer_pool_size
RAM size
disk footprint for tables (GB)