绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
提高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>


通过grep关键字‘TRANSACTION’来仅显示事务部分的内容,或仅统计某种状态事务的个数。

如果要关闭这些管道,只需要重新执行下pager即可。


总结

上面对edit和pager的使用例子只是抛砖引玉,但至少能说明如果灵活使用这两个特性,可以大大提升通过mysql客户端进行运维和问题分析的效率。

本文参考自mysql-client-program-some-interesting-features,感兴趣的同学可以点击了解更多mysql客户端特性。


分享好友

分享这个小栈给你的朋友们,一起进步吧。

数据库内核开发
创建时间:2019-12-11 16:43:06
网易数据库内核技术专家 8年多数据库和存储系统开发经验,《MySQL内核:InnoDB存储引擎 卷1》作者之一,申请技术专利10+,已授权5+。曾主导了网易公有云RDS、MongoDB等数据库云服务建设 现负责网易MySQL分支InnoSQL开发和维护。专注于数据库内核技术和分布式系统架构,擅长分析解决疑难问题。
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

栈主、嘉宾

查看更多
  • 温正湖
    栈主

小栈成员

查看更多
  • xzh1980
  • else
  • Jack2k
  • at_1
戳我,来吐槽~