00:00
好,同学们,咱们接着来看这个第九章,叫做性能分析工具的使用。啊,那提到这个性能分析工具啊,诶我们就谈到了,在实际的生产环境当中,那有时候呢,我们会出现了这个色Q比较慢的这种场景,对吧?那一旦色Q执行比较慢的话呢,我们就需要呢定位,诶到底是什么问题导致我们这个SQ执行比较慢,是我们索引设计的问题,还是我们服务器参数配置的问题,还有可能是我们需要增加缓存的问题呢?对吧?啊,那这时候呢,我们就谈到了,在数据库调试当中,咱们的目标呢,是为了这个响应时间会更快,存储量呢更大,那要想能够快速的找到我们这个色Q执行比较慢,这种调优的一个思路啊,咱们就需要呢,借助相关的工具啊,比如说宏观的这种监控的工具,乃至于说呢,我们微观当中呢,呃,相关的一些日志啊,我们进行这个分析是吧,然后帮助我们呢,去找到这种调约的思路。那么我这里呢,首先呢,列出来这个关于数据库服务器呢,优化的一个步骤啊,在这个过程当中呢,我们主要呢,是有两个呃,这种行为啊,第一种行为呢,叫做观察show status,通过观察我们相关的这种的场景,这里边儿呢,就涉及到一些工具的使用,对吧?然后呢,我们去做出对应的相关的一些行为啊,就对应的去做我们这种调优或者叫优化来这块我们做一个分析啊,这个流程图呢,我们走一下。
01:20
首先的话呢,我们去观察这个服务器的一个状态,诶看他呢,是否是存在这种周期性的这种波动的问题。啊,那如果呢,是存在这种周期性波动的问题的话呢,啊,你比如说啊,有可能是我们这种周期性节点造成的啊,比如说呢,像这种双11的活动啊,618呀,或者我们一些这种促销的活动啊导致的啊那这时候怎么办呢?我们可以考虑呢,去增加这种缓存,或者呢,去更改这个缓存失效的策略,这种方式呢,去解决这个问题。啊,那如果解决了当然更好是吧,那如果没有解决,或者说呢,我们不是存在这种周期性的这种波动的问题,诶它仍有这种不规则的这种延迟,或者呢,是这种卡顿的问题啊这时候怎么办呢?我们呢,可以考虑呢,去开启这种慢查询。
02:05
啊,我们可以呢去设置叫long que去定一个的一个阈值,然后呢,超过这个阈值呢,我们就定义为呢是执行比较慢的这个呃语句了,我们就把它呢记录在相关的叫慢查询日志当中,然后把这些SQL语句呢,我们找到,找到以后的话呢,我们就可以使用下边的一些工具,那比如说我们使用这个plan啊,我们去查看一下呢,经过优化器优化以后的我们这个具体的执行计划啊做一个分析。还可以呢,通过这个叫收啊是吧?呃,那对应的其实还有还是我们之前这个讲这个呃,逻辑架构的时候呢,提到过的这个收和这个收profile对吧,我们可以呢,去查看具体的每一个步骤当中的一个时间成本。啊,一个时间成本通过这样的工具的使用呢,我们看一看,到底是我们这个S呢,这个执行的时间比较长导致的,还是呢,由于我们这个S呢,等待的时间比较长导致呢,你是一个慢查询对吧?那如果呢,你是一个等待的时间比较长的话呢,我们就可以考虑呢,去调优相关的这个服务器的参数。
03:09
啊,你比如说我们去适当的增加这个服务器的,呃,这个数据库的这种缓存啊,缓冲池对吧?诶那解决它这个等待时间比较长的问题,那如果说呢,你是我们执行的时间比较长啊,不是我们缓存问题了,你现在在执行是你这个执行时间比较长,那我们就要考虑呢,是不是说我们这时候呢,所以这个优化有问题啊,这个优化上一个点,哎,另外的话呢,就是我们关联的这个表是不是过多了啊导致的我们需要呢做相关的优化。啊,第三的话呢,比如涉及到我们数据表相关的这种字段的设置啊,是不是合理,包括呢,我们是否遵循了这个相关的这种范式啊这块呢,就涉及到我们这个诶circleq本身这个circleq的一个优化的这个点上,好,那如果说呢,我们这个问题得到解决了,那更好,如果没有得到解决,那这是我们还要看另外一个点,那是不是我们这个售后查询呢,本身呢是达到一个瓶颈了。那如果说没有达到这瓶颈,那这是我们还要经过诶刚才讲的这些步骤呢,重新的再做一个这个检查啊,那如果说呢,是达到我们这个瓶颈了,那此时的话呢,我们就需要呢,去增加这个服务器的这种数量了,那我们可以通过这种叫呃主从复制啊来实现这种读写的分离,然后还可以呢,实现这种分库分表的方式啊,比如说垂直分库,垂直分表,水平分表,这样呢去分散哎我们相关的这种访问服务器的这种压力。
04:27
OK,那么通过这样的方式呢,我们整个人来实现对于服务器的一个调优啊,这呢,我列举出来了整体的这个步骤,在这个步骤当中啊,我们就需要呢,有相关的工具的这个使用,比如说我们这块提到这种慢查询的一个定位,对吧?诶,我们这里边就提到这种慢查询日志啊,在我们这一节当中啊提到的,然后呢,我们通过这个plan去查看我们的执行计划,查看我们相应的每一步的一个时间成本,在我们这里边都有讲解。诶,咱们这一章的重点的话呢,就是讲这些工具,那其中最重要的一个工具呢,就是这个plan啊,咱们在前面这个章节当中也稍微带大家看过这个plan了,这一章呢,我们就系统性的进行一个学习。
05:06
好的,那么这儿呢,就我们讲解的这样一个步骤,那做一个小结,那我们对这个服务器呢,进行调约的话呢,实际上呢,有这样的一个大块上的一个思路啊,那么从这个成本上来讲啊,从这个成本上来讲的话呢,那我们呢,是依次往上的,成本越来越高啊,SQ及索引这都属于我们自己编写SQ层面的是吧,这个成本是很低的,那数据库的一个表结构啊,这个呢,要动一动会影响到我们整个表中的一个数据啊,这呢也会就成本的比例要高一些,那以及呢,这个系统的一个配置,包括硬件的一个这个增容等等的啊,这个成本呢是越来越高的。但是从这个执行的效果上,就是我们最后调的效果上来看的话呢,凡是呢,越往下这个效果呢,是越来越好的啊,这个大家如果做过相关调的话呢,应该一看呢,也都能明白啊,这呢就是我们要给大家强调的这样一个点。好,那这个呢,我们就说到这儿。然后顺带呢,我们接着来看这个第二个点叫查看系统性能的一个参数。
06:02
这个在调的过程当中呢,我们可以查看相关的一些啊,整个服务器在执行过程当中的一些参数的信息啊,这边我们提到叫show status啊,这里边我们可以用的这个global或者这个session啊,一个呢是全局的,一个是我们这个当前绘画级别的,对吧?那么对应这个参数呢,就可以有下边这一些,通过这个参数呢,我们去查看服务器当中的一些性能啊,它的一些性能参数啊,包括执行的一些频率啊,比如说呢,我们连接MYSQ服务器的一个次数有多少啊,服务器上线的时间有多少了呀,慢查询的次数啊有多少次了呀啊等等啊这样的一些情况啊,那咱们这块呢,就给大家呢稍微做一个演示啊啊,那这个服务器呢,我们这个在3S上呢,把这个MAS服务器呢,都已经开启了,通过这个叉十的方式呢,咱们比如说做一个这个测试啊,首先呢,我们去登录一下。Building my you root。啊C23,那我们就以这个呢来给大家去做这个示啊,这我们使用的叫,那比如刚才提到一个叫吧,叫连接的一个时,哎,连接的一个这个什么次数是吧。
07:07
好,这呢,我们能看到这个连接的次数呢,是有这个12次啊,还有哪些参数呢?Show status like,叫up time。对吧。哎,这么多秒是吧,诶这块显示的话呢,就是我们这个,诶连接的这个,呃,服务器上线的这个时长,哎是多长时间。呃,那这块呢,咱们还可以这样啊,比如我们这块双击一下,再重新启动一个针对150的一个连接。把这个链接稍微慢一点。那么连接上以后的话呢,我此时呢,把咱们这个MYSQL服务器呢,我进行一个重启,哎重启的话呢,大家你再看一下我们这个服务器上线的时间,它是不是就变了啊,这个重启的话呢,我们通过这个叫system,哎,Control是吧,Restart。那我们这个Ms d OK。哎,通过这样的指令呢,我们做一个重启。哎,稍等片刻啊。那重启以后的话呢,大家你想象一下我们这个服务器上线时间呢,它是不是就应该从头来记了来,你看我们再去执行这个up time啊,这个五秒是吧?啊,七秒八秒,九秒,十秒,哎,就这样一个意思,哎,服务器上线的一个运行时间啊呃,那么接下来的话呢,还有关于我们这个叫slow queries,就是我们慢查询的次数啊,有多少次来我们也可以执行一下啊,Show status like。
08:23
啊,这个我们叫S。诶,Queriess是吧,诶这样的一个方式,这呢是零次啊,因为呢,诶目前我这相当于是又重新的来登录咱们这个MYSQL服务器,我还没有写相关的一些SQL语句,呃,这个这是其一,那其二的话呢,就是即使我写了一些SQ语句的话呢,也不一定它就是慢查询啊,这里边我们提到一个阈值的问题啊,咱们在下边呢有讲。嗯,在这块有讲,就是比如说啊,默认情况下呢,我们这个色扣在执行当中,如果呢,你花费了这个十秒以上,他就认为你是一个执行比较慢的一个色扣,那就会做一个记录,那比如说我们这块呢,写这个色扣呢,执行花了这个11秒啊,那就这一条记录,那这时候呢,它就会呈现的是一个一默认呢是十秒啊,那一会儿我们测试的时候呢,可以把这个十秒呢往下降一降啊咱们模拟数据的话呢,十秒这个时间呢,还是有点长的哈,咱们可以改成一秒,超过一秒咱们可以定位呢,它就是一个诶满查询。
09:15
OK,那么还有什么呢?呃,这里边我们看到还有这样的一些,比如DB啊,下划线Rose是吧,这样的一个情况,来我们可以看一下。啊,还可以呢,收啊status这个like啊,这个我们叫in啊DB啊下划线啊,Rose在下划线,比如来一个这个百分号这么着。啊,目前呢,我们看到呢,诶全是零是吧?呃,那这对应的是什么意思呢?是我们一楼DB当中,它查询到的时候,哎查询到读取了多少行,这其实就是增删改查嘛,是吧,增删这个这个改查是吧?那么这些操作的话呢,它诶对应的这个行数呢是多少。啊,对应的行数是多少,那举一个例子啊,比如说呢,呃,我们现在收一下data basis。
10:00
哎,对吧,还有这样一些,好,那此时的话呢,我们比如说呢,去use一下叫艾硅谷DB,哎,在这里边呢,我们去说一下这个叫tables。哎,抽一下这个tables,好,这里边呢,有我们比较熟悉的这个employees这个表,我们在上面的时候呢,主要就用了这些表,对吧?好,那此时呢,你看我们去select星from一下,这个叫an employees。诶,这呢是我们查询了一次,一共呢,有107条记录啊,然后我们再执行一次。那又107条记录好,那此时的话呢,大家你看我们去执行一下,这个叫status,哎,这个我们叫DB Rose好家你看到这个值呢,是不是就214啊,那107乘以二是不是就是214。诶,那我们就相当于是一共呢,查询的话呢,一共是诶查询到了这个214行数据,那我这里没有进行这个增删改操作啊,如果进行增删改操作的话呢,这块也会做相关的这种记录,诶通过这些参数的话呢,我们对整个服务器在执行过程当中的一些参数呢,进行一个查看,下边呢,还有这个关于这个诶增删改查操作的这个具体的一些次数啊,操作的一些次数,比如我们刚才呢,相当于是不是就执行了两次查询的操作呀,哎,就这个意思啊。
11:07
行啊,这个呢,我就不去再做测试了啊,这儿呢对应的就是我们说的这个第二项叫查看系统性能的参数啊,我们呢就说到这儿,然后呢,咱们再看一下这个第三个点啊,因为这个也比较简单啊,我们就这几个连着说了,这个呢叫统计circle的一个查询的成本。哎,Circle查询的一个成本啊,这个呢,对应的词呢,叫last que cost啊,你看这个意思呢,就是我们最近的一次查询操作呢,花费的这个成本呢,是多少啊,这个成本呢,其实主要呢,就是我们,诶你大家想想,我们比如说要进行一个查询操作,你说这个时间主要花在哪儿呢?是不是我们就是读这个数据啊,这个数据呢来自于这个数据页,那这时候主要关注点就是这个数据页呢,它到底是来自于我们的这个硬盘呀,还是内存呢,甚至说是来自于缓存啊,那么不同的位置呢,它这个时间的花费肯定是不一样的,所以这里边我们主要关注的就是成本多少,主要呢就是关注它到底是读取的这个液的数量是多少。
12:02
啊,这是这样的一个点,行,那这里边儿的话呢,我们可以用到咱们上一章呢,用到的这个student info啊以这个表为例呢,来进行一个说明。行,那这块儿我们还回过来,好,那回过来啊,首先的话呢,咱们切换一下这个数据库啊,咱们使用一下叫艾特硅谷DB1啊这样的一个数据库啊,没问题是吧?那首先的话呢,我们去收一下这个叫tables,查看一下我们当前这个数据库下呢具有的这个表,诶这呢,是不是就有我们这个叫student in这个表,对吧?然后这些表中呢,我们当时呢,是给大家模拟造了一共100万条数据,那此时的话呢,我们可以呢,去查找啊这个表中的一条记录啊举个例子啊,比如我这块呢,首先呢,去呃,Select from一下我们这个student啊ER这个表,然后呢,Where啊,我就通过这个主ID呢去做查找吧。嗯,我们呢,一共是100万条数据,那我就选这个900001这样一条记录看一下没问题吧,好可以好,那我们这块呢,执行一下。呃,这呢,因为咱们用的主件,而且找的是这一条记录啊,花的这个时间呢,太少了,以至于我们都看不出来了,对吧,这个针对我们最近的这样的一条这个查询语句呢,我们可以呢,去使用这个指令叫做show status like last que cost,查看一下我们当前最后的这一条查询语句呢,它所这个花费的这成本来走一下这个呢,Show status。
13:22
啊,这个like,那我们上面的话呢,第二节讲的是这个系统参数,对吧,也是用的这个show like啊,只不过这时候呢,我们可以不理解成叫系统参数了,嗯,这个呢,我们叫呃什么呀,还记得吧,哎,Last query。That query cost。哎,走起,哎直行好,那这时候我们看到这个值呢是一,这个一呢,其实就意味着我们当前这个查找的这个数据啊,它就是需要用到的一个数据页,那一个数据页中去加载就OK了,这就是它所谓的一个成本行,那是我们做一个修改,比如说我这块呢,把这个ID呢,改成一个呢,叫做between吧。
14:02
啊,Between这个900001,然后呢,来一个and。比如我们查呢,就是001,诶我看啊。嗯,900100行,这一共呢,是不是100条数据啊,来,我们走起。啊,这呢,用的这个时间呢,也是太少了,那我们看看这么多数据呢,它来自于哪些这个数据页当中,那我们看到这个结果呢,是20点多,那相当于呢,我们数据呢,是来自于21个数据页。整个呢,你看花的时间呢,都特别的少啊,都是0.00啊,这呢好像不利于我们去看这个整个的这个呃,对比了哈,因为都是0.00,你不知道它后边这个小数点的这个位数到底是多少,那咱们再换一个字段来进行操作吧,比如说我这时候呢,拿咱们这个student ID来进行说明。说student ID呢,首先咱们就来一个大于吧,争取让它出现的是0.00以上的啊嗯199,呃,咱们一共呢,是有100万条数据,这个student ID呢,一共是20万。那所以这块呢,我就用199000吧,来我们来看一下走起。
15:04
诶出来了,这事呢,我们这个数据呢,一共是有这么多,然后我们看到的这个秒数的话呢,是0.05秒,诶这个还可以,然后呢,我们去查询一下他这个cos。大家会发现呢,一共呢,是来自于这个2000多个页当中,行这是一个,然后呢,咱们再把这个酒呢,我。诶,这是一个是吧,我们再这么着一下吧,把这个零的话呢,我们再改成是个九,这儿呢,是不是199900可以来,我们这时候呢,再诶把这个结果呢,咱们这样盯一下啊。然后放在这儿,然后呢,把这个结果呢,我们再走起一下啊,大家看此时的话呢,咱们这个查询呢,是不是花了0.01秒,那跟这个0.05呢,现在有个五倍,但是整体上来讲呢,没有这个数量级之间的一个差别,然后我们再看一下它的这个cost,诶看到了是吧?好,那大家呢,来对比一下,发现我们这时候呢,两个不同的查询呢,它涉及到这个数据呢,差别其实还是挺大的,基本上快是这个十倍的这个差别了,对吧?但是的话呢,大家你会发现我们这个使用的这个查询对应的毫秒数的话呢,它却只有呃,0.0。
16:09
次这个秒的一个差别,相当于这个秒数上呢,它没有这个数量级的差别,但是我们数据呢,有这个差别啊,这是我们想说明的问题是什么呢?还是跟我们这个数据来自于哪是相关的,来这时候呢,我们回到这个课件上呢,咱们来做一个说明啊,这个。就是整个我们这个参数呢,说对于这个比较开销呢,是非常有帮助的,那我们对于这种查询的话呢,其实有啊这样的一个说明,咱们在前面呢,讲这个缓存的时候呢,其实提到过这个位置啊,提到过这个事情,说位置呢就决定了我们效率啊,批量呢也能决定这个效率,位置的话呢,就取决于我们这个数据呢,如果呢是从这个缓冲池中加载的,它的速度呢,肯定是要高于从内存中,内存中呢又高于来自于这个磁盘当中。对吧?啊,那么下一个问题呢,就是这个批量呢,决定了这个效率,呃,如果呢,我们从这个磁盘中对单独的一个页呢,进行一个随机读取的话呢,差不多这个时间呢,要花费这个十毫秒的时间。
17:03
那咱们讲这个内容的时候呢,我在讲咱们这个区段和这个表空间的时候呢,咱们是不是提到过这个问题对吧?那如果呢,我们选用的叫顺序L的方式呢,去读取,从我们磁盘当中批量的一次性啊读取了多个页,那我们摊下来的话呢,平均一个页花的时间呢,其实是很少的啊,那你看在我们这个当中呢,就存在这样的问题啊,明明我们这个页呢,你发现呢,直接呢跨了这个十倍,但是实际上呢,我们画的秒数呢并不多啊,就是因为我们这里边儿呢,实际上呢,体现了一个顺序IO啊这样的一个场景。OK啊,那么这个呢,叫last que cost啊,大家在实际当中呢,你呃想查看一下你最近这次查询的它这个跨度的这个页数是多少,那如果这个时间比较长的话呢,我们可以考虑呢,是把相应的这个树叶呢,是不是放在我们这个缓存当中啊,这时候呢,你再查询的这个速度呢,就会快一些,是吧,能够提高我们对应的这个吞吐量啊OK,行,这呢是我们,哎当前说到的这样的三个问题。
我来说两句