MySQL：为什么查询一行数据也要花费上百毫秒 _MySQL

不知道读者有没有遇到过这么一种异常情况，在使用MySQL时，仅仅是一次很简单的查询响应时间居然需要上百毫秒甚至1秒以上，到底是什么原因导致的这种非常异常的情况？这节课我们一起探究一下。
本篇文章使用的SQL数据如下所示。
mysql> CREATE TABLE `t` (`id` int(11) NOT NULL,`c` int(11) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB;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();1 查询长时间不返回【MySQL：为什么查询一行数据也要花费上百毫秒】假设存在如下这种场景，根据主键id查询如果出现长时间不返回，比如如下的语句：
select * from t where id = 1; 像这种根据主键查询还会长时间等待的语句，一般的猜测是有可能被锁。一般是执行show processlist命令查看当前的语句状态。
1.1 等待MDL锁使用show processlist命令查看WAIting for table metadata lock的示意图。出现这个状态原因是：现在正在有一个线程正在表t上请求或者持有MDL写锁，把select语句阻塞。

文章插图
在MySQL5.6版本可以用锁的章节进行复现；
在MySQL8.0版本可以使用三个连接client，一个执行select sleep(1) from t，一个执行alter，一个执行select，可以复现。
在MySQL5.7.30版本：

sessionA：begin; select c from t order by rand() limit 3;sessionB: alter table t add f int;[blocked]sessionC: select c from t order by rand() limit 3;[blocked]

文中的实例是在MySQL5.7复现，为：

文章插图
sessionA通过锁表获取MDL写锁，写锁具有排他性，因此sessionB虽然是执行读仅需要MDL读锁，也会被阻塞。
这类问题的处理方式，就是找到谁持有 MDL 写锁，然后把它 kill 掉。
但是，由于在 show processlist 的结果里面， session A 的 Command 列是“Sleep”，导致查找起来很不方便。不过有了 performance_schema 和 sys 系统库以后，就方便多了。
通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id ，把这个连接用 kill 命令断开即可。

文章插图
1.2 等待flush如果是执行如下语句出现卡顿：
mysql> select * from information_schema.processlist where id=1;注意其中的STATE字段，显示为：Waiting for table flush，也就是等待刷盘。

文章插图
即，此时数据不在内存中，会从磁盘读取到数据后加载到buffer pool中，如果此时buffer pool已经被占满，则会使用LRU淘汰掉旧数据，如果要淘汰的数据时脏页，就会触发flush，造成卡顿。
flush表有两种格式：

/**指定表t，代表只关闭表t*/flush tables t with read lock;/**没有指定表，代表只关闭MySQL打开的所有表*/flush tables with read lock;

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

文章插图

文章插图
根据show processlist查询的id，将select sleep(1) from t的进行先结束，然后flush table t的命令执行完， sessionC就会执行。
1.3 等待行锁还有第三种情况就是我们最为熟悉的锁。假设执行语句如下，在查询时开启共享锁：
mysql> select * from t where id=1 lock in share mode;在语句执行的加锁会增加锁冲突的几率，从而导致语句之间的相互等待锁释放。