当前位置:   article > 正文

MYSQL——SQL慢查询与案例分析_慢sql的场景

慢sql的场景

摘要

平时的工作中,不知道你有没有遇到过这样的场景,一条 SQL 语句,正常执行的时候特别快,但是有时也不知道怎么回事,它就会变得特别慢,并且这样的场景很难复现,它不只随机,而且持续时间还很短。接下来将详细的介绍的有关于SQL的慢查询的原理以及优化方案。

一、SQL慢查询原理

InnoDB 在处理更新语句的时候,只做了写日志这一个磁盘操作。这个日志叫作 redo log(重做日志)也就是《孔乙己》里咸亨酒店掌柜用来记账的粉板,在更新内存写完 redo log 后,就返回给客户端,本次更新成功。

做下类比的话,掌柜记账的账本是数据文件,记账用的粉板是日志文件(redo log),掌柜的记忆就是内存。掌柜总要找时间把账本更新一下,这对应的就是把内存里的数据写入磁盘的过程,术语就是 flush。在这个 flush 操作执行之前,孔乙己的赊账总额,其实跟掌柜手中账本里面的记录是不一致的。因为孔乙己今天的赊账金额还只在粉板上,而账本里的记录是老的,还没把今天的赊账算进去。当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为“干净页”不论是脏页还是干净页,都在内存中。在这个例子里,内存对应的就是掌柜的记忆。

“孔乙己赊账”更新和 flush 过程

回到文章开头的问题,你不难想象,平时执行很快的更新操作,其实就是在写内存和日志,而 MySQL 偶尔“抖”一下的那个瞬间,可能就是在刷脏页(flush)。

二、数据库的flush 过程

2.1 日志文件(redo log)满了

redo log 状态图

第一种场景是,粉板满了,记不下了。这时候如果再有人来赊账,掌柜就只得放下手里的活儿,将粉板上的记录擦掉一些,留出空位以便继续记账。当然在擦掉之前,他必须先将正确的账目记录到账本中才行。 这个场景,对应的就是 InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作,把 checkpoint 往前推进,redo log 留出空间可以继续写。

checkpoint 可不是随便往前修改一下位置就可以的。比如图 2 中,把 checkpoint 位置从 CP 推进到 CP’,就需要将两个点之间的日志(浅绿色部分),对应的所有脏页都 flush 到磁盘上。之后,图中从 write pos 到 CP’之间就是可以再写入的 redo log 的区域。

2.2 mysql的内不足

第二种场景是,这一天生意太好,要记住的事情太多,掌柜发现自己快记不住了,赶紧找出账本把孔乙己这笔账先加进去。 这种场景,对应的就是系统内存不足。当需要新的内存页,而内存不够用的时候,就要淘汰一些数据页,空出内存给别的数据页使用。如果淘汰的是“脏页”,就要先将脏页写到磁盘。 你一定会说,这时候难道不能直接把内存淘汰掉,下次需要请求的时候,从磁盘读入数据页,然后拿 redo log 出来应用不就行了?这里其实是从性能考虑的。如果刷脏页一定会写盘,就保证了每个数据页有两种状态:

  • 一种是内存里存在,内存里就肯定是正确的结果,直接返回;
  • 另一种是内存里没有数据,就可以肯定数据文件上是正确的结果,读入内存后返回。 这样的效率最高。

2.3 MySQL 系统“空闲”

第三种场景是,生意不忙的时候,或者打烊之后。这时候柜台没事,掌柜闲着也是闲着,不如更新账本。 这种场景,对应的就是 MySQL 认为系统“空闲”的时候。当然,MySQL“这家酒店”的生意好起来可是会很快就能把粉板记满的,所以“掌柜”要合理地安排时间,即使是“生意好”的时候,也要见缝插针地找时间,只要有机会就刷一点“脏页”。

2.4 MySQL 正常关闭

第四种场景是,年底了咸亨酒店要关门几天,需要把账结清一下。这时候掌柜要把所有账都记到账本上,这样过完年重新开张的时候,就能就着账本明确账目情况了。 这种场景,对应的就是 MySQL 正常关闭的情况。这时候,MySQL 会把内存的脏页都 flush 到磁盘上,这样下次 MySQL 启动的时候,就可以直接从磁盘上读数据,启动速度会很快。

三、数据库flush下四种场景对性能的影响

3.1 redo log 写满对性能的影响

第一种是“redo log 写满了,要 flush 脏页”,这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候,整个系统就不能再接受更新了,所有的更新都必须堵住。如果你从监控上看,这时候更新数会跌为 0。

3.2 内存不够用对性能的影响

第二种是“内存不够用了,要先将脏页写到磁盘”,这种情况其实是常态。InnoDB 用缓冲池(buffer pool)管理内存,缓冲池中的内存页有三种状态:

  • 第一种是,还没有使用的;
  • 第二种是,使用了并且是干净页;
  • 第三种是,使用了并且是脏页。

InnoDB 的策略是尽量使用内存,因此对于一个长时间运行的库来说,未被使用的页面很少。

而当要读入的数据页没有在内存的时候,就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉:如果要淘汰的是一个干净页,就直接释放出来复用;但如果是脏页呢,就必须将脏页先刷到磁盘,变成干净页后才能复用。

所以,刷脏页虽然是常态,但是出现以下这两种情况,都是会明显影响性能的:

  1. 一个查询要淘汰的脏页个数太多,会导致查询的响应时间明显变长;
  2. 日志写满,更新全部堵住,写性能跌为 0,这种情况对敏感业务来说,是不能接受的。

所以,InnoDB 需要有控制脏页比例的机制,来尽量避免上面的这两种情况。

3.3 MySQL 系统“空闲”对性能的影响

第三种情况是属于 MySQL 空闲时的操作,这时系统没什么压力。

3.4 MySQL 正常关闭对性能的影响

第四种场景是数据库本来就要关闭了。这两种情况下,你不会太关注“性能”问题。所以这里,我们主要来分析一下前两种场景下的性能问题。

四、InnoDB 刷脏页的控制策略

首先,你要正确地告诉 InnoDB 所在主机的 IO 能力,这样 InnoDB 才能知道需要全力刷脏页的时候,可以刷多快。

这就要用到 innodb_io_capacity 这个参数了,它会告诉 InnoDB 你的磁盘能力。这个值我建议你设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试,下面的语句是我用来测试磁盘随机读写的命令:

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 

其实,因为没能正确地设置 innodb_io_capacity 参数,而导致的性能问题也比比皆是。之前,就曾有其他公司的开发负责人找我看一个库的性能问题,说 MySQL 的写入速度很慢,TPS 很低,但是数据库主机的 IO 压力并不大。经过一番排查,发现罪魁祸首就是这个参数的设置出了问题。

他的主机磁盘用的是 SSD,但是 innodb_io_capacity 的值设置的是 300。于是,InnoDB 认为这个系统的能力就这么差,所以刷脏页刷得特别慢,甚至比脏页生成的速度还慢,这样就造成了脏页累积,影响了查询和更新性能。

虽然我们现在已经定义了“全力刷脏页”的行为,但平时总不能一直是全力刷吧?毕竟磁盘能力不能只用来刷脏页,还需要服务用户请求。所以接下来,我们就一起看看 InnoDB 怎么控制引擎按照“全力”的百分比来刷脏页。

4.1 设计策略控制刷脏页策略

这个问题可以这么想,如果刷太慢,会出现什么情况?首先是内存脏页太多,其次是 redo log 写满。所以,InnoDB 的刷盘速度就是要参考这两个因素:一个是脏页比例,一个是 redo log 写盘速度。InnoDB 会根据这两个因素先单独算出两个数字。参数 innodb_max_dirty_pages_pct 是脏页比例上限,默认值是 75%。InnoDB 会根据当前的脏页比例(假设为 M),算出一个范围在 0 到 100 之间的数字,计算这个数字的伪代码类似这样:

  1. F1(M)
  2. {
  3. if M>=innodb_max_dirty_pages_pct then
  4. return 100;
  5. return 100*M/innodb_max_dirty_pages_pct;
  6. }

InnoDB 每次写入的日志都有一个序号,当前写入的序号跟 checkpoint 对应的序号之间的差值,我们假设为 N。InnoDB 会根据这个 N 算出一个范围在 0 到 100 之间的数字,这个计算公式可以记为 F2(N)。F2(N) 算法比较复杂,你只要知道 N 越大,算出来的值越大就好了。然后,根据上述算得的 F1(M) 和 F2(N) 两个值,取其中较大的值记为 R,之后引擎就可以按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。

图中的 F1、F2 就是上面我们通过脏页比例和 redo log 写入速度算出来的两个值。

InnoDB 刷脏页速度策略

现在你知道了,InnoDB 会在后台刷脏页,而刷脏页的过程是要将内存页写入磁盘。所以,无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页,还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句,都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。

要尽量避免这种情况,你就要合理地设置 innodb_io_capacity 的值,并且平时要多关注脏页比例,不要让它经常接近 75%

其中,脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的,具体的命令参考下面的代码:

  1. select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
  2. select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
  3. select @a/@b;

一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时,这个查询就可能要比平时慢了。而 MySQL 中的一个机制,可能让你的查询会更慢:在准备刷一个脏页的时候,如果这个数据页旁边的数据页刚好是脏页,就会把这个“邻居”也带着一起刷掉;而且这个把“邻居”拖下水的逻辑还可以继续蔓延,也就是对于每个邻居数据页,如果跟它相邻的数据页也还是脏页的话,也会被放到一起刷。

在 InnoDB 中,innodb_flush_neighbors 参数就是用来控制这个行为的,值为 1 的时候会有上述的“连坐”机制,值为 0 时表示不找邻居,自己刷自己的。在 MySQL 8.0 中,innodb_flush_neighbors 参数的默认值已经是 0 了。

找“邻居”这个优化在机械硬盘时代是很有意义的,可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百,相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。

而如果使用的是 SSD 这类 IOPS 比较高的设备的话,我就建议你把 innodb_flush_neighbors 的值设置成 0。因为这时候 IOPS 往往不是瓶颈,而“只刷自己”,就能更快地执行完必要的刷脏页操作,减少 SQL 语句响应时间。

五、条件字段函数操作

假设你现在维护了一个交易系统,其中交易记录表 tradelog 包含交易流水号(tradeid)、交易员 id(operator)、交易时间(t_modified)等字段。为了便于描述,我们先忽略其他字段。这个表的建表语句如下:

  1. CREATE TABLE `tradelog` (
  2. `id` int(11) NOT NULL,
  3. `tradeid` varchar(32) DEFAULT NULL,
  4. `operator` int(11) DEFAULT NULL,
  5. `t_modified` datetime DEFAULT NULL,
  6. PRIMARY KEY (`id`),
  7. KEY `tradeid` (`tradeid`),
  8. KEY `t_modified` (`t_modified`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假设,现在已经记录了从 2016 年初到 2018 年底的所有数据,运营部门有一个需求是,要统计发生在所有年份中 7 月份的交易记录总数。这个逻辑看上去并不复杂,SQL语句可能会这么写:

select count(*) from tradelog where month(t_modified)=7;

由于 t_modified 字段上有索引,于是你就很放心地在生产库中执行了这条语句,但却发现执行了特别久,才返回了结果。如果你问 DBA 同事为什么会出现这样的情况,他大概会告诉你:如果对字段做了函数计算,就用不上索引了,这是 MySQL 的规定。现在你已经学过了 InnoDB 的索引结构了,可以再追问一句为什么?为什么条件是 where t_modified='2018-7-1’的时候可以用上索引,而改成 where month(t_modified)=7 的时候就不行了?

t_modified 索引示意图

如果你的 SQL 语句条件用的是 where t_modified='2018-7-1’的话,引擎就会按照上面绿色箭头的路线,快速定位到 t_modified='2018-7-1’需要的结果。实际上,B+ 树提供的这个快速定位能力,来源于同一层兄弟节点的有序性。但是,如果计算 month() 函数的话,你会看到传入 7 的时候,在树的第一层就不知道该怎么办了。也就是说,对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。需要注意的是,优化器并不是要放弃使用这个索引。在这个例子里,放弃了树搜索功能,优化器可以选择遍历主键索引,也可以选择遍历索引 t_modified,优化器对比索引大小后发现,索引 t_modified 更小,遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 t_modified。

我们使用 explain 命令,查看一下这条 SQL 语句的执行结果。

 key="t_modified"表示的是,使用了 t_modified 这个索引;我在测试表数据中插入了 10 万行数据,rows=100335,说明这条语句扫描了整个索引的所有值;Extra 字段的 Using index,表示的是使用了覆盖索引。也就是说,由于在 t_modified 字段加了 month() 函数操作,导致了全索引扫描。为了能够用上索引的快速定位能力,我们就要把 SQL 语句改成基于字段本身的范围查询。按照下面这个写法,优化器就能按照我们预期的,用上 t_modified 索引的快速定位能力了。

  1. select count(*) from tradelog where
  2. (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
  3. (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or
  4. (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

由于加了 month() 函数操作,MySQL 无法再使用索引快速定位功能,而只能使用全索引扫描。不过优化器在个问题上确实有“偷懒”行为,即使是对于不改变有序性的函数,也不会考虑使用索引。比如,对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句,这个加 1 操作并不会改变有序性,但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。所以,需要你在写 SQL 语句的时候,手动改写成 where id = 10000 -1 才可以。

六、隐式类型转换

select * from tradelog where tradeid=110717;

交易编号 tradeid 这个字段上,本来就有索引,但是 explain 的结果却显示,这条语句需要走全表扫描。你可能也发现了,tradeid 的字段类型是 varchar(32),而输入的参数却是整型,所以需要做类型转换。那么,现在这里就有两个问题:

6.1 数据类型转换的规则是什么?

这里有一个简单的方法,看 select “10” > 9 的结果:

  1. 如果规则是“将字符串转成数字”,那么就是做数字比较,结果应该是 1;
  2. 如果规则是“将数字转成字符串”,那么就是做字符串比较,结果应该是 0。

从图中可知,select “10” > 9 返回的是 1,所以你就能确认 MySQL 里的转换规则了:在 MySQL 中,字符串和数字做比较的话,是将字符串转换成数字。

6.2 为什么有数据类型转换,就需要走全索引扫描?

select * from tradelog where tradeid=110717;

就知道对于优化器来说,这个语句相当于:

select * from tradelog where  CAST(tradid AS signed int) = 110717;

也就是说,这条语句触发了我们上面说到的规则:对索引字段做函数操作,优化器会放弃走树搜索功能。

如果id 的类型是 int,如果执行下面这个语句,是否会导致全表扫描呢?

select * from tradelog where id="83126";

七、隐式字符编码转换

假设系统里还有另外一个表 trade_detail,用于记录交易的操作细节。为了便于量化分析和复现,我往交易日志表 tradelog 和交易详情表 trade_detail 这两个表里插入一些数据。

  1. CREATE TABLE `trade_detail` (
  2. `id` int(11) NOT NULL,
  3. `tradeid` varchar(32) DEFAULT NULL,
  4. `trade_step` int(11) DEFAULT NULL, /* 操作步骤 */
  5. `step_info` varchar(32) DEFAULT NULL, /* 步骤信息 */
  6. PRIMARY KEY (`id`),
  7. KEY `tradeid` (`tradeid`)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1. insert into tradelog values(1, 'aaaaaaaa', 1000, now());
  2. insert into tradelog values(2, 'aaaaaaab', 1000, now());
  3. insert into tradelog values(3, 'aaaaaaac', 1000, now());
  4. insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
  5. insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
  6. insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
  7. insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
  8. insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
  9. insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
  10. insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
  11. insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
  12. insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
  13. insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
  14. insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

这时候,如果要查询 id=2 的交易的所有操作步骤信息,SQL 语句可以这么写:

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /* 语句 Q1*/

我们一起来看下这个结果:

  1. 第一行显示优化器会先在交易记录表 tradelog 上查到 id=2 的行,这个步骤用上了主键索引,rows=1 表示只扫描一行;
  2. 第二行 key=NULL,表示没有用上交易详情表 trade_detail 上的 tradeid 索引,进行了全表扫描。

在这个执行计划里,是从 tradelog 表中取 tradeid 字段,再去 trade_detail 表里查询匹配字段。因此,我们把 tradelog 称为驱动表,把 trade_detail 称为被驱动表,把 tradeid 称为关联字段。

  • 第 1 步,是根据 id 在 tradelog 表里找到 L2 这一行;
  • 第 2 步,是从 L2 中取出 tradeid 字段的值;
  • 第 3 步,是根据 tradeid 值到 trade_detail 表中查找条件匹配的行。explain 的结果里面第二行的 key=NULL 表示的就是,这个过程是通过遍历主键索引的方式,一个一个地判断 tradeid 的值是否匹配。

你会发现第 3 步不符合我们的预期。因为表 trade_detail 里 tradeid 字段上是有索引的,我们本来是希望通过使用 tradeid 索引能够快速定位到等值的行。但,这里并没有。如果你去问 DBA 同学,他们可能会告诉你,因为这两个表的字符集不同,一个是 utf8,一个是 utf8mb4,所以做表连接查询的时候用不上关联字段的索引。这个回答,也是通常你搜索这个问题时会得到的答案。

我们说问题是出在执行步骤的第 3 步,如果单独把这一步改成 SQL 语句的话,那就是:

select * from trade_detail where tradeid=$L2.tradeid.value; 

其中,$L2.tradeid.value 的字符集是 utf8mb4。

参照前面的两个例子,你肯定就想到了,字符集 utf8mb4 是 utf8 的超集,所以当这两个类型的字符串在做比较的时候,MySQL 内部的操作是,先把 utf8 字符串转成 utf8mb4 字符集,再做比较。

因此, 在执行上面这个语句的时候,需要将被驱动数据表里的字段一个个地转换成 utf8mb4,再跟 L2 做比较。也就是说,实际上这个语句等同于下面这个写法:

select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value; 

作为对比验证,我给你提另外一个需求,“查找 trade_detail 表里 id=4 的操作,对应的操作者是谁”,再来看下这个语句和它的执行计划。

select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

这个语句里 trade_detail 表成了驱动表,但是 explain 结果的第二行显示,这次的查询操作用上了被驱动表 tradelog 里的索引 (tradeid),扫描行数是 1。

这也是两个 tradeid 字段的 join 操作,为什么这次能用上被驱动表的 tradeid 索引呢?

假设驱动表 trade_detail 里 id=4 的行记为 R4,那么在连接的时候(图 5 的第 3 步),被驱动表 tradelog 上执行的就是类似这样的 SQL 语句:

select operator from tradelog  where traideid =$R4.tradeid.value; 

你看,这里的 CONVERT 函数是加在输入参数上的,这样就可以用上被驱动表的 traideid 索引。

理解了原理以后,就可以用来指导操作了。如果要优化语句。

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

比较常见的优化方法是,把 trade_detail 表上的 tradeid 字段的字符集也改成 utf8mb4,这样就没有字符集转换的问题了。

alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;

如果能够修改字段的字符集的话,是最好不过了。但如果数据量比较大, 或者业务上暂时不能做这个 DDL 的话,那就只能采用修改 SQL 语句的方法了。

select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 

八、查询长时间不返回

需要说明的是,如果 MySQL 数据库本身就有很大的压力,导致数据库服务器 CPU 占用率很高或 ioutil(IO 利用率)很高,这种情况下所有语句的执行都有可能变慢,。

一般情况下,如果我跟你说查询性能优化,你首先会想到一些复杂的语句,想到查询需要返回大量的数据。但有些情况下,“查一行”,也会执行得特别慢。

为了便于描述,我还是构造一个表,基于这个表来说明今天的问题。这个表有两个字段 id 和 c,并且我在里面插入了 10 万行记录。

  1. CREATE TABLE `t` (
  2. `id` int(11) NOT NULL,
  3. `c` int(11) DEFAULT NULL,
  4. PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB;
  1. delimiter ;;
  2. create procedure idata()
  3. begin
  4. declare i int;
  5. set i=1;
  6. while(i<=100000)do
  7. insert into t values(i,i);
  8. set i=i+1;
  9. end while;
  10. end;;
  11. delimiter ;
  12. call idata();

在表 t 执行下面的 SQL 语句:

select * from t where id=1;

查询结果长时间不返回。一般碰到这种情况的话,大概率是表 t 被锁住了。接下来分析原因的时候,一般都是首先执行一下 show processlist 命令,看看当前语句处于什么状态。然后我们再针对每种状态,去分析它们产生的原因、如何复现,以及如何处理。

8.1 等 MDL 锁

就是使用 show processlist 命令查看 Waiting for table metadata lock 的示意图。

Waiting for table metadata lock 状态示意图

出现这个状态表示的是,现在有一个线程正在表 t 上请求或者持有 MDL 写锁,把 select 语句堵住了。我给你介绍过一种复现方法。但需要说明的是,那个复现过程是基于 MySQL 5.6 版本的。而 MySQL 5.7 版本修改了 MDL 的加锁策略,所以就不能复现这个场景了。

不过,在 MySQL 5.7 版本下复现这个场景,也很容易。如图 3 所示,我给出了简单的复现步骤。

session A 通过 lock table 命令持有表 t 的 MDL 写锁,而 session B 的查询需要获取 MDL 读锁。所以,session B 进入等待状态。这类问题的处理方式,就是找到谁持有 MDL 写锁,然后把它 kill 掉。

但是,由于在 show processlist 的结果里面,session A 的 Command 列是“Sleep”,导致查找起来很不方便。不过有了 performance_schema 和 sys 系统库以后,就方便多了。(MySQL 启动时需要设置 performance_schema=on,相比于设置为 off 会有 10% 左右的性能损失)。通过查询 sys.schema_table_lock_waits 这张表,我们就可以直接找出造成阻塞的 process id,把这个连接用 kill 命令断开即可。

8.2 等 flush过程

我在表 t 上,执行下面的 SQL 语句:

select * from information_schema.processlist where id=1;

你可以看一下图 5。我查出来这个线程的状态是 Waiting for table flush,

  1. flush tables t with read lock;
  2. flush tables with read lock;

这两个 flush 语句,如果指定表 t 的话,代表的是只关闭表 t;如果没有指定具体的表名,则表示关闭 MySQL 里所有打开的表。但是正常这两个语句执行起来都很快,除非它们也被别的线程堵住了。所以,出现 Waiting for table flush 状态的可能情况是:有一个 flush tables 命令被别的语句堵住了,然后它又堵住了我们的 select 语句。

现在,我们一起来复现一下这种情况,复现步骤如图 6 所示:

Waiting for table flush 的复现步骤

在 session A 中,我故意每行都调用一次 sleep(1),这样这个语句默认要执行 10 万秒,在这期间表 t 一直是被 session A“打开”着。然后,session B 的 flush tables t 命令再要去关闭表 t,就需要等 session A 的查询结束。这样,session C 要再次查询的话,就会被 flush 命令堵住了。

图 7 是这个复现步骤的 show processlist 结果。这个例子的排查也很简单,你看到这个 show processlist 的结果,肯定就知道应该怎么做了。

Waiting for table flush 的 show processlist 结果

8.3 等行锁

经过了表级锁的考验,我们的 select 语句终于来到引擎里了。

select * from t where id=1 lock in share mode; 

由于访问 id=1 这个记录时要加读锁,如果这时候已经有一个事务在这行记录上持有一个写锁,我们的 select 语句就会被堵住。

行锁复现
行锁 show processlist 现场

显然,session A 启动了事务,占有写锁,还不提交,是导致 session B 被堵住的原因。

这个问题并不难分析,但问题是怎么查出是谁占着这个写锁。如果你用的是 MySQL 5.7 版本,可以通过 sys.innodb_lock_waits 表查到。查询方法是:

select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G
通过 sys.innodb_lock_waits 查行锁

可以看到,这个信息很全,4 号线程是造成堵塞的罪魁祸首。而干掉这个罪魁祸首的方式,就是 KILL QUERY 4 或 KILL 4。不过,这里不应该显示“KILL QUERY 4”。这个命令表示停止 4 号线程当前正在执行的语句,而这个方法其实是没有用的。因为占有行锁的是 update 语句,这个语句已经是之前执行完成了的,现在执行 KILL QUERY,无法让这个事务去掉 id=1 上的行锁。实际上,KILL 4 才有效,也就是说直接断开这个连接。这里隐含的一个逻辑就是,连接被断开的时候,会自动回滚这个连接里面正在执行的线程,也就释放了 id=1 上的行锁。

九、查询慢

经过了重重封“锁”,我们再来看看一些查询慢的例子。先来看一条你一定知道原因的 SQL 语句:

mysql> select * from t where c=50000 limit 1;

由于字段 c 上没有索引,这个语句只能走 id 主键顺序扫描,因此需要扫描 5 万行。作为确认,你可以看一下慢查询日志。注意,这里为了把所有语句记录到 slow log 里,我在连接后先执行了 set long_query_time=0,将慢查询日志的时间阈值设置为 0。

全表扫描 5 万行的 slow log

 Rows_examined 显示扫描了 50000 行。你可能会说,不是很慢呀,11.5 毫秒就返回了,我们线上一般都配置超过 1 秒才算慢查询。但你要记住:坏查询不一定是慢查询。我们这个例子里面只有 10 万行记录,数据量大起来的话,执行时间就线性涨上去了。扫描行数多,所以执行慢,这个很好理解。

但是接下来,我们再看一个只扫描一行,但是执行很慢的语句。

select * from t where id=1

虽然扫描行数是 1,但执行时间却长达 800 毫秒。

加上 lock in share mode 的 slow log

如果我把这个 slow log 的截图再往下拉一点,你可以看到下一个语句,select * from t where id=1 lock in share mode,执行时扫描行数也是 1 行,执行时间是 0.2 毫秒。

看上去是不是更奇怪了?按理说 lock in share mode 还要加锁,时间应该更长才对啊。

两个语句的输出结果

第一个语句的查询结果里 c=1,带 lock in share mode 的语句返回的是 c=1000001。看到这里应该有更多的同学知道原因了。如果你还是没有头绪的话,也别着急。我先跟你说明一下复现步骤,再分析原因。 

你看到了,session A 先用 start transaction with consistent snapshot 命令启动了一个事务,之后 session B 才开始执行 update 语句。

session B 执行完 100 万次 update 语句后,id=1 这一行处于什么状态呢?你可以从图 16 中找到答案。

id=1 的数据状态

session B 更新完 100 万次,生成了 100 万个回滚日志 (undo log)。带 lock in share mode 的 SQL 语句,是当前读,因此会直接读到 1000001 这个结果,所以速度很快;而 select * from t where id=1 这个语句,是一致性读,因此需要从 1000001 开始,依次执行 undo log,执行了 100 万次以后,才将 1 这个结果返回。注意,undo log 里记录的其实是“把 2 改成 1”,“把 3 改成 2”这样的操作逻辑,画成减 1 的目的是方便你看图。

博文参考

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/煮酒与君饮/article/detail/771762
推荐阅读
相关标签
  

闽ICP备14008679号