mysql中带有索引和partiton的聚合查询

Question

到目前为止，我有一个大约有200万行的表。该表将继续增长，因为每月将增加约0.5-06万行。

例如，我有一个查询如下：

select `importer_name`, ROUND(SUM(total_value_usd_exchange), 2) AS top15_usd
from   `importer_bills`
WHERE  year(bill_of_entry_date)=2020
AND 
Month(bill_of_entry_date)=3
group  by `importer_name`
order  by ROUND(SUM(total_value_usd_exchange), 2) desc limit 15 offset 0;

当前执行此查询需要9.98秒。

遵循explain:的输出

1   SIMPLE  importer_bills  p0  ref idx_importer_bills_upwork_09,idx_importer_bills_year_month  idx_importer_bills_year_month   5   const,const 1106762 100.00  Using index condition; Using temporary; Using filesort

1. idx_importer_bills_upwork_09是importer_name列上的索引。

2. idx_importer_bills_year_month是bill_of_entry_year和bill_of_entry_month上的索引

我还通过bill_of_entry_year添加了一个分区。

我尝试用替换上述查询

select `importer_name`, ROUND(SUM(total_value_usd_exchange), 2) AS top15_usd
from   `importer_bills`
WHERE  
bill_of_entry_year=2020
AND
bill_of_entry_month = 3
group  by `importer_name`
order  by ROUND(SUM(total_value_usd_exchange), 2) desc limit 15 offset 0;

这花了9.01秒。

explain:的输出

1   SIMPLE  importer_bills  p0  ref idx_importer_bills_upwork_09,idx_importer_bills_year_month  idx_importer_bills_year_month   5   const,const 1106762 100.00  Using index condition; Using temporary; Using filesort

一堆这样的查询是如何基于比一年和一个月更多的过滤器的？有时只有一个年份过滤器。仅2M行10秒是不可接受的。我该如何进行优化？

where子句中使用的列总是会根据用户对筛选器的选择而更改，但可以考虑强制使用YEAR筛选器。可能也是一个月(但理想情况下不想这么做(

Answer 1

我首先将where子句中的日期过滤器重写为SARGable表达式：

where bill_of_entry_date >= '2020-03-01' and bill_of_entry_date < '2020-04-01'

这不会在日期列上使用日期函数，因此可能会利用索引。然后，我推荐以下索引：

importer_bills(bill_of_entry_year, importer_name, total_value_usd_exchange)

第一个索引列与where谓词匹配；以下列与group by列匹配，第三列为聚合列。不能保证MySQL会使用索引中的所有列，但是，如果where谓词足够有选择性，那么您仍然应该看到性能优势。