-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathSQL优化(1)_filesort.html
165 lines (153 loc) · 7.1 KB
/
SQL优化(1)_filesort.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
<!DOCTYPE html>
<html lang="" class="loading">
<head><meta name="generator" content="Hexo 3.8.0">
<!-- hexo-inject:begin --><!-- hexo-inject:end --><meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport" content="width=device-width, minimum-scale=1.0, maximum-scale=1.0, user-scalable=no">
<title>有朋自远方来不亦乐乎</title>
<meta name="apple-mobile-web-app-capable" content="yes">
<meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
<meta name="google" content="notranslate">
<meta name="keywords" content="huhu,">
<meta name="author" content="Mr.shi">
<link rel="alternative" href="atom.xml" title="有朋自远方来不亦乐乎" type="application/atom+xml">
<link rel="icon" href="/img/favicon.png">
<link rel="stylesheet" href="//cdn.jsdelivr.net/npm/gitalk@1/dist/gitalk.css">
<link rel="stylesheet" href="/css/diaspora.css">
<link rel="stylesheet" href="/css/prism-atom-dark.css" type="text/css"><!-- hexo-inject:begin --><!-- hexo-inject:end --></head>
</html>
<body class="loading">
<!-- hexo-inject:begin --><!-- hexo-inject:end --><div id="loader"></div>
<div id="single" class="page">
<div id="top">
<a class="icon-left image-icon" href="javascript:history.back()"></a>
</div>
<div class="section">
<div class="article">
<div class="main">
<div class="content">
<h3 id="what-is-filesort"><a href="#what-is-filesort" class="headerlink" title="what is filesort"></a>what is filesort</h3><p>文件排序？nonono，filesort是一种算法，filesort()对适合其内存的数据块进行快速排序，然后使用mergesort方法合并数据块。filesort()的可用内存量由@@sort_buffer_size变量控制。</p>
<div class="table-container">
<table>
<thead>
<tr>
<th>mysql> show variables like ‘%sort_buffer_size%’;<br>+————————————-+—————-+<br></th>
<th>Variable_name</th>
<th>Value</th>
<th><br>+————————————-+—————-+<br></th>
<th>innodb_sort_buffer_size</th>
<th>1048576</th>
<th><br></th>
<th>myisam_sort_buffer_size</th>
<th>106954752</th>
<th><br></th>
<th>sort_buffer_size</th>
<th>262144</th>
<th><br>+————————————-+—————-+<br></th>
<th></th>
</tr>
</thead>
<tbody>
<tr>
<td></td>
</tr>
</tbody>
</table>
</div>
<p>sort_buffer_size的大小只有256k，当需要sort的数据超过256k，则会分为很多块，这时候filesort就会使用临时文件来存储块。</p>
<p>另外filesort()的源数据始终来自一个表。如果需要对来自多个表的数据进行排序，MySQL将首先将数据收集到临时表中，然后为该临时表调用filesort()。</p>
<h3 id="when-use-filesort"><a href="#when-use-filesort" class="headerlink" title="when use filesort"></a>when use filesort</h3><blockquote>
<p>既然需要sort，想必我们一定用到了一个关键字，order by，那我们先想想mysql是怎么输出有序序列的？</p>
</blockquote>
</div>
</div>
</div>
</div>
</div>
<script type="text/x-mathjax-config">
MathJax.Hub.Config({
tex2jax: {
inlineMath: [ ["$","$"], ["\\(","\\)"] ],
skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code'],
processEscapes: true
}
});
MathJax.Hub.Queue(function() {
var all = MathJax.Hub.getAllJax();
for (var i = 0; i < all.length; ++i)
all[i].SourceElement().parentNode.className += ' has-jax';
});
</script>
<script src="http://cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML"></script><!-- hexo-inject:begin --><!-- Begin: Injected MathJax -->
<script type="text/x-mathjax-config">
MathJax.Hub.Config("");
</script>
<script type="text/x-mathjax-config">
MathJax.Hub.Queue(function() {
var all = MathJax.Hub.getAllJax(), i;
for(i=0; i < all.length; i += 1) {
all[i].SourceElement().parentNode.className += ' has-jax';
}
});
</script>
<script type="text/javascript" src="custom_mathjax_source">
</script>
<!-- End: Injected MathJax -->
<!-- hexo-inject:end -->
</body>
<script src="//cdn.jsdelivr.net/npm/gitalk@1/dist/gitalk.min.js"></script>
<script src="//lib.baomitu.com/jquery/1.8.3/jquery.min.js"></script>
<script src="/js/plugin.js"></script>
<script src="/js/diaspora.js"></script>
<link rel="stylesheet" href="/photoswipe/photoswipe.css">
<link rel="stylesheet" href="/photoswipe/default-skin/default-skin.css">
<script src="/photoswipe/photoswipe.min.js"></script>
<script src="/photoswipe/photoswipe-ui-default.min.js"></script>
<!-- Root element of PhotoSwipe. Must have class pswp. -->
<div class="pswp" tabindex="-1" role="dialog" aria-hidden="true">
<!-- Background of PhotoSwipe.
It's a separate element as animating opacity is faster than rgba(). -->
<div class="pswp__bg"></div>
<!-- Slides wrapper with overflow:hidden. -->
<div class="pswp__scroll-wrap">
<!-- Container that holds slides.
PhotoSwipe keeps only 3 of them in the DOM to save memory.
Don't modify these 3 pswp__item elements, data is added later on. -->
<div class="pswp__container">
<div class="pswp__item"></div>
<div class="pswp__item"></div>
<div class="pswp__item"></div>
</div>
<!-- Default (PhotoSwipeUI_Default) interface on top of sliding area. Can be changed. -->
<div class="pswp__ui pswp__ui--hidden">
<div class="pswp__top-bar">
<!-- Controls are self-explanatory. Order can be changed. -->
<div class="pswp__counter"></div>
<button class="pswp__button pswp__button--close" title="Close (Esc)"></button>
<button class="pswp__button pswp__button--share" title="Share"></button>
<button class="pswp__button pswp__button--fs" title="Toggle fullscreen"></button>
<button class="pswp__button pswp__button--zoom" title="Zoom in/out"></button>
<!-- Preloader demo http://codepen.io/dimsemenov/pen/yyBWoR -->
<!-- element will get class pswp__preloader--active when preloader is running -->
<div class="pswp__preloader">
<div class="pswp__preloader__icn">
<div class="pswp__preloader__cut">
<div class="pswp__preloader__donut"></div>
</div>
</div>
</div>
</div>
<div class="pswp__share-modal pswp__share-modal--hidden pswp__single-tap">
<div class="pswp__share-tooltip"></div>
</div>
<button class="pswp__button pswp__button--arrow--left" title="Previous (arrow left)">
</button>
<button class="pswp__button pswp__button--arrow--right" title="Next (arrow right)">
</button>
<div class="pswp__caption">
<div class="pswp__caption__center"></div>
</div>
</div>
</div>
</div>
</html>