上QQ阅读APP看本书，新人免费读10天
设备和账号都新为新人
9.4 查看InnoDB缓冲池中的热点数据有哪些
使用innodb_buffer_stats_by_schema视图可以按照schema分组查询InnoDB缓冲池的统计信息，默认按照已分配的buffer size（缓冲区大小）降序排列（allocated字段）。数据来源：information_schema.innodb_buffer_page。
下面是使用innodb_buffer_stats_by_schema视图查询的结果集。
mysql> select ＊ from innodb_buffer_stats_by_schema; +--------------------+------------+-----------+-------+-------------+----------+------------+ | object_schema | allocated | data | pages |pages_hashed | pages_old | rows_cached| +--------------------+------------+-----------+-------+-------------+----------+------------+ | InnoDB System | 23.73 MiB | 21.76 MiB | 1519 | 0 | 24 | 21474 | | mysql | 240.00 KiB | 14.57 KiB | 15 | 0 | 15 | 179 | | xiaoboluo | 128.00 KiB | 38.93 KiB | 8 | 0 | 5 | 982 | | sys | 16.00 KiB | 354 bytes | 1 | 0 | 1 | 6 | | 小萝卜 | 16.00 KiB | 135 bytes | 1 | 0 | 1 | 3 | +--------------------+------------+-----------+-------+-------------+----------+------------+ 5 rows in set（0.43 sec）
下面贴出视图查询语句文本。
SELECT IF（LOCATE（'.', ibp.table_name）=0, 'InnoDB System', REPLACE（SUBSTRING_INDEX（ibp. table_name, '.', 1）, '`', ''））AS object_schema,
sys.format_bytes（SUM（IF（ibp.compressed_size = 0, 16384, compressed_size）））AS allocated,
sys.format_bytes（SUM（ibp.data_size））AS data,
COUNT（ibp.page_number）AS pages,
COUNT（IF（ibp.is_hashed = 'YES', 1, NULL））AS pages_hashed,
COUNT（IF（ibp.is_old = 'YES', 1, NULL））AS pages_old,
ROUND（SUM（ibp.number_records）/COUNT（DISTINCT ibp.index_name））AS rows_cached
FROM information_schema.innodb_buffer_page ibp
WHERE table_name IS NOT NULL
GROUP BY object_schema
ORDER BY SUM（IF（ibp.compressed_size = 0, 16384, compressed_size））DESC;