MYSQL45讲学习笔记

19.为什么我只查一行的语句，也执行这么慢？

一般情况下，如果查询的数据量非常大，或者SQL语句非常复杂，那么可能SQL执行的时间会比较长。但是如果只查一条记录，SQL语句执行也非常慢呢？

当然如果系统CPU使用率过高，或者数据库IO压力很大， 也会造成SQL语句执行慢，以上几种场景不在本节讨论范围内。

为了方便说明，我们还是创建一张表，基于这张表来说明今天的问题。

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();

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

执行下面的SQL语句：

select * from t where id=1;

结果长时间不返回。

一般产生这种情况，大概率是表被锁住了。碰到这种情况， 一般是使用 show processlist 来查看线程的状态，然后再根据具体场景具体分析。

1.等MDL锁

 上图为 show processlist 的结果，显示当前线程在 waiting for table metadata lock，也就是在等待元数据锁，因此阻塞了select 操作。

对数据表上 MDL写锁 有两种方式：1.执行DDL操作，2.使用 lock table t write 语句。

因为MYSQL在5.6版本引入了 online DDL操作，会讲DDL 操作的 MDL写锁降级为读锁，所以执行DDL 操作不能复现这种场景，但是我们还可以用 lock table t write 语句。

session A	session B
lock table t write;
	select * from t where id=1;

如上图所示，session A 对表上了 MDL 写锁，因此session B 的select 操作被阻塞。

对于这种问题的解决办法，很简单，就是找到谁持有 MDL写锁，然后将它 kill掉。

show processlist 展示的信息有时不能很好的判断出 是谁持有了MDL写锁，但是有了 performance_schema 和 sys 系统表之后，就方便多了，（在启动时需要设置 performance_schema = ON,相比于设置为 OFF会有 10%的性能损失）

通过查看 sys.schema_table_lock_waits 表中的 blocking_pid，我们可以找到造成阻塞的 process id,然后将其 kill 掉即可。

等flush

如上图所示，显示有一个线程状态为 waiting for table flush,在等待flush。 

对表执行flush操作的写法有：

flush tables with read lock;
flush tables t with read lock;

语句1 表示对所有表执行 flush操作，语句2表示仅对表t 做flush操作。

但是这两个flush 操作其实执行也是很快的，除非这个flush操作被其他的线程阻塞了，然后这个flush操作又阻塞了 select 语句。

让我们来复现一下这种场景：

session A	session B	session C
select sleep(1) from t;
	flush tables t with read lock;
		select * from t where id = 1;

如上表所示，session A执行了sleep(1) 操作，sleep(1)代表有一条满足条件的数据，就执行一秒，因此session A会执行 10万秒，阻塞了session B 的flush 操作，也就因此阻塞了 session C的select 操作。

等行锁

经过了全剧锁和表级锁的重重考验之后，我们来到了 行锁 环节，来到了引擎层。

执行下面语句：

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

假如执行这条语句之前，有一个事务获得了ID =1 的行的写锁，并且没有释放，那么这个 select 语句就会被阻塞。

我们来复现一下这种场景：

session A	session B
begin;
update set c=c+1 where id = 1;
	select * from t where id = 1 lock in share mode;

如果MYSQL版本是5.7版本及以后，我们可以查看 sys.innodb_lock_waits表中的信息，来获取持有行锁的线程。

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

查询出来的信息很全，可以看到 blocking_pid 的值为 4，也就是线程4 阻塞了 select 语句。

同时还给出了解决办法， kill query 4 或者 kill 4, 但是其实这里不应该返回 “KILL QUERY 4”。“KILL QUERY 4”表示停止当前线程正在执行的操作，而占有行锁的是update 语句，这个语句已经完成了，所以无法释放行锁。

正确做法是执行“kill 4”，即关闭当前连接，也就自然释放了 行锁。

第二类：查询慢

让我们看下这条SQL语句：

select * from t where c = 50000 limit 1;

 这个问题很简单，因为 C列上没有索引，只能走 主键索引顺序扫描，扫描50000行之后返回数据。

上图为慢查询日志，扫描行数为 50000，查询时间为 11.5毫秒。

上图中的查询语句并不是很慢，但是需要注意，这是一个 坏查询语句，随着表中数据量的增大， 坏查询的执行时间将会线性增长。因此实际开发中要尽量避免。

下面我们再来看另一种情况。

session A	session B
start transaction with consistent snapshot;
	update t set c = c+1 where id = 1; // 执行100万次
select * from t wehre id = 1;
select * from t where id = 1 lock in share mode;

上图为 session A中两条 SQL语句的执行时间，可以看到，第一条语句执行了0.81秒，而 lock in share mode 反而只用了几乎0秒。

这是因为第一条语句为快照读，innodb引擎需要根据 undo log 会滚100W次，得到事务开始时的视图，而lock in share mode 是当前读，直接拿表中数据就可以。

上图为MYSQL根据 undo log 段进行会滚操作，需要注意 undo log 里其实记录的是 “把3改成2”，“把2改成1”这样的逻辑操作， 图片里的 减1 只是为了方便理解。

小结

本节介绍了MYQL执行简单的语句为什么非常慢的原因，包括，等flush,等MDL锁，等行锁，因为MVCC造成的查询慢等等，实际开发中遇到的场景问题可能会更复杂，但是只要我们掌握了方式方法，抽丝剥茧，都是可以慢慢查出来的，重点还是要掌握排除问题的思路和方法。

如果碰到上述问题，可以用show processlist 命令来查看状态，进行分析，

遇到表锁和全局锁，可以查看 sys.scheme_table_lock_wais;遇到行锁，可以查看sys.innodb_lock_waits。配合慢查询日志使用。