概述
MySQL5.7的新特性中,非常突出的特性之一就是sys库,不仅可以通过sys库完成MySQL信息的收集,还可以用来监控和排查问题。下面介绍一些常用的用法。
一、用户、连接类
1、查看每个客户端IP过来的连接消耗资源情况。
select * from sys.host_summary;
![b9bfcdb8eb05d687735e1cf056f9e02a.png](https://i-blog.csdnimg.cn/blog_migrate/3f607094a63f3e5820ba641ba2e329ac.jpeg)
2、查看每个用户消耗资源情况
select * from sys.user_summary;
![0353bc16c1c7f0d90ce000d48912a944.png](https://i-blog.csdnimg.cn/blog_migrate/d99eaa6f8097c172839717a2067bcbbc.jpeg)
3、查看当前连接情况(有多少连接就应该有多少行)
select host,current_connections,statements from sys.host_summary;
![53524ed7c78ce33eae817a8295db7ed2.png](https://i-blog.csdnimg.cn/blog_migrate/b73cddfbd5dc78c2492a5ebb8acb8c3d.jpeg)
4、查看当前正在执行的SQL
和执行show full processlist的结果差不多
select conn_id,pid,user,db,command,current_statement,last_statement,time,lock_latency from sys.session
![43cdc89934bd209aba79455d8c1c8e76.png](https://i-blog.csdnimg.cn/blog_migrate/2ee58722027c4803ff9d54b4628e3b97.jpeg)
二、SQL 和io类
1、查看发生IO请求前5名的文件。
select * from sys.io_global_by_file_by_bytes order by total limit 5;
![93921fa4109545c47469852a372c0661.png](https://i-blog.csdnimg.cn/blog_migrate/4e149b5c119ffb12ba8fbc05b7b41fed.jpeg)
三、buffer pool 、内存
1、查看总共分配了多少内存
select * from sys.memory_global_total;select * from sys.memory_global_by_current_bytes;
![fe2cc01da2876fdbc1d5f50f4ce86f0d.png](https://i-blog.csdnimg.cn/blog_migrate/93d0624cab3c43cb8e6f45a9da9f6b73.jpeg)
2、每个库(database)占用多少buffer pool
select * from sys.innodb_buffer_stats_by_schema order by allocated desc;
![0edd59160f94e818420102e879e5ab0c.png](https://i-blog.csdnimg.cn/blog_migrate/6832e474e12138171058c6fbc43f60f7.jpeg)
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;
![8d6ba86d3377c52c65bafe34a9b5f5a2.png](https://i-blog.csdnimg.cn/blog_migrate/9579030df7b181d3d31b65230d13e0ae.jpeg)
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;
![7b7d08bb0e9515bbbd7e600e1a466568.png](https://i-blog.csdnimg.cn/blog_migrate/c5f9c569da0ddbf8a7948a8814e6550a.jpeg)
四、字段、索引、锁
1、查看表自增字段最大值和当前值,有时候做数据增长的监控,可以作为参考。
select * from sys.schema_auto_increment_columns;
![273ed63dfbae4d0b174e66fcc24a2083.png](https://i-blog.csdnimg.cn/blog_migrate/cea1b02dd65cdc5c7d0e23a77bc9693d.jpeg)
2、MySQL索引使用情况统计
select * from sys.schema_index_statistics order by rows_selected desc;
![5d5631870e486f998cf8babe8b9d5697.png](https://i-blog.csdnimg.cn/blog_migrate/2412f1f0ff52d2492f4fd5fd7ba35266.jpeg)
3、MySQL中有哪些冗余索引和无用索引
若库中展示没有冗余索引,则没有数据;当有联合索引idx_abc(a,b,c)和idx_a(a),那么idx_a就算冗余索引了。
select * from sys.schema_redundant_indexes;
![f4a79d9d944cf9a1a38f5e4a08b467cd.png](https://i-blog.csdnimg.cn/blog_migrate/5912675651c9aa7624928d7e4af12b8e.jpeg)
4、查看INNODB 锁信息
在未来的版本将被移除,可以采用其他方式
select * from sys.innodb_lock_waits
![53e520e30b31ef7a3c90a50b7adb08a3.png](https://i-blog.csdnimg.cn/blog_migrate/dbf1a3edb1906e11fda34e55145da18e.jpeg)
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;
![86c1cb72c20b0d2a30e4ea33166b080b.png](https://i-blog.csdnimg.cn/blog_migrate/9e60e4899e7c0f6409f3aaca2dacac06.jpeg)
五、线程类
1、MySQL内部有多个线程在运行,线程类型及数量
select user,count(*) from sys.`processlist` group by user;
![9bc253e7960254798116d66f18d85bfe.png](https://i-blog.csdnimg.cn/blog_migrate/e8cc66f9d101ae3eaa86c85ce3cb6e9a.jpeg)
六、主键自增
查看MySQL自增id的使用情况
SELECT table_schema, table_name, ENGINE, Auto_increment FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ( "INFORMATION_SCHEMA