MySQL查询优化之profile

MySQL5.0.37版本以上支持PROFILING调试功能，可以了解SQL语句消耗资源的详细信息。因为它需要调用系统的getrusage()函数，所以只是在Linux/Unix类平台上才能使用，而不能在Windows平台上使用。而且，PROFILING是针对处理进程(process)而不是线程(thread)的，服务器上的其他应用，可能会影响调试结果，因此，这个工具适合开发过程中的调试，如果要在生产环境中调试使用，则要注意它的局限性。

1、来查看是否已经启用profile

mysql> select @@PROFILING;
+-------------+
| @@PROFILING |
+-------------+
| 0 |
+-------------+
1 row in set (0.03 sec)

如果profilng值为0，可以通过

SET profiling = 1;

来启用。启用profiling之后，我们执行一条查询语句，比如：

mysql> SELECT * FROM mysql.user;

然后show profiles查看如下：

mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------+
| 1 | 0.00015400 | select @@PROFILING |
| 2 | 0.00160000 | SELECT * FROM mysql.user |
+----------+------------+--------------------------------+
2 rows in set (0.00 sec)

2 rows in set (0.00 sec)

其中ID为2的语句是刚执行的查询语句

2、变量profiling是用户变量，每次都得重新启用。
下面是profile的常用功能：

mysql> use test
Database changed

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

现在开始执行需要测试的SQL语句，MySQL数据库将会记录想关的调试信息
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| bag_item | 
| bag_user | 
| score | 
| t | 
+----------------+
4 rows in set (0.03 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 2097152 | 
+----------+
1 row in set (0.74 sec)

显示当前所有已经记录的PROFILES：
mysql> show profiles;
+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.02717000 | show tables            | 
|        2 | 0.74770100 | select count(*) from t | 
+----------+------------+------------------------+
2 rows in set (0.00 sec)

某个特定查询的耗时，对应上面profiles中的Query_ID：
mysql> show profile for query 2;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| (initialization)               | 0.000004 | 
| checking query cache for query | 0.000044 | 
| Opening tables                 | 0.000012 | 
| System lock                    | 0.000017 | 
| Table lock                     | 0.00003  | 
| init                           | 0.000013 | 
| optimizing                     | 0.000008 | 
| statistics                     | 0.000013 | 
| preparing                      | 0.000011 | 
| executing                      | 0.000006 | 
| Sending data                   | 0.747313 | 
| end                            | 0.000014 | 
| query end                      | 0.000006 | 
| storing result in query cache  | 0.000006 | 
| freeing items                  | 0.000012 | 
| closing tables                 | 0.000009 | 
| logging slow query             | 0.000183 | 
+--------------------------------+----------+
17 rows in set (0.00 sec)

其他的一些具体的参数：
mysql> show profile block io,cpu for query 2;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| (initialization)               | 0.000004 | 0        | 0          |            0 |             0 | 
| checking query cache for query | 0.000044 | 0        | 0          |            0 |             0 | 
| Opening tables                 | 0.000012 | 0        | 0          |            0 |             0 | 
| System lock                    | 0.000017 | 0        | 0          |            0 |             0 | 
| Table lock                     | 0.00003  | 0        | 0          |            0 |             0 | 
| init                           | 0.000013 | 0        | 0          |            0 |             0 | 
| optimizing                     | 0.000008 | 0        | 0          |            0 |             0 | 
| statistics                     | 0.000013 | 0        | 0          |            0 |             0 | 
| preparing                      | 0.000011 | 0        | 0          |            0 |             0 | 
| executing                      | 0.000006 | 0        | 0          |            0 |             0 | 
| Sending data                   | 0.747313 | 0.746887 | 0          |            0 |             0 | 
| end                            | 0.000014 | 0        | 0          |            0 |             0 | 
| query end                      | 0.000006 | 0        | 0          |            0 |             0 | 
| storing result in query cache  | 0.000006 | 0        | 0          |            0 |             0 | 
| freeing items                  | 0.000012 | 0        | 0          |            0 |             0 | 
| closing tables                 | 0.000009 | 0        | 0          |            0 |             0 | 
| logging slow query             | 0.000183 | 0        | 0          |            0 |             0 | 
+--------------------------------+----------+----------+------------+--------------+---------------+
17 rows in set (0.00 sec)

mysql> show profile cpu,block io,memory,swaps,context switches,source for query 2;
……