mysql - sys.innodb_buffer_stats_by_table...中的pages_hashed是什么？

Question

有人可以解释一下sys.innodb_buffer_stats_by_table中的pages_hashed是什么吗？

mysql> select * from sys.innodb_buffer_stats_by_table;
+---------------+---------------------------+------------+-----------+-------+--------------+-----------+-------------+
| object_schema | object_name               | allocated  | data      | pages | pages_hashed | pages_old | rows_cached |
+---------------+---------------------------+------------+-----------+-------+--------------+-----------+-------------+
| sbtest        | sbtest1                   | 107.25 MiB | 98.86 MiB |  6864 |            0 |      2597 |      721229 |
| InnoDB System | SYS_TABLES                | 3.36 MiB   | 3.03 MiB  |   215 |            0 |         0 |       18889 |
| mysql         | help_keyword              | 112.00 KiB | 73.24 KiB |     7 |            0 |         0 |         472 |
| mysql         | help_topic                | 96.00 KiB  | 53.27 KiB |     6 |            0 |         0 |         206 |
| mysql         | help_category             | 32.00 KiB  | 6.29 KiB  |     2 |            0 |         0 |          40 |
| mysql         | engine_cost               | 16.00 KiB  | 112 bytes |     1 |            0 |         0 |           2 |
| mysql         | gtid_executed             | 16.00 KiB  | 0 bytes   |     1 |            0 |         0 |           0 |

Answer 1

要了解此表显示的内容，我们必须了解“sys”以及它的表是什么。

“sys”架构是什么？

The MySQL 'sys' schema is a collection of views, functions, and procedures that aggregate MySQL internal data into meaningful tables that can be queried against.

特定表 (sys.innodb_buffer_stats_by_table) 是一个 View 。

以下是创建“Pages_hashed ”列的 SQL:

COUNT(IF(ibp.is_hashed = 'YES', 1, NULL)) AS pages_hashed

上面代码中提到的ibp实际上是information_schema.INNODB_BUFFER_PAGE表的缩写。

什么是“pages_hashed”列？

The "pages_hashed" column shows how many pages are in the INNODB_BUFFER_PAGE table that have the "IS_HASHED" column marked "YES" for the specific table

示例:如果表“foo”有 100 个页面，其中 25 个页面已“散列”，则 information_schema 内将有 25 行。 INNODB_BUFFER_PAGE 与 TABLE_NAME='foo' 和 IS_HASHED='YES'

什么是哈希页面？

根据MySQL Reference Manual ，哈希页面由以下因素确定:

Whether a hash index has been built on this page

要了解有关哈希索引的更多信息，check out the MySQL Dev Manual