查看正在执行的mysql指令

1)我们先通过status命令查看Mysql运行状态

mysql> status;

--------------

mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

 

Connection id: 113752

Current database: information_schema

Current user: push_user@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.1.73 Source distribution

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db     characterset: utf8

Client characterset: latin1

Conn.  characterset: latin1

UNIX socket: /tmp/mysql.sock

Uptime: 22 days 8 hours 31 min 23 sec

 

Threads: 38  Questions: 1037751897  Slow queries: 2356  Opens: 79836  Flush tables: 1  Open tables: 64  Queries per second avg: 537.282

--------------

在上面显示列表的最后一条，我们来查看Slow queries这一项的值，如果多次查看的值大于0的话,说明有些查询sql命令执行时间过长。

 

2)这时再通过show processlist命令来查看当前正在运行的SQL，从中找出运行慢的SQL语句，找到执行慢的语句后，再用explain命令查看这些语句的执行计划。

 

mysql> show processlist;

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

| Id     | User      | Host                | db                 | Command | Time  | State | Info             |

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

|  50270 | ambari    | DataBase-01:41512   | ambari             | Sleep   |    23 |       | NULL             |

|  50271 | ambari    | DataBase-01:41511   | ambari             | Sleep   |     6 |       | NULL             |

|  50272 | ambari    | DataBase-01:41514   | ambari             | Sleep   |    23 |       | NULL             |

|  62452 | oozie     | DataBase-02:42987   | oozie              | Sleep   |    25 |       | NULL             |

|  63660 | ambari    | DataBase-01:56052   | ambari             | Sleep   |     0 |       | NULL             |

| 110404 | push_user | localhost:33817     | quartz             | Sleep   |    12 |       | NULL             |

| 112835 | push_user | localhost:46571     | hibernate          | Sleep   |     1 |       | NULL             |

| 113163 | push_user | localhost:56585     | hibernate          | Sleep   |     1 |       | NULL             |

| 113289 | push_user | 14.118.132.20:47333 | DW                 | Sleep   |   628 |       | NULL             |

| 113320 | push_user | localhost:47428     | hibernate          | Sleep   |     3 |       | NULL             |

| 113321 | push_user | localhost:47429     | hibernate          | Sleep   |     3 |       | NULL             |

| 113322 | push_user | localhost:47430     | hibernate          | Sleep   |     3 |       | NULL             |

| 113357 | push_user | localhost:52337     | hibernate          | Sleep   |     3 |       | NULL             |

| 113364 | push_user | localhost:57206     | hibernate          | Sleep   |     3 |       | NULL             |

| 113366 | push_user | localhost:34813     | hibernate          | Sleep   |     1 |       | NULL             |

| 113398 | push_user | localhost:37382     | hibernate          | Sleep   |     1 |       | NULL             |

| 113498 | push_user | localhost:47626     | quartz             | Sleep   | 12717 |       | NULL             |

| 113709 | push_user | localhost:59382     | hibernate          | Sleep   |     1 |       | NULL             |

| 113710 | push_user | localhost:33627     | hibernate          | Sleep   |     1 |       | NULL             |

| 113715 | hive      | DataBase-02:54968   | hive               | Sleep   |  2390 |       | NULL             |

| 113716 | hive      | DataBase-02:54969   | hive               | Sleep   |  2390 |       | NULL             |

| 113717 | hive      | DataBase-02:54974   | hive               | Sleep   |  2336 |       | NULL             |

| 113718 | hive      | DataBase-02:54975   | hive               | Sleep   |  2336 |       | NULL             |

| 113719 | push_user | localhost:48243     | hibernate          | Sleep   |     1 |       | NULL             |

| 113720 | push_user | localhost:48245     | hibernate          | Sleep   |     1 |       | NULL             |

| 113721 | push_user | localhost:48244     | hibernate          | Sleep   |     1 |       | NULL             |

| 113722 | push_user | localhost:48247     | hibernate          | Sleep   |     1 |       | NULL             |

| 113723 | push_user | localhost:48249     | hibernate          | Sleep   |     1 |       | NULL             |

| 113724 | push_user | localhost:48248     | hibernate          | Sleep   |     1 |       | NULL             |

| 113745 | push_user | localhost:50684     | hibernate          | Sleep   |     1 |       | NULL             |

| 113746 | push_user | localhost:50685     | hibernate          | Sleep   |     1 |       | NULL             |

| 113747 | push_user | localhost:50695     | hibernate          | Sleep   |     1 |       | NULL             |

| 113748 | push_user | localhost:50696     | hibernate          | Sleep   |     1 |       | NULL             |

| 113749 | push_user | localhost:50697     | hibernate          | Sleep   |     1 |       | NULL             |

| 113750 | push_user | localhost:50699     | hibernate          | Sleep   |     1 |       | NULL             |

| 113751 | push_user | localhost:50700     | hibernate          | Sleep   |     1 |       | NULL             |

| 113752 | push_user | localhost           | information_schema | Query   |     0 | NULL  | show processlist |

| 113753 | push_user | 14.118.132.20:28688 | DW                 | Sleep   |   396 |       | NULL             |

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

38 rows in set (0.00 sec)

或者通过如下命令查询:

mysql> use information_schema;

mysql> select * from PROCESSLIST where info is not null;

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

| ID     | USER      | HOST      | DB                 | COMMAND | TIME | STATE     | INFO                                             |

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

| 113752 | push_user | localhost | information_schema | Query   |    0 | executing | select * from PROCESSLIST where info is not null |

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

 

1 row in set (0.00 sec)

原文出处