schema_table_statistics,x$schema_table_statistics - xiaoboluo768/mysql-system-schema GitHub Wiki
-
查看表统计信息信息，默认情况下按照增删改查操作总的表I/O等待时间（执行时间，即也可以理解为是存在最多表I/O争用的表）降序排序，数据来源：table_io_waits_summary_by_table、x$ps_schema_table_statistics_io
- 这些视图使用了一个帮助视图x$ps_schema_table_statistics_io，详见该视图解释小节
-
schema_table_statistics和x$schema_table_statistics视图字段含义如下：
- TABLE_SCHEMA：包含TABLE_NAME字段的表schema名称
- TABLE_NAME：表名
- total_latency：表的I/O事件的总等待时间(执行时间)，针对表增删改查操作
- rows_fetched：表读取操作的总数据行数，针对表查询操作
- fetch_latency：表select操作的I/O事件的总等待时间(执行时间)，针对表查询操作
- rows_inserted：表插入操作的总数据行数，针对表插入操作
- insert_latency：表insert操作的I/O事件的等待时间(执行时间)，针对表插入操作
- rows_updated：表更新操作的总数据行数，针对表更新操作
- update_latency：表更新操作的I/O事件的总等待时间(执行时间)，针对表更新操作
- rows_deleted：表删除操作的总数据行数，针对表删除操作
- delete_latency：表删除操作的I/O事件的总等待时间(执行时间)，针对表删除操作
- io_read_requests：表读取操作总请求次数，针对表.ibd和.frm文件的读I/O操作
- io_read：表读操作相关的所有文件读取操作的总字节数，针对表.ibd和.frm文件的读I/O操作
- io_read_latency：表读操作相关的所有文件读取操作的总等待时间(执行时间)，针对表.ibd和.frm文件的读I/O操作
- io_write_requests：表写操作的总请求次数，针对表.ibd和.frm文件的写I/O操作
- io_write：表写操作相关的所有文件写操作的总字节数，针对表.ibd和.frm文件的写I/O操作
- io_write_latency：表写操作相关的所有文件写操作的总等待时间(执行时间)，针对表.ibd和.frm文件的写I/O操作
- io_misc_requests：表其他各种混杂操作相关的所有文件的I/O请求总次数，针对表.ibd和.frm文件的其他混杂I/O操作
- io_misc_latency：表其他各种混杂操作相关的所有文件的I/O请求的总等待时间(执行时间)，针对表.ibd和.frm文件的其他混杂I/O操作
-
视图定义语句
# schema_table_statistics
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW schema_table_statistics (
table_schema, table_name, total_latency, 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
) AS
SELECT pst.object_schema AS table_schema,
pst.object_name AS table_name,
sys.format_time(pst.sum_timer_wait) AS total_latency,
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
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
ORDER BY pst.sum_timer_wait DESC;
# x$schema_table_statistics
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW x$schema_table_statistics (
table_schema, table_name, total_latency, 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
) AS
SELECT pst.object_schema AS table_schema,
pst.object_name AS table_name,
pst.sum_timer_wait AS total_latency,
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
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
ORDER BY pst.sum_timer_wait DESC;
- 视图查询信息示例
admin@localhost : sys 11:52:25> select * from schema_table_statistics limit 1\G;
*************************** 1. row ***************************
table_schema: xiaoboluo
table_name: test
total_latency: 2.10 m
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: 43
io_read: 178.86 KiB
io_read_latency: 15.00 ms
io_write_requests: 10
io_write: 160.00 KiB
io_write_latency: 76.24 us
io_misc_requests: 42
io_misc_latency: 9.38 ms
1 row in set (0.03 sec)
ERROR:
No query specified
admin@localhost : sys 11:52:28> select * from x$schema_table_statistics limit 1\G;
*************************** 1. row ***************************
table_schema: xiaoboluo
table_name: test
total_latency: 125711643303375
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: 43
io_read: 183148
io_read_latency: 15001512375
io_write_requests: 10
io_write: 163840
io_write_latency: 76237125
io_misc_requests: 42
io_misc_latency: 9384933000
1 row in set (0.02 sec)
上一篇： schema_table_lock_waits,x$schema_table_lock_waits视图 |
下一篇： schema_table_statistics_with_buffer,x$schema_table_statistics_with_buffer视图