我需要有关优化此计数查询的帮助。以下是表格:
activities
- activity_id (PRIMARY)
- item_id
- user_id
- created_at
INDEX: user_id_created_at (user_id, created_at)
INDEX: item_id (item_id)
retail_activities
- activity_id (PRIMARY)
- item_id
- created_at
INDEX: item_id (activity_id, item_id)
INDEX: item_id_created (activity_id, item_id, created_at)
users
- user_id (PRIMARY)
- is_private
INDEX: user_id_private (user_id, is_private)
目标
我想查找在某个日期范围内发生的retail_activities
中的项目和唯一用户数的计数,其中活动表中的item_id
不等于retail_activities表中的item_id
,并且用户不是私有用户。
基本上,我想查找在选定item_id和选定时间范围内执行的唯一用户总数。
所以查询将是(X 是一个整数,如 1234 - 如果项目,这是 ID(
SELECT count(retail_activities.item_id) as total_items, count(distinct activities.user_id) as total_users
from activities
inner join retail_activities on retail_activities.activity_id = activities.activity_id
inner join users on users.user_id = activities.user_id
where users.is_private = 0 and
activities.item_id != retail_activities.item_id and retail_activities.item_id = X and
retail_activities.created_at > "2019-11-22 00:00:00"
此查询的说明如下:
1 SIMPLE retail_activities item_id_created item_id_created 5 const 812856 Using where; Using index
1 SIMPLE activities eq_ref PRIMARY PRIMARY 4 retail_activities.activity_id 1 Using where
1 SIMPLE users eq_ref PRIMARY PRIMARY 4 activities.user_id 1 Using where
因为这个项目有很多活动(350k(,所以它运行得很慢(8-25秒(。有什么方法可以加快速度吗?
对于此查询:
select count(*) as total_items, count(distinct a.user_id) as total_users
from activities a join
retail_activities ra
on ra.activity_id = a.activity_id join
users u
on u.user_id = a.user_id
where u.is_private = 0 and
a.item_id <> ra.item_id and
ra.item_id = X
ra.created_at > '2019-11-22';
我推荐以下索引:
retail_activities(item_id, created_at, activity_id)
activities(activity_id, item_id)
(如果activity_id
是主键,则不需要(users(user_id, is_private)
我建议您优化索引策略,并尽可能使用哈希索引。
根据MySQL文档:
作为复合索引的替代方法,您可以引入列 这是基于其他列中的信息进行"哈希处理"的。如果这个 列很短,合理唯一且已编入索引,可能会更快 而不是许多列上的"宽"索引。
此外,您无需显式INDEX: item_id (activity_id, item_id)
表中retail_activities
。这是因为您已经创建了一个涉及它们的复合主键。因此,这也将充当索引。
并研究索引合并优化。这是MySQL日志文档。 并且,阅读优化器开关以确定连接的理想顺序以获得最佳性能。以下是我认为您会发现有用的一些信息:
更改联接顺序。实现此目的的方法包括连接顺序 优化器提示(请参见第 8.9.3 节 "优化器提示"(,STRAIGHT_JOIN 紧跟在 SELECT 和 STRAIGHT_JOIN 连接运算符之后。