如何优化运行耗时2分25秒的sql查询

Question

我有一个包含3 GB数据的表(它将继续增加(，我需要显示总销售额、顶级类别和顶级产品(列中出现次数最多(。以下是给出上述结果的查询：

select t.category, 
sum(t.sale) sales,
(select product 
from demo 
where  category = t.category
group by product
order by count(*) desc
limit 1) top_product
from demo t
group by t.category

上述查询大约需要2分25秒。我找不到任何优化它的方法。有人可以推荐其他方法吗？

示例表：

category  product    sale 
C1         P1        10
C2         P2        12
C3         P1        14
C1         P2        15
C1         P1        02
C2         P2        10
C2         P3        22
C3         P1        01
C3         P2        27
C3         P3        02

输出：

category  Top product   Total sales 
C1         P1        27
C2         P2        44
C3         P1        44

Answer 1

您的查询可以这样写：

SELECT g1.category, g1.sum_sale, g2.product
FROM (
SELECT category, SUM(sale) AS sum_sale
FROM demo
GROUP BY category
) AS g1
INNER JOIN (
SELECT category, product, COUNT(*) AS product_count
FROM demo
GROUP BY category, product
) AS g2 ON g1.category = g2.category
INNER JOIN (
SELECT category, MAX(product_count) AS product_count_max
FROM (
SELECT category, product, COUNT(*) AS product_count
FROM demo
GROUP BY category, product
) AS x
GROUP BY category
) AS g3 ON g2.category = g3.category AND g2.product_count = g3.product_count_max

基本上，它试图找到每个类别的最大计数(*(，并据此计算产品。它可以从适当的指数中受益。

Answer 2

一个仅限MySQL的破解解决方案是将GROUP_CONCAT与嵌套的SUBSTRING_INDEX函数结合使用，以获得有序逗号分隔字符串中的第一个元素。

这不是一个理想的方法；但它将减少所需的子查询数量，并且可能对您的特殊情况有效。

您还需要使用SET SESSION group_concat_max_len = @@max_allowed_packet;。

我们基本上确定产品和类别组合的销售额和发生次数。然后将该结果集用作派生表，并使用Group_concat()破解来确定类别中具有最大计数的产品。

SET SESSION group_concat_max_len = @@max_allowed_packet;
SELECT 
dt.category, 
SUM(dt.sale_per_category_product) AS total_sales, 
SUBSTRING_INDEX(
SUBSTRING_INDEX(
GROUP_CONCAT(dt.product ORDER BY dt.product_count_per_category DESC)
, ','
, 1
)
, ','
, -1
) AS top_product 
FROM 
(
SELECT 
category, 
product, 
SUM(sale) AS sale_per_category_product, 
COUNT(*) AS product_count_per_category 
FROM demo 
GROUP BY category, product 
) AS dt 
GROUP BY dt.category 

架构(MySQL v5.7(

| category | total_sales | top_product |
| -------- | ----------- | ------------|
| C1       | 27          | P1          |
| C2       | 44          | P2          |
| C3       | 44          | P1          |

---

查看DB Fiddle