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> SET profiling = 1;
  
•    Query OK, 0 rows affected (0.00 sec)
  

　　

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

• mysql> select count(*) from order_items;
  
• +----------+
  
• | count(*) |
  
• +----------+
  
• |   154258 |
  
• +----------+
  
• 1 row in set (0.62 sec)
  
• 
  
• mysql> show profiles;
  
• +----------+------------+------------------------------------+
  
• | Query_ID | Duration   | Query                              |
  
• +----------+------------+------------------------------------+
  
• |        1 | 0.04020500 | select * from orders where id=2090 |
  
• |        2 | 0.02056800 | select count(*) from t1 |
  
• |        3 | 0.00059800 | select count(*) from t1 |
  
• |        4 | 0.00036700 | ser profiler=0                     |
  
• |        5 | 0.00053300 | select @@profiling                 |
  
• |        6 | 0.62734100 | select count(*) from order_items   |
  
• +----------+------------+------------------------------------+
  
• 6 rows in set (0.00 sec)
  

　　

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

• mysql> show profile cpu, block io for query 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 rows in set (0.03 sec)
  

　　

　　下面列出了show profile的参数：
　　

• 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
  

　　

　　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 PROFILE FOR QUERY 6;
  
• 
  
• mysql>SELECT STATE, FORMAT(DURATION, 6) AS DURATION 　　
        FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 6 ORDER BY SEQ;