mysql的profile_MySQL中如何使用profile分析SQL执行状态

打开profile

mysql> select @@have_profiling;

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

| @@have_profiling |

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

| YES              |

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

1 row in set (0.00 sec)

mysql> select @@profiling;

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

| @@profiling |

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

|           0 |

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

1 row in set (0.00 sec)

mysql> set session profiling=1;

Query OK, 0 rows affected (0.00 sec)

mysql> select @@profiling;

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

| @@profiling |

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

|           1 |

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

1 row in set (0.00 sec)

使用profile分析SQL，可以看到执行两次后,Send data和sending cached result to clien执行效率的变化

mysql> select count(*) from sakila.payment;

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

| count(*) |

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

|    16049 |

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

1 row in set (0.03 sec)

mysql> show profiles;

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

| Query_ID | Duration   | Query                               |

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

|        1 | 0.00020400 | select @@profiling                  |

|        2 | 0.00008900 | select count(*) from payment        |

|        3 | 0.00006800 | show databaes                       |

|        4 | 0.02102800 | show databases                      |

|        5 | 0.02847600 | select count(*) from sakila.payment |

本栏目更多精彩内容：http://www.bianceng.cn/database/MySQL/

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

5 rows in set (0.00 sec)

mysql> show profile for query 5;

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

| Status                         | Duration |

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

| starting                       | 0.000030 |

| Waiting for query cache lock   | 0.000005 |

| checking query cache for query | 0.000043 |

| checking permissions           | 0.000007 |

| Opening tables                 | 0.000027 |

| System lock                    | 0.000010 |

| Waiting for query cache lock   | 0.000010 |

| init                           | 0.000000 |

| optimizing                     | 0.023255 |

| statistics                     | 0.000118 |

| preparing                      | 0.000041 |

| executing                      | 0.000033 |

| Sending data                   | 0.003833 |

| end                            | 0.000054 |

| query end                      | 0.000045 |

| closing tables                 | 0.000045 |

| freeing items                  | 0.000072 |

| Waiting for query cache lock   | 0.000033 |

| freeing items                  | 0.000785 |

| Waiting for query cache lock   | 0.000016 |

| freeing items                  | 0.000002 |

| storing result in query cache  | 0.000005 |

| logging slow query             | 0.000003 |

| cleaning up                    | 0.000004 |

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

24 rows in set (0.00 sec)

mysql> select count(*) from sakila.payment;

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

| count(*) |

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

|    16049 |

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

1 row in set (0.00 sec)

mysql> show profiles;

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

| Query_ID | Duration   | Query                               |

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

|        1 | 0.00020400 | select @@profiling                  |

|        2 | 0.00008900 | select count(*) from payment        |

|        3 | 0.00006800 | show databaes                       |

|        4 | 0.02102800 | show databases                      |

|        5 | 0.02847600 | select count(*) from sakila.payment |

|        6 | 0.00006900 | select count(*) from sakila.payment |

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

6 rows in set (0.00 sec)

mysql> show profile for query 6;

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

| Status                         | Duration |

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

| starting                       | 0.000029 |

| Waiting for query cache lock   | 0.000004 |

| checking query cache for query | 0.000007 |

| checking privileges on cached  | 0.000004 |

| checking permissions           | 0.000008 |

| sending cached result to clien | 0.000012 |

| logging slow query             | 0.000002 |

| cleaning up                    | 0.000003 |

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

8 rows in set (0.00 sec)