MySQL 查询只返回一行的语句也执行的特别慢

造数：

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000) do
    insert into t values(i,i);
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();

当查询的时候遇到返回结果很慢，可能会有三种情况：

1、遇到了锁表

执行SQL语句如下：
mysql> select * from t where id=1;

如果长时间不返回结果，大概率是遇到锁冲突了，通过show processlist 命令，看看当前语句处于什么状态。

如果看到“ Waiting for table metadata lock”，就表示现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select 语句堵住了。

模拟锁表：

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

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

select  blocking_pid  from  sys.schema_table_lock_waits;

2、遇到了flush

通过如下SQL语句：mysql> select * from information_schema.processlist where id=1;

发现状态是：Waiting for table flush

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

flush tables t with read lock;

flush tables with read lock;

3、等待行锁

mysql> select * from t where id=1 lock in share mode; 
在mysql 5.7及之后版本，可以使用如下SQL来查看锁表的情况：
mysql> select * from t sys.innodb_lock_waits where locked_table='`test`.`t`'\G

 

start transaction with consistent snapshot;

select * from tabname where id=1 lock in share mode;  带 lock in share mode 的 SQL 语句，是当前读，因此会直接读到 1000001 这个结果，所以速度很快；

而 select * from t where id=1 这个语句，是一致性读，因此需要从 1000001 开始，依次执行 undo log，执行了 100 万次以后，才将 1 这个结果返回。