淘宝客网站做百度竞价,万网域名怎么绑定网站,外贸网络营销如何做,杭州亚运村建设指挥部网站TIPS 本文基于MySQL 8.0 本文探讨如何深入SQL内部#xff0c;去分析其性能#xff0c;包括了三种方式#xff1a; SHOW PROFILEINFORMATION_SCHEMA.PROFILINGPERFORMANCE_SCHEMA
SHOW PROFILE
SHOW PROFILE是MySQL的一个性能分析命令#xff0c;可以跟踪SQL各种资源消耗。… TIPS 本文基于MySQL 8.0 本文探讨如何深入SQL内部去分析其性能包括了三种方式 SHOW PROFILEINFORMATION_SCHEMA.PROFILINGPERFORMANCE_SCHEMA
SHOW PROFILE
SHOW PROFILE是MySQL的一个性能分析命令可以跟踪SQL各种资源消耗。使用格式如下
SHOW PROFILE [type [,type] ... ][FOR QUERY n][LIMIT row_count [OFFSET offset]]
type:{ALL 显示所有信息| BLOCK IO 显示阻塞的输入输出次数| CONTEXT SWITCHES 显示自愿及非自愿的上下文切换次数| CPU 显示用户与系统CPU使用时间| IPC 显示消息发送与接收的次数| MEMORY 显示内存相关的开销目前未实现此功能| PAGE FAULTS 显示页错误相关开销信息| SOURCE 列出相应操作对应的函数名及其在源码中的位置行| SWPS 显示swap交换次数
}默认情况下SHOW PROFILE只展示Status和Duration两列如果想展示更多信息可指定type。 使用步骤如下
使用如下命令查看是否支持SHOW PROFILE功能yes表示支持。MySQL5.0.37开始MySQL支持SHOW PROFILE。
select have_profiling;查看当前是否启用了SHOW PROFILE0表示未启用1表示已启用
select profiling;使用如下命令为当前会话开启或关闭性能分析设成1表示开启0表示关闭
set profiling 1;使用SHOWPROFILS命令可为最近发送的SQL语句做一个概要的性能分析。展示的条目数由profiling_history_size会话变量控制该变量的默认值是15最大值为100.将值设置为0具有禁用分析的实际效果。
-- 默认展示15条
show profiles;
-- 使用profiling_history_size调整展示的条目数
set profiling_history_size 100;使用show profile分析指定查询
SHOW PROFILES;SHOW PROFILE;默认情况下只展示Status和Duration两列如果想展示更多信息可指定type。
SHOW PROFILE FOR QUERY 154;展示CPU相关的开销
SHOW PROFILE CPU FOR QUERY 184;分析完成后记得关掉SHOW PROFILE功能 TIPS MySQL官方文档申明SHOW PROFILE已被废弃并建议使用PerforMance Schema作为代替品。在某些系统上性能分析只有部分功能可用。比如部分功能在Windows系统下无效show profile使用了getrusage()这个API而在Windows系统上将会返回false因为Windows不支持这个API此外性能分析是进程级的而不是线程级的这就意味着其他线程的活动可能会影响到一看到的计时信息。 INFORMATION_SCHEMA.PROFILING
INFORMATION_SCHEMA.PROFILING用来做性能分析它的内容对应SHOW PROFILE和SHOW PROFILES语句产生的信息。除非设置了set profiling 1否则该表不会有任何数据。该表包括以下字段
QUERY_ID语句的唯一标识SEQ一个序号展示具有相同QUERY_ID值的行的显示顺序STATE分析状态DURATION在这个状态下持续了多久秒CPU_USERCPU_SYSTEM用户和系统CPU使用情况秒CONTEXT_VOLUNTARYCONTEXT_INVOLUNTARY发生了多少次自愿和非自愿的上下文切换BLOCK_OPS_INBLOCK_OPS_OUT块输入和输出操作的数量MESSAGES_SENTMESSAGES_RECEIVED发送和接收的消息数PAGE_FAULTS_MAJORPAGE_FAULTS_MINOR主要和次要的页错误信息SWAPS发生了多少SWAPSOURCE_FUNCTIONSOURCE_FILESOURCE_LINE当前状态实在源码的哪里执行的 TIPS SHOW PROFILE本质上使用的也是INFORMATION_SCHEMA.PROFILING表INFORMATION_SCHEMA.PROFILING表已被废弃在未来可能会被删除。未来将可使用Performance Schema替代。下面两个SQL是等价的 SHOW PROFILE FOR QUERY 1; SELECT STATE,FORMAT(DURATION,6) AS DURATION FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID 1 ORDER BY SEQ; PERFORMANCE_SCHEMA
PERFORMANCE_SCHEMA是MySQL建议的性能分析方式未来SHOW PROFILEINFORMATION_SCHEMA.PROFILING都会废弃。PERFORMANCE_SCHEMA在MySQL5.6引入因此在此之上的更高的版本才能使用。可使用**SHOW VARIABLES LIKE ‘performance_schema’**查看启用情况MySQL5.7开始默认启用。
下面来用PERFORMANCE_SCHEMA去实现SHOW PROFILE类似的效果
查看是否开启性能监控
SELECT * FROM performance_schema.setuo_actors;默认是开启的。
也可以执行类似如下的SQL语句只监控指定用户执行的SQL
UPDATE performance_schema.setup_actors SET ENABLED NO,HISTORY NO WHERE HOST % AND USER %;INSERT INTO performance_schema.setup_actors (HOST,USER,ROLE,ENABLED,HISTORY) VALUE
(localhost,test_user,%,YES,YES);这样就只会监控localhost服务器上test_user用户发送过来的SQL。其他服务器、其他用户发过来的SQL统统不监控。
执行如下SQL语句开启相关监控项
UPDATE performance_schema.setup_instruments SET ENABLED YES,TIMED YES WHERE NAME LIKE %statement/%;UPDATE performance_schema.setup_instruments SET ENABLED YES,TIMED YES WHERE NAME LIKE %stage/%;UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE %events_statements_%;UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE %events_stages_%;使用开启监控的用户执行SQL语句比如
select * from employees where emp_no 10001;执行如下SQL获得语句的EVENT_ID
SELECT EVENT_ID,TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration,SQL_TEXT
FROM performance_schema.events_statements_history_long
WHERE SQL_TEXT LIKE %10001%;这一步类似于SHOW PROFILES。
执行如下SQL语句做性能分析这样就可以知道这条语句各个阶段的信息了。
SELECT event_name AS State,TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
FROM performance_schema.events_stages_history_long
WHERE NESTING_EVENT_ID 869;
