查看正在执行的mysql指令
来源:互联网 发布:微表情心理学软件 编辑:程序博客网 时间:2024/06/05 16:51
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)
原文出处
- 查看正在执行的mysql指令
- Mysql 查看正在执行的sql语句
- 查看mysql正在执行的SQL语句
- 查看mysql正在执行的SQL语句
- mysql查看正在执行的sql语句
- mysql查看正在执行的sql
- 查看mysql正在执行的SQL语句
- Mysql查看正在执行的进程
- mysql -- 查看mysql正在执行的SQL语句
- MySQL 查看正在执行的sql语句的方法
- 通过日志查看mysql正在执行的SQL语句
- 通过日志查看MySQL正在执行的SQL语句
- 查看正在执行的SQL
- 查看mysql数据库正在执行的sql 配置执行sql packet最大值 mysql添加删除主键
- 查看mysql正在执行的SQL语句,使用profile分析SQL执行状态
- sqlserver 查看数据库正在执行的命令
- 查看sqlserver当前正在执行的语句
- 查看正在执行的事务、锁、等待
- poj-3397【线段树区间合并】
- 如何利用nginx或tomcat搭建文件服务器
- 第七节表格表单及样式重置、特性
- View的onTouchEvent、setTouchListener、OnClick和setClickListener四者的区别
- 数据库的基本操作之一些基本语句
- 查看正在执行的mysql指令
- Arduino—电机控制
- meta标签中的http-equiv属性使用介绍
- 链栈
- cordova Process finished with exit code -1
- JVM学习01——内存区域及内存溢出
- Spring Boot 之 Scheduling Tasks定时任务
- JavaSE 俄罗斯方块
- python安装scrapy框架