MySQL：SQL性能分析
TIPS
本⽂基于MySQL 8.0
本⽂探讨如何深⼊SQL内部，去分析其性能，包括了三种⽅式：
mysqlschema作用SHOW PROFILE
INFORMATION_SCHEMA.PROFILING
PERFORMANCE_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 列出相应操作对应的函数名及其在源码中的位置(⾏)
| SWAPS 显⽰swap交换次数
}
默认情况下，SHOW PROFILE只展⽰Status和Duration两列，如果想展⽰更多信息，可指定type。
使⽤步骤如下：
使⽤如下命令，查看是否⽀持SHOW PROFILE功能，yes标志⽀持。从MySQL 5.0.37开始，MySQL⽀持SHOW PROFILE。
select @@have_profiling;
查看当前是否启⽤了SHOW PROFILE，0表⽰未启⽤，1表⽰已启⽤
select @@profiling;
使⽤如下命令为当前会话开启或关闭性能分析，设成1表⽰开启，0表⽰关闭
set profiling =1;
使⽤SHOW PROFILES命令，可为最近发送的SQL语句做⼀个概要的性能分析。展⽰的条⽬数⽬由profiling_history_size会话变量控制，该变量的默认值为15。最⼤值为100。将值设置为0具有禁⽤分析
的实际效果。
-- 默认展⽰15条
show profiles
-- 使⽤profiling_history_size调整展⽰的条⽬数
set profiling_history_size =100;
使⽤show profile分析指定查询：
mysql>SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query |
+----------+----------+--------------------------+
|0|0.000088|SET PROFILING =1|
|1|0.000136|DROP TABLE IF EXISTS t1 |
|2|0.011947|CREATE TABLE t1 (id INT)|
+----------+----------+--------------------------+
3rows in set(0.00 sec)
mysql>SHOW PROFILE;
+----------------------+----------+
|Status| Duration |
数据库原理及应用试卷+----------------------+----------+
| checking permissions |0.000040|
| creating table|0.000056|
|After create|0.011363|
| query end|0.000375|
| freeing items |0.000089|
| logging slow query |0.000019|
| cleaning up |0.000005|
+----------------------+----------+
7rows in set(0.00 sec)
-- 默认情况下，只展⽰Status和Duration两列，如果想展⽰更多信息，可指定type。
mysql>SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
|Status| Duration |
+--------------------+----------+
| query end|0.000107|
| freeing items |0.000008|
| logging slow query |0.000015|
| cleaning up |0.000006|
+--------------------+----------+
4rows in set(0.00 sec)个人主页设计源代码包
-- 展⽰CPU相关的开销
mysql>SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
|Status| Duration | CPU_user | CPU_system |
ip层负责什么的通信+----------------------+----------+----------+------------+
| checking permissions |0.000040|0.000038|0.000002|
| creating table|0.000056|0.000028|0.000028|
|After create|0.011363|0.000217|0.001571|
| query end|0.000375|0.000013|0.000028|
| freeing items |0.000089|0.000010|0.000014|
| logging slow query |0.000019|0.000009|0.000010|
| cleaning up |0.000005|0.000003|0.000002|
+----------------------+----------+----------+------------+
7rows in set(0.00 sec)
分析完成后，记得关闭掉SHOW PROFILE功能：
set profiling =0;
TIPS
单片机程序100例 完全免费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_USER，CPU_SYSTEM：⽤户和系统CPU使⽤情况（秒）
CONTEXT_VOLUNTARY，CONTEXT_INVOLUNTARY：发⽣了多少⾃愿和⾮⾃愿的上下⽂转换
BLOCK_OPS_IN，BLOCK_OPS_OUT：块输⼊和输出操作的数量
MESSAGES_SENT，MESSAGES_RECEIVED：发送和接收的消息数
PAGE_FAULTS_MAJOR，PAGE_FAULTS_MINOR：主要和次要的页错误信息
SWAPS：发⽣了多少SWAP
SOURCE_FUNCTION，SOURCE_FILE，SOURCE_LINE：当前状态是在源码的哪⾥执⾏的
TIPS
SHOW PROFILE本质上使⽤的也是INFORMATION_SCHEMA.PROFILING表；
INFORMATION_SCHEMA.PROFILING表已被废弃，在未来可能会被删除。未来将可使⽤Performance Schema替代，详见 “”
下⾯两个SQL是等价的：
SHOW PROFILE FOR QUERY 2;
SELECT STATE,FORMAT(DURATION,6)AS DURATION FROM
INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID =2ORDER BY SEQ;
PERFORMANCE_SCHEMA
PERFORMANCE_SCHEMA是MySQL建议的性能分析⽅式，未来SHOW PROFILE、INFORMATION_SCHEMA.PROFILING都会废弃。据笔者研究，PERFORMANCE_SCHEMA在MySQL 5.6引⼊，因此，在MySQL 5.6及更⾼版本才能使⽤。可使⽤SHOW VARIABLES LIKE 'performance_schema'; 查看启⽤情况，MySQL 5.7开始默认启⽤。
下⾯来⽤PERFORMANCE_SCHEMA去实现SHOW PROFILE类似的效果：
查看是否开启性能监控
mysql>SELECT*FROM performance_schema.setup_actors;
+------+------+------+---------+---------+
| HOST |USER| ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
|%|%|%| YES | YES |
dubbo项目怎么启动+------+------+------+---------+---------+
默认是开启的。
你也可以执⾏类似如下的SQL语句，只监控指定⽤户执⾏的SQL：
mysql>UPDATE performance_schema.setup_actors
SET ENABLED ='NO', HISTORY ='NO'
WHERE HOST ='%'AND USER='%';
mysql>INSERT INTO performance_schema.setup_actors
(HOST,USER,ROLE,ENABLED,HISTORY)
VALUES('localhost','test_user','%','YES','YES');
这样，就只会监控localhost机器上test_user⽤户发送过来的SQL。其他主机、其他⽤户发过来的SQL统统不监控。
执⾏如下SQL语句，开启相关监控项：
mysql>UPDATE performance_schema.setup_instruments
SET ENABLED ='YES', TIMED ='YES'
WHERE NAME LIKE'%statement/%';
mysql>UPDATE performance_schema.setup_instruments
SET ENABLED ='YES', TIMED ='YES'
WHERE NAME LIKE'%stage/%';
mysql>UPDATE performance_schema.setup_consumers
SET ENABLED ='YES'
WHERE NAME LIKE'%events_statements_%';
mysql>UPDATE performance_schema.setup_consumers
SET ENABLED ='YES'
WHERE NAME LIKE'%events_stages_%';
使⽤开启监控的⽤户，执⾏SQL语句，⽐如：
mysql>SELECT*ployees WHERE emp_no =10001;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
|10001|1953-09-02| Georgi | Facello | M |1986-06-26|
+--------+------------+------------+-----------+--------+------------+
执⾏如下SQL，获得语句的EVENT_ID。
mysql>SELECT EVENT_ID,TRUNCATE(TIMER_WAIT/1000000000000,6)as Duration, SQL_TEXT
FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like'%10001%';
+----------+----------+--------------------------------------------------------+
| event_id | duration | sql_text |
+----------+----------+--------------------------------------------------------+
|31|0.028310|SELECT*ployees WHERE emp_no =10001|
+----------+----------+--------------------------------------------------------+
这⼀步类似于 SHOW PROFILES。
执⾏如下SQL语句做性能分析，这样就可以知道这条语句各种阶段的信息了。
mysql>SELECT event_name AS Stage,TRUNCATE(TIMER_WAIT/1000000000000,6)AS Duration F
ROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=31; +--------------------------------+----------+
| Stage | Duration |
+--------------------------------+----------+
| stage/sql/starting|0.000080|
| stage/sql/checking permissions |0.000005|
| stage/sql/Opening tables|0.027759|
| stage/sql/init |0.000052|
| stage/sql/System lock|0.000009|
| stage/sql/optimizing |0.000006|
| stage/sql/statistics|0.000082|
| stage/sql/preparing |0.000008|
| stage/sql/executing |0.000000|
| stage/sql/Sending data|0.000017|
| stage/sql/end|0.000001|
| stage/sql/query end|0.000004|
| stage/sql/closing tables|0.000006|
| stage/sql/freeing items |0.000272|
| stage/sql/cleaning up |0.000001|
+--------------------------------+----------+
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论