MySQL Performance Schema

镇海的鱼

1.  Performance Schema Lock Tables
MySQL安装以后，我们会看到有这么两个数据库：information_schema 和 performance_schema ，它们对于排查问题是非常有用的。
Performance Schema 是一种存储引擎，默认情况下，它是启用的。

performance_schema数据库的名称是小写的，其中的表的名称也是小写的。查询应该用小写字母指定名称。

1. SHOW VARIABLES LIKE 'performance_schema';
3. SHOW ENGINES;
5. USE performance_schema;
7. SHOW TABLES;
9. SHOW TABLES FROM performance_schema;
11. SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema';
13. show variables like 'autocommit';

复制代码

Performance Schema 通过下面这些表来公开锁的信息：

• data_locks ：持有和请求的数据锁
  
• data_lock_waits ：数据锁的拥有者与被这些拥有者阻塞的请求者之间的关系
  
• metadata_locks ：持有和请求的元数据锁
  
• table_handles ：持有和请求的表锁
  

1.1.  data_locks表
data_locks表显示持有和请求的数据锁。它包含以下字段：

ENGINE	持有或请求锁的存储引擎
ENGINE_LOCK_ID	存储引擎持有或请求的锁的ID。元组(ENGINE_LOCK_ID, ENGINE)的值是唯一的
ENGINE_TRANSACTION_ID	事务在存储引擎里面的内部ID。对于InnoDB，为了获取详细信息，还需要关联INFORMATION_SCHEMA.INNODB_TRX表的TRX_ID列。
THREAD_ID	创建锁的会话的线程ID。为了获取线程的详细信息，需要关联performance_schema.threads表的THREAD_ID列。
EVENT_ID	造成锁的Performance Schema事件。元组(THREAD_ID, EVENT_ID)的值标识了Performance Schema表中的父事件。
OBJECT_SCHEMA	包含锁的表
OBJECT_NAME	被锁定表的名称
PARTITION_NAME	被锁定分区的名称
SUBPARTITION_NAME	被锁定的子分区的名称
INDEX_NAME	被锁定的索引的名称
OBJECT_INSTANCE_BEGIN	锁在内存中的地址
LOCK_TYPE	锁的类型（该值取决于存储引擎）
LOCK_MODE	如何请求锁（该值取决于存储引擎）
LOCK_STATUS	锁请求的状态（该值取决于存储引擎）
LOCK_DATA	与锁相关的数据(如果有的话)。该值依赖于存储引擎。对于InnoDB，如果LOCK_TYPE为RECORD，则显示一个值，否则为NULL。对于放置在主键索引上的锁，显示锁定记录的主键值。锁定记录的二级索引值显示为附加在二级索引上的锁的主键值。如果没有主键，LOCK_DATA将根据InnoDB集群索引的使用规则显示所选唯一索引的键值或唯一的InnoDB内部行ID号。

information_schema.INNODB_LOCKS 与 performance_schema.data_locks 表的区别：

• 如果一个事务持有一个锁，INNODB_LOCKS只在另一个事务正在等待它的时候显示这个锁。而data_locks不管是否有任何事务正在等待它都显示这个锁。
  
• 与INNODB_LOCKS相比，data_locks表没有LOCK_SPACE、LOCK_PAGE、LOCK_REC这几列。
  
• INNODB_LOCKS表需要全局的PROCESS权限。data_locks表需要在要选择的表上使用通常的SELECT权限。
  

INNODB_LOCKS到data_locks列的映射：
INNODB_LOCKS 列data_locks 列LOCK_IDENGINE_LOCK_IDLOCK_TRX_IDENGINE_TRANSACTION_IDLOCK_MODELOCK_MODELOCK_TYPELOCK_TYPELOCK_TABLEOBJECT_SCHEMA ,  OBJECT_NAMELOCK_INDEXINDEX_NAMELOCK_SPACENoneLOCK_PAGENoneLOCK_RECNoneLOCK_DATALOCK_DATA1.2.  data_lock_waits表
data_lock_waits表实现了一种多对多关系，显示了data_locks表中的哪些数据锁请求被data_locks表中持有的哪些数据锁阻塞。data_locks中的持有锁只有在阻塞了某些锁请求时才会出现在data_lock_waits中。
这些信息使得我们能够了解会话之间的数据锁依赖关系。该表不仅显示会话或事务正在等待的锁，还显示当前持有该锁的会话或事务。

ENGINE	请求锁的存储引擎
REQUESTING_ENGINE_LOCK_ID	存储引擎请求的锁ID。为了获得锁的详细信息，需要关联data_locks表的ENGINE_LOCK_ID列。
REQUESTING_ENGINE_TRANSACTION_ID	请求锁的事务的存储引擎内部ID
REQUESTING_THREAD_ID	请求锁的会话的线程ID
REQUESTING_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	造成阻塞锁的事件
BLOCKING_OBJECT_INSTANCE_BEGIN	阻塞锁在内存中的地址

INNODB_LOCK_WAITS表需要全局的PROCESS权限，data_lock_waits表只需要在选择的表上拥有SELECT权限即可。
2.  InnoDB 事务和锁信息
一个 INFORMATION_SCHEMA 表和两个 Performance Schema 表可以监视InnoDB事务并诊断潜在的锁问题。

• INNODB_TRX ：这个INFORMATION_SCHEMA表提供了当前在InnoDB内执行的每个事务的信息，包括事务状态(例如，它是正在运行还是正在等待锁)，事务何时启动，以及事务正在执行的特定SQL语句。
  
• data_locks ：这个Performance Schema表为每个持有锁和每个等待持有锁被释放的锁请求包含一行：
  
  
  
  • 无论持有锁的事务的状态（RUNNING, LOCK WAIT, ROLLING BACK, COMMITTING）如何，每个持有的锁都有一行
    
  • InnoDB中的每个事务都在等待另一个事务释放锁(INNODB_TRX.TRX_STATE 是 LOCK WAIT) 
    
  
  
• data_lock_waits ：此Performance Schema表指示哪些事务正在等待给定的锁，或者给定事务正在等待哪个锁
  

2.1.  INNODB_TRX表
INNODB_TRX表提供了当前在InnoDB内部执行的每个事务的信息，包括事务是否正在等待锁，事务何时启动，以及事务正在执行的SQL语句(如果有的话)。

TRX_ID	InnoDB内部唯一的事务ID号
TRX_WEIGHT	事务的权重，反映(但不一定是确切的计数)被更改的行数和被事务锁定的行数。为了解决死锁，InnoDB选择权值最小的事务作为回滚的“受害者”。
TRX_STATE	事务执行状态。允许的值有：RUNNING, LOCK WAIT, ROLLING BACK, COMMITTING
TRX_STARTED	事务开始时间
TRX_REQUESTED_LOCK_ID	如果TRX_STATE为 LOCK WAIT，则表示事务当前正在等待的锁的ID，否则无效。
TRX_WAIT_STARTED	如果TRX_STATE为 LOCK WAIT，则表示事务开始等待锁的时间，否则无效。
TRX_MYSQL_THREAD_ID	MySQL线程ID
TRX_QUERY	事务正在执行的SQL语句
TRX_OPERATION_STATE	事务的当前操作(如果有的话)，否则无效
TRX_TABLES_IN_USE	在处理该事务SQL语句使用的InnoDB表的数量
TRX_TABLES_LOCKED	当前SQL语句加行锁的表的个数
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_IS_READ_ONLY	1表示事务为只读

2.2.  识别阻塞事务
使用以下查询查看哪些事务正在等待，哪些事务正在阻塞它们

1. SELECT
2.   r.trx_id waiting_trx_id,
3.   r.trx_mysql_thread_id waiting_thread,
4.   r.trx_query waiting_query,
5.   b.trx_id blocking_trx_id,
6.   b.trx_mysql_thread_id blocking_thread,
7.   b.trx_query blocking_query
8. FROM
9.   performance_schema.data_lock_waits w
10.   INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
11.   INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;

复制代码

或者，更简单的方式，直接看sys数据库中的 innodb_lock_waits 视图

1. SELECT
2.     waiting_trx_id,
3.     waiting_pid,
4.     waiting_query,
5.     blocking_trx_id,
6.     blocking_pid,
7.     blocking_query
8. FROM
9.     sys.innodb_lock_waits;

复制代码

如果阻塞查询被报告是NULL值，即查询sys.innodb_lock_waits的结果集中blocking_query字段值为NULL，在这种情况下，使用以下步骤来确定阻塞查询：
第1步、确定阻塞事务的processlist ID。在sys.innodb_lock_waits表中，阻塞事务的processlist ID就是blocking_pid字段的值
第2步、用blocking_pid去performance_schema.threads表中查阻塞事务的线程ID

1. SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 9;

复制代码

第3步、用THREAD_ID去查performance_schema.events_statements_current表，以确定线程执行的最后一次查询

1. SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = 50;

复制代码

第4步、如果线程执行的最后一个查询没有足够的信息来确定为什么持有锁，我们还可以查询performance_schema.events_statements_history表来查看线程执行的最后10条语句

1. SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history WHERE THREAD_ID = 50 ORDER BY EVENT_ID;

复制代码

2.3.  InnoDB锁和锁等待信息
当一个事务更新表中的一行，或者用SELECT FOR UPDATE锁定该行时，InnoDB会在该行上建立一个锁列表或锁队列。类似地，InnoDB在表上维护一个表级锁的锁列表。如果第二个事务想以不兼容的方式更新一个被前一个事务锁住的行或锁住一个表，InnoDB就会向相应的队列中添加一个锁请求。为了让事务获得锁，必须删除先前进入该行或表的锁队列的所有不兼容的锁请求（当持有或请求这些锁的事务提交或回滚时发生）。
一个事务可以对不同的行或表有任意数量的锁请求。在任何给定的时间，一个事务可能请求另一个事务持有的锁，在这种情况下，它被另一个事务阻塞。请求事务必须等待持有锁的事务提交或回滚。如果事务没有等待锁，则处于RUNNING状态。如果事务正在等待锁，则处于LOCK WAIT状态。
Performance Schema的data_locks表为每个LOCK WAIT事务保存一个或多个行，表示任何阻止其进行的锁请求。这个表还包含一行，描述了为给定行或表挂起的锁队列中的每个锁。data_lock_waits表显示了某个事务已经持有的哪些锁正在阻塞其他事务请求的锁。
3.  排序索引构建
3.1.  B-tree
在数据库索引中常用的一种树状数据结构。该结构始终保持排序，支持精确匹配(等于操作符)和范围(例如大于、小于和BETWEEN操作符)的快速查找。这种类型的索引适用于大多数存储引擎，如InnoDB和MyISAM。
因为B-tree节点可以有很多子节点，所以B-tree与二叉树不同，二叉树每个节点只能有2个子节点。
MySQL存储引擎使用的B-tree结构可能被视为变体，因为在经典的B-tree设计中不存在复杂的结构。
3.2.  索引构建
InnoDB在创建或重建索引时执行批量加载，而不是一次插入一条索引记录。这种创建索引的方法也称为排序索引构建。
在引入排序索引构建之前，使用插入api将索引条目插入b树，每次插入一条记录。该方法涉及打开b树游标以查找插入位置，然后使用乐观插入将条目插入到b树页面中。如果由于页已满而导致插入失败，则执行悲观插入，这涉及打开b -树游标，并根据需要拆分和合并b -树节点，以便为条目找到空间。这种“自顶向下”构建索引的方法的缺点是搜索插入位置的成本，以及b树节点的不断拆分和合并。
排序索引构建使用“自底向上”的方法来构建索引。使用这种方法，对最右边的叶子页的引用将保存在b树的所有级别上。在必要的b树深度处分配最右边的叶子页，并根据它们的排序顺序插入条目。一旦一个叶页填满，就会向父页追加一个节点指针，并为下一次插入分配一个同级叶页。这个过程一直持续到所有条目都被插入，这可能导致插入到根级别。当分配兄弟页时，对先前固定的叶页的引用将被释放，新分配的叶页将成为最右边的叶页和新的默认插入位置。
4. 文档
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-data-locks-table.html
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-data-lock-waits-table.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-transactions.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-indexes.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

来源:https://www.cnblogs.com/cjsblog/p/17896744.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！