读mysql45讲-查询sql缓慢

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

一般碰到这个情况，很有可能是表被锁住了。可以使用 show processlist命令查看

等MDL锁

出现Waiting for table metadata lock表示的有一个线程正在请求这个表的MDL写锁或者已经持有MDL写锁，从而把select语句堵住了。

sessionA通过lock table的命令锁住了t表的MDL写锁，而sessionB需要获取读锁，所以session需要等待sessionA释放锁。

解决方式可以使用select blocking_pid from sys.schema_table_lock_waits 查询造成阻塞的process id,然后直接kill掉。

等flush

waiting for table flush这个状态表示有一个线程正要对表做flush操作。
flush表操作的命令有两个：

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

区别就是第一个指定了表名，第二个没有；第一个只会关闭t表，但是第二个会关闭数据库里所有打开的表。正常这两个操作是很快的，除非flush语句被其他sql堵住了。

sessionA查询表t，每一次都会sleep一秒钟，这个期间表t处于一直打开的状态；sessionB中的flush 命令想要取关闭表t，就需要等待sessionA结束。所以当sessionC中的select语句就会被flush命令而堵住。

解决方式就是在图三中host那一列可以看到端口号，可以通过 netstat -ano|findstr “port” 先查看端口号中的pid，再根据taskkill /pid 12345 -f -t 直接kill掉。

等行锁

sessionB中的查询语句会给id=1这一行的数据加上读锁，但是因为sessionA已经获取到写锁，所以sessionB的查询语句会被堵住。

可以看到select语句中对应的state状态是 statistics,如果长时间处于这个状态，表示磁盘一直在执行其他工作。对应这里也就是sessionA在持有id=1这一行的写锁，并且还未提交事务，所以sessionB会被卡住。

可以通过 select * from t sys.innodb_lock_waits where locked_table=tableName查询被堵塞的原因：

原文复制：

可以看到，这个信息很全，4号线程是造成堵塞的罪魁祸首。而干掉这个罪魁祸首的方式，就是 KILLQUERY 4或KILL 4。
不过，这里不应该显示“KILLQUERY 4”。这个命令表示停止4号线程当前正在执行的语句，而这 个方法其实是没有用的。因为占有行锁的是update语句，这个语句已经是之前执行完成了的， 现在执行KILLQUERY，无法让这个事务去掉id=1上的行锁。
实际上，KILL 4才有效，也就是说直接断开这个连接。这里隐含的一个逻辑就是，连接被断开的 时候，会自动回滚这个连接里面正在执行的线程，也就释放了id=1上的行锁。

第二类：查询慢

数据量很大，也会导致查询慢，这个是正常情况。但是有时也会出现查询单行很慢的情况。

可以看到两个查询语句都是查询id=1，但是前一个select用了0.8秒，而第二个查询甚至都会有加锁，释放锁的操作，但是不到10毫秒。

sessionA中先开启事务，然后在开始查询之前，sessionB获取到id=1的写锁，进行更新操作，并且更新完就进行提交了事务。
select * from t where id=1是一致性读，因为sessionB中开启的事务是在session开启的事务之后，所以select * from t where id=1这个查询就需要将id=1这一行数据，根据redo log日志记录的update操作进行回退，一直回退到c=1的那个时刻的值;
但是lock in share mode 是当前读，所以后一个查询语句就没有回退数据的操作，直接读取c的值。

小结：
当一个查询语句长时间不返回是因为表被锁住了，而锁表的原因可能是因为MDL写锁被长时间获取没有释放，也可能是因为需要获取的行锁被其他线程持有，一直没有释放；也可能是因为flush操作也就是需要刷脏页的时候。有某个sql在长时间操作数据库，导致表一直不能关闭；这两种情况都需要找到造成堵塞的sql的线程，然后kill掉。
当一个select单行数据很慢的时候，可能是因为一致性读的原因。