MySQL排序原理与优化方法(9/16)

order by排序优化

MySQL排序策略

内存临时表 or 磁盘临时表?

**内存临时表排序:**在MySQL中,使用InnoDB引擎执行排序操作时,当处理的数据量较小,可以在内存中完成排序时,MySQL会优先使用内存进行排序操作。在这种情况下,MySQL会创建一个临时内存表来存储排序结果,这样可以快速地对数据进行排序,提高查询效率。

**磁盘临时表排序:**如果排序操作需要处理的数据量超过了内存的限制,或者无法在内存中完成排序,MySQL会将数据溢写到磁盘上的临时表中进行排序。这种情况下,MySQL会创建一个磁盘临时表,并可能涉及到磁盘I/O操作,这会增加排序操作的时间。磁盘临时表的排序过程与内存临时表有所不同,可能会采用优先队列排序算法,减少了对临时文件的依赖。

MySQL有几个系统变量会影响排序操作是否使用内存临时表,例如sort_buffer_sizejoin_buffer_size

  • sort_buffer_size指定了MySQL在排序操作中使用的内存缓冲区大小。如果排序操作需要的内存超过这个值,MySQL会使用磁盘临时表。
  • join_buffer_size用于连接操作的内存缓冲区大小,但在某些情况下,也可以用于排序操作。

MySQL的查询优化器会根据查询的复杂性、数据量和可用内存等因素来决定是否使用内存临时表进行排序。优化器会估算使用内存临时表和磁盘临时表的代价,并选择成本更低的方案。

全字段排序 or rowid排序?

  1. 全字段排序

    • 当使用order by对查询结果进行排序时,MySQL会使用sort_buffer进行内存排序。
    • 如果排序数据量小于sort_buffer_size参数设置的内存大小,则排序在内存中完成;如果数据量过大,则需要使用外部排序,可能涉及到磁盘临时文件。
  2. rowid排序

    • 当查询的单行数据量max_length_for_sort_data,MySQL会采用rowid排序算法,只将排序用的字段和主键id放入sort_buffer中进行排序,能够减少内存使用。
    • 排序完成后,根据排序结果中的主键id回到原表中取出完整的行数据返回给客户端。这里因为涉及到回表过程,性能会下降。
常用优化方法
  1. 使用合适的索引

    • 创建合适的索引可以极大地提高排序速度。因为索引本身已经按照特定的顺序存储了数据。如果查询经常根据特定字段进行排序,考虑在这些字段上创建索引。
    • 如果查询中的order by子句与某个索引的顺序一致,MySQL可以利用这个索引来避免额外的排序操作。
    例如,如果有一个索引是(a, b),那么ORDER BY a, b与索引顺序一致,而ORDER BY b, a则不一致。
    
    • 使用覆盖索引,即一个包含所有查询字段的索引,这样可以避免回表查询,减少磁盘I/O操作。
    • 尽量避免查询条件无法有效利用索引而导致的全表扫描,因为这会增加排序的数据量。
  2. 减少排序数据量

    • 仅选择需要的字段,使用LIMIT子句来限制结果集的大小,避免不必要的数据传输和处理。
  3. 调整sort_buffer_size

    • 增加sort_buffer_size参数的值可以增加MySQL在排序过程中使用的内存量,有助于减少磁盘I/O操作,尤其是在处理大量数据时。
  4. 调整max_length_for_sort_data参数

    • 如果预期排序的数据行长度较大,可以通过调整max_length_for_sort_data参数来控制MySQL使用rowid排序还是全字段排序。
    • max_length_for_sort_data指定了单个数据行的最大长度(以字节为单位),如果单行数据的长度超过这个值,会转而使用rowid排序。
  5. 避免不必要的排序操作

    • 如果数据已经是排序好的,或者可以通过索引直接获取有序的数据,那么可以避免使用order by子句。
  6. 分析执行计划

    • 使用EXPLAIN命令来分析查询的执行计划,查看是否可以进行优化。

    • 使用OPTIMIZER_TRACE方法来进行分析,但可能会导致日志膨胀。

作者与版本更新计划

感谢您的阅读与支持!本文是《MySQL实战与优化》专栏中的一篇精选文章,该专栏共包含16篇文章,旨在为您提供实战中可直接应用的宝贵知识。

关注公众号【数舟】,获取作者最新动态,公众号后台回复【mysql2024】,即可免费领取这份包含16篇文章的完整的PDF专栏!

目前版本为v1.0,更新时间2024年4月10日。后续此文档更新与版本发布会同步到知识星球【数舟】中。

知识整理与创作不易,感谢大家理解与支持!

加入知识星球,您将获得更多独家内容、专栏更新以及与行业内专家和同行的互动交流机会。我们在知识星球等您,一起探索MySQL的深层次世界!

星球内目前包含300+精品文章,内容涵盖大数据、MySQL、运维、Python、调优、经验分享、数据分析等方向内容,会根据大家的学习需求更新更多方向的内容。

🔗 立即扫描下方二维码,加入知识星球,与行业精英共同成长,开启您的专属学习之旅!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/304491.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

知名的开源大模型及其特点

目前,开源的大模型领域涌现出了许多具有不同特点和优势的模型。这些开源大模型不仅推动了AI技术的发展,也为研究者和开发者提供了丰富的资源和工具,促进了AI应用的创新和多样化。以下是一些知名的开源大模型及其特点。北京木奇移动技术有限公…

Python测试框架之pytest详解

前言 Python测试框架之前一直用的是unittestHTMLTestRunner,听到有人说pytest很好用,所以这段时间就看了看pytest文档,在这里做个记录。 官方文档介绍: Pytest is a framework that makes building simple and scalable tests e…

2024年ERP软件上中下游结构分析及细分行业研究

环洋咨询Global Info Research的ERP软件市场调研报告提供ERP软件市场的基本概况,包括定义,分类,应用和产业链结构,同时还讨论发展政策和计划以及制造流程和成本结构,分析ERP软件市场的发展现状与未来市场趋势&#xff…

String类(1)

❤️❤️前言~🥳🎉🎉🎉 hellohello~,大家好💕💕,这里是E绵绵呀✋✋ ,如果觉得这篇文章还不错的话还请点赞❤️❤️收藏💞 💞 关注💥&a…

三年Android开发经验面试经历分享

最近,参加了多家公司的面试,下面是我所经历的一些面试问题及自己的回答思路。 一、京东面试 一面: 项目内容:主要讲述了在实习期间参与的项目,以及在项目中负责的工作和取得的成果。MVP模式:解释了MVP模…

CSS实现三栏自适应布局(两边固定,中间自适应)

绝对定位的元素会脱离文档流,它们是相对于包含块(通常是最近的具有相对定位、绝对定位或固定定位属性的父元素)进行定位的。当你把一个绝对定位的元素的高度设置为100%时,它会相对于其包含块的高度来确定自己的高度。如果包含块是…

政安晨:【深度学习神经网络基础】(六)—— 前馈神经网络

目录 简述 前馈神经网络结构 计算输出 初始化权重 径向基函数神经网络 径向基函数 政安晨的个人主页:政安晨 欢迎 👍点赞✍评论⭐收藏 收录专栏: 政安晨的机器学习笔记 希望政安晨的博客能够对您有所裨益,如有不足之处,欢迎…

Centos7源码方式安装Elasticsearch 7.10.2单机版

版本选择参考:Elasticsearch如何选择版本-CSDN博客 下载 任选一种方式下载 官网7.10.2版本下载地址: https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-7.10.2-linux-x86_64.tar.gz 网盘下载链接 链接:https://pan…

OpenGL Assimp 加载3D模型介绍

OpenGL Assimp 加载3D模型介绍 Assimp对应模型结构体解说 所有的模型、场景数据都包含在scene对象中,如所有的材质和Mesh。同样,场景的根节点引用也包含在这个scene对象中 场景的Root node(根节点)可能也会包含很多子节点和一个…

c++的学习之路:16、list(3)

上章有一些东西当时没学到,这里学到了将在补充,文章末附上代码,思维导图。 目录 一、赋值重载 二、带模板的创建 三、析构函数 四、代码 五、思维导图 一、赋值重载 这里的赋值重载就是直接利用交换函数进行把传参生成的临时数据和需要…

对称加密学习

对称加密是一种加密技术,它使用相同的密钥进行数据的加密和解密操作。这种加密方法因其高效性和速度优势,在数据加密领域得到了广泛的应用。 下面是两篇文章: AES加密学习-CSDN博客 加密算法学习-CSDN博客 推荐关注加密专栏: …

建模实例评点(6)业务流程-农业大棚

1 00:00:02,650 --> 00:00:06,000 假设这一步不是老司机来做 2 00:00:06,320 --> 00:00:08,430 主管不是老司机,是个小白 3 00:00:08,440 --> 00:00:09,470 比如像我这样 4 00:00:09,990 --> 00:00:11,580 潘加宇去做这个事情 5 00:00:12,460 -->…

C++感受4-HelloWorld中文版——认识编码

及时了解“编码”对编写代码的影响,是中国程序员越早知道越好的知识点。 一分钟了解什么叫“编码”和“解码”;通过实际演示,充分理解中文Windows下,C源代码编码需要注意的地方;通过 -finput-charsetutf8 等 g 编译配置…

如何训练自己的ChatGPT?需要多少训练数据?

近年,聊天机器人已经是很常见的AI技术。小度、siri、以及越来越广泛的机器人客服,都是聊天机器人的重要适用领域。然而今年,ChatGPT的面世让这一切都进行到一个全新的高度,也掀起了大语言模型(LLM)的热潮。…

麒麟系统ARM安装rabbitmq

简单记录下,信创服务器:麒麟系统,安装rabbitmq的踩坑记录。 本文章参考了很多大佬文章,我整理后提供。 一、安装基础依赖 yum -y install make gcc gcc-c kernel-devel m4 ncurses-devel openssl-devel unixODBC-devel 二、下载…

实现自动打包py及替换pyinstaller --add-data参数的方法

2024年了,PyInstaller已经来到了6.5.0版本,可我还是不会用它那个--add-data的方法,度了几圈试了试,始终不(行)如(不)意(通),就是没能把附加文件&a…

【nodejs基础学习三-浏览器偏好设置】

系列文章目录 第一章 nodejs基础学习–注释、变量、运算符、字符串、函数(一) 第二章 nodejs基础学习–循环、对象字符、模块导入出(二) 第三章 nodejs基础学习三-浏览器设置 系列文章目录一、开发者模式二、web偏好设置 一、开发…

2021年团体程序设计天梯赛-总决赛_L1

标题:L1-1 人与神 题目: 跨界大神 L. Peter Deutsch 有一句名言:“To iterate is human, to recurse divine.”(迭代的是人,递归的是神)。本题就请你直接在屏幕上输出这句话。 输入格式: 本题没…

Linux之线程互斥与同步

1.线程互斥相关概念 临界资源:多线程执行流共享的资源就叫做临界资源 。 临界区:每个线程内部,访问临界自娱的代码,就叫做临界区。 互斥:任何时刻,互斥保证有且只有一个执行流进入临界区,访问临…