您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

MySQL- 5.7数据库sys schema总结--性能优化必备

时间:2020-07-17 15:04:25  来源:  作者:

概述

performance_schema提供监控策略及大量监控项,包括:元数据锁、进度跟踪、事务、内存使用及存储程序等。但是,performance_schema又过于复杂,操作不便,所以5.7新增了 sys schema,基础数据来自于 performance 跟 information_shcema两个库,本身数据库不存储及集采数据。


一、sys schema里面视图的分类

那么sys schema在查询中的功能,哪些可以查看数据库服务资源的使用情况?哪些主机对数据库服务器的访问量最大?实际上的内存使用情况?这就要了解里面的视图了。

1、主机相关信息

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

2、innodb相关信息

以innodb开头的视图,汇总了innodb buffer page信息和事务等待innodb锁信息;

3、IO使用情况

以IO开头的视图,总结了IO使用者的信息,包括等待IO的情况、IO使用量情况,从各个角度分组展示;

4、内存相关信息

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

5、连接与会话相关信息

其中,processlist 和 session相关的视图,总结了会话相关的信息;

6、表相关信息

以schema_table开头的视图,从全表扫描、innodb缓冲池等方面展示了表统计信息;

7、索引相关信息

其中包含index的视图,统计了索引使用的情况,以及重复索引和未使用的索引情况;

8、语句相关信息

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

9、用户相关信息

以user开头的视图,统计了用户使用的文件IO,执行的语句统计信息等;

10、等待事件相关信息

以wait开头的视图,从主机和事件角度展示等待类事件的延迟情况;


二、sys schema日常应用

1、查看process

1.1、常用的3个查询

有以下3个查询:

其中,show processlist为简要查看当前连接数据库情况,包含SQL语句的statement列仅提供部分SQL,而show full processlist则提供完整的SQL 语句,information_schema.processlist的内容与show full processlist 内容一致,但是可以以表格查询的形式添加where条件,达到自己的使用需求。

show processlist; 
show full processlist; 
select * from information_schema.processlist where state!='';
MySQL- 5.7数据库sys schema总结--性能优化必备

 

1.2、sys的四个视图

除此之外,sys提供以下四个视图查看 连接情况,这四个则更为详细的提供了 行数情况、临时表情况、当前SQL以及最后提交SQL(即使是sleep状态,这里也有最后提交的SQL可以查看)等信息。

若想详细查看,可以通过 `performance_schema`.`events_statements_current` 表格查看,通过sys.processlist 的thd_id关联查看。

select * from sys.processlist;
select * from sys.session;
select * from sys.x$processlist;
select * from sys.x$session;

2、查看表访问量

SELECT
	table_schema,
	table_name,
	sum( io_read_requests + io_write_requests ) io 
FROM
	schema_table_statistics 
GROUP BY
	table_schema,
	table_name 
ORDER BY
	io DESC 
	LIMIT 10;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

3、冗余索引与未使用索引

--冗余索引查看 
select table_schema,table_name,redundant_index_name,redundant_index_columns,dominant_index_name,dominant_index_columns from sys.schema_redundant_indexes; 
--未使用索引查看 
select * from sys.schema_unused_indexes;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

4、表自增ID监控

select * from sys.schema_auto_increment_columns;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

5、监控全表扫描的sql语句

 select * from sys.statements_with_full_table_scans where db = 'pas_prod' order by exec_count desc;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

6、查看实际消耗磁盘IO的文件

 select file,avg_read+avg_write as avg_io from io_global_by_file_by_bytes order by avg_io desc limit 10;
MySQL- 5.7数据库sys schema总结--性能优化必备

 


三、sys schema视图一览表

sys schema只有一张表sys_config,一般关注statement_truncate_len列就行(影响函数format_statement()截断SQL后的长度,即最后SQL语句显示的总长度,默认为64),最重要的就是里面的视图了。

sys库的视图分为 带x$跟不带x$前缀的视图,这两种没啥实质性区别,不带 x$ 的视图是人性化的结果展示,会有一些单位换算,而带x$前缀的则是原始数据单位,未经换算。

1、主机相关

以host_summary开头的视图,提供IO延迟等相关信息

1.1、视图一览

  • The host_summary and x$host_summary Views
  • The host_summary_by_file_io and x$host_summary_by_file_io Views
  • The host_summary_by_file_io_type and x$host_summary_by_file_io_type Views
  • The host_summary_by_stages and x$host_summary_by_stages Views
  • The host_summary_by_statement_latency and x$host_summary_by_statement_latency Views
  • The host_summary_by_statement_type and x$host_summary_by_statement_type Views

1.2、应用场景

1.2.1、host_summary(常用)

日常中主要使用的是host_summary视图,可以根据连接数据库的host总的执行sql数目、执行时长、表扫描、文件IO、连接情况、用户情况及内存分布情况,可以让DBA快速定位到是哪台host最耗费数据库资源,对连接数据库的所有host有一个大致的资源使用情况的了解。

 select * from sys.host_summary;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

1.2.2、host_summary_by_file_io_type

详细查看每个host的主要是在什么文件类型上耗费IO资源,可以查看 host_summary_by_file_io_type视图

MySQL- 5.7数据库sys schema总结--性能优化必备

 

1.2.3、host_summary_by_file_io

仅查看每台host总的IO情况,则可以查看视图host_summary_by_file_io

 select * from sys.host_summary_by_file_io;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

2、innodb相关

以innodb开头的视图,汇总了innodb buffer page信息和事务等待innodb锁信息

2.1、视图一览

  • The innodb_buffer_stats_by_schema and x$innodb_buffer_stats_by_schema Views
  • The innodb_buffer_stats_by_table and x$innodb_buffer_stats_by_table Views
  • The innodb_lock_waits and x$innodb_lock_waits Views

2.2、应用场景

2.2.1、 innodb_buffer_stats_by_schema

当一个实例中有多个业务库,由于性能问题,可能想查看下各个数据库的内存占用情况,可以使用视图 innodb_buffer_stats_by_schema,但是少用慎用,因为会扫描整个buffer pool来统计,如果所在实例buffer pool非常大,那么这是一个极为耗费资源的查询,建议慎用。这个视图实际上是通过 视图 innodb_buffer_stats_by_table的数据做了group by object_schema得到的。

 select * from sys.innodb_buffer_stats_by_schema;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

2.2.2、 innodb_buffer_stats_by_table(常用,慎用)

在某种情况下,需要查询表格在内存中的占用情况,可以通过视图 innodb_buffer_stats_by_table来查询,也是扫描整个buffer pool统计,少用慎用。

 select * from sys.innodb_buffer_stats_by_table;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

3、IO相关

以IO开头的视图,等待IO情况/IO使用情况

3.1、视图一览

  • The io_by_thread_by_latency and x$io_by_thread_by_latency Views
    • 各个IO线程的使用情况
  • The io_global_by_file_by_bytes and x$io_global_by_file_by_bytes Views
    • 各个数据库文件的IO情况
  • The io_global_by_file_by_latency and x$io_global_by_file_by_latency Views
    • 各个数据库文件的IO耗时情况
  • The io_global_by_wait_by_bytes and x$io_global_by_wait_by_bytes Views
    • 数据库事件IO等待情况
  • The io_global_by_wait_by_latency and x$io_global_by_wait_by_latency Views
    • 数据库事件IO等待耗时情况
  • The latest_file_io and x$latest_file_io Views
    • 当前正在读写文件的情况

3.2、应用场景

3.2.1、io_global_by_file_by_bytes(常用)

查看数据库实例的IO分布情况,及着重优化对象,可以使用 io_global_by_file_by_bytes

 select * from sys.io_global_by_file_by_bytes order by count_read  desc;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

4、内存相关

以memory开头的视图,从主机/线程/用户等角度展示内存的使用情况

4.1、视图一览

  • The memory_by_host_by_current_bytes and x$memory_by_host_by_current_bytes Views
  • The memory_by_thread_by_current_bytes and x$memory_by_thread_by_current_bytes Views
  • The memory_by_user_by_current_bytes and x$memory_by_user_by_current_bytes Views
  • The memory_global_by_current_bytes and x$memory_global_by_current_bytes Views
  • The memory_global_total and x$memory_global_total Views

4.2、应用场景

4.2.1、memory_by_host_by_current_bytes、memory_by_thread_by_current_bytes 、memory_by_user_by_current_bytes

当前内存使用情况,从 host、thread、user等角度来分别查看,对应各自的视图即可。

 select * from sys.memory_by_host_by_current_bytes;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

5、连接与会话相关

含有processlist和session的视图,显示会话相关的信息

5.1、视图一览

  • The processlist and x$processlist Views
  • The session and x$session Views
  • The session_ssl_status View

5.2、应用场景

5.2.1、processlist(常用)

查看连接使用情况,session的结果跟processlist类似。查看连接情况,有非常多种方式,每种方式都有各自的使用情况。

 select * from sys.processlist;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

6、表相关

以schema_table开头的视图,从全表扫描/innodb缓冲池表现表统计信息

6.1、视图一览

  • The schema_table_lock_waits and x$schema_table_lock_waits Views
  • The schema_table_statistics and x$schema_table_statistics Views
  • The schema_table_statistics_with_buffer and x$schema_table_statistics_with_buffer Views
  • The schema_tables_with_full_table_scans and x$schema_tables_with_full_table_scans Views
  • The schema_auto_increment_columns View

6.2、应用场景

6.2.1、schema_table_statistics(常用)

查看表格的update、delete、insert、select的IO情况,可以使用schema_table_statistics视图

 select * from sys.schema_table_statistics;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

6.2.2、schema_tables_with_full_table_scans(常用)

查看表格的全表扫描情况,抓取需要重点优化的对象,可以使用视图schema_tables_with_full_table_scans

 select * from sys.schema_tables_with_full_table_scans;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

6.2.3、schema_auto_increment_columns(常用)

查看表格的自增长是否快达到瓶颈了,有些表格存在频繁的删除操作,可能导致自增ID的最大值跟表格数量极不相符合,为了避免问题,可以通过视图 schema_auto_increment_columns,查看有哪些表格快要达到自增的瓶颈值

 select * from sys.schema_auto_increment_columns order by auto_increment_ratio desc;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

7、索引相关

含有index的视图

7.1、视图一览

  • The schema_object_overview View
  • The schema_redundant_indexes and x$schema_flattened_keys Views
  • The schema_unused_indexes View
  • The schema_index_statistics and x$schema_index_statistics Views

7.2、应用场景

7.2.1、schema_object_overview(常用)

查看当前实例内各个数据的对象及索引分布情况,可以使用 schema_object_overview

 select * from sys.schema_object_overview where db='pas_prod';
MySQL- 5.7数据库sys schema总结--性能优化必备

 

7.2.2、schema_redundant_indexes(常用)

查看数据库的冗余索引情况,可以通过视图 schema_redundant_indexes,但不是所有冗余索引都要删除,需要衡量实际的使用情况、索引大小、索引扫描情况后再决定。

 select * from sys.schema_redundant_indexes;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

7.2.3、schema_unused_indexes(常用)

查看数据库没有使用的索引,可以使用 schema_unused_indexes

 select * from sys.schema_unused_indexes;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

7.2.4、schema_index_statistics(常用)

查看索引的select \updatedeleteinsert情况,可以使用schema_index_statistics

 select * from sys.schema_index_statistics;
MySQL- 5.7数据库sys schema总结--性能优化必备

 

8、语句相关

以statement开头的视图,显示错误数、警告数、执行全表扫描、使用临时表、执行排序等信息

8.1、视图一览

  • The statement_analysis and x$statement_analysis Views(常用)
  • The statements_with_errors_or_warnings and x$statements_with_errors_or_warnings Views(常用)
  • The statements_with_full_table_scans and x$statements_with_full_table_scans Views(常用)
  • The statements_with_runtimes_in_95th_percentile and x$statements_with_runtimes_in_95th_percentile Views
  • The statements_with_sorting and x$statements_with_sorting Views(常用)
  • The statements_with_temp_tables and x$statements_with_temp_tables Views(常用)

8.2、应用场景

汇总SQL中错误数、警告数、执行全表扫描、使用临时表、执行排序等信息,sql语句也是使用 format_statement() 函数做了长度限制,如果想查看完整的SQL,可以通过 这个表格的这一列查看performance_schema`.`events_statements_summary_by_digest`.`DIGEST_TEXT`,关联的添加列是 DIGEST

9、用户相关

以user开头的视图,显示用户使用的文件IO/执行语句的统计信息

9.1、视图一览

  • The user_summary and x$user_summary Views (常用)
  • The user_summary_by_file_io and x$user_summary_by_file_io Views
  • The user_summary_by_file_io_type and x$user_summary_by_file_io_type Views
  • The user_summary_by_stages and x$user_summary_by_stages Views
  • The user_summary_by_statement_latency and x$user_summary_by_statement_latency Views
  • The user_summary_by_statement_type and x$user_summary_by_statement_type Views

9.2、应用场景

从用户的角度,分别统计文件的IO情况、sql执行情况,如果数据库的用户是按照业务模块来划分的,那么则可以清晰的看到哪些业务耗费资源较多

10、等待信息

以wait开头的视图

10.1、视图一览

  • The wait_classes_global_by_avg_latency and x$wait_classes_global_by_avg_latency Views
    • 按事件event分组,统计各个event的平均延迟时长
  • The wait_classes_global_by_latency and x$wait_classes_global_by_latency Views
    • 按事件event分组,统计各个event的总延迟时长
  • The waits_by_host_by_latency and x$waits_by_host_by_latency Views
  • The waits_by_user_by_latency and x$waits_by_user_by_latency Views
  • The waits_global_by_latency and x$waits_global_by_latency Views
    • 所有event的延迟情况

10.2、应用场景

等待类视图,分别从事件、主机、用户等角度,进行查询分析。


sys schema上的视图对于做性能分析是很重要的,建议大家重点掌握~

后面会分享更多devops和DBA方面内容,感兴趣的朋友可以关注下!



Tags:MySQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  Tags: MySQL  点击:(7)  评论:(0)  加入收藏
一、为什么要搭建主从架构呢1.数据安全,可以进行数据的备份。2.读写分离,大部分的业务系统来说都是读数据多,写数据少,当访问压力过大时,可以把读请求给到从服务器。从而缓解数据...【详细内容】
2021-12-15  Tags: MySQL  点击:(12)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  Tags: MySQL  点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  Tags: MySQL  点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  Tags: MySQL  点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  Tags: MySQL  点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  Tags: MySQL  点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  Tags: MySQL  点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  Tags: MySQL  点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Tags: MySQL  点击:(31)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条