【Mysql进阶知识】

一、mysqlshow -显示数据库、表和列信息

1.1 作用

mysqlshow 客户端可用于快速查看存在哪些数据库、数据库中的表以及表中的列或索引

1.2 注意事项

mysqlshow 为⼀些 SHOW 语句提供了⼀个命令行接口。关于SHOW的使用参见官方文件“SHOW语句”。直接使用这些SQL语句也可以获得相同的信息。

1.3 使用方法

mysqlshow [options] [db_name [tbl_name [col_name]]]

• db_name tbl_name col_name 可以使用通配符*、?、%或_ 。

• 如果没有指定数据库，则显示所有数据库名称列表。

• 如果没有指定表，则显示数据库中所有匹配的表。

• 如果没有指定列，则显示表中所有匹配的列和列类型。

• 输出仅显示当前权限可以访问的数据库、表或列的名称。

#查看数据库的所有表
zyq@iZm5egpp4a85g2tfliaeikZ:~$ mysqlshow test_db -uroot -p
Enter password: 
Database: test_db
+---------+
| Tables  |
+---------+
| classes |
| course  |
| score   |
| student |
+---------+

//查看数据库中的某一个表
zyq@iZm5egpp4a85g2tfliaeikZ:~$ mysqlshow test_db student -uroot -p
Enter password: 
Database: test_db  Table: student
+-------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+----------+
| Field | Type        | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment  |
+-------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+----------+
| id    | int         |                    | NO   | PRI |         | auto_increment | select,insert,update,references |          |
| sn    | int         |                    | NO   |     |         |                | select,insert,update,references | 学号   |
| name  | varchar(20) | utf8mb4_general_ci | NO   |     |         |                | select,insert,update,references | 姓名   |
| mail  | varchar(20) | utf8mb4_general_ci | YES  |     |         |                | select,insert,update,references | QQ邮箱 |
+-------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+----------+

//查看数据库中的某一个表的某一列
zyq@iZm5egpp4a85g2tfliaeikZ:~$ mysqlshow test_db student id -uroot -p
Enter password: 
Database: test_db  Table: student  Wildcard: id
+-------+------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int  |           | NO   | PRI |         | auto_increment | select,insert,update,references |         |
+-------+------+-----------+------+-----+---------+----------------+---------------------------------+---------+

mysqlshow 的常用选项参考公共选项部分，可以在命令行中指定，也可以在选项文件中通过 [mysqlshow] 和 [client] 组进行指定，具体选项参考公共选项列表。

二、mysqldumpslow - 总结慢查询日志文件

2.1 作用

在平时使用MySQL数据库时，经常进行查询操作，有些查询语句执行的时间非常长，当执行时间超过设定的阈值时，我们称这个查询为慢查询，慢查询的相关信息通常需要用日志记录下来称为慢查询日志。

mysqldumpslow可以解析慢查询日志文件并汇总其内容，有关慢查询日志的内容我们在MySQL服务器配置与管理专题进行讲解。

2.2 注意事项

通常情况下，mysqldumpslow 会将相似的查询分组并显示摘要输出，⼀般会把数字和字符串用 N 和 "S" 代替,要想显示真实的值可以使用 -a 和 -n 选项

2.3 使用方法

mysqldumpslow [options] [log_file ...]

 在Linux下 慢查询日志文件通常在/var/lib/mysql 目录下

在没有给出任何选项的输出如下：

Reading mysql slow query log from /usr/local/mysql/data/mysqld80-slow.log
Count: 1 Time=4.32s (4s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
 insert into t2 select * from t1 # 执⾏的SQL 
Count: 3 Time=2.53s (7s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
 insert into t2 select * from t1 limit N # 执⾏的SQL 
Count: 3 Time=2.13s (6s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
 insert into t1 select * from t1 # 执⾏的SQL 

• count：执行的次数
• Time：单次耗时
• Lock：加锁与解锁的耗时
• Rows：获取数据的时间

2.4 常用选项

-s sort_type sort_type可选的值如下所示：

• t, at: 按查询时间或平均查询时间排序，默认排序

• l, al: 按锁占用时间或平均锁占用时间排序

• r, ar: 按发送的行数或平均发送的行数排序

• c: 按计数排序

三、mysqlbinlog - 处理⼆进制日志文件

3.1 作用

什么是二进制日志文件：

我们平时对数据库的修改，包括对数据的增删改，都会被描述成一个"事件"，每个"事件"都会以二进制的形式记录在一个文件里，这个文件就是服务器的二进制日志文件，称为Binary Log或binlog。

mysqlbinlog 能够以文本格式显示⼆进制日志⽂件中的内容。

3.2 注意事项

• binlog的默认保存路径是数据目录：

Linux下默认目录：/var/lib/mysql

Windows下默认目录：C:\ProgramData\MySQL\MySQL Server 8.0\Data

• binlog是以 .00000n 结尾命名的文件，n不断递增

root@iZm5egpp4a85g2tfliaeikZ:/var/lib/mysql# ll binlog*
-rw-r----- 1 mysql mysql   180 Jan 10 15:13 binlog.000001
-rw-r----- 1 mysql mysql   404 Jan 10 15:13 binlog.000002
-rw-r----- 1 mysql mysql   180 Jan 10 15:21 binlog.000003
-rw-r----- 1 mysql mysql   529 Jan 10 15:29 binlog.000004
-rw-r----- 1 mysql mysql   599 Jan 12 00:00 binlog.000005
-rw-r----- 1 mysql mysql   201 Jan 13 00:00 binlog.000006
-rw-r----- 1 mysql mysql   201 Jan 14 00:00 binlog.000007
-rw-r----- 1 mysql mysql   201 Jan 15 00:00 binlog.000008
-rw-r----- 1 mysql mysql   201 Jan 16 00:00 binlog.000009
-rw-r----- 1 mysql mysql 26683 Jan 16 15:16 binlog.000010
-rw-r----- 1 mysql mysql   160 Jan 16 00:00 binlog.index

3.3 使用方法

mysqlbinlog [options] log_file ...

例如要显示名为 binlog.000010 ⼆进制日志文件的内容，可以使用以下命令：

root@iZm5egpp4a85g2tfliaeikZ:/var/lib/mysql# mysqlbinlog binlog.000010
# ... 略
# at 37380
#230906 15:30:33 server id 1 end_log_pos 37442 CRC32 0x6d3de7e6 
Write_rows: table id 119 flags: STMT_END_F
BINLOG '
mSr4ZBMBAAAAQgAAAASSAAAAAHcAAAAAAAEAB3Rlc3RfZGIAB3N0dWRlbnQABAMDDw8EUABQAA8B
AQACAS1oGW+U
mSr4ZB4BAAAAPgAAAEKSAAAAAHcAAAAAAAMAAgAE/wAFAAAAVcMAAAbpkrHkuIMKcXFAYml0LmNv
bebnPW0=
'/*!*/;
# ... 略

binlog.000010 的输出内容中包含各种事件，事件信息包括 SQL 语句、执行语句的服务器 ID、语 句执行时的时间戳、花费的时间等等。

3.4 常用选项

mysqlbinlog 有如下常用选项，可以在命令行中指定，也可以在选项文件中通过 [mysqlbinlog] 和 [client] 组进行指定

• --base64-output=value，value允许的值：（默认AUTO）

• AUTO ("automatic")或UNSPEC ("unspecified")在必要时自动显示BINLOG语句。如果使用mysqlbinlog重新执行二进制日志文件内容，那么使用AUTO选项是唯一安全的行为，其他选项值仅用于调试或测试，如果--base64-output没有指定，那么默认值是AUTO。

• NEVER不显示BINLOG语句。

• DECODE-ROWS不显示加密内容，可以配合mysqlbinlog的-verbose选项以注释的形式只显示事件的SQL语句。

四、mysqlslap-负载仿真客户端

4.1 作用

mysqlslap是⼀个诊断程序，用于模拟MySQL服务器的客户端负载，并报告每个阶段的时间，就好比多个客户端正在访问服务器⼀样。

4.2 使用方法

mysqlslap [options]

4.3 注意事项

• 可以通过--create或--query选项，指定包含SQL语句的字符串或包含SQL语句的文件。
• 如果指定一个包含SQL语句的文件，默认情况下每行必须包含一条语句（也就是说，隐式语句分隔符是换行符）。
• 如果要把一条语句分为多行书写，可以使用--delimiter选项指定不同的分隔符。
• 不能在文件中包含注释，因为mysqlslap不能解析注释。 mysqlslap运行分为三个阶段：

a. 创建测试数据阶段：创建用于测试的库、表或数据，这个阶段使用单个客户端连接

b. 运行负载测试阶段，这个阶段可以使用许多客户端连接

c. 清理阶段：执行删除表，断开连接等操作，这个阶段使用单个客户端连接

4.4 常用选项

mysqlslap 有如下常用选项，可以在命令行中指定，也可以在选项文件中通过 [mysqlslap] 和 [client] 组进行指定

4.5 示例

 1、提供自定义的创建和查询语句，创建50个客户端连接，每个客户端进行200次 select 查询(在⼀ 行内输入命令)

# 在⼀⾏内输⼊，为了演⽰⽅便这⾥换⾏了 
mysqlslap --delimiter=";" 
 --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23);"
 --query="SELECT * FROM a;" --concurrency=50 --iterations=200

2. 让mysqlslap用包含两个 INT 列和三个 VARCHAR 列的表自动构建查询的SQL语句。使用5个客户端，每个客户端查询20次。

mysqlslap --concurrency=5 --iterations=20
 --number-int-cols=2 --number-char-cols=3
 --auto-generate-sql

3. 从指定的文件中加载创建、插入和查询SQL语句。SQL文件中的语句以 ";" 分隔。使用5个客户端，每个客户端查询5次。

mysqlslap --concurrency=5
 --iterations=5 --query=query.sql --create=create.sql
 --delimiter=";"