MySQL中如何使用profile分析SQL执行状态

操作方法

• 01

  打开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)