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

数据查询慢或无反馈的理论分析——锁、高频更新

author：陈镇坤27

创建时间：2021年11月23日00:41:12

编辑时间：2021年11月23日09:42:29

转载请注明出处

——————————————————————————————

查询始终无返回

问：一条查询语句始终无返回，可能是因为什么原因导致的？

答：

1）可能等MDL锁释放

查询语句执行时申请的是MDL读锁，若之前有MDL写锁处于申请中或正持有中，则MDL读锁等待。

2）可能等待flush锁
（在第六篇我们讲述过flush tables with read lock会加全局读锁）
执行flush statement类语句可能发生锁等待。
其中
flush tables with read lock（Closes all open tables and locks all tables for all databases with a global read lock.）会关闭所有打开的表并给所有的表在库层级添加一个全局的读锁。

flush table t with read lock（The operation first acquires exclusive metadata locks for the tables, so it waits for transactions that have those tables open to complete. Then the operation flushes the tables from the table cache, reopens the tables, acquires table locks (like LOCK TABLES ... READ), and downgrades the metadata locks from exclusive to shared. After the operation acquires locks and downgrades the metadata locks, other sessions can read but not modify the tables.）会首先获取t表的MDL写锁，因此它要首先等待所有打开t表的事务完成，然后才从表缓存中对表进行刷新，重新打开表，并获取表锁，随后将MDL写锁降级为MDL读锁。在获得表锁并进行MDL写锁降级后，其他连接可以读取该表，但不能更新表（DML、DDL）。

对应查询语句执行的情况，查询进行时，可能需要等待表关闭重新打开，而该表关闭可能由于其他原因被阻塞（见下方解释），所以最终查询线程进入了阻塞。

PS：flush statement类语句隐式提交锁（The FLUSH statement causes an implicit commit. ）

flush tables with read lock不会被其他会话的事务阻塞。例如开启一个会话事务，更新，然后直接执行本会话flush，此时再执行上一个会话事务的提交，提交由于flush进入了阻塞，在等flush被unlock后，该事务的提交继续执行。（想要复现flush tables with read lock被阻塞的场景，可以让select sleep(1) from t来进行——原理是持续不断地打开表对象）

flush table t with read lock反而会被对应表的其他事务给阻塞到。

3）可能等待行锁

如果查询的语句加了读锁——lock in share mode，那当读取的数据有写锁占用时，查询线程将被阻塞。

问：什么是隐式提交锁？

答：在执行语句之前，先提交当前会话的所有事务。

The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement.

问：怎么排查查询语句很久没有反馈的情况？讲一下你的步骤流程。

答：

-- 查看当前库的所有线程
show processlist

此外，还可以通过performance_schema库和sys库来排查。

show variables like '%performance_schema%';
set performance_schema = 'ON';

PS：开启此功能，会有10%的性能开销。

随后，执行

select * from performance_schema.setup_instruments where name='wait/lock/metadata/sql/mdl';
-- 若结果两者非YES,需设置yes，才可继续

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' where name='wait/lock/metadata/sql/mdl';

那么，当出现查询线程被阻塞时，在其他会话执行

SELECT * from sys.schema_table_lock_waits

即可查出当前持有锁的线程。

建议销毁线程时，执行kill X而非kill query X，因为当线程正在执行的语句非查询语句时，后者是无效的方法。

数据量极少但查询很慢

问：什么情况下，数据量极少，但查询却很慢呢？

答：热点更新语句，其在mvcc控制下，当前事务与最新数据版本间的回滚段过长时，查询会很慢。一般直接加读锁（会进行当前读）进行验证当前数据与事务一致性视图下数据差异。