mysql query profiler_MySQL 的 Query Profiler使用

MySQL 的 Query Profiler 是一个使用非常方便的 Query 诊断分析工具，通过该工具可以获取一条 Query 在整个执行过程中多种资源的消耗情况，如 CPU、IO、IPC、SWAP等，以及发生的 PAGE FAULTS、CONTEXT SWITCHE等，同时还能得到该 Query 执行过程中 MySQL 所调用的各个函数在源文件中的位置。下面看看 Query Profiler 的具体用法。

首先可以查看目前mysql中profiler是否开启：

mysql>SELECT@@profiling;

+-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec)

开启profiler：

mysql>SETprofiling = 1;

Query OK, 0 rowsaffected (0.00 sec)

在开启 Query Profiler 功能之后，MySQL 就会自动记录所有执行的 Query 的profile 信息。下面执行 Query：

mysql>selectcount(*)fromorder_items;

+----------+

| count(*) |

+----------+

|   154258 |

+----------+

1 row inset(0.62 sec)

mysql> show profiles;

+----------+------------+------------------------------------+

| Query_ID | Duration   | Query                              |

+----------+------------+------------------------------------+

|        1 | 0.04020500 | select*fromorderswhereid=2090 |

|        2 | 0.02056800 | selectcount(*)fromt1 |

|        3 | 0.00059800 | selectcount(*)fromt1 |

|        4 | 0.00036700 | ser profiler=0                     |

|        5 | 0.00053300 | select@@profiling                 |

|        6 | 0.62734100 | selectcount(*)fromorder_items   |

+----------+------------+------------------------------------+

6 rowsinset(0.00 sec)

获取概要信息之后，就可以根据概要信息中的 Query_ID 来获取某个 Query 在执行过程中详细的 profile 信息了，如果要查看CPU和IO消耗，具体操作如下：

mysql> show profile cpu, block ioforquery 6;

+----------------------+----------+----------+------------+--------------+---------------+

| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |

+----------------------+----------+----------+------------+--------------+---------------+

| starting             | 0.000064 | 0.000000 |   0.000000 |            0 |             0 |

| checking permissions | 0.000015 | 0.000000 |   0.000000 |            0 |             0 |

| Opening tables       | 0.390653 | 0.000000 |   0.000000 |            0 |             0 |

| System lock          | 0.000028 | 0.000000 |   0.000000 |            0 |             0 |

| init                 | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |

| optimizing           | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |

| statistics| 0.000017 | 0.000000 |   0.000000 |            0 |             0 |

| preparing            | 0.000015 | 0.000000 |   0.000000 |            0 |             0 |

| executing            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |

| Sending data         | 0.235932 | 0.036002 |   0.000000 |            0 |             0 |

| end| 0.000018 | 0.000000 |   0.000000 |            0 |             0 |

| query end| 0.000018 | 0.000000 |   0.000000 |            0 |             0 |

| closing tables       | 0.000023 | 0.000000 |   0.000000 |            0 |             0 |

| freeing items        | 0.000500 | 0.000000 |   0.000000 |            0 |             0 |

| logging slow query   | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |

| cleaning up          | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |

+----------------------+----------+----------+------------+--------------+---------------+

16 rowsinset(0.03 sec)

下面列出了show profile的参数：

SHOW PROFILE [type [, type] ... ]

[FORQUERY n]

[LIMIT row_count [OFFSET offset]]

type:

ALL

| BLOCK IO

| CONTEXT SWITCHES

| CPU

| IPC

| MEMORY

| PAGE FAULTS

| SOURCE

| SWAPS

Optional type values may be specified to display specific additional types of information:

ALL displays all information

BLOCK IO displays counts for block input and output operations

CONTEXT SWITCHES displays counts for voluntary and involuntary context switches

CPU displays user and system CPU usage times

IPC displays counts for messages sent and received

MEMORY is not currently implemented

PAGE FAULTS displays counts for major and minor page faults

SOURCE displays the names of functions from the source code, together with the name and line number of the file in which the function occurs

SWAPS displays swap counts

注意：Profiling只是针对单个session的，如果session结束，profiling信息将丢失！

可以在 INFORMATION_SCHEMA中的profiling表中获取profiling信息，

下面的两句输出结果是一致的：

mysql>SHOW PROFILEFORQUERY 6;

mysql>SELECTSTATE, FORMAT(DURATION, 6)ASDURATION FROMINFORMATION_SCHEMA.PROFILINGWHEREQUERY_ID = 6ORDERBYSEQ;