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的端口(如何快速修改MySQL用户的host属性)
- mysql慢日志设置多少合适(MySQL慢查询日志的作用和开启)
- mysql 查询语句group by用法(MySQL group by和order by如何一起使用)
- mysql数据库丢失怎么办(MySQL 数据丢失排查案例)
- mysql主从同步工作原理(MySQL是如何实现主备同步)
- 怎么把csv文件导入mysql(mysql导入csv的4种报错的解决方法)
- mysql索引建立及应用(MYSQL创建索引,这些知识应该了解)
- mysql的存储方法(MySQL中的binary类型使用操作)
- mysql清空数据库所有表格(MySQL用truncate命令快速清空一个数据库中的所有表)
- mysql 索引使用总结(MySQL复合索引的深入探究)
- mysql创建存储过程的代码(MySQL修改存储过程的详细步骤)
- xampp数据库表在哪个文件夹(XAMPP集成环境中MySQL数据库的使用)
- 如何使java与sqlserver数据库连接(java连接mysql数据库 java连接sql server数据库)
- MySQL中ROUND四舍五入函数需要注意的地方
- 在php中与数据库连接的技术(PHP7.0连接DB操作实例分析基于mysqli)
- 命令行如何使用xampp中的mysql(解决xampp自启动和mysql.sock问题)
- 被骂欺师灭祖,与郭德纲公开叫板,何云伟改名何沄伟,开始画画了(与郭德纲公开叫板)
- 相声转行影帝,被何晴抛弃,甩10年女友闪婚生子,刘威不靠谱情史(相声转行影帝被何晴抛弃)
- 岳云鹏不说相声,改行演员了 网友 快回来说相声(岳云鹏不说相声)
- 乔欣首演古装大女主,颜值演技双在线(乔欣首演古装大女主)
- 于正又推女性古装大剧 杨蓉乔欣演女配,两位女主成 重头戏(于正又推女性古装大剧)
- 乔欣古装女主戏获热度 作为女主,却没吃到红利(乔欣古装女主戏获热度)
热门推荐
- sqlserver创建表结构时添加约束(SQL Server 通过with as方法查询树型结构)
- 用javascript解析json(JavaScript JSON.stringify的使用总结)
- laravel模型详解(laravel config文件配置全局变量的例子)
- linux wait函数(分析从Linux源码看TIME_WAIT的持续时间)
- dedecms栏目标签的语法(dede中当前栏目选择技巧及注意事项)
- sqlserver常用的字符串函数(SqlServer 获取字符串中小写字母的sql语句)
- python eval函数原理(浅谈Python中eval的强大与危害)
- c语言获取系统日期函数(C# ling to sql 取多条记录最大时间)
- html5播放器按菜单键没反应(解决HTML5中的audio在手机端和微信端的不能自动播放问题)
- jquery导航栏动画(jQuery实现带展开动画的导航栏效果)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9