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

Answer 1

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