00:00
好,那咱们关于这个多表的连接查询呢,咱们就说到这儿了,诶那么最后的话呢,咱们不是提到了一种算法叫block connected loop这样的一种算法,对吧?这个呢,是针对于咱们5.7的这种场景,那么在这个8.0当中啊,咱们会发现呢,这个算法呢,又进行了优化,它改成了叫哈希join啊那么有兴趣的同学呢,下来呢,可以查询一下关于哈希join的一些,呃,具体的一些这个底层的一些情况啊这呢我们就不进行拓展了,好,那么接着的话呢,我们来谈下一个问题啊,下一个问题的话呢,就接触到了咱们在这个SQ编写当中也是非常重要一块内容,叫做子查询。那子查询的话呢,诶,在他出生这个这个定义之后啊,我们说呢,他还是有很多的好处的,他可以一次性的是完成我们之前呢,需要多个查询才能够完成的工作,对吧?诶能够实现一个比较复杂的一个查询结构了,但是通常情况下呢,我们说这个子查询的执行效率啊,一般都不是特别高,诶主要原因呢,就是我这里边儿列举出来三点啊,其实主要呢算是两点啊。来我们看一下,首先的话呢,我们说呢,去执行这个此查询的时候呢,我们针对内层这个查询,通常呢,是需要建立一个临时表的啊,那你建立这个临时表要建立了后边你再把它销毁,整个这个过程当中呢,我们就需要呢进行诶过多的一个CPU的一个资源,还有IO资源的一个消耗啊这呢往往也是我们产生这个慢查询的一个主要源头。
01:19
啊,这个大家是需要去注意的啊,占用内存空间同时对吧?啊这是一点,然后另外一点的话呢,就是针对于我们这个临时表啊,我们是没有办法呢,去给他去建立这个索引的啊,那没有办法建立索引,一旦我们这个临时表呢,它比较大的话呢,诶就是我们所谓这个结构机比较大,对吧,那对我们这个性能影响呢,是非常大的。啊,那怎么办呢?诶我们这一节呢,其实主要呢,想给大家抛的这个结论,就是呢,建议在实际开发当中啊,诶大家呢,可以考虑呢,把这个子查询呢,是不是把它再诶拆解成多个这个查询,对吧?你在拆解的过程当中呢,可以使用这种照的操作啊也就是呢,我们可以呢,使用这种多表连接的方式呢,去替代咱们的查询,一个搞不定,那你可以拆成多个。
02:00
啊,然后在我们这个多表连接当中呢,我们去使用相关的这个索引上面也讲过了啊,小表驱动大表啊等等啊,被驱动表我们有索引啊是吧,这样的一些这个具体的规则啊,就是这样的一个情况啊。好呃,那这块呢,就提到了,我们用这个照呢去替代这个子查询,就不用建临时表了啊,而而且速度呢,比子查询要快啊,因为我们还可以呢去创建索引对吧,那下边呢,就是给大家呢举了两个例子啊,举了两个例子,那首先我们来看一下这个第一个例子啊,这个例子的话,咱们直接也是在这个啊这个8.0当中呢,去做了演示啊,应该是到这个第四个了哈,叫子查询的优化。好直接呢,我们把这个呢就粘过来,呃,首先的话呢,针对于我们这个class这样的一个表呢,我们去创建一个索引啊,针对的叫monitor,就是每个班的这个班长这样一个字段行,下边呢,我们查询的看看是什么说select这个student,然后呢,学生的一个学号啊in。OK啊,这个意思的话呢,就是我们从班级当中找到这个班长啊,前提呢是这个班呢是有班长的,对吧,这个是意思闹闹的,然后找到班长以后的话呢,我们看哪些学生是这个班长啊,输出这个班长的信息,其实就是是吧啊相当于就是查询。
03:10
这个,哎,班长的这个信息。OK,诶就这样的一种方式啊,那我们选择呢,选中以后呢,把它做一个执行,那我们看到这里边是有这个三条的个信息的,嗯,主要呢,我们看一下这块啊叫materialized这个咱们前面呢讲这个explain,这个select这样个这个字段的时候呢,其实提到过这个materialized什么意思啊,就是我们在进行这个此查询的时候呢,我们会对这个内查询,相当于对它进行一个呃,叫什么物化是吧,那物化之后的话呢,跟我们外边这个查询呢,进行一个连接。啊,那这呢,就是我们所谓的这样的一个material这个意思啊。啊,这块呢,Using这个具体的where啊using index OK,行,那这呢,还用到了我们相应的这个索引是吧,OK。行,那这是我们说的这个点,然后呢,咱们把它跑一下吧,哎,执行一下这个select看一下。嗯,花费了0.2秒是吧,诶这个呢,还没查出来结果哈,正常来讲呢,是肯定有结果的,只不过呢,由于我们这个学生表中的学生的number和这里边monitor咱们都是随机生成的啊,所以说就没有匹配上啊,是这样一个尴尬的情况是吧?实际情况的话呢,那每一个班级里边班长是不是一定是学生啊,一定会匹配过来的,OK。
04:20
行,嗯,这是这个,然后我们这个呢,是一个子查询,咱们就可以把它改成改造成是一个多表的查询的情况啊,我们看我们CTRLC1下来粘过来,诶这个呢,就改造成是一个多表的一个查询了,对吧,相当于呢,就是学生表和我们这个班级表,然后呢,这个学生的number和我们这个诶班级的班长这个呢,等于然后并且呢,这个不能是空是吧?哎,我们这时候选中做个执行就可以了。啊,就这样情况啊,这个缩引也能够用得上对吧?哎,都是simple的这个状态啊行,然后的话呢,我们要是把这个选中做个执行啊,我们走起。啊,这个貌似呢,看执行的时间还长了一点是吧,诶当然呢,整个来讲呢,跟我们刚才上边这个呢,其实还都不查出这个数量级的一个差别啊,还都算是一个档档次一个级别的是吧?哎,成这个意思了哈,不明显这个效果,然后第二的话呢,我们来看看这个局,呃,这个取所有不为班长的这个同学的信息,哎,我们还是使用这个叫子查询来看一下。
05:16
那这呢是查询。哎,查询不为啊,这个班长的。班长的这个学生的这个信息啊,那就相当于呢,我们找到这个班级里边这个班长,然后呢,Student这个number is not in是吧,哎,不在我们这里边了,这就是哎选中了这做1EXPLAIN的操作了啊哎这呢是一个查询的方式,然后的话呢,我们直接呢选这个select啊,我们做一个执行。啊执行,诶这个效率看来还挺高的啊,0.007秒是吧,诶效率还挺高的,然后呢,我们这个结构呢,其实也可以呢,去转化成是一个什么呀,叫做子查询对吧?诶多表的一个连接啊。行,这块我们粘过来这个多瓣连接的话呢,就是这样一个格式啊,其实呢,大家也比较熟悉,就是我们这个阻碍连接的基础之上啊,阻碍连接什么呢?就是这个要这个也要,然后呢,在这个基础上呢,我们把这部分呢,是不是砍掉不要啊,那就相当于让我们b.monitor呢is no是不是就可以了啊B呢就是我们这个class,行,那我们这块呢,选中呢做个执行啊,这呢是分析的一个效果,哎,我们也可以呢,这个select做一个执行。
06:18
啊,你看这效率也很低是吧,哎,不是效率也很高啊,描述也很低,嗯,也是没有拉出来明显这个效果啊,但是从这个规律上啊,结论上来讲的话呢,我们推荐呢,把这个子查询呢,大家做一个改造,尤其呢上我们这个子查询比较复杂的时候呢,诶大家要考虑变成这个多表的一个查询,OK。行,那么在这里边呢,其实蕴含了一个小的公式哈,哎,你看我们这里边呢,是不是出现了一个叫not in呢,说这个表,然后呢,这个字段呢,Not in这个表中的这个字段。哎,Not in是吧,就是不在这里边的,那怎么办呢?我们可以把这两个连在一起,让它俩呢是相等的,然后同时的话呢,我们再去去掉这个B表里边的,相当于是它is not no的这个数据,那就是is no no数据,相当于都是is no is no no的是吧?然后我们只要它是闹的,相当于把这个公共的数据呢,它给去掉了,剩下的就是只有我们这个A表或者左表中有的这个数据了。
07:10
哎,就是我们这样的这个,诶情况呢,Not in或者not exist呢,都可以换成,诶前面呢,就比如我们这个student left,这个class on,哎,就是你这个连接关系,然后呢,让我们这个monitor is no是吧?诶都可以替换成这样的格式,这呢相当于是个套路啊,大家呢,可以考虑呢去使用。那么关于这个子查询的优化呢,就比较简单直接一些了啊,我们就过掉了,直接呢,我们来看一看,这叫排序优化啊,这块呢,我们涉及到内容呢,其实还不少。啊,排序优化啊,首先呢,抛一个问题,说这个在外二条件的字段上呢,我们去加上这个索引是吧?哎,我们说这个性能会好一点,那为什么我还要考虑在这个orderb的这个字段上去加索引呢?啊因为呢,呃,如果order上我们这个能用上索引的话呢,你想想你在排序的话,是不是就省时间了,因为本身的这个索引就是排好序的,对吧?哎,这那就是我们提到这个排序的问题了,那么在这个MY当中啊,我们支持两种排序方式,分别呢,叫和这个index,前面呢,我们在举例子的时候呢,其实大家呢,也看到过这个这个方式。
08:08
啊,以后呢,要养成一个概念,就是只要你看到file了,潜意识当中我们就认为呢,它是比较耗时的啊,我们能够用这个index的方式呢,那是最好的啊,因为呢,诶我们索引的话呢,本身就有序,你按按照它进行排序,是不是直接呢就可以操作了啊,然后效率呢,会比较高一些。而这个的话呢,呃,那就相当于我们需要呢,进行这个文件的一个排序了,一般的话呢,我们都得需要把这个数据呢加载到内存中,内存当中啊进行排序啊,这个时候呢,还需要占用这个CPU的资源,对吧?啊同时的话呢,如果我们要排序的这个数据啊,量很大,我们内存中呢,可能都乘不下啊,缓存中放不放不了怎么办啊,你还得排一部分数据呢,给他再写出去,然后再加载一部分呢,再去写出去啊还需要产生很多这个临时IO的这种场景,效率呢是很低的。啊,所以说呢,我们在实际开发中的这个建议是什么呢?诶,我们在这个SQ当中,在V尔子居中,在奥子集中,我们可以考虑呢去使用索引,Where中使用呢,是为了避免出现这个叫全表扫描,对吧?在outb中呢,我们使用这个索引呢,主要目的呢,就是为了避免出现这个file。
09:14
诶,就是这样一个目的,因为效率太低了是吧?啊但是啊,但是也有些极端情况下呢,我们出现了file,但是呢,它的性能呢,反而比我们的index呢还要高,诶是有这样的场景的,这里边呢,我案例实战当中,其实呢,就有这样的例子啊,一会儿呢我们说。这是第一个,哎第二的话呢,我们说尽量呢去使用这个index呢,去完成我们这个orderbi是吧?哎,那这地方你看如果说这个where啊和这个orderbi后边呢,是相同的列,天呐,太完美了对吧,你按照它进行过滤以后,好以后呢还用它来进行排序,那那是不是太完美了啊,最好的情况啊,那如果说呢,要是不同啊外后边的字段和奥BI中的字段不同呢,那我们就可以考虑呢,去建立一个联合的索引。啊,联合索引的话呢,因为我们一般呢,先执行的是VR嘛,所以你是最好呢,比如说这是A这是B的话呢,是不是你建立一个联合索引,就是先A后B是吧,这样的一个情况啊。
10:07
好,下一个说,如果呢,我们没有办法去使用这个index的时候呢,就是我们这个index这种排序方式,那你就只能是对文件排序方式呢,进行尽可能的调优了。啊,没有办法是吧,那你就是针对它来进行调用,OK,就这个意思行,那下边的话呢,我们来看一看具体的一些这个例子啊。首先的话呢,提到啊,要删除这个student和class表中已创建的一些索引啊,这个你要造的索引很多的话呢,我们就都要干掉,诶这块呢,我们是不是也还可以呢,使用咱们最初呃,在这一章的时候呢,刚开始咱们创建了一个呃存储过程是吧?诶删除我们这个数据库中这个表中的所有的这个索引哈。好来我们这块呢,来讲解一下咱们这个第五部分啊,这个叫排序索引啊,排序优化啊。首先的话呢,我们执行这样的一个语句啊,把咱们STEM的这个表里边的这个,呃,相当于是这个这个索引啊,除了主键索引之外呢,全给干掉了啊。
11:02
嗯,另外一个是不是也需要干掉啊。在class里边也删一下吧。那这个我们再执行一下,选择一下我们这个class。哎,这块我们选中。来执行一下啊,这块我们做个说明啊,删除哎,Student和。Class这个表中的非这个主键索引,OK,哎,上完以后的话呢,你可以在这呢去做一个受啊index。From一下我们这个student。收index一下我们这个class是吧。好,这个呢,我们选中执行啊,就这一个了啊,这个选中执行啊,也就这一个行,那么在这个情况下呢,我们去接着说下边这个例子啊,首先呢,这有一个叫过程一啊,CTRLC啊选中。呃,在我们这个student表中呢,就只剩了啊order和class ID啊,这就别想了,肯定是没有我们这个索引可用的,对吧,执行一下啊,没有任何的,哎,这呢叫using file直接呢我们在,诶这个数据呢,加载到内存中,我们进行这个排序啊,OK的。
12:13
行,这是一个,嗯,然后的话呢,我们再看下边这个啊,CTRLC。啊,下边这个其实呢,只是在我们上边这个结构的基础上呢,加了一个limit啊,加了一个限制啊叫呃分页是吧?哎,这时候也是啥也没有啊,没有任何的索引可以去使用啊,然后在这个基础之上的话呢,我们去添加了一个索引,然后的话呢,我们看这个操作,哎,我们把这个呢拿过来。啊,这个。嗯,上面叫过程一是吧,这个咱们也加一个吧。哎,过程一。这里边儿呢,就没有任何的索引了,只有用大写的一半。然后接下来来我们看下这个叫过程二,这个时候的话呢,叫order by时啊不limit,所以呢是一个时效的什么意思,看一下CTRLC。嗯,粘过来,此时的话呢,我们创立了一个,哎,在这个student这样的一个表当中,针对于age class ID和name建立了一个联合索引啊,注意一下我们这里边的顺序。
13:11
好,选中。执行。你稍微的需要花点时间可以了,然后的话呢,我们这时候呢,进行了一个order by,诶order by呢,你注意我这写的是age,这个是class ID是吧?诶大家觉得我们这块会用上索引吗?啊,不好说是吧,来走起,哎为什么说要不好说呢?哎,这里边呢,跟我们这个表中的数据量啊,哎包括呢,我们这个字段啊,啊其实是都是有关系的,哎都是有这个关系的啊那从这个结果上来看的话呢,大家会发现是不是没有使用咱们的索引。没有用啊,Using fair,这也是个out是吧?没有用,诶,为什么这块呢?我们有一个age和class ID开头的,它怎么不去使用呢?哎,其实这里边呢,就是我们这个查询优化器,它的一个优化策略了啊,嗯,什么意思啊。就是相当于呢,如果他要是用上索引的话呢,那其实就是用我们这个索引了,对吧,用上A啊,甚至说呢,再用上你这个class ID啊,乃至说再用上你这个name幕啊,用完之后的话呢,他发现呢,他还得是不是你这是相当于是一个二级索引了,是吧?用完之后的话呢,诶我们这个数据量还挺大的啊,然后呢,我还得是不是需要呢,回表一下,找到我们这个主表当中的啊,你那个去数索引,然后呢,因为你这要查询的是所有字段嘛,是吧,每一条数据呢,过来之后呢,是不是还得找到叶子节点。
14:25
呃,然后每一条数据都得如此,诶我得先走一下这个二级索引,然后每一条数据呢,还得回去呢,再回表,整个我们这个表中的数据量呢,是几百万条数据,呃,差运化器呢,发现说呢,算了,我我还是别这几百万条数据都回表了,我干脆啊直接呢把我们这个表中的所有数据呢,我都加载进来,然后我在内存级别呢,重新排个序得了,我发现我的这个花的时间啊,比你这个还少。哎,其实就是这样个原因。哎,这个大家注意一下,就这个原因啊,诶那要这么着的话呢,大家你看哈,我这时候呢,把这个星呢,我改成是一个A,哎这块呢,再加上一个叫class ID。
15:01
诶这么着的是吧,诶那么此时这块呢,我们一直行,大家你发现哎呦这时候呢,竟然用上了我们这个缩引不再是这个fair了是吧?诶这个呢是什么呀?诶这个呢,其实是由于我们这个时候呢,没有回表造成的。啊,这个没有回表呢,其实我们就称为呢,叫做覆盖索引。啊,咱们在这个下边这个点的时候呢,到时候再给大家去提这两个点啊,他没有回表的话呢,那这时候呢,我们是不是你要是在这个用上边这个file,那就有点是吧,有点太太太。是吧,太太费时了啊,我们这个呢,不用回表了,你用索引当然会更好了啊,这个不用回表操作呢,就是因为呢,我们诶这个索引就是我们这个二级索引当中呢,包含的这个字段呢,完全可以能够诶。支撑得住你这个select的这个字段是吧,都能覆盖的了啊,不用你再去找那个主表那个居素索引了啊,一会再说这个事。然后的话呢,我们再看下边这个。At cony。
16:01
啊转过来啊说呢,这个增加这个limit过滤条件了啊,在我们刚才这个条件呢,在这个呃,Select的基础之上呢,我们就加了个limit是吧?哎,加上这个limit以凑以后呢,我这块去做执行,诶大家你就会发现呢,我们使用上了这个索引。哎,使用上这个索引,哎,有同学可能就有点迷糊了,诶这是难道limit这个也跟索引有关系吗?啊这个主要原因是什么呀?就我刚才讲的哈,咱们呢,诶这块呢,数据量呢,其实挺大的,我们先这个用二级索引,索引完以后呢,每一条数据还得回表,然后呢,在这个居速索引当中,再一条条找到叶子节点啊这样整然后还不如我们直接呢,针对于我们这个,呃,内存当中的这个数据呢,直接排序快,而这时候呢,不一样了啊,你现在是不是只要这十条数据啊。那你只要这十条数据呢,那干脆我就排好序,以后呢,我就取前十个,就光这十个呢进行回表,那这个成本呢,他认为就很低啊,比你在内存中呢,所有的字段,哎,针对这俩字段呢,再重新排序,排序一遍,所有数据这个要来得快诶所以这块呢,它就用上所引哈,啊这个大家理解一下啊,就我们这里边呢,举的这些场景呢,其实就是给大家呢再提供一些。
17:09
呃,实际当中的一些案例啊,你知道是怎么回事儿啊,你说这个经验怎么增加的呢?啊,一有一些呢,是理论的啊,咱们说一些这个规则是吧?呃,另外一些的话呢,其实就让大家多见,你见多了以后呢,是不是这个见识就有了,经验就有了,对吧?好,我们这块呢,就相当于是大量的去见这样的一些实际情况啊。然后这里边的话呢,叫order顺序错误的话呢,所以呢也会失效啊,那么我们去创建一个新的索引啊,这个所以呢,我们是age class ID和student number啊选中以后啊,我们做一个执行。嗯,这个呢,咱们也,哎。哎,我也把这个呢,稍微的截一下啊。哎,放到这儿,现在呢,我们有两个大的这个联合索引。行这块呢,我们已经创建好了,创建好以后的话呢,说以下啊,哪些索引呢会失效是吧?哎大家看一看跟你想的是不是一样啊,首先第一个呢,Order by啊,咱们这个表里边除了主键索引就这俩,这个上来就来个class ID,这显然是不是用不上。
18:10
啥玩没有是吧,好下一个啊,这个的话呢,叫class ID name class ID name,嗯,这个也没有age是吧,就是你像你这种呢,上来呢,联合缩引的第一个指法都不考虑的,最起码呢,我们前面还讲过一个最最左前缀原则呢,是吧,像你这种肯定用不上的啊fair的。好,下边这个这个就有点意思了,我们order by呢h class ID啊student number诶好像跟这个能匹配的上,然后十来我们选中执行一下,那果真呢,是不是效果不错是吧,这个值呢是14。啊,这个能理解吗?哎,这个14呢,哎,我们这age class ID和number,来我们翻到上面来看一下啊。嗯,首先呢,Age啊,这个age呢是允许为闹的,相当于它呢是占五个字节是吧?然后呢,这个class ID的话呢,它是一个允许为闹的,它也是占五个字节,然后student number在这儿啊,它不是not,所以它占四个字节,所以一加呢是不是就14啊。
19:04
OK啊行,回过来,相当于我们这里边呢,是把这三个字段呢,是不是就都使用上了,哎,这个是挺不错的啊行,然后呢,我们再看这个,这个呢,我们是age class ID number呢,我们没出现。哎,那当然呢,你这块呢,还有一个这样的一个联合作引前两字段跟这个也一样,对吧,来我们选中了做个执行。啊,大家会看到呢,是不是我们选择的是上边这个对吧?诶,因为只用了两个字段呢,这两个里边会挑一个啊,同时的话呢,你发现这个K是73,相当于把我们这个name呢也给含在里边了啊,他也考虑在内了,所以你排完序以后的话呢,这个内幕相当于也是一个有序的状态啊。嗯,再往下我们再去执行,这叫order by age,诶,Order by age其实也能够用得上,而且呢,他还选择了这个的这个方式。哎,还选的是这种方式,OK,那其实像A相当于我们这时候你排完以后呢,不管这个A呢,有序的后边这个跟着字段呢,也都,哎有这个具体的序顺序了啊行这呢,就是我们能够看到呢,到底哪些有效,哪些失效啊这样的一些场景,那当然呢,我们这里边讲呢,其实呃,有一个前提呢,就是针对于我这个表中这个数据量的这种场景下的一些例子啊,那在实际当中呢,这个量例子呢,如这个数据量呢,不一样了啊,这个例子的这个结果呢,它可能也是不同的啊诶CTRLC一下。
20:15
接着我们来看一下这个,哎,这一波数据。诶,这波数据。那我这呢,也是把这个例子呢,咱们都写好了,直接呢来做分析啊,这样呢,大家可以见的例子呢,会更多一些,首先看这个。呃,直接呢,我们只考虑这个奥啊呢,这叫age啊de啊class ID啊,我们这age呢,相当于是一个这样,咱们写的话呢,其实就都是,呃算是一个升序的这种方式了啊来这块呢,我们去做个执行。啊,这样做个执行的话呢,发现没有使用任何的缩引,哎,主要原因呢,就是我们这个age的话呢,你是一个降序牌,这个我们就玩不了了。就玩不了了啊,这个这个这个不行是吧,然后呢,再接着啊,我们把这个呢,再执行一下。哎,这个呢,你像它都没有这个age啊,那没有age直接就蹦到后边这个字段上来,那肯定也用不上对吧?诶所以这个也不行,然后这个啊age AA class ID呢DA这个行不行。
21:09
这行不行?啊,结果一看这样也不行是吧,也不行啊,这个不行的话呢,就是诶主要原因是我们这个age呢跟这个class ID呢,你看它是AA,它是DEA,呃相当于呢,就是如果要是用索引的话呢,比如说咱们用这个啊哎我嗯行,比如我们就用上面这个,我这呢是一个升序的啊这是升序的,但是到这个class ID的话呢,你就变成降序了,那那就相当于我们这个A的话呢,嗯,假设我们这几个A呢,相当于都是相等的,相等的情况下呢,我们找这个class ID呢,我还得这这逆着来。那这样玩的话,结果发现还不如我们重新的在内存中区排序来的快是吧,Fair的来得快,哎,所以干脆啊就不用啊,主要是这个原因,诶跟我们这里边数据特点呢,其实也是有关系的啊。好,再看这个啊,诶这个有点意思了,我们这叫age d class ID也D,它俩呢,都是一个反着的。啊,这个呢,反而诶我们能够使用上这个索引了,你看是不是就用上了,而且呢,你看这块又提示说backward index sc啊,我们倒着去这个去便利就可以了,你不是这个age呢,你是一个序的吗?然后呢,在你这里边这个class ID呢,也是一个声序的,那我们干脆倒着来,你是不是这个age也好,Class ID也好,是不是都是相当于从大到小了。
22:20
哎,就这样的一个情况,所以呢,我们反而呢,哎这样呢,倒能用这个缩引,所以呢,如果要顺序错了,我们就不缩引,如果方向反的话呢,也不缩引,要反了就都反,哎,但是顺序呢得一样啊,先A后class ID是吧。行,这是我们说的这个叫过程四啊。然后还有一波啊,这个叫过程五。Very。哎,粘过来啊,这个呢叫无过滤不索引啊,大家呢,再去体会一下啊,这里边呢,反而呢也是有一定的这个难度的啊粘过来,哎,分析啊,还是呢,有这两个联合索引,这呢我有个age啊,有个order啊,有个age有个order by诶大家可能会想说,诶,那是不是能用上我们这个索引了。
23:03
来咱们走一些啊,确实呢用上了啊,确实呢用上了,这时候呢,他综合判断以后呢,使用的是咱们这个叫number就这个了是吧?啊这个就这个啊啊就没有用它用的这个,但用这个的话呢,大家你会发现呢,它这时候呢,只用了长度是五是吧。哎,所以这个难度呢,并不在于说呢,我们是不是用索引,而在于说呢,这个为什么是五。为什么是五啊,大家想想。说白了就是我们这时候是不是只考虑这个H呢,这个呢,是不是根本就没管它呀。对吧,也没管它哈,嗯,这块的话呢,其实我们要稍微的说一下哈,就是我们这个所有的这种排序啊,它都是基于我们先去过滤数据,对吧?那如果说我们过滤完以后的话呢,能够把大部分的数据呢,都过滤掉了,呃就剩下比如说几百条几千条,甚至说呢,这个上万的话呢,也不会特别多的这种啊,你别这个十十多万那种,或者几十万,那那那还差点是吧,呃,就是呃,比如一万多呀,2万啊这个可能也还好啊,诶当我们这个查询到以后的这个数据呢,不太多的时候呢,呃,有可能他就不再去使用,呃另外一个字段呢,去做这个索引了。
24:03
啊,去考虑这个收益呢,因为呢,呃,咱们觉得说几千,呃这个挺多的,那其实对于CPU来讲,在内存当中几千条数据呢,那其实so easy的的这个事儿啊,So easy的这个事。所以呢,我们会发现呢,你看这个是这个值呢,这五只考虑了我们这个A值。嗯,然后这块呢,你看他查询的这个Rose的话呢,100%的是有18000多行数据,嗯,按咱们来讲,1万多条数据其实不少了,但是呢,CPU来讲说这个数据呢,其实还是不多的啊,诶他觉得呢,说直接我们就诶回表啊,直接回表就可以了。啊,就没有考虑这个带啊,这个注意一下,这是优化器的一个选择,跟这个数据量呢有关系。然后再往下的话呢,这叫它,然后order class ID name啊那这个的话呢,就我们一执行是不是仍然是这样的效果是吧。啊,仍然是这样效果,他也没有考虑啊,这也是1万多条数据,嗯,所以你看通过这也能看出来,嗯,这个呢,诶我们只考虑,哎,当然这个这个很正常啊,因为这两个过滤条件一样啊,所以说你这个Rose呢,这个数值就是应该是一样的哈,只不过呢,它只使用了我们这个A啊这个段,然后再往下呢,诶这块我们选中了组合执行。
25:08
这个话呢,我们先考虑VR的话呢,你看根本都不具备了是吧,嗯,或者说他要先考虑A的话呢,嗯,这个嗯,其实这个我们跟下边这个对比着去说吧,下边这个话呢,我们只是在这个基础上呢,我们又加了一个limit。对吧,哎,我又加了个limit,那这个的话呢,我们诶会用上索引吗?诶同学们反而觉得说说跟上面这不一样吗?诶但是呢,你一执行发现呢,他还真用上了。对吧,还真用上了哈,嗯,我们直接的话呢,当然是先去做这个VR的一个过滤了,是吧,但是你看发现呢,我们这里边儿呢,竟然用到了我们这样的一个索引。嗯,而且呢,还全用上了。对吧,全用了啊这个怎么去理解呢。相当于呢,我们是按照这个age呢,它做了一个排序啊,就是使用我们这样的一个二级索引是吧?诶然后呢,找我们的age排序啊,先排好序以后呢,然后他在这个排序的基础之上呢,去找这个class ID呢是45的。
26:01
哎,然后呢,找到这个情况以后的话呢,只取前十个,直接呢,就从这里边儿呢去取前十个了。啊,所以说它这个呢,性能呢会更高一些啊,而我们这个的话呢,他觉得说呢,呃,你要是先这样A。啊,他要是先按照你这个来啊A制完以后,然后呢去找这个,然后你也没有这个living的限制,那就相当于这个数据呢,就全要这个数据量也很大,诶他认为的话呢,还不如我直接呢,就呃这个这个当然你也没有这个class ID开头的这样的索引啊,咱们要有的话呢,是不是也肯定会用到这个索引啊UID呢,就这个意思啊。看一下啊,此时的话呢,我们再去create一个index啊,我叫idx啊,叫cid是吧,然后呢,这个on。哎,Student里边呢,叫class ID,哎这么着。把他那选中了,我们做个执行。嗯,可以了是吧,然后在这个基础上呢,我们CTRLC一下啊,你再去执行我们这个语句。好CTRLC啊执行这个呢,毫无疑问是不是它一定会用到我们这个叫cid这样的一个索引了,就是相当于呢,我们这个呢,诶能用呢,当然是更好的,呃,当然呢,你要一开始没有这样一个索引的话呢,他就觉得呢,是我们直接呢进行全表的一个,呃,Five thought啊感觉呢是更好一些的。
27:12
啊,就这样一个道理了啊行,他就没有用我们哎提供的这两个索引啊,这个大家一定要注意一下啊嗯,并不是说我们一看到时后觉得哎他能用,是哎像这种情况呢,反而确实能用,就奥德B呢,跟我们这个class跟这个威尔在一起的时候呢,我们是可以考虑呢去构建这个联合索引的,但是呢,能不能用,值不值得用,还得经过查询优化器的分析。啊,这个呢,不就能看到哎一种场景对吧。啊,这块呢,稍微有点难度啊,好,那么这样的话呢,咱们就给大家举了一些例子啊,做一个简单的一个小结啊,这个注意这个小结当中呢,其实还得需要结合具体的,但这个这个实际的表中的数据我们去谈,咱们这呢,只是泛泛的说一些这个规则啊,能不能用啊,真正用没用上呢,其实还得再另看啊。比如说我们这里边儿呢,有个联合索引叫ABC啊,那从order by的这个角度来讲呢,是不是这样写都可以啊。
28:04
这个大家应该一看手机都能知道是吧,哎,要逆序的话呢,就都这个逆着来,但是你得ABC还得是保持有序的啊。嗯,那么在这个Y的情况下呢,我们,诶,比如Y中呢,我都出现A了,都是这个常量,很OK是吧?哎,当然先看第一个吧,在这个情况下呢,我们这样写是不是可以的对吧?那A是常量的话呢,B也是常量,然后呢,Order by c这是不是也行啊,这是常量,Order by b跟它可以,然后这个情况的话呢,这个情况呢也是可以的啊,也能够去使用这个索引,哎,当然具体应用的话呢,还得看这个表中的数据,呃,优化器呢,它要核算一下成本,但至少呢,它会作为一个选项啊来出现的。不能够去使用索引进行排序的。这这这乱序了是吧,哎,这就不行了啊,这是另外一个字段啊,这这不行是吧,那你这也没有A。呃,然后呢,这个A是个常量order by c没有B嘛,对吧?然后呢,A是一个常量order by ad多了个D是吧?那这就不能按照它排序了,A呢,In in呢就相当于还是个范围啊,范围就不行了是吧?OK啊。
29:04
行这呢,我们就说到这儿啊,具体的一些案例,然后呢,下边我们来看一个这个案例的一个实战啊,这个实战当中呢,其实就提到关于这个file和我们这个index这种sort的一个对比。接着我们来看这个实战,嗯,这块呢,也是咱们把这个学生表当中的这个索引呢,先都干掉啊。好,回过来。啊,这呢,相当于是我们一个实战啊。嗯,相当于测试一下,这个叫I felt。哎,So,哎,和我们这个index的这种排序行。首先的话呢,把我们这个student表中的这个呃,索引啊,先干掉啊,然后。嗯,这里边儿呢,说查询这样的一个需求是吧,来我们CTRLC一下拿过来。嗯,查询我们这个学生表当中age呢是30,这个呢小于这么多,然后order一个name啊,你现在都没有这个索引了,然后这块呢,查询肯定就啥玩意没有了,这个是是吧,OK。
30:01
行,然后呢,就诶这块我们跑一下这个情况啊,这块做一个执行。走起。哎哟,这个其实速度还挺快的哈。这么多是吧,嗯,再跑一遍吧。行,差不多啊,就稳定到这个数值范围啊,行,那这个呢,我们就OK了啊,然后呢,这个type是all,这个呢是file out,嗯,接着的话呢,我们说为了呢,去去掉咱们这个file,咱们去建立其中的一个索引啊。嗯嗯,为了去掉咱们这个file的话呢,我们建立一个索引,怎么建呢?嗯,你看看这呢是个age,这呢是name,这个是个范围啊,范围的话呢,我们就不考虑它了,嗯,这个因为涉及到范围了,所以我们建立一个age和name啊构成的一个缩引啊或这呢叫create。An index。啊,叫age和name on。Student age。对吧,哎,没问题啊行,那这样呢,我们选中啊做一个执行。
31:04
创建好以后的话呢,我们再啊CTRLCL一下,咱们把这个呢,哎,拿过来我们做一个啊plan。行,大家看到是不是我们就应用上这个索引了是吧?哎应用上以后的话呢,我们后边呢,是不是也没有这个叫using fair了,哎,所以我们就把它给去掉了,哎去掉以后的话呢,这个LAS呢,写的是个五,相当于我们还只考虑这个他,哎他还没给我们这个内幕这个面子是吧?啊相当于他觉得呢,这个剥滤完以后的话呢,这么多数据呢,直接呢进行一个。哎,汇报操作啊,他觉得这个性能挺高的啊,就成这样一个情况了啊,来我们这块呢,把这个执行一下,看它的效率情况啊来走起。好,这时候我们会看到这个效率呢,是不是比我们刚才啥也不用的话呢,确实要快一些是吧?啊没问题啊,嗯,这个呢,是我们把这个file呢,相当于是给去掉了。嗯,那这个去掉的话呢,它就一定比我们不去掉这个性能要高吗?来这块我们再去啊方二一下啊。
32:02
我呢,再去建立一个索引啊。Index,嗯,我建立一个age,注意看啊,Student number,然后来一个name是吧?啊啊,Student针对于我们的age。啊,Number,还有我们这个name,好大家看此时的话呢,我们建立这个这个索引呢,是先有age,后有这个student number后有我们这个name,啊这样的一个三个字段构成的联合索引。嗯,好了,那建立完以后的话呢,我们再去CTRLC,然后呢跑一下。再重选啊选中哎执行好,大家看这个时候的话呢,我们这时候呢,是不是又出现这个叫using file了啊,出现我们这个叫文件的一个排序了,是吧?然后前面这块呢,写的是这个九啊相当于我们用的是谁呀,用了age是不是用的student number啊用了这哥俩了。嗯,Age的话呢,应该是五吧,然后呢应该是四是吧,所以加一起呢是九。
33:01
对吧,咱们看一眼啊。嗯,往前边走,Student。哎,这个是吧,哎,它确实是四,然后这个呢,就是哎这不是四个,四个字节,再加上一个闹的这个情况就五个呗,这个是no闹的,所以它就是四个行没问题啊行,这是九个,相当于呢,咱们这里边呢,使用的就是这两个了,他没有考虑我们这个autobi,所以呢这个,所以排序的时候呢,那它就要file是吧?呃,那么你这块有file了,我们的理解是不是你这个性能呢,按说应该差一些,对吧?哎,这块我们选中了做一个执行。好,大家看你会发现呢,我们这个的结果呢,诶反而这个时间呢就更短了,诶我们叫这个0.002啊,你看比我们上边这个时间呢,是明显要短的,这个都查出来这个数量级了,所以这个呢明显是要好一些是吧。嗯,那这个事儿我们该怎么去解释啊。怎么解释啊?嗯。嗯,你看呃,因为呢,我们这个where啊,我们经过它过滤,然后呢,这个呢,你看咱们咱用那个啥都根本都没没理它,没屌它是吧?啊直接呢,上面这个情况呢,哎,只考虑了一下我们这个地址。
34:08
啊,只考虑H,然后这个呢,都没考虑的name幕,当你现在呢,又多了一个索引,注意我还没有把这个索引删掉哈,是多了一个索引情况下,他毫不犹豫的就奔向了我们这个索引的怀抱,哎,言外之意呢,长异化器就认为说,哎,你这俩构成了索引啊,其实这块呢,有没有我们这个name幕呢,是不是都无所谓了。哎,从这个结果上来看呢,本身人家99啊,跟你这个跟你这个name在不在,是不是一点关系也没有啊。OK,哎,我们经过这两个条件呢,合在一起构成这个索引呢,查询的这个结果数量就很少了。那你看这块呢,是不是就这么少了,哎,那就很少的话呢,这个order呢,也不用考虑了是吧,直接呢,我们就排排序就完事了,针对这十条数据啊,在内存当中进行排序,那效率呢,那我觉得挺高的是吧?哎跟我这个直接拿着它呢,诶过滤完以后数据排序相比,肯定这个要更高一些。也就是说呢,我们得到这个结论,就是说并不是我们看到filea的时候呢,大家就呃一定要很紧张是吧,非得把它改成没有file的。
35:04
啊,注意注意一下我们说的这个事儿啊。这个呢,我们就说完了,诶刚才的这个解释呢,也在这儿了啊,就是我们这个条件过滤的话呢,已经能够干掉大部分的数据了,所以呢,即使我们呃这个排序上呢,有这个索引呢,可能也没有去这个真正使用啊。啊,结论的话,我们看一下两个索引如果同时存在MYSQL呢,这个会自动的选择最后的方案啊,在我们这个类当中呢,相当于是他就用这个了是吧?诶但是随着数据量的变化呢,选择索引呢,也会随之改变,对这是一定的啊呃,那如果说我们这个呃,经过前边这两个字段啊,就是比如说呃,我们这个表中哈,经过你这两个字段之后呢,这个数据量呢,还是极其的庞大啊,那其实不排除呢,我们会考虑这个内蒙字段的一个使用的。啊,这个注意啊,因为现在呢,他觉得没有回没有回表呢,你没有我直接呢,呃,在那中去排序快了是吧。说当这个范围啊,条件和勾BYBY啊这个字段出现二选一的时候,优先观察呃,条件字段的过滤的数量啊,如果过滤的数据量啊,数据呢足够多啊,而需要排序的数据呢,并不多的时候呢,我们就把所以呢放在这个范围这个字段上了,哎,说的就是我们。
36:14
刚才说的这个最后的这种情况是吧,哎,人家这个过滤完以后呢,这个数据量就很小了。啊,所以人家就选择那个这俩这哥俩构成的这个索引了,对吧。来这个来啊。好,然后下面这个说,如果呢,我们要是没有使用这个age student number和这个name构成的这个三个字段索引,我们建立一个这样两个构成索引可不可以,当然可以了啊,当然可以了。因为本身其实大家看这是不是也能看得出来,来我们这块呢,执行一下试试啊好,首先呢,我们去drop一下,咱们刚才创建这个索引,然后呢,我们在创建两个资源构成的这样一个联合索引。啊,然后呢,我们再去这样去做个执行,其实是一样的效果是吧。啊,一样的效果啊,没问题。啊,你要想运行的话呢,你就跑一下到这儿。哎,走一下啊,是不是也OK是吧,好的。
37:02
行,那么这里边的话呢,我们就提到了这个叫fire sal这个问题,那那么针对这个fire sal的话呢,咱们来谈谈这个在设计它的时候呢,使用的这个算法啊,这个大家呢,也熟悉一下这个five so的话呢,有两种算法啊,这个主要是针对于mal版本不同造成的啊,一个呢叫双路排序,一个呢叫单路排序。哎,单路排序双路排序,这个双路排序呢,主要指的就是我们需要呢,来进行两次这个加载啊,两次加载,比如说呢,我们针对于这个叫,呃,咱们叫比如说age吧,是吧,Order by age啊。然后就简写了啊order by,哎如果呢,我们叫order by age的话呢,哎,我们需要呢,进行两次磁盘的扫描,首先的话呢,需要呢,找到你这order by的这个字段,哎,我们是不是叫age啊,然后呢,把你这个所有的age这个列呢加载到内存当中。然后在内存当中呢,针对A啊进行一顿排序是吧,排下序以后的话呢,然后我们通过这个A呢,再找到你这个表中的对应的那个完整的那一条数据,再把你完整的那条数据呢,哎,再取到我们这个内存当中。
38:03
啊,就是这样一个情况是吧,从磁盘中再取这个,哎,所有的这个字段是吧,就是这样一个情况,所以呢,它需要两次这个磁盘的扫描,第一个呢,是取我们这个out by的这个列啊排完序以后的话呢,我们再针对于这个排好序的这个字段呢,去我们整个磁盘中呢,取这个其他的字段。哎,就是这样的情况。两次扫描啊,嗯,比较耗时是吧?呃,因为IO的这个次数呢比较多,呃,那么之后的话呢,诶对,4.1之前用的是它啊,4.1之后的话呢,我们用的是这个叫单路排序了12D呢,就是只扫描一次。那这扫描一次是怎么做呀?诶我们就不是第一次先把order这个列呢拿过来了,直接呢,诶把order呢这个列所在这个B呢进行排序,说白了就是上来呢,就把磁盘中读取你所有的这个列。哎,把所有列呢,就直接读过来,然后呢,针对你这个autob age这个字段呢进行排序,哎,所以呢,就一次啊,当然我们能看到这个点呢,就是说这个对内存的要求呢,是不是要更高一些吧。对吧,更高一些啊,他也能够把这个好处呢,就是速度快了啊,把随机IO变成了数序IO,为什么呢?因为你从磁盘中读的时候呢,你这个数据呢,全在磁盘中啊,这个咱们比如说数据页呢,都在这个相应的这个区里边,然后呢,是不是一次性的就全读过来了,就是这就顺序IO嘛,而你上边这块呢,是先排好你这个A的这个字段以后呢,再回去呢,拿着每一个A啊,回去找你表中的这个具体的这个数据,那有可能这个跟下一个是不是就在不同的页当中了,这是随机业务嘛,哎,那你这个肯定性能要慢一些是吧。
39:29
哎,所以这个呢,是不是一看就肯定很靠谱是吧?啊再一个的话呢,你来看它是4.1之后用的,那肯定是后出现的,一定比先出现的这个要好嘛,它是一个替换关系对吧。啊,其实也不算严格的一个替换关系了,因为这个也有好处,就是它对内存的占用要小一点啊。嗯,那这里边儿呢,就提到了说这个,呃,单路这个排序的这种算法呢,它占用的这个空间要比较多。嗯,那这时候呢,我们就要担心这个容量够不够是吧?这时候呢,提到了一个叫salt buffer这个容量的问题啊,那如果说我们这个容量不够的话呢,就很恶心了啊,容量不够那就意味着我们排一部分呢,还得这个呃,给大家写出去,写出去呢,然后呢,再排下一部分再写出去,来来回回,反而这个IO的次数还很多是吧,反而会造成大量的IO操作啊,得不偿失了。那怎么办呢?我们就要。
40:15
考虑是不是增加这样的一个容量的大小,这个呢叫salt buffer size啊,默认情况下呢,这个哥们的大小呢,是一兆的空间。啊,那如果呢,我们这个,呃,要使用这个file了。哎,相当于呢,就是如果大家呢,咱们上面不是说了,呃说呢,你要是在开发当中啊,往上找一下。所以呢,如果在开发当中呢,我们尽量呢去使用这个index方式呢,去完成这个排序,但是你要避不开了啊,无法使用它呢,只能用这个filea的方式,那怎么办呢?那是不是要对它进行调优,怎么调优啊,诶这里边就提到这个方式,是不是我们要增加这个叫哎叫salt buffer这样一个空间的大小是吧。哎,OK,呃,这是一个啊。这个把它空间调大的话呢,我们就能够在单路排序的时候呢,你就尽量能够避免它反复的去在。
41:04
写出到磁盘里面啊,然后第二块呢,我们可以呢,尝试着叫提高这个max for data,诶这个变量的值影响是影响的是什么呀,它影响的就是我们到底选择的是这个单的还是这个。嗯,在这呢是吧,它一响是我们选的是单路的,还是我们这个双路的。啊,就是我们这个要查询的这个字段啊,查询这个字段的这个长度呢,跟我们这个变量呢,去做这个对比啊,如果我们这个字段的这个长度呢,比较短啊,那这时候呢,我们也可以考虑呢,是用这个双路的,是用这个单路的方式了。啊,如果我们这个字段比较长啊,已经大于我们这个变量的话呢,我们就考虑使用这个双路了,那相应的相应的这个性能呢,就要稍微差一些是吧?啊OK啊。呃,那这块的话呢,我们要查询的字段嘛,哎,那这块呢,我们说呢,这个字段呢,跟这个量做对比,诶当然小一点的时候呢,能用单独要好一点,那这时候怎么办呢?是我们在outb的时候呢,大家尽量就不要out select克星了啊,你select星就意味着你要查的段特别多,那就有可能是不是超出这辆,超出它的话呢,是不是就开始用双路了。
42:03
OK啊行,那这样的话呢,我们就把这个排序的这块呢,就讲清楚了啊,应该说这里边呢东西呢也不少啊,大家下来呢,再去捋一下。
我来说两句