00:00
好,那么接着的话呢,咱们来看一看,叫关联查询的一个优化啊,我们说在实际写这个SQL语句的时候呢,我们多贝尔的查询,还有这个子查询啊,我们说呢,一旦呢,这个色库比较复杂的时候呢,是不是都会涉及得到啊行,那下边的话呢,我们就专门来讲一下关于这个多表查询,还有子查询的这个优化啊,首先呢,我们来看一看这个多表的查询优化啊,这是我们的这个第三个点啊,首先呢,我们在这块呢说明一下啊,第三点啊,关联查询的优化。好,那提到这个多板的查询的话呢,首先呢,我们需要呢做一个分类啊回顾一下咱们呢,有不同的这个分类的角度对吧?那其中有一个角度的话呢,就是我们可以把这个桌边的连接呢,是分成叫内连接和外连接对吧?那么这个外连接,外连接的话呢,我们讲过叫左外连接,右外连接和买外连接啊在这个MYSQ当中,买外连接的话呢,我们得通过这个叫union的方式呢,去做这个操作,对吧?啊那这块呢,其实就是上边呢有可能或者下边呢,它是这个外连接的方式呢,所以买外连接呢,我们就不提了啊,它就相当于是一个组合了,那么这个对于左外连接和这个右外连接来讲啊,一个呢叫left,一个呢叫,那我们这右外连接的话呢,其实你把这两个表呢,交换一下,是不是也可以改造成了叫左外连接啊。
01:12
没问题是吧,所以说呢,这里边儿呢,我们讲这个外连接的话呢,咱们就只说其中的一种,就叫做左外连接了啊,就这是呢,我们这个叫情况一啊,以外连接为代表的话呢,我们只拿它呢来说明就可以了,那么在这个基础之上呢,我们再说一下这个叫情况二,咱们说一下叫内连接的问题啊,就这两个情况是不是就概括全了。行,那首先的话呢,我们来看一看这个外连接,那说到这些外连接的话呢,咱们这块呢,再给大家呢,去提供两个表,那通过这两个表呢,我们来做一个说明,那这两个表中的这个数据量呢,就不用那么大了,那我们也不需要呢,去对比一下用不用索引上的这个性能差别有多大,所以呢,我们这时候呢,就是诶添加了这个各20条数据,直接呢就写这个insert这个方式来实现的,这里边呢两个表,一个呢叫tap啊,一个呢叫book。Tap这个表里边呢,有俩字段啊,一个呢叫ID,那个一个一个叫car ID呢是一个主键啊,然后car呢,就是普通的一个字段,然后没有任何的索引,那这个book的话呢,不开D呢是主键,然后car的话呢,也是没有任何索引,各自呢有20条数据啊,这个呢,我就不用这块再去这个演示了,是吧?行,那么在这个基础之上的话呢,我们来谈一谈这个这个外连接的问题,首先的话呢,我们写了一个啊,写了一个这个诶关于这个阻外连接的一个操作。
02:27
啊也比较简单,就是呢,从我们这个time表呢,我们去连接一下这个book表阻碍连接的对吧,然后呢,让这两个普通的字段呢去做连接,由于呢,我们这两个表中啊,你没有任何的这个索引啊,就是我们这个字段上啊没有任何的这个索引,所以说呢,我们这个type呢,访问方法就全是奥啊就是全表的一个扫描。啊,这个大家应该能理解对吧,那这时候呢,具体是怎么扫描的,咱们这时候都是20条数据,咱换一个吧,比如说我们这个呢,有二条数据,这个book的话呢,假设呢是有30条数据,那这个呢,查询的过程是什么样子呢?我先说一个大概啊一会儿呢,我们专门的去讲一下这个join的一个底层原理。
03:00
好,那么它呢,其实就类似于我们讲过的这个叫嵌套循环一样啊,大家学编程语言,不管是Java呀,C呀,Python啊,Go啊,是不是都会有这种循环结构啊,那嵌到循环呢,跟这个呢就类似,我们呢先从这个,诶我们外边这个呢,上边这个呢,是不是叫驱动表,下边这个呢叫被驱动表,先从这个驱动表中呢,我们取一条数据。然后的话呢,根据这个连接条件啊,我们是不是去这个呃驱动呃被驱动表里边,是不是去找一下这个匹配关系啊啊,那我们就会从这个呃tab里边先取一条数据呢,在这个被巡动表里边呢,整个变了一遍,这呢是不是就有30次啊行,然后呢,我们呃找到这个关系条件以后呢,我们就放到这个结果集当中了,接着的话呢,我们再去从tab中取一条数据,然后接着里层这个是不是又是来一个30次。呃,所以呢,我们这个book的话呢,一共是不是需要执行20乘以30次这么多次数,跟这个签到循环是一样的,对吧?那么对于我们这个呃,内层或者说呢,叫被驱动表来讲的话呢,因为我们老师呢,需要便利它了,所以大家会看到这个查询优化器呢,实际上帮我们做了一个优化处理,就是使用一下的叫Java buffer。
04:04
啊,这个哈是哈状这种方式啊,然后把我们这个数据呢,缓存起来,那这样的话呢,呃,能够提升我们这样的一个检索的一个速度啊一个速度。行,这个呢,就是我们所看到的就是没有任何的这个索引的情况下啊,主要大家看这啊就可以了,这呢是一个优化啊,清楚一下,在这个基础之上呢,我给谁呀,我给这个book吧,咱们去加上一个这个索引啊,这个呢叫添加索引。哎,添加这个索引,好,那这时候呢,我们叫out table也行,或者我们叫create index也可以,咱们前面都讲过了啊,两种这个方式。啊index啊IDX,嗯,我们针对的这个是叫这个card的是吧。啊叫car的啊,嗯,这个卡这个我我这样写吧,这个我们叫一个Y吧,是吧,嗯,然后呢,哎啊咱们的这个叫book,嗯,这里边呢是叫哎card。理解是吧,行,那此时呢,我们选中啊做一个执行。
05:03
执行成功以后的话呢,我们接下来啊,再去执行一下我们这个plan。哎,这时候我们选中啊走起行,那这时候呢,大家你会发现呢,我们对于这个被驱动表来讲呢,此时呢叫ref啊,这个呢,访问方法的性能还是非常不错的,嗯,这个是用上这个K了,然后呢,关联就是我们这个驱动表里边的这个car了,这个呢叫using index是吧?诶那么此时的话呢,我们要执行起来的话呢,这个性能就要快很多了,从我们这个驱动表里边取一条数据,然后在我们这个被驱动表去找的时候呢,我们就能够使用索引了啊,你比如说这是一个B加数了,直接呢就往下找,找到你对应的想要的那条数据啊,找到这个次数的话呢,我们是不是叫log个N是吧,LOG2N是吧,这样的一个性能啊。嗯,整体来讲呢,这个性能呢,就要比原来的全面索引呢要好很多了啊,这不这个要好很多了嘛,嗯,然后呢,你接着再找外表的这一条数据,哎,对于外表来讲的话呢,或者我们这个驱动表来讲,它还是一个,呃,全表的一个扫描啊,全表一个扫描啊,这是一个另外的一个话呢,就是针对我们这个左外连接来讲,本身的话呢,我们说左板中的这个数据呢,是不是就全要啊。
06:05
啊,左外连接的话呢,相当于就是这样啊,左边一个表,右边一个表,除了这个内连接的数据要之外呢,左边中不满足条件的数据呢,也要,这不就是左外连接吗。对吧,那既然呢都要,那此时呢,我们这个呃,驱动表呢,它进行一个哦啊其实也行是吧呢,就是一条一条数据呢都过一遍,然后呢,看我们这个被驱动表呢这块呢,去使用上这个索引,这个时候呢,其实是比较好的一个选择。啊也就是说啊,也就是说,如果呢,我们这两个表呢来说只能够呃这这个字段是吧,只能够添加一个字段的这个索引,那一定要添加给这个被驱用表啊,就这个意思啊,当然了,你是不是也可以呢,给上边我们这个type这个里边也添加上啊哎没问题,那我们CTRLC一下粘过来,这个呢,我们比如说叫这个X了,那我这叫XY呢,是为了方便好区分啊哎,针对于我们这叫哎type这个表,哎type的话呢,它因为是一个这个关键字的啊,我们可以使用一下这个分隔符这样的方式,哎,选中来做一个执行。创建好以后的话呢,我们再去呢,执行这个plan啊,我们选中啊走起。
07:05
啊,可以了,那这时候大家会发现呢,你看我们是不是这两个表呢,都使用上了,这个叫索引对吧?哎,都使用上这个索引了,那这里边有个细节呢,大家还需要去注意一下什么呀,就是我们这两个字段上呢,都有这个索引啊,而且呢,现在呢也都应用上了,有一个隐含的条件呢,就是大家不要啊把我们两个表中的这个字段呢,名字可以不一样,但是他们的类型是一定要相同的,如果说它俩的类型要不一致的话呢,相当于我们还要相,还要经过一个饮食的转换,诶相当于呢,是不是又会出现呢,我们上面讲索引失效的这种场景,对吧?啊一旦呢,你使用上函数或者类型转换的话呢,就会导致我们这个索引失效。诶,所以大家呢,在设计这个多表连接的时候呢,这个呃,如果有这种连接关系,一定要让他们的类型是一样的啊,字段名了,你可以不一样,这无所谓了。好,这个呢,就说清楚了对吧,那么在我们这样的一个场景的基础之上呢,呃,我接着呢,去给它去这个索引啊去这个索引,呃去索引的话呢,我们比如说把这个诶book的索引呢,我们给它去掉吧,是吧,哎,这相当于我们去drop一下这个index。
08:06
嗯,叫Y啊,然后on book。哎,这个我们选中啊,做个执行。相当于我们把这个被驱动表的这个索引呢,是不是给干掉了啊,干掉以后的话呢,我们接着呢,再去做一个这样的执行啊选中啊周期哎,那相当于呢,你看时候我们这个呢,位置就成了一个O了啊就是成这样了是吧?然后呢,诶我们刚才呢,也讲过这优化策略,就是把它呢加到这个缓存当中啊这样的一个情况。哎,就说老师你演示这个有什么意义呢?呃,这个演示的话呢,主要呢,是咱们想跟这个内连接啊,做一个对比啊,在这里边呢,呃,当然没有对比的话呢,我们这个结论要说的话呢,其实是有点牵强啊,但是我还想把它先说出来,也就是说呢,在我们这个呃外连接当中啊,比如以这个阻碍连接为例,咱们这个左边这个表啊,它就相当于是叫驱动表,右边这个表呢,它就是作为叫被驱动表出现的。啊,我就先这样说一下的啊,那么我们在这个内连接当中啊,大家会发现呢,这个在这个驱动表和被驱动表呢,优化器呢,是有权利呢去来决定的,OK。
09:06
行,那我们就先说到这儿啊,然后接下来话呢,我们来看一看这个叫内连接的这个情况啊,内连接啊,走到我们这个课件这块,首先的话呢,我们把现有这个表中的这个索引呢,你都给它先删掉啊,诶我们把这个呢已经删了,我们再删另外一个啊drop一下,这个叫X是吧,我们是不是在这个tap当中的啊,这个你要怕它执行不了哈,记得加上这个操作啊啊选中啊执行。嗯,删掉了,那么这时候的话呢,我们再去执行这样的一个啊。是吧,呃,这个时候呢,我们把这个呢改成叫inner。那这就是变成了一个内连接了啊,使用这个tap呢,跟我们这个book呢做个连接,好此时这个呃,两个表上呢,关于car呢,都没有任何的这个索引了,所以你看这个都是O啊,Tab呢在上边不可在下边,相当于它还是驱动表,这个还是一个被驱动表,对吧?哎,这个跟我们刚才上面看那个最初的时候呢,阻碍连接是不是也是类似的一个效果。啊,没啥可说的,对吧,那么在这个基础之上呢,我们去添加这个索引。
10:04
啊,添加索引啊,这是添加索引的话呢,我们还是先给这个book呢去添加啊添加book的,那我们就这好粘一下啊CTRLCTT来粘过来,然后呢,我们再去执行我们这样的一个语句,哎,来我们选中啊走起。好,大家看此时的话呢,我们在这个扑上呢,是不是就加了一个索引,加上这个索引的话呢,诶我加上索引了没。这个怎么感觉呢,没加上似的。嗯,Book一个Y啊,在我们这个card上。Inner加上了是吧。嗯,我们这块呢,收一下啊。嗯,受这个index啊,然后呢,去on一下,诶from是吧,还有我们这个book啊。选中呢做一个执行。没有加上啊,Create index on,来,走起。啊,这这太假了啊好,那么接下来的话呢,我们去执行这个一。啊,这时候啊,这就有了啊,那么加上我们这个索引以后的话呢,呃,我们在这个位置呢,就出现了ref,然后这是个Y啊,这是这个啊这这没啥可说的是吧,大家应该比较清楚啊,那么在这个基础之上的话呢,我再去加索引。
11:08
啊,这个大家得跟着我这个节奏啊,别这个跟着跟着一会儿丢了啊,CTRLC一下。啊,这个我就把它删掉吧。我们呢,再去给咱们,呃,驱动表里边这个tap呢,加上一个索引啊,执行成功,然后呢,我们再去执行这个。好,这个时候的话呢,大家也注意我们这两个表的话呢,这个字段呢,是不是就都有这个索引了,都有索引的情况下呢,我们一走。诶一走,哎,这时候呢,大家你会看到,诶我们上面呢这个叫tap,下边这个呢,你看是不是叫book啊,诶这个时候呢,大家执行效果有可能啊,有可能跟我这块呢不一样。啊什么意思啊,呃,就是大家那块呢,有可能会出现这个tap跟这个book呀,他俩出现这个交换的这种场景啊。啊,出现这种交换的场景了啊呃,那我这个还没出现这个效果,还不太方便给大家去讲了啊呃,那我就诶大家那块呢,有可能会出现啊,诶我这块呢,就相当于说一下啊,我们此时的话呢,呃,这两个表它跟咱们这个阻碍连接不一样啊,阻碍连接的话呢,它有这个角色的区别是吧,人家是要这个表的所有的数据了啊,而我们这个内连接的话呢,是不是只要这两个表它们共同有的这个部分啊,所以说呢,我们此时呢,左右两边呢,这两个表的地位呢是一样的,那既然地位是一样的话呢,我们这个查询优化器呢,它就有这种决策的这种权利了。
12:28
啊,他就会去考虑说,诶我让谁去做这个驱动表,谁作为这个被驱动表,他俩呢,他就会都算一算,然后呢,选择一个成本比较低的啊,选择一个成本比较低的,然后呢去列出来,相当于这时候呢,我们发现这个book呢,还是作为我们这个被驱动表出现的。哎,就这样意思啊好,那这块呢,我还得说一下这个结论啊,只不过我这个结论在我这块呢,没有呈现出来而已啊。在我这个课件里边呢,这个呈现出来了,嗯,大家你看一下这。嗯,这是我给这个type呢加了一个上面呢,是不是给这个book加了一个,然后的话呢,我这块呢,做一个plan啊呃,先写的type后写的book,然后看结果当中是不是我先有book后写这个type呀。
13:08
啊,这个是我截图的时候的这个场景啊,就是是会出现这样的场景的啊,在这我们把这个结论说一下,说对于内连接来说。啊,那链接来说这个查询优化器啊,是可以决定。啊谁啊,作为啊谁。哎,作为这个驱动表啊,属于作为被驱动表出现的。啊,被驱动看表才出现的啊,这是我们说的这样一个结论啊好,这个说完之后,哎,然后的话呢,大家你看啊,我下边要删除索引了,哎删除索引你看我们现在的话呢,能够看到这个tap呢,它是在上边这个book呀,是不是在下边啊。好,然后呢,我删谁呀,我删book。哎,我这块呢,Drop一下这个book。Conry。然后呢,我再把这个呢CTRLC执行一下,注意看我现在删的是这个被驱动表的,没问题吧,好我把它选中啊执行一下。
14:08
执行完以后的话呢,大家你再注意看,我们去做这个执行,我刚才删的是被驱动表的啊,然后我们再去执行。啊,大家看。诶,你会发现呢,是不是他俩交换位置了,我们刚才这个顾客是在下边的,我把下边这个索引删掉之后呢,你会发现呢,我们这个tap呢,它就出现在下边了。啊,也就是说啊,也就是说,当我们呢,是一个内连接的时候呢,我们这个连接的这个字段当中,如果呢,只能有一个字段上是有索引的。啊,那么这时候呢,我们呃,就经过成本计算呢,会发现这个被驱动表上呢有索引是诶成本比较低的啊,这个大家可以作为一个结论呢,给大家固定下来啊,那因为这里边呢,我们是不是这个tap这里边呢是有索引的,所以呢,我们tap呢,就会充当一个叫哎被驱动的这样一个表。啊,那具体分析的话呢,我们先简单说一下,就跟我们上面讲的一样啊,这取一条数据在这里边呢,我们是不是直接呢去找这个B加数就行是吧,那就这样一个道理啊,一会呢,我们再详细的展开去说啊,我们先把这里时候这个结论呢也写一下。
15:08
啊,对于那两级来讲。哎,来讲。哎,说呢,哎,如果这个表的连接条件中,哎连接。啊,条件中啊,只能有一个。哎,只能有一个啊,这个字段有索引。啊,那么则哎这个有索引的这个字段,哎所在的这个表啊,会被作为。啊,被驱动表出现。哎,这呢,就是我们说这个查询优化器呢,优化的一个策略,哎,就是这样子的,行,那我们把这个就删掉了是吧,然后接下来的话呢,大家注意看啊,我又把这个给它加上啊,CTRLCL一下。哎,我在这块呢,是不是有create给大家再加上呗,哎,选中执行,然后再去执行我们这个CTRLC一下啊拿过来。
16:01
啊,选中走起啊,诶你看这时候呢都有了,以后呢,是不是又把我们这个book给放到下边了,就这不是一个随机的啊注意啊,它主要呢是要计算一下这个成本啊,谁作为这个被训表出现的是合理的,呃,这呢我们又加上了,然后的话呢,在这个基础之上啊,在这个基础上呢,我去添加数据。这时候咱们这两个表里边是不是数据呢,都是20条数据是吧,哎,我们向。诶,在咱们这个场景下啊,嗯,我向哪个表中去添加数据呢?嗯,我要是向book表中添加不式的,我得向这个type表中啊。嗯,Tap表中啊,添加这个数据啊,添加20条数据吧。嗯,这样子啊,嗯,注意啊,我这里边这个tap呢是在上边的,所以呢,我是往tap中去加的,大家那块呢,如果你的tap呢是在下边的,你就得是,那就不在上面吧,那你就往book里边去加啊,你别跟我这块一样啊,嗯,那我往tab里边加呢,咱们就可以是不是再选中这个操作呀。
17:03
哎,我把这个呢,CTRLC一下是吧,好拿过来。好,选中啊执行。嗯,哎哟,他这呢,说我们这ta不存在啊,主要这块呢,我们得给他改一下了是吧。嗯,写成这样的方式。啊,CTRLC。啊,这个我们做一个替换啊。你看它这个识别,我们刚才那块呢,是添加是可以成功的啊,但是呢,实际开发当中,大家确实呢,还是准确一点的话呢,要加上这样的一个分隔符。或者说我这块呢,给这个表起名的时候呢,起的这个名字呢,不太好是吧,好来我们再执行一下。可以了,好大家注意看啊,我现在呢,往这个表里边儿,我是不是添加这个数据了啊,那么现在的话呢,咱们这个tab表中这个数据啊是比较多的,诶我再CTRLC一下,咱们过来我去执行了啊。嗯,咱们刚才的话呢,执行的时候呢,Tap是不是在上边啊好,现在呢,我们再来看。
18:02
哎,大家看你发现的这个tap呢,是不是就跑下边了。就是老师我忘了啊,那你翻视频,你翻到刚才呢,你截个图啊,这个我还清晰的记得啊呃,Tab呢,现在跑下边了,也就是说呢,我们这两个表中的数据量呢,你看不一样了,那这个时候的话呢,它影响了我们谁作为被驱动表和驱动表出现了啊,那么这时候我们相当于结论什么呢。结论上说,对于内连接来说。哎,来来说是吧,嗯,这个在这个连接。那在两个表的啊,这个连接条件。哎,连接诶。哎,连接条件啊,都存在索引的情况下。诶所引的这个诶情况下怎么着呢,我们说呢,诶会选择诶我们这个book里边呢,是有20条数据,Type呢,是不是有这个诶40条数据,那相当于呢,我们此时呢,是不是把这个小表作为这个驱动表出现啊啊我们说会选择这个诶小表。
19:05
哎,小表,哎作为啊驱动表,哎那这呢,其实就对应了我们说的一句话啊,叫做小表驱动大表,哎大家呢,有可能听说过是吧?哎这样的一个原则,OK,行,那这里边呢,呃,就是我们给大家强调的这样的,哎外连接和这个内连接这样的一个实际的情况啊这个说完之后呢,大家呃相当于是清楚了,我们从实际的验证的效果来讲,诶我们就给大家把这个,呃实际的结论呢给大家,诶说出来了,呃那么到底我们底层呢,呃用数学的这个支撑,我们去做一个解释,是什么样子呢?咱们来看一看下边这叫照安语句的一个原理。好,我们这个呢,先停一下。
我来说两句