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

最新推荐文章于 2024-11-12 17:30:24 发布

原创最新推荐文章于 2024-11-12 17:30:24 发布 · 219 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql

mysql 专栏收录该内容

8 篇文章

订阅专栏

本文探讨了MySQL中查询长时间不返回的原因，包括表锁、行锁和等待flush的情况。通过showprocesslist和sys schema_table_lock_waits、sys.innodb_lock_waits表可以定位问题。同时，分析了带lockinsharemode与不带的查询差异，涉及回滚日志和一致性读的概念。了解这些机制有助于优化查询性能。

第一类：查询长时间不返回
在这里插入图片描述
一般这种情况是表t被锁住了,首先需要执行以下show processlist命令，看一下语句处于什么状态。
1.等MDL锁
出现这种状态代表一个线程正在表t上请求或者持有MDL写锁，把select语句堵住了。
由于通过show processlist查看到的结果中sessionA的command列处于sleep状态，导致查找起来不方便，所以可以通过查询sys.schema_table_lock_waits这张表，就可以直接找到阻塞的process id 在这里插入图片描述
2.等flush

出现Waiting for table flush状态的可能情况是：有一个flush tables命令被别的语句堵住了，然后它又堵住了我们的select语句。

在这里插入图片描述

等行锁
由于访问id=1这个记录要加读锁，如果这时候已经有一个事务在这行记录上持有一个写锁，我们的select语句就会被堵住。

可以通过sys.innodb_lock_waits 表查到。
查询方法是：
mysql> select * from t sys.innodb_lock_waits where locked_table='test'.'t'\G

查询慢的情况
执行以下语句时长长达800ms

mysql> select * from t where id=1；

但是执行select * from t where id=1 lock in share mode却只用时0.2毫秒。
原因如下：
在这里插入图片描述

session B更新完100万次会生成100万个回滚日志(undo log)。
带lock in share mode是当前读，所以会直接读到1000001这个结果。而select * from t where id=1是一致性读，会从1000001开始执行undo log，执行了100万次以后，才将1这个结果返回。