嗨,我正在尝试优化此查询。 如果时间范围内有很多事务,则在我的本地环境中执行最多可能需要 10 秒。 我尝试在created_at列上创建一个索引,但如果表中有很多行(我的表只有 4m 行(,它并不能解决问题。 有人可以推荐一些优化技巧吗?
select
count(*) as total,
trader_id
from
(select *
from `transactions`
where `created_at` >= '2018-05-04 10:54:00'
order by `id` desc)
as `transactions`
where
`transactions`.`market_item_id` = 1
and `transactions`.`market_item_id` is not null
and `gift` = 0
group by `trader_id`;
编辑:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE transactions NULL range transactions_market_item_id_foreign,transactions_trader_id_foreign,transactions_created_at_index transactions_created_at_index 5 NULL 107666 2.41 Using index condition; Using where; Using MRR; Using temporary; Using filesort
删除(不必要的(内部查询:
select
count(*) as total,
trader_id
from transactions
where created_at >= '2018-05-04 10:54:00'
and market_item_id = 1
and gift = 0
group by trader_id
笔记:
- 删除了不必要的内部查询,增加了成本,大大增加了临时存储要求和使用量,并阻止了任何索引使用其他条件
- 删除
order by
,这将花费很多,但对结果没有任何影响 - 删除
market_item_id is not null
条件,因为market_item_id = 1
已经断言 - 删除了反引号,因为我不喜欢它们
波西米亚查询的更好版本在这里 -
SELECT count(*) as total
,trader_id
FROM `transactions`
WHERE `created_at` >= '2018-05-04 10:54:00'
AND `market_item_id` = 1
AND `gift` = 0
GROUP BY `trader_id`