为么 SQL 语句不要太多的 join？
<span style="color: black;">作者：柯三</span><span style="color: black;">链接：juejin.im/post/5e0443ae6fb9a0162277a2c3</span><h2 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">送分题</span></h2>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>：有操作过Linux吗?</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：有的呀</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>：我想查看内存的<span style="color: black;">运用</span><span style="color: black;">状况</span>该用什么命令</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：free <span style="color: black;">或</span> top</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>：那你说一下用free命令都<span style="color: black;">能够</span>看到啥信息</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：那，如下图所示 <span style="color: black;">能够</span>看到内存以及缓存的使用<span style="color: black;">状况</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">total 总内存</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">used 已用内存</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">free 空闲内存</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">buff/cache 已<span style="color: black;">运用</span>的缓存</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">avaiable 可用内存</p><img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hNKIPErMWem8gHlbmc6OiazPT3lojlBMwaY4jsjjNZovOibKxKwdk6mvuA/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>：那你<span style="color: black;">晓得</span>怎么清理已<span style="color: black;">运用</span>的缓存吗(buff/cache)</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：em... 不<span style="color: black;">晓得</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>：sync; echo 3 > /proc/sys/vm/drop_caches就<span style="color: black;">能够</span>清理buff/cache了，你说说我在线上执行这条命令做好<span style="color: black;">欠好</span>?</p><img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hNraCzI2T8RnmibpAv7GlSUrV9IQLIc1F7ZWHYicLt943c07qVNm3bXwGA/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：(送分题,内心大喜)好处大大的有，清理出缓存<span style="color: black;">咱们</span>就有<span style="color: black;">更加多</span>可用的内存空间, 就跟pc上面xx卫士的小火箭<span style="color: black;">同样</span>，点一下，就释放出好多的内存</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>：em...., 回去等<span style="color: black;">通告</span>吧</p>
<h2 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">再谈SQL Join</span></h2>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>：换个<span style="color: black;">专题</span>，谈谈你对join的理解</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：好的（再答错就彻底完了,把握住机会)</p>
<h3 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">回顾</span></h3>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">SQL中的<a style="color: black;">join</a><span style="color: black;">能够</span><span style="color: black;">按照</span>某些<span style="color: black;">要求</span>把指定的表给结合起来并将数据返回给客户端</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><a style="color: black;"><span style="color: black;">join</span></a><span style="color: black;">的方式有：</span><a style="color: black;">5 种 Join 连接及实战案例！</a></p>inner join 内连接<img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hNHvJwWBuHntTveI6laYpBaRXHhXDH9HtgxPue5rqNkYWWjRYM9Gl4UA/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">left join 左连接<img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hNruMiaFrFXjqb7MVLoSxPFD2emYqV7TrMEeqGKI5WgQiciaL13flA2ziasg/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">right join 右连接<img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hNSpvGZLObdB6kibrBrLsXib3A4OkjQkXs0acV2KfzN7H1GsZqElMq2kYQ/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">full join 全连接<img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hNtpOYRqEWkRMEh54QIj5alvP39ulPZ26jjc78vJGd3EmVOnlawicBJNg/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">以上<span style="color: black;">照片</span>源：https://www.cnblogs.com/reaptomorrow-flydream/p/8145610.html</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>：在项目<span style="color: black;">研发</span>中<span style="color: black;">倘若</span><span style="color: black;">必须</span><span style="color: black;">运用</span>join语句，<span style="color: black;">怎样</span>优化<span style="color: black;">提高</span>性能?</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：分为两种<span style="color: black;">状况</span>，数据规模小的，数据规模大的。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>: <span style="color: black;">而后</span>?</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：<span style="color: black;">针对</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">数据规模较小</strong> <span style="color: black;">所有</span>干进内存就完事了嗷</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">数据规模<span style="color: black;">很强</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">能够</span><span style="color: black;">经过</span><span style="color: black;">增多</span>索引来优化</span>join<span style="color: black;">语句的执行速度 <span style="color: black;">能够</span><span style="color: black;">经过</span>冗余信息来减少</span>join<span style="color: black;">的次数 <span style="color: black;">尽可能</span>减少表连接的次数，一个SQL语句表连接的次数不要超过5次</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>：<span style="color: black;">能够</span>总结为<a style="color: black;">join</a>语句是相对比较耗费性能，对吗？</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：是的</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>: <span style="color: black;">为何</span>?</p>
<h3 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">缓冲区</span></h3>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>: 在执行join语句的时候必然要有一个比较的过程</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>: 是的</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：逐条比较两个表的语句是比较慢的，<span style="color: black;">因此呢</span><span style="color: black;">咱们</span><span style="color: black;">能够</span>把两个表中数据依次读进一个内存块中, 以MySQL的InnoDB引擎为例，<span style="color: black;">运用</span>以下语句<span style="color: black;">咱们</span>必然<span style="color: black;">能够</span>查到<span style="color: black;">关联</span>的内存区域show variables like %buffer%</p><img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hNuIt8pX75Eickib3yvibqQ6Tic9ciaU8xGvDrTZf1HKHlemU2Tc7bbnA8Ueg/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">如下图所示join_buffer_size的<span style="color: black;">体积</span>将会影响<span style="color: black;">咱们</span>join语句的执行性能</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>: 除此之外呢?</p>
<h3 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">一个大前提</span></h3>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：任何项目终究要上线，不可避免的要产生数据，数据的规模又不可能太小</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>: 是<span style="color: black;">这般</span>的</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：大部分数据库中的数据<span style="color: black;">最后</span>要<span style="color: black;">保留</span>到硬盘上,并且以文件的形式进行存储。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">以<a style="color: black;"><span style="color: black;">MySQL</span></a>的InnoDB引擎为例</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">InnoDB以页(page)为基本的IO单位，<span style="color: black;">每一个</span>页的<span style="color: black;">体积</span>为16KB</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">InnoDB会为<span style="color: black;">每一个</span>表创建用于存储数据的.ibd文件</p><img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hNgYfIOaBb7szib1rODco5fPrchHKXJRWbbr0SYBsGBWojORzNGlwOdRA/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">验证</p><img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hN1Vs7cMONz6ibYyzQl6e5Jm94ykxuWcWaicjXnn6tWcDZFLrysGhuYicSQ/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：这<span style="color: black;">寓意</span>着<span style="color: black;">咱们</span>有多少表要连接就<span style="color: black;">必须</span>读多少个文件，虽然<span style="color: black;">能够</span>利用索引，但还是免不了频繁的移动硬盘的磁头</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>：<span style="color: black;">亦</span><span style="color: black;">便是</span>说频繁的移动磁头会影响性能对吧</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：是的，<span style="color: black;">此刻</span>的开源框架不都<span style="color: black;">爱好</span>说自己<span style="color: black;">经过</span><span style="color: black;">次序</span>读写大大的<span style="color: black;">提高</span>了性能吗，<span style="color: black;">例如</span>hbase、kafka</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>：说的没错，那你认为<a style="color: black;">Linux</a>有对此做出优化吗?提示，你<span style="color: black;">能够</span>再执行一次free命令看一下</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：奇怪缓存怎么占用了1.2G多</p><img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hNe9ro6x66vyKC7iby8icWOH8ibGwGYOrDic1KCMftunV3waKM1JfhAGmhfA/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"><img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hN8jfiblCvnIsFS57nCUib073Rt2MWrqXUf0j21mbyl7zFYZvqOP7r68Ng/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">照片</span><span style="color: black;">源自</span>：https://www.linuxatemyram.com/</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>: 你有<span style="color: black;">无</span>想过</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">buff/cache 里面存的是什么,？</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">为何</span>buff/cache 占了<span style="color: black;">那样</span>多内存，可用内存即availlable还有1.1G？</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">为何</span>你<span style="color: black;">能够</span><span style="color: black;">经过</span>两条命令来清理buff/cache占用的内存，而想要释放used只能<span style="color: black;">经过</span>结束进程来实现?</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">品，你细品</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">思考了几分钟后</p><img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hNKIPErMWem8gHlbmc6OiazPT3lojlBMwaY4jsjjNZovOibKxKwdk6mvuA/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：这么随便就释放了buff/cache所占用的内存，说明它就不重要, 清除它不会对系统的运行<span style="color: black;">导致</span>影响</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>: 不完全对</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：难道是？想起来《CSAPP》（深入理解计算机系统）里面说过一句话</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">存储器层次结构的本质是，每一层存储设备都是较低一层设备的缓存</span></p><img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hNHWN9fKXgHjMq47Toia62zNNCy9XfSuYapAicVNwibAfVPmaIpGNHw6X2A/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">翻译成人话，<span style="color: black;">便是</span>说<strong style="color: blue;">Linux会把内存当作是硬盘的高速缓存</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">关联</span>资料：http://tldp.org/LDP/sag/html/buffer-cache.html</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>：<span style="color: black;">此刻</span><span style="color: black;">晓得</span>那道送分题应该怎么回答了吧</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：我....</p><img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hNziaJXaFxRfpQtPXOndG1mzCibjq8T2gdkboiatp8bfR6MRRjTwf5K9o8g/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<h3 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">Join算法</span></h3>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>：再给你个机会，<span style="color: black;">倘若</span>让你来实现Join算法你会怎么做?</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：无索引的话,嵌套循环就完事了嗷。有索引的话，则<span style="color: black;">能够</span>利用索引来<span style="color: black;">提高</span>性能.</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>：说回join_buffer 你认为join_buffer里面存储的是什么?</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：在扫描过程中，数据库会<span style="color: black;">选取</span>一个表把他<strong style="color: blue;">要返回以及<span style="color: black;">必须</span>进行和其他表进行比较的数据</strong>放进join_buffer</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">面试官</strong>：有索引的<span style="color: black;">状况</span>下是怎么处理的？</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">我</strong>：这个就比较简单了，直接读取两个表的索引树进行比较就完事了嗷，我这边介绍一下无索引的处理方式</p>Nested Loop Join<img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hN0icsfZicNribGTA9iaqvjuxRL6ZLVJaJn5b3F9Aqvicj5D0yl28NFcrzlSg/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">嵌套循环，每次只读取表中的一行数据，<span style="color: black;">亦</span><span style="color: black;">便是</span>说<span style="color: black;">倘若</span>outerTable有10万行数据, innerTable有100行数据，<span style="color: black;">必须</span>读取10000000次(假设这两个表的文件<span style="color: black;">无</span>被操作系统给缓存到内存, <span style="color: black;">咱们</span><span style="color: black;">叫作</span>之为冷数据表)</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">当然<span style="color: black;">此刻</span>没啥数据库引擎<span style="color: black;">运用</span>这种算法（太慢了)</p>Block nested loop<img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hNicCtpBmicqwaDFFVVUNhZvPiceOJPINkCwDkIII7BlJL5CwvhGia59UuRA/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">Block 块，<span style="color: black;">亦</span><span style="color: black;">便是</span>说每次都会取<span style="color: black;">一起</span>数据到内存以减少I/O的开销</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">当<span style="color: black;">无</span>索引<span style="color: black;">能够</span><span style="color: black;">运用</span>的时候，MySQL InnoDB 就会<span style="color: black;">运用</span>这种算法</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">思虑</span>以下两个表 t_a 和t_b</p><img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hNh8ZGp7OjKK4dvjNwbA45uMAicyvMMcdQuC6yCS3RjX4zE5KoJoDFU3w/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">当<span style="color: black;">没法</span><span style="color: black;">运用</span>索引执行join操作的时候，InnoDB会自动<span style="color: black;">运用</span>Block nested loop 算法</p><img src="https://mmbiz.qpic.cn/mmbiz/TNUwKhV0JpSpZv0r0YE6jwM0RUWvE7hNnMkMMlqZgzD2LfZqsibG6a54B0uS1gKsNOMBGff26SzY23jSklpH3bg/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<h2 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">总结</span></h2>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">上学时，数据库老师最<span style="color: black;">爱好</span>考数据库范式，直到上班才学会一切以性能为准，能冗余就冗余，实在冗余不了的就join<span style="color: black;">倘若</span>join真的影响到性能。试着调大你的join_buffer_size, <span style="color: black;">或</span>换固态硬盘。</p>
<h3 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">参考资料</span></h3>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">《深入理解计算机系统》- 第6章 存储器层次结构</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">https://www.linuxatemyram.com/play.html 作者<span style="color: black;">经过</span>几个例子<span style="color: black;">来讲</span>明硬盘缓存对程序执行性能的影响</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">https://www.linuxatemyram.com/ Free参数的解释</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">https://www.thegeekdiary.com/how-to-clear-the-buffer-pagecache-disk-cache-under-linux/ <span style="color: black;">文案</span>开头送分题命令的解释</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">https://juejin.im/book/5bffcbc9f265da614b11b731/section/5c061a4de51d451df113c10d MySQL 是<span style="color: black;">怎么样</span>运行的：从根儿上理解 MySQL</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">https://mariadb.com/kb/en/block-based-join-algorithms/ 来自MariaDB官方文档解释了Block-Nested-Loop算法的实现</span></p><span style="color: black;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;">重磅！</strong></span><span style="color: black;"><strong style="color: blue;">程序员交流群</strong></span><span style="color: black;"><strong style="color: blue;">已成立</strong></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">公众号运营<span style="color: black;">迄今</span>，离不开小伙伴们的支持。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">为给小伙伴们<span style="color: black;">供给</span>一个互相学习交流的平台，特地开通了程序员交流群</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">群里有干了</span><span style="color: black;">10年的技术大佬在线答疑</span><span style="color: black;">，不时会分享<span style="color: black;">有些</span>技术要点，不时分享<span style="color: black;">有些</span><span style="color: black;">优秀</span>的学习资料，还有送书活动。。（群完全免费）</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">必须</span>进群的<span style="color: black;">伴侣</span>，可长按扫描二维码。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">▲长按扫码</span></p>
</span>
顶楼主，说得太好了！ 系统提示我验证码错误1500次 ＼~゛， 我完全赞同你的观点，思考很有深度。 期待更新、坐等、迫不及待等。
页:
[1]