schema_table_statistics_with_buffer,x$schema_table_statistics_with_buffer

• 查询表统计信息，包括InnoDB缓冲池统计信息，默认情况下按照增删改查操作总的表I/O等待时间（执行时间，即也可以理解为是存在最多表I/O争用的表）降序排序，数据来源：table_io_waits_summary_by_table、x$ps_schema_table_statistics_io、sys.x$innodb_buffer_stats_by_table

  • 这些视图使用了帮助视图x$ps_schema_table_statistics_io

• schema_table_statistics_with_buffer和x$schema_table_statistics_with_buffer视图字段含义如下：

  • 表状态统计部分的字段含义与视图schema_table_statistics的字段含义相同，详见 2.3.29. schema_table_statistics,x$schema_table_statistics
  • innodb_buffer_allocated：当前已分配给表的总字节数
  • innodb_buffer_data：当前已分配给表的数据部分使用的字节总数
  • innodb_buffer_free：当前已分配给表的非数据部分使用的字节总数(即空闲页所在的字节数，计算公式：innodb_buffer_allocated - innodb_buffer_data)
  • innodb_buffer_pages：当前已分配给表的总页数
  • innodb_buffer_pages_hashed：当前已分配给表的自适应hash索引页总数
  • innodb_buffer_pages_old：当前已分配给表的旧页总数（位于LRU列表中的旧块子列表中的页数）
  • innodb_buffer_rows_cached：buffer pool中为表缓冲的总数据行数

• 视图定义语句

# schema_table_statistics_with_buffer
CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW schema_table_statistics_with_buffer (
  table_schema,  table_name,  rows_fetched,  fetch_latency,  rows_inserted,  insert_latency,  rows_updated,  update_latency,  rows_deleted,  delete_latency,  io_read_requests,
  io_read,  io_read_latency,  io_write_requests,  io_write,  io_write_latency,  io_misc_requests,  io_misc_latency,  innodb_buffer_allocated,  innodb_buffer_data,  innodb_buffer_free,
  innodb_buffer_pages,  innodb_buffer_pages_hashed,  innodb_buffer_pages_old,  innodb_buffer_rows_cached
) AS
SELECT pst.object_schema AS table_schema,
      pst.object_name AS table_name,
      pst.count_fetch AS rows_fetched,
      sys.format_time(pst.sum_timer_fetch) AS fetch_latency,
      pst.count_insert AS rows_inserted,
      sys.format_time(pst.sum_timer_insert) AS insert_latency,
      pst.count_update AS rows_updated,
      sys.format_time(pst.sum_timer_update) AS update_latency,
      pst.count_delete AS rows_deleted,
      sys.format_time(pst.sum_timer_delete) AS delete_latency,
      fsbi.count_read AS io_read_requests,
      sys.format_bytes(fsbi.sum_number_of_bytes_read) AS io_read,
      sys.format_time(fsbi.sum_timer_read) AS io_read_latency,
      fsbi.count_write AS io_write_requests,
      sys.format_bytes(fsbi.sum_number_of_bytes_write) AS io_write,
      sys.format_time(fsbi.sum_timer_write) AS io_write_latency,
      fsbi.count_misc AS io_misc_requests,
      sys.format_time(fsbi.sum_timer_misc) AS io_misc_latency,
      sys.format_bytes(ibp.allocated) AS innodb_buffer_allocated,
      sys.format_bytes(ibp.data) AS innodb_buffer_data,
      sys.format_bytes(ibp.allocated - ibp.data) AS innodb_buffer_free,
      ibp.pages AS innodb_buffer_pages,
      ibp.pages_hashed AS innodb_buffer_pages_hashed,
      ibp.pages_old AS innodb_buffer_pages_old,
      ibp.rows_cached AS innodb_buffer_rows_cached
  FROM performance_schema.table_io_waits_summary_by_table AS pst
  LEFT JOIN x$ps_schema_table_statistics_io AS fsbi
    ON pst.object_schema = fsbi.table_schema
  AND pst.object_name = fsbi.table_name
  LEFT JOIN sys.x$innodb_buffer_stats_by_table AS ibp
    ON pst.object_schema = ibp.object_schema
  AND pst.object_name = ibp.object_name
ORDER BY pst.sum_timer_wait DESC;

# x$schema_table_statistics_with_buffer
CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$schema_table_statistics_with_buffer (
  table_schema,  table_name,  rows_fetched,  fetch_latency,  rows_inserted,  insert_latency,  rows_updated,  update_latency,  rows_deleted,  delete_latency,  io_read_requests,  io_read,  io_read_latency,
  io_write_requests,  io_write,  io_write_latency,  io_misc_requests,  io_misc_latency,  innodb_buffer_allocated,  innodb_buffer_data,  innodb_buffer_free,  innodb_buffer_pages,  innodb_buffer_pages_hashed,
  innodb_buffer_pages_old,  innodb_buffer_rows_cached
) AS
SELECT pst.object_schema AS table_schema,
      pst.object_name AS table_name,
      pst.count_fetch AS rows_fetched,
      pst.sum_timer_fetch AS fetch_latency,
      pst.count_insert AS rows_inserted,
      pst.sum_timer_insert AS insert_latency,
      pst.count_update AS rows_updated,
      pst.sum_timer_update AS update_latency,
      pst.count_delete AS rows_deleted,
      pst.sum_timer_delete AS delete_latency,
      fsbi.count_read AS io_read_requests,
      fsbi.sum_number_of_bytes_read AS io_read,
      fsbi.sum_timer_read AS io_read_latency,
      fsbi.count_write AS io_write_requests,
      fsbi.sum_number_of_bytes_write AS io_write,
      fsbi.sum_timer_write AS io_write_latency,
      fsbi.count_misc AS io_misc_requests,
      fsbi.sum_timer_misc AS io_misc_latency,
      ibp.allocated AS innodb_buffer_allocated,
      ibp.data AS innodb_buffer_data,
      (ibp.allocated - ibp.data) AS innodb_buffer_free,
      ibp.pages AS innodb_buffer_pages,
      ibp.pages_hashed AS innodb_buffer_pages_hashed,
      ibp.pages_old AS innodb_buffer_pages_old,
      ibp.rows_cached AS innodb_buffer_rows_cached
  FROM performance_schema.table_io_waits_summary_by_table AS pst
  LEFT JOIN x$ps_schema_table_statistics_io AS fsbi
    ON pst.object_schema = fsbi.table_schema
  AND pst.object_name = fsbi.table_name
  LEFT JOIN sys.x$innodb_buffer_stats_by_table AS ibp
    ON pst.object_schema = ibp.object_schema
  AND pst.object_name = ibp.object_name
ORDER BY pst.sum_timer_wait DESC;

• 视图查询信息示例

admin@localhost : sys 12:36:57> select * from schema_table_statistics_with_buffer limit 1\G;
*************************** 1. row ***************************
              table_schema: xiaoboluo
                table_name: test
              rows_fetched: 1561
            fetch_latency: 2.08 m
            rows_inserted: 1159
            insert_latency: 865.33 ms
              rows_updated: 0
            update_latency: 0 ps
              rows_deleted: 0
            delete_latency: 0 ps
          io_read_requests: 48
                  io_read: 179.29 KiB
          io_read_latency: 15.02 ms
        io_write_requests: 10
                  io_write: 160.00 KiB
          io_write_latency: 76.24 us
          io_misc_requests: 47
          io_misc_latency: 9.47 ms
  innodb_buffer_allocated: 112.00 KiB
        innodb_buffer_data: 48.75 KiB
        innodb_buffer_free: 63.25 KiB
      innodb_buffer_pages: 7
innodb_buffer_pages_hashed: 0
  innodb_buffer_pages_old: 0
innodb_buffer_rows_cached: 1162
1 row in set (2.21 sec)

admin@localhost : sys 12:37:35> select * from x$schema_table_statistics_with_buffer limit 1\G;
*************************** 1. row ***************************
              table_schema: xiaoboluo
                table_name: test
              rows_fetched: 1561
            fetch_latency: 124846318302750
            rows_inserted: 1159
            insert_latency: 865325000625
              rows_updated: 0
            update_latency: 0
              rows_deleted: 0
            delete_latency: 0
          io_read_requests: 48
                  io_read: 183595
          io_read_latency: 15019373250
        io_write_requests: 10
                  io_write: 163840
          io_write_latency: 76237125
          io_misc_requests: 47
          io_misc_latency: 9465938250
  innodb_buffer_allocated: 114688
        innodb_buffer_data: 49917
        innodb_buffer_free: 64771
      innodb_buffer_pages: 7
innodb_buffer_pages_hashed: 0
  innodb_buffer_pages_old: 0
innodb_buffer_rows_cached: 1162
1 row in set (2.12 sec)

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-schema-table-statistics-with-buffer.html

---

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

下一篇： schema_tables_with_full_table_scans,x$schema_tables_with_full_table_scans视图