mysql 执行sql文件_Mysql中sql执行如此慢

我们经常发现,往往执行一条简单的查询语句,但是很长时间都没有返回,今天我们看看是什么原因导致的 第一类:查询长时间不能返回 执行下面语句

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=1select *  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返回.

如果对您有一丝丝帮助，麻烦点个关注，也欢迎转发，谢谢

扫码关注