导致查询操作阻塞或变慢的可能原因及分析方法

一般情况下，对于“查询”的性能优化是针对于一些复杂的语句，并且需要返回大量的数据的场景。但有些情况下，“查一行”也会执行的特别慢，本文介绍可能出引发这种现象的几类，并总结出解决方法。

1. 第一类：查询长时间不返回：
1.1 第1种情况：等MDL锁：
mysql> select * from t where id = 1;

如果查询结果长时间不返回，一般碰到这种情况，大概率是表t被锁住了。
接下来分析原因的时候，一般都是首先执行以下 show processlist 命令，看看当前语句处于什么状态。然后再针对每种状态，去分析它们产生的原因、如何复现，以及如何处理。

手动构造MDL锁的方法：

mysql> lock table runoob_test write;
//对一个表直接上锁。

mysql> show processlist;
+----+-----------------+-----------------+----------+---------+---------+---------------------------------+-----------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+----------+---------+---------+---------------------------------+-----------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 4256410 | Waiting on empty queue | NULL |
| 55 | root | localhost | runoob | Query | 6 | Waiting for table metadata lock | select * from runoob_test where runoob_id = 1 |
| 56 | root | localhost | runoob | Query | 0 | init | show processlist |
+----+-----------------+-----------------+----------+---------+---------+---------------------------------+-----------------------------------------------+
5 rows in set (0.01 sec)

可以查看到这条语句的状态是 “Waiting for table metadata lock”

mysql> select blocking_pid from sys.schema_table_lock_waits;
+--------------+
| blocking_pid |
+--------------+
| 59 |
+--------------+
1 row in set (0.05 sec)

再通过查询 sys.schema_table_lock_waits 表找到导致占用锁的线程ID，将其处理掉即可解决阻塞问题。

注：
表级锁是Server层级别的锁，行级锁是InnoDB存储引擎级别的锁。

1.2 第2种情况：等flush：
手动触发MySQL执行flush操作的命令：

mysql> flush tables runoob_test with read lock;
正常情况下flush操作会执行的很快，除非它们也被别的线程堵住了。
所以出现“Waiting for table flush”状态的可能情况是：有一个flush tables命令被别的语句阻塞了，然后它又阻塞了当前的select语句。
遇到这种情况时，同样可以使用 show processlist 进行解决：

1.3 第3种情况：等行级锁：
这类情况比较常见，例如两个事务A、B同时运行，当事务B中占有了某一行的写锁，此时如果事务A试图去访问这一行，则事务A会被阻塞，事务A中的查询语句不返回。

如果使用的是MySQL 5.7版本，可以通过 sys.innodb_lock_waits 表查到（MySQL 8.0中似乎无法查到）。

2. 第二类：查询慢：
MySQL提供了“慢查询”日志的功能，可以将超过用户指定的查询时间阈值的查询命令存入到 slow_query 日志中，供用户分析优化查询语句使用。

具体的操作方法是：

（1）方法一： 在配置文件中修改：

[mysqld]
slow_query_log = 1 //=1表示开启慢查询日志
slow_query_log_file = /var/log/mysql/slow-query.log //设置日志的保存路径
long_query_time = 5 //设置判定为慢查询的时间阈值，单位为秒

（Ubuntu中mysql配置文件所在路径：/etc/mysql/mysql.conf.d/mysqld.cnf）

（2）方法二： 通过client客户端命令操作的方式：

mysql> set global slow_query_log = 1;
mysql> set global slow_qeury_log_file = "/var/log/mysql/slow-query.log";
mysql> set global long_query_time = 5;

使用 set long_query_time = 0，可以将“慢查询”

查看 mysql-slow.log 慢查询日志中的内容：

select * from runoob_test where runoob_id = 1;
# Time: 2021-08-08T06:12:43.052494Z
# User@Host: root[root] @ localhost [] Id: 60
# Query_time: 0.005899 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
其中，Query_time 表示查询语句执行的时间（单位秒）。

PS： 在MySQL客户端查看服务器中某个配置参数的值：
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
1 row in set (0.01 sec)


mysql> show variables like 'slow_query_log_file';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
+---------------------+-------------------------------+
1 row in set (0.01 sec)


mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 0.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

3. 小结：
本文介绍的内容是执行“查一行”这种操作时可能出现的被锁住和执行慢的例子。

其中，“被锁住”一般是由于被其他线程占用了锁（表锁、行锁）或系统的flush操作被阻塞导致的间接阻塞。定位方法是在问题复现时（查询操作处于阻塞状态时），使用 show processlist 命令，通过 state 字段查看被锁住具体原因（例如Waiting for metadata lock / table flush / statistics），通过 sys.schema_table_lock_waits 表 和 sys.innodb_lock_wait 表可分别查看到持有表锁 或 行锁 的线程id，从而进一步决定对此线程的处理方式（例如直接kill掉）；

“执行慢”的原因有很多，例如在事务中所要查询的行数据的undo log过大（行数据被update更新了100万次，则undo log中将生成100万条行数据的版本），定位“执行慢”的方法是使用 “慢查询日志”，将阈值设置为合理的值，复现问题后通过 slow-query-log 锁定执行慢的语句并对其进行分析。