MySQL实战：为什么我就查一行语句，也执行这么慢？

首先有一张表如下所示：

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

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

如下的查询代码：

mysql> select * from t where id=1;

查询结果长时间不返回

大概率是表t被锁住了，show processlist 查看命令

等待 MDL 锁

当前有一个线程正在表T上请求或者持有MDL写锁，把select语句锁住了

• 解决办法：
  通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。

等 flush

结果也很幼稚，sleep（1）是每次执行的，因此事务B每次都需要等待这个事务A查询完再可以flush关闭，事务C需要等到事务Bcommit之后才能执行，因此造成事务C永远无法执行

等行锁

这个也容易理解，X锁和S锁之间的排斥，事务A执行了更新操作，拿到了X锁，但是不提交，这也是事务B被堵住的原因。

• 解决措施——杀连接

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

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

第二类：查询慢

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

这条其实是我们建库时候的锅，因为C上面我们并没有指定index，使其成为普通索引，因此这个语句只能走主键索引，并且我们的数据特征是 主键和C都是顺序递增且相同的，因此这个语句需要扫描5万行，并且每次都要把每条数据拿出来，看一下这个C字段是不是等于 50000。

看一下慢查询日志（时间阈值设置为0）

11.5毫秒的花费时间，如果数据量再大，那这个时间就不可接受

接下来再看一个只扫描一行的查询语句的实现，代码如下：

mysql> select * from t where id=1；

实现方式如下：

事务A启动了一个持续事务，事务B进行了100万次更新之后，事务A先后进行了两次查询，结果是第一次使用时间特别长，第二次时间特别短，显然有问题的是第一次读，当然也不能说是有问题,

• 只不过第一条查询语句默认是可重复读（一致性读），因此需要根据100万次的undo log 回滚日志去从100万开始进行回滚100万次，当然花费很多时间了。
• 第二次使用的是share mode 是当前读的隔离级别，此时直接读100万零1即可，不用回滚，因此很快

总结与思考题

• 总结
  这节课虽然是从一条慢查询的语句说来的，但是涉及了表锁，行锁，一致性读的知识。

• 思考题
  我们在举例加锁读的时候，用的是这个语句，select * from t where id=1 lock in share mode。由于 id 上有索引，所以可以直接定位到 id=1 这一行，因此读锁也是只加在了这一行上。但如果是下面的 SQL 语句，

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

由于C不是主键，也没有索引，所以需要一条条数据进行查找并且进行加行锁，但是检测出不符合条件，行锁就会被释放，从select开始就会对数据开始进行加锁，只不过c=5地方的锁需要等到commit的时候才释放。