00:00
好,那承接前面的内容啊,咱们接着来讲这个第十章叫做索引优化与查询优化啊,那么前面的话呢,我们已经讲了关于索引,比如它底层的这个B加数的这个结构啊,相应的我们还跟其他的,比如说这个哈希索引啊,B数啊这样的结构,我们还做了对比,知道了什么叫句速索引,什么叫非句子索引,或者叫二级索引,对吧?我们知道了应该在哪些字段上呢,适合加索引,哪些字段上不适合加索引,以及呢,我们在代码层面该如何呢?去写啊,去创建啊,一个表中的索引,包括呢,去删除索引这样的一些行为,对吧?我们还谈到了是不是说相关的一些调优工具啊,我们上一章讲的,那么这一章呢,我们来讲一讲,叫索引优化与查询优化啊,那么SQL优化的话呢,其中很重要的一块内容呢,就是关于查询的优化,那么在查询优化当中非常重要的一块内容呢,就是关于索引的优化,所以这一章呢,我们重点呢,是从经验的角度呢,我们来谈一谈这个索引和查询的这个优化问题。OK行,那么我们首先呢,先泛泛的讲一讲,说都有哪些维度可以支撑我们去实现数据库的调优啊,我们先简单的去描述一下,咱们下下章当中啊,还会进行一个整体的汇总,那首先的话呢,我们提道了关于比如说索引失效了啊,这个当然我们知道了,如果索引失效的话呢,这个诶我们相应来讲啊,一般情况下呢,查询的效率呢,就会差一些,索引失效了或者没有充分的去利用到索引,那怎么办呢?啊,你要是没有利用索引,或者没有键索引,那就是去建立索引,那索引失效的话呢,那可能就是由于我们错误的这种SQL这个编写方式导致的,那我们呢,就可以呢,去注意相应的一些这个准则,对吧?这呢是我们这个第二节呢,主要给大家去讲解的,那么下一点的话呢,说关联查询太多的照。
01:36
啊,那我们呢,如果涉及到这个多表的查询的话呢,是需要用照安的,一般的话咱们说最好不要超过三张表,那超过以后的话呢,相应的性能呢,也会受到一些影响啊,那我们呢,一旦发现这个照样的表过多的话呢,适当的做一些社会优化。啊,包括呢,我们下一章呢,会给大家讲到这个范式啊,那么呃,为了减少这个过多的这个照影操作呢,我们可以呢,去叫反范式化的一种设计啊,增加必要的一些冗余,然后实现呢,就诶不用太多的一个招唤行为了,OK,下一章我们再去讲,然后的话呢,我们还会提到关于服务器的一个调优和各个参数的一个设置。
02:10
比如说呢,我们去设置相关的这种缓存的一个大小啊,设置相关的这个线程数的这种多少是吧,诶这样的方式去调整点CF或者买点ni是吧,主要呢,就看我们是在哪个这个这个平台上呢,去搭建的我们这个呃服务器了啊这个MYSQL数据库服务器,好,那我们这一章当中呢,其实会涉及到了关于一些参数的设置,那这个更多的参数设置的话呢,我们在下下当当中呢,去给大家去讲解。啊,再者的话呢,我们还会面对的,比如这个数据过多的问题,比如说我们这个SQL调优啊,已经是到达这个极限了啊,这个SQ优化呢,已经不能再去优化了,那如何呢,去面对这种高并发的这种访问的场景,怎么办呢?我们就可以提供更多的更多的这种服务器是吧,实现了对于这个数据库的一个分库分表的方式啊,主从复制,读写分离这样的一些行为呢,我们去分散这个服务器的这个压力啊,这样的一个行为,这个咱们在这个呃,下下篇当中啊,咱们给大家呢去做这个讲解啊,就是在我们这后边这个内容当中,好,这个我们拉回来。
03:06
那么整体上来讲的话呢,关于数据库调的这个知识点啊,应该说是非常的分散的啊,说不同的数据库管理系统啊,这个比如你是MY啊,Oracle啊是吧啊等等啊,由于不同的管理系统呢,它底层的设计的原理不太一样,所以我们具体的调优方式的话呢,也会有一些不同,包括呢,在不同的公司,不同的职位,不同的项目当中,咱们遇到的问题啊,也是不尽相同的,诶所以这里边呢,我们主要呢,针对这个MYSQL,咱们分成了三个章节呢,给大家去讲解,一个呢,是咱们当前这个关于索引优化和这个查询优化,然后下一章呢,我们提到关于这个范式啊,ER模型啊等等,包括这个反范式是吧,然后呢,我们再关注一下其他这种调优策略,主要呢,通过我们这样的三个章节呢,给大家呢,做一个细致的讲解啊,应该说呢,这一章啊,大家能够看到呢,这一章咱们这个词数呢,也不少,将近这个3万这个词啊,这个应该说呢,这一趟也花了我很多的这个精力呢,去做这个整理哈,呃,这个整理的话呢。
04:02
花的时间又比较多,呃,然后录的话呢,呃,主要还是希望呢,能够以更加的这个通俗易懂的方式啊,包括呢,辅助一些案例的方式给大家呢去讲解出来啊,应该说呢,呃,在你学这个缩印化之前,跟你学完这章之后的话呢,功力上应该是有一个大的提升的。好,那这个多余的不多说了啊,我们接着往下看,说这个思后查询优化带的技术啊,有很多啊,这个包括面试的时候呢,你也可以跟对方呢,是不是先抛出这样的一个大观点是吧?叫大处着眼啊,那下边的话呢,都是小处着手了啊,那么大处着眼呢,就是大方向上来讲,我们关于查询的优化呢,主要呢是有两大方向,第一个呢,叫物理查询优化,第二呢叫逻辑查询优化啊前面咱们在讲某一章的时候,我提到过这两个词,对吧?那所谓的物理查询优化呢,你可以去想,实际物理磁盘上,你像我们创建一个索引,是不是我们对应那个ID文件中就会有这个索引的相关的这个空间了,对是吧?所以呢,就是物理层面的一个优化,那我们可以呢,通过这种索引或者这种表连接的方式啊进行优化,这个呢,也是我们重点要掌握的啊,尤其是这个索引,那么关于这个逻辑查询优化的话呢,这就是仁者见仁,智者见智了,那不同的人啊,积累的这个SQ开发的经验的不同,那么这时候你写出SQ的这个质量呢,也是不一样的,就让我们去写编程语言里边实现一个具体的算法,也是同样的道理,对吧?啊那么好。
05:20
好的,这个circleq的话呢,我们查询的效率就会高一些,那差一点的话呢,查询效率呢就会低一些啊,那我们可以呢,通过这种circleq这个叫等价变换的方式呢,去提升我们这个circleq的一个质量啊提升circleq的质量,包括呢,我们提到了叫呃这个my circleq的是不是叫查询优化器,它也会对我们这个circleq啊进行个优化之后呢,是不是进行一个呃测库的重启是有可能出现的,咱们在上一章当中也提到了,咱们写的是一个子查询,是不是就给我们转换成了一个,是不是这个叫内连接的方式,对吧?诶当时咱们也提到过啊好,那这呢就我们范盘的一个概述,那么为了我们这张呢,更好的去实现一些效果啊,所以我们还是要这个提供一些数据的,这里边呢提到了两个表,一个呢叫做class,叫班级表有1万条数据,然后学员表的话呢,一共是有50万条数据啊,那这个创建这个表,这个提供数据啊,模拟数据,这个操作呢,我把代码都放在这儿了,那首先呢,把这个过程呢一起来做一下。
06:13
好,那回到咱们这个远程连接的150MYSQL8.0的这样的一个MYSQL数据服务器上啊,首先的话呢,呃,首先咱们先创建一个文件吧。嗯,这个文件呢,咱们是该到零六了吧,啊,这个咱们叫A索引啊优化啊。哎,与这个叫查询优化。啊,这呢,我们把这个文件呢,先给它保存一下啊,CTRLCCTRLS。啊,这个我们保存一下啊,到咱们的这个代码啊,是该这个零六了啊好保存一下。好,那么首先呢,第一个问题呢,我们是叫哎数据的一个准备工作。啊,准备工作啊,那这里边呢,我们去创建一个新的数据库啊,这个我们这一章里边呢,其实还涉及到其他的一些表,所以我们就不跟这个艾特硅谷DB去掺和了啊,我们再create一个啊,Data base啊,At硅谷啊,DB2啊这样的一个数据库,行,我这也不指明这个字数集了啊,默认的几个我们参数配置的那个U8。
07:08
啊,这个MA458的零论不用我们显示配置了是吧,默认的就是UTL8了啊5.7的时候呢,就跟我们自己配置的那个配置文件里边,比如诶UTL8呢是一样的了,行,然后接下来的话呢,我们就创建这个表啊CTRLC一下。啊,这呢是电表。好转过来,这呢,我们是创建两张表啊,这个是班级表。啊,这个是学生表啊,走起,那这里边呢啊学生表哦哟,这个咱们是还是用的at特硅谷DB1了是吧,这个忘了指行这个操作了啊,我们去use一下啊,哎,在硅谷DB2是吧。嗯,这个class的话呢,其实用不着啊,F5刷新一下,这里边帮我们创建了class,这个那留不留都行,我这呢给它删掉吧。好呃,先选中一下我们这个执行啊,选择呢,我们新的这个数据库,然后这两个表的话呢,你可以同时选中呢,给他去做创建啊走起。
08:03
这里边的话呢,关于我们这个student和这个class呢,有个外界啊,这个大家加不加都行啊,就是不影响我们后边这个呃,数据的一个测试啊,行,那表创建好以后的话呢,接下来我们要做的事呢,应该是先创建函数啊,函数呢提供我们随机字符串啊,或者随机的这个编号啊,然后呢,我们再去创建一个存储过程啊,同样的啊,这个函数的话我们要创建呢,呃,由于我这块呢,已经重启过MYSQL数据服务器了啊,所以这块我必须要把这个打开是吧,前面我们已经多次出现过这样的一个情况。啊来我们选中。啊,执行啊,就相对呢,让我们输入服务器呢,去信任咱们关于函数的一个创建,首先呢,我们要产生随机的字符串啊CTRLC。啊,我们这个表里边儿呢,会有一些字符串的信息,我们就需要呢,通过这个函数呢,去给我们随机生成这个字符串的这个值。好,产生这个随机的编号啊,CTRLC。就是这几章里边呢,我们只要呢,涉及到这个模拟数据的这个创建啊,都会用到这样的这个函数啊,只不过这个函数有的这个细节啊,我提供的不一样,有的呢是从哪到哪啊,有的呢就没有写具体这个范围了啊哎,就是直接呢是个空餐的。
09:12
行,然后接下来的话呢,我们去创建这个存储过程,呃,这个存储过程的话呢,是我们往这个student这个表里边呢,去添加数据的这样一个存储过程。那这个我们就直接呢去先跑一下。好了来注意这时候呢,并没有真正的帮我们去创建数据,只是呢,我们把这个创建数据这个功能呢,先提供好。哎,从这到这啊,选中执行。好OK了,那创建完以后的话呢,下边我们是不是要真实的去创建这个数据了,那我们往这个叫班级表里边呢,添加1万条数据。哎,这是1万条数据,好,选中了走起。好,然后呢,往我们这个学生表里边呢,我们去添加50万条数据。啊,这个50万条数据呢,时间会稍微有点长啊,我们先选中了,让他跑一会儿。
10:04
好这个呢,我稍微的先按一个这个暂停。好,那这时候呢,我们两个这个存储过程呢,就都执行完了啊执行完以后的话呢,我们首先呢,先去做一个这个select,诶看一下呢,是不是把这个数据呢,添加成功了,首先呢,我看清一下,诶from下我们这个叫class。哎,选中。走起。好,这那是不是1万条数据,然后接着我们把它那CTRLC复制一份。啊,针对我们这个student。啊,这个表好选中。哎,大家你看一下我们这个执行的时候呢,他是不是需要花一点时间啊走起。诶这个稍微还还还算是比较快的啊,诶你看它其实是花了一定的时间了,就是呢,我们在这个in DB的这样的一个存储引擎当中,我们呢,去做这个查询的时候呢,它就是真实的呢,需要去诶扫描一遍啊,看看是有多少条这个数据,不像呢,这个MYSM当中呢,直接有一个变量呢,记录一下我们这个表中的这个记录数啊,那个呢,花的时间呢就极少了,约等于就0.000了是吧?好,那这样的话呢,我们这个数据呢,相当于是添加成功了啊,然后的话呢,大家会发现我现这块呢,还有一个这个存储过程叫删除某表上的这个索引啊,这个呢,咱们暂且呢先把这个呢执行一下啊,目前呢是用不着一会儿呢,我们就用着了。
11:13
这个是干什么用的,说一下啊。在我们这个存储过程当中。那需要呢,传入两个参数,第一个呢叫数据库名,第二呢叫表明你把这个传完以后啊,如果呢,都是真实存在的数据库和这个表的话呢,我就把这个数据库中这个表里边的所有的索引啊,当然这块呢,你要是有个主键的话,主键就不给你删了啊,诶把这个索引的话呢,就都给我们删掉了,诶同学说诶要这个存储过程是干什么用啊,就是咱们下边在演示的时候呢,经常的会创建好多索引,然后再往下去演示一些效果的时候呢,可能需要呢,把前面这个索引呢给它删掉,那要是一个个去删呢,还挺麻烦,那此时呢,我们就可以去调用咱们这样个存储过程。啊,去实现一个删除就OK,好这呢我就把它执行完了啊执行完以后呢,以后的话呢,我们来这块呢,稍微的看一眼啊爱的硅谷DB two啊存储过程看一下啊这个呢,是不是也有了行没有问题,好那么这样的话呢,我们就把这个数据呢先准备好了,然后接下来的话呢,我们先给大家呢去讲一下关于这个索引失效的这个案例。
12:07
啊,然后的话呢,我们再看看一下,针对于这个呃查询语句啊,我们谈一谈,比如关联查询,这是很重要的一块内容子查询啊是吧,关联查询子查询这都是大块的内容,包括呢,我们在整个呢,这个查询句当中可能会出现叫排序,出现group by,包括出现这个limit啊还有呢,叫覆盖索引啊,组串添加索引,呃索引下推啊面试的时候呢,这还是一个高级的问题啊等等啊这些点呢,我们再分别展开呢,去给大家讲解啊。好,那么数据的话呢,我们就创建好了,接下来的话呢,我们看一看这个第二节叫做索引失效的案例,我们先来从这个索引啊,在这个表中呢,诶起不起作用啊,为这个突破口,我们去做这个讲解,那么在前面呢,我们已经提到了啊,这个要想能够提高我们查询那个效率最有效的方式呢,是不是就设置合理的索引,对吧?啊,那么通过索引的话呢,我们能够快速的去定位这个表中的某条记录,那比如说这个记录的话,你是存在我们具体的这个叶子节点的这个页当中,我们就直接呢把这个页呢,是不是加载到我们这个内存中就可以了。
13:05
对吧,那从这个磁盘中呢,做一个加载就可以了,那如果说你要没有使用索引的话呢,我们就需要扫描表中的所有的记录,哇,这这个就很崩溃了,那所有的记录的话呢,它存放在不同的页当中,那如果说这一页呢,还没有在内存中进行加载,那我们还需要呢,把它们都加载到内存当中才可以啊,这是极其崩溃的一件事情啊,那所以说呢,我们在大多数情况下下呢,咱们进行这个查询的时候,尤其是你这个表数据量非常庞大的时候呢,我们是一定要考虑用索银的。啊,那么这里边呢,咱们的讲解呢,主要都是以这个B加数呢来进行说明的,像这个度DBMY呢,诶默认的都是用的B加数,那对于这种空间类型的话呢,它用的是这个R数,然后呢,这个memory呢,默认的支持是这个哈希算法为主的这个哈希索引,啊这呢咱们就不提了啊,咱们就以这个B加数呢来给大家做这个讲解。好,呃,那么这个当然这个B加数的话呢,MY当中跟印度DB还有区别,咱们前面也说过了,这个MY当中它是数据跟索引呢是分离的,它不存在这个句式索引这样的概念,咱们这块的讲解的话呢,它主要呢,是以这个呃in DB啊为主来进行讲解,OK,好,那下边呢,提到说用不用索引,最终呢都是优化器说了算。
14:14
啊,优化器说了算,就是我们呢,这个写完搜Q以后,上面你也有索引,但是呢,优化器最终发现呢,说我不用索引的成本可能会更低,他可能还真就不用了,哎,那你这一块呢,再看一下呢,你这个是不是需要去修改一下你这个索引的这个设置,为什么导致加了索引之后呢,结果还还不如不用索引的是吧?诶这个你要做一个思考啊,诶所以呢,我们要讲这么多的案例啊,通过这个案例的话呢,大家去,呃,相当于是发现一些优化器的一些规则啊,那这优化器的话呢,它是基于什么的,优化器怎么做的选择呢?这里边说是基于这个叫成本开销的啊这种这个规则它不是基于这个规则式的,也不是基于这个语式的啊,基于基于成本开销叫cost。啊,那这时候大家一定要注意我们这个开销呢,不是说这个时间长短啊,这个你看一下咱们讲这个第九章的时候,咱们呢,是不是说到这个explain,这个explain呢,说它有几种格式,那其中这个格式呢,是不是它主要呢,就是来呈现给我们这个叫成本的开销,对吧?比如我们这个查询成本呢,是这么多啊,这个没说这个对应的是秒数啊,诶就是一个成本的一个数值单位呢,你可以理解成是一。
15:15
然后呢,这是针对这个外层查询呢,你看这个数值加这个数值,这俩加一起呢,是这个数值,然后这个数值呢,再加上我们后边这个内层这个查询里边的,呃,这个相当于是这俩数值。啊,就是加一起是吧,一共呢就是这个数值,这个数值不就是我们最外边,呃,上面写的是这个数值嘛,对吧,那这个呢,就叫一个成本开销,那里边呢,涉及到了比如叫read cost和这个cost这两个值的话呢,咱们上一章的时候呢,也提到过了,是不是它有具体的这种计算公式,公式里边呢,涉及到了我们这个表中的行数啊,还有这个future的百分比啊,来做的这个运算得出来的这个结果,所以说的话呢,它不是这个单位,不是时间啊,这个大家一定要注意一下。好,那另外的话呢,说这个circleql语句是否使用缩引,跟我们的数据库的版本,跟我们的数据量,还有数据的选择度都有关系啊,什么叫选择度,你比如说我们要select星,这个呢,咱们说的一定要尽量避免在产品中用select星啊,这个一定会导致我们这个是不是要用的这个这个叫什么呀,居促索引了是吧,很多时候呢,这个selecting呢,是非常糟糕的一个选择啊,那比如说我们这时候呢,就选择这个selecting了,还是说呢,你就选了个select ID啊select的具体的一个,比如说你用的这个索引的这个字段,那其实呢,有可能我们就直接呢用这个叫居素索引了啊,就我后边讲的这个居住索引啊,就用不着呢,你再去回表呢,查询我们整个表中的数据了,所以呢,跟这个数据选择度有关系,还跟我们这个数据量呢也有关系,你比如说我们想查询这个表中的所有数据。
16:39
啊,这是一个选择,再一个呢,我就查询我们这个表中的这个,呃,比如limit一个十,我就查询十条数据啊,有的时候你会发现呢,我们要是不加这个limit,咱们后边也有这样的例子啊,你要不加这limit呢,是不是就意味着我们对这个表中的所有数据进行一个查询,对吧?那这时候在查询的时候呢,即使我们这块比如有个字段,诶上面有索引,那有可能优化器呢,最终也没有选择这个索引,而是选择了,哎,我们这个tap呢,叫做奥。
17:03
啊,Type就我们这个a Fla呢,在这个这个分析我们这个语句的时候呢,是不是有个字段叫tap啊,叫做访问方法啊,他就选择all了,相当于我们进行了一个全表的一个扫描,那他认为说呢,我用这个索引呢,这个效率反而更低啊,因为呢,你这个前面还写了个select星了,我用了这个索引还对这个回表进行全表的一个查询,那还不如干脆我直接全表查询得了啊,效率反而是更高一些是吧?诶就是反而不用你这个索引了啊,但是你要加上这个limit的话呢,说我只只要十条数据,那这时候呢,呃,这个优化器一核算时,哎呀,我要全面扫描的话呢,可能得有1000条数据或者1万条数据呢,需要去查找,你那只要十条行,那我就用一下你这个索引吧,可能成本会更低。啊,这个呢,就是我们想强调的这个数据量多跟少也是有区别的啊,所以呢,咱们在这个造表的时候呢,大家会发现通常我这块造的这个数据量呢,可能都不算小啊,一整呢,是不是就10万级别以上了,对吧。OK啊,当然另外一个角度的话呢,还是因为咱们下边呢,去演示用索引跟不用索引的时候呢,也想看出来查询的时候这个时间差,所以这个数据量呢,尽量呢稍微的大一点啊,效果呢能明明显一些啊好,那下边的话呢,我们就来看具体的,我这里边儿一共列出来了,属于要11条这样的一个规则。
18:10
啊,我把相应的一些规则呢,也做了一些合并啊,那么大方向来讲呢,一共是有这样的11个规则,那我们就一个一个的进行说明啊,这个我们把它的CTRLC先粘一下啊过来。啊,这是我们的这个第二个点啊,第二点里边呢,我首先我们说这个第一个问题啊,这个我写一个。一小,诶这样吧。这个的话呢,叫全职匹配我最爱是吧?哎,这个名字的话呢,诶大家能看懂吗?哎,全职匹配我在我最啊首先的话呢,这里边我写了三个叫explain啊,那我们把这块呢先粘过来,针对的都是我们这个student的这个表,那么student这个表啊,咱们首先呢,先搂一眼,这个表里边呢,我们有一个ID,这个ID的话呢,是一个主见,除此之外的话呢,其他的这些呢,No no啊no啊是吧?呃,也没有其他的一些这个所谓的约束了。啊,或者说我们没有其他一些所谓的这个引索引了是吧?好,那这块的话呢,我们针对于这里边的,在没有创建新的索引的情况下,你看这时候呢,我们去斯兰也查询这个叫age啊走起,那显然的话呢,这时候你看没有任何的这个,哎,我们说这缩引可用了啊,所以这块呢,Type看也是个O啊这呢也是个no是吧?那下边呢,是不是同样道理啊,哎,走起那也是是吧,然后在下边这个呢,再走起一下啊,也是同样道理啊这呢就没有任何的这个索引可以去使用啊,没有这个using index啥的是吧?好那么在这种场景下呢,我们去创建一个。
19:31
哎,这个呢,相当于是做了一个执行啊,就是针对的这个执行。啊,那咱把这个也跑一下吧,哎,CTRLC一下我粘到这儿查询一下我们这个表中的这样的一条记录。啊,这呢,我写了三个这个啊,这个条件啊,走起。最终的话呢,没有查询数据花的时间的话呢,在这呢也有罗列啊,这个在这是吧,嗯,总数这个时间啊,就看这个吧。好,我这个呢,稍微的盯一下啊,放在这儿好,那么接下来的话呢,我们去给这个表啊,去添加必要的一些索引啊,CTRLC一下,这索引的话呢,你看我写了这三个啊,咱们就一个个来说创建索引的这个语句的话呢,这个不是咱们现在要讲解的这个重点了,前面已经都讲过了,好,嗯,这里边儿呢,你看我们针对这个age的话呢,我们是不是创建了一个缩引来我们走起。
20:17
大家会发现呢,我们创建索引的过程是不是要花时间对应呢,我们在底层的话呢,是不是对应那个ibd这个文件当中呢,是不是就要变大了,那因为所以呢,要存储这个数据在这里边了,好,那这个索引创建完以后的话呢,接着咱们看一下,诶针对我们这个s select查询的话呢,其实它这个时间呢就会变小一些。嗯,先先看这个吧,我们先看这个啊,走起。好,大家看这个时候的话呢,你发现呢,我们这个呃语句的话呢,是不是就使用上这个索引了,它用的这个恰好就是我们刚才创建这个IDX这个A,因为我们这里边呢,是不是出现了H了,我就用这个呢?哎,所以啊,用上了这个是无是因为我们这个age本身的话呢,它是一个int类型的。哎,嗯,在这儿呢是吧,它是int类型的,然后呢,它又允许为这个闹值,那是不是本身呢,是占四个字节,加上一个一,是不是就五个字节了啊没有问题,好啊,这个呢,所以它就是个五好,那么在这个情况下的话呢,我要去执行这个select,应该说这个时间呢,是不是会稍微的少一点啊,大家会发现呢,是不是确实是少了,诶0.022是吧?哎,这是这个时间,行,那么接下来的话呢,我们再去创建一个索引,这个时候呢,我们把age和class ID呢,诶都计算在内啊,我们走起。
21:27
哎,都走起啊,哎可以了,那创建完以后的话呢,我们再去做这个plan,此时的话呢,我们有两个索引,大家你看一看它会选择哪一个,哎,我们再选择执行,好大家看,哎,我们现在有两个缩引,他选择的是不是我们这个稍微长一点的,把age和class ID呢,是不是都算上了。哎,都计算上以后的,哎这样的一个情况,那理论上来讲的话呢,我们再去执行这个select的时候呢,它就会更诶快一点。啊,还挺给力的是吧,0.001啊,也确实看到了这样的一个效果。啊,这个效果出来了是吧?诶我们这时候呢,相当于是用的这个时间呢,就会更短一些,那为什么说在这两个去比较的时候,我们选择这个了呢?呃,因为呢,你看我们构建的这个B加数的话呢,是不是先按照age进行排序,然后再按照class ID进行排序,而这个的话呢,是不是就没有再按照classd排了,那我们要是找到这个age以后呢,下边的话呢,你就没法去使用了,而我们这个已经有序的话呢,你是在age的基础之上啊,比如我们这个找到A是30了,A30的话呢,假设是我们对应的有这样的几条记录了,然后接着呢,在这里边儿你是不是在,诶如果要用这个索引的话呢,接着呢去看它这里边,诶不管比如我们这个是非页的节点啊,下边呢,是不是直接呢,就就有我们这个class ID,在我们这A是30的时候呢,你直接呢,看你这个class ID呢,是不是四是不是就可以了呀。
22:40
是吧,就比较简单一些啊,你要是用我们第一个这种索引的话呢,你找到我们这个呃,Index edge呢,是30的时候呢,它这个非节点中是没有这个class ID的,那这时候你还有这个条件,那怎么办呢?是不是只能是回表找我们这个主键了,诶所以他花的时间呢,就要更多一些啊,更多一些。好,那这个呢,是我们说这个事,然后接下来的话呢,我们再去create一个index,此时呢,我把age这个class ID和这个name呢全都算上,走起建了一个更长的一个联合索引。
23:11
诶,建立好了,建立好以后的话呢,呃,这时候我们再去执行一下这个select操作啊,走起。啊,这个呢,大家会发现这个时间好像更长一点了,是吧,来我们选一下我们这个plan呢,咱们做个执行。啊,大家看呃,这个呢是0.002啊,因为整个这个时间呢,已经是非常小了啊,0.001 0.002,其实这块呢,就是因相当于说呢,是没有本质的区别了,呃,主要原因呢,是因为我们最后呢,筛选完这两个字段之后呢,剩下这个字段的话呢,其实呃数据量已经极小了,或者说在我们这里边呢,根本刚才咱们查也没有查到数据啊,所以说呢,就区分不出来了,如果我们这个达到几百万条甚至几千万条数据,诶如果有这样的记录的话呢,应该会看到这个还是要更小一点。那这时候呢,我们会发现呢,这个长运化器呢,它是不是帮我们选择了最长的一个索引啊啊这个呢,诶就相当于我们刚才为什么选两个没选第一个的原理是一样的,呃,那基于第三个去选的话呢,我们是不是直接呢,就在这个索引的这个呃,B加数当中,我们直接呢,去找这个内是不是abcd是不是就可以了呀。
24:10
啊,这个要更好一些啊,更好一些啊行,所以这个呢,就是我们所谓的叫全职匹配我最爱啊,就是当我们这个外置条件中,你看有这几个,目前我们都还是这种等值的这种关系对比的时候啊,那我们呢,呃,你就可以呢,去创建关于这几个字段的一个联合索引,它的效果呢,呃,效率呢,就要比你创建其中部分的这个效果呢,是要好一些的,尤其是当我们这个数据量比较大的时候呢,大家就能看出来这样的一个情况啊这呢就我们要强调的第一个点啊,全职匹配我最爱啊,那么第二的话呢,我们想说的叫最佳做前缀的规则,这个咱们在前面讲解过程当中啊,其实呢,是说到过这样的问题的,哎,我们这时候呢,再明确一下这个概念。最佳左权K的一个匹配规则啊,举例啊,我们这儿呢,有一个例子,那我们把它呢,这个拿过来啊,下边的还有一个我们也先都拿过来啊啊CTRLC一下。
25:03
哎,这么着,哎,下边的还有一个啊,下边这个呢,是说是不是能够使用我们刚才创建的第三个那个索引啊。好,这个稍微小一点啊,我们看一下这个第一个,呃,这个时候的话呢,呃就表示我们不是使用这个查询缓存的,就是实打实的,我们去看一下,如果在没有这个查询缓存的这个情况下的话呢,我们看看这个应该是呃这个花多长时间是吧?呃就是这块呢,有这样一个语句啊,前面我们讲过,讲这个逻辑架构的查询缓存的时候说过。好,这个from student where啊,这个有个age,这里有个name。嗯,这有个age这个name,咱们目前的话呢,是不是有这样的三个索引吧。当然再加上一个咱们那个主键啊,主键与主键的那个索引啊,嗯,Age和name,现在是不是我们只有一个age的啊,那问一下这个里边呢,会不会用上缩引啊。你看是不是会啊,哎,只用上我们的age了,因为这个咱没有建立嘛,是吧,那第二的话呢,哎,我们走起一下是不是就没有了,哎,因为我们既没有class ID,也没有专门针对内的这个索引,哎同学说,诶老师这个不是有吗?注意这个词的话呢,我们联合索引当中是不是先写的age啊啊先写的age啊,后有的class ID,那就意味着我们这个B加里边呢,我们先按照age呢先进行排序,你这里边呢,虽然是存在着classd和的,但实际上是用不上的。
26:19
对吧,这个应该很容易去理解吧,我们这个B加数里边呢,你这个整个数形结构呢,我们是先考虑age age相同的话呢,我们再看class ID,你上来就克class ID,你说这个数能用吗?肯定不能用啊,对吧?哎,这个是不行的啊,行,所以说这个呢是用不上了,所以的最佳左前缀原则呢,你要是想用这个索引,或者想用这个索引呢,由于你这里边没有最左前缀的这样一个字段,所以就用不上啊好,我们接着看下边这个。这个稍微的有点长,咱们给大家换个行说呢,哎,Where有一个class ID,有一个age有一个name啊有一个age啊,有个age有一个name啊,有一个class ID,看着是不是挺好,那我们选中了做个执行好,这时候大家会发现呢,你看是不是使用上我们这个索引了,跟我们上面这个区别呢,就是我们加了一个A。
27:06
啊,效果的话呢,你看它就变成叫ref了,而你上面这块呢,用不上索引,那是不是就是个O啊。诶,显然的这个效率呢,就会差别比较大啊,诶OK,呃,那这呢,就是我们所谓的叫最佳左权缀的一个法则,就是我们呢,如果有这样的一个,呃,叫联合查询的话,那咱们这块呢,主要说的是这个联合查询,联合索引啊,不是这个单列索引了,在这种联合索引当中,我们这个先写谁后写谁呢,这个是有严格的不同的啊,那我们在查询当中要想使用这个联合索引,是必须要把左边这个字段呢,你得先考虑上是吧,然后呢,你可以再考虑上这个字段,然后再考虑这个字段。你也不能,你比如说大家看我现在再写一个啊,CTRLC一下。啊,我这么着啊,呃,在这里边的话呢,我写上age,我写上name啊,这个class ID呢,我就不要了。对吧,哎,我写上A写上内。啊,然后这里边呢,我还得把这个,哎,所以呢,咱们给它drop一下啊,避免了干扰一下啊drop index啊age这个我们是on一下哎,Student。
28:08
好,这时候我们选中了,执行一把。哎,可以了是吧,嗯,这个操作呢,因为咱们一会呢,经常性的会去做一些删除啊,或者做一些查看,哎这呢,我写个show吧,哎,Show index from一下我们这个student。哎,这是我们选中执行。嗯,Student,咱们针对的是爱的硅to啊哇,这里边索引还挺多了啊,我们这里边儿呢,你看一下啊,把这个给删掉了,咱们现在里边呢,其实主要有的应该是。这个和这个吧。啊呃,一个是主见啊,一个是他俩,一个是他这个是吧,也就是两个好,那现在的话呢,大家你看我们此时呢,去执行咱们这个操作啊,走起。这个操作里边呢,哎,我们是age跟class ID了,有age class I现在于选这个,那我把这个还得也得闪一下啊。帐篷。哎,大家你看一下我想说明什么问题。
29:01
啊age还有这个就直接这样选一下啊。哎,走起行,那那这里边儿我们自己创建,所以呢,其实就剩这个第三个了,那第三个里边的话呢,你看一下我们这个时候的它的一个选择情况啊,走起那这块呢,是不是选择我们最后这个了,但是这块呢只选了一个五。对吧,只写了个五,哎,为什么只有五的话呢?哎,那相应相应的就是我们只考虑这个age,它本身呢,是in的类型,四个字节加上一个闹是不是就无了,然后在后边呢,你看我们有内幕怎么也没考虑到呢?因为呢,你没有写这个class ID,在我们这个B加数当中是我们这个A呢,相同的情况下呢,我们再往下看呢,是先来看这个class ID,再去考虑这个name的,你现在呢,都略过这个class ID呢,直接去找name的就不合适了。啊,所以说我们只能够用到其中联合索引的这个一部分啊,叫最佳左前缀的法则,在我们索引这个确定的这个情况下,大家写这个where的时候呢,那你要是在where中是不是出现了这个字段了,那那这个索引呢,就可以考虑去使用了,然后接下来的话呢,又出现这个字段了,这个仍然可以考虑使用啊,你要说这个没有出现,直接出现这个了,这个也有可能考虑属性,但是呢,你只能够用上我们第一个就是诶必须得有前边左边的情况下呢,你在写右边的,我们才能够整体上考虑上这个索引更多的这个部分啊,这就是这样的一个道理。
30:21
啊,就这样一个道理,行啊,结论的话呢,就是我们创建多个字段的这个索引,一个索引的话呢,最多可以有16个啊,或者说咱们默认情况下呢,可以有16个这个字段,诶过滤条件呢,呃,要使用索引必须要按照这个建立索引的一个顺序啊,依次满足,一旦跳过呢,某个字段后边就无法使用了啊很清楚是吧?啊就过了啊第三的话呢,我们就要组建一个插入的顺序。啊,这个给大家呢,一描述就清楚了。啊,第三个啊,主键插入的一个顺序啊,这个呢,也是给我们有个启示作用啊,就是我们在设置这个主键的时候呢,咱们呢,通常都是让它,呃这个依次递增的这样的一个场景,不会说呢,这个是一个混乱顺序的啊,主要原因呢,就是因为在印度DB当中啊,咱们这个数据存储的时候呢,是不是都要按照这个去速索引的方式呢,去做一个添加的,对吧?那如果说我们这个主件的话呢,呃,你是忽大忽小的啊,你要是默认的是从小到大顺序的话呢,我们就始终呢,是在我们这个页面当中的右边去做这个添加就可以了啊,你要不足的是我们在新开辟一个数据页,然后再去添加就OK了,对吧?但是呢,你如果说呢,逐渐值啊,比如像我们这里边的啊,虽然也是个递增的,但是呢,中间有一些空空隙是吧。
31:31
就是我们八后边呢,比如空了一个九直接就十的,那有可能呢,用户呢,后边又把这个九呢又插入进来,那这个插入的话呢,由于我们这个,所以呢,都是一个默认升序的一个排列的,就必须把九呢就插到这儿,而我们这个页呢,如果说恰好数据也满了,那咱们这些数据呢,是不是就要考虑呢,是移到下边一个页当中,这就出现了这样页分裂。啊,然后呢,这个呃,这个我们再把这个酒呢,再添加到这个位置,这样的话呢,肯定会影响我们的这个性能。啊,影响我们的一个性能,所以呢,在实际开发当中,我们的建议是什么呢?就是大家呢,一定要让我们这个主件呢,它是依次递增这样一个顺序,那比较好的一种选择呢,就是让主件呢,是不是auto increment就可以了。
32:10
对吧,哎,就我们在创建一个表的时候呢,针对这个主键这个字段,哎,我们呢,让它叫automent,而且呢,Automent本身它还要求呢,你对应的这个字段得是个主键,或者是一个唯一约束的一个,呃,这样的一个。这个字段是吧,诶这个咱们前面也都讲过了哈,行,那咱们的关于这个主见呢,暂时就说到这儿,咱们在讲到这块的时候啊,大家你看这个第13节的时候呢,我还要说一下,在实际开发当中,我们对于这种非核心业务的这个表啊,咱们可以考虑呢,是用auto increment,但是如果呢,你要是这种核心业务的表啊,比如说是像我们这个订单表,淘宝当中的是吧,订单表中的这个,呃,一条条数据,我们针对主件呢,用automent啊,就不是一个很好的选择了啊为什么?那我们到时候呢再说啊,大家先有这样的一个直观的一个感觉,先肯定auto equipment,然后我们再去推翻它啊叫否定之否定是吧,好,这就过了,然后呢,接着我们再看这个第四个点啊,CTRLC一下。
33:09
诶第四个的话呢,提到了我们在这个呃外当中的这个字段上呢,去呃有一些运算了,有一些函数了,这样的一些场景,那么这些场景的话呢,我们说会导致我们的索引呢失效,这个呢,在大家实际开发当中是一定要小心啊谨慎的这样的一个点,这呢我写了一个plan啊这呢再写一个,那我们去比较一下,你说这两个索引啊,这两个这个语句哪一个呢能够用上索引,哪一个呢会更好一些。主要区别的话呢,就是我们这个where啊student name呢,它like是一个模糊查询,这呢是一个where是这样写的,首先呢,作用是一样的。啊,当然了,最简单的同学会认为说这两个没有什么区别啊,都可以啊,这是其一啊,其实从这个运行结果上来讲呢,确实没区别。但是从这个效率上来讲呢,会考虑有这个区别了。啊呃,那有同学呢,可能会说老师我就觉得这个好。
34:00
哎,这个答案呢,也确实选对了啊,咱们这个呢,确实比下边这个要好啊,说词这个语句啊比啊下一条。啊,要好。看下一条要好。这个所谓的好的话呢,就是我们能够呃使用上。哎,使用上这个索引啊,但是前提是呢,咱们是不是得先有一个索引才行啊,那咱们先去创建一个啊create啊这个index,嗯,IDX,咱们叫个name这个字段,哎,咱们去加个索引啊create它,然后呢,On,咱们student。它里边的这个name字段是吧。好,针对这个内幕资料的话呢,我上面呢,是有一个索引的啊,那么在有这个索引的这个情况下呢,你看一下我们是这个好还是这个好,刚才有同学说说这个好,哎,他选的呢,确实是对的啊,为什么是选这个好啊,他说老师我这个单词呢不太认识啊,这个函数呢不太熟啊,那不熟的呢,我就觉得不好,所以我选这个。啊,这个要是因为这样原因去选的话呢,这个反而还是更low了是吧?啊另外一个同学呢,说这俩没区别呢,最起码人家还认识这个life的这个函数是吧?哈,哎,成这样了啊嗯,那结论的话呢,当然是上边这个好啊,主要原因是什么呀?就是因为咱们上边这个能够使用上这个索引啊,你看咱们创建了索引呢,然后我去做个执行,大家会发现呢,我们这个K这个位置你看是不是有啊,这个type这块呢,写的叫range是吧?啊就是还还还算不错的这样的一个访问方法啊,然后我们要使用下边这个呢,逆执行,你看这个很糟糕了,这是全表的一个访问,然后这张也没有用T是吧?呃,那为什么这个会出现这样的原因呢?
35:32
诶大家呢,你要认可这样一个点啊,你看我们这呢,是不是针对内部字段构建这个B加数吧,啊构建这个B加数以后呢,诶我们这个操作的话呢,你是不是就从这个B加数里边去,诶去找我们这样的一个一个这个name这样的字段值的,然后看一下呢,是不是ABC开头的,是不是就这样去找就行了。啊,不是ABC开的,因为本身的这个我们说它也是针对内幕,不是排好序了吗?前面这个呢。如果都不是这个ABC的,那就过掉哈,直接找到这首首先呢是A的了是吧?啊,然后第二个还是B的了,第三个是C的,然后从这一波里边呢,我们开始去找这个就相当于是使用上索引了,而我们这个呢,上来就跟这个函数呢,相当于是诶做呃跟我们这个字段上面做了一个函数,这个就怎么就用不上了呢?诶它实际上会怎么着啊,你想想你这个里边作用这个函数,作用函数以后呢,跟另外一个字符号去比,我也不知道你这个函数到底是作用的是什么。
36:23
啊,千奇百怪的函数那多了去了,对吧,那我能做的是什么呀?那我就是老老实实的诶把这个字段的这个值啊,一个一个的取出来,诶取每取出来一个,我就取它的前三个啊,这不是你这个函数的作用嘛,取前三个,然后取完以后呢,看看跟你这个是不是相等啊,每取出来一个我们都这样作用和函数作用完以后的话呢,看看是不是它,所以呢,我们在一开始的时候,你根本就没有办法去锁定我们用要要用这个B加数里边的这个ABC开头的。对吧,你是先作用函数再去操作,哎,你才能够考虑ABC呢,那那这时候这个B加数是用不上的啊,你需要一个一个的去取我们这个字段的值,所以说呢,我们就用不上索引了。
37:03
那自然而然的就不是一个好的选择。对吧,诶OK啊,所以说呢,像我们这个呢,在现有的函数上,现有的字段上,本来有索引你作用的函数了,就不行了,这是一种情况,然后呢,我们再来下边也有这个具体的举例,它运行这个效率这个区别啊,我就不去演示了啊。嗯,这个就过了,这个呢,是我们的第二种情况呢,看三秒多钟啊,然后再再接着去举例子啊,针对于我们这样的一个情况,来CTRLC一下。那这是有举的例子,在这里边的话呢,我们出现了叫student number啊这个咱们得确保呢,它上面呢,是有一个索引的。哎,我们这么着啊,CTRLCL一下这个粘过来这个IDX是吧。Student number,然后这样来去写。好,选中啊执行一下。好,那在我们这个字段上呢,它是有一个具体的索引的,然后呢,我是where啊student number加一等于就是900001是吧?好,我们选中了做个执行,你会发现呢,你看这时候也没有,这也是个全表的一个扫描,对吧?然后把这个语句呢,我们复制一下呢,再粘过来。
38:11
啊,CTRLC一下粘过来,然后呢,我把这个加一呢,我诶左边减个一,右边也减个一,这个大家小学都学过,是不是就这个数啊,这跟上边这个呢,其实表达的是一个意思,但是这个操作呢,你看我们走起一下,明显的我们是不是就使用上这个索引了。哎,Number呢,它这个应该还是一个非空的一个限制了啊,那四个字节了是吧,哎这个呢就能用上,哎,那这个效率呢,显然会更高一些,那比如我们选择这个select来走起。这呢是0.042。然后把这个呢执行一下。0.042,那这个呢就要更多一些。好,这就是这样一个道理,嗯,我们在现有的这个资源上,你加了个值,相当你要做个运算,那做运算的话呢,我就没办法去找你后边这个值了,像这种就可以直接呢使用这个缩引去找那个B加数中这个值是吧,你这在做运算,那我就得一个一个的都取出来,然后再去做运算,所以就用不上缩引啊。
39:02
啊,这是这个情况,然后的话呢,再举例啊,再举例呢,就看这个了啊,CTRLC以下。嗯,这个嗯,这个呢,针对的是咱们这个name字段,Name字段的话呢,也是上边作用的一个函数,然后呢,等于ABC啊,那这个显然是不是就也用不上了,这个类目呢,咱们上边是不是刚才创建了一个索引是吧?好,那你看我们这个时候呢,去做一个执行。啊,你看这时候是不是也是哦,这是一个no,不行,那也取前三个字段啊,那建议的话呢,我们是不是这样写前三个字段呢,是ABC的,那显然呢,是不是就能够用上这个缩引了是吧?哎,可以啊,这个咱们name的话呢,是20个字己按20个字符找一下上面这个。Name。这20个,然后呢,它是可以闻到的,然后呢,再加上你这个可变呢,需要两个字,哎字节呢去记录,所以20呢,乘以三加一加二,是不是一共就是63啊,那就这个道理好,哎这个呢也不是一个很好的选择是吧?哎,因为上面你用这个函数了。
40:00
行,那这呢,就我们说的这个,诶,关于咱们这里边出现了这个运算了,出现了函数了,哎,那这时候呢,出现这种类型转换了啊,类型转化的话呢,咱们下边这有个例子啊,那咱们把这个例子呢也说一下啊,这个出现类型转换也不行。那这是我们这个第。第几个,第五个是吧。类型转换的一个失效的场景,好比如我们看这个,来CTRLCL一下。这个name上的话呢,咱们已经是有这个索引了,嗯,在这写的是吧?行,你看啊,这时候我们where name呢,等于123来我们做一个explain,但是你发现呢,是不是这也是个哦,这也是个no啊不行是吧,那怎么呢,就能够使用了呢?你看我们这块选中执行把这个呃,123的话呢,咱们加上这个对应的单引号,因为本身name的话呢,它就是一个字符串类型的,所以呢,我们右边呢,也是个字符串,它就不会出现这个类型转换的一个问题,那我们这块呢,走起那就使用上了。没有问题,而这个呢就没有用,相当于呢,我们是先把这个字段上面呢,作为一个隐式的函数啊,对吧,那做了一个转换,然后跟我们这个123做对比的啊,那既然一一旦用上函数的话呢,是不是就索引就失效了,所以大家在开发当中呢,要注意这样的问题,要想索引呢能够有效,那这里边的这一点呢,一定要考虑在内。
41:19
好,这呢是我们说关于索引失效的这个第一波的情况。
我来说两句