排序相关问题

这篇具有很好参考价值的文章主要介绍了排序相关问题。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

本篇博客在B站做了内部分享,标题为「排序相关问题」

MySQL的ORDER BY有两种排序实现方式:

  1. 利用有序索引获取有序数据
  2. (不得不进行)文件排序

在explain中分析时,利用有序索引获取有序数据显示Using index,文件排序显示Using filesort

排序相关问题,后端



1. 能够 利用有序索引获取有序数据 的条件比较苛刻


以下几种优化方式,可能使order by利用到索引,而无需进行filesort:

1、ORDER BY的索引优化。如果一个SQL语句形如:
SELECT [column1],[column2],…. FROM [TABLEORDER BY [sort];
在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。
 
2、WHERE + ORDER BY的索引优化,形如:
SELECT [column1],[column2],…. FROM [TABLEWHERE [columnX] = [valueORDER BY [sort];
建立一个联合索引(columnX,sort)来实现order by 优化。
 
注意:如果columnX对应多个值,如下面语句就无法利用索引来实现order by的优化
SELECT [column1],[column2],…. FROM [TABLEWHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];
 
3、WHERE+ 多个字段ORDER BY
SELECT * FROM [tableWHERE uid=1 ORDER x,y LIMIT 0,10;
建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多。
排序相关问题,后端

Order By不能使用索引来优化排序的情况:

  • 对不同的索引键做 ORDER BY :(key1,key2分别建立索引)

      SELECT * FROM t1 ORDER BY key1, key2;

  • 在非连续的索引键部分上做 ORDER BY:(key_part1,key_part2建立联合索引;key2建立索引)

      SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

  • 同时使用了 ASC 和 DESC:(key_part1,key_part2建立联合索引)

      SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

  • 用于搜索记录的索引键和做 ORDER BY 的不是同一个:(key1,key2分别建立索引)

      SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

  • 如果在WHERE和ORDER BY的栏位上应用表达式(函数)时,则无法利用索引来实现order by的优化

      SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;

CREATE TABLE `weekxxxxxnor_detail` (
  `id` int(11unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `mid` int(11NOT NULL DEFAULT '0' COMMENT '用户ID',
  `hid` int(11NOT NULL DEFAULT '0' COMMENT '荣誉ID',
  `word` varchar(10NOT NULL DEFAULT '' COMMENT '字',
  `text` varchar(20NOT NULL DEFAULT '' COMMENT '文案',
  `description` varchar(40NOT NULL DEFAULT '' COMMENT '说明',
  `xxxxx_date` date NOT NULL DEFAULT '0000-00-00' COMMENT 'xx生成日期(每周日)',
  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `ix_mtime` (`mtime`),
  KEY `ix_mid_honor_date` (`mid`,`honor_date`)
ENGINE=InnoDB AUTO_INCREMENT=149628474 DEFAULT CHARSET=utf8 COMMENT='xxxxxx详情记录'


详细参见 https://note.youdao.com/web/#/file/WEB00c9fc9e542b90ea18d0c3cc53e74d96/note/WEBcb79302cc9f9cadb9d543963f9793baf/

搜索 ENGINE=InnoDB AUTO_INCREMENT=149628474 DEFAULT CHARSET=utf8 COMMENT=

排序相关问题,后端
排序相关问题,后端



2. filesort


2.1 在内存中可能用 堆排序或快速排序,


具体使用哪一种排序方式是优化器决定的,基本原则如下

快速排序算法:大量排序
堆排序算法:排序量不大

快速排序和堆排序都是不稳定的排序算法,对于重复值不能保证顺序。这就是Order by排序可能会不稳定的原因

排序相关问题,后端

之前遇到的坑:

排序相关问题,后端

2.1.1 在把数据加载到BUFFER内部时有两种方式:

  • 双路排序:(rowid排序/二次访问排序/回表排序模式)

首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息,然后在sort buffer 中进行排序。排序后再把查询字段依照行指针取出,共执行两次磁盘io。

  • 单路排序:MySQL4.1之后新增(全字段排序/一次访问排序)

一次性取出满足条件行的所有字段,然后在sort buffer中进行排序。 执行一次磁盘io。代价是对内存占用大


使用哪种方式,取决于设定的系统参数max_length_for_sort_data(默认为1K) 和Query 语句所取出的字段类型大小总和的大小关系, 来判定是使用双路排序还是单路排序。

如果单行的长度超过max_length_for_sort_data的值,MySQL就认为单行太大,使用双路排序方式;

如果 max_length_for_sort_data更大,则使用第二种优化后的算法。


所以如果希望 ORDER BY 操作的效率尽可能的高,一定要注意max_length_for_sort_data 参数的设置。

排序相关问题,后端

2.2 在外部使用多路归并排序算法:


排序相关问题,后端

2.3 整个filesort的过程如下:


(1)根据表的索引或者全表扫描,读取所有满足条件的记录。

(2)对于每一行,存储一对值到缓冲区(排序列和行记录指针,或者是排序列和查询需要的所有列),缓冲区的大小为sort_buffer_size大小(默认为1M)。

(3)当缓冲区满后,运行一个快速排序(qsort; 数据量不大时也可能用堆排序)来将缓冲区中数据排序,并将排序完的数据存储到一个临时文件,并保存一个存储块的指针,当然如果缓冲区不满,则不会重建临时文件了。

(4)重复以上步骤,直到将所有行读完,并建立相应的有序的临时文件。

(5)对块级进行排序,使用归并排序算法,通过对几个临时文件的指针来不断交换数据,最终达到几个文件,都是有序的。

(6)重复5直到所有的数据都排序完毕。

(7)采取顺序读的方式,将每行数据读入内存(这里读取数据时并不是一行一行读),并取出数据传到客户端,读取缓存大小由read_rnd_buffer_size来指定。

排序相关问题,后端

参考:

https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html#order-by-filesort-in-memory

本文由 mdnice 多平台发布文章来源地址https://www.toymoban.com/news/detail-708156.html

到了这里,关于排序相关问题的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

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

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

相关文章

  • Vue 中 element-ui table 结合后端请求实现排序

    一.需求 需要对指定列,结合后端请求进行排序 二.效果 三.知识点 3.1 如果需要结合后端请求排序,将需要排序的列上设置sortable为custom 3.2 同时在el-table标签上监听sort-change事件,在事件回调中可以获取当前排序列的字段名和排序顺序,从而将这些作为发起接口请求的入参 3

    2024年02月15日
    浏览(42)
  • 【数据结构】——排序算法的相关习题

    1、直接插入排序 1、对n个元素进行直接插入排序,需要进行()趟处理。 A、n B、n+1 C、n-1 D、2n 解析: (C) 直接插入排序是将要排序的序列按照的大小插入至已排好序的子序列中,一直进行直到整个序列有序,所以对n个元素进行直接插入排序,一共插入元素n-1次,

    2024年02月03日
    浏览(45)
  • C#中sort排序相关用法介绍

     C#中,List.Sort() 不仅为我们提供了默认的排序方法,还为我们提供了4种自定义排序的方法,通过默认排序方法,我们无需重写任何Sort()方法的实现代码,就能对单参数类型的List数据进行单一规则的排序,如果通过对这些方法进行改进我们可以轻松做到对多参数、多规则的复

    2024年02月15日
    浏览(60)
  • Lucene(10):Lucene相关度排序

    1 什么是相关度排序 Lucene对查询和索引文档的相关度进行打分,得分高的就排在前边。 1.1 如何打分 Lucene是在用户进行检索时实时根据搜索的计算出来的,分两步: 计算出词(Term)的权重 根据词的权重值,计算文档相关度得分。 1.2 什么是词的权重 明确索引的

    2024年02月10日
    浏览(40)
  • ES:先按相关性分数进行排序,分数相同时再按其他字段排序

    最近,在公司学习ES的使用,导师给了个题目,如何对一个文档先计算分数,用分数进行排序,在分数相同的情况下再按照别的字段(如时间)进行排序,为此,从来没接触过ES的我开启了艰难的学习之路 本文参考自 ES权威指南(中文版) 以下是目录: 相关性算分描述了一个

    2024年02月05日
    浏览(43)
  • 【数据结构】【算法】二叉树、二叉排序树、树的相关操作

    树结构是以分支关系定义的一种层次结构,应用树结构组织起来的数据,逻辑上都具有明显的层次关系。 操作系统中的文件管理系统、网络系统中的域名管理、数据库系统中的索引管理等都使用了树结构来组织和管理数据。 树 Tree 是由n个节点组成的有限集合。在任意一颗非

    2024年02月04日
    浏览(54)
  • Springcloudalibaba整合es!实现相关性排序,集成完代码真香

    org.springframework.boot spring-boot-starter-data-elasticsearch 2.0.1.RELEASE com.querydsl querydsl-apt 4.2.2 com.querydsl querydsl-jpa 4.2.2 ma.glasnost.orika orika-core 1.5.2 相关性排序实现思路原理图: ============= 核心代码实现: kibana:查询json { “function_score” : { “query” : { “bool” : { “must” : [ { “multi_match”

    2024年04月22日
    浏览(30)
  • 【MySQL】关于数据库字符编码以及字符集排序相关知识

    通过设置表的字符集和排序规则,解决MySQL查询时不区分字母大小写、插入时不支持特殊字符的问题。 关于MySQL查询时不区分字母大小写、插入时不支持特殊字符的问题,只有修改表的字符集和排序规则才能根治,而且事半功倍。utf8mb4支持的最低mysql版本为5.5.3+,若不是,请

    2024年02月12日
    浏览(45)
  • js对map排序,后端返回有序的LinkedHashMap类型时前端获取后顺序依旧从小到大的解决方法

    js对map排序,后端返回有序的LinkedHashMap类型时前端获取后顺序依旧从小到大的解决方法 浏览器获取结果变成了: [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0vkNViJ4-1683592135147)(C:UsersquyanliangAppDataRoamingTyporatypora-user-images1683592070705.png)] 所以

    2024年02月05日
    浏览(51)
  • Lucene中的Field域、索引维护、搜索、相关度排序和中文分词器讲解

    Field是文档中的域,包括 Field名 和 Field值 两部分,一个文档可以包括多个Field,Document只是Field的一个承载体,Field值即为要索引的内容,也是要搜索的内容。 是否分词(tokenized) 是:作分词处理,即将Field值进行分词, 分词的目的是为了索引 。 比如:商品名称、商品简介等,

    2024年02月05日
    浏览(42)

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

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

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

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

二维码1

领取红包

二维码2

领红包