【MySQL】常见的SQL优化方式（二）-group by 优化

时间：2024-09-30 16:13:54

        在写 SQL 查询时，GROUP BY 是我们经常用来对数据进行分组的操作，但它也容易成为性能的瓶颈，那怎样才可以让分组操作更快呢？

（1）使用索引

        分组操作时，使用索引可以大幅提高效率。数据库通过索引可以快速定位到需要的数据，而不用去扫描整个表。和其他查询一样，GROUP BY 也遵循最左前缀法则，意思是如果我们用多个字段来分组，数据库只会使用最左边的字段上的索引来加速操作。所以，如果我们在 GROUP BY 中使用的字段是有索引的，并且顺序和索引的定义一致，就能提升查询效率。

# 执行分组操作，根据 profession 字段分组
EXPLAIN SELECT profession, COUNT(*) 
FROM tb_user 
GROUP BY profession;

# 创建新的联合索引
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession, age, status);

# 执行分组操作，根据 profession 字段分组
EXPLAIN SELECT profession, COUNT(*) 
FROM tb_user 
GROUP BY profession;

# 执行分组操作，根据 profession 和 age 字段分组
EXPLAIN SELECT profession, COUNT(*) 
FROM tb_user 
GROUP BY profession, age;

（2）避免排序

        GROUP BY 默认会对分组字段进行排序。如果不需要排序，可以通过 ORDER BY NULL 来告诉数据库不需要额外排序，这样可以节省排序的开销。

SELECT profession, COUNT(*)
FROM tb_user
GROUP BY profession
ORDER BY NULL;

（3）分析查询

        想知道 GROUP BY 是否使用了索引？可以用 EXPLAIN 命令查看执行计划，它会告诉我们查询是如何被执行的，是否用到了索引，是否有全表扫描等信息。如果发现索引没用上，那就得考虑调整 GROUP BY 字段的顺序或添加适合的索引了。

EXPLAIN SELECT profession, COUNT(*)
FROM tb_user
GROUP BY profession
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tb_user index idx_user_pro_age_sta 10 NULL 20 Using index

分析结果：

  • type：index 表示使用了索引扫描，这意味着查询是基于索引执行的，不再是全表扫描 (ALL)，因此性能提升。
  • possible_keys：显示可用的索引，表明查询可以使用 idx_user_pro_age_sta。
  • key：实际使用的索引是 idx_user_pro_age_sta，表明查询已经利用了我们创建的联合索引。
  • rows：这里显示的是估计扫描的行数。
  • Extra：Using index 表示查询直接从索引中获取数据，避免回表（从索引到表中的其他列查找），提高了效率。

相关文章