mysql锁问题排查_实战 MySQL 锁等待问题的定位与排查

上面提到，排查 SQL 执行超时的一个重要手段是通过 show processlist 命令查看 SQL 执行各状态的耗时情况，但这是通过 SQL 执行完成后的 queryID 来进行查询的

在 SQL 执行过程中，也可以通过 show processlist 命令查看当前 SQL 的执行状态：

如上图所示，可以看到，我们正在执行的 SQL 状态是 Waiting for table metadata lock，这就说明他正在等待 MDL 锁，可是如何找到是谁持有 MDL 锁的呢？

通过 performance_schema 和 sys 两个数据库中的数据我们可以看到数据库执行过程中的各项情况，包括 MDL 锁的情况等

performance_schema

performance_schema 是 mysql 提供的一种在数据库运行时监控 server 各项执行状态的机制，监控数据都保存在 performance_schema 数据库中，而该数据库使用的存储引擎为 performance_schema，因此 performance_schema 同时具有以下三种含义：一种 MySQL 自带的对 server 运行状态的监控机制

MySQL 中的一个数据库名

一种存储引擎

performance_schema 机制

通过 MySQL 启动前在配置文件中配置 performance_schema=on 开启，相比于未开启会有 10% 左右的性能损失，MySQL 5.7 以后是默认开启的

可以通过 MySQL 的 performance_schema 全局变量来查看是否已开启：

performance_schema 机制通过监听 server 事件实现对 server 的监控，事件中包含了函数调用、操作系统的等待、SQL语句执行的阶段(如sql语句执行过程中的parsing 或 sorting阶段)或者整个SQL语句与SQL语句集合等发生时相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息以及事件发生的耗时、调用次数等信息

performance_schema 存储引擎与数据库

performance_schema 存储引擎是一个纯内存的存储引擎，因此一旦服务器重启，其中所有数据都会丢失，而同时，所有对 performance_schema 数据库中表的操作都不会写入 binlog，因此也不会在集群中进行同步

performance_schema 数据库中大部分表可以直接通过 select 语句进行查询，以便直接获取对 server 的监控数据，以 setup_ 开头的一系列数据表用于存储数据收集时的配置信息，可以通过修改表中的数据项来实现对配置的调整，这些修改都是立即生效的

performance_schema 共有 87 个表，主要包含：MySQL 执行的 SQL 语句记录

等待事件记录表

阶段事件记录表

事务事件记录表

监视文件系统调用记录表

监视内存使用记录表

实时配置表

sys 数据库

performance_schema 库中拥有大量的表以及复杂的关联关系，想要掌握其复杂的信息检索是非常难的，因此，MySQL 提供了 sys 库，将 performance_schema 数据库中的数据组合成视图，便于人工检索和使用，主要分为：sys_config -- 配置表，用于存储配置 sys 数据库的配置参数

host -- 以 IP 分组相关的统计信息

innodb -- innodb buffer 相关信息

io -- 数据内不同维度展的IO相关的信息

memory -- 以 IP，连接，用户，分配的类型分组及总的占用显示内存的使用

metrics -- DB 的内部的统计值

processlist -- 线程相关的信息(包含内部线程及用户连接)

ps_ -- 没有工具统计的一些变量(没看出来存在的价值)

schema -- 表结构相关的信息，例如-- 自增，索引， 表里的每个字段类型，等待的锁等等

session -- 用户连接相关的信息

statement -- 基于语句的统计信息(重店)

statements_ -- 出错的语句，进行全表扫描， 运行时间超长，排序相等(重点)

user_ -- 和host_开头的相似，只是以用户分组统计

wait --  等待事件

waits -- 以IP，用户分组统计出来的一些延迟事件，有一定的参考价值

x$开头 -- 适合工具采集数据的原始类数据

除 sys_config 外其他均为数据视图，我们可以方便的查询数据库当前的各项指标，当然也包括各个表的 MDL 锁使用情况

排查 MDL 锁的占用情况

在 sys 数据库的 schema_table_lock_waits 数据视图中就存有 MDL 锁的信息(5.7.9 版本加入)：

接着我们可以通过 kill pid 命令来杀掉对应的执行，也可以通过下面语句查询对应 sql 的详细信息：

select * from information_schema.`PROCESSLIST` where ID = 4;