MySQL sys库视图记录

sys schema视图摘要：

主机相关信息：以host_summary开头的视图，主要汇总了IO延迟的信息，从主机、文件事件类型、语句类型等角度展示文件IO的信息。

innodb相关信息：以innodb开头的视图，汇总了innodb buffer page信息和事务等待 innodb 锁信息。

IO使用情况：以IO开头的视图，总结了IO使用的信息，包括等待IO的情况，IO使用量情况。

内存使用情况：以memory开头的视图，从主机、线程、用户、事件角度展示内存使用情况。

连接与会话信息：其中processlist和session相关的视图，总结了会话相关信息。

表相关信息：以schema_table开头的的视图，从全表扫描、innodb缓冲池等方面展示了表统计信息。

索引信息：其中包含了index的视图，统计了索引使用情况，以及重复索引和未使用的索引情况。

语句相关信息：以statement开头的视图，统计规范化的语句使用情况，包括错误数、警告数、执行全表扫描的、使用临时表的、执行排序等信息。

用户的相关信息：以user开头的视图，统计了用户使用的文件IO、执行的语句统计信息等。

等待事件相关信息：以wait开头的视图，从主机和事件角度展示等待类事件的延迟情况。

查看表的访问量

select table_schema,table_name,sum(io_requests+io_write_requests) from schema_table_statistics ;

 

select table_schema,table_name,io_read_requests+io_write_requests as io_total from schema_table_statistics ;

 

冗余索引

select * from sys.schema_redundant_indexes;

长期未使用的索引

select * from sys.schema_unused_indexes;

表自增ID监控

随着业务表数据的增长，可能某张表自增量快要超过阈值了，继而导致业务出现问题，这时需要DBA清楚地直到每个表的增量列情况，从5.7开始可以用sys库下的schema_auto_increment_columns视图，查到每个表的自增量使用情况。在该视图中，详细的展示了表的自增量列名、数据类型、当前使用量、最大值及使用率情况。

select * from schema_auto_increment_columns \G;

监控全表扫描的SQL语句

可以帮助我们很快的定位到哪些SQL由于未使用索引而导致全表扫描，这个视图里帮我们记录了SQL执行次数，未使用索引的次数。

select * from sys.statements_with_full_table_scans where db = 'db_name'\G;

查看实例消耗的磁盘I\O

有时候业务方经常抱怨数据库慢了，这时可以通过avg_io from io_global_by_file_by_bytes视图帮助我们查看是数据库的哪些文件消耗了大量磁盘I\O，排查问题的时候会简单很多。针对性的对某些表、某些库进行优化，提高性能

select file,avg_read+avg_write as avg_io from io_global_by_file_by_bytes order by avg_io desc limit 10;

 查看没有主键的表

select table_schema,table_name from information_schema.tables
where (table_schema,table_name) not in(
    select distinct table_schema,table_name from information_schema.columns where COLUMN_KEY='PRI'   
)
and table_schema not in (
    'sys','mysql','information_schema','performance_schema'
);

MySQL从5.7.7版本开始提供sys Schema，其中包含了大量表和视图，建议尽量不要在线上大量查询sys或performance_schema，因为查询这些信息时，MySQL会消耗大量资源去手机相关信息，严重的可能导致业务请求备阻塞。

 

http://dev.mysql.com/doc/refman/5.7/en/sys-schema.html