让order by、group by查询更快

最新推荐文章于 2023-05-30 17:08:54 发布
最新推荐文章于 2023-05-30 17:08:54 发布
阅读量452 收藏
点赞数
分类专栏： MySQL 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36986015/article/details/106186740
版权
本文深入探讨了MySQL中Order By和Group By查询的原理及优化策略。讲解了Filesort的内存与磁盘排序，如何判断排序方式，以及Filesort的不同排序模式。文章还提供了Order By的优化建议，包括添加合适索引、去掉不必要的返回字段以及修改系统参数。最后，讨论了Group By优化，指出默认排序与无序分组的区别。
摘要由CSDN通过智能技术生成

1. Order By原理

1.1 MySQL的排序方式

按照排序原理分，MySQL排序方式分两种：

  • 通过有序索引直接返回有序数据
  • 通过Filesort进行排序

我们可以使用explain来查看该排序SQL的执行计划，主要看Extra字段：

  • 如果该字段里显示是Using index，则表示通过有序索引直接返回有序数据；
  • 如果该字段里显示是Using filesort，则表示该SQL通过filesort进行排序；

1.2 Filesort是在内存中还是在磁盘中完成排序的？

filesort并不一定是在磁盘文件中进行排序，也有可能在内存中排序，内存排序还是磁盘排序取决于排序的数据大小和sort_buffer_size配置的大小：

  • 如果“排序的数据大小” < sort_buffer_size：内存排序
  • 如果“排序的数据大小” > sort_buffer_size：磁盘排序

1.3 怎么确定使用Filesort排序的SQL是在内存中还是在磁盘中进行？

可以使用trace进行分析，关注number_of_tmp_files字段，如果等于0，则表示排序过程没使用临时文件，在内存中就完成排序；如果大于0，则表示排序过程中使用了临时文件。如下图number_of_tmp_files等于0
，表示未使用临时文件进行排序，所以是内存排序。

最低0.47元/天 解锁文章
程序员翟同学
关注
专栏目录
使用mysql的disctinct group by查询不重复记录
12-15
可以使用索引来提高查询速度，特别是对`GROUP BY`和`ORDER BY`涉及的列建立索引。在选择聚合函数时，也要考虑到其对性能的影响，例如，`MIN`和`MAX`通常比`AVG`和`SUM`更快。 总之，`DISTINCT`和`GROUP BY`是MySQL...
【Oralce】记一次神奇的sql查询经历，group by慢查询优化（已解决）
承接GoodERP/Odoo二次开发.部署实施；OpenACCM物联网PaaS平台，基于云边协同的设备数字化一站式解决方案。
03-07 468
一、问题背景 现网出现慢查询，在500万数量级的情况下，单表查询速度在30多秒，需要对sql进行优化，sql如下： 我在测试环境构造了500万条数据，模拟了这个慢查询。 简单来说，就是查询一定条件下，都有哪些用户的。很简单的sql，可以看到，查询耗时为37秒。 说一下app_account字段的分布情况，随机生成了5000个不同的随机数，然后分布到了这500万条数据里，平均来说，每个app_ac...
oracle大表执行慢的优化方法,Oracle之SQL语句性能优化（34条优化方法）
weixin_36435325的博客
04-02 2904
好多同学对sql的优化好像是知道的甚少，最近总结了以下34条仅供参考。(1)选择最有效率的表名顺序(只在基于规则的优化器中有效)：ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(in...
mysql distinct效率优化,group by的效率和distinct与order by的效率比较哪个更好 ？
weixin_35671843的博客
03-17 338
没有区别，因为没有 limit 关键字，所以两个都是进行全局扫描 。•老师请问如何尽快学好SQL 优化方法论呢 ？答：报班学习，因为有系统课程+学习氛围+讨论，能有效提升学习的效率。• 推荐理解MySQL底层运行机制的资料答：叶老师是国内MySQL圈里权威人物，他的课程值得信赖。•select a ，b from tab where c=2 group by a 怎么优化答：首先 这个SQL是有点...
Oracle SQL性能优化
守护的博客
02-14 139
  （1）      选择最有效率的表名顺序(只在基于规则的优化器中有效)： ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交...
GROUP BY 查询
qq_27487739的博客
05-30 432
注意，GROUP BY 子句必须放在 WHERE 子句之后，在 SELECT 子句之前。查询时，除了聚合函数之外的所有列都必须在 GROUP BY 子句中列出。否则，将会报错或者得到意外的结果。GROUP BY 是一种 SQL 查询语句，可以用于将结果集按一列或多列进行分组，然后对每个组执行聚合函数，例如 COUNT、SUM、AVG、MIN 或 MAX。
MySQL索引系列--索引的优化--ORDER BY
IT利刃出鞘的博客
02-08 5747
本文介绍MySQL的ORDER BY索引的优化方案。 MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。 MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。
如何让SQL运行得更快.doc
03-17
* 有大量重复值、且经常有范围查询（between, >, <, >=, ）和 order by、group by 发生的列，可以考虑建立群集索引。 * 经常同时存取多列，且每列都含有重复值可考虑建立组合索引。 * 组合索引要尽量使关键查询形成...
MySQL前缀索引导致的慢查询分析总结
12-15
通过`EXPLAIN`命令分析了两种查询：一种是无索引的`LIMIT`查询，另一种是带有`ORDER BY`和`LIMIT`的查询。在不使用索引的情况下，查询速度快；而在使用索引并进行排序时，查询速度反而变慢，原因是MySQL使用了`...
mysql 查询重复的数据的SQL优化方案
09-10
这个优化后的查询将显著提高性能，因为临时表的索引使得匹配过程更快。对于大规模数据，这种方法能避免服务器资源的过度消耗，保证系统稳定运行。 此外，还有一些其他优化策略可以考虑： - **使用`EXISTS`子查询**...
第05章销售管理数据库的数据查询.ppt
11-21
SELECT语句的基本语法格式是：`SELECT <输出列表>[INTO <新表名>] FROM <数据源或视图列表> [WHERE <查询条件表达式>] [GROUP BY <分组表达式> [HAVING <过滤条件>] ] [ORDER BY <排序表达式> [ASC | DESC] ] ...
MYSQL中 order by、group by 如何更快
weixin_47245896的博客
06-22 973
按照原理划分，mysql排序方式有两种：Filesort 是在内存中还是在磁盘中完成排序的？MySQL 中的 Filesort 并不一定是在磁盘文件中进行排序的，也有可能在内存中排序，内存排序还是磁盘排序取决于排序的数据大小和 sort_buffer_size 配置的大小。Sort_mode参数的意义：Filesort 下的排序模式有三种，具体介绍如下:因为打包数据排序模式是单路排序的一种升级模式，因此重点探讨双路排序和单路排序的区别。MySQL 通过比较系统变量 max_length_for_sort_d
ORDER BY用法，避坑
weixin_51582215的博客
01-15 3493
数据库中ORDER BY用法
讲一讲order by和group by
kyhoon
06-18 367
order by 和 group by原理
SQL优化之提高order by的速度
qq_39338079的博客
07-26 3201
MySQL 优化查询速度的两项配置
kenvic00000的专栏
11-05 153
源自 http://blog.sina.com.cn/s/blog_4dd4753901017k6o.html   MySQL 如何优化 LIMIT 如果不能使用索引，可以试着遵循以下策略： 增加 sort_buffer_size 的值。 增加 read_rnd_buffer_size 的值。 修改 tmpdir，让它指向一个有很多剩余空间的专用文件系统。如果使用My...
order by 原理和优化
Meiko记录
10-12 1838
一个使用order by 的简单例子 假设用一张员工表，表结构如下： CREATE TABLE `staff` ( `id` BIGINT ( 11 ) AUTO_INCREMENT COMMENT '主键id', `id_card` VARCHAR ( 20 ) NOT NULL COMMENT '身份证号码', `name` VARCHAR ( 64 ) NOT NULL COMMENT '姓名', `age` INT ( 4 ) NOT NULL COMMENT '年龄', `city` VAR
关于group by 又order by优化的问题
天蓬元帅的博客
09-05 571
-- 上面的写法，等同于下面的写法，尽量使用上面的写法，不推荐下面的写法 SELECT NORMAL_FILE_NAME,STD_DOC_CODE,STD_DOC_NAME from MED_FILE_STD_REF WHERE !ISNULL(STD_DOC_CLASS_NAME) GROUP BY STD_DOC_CLASS_NAME DESC SELECT NORMAL_FILE_NAME...
ORALCE hash group by 和 sort group by
最新发布
11-03
ORACLE数据库中的GROUP BY操作可以通过HASH GROUP BY和SORT GROUP BY两种方式实现。在10g以前，GROUP BY是通过按照GROUP BY中的列对记录进行排序实现的。而在10g以后，HASH GROUP BY通过在读取表时将聚合计算在散列表中避免了排序，理论上比SORT GROUP BY速度更快并消耗更少的内存。然而，HASH GROUP BY的初始实现存在很多bug，在11.1.0.7和10.2.0.4版本发布后基本解决了。如果遇到了和HASH GROUP BY方法相关的问题，可以尝试关闭HASH GROUP BY，通过参数_GBY_HASH_AGGREGATION_ENABLED设置为FALSE来禁用HASH GROUP BY。如果在同一个表达式中使用GROUP BY和ORDER BY，优化器通常会决定使用SORT GROUP BY(而不是HASH GROUP BY)，因为SORT GROUP BY会按照顺序返回记录。在10g以前的版本，GROUP BY操作通常会按照排序的顺序返回记录，因此ORDER BY子句事实上不起什么作用。然而，从10g以后，ORDER BY会抑制HASH GROUP BY方法的使用，因此可能会降低性能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值