当前位置：首页 > news >正文

《MySQL实战45讲》——学习笔记19 “SQL查一行执行慢的排查、锁等待/一致性读“【建议收藏】

news 来源：原创 2024/5/14 7:09:08

由于SQL本身的写法问题（如join太多表、未走索引/索引失效、一次查太多数据等），或是MySQL节点CPU占用率很高或IO利用率很高，都会导致一条SQL执行的比较慢；但是有时候，"只查一行数据"，也会出现"比较慢"的现象；

本篇例举在一个在一个简单的表上执行"查一行“的SQL语句可能出现被锁住和执行慢现象的例子，其中涉及到了表锁、行锁和一致性读的概念；

为了便于描述，下面构造一个表，基于这个表来说明问题；这个表有两个字段id和c，并且我在里面插入了(1,1)、(2,2)...、(100000,100000)这10万行记录；

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

现象A：查单条记录的SQL执行后长时间不返回

现象描述：执行下面的一条简单的SQL语句，查询结果长时间不返回；

select * from t where id=1;

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

情况1：等MDL锁

元数据锁（metadata lock）简称MDL锁，是表级锁；MDL不需要显式使用，在访问一个表的时候会被自动加上，它的作用是保证读写的正确性；

当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁；读锁之间不互斥，读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性；

下面给出这条SQL长时间不返回的简单的复现步骤；

分析：sessionA通过lock table命令持有表t的MDL写锁，而sessionB的查询需要获取MDL读锁；所以，sessionB进入等待状态；

这类问题的处理方式，就是找到谁持有MDL写锁，然后把它kill掉；如何找到这条进程ID呢？——通过查询 sys.schema_table_lock_waits 这张表，就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可；

情况2：等flush表

接下来，介绍外一种查询被堵住的情况；在表t上，执行下面的SQL语句查看进程列表；

select * from information_schema.processlist;

查出来这个线程的状态是 Waiting for table flush；

先来说一下flush表的作用，flush tables意味着关闭所有已打开的表对象，同时将查询缓存中的结果清空；就是说flush tables的一个效果就是会等待所有正在运行的SQL请求结束；因为，SQL语句在执行前，都会打开相应的表对象，如 select * fromt t 语句，会找到表t的frm文件，并打开表内存对象；为了控制表对象使用的内存空间和其他资源，MySQL会隐式（后台表对象管理线程）或显式（flush tables等）来关闭已打开但并没有使用的表对象；然而，正在使用的表对象是不能关闭的（如SQL请求仍在运行），因此，flush tables操作会被正在运行的SQL请求阻塞；

正常情况下，flush tables 语句执行起来很快，除非它也被别的线程堵住了；所以，出现Waiting for table flush状态的可能情况是：有一个flush tables命令被别的语句堵住了，然后它又堵住了我们的select语句；

复现一下这种情况，复现步骤下；

分析：

（1）在sessionA中，我故意每行都调用一次sleep(1)，这样在这个有10W条数据的表中，这个语句默认要执行10万秒，在这期间表t一直是被sessionA“打开”着；
（2）然后，sessionB的flush tablest命令再要去关闭表t，就需要等sessionA的查询结束；
（3）这样，sessionC要再次查询的话，就会被flush命令堵住了；

下图是这个复现步骤的 show processlist 结果；

这个例子的排查也很简单，当看到这个 show processlist 的结果，去找sessionA的线程ID，就要先将sessionA的进程先kill掉，然后等flush tables的命令执行完，select * from t where id=1 的命令就能执行了；

情况3：等行锁

现在，这条语句经过了表级锁的考验，终于来到存储引擎这一层了；执行下面的SQL语句：

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

这条语句的用法表示"当前读"，也就是说需要获取最新的数据，因此需要加读锁（S锁，共享锁）；如果此时有事务正在更新这条数据，那么这个事务持有这行数据的一个写锁（X锁，排他锁），那这条"当前读"的SQL就需要等这个事务提交后释放锁，释放前， select 语句就会被一直阻塞等待获取锁；

复现一下这种情况，复现步骤下；

分析：sessionA启动了事务，占有写锁，还未提交，导致sessionB被堵住；

这个问题并不难分析，但问题是怎么查出是谁占着这个写锁；如果你用的是MySQL5.7版本，可以通过sys.innodb_lock_waits表查到；查询方法是：

mysql> select * from t sys.innodb_lock_waits where locked_table='`test`.`t`'\G

结果如下：

可以看到，这个信息很全，线程ID=4的S锁阻塞了后面SQL语句，通过 KILL 4 命令即可释放行锁；连接被断开的时候，会自动回滚这个连接里面正在执行的线程，也就释放了 id=1 这条记录上的行锁；

现象B：查单条记录的SQL执行实际耗时长

经过了重重封“锁”，再来看看一些查询实际执行慢的例子；

情况1：全表扫描

执行以下SQL语句：

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

由于字段c上没有索引，这个语句只能走id主键顺序扫描，查完所有记录都不满足条件，因此需要扫描10万行；作为确认，可以看一下慢查询日志；

Rows_examined显示扫描了50000行，执行时长11ms；你可能会说，11.5ms就返回了不是很慢，线上一般都配置超过1000ms才算慢查询；但你要记住：坏查询不一定是慢查询，当某个条件变化时如表数据量增大，原先的坏查询就"原形毕露"了；我们这个例子里面只有10万行记录，如果数据量大起来的话，执行时间就线性涨上去了；扫描行数多，所以执行慢，这个很好理解；

情况2：大量更新产生大量的undo log

执行以下SQL语句并查看它的慢日志 slow log：

select * from t where id=1;

现象：虽然扫描行数是1，但执行时间却长达800ms；

再看下一条SQL语句以及他的慢日志：

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；

分析：说明"当前读"读到的最新版本的数据c的值是1000001，而第一条语句"一致性视图"中这行记录的版本数据c=1，说明这中间一定存在对这条记录的更新操作；

复现一下这种情况，复现步骤下；

sessionA先用start transaction with consistent snapshot命令启动了一个事务，之后sessionB执行100万次update语句，此时id=1这一行的状态如下；

sessionB更新完100万次，生成了100万个回滚日志(undolog)；带lock in share mode的SQL语句是当前读，因此会直接读到1000001这个结果，所以速度很快；而select * from t whereid=1这个语句是一致性读，因此需要从1000001开始，依次执行undolog，执行了100万次以后，才将1这个结果返回，所以较慢；

小结

本文介绍了在一个简单的表上执行“查一行”的SQL，也可能会出现执行慢的情况；

（1）对于长时间不返回的情况，原因可能是：等MDL表锁、等flush表（flush之前有较多的还在执行的SQL）、"当前读"等行锁；

（2）对于一个简单查询执行时间远大于预期时间的情况，原因可能是：查询时，未提交的事务中对该行记录执行了大量的更新，导致"一致性读"时需要从当前版本执行多次undolog；

下篇文章：待定

本章参考：19 | 为什么我只查一行的语句，也执行这么慢？-极客时间