innodb_buffer_stats_by_table,x$innodb_buffer_stats_by_table

• 按照schema和表分组的 InnoDB缓冲区统计信息，与innodb_buffer_stats_by_schema视图类似，但是按照schema name和table name分组。数据来源：information_schema.innodb_buffer_page

• innodb_buffer_stats_by_table,x$innodb_buffer_stats_by_table视图字段含义如下：

  • object_name：表级别对象名称，通常是表名
  • 其他字段含义与innodb_buffer_stats_by_schema视图字段含义相同，详见 2.3.7. innodb_buffer_stats_by_schema,x$innodb_buffer_stats_by_schema。但这些字段是按照object_name表级别统计的

• 视图定义语句

# innodb_buffer_stats_by_table
CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW innodb_buffer_stats_by_table (
  object_schema,  object_name,  allocated,  data,  pages,  pages_hashed,  pages_old,  rows_cached
) AS
SELECT IF(LOCATE('.', ibp.table_name) = 0, 'InnoDB System', REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', 1), '`', '')) AS object_schema,
      REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', -1), '`', '') AS object_name,
      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, object_name
ORDER BY SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) DESC;

# x$innodb_buffer_stats_by_table
CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$innodb_buffer_stats_by_table (
  object_schema,  object_name,  allocated,  data,  pages,  pages_hashed,  pages_old,  rows_cached
) AS
SELECT IF(LOCATE('.', ibp.table_name) = 0, 'InnoDB System', REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', 1), '`', '')) AS object_schema,
      REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', -1), '`', '') AS object_name,
      SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) AS allocated,
      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(IFNULL(SUM(ibp.number_records)/NULLIF(COUNT(DISTINCT ibp.index_name), 0), 0)) AS rows_cached
  FROM information_schema.innodb_buffer_page ibp
WHERE table_name IS NOT NULL
GROUP BY object_schema, object_name
ORDER BY SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) DESC;

• 视图查询信息示例

root@localhost : sys 12:41:25> select * from innodb_buffer_stats_by_table limit 3;
+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+
| object_schema | object_name | allocated | data      | pages | pages_hashed | pages_old | rows_cached |
+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+
| InnoDB System | SYS_TABLES  | 11.58 MiB | 10.63 MiB |  741 |            0 |        3 |      36692 |
| luoxiaobo    | t_luoxiaobo | 80.00 KiB | 29.21 KiB |    5 |            0 |        0 |        1658 |
| InnoDB System | SYS_COLUMNS | 48.00 KiB | 16.03 KiB |    3 |            0 |        3 |        239 |
+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+
3 rows in set (0.12 sec)

root@localhost : sys 12:41:41> select * from x$innodb_buffer_stats_by_table limit 3;
+---------------+-------------+-----------+----------+-------+--------------+-----------+-------------+
| object_schema | object_name | allocated | data    | pages | pages_hashed | pages_old | rows_cached |
+---------------+-------------+-----------+----------+-------+--------------+-----------+-------------+
| InnoDB System | SYS_TABLES  |  12140544 | 11154757 |  741 |            0 |        3 |      36702 |
| luoxiaobo    | t_luoxiaobo |    81920 |    29913 |    5 |            0 |        0 |        1658 |
| InnoDB System | SYS_COLUMNS |    49152 |    16412 |    3 |            0 |        3 |        239 |
+---------------+-------------+-----------+----------+-------+--------------+-----------+-------------+
3 rows in set (0.12 sec)

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-innodb-buffer-stats-by-table.html

---

上一篇： innodb_buffer_stats_by_schema,x$innodb_buffer_stats_by_schema视图 |

下一篇： innodb_lock_waits,x$innodb_lock_waits视图