『MySQL 实战 45 讲』19 - 为什么我只查一行的语句，也执行这么慢？

为什么我只查一行的语句，也执行这么慢？

1. 需求：创建一个表，有两个字段 id 和 c，并且在里面插入了 10 万行记录

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. 执行下面语句，长时间不返回

select * from t where id=1;

2. 大概率是表 t 被锁住了，需要通过执行 show processlist 命令，查看语句得状态

等 MDL 锁

1. 使用 show processlist 命令查看 Waiting for table metadata lock
   
2. 出现这个状态表示的是，现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select 语句堵住了
3. 复现场景
   
4. 解决办法就是找到持有 MDL 写锁的线程，然后 kill 掉

• 通过 show processlist 只能看到 Command 是 “sleep”
• 需要设置 performance_schema=on（相比于设置为 off 会有 10% 左右的性能损失，查询 sys.schema_table_lock_waits 表，找到 process id

# 查询是否开启
show variables like 'performance_schema'
# 找到有 MDL 写锁的线程
select blocking_pid from sys.schema_table_lock_waits;

等 flush

1. MYSQL 里面对表做 flush 操作一般有以下两个

# 只关闭表 t
flush tables t with read lock;
# 关闭 MySQL 里所有打开的表
flush tables with read lock;

2. Waiting for table flush 的复现步骤
   
3. 通过 show processlist 可以知道结果，从而排查问题
   

等行锁

1. 行锁复现
   
2. show processlist 现场
   
3. 但是是查不到谁在占用这个写锁，可以通过下面命令来看

select * from sys.innodb_lock_waits where locked_table='`testdb`.`t`'\G

4. 可以看出 905 号线程是问题根源，可以执行 KILL QUERY 905 或者 KILL 905

• KILL QUERY 905：表示停止 905 号线程当前正在执行的语句，而这个方法其实是没有用的。因为占有行锁的是 update 语句，这个语句已经是之前执行完成了的，现在执行 KILL QUERY，无法让这个事务去掉 id=1 上的行锁
• KILL 905：实际上，该命令才有效，也就是说直接断开这个连接。这里隐含的一个逻辑就是，连接被断开的时候，会自动回滚这个连接里面正在执行的线程，也就释放了 id=1 上的行锁

查询慢

1. 扫描行数多，执行慢。通过查看慢查询日志，Rows_examined 可以看到扫描过的量

# 开启慢查询日志
SET GLOBAL slow_query_log=1;
# 查询是否开启慢查询日志
SHOW VARIABLES LIKE '%slow_query_log%';
# 设置慢查询时间
set long_query_time = 0;

select * from t where c=50000 limit 1;

2. 扫描 1 行但是执行还是很慢的情况

• 复现步骤，其中会话 B 执行了 100w 次
  
• 这时候选择执行 select * from t where id=1 会很慢，执行 select * from t where id=1 lock in share mode 会很快
• 因为生成了 100w 个回滚日志