【mysql学习篇】Order by与Group by优化以及排序算法详解

这篇具有很好参考价值的文章主要介绍了【mysql学习篇】Order by与Group by优化以及排序算法详解。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、Order by与Group by优化

Case1:

【mysql学习篇】Order by与Group by优化以及排序算法详解,Mysql,mysql,学习,排序算法

分析: 利用最左前缀法则:中间字段不能断,因此查询用到了name索引,从key_len=74也能看出,age索引列用在排序过程中,因为Extra字段里没有using filesort

注意: order by age 虽然用到了索引,但是不会在key_len列体现

Case 2:

【mysql学习篇】Order by与Group by优化以及排序算法详解,Mysql,mysql,学习,排序算法

分析: 从explain的执行结果来看:key_len=74,查询使用了name索引,由于用了position进行排序,跳过了age,出现了Using filesort。

注意: 这里跳过了age,这里position是无序的,所以不会走索引

Case 3:

【mysql学习篇】Order by与Group by优化以及排序算法详解,Mysql,mysql,学习,排序算法

分析: 查找只用到索引name,age和position用于排序,无Using filesort。

Case 4:

【mysql学习篇】Order by与Group by优化以及排序算法详解,Mysql,mysql,学习,排序算法
分析: 和Case 3中explain的执行结果一样,但是出现了Using filesort,因为索引的创建顺序为name,age,position,但是排序的时候age和position颠倒位置了。

重点注意: 这边颠倒age和position,mysql不会像前面提到的where后面一样优化最左前缀

Case 5:

【mysql学习篇】Order by与Group by优化以及排序算法详解,Mysql,mysql,学习,排序算法

分析: 与Case 4对比,在Extra中并未出现Using filesort,因为age为 常量,在排序中被优化,所以索引未颠倒,不会出现Using filesort。

Case 6:

【mysql学习篇】Order by与Group by优化以及排序算法详解,Mysql,mysql,学习,排序算法

分析: 虽然排序的字段列与索引顺序一样,且order by默认升序,这里position desc变成了降序,导致与索引的排序方式不同,从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式

Case 7:

【mysql学习篇】Order by与Group by优化以及排序算法详解,Mysql,mysql,学习,排序算法

分析: 对于排序来说,多个相等条件也是范围查询

Case 8:

【mysql学习篇】Order by与Group by优化以及排序算法详解,Mysql,mysql,学习,排序算法

可以用覆盖索引优化

【mysql学习篇】Order by与Group by优化以及排序算法详解,Mysql,mysql,学习,排序算法

二、Using filesort 文件排序原理详解

filesort文件排序方式

在使用explain分析查询的时候,利用有序索引获取有序数据显示Using index。如果MySQL在排序的时候没有使用到索引那么就会输出using filesort,即使用文件排序。

文件排序是通过相应的排序算法,将取得的数据在内存中进行排序:

  1. MySQL需要将数据在内存中进行排序,所使用的内存区域也就是我们通过sort_buffer_size系统变量所设置的sort buffer(排序区)。
  2. 这个sort buffer是每个Thread独享的,所以说可能在同一时刻在MySQL中可能存在多个sort buffer内存区域。

1. 双路排序(又叫回表排序模式)

  1. 首先根据相应的条件取出相应的 排序字段可以直接定位行数据的行 ID
  2. 然后在 sort buffer (内存排序)中进行排序,排序完后需要再次取回其它需要的字段;
  3. 用trace工具可以看到sort_mode信息里显示< sort_key, rowid >

第一遍扫描出需要排序的字段,然后进行排序后,根据排序结果,第二遍再扫描一下需要select的列数据。这样会引起大量的随机IO,效率不高,但是节约内存。排序使用quick sort,但是如果内存不够则会按照 block 进行排序,将排序结果写入磁盘文件,然后再将结果合并。

2. 单路排序

  1. 一次性取出满足条件行的 所有字段,然后在 sort buffer 内存中进行排序;
  2. 用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
  3. 不需要回表获取其他字段效率高,但将所有字段取出,在sort buffer中排序,占用内存

如何选择文件排序方式

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

  1. 如果 字段的总长度小于max_length_for_sort_data ,那么使用 单路排序模式
  2. 如果 字段的总长度大于max_length_for_sort_data ,那么使用 双路排序模式

示例验证下各种排序方式:

【mysql学习篇】Order by与Group by优化以及排序算法详解,Mysql,mysql,学习,排序算法

查看下这条sql对应trace结果如下(只展示排序部分):

mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;  --开启trace
mysql> select * from employees where name = 'zhuge' order by position;
mysql> select * from information_schema.OPTIMIZER_TRACE;

trace排序部分结果:
"join_execution": {    --Sql执行阶段
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`employees`",
                "field": "position"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {                      --文件排序信息
              "rows": 10000,                           --预计扫描行数
              "examined_rows": 10000,                  --参与排序的行
              "number_of_tmp_files": 3,                --使用临时文件的个数,这个值如果为0代表全部使用的sort_buffer内存排序,否则使用的磁盘文件排序
              "sort_buffer_size": 262056,              --排序缓存的大小,单位Byte
              "sort_mode": "<sort_key, packed_additional_fields>"       --排序方式,这里用的单路排序
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
      
      
mysql> set max_length_for_sort_data = 10;    --employees表所有字段长度总和肯定大于10字节
mysql> select * from employees where name = 'zhuge' order by position;
mysql> select * from information_schema.OPTIMIZER_TRACE;

trace排序部分结果:
"join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`employees`",
                "field": "position"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
              "rows": 10000,
              "examined_rows": 10000,
              "number_of_tmp_files": 2,
              "sort_buffer_size": 262136,   
              "sort_mode": "<sort_key, rowid>"         --排序方式,这里用的双路排序
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */


mysql> set session optimizer_trace="enabled=off";    --关闭trace

我们先看单路排序的详细过程:

  1. 从索引name找到第一个满足 name = ‘zhuge’ 条件的主键 id
  2. 根据主键 id 取出整行,取出所有字段的值,存入 sort_buffer 中
  3. 从索引name找到下一个满足 name = ‘zhuge’ 条件的主键 id
  4. 重复步骤 2、3 直到不满足 name = ‘zhuge’
  5. 对 sort_buffer 中的数据按照字段 position 进行排序
  6. 返回结果给客户端

我们再看下双路排序的详细过程:

  1. 从索引 name 找到第一个满足 name = ‘zhuge’ 的主键id
  2. 根据主键 id 取出整行,把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
  3. 从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id
  4. 重复 3、4 直到不满足 name = ‘zhuge’
  5. 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
  6. 遍历排序好的 id 和字段 position,按照 id 的值回到原表中取出 所有字段的值返回给客户端

三、总结

  1. 其实对比两个排序模式,单路排序会把所有需要查询的字段都放到 sort buffer 中,而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序,然后再通过主键回到原表查询需要的字段。

  2. 如果 MySQL 排序内存 sort_buffer 配置的比较小并且没有条件继续增加了,可以适当把 max_length_for_sort_data 配置小点,让优化器选择使用双路排序算法,可以在sort_buffer 中一次排序更多的行,只是需要再根据主键回到原表取数据。

  3. 如果 MySQL 排序内存有条件可以配置比较大,可以适当增大 max_length_for_sort_data 的值,让优化器优先选择全字段排序(单路排序),把需要的字段放到 sort_buffer 中,这样排序后就会直接从内存里返回查询结果了。

  4. 所以,MySQL通过 max_length_for_sort_data 这个参数来控制排序,在不同场景使用不同的排序模式,从而提升排序效率。

注意: 如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序,但不能因为这个就随便增大sort_buffer(默认1M),mysql很多参数设置都是做过优化的,不要轻易调整。文章来源地址https://www.toymoban.com/news/detail-608522.html

到了这里,关于【mysql学习篇】Order by与Group by优化以及排序算法详解的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

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

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

相关文章

  • mysql织梦索引优化之MySQL Order By索引优化

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

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

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

    2024年02月10日
    浏览(49)
  • MySQL Group by 优化查询

      使用的是临时表,加文件排序(数据量小用内存排序) 注意:这里加的索引一般不会仅仅是group by后面的字段索引(大多数多少条件是一个以 该字段开头联合索引 ,方便使用覆盖索引或者索引下推)。如果该字段是一个varchar类型, 最好 加个int冗余字段,建立索引的字段,

    2024年02月05日
    浏览(45)
  • 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】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日
    浏览(57)
  • MySQL 数据库查询与数据操作:使用 ORDER BY 排序和 DELETE 删除记录

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

    2024年02月05日
    浏览(80)
  • 【ABAP】OPEN SQL(七)「GROUP BY | HAVING | ORDER BY」

    💂 作者简介: THUNDER王,一名热爱财税和SAP ABAP编程以及热爱分享的博主。目前于江西师范大学本科在读,同时任汉硕云(广东)科技有限公司ABAP开发顾问。在学习工作中,我通常使用偏后端的开发语言ABAP,SQL进行任务的完成,对SAP企业管理系统,SAP ABAP开发和数据库具有较

    2024年02月08日
    浏览(83)
  • MySQL 数据库 group by 语句怎么优化?

    我这里创建一张订单表 复制代码 同时也在表里插了一些数据 现在我们这里执行 group by 语句 复制代码 很明显,这里就可以统计出来 每件商品一共有多少订单数据! 2.1、explain 分析 不同的数据库版本,用 explain 执行的结果并不一致,同样是上面 sql 语句 「MySQL 5.7 版本」 Extr

    2024年02月06日
    浏览(44)
  • Elasticsearch增删改查、count、sum、group by、order by、like

    1、查找所有索引 2、查询 3、count 4、查询SQL:

    2024年02月16日
    浏览(43)
  • Hive:聚合函数、GROUP BY、ORDER BY、LIMIT、执行顺序和JOIN、函数

    1.聚合函数 常见的聚合函数: Count、Sum、Max、Min和Avg 特点:不管原始数据多少条,聚合之后只有一条 Count(column)返回某列的行数,不包括NULL值 2.GROUP BY select中的字段要么是GROUP BY字段,要么是被聚合函数应用的字段 2.HAVING WHERE中无法出现聚合函数,所以有了HAVING WHERE是分组前

    2024年02月07日
    浏览(48)

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

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

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

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

二维码1

领取红包

二维码2

领红包