mysql进阶使用技巧

看mysql版本

select version();

1. 开启 general_log

general_log 是一个查询日志，它会记录你所有对数据库的操作

开启

# 查询 general_log 日志开启情况
show global variables like '%general%';
# 将日志输出到 mysql数据库下的 general_log表中
set global log_output='table';
# 将日志输出到文件中
set global log_output='/temp/general_log.log';
# 开启general_log
set global general_log=on;
# 关闭general_log 
set global general_log=off;

2. 关闭长事务

mysql 除了只读事务以外，其他事务的信息都会记录在information_schema.Innodb_trx 表里面，可以根据此表的TRX_MYSQL_THREAD_ID字段获取死锁事务的mysql线程ID，kill掉此线程即可。下面是这个表里面的字段注释

trx_id：唯一事务id号，只读事务和非锁事务是不会创建id的。
TRX_WEIGHT：事务的高度，代表修改的行数（不一定准确）和被事务锁住的行数。为了解决死锁，innodb会选择一个高度最小的事务来当做牺牲品进行回滚。已经被更改的非交易型表的事务权重比其他事务高，即使改变的行和锁住的行比其他事务低。
TRX_STATE：事务的执行状态，值一般分为：RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.
TRX_STARTED：事务的开始时间
TRX_REQUESTED_LOCK_ID:如果trx_state是lockwait,显示事务当前等待锁的id，不是则为空。想要获取锁的信息，根据该lock_id，以innodb_locks表中lock_id列匹配条件进行查询，获取相关信息。
TRX_WAIT_STARTED：如果trx_state是lockwait,该值代表事务开始等待锁的时间；否则为空。
TRX_MYSQL_THREAD_ID：mysql线程id。想要获取该线程的信息，根据该thread_id，以INFORMATION_SCHEMA.PROCESSLIST表的id列为匹配条件进行查询。
TRX_QUERY：事务正在执行的sql语句。
TRX_OPERATION_STATE：事务当前的操作状态，没有则为空。
TRX_TABLES_IN_USE：事务在处理当前sql语句使用innodb引擎表的数量。
TRX_TABLES_LOCKED：当前sql语句有行锁的innodb表的数量。（因为只是行锁，不是表锁，表仍然可以被多个事务读和写）
TRX_LOCK_STRUCTS：事务保留锁的数量。
TRX_LOCK_MEMORY_BYTES：在内存中事务索结构占得空间大小。
TRX_ROWS_LOCKED：事务行锁最准确的数量。这个值可能包括对于事务在物理上存在，实际不可见的删除标记的行。
TRX_ROWS_MODIFIED：事务修改和插入的行数
TRX_CONCURRENCY_TICKETS：该值代表当前事务在被清掉之前可以多少工作，由 innodb_concurrency_tickets系统变量值指定。
TRX_ISOLATION_LEVEL：事务隔离等级。
TRX_UNIQUE_CHECKS：当前事务唯一性检查启用还是禁用。当批量数据导入时，这个参数是关闭的。
TRX_FOREIGN_KEY_CHECKS：当前事务的外键坚持是启用还是禁用。当批量数据导入时，这个参数是关闭的。
TRX_LAST_FOREIGN_KEY_ERROR：最新一个外键错误信息，没有则为空。
TRX_ADAPTIVE_HASH_LATCHED：自适应哈希索引是否被当前事务阻塞。当自适应哈希索引查找系统分区，一个单独的事务不会阻塞全部的自适应hash索引。自适应hash索引分区通过 innodb_adaptive_hash_index_parts参数控制，默认值为8。
TRX_ADAPTIVE_HASH_TIMEOUT：是否为了自适应hash索引立即放弃查询锁，或者通过调用mysql函数保留它。当没有自适应hash索引冲突，该值为0并且语句保持锁直到结束。在冲突过程中，该值被计数为0，每句查询完之后立即释放门闩。当自适应hash索引查询系统被分区（由 innodb_adaptive_hash_index_parts参数控制），值保持为0。
TRX_IS_READ_ONLY：值为1表示事务是read only。
TRX_AUTOCOMMIT_NON_LOCKING：值为1表示事务是一个select语句，该语句没有使用for update或者shared mode锁，并且执行开启了autocommit，因此事务只包含一个语句。当TRX_AUTOCOMMIT_NON_LOCKING和TRX_IS_READ_ONLY同时为1，innodb通过降低事务开销和改变表数据库来优化事务。

3. 数据库备份

3.1 使用mysqldump

 # 导出 pibigstar 数据库
 mysqldump -uroot -p -B pibigstar --single-transaction –master-data=2  > backup.sql

# 恢复数据
mysql -uroot -p pibigstar < backup.sql

常用参数

-u: 指定连接的用户名；
-p: 指定用户的密码，可以交互输入密码；
-S: 指定socket文件连接，本地登录才会使用。
-h: 指定连接的服务器名称或者IP。
-P: 连接数据库监听的端口。
-A: 导出所有数据库。不过默认情况下是不会导出information_schema库。
-B: 导出指定的某个/或者某几个数据库，参数后面所有名字参量都被看作数据库名，包含CREATE DATABASE创建库的语句。
--single-transaction: 导数据之前会启动一个事务，来确保拿到一致性视图

3.2 手动备份

如果数据库引擎是MYISAM就不支持事务了，也就不能使用 mysqldump来开启一个事务去备份了，这个时候要备份数据库的话，需要将数据库设置为只读状态，也就是加一个数据库全局锁，这样才能保证数据的一致性。

# 为数据库加全局锁
 flush tables with read lock;

也可以通过下面语句将数据库设置为只读状态，不过不推荐使用，因为readonly常用来被判断该库是主库还是从库，所以轻易还是不要改这个值。

set global readonly=true;

## 3.3 备份表

// 创建备份表
create table user_back like user;
// 插入数据
insert into user_back select * from user;

4. 安全添加字段

表级锁有两种，一种是表锁，一种是元数据锁（MDL），当执行DML语句时数据库会自动给表添加一个MDL读锁，而执行 DDL语句时，会自动加一个MDL写锁，读写锁会互斥，而MDL锁释放需要等到事务提交之后才会释放，所以当我们为表添加字段的时候，为了更加安全，防止一直阻塞，可以添加一个等待时间。

ALTER TABLE user WAIT 10 add column age int(2) NOT NULL DEFAULT 0;

5. 使用Explain分析SQL

Explain返回结果字段介绍

字段名	字段描述
id	查询语句内SELECT的序列号
select_type	SELECT类型
table	访问的表名
partitions	命中分区
type	数据访问类型
possible_keys	有关索引，实际情况可能不可用
key	MySQL查询优化器实际使用的索引
key_len	索引存储长度
ref	实际使用的索引中，用于比较的常量或列
rows	扫描行数
Extra	执行情况的说明和描述

我们分析一条SQL语句时，重点关注key type Extra 这三个字段的信息，首先查看key中使用到的索引是否是我们预期的，然后通过type判别我们这条sql语句扫描的范围，最后通过Extra判断该条语句使用了那种方式，Extra可直观的看出该条语句的好坏。

5.1 type

type值，性能由差到好依次为，如果值为 ALL或 index时，就要考虑优化下语句了

字段	扫描信息
ALL	全表扫描
index	索引全扫描
range	索引范围扫描
ref	非唯一索引扫描
eq_ref	唯一索引扫描
const,system	单表最多有一个匹配行
NULL	不用扫描表或索引

5.2 Extra

下面是常用参数详解，性能从好到差依次排列，当遇到Using temporary,Using Flesort时，就可以考虑优化下SQL了。

字段	说明
Using Index	表示索引覆盖，不会回表查询
Using Where	表示进行了回表查询
Using Index Condition	表示进行了ICP优化
Using temporary	对查询结果排序时会使用一个临时表，尽量避免使用临时表。
Using Flesort	表示MySQL需额外排序操作, 不能通过索引顺序达到排序效果

6. 死锁排查

# 查询 innodb 状态
SHOW ENGINE INNODB STATUS;

可以看到 有 gap (间隙)锁

打开 mysql的锁统计，这个线上不推荐打开打开的话日志会记录得比较多。

set GLOBAL innodb_status_output_locks=ON;
set GLOBAL innodb_status_output=ON;

7. 数据恢复

// 查看binlog是否开启
show VARIABLES like 'LOG_BIN%'
// 查询某一段时间的binlog
mysqlbinlog --start-datetime='2021-09-03 10:11:00' --stop-datetime='2021-09-03 10:15:00' bin-log.00001
// 恢复某一段数据
mysqlbinlog --start-datetime='2021-09-03 10:11:00' --stop-datetime='2021-09-03 10:15:00' bin-log.00001 | mysql -uroot -p123456 数据库名