mysql全表查询如何优化(MySQL 分组查询的优化方法)
mysql全表查询如何优化
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 分组查询的优化的资料请关注开心学习网其它相关文章!
原文链接:https://juejin.cn/post/6960630605323173895
- mysql和mycat半主从复制项目(Linux如何使用 MyCat 实现 MySQL 主从读写分离)
- mysql触发器如何创建(MySQL 触发器的使用和理解)
- docker 镜像mysql(解决docker拉取mysql镜像太慢的情况)
- mysql获取当前日期时间(MySQL DATEDIFF函数获取两个日期的时间间隔的方法)
- docker部署mysql如何访问(完美解决docker安装mysql后Navicat连接不上的问题)
- mysql主键为什么用varchar(Mysql中varchar类型一些需要注意的地方)
- mysql有哪些约束(MySQL完整性约束的定义与实例教程)
- mysql架构示意图(MySQL创建横向直方图的解决方案)
- mysql存储json的方式(MySQL中查询json格式的字段实例详解)
- mysql怎么解决幻读问题(详解MySQL幻读及如何消除)
- zabbix如何配置监控(zabbix监控mysql的实例方法)
- 分享mysql设计规范(MySQL 可扩展设计的基本原则)
- mysql8.0.18.0安装详细教程(mysql 8.0.22 下载安装配置方法图文教程)
- mysql各种备份方式(MySQL 逻辑备份与恢复测试的相关总结)
- mysql忽略大小写(MySQL大小写敏感的注意事项)
- windows7mysql服务无法启动(Windows系统下MySQL无法启动的万能解决方法)
- 赵丽颖第一部当女主的戏,主角配角个个都是实力演员(赵丽颖第一部当女主的戏)
- 乾隆为何这么喜爱白塔原因是什么(乾隆为何这么喜爱白塔原因是什么)
- 逐渐消失的东北八大怪现象,进步的社会里我们遗失的是什么(逐渐消失的东北八大怪现象)
- 今日大雪,大雪养生这样做,一年都有好气色(大雪养生这样做)
- 粗盐是什么 粗盐的功效与作用(粗盐的功效与作用)
- 会 吃人 的客机 从天堂到地狱只需5分钟,图-104如何做到(会吃人的客机)
热门推荐
- php 常见的设计模式(PHP中常用的三种设计模式详解单例模式、工厂模式、观察者模式)
- python安装pil模板教程(详解python3安装pillow后报错没有pillow模块以及没有PIL模块问题解决)
- css自动换行左右对齐(CSS自动换行、强制不换行、强制断行、超出显示省略号)
- sqlserver索引策略(SQLServer性能优化--间接实现函数索引或者Hash索引)
- dedecms怎么添加栏目(Dedecms频道,列表页,内容页中调用全站最新文章的方法)
- 开发中常用工具推荐
- dockerswarm滚动部署(使用Docker部署war包项目的实现)
- docker和tomcat建立连接(如何基于Dockerfile构建tomcat镜像)
- sql解析器配置(sql字段解析器的实现示例)
- ASP.NET私有构造函数作用
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9