mysql45
/
16讲“orderby”是怎么工作的.html

<html>
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta name="viewport"
          content="width=device-width,initial-scale=1,maximum-scale=1,minimum-scale=1,user-scalable=no,viewport-fit=cover">
    <meta name="format-detection" content="telephone=no">
    <style type="text/css">

#watermark {

  position: relative;
  overflow: hidden;
}

#watermark .x {
  position: absolute;
  top: 800;
  left: 400;
  color: #3300ff;
  font-size: 50px;
  pointer-events: none;
  opacity:0.3;
  filter:Alpha(opacity=50);


}
    </style>


    <style type="text/css">
 html{color:#333;-webkit-text-size-adjust:100%;-ms-text-size-adjust:100%;text-rendering:optimizelegibility;font-family:Helvetica Neue,PingFang SC,Verdana,Microsoft Yahei,Hiragino Sans GB,Microsoft Sans Serif,WenQuanYi Micro Hei,sans-serif}html.borderbox *,html.borderbox :after,html.borderbox :before{box-sizing:border-box}article,aside,blockquote,body,button,code,dd,details,dl,dt,fieldset,figcaption,figure,footer,form,h1,h2,h3,h4,h5,h6,header,hr,input,legend,li,menu,nav,ol,p,pre,section,td,textarea,th,ul{margin:0;padding:0}article,aside,details,figcaption,figure,footer,header,menu,nav,section{display:block}audio,canvas,video{display:inline-block}body,button,input,select,textarea{font:300 1em/1.8 PingFang SC,Lantinghei SC,Microsoft Yahei,Hiragino Sans GB,Microsoft Sans Serif,WenQuanYi Micro Hei,Helvetica,sans-serif}button::-moz-focus-inner,input::-moz-focus-inner{padding:0;border:0}table{border-collapse:collapse;border-spacing:0}fieldset,img{border:0}blockquote{position:relative;color:#999;font-weight:400;border-left:1px solid #1abc9c;padding-left:1em;margin:1em 3em 1em 2em}@media only screen and (max-width:640px){blockquote{margin:1em 0}}abbr,acronym{border-bottom:1px dotted;font-variant:normal}abbr{cursor:help}del{text-decoration:line-through}address,caption,cite,code,dfn,em,th,var{font-style:normal;font-weight:400}ol,ul{list-style:none}caption,th{text-align:left}q:after,q:before{content:""}sub,sup{font-size:75%;line-height:0;position:relative}:root sub,:root sup{vertical-align:baseline}sup{top:-.5em}sub{bottom:-.25em}a{color:#1abc9c}a:hover{text-decoration:underline}.typo a{border-bottom:1px solid #1abc9c}.typo a:hover{border-bottom-color:#555;color:#555}.typo a:hover,a,ins{text-decoration:none}.typo-u,u{text-decoration:underline}mark{background:#fffdd1;border-bottom:1px solid #ffedce;padding:2px;margin:0 5px}code,pre,pre tt{font-family:Courier,Courier New,monospace}pre{background:hsla(0,0%,97%,.7);border:1px solid #ddd;padding:1em 1.5em;display:block;-webkit-overflow-scrolling:touch}hr{border:none;border-bottom:1px solid #cfcfcf;margin-bottom:.8em;height:10px}.typo-small,figcaption,small{font-size:.9em;color:#888}b,strong{font-weight:700;color:#000}[draggable]{cursor:move}.clearfix:after,.clearfix:before{content:"";display:table}.clearfix:after{clear:both}.clearfix{zoom:1}.textwrap,.textwrap td,.textwrap th{word-wrap:break-word;word-break:break-all}.textwrap-table{table-layout:fixed}.serif{font-family:Palatino,Optima,Georgia,serif}.typo-dl,.typo-form,.typo-hr,.typo-ol,.typo-p,.typo-pre,.typo-table,.typo-ul,.typo dl,.typo form,.typo hr,.typo ol,.typo p,.typo pre,.typo table,.typo ul,blockquote{margin-bottom:1rem}h1,h2,h3,h4,h5,h6{font-family:PingFang SC,Helvetica Neue,Verdana,Microsoft Yahei,Hiragino Sans GB,Microsoft Sans Serif,WenQuanYi Micro Hei,sans-serif;color:#000;line-height:1.35}.typo-h1,.typo-h2,.typo-h3,.typo-h4,.typo-h5,.typo-h6,.typo h1,.typo h2,.typo h3,.typo h4,.typo h5,.typo h6{margin-top:1.2em;margin-bottom:.6em;line-height:1.35}.typo-h1,.typo h1{font-size:2em}.typo-h2,.typo h2{font-size:1.8em}.typo-h3,.typo h3{font-size:1.6em}.typo-h4,.typo h4{font-size:1.4em}.typo-h5,.typo-h6,.typo h5,.typo h6{font-size:1.2em}.typo-ul,.typo ul{margin-left:1.3em;list-style:disc}.typo-ol,.typo ol{list-style:decimal;margin-left:1.9em}.typo-ol ol,.typo-ol ul,.typo-ul ol,.typo-ul ul,.typo li ol,.typo li ul{margin-bottom:.8em;margin-left:2em}.typo-ol ul,.typo-ul ul,.typo li ul{list-style:circle}.typo-table td,.typo-table th,.typo table caption,.typo table td,.typo table th{border:1px solid #ddd;padding:.5em 1em;color:#666}.typo-table th,.typo table th{background:#fbfbfb}.typo-table thead th,.typo table thead th{background:hsla(0,0%,95%,.7)}.typo table caption{border-bottom:none}.typo-input,.typo-textarea{-webkit-appearance:none;border-radius:0}.typo-em,.typo em,caption,legend{color:#000;font-weight:inherit}.typo-em{position:relative}.typo-em:after{position:absolute;top:.65em;left:0;width:100%;overflow:hidden;white-space:nowrap;content:"\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB\30FB"}.typo img{max-width:100%}.common-content{font-weight:400;color:#353535;line-height:1.75rem;white-space:normal;word-break:normal;font-size:1rem}.common-content img{display:block;max-width:100%;background-color:#eee}.common-content audio,.common-content video{width:100%;background-color:#eee}.common-content center,.common-content font{margin-top:1rem;display:inline-block}.common-content center{width:100%}.common-content pre{margin-top:1rem;padding-left:0;padding-right:0;position:relative;overflow:hidden}.common-content pre code{font-size:.8rem;font-family:Consolas,Liberation Mono,Menlo,monospace,Courier;display:block;width:100%;box-sizing:border-box;padding-left:1rem;padding-right:1rem;overflow-x:auto}.common-content hr{border:none;margin-top:1.5rem;margin-bottom:1.5rem;border-top:1px solid #f5f5f5;height:1px;background:none}.common-content b,.common-content h1,.common-content h2,.common-content h3,.common-content h4,.common-content h5,.common-content strong{font-weight:700}.common-content h1,.common-content h2{font-size:1.125rem;margin-bottom:.45rem}.common-content h3,.common-content h4,.common-content h5{font-size:1rem;margin-bottom:.45rem}.common-content p{font-weight:400;color:#353535;margin-top:.15rem}.common-content .orange{color:#ff5a05}.common-content .reference{font-size:1rem;color:#888}.custom-rich-content h1{margin-top:0;font-weight:400;font-size:15.25px;border-bottom:1px solid #eee;line-height:2.8}.custom-rich-content li,.custom-rich-content p{font-size:14px;color:#888;line-height:1.6}table.hljs-ln{margin-bottom:0;border-spacing:0;border-collapse:collapse}table.hljs-ln,table.hljs-ln tbody,table.hljs-ln td,table.hljs-ln tr{box-sizing:border-box}table.hljs-ln td{padding:0;border:0}table.hljs-ln td.hljs-ln-numbers{min-width:15px;color:rgba(27,31,35,.3);text-align:right;white-space:nowrap;cursor:pointer;user-select:none}table.hljs-ln td.hljs-ln-code,table.hljs-ln td.hljs-ln-numbers{font-family:SFMono-Regular,Consolas,Liberation Mono,Menlo,Courier,monospace;font-size:12px;line-height:20px;vertical-align:top}table.hljs-ln td.hljs-ln-code{position:relative;padding-right:10px;padding-left:10px;overflow:visible;color:#24292e;word-wrap:normal;white-space:pre}video::-webkit-media-controls{overflow:hidden!important}video::-webkit-media-controls-enclosure{width:calc(100% + 32px);margin-left:auto}.button-cancel{color:#888;border:1px solid #888;border-radius:3px;margin-right:12px}.button-cancel,.button-primary{-ms-flex-positive:1;flex-grow:1;height:35px;display:inline-block;font-size:15px;text-align:center;line-height:36px}.button-primary{color:#fff;background-color:#ff5a05;border-radius:3px}@font-face{font-family:iconfont;src:url(//at.alicdn.com/t/font_372689_bwwwtosxtzp.eot);src:url(//at.alicdn.com/t/font_372689_bwwwtosxtzp.eot#iefix) format("embedded-opentype"),url(//at.alicdn.com/t/font_372689_bwwwtosxtzp.woff) format("woff"),url(//at.alicdn.com/t/font_372689_bwwwtosxtzp.ttf) format("truetype"),url(//at.alicdn.com/t/font_372689_bwwwtosxtzp.svg#iconfont) format("svg")}@font-face{font-family:player-font;src:url(//at.alicdn.com/t/font_509397_1cyjv4o90qiod2t9.eot);src:url(//at.alicdn.com/t/font_509397_1cyjv4o90qiod2t9.eot#iefix) format("embedded-opentype"),url(//at.alicdn.com/t/font_509397_1cyjv4o90qiod2t9.woff) format("woff"),url(//at.alicdn.com/t/font_509397_1cyjv4o90qiod2t9.ttf) format("truetype"),url(//at.alicdn.com/t/font_509397_1cyjv4o90qiod2t9.svg#player-font) format("svg")}.iconfont{font-family:iconfont!important;font-size:16px;font-style:normal;-webkit-font-smoothing:antialiased;-webkit-text-stroke-width:.2px;-moz-osx-font-smoothing:grayscale}html{background:#fff;min-height:100%;-webkit-tap-highlight-color:rgba(0,0,0,0)}body{width:100%}body.fixed{overflow:hidden;position:fixed;width:100vw;height:100vh}i{font-style:normal}a{word-wrap:break-word;-webkit-tap-highlight-color:rgba(0,0,0,0)}a:hover{text-decoration:none}.fade-enter-active,.fade-leave-active{transition:opacity .3s}.fade-enter,.fade-leave-to{opacity:0}.MathJax,.MathJax_CHTML,.MathJax_MathContainer,.MathJax_MathML,.MathJax_PHTML,.MathJax_PlainSource,.MathJax_SVG{outline:0}.ios-app-switch .js-audit{display:none}._loading_wrap_{position:fixed;width:100vw;height:100vh;top:50%;left:50%;transform:translate(-50%,-50%);z-index:999}._loading_div_class_,._loading_wrap_{display:-ms-flexbox;display:flex;-ms-flex-pack:center;justify-content:center;-ms-flex-align:center;align-items:center}._loading_div_class_{word-wrap:break-word;padding:.5rem .75rem;text-align:center;z-index:9999;font-size:.6rem;max-width:60%;color:#fff;border-radius:.25rem;-ms-flex-direction:column;flex-direction:column}._loading_div_class_ .message{color:#353535;font-size:16px;line-height:3}.spinner{animation:circle-rotator 1.4s linear infinite}.spinner *{line-height:0;box-sizing:border-box}@keyframes circle-rotator{0%{transform:rotate(0deg)}to{transform:rotate(270deg)}}.path{stroke-dasharray:187;stroke-dashoffset:0;transform-origin:center;animation:circle-dash 1.4s ease-in-out infinite,circle-colors 5.6s ease-in-out infinite}@keyframes circle-colors{0%{stroke:#ff5a05}to{stroke:#ff5a05}}@keyframes circle-dash{0%{stroke-dashoffset:187}50%{stroke-dashoffset:46.75;transform:rotate(135deg)}to{stroke-dashoffset:187;transform:rotate(450deg)}}.confirm-box-wrapper,.confirm-box-wrapper .mask{position:absolute;top:0;left:0;right:0;bottom:0}.confirm-box-wrapper .mask{background:rgba(0,0,0,.6)}.confirm-box-wrapper .confirm-box{position:fixed;top:50%;left:50%;width:267px;background:#fff;transform:translate(-50%,-50%);border-radius:7px}.confirm-box-wrapper .confirm-box .head{margin:0 18px;font-size:18px;text-align:center;line-height:65px;border-bottom:1px solid #d9d9d9}.confirm-box-wrapper .confirm-box .body{padding:18px;padding-bottom:0;color:#353535;font-size:12.5px;max-height:150px;overflow:auto}.confirm-box-wrapper .confirm-box .foot{display:-ms-flexbox;display:flex;-ms-flex-direction:row;flex-direction:row;padding:18px}.confirm-box-wrapper .confirm-box .foot .button-cancel{border:1px solid #d9d9d9}.hljs{display:block;overflow-x:auto;padding:.5em;color:#333;background:#f8f8f8}.hljs-comment,.hljs-quote{color:#998;font-style:italic}.hljs-keyword,.hljs-selector-tag,.hljs-subst{color:#333;font-weight:700}.hljs-literal,.hljs-number,.hljs-tag .hljs-attr,.hljs-template-variable,.hljs-variable{color:teal}.hljs-doctag,.hljs-string{color:#d14}.hljs-section,.hljs-selector-id,.hljs-title{color:#900;font-weight:700}.hljs-subst{font-weight:400}.hljs-class .hljs-title,.hljs-type{color:#458;font-weight:700}.hljs-attribute,.hljs-name,.hljs-tag{color:navy;font-weight:400}.hljs-link,.hljs-regexp{color:#009926}.hljs-bullet,.hljs-symbol{color:#990073}.hljs-built_in,.hljs-builtin-name{color:#0086b3}.hljs-meta{color:#999;font-weight:700}.hljs-deletion{background:#fdd}.hljs-addition{background:#dfd}.hljs-emphasis{font-style:italic}.hljs-strong{font-weight:700}


    </style>
    <style type="text/css">
        .button-cancel[data-v-87ffcada]{color:#888;border:1px solid #888;border-radius:3px;margin-right:12px}.button-cancel[data-v-87ffcada],.button-primary[data-v-87ffcada]{-webkit-box-flex:1;-ms-flex-positive:1;flex-grow:1;height:35px;display:inline-block;font-size:15px;text-align:center;line-height:36px}.button-primary[data-v-87ffcada]{color:#fff;background-color:#ff5a05;border-radius:3px}.pd[data-v-87ffcada]{padding-left:1.375rem;padding-right:1.375rem}.article[data-v-87ffcada]{max-width:70rem;margin:0 auto}.article .article-unavailable[data-v-87ffcada]{color:#fa8919;font-size:15px;font-weight:600;line-height:24px;border-radius:5px;padding:12px;background-color:#f6f7fb;margin-top:20px}.article .article-unavailable .iconfont[data-v-87ffcada]{font-size:12px}.article .main[data-v-87ffcada]{padding:1.25rem 0;margin-bottom:52px}.article-title[data-v-87ffcada]{color:#353535;font-weight:400;line-height:1.65rem;font-size:1.34375rem}.article-info[data-v-87ffcada]{color:#888;font-size:.9375rem;margin-top:1.0625rem}.article-content[data-v-87ffcada]{margin-top:1.0625rem}.article-content.android video[data-v-87ffcada]::-webkit-media-controls-fullscreen-button{display:none}.copyright[data-v-87ffcada]{color:#b2b2b2;padding-bottom:20px;margin-top:20px;font-size:13px}.audio-player[data-v-87ffcada]{width:100%;margin:20px 0}.to-comment[data-v-87ffcada]{overflow:hidden;padding-top:10px;margin-bottom:-30px}.to-comment a.button-primary[data-v-87ffcada]{float:right;height:20px;font-size:12px;line-height:20px;padding:4px 8px;cursor:pointer}.article-comments[data-v-87ffcada]{margin-top:2rem}.article-comments h2[data-v-87ffcada]{text-align:center;color:#888;position:relative;z-index:1;margin-bottom:1rem}.article-comments h2[data-v-87ffcada]:before{border-top:1px dotted #888;content:"";position:absolute;top:56%;left:0;width:100%;z-index:-1}.article-comments h2 span[data-v-87ffcada]{font-size:15.25px;font-weight:400;padding:0 1rem;background:#fff;display:inline-block}.article-sub-bottom[data-v-87ffcada]{z-index:10;cursor:pointer}.switch-btns[data-v-87ffcada]{height:76px;cursor:pointer;padding-top:24px;padding-bottom:24px;border-bottom:10px solid #f6f7fb;position:relative}.switch-btns[data-v-87ffcada]:before{content:" ";height:1px;background:#e8e8e8;position:absolute;top:0;left:0;-webkit-box-sizing:border-box;box-sizing:border-box;left:1.375rem;right:1.375rem}.switch-btns .btn[data-v-87ffcada]{height:38px;display:-webkit-box;display:-ms-flexbox;display:flex;-webkit-box-align:center;-ms-flex-align:center;align-items:center}.switch-btns .btn .tag[data-v-87ffcada]{-webkit-box-flex:0;-ms-flex:0 0 62px;flex:0 0 62px;text-align:center;color:#888;font-size:14px;border-radius:10px;height:22px;line-height:22px;background:#f6f7fb;font-weight:400}.switch-btns .btn .txt[data-v-87ffcada]{margin-left:10px;-webkit-box-flex:1;-ms-flex:1 1 auto;flex:1 1 auto;color:#888;font-size:15px;height:22px;line-height:22px;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;font-weight:400}@media (max-width:769px){.article .breadcrumb[data-v-87ffcada]{padding-top:10px;padding-bottom:10px}}


    </style>

    <style type="text/css">
        .comment-item{list-style-position:inside;width:100%;display:-webkit-box;display:-ms-flexbox;display:flex;-webkit-box-orient:horizontal;-webkit-box-direction:normal;-ms-flex-direction:row;flex-direction:row;margin-bottom:1rem}.comment-item a{border-bottom:none}.comment-item .avatar{width:2.625rem;height:2.625rem;-ms-flex-negative:0;flex-shrink:0;border-radius:50%}.comment-item .info{margin-left:.5rem;-webkit-box-flex:1;-ms-flex-positive:1;flex-grow:1}.comment-item .info .hd{width:100%;display:-webkit-box;display:-ms-flexbox;display:flex;-webkit-box-orient:horizontal;-webkit-box-direction:normal;-ms-flex-direction:row;flex-direction:row;-webkit-box-pack:justify;-ms-flex-pack:justify;justify-content:space-between;-webkit-box-align:center;-ms-flex-align:center;align-items:center}.comment-item .info .hd .username{color:#888;font-size:15.25px;font-weight:400;line-height:1.2}.comment-item .info .hd .control{display:-webkit-box;display:-ms-flexbox;display:flex;-webkit-box-orient:horizontal;-webkit-box-direction:normal;-ms-flex-direction:row;flex-direction:row;-webkit-box-align:center;-ms-flex-align:center;align-items:center}.comment-item .info .hd .control .btn-share{color:#888;font-size:.75rem;margin-right:1rem}.comment-item .info .hd .control .btn-praise{display:-webkit-box;display:-ms-flexbox;display:flex;-webkit-box-orient:horizontal;-webkit-box-direction:normal;-ms-flex-direction:row;flex-direction:row;-webkit-box-align:center;-ms-flex-align:center;align-items:center;font-size:15.25px;text-decoration:none}.comment-item .info .hd .control .btn-praise i{color:#888;display:inline-block;font-size:.75rem;margin-right:.3rem;margin-top:-.01rem}.comment-item .info .hd .control .btn-praise i.on,.comment-item .info .hd .control .btn-praise span{color:#ff5a05}.comment-item .info .bd{color:#353535;font-size:15.25px;font-weight:400;white-space:normal;word-break:break-all;line-height:1.6}.comment-item .info .time{color:#888;font-size:9px;line-height:1}.comment-item .info .reply .reply-hd{font-size:15.25px}.comment-item .info .reply .reply-hd span{margin-left:-12px;color:#888;font-weight:400}.comment-item .info .reply .reply-hd i{color:#ff5a05;font-size:15.25px}.comment-item .info .reply .reply-content{color:#353535;font-size:15.25px;font-weight:400;white-space:normal;word-break:break-all}.comment-item .info .reply .reply-time{color:#888;font-size:9px}


    </style>
</head>
<body>
<div id="app">


    <div data-v-87ffcada="" class="article" id="watermark">
        <div data-v-87ffcada="" class="main main-app">
            <h1 data-v-87ffcada="" class="article-title pd">
                16讲“orderby”是怎么工作的
            </h1>
            <div data-v-87ffcada="" class="article-content typo common-content pd"><img data-v-87ffcada=""
                                                                                        src="https://static001.geekbang.org/resource/image/71/37/716e55f77ce1ac695121595a1eb4d937.jpg">

                <div>
                    <audio controls="controls" height="100" width="100">
                        <source src="16讲“orderby”是怎么工作的.mp3" type="audio/mp3" />
                        <embed height="100" width="100" src="16讲“orderby”是怎么工作的.mp3" />
                    </audio>
                </div>
                <div data-v-87ffcada="" id="article-content" class="">
                    <div class="text">
                        <p>在你开发应用的时候，一定会经常碰到需要根据指定的字段排序来显示结果的需求。还是以我们前面举例用过的市民表为例，假设你要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前1000个人的姓名、年龄。</p><p>假设这个表的部分定义是这样的：</p><pre><code>CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;
</code></pre><p>这时，你的SQL语句可以这么写：</p><pre><code>select city,name,age from t where city='杭州' order by name limit 1000  ;
</code></pre><p>这个语句看上去逻辑很清晰，但是你了解它的执行流程吗？今天，我就和你聊聊这个语句是怎么执行的，以及有什么参数会影响执行的行为。</p><h1>全字段排序</h1><p>前面我们介绍过索引，所以你现在就很清楚了，为避免全表扫描，我们需要在city字段加上索引。</p><p>在city字段上创建索引之后，我们用explain命令来看看这个语句的执行情况。</p><p><img src="https://static001.geekbang.org/resource/image/82/03/826579b63225def812330ef6c344a303.png" alt=""></p><center><span class="reference">图1 使用explain命令查看语句的执行情况</span></center><p>Extra这个字段中的“Using filesort”表示的就是需要排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。</p><p>为了说明这个SQL查询语句的执行过程，我们先来看一下city这个索引的示意图。</p><p><img src="https://static001.geekbang.org/resource/image/53/3e/5334cca9118be14bde95ec94b02f0a3e.png" alt=""></p><center><span class="reference">图2 city字段的索引示意图</span></center><p>从图中可以看到，满足city='杭州’条件的行，是从ID_X到ID_(X+N)的这些记录。</p><p>通常情况下，这个语句执行流程如下所示 ：</p><!-- [[[read_end]]] --><ol>
<li>
<p>初始化sort_buffer，确定放入name、city、age这三个字段；</p>
</li>
<li>
<p>从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；</p>
</li>
<li>
<p>到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；</p>
</li>
<li>
<p>从索引city取下一个记录的主键id；</p>
</li>
<li>
<p>重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_Y；</p>
</li>
<li>
<p>对sort_buffer中的数据按照字段name做快速排序；</p>
</li>
<li>
<p>按照排序结果取前1000行返回给客户端。</p>
</li>
</ol><p>我们暂且把这个排序过程，称为全字段排序，执行流程的示意图如下所示，下一篇文章中我们还会用到这个排序。</p><p><img src="https://static001.geekbang.org/resource/image/6c/72/6c821828cddf46670f9d56e126e3e772.jpg" alt=""></p><center><span class="reference">图3 全字段排序</span></center><p>图中“按name排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。</p><p>sort_buffer_size，就是MySQL为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。</p><p>你可以用下面介绍的方法，来确定一个排序语句是否使用了临时文件。</p><pre><code>/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on';

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;
</code></pre><p>这个方法是通过查看 OPTIMIZER_TRACE 的结果来确认的，你可以从 number_of_tmp_files中看到是否使用了临时文件。</p><p><img src="https://static001.geekbang.org/resource/image/89/95/89baf99cdeefe90a22370e1d6f5e6495.png" alt=""></p><center><span class="reference">图4 全排序的OPTIMIZER_TRACE部分结果</span></center><p>number_of_tmp_files表示的是，排序过程中使用的临时文件数。你一定奇怪，为什么需要12个文件？内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。可以这么简单理解，<strong>MySQL将需要排序的数据分成12份，每一份单独排序后存在这些临时文件中。然后把这12个有序文件再合并成一个有序的大文件。</strong></p><p>如果sort_buffer_size超过了需要排序的数据量的大小，number_of_tmp_files就是0，表示排序可以直接在内存中完成。</p><p>否则就需要放在临时文件中排序。sort_buffer_size越小，需要分成的份数越多，number_of_tmp_files的值就越大。</p><p>接下来，我再和你解释一下图4中其他两个值的意思。</p><p>我们的示例表中有4000条满足city='杭州’的记录，所以你可以看到 examined_rows=4000，表示参与排序的行数是4000行。</p><p>sort_mode 里面的packed_additional_fields的意思是，排序过程对字符串做了“紧凑”处理。即使name字段的定义是varchar(16)，在排序过程中还是要按照实际长度来分配空间的。</p><p>同时，最后一个查询语句select @b-@a 的返回结果是4000，表示整个执行过程只扫描了4000行。</p><p>这里需要注意的是，为了避免对结论造成干扰，我把internal_tmp_disk_storage_engine设置成MyISAM。否则，select @b-@a的结果会显示为4001。</p><p>这是因为查询OPTIMIZER_TRACE这个表时，需要用到临时表，而internal_tmp_disk_storage_engine的默认值是InnoDB。如果使用的是InnoDB引擎的话，把数据从临时表取出来的时候，会让Innodb_rows_read的值加1。</p><h1>rowid排序</h1><p>在上面这个算法过程里面，只对原表的数据读了一遍，剩下的操作都是在sort_buffer和临时文件中执行的。但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么sort_buffer里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。</p><p>所以如果单行很大，这个方法效率不够好。</p><p>那么，<strong>如果MySQL认为排序的单行长度太大会怎么做呢？</strong></p><p>接下来，我来修改一个参数，让MySQL采用另外一种算法。</p><pre><code>SET max_length_for_sort_data = 16;
</code></pre><p>max_length_for_sort_data，是MySQL中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法。</p><p>city、name、age 这三个字段的定义总长度是36，我把max_length_for_sort_data设置为16，我们再来看看计算过程有什么改变。</p><p>新的算法放入sort_buffer的字段，只有要排序的列（即name字段）和主键id。</p><p>但这时，排序的结果就因为少了city和age字段的值，不能直接返回了，整个执行流程就变成如下所示的样子：</p><ol>
<li>
<p>初始化sort_buffer，确定放入两个字段，即name和id；</p>
</li>
<li>
<p>从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；</p>
</li>
<li>
<p>到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中；</p>
</li>
<li>
<p>从索引city取下一个记录的主键id；</p>
</li>
<li>
<p>重复步骤3、4直到不满足city='杭州’条件为止，也就是图中的ID_Y；</p>
</li>
<li>
<p>对sort_buffer中的数据按照字段name进行排序；</p>
</li>
<li>
<p>遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回给客户端。</p>
</li>
</ol><p>这个执行流程的示意图如下，我把它称为rowid排序。</p><p><img src="https://static001.geekbang.org/resource/image/dc/6d/dc92b67721171206a302eb679c83e86d.jpg" alt=""></p><center><span class="reference">图5 rowid排序</span></center><p>对比图3的全字段排序流程图你会发现，rowid排序多访问了一次表t的主键索引，就是步骤7。</p><p>需要说明的是，最后的“结果集”是一个逻辑概念，实际上MySQL服务端从排序后的sort_buffer中依次取出id，然后到原表查到city、name和age这三个字段的结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的。</p><p>根据这个说明过程和图示，你可以想一下，这个时候执行select @b-@a，结果会是多少呢？</p><p>现在，我们就来看看结果有什么不同。</p><p>首先，图中的examined_rows的值还是4000，表示用于排序的数据是4000行。但是select @b-@a这个语句的值变成5000了。</p><p>因为这时候除了排序过程外，在排序完成后，还要根据id去原表取值。由于语句是limit 1000，因此会多读1000行。</p><p><img src="https://static001.geekbang.org/resource/image/27/9b/27f164804d1a4689718291be5d10f89b.png" alt=""></p><center><span class="reference">图6 rowid排序的OPTIMIZER_TRACE部分输出</span></center><p>从OPTIMIZER_TRACE的结果中，你还能看到另外两个信息也变了。</p><ul>
<li>sort_mode变成了&lt;sort_key, rowid&gt;，表示参与排序的只有name和id这两个字段。</li>
<li>number_of_tmp_files变成10了，是因为这时候参与排序的行数虽然仍然是4000行，但是每一行都变小了，因此需要排序的总数据量就变小了，需要的临时文件也相应地变少了。</li>
</ul><h1>全字段排序 VS rowid排序</h1><p>我们来分析一下，从这两个执行流程里，还能得出什么结论。</p><p>如果MySQL实在是担心排序内存太小，会影响排序效率，才会采用rowid排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。</p><p>如果MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。</p><p>这也就体现了MySQL的一个设计思想：<strong>如果内存够，就要多利用内存，尽量减少磁盘访问。</strong></p><p>对于InnoDB表来说，rowid排序会要求回表多造成磁盘读，因此不会被优先选择。</p><p>这个结论看上去有点废话的感觉，但是你要记住它，下一篇文章我们就会用到。</p><p>看到这里，你就了解了，MySQL做排序是一个成本比较高的操作。那么你会问，是不是所有的order by都需要排序操作呢？如果不排序就能得到正确的结果，那对系统的消耗会小很多，语句的执行时间也会变得更短。</p><p>其实，并不是所有的order by语句，都需要排序操作的。从上面分析的执行过程，我们可以看到，MySQL之所以需要生成临时表，并且在临时表上做排序操作，<strong>其原因是原来的数据都是无序的。</strong></p><p>你可以设想下，如果能够保证从city这个索引上取出来的行，天然就是按照name递增排序的话，是不是就可以不用再排序了呢？</p><p>确实是这样的。</p><p>所以，我们可以在这个市民表上创建一个city和name的联合索引，对应的SQL语句是：</p><pre><code>alter table t add index city_user(city, name);
</code></pre><p>作为与city索引的对比，我们来看看这个索引的示意图。</p><p><img src="https://static001.geekbang.org/resource/image/f9/bf/f980201372b676893647fb17fac4e2bf.png" alt=""></p><center><span class="reference">图7 city和name联合索引示意图</span></center><p>在这个索引里面，我们依然可以用树搜索的方式定位到第一个满足city='杭州’的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要city的值是杭州，name的值就一定是有序的。</p><p>这样整个查询过程的流程就变成了：</p><ol>
<li>
<p>从索引(city,name)找到第一个满足city='杭州’条件的主键id；</p>
</li>
<li>
<p>到主键id索引取出整行，取name、city、age三个字段的值，作为结果集的一部分直接返回；</p>
</li>
<li>
<p>从索引(city,name)取下一个记录主键id；</p>
</li>
<li>
<p>重复步骤2、3，直到查到第1000条记录，或者是不满足city='杭州’条件时循环结束。</p>
</li>
</ol><p><img src="https://static001.geekbang.org/resource/image/3f/92/3f590c3a14f9236f2d8e1e2cb9686692.jpg" alt=""></p><center><span class="reference">图8 引入(city,name)联合索引后，查询语句的执行计划</span></center><p>可以看到，这个查询过程不需要临时表，也不需要排序。接下来，我们用explain的结果来印证一下。</p><p><img src="https://static001.geekbang.org/resource/image/fc/8a/fc53de303811ba3c46d344595743358a.png" alt=""></p><center><span class="reference">图9 引入(city,name)联合索引后，查询语句的执行计划</span></center><p>从图中可以看到，Extra字段中没有Using filesort了，也就是不需要排序了。而且由于(city,name)这个联合索引本身有序，所以这个查询也不用把4000行全都读一遍，只要找到满足条件的前1000条记录就可以退出了。也就是说，在我们这个例子里，只需要扫描1000次。</p><p>既然说到这里了，我们再往前讨论，<strong>这个语句的执行流程有没有可能进一步简化呢？</strong>不知道你还记不记得，我在第5篇文章<a href="https://time.geekbang.org/column/article/69636">《 深入浅出索引（下）》</a>中，和你介绍的覆盖索引。</p><p>这里我们可以再稍微复习一下。<strong>覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。</strong></p><p>按照覆盖索引的概念，我们可以再优化一下这个查询语句的执行流程。</p><p>针对这个查询，我们可以创建一个city、name和age的联合索引，对应的SQL语句就是：</p><pre><code>alter table t add index city_user_age(city, name, age);
</code></pre><p>这时，对于city字段的值相同的行来说，还是按照name字段的值递增排序的，此时的查询语句也就不再需要排序了。这样整个查询语句的执行流程就变成了：</p><ol>
<li>
<p>从索引(city,name,age)找到第一个满足city='杭州’条件的记录，取出其中的city、name和age这三个字段的值，作为结果集的一部分直接返回；</p>
</li>
<li>
<p>从索引(city,name,age)取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；</p>
</li>
<li>
<p>重复执行步骤2，直到查到第1000条记录，或者是不满足city='杭州’条件时循环结束。</p>
</li>
</ol><p><img src="https://static001.geekbang.org/resource/image/df/d6/df4b8e445a59c53df1f2e0f115f02cd6.jpg" alt=""></p><center><span class="reference">图10 引入(city,name,age)联合索引后，查询语句的执行流程</span></center><p>然后，我们再来看看explain的结果。</p><p><img src="https://static001.geekbang.org/resource/image/9e/23/9e40b7b8f0e3f81126a9171cc22e3423.png" alt=""></p><center><span class="reference">图11 引入(city,name,age)联合索引后，查询语句的执行计划</span></center><p>可以看到，Extra字段里面多了“Using index”，表示的就是使用了覆盖索引，性能上会快很多。</p><p>当然，这里并不是说要为了每个查询能用上覆盖索引，就要把语句中涉及的字段都建上联合索引，毕竟索引还是有维护代价的。这是一个需要权衡的决定。</p><h1>小结</h1><p>今天这篇文章，我和你介绍了MySQL里面order by语句的几种算法流程。</p><p>在开发系统的时候，你总是不可避免地会使用到order by语句。你心里要清楚每个语句的排序逻辑是怎么实现的，还要能够分析出在最坏情况下，每个语句的执行对系统资源的消耗，这样才能做到下笔如有神，不犯低级错误。</p><p>最后，我给你留下一个思考题吧。</p><p>假设你的表里面已经有了city_name(city, name)这个联合索引，然后你要查杭州和苏州两个城市中所有的市民的姓名，并且按名字排序，显示前100条记录。如果SQL查询语句是这么写的 ：</p><pre><code>mysql&gt; select * from t where city in ('杭州',&quot;苏州&quot;) order by name limit 100;
</code></pre><p>那么，这个语句执行的时候会有排序过程吗，为什么？</p><p>如果业务端代码由你来开发，需要实现一个在数据库端不需要排序的方案，你会怎么实现呢？</p><p>进一步地，如果有分页需求，要显示第101页，也就是说语句最后要改成 “limit 10000,100”， 你的实现方法又会是什么呢？</p><p>你可以把你的思考和观点写在留言区里，我会在下一篇文章的末尾和你讨论这个问题。感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。</p><h1>上期问题时间</h1><p>上期的问题是，当MySQL去更新一行，但是要修改的值跟原来的值是相同的，这时候MySQL会真的去执行一次修改吗？还是看到值相同就直接返回呢？</p><p>这是第一次我们课后问题的三个选项都有同学选的，所以我要和你需要详细说明一下。</p><p>第一个选项是，MySQL读出数据，发现值与原来相同，不更新，直接返回，执行结束。这里我们可以用一个锁实验来确认。</p><p>假设，当前表t里的值是(1,2)。</p><p><img src="https://static001.geekbang.org/resource/image/6d/90/6d9d8837560d01b57d252c470157ea90.png" alt=""></p><center><span class="reference">图12 锁验证方式</span></center><p>session B的update 语句被blocked了，加锁这个动作是InnoDB才能做的，所以排除选项1。</p><p>第二个选项是，MySQL调用了InnoDB引擎提供的接口，但是引擎发现值与原来相同，不更新，直接返回。有没有这种可能呢？这里我用一个可见性实验来确认。</p><p>假设当前表里的值是(1,2)。</p><p><img src="https://static001.geekbang.org/resource/image/44/96/441682b64a3f5dd50f35b12ca4b87c96.png" alt=""></p><center><span class="reference">图13 可见性验证方式</span></center><p>session A的第二个select 语句是一致性读（快照读)，它是不能看见session B的更新的。</p><p>现在它返回的是(1,3)，表示它看见了某个新的版本，这个版本只能是session A自己的update语句做更新的时候生成。（如果你对这个逻辑有疑惑的话，可以回顾下第8篇文章<a href="https://time.geekbang.org/column/article/70562">《事务到底是隔离的还是不隔离的？》</a>中的相关内容）</p><p>所以，我们上期思考题的答案应该是选项3，即：InnoDB认真执行了“把这个值修改成(1,2)"这个操作，该加锁的加锁，该更新的更新。</p><p>然后你会说，MySQL怎么这么笨，就不会更新前判断一下值是不是相同吗？如果判断一下，不就不用浪费InnoDB操作，多去更新一次了？</p><p>其实MySQL是确认了的。只是在这个语句里面，MySQL认为读出来的值，只有一个确定的 (id=1), 而要写的是(a=3)，只从这两个信息是看不出来“不需要修改”的。</p><p>作为验证，你可以看一下下面这个例子。</p><p><img src="https://static001.geekbang.org/resource/image/63/c1/63dd6df32dacdb827d256e5acb9837c1.png" alt=""></p><center><span class="reference">图14 可见性验证方式--对照</span></center><p><strong>补充说明：</strong></p><p>上面我们的验证结果都是在binlog_format=statement格式下进行的。</p><p>@didiren 补充了一个case， 如果是binlog_format=row 并且binlog_row_image=FULL的时候，由于MySQL需要在binlog里面记录所有的字段，所以在读数据的时候就会把所有数据都读出来了。</p><p>根据上面说的规则，“既然读了数据，就会判断”， 因此在这时候，select * from t where id=1，结果就是“返回 (1,2)”。</p><p>同理，如果是binlog_row_image=NOBLOB, 会读出除blob 外的所有字段，在我们这个例子里，结果还是“返回 (1,2)”。</p><p>对应的代码如图15所示。这是MySQL 5.6版本引入的，在此之前我没有看过。所以，特此说明。</p><p><img src="https://static001.geekbang.org/resource/image/d4/89/d413b9235d56c62f9829750a68b06b89.png" alt=""></p><center><span class="reference">图15 binlog_row_image=FULL读字段逻辑</span></center><p>类似的，@mahonebags 同学提到了timestamp字段的问题。结论是：如果表中有timestamp字段而且设置了自动更新的话，那么更新“别的字段”的时候，MySQL会读入所有涉及的字段，这样通过判断，就会发现不需要修改。</p><p>这两个点我会在后面讲更新性能的文章中再展开。</p><p>评论区留言点赞板：</p><blockquote>
<p>@Gavin 、@melon、@阿建 等同学提到了锁验证法；<br>
@郭江伟 同学提到了两个点，都非常好，有去实际验证。结论是这样的：<br>
第一，hexdump看出来没改应该是WAL机制生效了，要过一会儿，或者把库shutdown看看。<br>
第二，binlog没写是MySQL Server层知道行的值没变，所以故意不写的，这个是在row格式下的策略。你可以把binlog_format 改成statement再验证下。</p>
</blockquote><p><img src="https://static001.geekbang.org/resource/image/09/77/09c1073f99cf71d2fb162a716b5fa577.jpg" alt=""></p>
                    </div>
                </div>

            </div>
            <div data-v-87ffcada="" class="article-comments pd"><h2 data-v-87ffcada=""><span
                    data-v-87ffcada="">精选留言</span></h2>
                <ul data-v-87ffcada="">

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/f8/70/f3a33a14.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">某、人</span>
                            </div>
                            <div class="bd">回答下@发条橙子同学的问题:<br>问题一：<br>1)无条件查询如果只有order by create_time,即便create_time上有索引,也不会使用到。<br>因为优化器认为走二级索引再去回表成本比全表扫描排序更高。<br>所以选择走全表扫描,然后根据老师讲的两种方式选择一种来排序<br>2)无条件查询但是是order by create_time limit m.如果m值较小,是可以走索引的.<br>因为优化器认为根据索引有序性去回表查数据,然后得到m条数据,就可以终止循环,那么成本比全表扫描小,则选择走二级索引。<br>即便没有二级索引,mysql针对order by limit也做了优化,采用堆排序。这部分老师明天会讲<br><br>问题二:<br>如果是group by a,a上不能使用索引的情况,是走rowid排序。<br>如果是group by limit,不能使用索引的情况,是走堆排序<br>如果是只有group by a,a上有索引的情况,又根据选取值不同,索引的扫描方式又有不同<br>select * from t group by a  --走的是索引全扫描,至于这里为什么选择走索引全扫描,还需要老师解惑下<br>select a from t group by a  --走的是索引松散扫描,也就说只需要扫描每组的第一行数据即可,不用扫描每一行的值<br><br>问题三:<br>bigint和int加数字都不影响能存储的值。<br>bigint(1)和bigint(19)都能存储2^64-1范围内的值,int是2^32-1。只是有些前端会根据括号里来截取显示而已。建议不加varchar()就必须带,因为varchar()括号里的数字代表能存多少字符。假设varchar(2),就只能存两个字符,不管是中文还是英文。目前来看varchar()这个值可以设得稍稍大点,因为内存是按照实际的大小来分配内存空间的,不是按照值来预分配的。<br><br>老师我有几个问题:<br>1.我还是想在确认之前问的问题。一个长连接,一条sql申请了sort_buffer_size等一系列的会话级别的内存,sql成功执行完,该连接变为sleep状态。这些内存只是内容会被情况,但是占用的内存空间不会释放?<br>2.假设要给a值加1,执行器先找引擎取a=1的行,然后执行器给a+1,在调用接口写入a+1了数据。那么加锁不应该是在执行器第一次去取数据时，引擎层就加该加的锁？为什么要等到第二次调用写入数据时,才加锁。第一次和第二次之间,难道不会被其他事务修改吗？如果没有锁保证<br>3.始终没太明白堆排序是采用的什么算法使得只需要对limit的数据进行排序就可以,而不是排序所有的数据在取前m条。--不过这里期待明天的文章 <br></div>
                            <span class="time">2018-12-20 17:17</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">发条橙子同学的问题：<br>问题1:你回答得比我回复的答案还好！👍🏿<br>问题2:这个后面我们展开哈，要配图才能说得清😄<br>问题3:回答得也很好，需要注意的是255这个边界。小于255都需要一个字节记录长度，超过255就需要两个字节<br><br>你的问题：#好问题_#<br>1. 排序相关的内存在排序后就free掉还给系统了<br>2. 读的时候加了写锁的<br>3. 堆排序要读所有行的，只读一次，我估计你已经理解对了😄<br><br></p>
                                <p class="reply-time">2018-12-20 18:52</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/eb/20/a02c4f61.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">didiren</span>
                            </div>
                            <div class="bd">刚才又测了一下，在binlog-row-image=full的情况下，第二次update是不写redolog的，说明update并没有发生<br>这样我就理解了，当full时，mysql需要读到在更新时读到a值，所以会判断a值不变，不需要更新，与你给出的update t set a=3 where id=1 and a=3原理相同，但binlog-row-image会影响查询结果还是会让人吃一惊 <br></div>
                            <span class="time">2018-12-19 17:24</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">是的。<br><br>这个我也盲点了。<br><br>但是细想MySQL 选择这个策略又是合理的。<br><br>我需要再更新一下专栏内容</p>
                                <p class="reply-time">2018-12-19 19:03</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">null</span>
                            </div>
                            <div class="bd">re: 问题3:回答得也很好，需要注意的是255这个边界。小于255都需要一个字节记录长度，超过255就需要两个字节<br><br>11 月过数据库设计方案，总监现场抛了一个问题，就是关于 varchar 255 的。现在回看，木有人回答到点上，都说是历史原因。<br>下回再问，就可以分享这一点了。ꉂ ೭(˵¯̴͒ꇴ¯̴͒˵)౨”哇哈哈～ <br></div>
                            <span class="time">2018-12-21 09:45</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">最怕的回答“历史原因”、“大家都这么做的所以…”、“别人要求的” 😄</p>
                                <p class="reply-time">2018-12-21 10:41</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/03/f7/3a493bec.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">老杨同志</span>
                            </div>
                            <div class="bd">1)<br>mysql&gt; select * from t where city in (&#39;杭州&#39;,&quot; 苏州 &quot;) order by name limit 100;<br>需要排序<br>原因是索引顺序城市、名称 与 单独按name排序的顺序不一致。<br><br>2）如果不想mysql排序<br>方案a<br>可以执行两条语句<br>select * from t where city = &#39;杭州&#39;  limit 100;<br>select * from t where city = &#39;苏州&#39;  limit 100;<br>然后把200条记录在java中排序。<br>方案b<br>分别取前100，然后在数据端对200条数据进行排序。可以sort buffer就可以完成排序了。<br>少了一次应用程序与数据库的网络交互<br>select * from (<br>	select * from t where city = &#39;杭州&#39;  limit 100<br>	union all<br>  select * from t where city = &#39;苏州&#39;  limit 100<br>) as tt order by name limit 100<br><br><br>3）对分页的优化。<br>  没有特别好的办法。如果业务允许不提供排序功能，不提供查询最后一页，只能一页一页的翻，基本上前几页的数据已经满足客户需求。<br>  为了意义不大的功能优化，可能会得不偿失。<br>  如果一定要优化可以 select id from t where city in (&#39;杭州&#39;,&quot; 苏州 &quot;) order by name limit 10000,100<br>  因为有city\name索引，上面的语句走覆盖索引就可以完成，不用回表。<br>  最后使用 select * from t where id in (); 取得结果<br>  对于这个优化方法，我不好确定的是临界点，前几页直接查询就可以，最后几页使用这个优化方法。<br>  但是中间的页码应该怎么选择不太清楚<br>   <br></div>
                            <span class="time">2018-12-19 10:04</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">从业务上砍掉功能，这个意识很好👌👍🏿</p>
                                <p class="reply-time">2018-12-19 12:28</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/f7/95/3ebafd36.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">波波</span>
                            </div>
                            <div class="bd">笔记:<br>1.MySQL会为每个线程分配一个内存（sort_buffer）用于排序该内存大小为sort_buffer_size<br>  1&gt;如果排序的数据量小于sort_buffer_size，排序将会在内存中完成<br>  2&gt;如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序<br>  3&gt;在使用外部排序时，MySQL会分成好几份单独的临时文件用来存放排序后的数据，然后在将这些文件合并成一个大文件<br><br><br>2.mysql会通过遍历索引将满足条件的数据读取到sort_buffer，并且按照排序字段进行快速排序<br>	1&gt;如果查询的字段不包含在辅助索引中，需要按照辅助索引记录的主键返回聚集索引取出所需字段<br>  	2&gt;该方式会造成随机IO，在MySQL5.6提供了MRR的机制，会将辅助索引匹配记录的主键取出来在内存中进行排序，然后在回表<br> 	3&gt;按照情况建立联合索引来避免排序所带来的性能损耗，允许的情况下也可以建立覆盖索引来避免回表<br><br>全字段排序<br>1.通过索引将所需的字段全部读取到sort_buffer中<br>2.按照排序字段进行排序<br>3.将结果集返回给客户端<br><br><br>缺点：<br>1.造成sort_buffer中存放不下很多数据，因为除了排序字段还存放其他字段，对sort_buffer的利用效率不高<br>2.当所需排序数据量很大时，会有很多的临时文件，排序性能也会很差<br><br>优点：MySQL认为内存足够大时会优先选择全字段排序，因为这种方式比rowid 排序避免了一次回表操作<br><br><br>rowid排序<br>1.通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据，max_length_for_sort_data<br>2.只将需要排序的字段和主键读取到sort_buffer中，并按照排序字段进行排序<br>3.按照排序后的顺序，取id进行回表取出想要获取的数据<br>4.将结果集返回给客户端<br><br>优点：更好的利用内存的sort_buffer进行排序操作，尽量减少对磁盘的访问<br><br>缺点：回表的操作是随机IO，会造成大量的随机读，不一定就比全字段排序减少对磁盘的访问<br><br><br>3.按照排序的结果返回客户所取行数 <br></div>
                            <span class="time">2018-12-19 13:33</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">👍🏿</p>
                                <p class="reply-time">2018-12-21 14:11</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/10/1d/13/31ea1b0b.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">峰</span>
                            </div>
                            <div class="bd">由于city有两个值，相当于匹配到了索引树的两段区域，虽然各自都是按name排序，但整体需要做一次归并，当然只是limit100，所以够数就行。再然后如果需要不做排序，业务端就按city不同的取值查询两次，每次都limit100，然后业务端做归并处理喽。再然后要做分页的话，好吧，我的思路是先整出一张临时的结果表，create table as select rownumber,* from t where city=x order by name(写的不对哈，只是表达意思，rownumber为行数,并为主键)然后直接从这张表中按rownumber进行分页查询就好。 <br></div>
                            <span class="time">2018-12-19 08:31</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">分页这个再考虑考虑哈😄</p>
                                <p class="reply-time">2018-12-19 09:39</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/e2/9d/fbbd4611.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">cyberbit</span>
                            </div>
                            <div class="bd"><br>1.不会有排序，这种情况属于《高性能mysql》里提到的“in技法”，符合索引的最左原则，是2个等值查询，可以用到右边的索引列。<br>2.分页查询，可以用延迟关联来优化：<br>select * from t join <br>(select id from t where city in(&#39;杭州&#39;,&#39;苏州&#39;) order by name limit 10000,100) t_id<br>on t.id=t_id.id;<br><br> <br></div>
                            <span class="time">2018-12-19 11:54</span>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/07/1e/bdbe93f4.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">尘封</span>
                            </div>
                            <div class="bd">请问，第7步中遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端：这里会把id再进行排序吗？转随机io为顺序io？ <br></div>
                            <span class="time">2018-12-19 01:30</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">要是排序就结果不符合order by 的语义逻辑了…</p>
                                <p class="reply-time">2018-12-19 09:39</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/14/4f/fb/f0c5c4cf.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">赵海亮</span>
                            </div>
                            <div class="bd">老师你好，全字段排序那一节，我做了实验，我的排序缓存大小是1M， examined rows 是7715892，查询的三个字段都有数据，那么如果这些数据都放到缓存应该需要（4+8+11）*7715892等于160M，但是我看了都没有用到临时表，这是为什么？<br><br>CREATE TABLE `phone_call_logs` (<br>  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT &#39;主键ID&#39;,<br>  `city_id` int(11) NOT NULL DEFAULT &#39;11&#39;,<br>  `call_sender` varchar(40) DEFAULT NULL COMMENT &#39;电话主叫号码&#39;,<br>  `phone_id` bigint(20) NOT NULL DEFAULT &#39;0&#39; COMMENT &#39;手机id&#39;,<br>  PRIMARY KEY (`id`),<br>  KEY `idx_city` (`city_id`)<br>) ENGINE=InnoDB AUTO_INCREMENT=64551193;<br>----------------sort_buffer_size=1M----------------------------<br>root:(none)&gt; show variables like &#39;sort_buffer_size&#39;;<br>+------------------+---------+<br>| Variable_name    | Value   |<br>+------------------+---------+<br>| sort_buffer_size | 1048576 |<br>+------------------+---------+<br>1 row in set (0.00 sec)<br>---------------查询sql---------------------<br> select city_id,phone_id,call_sender from phone_call_logs where city_id=11 order by phone_id desc limit 1000;<br><br><br>-----------------------执行计划结果---------------------------------------------<br><br>  &quot;filesort_priority_queue_optimization&quot;: {<br>              &quot;limit&quot;: 1000,<br>              &quot;rows_estimate&quot;: 146364461,<br>              &quot;row_size&quot;: 146,<br>              &quot;memory_available&quot;: 1048576,<br>              &quot;chosen&quot;: true<br>            },<br>            &quot;filesort_execution&quot;: [<br>            ],<br>            &quot;filesort_summary&quot;: {<br>              &quot;rows&quot;: 1001,<br>              &quot;examined_rows&quot;: 7715892,<br>              &quot;number_of_tmp_files&quot;: 0,<br>              &quot;sort_buffer_size&quot;: 154160,<br>              &quot;sort_mode&quot;: &quot;&lt;sort_key, additional_fields&gt;&quot;<br> <br></div>
                            <span class="time">2018-12-19 19:41</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">好问题，明天见 😁<br>（明天的一篇也是跟排序有关的哦）</p>
                                <p class="reply-time">2018-12-20 00:29</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/eb/20/a02c4f61.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">didiren</span>
                            </div>
                            <div class="bd">感谢！针对我之前提出的疑问，我又详细的做了实验，发现一个新的问题，我感觉是个bug，希望解答<br># SessionA<br>mysql&gt; show variables like &#39;%binlog_row_image%&#39;;<br>| Variable_name    | Value |<br>| binlog_row_image | FULL  |<br>mysql&gt; create table t (id int not null primary key auto_increment,<br>    -&gt; a int default null)<br>    -&gt; engine=innodb;<br>mysql&gt; insert into t values(1,2);<br>mysql&gt; set tx_isolation = &#39;repeatable-read&#39;;<br>mysql&gt; begin;<br>mysql&gt; select * from t where id = 1;<br>| id | a    |<br>|  1 |    2 |<br>此时在另一个SessionB执行update t set a=3 where id = 1;成功更新一条记录。通过show engine innodb status看，Log sequence number 2573458<br>然后在SessionA继续。。<br>mysql&gt; update t set a=3 where id = 1;<br>Rows matched: 1  Changed: 0  Warnings: 0<br>Log sequence number 2573467<br>mysql&gt; select * from t where id = 1;<br>| id | a    |<br>|  1 |    2 |<br><br>这里与你给出的答案里的实验结果不同<br>可以看到redolog是记录了第二次的update的，但是select却没有看到更新后的值，于是我又换了一个平时测试用的实例，同样的步骤却得到了与你的答案相同的结果<br>然后我对比了2个实例的参数，发现当binlog-row-image=minimal时第二次查询结果a=3，当binlog-row-image=full时第二次查询结果a=2，而且不论哪个参数，redolog都会因为SessionA的update增长，说明redolog都做了记录，update是发生了的，但是binlog-row-image参数会影响查询结果，难以理解，我用的mysql版本是官方的5.7.13<br><br>下面是binlog-row-image = minimal的实验结果<br>mysql&gt; set  binlog_row_image=MINIMAL;<br>mysql&gt; drop table t;<br>mysql&gt; create table t (id int not null primary key auto_increment,<br>    -&gt; a int default null)<br>    -&gt; engine=innodb;<br>insert into t values(1,2);<br>mysql&gt; insert into t values(1,2);<br>mysql&gt; set tx_isolation = &#39;repeatable-read&#39;;<br>mysql&gt; begin;<br>mysql&gt; select * from t where id = 1;<br>| id | a    |<br>|  1 |    2 |<br>此时在另一个SessionB执行update t set a=3 where id = 1;成功更新一条记录。<br>mysql&gt; update t set a=3 where id = 1;<br>Rows matched: 1  Changed: 0  Warnings: 0<br>mysql&gt; select * from t where id = 1; <br>| id | a    |<br>|  1 |    3 | <br></div>
                            <span class="time">2018-12-19 17:01</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">！！！<br><br>你说的对<br><br>我验证的是statement格式。<br><br>MySQL 看来选了不错吧路径。<br><br>这个我之前真不知道😓<br><br>多谢<br><br><br></p>
                                <p class="reply-time">2018-12-19 19:02</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/e1/e7/d1b2e914.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">明亮</span>
                            </div>
                            <div class="bd">需要排序，可以将原来的索引中name字段放前面，city字段放后面，来建索引就可以了 <br></div>
                            <span class="time">2018-12-19 08:08</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">这样不太好哈，变成全索引扫描了</p>
                                <p class="reply-time">2018-12-19 09:36</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/12/36/ec/ca162d6b.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">edcSam</span>
                            </div>
                            <div class="bd">类如图二的索引图，我一直有一个困惑。city的索引图上，有小一些的长方形和大一些的长方形。有时候箭头源自于小长方形，有些箭头又源自于大长方形。这究竟是什么含义？ <br></div>
                            <span class="time">2019-01-12 09:34</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">原意是要宽的表示数据项，窄的表示指针<br>我们修改下图哈，不容易引起疑问。<br>👍细致</p>
                                <p class="reply-time">2019-01-12 12:53</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="http://thirdwx.qlogo.cn/mmopen/vi_32/JKKWS6TzhncvAA0p0NDiaATPIvMicSM76vNAg9IG1ibibcJYPAiaicYjZfq4gAV8GRtcTpOibfRD8vzqHBtL0ibmhwQsbg/132" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">唐名之</span>
                            </div>
                            <div class="bd">1：用@cyberbit 提供的方式，执行计划是不会使用到排序，但执行时间比使用排序消耗的多；<br>2：分页limit过大时会导致大量排序，可以记录上一页最后的ID，下一页查询条件带上 where  ID&gt;上一页最后ID limit 100 <br></div>
                            <span class="time">2019-01-09 14:56</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">1.  为什么这么说呢？<br>2. 对的</p>
                                <p class="reply-time">2019-01-10 19:27</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">felix</span>
                            </div>
                            <div class="bd">MySQL对limit有什么优化吗？是只能配合索引使用吗？谢谢 <br></div>
                            <span class="time">2019-01-04 09:09</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">没有一定要配合索引哦<br><br>下一篇有讲到一个limit 排序优化</p>
                                <p class="reply-time">2019-01-04 09:38</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/10/43/2f/4ff46e78.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">🐒 刘会俊🐒</span>
                            </div>
                            <div class="bd">接上一次的留言。谢谢老师的回复，昨天找到原因了，因为我造的总数据只有6条，使用select city,name,age from t where city=&#39;杭州&#39; order by name limit 1000的时候，使用explain分析的结果中，type为ALL，优化器认为6条数据走全表扫描比用city索引再回表的方式快，所以没有走city索引，看到的结果里examined_rows就是总的6条数据了，我用了force index(city)结果就跟您的一样了。不好意思老师，我是个开发，数据库的一些参数和知识不太熟悉，提了一些弱智问题请您见谅。 <br></div>
                            <span class="time">2019-01-03 08:38</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content"><br>把问题描述清楚大家就共同学习到啦😄<br>🤝</p>
                                <p class="reply-time">2019-01-03 10:03</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/10/43/2f/4ff46e78.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">🐒 刘会俊🐒</span>
                            </div>
                            <div class="bd">老师，我使用那一条建表语句和查询语句，最后使用explain得出的结果是using where；using filesort，然后我发现select * from information_schema.optimizer_trace\G;得到的结果是 <br>&quot;filesort_summary&quot;: {<br>              &quot;rows&quot;: 4,<br>              &quot;examined_rows&quot;: 6,<br>              &quot;number_of_tmp_files&quot;: 0,<br>              &quot;sort_buffer_size&quot;: 71880,<br>              &quot;sort_mode&quot;: &quot;&lt;sort_key, packed_additional_fields&gt;&quot;<br>            }<br>表里只有4条符合的数据，我没搞懂为什么examined_rows是6，因为6是总数据量为6条。 <br></div>
                            <span class="time">2019-01-02 16:10</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">哪个查询语句…</p>
                                <p class="reply-time">2019-01-02 17:38</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/10/bd/ed/95e8a314.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">过去、今日</span>
                            </div>
                            <div class="bd">进行排序后的临时文件分成几份是和sort_buffer_size有关，那数据库设置这个值会不会越大越好？ <br></div>
                            <span class="time">2018-12-31 18:25</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">好问题<br><br>不能哦，并发起来的时候内存占用过大，会到自己系统OOM的</p>
                                <p class="reply-time">2018-12-31 20:29</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/ee/49/8c1e831f.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">吴鸿达</span>
                            </div>
                            <div class="bd">波波可是百度dba😁 <br></div>
                            <span class="time">2018-12-28 22:02</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">额感觉你知道些什么😄</p>
                                <p class="reply-time">2018-12-28 22:44</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/13/e9/ab/37903736.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">J!</span>
                            </div>
                            <div class="bd">order by 主键 desc limit 1也会索引全表？ <br></div>
                            <span class="time">2018-12-27 21:16</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">索引全表是什么意思</p>
                                <p class="reply-time">2018-12-27 22:11</p>
                            </div>

                        </div>
                    </li>

                    <li data-v-87ffcada="" class="comment-item"><img
                            src="https://static001.geekbang.org/account/avatar/00/12/75/d9/24786b20.jpg" class="avatar">
                        <div class="info">
                            <div class="hd"><span class="username">小岭哥</span>
                            </div>
                            <div class="bd">为什么建立组合索引之后会天然的按照name递增排序呢 <br></div>
                            <span class="time">2018-12-26 07:14</span>

                            <div class="reply">
                                <div class="reply-hd"><span>作者回复</span></div>
                                <p class="reply-content">因为索引要求“有序”</p>
                                <p class="reply-time">2018-12-26 09:56</p>
                            </div>

                        </div>
                    </li>


                </ul>
            </div>
        </div>
    </div>
</div>
</body>
</html>