19.为什么我只查一行的语句,也执行这么慢?
一般情况下,如果查询的数据量非常大,或者SQL语句非常复杂,那么可能SQL执行的时间会比较长。但是如果只查一条记录,SQL语句执行也非常慢呢?
当然如果系统CPU使用率过高,或者数据库IO压力很大, 也会造成SQL语句执行慢,以上几种场景不在本节讨论范围内。
为了方便说明,我们还是创建一张表,基于这张表来说明今天的问题。
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000) do
insert into t values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
第一类:查询长时间不返回
执行下面的SQL语句:
select * from t where id=1;
结果长时间不返回。
一般产生这种情况,大概率是表被锁住了。碰到这种情况, 一般是使用 show processlist 来查看线程的状态,然后再根据具体场景具体分析。
1.等MDL锁
上图为 show processlist 的结果,显示当前线程在 waiting for table metadata lock,也就是在等待元数据锁,因此阻塞了select 操作。
对数据表上 MDL写锁 有两种方式:1.执行DDL操作,2.使用 lock table t write 语句。
因为MYSQL在5.6版本引入了 online DDL操作,会讲DDL 操作的 MDL写锁降级为读锁,所以执行DDL 操作不能复现这种场景,但是我们还可以用 lock table t write 语句。
session A | session B |
lock table t write; | |
select * from t where id=1; |
如上图所示,session A 对表上了 MDL 写锁,因此session B 的select 操作被阻塞。
对于这种问题的解决办法,很简单,就是找到谁持有 MDL写锁,然后将它 kill掉。
show processlist 展示的信息有时不能很好的判断出 是谁持有了MDL写锁,但是有了 performance_schema 和 sys 系统表之后,就方便多了,(在启动时需要设置 performance_schema = ON,相比于设置为 OFF会有 10%的性能损失)
通过查看 sys.schema_table_lock_waits 表中的 blocking_pid,我们可以找到造成阻塞的 process id,然后将其 kill 掉即可。
等flush
如上图所示,显示有一个线程状态为 waiting for table flush,在等待flush。
对表执行flush操作的写法有:
flush tables with read lock;
flush tables t with read lock;
语句1 表示对所有表执行 flush操作,语句2表示仅对表t 做flush操作。
但是这两个flush 操作其实执行也是很快的,除非这个flush操作被其他的线程阻塞了,然后这个flush操作又阻塞了 select 语句。
让我们来复现一下这种场景:
session A | session B | session C |
select sleep(1) from t; | ||
flush tables t with read lock; | ||
select * from t where id = 1; |
如上表所示,session A执行了sleep(1) 操作,sleep(1)代表有一条满足条件的数据,就执行一秒,因此session A会执行 10万秒,阻塞了session B 的flush 操作,也就因此阻塞了 session C的select 操作。
等行锁
经过了全剧锁和表级锁的重重考验之后,我们来到了 行锁 环节,来到了引擎层。
执行下面语句:
select * from t where id = 1 lock in share mode;
假如执行这条语句之前,有一个事务获得了ID =1 的行的写锁,并且没有释放,那么这个 select 语句就会被阻塞。
我们来复现一下这种场景:
session A | session B |
begin; | |
update set c=c+1 where id = 1; | |
select * from t where id = 1 lock in share mode; |
如果MYSQL版本是5.7版本及以后,我们可以查看 sys.innodb_lock_waits表中的信息,来获取持有行锁的线程。
select * from sys.innodb_lock_waits where locked_table='`test`.`t`'\G
查询出来的信息很全,可以看到 blocking_pid 的值为 4,也就是线程4 阻塞了 select 语句。
同时还给出了解决办法, kill query 4 或者 kill 4, 但是其实这里不应该返回 “KILL QUERY 4”。“KILL QUERY 4”表示停止当前线程正在执行的操作,而占有行锁的是update 语句,这个语句已经完成了,所以无法释放行锁。
正确做法是执行“kill 4”,即关闭当前连接,也就自然释放了 行锁。
第二类:查询慢
让我们看下这条SQL语句:
select * from t where c = 50000 limit 1;
这个问题很简单,因为 C列上没有索引,只能走 主键索引顺序扫描,扫描50000行之后返回数据。
上图为慢查询日志,扫描行数为 50000,查询时间为 11.5毫秒。
上图中的查询语句并不是很慢,但是需要注意,这是一个 坏查询语句,随着表中数据量的增大, 坏查询的执行时间将会线性增长。因此实际开发中要尽量避免。
下面我们再来看另一种情况。
session A | session B |
start transaction with consistent snapshot; | |
update t set c = c+1 where id = 1; // 执行100万次 | |
select * from t wehre id = 1; | |
select * from t where id = 1 lock in share mode; |
上图为 session A中两条 SQL语句的执行时间,可以看到,第一条语句执行了0.81秒,而 lock in share mode 反而只用了几乎0秒。
这是因为第一条语句为快照读,innodb引擎需要根据 undo log 会滚100W次,得到事务开始时的视图,而lock in share mode 是当前读,直接拿表中数据就可以。
上图为MYSQL根据 undo log 段进行会滚操作,需要注意 undo log 里其实记录的是 “把3改成2”,“把2改成1”这样的逻辑操作, 图片里的 减1 只是为了方便理解。
小结
本节介绍了MYQL执行简单的语句为什么非常慢的原因,包括,等flush,等MDL锁,等行锁,因为MVCC造成的查询慢等等,实际开发中遇到的场景问题可能会更复杂,但是只要我们掌握了方式方法,抽丝剥茧,都是可以慢慢查出来的,重点还是要掌握排除问题的思路和方法。
如果碰到上述问题,可以用show processlist 命令来查看状态,进行分析,
遇到表锁和全局锁,可以查看 sys.scheme_table_lock_wais;遇到行锁,可以查看sys.innodb_lock_waits。配合慢查询日志使用。