mysql

3 posts with the tag “mysql”

Mysql Explain

Sep 9, 2024

在 MySQL 中，EXPLAIN 命令是一个非常有用的工具，它可以帮助我们深入了解 MySQL 执行一条 SQL 语句时的优化过程。 type 列和 extra 列是 EXPLAIN 结果中两个最重要的列，它们能提供关于查询执行方式的详细信息。

注意： EXPLAIN 提供的信息是基于 MySQL 优化器对 SQL 语句的分析，实际执行情况可能会有所不同。

type 列

type 列显示了 MySQL 处理查询时所使用的访问类型。不同的访问类型代表不同的查询执行效率，一般来说，访问类型越靠前，查询效率越高。

常见的访问类型包括：

system: 表中只有一行记录（系统表），直接返回。
const: 表中最多只有一条匹配的行，如主键或唯一索引。
eq_ref: PRIMARY KEY 或 UNIQUE index 的所有部分被用在连接中。
ref: 非唯一索引列或常数被用在连接。
range: 使用索引来检索一个范围的行，如使用 between、<、> 等操作符。
index: 全索引扫描，根据索引来获取所需的行。
all: 全表扫描，MySQL必须检索表中的所有行以找到匹配的行。

一般来说，我们希望查询的 type 尽可能靠前，比如 system 或 const，这表示 MySQL 能很快地找到数据。而 all 则表示 MySQL 需要扫描整个表，效率最低。

extra 列

extra 列显示了 MySQL 执行查询时所使用的访问类型的详细信息。一些常见的 extra 值包括：

using index: 表示 MySQL 只使用了索引来完成查询，没有读取表中的行数据。
using where: 表示 MySQL 在读取索引后，又使用 where 条件对结果集进行了过滤。
using temporary: MySQL 需要创建一个临时表来存储结果。
using filesort: MySQL 需要对结果集进行排序，这通常发生在排序列没有索引的情况下。

一般来说，我们希望 extra 中出现 Using index，这表示查询效率很高。而 Using temporary 和 Using filesort 则表示查询效率较低，可能需要优化。

示例

MySQL root@localhost:sakila> explain select * from inventory\G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | inventory
partitions    | <null>
type          | ALL
possible_keys | <null>
key           | <null>
key_len       | <null>
ref           | <null>
rows          | 4581
filtered      | 100.0
Extra         | <null>

MySQL root@localhost:sakila> explain select store_id , film_id FROM inventory\G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | inventory
partitions    | <null>
type          | index
possible_keys | <null>
key           | idx_store_id_film_id
key_len       | 3
ref           | <null>
rows          | 4581
filtered      | 100.0
Extra         | Using index

总结

type 列和 extra 列是分析 MySQL 查询性能的重要指标。通过了解它们的含义，我们可以针对性地优化 SQL 语句，提高查询效率。

优化建议：

创建合适的索引：为经常用在 where 条件、order by 和 group by 子句中的列创建索引。
避免使用 select *：只查询需要的列，减少数据的传输。
优化 where 条件：尽量使用索引列，避免使用函数和表达式。
分表：当数据量过大时，可以考虑分表。

Mysql的mysqldump详解

抄录自：https://www.cnblogs.com/flagsky/p/9762726.html

一、导出

1.1 导出表结构及数据

mysqldump -uroot -p --set-gtid-purged=OFF database table1 table2 > mysqldump.sql

1.2 只导出表结构

mysqldump -uroot -p --set-gtid-purged=OFF -d database table1 table2 > mysqldump.sql

1.3 只导出数据

mysqldump -uroot -p --set-gtid-purged=OFF -t database table1 table2 > mysqldump.sql

1.4 导出单张表

(1)导出表结构及数据（有where条件）

mysqldump -uroot -p --set-gtid-purged=OFF database table --where "限制条件" > mysqldump.sql
(2)只导出数据（有where条件）
mysqldump -uroot -p --set-gtid-purged=OFF --no-create-info database table --where "限制条件" > mysqldump.sql

参数说明： -d参数：等价于-–no-data，含义是不导出任何数据，只导出数据库表结构； -t参数：等价于—no-create-info，含义是只导出数据，而不添加CREATE TABLE语句；

2、导出数据库

2.1 导出所有数据库

mysqldump -uroot -p --all-databases > mysqldump.sql

2.2 导出单个数据库

mysqldump -uroot -p --databases db1 > mysqldump.sql

2.3 导出多个数据库

mysqldump -uroot -p --databases db1 db2 > mysqldump.sql

二、导入

1、导入表

mysql -uroot -p database < mysqldump.sql

2、导入数据库

mysql -uroot -p < mysqldump.sql

三、mysqldump常用参数说明

1、—all-databases , -A

含义：导出全部数据库。示例：

mysqldump -uroot -p --all-databases

2、—all-tablespaces , -Y

含义：导出全部表空间。示例：

mysqldump -uroot -p --all-databases --all-tablespaces

3、—no-tablespaces , -y

含义：不导出任何表空间信息。示例：

mysqldump -uroot -p --all-databases --no-tablespaces

4、—add-drop-database

含义：每个数据库创建之前添加drop数据库语句。示例：

mysqldump -uroot -p --all-databases --add-drop-database

5、—add-drop-table

含义：每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用—skip-add-drop-table取消选项) 示例：

mysqldump -uroot -p --all-databases  (默认添加drop语句)
mysqldump -uroot -p --all-databases –skip-add-drop-table  (取消drop语句)

6、—add-locks

含义：在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态，使用—skip-add-locks取消选项) 示例：

mysqldump -uroot -p --all-databases  (默认添加LOCK语句)
mysqldump -uroot -p --all-databases –skip-add-locks   (取消LOCK语句)

7、—create-options, -a

含义：在CREATE TABLE语句中包括所有MySQL特性选项。(默认为打开状态) 示例：

mysqldump -uroot -p --all-databases

8、—databases, -B

含义：导出几个数据库。参数后面所有名字参量都被看作数据库名。示例：

mysqldump -uroot -p --databases test mysql

9、—default-character-set

含义：设置默认字符集，默认值为utf8 示例：

mysqldump -uroot -p --all-databases --default-character-set=utf8

10、—events, -E

含义：导出事件。示例：

mysqldump -uroot -p --all-databases --events

11、—flush-logs

含义：开始导出之前刷新日志。请注意：假如一次导出多个数据库(使用选项—databases或者—all-databases)，将会逐个数据库刷新日志。除使用—lock-all-tables或者—master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用—lock-all-tables 或者—master-data 和—flush-logs。示例：

mysqldump -uroot -p --all-databases --flush-logs

12、—flush-privileges

含义：在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。示例：

mysqldump -uroot -p --all-databases --flush-privileges

13、—help

含义：显示帮助信息并退出。示例：

mysqldump --help

14、—host, -h

含义：需要导出的主机信息示例：

mysqldump -uroot -p --host=localhost --all-databases

15、—ignore-table

含义：不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：—ignore-table=database.table1 —ignore-table=database.table2 …… 示例：

mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user

16、—lock-all-tables, -x

含义：提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭—single-transaction 和—lock-tables 选项。示例：

mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables

17、—no-create-db, -n

含义：只导出数据，而不添加CREATE DATABASE 语句。示例：

mysqldump -uroot -p --host=localhost --all-databases --no-create-db

18、—no-create-info, -t

含义：只导出数据，而不添加CREATE TABLE 语句。示例：

mysqldump -uroot -p --host=localhost --all-databases --no-create-info

19、—no-data, -d

含义：不导出任何数据，只导出数据库表结构。示例：

mysqldump -uroot -p --host=localhost --all-databases --no-data

20、—password, -p

含义：连接数据库密码

21、—port, -P

含义：连接数据库端口号

22、—routines, -R

含义：导出存储过程以及自定义函数。示例：

mysqldump -uroot -p --host=localhost --all-databases --routines

23、—tables

含义：覆盖—databases (-B)参数，指定需要导出的表名，在后面的版本会使用table取代tables。示例：

mysqldump -uroot -p --host=localhost --databases test --tables test

24、—triggers

含义：导出触发器。该选项默认启用，用—skip-triggers禁用它。示例：

mysqldump -uroot -p --host=localhost --all-databases --triggers

25、—user, -u

含义：指定连接的用户名。

26、—version, -V

含义：输出mysqldump版本信息并退出

27、—where, -w

含义：只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。示例：

mysqldump -uroot -p --host=localhost --all-databases --where=” user=’root’”

28、—xml, -X

含义：导出XML格式. 示例：

mysqldump -uroot -p --host=localhost --all-databases --xml

Mysql_kill_process

May 10, 2020

xiwang

全栈开发者

使用kill命令可以杀掉等待msyql线程。

终止一个线程

每个与 mysqld 的连接都在一个独立的线程里运行，您可以使用 SHOW PROCESSLIST 语句查看哪些线程正在运行，并使用 KILL thread_id 语句终止一个线程。

当进行一个 KILL 时，对线程设置一个特有的终止标记。在多数情况下，线程终止可能要花一些时间，这是因为终止标记只会在在特定的间隔被检查：

· 在 SELECT, ORDER BY 和 GROUP BY 循环中，在读取一组行后检查标记。如果设置了终止标记，则该语句被放弃。

· 在 ALTER TABLE 过程中，在每组行从原来的表中被读取前，检查终止标记。如果设置了终止标记，则语句被放弃，临时表被删除。

· 在 UPDATE 或 DELETE 运行期间，在每个组读取之后以及每个已更行或已删除的行之后，检查终止标记。如果终止标记被设置，则该语句被放弃。注意，如果您正在使用事务，则变更不会被回滚。

· GET_LOCK() 会放弃和返回 NULL。

· INSERT DELAYED 线程会快速地刷新（插入）它在存储器中的所有的行，然后终止。

· 如果线程在表锁定管理程序中（状态：锁定），则表锁定被快速地放弃。

· 如果在写入调用中，线程正在等待空闲的磁盘空间，则写入被放弃，并伴随 “disk full” 错误消息。

· 警告：对 MyISAM 表终止一个 REPAIR TABLE 或 OPTIMIZE TABLE 操作会导致出现一个被损坏的没有用的表。对这样的表的任何读取或写入都会失败，直到您再次优化或修复它（不中断）。

使用命令：

mysql> show full PROCESSLIST;

可以查看到mysql当前正在运行的中的进程。

使用命令：

mysql> kill pid;//如：kill 211598；

可以杀死指定的进程。

如果进程非常多，这就非常低效，可以先生生成命令字符，再复制执行，即可：

mysql> select group_concat(concat('kill ',id) separator '; ') as cmd
from information_schema.processlist where user='yb' and Host like '171.43.215.39%';

+----------------------------------+
| cmd                              |
+----------------------------------+
| kill 211598;kill 4;kill 211599 |
+----------------------------------+

然后执行：

> kill 211598;kill 4;kill 211599;

查看数据库状态

show status;

该命令会将 DB 所有的状态打印出来，如需其中特定的项可以加上 like ‘% 变量名称 %’，如：

show status like '%Connections%';

常用变量有：

Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。

Aborted_connects 尝试已经失败的 MySQL 服务器的连接的次数。

Connections 试图连接 MySQL 服务器的次数。

Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。

Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。

Delayed_writes 用 INSERT DELAYED 写入的行数。

Delayed_errors 用 INSERT DELAYED 写入的发生某些错误 (可能重复键值) 的行数。

Flush_commands 执行 FLUSH 命令的次数。

Handler_delete 请求从一张表中删除行的次数。

Handler_read_first 请求读入表中第一行的次数。

Handler_read_key 请求数字基于键读行。

Handler_read_next 请求读入基于一个键的一行的次数。

Handler_read_rnd 请求读入基于一个固定位置的一行的次数。

Handler_update 请求更新表中一行的次数。

Handler_write 请求向表中插入一行的次数。

Key_blocks_used 用于关键字缓存的块的数量。

Key_read_requests 请求从缓存读入一个键值的次数。

Key_reads 从磁盘物理读入一个键值的次数。

Key_write_requests 请求将一个关键字块写入缓存次数。

Key_writes 将一个键值块物理写入磁盘的次数。

Max_used_connections 同时使用的连接的最大数目。

Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。

Not_flushed_delayed_rows 在 INSERT DELAY 队列中等待写入的行的数量。

Open_tables 打开表的数量。

Open_files 打开文件的数量。

Open_streams 打开流的数量 (主要用于日志记载）

Opened_tables 已经打开的表的数量。

Questions 发往服务器的查询的数量。

Slow_queries 要花超过 long_query_time 时间的查询数量。

Threads_connected 当前打开的连接的数量。

Threads_running 不在睡眠的线程数量。

Uptime 服务器工作了多少秒。