01.26 Day 7 - 表锁、行锁和一致性读

大家好，我是 Snow Hide，作为《MySQL 实战》这个专栏的学员之一，这是我打卡的第 7 天，也是我第 49 次进行这种操作。

今天我温习了该专栏里一篇叫《为什么我只查一行的语句，也执行这么慢？》的文章。

关键词总结：第一类语句慢的原因，查询长时间不返回（等 MDL 锁、等 flush、等行锁）、第二类语句慢的原因，查询慢（一致性读、当前读）。

 

所学总结：

 

第一类语句慢的原因，查询长时间不返回

使用 show processlist 命令查看当前语句的状态。

等 MDL 锁

Waiting for table metadata lock 的意思是，现在有一个线程正在表上请求或者持有 MDL 写锁，把 select 语句堵住了。
以性能损耗 10% 为代价将 performance_schema 设置成 on 之后，通过查询 sys.schema_table_lock_waits 这张表，就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。

等 flush

Waiting for table flush 的意思是，现在又一个线程正要对表做 flush 操作。MySQL 里面对表做 flush 操作的用法，一般有以下两个：

flush tables 表名 with read lock;
flush tables with read lock;

等行锁

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

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

MySQL 5.7 可以通过 sys.innodb_lock_waits 表查到占用写锁的连接：

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

可以通过 sql_kill_blocking_connection 的值来杀掉连接。
 

第二类语句慢的原因，查询慢

执行 set long_query_time=0 来将慢查询的阈值设置为 0。然后查看慢查询日志。

select * from t where c=50000 limit 1;

由于字段 c 上没有索引，这个局与只能走 id 逐渐顺序扫描，因此需要扫描 5 万行。

一致性读

看一个只扫描一行，但是执行很慢的语句：

select * from t where id=1；

扫描行数是 1，但执行时间却长达 800 毫秒。
由于 select * from t where id=1 是一致性读，因此需要从末尾开始，依次执行 undo log，执行了 N 次以后，才将 1 这个结果返回。

当前读

select * from t where id=1 lock in share mode；

而加了 lock in share mode 的 SQL 语句是当前读，所以只要 0.2 毫秒。
 

末了

重新总结了一下文中提到的内容：查一行可能出现被锁和执行慢的情况、表锁、行锁、一致性读。