Mysql性能分析利器Query Profiler 快速入门

Query Profiler是MYSQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的性能瓶颈在什么地方。

Query Profiler可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。

首先看一下是否开启此功能

mysql> show variables like '%profiling%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | have_profiling | YES | | profiling | OFF | | profiling_history_size | 15 | +------------------------+-------+ 3 rows in set (0.00 sec)

开启性能分析:

mysql> set profiling=1; Query OK, 0 rows affected, 1 warning (0.00 sec)

执行目标sql

mysql> update MPushLog set PUSHSTATUS=1, RESPCONTENT = '1a' where id=1500; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0

检测完记得关闭, 会影响sql执行性能:

mysql> set profiling=0; Query OK, 0 rows affected, 1 warning (0.00 sec)

查看执行时间表

mysql> show profiles; +----------+------------+----------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+----------------------------------------------------------------------+ | 1 | 0.00310800 | show variables like '%profiling%' | | 2 | 0.00018100 | update MPushLog set PUSHSTATUS=1, RESPCONTENT = '1a' where id=1500 | | 3 | 0.00033900 | SELECT DATABASE() | | 4 | 0.00904500 | show databases | | 5 | 0.00628200 | show tables | | 6 | 0.00282300 | update MPushLog set PUSHSTATUS=1, RESPCONTENT = '1a' where id=1500 | | 7 | 0.00035400 | update MPushLog set PUSHSTATUS=1, RESPCONTENT = '1a' where id=1500 | +----------+------------+----------------------------------------------------------------------+ 7 rows in set, 1 warning (0.00 sec)

如果你想看得更详细

mysql> show profile cpu ,swaps for query 1; +----------------------+----------+----------+------------+-------+ | Status | Duration | CPU_user | CPU_system | Swaps | +----------------------+----------+----------+------------+-------+ | starting | 0.000289 | 0.000079 | 0.000058 | 0 | | checking permissions | 0.000019 | 0.000015 | 0.000004 | 0 | | Opening tables | 0.000023 | 0.000028 | 0.000014 | 0 | | init | 0.000115 | 0.000080 | 0.000009 | 0 | | System lock | 0.000014 | 0.000010 | 0.000003 | 0 | | optimizing | 0.000006 | 0.000004 | 0.000003 | 0 | | optimizing | 0.000005 | 0.000003 | 0.000002 | 0 | | statistics | 0.000017 | 0.000014 | 0.000002 | 0 | | preparing | 0.000020 | 0.000018 | 0.000002 | 0 | | statistics | 0.000011 | 0.000009 | 0.000003 | 0 | | preparing | 0.000009 | 0.000006 | 0.000002 | 0 | | executing | 0.000022 | 0.000013 | 0.000009 | 0 | | Sending data | 0.000011 | 0.000008 | 0.000003 | 0 | | executing | 0.000017 | 0.000005 | 0.000013 | 0 | | Sending data | 0.002391 | 0.002004 | 0.000386 | 0 | | end | 0.000010 | 0.000004 | 0.000007 | 0 | | query end | 0.000008 | 0.000006 | 0.000001 | 0 | | closing tables | 0.000004 | 0.000002 | 0.000002 | 0 | | removing tmp table | 0.000049 | 0.000023 | 0.000027 | 0 | | closing tables | 0.000013 | 0.000009 | 0.000003 | 0 | | freeing items | 0.000028 | 0.000013 | 0.000016 | 0 | | cleaning up | 0.000027 | 0.000013 | 0.000014 | 0 | +----------------------+----------+----------+------------+-------+ 22 rows in set, 1 warning (0.00 sec)