要想优化一条Query，我们就需要清楚的知道这条Query的性能瓶颈到底在哪里，是消耗的CPU计算太多，还是需要的的IO操作太多？
MySQL的Query Profiler是一个使用非常方便的Query诊断分析工具，通过该工具可以获取一条Query在整个执行过程中多种资源的消耗情况，如CPU，IO，IPC，SWAP等，以及发生的PAGE FAULTS，CONTEXT SWITCHE等等，同时还能得到该Query执行过程中MySQL所调用的各个函数在源文件中的位置。
1、打开profiling参数
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
2、查询sql语句
mysql> select count(*) from t_crcs_check_diff;
+----------+
| count(*) |
+----------+
| 12608 |
+----------+
1 row in set (0.01 sec)
3、获取系统中保存的所有Query的profile概要信息
mysql> show profiles;
+----------+------------+----------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------+
| 1 | 0.00673850 | select count(*) from t_crcs_check_diff |
+----------+------------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)
4、针对单个Query获取详细的profile信息
mysql> show profile cpu, block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000163 | 0.000042 | 0.000025 | 0 | 0 |
| checking permissions | 0.000008 | 0.000005 | 0.000003 | 0 | 0 |
| Opening tables | 0.000027 | 0.000017 | 0.000010 | 0 | 0 |
| init | 0.000027 | 0.000017 | 0.000010 | 0 | 0 |
| System lock | 0.000012 | 0.000008 | 0.000004 | 0 | 0 |
| optimizing | 0.000006 | 0.000003 | 0.000002 | 0 | 0 |
| statistics | 0.000018 | 0.000012 | 0.000007 | 0 | 0 |
| preparing | 0.000016 | 0.000010 | 0.000006 | 0 | 0 |
| executing | 0.000008 | 0.000004 | 0.000003 | 0 | 0 |
| Sending data | 0.006369 | 0.006372 | 0.000000 | 0 | 0 |
| end | 0.000014 | 0.000011 | 0.000000 | 0 | 0 |
| query end | 0.000011 | 0.000010 | 0.000000 | 0 | 0 |
| closing tables | 0.000009 | 0.000010 | 0.000000 | 0 | 0 |
| freeing items | 0.000027 | 0.000027 | 0.000000 | 0 | 0 |
| cleaning up | 0.000025 | 0.000025 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)
5、关闭profiling
mysql> set profiling=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)