mysql profile的作用_mysql profile功能

2、变量profiling是用户变量，每次都得重新启用。

以下是我做的一些实验。数据很明显，就不多解释了。

mysql> use test

Database changed

mysql> set profiling=1;

Query OK, 0 rows affected (0.00 sec)

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)

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)

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> insert into t(username) select username from t;

Query OK, 2097152 rows affected (34.17 sec)

Records: 2097152  Duplicates: 0  Warnings: 0

mysql> show profiles;

+----------+-------------+------------------------------------------------+

| Query_ID | Duration    | Query                                          |

+----------+-------------+------------------------------------------------+

|        1 |  0.02717000 | show tables                                    |

|        2 |  0.74770100 | select count(*) from t                         |

|        3 |  0.00004200 | show prifile for query 2                       |

|        4 | 34.30410100 | insert into t(username) select username from t |

+----------+-------------+------------------------------------------------+

4 rows in set (0.00 sec)

mysql> show profile cpu,block io,memory,swaps for query 4;

mysql> select count(*) from t;

+----------+

| count(*) |

+----------+

|  4194304 |

+----------+

1 row in set (1.51 sec)

mysql> show profiles;

+----------+-------------+------------------------------------------------+

| Query_ID | Duration    | Query                                          |

+----------+-------------+------------------------------------------------+

|        1 |  0.02717000 | show tables                                    |

|        2 |  0.74770100 | select count(*) from t                         |

|        3 |  0.00004200 | show prifile for query 2                       |

|        4 | 34.30410100 | insert into t(username) select username from t |

|        5 |  1.50563800 | select count(*) from t                         |

+----------+-------------+------------------------------------------------+

5 rows in set (0.00 sec)

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

……

mysql> update t set username = 'waill';

Query OK, 4194304 rows affected (44.82 sec)

Rows matched: 4194304  Changed: 4194304  Warnings: 0

mysql> show profiles;

+----------+-------------+------------------------------------------------+ | Query_ID | Duration    | Query                                          | +----------+-------------+------------------------------------------------+ |        1 |  0.02717000 | show tables                                    |  |        2 |  0.74770100 | select count(*) from t                         |  |        3 |  0.00004200 | show prifile for query 2                       |  |        4 | 34.30410100 | insert into t(username) select username from t |  |        5 |  1.50563800 | select count(*) from t                         |  |        6 | 44.82054700 | update t set username = 'waill'                |  +----------+-------------+------------------------------------------------+ 6 rows in set (0.00 sec) mysql> show profile cpu,block io,memory,swaps,context switches,source for query 6;