MySQL中怎样行使profile说明SQL执行状态

sending data比较耗费cpu

mysql> select min(seq) seq,state,count(*) numb_ops,

->     round(sum(duration),5) sum_dur, round(avg(duration),5) avg_dur,

->     round(sum(cpu_user),5) sum_cpu, round(avg(cpu_user),5) avg_cpu

->     from information_schema.profiling

->     where query_id = 5

->     group by state

->     order by seq;

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

| seq  | state                          | numb_ops | sum_dur | avg_dur | sum_cpu | avg_cpu |

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

|    2 | starting                       |        1 | 0.00003 | 0.00003 | 0.00000 | 0.00000 |

|    3 | Waiting for query cache lock   |        4 | 0.00006 | 0.00002 | 0.00100 | 0.00025 |

|    4 | checking query cache for query |        1 | 0.00004 | 0.00004 | 0.00000 | 0.00000 |

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

|    5 | checking permissions           |        1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |

|    6 | Opening tables                 |        1 | 0.00003 | 0.00003 | 0.00000 | 0.00000 |

|    7 | System lock                    |        1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |

|    9 | init                           |        1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |

|   10 | optimizing                     |        1 | 0.02326 | 0.02326 | 0.00000 | 0.00000 |

|   11 | statistics                     |        1 | 0.00012 | 0.00012 | 0.00000 | 0.00000 |

|   12 | preparing                      |        1 | 0.00004 | 0.00004 | 0.00000 | 0.00000 |

|   13 | executing                      |        1 | 0.00003 | 0.00003 | 0.00000 | 0.00000 |

|   14 | Sending data                   |        1 | 0.00383 | 0.00383 | 0.00300 | 0.00300 |

|   15 | end                            |        1 | 0.00005 | 0.00005 | 0.00000 | 0.00000 |

|   16 | query end                      |        1 | 0.00005 | 0.00005 | 0.00000 | 0.00000 |

|   17 | closing tables                 |        1 | 0.00005 | 0.00005 | 0.00000 | 0.00000 |

|   18 | freeing items                  |        3 | 0.00086 | 0.00029 | 0.00000 | 0.00000 |

|   23 | storing result in query cache  |        1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |

|   24 | logging slow query             |        1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |

|   25 | cleaning up                    |        1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |

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

19 rows in set (0.00 sec)

mysql> show profile cpu for query 5;

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

| Status                         | Duration | CPU_user | CPU_system |

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

| starting                       | 0.000030 | 0.000000 |   0.000000 |

| Waiting for query cache lock   | 0.000005 | 0.000000 |   0.000000 |

| checking query cache for query | 0.000043 | 0.000000 |   0.000000 |

| checking permissions           | 0.000007 | 0.000000 |   0.000000 |

| Opening tables                 | 0.000027 | 0.000000 |   0.000000 |

| System lock                    | 0.000010 | 0.000000 |   0.000000 |

| Waiting for query cache lock   | 0.000010 | 0.001000 |   0.000000 |

| init                           | 0.000000 | 0.000000 |   0.000000 |

| optimizing                     | 0.023255 | 0.000000 |   0.000000 |

| statistics                     | 0.000118 | 0.000000 |   0.000000 |

| preparing                      | 0.000041 | 0.000000 |   0.000000 |

| executing                      | 0.000033 | 0.000000 |   0.000000 |

| Sending data                   | 0.003833 | 0.002999 |   0.001000 |

| end                            | 0.000054 | 0.000000 |   0.000000 |

| query end                      | 0.000045 | 0.000000 |   0.000000 |

| closing tables                 | 0.000045 | 0.000000 |   0.000000 |

| freeing items                  | 0.000072 | 0.000000 |   0.000000 |

| Waiting for query cache lock   | 0.000033 | 0.000000 |   0.000000 |

| freeing items                  | 0.000785 | 0.000000 |   0.000000 |

| Waiting for query cache lock   | 0.000016 | 0.000000 |   0.000000 |

| freeing items                  | 0.000002 | 0.000000 |   0.000000 |

| storing result in query cache  | 0.000005 | 0.000000 |   0.000000 |

| logging slow query             | 0.000003 | 0.000000 |   0.000000 |

| cleaning up                    | 0.000004 | 0.000000 |   0.000000 |

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

24 rows in set (0.00 sec)

mysql> show profile block io for query 5;

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

| Status                         | Duration | Block_ops_in | Block_ops_out |

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

| starting                       | 0.000030 |            0 |             0 |

| Waiting for query cache lock   | 0.000005 |            0 |             0 |

| checking query cache for query | 0.000043 |            0 |             0 |

| checking permissions           | 0.000007 |            0 |             0 |

| Opening tables                 | 0.000027 |            0 |             0 |

| System lock                    | 0.000010 |            0 |             0 |

| Waiting for query cache lock   | 0.000010 |            0 |             0 |

（编辑：济南站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!