如何看mysql执行的sql语句

Question

如题所述

Answer 1

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)

Answer 2

SQL 语句摘要可以用在MySQL的各个方面，比如 性能字典里对语句的分析，查询重写插件规则改写等等。
接下来依次看下语句摘要在这两方面的使用。
1. 性能字典
mysql> call sys.ps_setup_enable_consumer('statements');
+---------------------+
| summary |
+---------------------+
| Enabled 4 consumers |
+---------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
开启后，执行几次之前的几条 SQL。
完后可以很方便的从 sys 库里分析这类语句的执行情况，包括执行次数，执行时间，扫描的记录数，锁定的时间，是否用到排序等等。

2. 查询重写插件
比如要阻止对表 p1 通过字段 r1 的删除动作，可以用查询重写插件在 MySQL 语句分析层直接转换，这时候就得用到摘要函数 statement_digest_text。
假设：表 p1 字段 id 值全部为正。
delete from p1 where id = 1000;

要改写为，
delete from p1 where id = -1;

利用函数 statement_digest_text 来定制这条 SQL 的重写规则。
mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement,pattern_database) -> VALUES( -> statement_digest_text('delete from p1 where id = 1000') , -> statement_digest_text('delete from p1 where id = -1'), -> 'ytt' -> );Query OK, 1 row affected (0.01 sec)
语句被查询重写后的效果：
mysql> delete from p1 where id = 20000;Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings\G*************************** 1. row *************************** Level: Note Code: 1105Message: Query 'delete from p1 where id = 20000' rewritten to 'DELETE FROM `p1` WHERE `id` = - 20000' by a query rewrite plugin1 row in set (0.00 sec)mysql> select count(*) from p1;+----------+| count(*) |+----------+| 9000001 |+----------+1 row in set (1.59 sec)
总结
MySQL 8.0 新增的语句摘要函数可以很方便的分析 SQL 语句执行的各个方面，比以前分析类似的场景要简单的多。

Answer 3

用show processlist；就可以看到正在执行的进程

Answer 4

如果是想看在执行的所有SQL，可以开启MySQL General Log，监测所有SQL请求（生产环境不建议长时间开启，分析任务完成后可关闭general_log）

管理员登陆

$ mysql -u root -p

#设置存放路径

set global general_log_file=/data/log/mysql_general_log.log;

#设置开启general log, 1开启（0关闭）,即时生效,不用重启MySQL

set global general_log=1;

想看慢查询的话可以开启慢查询日志，生产环境可以开启。

启用慢查询日志记录 （建议生产环境开启）

vim /etc/mysql/mariadb.conf.d/50-server.cnf

或者 vim /etc/mysql/my.cnf

slow_query_log=1

log-queries-not-using-indexes

long_query_time=1

log-slow-queries=/data/log/mysql/log-slow-queries.log 

超过1s的都认为是慢查询。 开启慢查询以后，可以在慢查询日志文件中查看运行慢的SQL，

$ tail -f /data/log/mysql/log-slow-queries.log

详情参考：MySQL查询监测