数据库查询并修改数据

对 MySQL 查询重写以提高性能是每个 DBA 应该意识到的重要过程，以便他们可以在运行时修复错误的查询，而无需在应用程序端更改代码。

到目前为止，MySQL 社区提供了两个内置的查询重写插件来执行此任务。

query rewriter plugin：它支持 INSERT / UPDATE / DELETE / REPLACE 语句，在  MySQL 8.0.12 引入。

ddl_rewritter plugin：它支持 CREATE TABLE 语句。在 MySQL 8.0.16 引入。

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)

---