概述

MySQL5.7的新特性中，非常突出的特性之一就是sys庫，不僅可以通過sys庫完成MySQL信息的收集，還可以用來監控和排查問題。下面介紹一些常用的用法。

一、用戶、連接類

1、查看每個客戶端IP過來的連接消耗資源情況。

select * from sys.host_summary;

2、查看每個用戶消耗資源情況

select * from sys.user_summary;

3、查看當前連接情況（有多少連接就應該有多少行）

select host,current_connections,statements from sys.host_summary;

4、查看當前正在執行的SQL

和執行show full processlist的結果差不多

select conn_id,pid,user,db,command,current_statement,last_statement,time,lock_latency from sys.session

二、SQL 和io類

1、查看發生IO請求前5名的文件。

select * from sys.io_global_by_file_by_bytes order by total limit 5;

三、buffer pool 、內存

1、查看總共分配了多少內存

select * from sys.memory_global_total;
select * from sys.memory_global_by_current_bytes;

2、每個庫（database）佔用多少buffer pool

select * from sys.innodb_buffer_stats_by_schema order by allocated desc;

pages是指在buffer pool中的page數量；pages_old指在LUR 列表中處於後37%位置的page。

當出現buffer page不夠用時，就會徵用這些page所佔的空間。37%是默認位置，具體可以自定義。

3、統計每張表具體在InnoDB中具體的情況，比如佔多少頁？

注意和前面的pages的總數都是相等的，也可以借用sum（pages）運算驗證一下。

select * from sys.innodb_buffer_stats_by_table;

4、查詢每個連接分配了多少內存

利用session表和
memory_by_thread_by_current_bytes分配表進行關聯查詢。

SELECT
 b.USER,
 current_count_used,
 current_allocated,
 current_avg_alloc,
 current_max_alloc,
 total_allocated,
 current_statement 
FROM
 sys.memory_by_thread_by_current_bytes a,
 sys.SESSION b 
WHERE
 a.thread_id = b.thd_id;

四、字段、索引、鎖

1、查看錶自增字段最大值和當前值，有時候做數據增長的監控，可以作為參考。

select * from sys.schema_auto_increment_columns;

2、MySQL索引使用情況統計

select * from sys.schema_index_statistics order by rows_selected desc;

3、MySQL中有哪些冗餘索引和無用索引

若庫中展示沒有冗餘索引，則沒有數據；當有聯合索引idx_abc(a,b,c)和idx_a(a)，那麼idx_a就算冗餘索引了。

select * from sys.schema_redundant_indexes;

4、查看INNODB 鎖信息

在未來的版本將被移除，可以採用其他方式

select * from sys.innodb_lock_waits

5、查看庫級別的鎖信息，這個需要先打開MDL鎖的監控：

--打開MDL鎖監控
update performance_schema.setup_instruments set enabled='YES',TIMED='YES' where name='wait/lock/metadata/sql/mdl';
select * from sys.schema_table_lock_waits;

五、線程類

1、MySQL內部有多個線程在運行，線程類型及數量

select user,count(*) from sys.`processlist` group by user;

六、主鍵自增

查看MySQL自增id的使用情況

SELECT
 table_schema,
 table_name,
 ENGINE,
 Auto_increment 
FROM
 information_schema.TABLES 
WHERE
 TABLE_SCHEMA NOT IN ( "INFORMATION_SCHEMA", "PERFORMANCE_SCHEMA", "MYSQL", "SYS" )