如何快速的复制一张表

我们往往会采用insert … select。但是这种做法会对原表加锁。所以更稳妥的办法是将表导出到临时文件中。本文章会讨论如何快速的赋值一张表。测试数据如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

create database db1;
use db1;

create table t(id int primary key, a int, b int, index(a))engine=innodb;
delimiter ;;
  create procedure idata()
  begin
    declare i int;
    set i=1;
    while(i<=1000)do
      insert into t values(i,i,i);
      set i=i+1;
    end while;
  end;;
delimiter ;
call idata();

create database db2;
create table db2.t like db1.t

mysqldump方法

例如命令

1

mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

参数说明：

1. single-transaction 不对表db1 t 加锁， START TRANSACTION WITH CONSISTENT SNAPSHOT的方法；
2. add-locks 输出的文件不增加”LOCK TABLES t WRITE”
3. no-create-info 不需要导出表结构
4. set-gtid-purged 不需要gtid的信息
5. result-file 生成文件的位置，注意是客户机的位置

这时候t.sql就是一条insert语句，我们在新表上执行t.sql就可以导入数据了，导入语句如下

1

mysql -h127.0.0.1 -P13000  -uroot db2 -e "source /client_tmp/t.sql"

注：source并不是Mysql的命令，真正的语句还是Insert

导出CSV文件

1

select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

这条语句注意如下：

1. 受参数secure-file-priv控制
   1. 如果是NULL，说明 outfile被进制
   2. 设置为empty，可写到任意地方，一般不建议这么设置这是不安全的
   3. 设置为目录，说明outfile只能到指定的目录
2. t.csv没有覆盖功能，所以要保证目标目录下的t.csv不存在否则会报错
3. 原则是一条数据行对应文本一行，但是如果数据行有换行符能会跟上“\”这个转义字符这样字段之间、数据行之间可能会分开

导入命令如下：

1

load data infile '/server_tmp/t.csv' into table db2.t;

执行流程如下：

1. 打开文件，”\t”区分字段，“\n”区分数据
2. 校验csv文件的字段和新表字段是否一致，不一致会报错
3. 循环写入到新表知道写入完成
4. 如果binlog_format是statement
   1. 主库完成后，将csv文件内容直接写到binlog中
   2. binlog写入load data local infile ‘/tmp/SQL_LOAD_MB-1-0’ INTO TABLE db2.t。
   3. 这个binlog传到备库
   4. 备库apply接到这个binlog
      1. 现将csv内容写入到SQL_LOAD_MB-1-0中
      2. 执行loaddate导入数据

如图：

注意：loaddata有俩种方式

• 加local，读取的是服务器文件，受参数secure-file-priv控制
• 不加local，读取的是客户端文件，不收参数secure-file-priv控制

还有csv里不包含表结构，所以可以用下面的语句导出表结构和csv数据文件

1

mysqldump -h$host -P$port -u$user ---single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv

物理copy法

优点：速度最快，适合大表的导入
缺点：只能在服务器copy表文件；并且没法导入部分数据；且双方都得是innodb表

流程如下

1. 执行create table r like t;
2. 执行alter table r discard tablespace，这时候r.ibd会被删掉
3. 执行flush table t for export，这时候db1会生成t.cfg文件
4. 在db1目录下执行cp t.cfg r.cfg;cp t.ibd r.ibd
5. 执行unlock tables，这时候t.cfg会被删除
6. 执行alter table r import tablespace,将这个r.ibd作为表空间，由于是步骤4cp过来的所以就有了表数据

如图

注意：

• 在步骤3 flush 表之后，表是被lock的
• 执行import tablespace时候为了让表空间id和数据文件一直会刷新所有的数据页，import会有一定执行空间但是比逻辑导入会快很多。
• 一定要注意权限问题（尤其是执行步骤4时候要确认好是否对.ibd有读写权限）

insert…select为什么要对目标表加锁

测试语句如下，注：隔离模式是RR，binlog_format=statement

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t

执行如下的语句：

1

insert into t2(c,d) select c,d from t;

这条语句是给表t所有的字段和间隙加锁，原因是考虑到数据的一致性例如下面的情况:

SessionA和SessionB同时执行，SessionB会给索引c的(-supernum,-1]加上nextkey-lock，原因是如果没有这个锁，很可能主库上B先执行，A后执行，但是binlog上回是A先执行，B后执行，导致从库会出现-1的值，造成主备不一致

当然了，目标表的锁其实是给涉及到的字段加锁，详见前面的加锁原理

见下面俩种情况：

1

insert into t2(c,d) select c+1,d from t order by c desc limit 1;

这条语句实际上会对表t中的索引c(3,4]和(4,supernum)加锁。扫描行数只有2行

但是下面的语句就不是很好了,把表t最大值+1插入会原表

1

insert into t(c,d) select c+1,d from t order by c desc limit 1;

原因是：

• 在一张表中操作，mysql为了select时候读到insert的值，需要将select的结果导入到临时表中
• 临时表是memory类型的表，因为有limit 1，需要将表里的数据都导入到临时表中，排序取第一条
• 所以需要全表扫描，并且对全表所有的行加锁。

优化的方法是：采用临时表来解决语句如下

1
2
3
4

create temporary table temp_t(c int,d int) engine=memory;
insert into temp_t  (select c+1, d from t force index(c) order by c desc limit 1);
insert into t select * from temp_t;
drop table temp_t;

insert违反唯一约束加锁

见下图：

SessionA在第2次插入时候违反了唯一约束，这时候SessionB的语句并没有因为SessionA的insert失败开始执行反而是block住。原因是SessionA在后滚之前为冲突的索引(5,10]加了nextkey-lock读锁。

在并发情况下也会引起死锁，如图：

原因是：

1. SessaionA为记录加行锁c=5。（唯一索引等值查询是行锁）
2. T2时刻SessionB和SessionC都对c=5加了读锁。（违反唯一约束）这时候都是等待状态
3. T3时刻SessionArollback，
   1. SessionB和SessionCD的读锁生效；
   2. SessionB和SessionC要继续执行插入操作，这时候要对数据加写锁，这时候需要等待对方释放读锁，循环等待发生死锁。

见图:

解决方法

1. 事务中唯一约束冲突后要尽快提交或者回滚，防止并发写循环等待；
2. 采用insert into…duplicate key update的方式，因为这时候会加一个nextkey-lock写锁是互斥的。这样sessionB和sessionC不会循环等待。

自增主键的原理

首先创建测试表

1
2
3
4
5
6
7

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

我们MySql的主键往往设置成auto_incrment

• 原理是什么呢？
• 为什么会有不连续的情况？

自增字值保存在哪里

不同的表引擎对于auto_incrment处理是不同的

• MyIsam表是保存在数据结构中
• Innodb表是保存在内存中的
  • 5.7以及之前的版本是保存在内存中，每次数据库启动时候会查找表的max(id)，然后+1。如果这时候有auto_incrment=11,id=10,如果这条id=10的数据被删除后从起mysql，Auto_incrment的值又变成10
  • 在Mysql 8.0中auto_incrment的值保存在redolog中，依靠redolog来恢复

自增值的修改方式

如果一个值被设置为auto_incrment，当一条插入语句时候

1. 如果这个值是null或者0，会把当前的auto_incrment值设置进去
2. 如果手动设置了值，就是用设置的值

这时候auto_incrment的变化如下，插入值是X，auto_incrment的值是Y，

1. 如果X < Y，自增值不变
2. 如果X ≥ Y，自增值变为新的自增值，计算方法是：当前的auto_incrment_offset+auto_incrment_increment知道比X大。

自增值的修改时机来看为什么自增值不连续

1
2
3
4
5

# 1
insert into t values(null, 1, 1);

# 2
insert into t values(null, 1, 1);

主要看第2条语句的执行流程

1. Innodb调用引擎写入一条数据(null, 1, 1)
2. 因为id是null所以获取当前的auto_incrment，现在是2
3. 改变auto_incrment，变为3
4. 这时候插入（2,1,1）;
5. 所以C冲突插入失败
6. 语句结束

可以看到这时候auto_incrment是没有变回2。原因是，因为有可能这时候并发写入，如果回滚了后续的写入都会报主键冲突，同理事物的回滚也类似。

自增锁的优化

为了增加并发能力，Mysql为自增字段加锁不是一个事务锁，每次申请完了都会释放。但是在Mysql5.1之前不是这样的。

在5.1.22版本中增加了一个新策略inoodb_autoinc_lock_mode,默认值是1

• 0，类似之前的设计直到每个语句执行结束才会释放
• 1，普通的insert会立刻释放，select。。。。insert ，load等批量操作需要等语句执行完
• 2，都会立刻释放

推荐设置成2，同事binlog格式设置成row，既能保证并发度，又能保证主备数据不一致。原理是

当binlog的格式是statement时候，select。。。。insert 在并发阶段很可能造成主从数据不一致。比如下图：

• 很可能涉及sessionB的binlog拿到从库执行由于id都是null，是没法保证执行顺序的造成主备不一致。

Memory引擎表的特点

• Memory表是堆组织表，对应的Iondb是索引组织表。
• Memory索引顺序是按照写入顺序，Innodb是按照主键顺序
• Memory的索引存放规则决定这他所有索引是一样的没有主键概念
• Memory的索引和数据是分开存放的，在主键ID索引里，存的是每个数据的位置，且顺序是按照hash顺序。Innodb是按照B+树存放的
• Memory不支持blog和Text字段，所有行数的长度都相同。（要充分的利用内存嘛~）
• Memory数据出现空洞时候可以任意位置插入数据，Innodb为了保证索引顺序只能在后面递增插入数据
• Memory数据位置发生改变需要改所有的索引，而Innodb只需要改主键位置，

不推荐用Mermory在生产环境的原因

1. 锁粒度不够：不支持行锁，性能会很差，往往不如Innodb
2. 由于Mysql重启后会删除Memory表数据，数据持久性会有问题
   1. 尤其是双M架构，当备库重启后，会同步binlog给主库把表清空

Mysql何时会使用内部临时表

Mysql除了我们显式的创建临时表外，在某些情况下会用到内部临时表，在开始之前可以先用下面脚本创建一个测试库

1
2
3
4
5
6
7
8
9
10
11
12
13
14

create table t1(id int primary key, a int, b int, index(a));
delimiter ;;
create procedure idata()
begin
  declare i int;

  set i=1;
  while(i<=1000)do
    insert into t1 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

Union

如下面的语句，取俩条语句结果的并集

1

(select 1000 as f) union (select id from t1 order by id desc limit 2);

执行计划如下：

1
2
3
4
5
6
7

+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | NULL       | NULL       | NULL  | NULL          | NULL    | NULL    | NULL | NULL |     NULL | No tables used  |
|  2 | UNION        | t1         | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | Using index     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+

最后的Extra字段Using temporary表示使用了临时表

该语句流程如下：

1. 创建一个内存临时表，只有一个字段是f，f是主键;
2. 将第一条查询语句结果插入到临时表中
3. 执行第2个子查询
   1. 将符合条件的插入到临时表中，如果是union取并集，违反唯一约束写入失败；
   2. 继续插入直到不符合规则
4. 返回结果，删除临时表
5. 注:如果是union all因为没有计算并集的需求也就没有唯一约束就不用临时表了

GROUP BY

例如下面的语句

1

select id%10 as m, count(*) as c from t1 group by m;

执行计划如下：

1
2
3
4
5

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                        |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | PRIMARY,a     | a    | 5       | NULL | 1000 |   100.00 | Using index; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+

通过Extra字段可以看到3个信息

• Using index 因为需要遍历索引树，这里没选PRIMARY原因是因为a索引够用（只取count），并且相对PK更小
• Using temporary 用了临时表
• Using filesort 排序

流程如下：

1. 创建内存临时表，创建俩个字段（m,c），且m为主键
2. 将id%10，然后得到m，写入到临时表
   1. 如果m不存在，insert m,1
   2. 如果m存在，update c字段+1；
3. 然后临时表通过filesort排序，内存临时表rowid排序，见前面的文章
4. 返回结果，移除临时表。

上面提到里都是创建一个内存临时表，这是由参数tmp_table_size值控制的默认是16M，如果超过这个值就采用磁盘临时表了

group by优化方案

临时表会消耗内存，甚至是磁盘。所以在生产环境上我们哟啊尽量避免这种内部临时表的产生，针对上面说的group by有如下几点优化建议

通过索引

上面的例子，我们可以创建一个id%10的字段,然后根据这个字段group by

1
2
3
4
5

alter table t1 add column z int generated always as(id % 100), add index(z);

select z, count(*) as c from t1 group by z;

执行计划会告诉这你这条语句走的是索引z

直接排序

原理是，因为mysql会默认走临时表，但是我们可以通过SQL_BIG_RESULT来提示mysql这个结果集很大，让mysql直接选择磁盘临时表，mysql发现这个临时表是b+tree结构，不如数组方式排序效率高，就不会采用临时表而是至用filesortset了

sql语句如下

1

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

执行计划如下

1
2
3
4
5

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | PRIMARY,a     | a    | 5       | NULL | 1000 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------+

语句执行流程是：

1. 初始化sort_buffer，去顶放入整形字段m
2. 扫秒表t1索引树a,将id%100值放入sort_buffer中
3. 在sort_buffer对m排序
4. 返回数组

总结mysql何时会用临时表呢

1. 如果mysql一边读数据，一边直接获取结果不需要额外的内存保存结果，否则会需要额外内存；
2. join_buffer是无序数据，sort_buffer是有序数组，临时表是二维表结构
3. 如果执行逻辑需要二维表特性，就优先使用临时表，比如:Union需要唯一约束，groupby需要额外保存计数

外部临时表

本问主要解决如下的问题：

1. 临时表为什么能重名
2. 什么情况下使用临时表

临时表和引擎是Mermory表的区别

• 首先内存表的数据是保存到内存中的，当数据库关闭后数据会消失，只保留表结构。
• 而临时表是可以指定任意表引擎的，且临时表和当前Mysql的htread现关，当一个session关闭后临时表会被删掉。
• 不同session的临时表可以重名

为什么临时表能重名

临时表是和当前的线程相关：

• Mysql5.6包含5.6之前，Mysql会在临时文件目录下建立一个”前缀.ibd”的文件，用来存放数据文件
• Mysql5.7之后的版本，引入了一个临时表空间，专门用来存放练市文件，不需要在创建ibd文件了。
• 前缀是：库名+表名+serverid名+线程id。

Mysql每个名都有一个def_table_name，临时表的表明就是”库名+表名+serverid名+线程id”，所以不同线程之间的临时表是可以重名的。

注：innodb类型的临时表会在临时文件目录下，文件名的是#sql{进程 id}+{线程 id}+序列号，语句如下：

1

create temporary table temp_t(id int primary key)engine=innodb;

如何使用临时表

1
2
3
4
5
6
7
8
9
10
11

#建表语法是：
create temporary table temp_t like t1;

#查看临时表
show create table temp_t;

#对临时表操作
alter table temp_t add index(b);
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

mysql每个库都维护一个表名的链表，临时表排在前面，查找表名时候会轮训链表，发现表名对应的表是临时表时候会优先使用临时表。

临时表的使用场景

分库分表：比如我们按照字段对一个表进行分库分表，当有一个查询条件不是按照分表规则进行查询时候有俩种解决方法

1. proxy来做聚合
2. 通过临时表聚合

临时表的主备库处理

• 当binlog格式是state时候，主从同步时候要把临时表涉及的binglo都同步过去
• 如果是row格式则不用。
• 另外删除临时表的binlog会在一条语句中完成，所以只有drop语句binlog会格式化，crud的语句statement会原封不懂的传递给从库

MySql的join

我们在考虑sql语句用join的时候往往基于以下两点来看：

1. 业务是否能用join
2. 哪个表是驱动表

Index Nested-Loop Join（NLJ）

见下面的语句

1
2

# a有索引
select * from t1 straight_join t2 on (t1.a=t2.a);

这条语句的执行顺序如下：

1. 表T读出一行数据R
2. 用数据R到t2中找
3. 取出t2中的一行作为结果集
4. 重复1-3步骤,t1循环结束

这里先对是t1的全表扫描,t2查找相关数据，时间复杂度（N+N*2log2N 注意N是驱动表，2log2N是采用2级索引），如果是采用这种nlj算法，显然join会比用程序一行行循环查找要好。因为减少了和数据库的交互次数

同时因为时间复杂度是N+N*2log2N ，所以N越小越好，也就是驱动表是小表。

Block Nested-Loop Join（BNL）

如果join的字段如果没有所以呢?如:

1

select * from t1 straight_join t2 on (t1.a=t2.b);

这时候mysql为了防止多次扫描驱动表，会用到join_buffer，通过参数join_buffer_size，默认是256K

执行的过程如下：

1. 扫描表t1，顺序读取t1，数据放到join_buffer中，如果超过join_buffer就放一部分
2. 扫描表t2，把t2中的每一行取出来，跟join_buffer做对比，满足条件的当结果集返回
3. 清空join_buffer
4. 继续扫描表t1,执行步骤2-3直到结束。

当t1表数据不超过join_buffer_size时候，谁当驱动表都无所谓；否则的话小表当驱动表，减少写join_buffer次数。

BNL的坏处：

1. 多次扫描被驱动表，增大磁盘的IO负担
2. 内存中计算的时间复杂度可能是M*N,增大CPU负担
3. 影响buffer_pool：对于大的冷数据表的join查询，可能会将这些数据移到buffer_pool的young区，影响业务的buff_pool命中率，而1、2在语句结束后会停止影响，可是3的影响是持续的需要后面业务主键淘汰掉这些数据页的buffer

优化建议

对于NLJ

可以开启mrr（multi-range-read）和bka(batch-key-access)

• 原理是 原来是需要从驱动表找到一条数据，然后一条去被驱动表查询。且是随机读，速度很慢；
• 开启mrr和bak
  • mrr原理是在查询的索引上做一次排序，然后把随机度改为顺序读。
  • bak是基于mrr，将join时候从驱动表中尽量多取出一些行（只保留必要字段），放到join_buffer中如果超过join_buffer_size会分多段，然后去被驱动表中查询。批量的顺序读

1

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

对于BNL

通过建立索引的方式转成NLJ的方式
对于不适合建立索引的，通过业务拆分优化

全表扫描对server的影响

一条返回值聚到的sql语句是怎么处理的

假设我们执行下面的语句执行全表扫描，这条sql语句从server到客户端的流程如下

1

mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file

1. 结果先写入到net_buffer中，由参数net_buffer_lenght决定（def:16m）
2. net_buffer写满后，通过网络接口发送出去
3. 接续写入新的数据到net_buffer
4. 如果发送函数遇到返回值EAGAIN状态，或者WSAEWOULDBLOCK，说明本地网络栈被写满了，进入等待状态，知道网络栈可用继续写入。

可见mysql对于查询结果的发送是边读取边写的这样防止了大的sql语句一次性读写导致oom

如图所示：

send data to client和sending data的区别

上面发送数据给client过程中sql语句状态会被置为send data to client状态，要和sending data区分开

sending data是当一条sql语句执行时候就被设置为该状态，知道这条语句结束，所以sending data并不能表示这条语句正在往客户端发送数据

当数据继续开始往客户端发送时候数据处于send data to client阶段。
当执行器开始执行sql语句时候，这条sql语句处于sending data阶段

mysql_store_result

一般我们都会采用参数：mysql_store_result直接把查询结果保存到本地，而不是mysql_use_result,读一行处理一行，因为如果一个事务逻辑很复杂很可能要处理很久才会去读下一行

全表扫描对innodb的影响

还记得Mysql更新采用了BUFF_POOL+WAL的顺序写机制替代了，随机写，加速了写的性能么？其实BUFF_POOL的这个机制还加速了查询的性能。比如一个sql语句查询数据的时候，如果数据页在内存中，那么就不用扫描磁盘了，一般一个稳定的线上业务buff_pool的磁盘命中率都会很高我们可以用命令show engine innodb status来查看命中率。

那么如果全表扫描一个很大的表，触发了buff_pool的Lru算法是否很极大的降低buff_pool的命中率，增大磁盘的开销呢？mysql是采用什么策略优化这种情况的呢？

Mysql的lru算法如图：

1. mysql吧buff_pool分了young和old代，占比是5:3
2. 如果命中的数据页在young代会被移动到链表的head处
3. 如果新插入了一个数据页，Px,会淘汰最靠近tail的pm,这时候px会插入到old代的头部；
4. 处于old区域的数据页被访问时候，Mysql会有一个策略
   1. 发现数据页已经在old区存在了1秒以上了，会移动到young
   2. 反之，不移动，慢慢被淘汰

mysql用这种策略来优化全表扫描，因为全表扫描时候这些数据页会顺序读，所以肯定不会超过1秒，所以不会移动到young，只会在old代慢慢被淘汰，而且淘汰只会限制在old代，对young完全无影响（正常业务的查询）

误删数据的几种情况

对于误删除数据进行了简单的分类：

1. 使用delete语句误删除数据航；
2. 使用droptable或者truncate table误删除表数据；
3. 使用dropdatabase语句误删除数据库；
4. 使用rm命令删除整个mysql实例；

误删行–用delete

• 方法：可使用工具Flashback来恢复误删除的数据；
• 条件：使用FalshBack的条件，binlog_format=row和 binlog_row_image=FULL
• 原理：解析binlog，并且把binlog的逆向修改，那会原数据库重放；
• 注意：
  • 逆向后不要重新那回主库重放，要先从一个林水库开始，然后由临时库在恢复到主库上，防止直接在一个在线的主库上还原，破坏了数据关联，造成数据2次破坏；
  • 数据库的防范于未然：sql语句的安全模式：set [global] sql_safe_updates = 1，这样当update delete没有where条件时候就会报错；

drop table或者truncate table

delete删除全表往往性能会很差，因为要写undolog redolog binlog等，这时候如果有删除全表的需求就可以考虑用：drop table或者truncate table来删除
。
当使用这几个命令时候我们的binlog是statement格式的，所以也没法用上面的方法恢复数据。

误删库表恢复的流程是怎样的呢？

1. 去最后一次全量备份的库，恢复出一个临时库；
2. 在日志备份里，取当前备份点之后的日志，除了误删除的语句，应用到临时库中；

如图：

注意：

1. 如果临时库的实例上有多个数据库，在使用mysqlbinlog命令可以加上–database参数，来指定误删除库；
2. 恢复数据跳过误删除的binlog的方法上：
   1. 如果原实例没有GTID模式:我们只能在备份日志中先找到误删除的那个操作用-stop-position参数执行前的日志，在用-start-position从误删除之后的操作执行；
   2. GTID模式:我们可以在临时库上执行：set gtid_next=gtid1;begin;commit；先把这个gtid加到临时库的gtidset中，之后重放binlog会自动跳过这个操作

恢复表和库的加速

上面的做法还是不够快，因为：

1. 如果是误删表：我们期望只恢复这张表的数据，但是mysqlbinlog工具不能只解析一张表的日志；
2. 用mysqlbinlog恢复出的应用日志，只能是单线程恢复，无法利用并行复制的功能；

解决方案：

第一种方法：临时库在被全量恢复（设置了gtid后）后，将这个临时库置为主库的从库，然后这个临时库在执行start salve之前执行；

1
2

#让临时库只同步误删除的表，同时主->备可以利用并行复制
change replication filter replicate_do_table = (tbl_name)

如图：

mysql的binlog丢失

有一种情况，如果要恢复的是个很久远的库，备库上又不能无限制的保存binlog,导致需要的binlog备库没有log的情况该如何操作呢？

1. 假设临时库上需要的master.000005，而备库上从master.000007开始的，意味这我们要从备份系统上下载master.000005，master.000006放到备库上
2. 之后打开备库的master.index文件上（根据配置名字可能不同），加上”./master.000005”，”./master.000006”
3. 重启备库，让备库识别这俩个文件
4. 建立主备关系开始同步数据

综上：误删库的恢复是通过定期全量备份+增量备份来恢复数据库，建议DBA把流程做成自动化，同时定期演练减伤损失。

搭建延迟复制备库

假设：当一个大库的备份周期是1周一备份，同时在第6天发生了误操作，这时候就要恢复6天的binlog，恢复时间可能是按天计的，对于在线业务是不允许的。这时候我们可以通过搭建一个延迟赋值的备库来解决。

1. 这个机制是mysql5.6之后引入的有一个机制，我们在备库可以通过命令CHANGE MASTER TO MASTER_DELAY=N（单位：秒），即延迟N秒同步命令。
2. 比如：CHANGE MASTER TO MASTER_DELAY=3600，那么当一个误操作发生后，这个备库会在一个小时以后收到这条命令，我们可以在这段时间内stop salve;
3. 然后恢复这一个小时的数据并且跳过这个命令即可；

预防误删除库/表的方法

我们应该尽量最大化的目标误删除操作：

1. 账号分离：

   1. 业务账号应该只有MDL权限
   2. 日常的数据查询值用只读账号

2. 指定操作规范：

   1. 删除表前线对表改名，观察一段时间
   2. 该表明要有命名规范比如_to_be_deleted

rm删除

HA会选取一个从库作为主库，我们只需要回复这个机器即可；
为防止这个事情发生可以做跨机房或者跨城市备份

mysql的索引-草稿

唯一索引和普通索引区别
读：
唯一索引在查询上和普通索引的差距不大
原因：mysql是将整个数据页加载到内存中，1个数据页有16KB，可以加载很多数据（都是索引和主键指针），儿唯一索引和普通索引的区别就是查询完后是break还是continue
写：
唯一索引，需要在每次写入时候读取数据判断是否唯一，所以不能利用changebuffer。所以更新需要将数据页cp到内存中更新。（如果数据页不巧没在内存中，性能消耗就会很大，数据页加载涉及到随机读）
数据更新，只需要将数据写到changebuffer中，然后在下次用到数据（或者定时）将changebuffer，merge到数据页，所以会很快。

索引的使用上
索引使用是在优化器上进行的。优化器使用索引的依据是索引的基数（不重复的数据，可以用show index xxx看索引基数），使用基数大的索引。当频繁的删除，有可能导致基数不准选错索引。
同时优化器在判断索引时候回考虑，会表的可能有可能会因为回表导致，选错索引

方法
用ANALYZE TABLE table;优化索引
删除不需要的索引

有什么情况用不到索引
原则：对于索引字段进行计算不会走上索引，会采用全索引遍历或者全表扫描
例如：

1. 索引字段加减；a+1=1000
2. 字段类型转化 比如 id='1111'--->CAST(id ,varchar)='1111'
3. 字段隐式类型转化：比如CAST(id,utf8mb4)='xxx'