MySQL查询执行（四）：查一行也很慢

假设存在表t，这个表有两个字段id和c，并且我在里面插入了10万行记录。

-- 创建表t
CREATE TABLE `t` (`id` int(11) NOT NULL,`c` int(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB;-- 通过存储过程向t写入10w行数据
delimiter ;;
create procedure idata()
begindeclare i int;set i=1;while(i<=100000)doinsert into t values(i,i);set i=i+1;end while;
end;;
delimiter ;-- 插入数据
call idata();

查询长时间不返回

---

查询长时间不返回的原因：被其他线程堵住了。

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

select * from t where id = 1;

查询结果长时间不返回。

一般碰到这种情况的话， 大概率是表t被锁住了。 接下来分析原因的时候， 一般都是首先执行一下show processlist命令， 看看当前语句处于什么状态。

然后我们再针对每种状态， 去分析它们产生的原因、 如何复现， 以及如何处理。

场景一：等MDL锁

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

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

在MySQL 5.7版本下复现这个场景， 如图所示， 我给出了简单的复现步骤。

session A 通过lock table命令持有表t的MDL写锁， 而session B的查询需要获取MDL读锁。 所以， session B进入等待状态。

这类问题的处理方式， 就是找到谁持有MDL写锁， 然后把它kill掉。

问：如何找到是谁持有MDL写锁呢？

方式一：如果session A在加表锁后，接着执行了别的语句，那么便可通过执行show processlist命令找到造成阻塞的process id。

方式二：如果如果session A在加表锁后，没有执行任何命令，即Command列是“Sleep”。此时需要通过查询sys.schema_table_lock_waits这张表，找出造成阻塞的process id。（MySQL启动时需要设置performance_schema=on，MySQL5.5默认是关闭此功能的，若要开启，需手动在配置文件里添加，从MySQL5.6开始，默认打开此功能）

在上述场景下，另启一个session C，然后执行：

select blocking_pid from sys.schema_table_lock_waits;

执行结果：

最后，在找到造成阻塞的process id后，把这个连接用kill命令断开即可。

场景二：等flush

MySQL里面对表做flush操作的用法， 一般有以下两个：

-- flush指定table
flush tables t with read lock;--flush全部table 
flush tables with read lock;

这两个flush语句， 如果指定表t的话， 代表的是只关闭表t； 如果没有指定具体的表名， 则表示关闭MySQL里所有打开的表。

正常情况下，这两个语句执行起来都很快， 除非它们也被别的线程堵住了。

所以， 出现Waiting for table flush状态的可能情况是： 有一个flush tables命令被别的语句堵住了， 然后它又堵住了我们的select语句。

注：flush table的作用，关闭表，并清空查询缓存。

现在， 我们一起来复现一下这种情况， 复现步骤如图所示：

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

这个复现步骤的show processlist结果：

看到这个show processlist的结果， 肯定就知道应该怎么做了。

场景三：等行锁

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

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

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

复现步骤和现场如下：

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

问：等行锁的场景下，怎么查出是谁占着这个写锁呢？

答：如果是MySQL5.7及以上版本，可以通过sys.innodb_lock_waits 表查到。

查询方法：

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

执行结果：

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

查询慢

---

经过了重重封“锁”， 我们再来看看一些查询慢的例子。

先来看一条你一定知道原因的SQL语句：

select * from t where c = 50000 limit 1;

由于字段c上没有索引， 这个语句只能走id主键顺序扫描， 因此需要扫描5万行。

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

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)。

session A在执行select * from t where id=1时，需要判断当前记录的可见性，由于session B执行了100万次，所以session A需要判断100万次，这也就是为什么select * from t where id=1语句执行的比较慢。

session A在执行select * from t where id=1 lock in share mode时，直接读最新记录即可，无需判断记录的可见性，所以执行的比较快。

上述两个语句的执行输出结果：

 假设有如下表结构：

CREATE TABLE `t`(`id` int(11) NOT NULL,`c` int(11) DEFAULT NULL,`d` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY c (`c`)
) ENGINE=InnoDB;insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

小结：思考题

---

思考：如果执行如下语句，语句序列是怎么加锁的？锁又是什么时候释放的？

begin;
select * from t where c = 5 for update;
commit;

 这个语句会命中d=5的这一行， 对应的主键id=5， 因此在select 语句执行完成后， id=5这一行会加一个写锁， 而且由于两阶段锁协议， 这个写锁会在执行commit语句的时候释放。