MySQL :: MySQL 8.4 参考手册 :: 28.4.3 INFORMATION_SCHEMA INNODB_BUFFER_PAGE

MySQL 8.4 参考手册 · 中文镜像

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

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

MySQL 8.4 参考手册 / 28 个 INFORMATION_SCHEMA 表 / 28.4 INFORMATION_SCHEMA InnoDB 表 / 28.4.3 INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU 表

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

28.4.3 INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU 表28.4.3 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table

INNODB_BUFFER_PAGE_LRU 表提供了关于 InnoDB 缓冲池中页面的信息；特别是，它显示了这些页面在 LRU 列表中的排列顺序，该列表决定了当缓冲池满时哪些页面将被驱逐出缓冲池。The INNODB_BUFFER_PAGE_LRU table provides information about the pages in the InnoDB buffer pool; in particular, how they are ordered in the LRU list that determines which pages to evict from the buffer pool when it becomes full.

INNODB_BUFFER_PAGE_LRU 表与 INNODB_BUFFER_PAGE 表的列相同，但有一些例外。它有 LRU_POSITION 和 COMPRESSED 列，而不是 BLOCK_ID 和 PAGE_STATE 列，并且不包含 IS_STALE 列。The INNODB_BUFFER_PAGE_LRU table has the same columns as the INNODB_BUFFER_PAGE table with a few exceptions. It has LRU_POSITION and COMPRESSED columns instead of BLOCK_ID and PAGE_STATE columns, and it does not include and IS_STALE column.

有关相关用法信息和示例，请参见第 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_LRU 表可能会影响性能。除非您了解性能影响并确定其可接受，否则不要在生产系统上查询此表。为了避免对生产系统性能造成影响，请在测试实例上重现您要调查的问题，并查询缓冲池统计信息。Querying the INNODB_BUFFER_PAGE_LRU 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_LRU 表具有以下列：The INNODB_BUFFER_PAGE_LRU table has these columns:

POOL_ID
缓冲池 ID。这是用于区分多个缓冲池实例的标识符。The buffer pool ID. This is an identifier to distinguish between multiple buffer pool instances.
LRU_POSITION
页面在 LRU 列表中的位置。The position of the page in the LRU list.
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.6 INNODB_BUFFER_PAGE_LRU.PAGE_TYPE 值Table 28.6 INNODB_BUFFER_PAGE_LRU.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.
COMPRESSEDCOMPRESSED
页面是否被压缩。Whether the page is compressed.
IO_FIX
此页面是否有任何 I/O 挂起：IO_NONE = 无挂起的 I/O，IO_READ = 挂起读取，IO_WRITE = 挂起写入。Whether any I/O is pending for this page: IO_NONE = no pending I/O, IO_READ = read pending, IO_WRITE = write pending.
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.

示例Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU LIMIT 1\G
*************************** 1. row ***************************
            POOL_ID: 0
       LRU_POSITION: 0
              SPACE: 97
        PAGE_NUMBER: 1984
          PAGE_TYPE: INDEX
         FLUSH_TYPE: 1
          FIX_COUNT: 0
          IS_HASHED: YES
NEWEST_MODIFICATION: 719490396
OLDEST_MODIFICATION: 0
        ACCESS_TIME: 3378383796
         TABLE_NAME: `employees`.`salaries`
         INDEX_NAME: PRIMARY
     NUMBER_RECORDS: 468
          DATA_SIZE: 14976
    COMPRESSED_SIZE: 0
         COMPRESSED: NO
             IO_FIX: IO_NONE
             IS_OLD: YES
    FREE_PAGE_CLOCK: 0

注意事项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.
查询此表可能需要 MySQL 分配一大块连续内存，其大小超过缓冲池中活动页面数量乘以 64 字节。对于多 GB 缓冲池的系统，这种分配可能会导致内存不足错误。Querying this table can require MySQL to allocate a large block of contiguous memory, more than 64 bytes times the number of active pages in the buffer pool. This allocation could potentially cause an out-of-memory error, especially for systems with multi-gigabyte buffer pools.
查询此表需要 MySQL 在遍历 LRU 列表时锁定表示缓冲池的数据结构，这可能会降低并发性，尤其是对多 GB 缓冲池的系统。Querying this table requires MySQL to lock the data structure representing the buffer pool while traversing the LRU list, which can reduce concurrency, especially for systems with multi-gigabyte buffer pools.
当表、表行、分区或索引被删除时，相关的页面会保留在缓冲池中，直到需要空间存放其他数据。INNODB_BUFFER_PAGE_LRU 表会报告这些页面的信息，直到它们被缓冲池驱逐。有关 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_LRU 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”.

首页上一级下一节