MySQL深度解析---单行SQL慢查询原因分析

此处讨论不涉及CUP占用高或IO利用率过高导致的SQL执行慢问题

一、查询长时间不返回

对于一个SQL，如果长时间不返回，可以执行show processlist命令来查看所有线程的状态

mysql> show processlist

1.等MDL锁

执行show processlist命令后，State中显示：Waiting for table metadata lock 表示的是，现在有一个线程正在表t上请求或者持有MDL写锁，把select语句堵住了。

处理方式

找到谁持有MDL写锁，然后把它kill掉

如果MySQL启动时，参数performance_schema设置为on ，就可以使用performance_schema和sys系统库，用来查找造成阻塞的process id（线程ID）

//查询所有正在锁等待的线程ID
mysql> select blocking_id from sys.schema_table_lock_waits

查出阻塞的线程ID后，使用kill命令断开线程即可。

MySQL启动时设置performance_schema=on，相比于设置为off会有10%左右的性能损失

2.等flush

执行show processlist命令后，State中显示：Waiting for table flush 表示的是，有一个flush tables命令被别的语句堵住了，然后它又堵住了我们的select语句。（由于flush 过程比较快，一般不会造成堵塞）

处理方式

这样的情况就需要找到造成堵塞的语句，kill掉。

3.等行锁

执行show processlist命令后，State中显示：statistics 表示该线程在等待互斥锁

解决方法

如果是MySQL 5.7版本，可以通过sys.innodb_lock_waits 表查到。

查询方法是：

mysql> select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G

结果中，blocking_pid 是当前加锁线程ID，kill 掉该线程即可。但注意kill query pid 是无效的，因为占有行锁的是update语句，这个语句已经是之前执行完成了的，现在执行KILL QUERY，无法让这个事务去掉id=1上的行锁。

隐含的一个逻辑就是，连接被断开的时候，会自动回滚这个连接里面正在执行的线程，也就释放了id=1上的行锁。

二、查询慢

如果一个事务中先对同一行进行了大量的修改操作，然后再查询这一行的话，查询返回将会很慢。

例：

session B更新完100万次，生成了100万个回滚日志(undo log)。

带lock in share mode的SQL语句，是当前读，因此会直接读到1000001这个结果，所以速度很快；
而select * from t where id=1这个语句，是一致性读，因此需要从1000001开始，依次执行undo log，执行了100万次以后，才将1这个结果返回。所以导致查询慢。