参考官方文档：
https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema.html

InnoDB INFORMATION_SCHEMA表提供有关InnoDB存储引擎各个方面的元数据，状态信息和统计信息。您可以通过在INFORMATION_SCHEMA数据库上发出SHOW TABLES语句来查看InnoDB INFORMATION_SCHEMA表的列表：

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB%';

14.15.1 其中关于压缩的表

有两对关于压缩的InnoDB INFORMATION_SCHEMA表，可以深入了解压缩的整体运行情况：

INNODB_CMP和INNODB_CMP_RESET包含有关压缩操作数和执行压缩所花费的时间的信息。
INNODB_CMPMEM和INNODB_CMP_RESET包含有关为压缩分配内存的方式的信息。

14.15.2 InnoDB INFORMATION_SCHEMA事务和锁定信息

三个InnoDB INFORMATION_SCHEMA表使您能够监视事务并诊断潜在的锁定问题：
INNODB_TRX：包含有关InnoDB中当前正在执行的每个事务的信息，包括事务状态（例如，它是否正在运行或等待锁定），事务何时启动以及事务正在执行的特定SQL语句。
INNODB_LOCKS：InnoDB中等待另一个事务释放锁定（INNODB_TRX.TRX_STATE为LOCK WAIT）的每个事务，都被一个阻塞锁定请求阻塞。阻塞锁定请求是针对由不兼容模式中的另一个事务持有的行或表锁定。阻止事务的锁始终处于与请求锁定模式不兼容的模式（读与写，共享与排他）。在另一个事务提交或回滚之前，阻塞的事务无法继续，从而释放所请求的锁。对于每个被阻止的事务，INNODB_LOCKS包含一行描述事务已请求的每个锁，以及它正在等待的每个锁。对于阻止另一个事务的每个锁，INNODB_LOCKS也包含一行，无论持有锁的事务的状态如何（INNODB_TRX.TRX_STATE是RUNNING，LOCK WAIT，ROLLING BACK或COMMITTING）。
INNODB_LOCK_WAITS：此表指示哪些事务正在等待指定的锁定，或者给定事务正在等待哪个锁定。此表包含每个被阻止事务的一个或多个行，指示它已请求的锁以及阻止该请求的任何锁。 REQUESTED_LOCK_ID值指的是事务请求的锁，BLOCKING_LOCK_ID值指的是阻止第一个事务继续进行的锁（由另一个事务持有）。对于任何给定的阻塞事务，INNODB_LOCK_WAITS中的所有行都具有相同的REQUESTED_LOCK_ID值和不同的BLOCKING_LOCK_ID值。

更简单地说，使用sys schema innodb_lock_waits视图：

SELECT waiting_trx_id, waiting_pid, waiting_query, blocking_trx_id, blocking_pid, blocking_query FROM sys.innodb_lock_waits;

在发布会话变为空闲之后识别阻止查询

标识阻塞事务时，如果发出查询的会话已变为空闲，则会为阻止查询显示NULL值。在这种情况下，请使用以下步骤确定阻止查询：

标识阻塞事务的进程列表ID。在sys.innodb_lock_waits表中，阻塞事务的进程列表ID是blocking_pid值。
使用blocking_pid，查询MySQL Performance Schema threads表以确定阻塞事务的THREAD_ID。例如，如果blocking_pid为6，则发出以下查询：

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;

用THREAD_ID，查询Performance Schema events_statements_current表以确定线程执行的最后一个查询。例如，如果THREAD_ID为28，则发出以下查询：

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = 28\G

如果线程执行的最后一个查询不足以确定锁定的原因，则可以查询Performance Schema events_statements_history表以查看该线程执行的最后10个语句

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

将InnoDB事务与MySQL会话相关联

有时将内部InnoDB锁定信息与MySQL维护的会话级信息相关联是有用的。例如，您可能想知道，对于给定的InnoDB事务ID，阻止其他事务的相应的MySQL会话ID和可能持有锁的会话名称。

INFORMATION_SCHEMA表的以下输出来自有点负载的系统。可以看出，有几个事务正在运行。
INFORMATION_SCHEMA.PROCESSLIST
INFORMATION_SCHEMA.INNODB_TRX
INFORMATION_SCHEMA.INNODB_LOCKS
INFORMATION_SCHEMA.INNODB_LOCK_WAITS

14.15.2.2 InnoDB锁定和锁定等待信息

当事务更新表中的行或使用SELECT FOR UPDATE锁定它时，InnoDB会在该行上建立锁定的列表或队列。类似地，InnoDB维护表的锁定列表以用于表级锁。如果第二个事务要在不兼容模式下更新行或锁定先前事务已锁定的表，InnoDB会将该行的锁定请求添加到相应的队列。对于要由事务获取的锁，必须删除先前进入到该行或表的锁队列中的所有不兼容的锁请求（当持有或请求这些锁的事务提交或回滚时发生）。

事务可以对不同的行或表具有任意数量的锁请求。在任何给定时间，事务可以请求由另一个事务持有的锁，在这种情况下，它被该另一个事务阻止。请求事务必须等待持有阻塞锁的事务提交或回滚。如果事务没有等待锁定，则它处于RUNNING状态。如果事务正在等待锁定，则它处于LOCK WAIT状态。（INFORMATION_SCHEMA INNODB_TRX表指示事务状态值。）

INNODB_LOCKS表为每个LOCK WAIT事务保存一行或多行，指示阻止其进度的任何锁定请求。此表还包含一行，用于描述给定行或表的挂起锁定队列中的每个锁。 INNODB_LOCK_WAITS表显示事务已经保留的锁是阻止其他事务请求的锁。

14.15.2.3 InnoDB事务和锁定信息的持久性和一致性

事务和锁定表（INNODB_TRX，INNODB_LOCKS和INNODB_LOCK_WAITS）公开的数据代表了快速变化数据的一瞬间。这与用户表不同，只有在应用程序启动的更新发生时，数据才会更改。底层数据是内部系统管理的数据，并可以非常快速地变化。

出于性能原因，并且为了最小化在事务和锁定表之间误导连接的可能性，只要在任何表上发出SELECT，InnoDB就会将所需的事务和锁定信息收集到中间缓冲区中。仅当自上次读取缓冲区起超过0.1秒后，才会刷新此缓冲区。填充三个表所需的数据以原子方式一致地获取，并保存在此全局内部缓冲区中，形成时间点“快照”。如果在0.1秒内发生多个表访问（因为当MySQL处理这些表之间的连接时几乎肯定会这样做），则使用相同的快照来满足查询。

在单个查询中将这些表任意连接在一起时，将返回正确的结果，因为这三个表的数据来自同一个快照。由于缓冲区不会在这些表的每个查询中刷新，因此如果在十分之一秒内对这些表发出单独的查询，则从查询到查询的结果是相同的。另一方面，如果2个分离的查询相同或不同表超过了10分之一秒，由于数据来自不同的快照，则可能会看到不同的结果。

由于InnoDB必须在收集事务和锁定数据时暂时停止，因此对这些表的过于频繁的查询会对其他用户的性能产生负面影响。

由于这些表包含敏感信息（INNODB_LOCKS.LOCK_DATA和INNODB_TRX.TRX_QUERY），出于安全原因，只允许具有PROCESS权限的用户从中进行SELECT。

如前所述，填充事务和锁定表（INNODB_TRX，INNODB_LOCKS和INNODB_LOCK_WAITS）的数据会自动获取并保存到提供“时间点”快照的中间缓冲区。从同一快照查询时，所有三个表中的数据是一致的。但是，基础数据变化如此之快，以至于对其他类似快速变化的数据的类似的瞬间可能不同步。因此，在将InnoDB事务和锁定表中的数据与PROCESSLIST表中的数据进行比较时应该小心。 PROCESSLIST表中的数据与来自锁定和事务的数据不是同一个快照。即使您发出单个SELECT（例如，加入INNODB_TRX和PROCESSLIST），这些表的内容通常也不一致。 INNODB_TRX可以引用PROCESSLIST中不存在的行，或者INNODB_TRX.TRX_QUERY中显示的事务的当前正在执行的SQL查询可能与http://PROCESSLIST.INFO中的不同。

14.15.3 InnoDB INFORMATION_SCHEMA系统表

您可以使用InnoDB INFORMATION_SCHEMA系统表提取有关InnoDB管理的架构对象的元数据。此信息来自InnoDB内部系统表（也称为InnoDB数据字典），无法像常规InnoDB表那样直接查询。传统上，您将使用第14.17节“InnoDB监视器”中的技术获取此类信息，设置InnoDB监视器并解析SHOW ENGINE INNODB STATUS语句的输出。 InnoDB INFORMATION_SCHEMA表接口允许您使用SQL查询此数据。

除了INNODB_SYS_TABLESTATS之外，没有相应的内部系统表，InnoDB INFORMATION_SCHEMA系统表直接从内部InnoDB系统表读取数据填充，而不是从内存中缓存的元数据中读取。

InnoDB INFORMATION_SCHEMA系统表包括下面列出的表。在MySQL 5.6.6中添加了INNODB_SYS_DATAFILES和INNODB_SYS_TABLESPACES，并引入了对CREATE TABLE语句的DATA DIRECTORY ='directory'子句的支持，该子句允许在一个位置创建InnoDB每表文件表空间（.ibd文件）在MySQL数据目录之外。

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_SYS%';

表名表示所提供的数据类型：

INNODB_SYS_TABLES提供有关InnoDB表的元数据，相当于InnoDB数据字典中SYS_TABLES表中的信息。
INNODB_SYS_COLUMNS提供有关InnoDB表列的元数据，相当于InnoDB数据字典中SYS_COLUMNS表中的信息。
INNODB_SYS_INDEXES提供有关InnoDB索引的元数据，相当于InnoDB数据字典中SYS_INDEXES表中的信息。
INNODB_SYS_FIELDS提供有关InnoDB索引的键列（字段）的元数据，相当于InnoDB数据字典中SYS_FIELDS表中的信息。
INNODB_SYS_TABLESTATS提供了有关InnoDB表的低级状态信息的视图，该信息源自内存数据结构。没有相应的内部InnoDB系统表。
INNODB_SYS_DATAFILES为InnoDB每表和一般表空间提供数据文件路径信息，相当于InnoDB数据字典中SYS_DATAFILES表中的信息。
INNODB_SYS_TABLESPACES提供有关InnoDB文件每表和一般表空间的元数据，相当于InnoDB数据字典中SYS_TABLESPACES表中的信息。
INNODB_SYS_FOREIGN提供有关InnoDB表上定义的外键的元数据，相当于InnoDB数据字典中SYS_FOREIGN表中的信息。
INNODB_SYS_FOREIGN_COLS提供有关InnoDB表上定义的外键列的元数据，相当于InnoDB数据字典中SYS_FOREIGN_COLS表中的信息。

InnoDB INFORMATION_SCHEMA系统表可以通过TABLE_ID，INDEX_ID和SPACE等字段连接在一起，使您可以轻松检索要研究或监视的对象的所有可用数据。

有关每个表的列的信息，请参阅InnoDB INFORMATION_SCHEMA文档。

例子 14.4 连接 InnoDB INFOMATION_SCHEMA 系统表

此示例演示如何连接三个InnoDB INFORMATION_SCHEMA系统表（INNODB_SYS_TABLES，INNODB_SYS_TABLESPACES和INNODB_SYS_TABLESTATS）以收集有关employees示例数据库中表的文件格式，行格式，页面大小和索引大小信息。

以下表名称别名用于缩短查询字符串：

INFORMATION_SCHEMA.INNODB_SYS_TABLES: a
INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES: b
INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS: c

IF（）控制流函数用于计算压缩表。如果压缩表，则使用ZIP_PAGE_SIZE而不是PAGE_SIZE计算索引大小。以字节为单位报告的CLUST_INDEX_SIZE和OTHER_INDEX_SIZE除以1024 * 1024，以提供以兆字节（MB）为单位的索引大小。使用ROUND（）函数将MB值四舍五入。

SELECT a.NAME, a.FILE_FORMAT, a.ROW_FORMAT, @page_size := IF(a.ROW_FORMAT='Compressed', b.ZIP_PAGE_SIZE, b.PAGE_SIZE) AS page_size, ROUND((@page_size * c.CLUST_INDEX_SIZE) /(1024*1024)) AS pk_mb, ROUND((@page_size * c.OTHER_INDEX_SIZE) /(1024*1024)) AS secidx_mb FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES a INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES b on a.NAME = b.NAME INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS c on b.NAME = c.NAME WHERE a.NAME LIKE 'employees/%' ORDER BY a.NAME DESC;

14.15.5 InnoDB INFORMATION_SCHEMA buffer Pool 表

InnoDB INFORMATION_SCHEMA缓冲池表提供有关InnoDB缓冲池中页面的缓冲池状态信息和元数据。

InnoDB INFORMATION_SCHEMA缓冲池表包括下面列出的表：

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_BUFFER%';

+-----------------------------------------------+

| Tables_in_INFORMATION_SCHEMA (INNODB_BUFFER%) |

+-----------------------------------------------+

+-----------------------------------------------+

表概述

INNODB_BUFFER_PAGE：保存InnoDB缓冲池中每个页面的信息。
INNODB_BUFFER_PAGE_LRU：保存有关InnoDB缓冲池中页面的信息，特别是它们在LRU列表中的排序方式，确定哪些页面在缓冲池变满时从缓冲池中逐出。 INNODB_BUFFER_PAGE_LRU表与INNODB_BUFFER_PAGE表具有相同的列，但INNODB_BUFFER_PAGE_LRU表具有LRU_POSITION列而不是BLOCK_ID列。
INNODB_BUFFER_POOL_STATS：提供缓冲池状态信息。许多相同的信息由SHOW ENGINE INNODB STATUS输出提供，或者可以使用InnoDB缓冲池服务器状态变量获得

警告

查询INNODB_BUFFER_PAGE或INNODB_BUFFER_PAGE_LRU表可能会影响性能。除非您了解性能影响并确定其可接受，否则请勿在生产系统上查询这些表。为避免影响生产系统的性能，请在测试实例上重现要调查的问题并查询缓冲池统计信息。

例子 14.6 在INNODB_BUFFER_PAGE 表查询系统数据

此查询通过排除TABLE_NAME值为NULL或包含斜杠/或句点的页面，提供包含系统数据的页面的近似计数，表示用户定义的表。

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);
+----------+
| COUNT(*) |
+----------+
| 1516 |
+----------+

此查询返回包含系统数据的大致页数，缓冲池页的总数以及包含系统数据的页的大致百分比。

mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0) ) AS system_pages, ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE ) AS total_pages, ( SELECT ROUND((system_pages/total_pages) * 100) ) AS system_page_percentage;

可以通过查询PAGE_TYPE值来确定缓冲池中的系统数据类型。例如，以下查询在包含系统数据的页面中返回八个不同的PAGE_TYPE值：

mysql> SELECT DISTINCT PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);

例子 14.7 查询在INNODB_BUFFER_PAGE 表的用户数据

此查询通过计算TABLE_NAME值为NOT NULL且 NOT LIKE '％INNODB_SYS_TABLES％'的页面，提供包含用户数据的页面的近似计数。

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NOT NULL AND TABLE_NAME NOT LIKE '%INNODB_SYS_TABLES%';
+----------+
| COUNT(*) |
+----------+
| 7897 |
+----------+

此查询返回包含用户数据的大致页数，缓冲池页面的总数以及包含用户数据的页面的大致百分比。

mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, '/') > 0 OR INSTR(TABLE_NAME, '.') > 0) ) AS user_pages, ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE ) AS total_pages, ( SELECT ROUND((user_pages/total_pages) * 100) ) AS user_page_percentage;

此查询使用缓冲池中的页面标识用户定义的表：

mysql> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, '/') > 0 OR INSTR(TABLE_NAME, '.') > 0) AND TABLE_NAME NOT LIKE '`mysql`.`innodb_%';
+-------------------------+
| TABLE_NAME |
+-------------------------+
| `employees`.`salaries` |
| `employees`.`employees` |
+-------------------------+

例子 14.8 INNODB_BUFFER_PAGE表中查询索引数据

有关索引页面的信息，请使用索引名称查询INDEX_NAME列。例如，以下查询返回在employees.salaries表上定义的emp_no索引的页数和页面总数据大小：

mysql> SELECT INDEX_NAME, COUNT(*) AS Pages, ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@global.innodb_page_size, COMPRESSED_SIZE))/1024/1024) AS 'Total Data (MB)' FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE INDEX_NAME='emp_no' AND TABLE_NAME = '`employees`.`salaries`';

例子 14.9 查询INNODB_BUFFER_PAGE_LRU表中的LRU_POSITION数据

INNODB_BUFFER_PAGE_LRU表保存有关InnoDB缓冲池中页面的信息，特别是它们的排序方式，用于确定哪些页面在缓冲池变满时从缓冲池中逐出。此页面的定义与INNODB_BUFFER_PAGE相同，但此表具有LRU_POSITION列而不是BLOCK_ID列。

此查询计算employees.employees表的页面占用的LRU列表中特定位置的位置数。

mysql> SELECT COUNT(LRU_POSITION) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU WHERE TABLE_NAME='`employees`.`employees`' AND LRU_POSITION < 3072;

例子 14.10 查询INNODB_BUFFER_POOL_STATS 表

INNODB_BUFFER_POOL_STATS表提供类似于SHOW ENGINE INNODB STATUS和InnoDB缓冲池状态变量的信息。

SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS \G

为了比较，SHOW ENGINE INNODB STATUS输出和InnoDB缓冲池状态变量输出如下所示，基于相同的数据集。

SHOW STATUS LIKE 'Innodb_buffer%';

14.15.6 InnoDB INFOMATION_SCHEMA 指标表

MySQL 5.6.2中引入的INNODB_METRICS表将所有InnoDB性能和资源相关计数器合并到一个INFORMATION_SCHEMA表中。

INNODB_METRICS表的列显示在以下示例中。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts" \G

启用，禁用和重置计数器

您可以使用以下配置选项启用，禁用和重置计数器：

innodb_monitor_enable：启用一个或多个计数器。

SET GLOBAL innodb_monitor_enable = [counter-name|module_name|pattern|all];

innodb_monitor_disable：禁用一个或多个计数器。

SET GLOBAL innodb_monitor_disable = [counter-name|module_name|pattern|all];

innodb_monitor_reset：将一个或多个计数器的计数值重置为零。

SET GLOBAL innodb_monitor_reset = [counter-name|module_name|pattern|all];

innodb_monitor_reset_all：重置一个或多个计数器的所有值。在使用innodb_monitor_reset_all之前，必须禁用计数器。

SET GLOBAL innodb_monitor_reset_all = [counter-name|module_name|pattern|all];

您还可以使用MySQL服务器配置文件在启动时启用计数器和计数器模块。例如，要启用日志模块，metadata_table_handles_opened和metadata_table_handles_closed计数器，请在my.cnf配置文件的[mysqld]部分中输入以下行。

[mysqld]
innodb_monitor_enabl = module_recovery,metadata_table_handles_opened,metadata_table_handles_closed

在配置文件中启用多个计数器或模块时，必须指定innodb_monitor_enable配置选项，后跟由逗号分隔的计数器和模块名称，如上例所示。只能在配置文件中使用innodb_monitor_enable选项。在命令行上仅支持禁用和重置配置选项。

Counters

INNODB_METRICS表中表示的计数器可能会发生变化，因此对于最新列表，请查询正在运行的MySQL服务器。

默认启用的计数器对应于SHOW ENGINE INNODB STATUS使用的计数器。 SHOW ENGINE INNODB STATUS使用的计数器在系统级别始终处于“打开”状态，但您可以根据需要为INNODB_METRICS表禁用这些计数器。此外，计数器状态不是持久的。除非另行指定，否则在重新启动服务器时，计数器将恢复为其默认启用或禁用状态。

如果您运行的程序会受到INNODB_METRICS表的添加或更改的影响，建议您在升级之前查看发行说明并查询INNODB_METRICS表以获取新版本。

mysql> SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS ORDER BY NAME;

Counter 模块

模块名称对应于INNODB_METRICS表的SUBSYSTEM列中的值，但不相同。而是单独启用，禁用或重置计数器，您可以使用模块名称快速启用，禁用或重置特定子系统的所有计数器。例如，使用module_dml启用与dml子系统关联的所有计数器。

mysql> SET GLOBAL innodb_monitor_enable = module_dml;
mysql> SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem ='dml';

以下是您可以将module_name与innodb_monitor_enable和相关配置选项一起使用的值，以及相应的SUBSYSTEM名称：

module_adaptive_hash (subsystem =adaptive_hash_index)
module_buffer (subsystem = buffer)
module_buffer_page (subsystem =buffer_page_io)
module_compress (subsystem =compression)
module_ddl (subsystem = ddl)
module_dml (subsystem = dml)
module_file (subsystem = file_system)
module_ibuf_system (subsystem =change_buffer)
module_icp (subsystem = icp)
module_index (subsystem = index)
module_innodb (subsystem = innodb)
module_lock (subsystem = lock)
module_log (subsystem = recovery)
module_metadata (subsystem = metadata)
module_os (subsystem = os)
module_purge (subsystem = purge)
module_trx (subsystem = transaction)

例子 14.11 使用INNODB_METRICS表计数器

此示例演示启用，禁用和重置计数器，以及查询INNODB_METRICS表中的计数器数据。

创建一个简单的InnoDB 表：

mysql> USE test;
Database changed
mysql> CREATE TABLE t1 (c1 INT) ENGINE=INNODB;
Query OK, 0 rows affected (0.02 sec)

启用 dml_inserts 基数器

mysql> SET GLOBAL innodb_monitor_enable = dml_inserts; Query OK, 0 rows affected (0.01 sec)

可以在INNODB_METRICS表的COMMENT列中找到dml_inserts计数器的说明：

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts";

在INNODB_METRICS表中查询dml_inserts计数器数据。由于未执行任何DML操作，因此计数器值为零或NULL。 TIME_ENABLED和TIME_ELAPSED值指示计数器上次启用的时间以及自此时间以来经过的秒数。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts" \G

在表中插入三行数据。

mysql> INSERT INTO t1 values(1); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t1 values(2); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t1 values(3); Query OK, 1 row affected (0.00 sec)

再次查询INNODB_METRICS表以获取dml_inserts计数器数据。现在，许多计数器值已递增，包括COUNT，MAX_COUNT，AVG_COUNT和COUNT_RESET。有关这些值的说明，请参阅INNODB_METRICS表定义。

SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G

重置dml_inserts计数器，并再次查询INNODB_METRICS表以获取dml_inserts计数器数据。先前报告的％_RESET值（例如COUNT_RESET和MAX_RESET）将重新设置为零。从计数器启用时起累积收集数据的COUNT，MAX_COUNT和AVG_COUNT等值不受重置的影响。

mysql> SET GLOBAL innodb_monitor_reset = dml_inserts; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G

要重置所有计数器值，必须先禁用计数器。禁用计数器会将STATUS值设置为disbaled。

mysql> SET GLOBAL innodb_monitor_disable = dml_inserts;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G

注意：
计数器和模块名称支持通配符匹配。例如，您可以指定dml_i％，而不是指定完整的dml_inserts计数器名称。您还可以使用通配符匹配一次启用，禁用或重置多个计数器或模块。例如，指定dml_％以启用，禁用或重置以dml_％开头的所有计数器。

禁用计数器后，可以使用innodb_monitor_reset_all选项重置所有计数器值。所有值都设置为零或NULL。

mysql> SET GLOBAL innodb_monitor_reset_all = dml_inserts; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G

例子 14.12 INNODB_TEMP_TABLE_INFO

此示例演示INNODB_TEMP_TABLE_INFO表的特征。

创建简单的 InnoDB 临时表：

mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;

查询INNODB_TEMP_TABLE_INFO以查看临时表元数据。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G *************************** 1. row ***************************
TABLE_ID: 194
NAME: #sql7a79_1_0
N_COLS: 4
SPACE: 182
PER_TABLE_TABLESPACE: FALSE
IS_COMPRESSED: FALSE

TABLE_ID是临时表的唯一标识符。 NAME列显示临时表的系统生成的名称，前缀为“#sql”。列数（N_COLS）是4而不是1，因为InnoDB总是创建三个隐藏的表列（DB_ROW_ID，DB_TRX_ID和DB_ROLL_PTR）。压缩临时表的PER_TABLE_TABLESPACE和IS_COMPRESSED报告为TRUE。否则，这些字段为FALSE。
创建被压缩的临时表

mysql> CREATE TEMPORARY TABLE t2 (c1 INT) ROW_FORMAT=COMPRESSED ENGINE=INNODB;

再此查询 INNODB_TEMP_TABLE_INFO

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G

对于压缩的临时表，PER_TABLE_TABLESPACE和IS_COMPRESSED显示为TRUE。压缩临时表的SPACE ID不同，因为压缩临时表是在单独的每表文件表空间中创建的。在共享临时表空间（ibtmp1）中创建非压缩临时表，并显示相同的SPACE ID。

重启MYSQL并查询 INNODB_TEMP_TABLE_INFO.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G Empty set (0.00 sec)

返回空集，因为INNODB_TEMP_TABLE_INFO及其中的数据在服务器关闭时未持久保存到磁盘。
创建新的临时表

mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;

查询INNODB_TEMP_TABLE_INFO以查看临时表元数据。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G

TABLE_ID: 196
NAME: #sql7b0e_1_0
N_COLS: 4
SPACE: 184
PER_TABLE_TABLESPACE: FALSE
IS_COMPRESSED: FALSE

SPACE ID是新的，因为它是在服务器重新启动时动态生成的。

14.15.8 从 INFOMATION_SCHEMA.FILES中检索 InnoDB 表空间元数据

INFORMATION_SCHEMA.FILES表提供有关所有InnoDB表空间类型的元数据，包括每表文件表空间，通用表空间，系统表空间，临时表表空间和撤消表空间（如果存在）。

注意：
INNODB_SYS_TABLESPACES和INNODB_SYS_DATAFILES表还提供有关InnoDB表空间的元数据，但数据仅限于每个表文件和一般表空间。

此查询从与InnoDB表空间相关的INFORMATION_SCHEMA.FILES表的字段中检索有关InnoDB系统表空间的元数据。与InnoDB无关的INFORMATION_SCHEMA.FILES字段始终返回NULL，并从查询中排除。

mysql> SELECT FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME,FREE_EXTENTS, TOTAL_EXTENTS, EXTENT_SIZE, INITIAL_SIZE,MAXIMUM_SIZE, AUTOEXTEND_SIZE, DATA_FREE, STATUS ENGINE FROMINFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME LIKE 'innodb_system'\G

此查询检索InnoDB file-per-table和一般表空间的FILE_ID（相当于空间ID）和FILE_NAME（包括路径信息）。 File-per-table和general tablespaces具有.ibd文件扩展名。

mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHEREFILE_NAME LIKE '%.ibd%' ORDER BY FILE_ID;

此查询检索InnoDB临时表空间的FILE_ID和FILE_NAME。临时表空间文件名以ibtmp为前缀。

mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHEREFILE_NAME LIKE '%ibtmp%';

同样，InnoDB undo表空间文件名以undo为前缀。如果配置了单独的undo表空间，则以下查询将返回InnoDB undo表空间的FILE_ID和FILE_NAME。

mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHEREFILE_NAME LIKE '%undo%';

ITPub博客

14.15 INFOMATION_SCHEMA 的表

最新文章