
innodb是索引存储表，表数据按主键顺序存放

[toc]

# 基础概念

聚簇索引、辅助索引、联合索引

## 聚簇索引

聚集索引按照每张表的主键构造一棵B+树，叶子节点存放的是整张表的行记录数据，叶子节点被称为数据页。

每张表只能按照一颗B+树排序，每张表只能有一个聚集索引

聚集索引对于主键的排序查找和范围查找速度非常快

没有主键或唯一索引，innodb默认会生成一个行号(不可见)，作为聚集索引的主键

## 辅助索引

辅助索引叶子节点不会包含行记录的所有数据。叶子节点除了包含键值以外还会包含主键，来查找叶子节点以外的其它数据。

辅助索引通过主键id来关联记录

## 联合索引

对表上的多个列进行索引

联合索引的技巧

1. 覆盖索引：从索引即可查询到所需字段，不需要查询聚集索引中的记录，减少io操作。

2. 最左前缀：对a,b创建索引

   - 可以用到索引
     - where a = 1;
     - where a = 1 and b = 2;
     - where a = 1 order by b;

   - 用不到索引
     - where b = 2;

3. 索引下推：a like 'hello%’ and b >10，检索出来的数据会先过滤掉b <= 10 的数据，然后在进行回表查询，减少回表查

## MRR(multi-range read)

目的： 减少磁盘的随机访问，将随机访问变为顺序的数据访问。

MRR执行过程：

- 优化器将二级索引查询到的记录放到一块缓存中
- 如果二级索引扫描到文件的末尾或者缓冲区已满，使用快排对缓冲区中的内容按照住建进行排序
- 用户线程调用MRR接口取cluster index, 然后根据cluster index取行数据
- 当根据缓冲区中的cluster index取完数据，继续调用2、3过程，直至扫面结束 

MRR好处：

- 使数据访问变得较为顺序，在查询辅助索引时，首先根据得到的结果，按照主键排序，
- 减少缓冲池中页被替换的次数
- 批量处理对键值的查询

explain 可以看到extra列 using MRR

- [MySQL · 特性分析 · 优化器 MRR & BKA](http://mysql.taobao.org/monthly/2016/01/04/)

## ICP

mysql 5.6开始支持，将where的过滤部分放到存储层

```sql
select * from salaries
where (from_date between "2019-01-10" and "2019-02-10") and (salaries between 3800 and 4000)
```

不启用ICP，则数据需要先取出from_date between 2019-01-10 and 2019-02-10的数据，然后再过滤 3800-4000的数据

启用ICP，则会在索引取出时就会进行where过滤，前提是where条件被索引覆盖

explain 可以看到extra列 using index condition

- [MySQL · 特性分析 · Index Condition Pushdown (ICP)](http://mysql.taobao.org/monthly/2015/12/08/)

# 疑难杂症

## 普通索引和唯一索引应该怎么选择？

例如身份证号，身份证号具有唯一性，由于身份证号比较大，不建议把身份证号作为主键，要么给身份证号加一个唯一索引，要么加一个普通索引。如果业务代码已经保证不会写入重复的身份证号，那么是选择普通索引还是唯一索引比较好？

查询过程：

- 对于普通索引，查找到第一个满足条件的索引后，需要继续查找下一个记录，知道碰到第一个不满足条件的记录
- 对于唯一索引，由于索引定义了唯一性，查找到第一个满足条件的记录后，会停止继续检索

这点查询性能影响很小，可以忽略，所以唯一索引和普通索引查询性能相差不多(别钻牛角尖，如果是字段重复度高，这种情况不符合我们讨论的场景)。

更新过程：

​	对于插入一条(4,500)

- 这个记录要更新的目标页在内存中
  - 唯一索引，找到3和5之间的位置，判断没有冲突值，插入这个值，语句结束；
  - 普通索引，找到3和5之间的位置，插入这个值，语句结束
- 这个记录要更新的目标页没有在内存中
  - 唯一索引，需要将目标页读入到内存中，判断没有冲突，插入这个值，语句结束
  - 普通索引，将更新记录在change buffer中，语句执行结束

数据从磁盘读入到内存涉及到随机I/O的访问，是数据库成本最高的操作之一。change buffer减少了随机磁盘访问。对于提升更新性能很明显

## change buffer

**什么条件下可以使用change buffer？**

对于唯一索引，所有的更新操作都要先判断这个操作是否违反唯一约束，要判断值是否就必须先把数据页读入到内存中才能判断，如果已经读入到内存中，那直接更新内存会更快，没必要使用change buffer了

唯一索引更新就不能使用change buffer了，实际上只有普通索引可以使用

**change buffer的使用场景？**

- change buffer对于更新过程有加速作用
- merge是数据真正更新的时刻，在一个数据库做merge之前，change buffer记录的越多，收益越大。写多读少的场景change buffer使用效果最好。
- 如果一个业务的更新模式写入之后马上做查询，将更新记录在了change buffer中，但之后由于马上要访问这个数据页，马上会触发merge，反而增加change buffer的维护成本。

**change buffer和redo log**

change buffer主要是在更新时，不用将数据读入到内存中，节省的是随机读磁盘的I/O消耗。

redo log主要节省的是随机写磁盘的I/O消耗（顺序写）

## 如何重建索引和主键索引？

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，造成空间浪费

```sql
alter table T engine=InnoDB;
```

## 字符串如何创建索引?

**前缀索引**

定义好长度，可以做到既节省空间，又不用额外增加太多的查询成本。

例如邮箱，对于每个记录都是只取前6个字节

```sql
alter table user add index index2(email(6));
```

- 占用的空间更小
- 增加额外的扫描次数
- 用不上覆盖索引对查询性能的优化，需要回主键索引查一次

```sql
select id, email from SUser where email='douyacun@gmail.com';
```

当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多长的前缀呢？

```sql
select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from user;
```

**倒序存储**

例如：身份证号前面的几位是地址码重复度高，这个索引的区分度就非常低了。按照前缀索引来创建的索引需要创建12位以上，才能满足区分度要求。

存储身份证号的时候把它倒过来存，每次查询的时候，可以这么写：

```sql
select field_list from t where id_card = reverse(:id_card);
```

**hash字段**

使用crc32函数得到校验码使用整型存储，只在这个列上加索引，索引空间就会小很多。但是CRC32函数很容易碰撞，需要加上原条件判断

```sql
select field_list from t where id_card_crc = crc32(:id_card) and id_card = :id_card;
```

普通索引和唯一索引应该怎么选择？字符串如何加索引才能更高效并且节省空间？

mysql索引


[toc]

# 全局锁

对整个数据实例加锁，FTWRL

```sql
flush table with read lock
```

使用这个语句后其他线程的语句会阻塞：数据更新语句，数据表定义语句和更新事务的提交语句

**使用场景**：全库逻辑备份，mysqldump导出的时候使用MVCC的一致性保证innodb导出数据，但是对于MyISAM来说是不支持的，备份只能通过FTWRL来保证

**为什么不用 set global readonly = true**

1.  readonly的值，一般被用作判断一个库是主库还是从库，修改global变量的方式影响面大，不建议使用
2.  异常处理机制上，如果执行FTWRT由于客户端发生异常断开，mysql会自动释放这个全局锁，而设置全局readonly后，如果客户端发生异常，则数据库会一直保持readonly状态，导致整个库事件处于不可写状态，风险较高

# 表级锁

mysql表级锁有两种: 

**表锁**

与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放

```sql
lock tables t1 read, t2 write;
```

其他线程写t1，读写t2都会被阻塞，该线程也只能读t1，读写t2

不推荐使用，粒度太大，目前很少见到了

**MDL（元数据锁，meta data lock）**

MDL不需要显示使用，在访问一个表的时候会自动加上，MDL的作用是，保证读写的正确性，防止在一个查询在执行期间另一线程对这个表结构做变更，删除了一列，导致查询线程的结果和表结构对不上。

对表做CURD时，加MDL读锁；对表结构做变更操作时，加MDL写锁；

-   MDL读锁之间不互斥，可以并发读写
-   MDL写锁互斥，两个线程同时修改，只能等拿到锁的线程执行完成

| 时间点 | 会话A                                | 会话B                        | 会话C                             | 会话D                                                |
| ------ | ------------------------------------ | ---------------------------- | --------------------------------- | ---------------------------------------------------- |
| 1      | begin; <br />Select count(*) from t; |                              |                                   |                                                      |
| 2      |                                      | Select * from t where id =2; |                                   |                                                      |
| 3      |                                      |                              | alter  table t add column c3 int; |                                                      |
| 4      |                                      |                              |                                   | Show  processlist；B：copy to tmp table              |
| 5      |                                      |                              | 阻塞                              | Show  processlist;B：Waiting for table metadata lock |
| 6      | A：执行完毕                          |                              |                                   |                                                      |
| 7      |                                      | B: 执行完成                  |                                   | Show  processlist; B：rename table                   |
| 8      | Select  count(*) from t;             |                              |                                   |                                                      |
| 9      |                                      |                              | C：执行完毕                       |                                                      |
| 10     |                                      |                              |                                   | Show  processlist; A: Sending data                   |
| 11     | A:执行完毕                           |                              |                                   |                                                      |

session A先启动，这是会对t加一个MDL读锁，session B需要的也是加MDL读锁。读锁不互斥可以正常执行

而session C需要MDL写锁，而之后所有要在表t上申请MDL读锁的请求也会被session C阻塞。等于现在这个表t完全不可读写了，而客户端超时会有重试机制，新的session请求，这个库的线程很快就爆满。

**如何安全的进行表结构变更**

如果是热表的话，就麻烦了，虽然数据量不大，但是请求频繁。这时候kill掉未必管用，因为新的请求马上就来了，一般能遇到这种情况的公司都是有点实力的了，必定会有DBA的支撑了，很幸运和DBA聊了一下

一般表结构变更直接执行就好了，如果遇到一直拿不到MDL锁的情况, 考虑

-   处理慢查询，商量能不能kill
-   解决长事务，会一直占着MDL锁，[长事物解决方案](innodb事务#长事务)
-   对于热表的话，因为查询很频繁，会一直拿不到MDL写锁，考虑晚上执行，到这就很头疼了。
-   如果是上亿数据的大表（数据几千万，甚至过亿）的话, 或者说一直拿不到MDL写锁的话，推荐方案：
    -   [pt-online-schema-change](https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html)
    -   [gh-ost](https://github.com/github/gh-ost)
    -   [阿里云-不锁表结构变更](https://help.aliyun.com/document_detail/98373.html)

主要原理：

-   1、创建临时表：CREATE TABLE tmp_table_table LIKE table_name
-   2、变更临时表结构： ALTER TABLE tmp_table_table XXXX
-   3、全量拷贝数据：INSERT IGNORE INTO tmp_table_table (SELECT %s FROM table_name FORCE INDEX (%s) WHERE xxx
-   4、增量数据binlog同步： UPDATA/INSRT/DELETE tmp_table_name
-   5、切换新旧表： RENAME TABLE table_name to old_tmp_table_table, tmp_table_name to table_name



# 行锁

**两阶段锁协议**

解锁阶段：事务提交或者回滚

加锁阶段：第一个变更开始

在innodb事务中，行锁是在需要的时候才加上的，但并不是不需要了就立即释放，而是要等到事务结束时才释放，这个就是两阶段锁协议

**加锁原则**

1.  原则1：加锁的基本单位是next-key lock。next-key lock是前开后闭区间。
2.  原则2：查找过程中访问到的对象才会加锁。
3.  优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
4.  优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
5.  一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

## 三种算法

**幻像读**

innodb事物默认的隔离级别是REPEATBLE RRAD， 

幻想读是指在同一事物下，连续执行两次相同的sql，会有不同的结果，第二次sql可能会返回之前不存在的行。

next key lock是如何解决的?

```sql
select * from t where key > 20 for update;
```

next key lock会锁住(20, ∞]区间，在此事物未提交期间，key > 20记录都是不能插入的。

- Record Lock

单个行记录上锁，总是锁住索引记录。如果表在建立是并没有建立索引，innodb会使用隐式的主键来锁

索引可以是主键、唯一、普通索引

- Gap lock

间隙锁，锁定一个范围，但不包含记录本身。

- Next key lock

gap lock + record lock 锁定范围并锁定记录本身。

innodb对于行的查询采用的是这种方式，eg. 有一个索引，有10、11、13、20四个值，那么该索引被Next key lock的区间是

- (-∞, 10)
- [10, 11)
- [11, 13)
- [13, 20)
- [20, +∞)

**等值间隙锁**

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

insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
```



| session A                             | session B                                | session C                             |
| ------------------------------------- | ---------------------------------------- | ------------------------------------- |
| begin;                                |                                          |                                       |
| update t set  d = d + 1 where id = 7; |                                          |                                       |
|                                       | insert into t  values(8,8,8);     block; |                                       |
|                                       |                                          | update t set d = d + 1 where id = 10; |

Session B 会被block，因为session A的加锁区间是(5,10]; 因为id=7不存在，退化为间隙锁。session B会被阻塞，10是开区间，这行是可以修改的。

**非唯一索引等值锁**

| session A                                         | session B                                     | session C                                   |
| ------------------------------------------------- | --------------------------------------------- | ------------------------------------------- |
| begin;                                            |                                               |                                             |
| select id from t where c = 5 lock in  share mode; |                                               |                                             |
|                                                   | update t set d = d + 1 where id = 5;          |                                             |
|                                                   | update t set d = d + 1 where c =  5; (block); |                                             |
|                                                   |                                               | insert into t values  (7,7,7);     (block); |

1. `update t set d = d + 1 where id = 5;` 为什么不会阻塞？

where条件是id = 5，session A `select id from t where c = 5 lock in  share mode;` 

lock in share mode只锁**覆盖索引**，不会影响主键索引。for update 就不一样了，系统会认为接下来会更新数据，顺便会对主键索引上满足条件的索引加锁。

如果session A查询是 `select d from t where c = 5 lock in share mode;`  session B的`update t set d = d + 1 where id = 5;`  就会被阻塞了。

2. `update t set d = d + 1 where c = 5; ` 为什么会阻塞？

加锁原则中的优化2: 索引上等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。加锁的范围会变为 (c, 10];  session c `insert into t values  (7,7,7);`  也会被阻塞



## 死锁

并发线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入等待的状态。超时以后客户端会又重试机制，这时mysql的连接数就上来了，负载飙升。

**死锁处理策略**：

- 超时策略：`innodb_lock_wait_timeout` 默认50s
- 死锁检测：`innodb_deadlock_detect` 默认时开启的

**避免死锁的建议**：

- 通过两阶段锁协议我们知道：行锁是在事务提交的时候才会释放锁，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
- 控制访问相同资源的并发事务量，比如：抢购商品分成10条记录，或放入队列

## 乐观锁

[乐观锁应用场景](https://segmentfault.com/a/1190000008935924)

总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，不会上锁，但是更新时会判断在此期间有没有人更新这个数据。实现方式有两种：

- 使用数据版本(version)记录机制实现
- 使用时间戳

```sql
SELECT * FROM table_name WHERE ... FOR UPDATE。
```

select： 读锁，共享锁
update \ insert \ delete : 每次都会自己加排他锁

一个商品A, 库存有10个， 一百人来抢，不要超卖
update table set num=num-1 where num=10;

select num from table;
update table set num=num-1 where num=9;

## 悲观锁: 

锁的是索引， 如果没有索引，直接锁表。

总是假设最坏的情况，每次去拿数据时总会假设别人会修改，所以每次在拿数据时都会加锁。别人想拿这条数据就会阻塞直到他拿到锁，

### 共享锁

读取的任何行上设置共享模式锁定，其他会话可以读取行，但是在事务提交之前不能修改它们，如果这些行中的任何一个被尚未提交的另一个事务修改，则查询等待直到该事务结束，然后使用最新职

```sql
-- 5.7
SELECT * FROM table_name WHERE ... lock in share mode
-- 8.0
SELECT * FROM table_name WHERE ... for share
```

### 排他锁 

排他锁也叫写锁，简称x锁，原理：一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁（排他锁或者共享锁），即一个事务在读取一个数据行的时候，其他事务不能对该数据行进行增删改查。

子查询中的行不会被锁定，除非在子查询中也指定了锁

```sql
SELECT * FROM table_name WHERE ... FOR UPDATE。
```

注意点:
- 对于select语句，innodb不会加任何锁，也就是可以多个并发去进行select的操作，不会有任何的锁冲突，因为根本没有锁。
- 对于insert，update，delete操作，innodb会自动给涉及到的数据加排他锁，只有查询select需要我们手动设置排他锁。
- 共享锁和排他锁都是锁的行记录，意向共享锁和意向排他锁锁定的是表。

悲观锁，每次在拿数据都会上锁，其他session想拿这个数据就会被阻塞知道它拿到锁

**脏读**

脏数据：未提交的数据

脏读：一个事物读取到了另一个事物未提交的数据

脏读违背了事物的隔离性，关注一下innodb是如何隔离事务的。

现在不会见到脏读了，数据隔离级别要设置成 READ UNCOMMITTED级别才可以，现在默认级别是READ REAPEATABLE

那么READ UNCOMMITTED只有特殊场景才会遇到，如：slave

**不可重复读**

不可重读读是指：在一个事物内多次读取同一数据集合，在这个事物还没有结束时，另一个事物对该数据集合做了一些DML操作。这样就发生了同一事物内两次读取同一数据集合，数据不一致。

不可重复读和脏读的区别是，脏读是读未提交，而不可重复读的已经提交

next key lock算法下，不仅锁住扫描到的索引，还锁住了这些索引覆盖的范围，这个范围内的插入是不允许的，这样就避免了另外一个事物在这个范围内插入数据，导致多次读取数据不一致。

**阻塞**

一个事物中的锁需要等待另一个事物中的锁释放他所占的资源，这就是阻塞

**innodb_lock_wait_timeout** 用来控制等待的时间默认50秒

**innodb_rollback_on_timeout** 用来设置在等待超时是否对进行中的事物进行回滚。



为啥数据库CPU会飙升，真的是业务上涨导致的吗，考虑过死锁吗？如何避免死锁？ 什么是MDL锁？

mysql锁

mysql中常见的日志文件：
- 错误日志(error log)
- 二进制日志(binlog)
- 慢查询日志(slow log)
- 查询日志(log)

# 错误日志
查看错误日志的位置
```shell
mysql> show variables like 'log_error'\G;
```

# 慢查询日志

开启慢查询日志`log_slow_queries`
```shell
mysql> show variables like 'log_slowqueries'\G;
```
慢查询时间`long_query_time`
```shell
mysql> show variables like 'long_query_time'\G
```

sql没有用到索引也会记录到慢查询日志
```shell
mysql> show variables like 'long_throttle_queries_not_using_indexes'\G;
```

mysqldumpslow查看执行时间最长的10条sql
```shell
$ mysqldumpslow -s -al -n 10 mysqlslow.log
```

mysql支持将慢查询日志写入到slow_log表，**log_output**指定慢查询输出格式，默认为file，设置为table会写入到slow_log表

```shell
mysql> show create table mysql.slow_log;
```

# rds执行日志记录sls
阿里云关系数据库RDS（MySQL版本）的SQL执行日志已经与日志服务打通，提供准实时的SQL执行分析功能，并提供内置可深钻的报表、支持自定义审计规则、异常报警、报表订阅与生态对接能力等。
- 据库的登录、退出。
- DDL（Data Definition Language）：对数据库结构定义的SQL，例如CREATE、ALTER DROP、TRUNCATE、COMMENT等。
- DML（Data Manipulation Language）：SQL操作语句，包括SELECT、INSERT、UPDATE、DELETE等。
- 其他SQL执行，包括任何其他通过SQL执行的控制，例如回滚、控制等。
- 失败的SQL执行也会被记录。
- 对SQL执行的延迟、执行结果、影响的行数等信息的提取。

https://help.aliyun.com/document_detail/118266.html

# 二进制日志
- max_binlog_size: 二进制文件最大值，超过该值产生新的文件，后缀加1
- binlog_cache_size: innodb所有未提交的事物都会被记录缓存中去，事物提交时，在写二进制日志中。当一个session开启一个事物时，会自动初始化一个大小为binlog_cache_size大小的缓存，当事物的记录大于binlog_cache_size的时候，会把缓冲的日志写入一个临时文件
    - binlog_cache_use 缓冲写二进制日志次数 
    - binlog_cache_disk_use 临时文件写二进制日志次数

查看binlog文件的内容：
```shell
mysql> mysqlbinlog --start-position=203 douyacun.00007
```

# 套接字文件
本地mysql可以使用套接字文件来连接
```shell
mysql> show variables like 'socket';
```

# pid文件
进程pid，可以用pid文件来重启进程
```shell
mysql> show variables like "";
```



错误日志,慢查询日志,查询日志,二进制日志

mysql日志文件

**mysql支持输入表情符号的问题**

1. 备份,备份所有需要升级的字符串编码的数据库

    - dump出来

2. 升级

    utf8mb4是MySQL5.5.3版本之后支持的字符集

3. 修改

    可以为一个database设置字符编码，可以为一张表设置字符编码，甚至可以为某一个字段设置字符编码

    - 查看当前系统默认的字符集设置

    ```
    mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
    +--------------------------+-----------------+
    | Variable_name            | Value           |
    +--------------------------+-----------------+
    | character_set_client     | utf8            |
    | character_set_connection | utf8            |
    | character_set_database   | utf8            |
    | character_set_filesystem | binary          |
    | character_set_results    | utf8            |
    | character_set_server     | utf8            |
    | character_set_system     | utf8            |
    | collation_connection     | utf8_general_ci |
    | collation_database       | utf8_general_ci |
    | collation_server         | utf8_general_ci |
    +--------------------------+-----------------+
    10 rows in set (0.01 sec)

    mysql>

    ```

    - 查看database的字符编码

    ```
    mysql> show create database polarsnow;
    +-----------+--------------------------------------------------------------------+
    | Database  | Create Database                                                    |
    +-----------+--------------------------------------------------------------------+
    | polarsnow | CREATE DATABASE `polarsnow` /*!40100 DEFAULT CHARACTER SET utf8 */ |
    +-----------+--------------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql>
    ```

    - 查看table的字符编码

    ```
    mysql> show create table ps;
    +-------+---------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                |
    +-------+---------------------------------------------------------------------------------------------+
    | ps    | CREATE TABLE `ps` (
      `name` varchar(100) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +-------+---------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    ```

    - 查看column的字符编码

    ```
    mysql> show full columns from ps;
    +-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
    | Field | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
    +-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
    | name  | varchar(100) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
    +-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
    1 row in set (0.00 sec)
    ```

    - 修改database默认的字符集
    ```
    ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

    mysql> ALTER DATABASE polarsnow CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
    Query OK, 1 row affected (0.03 sec)

    mysql> show create database polarsnow;
    +-----------+--------------------------------------------------------------------------------------------------+
    | Database  | Create Database                                                                                  |
    +-----------+--------------------------------------------------------------------------------------------------+
    | polarsnow | CREATE DATABASE `polarsnow` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
    +-----------+--------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql> show tables;
    +---------------------+
    | Tables_in_polarsnow |
    +---------------------+
    | ps                  |
    +---------------------+
    1 row in set (0.00 sec)

    mysql> show create table ps;
    +-------+---------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                |
    +-------+---------------------------------------------------------------------------------------------+
    | ps    | CREATE TABLE `ps` (
      `name` varchar(100) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +-------+---------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql> show full columns from ps;
    +-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
    | Field | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
    +-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
    | name  | varchar(100) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
    +-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
    1 row in set (0.00 sec)

    mysql> create table test_tb2 (tb2 varchar(100) );
    Query OK, 0 rows affected (0.21 sec)

    mysql> show tables;
    +---------------------+
    | Tables_in_polarsnow |
    +---------------------+
    | ps                  |
    | test_tb2            |
    +---------------------+
    2 rows in set (0.00 sec)

    mysql> show create table test_tb2;
    +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table    | Create Table                                                                                                                                              |
    +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
    | test_tb2 | CREATE TABLE `test_tb2` (
      `tb2` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
    +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql>
    ```

    - 修改column默认的字符集

    ` ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; `

4. 修改配置文件
```
SET NAMES utf8 COLLATE utf8_unicode_ci becomes SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci

> vim /etc/my.cnf
# 对本地的mysql客户端的配置
[client]
default-character-set = utf8mb4

# 对其他远程连接的mysql客户端的配置
[mysql]
default-character-set = utf8mb4

# 本地mysql服务的配置
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
> service mysqld restart
```

5. 检查修改
    ```
    mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
    +--------------------------+--------------------+
    | Variable_name            | Value              |
    +--------------------------+--------------------+
    | character_set_client     | utf8mb4            |
    | character_set_connection | utf8mb4            |
    | character_set_database   | utf8mb4            |
    | character_set_filesystem | binary             |
    | character_set_results    | utf8mb4            |
    | character_set_server     | utf8mb4            |
    | character_set_system     | utf8               |
    | collation_connection     | utf8mb4_unicode_ci |
    | collation_database       | utf8mb4_unicode_ci |
    | collation_server         | utf8mb4_unicode_ci |
    +--------------------------+--------------------+
    10 rows in set (0.00 sec)
    ```

6. 修改 & 优化所有数据表

    ```
    mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
    +--------------------------+--------------------+
    | Variable_name            | Value              |
    +--------------------------+--------------------+
    | character_set_client     | utf8mb4            |
    | character_set_connection | utf8mb4            |
    | character_set_database   | utf8mb4            |
    | character_set_filesystem | binary             |
    | character_set_results    | utf8mb4            |
    | character_set_server     | utf8mb4            |
    | character_set_system     | utf8               |
    | collation_connection     | utf8mb4_unicode_ci |
    | collation_database       | utf8mb4_unicode_ci |
    | collation_server         | utf8mb4_unicode_ci |
    +--------------------------+--------------------+
    10 rows in set (0.00 sec)
    ```

7. 最重要的修改 php 的数据库连接,不用担心, `utf8mb4` 支持 `utf8` 的链接!

    ```
    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST'),
        'port' => env('DB_PORT'),
        'database' =>env('BYIDB_DATABASE'),
        'username' => env('DB_USERNAME'),
        'password' => env('DB_PASSWORD'),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],
    ```

utf8mb4格式存储数据


家里一台联想笔记本作为爬虫服务器，有一台公网阿里云服务器，目的爬虫跑完之后，公网服务器可以主从同步，家里的服务器和公网服务器通信的问题通过[内网穿透工具(frp)](https://github.com/fatedier/frp)解决, 优秀简单，具体可以看[frp内网穿透_mysql服务_ssh服务_http服务](/linux/frp内网穿透_mysql服务_ssh服务_http服务.md)

MYSQL主从同步是在MySQL主从复制(Master-Slave Replication)基础上实现的，通过设置在Master MySQL上的binlog(使其处于打开状态)，Slave MySQL上通过一个I/O线程从Master MySQL上读取binlog，然后传输到Slave MySQL的中继日志中，然后Slave MySQL的SQL线程从中继日志中读取中继日志，然后应用到Slave MySQL的数据库中。这样实现了主从数据同步功能。

# 配置

## 主库

1. 配置
```conf
# 日志文件名
log-bin = mysql-bin

# 主数据库端ID号
server-id = 1

# 不同步哪些数据库
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema

# 只同步哪些数据库，除此之外，其他不同步
binlog-do-db = game

# 日志保留时间
expire_logs_days = 10

# 控制binlog的写入频率。每执行多少次事务写入一次
# 这个参数性能消耗很大，但可减小MySQL崩溃造成的损失
sync_binlog = 5

# 日志格式，建议默认mixed
# statement 保存SQL语句
# row 保存影响记录数据
# mixed 前面两种的结合
binlog_format = mixed
```

2. 重启数据库，创建同步账户
```sql
-- 创建slave帐号slave，密码123456, 这里host 是 127.0.0.1，frp连接的内网，不需要开放，大家可以写成%
grant replication slave on *.* to 'slave'@'127.0.0.1' identified by '123456';


CREATE DATABASE videos DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 更新数据库权限
flush privileges;
```
> 可以去这里看[frp内网穿透_mysql服务_ssh服务_http服务](/linux/frp内网穿透_mysql服务_ssh服务_http服务.md)

1. 查询master的状态
```
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      196 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set
```

## 从库

1. 配置
```conf
log-bin=mysql-bin
server-id=20
```

2. 执行同步命令
```sql
-- 执行同步命令，设置主数据库ip，同步帐号密码，同步位置
change master to master_host='127.0.0.1',master_port=3307, master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=151459295;

-- 开启同步功能
start slave;
```

3. 检查从库状态, 在执行几条sql看看sql同步情况
```
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: slave
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 185295
               Relay_Log_File: 00h-relay-bin.000002
                Relay_Log_Pos: 756
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes    -----------> 这里是连接成功
            Slave_SQL_Running: Yes    -----------> 这里sql执行成功
            ......
1 row in set (0.00 sec)
```

4. 常用
```sql
-- 停止主从同步
stop slave;

-- 连接断开时，重新连接超时时间
change master to master_connect_retry=50;

-- 开启主从同步
start slave;
```


# 关于`binlog`和`relaylog`

## 格式化

主库binlog
```
 mysqlbinlog --base64-output=decode-rows -v -v /var/lib/mysql/mysql-bin.000001 > binlog
```

从库relaylog
```
mysqlbinlog --base64-output=decode-rows -v -v mysql-relay-bin.000001 > relaylog
```

##  binlog和relaylog的rotate机制
binlog
- Rotate：每一条binary log写入完成后，都会判断当前文件是否超过 max_binlog_size(默认1g)，如果超过则自动生成一个binlog file
- delete: expire-logs-days 只在 实例启动时 和 flush logs 时以及文件文件是否超过 max_binlog_size时判断是否有过期的binlog文件，如果文件访问时间早于设定值，则purge file

relaylog
- Rotate：每从Master fetch一个events后，判断当前文件是否超过 max_relay_log_size（默认1g) 如果超过则自动生成一个新的relay-log-file
- Delete：purge-relay-log 在SQL Thread每执行完一个events时判断，如果该relay-log 已经不再需要则自动删除


## 配置解析

binlog 配置
```
mysql> show variables like "%bin%";
+--------------------------------------------+--------------------------------+
| Variable_name                              | Value                          |
+--------------------------------------------+--------------------------------+
| bind_address                               | *                              |
| binlog_cache_size                          | 32768                          |
| binlog_checksum                            | CRC32                          |
| binlog_direct_non_transactional_updates    | OFF                            |
| binlog_error_action                        | ABORT_SERVER                   |
| binlog_format                              | ROW                            |
| binlog_group_commit_sync_delay             | 0                              |
| binlog_group_commit_sync_no_delay_count    | 0                              |
| binlog_gtid_simple_recovery                | ON                             |
| binlog_max_flush_queue_time                | 0                              |
| binlog_order_commits                       | ON                             |
| binlog_row_image                           | FULL                           |
| binlog_rows_query_log_events               | OFF                            |
| binlog_stmt_cache_size                     | 32768                          |
| binlog_transaction_dependency_history_size | 25000                          |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER                   |
| innodb_api_enable_binlog                   | OFF                            |
| innodb_locks_unsafe_for_binlog             | OFF                            |
| log_bin                                    | ON                             |
| log_bin_basename                           | /var/lib/mysql/mysql-bin       |
| log_bin_index                              | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators            | OFF                            |
| log_bin_use_v1_row_events                  | OFF                            |
| log_statements_unsafe_for_binlog           | ON                             |
| max_binlog_cache_size                      | 18446744073709547520           |
| max_binlog_size                            | 1073741824                     |
| max_binlog_stmt_cache_size                 | 18446744073709547520           |
| sql_log_bin                                | ON                             |
| sync_binlog                                | 1                              |
+--------------------------------------------+--------------------------------+
29 rows in set (0.01 sec)
```

- log_bin
设置此参数表示启用binlog功能，并指定路径名称
- log_bin_index
设置此参数是指定二进制索引文件的路径与名称
- binlog_do_db
此参数表示只记录指定数据库的二进制日志
- binlog_ignore_db
此参数表示不记录指定的数据库的二进制日志
- max_binlog_cache_size
此参数表示binlog使用的内存最大的尺寸
- binlog_cache_size
此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。
- binlog_cache_use：使用二进制日志缓存的事务数量
- binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
- max_binlog_size
Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束
- sync_binlog”：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。
从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多

relaylog 配置
```
mysql> show variables like '%relay%';
+---------------------------+------------------------------------+
| Variable_name             | Value                              |
+---------------------------+------------------------------------+
| max_relay_log_size        | 0                                  |
| relay_log                 |                                    |
| relay_log_basename        | /var/lib/mysql/00h-relay-bin       |
| relay_log_index           | /var/lib/mysql/00h-relay-bin.index |
| relay_log_info_file       | relay-log.info                     |
| relay_log_info_repository | FILE                               |
| relay_log_purge           | ON                                 |
| relay_log_recovery        | OFF                                |
| relay_log_space_limit     | 0                                  |
| sync_relay_log            | 10000                              |
| sync_relay_log_info       | 10000                              |
+---------------------------+------------------------------------+
11 rows in set (0.01 sec)
```

- max_relay_log_size：标记relay log 允许的最大值，如果该值为0，则默认值为max_binlog_size(1G)；如果不为0，则max_relay_log_size则为最大的relay_log文件大小；
- relay_log：定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录，文件名为host_name-relay-bin.nnnnnn（By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory）；
- relay_log_index：同relay_log，定义relay_log的位置和名称；
- relay_log_info_file：设置relay-log.info的位置和名称（relay-log.info记录MASTER的binary_log的恢复位置和relay_log的位置）
- relay_log_purge：是否自动清空不再需要中继日志时。默认值为1(启用)。
- relay_log_recovery：当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。
- relay_log_space_limit：防止中继日志写满磁盘，这里设置中继日志最大限额。但此设置存在主库崩溃，从库中继日志不全的情况，不到万不得已，不推荐使用；
- sync_relay_log：这个参数和sync_binlog是一样的，当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay log中继日志里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。当设置为0时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是0，可动态修改，建议采用默认值。
- sync_relay_log_info：这个参数和sync_relay_log参数一样，当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay-log.info里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。当设置为0时，并不是马上就刷入relay-log.info里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是0，可动态修改，建议采用默认值。


# mysql 主从同步原理
**主从复制原理**

1.  主服务器：binlog线程记录下所有改变了数据库数据的语句，放进master的binlog中
2.  从服务器：在start slave 以后，I/O线程请求主服务器拉去binlog内容，主服务器binlog dump负责响应，存到中继日志(relay log)中. 
3.  从服务器：sql执行线程，执行relay log中的语句

**主从一致性延时，数据恢复**

-   异步复制：mysql复制是异步的，主机将event写入二进制日志，但不知道从库是否或者何时检索并处理了他们，如果主服务器崩溃，则它提交的事务可能不会传输到任何服务器，主服务器到从服务器的故障转移可能会导致未同步事务丢失
-   半同步复制：主库在应答客户端提交的事务前需要保证至少一个从库接受并写到relay log中，半同步服务器通过`rpl_semi_sync_master_wait_point` 控制master在哪个环节接受slave ack, master接收到ack返回状态给客户端
    -   `WAIT_AFTER_COMMIT`: master write binlog -> slave sync binlog -> **master commit** -> **salve ack** -> master return result。
    -   `WAIT_AFTER_SYNC` （默认）:master write binlog -> slave sync binlog -> **salve ack** -> **master commit** -> master return result

# myaql主从同步延时问题

如果主从同步延时比较严重，解决方案：
- 分库，将一个主库拆分为多个主库，每个主库的写压力就小了几倍，此时延迟问题就会小了
- 并行复制，多个库并行复制如果某个库的写入特别高，并行复制还是没
- 重构代码

**主从一致性检测**



-   [MySQL 8 中复制延时的测量与监控分析]([https://dbalife.info/2018/06/12/MySQL-8-%E4%B8%AD%E5%A4%8D%E5%88%B6%E5%BB%B6%E6%97%B6%E7%9A%84%E6%B5%8B%E9%87%8F%E4%B8%8E%E7%9B%91%E6%8E%A7/](https://dbalife.info/2018/06/12/MySQL-8-中复制延时的测量与监控/))

# mysql故障转移

mysql主从同步配置一台内网服务器和一台外网服务器

mysql主从同步



```sql
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

select city,name,age from t where city='杭州' order by name limit 1000;
```



# 全字段排序

排序可能在内存中完成，也可能使用使用外部排序

`sort_buffer_size`

-   如果排序的数据量小于sort buffer size，排序在内存中完成，

-   如果排序数据量大，内存放不下时，就要使用外部排序，一般是归并排序， 将需要排序的数据分成12份，每一份单独排序后存在这些临时文件中。然后把这12个有序文件再合并成一个有序的大文件。

执行过程

1.  初始化sort_buffer，确定放入name、city、age这三个字段
2.  从索引city找到第一个满足city='杭州’条件的主键id
3.  到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中
4.  从索引city取下一个记录的主键id
5.  重复步骤3、4直到city的值不满足查询条件为止
6.  对sort_buffer中的数据按照字段name做快速排序
7.  按照排序结果取前1000行返回给客户端

# rowid排序

如果字段太多，排序的单行长度太大，mysql回采用row id来排序。只把需要排序的字段放入sort buffer，然后根据id的值回到原表取其他的字段

`SET max_length_for_sort_data = 16;` 可以控制排序字段的长度

执行过程

1.  初始化sort_buffer，确定放入两个字段，即name和id
2.  从索引city找到第一个满足city='杭州’条件的主键id
3.  到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中
4.  从索引city取下一个记录的主键id
5.  重复步骤3、4直到不满足city='杭州’条件为止
6.  对sort_buffer中的数据按照字段name进行排序
7.  遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回给客户

# 排序优化

**联合索引**: city取出来的数据是天然排好序的，就不需要在排序了, 创建一个city,name的联合索引

```sql
alter table t add index city_user(city, name);
```

执行过程：

1.  从索引(city,name)找到第一个满足city='杭州’条件的主键id；
2.  到主键id索引取出整行，取name、city、age三个字段的值，作为结果集的一部分直接返回；
3.  从索引(city,name)取下一个记录主键id；
4.  重复步骤2、3，直到查到第1000条记录，或者是不满足city='杭州’条件时循环结束

**索引覆盖**: age，就不需要回主键索引查age了

```sql
alter table t add index city_user(city, name, age);
```

执行过程：

1.  从索引(city,name,age)找到第一个满足city='杭州’条件的记录，取出其中的city、name和age这三个字段的值，作为结果集的一部分直接返回；
2.  从索引(city,name,age)取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
3.  重复执行步骤2，直到查到第1000条记录，或者是不满足city='杭州’条件时循环结束。

# 取随机值

总行数：

```sql
select count(*) as c from t;
```

随机数：

```php
$offset = mt_rand(1, c)
```

跳过offset取1条

```sql
select * from t limit :offset, 1;
```

这样要比`order by rand()` 效率高，且随机性也比较好；

排序模式

# 最终一致性

> 建议阅读
> - [分布式事务一致性，3种模式](https://zhuanlan.zhihu.com/p/36153160)
> - [TCC](https://juejin.im/post/5bf201f7f265da610f63528a)

原则还是尽量保持单机事物，只有单库执行才能保证数据库的ACID特性，遇到跨库事物， 通常通过“最终一致”事物保证事物执行的吞吐量，

- 定义
- 原理

# 定义
系统中所有的数据副本经过一段时间后最终能够达到一致的状态

# 原理
保证核心事物的正向执行，然后保存事物中间状态，其他事物异步执行，执行完成后达到最终的事物一致，避免跨库事物时间序列执行阻塞，提升事物吞吐量

# 实现模式
- 可靠事件模式
- 业务补偿模式
- TCC模式


# 可靠事件

关键点在于：**可靠事件投递** 和 **避免事件重复消费**

可靠事件投递：
  - 每个服务原子的业务操作和发布事件
  - 事件代理系统确保事件传递至少一次，避免重复消费，要求事件消费系统实现 **幂等性**，eg. 支付服务不能因为重复收到事件就多次支付

## 本地事件表

1. 在同一个本地事物中记录业务数据和事件
2. 实时发布一个事件 事件系统 立即通知关联的业务服务，事件发布成功立即删除记录的事件
3. 事件恢复服务定时从事件表中恢复未发布成功的事件，重新发布，重新发布成功才删除记录
  

2要求实时性，第三条保证一定发布，额外的事件数据库可能会给数据库带来额外的压力

## 外部事件表

将事件持久化到外部事件系统，事件系统需要提供实时事件服务以接受微服务发布事件，事件系统需要提供事件恢复服务来确认和恢复服务

1. 业务服务 在事物提交前，通过实时事件服务向事件系统请求 *发送事件*, 事件系统只记录事件不会真正发送事件
2. 业务服务 在事物提交后，向事件系统确认发送，事件得到确认后事件系统才真正发送事件
3. 业务服务 在事物回滚后，请求事件系统取消发送
4. 事件系统定时查找未确认发送的事件向业务服务查询状态，根据返回状态决定取消还是发布

需要一次确认操作 并且需要 业务服务提供额外的查询接口， 事件幂等性


### issue

1. 幂等性

    TODO:: 待完善

    幂等事件要考虑执行顺序，
    - 保证事件顺序的一个简单做法就是在添加时间戳，微服务记录最后处理的时间戳，如果事件戳早于记录的时间戳，丢弃
    - 更稳妥的做法是，每个事件有个全局递增的事件id，为每条事件记录处理结果，如果处理过返回上一次的处理结果，否则调度执行事件

2. 事件消费的开销

    需要考虑重复执行一条事件和查询存储结果的开销

    对于重复处理开销很小的事件，或者只有非常少的事件会被重复处理接收，可以重复处理一次事件，在将事件数据持久化时由数据库 **唯一索引** 抛出唯一性约束异常

    对于重复处理一条事件的开销比额外一次查询的开销要高很多， 可以使用过滤服务过滤处理的过的事件， 每个事件执行完成后记录执行结果

3. 过滤服务是在事件处理完成后记录结果，可能处理中间就再次收到重复事件

    可以记录事件的处理过程：
     - 接收
     - 处理中
     - 收到应答
     - 处理完成
    
    可以根据不同的状态，做不同的处理

4. 业务服务的 消息处理状态，发送请求成功但是没有收到处理结果

    选择暂时不一致性，采用对账和人工接入的方式保证一致性



# 业务补偿模式

- 业务异常：业务逻辑异常，eg. 商品库存不足、账户余额不足
- 技术异常：非业务逻辑异常，eg. 网络连接异常、请求超市

补偿模式使用一个额外的协调服务来保证各个需要保证一致性的微服务，协调服务按顺序调用各个微服务，如果某个微服务调用异常（包括业务异常和技术异常）就取消之前已经调用成功的微服务

为了降低开发的复杂度和效率，协调服务需要实现为一个通用补偿框架，提供服务编排和自动完成补偿的能力
    - 确定失败的步骤和状态，从而确定补偿的范围
    - 能提供补偿操作使用到的业务数据
    - 生成全局唯一的业务流水号
    - 关联表，获取各个步骤的业务数据和状态
    - 提供重试策略



## 重试策略

- 失败原因不是暂时性的，由于业务因素导致的，应立即终止重试
- 错误是一些罕见的异常，eg. 网络传输过程中数据丢失，应该立即重试
- 如果错误的是系统繁忙，eg. 500,或者超时 需要等一段时间再次重试
- 重试操作要指定次数，达到上线就不应该再次重试了
- 如果重试时仍然错误，可逐渐增加时间，直到达到上限后
- 如果某个时刻存在大量重试操作，补偿框架要控制请求的流量，防止对工作服务造成过大的压力


# TCC模式

建议阅读
- [TCC](https://juejin.im/post/5bf201f7f265da610f63528a)

3个阶段

## try
预处理阶段

## confirm
事物提交阶段

## cancel
事物回滚阶段


原则还是尽量保持单机事物，只有单库执行才能保证数据库的ACID特性，遇到跨库事物， 通常通过“最终一致”事物保证事物执行的吞吐量，