mysql性能分析：mysql profiling 应用

1）先打开profiling ==> set profiling = 1;
mysql> set profiling = 1;
Query OK, 0 rows affected (0.00 sec)

2) 执行查query操作 mysql> select * from player where id = 12;

3）show profiles; 显示所有的profile 选择想查看的Query_ID
mysql> show profiles;
+———-+————+—————————————+
| Query_ID | Duration | Query |
+———-+————+—————————————+
| 1 | 0.00013125 | select * from player where id = 12 |
| 2 | 0.00017800 | SELECT DATABASE() |
| 3 | 0.00046525 | select * from player where id = 12 |
| 4 | 0.44358225 | select * from player where accid = 12 |
+———-+————+—————————————+
4 rows in set (0.00 sec)

4）查看具体的profiling

mysql> show profile cpu, block io for query 2;
+——————————–+———-+———-+————+————–+—————+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+——————————–+———-+———-+————+————–+—————+
| starting | 0.000048 | 0.000015 | 0.000009 | 0 | 0 |
| checking query cache for query | 0.000069 | 0.000038 | 0.000019 | 0 | 0 |
| Opening tables | 0.000019 | 0.000012 | 0.000006 | 0 | 0 |
| System lock | 0.000007 | 0.000005 | 0.000002 | 0 | 0 |
| Table lock | 0.000064 | 0.000042 | 0.000022 | 0 | 0 |
| init | 0.000046 | 0.000030 | 0.000016 | 0 | 0 |
| optimizing | 0.000014 | 0.000009 | 0.000005 | 0 | 0 |
| statistics | 0.000068 | 0.000045 | 0.000023 | 0 | 0 |
| preparing | 0.000022 | 0.000015 | 0.000007 | 0 | 0 |
| executing | 0.000009 | 0.000005 | 0.000003 | 0 | 0 |
| Sending data | 0.000125 | 0.002605 | 0.000000 | 0 | 0 |
| end | 0.000025 | 0.000007 | 0.000000 | 0 | 0 |
| query end | 0.000027 | 0.000012 | 0.000000 | 0 | 0 |
| freeing items | 0.000034 | 0.000019 | 0.000000 | 0 | 0 |
| storing result in query cache | 0.000016 | 0.000011 | 0.000000 | 0 | 0 |
| logging slow query | 0.000006 | 0.000004 | 0.000000 | 0 | 0 |
| cleaning up | 0.000010 | 0.000006 | 0.000000 | 0 | 0 |
+——————————–+———-+———-+————+————–+—————+
17 rows in set (0.00 sec)