加入收藏 | 设为首页 | 会员中心 | 我要投稿 航空爱好网 (https://www.52kongjun.com/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

MySQL怎样使用profiling

发布时间:2021-12-26 16:48:40 所属栏目:MySql教程 来源:互联网
导读:这篇文章将为大家详细讲解有关MySQL如何使用profiling,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。 Mysql SQL优化工具我们常使用explain去解析sql的执行,根据执行计划去评估sql的性能消耗瓶颈,而MYSQL Profiling提
这篇文章将为大家详细讲解有关MySQL如何使用profiling,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。
 
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 |
| statistics           | 0.000016 |
| preparing            | 0.000015 |
| executing            | 0.000007 |
| Sending data         | 0.000063 |
| end                  | 0.000004 |
| query end            | 0.000010 |
| closing tables       | 0.000012 |
| freeing items        | 0.000016 |
| logging slow query   | 0.000003 |
| logging slow query   | 0.000070 |
| cleaning up          | 0.000014 |
+----------------------+----------+
4)查看指定profiling记录的sql,并且显示cpu/block io/的步骤消耗信息
 
(root:localhost:Wed Nov 15 16:43:47 2017)[dbtest]> show profile cpu ,block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000103 |     NULL |       NULL |         NULL |          NULL |
| checking permissions | 0.000008 |     NULL |       NULL |         NULL |          NULL |
| Opening tables       | 0.000060 |     NULL |       NULL |         NULL |          NULL |
| init                 | 0.000023 |     NULL |       NULL |         NULL |          NULL |
| System lock          | 0.000011 |     NULL |       NULL |         NULL |          NULL |
| optimizing           | 0.000007 |     NULL |       NULL |         NULL |          NULL |
| statistics           | 0.000016 |     NULL |       NULL |         NULL |          NULL |
| preparing            | 0.000015 |     NULL |       NULL |         NULL |          NULL |
| executing            | 0.000007 |     NULL |       NULL |         NULL |          NULL |
| Sending data         | 0.000063 |     NULL |       NULL |         NULL |          NULL |
| end                  | 0.000004 |     NULL |       NULL |         NULL |          NULL |
| query end            | 0.000010 |     NULL |       NULL |         NULL |          NULL |
| closing tables       | 0.000012 |     NULL |       NULL |         NULL |          NULL |
| freeing items        | 0.000016 |     NULL |       NULL |         NULL |          NULL |
| logging slow query   | 0.000003 |     NULL |       NULL |         NULL |          NULL |
| logging slow query   | 0.000070 |     NULL |       NULL |         NULL |          NULL |
| cleaning up          | 0.000014 |     NULL |       NULL |         NULL |          NULL |
+----------------------+----------+----------+------------+--------------+---------------+
 
5)查看指定profiling记录的sql,并且显示每步源码文件信息
(root:localhost:Wed Nov 15 16:44:09 2017)[dbtest]> show profile  source for query 2;
+----------------------+----------+-----------------------+------------------+-------------+
| Status               | Duration | Source_function       | Source_file      | Source_line |
+----------------------+----------+-----------------------+------------------+-------------+
| starting             | 0.000103 | NULL                  | NULL             |        NULL |
| checking permissions | 0.000008 | check_access          | sql_parse.cc     |        5635 |
| Opening tables       | 0.000060 | open_tables           | sql_base.cc      |        5029 |
| init                 | 0.000023 | mysql_prepare_select  | sql_select.cc    |        1051 |
| System lock          | 0.000011 | mysql_lock_tables     | lock.cc          |         304 |
| optimizing           | 0.000007 | optimize              | sql_optimizer.cc |         138 |
| statistics           | 0.000016 | optimize              | sql_optimizer.cc |         381 |
| preparing            | 0.000015 | optimize              | sql_optimizer.cc |         504 |
| executing            | 0.000007 | exec                  | sql_executor.cc  |         110 |
| Sending data         | 0.000063 | exec                  | sql_executor.cc  |         187 |
| end                  | 0.000004 | mysql_execute_select  | sql_select.cc    |        1106 |
| query end            | 0.000010 | mysql_execute_command | sql_parse.cc     |        5307 |
| closing tables       | 0.000012 | mysql_execute_command | sql_parse.cc     |        5383 |
| freeing items        | 0.000016 | mysql_parse           | sql_parse.cc     |        6676 |
| logging slow query   | 0.000003 | log_slow_do           | sql_parse.cc     |        2077 |
| logging slow query   | 0.000070 | log_slow_do           | sql_parse.cc     |        2078 |
| cleaning up          | 0.000014 | dispatch_command      | sql_parse.cc     |        1878 |
+----------------------+----------+-----------------------+------------------+-------------+
17 rows in set, 1 warning (0.00 sec)
 
6)查看指定profiling记录的sql,并且显示所有的步骤消耗信息
(root:localhost:Wed Nov 15 16:40:34 2017)[dbtest]> show profile all for query 2  G
*************************** 1. row ***************************
             Status: starting
           Duration: 0.000103
           CPU_user: NULL
         CPU_system: NULL
  Context_voluntary: NULL
Context_involuntary: NULL
       Block_ops_in: NULL
      Block_ops_out: NULL
      Messages_sent: NULL
  Messages_received: NULL
  Page_faults_major: NULL
  Page_faults_minor: NULL
              Swaps: NULL
    Source_function: NULL
        Source_file: NULL
        Source_line: NULL
*************************** 2. row ***************************
             Status: checking permissions
           Duration: 0.000008
           CPU_user: NULL
         CPU_system: NULL
  Context_voluntary: NULL
Context_involuntary: NULL
       Block_ops_in: NULL
      Block_ops_out: NULL
      Messages_sent: NULL
  Messages_received: NULL
  Page_faults_major: NULL
  Page_faults_minor: NULL
              Swaps: NULL
    Source_function: check_access
        Source_file: sql_parse.cc
        Source_line: 5635
*************************** 3. row ***************************
             Status: Opening tables
           Duration: 0.000060
           CPU_user: NULL
         CPU_system: NULL
  Context_voluntary: NULL
Context_involuntary: NULL
       Block_ops_in: NULL
      Block_ops_out: NULL
      Messages_sent: NULL
  Messages_received: NULL
  Page_faults_major: NULL
  Page_faults_minor: NULL
              Swaps: NULL
    Source_function: open_tables
        Source_file: sql_base.cc
        Source_line: 5029
*************************** 4. row ***************************
             Status: init
           Duration: 0.000023
           CPU_user: NULL
         CPU_system: NULL
  Context_voluntary: NULL
Context_involuntary: NULL
       Block_ops_in: NULL
      Block_ops_out: NULL
      Messages_sent: NULL
  Messages_received: NULL
  Page_faults_major: NULL
  Page_faults_minor: NULL
              Swaps: NULL
    Source_function: mysql_prepare_select
        Source_file: sql_select.cc
        Source_line: 1051
*************************** 5. row ***************************
             Status: System lock
           Duration: 0.000011
           CPU_user: NULL
         CPU_system: NULL
  Context_voluntary: NULL
Context_involuntary: NULL
       Block_ops_in: NULL
      Block_ops_out: NULL
      Messages_sent: NULL
  Messages_received: NULL
  Page_faults_major: NULL
  Page_faults_minor: NULL
              Swaps: NULL
    Source_function: mysql_lock_tables
        Source_file: lock.cc
        Source_line: 304
*************************** 6. row ***************************
             Status: optimizing
           Duration: 0.000007
           CPU_user: NULL
         CPU_system: NULL
  Context_voluntary: NULL
Context_involuntary: NULL
       Block_ops_in: NULL
      Block_ops_out: NULL
      Messages_sent: NULL
  Messages_received: NULL
  Page_faults_major: NULL
  Page_faults_minor: NULL
              Swaps: NULL
    Source_function: optimize
        Source_file: sql_optimizer.cc
        Source_line: 138
*************************** 7. row ***************************
             Status: statistics
           Duration: 0.000016
           CPU_user: NULL
         CPU_system: NULL
  Context_voluntary: NULL
Context_involuntary: NULL
       Block_ops_in: NULL
      Block_ops_out: NULL
      Messages_sent: NULL
  Messages_received: NULL
  Page_faults_major: NULL
  Page_faults_minor: NULL
              Swaps: NULL
    Source_function: optimize
        Source_file: sql_optimizer.cc
        Source_line: 381
*************************** 8. row ***************************
             Status: preparing
           Duration: 0.000015
           CPU_user: NULL
         CPU_system: NULL
  Context_voluntary: NULL
Context_involuntary: NULL
       Block_ops_in: NULL
      Block_ops_out: NULL
      Messages_sent: NULL
  Messages_received: NULL
  Page_faults_major: NULL
  Page_faults_minor: NULL
              Swaps: NULL
    Source_function: optimize
        Source_file: sql_optimizer.cc
        Source_line: 504
*************************** 9. row ***************************
             Status: executing
           Duration: 0.000007
           CPU_user: NULL
         CPU_system: NULL
  Context_voluntary: NULL
Context_involuntary: NULL
       Block_ops_in: NULL
      Block_ops_out: NULL
      Messages_sent: NULL
  Messages_received: NULL
  Page_faults_major: NULL
  Page_faults_minor: NULL
              Swaps: NULL
    Source_function: exec
        Source_file: sql_executor.cc
        Source_line: 110
 
关于“MySQL如何使用profiling”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。

(编辑:航空爱好网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!