单行数据查询之谜：为什么慢？细解查询长时间不返回和查询慢

单行数据查询速度缓慢，你有没有想过其中的原因是什么？本文深入分析了单行数据查询为何会变慢的两大类原因：查询长时间不返回和查询慢。我们将揭开这个谜团，并探索其中的细致原因。

首先来看一下这张思维导图，对本文内容有个直观的认识。

 

接下来进入正文。

一般情况下，如果我跟你说查询性能优化，你首先会想到一些复杂的语句，想到查询需要返回大量的数据。但有些情况下，“查一行”，也会执行得特别慢。

查询长时间不返回

SQL复制代码select * from t_color where id=1;

查询结果长时间不返回。

 

一般碰到这种情况的话，大概率是表 t 被锁住了。接下来分析原因的时候，一般都是首先执行一下 show processlist 命令，看看当前语句处于什么状态。

等 MDL 写锁

基于 MySQL 5.7版本，按照如下操作步骤执行这两条 SQL，来复现查询结果长时间不返回的情况。

其中在 MySQL5.5及以后的版本中，lock table t1 write 就是通过加 MDL 写锁，来阻止其他线程访问的。

SQL复制代码// 先执行
lock table t_color write;
// 后执行
select * from t_color where id=1;

 

 

为了查找解决方案，可以先从执行 show processlist 命令，看看当前语句处于什么状态。

SQL复制代码show processlist;

执行结果如下：

 

背后原因分析：

session A 通过 lock table 命令持有表 t_color 的 MDL 写锁，而 session B 的查询需要获取 MDL 读锁。所以，session B 进入等待状态。

通过上述命令并不能准确定位到是哪个 session 持有 MDL 写锁，接下来我们通过另一条命令来定位。

通过查询
sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。（ performance_schema 和 sys 系统库都包含该张表，MySQL 启动时如果设置 performance_schema=on，相比于设置为 off 会有 10% 左右的性能损失 ）

我们新开一个连接，执行下述语句，结果为：

SQL复制代码select blocking_pid from sys.schema_table_lock_waits;

 

根据 MySQL 官网文章介绍可知，先执行下述语句，看看结果如何。

SQL复制代码select * from performance_schema.setup_instruments where name='wait/lock/metadata/sql/mdl';

 

看一下ENABLED和TIMED是不是都是YES，只有两个都是YES的时候才能通过查询 schema_table_lock_waits 表得到阻塞的进程id。所以我们需要执行 update 操作。

SQL复制代码UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

最后再尝试执行查询阻塞进程id，结果如下：

 

等 flush

MySQL 里面对表做 flush 操作的用法，一般有以下两个：

SQL复制代码flush tables t with read lock;

flush tables with read lock;

这两个 flush 语句，如果指定表 t 的话，代表的是只关闭表 t；如果没有指定具体的表名，则表示关闭 MySQL 里所有打开的表。

但是正常这两个语句执行起来都很快，除非它们也被别的线程堵住了。所以，出现 Waiting for table flush 状态的可能情况是：有一个 flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句。

复现步骤

SQL复制代码select sleep(1) from t_color;

flush tables t_color;

select * from t_color where id=1;

 

等行锁（当前读）

Shell复制代码mysql> select * from t_color where id=1 lock in share mode; 

由于访问 id=1 这个记录时要加读锁，如果这时候已经有一个事务在这行记录上持有一个写锁，我们的 select 语句就会被堵住。

复现步骤

SQL复制代码begin;
update t_color set c=c+1 where id=1;

select * from t_color where id=1 lock in share mode;

 

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

SQL复制代码select * from sys.innodb_lock_waits where locked_table='`mysql_db`.`t_color`'\G

 

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

查询慢

无索引 limit 1

SQL复制代码select * from t_color where c=50000 limit 1;

由于字段 c 上没有索引，这个语句只能走 id 主键顺序扫描，因此需要扫描 5 万行。

此处我们需要通过设置慢查询，可以看到扫描了多少数据行。

开启慢查询的两个步骤，全局变量设置：

1. 将 slow_query_log 全局变量设置为“ON”状态 Shell复制代码mysql> set global slow_query_log='ON';
2. 查询超过0秒就记录 Shell复制代码set global long_query_time=0;

查看配置效果：

Shell复制代码mysql> show variables like 'slow_query%';
mysql> show variables like 'long_query_time';

 

扫描行数多，所以执行慢。

主键索引 一致性读，undolog空间激增

如果根据主键来查询单行数据呢？会不会出现很慢的情况。

Shell复制代码mysql> select * from t_color where id=1

虽然扫描行数是 1，但执行时间却长达 1.2秒。

 

 

复现过程

SQL复制代码start transaction with consistent snapshot;

delimiter ;;
create procedure iudata()
begin
  declare i int;
  set i=1;
  while(i<=1000000) do
    update t_color set c=c+1 where id=1;
    set i=i+1;
  end while;
end;;
delimiter ;

call iudata();


select * from t_color where id=1;
select * from t_color where id=1 lock in share mode;

 

 

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

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