MySQL :: MySQL 8.4 参考手册 :: 28.4.2 INFORMATION_SCHEMA INNODB_BUFFER

MySQL 8.4 参考手册 · 中文镜像

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

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

MySQL 8.4 参考手册 / 28 个 INFORMATION_SCHEMA 表 / 28.4 INFORMATION_SCHEMA InnoDB 表 / 28.4.2 INFORMATION_SCHEMA INNODB_BUFFER_PAGE 表

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

28.4.2 INFORMATION_SCHEMA INNODB_BUFFER_PAGE 表28.4.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table

INNODB_BUFFER_PAGE 表提供有关 InnoDB 缓冲池中每个页面的信息。The INNODB_BUFFER_PAGE table provides information about each page in the InnoDB buffer pool.

有关相关用法信息和示例，请参见第 17.15.5 节，“InnoDB INFORMATION_SCHEMA 缓冲池表”。For related usage information and examples, see Section 17.15.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.

Warning

查询 INNODB_BUFFER_PAGE 表可能会影响性能。除非您了解性能影响并确定其可接受，否则不要在生产系统上查询此表。为了避免影响生产系统的性能，请在测试实例上重现您要调查的问题并查询缓冲池统计信息。Querying the INNODB_BUFFER_PAGE table can affect performance. Do not query this table on a production system unless you are aware of the performance impact and have determined it to be acceptable. To avoid impacting performance on a production system, reproduce the issue you want to investigate and query buffer pool statistics on a test instance.

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

POOL_ID
缓冲池 ID。这是用于区分多个缓冲池实例的标识符。The buffer pool ID. This is an identifier to distinguish between multiple buffer pool instances.
BLOCK_ID
缓冲池块 ID。The buffer pool block ID.
SPACE
表空间 ID；与 INNODB_TABLES.SPACE 的值相同。The tablespace ID; the same value as INNODB_TABLES.SPACE.
PAGE_NUMBER
页码。The page number.

PAGE_TYPE

页类型。下表显示允许的值。The page type. The following table shows the permitted values.

表 28.4 INNODB_BUFFER_PAGE.PAGE_TYPE 值Table 28.4 INNODB_BUFFER_PAGE.PAGE_TYPE Values

页类型Page Type	说明Description
`ALLOCATED`	新分配的页面Freshly allocated page
`BLOB`	未压缩的 BLOB 页面Uncompressed BLOB page
`COMPRESSED_BLOB2`	后续压缩 BLOB 页面Subsequent comp BLOB page
`COMPRESSED_BLOB`	第一个压缩 BLOB 页面First compressed BLOB page
`ENCRYPTED_RTREE`	加密 R 树Encrypted R-tree
`EXTENT_DESCRIPTOR`	区段描述符页面Extent descriptor page
`FILE_SPACE_HEADER`	文件空间头File space header
`FIL_PAGE_TYPE_UNUSED`	未使用Unused
`IBUF_BITMAP`	插入缓冲区位图Insert buffer bitmap
`IBUF_FREE_LIST`	插入缓冲区空闲列表Insert buffer free list
`IBUF_INDEX`	插入缓冲区索引Insert buffer index
`INDEX`	B 树节点B-tree node
`INODE`	索引节点Index node
`LOB_DATA`	未压缩的 LOB 数据Uncompressed LOB data
`LOB_FIRST`	未压缩 LOB 的第一页First page of uncompressed LOB
`LOB_INDEX`	未压缩 LOB 索引Uncompressed LOB index
`PAGE_IO_COMPRESSED`	压缩页Compressed page
`PAGE_IO_COMPRESSED_ENCRYPTED`	已压缩和加密的页面Compressed and encrypted page
`PAGE_IO_ENCRYPTED`	加密页面Encrypted page
`RSEG_ARRAY`	回滚段数组Rollback segment array
`RTREE_INDEX`	R 树索引R-tree index
`SDI_BLOB`	未压缩的 SDI BLOBUncompressed SDI BLOB
`SDI_COMPRESSED_BLOB`	已压缩的 SDI BLOBCompressed SDI BLOB
`SDI_INDEX`	SDI 索引SDI index
`SYSTEMSYSTEM`	系统页面System page
`TRX_SYSTEM`	事务系统数据Transaction system data
`UNDO_LOG`	回滚日志页Undo log page
`UNKNOWNUNKNOWN`	未知Unknown
`ZLOB_DATA`	压缩的 LOB 数据Compressed LOB data
`ZLOB_FIRST`	压缩 LOB 的第一页First page of compressed LOB
`ZLOB_FRAG`	压缩 LOB 片段Compressed LOB fragment
`ZLOB_FRAG_ENTRY`	压缩 LOB 片段索引Compressed LOB fragment index
`ZLOB_INDEX`	压缩 LOB 索引Compressed LOB index

FLUSH_TYPE
刷新类型。The flush type.
FIX_COUNT
在缓冲池中使用该块的线程数。当为零时，该块有资格被淘汰。The number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted.
IS_HASHED
该页是否已建立哈希索引。Whether a hash index has been built on this page.
NEWEST_MODIFICATION
最新修改的日志序列号。The Log Sequence Number of the youngest modification.
OLDEST_MODIFICATION
最早修改的日志序列号。The Log Sequence Number of the oldest modification.
ACCESS_TIME
用于判断页面首次访问时间的抽象值。An abstract number used to judge the first access time of the page.
TABLE_NAMETABLE_NAME
页面所属表的名称。此列仅适用于 PAGE_TYPE 值为 INDEX 的页面。如果服务器尚未访问该表，则该列为 NULL。The name of the table the page belongs to. This column is applicable only to pages with a PAGE_TYPE value of INDEX. The column is NULL if the server has not yet accessed the table.
INDEX_NAME
页面所属索引的名称。这可以是聚集索引或二级索引的名称。此列仅适用于 PAGE_TYPE 值为 INDEX 的页面。The name of the index the page belongs to. This can be the name of a clustered index or a secondary index. This column is applicable only to pages with a PAGE_TYPE value of INDEX.
NUMBER_RECORDS
页面内记录的数量。The number of records within the page.
DATA_SIZE
记录大小的总和。此列仅适用于 PAGE_TYPE 值为 INDEX 的页面。The sum of the sizes of the records. This column is applicable only to pages with a PAGE_TYPE value of INDEX.
COMPRESSED_SIZE
压缩页面大小。对于未压缩的页面，值为 NULL。The compressed page size. NULL for pages that are not compressed.

PAGE_STATE

页面状态。下表显示允许的值。The page state. The following table shows the permitted values.

表 28.5 INNODB_BUFFER_PAGE.PAGE_STATE 值Table 28.5 INNODB_BUFFER_PAGE.PAGE_STATE Values

页面状态Page State	说明Description
`FILE_PAGE`	一个缓冲文件页面A buffered file page
`MEMORYMEMORY`	包含主存对象Contains a main memory object
`NOT_USED`	在空闲列表中In the free list
`NULL`	干净的压缩页面、在刷新列表中的压缩页面、用作缓冲池监视哨的页面Clean compressed pages, compressed pages in the flush list, pages used as buffer pool watch sentinels
`READY_FOR_USE`	一个空闲页面A free page
`REMOVE_HASH`	在放入空闲列表之前应移除哈希索引Hash index should be removed before placing in the free list

IO_FIX
此页面是否有任何待处理的 I/O：IO_NONE = 无待处理 I/O，IO_READ = 待读取，IO_WRITE = 待写入，IO_PIN = 不允许迁移和从刷新中移除。Whether any I/O is pending for this page: IO_NONE = no pending I/O, IO_READ = read pending, IO_WRITE = write pending, IO_PIN = relocation and removal from the flush not permitted.
IS_OLD
该块是否属于 LRU 列表的旧块子列表。Whether the block is in the sublist of old blocks in the LRU list.
FREE_PAGE_CLOCK
当该块最后一次被放置在 LRU 列表头部时 freed_page_clock 计数器的值。freed_page_clock 计数器用于跟踪从 LRU 列表末端移除的块数。The value of the freed_page_clock counter when the block was the last placed at the head of the LRU list. The freed_page_clock counter tracks the number of blocks removed from the end of the LRU list.
IS_STALE
此页面是否已过期。Whether the page is stale.

示例Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE LIMIT 1\G
*************************** 1. row ***************************
            POOL_ID: 0
           BLOCK_ID: 0
              SPACE: 97
        PAGE_NUMBER: 2473
          PAGE_TYPE: INDEX
         FLUSH_TYPE: 1
          FIX_COUNT: 0
          IS_HASHED: YES
NEWEST_MODIFICATION: 733855581
OLDEST_MODIFICATION: 0
        ACCESS_TIME: 3378385672
         TABLE_NAME: `employees`.`salaries`
         INDEX_NAME: PRIMARY
     NUMBER_RECORDS: 468
          DATA_SIZE: 14976
    COMPRESSED_SIZE: 0
         PAGE_STATE: FILE_PAGE
             IO_FIX: IO_NONE
             IS_OLD: YES
    FREE_PAGE_CLOCK: 66
           IS_STALE: NO

说明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.
当表、表行、分区或索引被删除时，相关的页面会保留在缓冲池中，直到需要空间存放其他数据。INNODB_BUFFER_PAGE 表会报告这些页面的信息，直到它们从缓冲池中被移除。有关 InnoDB 如何管理缓冲池数据的更多信息，请参见第 17.5.1 节，“缓冲池”。When tables, table rows, partitions, or indexes are deleted, associated pages remain in the buffer pool until space is required for other data. The INNODB_BUFFER_PAGE table reports information about these pages until they are evicted from the buffer pool. For more information about how the InnoDB manages buffer pool data, see Section 17.5.1, “Buffer Pool”.

首页上一级下一节