51.尚硅谷_MySQL高级_用Show Profile进行sql分析.avi原创

好，那么呢，来看一下我们接着昨天的内容讲到了批量数据脚本的插入，我们呢，下面来看看新的一个优化的方式，收。那么它又是拿来干什么呢？好，同学们这儿回顾一下我们所说过的，如果你要进行SQL的数据调优和排查，第一步是不是先让故障重现啊，但是这个不是说这一分钟有事，下一秒就OK，那么正常来说的企业里面的流程差不多都是这些步骤，第一个一定是DBA或者是什么运维工程师，他们从监控系统里面收到了爆炸，系统变慢了，因为大家都知道重要的核心系统都会有另外一套辅助的系统监控，这种监控系统，比如说现在这个系统慢于每一个模块平均时间，优化了以后可能五秒钟就应该执行完，但是这个已经长达20秒了，那么请问这个时候同学们，咱们是不是要判断为什么慢了那么呢？有很多种原因，也许是。

程序的内存泄露，也许是思索，也许是网络，也许是数据库，搜Q写的烂，那么好，假设是SQ的问题，那么我们现在是不是需要把crccu抓出来呀？那么一般而言，第一步收到爆炸，第二步给srccu跑一下，然后尽量在测试环境里面重现我们的故障，那么这个时候我们呢，将要做的事是不是首先打开我们的慢查询日志啊，如果慢100条SQL里面不可能100条SQL都慢，那么这个时候咱们是不是要把有问题的SQL提取出来，相当于说我们来排查的话一受到爆炸。

诊断CIRCLEQL2、开启慢查询日志，是不是抓出执行的慢的circleql啊？第三步是不是采取我们的explain开始分析第四一步当然基本上正常的来说到explain，结合你的业务和circq应该是能够找到相应的问题症结所在，但是如果你说还是没有摆平我们这个SQL在传输，在网络在连接，是否是思索等等，你需要进入入一个比explain更加细力度的查询和排查，那么呢，到了我们的第三步以后，咱们是不是要用收profile，差不多到这两步基本上95%的问题O了，那么最后如果说通过前两步的分析还是解决了一般般，那么呢，干嘛？是不是到我们的第四步配合着DBA去卖点看复我们的MYSQL数据库里面的。

各种节点的参数，各种性能的参数进行调优和修改啊，那么差不多就是慢查询，抓取和截取explain profile fair，以及是不是服务器的数据和脚本以及重要属性参数的调优啊，那么差不多数据库的优化就是这四个维度，当然结合我们Java程序员，因为第四个维度在服务器上改参数这种问题我们也说过，这个是不是我们没有权限去动生产数据库啊，那么呢，知道即可，比如说我们昨天讲过的，我把sort buffer排序的这个缓冲区，然后maxs是不是要把它调大一点点了？好，那么下面我们呢，来看看so profile它是个什么东东，来，同学们往下走。第一个它是什么？是my thankq提供可以用来分析当前绘话中语句执行的什么呀？抬头是否还记得我们第一天所学过的四层结构，连接、服务以及存储，那么也就是说一条SQL主人你打一条SQL过来，我这个完整的生命周期，我是不是要走一圈？假设有一种命令能够把我走的全部路径有点甚至是什么偏底层硬件运行的，你键连接多长时间，查缓存多长时间，最后示范表多长时间把假设你这个SQ慢哇，十秒钟这么慢，那么能不能有一张清单告诉你你到底是多少个步骤，比如说第一步你花了1.37秒，第二步你花了0.46秒，第三。

步，你花了3.23秒，告诉你每一步分别花了多少，如同你去超市，本次消费花了325块，你肯定是不是需要人家给你一个购物清单让你知道诶，怎么我这次买东西是不是花了这么多钱啊？换句话说，是不是需要出具一个明细条啊？那么搜fair有点类似这样的功能，我们往下看，可以用于SQ的调优测量，那么呢，官网上的介绍就在这儿，同学们有兴趣的可以看一下，那么呢，我们呢，来看看这会有些什么东西？第一个默认情况下什么状态？大家是不是发现它的慢查询啊，收profile这些是通通是默认关闭啊，默认呢，保存最近15次的运行结果。那么言下之意就是什么概念呢？你跑吧。

我开启了以后，我收profile，偷偷的在后台就把你记录着，你现在能够打出来的QL分别是些什么，我是不是偷偷的记录，这样我每条Q，然后根据D抓出来以后，那么呢，我们去分析有问题的Q，那么怎么分析呢？

来同学们请看一下，首先咱们是不是得看看你这个当前的版本是否支持啊，好，那么来命令variable，我们呢，直接过来来执行一下，我们的目前是关闸还是开闸，那么呢，默认关闭，那么呢，这个时候干嘛呢？如果我们要运行这个功能，到第二步请将其打开，那么这个时候我们呢，可以看一下一。设置shop pro等于二，第二直接将其开启，大家可以看前一步是off默认关闭，现在咱们是不是开通了，那么现在咱们就是二那么好的完火以后，我们下面呢干嘛呢，运行一下我们的circleq，那么这个circleq怎么运行呢？来同学们，咱们是不是写过最简单的，我们从简单到复杂的写好吧，那么假设我们。

Selection from t，同学们，这个没问题吧，好，注意开启功能以后相当于双收，Fair在后台就已经偷偷的帮我记录了这条色条，那么回头我有一张底账，有一份抵账清单的时候，咱们是不是照单拿药照单抓色平来分析啊？那么再来我们现在如果说e in了，就以T比LDPT干嘛呢？按什么呀，1.eptid是不是等于d.ID干过这事吧？那么这个时候那个脑图那个圈圈什么颜色没问题吧？那么好，同学们，哎，抱歉哈，那么这个时候是不是应该有一个D啊啊。

好，那么这个时候一回车跟我讲，我是不是又执行了一条好假设我又执行了一次，我又执行了一次什么概念，相当于说是不是有些时候不停的点某个按钮的时候，是不是重复发了4Q，好，那么再来，我现在呢，张三完了以后，李四是不是又发送了好干嘛呢，他也弄了，那么请问各位亲，慢慢的后台是不是记录了我这些CQ从连接到运行到抓取数据推送给客户看到的效果呀，那么再来，昨天我们是不是整了个50万啊好，我们现在呢，运行一下Q，知道这是什么意思吧，这个没有任何意义啊，故意让写了耗时，它的意思就是什么？By ID除以十。

值，请问比如说十除以三余数是多少？那么十除以四呢？啊OK，那么这些是不是按照余数来分组啊，好，那么这个是什么？除数取余没问题吧？好，那么这个时候啊，同学们看一下，那么呢，我们呢，现在来执行一下这条色调。比如说半IDP是有主件ID啊，说白了它就是属于什么，但后面做集取有些最简单的算算法是求模取余啊，那么干嘛呢？这个时候假设余速是一的，余速是二的，余速是三的，是不是余速是一的是一号机，二的是二号机，三的取三号机，这样分组分开啊，好那么这个时候厘米塔我们看全表太多，我们是不是只要15啊好，那么这个时候我们来看看哈，那么呢，一执行会产生一些什么鬼。

各位亲，你告诉我上面的是不是就非常快这些字Q简单吗？跟我讲下面的是不是开始慢了，好卡顿拖慢的这种CQ，请问由于我们后台开启的服务，它是不是就偷偷的记录了，待会儿我们是不是来看好你说这条S慢了，这条有问题的S，我们是不是要看看人家慢在哪啊？好，那么再来，我再实行一次。

注意这个是不是按照20啊好，那么呢，没有什么太多意义哈，这个这条色频没有任何查询的意义，我是不是故意给他查的慢啊，因为我们现在可是50万条记录。那么呢，这。要是快的话，大家看一我的是不是瞬间出来好，那么现在这个问题就是干嘛呢，我们有没有一种方法看看现在这个系统上所有执行过的Q啊。听懂这意思吗？好，那么来同学们profile，这是不是一直行？那么呢，大家呢，可以看一下我们的结果将会出现一些什么鬼。

来，大家有没有发现这个是不是query查询ID，从开工到现在我是不是发过九条三条，这什么意思啊，是不是持久持续的时间了？好，大家请看这块是不是主人，我马上就记录了你每一步的操作呀，那么请问大家看刚才我所执行的这些东西是不是全是历历在目了，好看一下。那么呢，聊了刚才的这些命令呢以后，我们大家可以看到目前我们是不是获得了，诶，比如说这条CQL是不是超过两秒了，那么好，我们前面也说过，粗略的来说SQL导致服务器慢，要么就是CPU运算复杂，要么是不是就是频繁IO啊好，我们来看看下面呢，我们呢来试试收profile的诊断，那么呢，其命令就是show profile CPU for query干嘛呢？我们抓出来的circle，请同学们搂一眼，我这是不是有宽RYID啊好，那么假设啊，我们现在看这一条，大家看这条是不是有0.01啊，没问题吧，这条是不是0.04啊，当然这条是不是两秒以上啊。

我们来看看两者有不同或者又是哪些问题，那么就是show profile，看看你的CPU和block IO对于查询什么概念呢？这边大家看是不是有三号查询ID啊，三号查询ID我们一回车来看看他会有些什么鬼。

大家请看，开始waiting for corry，开始洛克是不是符合我们第一天学过的逻辑结构图，开始了以后是不是连接啦，然后人家先是不是先去找缓存啊，这个是不是看看你的权限，然后从from开始，是不是open tables啊，慢慢的1.waitingforcurry，初始化等等，然后干嘛存储结果进查询缓存，什么意思啊，因为我们以前是不是执行过呀，好，那么呢，全部OK。那么言下之意，同学们跟我讲，这个东西是不是代表了你查询到完整的一个生命周期和过程呢？那么他就告诉你，就是说主人为什么你的这条circle要花费0.4秒左右，那么言下之意，那么来，同学们。

假设我们再来一条，现在YID等于四可以吧，OK，那么这个时候我们大家可以看，我们修S，大家看这条记录是不是编号是十啊，那么好，我们呢，可以呢，FORRY10号，那么大家看。是不是明显的跟前面的不一样了，他会看，那么你为什么会花了那么多时间，你把这个总量给我分解步骤，这个是不是持久时间啊，这个是CPU用户和系统占CPU的内存，Blocking选项是不是IO的输入输出啊，那么这么看了以后，你看可以看到starting等等等等初始化，请问这个是不是智能优化器开始登场了，然后干嘛，这个什么意思啊，是不是策略的意思呀？好，再来prepare，那么这个时候是不是查询执行了，还记不记得昨天我们说过一个问题，我跟大家聊过select from的时候，大家还记不记得我们说过查询的慢还是传输慢啊，没问题吧，你看这是有个呀，你查出来了。

好，最后是不是end呀？然后查询完成close table，那么言下之意，这有open table，这是不是应该有close table啊？那么以及到最后释放一些资源干嘛？缓存池出去，最终至记录慢查询日志，最终clean up是不是清空啊？那么也就是说一条C的完整生命周期你在这儿是不是可以看到啊？那么言下之意，是不是每一步你将会看到，到底是不是有一些什么鬼啊？那么好，同学们，这个时候你会来了，那杨该痛快点。

说实话，正常人类恐怕不可能记住这么多吧，也就是说你能不能告我，现在我在这个status这条下面哪些选项出现了，证明我的crccu肯定有问题，咱得这是病，咱得治。那么这个时候我们呢，别忘了上面是不是有个重量级的八号记录啊，好，那么呢，在它之前我们先来看一下这个这条CQ，我是看了CPU和block IO，那么杨哥除了可以看CPU和block IO以外，还能不能看别的呢？目前我是不是把最通用的CPU和IO堵塞CPU查询慢给大家整出来了，那么还有有呢？答案是肯定的，为什么呢？大家可以看最狠的是有个显示所有开销，这是这是CP，这是不是内存相关的等等啊，也就是你要是想看内存是不是再加个memory参数啊好，那么这我呢就把最常用的呢给大家罗列，主要就是CPU和IO，那么问题是。

重要的是我这看到这些以后，我怎么知道哪些参数有毛病，哪些参数没毛病呢？那么呢，主要是四个，请同学们看一下。如果说你在收profile里面，按照我刚才的优化命令一查，那么这个时候是不是得到了这条Q从开始到clean up之间完整的生命周期和分布分解的细力度化的步骤啊？假设在这个栏目里面，各位亲，你们出现了这些鬼。

Converting heap to my item这个意思就坏事的，也就是说这四个都是会给你惹事的，也就是说只要你在data的状态里面这四个当中，当然你说杨哥这四个我都出现了，那请把这条发给我，我想看看兄弟你以什么样的功力写出来是如此惊天动地泣鬼神的色，求听懂，出现一个就够你受的了，你要出现四个是不是宿罪并罚呀？那么这个时候请大家看第一种干嘛？Convert hep to my查询结果单内存都不够用了，开始是不是往磁盘上搬了？第二个creating table创建临时表，我第一次是不是说过group by这种东西准备好是不是要创建临时表啊？分组之前先排序，而且我们说过最好是不是跟我们所引顺序一致啊？好，我们先把这四个看下来，为什么创建临时角费事呢？第一步。

新建临时表，第二步是不是拷贝数据及临时表，第三步数据推送给主人以后，我是不是还得把临时表删了？那么各位亲，现在我还给大家个面子啊，整了一个LIMIT15万，我要不加这个limit，假设我现在的数据里面是有150万，那你跟我说，如果你要拷贝150万，里面刚好你查出来符合条件的有120万，你要把这120万数据拷贝到一个临时表里面，跟我讲这个过程发不发生性能损耗，完了以后再推送给客户，最后你再把临时表删掉，那么这个时候是不是导致你SQL变慢的罪魁祸首和元凶啊？那么也就是说第二个比较好，第三个copy on disc危险，把内存中的临时表的附上磁盘，那么第四个好啦，那么所以说如果这四个你都出现了，恭喜你发了，那么好，同学们，那我们来看看刚才。

咱们是不是有八号记录啊，也就是说你跟我讲，我这条记录才0.00008，需不需要去优化完全每一步，那你跟我说我这块的status状态了以后，有没有出现让大家觉得危险的这四个中的一个呀，那么说明这条circle是过呀，但是别忘了八号是不是给你惹事了，那么我们来看看八号。来看好一回车，同学们请看这个时候我们是不是出现了第一个创建temp table啊，大家看copy to table是不是花了2.58秒啊，那么这个时候你要是有本事把这种化验单成交给dpa和你的技术经理的话，那么呢，人家是不是一定会带你玩啊，你可以非常准确的告诉他你的CQ有问题，理由是常见的临时表，我们在临时表这就花了2.58秒，最后大家看有什么，是不是fair又来了，之后查出来在sending data，然后完成，然后copy创建临时表，拷贝数据性临时表，最后是不是删除临时表啊，相当于说一个对象是不是新建使用回。

你说这三步而言，你是不是加重了数据库系统的负担啊，那么呢，也就是说这样的话，同学们，我们这儿可以看到持续花了多长时间，2.58秒怎么来的，光是CPU的运算是不是就花了1.2秒啊，那么你拿这种去跟人家讨论问题，你觉得你专业了，我相信那个人恐怕一定是闭嘴了，是不是根本就不敢跟你说什么，我觉得你不用觉得咱们这儿是不是就像我们说化验单为准啊，OK，那么呢，出现了这四个重点一个，所以说请同学们一定要注意这，尤其是这个copy to change一定是非常耗费内存的，耗费系统资源多了以后CPU1定会慢听到好，那么这个时候呢，我们呢，可以看到通过上面的。

查看开启运行CU以后，观察找出其中有问题的，最后CPU block把上一步的查询ID进一步的细致度话呢，查看，只要这四个当中有任何一个你必须得优化听懂，OK，那么这个呢，就是到了我们的show profile。