查看profiling是否开启:
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|0 |
+-------------+
1 row in set (0.00 sec)


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

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|1 |
+-------------+
1 row in set (0.00 sec)

执行若干条SQL查询:
mysql> use mysql
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)

mysql> select host,user,Password from user limit 10;
+-----------+-----------------+-------------------------------------------+
| host | user| Password |
+-----------+-----------------+-------------------------------------------+
| localhost | root| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| % | chenfeng| *716E7D76E850A91A8311F35B6BFB1213B751F230 |
| % | test@localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| % | sa@localhost| *4D0DD2673C1DE57138354E81A957460B774C4BC2 |
| % | admin@localhost | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| localhost | dsf | *AFED783E50FCEF7B1C89AC89C6E1A0405BD7F313 |
| localhost | dsf1| *98D1D8D997EA32BABDFEEC2EBB62BB6D010036C6 |
| % | dsf2| *0007B182797FAD2A8A37C3F73A011BBD36C49857 |
+-----------+-----------------+-------------------------------------------+
8 rows in set (0.00 sec)

查询上述语句的Profiling情况:
mysql> show profiles;
+----------+------------+----------------------------------------------+
| Query_ID | Duration| Query|
+----------+------------+----------------------------------------------+
|1 | 0.00012600 | select @@profiling|
|2 | 0.00018450 | SELECT DATABASE()|
|3 | 0.00011950 | select database()|
|4 | 0.11296025 | show columns from user|
|5 | 0.00038725 | select host,user,Password from user limit 10 |
+----------+------------+----------------------------------------------+
14 rows in set (0.00 sec)

查看Query_ID=5语句的I/O消耗情况：
mysql> show profile block io for query 5;
+--------------------------------+----------+--------------+---------------+
| Status | Duration | Block_ops_in | Block_ops_out |
+--------------------------------+----------+--------------+---------------+
| starting| 0.000018 | NULL | NULL |
| Waiting for query cache lock| 0.000003 | NULL | NULL |
| checking query cache for query | 0.000077 | NULL | NULL |
| checking permissions| 0.000009 | NULL | NULL |
| Opening tables | 0.000020 | NULL | NULL |
| System lock| 0.000014 | NULL | NULL |
| init| 0.000020 | NULL | NULL |
| optimizing | 0.000005 | NULL | NULL |
| statistics | 0.000011 | NULL | NULL |
| preparing | 0.000009 | NULL | NULL |
| executing | 0.000003 | NULL | NULL |
| Sending data| 0.000071 | NULL | NULL |
| end| 0.000004 | NULL | NULL |
| query end | 0.000002 | NULL | NULL |
| closing tables | 0.000011 | NULL | NULL |
| freeing items | 0.000101 | NULL | NULL |
| logging slow query | 0.000006 | NULL | NULL |
| cleaning up| 0.000004 | NULL | NULL |
+--------------------------------+----------+--------------+---------------+
18 rows in set (0.00 sec)

查看Query_ID=5语句的CPU消耗情况：
mysql> show profile cpu for query 5;
+--------------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting| 0.000018 | 0.000000 |0.000000 |
| Waiting for query cache lock| 0.000003 | 0.000000 |0.000000 |
| checking query cache for query | 0.000077 | 0.000000 |0.000000 |
| checking permissions| 0.000009 | 0.000000 |0.000000 |
| Opening tables | 0.000020 | 0.000000 |0.000000 |
| System lock| 0.000014 | 0.000000 |0.000000 |
| init| 0.000020 | 0.000000 |0.000000 |
| optimizing | 0.000005 | 0.000000 |0.000000 |
| statistics | 0.000011 | 0.000000 |0.000000 |
| preparing | 0.000009 | 0.000000 |0.000000 |
| executing | 0.000003 | 0.000000 |0.000000 |
| Sending data| 0.000071 | 0.000000 |0.000000 |
| end| 0.000004 | 0.000000 |0.000000 |
| query end | 0.000002 | 0.000000 |0.000000 |
| closing tables | 0.000011 | 0.000000 |0.000000 |
| freeing items | 0.000101 | 0.000000 |0.000000 |
| logging slow query | 0.000006 | 0.000000 |0.000000 |
| cleaning up| 0.000004 | 0.000000 |0.000000 |
+--------------------------------+----------+----------+------------+
18 rows in set (0.00 sec)

mysql>