MySQL :: MySQL 8.4 参考手册 :: 28.4.26 INFORMATION_SCHEMA INNODB

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 28 个 INFORMATION_SCHEMA 表 / 28.4 INFORMATION_SCHEMA InnoDB 表 / 28.4.26 INFORMATION_SCHEMA INNODB_TABLESTATS 视图

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 73%

28.4.26 INFORMATION_SCHEMA INNODB_TABLESTATS 视图28.4.26 The INFORMATION_SCHEMA INNODB_TABLESTATS View

INNODB_TABLESTATS 表提供了关于 InnoDB 表的底层状态信息的视图。这些数据被 MySQL 优化器用来计算在查询 InnoDB 表时使用哪个索引。这些信息源自内存中的数据结构，而非存储在磁盘上的数据。这没有对应的内部 InnoDB 系统表。The INNODB_TABLESTATS table provides a view of low-level status information about InnoDB tables. This data is used by the MySQL optimizer to calculate which index to use when querying an InnoDB table. This information is derived from in-memory data structures rather than data stored on disk. There is no corresponding internal InnoDB system table.

如果 InnoDB 表自上次服务器重启以来已被打开且尚未从表缓存中淘汰，则该视图中会显示这些表。对于有持久统计信息可用的表，该视图中始终会显示这些表。InnoDB tables are represented in this view if they have been opened since the last server restart and have not aged out of the table cache. Tables for which persistent stats are available are always represented in this view.

表统计信息仅在修改索引列的 DELETE 或 UPDATE 操作时更新。仅修改非索引列的操作不会更新统计信息。Table statistics are updated only for DELETE or UPDATE operations that modify indexed columns. Statistics are not updated by operations that modify only nonindexed columns.

ANALYZE TABLE 会清除表统计信息，并将 STATS_INITIALIZED 列设置为 Uninitialized。下次访问表时将重新收集统计信息。ANALYZE TABLE clears table statistics and sets the STATS_INITIALIZED column to Uninitialized. Statistics are collected again the next time the table is accessed.

有关相关的使用信息和示例，请参见第 17.15.3 节，“InnoDB INFORMATION_SCHEMA 模式对象表”。For related usage information and examples, see Section 17.15.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.

INNODB_TABLESTATS 表具有以下列：The INNODB_TABLESTATS table has these columns:

TABLE_ID
表示可用统计信息的表的标识符；与 INNODB_TABLES.TABLE_ID 的值相同。An identifier representing the table for which statistics are available; the same value as INNODB_TABLES.TABLE_ID.
NAMENAME
表的名称；与 INNODB_TABLES.NAME 的值相同。The name of the table; the same value as INNODB_TABLES.NAME.
STATS_INITIALIZED
如果统计信息已收集，则值为 Initialized，如果未收集，则为 Uninitialized。The value is Initialized if the statistics are already collected, Uninitialized if not.
NUM_ROWS
表中的当前估计行数。每次 DML 操作后更新。如果有未提交的事务正在向表中插入或删除数据，则该值可能不精确。The current estimated number of rows in the table. Updated after each DML operation. The value could be imprecise if uncommitted transactions are inserting into or deleting from the table.
CLUST_INDEX_SIZE
磁盘上存储聚簇索引的页数，该索引按主键顺序保存 InnoDB 表数据。如果表尚未收集统计信息，则此值可能为 null。The number of pages on disk that store the clustered index, which holds the InnoDB table data in primary key order. This value might be null if no statistics are collected yet for the table.
OTHER_INDEX_SIZE
磁盘上存储表的所有二级索引的页数。如果表尚未收集统计信息，则此值可能为 null。The number of pages on disk that store all secondary indexes for the table. This value might be null if no statistics are collected yet for the table.
MODIFIED_COUNTER
由 DML 操作修改的行数，例如 INSERT、UPDATE、DELETE，以及来自外键的级联操作。每次表统计信息重新计算时，此列都会重置。The number of rows modified by DML operations, such as INSERT, UPDATE, DELETE, and also cascade operations from foreign keys. This column is reset each time table statistics are recalculated
AUTOINC
用于任何基于自增的操作的下一个发号。AUTOINC 值变化的速率取决于自增号被请求的次数以及每次请求授予的数量。The next number to be issued for any auto-increment-based operation. The rate at which the AUTOINC value changes depends on how many times auto-increment numbers have been requested and how many numbers are granted per request.
REF_COUNT
当此计数器达到零时，表元数据可以从表缓存中驱逐。When this counter reaches zero, the table metadata can be evicted from the table cache.

示例Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESTATS where TABLE_ID = 71\G
*************************** 1. row ***************************
         TABLE_ID: 71
             NAME: test/t1
STATS_INITIALIZED: Initialized
         NUM_ROWS: 1
 CLUST_INDEX_SIZE: 1
 OTHER_INDEX_SIZE: 0
 MODIFIED_COUNTER: 1
          AUTOINC: 0
        REF_COUNT: 1

注意事项Notes

此表主要用于专家级性能监控，或者在为 MySQL 开发与性能相关的扩展时使用。This table is useful primarily for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
你必须拥有 PROCESS 权限才能查询此表。You must have the PROCESS privilege to query this table.
使用 INFORMATION_SCHEMA COLUMNS 表或 SHOW COLUMNS 语句查看关于此表列的更多信息，包括数据类型和默认值。Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

首页上一级下一节