MySQL实战解析底层---“order by“是怎么工作的

这篇具有很好参考价值的文章主要介绍了MySQL实战解析底层---“order by“是怎么工作的。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

目录

前言

全字段排序

rowid排序

全字段排序 VS rowid排序


  • 前言

  • 在开发应用的时候,一定会经常碰到需要根据指定的字段排序来显示结果的需求
  • 以举例市民表为例,假设你要查询城市是“杭州”的所有人名字,并且按照姓名排序返回前1000个人的姓名、年龄
  • 假设这个表的部分定义是这样的:

    MySQL实战解析底层---“order by“是怎么工作的

  • 这时的SQL语句可以这么写:

    MySQL实战解析底层---“order by“是怎么工作的

  • 这个语句看上去逻辑很清晰,但是你了解它的执行流程吗?
  • 在这篇文章聊聊这个语句是怎么执行的,以及有什么参数会影响执行的行为
  • 全字段排序

  • 前面介绍过索引,所以现在就很清楚了,为避免全表扫描,需要在city字段加上索引
  • 在city字段上创建索引之后,用explain命令来看看这个语句的执行情况

    MySQL实战解析底层---“order by“是怎么工作的

  • Extra这个字段中的“Using filesort”表示的就是需要排序,MySQL会给每个线程分配一块内存用于排序,称为sort_buffer
  • 为了说明这个SQL查询语句的执行过程,先来看一下city这个索引的示意图:

    MySQL实战解析底层---“order by“是怎么工作的

  • 从图中可以看到,满足city='杭州'条件的行,是从ID_X到ID_(X+N)的这些记录
  • 通常情况下,这个语句执行流程如下所示:
    • 1-初始化sort_buffer,确定放入name、city、age这三个字段
    • 2-从索引city找到第一个满足city='杭州'条件的主键id,也就是图中的ID_X
    • 3-到主键id索引取出整行,取name、city、age三个字段的值,存入sort_buffer中
    • 4-从索引city取下一个记录的主键id
    • 5-重复步骤3、4直到city的值不满足查询条件为止,对应的主键id也就是图中的ID_Y
    • 6-对sort_buffer中的数据按照字段name做快速排序
    • 7-按照排序结果取前1000行返回给客户端
  • 暂且把这个排序过程,称为全字段排序,执行流程的示意图如下所示:

    MySQL实战解析底层---“order by“是怎么工作的

  • 图中“按name排序”这个动作,可能在内存中完成,也可能需要使用外部排序,这取决于排序所需的内存和参数sort_buffer_size
  • sort_buffer_size,就是MySQL为排序开辟的内存(sort_buffer)的大小
  • 如果要排序的数据量小于sort_buffer_size,排序就在内存中完成
  • 但如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序
  • 可以用一些方法,来确定一个排序语句是否使用了临时文件
  • 某个方法是通过查看 OPTIMIZER_TRACE 的结果来确认的,可以从 number_of_tmp_files中看到是否使用了临时文件

    MySQL实战解析底层---“order by“是怎么工作的

  • number_of_tmp_files表示的是,排序过程中使用的临时文件数
  • 你一定奇怪,为什么需要12个文件?
  • 内存放不下时,就需要使用外部排序,外部排序一般使用归并排序算法
  • 可以这么简单理解,MySQL将需要排序的数据分成12份,每一份单独排序后存在这些临时文件中
  • 然后把这12个有序文件再合并成一个有序的大文件
  • 如果sort_buffer_size超过了需要排序的数据量的大小,number_of_tmp_files就是0,表示排序可以直接在内存中完成
  • 否则就需要放在临时文件中排序
  • sort_buffer_size越小,需要分成的份数越多,number_of_tmp_files的值就越大
  • 接下来再解释一下上图中其他两个值的意思
  • 示例表中有4000条满足city='杭州’的记录,所以可以看到 examined_rows=4000,表示参与排序的行数是4000行
  • sort_mode 里面的packed_additional_fields的意思是,排序过程对字符串做了“紧凑”处理
  • 即使name字段的定义是varchar(16),在排序过程中还是要按照实际长度来分配空间的
  • 这里需要注意的是,为了避免对结论造成干扰,把internal_tmp_disk_storage_engine设置成MyISAM
  • internal_tmp_disk_storage_engine:内部临时表默认采用的存储引擎
  • 否则,select 的结果会显示为4001
  • 这是因为查询OPTIMIZER_TRACE这个表时,需要用到临时表,而internal_tmp_disk_storage_engine的默认值是InnoDB
  • 如果使用的是InnoDB引擎的话,把数据从临时表取出来的时候,会让Innodb_rows_read的值加1
  • rowid排序

  • 在上面这个算法过程里面,只对原表的数据读了一遍,剩下的操作都是在sort_buffer和临时文件中执行的
  • 但这个算法有一个问题,就是如果查询要返回的字段很多的话,那么sort_buffer里面要放的字段数太多,这样内存里能够同时放下的行数很少,要分成很多个临时文件,排序的性能会很差
  • 所以如果单行很大,这个方法效率不够好
  • 那么,如果MySQL认为排序的单行长度太大会怎么做呢?
  • 接下来,修改一个参数,让MySQL采用另外一种算法

    MySQL实战解析底层---“order by“是怎么工作的

  • max_length_for_sort_data,是MySQL中专门控制用于排序的行数据的长度的一个参数
  • 它的意思是,如果单行的长度超过这个值,MySQL就认为单行太大,要换一个算法
  • city、name、age 这三个字段的定义总长度是36,把max_length_for_sort_data设置为16,再来看看计算过程有什么改变
  • 新的算法放入sort_buffer的字段,只有要排序的列(即name字段)和主键id
  • 但这时,排序的结果就因为少了city和age字段的值,不能直接返回了,整个执行流程就变成如下所示的样子:
    • 1-初始化sort_buffer,确定放入两个字段,即name和id
    • 2-从索引city找到第一个满足city='杭州’条件的主键id,也就是图中的ID_X
    • 3-到主键id索引取出整行,取name、id这两个字段,存入sort_buffer中
    • 4.从索引city取下一个记录的主键id
    • 5-重复步骤3、4直到不满足city='杭州’条件为止,也就是图中的ID_Y
    • 6.对sort_buffer中的数据按照字段name进行排序
    • 7-遍历排序结果,取前1000行,并按照id的值回到原表中取出city、name和age三个字段返回给客户端
  • 这个执行流程的示意图如下,我把它称为rowid排序:

    MySQL实战解析底层---“order by“是怎么工作的

  • 对比之前的全字段排序流程图你会发现,rowid排序多访问了一次表t的主键索引,就是步骤7
  • 需要说明的是,最后的“结果集”是一个逻辑概念,实际上MySQL服务端从排序后的sort_buffer中依次取出id,然后到原表查到city、name和age这三个字段的结果,不需要在服务端再耗费内存存储结果,是直接返回给客户端的
  • 根据这个说明过程和图示,可以想一下,这个时候执行select ,结果会是多少呢?
  • 现在就来看看结果有什么不同
  • 首先,图中的examined_rows的值还是4000,表示用于排序的数据是4000行
  • 但是select 这个语句的值变成5000了
  • 因为这时候除了排序过程外,在排序完成后,还要根据id去原表取值
  • 由于语句是limit 1000,因此会多读1000行

    MySQL实战解析底层---“order by“是怎么工作的

  • 从OPTIMIZER_TRACE的结果中,还能看到另外两个信息也变了
  • sort_mode变成了<sort_key, rowid>,表示参与排序的只有name和id这两个字段
  • number_of_tmp_files变成10了,是因为这时候参与排序的行数虽然仍然是4000行,但是每一行都变小了,因此需要排序的总数据量就变小了,需要的临时文件也相应地变少了
  • 全字段排序 VS rowid排序

  • 来分析一下,从这两个执行流程里,还能得出什么结论
  • 如果MySQL实在是担心排序内存太小,会影响排序效率,才会采用rowid排序算法,这样排序过程中一次可以排序更多行,但是需要再回到原表去取数据
  • 如果MySQL认为内存足够大,会优先选择全字段排序,把需要的字段都放到sort_buffer中,这样排序后就会直接从内存里面返回查询结果了,不用再回到原表去取数据
  • 这也就体现了MySQL的一个设计思想:如果内存够,就要多利用内存,尽量减少磁盘访问
  • 对于InnoDB表来说,rowid排序会要求回表多造成磁盘读,因此不会被优先选择
  • 这个结论看上去有点废话的感觉,但是要记住它
  • 看到这里就了解了,MySQL做排序是一个成本比较高的操作
  • 那么是不是所有的order by都需要排序操作呢?
  • 如果不排序就能得到正确的结果,那对系统的消耗会小很多,语句的执行时间也会变得更短
  • 其实,并不是所有的order by语句,都需要排序操作的
  • 从上面分析的执行过程,可以看到,MySQL之所以需要生成临时表,并且在临时表上做排序操作,其原因是原来的数据都是无序的
  • 可以设想下,如果能够保证从city这个索引上取出来的行,天然就是按照name递增排序的话,是不是就可以不用再排序了呢?确实是这样的
  • 所以可以在这个市民表上创建一个city和name的联合索引,对应的SQL语句是:

    MySQL实战解析底层---“order by“是怎么工作的

  • 作为与city索引的对比,来看看这个索引的示意图

    MySQL实战解析底层---“order by“是怎么工作的

  • 在这个索引里面,依然可以用树搜索的方式定位到第一个满足city='杭州'的记录,并且额外确保了,接下来按顺序取“下一条记录”的遍历过程中,只要city的值是杭州,name的值就一定是有序的
  • 这样整个查询过程的流程就变成了:

    MySQL实战解析底层---“order by“是怎么工作的

    • 1-从索引(city,name)找到第一个满足city='杭州'条件的主键id
    • 2-到主键id索引取出整行,取name、city、age三个字段的值,作为结果集的一部分直接返回
    • 3-从索引(city,name)取下一个记录主键id
    • 4-重复步骤2、3,直到查到第1000条记录,或者是不满足city='杭州'条件时循环结束
  • 可以看到,这个查询过程不需要临时表,也不需要排序
  • 接下来用explain的结果来印证一下

    MySQL实战解析底层---“order by“是怎么工作的

  • 从图中可以看到,Extra字段中没有Using filesort了,也就是不需要排序了
  • 而且由于(city,name)这个联合索引本身有序,所以这个查询也不用把4000行全都读一遍,只要找到满足条件的前1000条记录就可以退出了
  • 也就是说,在这个例子里,只需要扫描1000次
  • 那这个语句的执行流程有没有可能进一步简化呢?
  • 覆盖索引是指,索引上的信息足够满足查询请求,不需要再回到主键索引上去取数据
  • 按照覆盖索引的概念,可以再优化一下这个查询语句的执行流程
  • 针对这个查询,可以创建一个city、name和age的联合索引,对应的SQL语句就是:

    MySQL实战解析底层---“order by“是怎么工作的

  • 这时,对于city字段的值相同的行来说,还是按照name字段的值递增排序的,此时的查询语句也就不再需要排序了
  • 这样整个查询语句的执行流程就变成了:

    MySQL实战解析底层---“order by“是怎么工作的

    • 1-从索引(city,name,age)找到第一个满足city='杭州’条件的记录,取出其中的city、name和age这三个字段的值,作为结果集的一部分直接返回
    • 2-从索引(city,name,age)取下一个记录,同样取出这三个字段的值,作为结果集的一部分直接返回
    • 3-重复执行步骤2,直到查到第1000条记录,或者是不满足city='杭州'条件时循环结束
  • 然后再来看看explain的结果

    MySQL实战解析底层---“order by“是怎么工作的文章来源地址https://www.toymoban.com/news/detail-497218.html

  • 可以看到,Extra字段里面多了“Using index”,表示的就是使用了覆盖索引,性能上会快很多
  • 当然,这里并不是说要为了每个查询能用上覆盖索引,就要把语句中涉及的字段都建上联合索引,毕竟索引还是有维护代价的
  • 这是一个需要权衡的决定

到了这里,关于MySQL实战解析底层---“order by“是怎么工作的的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • mysql order by 索引问题综合分析

    一,文章1 Mysql-索引失效 order by优化_orderby索引失效_zyk1.的博客-CSDN博客 总结: 0,索引 与 查询条件 与 排序字段关系,Using filesort出现场景 1.联合索引,最左匹配原则,不仅查询条件需要遵循,排序也需要遵循,查询+排序组合也要遵循 2.extra 避免出现filesort,使用index排序

    2024年02月07日
    浏览(43)
  • mysql织梦索引优化之MySQL Order By索引优化

    在一些情况下,MySQL可以直接使用索引来满足一个ORDER BY 或GROUP BY 子句而无需做额外的排序。尽管ORDER BY 不是和索引的顺序准确匹配,索引还是可以被用到,只要不用的索引部分和所有的额外的ORDER BY 字段在WHERE 子句中都被包括了。 使用索引的MySQL Order By 下列的几个查询都会

    2024年02月04日
    浏览(47)
  • 【mysql学习篇】Order by与Group by优化以及排序算法详解

    Case1: 分析: 利用 最左前缀 法则:中间字段不能断,因此查询用到了name索引,从key_len=74也能看出,age索引列用在排序过程中,因为Extra字段里没有using filesort 注意: order by age 虽然用到了索引,但是不会在key_len列体现 Case 2: 分析: 从explain的执行结果来看:key_len=74,查询

    2024年02月15日
    浏览(61)
  • MySQL Execution Plan -- IN条件与ORDER BY组合优化

    MySQL版本: 5.7.27-30-log Percona Server (GPL), wsrep_31.39 涉及表结构: 涉及SQL: 在系统没有任何压力情况下,该SQL执行时间超过200ms。 查看SQL对应执行计划: 查看满足WHERE条件数据: 通过profiling查看耗时情况: 根据profiling结果可以发现99.9%的耗时在 Creating sort index 环节,查询条件中

    2024年02月14日
    浏览(44)
  • MySQL使用SELECT 语句不加ORDER BY默认是如何排序的?

    大家好,我是阿飞云 怕什么真理无穷,进一步有近一步的欢喜 记录一个 MySQL 查询排序的问题,一个SQL语句没有加 order by ,那么查询出来的结果到底是按照什么规则排序的呢?查询了网上的一些资料,分享如下: •MyISAM 表 MySQL Select 默认排序是按照物理存储顺序显示的(不

    2024年02月10日
    浏览(49)
  • 【MySQL】union (all) 后 order by 子查询排序不生效问题解决方案

    2308. 按性别排列表格 表:Genders Column Name Type user_id int gender varchar user_id 是该表的主键(具有唯一值的列)。 gender 的值是 ‘female’,‘male’,‘other’ 之一。 该表中的每一行都包含用户的 ID 及其性别。 表格中 ‘female’,‘male’,‘other’ 数量相等。 编写一个解决方案以重新

    2024年01月17日
    浏览(58)
  • MySQL 数据库查询与数据操作:使用 ORDER BY 排序和 DELETE 删除记录

    使用 ORDER BY 语句按升序或降序对结果进行排序。 ORDER BY 默认按升序排序。要按降序排序结果,使用 DESC 。 示例按名称按字母顺序排序结果: ORDER BY DESC 使用 DESC 以降序排序结果。 示例按名称以字母逆序排序结果: 您可以使用\\\"DELETE FROM\\\"语句从现有表格中

    2024年02月05日
    浏览(80)
  • Mysql 中,为什么 WHERE 使用别名会报错,而 ORDER BY 不会报错?

       我们先对salary * 12 命名一个别名annual_sal  这段代码以annual_sal升序输出且正常执行没有报错。说明 order by 可以使用别名  我们再看看这个段代码 这段代码就报错了,报错说明是 Unknown column \\\'annual_sal\\\' in \\\'where clause\\\'。 由此可以得出where语句执行是找不到annual_sal. 要解决这个问

    2023年04月15日
    浏览(44)
  • 玩转Mysql系列 - 第8篇:详解排序和分页(order by & limit),及存在的坑

    这是Mysql系列第7篇。 环境:mysql5.7.25,cmd命令中进行演示。 代码中被[]包含的表示可选,|符号分开的表示可选其一。 本章内容 详解排序查询 详解limit limit存在的坑 分页查询中的坑 排序查询(order by) 电商中:我们想查看今天所有成交的订单,按照交易额从高到低排序,此

    2024年02月11日
    浏览(46)
  • mysql8之前如何实现row_number() over(partition by xxx order by xxx asc/desc)

    最近笔者在进行对广告业务的数据统计时遇到这种情况,业务方嫌弃离线数仓太慢,又无需太高的实时性本该使用即席查询的OLAP去做,但是当前公司调研的OLAP还没有推到广告业务侧,无奈只得使用mysql暂时顶一下。我们当前使用的是mysql5.7。 一充用户 :当日只有一次充值的

    2024年02月05日
    浏览(58)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包