查询长时间不返回

等MDL锁

使用show processlist命令可查看到State为Waiting for table metadata lock
image.png

哪些语句会获取MDL锁：

lock table t write（MDL写锁），lock table t read（MDL读锁），DDL操作（MDL写锁）

处理方式

找到持有MDL锁的线程kill掉。

如果通过show processlist不好查找持有锁的线程ID，可以通过在MySQL启动时设置performance_schema=on，通过查询sys.schema_table_lock_waits这张表，直接找出造成阻塞的processId，kill掉就行，但是这个参数开启后会有10%的性能损失。

在实际工作中遇到过为表增加字段时，出现等待MDL锁的情况，当时没有开启performance_schema=on参数，是通过先将该表相关的服务停止，kill掉相关连接后再次执行DDL语句解决的。

等flush

  1. --只关闭表t
  2. flush tables t with read lock;
  3. --关闭MySQL里所有打开的表
  4. flush tables with read lock;

image.png
出现Waiting for table flush的情况是flush语句被阻塞住，比如对表t执行大SQL（查询时间很长的SQL，select sleep(x)）后，执行flush tables t with read lock;则会出现这种情况。

处理方式

使用show processlist可以看到那个进程正在执行大SQL或者执行flush语句，kill掉即可

等行锁

等行锁的情况就是线程A获取到该行的X锁，线程B执行查询时要获取S/X锁，就要等待线程A释放锁才行。

处理方式

通过以下SQL获取到占用锁的线程：

SELECT * FROM sys.innodb_lock_waits WHERE locked_table='`db1`.`t`';

image.png
可以看到阻塞线程id和阻塞模式，kill掉即可

查询慢

扫描行数多

当查询条件为非索引列时，如果扫描行数过多，会导致查询慢

非当前读，执行undo log时间长

session A session B
start transaction with consistent snapshot
update t set c=c+1 where id=1;//执行100万次，c当前值为1000001
select * from t where id=1 lock in share mode;(用时10ms)
select * from t where id=1;（用时800ms）

在读已提交和可重复读隔离级别下到底能读到哪些记录？中我们讲过lock in share mode是当前读，不需要通过undo log去回滚到自己能读到的版本，而普通的查询则需要通过该行记录的undo log去回滚到自己能读到的数据版本。线程B又执行了100万次更新，则需要执行100万次undo log才能得到自己能看到的数据版本，耗时就耗在这里。