MySQL优化GROUP BY（松散索引扫描与紧凑索引扫描）

MySQL中的GROUP BY语句可以用于将相似的数据分组并计算其汇总值。但是，当数据集很大时，GROUP BY语句对性能的影响也会很大。本文将详细讲解如何通过松散索引扫描和紧凑索引扫描来优化MySQL中的GROUP BY语句。

松散索引扫描优化

在MySQL中，使用GROUP BY语句分组时，如果不指定排序（ORDER BY）的话，MySQL会随机选择一个索引进行扫描。如果想要优化GROUP BY语句的性能，可以采用松散索引扫描方式。

松散索引扫描的思路是：先使用WHERE子句中的条件过滤数据，然后再使用GROUP BY语句进行分组，并在分组后对每个分组进行进一步的限制条件过滤。这个分组后的限制条件过滤可以使用HAVING子句来实现。

示例1：

SELECT category, COUNT(*) 
FROM products 
WHERE price > 100 
GROUP BY category 
HAVING COUNT(*) > 5;

在这个示例中，先通过WHERE子句过滤掉价格低于100的商品，然后再对剩余的商品数据进行分组，并在分组后，只返回每个分类中商品数量大于5的分类。

示例2：

SELECT DATE(date), COUNT(*) 
FROM orders 
WHERE user_id = 1 
GROUP BY DATE(date) 
HAVING COUNT(*) > 3;

在这个示例中，先通过WHERE子句过滤掉不属于用户1的订单数据，然后再按订单日期分组，并在分组后，只返回每天中订单数量大于3的日期。

紧凑索引扫描优化

在MySQL中，如果无法使用WHERE子句或者HAVING子句来过滤数据，可以考虑紧凑索引扫描优化。紧凑索引扫描的思路是尽量减小扫描的数据量。

示例1：

SELECT MAX(price) 
FROM products 
GROUP BY category;

在这个示例中，因为没有使用WHERE子句来过滤商品数据，所以无法使用松散索引扫描优化。但是，由于只需要获取每个分类中的最大价格，因此可以只扫描每个分类中的第一个商品。这个优化可以使用内部子查询来实现：

SELECT (SELECT MAX(price) FROM products WHERE category = p.category) 
FROM products p 
GROUP BY category;

示例2：

SELECT user_id, COUNT(*) 
FROM orders 
GROUP BY user_id 
HAVING COUNT(*) > 10;

在这个示例中，因为没有使用WHERE子句来过滤订单数据，所以无法使用松散索引扫描优化。但是，由于只需要获取订单数量大于10的用户ID，因此可以先使用内部子查询将所有的用户ID过滤出来，然后再使用外部查询进行分组和计数：

SELECT user_id, COUNT(*) 
FROM orders 
WHERE user_id IN (SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) > 10) 
GROUP BY user_id;

这样可以只扫描订单数量大于10的所有用户的订单数据，避免了查询未命中的无用数据。

通过以上两个优化方式，我们可以在GROUP BY语句中避免全表扫描，显著提升MySQL查询性能。