意见保藏!这份MySQL 连接查询超全详解送给你
<span style="color: black;"><strong style="color: blue;">| 作者</strong></span><strong style="color: blue;"><span style="color: black;">肖泽凡,腾讯TEG<span style="color: black;">开发</span>管理部小小后台攻城狮一枚,负责腾讯敏捷<span style="color: black;">制品</span><span style="color: black;">开发</span>平台TAPD的<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>于SegmentFault,博客名“X先生”,欢迎与我交流~</span></strong><span style="color: black;">在数据库中join操作被<span style="color: black;">叫作</span>为连接,<span style="color: black;">功效</span>是能连接</span><span style="color: black;">多个表的数</span><span style="color: black;">据(<span style="color: black;">经过</span>连接<span style="color: black;">要求</span>),从多个表中获取数据合并在<span style="color: black;">一块</span><span style="color: black;">做为</span>结果集返回给客户端。</span><span style="color: black;">例如:</span><p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">表A:</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">id</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">name</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">age</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">1</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">A</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">18</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">2</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">B</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">19</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">3</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">C</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">20</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">表B:</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">id</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">uid</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">gender</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">1</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">1</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">F</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">2</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">2</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">M</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><span style="color: black;"><span style="color: black;">select</span> A.*,B.gender <span style="color: black;">from</span> A <span style="color: black;">left</span> <span style="color: black;">join</span> B <span style="color: black;">on</span> A.id=B.uid</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">id</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">name</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">age</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">gender</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">1</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">A</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">18</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">F</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">2</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">B</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">19</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">M</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">3</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">C</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">20</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">null</span></p>
<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></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>用两个关键字:on,using。on<span style="color: black;">能够</span>指定<span style="color: black;">详细</span><span style="color: black;">要求</span>,using则指定相同名字和数据类型的列<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></p><span style="color: black;">on: <span style="color: black;">select</span> * <span style="color: black;">from</span> A <span style="color: black;">join</span> B <span style="color: black;">on</span> A.id=B.id <span style="color: black;">and</span> B.name=</span><span style="color: black;"><span style="color: black;">using</span>: <span style="color: black;">select</span> * <span style="color: black;">from</span>A<span style="color: black;">join</span> B <span style="color: black;">using</span>(<span style="color: black;">id</span>,<span style="color: black;">name</span>) = <span style="color: black;">select</span> * <span style="color: black;">from</span> A <span style="color: black;">join</span> B <span style="color: black;">on</span> A.id=B.id <span style="color: black;">and</span> A.name=B.name</span>
<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;"><span style="color: black;"><span style="color: black;"><span style="color: black;">1、</span>内连接</span></span></strong></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;">1. 语法:</span><span style="color: black;">A join | inner join | cross join B</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">2. 表现:A和B满足连接<span style="color: black;">要求</span>记录的交集,<span style="color: black;">倘若</span><span style="color: black;">无</span>连接条件,则是A和B的笛卡尔积</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">3. 特点:在MySQL中,</span><span style="color: black;">cross join</span><span style="color: black;"> ,</span><span style="color: black;">inner join</span><span style="color: black;">和</span><span style="color: black;">join</span><span style="color: black;">所实现的功能是<span style="color: black;">同样</span>的。<span style="color: black;">因此呢</span>在MySQL的</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;"><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;">1. 语法:</span><span style="color: black;">from A,B,C</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">2. 表现:相当于<span style="color: black;">没法</span><span style="color: black;">运用</span></span><span style="color: black;">on</span><span style="color: black;">和</span><span style="color: black;">using</span><span style="color: black;">的</span><span style="color: black;">join</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">3. 特点:逗号是隐式连接运算符。隐式连接是SQL92中的标准内容,而在SQL99中显式连接才是标准,虽然<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>,还是<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>出多个表之间的连接关系和连接依赖的属性。</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 style="color: black;">2、</span>外连接</span></strong></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;">1. 语法:</span><span style="color: black;">A left join B</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">2. 表现:左表的数据<span style="color: black;">所有</span><span style="color: black;">保存</span>,右表满足连接<span style="color: black;">要求</span>的记录展示,不满足的<span style="color: black;">要求</span>的记录则全是</span><span style="color: black;">null</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;">1. 语法:</span><span style="color: black;">A right join B</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">2. 表现:右表的数据<span style="color: black;">所有</span><span style="color: black;">保存</span>,左表满足连接<span style="color: black;">要求</span>的记录展示,不满足的<span style="color: black;">要求</span>的记录则全是</span><span style="color: black;">null</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;">MySQL不支持全外连接,只支持左外连接和右外连接。<span style="color: black;">倘若</span>要获取全连接的数据,要<span style="color: black;">能够</span><span style="color: black;">经过</span>合并<span style="color: black;">上下</span>外连接的数据获取到,如</span><span style="color: black;">select * from A left join B onA.name = B.name union select * from A right join B on B.name = </span><span style="color: black;">B.name;</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><span style="color: black;">union</span><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;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">3、</span>自然连接</span></strong></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;">A natural join B ==== A natural left join B ==== A natural right join B</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>的连接,MySQL会<span style="color: black;">运用</span><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 style="color: black;">要求</span>由MySQL自动判定。</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;">在连接过程中,MySQL各关键字执行的<span style="color: black;">次序</span>如下:</span></p><span style="color: black;"><span style="color: black;">from</span> -> <span style="color: black;">on</span>|<span style="color: black;">using</span> -><span style="color: black;">where</span> -> <span style="color: black;">group</span> <span style="color: black;">by</span> -> having -><span style="color: black;">select</span> ->order <span style="color: black;">by</span> -> limit</span>
<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><span style="color: black;">where</span><span style="color: black;">的,<span style="color: black;">亦</span><span style="color: black;">便是</span>先连接<span style="color: black;">得到</span>结果集后,才对结果集进行</span><span style="color: black;">where</span><span style="color: black;">筛选,<span style="color: black;">因此</span><strong style="color: blue;">在<span style="color: black;">运用</span></strong></span><span style="color: black;"><strong style="color: blue;">join</strong></span><span style="color: black;"><strong style="color: blue;">的时候,<span style="color: black;">咱们</span>要尽可能<span style="color: black;">供给</span>连接的<span style="color: black;">要求</span>,而少用</strong></span><span style="color: black;"><strong style="color: blue;">where</strong></span><span style="color: black;"><strong style="color: blue;">的<span style="color: black;">要求</span></strong>,<span style="color: black;">这般</span><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;"><strong style="color: blue;">连接算法</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">join</span><span style="color: black;">有三种算法,分别是</span><span style="color: black;">Nested Loop Join</span><span style="color: black;">,</span><span style="color: black;">Hash join</span><span style="color: black;">,</span><span style="color: black;">Sort Merge Join</span><span style="color: black;">。MySQL官方文档中<span style="color: black;">说到</span>,MySQL只支持</span><span style="color: black;">Nested Loop Join</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 style="color: black;">详细</span><span style="color: black;">来讲</span>Nested Loop Join又分三种细分的算法:</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">1. SNLJ</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">2. BNLJ</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">3. INLJ</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><span style="color: black;">select * from A left join B on A.id=B.tid</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;"><span style="color: black;"><span style="color: black;">1、</span>Simple NestedLoop Join(SNLJ)</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">SNLJ</span><span style="color: black;">是在<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><span style="color: black;">便是</span>让两张表做笛卡尔积进行扫描,是比较暴力的算法,会比较耗时。其过程如下:</span></p><span style="color: black;"><span style="color: black;">for</span> (a <span style="color: black;">in</span> A) {</span><span style="color: black;"> <span style="color: black;">for</span> (b <span style="color: black;">in</span> B) {</span><span style="color: black;"> <span style="color: black;">if</span> (a.id == b.tid) {</span><span style="color: black;">output <a, b>;</span><span style="color: black;"> }</span><span style="color: black;"> }</span><span style="color: black;"> }</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">当然,MySQL即使在无索引可用,<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><span style="color: black;">SNLJ</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;"><span style="color: black;"><span style="color: black;">2、</span>Block NestedLoop Join(BNLJ)</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">INLJ</span><span style="color: black;">是MySQL<span style="color: black;">没法</span><span style="color: black;">运用</span>索引的时候采用的</span><span style="color: black;">join</span><span style="color: black;">算法。会将外层循环的行分片存入</span><span style="color: black;">join buffer</span><span style="color: black;">, 内层循环的每一行与<span style="color: black;">全部</span></span><span style="color: black;">buffer</span><span style="color: black;">中的记录做比较,从而减少内层循环的次数,<span style="color: black;">详细</span><span style="color: black;">规律</span>如下:</span></p><span style="color: black;"><span style="color: black;">for</span> (blockA <span style="color: black;">in</span> A.blocks) {</span><span style="color: black;"> <span style="color: black;">for</span> (b <span style="color: black;">in</span> B) {</span><span style="color: black;"> <span style="color: black;">if</span> (b.tid <span style="color: black;">in</span> blockA.id) {</span><span style="color: black;"> output <a, b>;</span><span style="color: black;"> }</span><span style="color: black;"> }</span><span style="color: black;"> }</span>
<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;">SNLJ</span><span style="color: black;">算法,BNLJ算法通过外层循环的结果集的分块,<span style="color: black;">能够</span>有效的减少内层循环的次数。</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>,外层循环的结果集是100行,<span style="color: black;">运用</span></span><span style="color: black;">SNLJ</span><span style="color: black;">算法<span style="color: black;">必须</span>扫描内部表100次,<span style="color: black;">倘若</span><span style="color: black;">运用</span></span><span style="color: black;">BNLJ</span><span style="color: black;">算法,假设每次分片的数量是10,则会先把对</span><span style="color: black;">Outer Loop</span><span style="color: black;">表(<span style="color: black;">外边</span>表)每次读取的10行记录放到</span><span style="color: black;">join buffer</span><span style="color: black;">,<span style="color: black;">而后</span>在</span><span style="color: black;">InnerLoop</span><span style="color: black;">表(内部表)中每次循环都直接匹配这10行数据,<span style="color: black;">这般</span>内层循环只<span style="color: black;">必须</span>10次,对内部表的扫描减少了9/10,<span style="color: black;">因此</span></span><span style="color: black;">BNLJ</span><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><span style="color: black;">SNLJ</span><span style="color: black;">还是</span><span style="color: black;">BNLJ</span><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;"><span style="color: black;">BNLJ</span><span style="color: black;">算法减少的是总的扫描行数,</span><span style="color: black;">SNLJ</span><span style="color: black;">算法是外层循环要一行行扫描A表的数据,<span style="color: black;">而后</span>取</span><span style="color: black;">A.id</span><span style="color: black;">去表</span><span style="color: black;">B</span><span style="color: black;">一行行扫描看<span style="color: black;">是不是</span>匹配。而</span><span style="color: black;">BNLJ</span><span style="color: black;">算法则是外层循环要一行行扫描A表的数据,<span style="color: black;">而后</span>放到内存分块里,<span style="color: black;">而后</span>去表</span><span style="color: black;">B</span><span style="color: black;">一行行扫描,扫描出来的</span><span style="color: black;">B</span><span style="color: black;">的一行数据与内存分块里的</span><span style="color: black;">A</span><span style="color: black;">的数据块进行比较。<span style="color: black;">这儿</span><span style="color: black;">能够</span>一次<span style="color: black;">便是</span><span style="color: black;">非常多</span>行</span><span style="color: black;">A</span><span style="color: black;">的数据与</span><span style="color: black;">B</span><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;"><strong style="color: blue;"><span style="color: black;">影响<span style="color: black;">原因</span></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><span style="color: black;">BNLJ</span><span style="color: black;">算法总的扫描行数<span style="color: black;">是由于</span>外层循环的数据量</span><span style="color: black;">N</span><span style="color: black;">,和分块数量</span><span style="color: black;">K</span><span style="color: black;">还有内层循环的数据量</span><span style="color: black;">M</span><span style="color: black;">决定的。其中分块数量</span><span style="color: black;">K</span><span style="color: black;">与外层循环的数据量</span><span style="color: black;">N</span><span style="color: black;">又是息息<span style="color: black;">关联</span>的,<span style="color: black;">咱们</span><span style="color: black;">能够</span><span style="color: black;">暗示</span>为</span><span style="color: black;">λN</span><span style="color: black;">,其中λ取值为</span><span style="color: black;">(0~1)</span><span style="color: black;">。则总扫描次数</span><span style="color: black;">C=N+λNM</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 style="color: black;">能够</span>看出,在这个式子里,</span><span style="color: black;">N</span><span style="color: black;">和</span><span style="color: black;">λ</span><span style="color: black;">的<span style="color: black;">体积</span>都会影响扫描行数,<span style="color: black;">然则</span></span><span style="color: black;">λ</span><span style="color: black;">才是影响扫描行数的关键<span style="color: black;">原因</span>,这个值越小越好(除非</span><span style="color: black;">N</span><span style="color: black;">和</span><span style="color: black;">M</span><span style="color: black;">的差值非常大,<span style="color: black;">此时</span>候</span><span style="color: black;">N</span><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;"><span style="color: black;">那什么会影响 </span><span style="color: black;">λ</span><span style="color: black;"> 的<span style="color: black;">体积</span>呢?那<span style="color: black;">便是</span> MySQL的</span><span style="color: black;">join_buffer_size</span><span style="color: black;">设置项的<span style="color: black;">体积</span>了。λ和</span><span style="color: black;">join_buffer_size</span><span style="color: black;">成倒数关系,</span><span style="color: black;">join_buffer_size</span><span style="color: black;">越大,分块越大,</span><span style="color: black;">λ</span><span style="color: black;">越小,分块数量<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>不上索引的时候,<span style="color: black;">咱们</span>要优先<span style="color: black;">思虑</span>扩大</span><span style="color: black;">join_buffer_size</span><span style="color: black;">的<span style="color: black;">体积</span>,<span style="color: black;">这般</span>优化效果会更<span style="color: black;">显著</span>。而在能<span style="color: black;">运用</span>上索引的时候,MySQL会<span style="color: black;">运用</span>以下算法来进行</span><span style="color: black;">join</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;"><span style="color: black;"><span style="color: black;">3、</span>Index NestedLoop Join(INLJ)</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">INLJ</span><span style="color: black;">是</span><span style="color: black;">MySQL</span><span style="color: black;">判断能<span style="color: black;">运用</span>到被驱动表的索引的<span style="color: black;">状况</span>下采用的算法。假设</span><span style="color: black;">A</span><span style="color: black;">表的数据<span style="color: black;">行径</span>10,</span><span style="color: black;">B</span><span style="color: black;">表的数据<span style="color: black;">行径</span>100,且</span><span style="color: black;">B.tid</span><span style="color: black;"><span style="color: black;">创立</span>了索引,则<span style="color: black;">针对</span></span><span style="color: black;">select * from A left join B on A.id=B.tid</span><span style="color: black;">,MySQL会采用</span><span style="color: black;">Index Nested Loop Join</span><span style="color: black;">。其过程如下:</span></p><span style="color: black;"><span style="color: black;">for</span> (a <span style="color: black;">in</span> A) {</span><span style="color: black;"> <span style="color: black;">if</span> (a.id <span style="color: black;">in</span> B.tid.Index) {</span><span style="color: black;"> output <a, tid.Index所在行>;</span><span style="color: black;"> }</span><span style="color: black;"> }</span>
<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>循环10次A,每次循环的时候<span style="color: black;">经过</span>索引<span style="color: black;">查找</span>一次</span><span style="color: black;">B</span><span style="color: black;">的数据。而<span style="color: black;">倘若</span><span style="color: black;">咱们</span>反过来是</span><span style="color: black;">B left join A</span><span style="color: black;">的话,总共要循环100次</span><span style="color: black;">B</span><span style="color: black;">,由此可见</span><strong style="color: blue;"><span style="color: black;"><span style="color: black;">倘若</span><span style="color: black;">运用</span></span><span style="color: black;">join</span><span style="color: black;">的话,<span style="color: black;">必须</span>让小表做驱动表</span></strong><span style="color: black;">,<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><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;">INLJ</span><span style="color: black;">内层循环读取的是索引,<span style="color: black;">能够</span>减少内存循环的次数,<span style="color: black;">加强</span></span><span style="color: black;">join</span><span style="color: black;">效率,<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>非索引的数据,会产生一个回表读取数据的操作,这就多了一次随机的I/O操作。例如上面在索引里匹配到了</span><span style="color: black;">tid</span><span style="color: black;">,还要去找</span><span style="color: black;">tid</span><span style="color: black;">所在的行在磁盘所在的位置,<span style="color: black;">详细</span><span style="color: black;">能够</span>见我以前的<span style="color: black;">文案</span>:</span><span style="color: black;">MySQL索引详解之索引的存储方式</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;"><strong style="color: blue;"><span style="color: black;">重视</span>点</strong></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">1. <span style="color: black;">尽可能</span><span style="color: black;">增多</span>连接<span style="color: black;">要求</span>,减少join后数据集的<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;">2. 用小结果集驱动大结果集,将筛选结果小的表<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;">3. 被驱动表的被join的字段要<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><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;">4. 设置足够大的join_buffer_size</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;">Q:<span style="color: black;">倘若</span>想筛选驱动表的数据,例如左连接筛选左表的数据,该在连接<span style="color: black;">要求</span>还是</span><span style="color: black;">where</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;">A:要<span style="color: black;">经过</span></span><span style="color: black;">where</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;">Q:被驱动表匹配的数据行不<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></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">A:</span><span style="color: black;">join</span><span style="color: black;">之前先对被驱动表去重,例如<span style="color: black;">经过</span></span><span style="color: black;">group by</span><span style="color: black;">去重:</span><span style="color: black;">A lef join (select * from B group by name)</span><span style="color: black;">。</span></p><span style="color: black;"><strong style="color: blue;">特惠体验云数据库 </strong></span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/XS2esJew19ZyibdN87bZ9K85Q39tmic7goVVJf5ia0hny3IeiccopDq7iaAkgkxu0VwJkMicC9wQlf8Kz5BHIIq63ggg/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p><strong style="color: blue;">↓↓<span style="color: black;">更加多</span>惊喜优惠请点这儿~</strong>
你的话语如春风拂面,让我心生暖意。 楼主听话,多发外链好处多,快到碗里来!外链论坛 http://www.fok120.com/ 外链发布论坛学习网络优化SEO。
页:
[1]