提高mysql客户端使用效率的2个特性

2020-04-17 10:38:53

本文介绍mysql这个MySQL客户端登录工具的2个特性edit和pager，可以提高我们使用mysql进行MySQL查询、问题分析和定位的效率。

一. edit

有时候我们可能会从其他地方拷贝一些sql语句，并进行改动。默认情况下，在mysql中进行换行后，前一行的数据就无法更改。所以，如果粘贴的sql语句是多行模式，就无法随心所欲得进行更改。这个时候edit就可以派上用场。

通过输入edit或“\e”：

node1>create table edittest (a int);
Query OK,  rows affected (0.00 sec)

node1>show create table edittest;
+----------+----------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                 |
+----------+----------------------------------------------------------------------------------------------+
| edittest | CREATE TABLE `edittest` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

node1>\e

回车后会新建并打开临时文件：

临时文件中默认会有后一次执行的sql语句，案例中为“show create table edittest”。我们可以在上面像用vi打开文件一样进行修改（改为“create table test1 (a int primary key);”）并通过“wq”保存，重新回到sql输入界面：

node1>\e
    -> ;
Query OK,  rows affected (0.00 sec)
node1>show create table test1;
+-------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

使用“;”收尾后换行，语句被成功执行。

有了edit特性，我们可以自由得进行sql语句的拷贝、粘贴和编辑。

二. pager

pager是更加强大的工具。相当于在我们要执行的sql结果上增加一到多个管道。每个管道均可进行结果的流水线处理。使用方法很简单，只需要mysql客户端输入关键词pager并加上所需的linux shell命令即可。

show processlist

下面举几个show processlist相关的例子，这是大家经常需要执行的命令：

node1>show processlist;
+----+---------------+-----------+--------------------+---------+------+---------------+--------------------------------------------------------+------------------+
| Id | User          | Host      | db                 | Command | Time | ACK_WAIT_TIME | State                                                  | Info             |
+----+---------------+-----------+--------------------+---------+------+---------------+--------------------------------------------------------+------------------+
|  7 | system user   |           | NULL               | Connect |  165 |             0 | executing                                              | NULL             |
| 10 | system user   |           | NULL               | Connect |  136 |             0 | Slave has read all relay log; waiting for more updates | NULL             |
| 11 | system user   |           | NULL               | Connect |  165 |             0 | Waiting for an event from Coordinator                  | NULL             |
| 12 | system user   |           | NULL               | Connect |  165 |             0 | Waiting for an event from Coordinator                  | NULL             |
| 13 | system user   |           | NULL               | Connect |  165 |             0 | Waiting for an event from Coordinator                  | NULL             |
| 14 | system user   |           | NULL               | Connect |  165 |             0 | Waiting for an event from Coordinator                  | NULL             |
| 15 | system user   |           | NULL               | Connect |  165 |             0 | Waiting for an event from Coordinator                  | NULL             |
| 16 | system user   |           | NULL               | Connect |  165 |             0 | Waiting for an event from Coordinator                  | NULL             |
| 17 | system user   |           | NULL               | Connect |  165 |             0 | Waiting for an event from Coordinator                  | NULL             |
| 18 | system user   |           | NULL               | Connect |  165 |             0 | Waiting for an event from Coordinator                  | NULL             |
| 19 | system user   |           | NULL               | Connect |  165 |             0 | Waiting for an event from Coordinator                  | NULL             |
| 20 | system user   |           | NULL               | Connect |  165 |             0 | Waiting for an event from Coordinator                  | NULL             |
| 21 | system user   |           | NULL               | Connect |  165 |             0 | Waiting for an event from Coordinator                  | NULL             |
| 22 | system user   |           | NULL               | Connect |  165 |             0 | Waiting for an event from Coordinator                  | NULL             |
| 23 | system user   |           | NULL               | Connect |  165 |             0 | Waiting for an event from Coordinator                  | NULL             |
| 24 | system user   |           | NULL               | Connect |  165 |             0 | Waiting for an event from Coordinator                  | NULL             |
| 25 | system user   |           | NULL               | Connect |  165 |             0 | Waiting for an event from Coordinator                  | NULL             |
| 26 | system user   |           | NULL               | Connect |  165 |             0 | Waiting for an event from Coordinator                  | NULL             |
| 32 | mysql.session | localhost | NULL               | Sleep   |  163 |             0 | NULL                                                   | PLUGIN           |
| 47 | root          | localhost | performance_schema | Query   |     |             0 | starting                                               | show processlist |
+----+---------------+-----------+--------------------+---------+------+---------------+--------------------------------------------------------+------------------+
20 rows in set (.00 sec)

node1>pager grep -v 'system user'
PAGER set to 'grep -v 'system user''
node1>show processlist;
+----+---------------+-----------+--------------------+---------+------+---------------+--------------------------------------------------------+------------------+
| Id | User          | Host      | db                 | Command | Time | ACK_WAIT_TIME | State                                                  | Info             |
+----+---------------+-----------+--------------------+---------+------+---------------+--------------------------------------------------------+------------------+
| 32 | mysql.session | localhost | NULL               | Sleep   |  213 |             0 | NULL                                                   | PLUGIN           |
| 47 | root          | localhost | performance_schema | Query   |     |             0 | starting                                               | show processlist |
+----+---------------+-----------+--------------------+---------+------+---------------+--------------------------------------------------------+------------------+
20 rows in set (.00 sec)

node1>

如上，我们将show processlist的输出结果过滤掉那些由系统用户‘system user’执行的连接。

node1>pager grep 'Waiting for an event from Coordinator' | wc -l
PAGER set to 'grep 'Waiting for an event from Coordinator' | wc -l'
node1>show processlist;
16
20 rows in set (0.00 sec)

node1>

通过show processlist查看当前从库的复制并发度，输出结果就是处于空闲状态的worker线程数量。

mysql> pager grep Sleep | wc -l
PAGER set to 'grep Sleep | wc -l'
mysql> show processlist;
337
346 rows in set (0.00 sec)

查看空闲连接和总连接的数值，了解当前系统的繁忙程度。

mysql> pager cat > /dev/null
PAGER set to 'cat > /dev/null'

# Trying an execution plan
mysql> SELECT ...
1000 rows in set (0.91 sec)

# Another execution plan
mysql> SELECT ...
1000 rows in set (1.63 sec)

通过将结果集导入黑洞来仅显示返回的记录条数和所需时间。这在进行不同索引查询的时间对比等场景比较有用，因为有时候查询结果可能需要数屏才能显示，那么进行时间对比就不直观。通过该方法可以直接在同一屏上看到对比数据。

node1>pager grep -v 'ACK_WAIT_TIME' | awk -F '|' '{print $6}' | sort | uniq -c | sort -r
PAGER set to 'grep -v 'ACK_WAIT_TIME' | awk -F '|' '{print $6}' | sort | uniq -c | sort -r'
node1>show processlist;
      3
      1  Sleep
      1  Query
     18  Connect
20 rows in set (0.00 sec)

node1>

上面是多个管道的例子，通过该表达式可以了解处于不同状态的连接的个数。

show engine innodb status

该命令很强大，innodb的大部分状态信息都通过其返回。不过其可读性并不是让人恭维。一般都会输出好几屏的内容。这种情况下，也可以通过pager来提高查看效率。

node1>pager less
PAGER set to 'less'
node1>show engine innodb status\G

通过less来返回输出结果，并输入查找关键字来直接定位到所需的部分。比如“BUFFER POOL AND MEMORY”，如下：

node1>pager grep 'TRANSACTION '
PAGER set to 'grep 'TRANSACTION ''
node1>show engine innodb status\G
---TRANSACTION 421345192110096, not started
---TRANSACTION 421345192109176, not started
---TRANSACTION 421345192108256, not started
---TRANSACTION 421345192107336, not started
---TRANSACTION 421345192106416, not started
---TRANSACTION 421345192105496, not started
---TRANSACTION 421345192104576, not started
---TRANSACTION 421345192103656, not started
---TRANSACTION 421345192102736, not started
---TRANSACTION 421345192101816, not started
---TRANSACTION 421345192100896, not started
---TRANSACTION 421345192099976, not started
---TRANSACTION 421345192099056, not started
---TRANSACTION 421345192098136, not started
---TRANSACTION 421345192097216, not started
---TRANSACTION 421345192096296, not started
---TRANSACTION 421345192095376, not started
---TRANSACTION 421345192094456, not started
---TRANSACTION 421345192093536, not started
1 row in set (.00 sec)

node1>pager grep 'TRANSACTION ' | grep 'not started' | wc -l
PAGER set to 'grep 'TRANSACTION ' | grep 'not started' | wc -l'
node1>show engine innodb status\G
19
1 row in set (.00 sec)

node1>