有时候只查询一行语句,执行也慢
1 查询长时间不返回
-
假如有一个SQL
mysql> select * from t where id=1;
-
查询结果长时间不返回
-
一般是表t被锁住了,这时候执行show processlist
-
1.1 等MDL锁
-
使用 show processlist 命令查看 Waiting for table metadata lock 的示意图。
- 出现这个状态表示,现在有一个线程正在表t上请求或持有MDL写锁,把select堵住了
- 查询sys.schema_table_lock_waits这张表,找到阻塞的process id,用kill命令将连接断开
1.2 等flush
-
执行SQL
select * from information_schema.processlist where id=1;
-
查出来这个线程的状态是 Waiting for table flush,你可以设想一下这是什么原因。
-
-
这个状态表示的是,现在又一个线程正要堆表t做flush操作,MySQL里面堆表做flush操作的用法,一般有以下两个:
flush tables t with read lock; flush tables with read lock; //flush tables 是将buffer pool的数据刷回到磁盘中
-
这两个flush语句,如果指定表t的话,代表的是只关闭表t;如果没有指定具体的表明,则表示关闭MySQL里所有打开的表。
- 正常情况下这语句执行很快,除非也是被堵住了。
- 所以,出现 Waiting for table flush 状态的可能情况是:有一个 flush tables 命令被别的语句堵住了,然后它又堵住了我们的 select 语句。
-
-
复现步骤
-
在session A中,每行都调用一次sleep(1),这样这个语句默认要执行10万秒,在这期间表t一直被session A打开。
-
show processlist
-
将sessionA 线程kill 13
KILL [CONNECTION | QUERY] processlist_id kill query 13 //连接有SQL执行会继续执行 kill 13 //不管有没有,都会将线程杀掉。
-
1.3 等行锁
-
SQL
select * from t where id=1 lock in share mode; select k from t where id=1 lock in share mode; select k from t where id=1 for update; select 语句如果加锁,是当前读; 分别加了读锁(S 锁,共享锁)和写锁(X 锁,排他锁)。
-
由于id = 1 这个记录加了读锁,如果这时候已经有一个事务在这行记录上持有一个读锁,我们的select 语句就会被锁住。
-
-
查询语句被锁住:
-
因此是session A启动事务,占有写锁后,还不进行提交,导致了session B被锁住。
-
查询占用
select * from t sys.innodb_lock_waits where locked_table='`test`.`t`'\G
-
然后将query kill掉,需要直接进行kill id ,直接将连接断开后,才会事务回滚释放锁。
-
2 查询慢
-
select * from t where c=50000 limit 1;
-
由于字段c上没有索引,所以这个语句直走id主键索引,进行全表扫描。
- 扫描行数多,执行慢。坏查询不一定是慢查询
-
diselect * from t where id=1; select * from t where id=1 lock in share mode;
-
第一条语句慢查询:
-
第二条语句慢查询:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-b9UYTdjJ-1656643464196)(https://cdn.jsdelivr.net/gh/Cltlient/PiGoCDN/img/20220113171449.png)]
-
执行结果:
-
-
复现步骤:
- session A 先用 start transaction with consistent snapshot 命令启动了一个事务,之后 session B 才开始执行 update 语句。、
- session B执行万100万次update 后,id = 1 这一行状态如下:[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P5g5ZU38-1656643464197)(https://cdn.jsdelivr.net/gh/Cltlient/PiGoCDN/img/20220113171702.png)]
- session B更新完100万次,生成了100万个回滚日志(undo log)。
- 带lock in share mode的SQL语句,是当前读,因此会直接读到100001这个结果,所以速度很快;
- select * from where id =1 这个语句,是一致性读,因此需要从100001开始,一次执行undolog,执行100万次,将1这个记过返回。
3 小结:
-
差一行:可能会出现的被锁住和执行慢的例子,主要是表锁、行锁和一致性读的概念。
-
问题:
-
举例加锁读的时候,用的是这个语句,select * from t where id=1 lock in share mode。由于 id 上有索引,所以可以直接定位到 id=1 这一行,因此读锁也是只加在了这一行上。
-
但如果是下面的 SQL 语句,
begin; select * from t where c=5 for update; commit;
-
这个语句怎么加锁的呢?加的锁又是什么时候释放?
-