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

• 有个表 t：mysql> CREATE TABLEt(idint(11) NOT NULL,cint(11) DEFAULT NULL,  PRIMARY KEY (id)) ENGINE=InnoDB;

第一类：查询长时间不返回

等 MDL 锁

• 使用 show processlist 命令查看 Waiting for table metadata lock 的示意图

  • 

• 出现这个状态表示的是，现在有一个线程正在表 t 上请求或持有 MDL 写锁，把 select 语句堵住了

• 简单的复现步骤

  • 

• 处理方式：找到谁持有 MDL 写锁，kill 掉

• 但是，由于在 show processlist 的结果里面，session A 的 Command 列是“Sleep”，导致查找起来很不方便。不过有了 performance_schema 和 sys 系统库以后，就方便多了。（MySQL 启动时需要设置 performance_schema=on，相比于设置为 off 会有 10% 左右的性能损失)

• 通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。

  • 

等 flush

• mysql> select * from information_schema.processlist where id=1;

• Waiting for table flush 状态示意图

  • 

• 现在有一个线程正要对表 t 做 flush 操作。MySQL 里面对表做 flush 操作的用法，一般有以下两个：

  • flush tables t with read lock;

  • flush tables with read lock;

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

  • 但是正常这两个语句执行起来都很快，除非它们也被别的线程堵住了。

  • 所以，出现 Waiting for table flush 状态的可能情况是：有一个 flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句。

• 排查

  • 图 7

    • 

  • 这个例子的排查也很简单，你看到这个 show processlist 的结果，肯定就知道应该怎么做了。

等行锁

• mysql> select * from t where id=1 lock in share mode;

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

  • 

• 行锁 show processlist

  • 
  • session A 启动了事务，占有写锁，还不提交，是导致 session B 被堵住的原因。

• MySQL 5.7 版本，可以通过 sys.innodb_lock_waits 表查到是谁占着行锁 mysql> select * from t sys.innodb_lock_waits where locked_table='test.t'\G

  • 

• 可以看到，这个信息很全，4 号线程是造成堵塞的罪魁祸首。而干掉这个罪魁祸首的方式，就是 KILL QUERY 4 或 KILL 4。不过，这里不应该显示“KILL QUERY 4”。这个命令表示停止 4 号线程当前正在执行的语句，而这个方法其实是没有用的。因为占有行锁的是 update 语句，这个语句已经是之前执行完成了的，现在执行 KILL QUERY，无法让这个事务去掉 id=1 上的行锁。实际上，KILL 4 才有效，也就是说直接断开这个连接。这里隐含的一个逻辑就是，连接被断开的时候，会自动回滚这个连接里面正在执行的线程，也就释放了 id=1 上的行锁。

第二类：查询慢

• 在连接后执行 set long_query_time=0 可以将慢查询日志的时间阈值设置为 0
• 一个例子

  • 800 毫秒 mysql> select * from t where id=1

    • id 上有索引

  • 0.2 毫秒 mysql> select * from t where id=1 lock in share mode

  • 提示

    • 

  • 复现

    • 

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

思考题

• begin;select * from t where c=5 for update;commit;

  • c 上没索引

• 这个语句序列是怎么加锁的呢？

  • RR 隔离级别下，为保证 binlog 记录顺序，非索引更新会锁住全表记录，且事务结束前不会对不符合条件记录有逐步释放的过程。
  • 在 Read Committed 隔离级别下，会锁上聚簇索引中的所有记录；
  • 在 Repeatable Read 隔离级别下，会锁上聚簇索引（主键索引）中的所有记录，并且会锁上聚簇索引内的所有 GAP（间隙锁）；

• 加的锁又是什么时候释放呢？

  • commit 的时候释放
  • 在上面两个隔离级别的情况下，如果设置了 innodb_locks_unsafe_for_binlog 开启 semi-consistent read 的话，对于不满足查询条件的记录，MySQL 会提前放锁，不过加锁的过程是不可避免的。