Mysql SQL优化工具我们常使用explain去解析sql的执行,根据执行计划去评估sql的性能消耗瓶颈,而MYSQL Profiling提供我们详细的SQL执行过程中的cpu/io/swap/memory等使用情况以及每个过程执行时间消耗。
主要用途为1:查看SQL执行消耗瓶颈位置2、查看sql的执行过程,每步操作在具体哪个源码文件的什么位置
这里简单介绍下其使用方式:
profiling在mysql 5.0.37版本以后支持,在mysql5.7后可以通过performance_schema替代(25.18.1 Query Profiling Using Performance Schema),但通过session级别的追踪比较方便
1、相关变量
(root:localhost:Wed Nov 15 16:32:50 2017)[performance_schema]> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES | ##是否支持profile功能
| profiling | ON | ##是否开启profile ,0|off表示关闭,1|on表示开启
| profiling_history_size | 15 | ##展示的历史sql数,默认是最近的15条,最大值是100
+------------------------+-------+
2、查看语法
可以通过help show profiles查看帮助文档
show profiles可以查看历史执行最近的15条sql
点击(此处)折叠或打开
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type:
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS
3、使用实例
1)开启profling
(root:localhost:Wed Nov 15 16:37:00 2017)[dbtest]> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
(root:localhost:Wed Nov 15 16:37:14 2017)[dbtest]> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.00 sec)
2)查看所有profiling记录的sql
(root:localhost:Wed Nov 15 16:37:16 2017)[dbtest]> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------+
| 1 | 0.00089900 | show variables like '%profil%' |
+----------+------------+--------------------------------+
3)查看指定profiling记录的sql
(root:localhost:Wed Nov 15 16:39:14 2017)[dbtest]> show profile for query 2 ;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000103 |
| checking permissions | 0.000008 |
| Opening tables | 0.000060 |
| init | 0.000023 |
| System lock | 0.000011 |
| optimizing | 0.000007 |
| statis