Documentation Home
MySQL 5.6 参考手册
MySQL 8.0 参考手册  / 第 15 章 InnoDB 存储引擎  / 15.15 InnoDB INFORMATION_SCHEMA 表  /  14.15.5 InnoDB INFORMATION_SCHEMA 缓冲池表

14.15.5 InnoDB INFORMATION_SCHEMA 缓冲池表

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_LRU                        |
| INNODB_BUFFER_PAGE                            |
| INNODB_BUFFER_POOL_STATS                      |
+-----------------------------------------------+

表格概览

警告

查询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(*) |
+----------+
|     1320 |
+----------+

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

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;
+--------------+-------------+------------------------+
| system_pages | total_pages | system_page_percentage |
+--------------+-------------+------------------------+
|         1320 |        8192 |                     16 |
+--------------+-------------+------------------------+

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);
+-------------------+
| PAGE_TYPE         |
+-------------------+
| SYSTEM            |
| IBUF_BITMAP       |
| UNDO_LOG          |
| UNKNOWN           |
| FILE_SPACE_HEADER |
| INODE             |
| ALLOCATED         |
| TRX_SYSTEM        |
+-------------------+

示例 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;
+----------+
| COUNT(*) |
+----------+
|     6872 |
+----------+

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

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;
+------------+-------------+----------------------+
| user_pages | total_pages | user_page_percentage |
+------------+-------------+----------------------+
|       6872 |        8192 |                   84 |
+------------+-------------+----------------------+

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

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使用索引名称查询列。emp_no例如，以下查询返回表中定义 的索引的页数和页的总数据大小 employees.salaries： 

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`';
+------------+-------+-----------------+
| INDEX_NAME | Pages | Total Data (MB) |
+------------+-------+-----------------+
| emp_no     |  1756 |              27 |
+------------+-------+-----------------+

此查询返回表上定义的所有索引的页数和页的总数据大小 employees.salaries： 

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 TABLE_NAME = '`employees`.`salaries`'
       GROUP BY INDEX_NAME;
+------------+-------+-----------------+
| INDEX_NAME | Pages | Total Data (MB) |
+------------+-------+-----------------+
| emp_no     |  1756 |              27 |
| PRIMARY    |  4838 |              76 |
+------------+-------+-----------------+

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

该INNODB_BUFFER_PAGE_LRU表包含有关 InnoDB缓冲池中页面的信息，特别是它们的排序方式决定了当缓冲池变满时要从缓冲池中逐出哪些页面。此页面的定义与 for 相同INNODB_BUFFER_PAGE，只是此表有一个LRU_POSITION列而不是一个BLOCK_ID列。

该查询统计表的页面在 LRU 列表中特定位置的位置数 employees.employees。 

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

示例 14.10 查询 INNODB_BUFFER_POOL_STATS 表

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

mysql> SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS \G
*************************** 1. row ***************************
                         POOL_ID: 0
                       POOL_SIZE: 8192
                    FREE_BUFFERS: 1024
                  DATABASE_PAGES: 7029
              OLD_DATABASE_PAGES: 2574
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 0
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 173
            PAGES_NOT_MADE_YOUNG: 3721891
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 1075
            NUMBER_PAGES_CREATED: 12594
            NUMBER_PAGES_WRITTEN: 13525
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 27873240
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 576
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0

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

有关 SHOW ENGINE INNODB STATUS输出的更多信息，请参阅 第 14.17.3 节，“InnoDB 标准监视器和锁定监视器输出”。 

mysql> SHOW ENGINE INNODB STATUS \G
...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 99725
Buffer pool size   8192
Free buffers       1024
Database pages     7029
Old database pages 2574
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 173, not young 3721891
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1075, created 12594, written 13525
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7029, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
...

有关状态变量的描述，请参阅 第 5.1.9 节，“服务器状态变量”。 

mysql> SHOW STATUS LIKE 'Innodb_buffer%';
+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
| Innodb_buffer_pool_dump_status        | not started |
| Innodb_buffer_pool_load_status        | not started |
| Innodb_buffer_pool_pages_data         | 7029        |
| Innodb_buffer_pool_bytes_data         | 115163136   |
| Innodb_buffer_pool_pages_dirty        | 0           |
| Innodb_buffer_pool_bytes_dirty        | 0           |
| Innodb_buffer_pool_pages_flushed      | 13525       |
| Innodb_buffer_pool_pages_free         | 1024        |
| Innodb_buffer_pool_pages_misc         | 139         |
| Innodb_buffer_pool_pages_total        | 8192        |
| Innodb_buffer_pool_read_ahead_rnd     | 0           |
| Innodb_buffer_pool_read_ahead         | 576         |
| Innodb_buffer_pool_read_ahead_evicted | 0           |
| Innodb_buffer_pool_read_requests      | 27873240    |
| Innodb_buffer_pool_reads              | 500         |
| Innodb_buffer_pool_wait_free          | 0           |
| Innodb_buffer_pool_write_requests     | 11966441    |
+---------------------------------------+-------------+