分类导航

MySQL 发布时间：2022-05-15 发布网站：大佬教程 code.js-code.com

大佬教程收集整理的这篇文章主要介绍了MySQL排序内部原理探秘，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

［目录］

一、我们要解决什么问题
二、排序，排序，排序
三、索引优化排序
四、排序模式
五、外部排序
六、trace结果解释
七、MysqL其他相关排序参数
八、MysqL排序优化总结
九、参考文献

<h2 style="font-family:'microsoft yahei',arial;font-weight:500;line-height:1.1;color:rgb(51,51);font-size:30px;text-align:justify;">
一、我们要解决什么问题

MysqL排序其实是一个老生常谈的问题了，但是我们这次想由浅入深详细说说MysqL排序模式，怎么影响MysqL选择不同的排序模式和怎么优化排序。

同时也希望通过这篇文章解决大家的以下疑问：

@H_567_24@mysqL在哪些地方会使用排序，怎么判断MysqL使用了排序； @H_567_24@mysqL有几种排序模式，我们可以通过什么方法让MysqL选择不同的排序模式； @H_567_24@mysqL排序跟有啥关系，在哪些情况下增加能优化排序；

怎么判断MysqL使用到了磁盘来排序，怎么避免或者优化磁盘排序；

排序时变长字段（varchar）数据在内存是怎么存储的，5.7有哪些改进；

在

情况下，排序模式有哪些改进；

到底是什么鬼，该状态值过大说明了什么问题，可以通过什么方法解决；

最后MysqL使用到了排序的话，依次可以通过什么办法分析和优化让排序更快？

@H_280_50@mysqL执行计划时，经常会看到在Extra列中显示Using filesort。

@H_280_50@mysqL使用了排序。

disTinct、join等情况下。

默认采用的是B tree索引，B tree索引本身就是有序的，如果有一个查询如下：

一个()的索引就能够利用B tree的特性来避免额外排序。

title="MySQL排序内部原理探秘" alt="MySQL排序内部原理探秘" src="http://code.js-code.com/res/2019/01-10/21/5c035f70ceb672dec0256cb1bbe2ad2f.jpg" style="border:0px;vertical-align:middle;display:table;">

source Code Pro',monospace;color:inherit;display:block;"> *  t1
    key_part1,key_part2,... ;
<span class="hljs-operator"><span class="hljs-keyword" style="color:rgb(0,136);">WHERE key_part1 = constant

<span class="hljs-keyword" style="color:rgb(0,136);">BY key_part2;
<span class="hljs-operator"><span class="hljs-keyword" style="color:rgb(0,136);">BY key_part1 <span class="hljs-keyword" style="color:rgb(0,136);">DESC,key_part2 <span class="hljs-keyword" style="color:rgb(0,136);">DESc;
<span class="hljs-operator"><span class="hljs-keyword" style="color:rgb(0,136);">WHERE key_part1 = <span class="hljs-number" style="color:rgb(0,102,102);">1

<span class="hljs-keyword" style="color:rgb(0,136);">WHERE key_part1 > constant

<span class="hljs-keyword" style="color:rgb(0,136);">ASc;

WHERE key_part1 < constant WHERE key_part1 = constant1 AND key_part2 > constant2 BY key_part2;

从以上例子里面我们也可以看到，如果要让MysqL使用索引优化排序应该怎么建组合索引。

<h2 style="font-family:'microsoft yahei',51);font-size:30px;text-align:justify;">
四、排序模式
<h3 style="font-family:'microsoft yahei',51);font-size:24px;text-align:justify;">
4.1 实际trace结果

但是还是有非常多的sql没法使用索引进行排序，例如

@R_674_10288@ct * from film where Producer like '东京热%' and prod_time>'2015-12-01' order by actor_age;

我们想查询“东京热”出品的，从去年12月1号以来，并且按照演员的年龄排序的电影信息。
（好吧，假设我这里有一个每一位男DBA都想维护的数据库:）

这种情况下，使用索引已经无法避免排序了，那MysqL排序到底会怎么做列。

笼统的来说，它会按照：

依据’2015-12-01’过滤数据，查找需要的数据；

对查找到的数据按照进行排序,并按照将必要的数据按照依序返回给客户端。

@H_280_50@mysqL的optimize trace来查看是否如上所述。

@H_845_141@mysqL优化器trace信息，可以查看阿里印风的博客初识5.6的optimizer trace。

依据’2015-12-01’过滤数据，查找需要的数据

 '2015-12-01'))","attached_conditions_computation": [
      ],"attached_conditions_sumMary": [
        {
          "table": "`film`","attached": "((`film`.`Producer` like '东京热%') and (`film`.`prod_time` > '2015-12-01'))"
        }
      ]
    }

对查找到的数据按照source Code Pro',并按照依序返回给客户端



source Code Pro',monospace;color:inherit;display:block;">      "join_execution": {
        "#stepsfilesort_@R_118_4036@iondirectionfieldactor_agefilesort_priority_queue_optimizationusablecause applicable ( LIMIT)filesort_executionfilesort_sumMaryexamined_rowsnumber_of_tmp_filessorT_Buffer_sizesort_mode
@H_280_50@mysqL在执行这个@R_674_10288@ct的时候执行了针对film表字段的asc排序操作。
source Code Pro',monospace;color:inherit;display:block;">4036@ion": [
              {
                : ,: ,0);">"field": 
              }

@H_280_50@mysqL到底是怎么排序的，采用了什么排序算法。

"

@H_280_50@mysqL的sort_mode有三种。
sql/filesort.cc源码如下：
source Code Pro',monospace;color:inherit;display:block;">  Opt_trace_object(trace,0);">"filesort_sumMary")
    (,num_rows)
    examined_rows",paramexamined_rows)
    ,num_chunks)
    ,table_sort.sorT_Buffer_size())
    _alnum(,68);">.using_packed_addons() ?
               " :
               param_addon_fields() ?
                : )
@H_944_285@< sort_key,rowid="">”和“< sort_key,additional_fields="">看过其他介绍介绍MysqL排序文章的同学应该比较清楚，< sort_key,packed_additional_fields="">相对较新。


对应的是MysqL 4.1以后引入的“修改后排序模式”

根据索引或者全表扫描，按照过滤条件获得需要查询的排序字段值和row ID；


将要排序字段值和row ID组成键值对，存入sort buffer中；
如果sort buffer内存大于这些键值对的内存，就不需要创建临时文件了。否则，每次sort buffer填满以后，需要直接用qsort(快速排序算法)在内存中排好序，并写到临时文件中；
重复上述步骤，直到所有的行数据都正常读取了完成；
用到了临时文件的，需要利用磁盘外部排序，将row id写入到结果文件中；
根据结果文件中的row ID按序读取用户需要返回的数据。由于row ID不是顺序的，导致回表时是随机IO，为了进一步优化性能（变成顺序IO），MysqL会读一批row ID，并将读到的数据按排序字段顺序插入缓存区中(内存大小)。

title="MySQL排序内部原理探秘" alt="MySQL排序内部原理探秘" src="http://code.js-code.com/res/2019/01-10/21/9c4df1648b4ab7d0a2d6cf64d9ce75c4.jpg" style="border:0px;vertical-align:middle;display:table;">


根据索引或者全表扫描，按照过滤条件获得需要查询的数据；
将要排序的列值和用户需要返回的字段组成键值对，存入sort buffer中；
如果sort buffer内存大于这些键值对的内存，就不需要创建临时文件了。否则，每次sort buffer填满以后，需要直接用qsort(快速排序算法)在内存中排好序，并写到临时文件中；
重复上述步骤，直到所有的行数据都正常读取了完成；
用到了临时文件的，需要利用磁盘外部排序，将排序后的数据写入到结果文件中；
直接从结果文件中返回用户需要的字段数据，而不是根据row ID再次回表查询。

title="MySQL排序内部原理探秘" alt="MySQL排序内部原理探秘" src="http://code.js-code.com/res/2019/01-10/21/d93d3fc8ea2666489b89f6c7dc70f0f9.jpg" style="border:0px;vertical-align:middle;display:table;">





间的方法。
用户要查询的数据非常大的话，很多时间浪费在多次磁盘外部排序，导致更多的IO操作，效率可能还不如第一种方式。
@H_280_50@mysqL给用户提供了一个的参数。当“排序的键值对大小” > 时，MysqL认为磁盘外部排序的IO效率不如回表的效率，会选择第一种排序模式；反之，会选择第二种不回表的模式。
title="MySQL排序内部原理探秘" alt="MySQL排序内部原理探秘" src="http://code.js-code.com/res/2019/01-10/21/72d4babe19b1364f287f57966726f3fe.jpg" style="border:0px;vertical-align:middle;display:table;">

解决变长字符数据存储空间浪费的问题，对于实际数据不多，字段定义较长的改进效果会更加明显。
文章写到这里可能就差不多了，但是大家忘记关注一个问题了：“如果排序的数据不能完全放在sort buffer内存里面，是怎么通过外部排序完成整个排序过程的呢？”
解决这个问题，我们首先需要简单查看一下外部排序到底是怎么做的。





从要排序的900M数据中读取100MB数据到内存中，并按照传统的内部排序算法（快速排序）进行排序；
将排序好的数据写入磁盘；
重复1，2两步，直到每个100MB chunk大小排序好的数据都被写入磁盘；
每次读取排序好的chunk中前10MB（= 100MB / (9 chunks + 1)）数据，一共9个chunk需要90MB，剩下的10MB作为输出缓存；
对这些数据进行一个“9路归并”，并将结果写入输出缓存。如果输出缓存满了，则直接写入最终排序结果文件并清空输出缓存；如果9个10MB的输入缓存空了，从对应的文件再读10MB的数据，直到读完整个文件。最终输出的排序结果文件就是900MB排好序的数据了。

title="MySQL排序内部原理探秘" alt="MySQL排序内部原理探秘" src="http://code.js-code.com/res/2019/01-10/21/9fa80c2c693a4393257a7a43bddc457c.jpg" style="border:0px;vertical-align:middle;display:table;">


一个两路排序算法（先排序，后归并）。但是这种算法有一个问题，假设要排序的数据是50GB而内存只有100MB，那么每次从500个排序好的分片中取200KB（100MB / 501 约等于200KB）就是很多个随机IO。效率非常慢，对应可以这样来改进：
从要排序的50GB数据中读取100MB数据到内存中，并按照传统的内部排序算法（快速排序）进行排序；
将排序好的数据写入磁盘；
重复1，2两步，直到每个100MB chunk大小排序好的数据都被写入磁盘；
每次取25个分片进行归并排序，这样就形成了20个（500/25=20）更大的2.5GB有序的文件；
对这20个2.5GB的有序文件进行归并排序，形成最终排序结果文件。


title="MySQL排序内部原理探秘" alt="MySQL排序内部原理探秘" src="http://code.js-code.com/res/2019/01-10/21/32356ff65a2749931f5519ce405fc860.jpg" style="border:0px;vertical-align:middle;display:table;">

@H_287_241@mysqL外部排序
@H_87_450@@H_533_81@mysqL外部排序算法
@H_280_50@mysqL使用的外部排序是怎么样的列，我们以回表排序模式为例：

根据索引或者全表扫描，按照过滤条件获得需要查询的数据；


将要排序的列值和row ID组成键值对，存入sort buffer中；


如果sort buffer内存大于这些键值对的内存，就不需要创建临时文件了。否则，每次sort buffer填满以后，需要直接用qsort(快速排序模式)在内存中排好序，作为一个block写到临时文件中。跟正常的外部排序写到多个文件中不一样，MysqL只会写到一个临时文件中，并通过保存文件偏移量的方式来模拟多个文件归并排序；


重复上述步骤，直到所有的行数据都正常读取了完成；


每MERGEBUFF (7) 个block抽取一批数据进行排序，归并排序到另外一个临时文件中，直到所有的数据都排序好到新的临时文件中；


重复以上归并排序过程，直到剩下不到MERGEBUFF2 (15)个block。
通俗一点解释： 
第一次循环中，一个block对应一个sort buffer（大小为）排序好的数据；每7个做一个归并。 
第二次循环中，一个block对应MERGEBUFF (7) 个sort buffer的数据，每7个做一个归并。 
… 
直到所有的block数量小于MERGEBUFF2 (15)。



最后一轮循环，仅将row ID写入到结果文件中；


根据结果文件中的row ID按序读取用户需要返回的数据。为了进一步优化性能，MysqL会读一批row ID，并将读到的数据按排序字段要求插入缓存区中(内存大小)。



@H_567_24@mysqL把外部排序好的分片写入同一个文件中，通过保存文件偏移量的方式来区别各个分片位置；
@H_567_24@mysqL每MERGEBUFF (7)个分片做一个归并，最终分片数达到MERGEBUFF2 (15)时，做最后一次归并。这两个值都写死在代码中了……


@H_280_50@mysqL手册中对的描述只有一句话
source Code Pro',monospace;color:inherit;display:block;"> Sort_merge_passes
The    passes that   algorithm has had  . If this  is large,you should consider increasing     sorT_Buffer_size stem .
到底是什么，该值比较大时说明了什么，通过什么方式可以缓解这个问题。
@H_280_50@mysqL的外部排序算法搞清楚了，这个问题就清楚了。
对应的就是MysqL做归并排序的次数，也就是说，如果值比较大，说明和要排序的数据差距越大，我们可以通过增大或者让填入的键值对更小来缓解归并排序的次数。

@H_280_50@mysqL外部排序的算法中第5到第7步，是通过sql/filesort.cc文件中函数来实现，第5步单次归并使用实现，源码摘录如下：
source Code Pro',monospace;color:inherit;display:block;">int merge_many_buff(Sort_param *param,SorT_Buffer sorT_Buffer,Merge_chunk_array chunk_array,size_t *p_num_chunks,IO_CACHE *t_filE)
{

<span class="hljs-keyword" style="color:rgb(0,136);"&gt;for</span> (i=<span class="hljs-number" style="color:rgb(0,102);"&gt;0</span> ; i < num_chunks - MERGEBUFF * <span class="hljs-number" style="color:rgb(0,102);"&gt;3</span> / <span class="hljs-number" style="color:rgb(0,102);"&gt;2</span> ; i+= MERGEBUFF)
{
  <span class="hljs-keyword" style="color:rgb(0,136);"&gt;if</span> (merge_buffers(p<a href="http://code.js-code.com/tag/ara/" target="_blank" class="keywords">ara</a>m,// p<a href="http://code.js-code.com/tag/ara/" target="_blank" class="keywords">ara</a>m
                    from_file,// from_file
                    to_file,// to_file
                    sorT_Buffer,// sorT_Buffer
                    last_chunk++,// last_chunk [out]
                    Merge_chunk_array(&amp;chunk_arraY[i],MERGEBUFF),<span class="hljs-number" style="color:rgb(0,102);"&gt;0</span>))                     // flag
  goto cleanup;
}
<span class="hljs-keyword" style="color:rgb(0,from_file,to_file,sorT_Buffer,last_chunk++,Merge_chunk_array(&amp;chunk_arraY[i],num_chunks - i),102);"&gt;0</span>))
  <span class="hljs-keyword" style="color:rgb(0,136);"&gt;break</span>;                                    /* purecov: inspected */
<span class="hljs-keyword" style="color:rgb(0,136);">...

}

<p style="color:rgb(51,arial;font-size:16px;text-align:justify;">

截取部分<code style="font-family:'source Code Pro',63);">merge_buffers()的代码如下，


<pre class="prettyprint" style="overflow:auto;font-family:'source Code Pro',monospace;color:inherit;display:block;">int merge_buffers(Sort_param param,IO_CACHE from_file,IO_CACHE to_file,Merge_chunk last_chunk,int flag)

{

<span class="hljs-keyword" style="color:rgb(0,136);">...

current_thd->inc_status_sort_merge_passes();

<span class="hljs-keyword" style="color:rgb(0,arial;font-size:16px;text-align:justify;">

可以看到：每个<code style="font-family:'source Code Pro',63);">merge_buffers()都会增加<code style="font-family:'source Code Pro',63);">sort_merge_passes，也就是说每一次对MERGEBUFF

(7)个block归并排序都会让<code style="font-family:'source Code Pro',63);">sort_merge_passes加一，<code style="font-family:'source Code Pro',63);">sort_merge_passes越多表示排序的数据太多，需要多次merge

pass。解决的方案无非就是缩减要排序数据的大小或者增加<code style="font-family:'source Code Pro',63);">sorT_Buffer_size。


<p style="color:rgb(51,arial;font-size:16px;text-align:justify;">

打个小广告，在我们的qmonitor中就有<code style="font-family:'source Code Pro',63);">sort_merge_pass的性能指标和参数值过大的报警设置。


<h2 style="font-family:'microsoft yahei',51);font-size:30px;text-align:justify;">

六、trace结果解释

<p style="color:rgb(51,arial;font-size:16px;text-align:justify;">

说明白了三种排序模式和外部排序的方法，我们回过头来看一下trace的结果。


<h3 style="font-family:'microsoft yahei',51);font-size:24px;text-align:justify;">

6.1 是否存在磁盘外部排序

<p style="color:rgb(51,63);">"number_of_tmp_files": 0,


<p style="color:rgb(51,63);">number_of_tmp_files表示有多少个分片，如果<code style="font-family:'source Code Pro',63);">number_of_tmp_files不等于0，表示一个<code style="font-family:'source Code Pro',63);">sorT_Buffer_size大小的内存无法保存所有的键值对，也就是说，MysqL在排序中使用到了磁盘来排序。


<h3 style="font-family:'microsoft yahei',51);font-size:24px;text-align:justify;">

6.2 是否存在优先队列优化排序

<p style="color:rgb(51,arial;font-size:16px;text-align:justify;">

由于我们的这个sql里面没有对数据进行分页限制，所以<code style="font-family:'source Code Pro',63);">filesort_priority_queue_optimization并没有启用


<pre class="prettyprint" style="overflow:auto;font-family:'source Code Pro',0);">"filesort_priority_queue_optimization": {

<span class="hljs-String" style="color:rgb(0,0);">"usable": <span class="hljs-literal" style="color:rgb(0,102);">false,0);">"cause": <span class="hljs-String" style="color:rgb(0,0);">"not applicable (no LIMIT)"

},

<p style="color:rgb(51,arial;font-size:16px;text-align:justify;">

而正常情况下，使用了Limit会启用优先队列的优化。优先队列类似于FIFO先进先出队列。


<p style="color:rgb(51,arial;font-size:16px;text-align:justify;">

算法稍微有点改变，以回表排序模式为例。


<p style="color:rgb(51,arial;font-size:16px;text-align:justify;">

<span style="font-weight:700;"><code style="font-family:'source Code Pro',63);">sorT_Buffer_size足够大

<p style="color:rgb(51,arial;font-size:16px;text-align:justify;">

如果Limit限制返回N条数据，并且N条数据比<code style="font-family:'source Code Pro',63);">sorT_Buffer_size小，那么MysqL会把sort buffer作为priority queue，在第二步插入priority queue时会按序插入队列；在第三步，队列满了以后，并不会写入外部磁盘文件，而是直接淘汰最尾端的一条数据，直到所有的数据都正常读取完成。


<p style="color:rgb(51,arial;font-size:16px;text-align:justify;">

算法如下：

<ul style="color:rgb(51,arial;font-size:16px;text-align:justify;">
根据索引或者全表扫描，按照过滤条件获得需要查询的数据
将要排序的列值和row ID组成键值对，按序存入中priority queue中
如果priority queue满了，直接淘汰最尾端记录。
重复上述步骤，直到所有的行数据都正常读取了完成
最后一轮循环，仅将row ID写入到结果文件中
根据结果文件中的row ID按序读取用户需要返回的数据。为了进一步优化性能，MysqL会读一批row ID，并将读到的数据按排序字段要求插入缓存区中(内存大小不够大
大的情况下，MysqL无法直接利用sort buffer作为priority queue，正常的文件外部排序还是一样的，只是在最后返回结果时，只根据N个row ID将数据返回出来。具体的算法我们就不列举了。
@H_280_50@mysqL到底是否选择priority queue是在sql/filesort.cc的函数中确定的，具体的代码细节这里就不展开了。
@H_727_660@mysqL的Limit m,n 其实是取m+n行数据，最后把M条数据丢掉。
足够大对Limit数据比较小的情况，优化效果是很明显的。
@H_470_49@mysqL其他相关排序参数


和。
@H_944_285@是为了让MysqL选择的模式。
是键值对的大小无法确定时（比如用户要查询的数据包含了 ）MysqL会对每个键值对分配个字节的内存，这样导致内存空间浪费，磁盘外部排序次数过多。
disable_sort_file_cache

@H_944_285@disable_sort_file_cache设置为ON的话，表示在排序中生成的临时文件不会用到文件系统的缓存，类似于打开文件。


sql排序没有什么关系。设置的是在创建InnoDB索引时，使用到的sort buffer的大小。
用户自定义设置这个参数了。
@H_470_49@mysqL排序优化总结
@H_280_50@mysqL排序的手段
排序和查询的字段尽量少。只查询你用到的字段，不要使用@R_674_10288@ct *；使用Limit查询必要的行数据；
要排序或者查询的字段，尽量不要用不确定字符函数，避免MysqL直接分配，导致sort buffer空间不足；
使用索引来优化或者避免排序；
增加大小，避免磁盘排序；
不得不使用original排序算法时，增加；
字段长度定义合适就好（避免过长）；
tmpdir建议独立存放，放在高速存储设备上。




大佬总结
       以上是大佬教程为你收集整理的MySQL排序内部原理探秘全部内容，希望文章能够帮你解决MySQL排序内部原理探秘所遇到的程序开发问题。
		如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。
		本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。


			标签：排序


	
		 
			
			   上一篇: 数据库索引
				下一篇:mybatis-config.xml文件标签介绍
		
	
    
    
       
	
    

	
		猜你在找的MySQL相关文章
		 
						MySql分级命令总结
						2022-05-15
			

						MySQL视图的使用
						2019-11-05
			

						MySQL删除视图
						2019-11-05
			

						MySQL更新视图
						2019-11-05
			

						MySQL修改视图
						2019-11-05
			

						MySQL查看视图
						2019-11-05
			

						MySQL在多表上创建视图
						2019-11-05
			

						MySQL在单表上创建视图
						2019-11-05
			

						Linux登录MySQL时出现 Can't connect to local MySQL server through socket '...
						2019-11-05
			

						MySql分级命令总结
						2019-11-05
			
	      
    
    
			其他相关热搜词更多
			phpJavaPython程序员load中如何string使用参数jquery开发安装listlinuxiosandroid工具javascriptcap

 
	   
		
		
			
			
			
			
		

		
			最新MySQL教程
			
							MySql分级命令总结
							
				

							Linux登录MySQL时出现 Can't connect to local MySQL server through socket '...
							
				

							MySQL在单表上创建视图
							
				

							MySQL查看视图
							
				

							MySQL修改视图
							
				

							MySQL更新视图
							
				

							MySQL视图的使用
							
				

							MySQL数据库备份
							
				

							MySQL数据库还原
							
				

							MySQL创建普通用户
							
				
		
		
		
			热门MySQL教程
			 
							MySQL收回权限
							
				 

							MySQL查询权限
							
				 

							MySQL授予权限
							
				 

							MySQL的权限
							
				 

							MySQL解决root用户密码丢失问题
							
				 

							MySQL普通用户修改密码
							
				 

							MySQL的root用户修改普通用户密码
							
				 

							MySQL修改root用户密码
							
				 

							MySQL数据库还原
							
				 

							MySQL数据库备份