使用子查询优化SQL查询

Question

我有一个SQL查询，我试图优化它，我可以通过各种方式将时间从5秒以上减少到1.3秒左右，但不能再进一步了。我想知道是否有人能够提出进一步的改进建议。

Explain图显示了一个完整的扫描：解释图

"解释"表将为您提供更多详细信息：解释表格

查询被简化，如下所示——仅供参考，我使用的是MySQL 5.6

select * from (
select 
@row_num := if(@yacht_id = yacht_id and @charter_type = charter_type and @start_base_id = start_base_id and @end_base_id = end_base_id, @row_num +1, 1) as row_number,
@yacht_id := yacht_id as yacht_id, 
@charter_type := charter_type as charter_type,
@start_base_id := start_base_id as start_base_id,
@end_base_id := end_base_id as end_base_id,
model, offer_type, instant, rating, reviews, loa, berths, cabins, currency, list_price, list_price_per_day, 
discount, client_price, client_price_per_day, days, date_from, date_to, start_base_city, end_base_city, start_base_country, end_base_country, 
service_binary, product_id, ext_yacht_id, main_image_url
from (
select
offer.yacht_id, offer.charter_type, yacht.model, offer.offer_type, offer.instant, yacht.rating, yacht.reviews, yacht.loa, 
yacht.berths, yacht.cabins, offer.currency, offer.list_price, offer.list_price_per_day, 
offer.discount, offer.client_price, offer.client_price_per_day, offer.days, date_from, date_to,
offer.start_base_city, offer.end_base_city, offer.start_base_country, offer.end_base_country,
offer.service_binary, offer.product_id, offer.start_base_id, offer.end_base_id,
yacht.ext_yacht_id, yacht.main_image_url
from website_offer as offer
join website_yacht as yacht
on offer.yacht_id = yacht.yacht_id, 
(select @yacht_id:='') as init
where date_from > CURDATE() 
and date_to <= CURDATE() + INTERVAL 3 MONTH
and days = 7
order by offer.yacht_id, charter_type, start_base_id, end_base_id, list_price_per_day asc, discount desc
) as filtered_offers
) as offers
where row_number=1;

谢谢，goppi

UPDATE

我不得不放弃一些性能改进，用新的替换了原来的select。选择查询实际上是由后台根据设置的筛选条件动态构建的。因此，最内部选择的where子句可以解释很多。然而，如果没有设置过滤器，这是默认的选择，并且是需要明显长于1秒的版本。

以文本形式解释-结果不太好，因为我不知道如何格式化表格，但它在这里：

1 PRIMARY ref<auto_key0>lt；auto_key0>9常量10
2派生ALL 385967
3派生系统1使用文件端口3衍生报价参考idx_yachtid，idx_search，idx_dates idx_dates5 const385967使用索引条件；使用where3衍生游艇eq_ref PRIMARY，id_UNIQUE PRIMARY 4游艇租船合同.offer.yacht_id 1
4衍生未使用表

Answer 1

子选择从来都不是很棒的，你应该在这里注册：https://www.eversql.com/

运行它，它将为您提供该查询所需的所有正确索引和优化。

Answer 2

您仍然可以使用一些优化。考虑到子查询只返回5000行，您可以为其使用索引

首先将谓词改写为：

select *
from website_offer
where date_from >= CURDATE() + INTERVAL 1 DAY -- rephrased here
and date(date_to) <= CURDATE() + INTERVAL 3 MONTH
and days = 7
order by yacht_id, charter_type, list_price_per_day asc, discount desc
limit 5000    

然后，如果添加以下索引，性能可能会提高：

create index ix1 on website_offer (days, date_from, date_to);