本文介绍了MySQL从5.0.37版本开始引入的性能诊断工具Profiler，用于优化SQL语句。Profiler可以帮助定位SQL执行的性能瓶颈，如CPU、IO等资源消耗。通过设置`profiling=1`开启该功能，然后通过`show profiles`和`show profile`命令查看查询的详细执行时间和资源消耗情况。在完成分析后，建议关闭Profiler以节省资源。
在通过查看执行计划explain外，优化一条sql语句外的另一个诊断工具profiler，在5.0以后加入了mysql大家庭。其使用在后续的版本中得到逐步的丰富和扩展。以下的相关测试是在5.5版本中的测试，有不同之处请查看官网的相关文档说明为准。
要优化一条sql，需要遭到这条sql的性能瓶颈，找到消耗资源的地方在哪里，比如说，CPU,IO，OPS等等，在mysql5.0.37版本以后当中，可以使用profiling功能进行这些数据信息的定位。
其作为一种非常方便的性能诊断工具，可以获取各种资源的消耗数据情况。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
首先要开启profiling参数
set profiling=1 开启
set profiling=0 关闭（默认）
开启功能后，mysql机会记录所有的sql查询的profiler的信息；
其参数默认是0，关闭状态；
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT a.id,a.west_name,a.west_class_id,a.west_way, a.west_med_name,a.release_time,a.is_check,u.user_name,m.med_name
-> FROM msp.msp_western_medicine a
-> JOIN msp.msp_user u ON u.id=a.user_id
-> JOIN msp.msp_medcine_company m ON m.id=a.med_id
-> WHERE 1=1 AND a.is_check <> 2 AND a.west_class_id = '2' ORDER BY a.id DESC LIMIT 10;
+------+-----------+---------------+----------+---------------+---------------------+----------+---------------+----------------+
| id | west_name | west_class_id | west_way | west_med_name | release_time | is_check | user_name | med_name |
+------+-----------+---------------+----------+---------------+---------------------+----------+---------------+----------------+
| 2340 | ??? | 2 | 2 | NULL | 2012-11-22 13:22:20 | 1 | happywulingli | ????????????? |
| 2339 | ??? | 2 | 2 | NULL | 2012-11-22 13:23:07 | 1 | happywulingli | ???????????? |
| 2338 | ?????? | 2 | 2 | NULL | 2012-11-22 13:25:48 | 1 | happywulingli | ??????????? |
| 2337 | ????? | 2 | 2 | NULL | 2012-11-22 13:27:00 | 1 | happywulingli | ?????????? |
| 2336 | ?????? | 2 | 2 | NULL | 2012-11-22 17:12:06 | 1 | happywulingli | ???????????? |
| 2335 | ????? | 2 | 2 | NULL | 2012-11-22 17:12:18 | 1 | happywulingli | ?????????? |
| 2334 | ???? | 2 | 2 | NULL | 2012-11-22 17:12:29 | 1 | happywulingli | ???????????? |
| 2333 | ????? | 2 | 2 | NULL | 2012-11-22 17:12:44 | 1 | happywulingli | ??????(??)???? |
| 2332 | ???? | 2 | 2 | NULL | 2012-11-22 17:13:13 | 1 | happywulingli | ?????????????? |
| 2331 | ???? | 2 | 2 | NULL | 2012-11-22 17:13:24 | 1 | happywulingli | ???(??)?????? |
+------+-----------+---------------+----------+---------------+---------------------+----------+---------------+----------------+
10 rows in set (0.00 sec)
mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.00119825 | SELECT a.id,a.west_name,a.west_class_id,a.west_way, a.west_med_name,a.release_time,a.is_check,u.user_name,m.med_name
FROM msp.msp_western_medicine a
JOIN msp.msp_user u ON u.id=a.user_id
JOIN msp.msp_medcine_company m ON m.id=a.med_id
WHERE 1=1 AND a.is_check <> 2 AND a.west_class_id = '2' ORDER |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
query 1就是我们上面看到的Query_ID的编号信息，以下只是统计了CPU,IPS等IO消耗，其他相关统计可以参照上面提到的相关语法选择说明即可。
mysql> show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000138 | 0.000046 | 0.000086 | 0 | 0 |
| checking permissions | 0.000002 | 0.000003 | 0.000007 | 0 | 0 |
| checking permissions | 0.000002 | 0.000003 | 0.000004 | 0 | 0 |
| checking permissions | 0.000002 | 0.000003 | 0.000008 | 0 | 0 |
| Opening tables | 0.000136 | 0.000040 | 0.000075 | 0 | 0 |
| System lock | 0.000065 | 0.000023 | 0.000042 | 0 | 0 |
| init | 0.000072 | 0.000024 | 0.000048 | 0 | 0 |
| optimizing | 0.000035 | 0.000012 | 0.000021 | 0 | 0 |
| statistics | 0.000044 | 0.000015 | 0.000029 | 0 | 0 |
| preparing | 0.000028 | 0.000010 | 0.000018 | 0 | 0 |
| executing | 0.000002 | 0.000002 | 0.000006 | 0 | 0 |
| Sorting result | 0.000002 | 0.000004 | 0.000007 | 0 | 0 |
| Sending data | 0.000596 | 0.000201 | 0.000381 | 0 | 0 |
| end | 0.000002 | 0.000004 | 0.000007 | 0 | 0 |
| query end | 0.000002 | 0.000004 | 0.000006 | 0 | 0 |
| closing tables | 0.000036 | 0.000005 | 0.000012 | 0 | 0 |
| freeing items | 0.000031 | 0.000012 | 0.000020 | 0 | 0 |
| logging slow query | 0.000002 | 0.000002 | 0.000006 | 0 | 0 |
| cleaning up | 0.000002 | 0.000004 | 0.000007 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
19 rows in set (0.00 sec)
mysql>
以上可以清楚的查看到CPU,IO等消耗，对于定位性能瓶颈非常方便，在用完后，建议关闭profiler；
mysql> set profiling=0
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql>
扫一扫
653
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
