MySQL15-只查一行数据却执行很慢

1、先编写表结构和数据

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

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i,i)
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

第一类： 查询长时间不返回

如图1所示， 在表t执行下面的SQL语句：

mysql> select * from t where id=1;

查询结果长时间不返回。

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

第一种：等MDL锁

出现这个状态表示的是， 现在有一个线程正在表t上请求或者持有MDL写锁， 把select语句堵住了。

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

第二种：等flush（关闭table）

有一个flush tables命令被别的语句堵住了， 然后它又堵住了我们的select语句。

flush表的操作方式

• flush tables t with read lock;关闭指定表t
• flush tables with read lock;关闭所有表

复现情景

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

通过show processlist结果

第三种：等行锁

比如执行下面语句

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

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

复现场景

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

如何知道谁持有了两这个写锁？
查询方法是：

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

可以看到， 这个信息很全， 4号线程是造成堵塞的罪魁祸首。 而干掉这个罪魁祸首的方式， 就是KILL QUERY 4或KILL 4。

不过， 这里不应该显示“KILL QUERY 4”。 这个命令表示停止4号线程当前正在执行的语句， 而这个方法其实是没有用的。 因为占有行锁的是update语句， 这个语句已经是之前执行完成了的，现在执行KILL QUERY， 无法让这个事务去掉id=1上的行锁

KILL 4才有效， 也就是说直接断开这个连接。 这里隐含的一个逻辑就是， 连接被断开的时候， 会自动回滚这个连接里面正在执行的线程， 也就释放了id=1上的行锁。

第二类：查询慢

1. 全表扫描

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

如果c字段上面没有添加索引，那么需要遍历主键索引

2. 只扫描一行

mysql> select * from t where id=1；

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

是不是有点奇怪呢， 这些时间都花在哪里了？

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

看上去是不是更奇怪了？ 按理说lock in share mode还要加锁， 时间应该更长才对啊。我们看一下这两个语句的执行输出结果

复现场景

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

session B更新完100万次， 生成了100万个回滚日志(undo log)。

带lock in share mode的SQL语句， 是当前读， 因此会直接读到1000001这个结果， 所以速度很快； 而select * from t where id=1这个语句， 是一致性读， 因此需要从1000001开始， 依次执行undo log， 执行了100万次以后， 才将1这个结果返回。