mysql优化和常用问题定位
1. mysql优化和常用问题定位
目录
- null
- 1. mysql优化和常用问题定位
- 1.1. 常用查询：
- 1.1.1. 查询数据库操作类型
- 1.1.2. 定位执行效率低的sql
- 1.1.3. 查询出造成阻塞的SQL和会话信息
- null
- 1.1.4. 查看占用内存或者CPU高的SQL
- 1.1.5. 查看当前系统中存在的锁信息
- 1.1.6. MYSQL出现WAITING FOR TABLE METADATA LOCK的原因和解决方案:
- 1.1.7. 通过如下语句找到执行次数较多，且耗时较多的sql和会话信息
- 1.1.8. 查看当前正在执行的sql
- 1.1.9. 查看当前连接ID，对应processlist中的ID
- 1.1.10. 查看加了锁的表和0判断表是否有被加锁
- 1.1.11. 判定某个表有没有加锁？
- 1.1.12. 查看当前的连接数量和并发数
- 1.1.13. 查找TOP sql -性能视图方式
- 1.1.14. 查找TOP sql -存储过程方式
- 1.2. 常用的性能分析表
- 1.1. 常用查询：
- 关于排查未提交事务导致的事务阻塞问题
1.1. 常用查询：
1.1.1. 查询数据库操作类型
通过以下 SQL 可以查询数据库是以更新为主，还是以查询操作为主：
show status like 'com_%';
其中如下参数是我们主要关注的参数：（针对所有存储引擎）
- Com_select: 执行select操作的次数，一次查询只累加1。
- Com_insert: 执行insert操作的次数，对于批量插入，只记录一次。
- Com_update: 执行update操作的次数。
- Com_delete: 执行delete操作的次数。
如下的参数只针对InnoDB
- Innodb_rows_read: select 查询返回的函数
- Innodb_rows_inserted: 执行insert操作插入的行数
- Innodb_rows_updated: 执行update操作更新的行数
- Innodb_rows_deleted: 执行delete操作删除的行数
通过如上的参数可以简易的了解到这个数据库是以插入更新为主还是以查询操作为主的数据库。
另外，通过com_comit 和com_rollback可以了解到事务提交和回滚的情况。
如果回滚非常频繁的数据库，可能意味着应用编写有问题。
1.1.2. 定位执行效率低的sql
可以通过如下两种方式定位执行效率低的sql：
- 1.慢查询日志：通过慢查询日志定位执行效率低的sql，但它只能在查询结束以后才能记录，在应用出问题的时候并不能直接定位，。
- 2.show processlist：在应用出问题时，可通过 show processlist 查看当前 MySQL 在进行的线程，包括线程的状态、是否锁表等，能实时查看 SQL 的执行情况，同时可对一些锁表的操作进行优化。
这时可以通过show processlist查看当前mysql在进行的线程 ，包括线程的状态、是否锁表等，可以实时的查看sql的执行情况，同时对一些锁表的操作进行优化。
show processlist;
1.1.3. 查询出造成阻塞的SQL和会话信息
1.1.3.1. mysql5.7及以下版本
SELECT
r.trx_wait_started AS wait_started ,
TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age ,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs ,
r1.lock_table AS locked_table ,
r1.lock_index AS locked_index ,
r1.lock_type AS locked_type ,
r.trx_id AS waiting_trx_id ,
r.trx_started AS waiting_trx_started ,
TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age ,
r.trx_rows_locked AS waiting_trx_rows_locked ,
r.trx_rows_modified AS waiting_trx_rows_modified ,
r.trx_mysql_thread_id AS waiting_pid ,
sys.format_statement(r.trx_query) AS waiting_query ,
r1.lock_id AS waiting_lock_id ,
r1.lock_mode AS waiting_lock_mode ,
b.trx_id AS blocking_trx_id ,
b.trx_mysql_thread_id AS blocking_pid ,
sys.format_statement(b.trx_query) AS blocking_query ,
b1.lock_id AS blocking_lock_id ,
b1.lock_mode AS blocking_lock_mode ,
b.trx_started AS blocking_trx_started ,
TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age ,
b.trx_rows_locked AS blocking_trx_rows_locked ,
b.trx_rows_modified AS blocking_trx_rows_modified,
CONCAT('KILL QUERY ',b.trx_mysql_thread_id) AS sql_kill_blocking_query ,
CONCAT('KILL ', b.trx_mysql_thread_id) AS sq1_kill_blocking_connection
FROM
information_schema.innodb_lock_waits w
INNER JOIN
information_schema.innodb_trx b
ON
b.trx_id=w.blocking_trx_id
INNER JOIN
information_schema.innodb_trx r
ON
r.trx_id=w.requesting_trx_id
INNER JOIN
information_schema.innodb_locks b1
ON
b1.lock_id=w.blocking_lock_id
INNER JOIN
information_schema.innodb_locks r1
ON
r1.lock_id=w.requested_lock_id
ORDER BY
r.trx_wait_started \G;
-- 如果获取不到具体的锁的语句，但是其他回话又一直被阻塞，等待锁，可以通过trx表去查看当前正在执行的事物
select * from information_schema.innodb_trx
1.1.3.2. mysql 8.0
包含用户名，主机，对应的表，数据库，数据行
SELECT
t.PROCESSLIST_ID ,
t.THREAD_OS_ID ,
dl.OBJECT_SCHEMA ,
dl.OBJECT_NAME ,
dl.INDEX_NAME ,
dl.LOCK_TYPE ,
dl.LOCK_MODE ,
dl.LOCK_STATUS ,
dl.LOCK_DATA ,
t.NAME ,
t.TYPE ,
t.PROCESSLIST_USER ,
t.PROCESSLIST_HOST ,
t.PROCESSLIST_DB ,
t.PROCESSLIST_COMMAND,
t.PROCESSLIST_TIME ,
t.PROCESSLIST_STATE ,
t.PROCESSLIST_INFO ,
t.CONNECTION_TYPE
FROM
performance_schema.data_locks dl
JOIN
performance_schema.data_lock_waits dlW
ON
dl.ENGINE_LOCK_ID=dlW.BLOCKING_ENGINE_LOCK_ID
JOIN
performance_schema.threads t
ON
t.THREAD_ID=dl.THREAD_ID;
1.1.4. 查看占用内存或者CPU高的SQL
- 获取mysql进程id
ps -ef | grep -i mysql
- 实时查看mysql进程中占用CPU，内存最多的操作系统线程ID
top -p 2296 -H
- 根据操作系统线程ID，查看mysql数据库中对应的线程ID
select thread_id,name ,PROCESSLIST_ID,THREAD_OS_ID from performance_schema.threads where thread_os_id = 2369 ;
SELECT
a.THREAD_OS_ID,
b.id,
b.USER,
b.HOST,
b.db,
b.command,
b.TIME,
b.state,
b.info
FROM
PERFORMANCE_SCHEMA.threads a,
information_schema.PROCESSLIST b
WHERE
b.id = a.processlist_id
AND a.THREAD_OS_ID =<具体 pid >;
通过mysql thread_id 查找os id
select thread_id,name ,PROCESSLIST_ID,THREAD_OS_ID from performance_schema.threads where thread_id = 4324460 ;
-
PROCESSLIST_ID如果返回null则表示为后台系统进程，否者为前台进程
-
根据mysql数据库的线程ID获取sql
select sql_text from performance_schema.events_statements_current where thread_id = 78 \G;
6.批量kill掉链接的session
select concat('KILL ',id,';') from information_schema.processlist where time>10 and db is not null and command!='sleep' into outfile '/tmp/a.txt';
1.1.5. 查看当前系统中存在的锁信息
1.1.5.1. 方法一、metadata_locks表
（包括所有的读和写锁）5.7版本新增的，用来记录一些Server层的锁信息（包括全局读锁和MDL锁等）
1.查看除了系统锁的所有业务锁信息
select * from performance_schema.metadata_locks where owner_thread_id!=sys.ps_thread_id(connection_id()) \G;
注意：其中记录的顺序代表持有锁的时间顺序，结合LOCK_TYPE和LOCK_STATUS 我们可以判断是谁是造成锁定的源头然后根据以下命令查找出线程ID，然后找出造成阻塞的线程ID，判断是否可以kill(id)
show processlist; 查看id
select sys.ps_thread_id(id);
1.1.5.2. 方法二、show engine innodb status;
show engine innodb status;
1.1.5.3. 方法三、innodb_lock_waits
5.6/5.7 版本可以通过以下的sql,直接查出造成阻塞的源头的processid, 然后拼接出kill命令。
查询出的结果就是造成整改sql被阻塞的源头：
SELECT r.trx_wait_started AS wait_started,
TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs ,
r1.lock_table AS locked_table,
r1.lock_index AS locked_index,
r1.lock_type AS locked_type,
r.trx_id AS waiting_trx_id,
r.trx_started as waiting_trx_started,
TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age,
r.trx_rows_locked AS waiting_trx_rows_locked,
r.trx_rows_modified AS waiting_trx_rows_modified,
r.trx_mysql_thread_id AS waiting_pid,
sys.format_statement(r.trx_query) AS waiting_query,
r1.lock_id AS waiting_lock_id,
r1.lock_mode AS waiting_lock_mode,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_pid,
sys.format_statement(b.trx_query) AS blocking_query,
b1.lock_id AS blocking_lock_id,
b1.lock_mode AS blocking_lock_mode,
b.trx_started AS blocking_trx_started,
TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age,
b.trx_rows_locked AS blocking_trx_rows_locked,
b.trx_rows_modified AS blocking_trx_rows_modified,
CONCAT('KILL QUERY ',b.trx_mysql_thread_id) AS sql_kill_blocking_query,
CONCAT('KILL ', b.trx_mysql_thread_id) AS sq1_kill_blocking_connection
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id=w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id=w.requesting_trx_id
INNER JOIN information_schema.innodb_locks b1 ON b1.lock_id=w.blocking_lock_id
INNER JOIN information_schema.innodb_locks r1 ON r1.lock_id=w.requested_lock_id
ORDER BY r.trx_wait_started \G;
查询当前的锁情况：
SELECT r.trx_mysql_thread_id AS waiting_thread,
r.trx_id AS waiting_trx_id,
timestampdiff(second, r.trx_wait_started, current_timestamp) AS wait_time,
r.trx_query AS waiting_query,
l.lock_table AS waiting_table_lock,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_id AS blocking_trx_id,
p.HOST AS blocking_host,
IF(p.COMMAND = "Sleep", p.TIME, 0) AS ilde_in_trx,
b.trx_query AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r
ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.INNODB_LOCKS l
ON w.requested_lock_id = l.lock_id
LEFT JOIN information_schema.PROCESSLIST p
ON p.ID = b.trx_mysql_thread_id
ORDER BY wait_time DESC\G;
1.1.5.4. 查看server层的元数据库锁
MYSQL为了保护字典元数据，使用了metadata lock，即DML锁，保证在并发的情况下，结构变更的一致性，
创建DML锁的原因
- 有大事物正在使用表
- 存在未提交的事务
- 存在有未提交的事务，且是失败操作
DDL 语句被阻塞通常因为存在获取资源后未及时提交释放的长事务。因此，查找 kill 掉事务运行时间大于 DDL 运行时间的会话即可使 DDL 语句顺利下发，SQL 语句如下：
select * from information_schema.processlist where command<>'sleep'
UPDATE performance_schema.setup_instruments set enabled='YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
SELECT a.OBJECT_SCHEMA,
a.OBJECT_NAME,
a.OBJECT_TYPE,
a.LOCK_TYPE,
a.LOCK_DURATION,
a.LOCK_STATUS,
p.PROCESSLIST_ID,
p.PROCESSLIST_USER,
p.PROCESSLIST_HOST,
p.PROCESSLIST_DB,
p.PROCESSLIST_COMMAND,
p.PROCESSLIST_STATE,
p.PROCESSLIST_INFO
FROM performance_schema.metadata_locks a,
performance_schema.metadata_locks b,
performance_schema.threads p
WHERE a.OBJECT_TYPE=b.OBJECT_TYPE
AND a.OBJECT_SCHEMA=b.OBJECT_SCHEMA
AND a.OBJECT_NAME=b.OBJECT_NAME
AND a.LOCK_STATUS != b.LOCK_STATUS
and p.THREAD_ID=a.OWNER_THREAD_ID
ORDER BY a.LOCK_STATUS;
临时开启，动态生效
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME ='global_instrumentation';
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES'
WHERE NAME ='wait/lock/metadata/sql/mdl';
// 查找事务运行时间 >= DDL等待时间的线程
SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
NOW(),
TRX_STARTED,
TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
USER,
HOST,
DB,
TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx_mysql_thread_id != connection_id()
AND TO_SECONDS(now())-TO_SECONDS(trx_started) >=
(SELECT MAX(Time)
FROM INFORMATION_SCHEMA.processlist
WHERE STATE='Waiting for table metadata lock'
AND INFO LIKE 'alter%table%' OR INFO LIKE 'truncate%table%') ;
注：因 MySQL 元数据信息记录有限，此处可能误杀无辜长事务，且误杀无法完全避免。
1.1.6. MYSQL出现WAITING FOR TABLE METADATA LOCK的原因和解决方案:
1.1.7. 通过如下语句找到执行次数较多，且耗时较多的sql和会话信息
select db,exec_count,no_index_used_count,no_good_index_used_count,no_index_used_pct,query
from sys.statements_with_full_table_scans
where db='ability' and last_seen > DATE_SUB(NOW(), INTERVAL 60 MINUTE);
1.1.8. 查看当前正在执行的sql
包含正在执行的sql和事务中执行完成，但是没有提交的sql
select * from performance_schema.events_statements_current limit 20;
通过如下
show processlist; 查看id
select sys.ps_thread_id(id);
1.1.9. 查看当前连接ID，对应processlist中的ID
select connection_id()
对应processlist 中的ID 对应performance_schema.threads.processlist_id
对应 INNODB_TRX 中的 trx_mysql_thread_id
1.1.10. 查看加了锁的表和0判断表是否有被加锁
show open tables where in_use;
1.1.11. 判定某个表有没有加锁？
show open tables where in_use>0;
1.1.12. 查看当前的连接数量和并发数
show status like 'Threads%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 0 |
| Threads_connected | 1 | 连接数
| Threads_created | 1 |
| Threads_running | 1 | 当前运行的连接数（并发数）
+-------------------+-------+
show global status like '%threads_running%';
Threads_connected 跟show processlist结果相同，表示当前连接数。准确的来说，Threads_running是代表当前并发数
这是是查询数据库当前设置的最大连接数
show variables like '%max_connections%';
可以在/etc/my.cnf里面设置数据库的最大连接数
[mysqld]
max_connections = 1000
常用的表
SELECT * FROM sys.metrics; 查看当前变量和值
1.1.13. 查找TOP sql -性能视图方式
1.1.13.1. 查找TOP sql
以下的方式仅展示数据库没有重启的这段时间的所有sql
在不方便修改客户的系统配置的前提下，我们可以直接查看如下的视图，抓出系统只能怪执行过的时间最长的sql，有些时候可能不愿意启用慢查询等配置，那么我们就以使用如下的方法分析sql
在视图sys.statement_analysis中找出总计执行时间最长的SQL语句：
select * from sys.statement_analysis limit 1\G;
视图sys.statement_analysis已经是按照总的执行时间降序排序按了，所以我们直接查询即可
还可以在视图sys.statements_with_runtimes_in_95th_percentline 中可以查询到运行时间最长的5%的语句
select * from sys.statements_with_runtimes_in_95th_percentile limit 3;
1.1.13.2. 查询平均时间最长的sql
下面的sql语句列出平均执行时间最长的语句，这类sql语句通常优化空间最大
select * from performance_schema.events_statements_summary_by_digest order by avg_timer_wait desc limIt 1 \G;
查询执行次数最多的,这种对整体信息影响较大
select * from performance_schema.events_statements_summary_by_digest order by count_star desc limit 3 \G;
检查函数最多的sql，消耗读最多
sum_row_examined
返回行数最多，消耗最多的网络带宽
sum_rows_sent
1.1.14. 查找TOP sql -存储过程方式
存储过程
diagnostics()
ps_trace_statement_digest()
statement_performance_analyzer()
ps_trace_thread()
1.1.14.1. diagnostics()
diagnostics() 存储过会生成一个关于当前msyql整体性能的诊断报告
eg：
tee diagnostics.log
call sys.diagnostics(null,null,'current');
notee;
1.1.14.2. ps_trace_statement_digest()
1.1.14.3. statement_performance_analyzer()
1.1.14.4. ps_trace_thread()
INNODB_LOCKS, INNODB_LOCK_WAITS, INNODB_TRX是MYSQL中事务和锁相关的表。通常我们遇到事务超时或锁相关问题时，直接运行下面SQL语句即可进行简单检查：
--查看事务
select * from information_schema.INNODB_TRX;
--查看锁
select * from information_schema.INNODB_LOCKS;
--查看锁等待
select * from information_schema.INNODB_LOCK_WAITS;
1.2. 常用的性能分析表
1.2.1. INNODB_TRX
select * from INFORMATION_SCHEMA.INNODB_TRX;
innodb_trx表提供了当前innodb引擎内每个事务的信息（只读事务除外），包括当一个事务启动，事务是否在等待一个锁，以及交易正在执行的语句（如果有的话）。查询语句
，事务何时启动以及事务正在执行的SQL语句（如果有）
trx_state: 事务状态，一般为RUNNING
trx_started: 事务执行的起始时间，若时间较长，则要分析该事务是否合理
trx_mysql_thread_id: MySQL的线程ID，用于kill 对应的是show processlist中的ID
trx_query: 事务中的sql
1.2.1.1. 表字段
trx_id：唯一事务id号，只读事务和非锁事务是不会创建id的。
TRX_WEIGHT：事务的权重，反应修改的行数（不一定准确）和被事务锁住的行数。为了解决死锁，
innodb会选择一个高度最小的事务来当做牺牲品进行回滚。已经被更改的非交易型表的事务权重比其他事务高，即使改变的行和锁住的行比其他事务低。
TRX_STATE：事务的执行状态，值一般分为：RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.
TRX_STARTED：事务的开始时间
TRX_REQUESTED_LOCK_ID:如果trx_state是lockwait,显示事务当前等待锁的id，不是则为空。想要获取锁的信息，根据该lock_id，以innodb_locks表中lock_id列匹配条件进行查询，获取相关信息。
TRX_WAIT_STARTED：如果trx_state是lockwait,该值代表事务开始等待锁的时间；否则为空。
TRX_MYSQL_THREAD_ID：mysql线程id。想要获取该线程的信息，根据该thread_id，以
INFORMATION_SCHEMA.PROCESSLIST表的id列为匹配条件进行查询。
TRX_QUERY：事务正在执行的sql语句。
TRX_OPERATION_STATE：事务当前的操作状态，没有则为空。
TRX_TABLES_IN_USE：事务在处理当前sql语句使用innodb引擎表的数量。
TRX_TABLES_LOCKED：当前sql语句有行锁的innodb表的数量。（因为只是行锁，不是表锁，表仍然可以被多个事务读和写）
TRX_LOCK_STRUCTS：事务保留锁的数量。
TRX_LOCK_MEMORY_BYTES：在内存中事务索结构占得空间大小。
TRX_ROWS_LOCKED：事务行锁最准确的数量。这个值可能包括对于事务在物理上存在，实际不可见的删除标记的行。
TRX_ROWS_MODIFIED：事务修改和插入的行数
TRX_CONCURRENCY_TICKETS：该值代表当前事务在被清掉之前可以多少工作，由 innodb_concurrency_tickets系统变量值指定。
TRX_ISOLATION_LEVEL：事务隔离等级。
TRX_UNIQUE_CHECKS：当前事务唯一性检查启用还是禁用。当批量数据导入时，这个参数是关闭的。
TRX_FOREIGN_KEY_CHECKS：当前事务的外键坚持是启用还是禁用。当批量数据导入时，这个参数是关闭的
。
TRX_LAST_FOREIGN_KEY_ERROR：最新一个外键错误信息，没有则为空。
TRX_ADAPTIVE_HASH_LATCHED：自适应哈希索引是否被当前事务阻塞。当自适应哈希索引查找系统分
区，一个单独的事务不会阻塞全部的自适应hash索引。自适应hash索引分区通过innodb_adaptive_hash_index_parts参数控制，默认值为8。
TRX_ADAPTIVE_HASH_TIMEOUT：是否为了自适应hash索引立即放弃查询锁，或者通过调用mysql函数保留它。当没有自适应hash索引冲突，该值为0并且语句保持锁直到结束。在冲突过程中，该值被计数为0，每句查询完之后立即释放门闩。当自适应hash索引查询系统被分区（由 innodb_adaptive_hash_index_parts参数控制），值保持为0。
TRX_IS_READ_ONLY：值为1表示事务是read only。
TRX_AUTOCOMMIT_NON_LOCKING：值为1表示事务是一个select语句，该语句没有使用for update或者
shared mode锁，并且执行开启了autocommit，因此事务只包含一个语句。当TRX_AUTOCOMMIT_NON_LOCKING和TRX_IS_READ_ONLY同时为1，innodb通过降低事务开销和改变表数据库来优化事务。
1.2.1.2. 注意事项
该表用于当系统负载较高时，诊断性能问题。
查询该表必须有process权限。
1.2.2. INNODB_LOCKS
提供有关InnoDB 事务已请求但尚未获取的每个锁的信息，以及事务持有的阻止另一个事务的每个锁。
- mysql 8.0以下的版本表名为INFORMATION_SCHEMA.INNODB_LOCKS;
- mysql 8.0开始已经废弃了information_schema.innodb_locks表，变成了
performance_schema.data_locks
1.2.2.1. 表字段
LOCK_ID 一个唯一的锁ID号，内部为 InnoDB。
LOCK_TRX_ID 持有锁的交易的ID
LOCK_MODE 如何请求锁定。允许锁定模式描述符 S，X， IS，IX， GAP，AUTO_INC，和 UNKNOWN。锁定模式描述符可以组合使用以识别特定的锁定模式。
LOCK_TYPE 锁的类型
LOCK_TABLE 已锁定或包含锁定记录的表的名称
LOCK_INDEX 索引的名称，如果LOCK_TYPE是 RECORD; 否则NULL
LOCK_SPACE 锁定记录的表空间ID，如果 LOCK_TYPE是RECORD; 否则NULL
LOCK_PAGE 锁定记录的页码，如果 LOCK_TYPE是RECORD; 否则NULL。
LOCK_REC 页面内锁定记录的堆号，如果 LOCK_TYPE是RECORD; 否则NULL。
LOCK_DATA 与锁相关的数据（如果有）。如果 LOCK_TYPE是RECORD，是锁定的记录的主键值，否则NULL。此列包含锁定行中主键列的值，格式为有效的SQL字符串。
如果没有主键，LOCK_DATA则是唯一的InnoDB内部行ID号。如果对键值或范围高于索引中的最大值的间隙锁定，则LOCK_DATA 报告supremum pseudo-record。当包含锁定记录的页面不在缓冲池中时（如果在保持锁定时将其分页到磁盘），InnoDB不从磁盘获取页面，以避免不必要的磁盘操作。相反， LOCK_DATA设置为 NULL。
1.2.3. INNODB_LOCK_WAITS
包含每个被阻止InnoDB 事务的一个或多个行，指示它已请求的锁以及阻止该请求的任何锁。
performance_schema.INNODB_LOCK_WAITS;
1.2.3.1. 表字段
REQUESTING_TRX_ID 请求（阻止）事务的ID。
REQUESTED_LOCK_ID 事务正在等待的锁的ID。
BLOCKING_TRX_ID 阻止事务的ID。
BLOCKING_LOCK_ID 由阻止另一个事务继续进行的事务所持有的锁的ID
1.2.4. metadata_locks
可以查看会话正在等待哪个锁，还可以查看当前哪个会话持有该锁。
Performance Schema 通过 metadata_locks 表公开元数据锁信息：
• 已授予的锁（显示哪些会话拥有哪些当前元数据锁）。
• 已请求但尚未授予的锁（显示哪些会话正在等待哪些会话正在等待）
元数据锁）。
• 已被死锁检测器终止的锁定请求。
• 已超时并正在等待请求会话的锁定请求被处理的锁定请求
1.2.4.1. 表字段
OBJECT_TYPE：
元数据锁子系统中使用的锁类型。 该值是 GLOBAL、SCHEMA、TABLE、
FUNCTION、PROCEDURE、TRIGGER（当前未使用）、EVENT、COMMIT、USER LEVEL LOCK、
A value of USER LEVEL LOCK indicates a lock acquired with GET_LOCK().
A value of LOCKING SERVICE indicates a lock acquired with the locking service
OBJECT_SCHEMA：
The schema that contains the object
OBJECT_NAME ：
The name of the instrumented object.
COLUMN_NAME：
OBJECT_INSTANCE_BEGIN
检测对象在内存中的地址。
The address in memory of the instrumented object.
LOCK_TYPE
来自元数据锁子系统的锁类型。
INTENTION_EXCLUSIVE 之一，
SHARED、SHARED_HIGH_PRIO、SHARED_READ、SHARED_WRITE、SHARED_UPGRADABLE、
SHARED_NO_WRITE、SHARED_NO_READ_WRITE 或 EXCLUSIVE。
LOCK_DURATION
元数据锁定子系统的锁定持续时间。 该值是 STATEMENT 之一，
交易，或明确。 STATEMENT 和 TRANSACTION 值分别表示在语句或事务结束时隐式释放的锁。 EXPLICIT 值表示锁定
在语句或事务结束后幸存下来并通过显式操作释放，例如全局锁
使用带读锁的 FLUSH TABLES 获得。
LOCK_STATUS
从元数据锁子系统中获得的锁定状态
The value is one of PENDING, GRANTED,
VICTIM, TIMEOUT, KILLED, PRE_ACQUIRE_NOTIFY, or POST_RELEASE_NOTIFY.
由Performance Schema指定
SOURCE
包含产生事件的检测代码的源文件的名称，以及发生检测的文件中的行号。这使您可以检查源代码，以确定到底涉及了哪些代码。
OWNER_THREAD_ID
The thread requesting a metadata lock.
OWNER_EVENT_ID
The event requesting a metadata lock.
1.2.5. threads
select * from performance_schema.threads;
1.2.5.1. 表字段：
THREAD_ID
唯一的线程标识符。
NAME
与服务器中的线程检测代码关联的名称。例如，thread/sql/one_connection与代码中负责处理用户连接的线程功能相对应，thread/sql/main代表main()服务器的功能。
TYPE
线程类型FOREGROUND或BACKGROUND。用户连接线程是前台线程。与内部服务器活动关联的线程是后台线程。示例包括内部InnoDB线程，将信息发送到从属服务器的“ binlog dump ”线程以及从属I/ O和SQL线程。
PROCESSLIST_ID
对于INFORMATION_SCHEMA.PROCESSLIST表中显示的线程，此值ID与该表的列中显示的值相同。它也是在输出Id列中显示的值SHOW PROCESSLIST，以及CONNECTION_ID()将在该线程内返回的值。
对于后台线程（与用户连接无关的线程），PROCESSLIST_ID为NULL，因此值不是唯一的。
PROCESSLIST_USER
与前台线程关联的用户，NULL用于后台线程。
PROCESSLIST_HOST
与前台线程关联的客户端的主机名，NULL用于后台线程。
不同于HOST所述的列INFORMATION_SCHEMAPROCESSLIST表或Host列SHOW PROCESSLIST输出，所述PROCESSLIST_HOST列不包括用于TCP / IP连接的端口号。要从性能架构中获取此信息，请启用套接字检测（默认情况下未启用）并
PROCESSLIST_DB
线程的默认数据库，NULL如果没有。（正在操作的DB）
PROCESSLIST_COMMAND
对于前台线程，该线程代表客户端执行的命令类型，或者Sleep会话处于空闲状态。有关线程命令的描述，请参见“检查线程信息”。该列的值对应于客户端/服务器协议的命令和状态变量。请参见“服务器状态变量”COM_xxxCom_xxx
后台线程不代表客户端执行命令，因此此列可能为NULL。
PROCESSLIST_TIME
线程处于其当前状态的时间（以秒为单位）。
PROCESSLIST_STATE
指示线程正在执行的操作，事件或状态。有关PROCESSLIST_STATE值的描述，请参见“检查线程信息”。如果值为if NULL，则该线程可能对应于一个空闲的客户端会话，或者该线程正在执行的工作没有阶段性。
大多数状态对应于非常快速的操作。如果线程在给定状态下停留许多秒钟，则可能存在值得研究的问题。
PROCESSLIST_INFO
线程正在执行的语句，或者NULL未执行任何语句的语句。该语句可能是发送到服务器的那条语句，或者是最内部的语句（如果该语句执行其他语句）。例如，如果一条CALL语句执行了正在执行一条SELECT语句的存储过程，则该PROCESSLIST_INFO值将显示该SELECT语句。
PARENT_THREAD_ID
如果此线程是子线程（由另一个线程生成），则这是生成线程的THREAD_ID值。
ROLE
没用过。
INSTRUMENTED
1.是否检测线程执行的事件。值为YES或NO。
- 对于前台线程，初始INSTRUMENTED值由与该线程关联的用户帐户是否与setup_actors表中的任何行匹配来确定。匹配基于PROCESSLIST_USER和PROCESSLIST_HOST列的值。
2.如果线程产生了一个子线程，则为该子线程threads创建的表行将再次发生匹配。
- 对于后台线程，INSTRUMENTED就是YES默认。setup_actors未咨询，因为没有关联用户使用后台线程。
- 对于任何线程，INSTRUMENTED可以在线程的生存期内更改其值。
为了监视由线程执行的事件的发生，这些情况必须为真：
- 表中的thread_instrumentation使用者setup_consumers必须为YES。
- 该threads.INSTRUMENTED列必须为YES。
- 监测只发生于从具有仪器产生的那些线程事件ENABLED列设置为YES在setup_instruments表中。
HISTORY
1.是否记录线程的历史事件。值为YES或NO。
对于前台线程，初始HISTORY值由与该线程关联的用户帐户是否与setup_actors表中的任何行匹配来确定。匹配基于PROCESSLIST_USER和PROCESSLIST_HOST列的值。
如果线程产生了一个子线程，则为该子线程threads创建的表行将再次发生匹配。
对于后台线程，HISTORY就是YES默认。setup_actors未咨询，因为没有关联用户使用后台线程。
对于任何线程，HISTORY可以在线程的生存期内更改其值。
为了使线程发生历史事件日志，这些条件必须为真：
setup_consumers必须在表中启用与历史记录相关的适当使用者。例如，在events_waits_history和events_waits_history_long表中的等待事件日志记录要求相应的events_waits_history和events_waits_history_long使用者为YES。
该threads.HISTORY列必须为YES。
仅对在表中将ENABLED列设置为的仪器产生的那些线程事件进行记录。YESsetup_instruments
CONNECTION_TYPE
用于建立连接或NULL后台线程的协议。允许的值为TCP/IP（未加密建立的TCP / IP连接），SSL/TLS（通过加密建立的TCP / IP连接），Socket（Unix套接字文件连接），Named Pipe（Windows命名管道连接）和Shared Memory（Windows共享内存连接）。
THREAD_OS_ID
基础操作系统定义的线程或任务标识符（如果有）：
当MySQL线程在其生命周期内与同一操作系统线程关联时，THREAD_OS_ID包含操作系统线程ID。
- 当MySQL线程在其生命周期内未与同一操作系统线程关联时，THREAD_OS_ID包含NULL。使用线程池插件时，这通常用于用户会话（请参见“ MySQL Enterprise线程池”）。
RESOURCE_GROUP
资源组标签。该值是NULL当前平台或服务器配置上不支持资源组的情况（请参阅资源组限制）。
1.2.6. mysql 8.0中的data_locks
全新的MySQL 8.0新增了全新的锁观测方式，在performance_schema下新增了data_locks表和data_lock_waits表
show tables like '%data_lock%';
select * from performance_schema.data_locks \G;
关联：
THREAD_ID 和performance_schema.threads的THREAD_ID
1.2.6.1. 表字段：
ENGINE：持有或请求锁定的存储引擎
ENGINE_LOCK_ID：存储引擎持有或请求的锁的ID，锁ID格式是内部的，随时可能更改。
ENGINE_TRANSACTION_ID：请求锁定的事务存储引擎内部ID，可以将其视为锁的所有者
THREAD_ID：对应事务的线程ID，如果需要获取更详细的信息，需要关联threads表的THREAD_ID
EVENT_ID：指明造成锁的EVENT_ID，THREAD_ID+EVENT_ID对应parent EVENT，可以在以下几张表内获得信息
- events_waits_xx表查看等待事件
- events_stages_xxx查看到了哪个阶段
- events_statements_xx表查看对应的SQL语句
- events_transactions_current对应查看事务信息
OBJECT_SCHEMA：对应锁表的schema名称
OBJECT_NAME：对应锁的表名
PARTITION_NAME：对应锁的分区名
SUBPARTITION_NAME：对应锁的子分区名
INDEX_NAME：锁对应的索引名称，InnoDB表不会为NULL
OBJECT_INSTANCE_BEGIN：锁对应的内存地址
LOCK_TYPE：对应的锁类型，对InnoDB而言，可为表锁或者行锁
LOCK_MODE：锁模式，对应值可能为S[,GAP], X[, GAP], IS[,GAP], IX[,GAP], AUTO_INC和UNKNOWN
LOCK_STATUS：锁状态，可能为GRANTED或者WAITING
LOCK_DATA：锁对应的数据，例如如果锁定的是主键，那么该列对应的就是加锁的主键值
1.2.7. data_lock_waits
select * from performance_schema.data_lock_waits \G;
关联：
BLOCKING_ENGINE_LOCK_ID 对应data_locks.ENGINE_LOCK_ID;
1.2.7.1. 表字段
ENGINE：请求的锁的引擎
REQUESTING_ENGINE_LOCK_ID：请求的锁在存储引擎中的锁ID
REQUESTING_ENGINE_TRANSACTION_ID：请求锁的事务对应的事务ID
REQUESTING_THREAD_ID：请求锁的线程ID
REQUESTING_EVENT_ID：请求锁的EVENT ID
REQUESTING_OBJECT_INSTANCE_BEGIN：请求的锁的内存地址
BLOCKING_ENGINE_LOCK_ID：阻塞的锁的ID，对应data_locks表的ENGINE_LOCK_ID列
BLOCKING_ENGINE_TRANSACTION_ID：锁阻塞的事务ID
BLOCKING_THREAD_ID：锁阻塞的线程ID
BLOCKING_EVENT_ID：锁阻塞的EVENT ID
BLOCKING_OBJECT_INSTANCE_BEGIN：阻塞的锁内存地址
区别于之前的通过innodb_lock_waits的方式，即便没有产生锁等待，data_locks也能显示出已经加锁的行，另外隐式锁能够显示，这对于DBA分析锁来说无疑是非常有帮助的，相信有了这两张表的加持，DBA分析锁能够更加得心应手。
关于排查未提交事务导致的事务阻塞问题
1.查询当前正在进行的事务信息
包含事务的sql,用户名，主机名、数据库等信息
select a.*,b.* from information_schema.INNODB_TRX a
LEFT JOIN information_schema.`PROCESSLIST` b
on a.trx_mysql_thread_id=b.id
2.查询当前数据库中所有表的锁信息
select * from performance_schema.metadata_locks where owner_thread_id!=sys.ps_thread_id(connection_id()) and object_schema='idoc'
更具事务ID 关联，获取所有的表的执行的sql语句信息
select * from performance_schema.events_statements_current where event_id in('428425',
'43',
'46',
'47',
'50',
'45',
'41',
'830554')
获取具体被锁的数据
select * from performance_schema.data_locks
select * from rec_serial_no where id=2219471096875319303
浙公网安备 33010602011771号