MySQL 分组查询的优化方法

MySQL 在处理 GROUP BY 和 DISTINCT 查询的方式在大多数情况下类似，事实上，在优化过程中有时候会把在这两种方式中转换。两类查询都能够从索引中受益，通常，这也是优化这两种查询最为重要的方式。

在无法使用索引时，MySQL 对于 GROUP BY 查询有两种策略：使用临时表或者 filesort 执行分组。对于给定的查询，两种方式都没法更高效。我们可以通过配置 SQL_BIG_RESULT 和 SQL_SMALL_RESULT 来指定优化器选择其中一个方式。

通常，对查询表的id 进行分组比使用值进行分组效率更高，例如下面的查询效率就比较低：

SELECT actor.first_name, actor.last_name, COUNT(*)
FROM sakila.file_actor
INNER JOIN sakila.actor USING(actor_id)
GROUP BY actor.first_name, actor.last_name;

而下面的查询方式则更有效：

SELECT actor.first_name, actor.last_name, COUNT(*)
FROM sakila.file_actor
INNER JOIN sakila.actor USING(actor_id)
GROUP BY film_actor.actor_id;

而使用 actor.actor_id 进行分组会比 film_actor.actor_id更高效。

这个查询能够发挥其优势的依据是演员(actor)的姓名是依赖于 actor_id 的，因此会返回相同的结果，但是如果返回的结果不同的话就不能这么做了。甚至有些时候服务端通过 SQL_MODE 配置禁用了 GROUP BY。此时如果不关心获取的值，而且用于分组的列的值是唯一的，这可以使用 MIN和 MAX 来解决这个问题。

SELECT MIN(actor.first_name), MAX(actor.last_name), ...;

对于完美主义者，他们会认为你的分组是错误的，他们也是对的。一个虚拟的 MIN 或 MAX 的结果是查询并不会正确地组装。然而，有时候你只是为了让 MySQL 更快地执行查询。完美主义者对于下面的查询会满意：

SELECT actor.fisrt_name, actor.last_name, c.cnt
FROM sakila.actor
	INNER JOIN (
    SELECT actor_id, COUNT(*) AS cnt
    FROM sakila.film_actor
    GROUP BY actor_id
  ) AS c USING(actor_id);

然而，子查询中创建和填充临时表的代价可能比理论上看起来的死办法更高。需要记住的是，子查询构建的临时表是没有索引的，这会导致性能上的下降。

通常在分组查询中，选择没有分组的列是一个糟糕的主意。这是因为查询结果是不确定的，一旦改变了索引或优化器使用了不同的策略都会导致结果被改变。事实上，我们建议将服务端的 SQL_MODE 设置为 ONLY_FULL_GROUP_BY，这时写了一个糟糕的分组查询时，系统会产生一个错误而不是直接执行。开启 ONLY_FULL_GROUP_BY 后，SELECT 的字段只能是 GROUP BY 指定的字段，此时可以通过构建分步查询或子查询的方式，先分组查出分组的列，再做二次查询。

MySQL 会根据 GROUP BY 指定的列次序自动分组，除非是使用了 ORDER BY 指定排序规则。如果不在乎次序并且发现了这导致了一个 filesort，这时候可以使用 ORDER BY NULL 来跳过自动排序。也可以通过在 GROUP BY 后面增加 DESC 或 ASC 来指定结果按指定的方向排序。

有时候可以在分组查询时要求 MySQL 在结果中做一次超级聚合。这可以通过在 GROUP BY 后面增加WITH ROLLUP 子句完成，但是这不一定能够达到优化的预期。可以通过 EXPLAIN 检查执行的方法，注意分组有没有通过 filesort 或临时表完成。然后在对相同的查询移除 WITH ROLLUP 后进行对比。通过对比也许可以找到优化的办法。

有些时候通过增加聚合查询会使得效率更高，虽然这种方式会返回更多的行。也可以通过在 FROM 后面嵌套子查询来保持中间查询结果，然后再使用 UNION 获取最终结果。

但是注意的是，在应用程序中最好是移除 WITH ROLLUP，而通过优化来完成分组查询。

结语：使用 GROUP BY 进行分组查询时最好是使用索引列分组，若无需指定次序可以使用 ORDER BY NULL 进行优化。倘若不按索引列分组的时候，则需要考虑变通的办法，并且考虑是否要使用子查询或使用 WITH ROLLUP 检查性能后再做优化。同时，为了防止分组查询出现不可预料的错误，最好是开启 ONLY_FULL_GROUP_BY。

以上就是MySQL 分组查询的优化方法的详细内容，更多关于MySQL 分组查询的优化的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • MySql中流程控制函数/统计函数/分组查询用法解析

    路漫漫其修远兮,吾将上下而求索,又到了周末,我继续带各位看官学习回顾Mysql知识. 上次说到了流程控制函数,那就从流程控制函数来继续学习吧! #五.流程控制函数 #1.if函数:if else的效果 IF(条件表达式,成立返回1,不成立返回2) #与Java三元运算相同 SELECT IF(10>5,'大','小'); SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金呵呵','有奖金嘻嘻') AS 备注 FROM em

  • MySQL 子查询和分组查询

    概述 子查询是SQL查询中的重要一块,是我们基于多表之间进行数据聚合和判断的一种手段,使得我们的处理复杂数据更加的便捷,这一节我们主要来了解一下子查询. 先做一下数据准备,这边建立三张表:班级.学生.毕业成绩表,用于后面的操作: drop database if exists `Helenlyn_Class`; create database `Helenlyn_Class`; /*班级表*/ DROP TABLE IF EXISTS `classes`; CREATE TABLE `class

  • 详解MySQL中的分组查询与连接查询语句

    分组查询 group by group by 属性名 [having 条件表达式][ with rollup] "属性名 "指按照该字段值进行分组:"having 条件表达式 "用来限制分组后的显示,满足条件的结果将被显示:with rollup 将会在所有记录的最后加上一条记录,该记录是上面所有记录的总和. 1)单独使用 group by 单独使用,查询结果只显示一个分组的一条记录. 实例: select * from employee group by sex;

  • MySQL分组查询Group By实现原理详解

    MySQL分组查询Group By实现原理详解

    由于GROUP BY 实际上也同样会进行排序操作,而且与ORDER BY 相比,GROUP BY 主要只是多了排序之后的分组操作.当然,如果在分组的时候还使用了其他的一些聚合函数,那么还需要一些聚合函数的计算.所以,在GROUP BY 的实现过程中,与 ORDER BY 一样也可以利用到索引. 在MySQL 中,GROUP BY 的实现同样有多种(三种)方式,其中有两种方式会利用现有的索引信息来完成 GROUP BY,另外一种为完全无法使用索引的场景下使用.下面我们分别针对这三种实现方式做一个分

  • MySQL 分组查询和聚合函数

    概述 相信我们经常会遇到这样的场景:想要了解双十一天猫购买化妆品的人员中平均消费额度是多少(这可能有利于对商品价格区间的定位):或者不同年龄段的化妆品消费占比是多少(这可能有助于对商品备货量的预估). 这个时候就要用到分组查询,分组查询的目的是为了把数据分成多个逻辑组(购买化妆品的人员是一个组,不同年龄段购买化妆品的人员也是组),并对每个组进行聚合计算的过程:. 分组查询的语法格式如下: select cname, group_fun,... from tname [where conditio

  • MySQL 分组查询的优化方法

    MySQL 在处理 GROUP BY 和 DISTINCT 查询的方式在大多数情况下类似,事实上,在优化过程中有时候会把在这两种方式中转换.两类查询都能够从索引中受益,通常,这也是优化这两种查询最为重要的方式. 在无法使用索引时,MySQL 对于 GROUP BY 查询有两种策略:使用临时表或者 filesort 执行分组.对于给定的查询,两种方式都没法更高效.我们可以通过配置 SQL_BIG_RESULT 和 SQL_SMALL_RESULT 来指定优化器选择其中一个方式. 通常,对查询表的i

  • MYSQL数据库表结构优化方法详解

    本文实例讲述了MYSQL数据库表结构优化方法.分享给大家供大家参考,具体如下: 选择合适的数据类型 1.使用可以存下你的数据的最小的数据类型 2.使用简单的数据类型.Int要比varchar类型在mysql处理上简单 3.尽可能的使用not null定义字段 4.尽量少用text类型,非用不可时最好考虑分表 使用int来存储日期时间,利用FROM_UNIXTIME()[将int类型时间戳转换成日期时间格式],UNIX_TIMESTAMP()[将日期时间格式转换成int类型]两个函数进行转换 使用

  • MySQL数据库查询性能优化策略

    优化查询 使用Explain语句分析查询语句 Explain 用来分析 SELECT 查询语句,开发人员可以通过分析 Explain 结果来优化查询语句. 通过对查询语句的分析,可以了解查询语句的执行情况,找出查询语句执行的瓶颈,从而优化查询语句. 使用索引查询 MySql中提高性能的一个最有效的方式就是对数据表设计合理的索引. 索引提供了高效访问数据的方法,并且加快查询速度. 如果查询时没有使用索引,那么查询语句将扫描表中所有的记录.在数据量大的时候,这样查询速度会很慢. 使用索引进行查询,查

  • MySQL 分页查询的优化技巧

    在有分页查询的应用中,包括 LIMIT 和 OFFSET 的查询十分常见,而且几乎每个都会有一个 ORDER BY 子句.如果使用索引排序的话将对性能优化十分有帮助,否则服务端需要做很多文件排序. 一个高频的问题是 offset 的值过大.如果查询类似 LIMIT 10000, 20,将会产生10020行,并将之前的10000行丢弃,这样的代价很高.假设所有的页使用相同的频次访问,这样的查询将平均扫描一半数据表.为了优化他们,你可以在分页视图中限制最多可访问的页数,或者让大便宜的查询更有效. 一

  • MySQL数据库查询性能优化的4个技巧干货

    MySQL数据库查询性能优化的4个技巧干货

    目录 前言 SQL的执行频率 慢查询日志 show profiles详情分析 explain执行计划 1.ID参数 2.select_type参数 3.type参数 前言 MySQL性能优化是一个老生常谈的问题,无论是在实际工作中还是面试中,都不可避免遇到相应的场景,下面博主就总结一些能够帮助大家解决这个问题的小技巧. SQL优化之前需要确认哪些SQL需要优化,这时就需要引起SQL性能分析工具,主要优化的是查询语句. SQL的执行频率 SQL性能优化一般是针对查询语句,所以在定位是否需要优化之前

  • MySQL Order By索引优化方法

    尽管 ORDER BY 不是和索引的顺序准确匹配,索引还是可以被用到,只要不用的索引部分和所有的额外的 ORDER BY 字段在 WHERE 子句中都被包括了. 使用索引的MySQL Order By 下列的几个查询都会使用索引来解决 ORDER BY 或 GROUP BY 部分: 复制代码 代码如下: SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; SELECT * FROM t1 WHERE key_part1=constant ORD

  • MyBatis 多表联合查询及优化方法

    MyBatis 多表联合查询及优化方法

    目录 背景 正文 关于优化 这篇文章我打算来简单的谈谈 mybatis 的多表联合查询.起初是觉得挺简单的,没必要拿出来写,毕竟 mybatis 这东西现在是个开发的都会用,而且网上的文章也是一搜罗一大堆,根本就用不着我来重复.但是吧,就我前几天在做一个多表联合查询的时候,竟然出了很多意想不到的问题,而且这些问题的出现,并不是对 mybatis 不了解,而是在用的过程中会或多或少的忽略一些东西,导致提示各种错误. 背景 老规矩,开始之前,还是要先说说这件事的背景.也就是最近几天,公司要做一个后台

  • MySQL延迟关联性能优化方法

    MySQL延迟关联性能优化方法

    [背景] 某业务数据库load 报警异常,cpu usr 达到30-40 ,居高不下.使用工具查看数据库正在执行的sql ,排在前面的大部分是: 复制代码 代码如下: SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where bi

  • mysql 性能的检查和优化方法

    1.索引没有建好; 2.sql写法过于复杂; 3.配置错误; 4.机器实在负荷不了; 1.索引没有建好 如果看到mysql消耗的cpu很大,可以用mysql的client工具来检查. 在linux下执行 /usr/local/mysql/bin/mysql -hlocalhost -uroot -p 输入密码,如果没有密码,则不用-p参数就可以进到客户端界面中. 看看当前的运行情况 show full processlist 可以多运行几次 这个命令可以看到当前正在执行的sql语句,它会告知执行

随机推荐