历史大数据查询慢

Question

我有查询慢的问题。

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,帮助我提高性能?

Answer 1

更改索引。您当前有

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)