mysql查看sql执行计划命令_Mysql利用profiles来查看sql 语句执行计划

Mysql利用profiles来查看sql语句执行计划

要使用该功能，mysql的版本必须在

5.0.37版本以上。否则只能使用explain的方式来检查。

profiling功能可以了解到cpu io等更详细的信息。

show profile的格式如下：

SHOW PROFILE [type [, type] ... ]

[FOR QUERY n]

[LIMIT row_count [OFFSET offset]]

type:

ALL  | BLOCK IO  | CONTEXT SWITCHES  | CPU  | IPC  | MEMORY  | PAGE FAULTS  | SOURCE  | SWAPS

默认方式下该功能是关闭的。

查看PROFILE的初始状态：0表示关闭，1表示开放。

mysql> select @@profiling;

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

| @@profiling |

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

|           0 |

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

1 row in set (0.00 sec)

打开profile功能

mysql>set profiling=1;

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

| @@profiling |

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

|           1 |

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

1 row in set (0.00 sec)

执行希望查看PROFILE信息的SQL。

mysql> select count(name) from wb_company;

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

| count(name) |

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

|     2674816 |

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

1 row in set (0.00 sec)

查询当然缓冲区中被缓冲PROFILE信息的SQL语句列表：

mysql> show profiles;

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

| Query_ID | Duration   | Query                                             |

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

|        1 | 0.00015425 | select count(name) from wb_company                |

|        2 | 0.00152100 | select name,address from wb_company limit 100,200 |

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

2 rows in set (0.00 sec)

通过指定的Query_ID来查询指定的sql语句的执行信息：

mysql> show profile for query 2;

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

| Status                         | Duration |

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

| starting                       | 0.000017 |

| checking query cache for query | 0.000043 |

| Opening tables                 | 0.001014 |

| System lock                    | 0.000003 |

| Table lock                     | 0.000024 |

| init                           | 0.000010 |

| optimizing                     | 0.000002 |

| statistics                     | 0.000006 |

| preparing                      | 0.000005 |

| executing                      | 0.000002 |

| Sending data                   | 0.000265 |

| end                            | 0.000003 |

| query end                      | 0.000002 |

| freeing items                  | 0.000056 |

| storing result in query cache  | 0.000004 |

| logging slow query             | 0.000001 |

| logging slow query             | 0.000063 |

| cleaning up                    | 0.000003 |

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

18 rows in set (0.00 sec)

如果不带for参数则指列出最后一条语句的profile信息。

mysql> show profile cpu,block io for query 2;

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

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

| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |

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

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

| starting                       | 0.000017 |     NULL |       NULL |         NULL |          NULL |

| checking query cache for query | 0.000043 |     NULL |       NULL |         NULL |          NULL |

| Opening tables                 | 0.001014 |     NULL |       NULL |         NULL |          NULL |

| System lock                    | 0.000003 |     NULL |       NULL |         NULL |          NULL |

| Table lock                     | 0.000024 |     NULL |       NULL |         NULL |          NULL |

| init                           | 0.000010 |     NULL |       NULL |         NULL |          NULL |

| optimizing                     | 0.000002 |     NULL |       NULL |         NULL |          NULL |

| statistics                     | 0.000006 |     NULL |       NULL |         NULL |          NULL |

| preparing                      | 0.000005 |     NULL |       NULL |         NULL |          NULL |

| executing                      | 0.000002 |     NULL |       NULL |         NULL |          NULL |

| Sending data                   | 0.000265 |     NULL |       NULL |         NULL |          NULL |

| end                            | 0.000003 |     NULL |       NULL |         NULL |          NULL |

| query end                      | 0.000002 |     NULL |       NULL |         NULL |          NULL |

| freeing items                  | 0.000056 |     NULL |       NULL |         NULL |          NULL |

| storing result in query cache  | 0.000004 |     NULL |       NULL |         NULL |          NULL |

| logging slow query             | 0.000001 |     NULL |       NULL |         NULL |          NULL |

| logging slow query             | 0.000063 |     NULL |       NULL |         NULL |          NULL |

| cleaning up                    | 0.000003 |     NULL |       NULL |         NULL |          NULL |

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

18 rows in set (0.00 sec)

关闭PROFILE功能：

mysql> set profiling=0;

Query OK, 0 rows affected (0.00 sec)

mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ |           0 | +-------------+ 1 row in set (0.00 sec)