我们经常发现,往往执行一条简单的查询语句,但是很长时间都没有返回,今天我们看看是什么原因导致的
第一类:查询长时间不能返回
执行下面语句
select * from t where id =1;
等待MDL锁
我们按照下面操作,看看会发生什么呢
我们发现sql语句很长时间都不见返回响应,我们先看一下他的状态,发现果然是被锁住了.
此类问题我们直接可以找到谁持有MDL的写锁,直接kill.
可以用查询sys.schema_table_lock_waits这张表,我们就可以直接找到阻塞的process id ,把这个连接用kill命令断开即可(mysql启动的时候设置performation_schema=on)
等待flush
下面我们说另外一种查询被阻塞的情况,当一个线程正好对表进行flush操作,本身这个线程执行的很快,但是如果这个线程flush线程被其他线程阻塞,最终会导致阻塞表t的查询,如下图所示
sessionA中,我们故意调用一次sleep(1),默认执行10万秒,这个时候t表是打开的,使用flush去关闭表t,就必须等待sessionA结束,同时也会阻塞sessionC
等待行锁
首先,我们看看下面sql语句
mysql> select * from t where id=1 lock in share mode;
要执行上面语句的时候,这个记录就会要加读锁,如果这个时候已经有一个事物在这行记录上持有一个写锁,我们select 语句就会被阻塞。
这个问题并并不难分析,问题是如何查出谁占着这个写锁,如果你用的mysql5.7,可以使用下面语句
mysql> select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G
可以看到4号线程就是阻塞的罪魁祸首,因此只要干掉他就可以了,
不过,这里不应该显示kill query 4,这个命令是指把正在执行的语句停止,但是我们的update语句已经执行完成了,这样是无法去掉id=1的行锁.
实际上,kill 4才有效,也就是直接断开这个连接,这里连接被断开的时候,会自动回滚这个连接里面正在执行的线程,也就是释放id=1上的行锁.
第二类:查询慢
我们执行下面语句
select * from t where c=50000 limit 1;
有字段c没有索引,这个语句只能全表扫描,因此要扫描5万行,再看看慢日志的记录.
发现扫描了50000行,消耗时间13.5毫秒,看起来很快,但是目前数据的数据只有10万行数据,如果数据量到千万级别,这个sql就会消耗很多时间。
我们在看看另外sql,如下图
select * from t where id=1
select * from t where id=1 lock in share mode
按照上面操作我们再看看对应的慢查询日志
我们发现lock in share mode加锁操作居然时间比没有加锁的查询块了,超出了我们的预期,我们再看看每个sql查询结果
此时我们就知道原因了,是因为session A先用start transaction with consistent snapshot启动了一个事物,然后sessionB才进行更新语句,然后在执行完100万次update语句后,此时的id是处于下图的状态
发现session B生产100万回滚日志(undo log),此时lock in share mode的sql语句,是当前读,因此会直接读到100001,速度很快,但是select * from t where id=1,是一致性读,因此从1000001开始,依次执行undo log,执行100万次,才会把1返回.