《MySql学习》 Select 查询语句慢的非性能原因

一.查询被阻塞

A会话执行 查询操作，长时间没有返回信息,此时我们就可以去排查一下是否是被阻塞了

select * from words 

被阻塞的原因有很多，首先列举第一种情况

1.等MDL锁

当我们执行DDL语句时，会自动给表加上MDL写锁。当执行DML和DQL时，会给表加上MDL读锁。

对MDL锁来说，读读共享，读写互斥。 因此，有可能会话A正在执行DDL语句，并且事务未提交。此时会话B执行DQL语句，那么会话B将被阻塞，查询语句长时间没有返回。

如果出现这种现象，我们可以查询到等待MDL锁的现象

show processlist

但是 id ： 86 是我们的查询语句，想找出是哪个会话ID造成的查询语句堵塞，还得使用下面的语句

select * from sys.schema_table_lock_waits

此时可以看到是 87 阻塞了我们的查询语句，把它kill掉即可

2.等待 Flush

flush tables words with read lock;

flush tables with read lock;

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

3.等行锁

当执行下面的语句获取最新的值时，将有可能被阻塞 (普通读不会加锁，并不会阻塞)

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

如上图所示，此时 session B 将被阻塞

如需找出死锁的会话ID，可以通过下面的SQL进行排查

select * from sys.innodb_lock_waits;

2.undo log导致查询慢

如上图所示，由于MySQL 的MVCC多版本并发控制实现，session b 将产生大量的 undo.log 日志

导致执行 select * from t where id =1（一致性读）需要遍历100W次并判断才能找到自己能读到的数据

而 select * from t where id =1 lock in share mode (当前读) 的速度将会很快，因为当前读不需要遍历版本链