提高mysql客户端使用效率的2个特性
本文介绍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>通过grep关键字‘TRANSACTION’来仅显示事务部分的内容，或仅统计某种状态事务的个数。
如果要关闭这些管道，只需要重新执行下pager即可。
总结
上面对edit和pager的使用例子只是抛砖引玉，但至少能说明如果灵活使用这两个特性，可以大大提升通过mysql客户端进行运维和问题分析的效率。
本文参考自mysql-client-program-some-interesting-features，感兴趣的同学可以点击了解更多mysql客户端特性。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69923634/viewspace-3148752/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
