带你认识 MySQL sys schema

用户头像
Simon
关注
发布于: 2020 年 08 月 21 日
带你认识MySQL sys schema

前言:



MySQL 5.7中引入了一个新的sys schema,sys是一个MySQL自带的系统库,在安装MySQL 5.7以后的版本,使用mysqld进行初始化时,会自动创建sys库。



sys库里面的表、视图、函数、存储过程可以使我们更方便、快捷的了解到MySQL的一些信息,比如哪些语句使用了临时表、哪个SQL没有使用索引、哪个schema中有冗余索引、查找使用全表扫描的SQL、查找用户占用的IO等,sys库里这些视图中的数据,大多是从performanceschema里面获得的。目标是把performanceschema的复杂度降低,让我们更快的了解DB的运行情况。



1.sys库总览



本篇文章是基于MySQL 5.7.23版本实验的。打开sys库(希望你跟着我一起做哦),我们会发现sys schema里包含1个表,100个视图,存储过程及函数共48个,如下图所示:









其实我们经常用到的是sys schema下的视图,下面将主要介绍各个视图的作用,我们发现sys schema里的视图主要分为两类,一类是正常以字母开头的,共52个,一类是以 x$ 开头的,共48个。字母开头的视图显示的是格式化数据,更易读,而 x$ 开头的视图适合工具采集数据,显示的是原始未处理过的数据。



下面我们将按类别来分析以字母开头的52个视图:



  • host_summary:这个是服务器层面的,以IP分组,比如里面的视图hostsummaryby_file_io;

  • user_summary:这个是用户层级的,以用户分组,比如里面的视图usersummaryby_file_io;

  • innodb:这个是InnoDB层面的,比如视图innodbbufferstatsbyschema;

  • io:这个是I/O层的统计,比如视图ioglobalby_file_by_bytes;

  • memory:关于内存的使用情况,比如视图memorybyhost_by_current_bytes;

  • schema:关于schema级别的统计信息,比如schematablelock_waits;

  • session:关于会话级别的,这类视图少一些,只有session和sessionsslstatus;

  • statement:关于语句级别的,比如statementswitherrorsorwarnings;

  • wait:关于等待的,比如视图waitsbyhost_by_latency。



2.常用查询介绍



1,查看每个客户端IP过来的连接消耗了多少资源。

mysql> select * from host_summary;



2,查看某个数据文件上发生了多少IO请求。

mysql> select * from ioglobalby_file_by_bytes;



3,查看每个用户消耗了多少资源。

mysql> select * from user_summary;



4,查看总共分配了多少内存。

mysql> select * from memoryglobaltotal;



5,数据库连接来自哪里,以及这些连接对数据库的请求情况是怎样的?

查看当前连接情况。

mysql> select host, currentconnections, statements from hostsummary;



6,查看当前正在执行的SQL和执行show full processlist的效果相当。

mysql> select connid, user, currentstatement, last_statement from session;



7,数据库中哪些SQL被频繁执行?

执行下面命令查询TOP 10最热SQL。

mysql> select db,execcount,query from statementanalysis order by exec_count desc limit 10;



8,哪个文件产生了最多的IO,读多,还是写的多?

mysql> select * from ioglobalby_file_by_bytes limit 10;



9,哪个表上的IO请求最多?

mysql> select * from ioglobalby_file_by_bytes where file like ‘%ibd’ order by total desc limit 10;



10,哪个表被访问的最多?先访问statement_analysis,根据热门SQL排序找到相应的数据表。

mysql> select * from statementanalysis order by avglatency desc limit 10;



11,哪些SQL执行了全表扫描或执行了排序操作?

mysql> select * from statementswithsorting;

mysql> select * from statementswithfull_table_scans;



12,哪些SQL语句使用了临时表,又有哪些用到了磁盘临时表?

查看statementanalysis中哪个SQL的tmptables 、tmpdisktables值大于0即可。

mysql> select db, query, tmptables, tmpdisk_tables from statement_analysis where tmptables>0 or tmpdisk_tables >0 order by (tmp_tables tmpdisktables) desc limit 20;



13,哪个表占用了最多的buffer pool?

mysql> select * from innodbbufferstatsbytable order by allocated desc limit 10;



14,每个库(database)占用多少buffer pool?

mysql> select * from innodbbufferstatsbyschema order by allocated desc limit 10;



15,每个连接分配多少内存?

利用session表和memorybythreadbycurrent_bytes分配表进行关联查询。

mysql> select b.user, currentcountused, currentallocated, currentavg_alloc, current_maxalloc, totalallocated,currentstatement from memoryby_thread_bycurrentbytes a, session b where a.threadid = b.thdid;



16,MySQL自增长字段的最大值和当前已经使用到的值?

mysql> select * from schemaautoincrement_columns;



17,MySQL有哪些冗余索引和无用索引?

mysql> select * from schemaredundantindexes;

mysql> select * from schemaunusedindexes;



18,查看事务等待情况

mysql> select * from innodblockwaits;



总结:



本文主要介绍sys库相关内容,其实sys库有很多有用的查询,可以帮助你轻松了解数据库的运行情况,原本需要查找performance_schema中多个表才能获得的数据,现在查询一个视图即可满足。当然,sys库需要你详细去了解,总结出你需要的查询方法。



参考资料:





发布于: 2020 年 08 月 21 日 阅读数: 39
用户头像

Simon

关注

MySQL技术学习者 2018.06.08 加入

公众号『MySQL技术』作者

评论

发布
暂无评论
带你认识MySQL sys schema