x$ps_schema_table_statistics_io

• 帮助视图(辅助试图)，用于帮助schema_table_statistics,x$schema_table_statistics、schema_table_statistics_with_buffer,x$schema_table_statistics_with_buffer表统计视图生成表统计信息，数据来源：file_summary_by_instance

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

  • table_schema：包含table_name的schema名称
  • table_name：表名
  • count_read：对应表的文件读I/O事件发生的总次数
  • sum_number_of_bytes_read：对应表的文件读I/O事件的总字节数
  • sum_timer_read：对应表的文件读I/O事件的总延迟时间(执行时间)
  • count_write：对应表的文件写I/O事件发生的总次数
  • sum_number_of_bytes_write：对应表的文件写I/O事件的总字节数
  • sum_timer_write：对应表的文件写I/O事件的总延迟时间(执行时间)
  • count_misc：对应表的文件混杂I/O事件发生的总次数
  • sum_timer_misc：对应表的文件混杂I/O事件的总延迟时间(执行时间)

• 视图定义语句

CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$ps_schema_table_statistics_io (
  table_schema,  table_name,  count_read,  sum_number_of_bytes_read,  sum_timer_read,  count_write,  sum_number_of_bytes_write,  sum_timer_write,  count_misc,  sum_timer_misc
) AS
SELECT extract_schema_from_file_name(file_name) AS table_schema,
      extract_table_from_file_name(file_name) AS table_name,
      SUM(count_read) AS count_read,
      SUM(sum_number_of_bytes_read) AS sum_number_of_bytes_read,
      SUM(sum_timer_read) AS sum_timer_read,
      SUM(count_write) AS count_write,
      SUM(sum_number_of_bytes_write) AS sum_number_of_bytes_write,
      SUM(sum_timer_write) AS sum_timer_write,
      SUM(count_misc) AS count_misc,
      SUM(sum_timer_misc) AS sum_timer_misc
  FROM performance_schema.file_summary_by_instance
GROUP BY table_schema, table_name;

• 视图查询信息示例

admin@localhost : sys 01:00:42> select * from x$ps_schema_table_statistics_io limit 3;
+-----------------+-----------------+------------+--------------------------+----------------+-------------+---------------------------+-----------------+------------+----------------+
| table_schema    | table_name      | count_read | sum_number_of_bytes_read | sum_timer_read | count_write | sum_number_of_bytes_write | sum_timer_write | count_misc | sum_timer_misc |
+-----------------+-----------------+------------+--------------------------+----------------+-------------+---------------------------+-----------------+------------+----------------+
| @5c0f@841d@535c | @5c0f@841d@535c |        11 |                  115897 |    40409405625 |          0 |                        0 |              0 |        11 |    6395506125 |
| binlog          | mysql-bin      |        279 |                  411513 |    4898542125 |        459 |                    408800 |      9443458500 |        455 |  2049668827875 |
| charsets        | Index          |          1 |                    18710 |    16713311625 |          0 |                        0 |              0 |          2 |      83737125 |
+-----------------+-----------------+------------+--------------------------+----------------+-------------+---------------------------+-----------------+------------+----------------+
3 rows in set (0.01 sec)

• 参考链接：辅助视图无参考链接

---

上一篇： x$ps_digest_avg_latency_distribution视图 |

下一篇： x$schema_flattened_keys视图