【Java开发岗：MySQL篇】

点击：【第一章：Java开发岗：基础篇】

计算机基础问题、HashMap、Fail-safe机制/Fail-fast机制、Synchronized、ThreadLocal、AQS、线程池、JVM内存模型、内存屏障、class文件结构、类加载 机制、双亲委派、垃圾回收算法、垃圾回收器、空间分配担保策略、安全点、JIT技术、可达性分析、强软弱虚引用、gc的过程、三色标记、跨代引用、 逃逸分析、 内存泄漏与溢出、JVM线上调优、CPU飙高系统反应慢怎么排查。

点击：【第二章：Java开发岗：MySQL篇】

隔离级别、ACID底层实现原理、 一致性非锁定读（MVCC的原理）、BufferPool缓存机制、filesort过程、 离散读、ICP优化、全文检索、 行锁、表锁、间隙锁、死锁、主键自增长实现原理、索引数据结构、SQL优化、索引失效的几种情况、聚集索引、辅助索引、覆盖索引、联合索引、redo log、bin log、undolog、分布式事务、SQL的执行流程、重做日志刷盘策略、有MySQL调优、分库分表、主从复制、读写分离、高可用。

点击：【第三章：Java开发岗：Redis篇】

多路复用模式、单线程模型、简单字符串、链表、字典、跳跃表、压缩列表、encoding属性编码、持久化、布隆过滤器、分布式寻址算法、过期策略、内存淘汰策略 、Redis与数据库的数据一致性、Redis分布式锁、热点数据缓存、哨兵模式、集群模式、多级缓存架构、并发竞争、主从架构、集群架构及高可用、缓存雪崩、 缓存穿透、缓存失效。

点击：【第四章：Java开发岗：MQ篇】

RabbitMQ、RockerMQ、Kafka 三种消息中间件出现的消息可靠投递、消息丢失、消息顺序性、消息延迟、过期失效、消息队列满了、消息高可用等问题的解决方案。RabbitMQ的工作模式，RocketMQ的消息类型，Kafka消费模式、主题/分区/日志、核心总控制器以及它的选举机制、Partition副本选举Leader机制、消费者消费消息的offset记录机制、消费者Rebalance机制、Rebalance分区分配策略、Rebalance过程、 producer发布消息机制、HW与LEO、日志分段存储、十亿消息数据线上环境规划、JVM参数设置。

点击：【第五章：Java开发岗：Spring篇】

SpringBean生命周期、Spring循环依赖、Spring容器启动执行流程、Spring事务底层实现原理、Spring IOC容器加载过程、Spring AOP底层实现原理、Spring的自动装配、Spring Boot自动装配、Spring Boot启动过程、SpringMVC执行流程、Mybatis的缓存机制。

点击：【第六章：Java开发岗：SpringCould篇】

微服务构建、客户端负载均衡、服务治理、服务容错保护、声明式服务调用、API网关服务、分布式配置中心、消息总线、消息驱动、分布式服务追踪、分布式事务、流量控制。

点击：【第七章：Java开发岗：项目篇】

简历上面的项目经历怎么写(项目介绍、负责模块、使用技术)，面试项目实战（秒杀下单设计、权限设计、红包雨设计）

---

系列文章：每篇文章字数都是大几万，保证质量，文章以备战面试为背景，薪资参考坐标：上海；每个地方，每个时间段薪资待遇都不一样，文章仅做面试参考，具体能否谈到理想的薪资取决于面试表现、平时的积累、市场行情、机遇。
提示：系列文章还未全部完成，后续的文章，会慢慢补充进去的。

这里总结一下35k的Java开发岗需要掌握的面试题，帮助大家快速复习，突破面试瓶颈。本章主讲Mysql知识点，知识点有：隔离级别、ACID底层实现原理、 一致性非锁定读（MVCC的原理）、BufferPool缓存机制、filesort过程、 离散读、ICP优化、全文检索、 行锁、表锁、间隙锁、死锁、主键自增长实现原理、索引数据结构、SQL优化、索引失效的几种情况、聚集索引、辅助索引、覆盖索引、联合索引、redo log、bin log、undolog、分布式事务、SQL的执行流程、重做日志刷盘策略、有MySQL调优、分库分表、主从复制、读写分离、高可用。大致估算可以讲八小时左右，作为备战面试的Mysql知识点还是很不错的。35k薪资参考的坐标：上海，参考时间：2022年8月。

隔离级别

数据库隔离的四个级别分别为：

Read Uncommitted（读未提交）

在一个事务处理过程里读取了另一个未提交的事务中的数据。会导致脏读。

脏读(Drity Read)：

某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个回滚了操作，则后一个事务所读取的数据就会是不正确的。

举个例子，公司发工资了，领导把四万块钱打到我的账号上，但是该事务并未提交，而我正好去查看账户，发现工资已经到账，是四万，非常高兴。可是不幸的是，领导发现发给我的工资金额不对，是三万五元，于是迅速修改金额，将事务提交，最后我实际的工资只有三万五元，我就白高兴一场。

Read Committed（读已提交）

这是大多数数据库系统的默认隔离级别，但不是MySQL默认的。会导致不可重复读，事务a读取数据，事务b立马修改了这个数据并且提交事务给数据库，事务a再次读取这个数据就得到了不同的结果。

不可重复读(Non-repeatable read):

在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间更新了原有的数据。

举个例子，我拿着工资卡去消费，系统读取到卡里确实有一百块钱，这个时候我的女朋友刚好用我的工资卡在网上转账，把我工资卡的一百块钱转到另一账户，并在我之前提交了事务，当我扣款时，系统检查到我的工资卡已经没有钱，扣款失败，廖志伟十分纳闷，明明卡里有钱的。

Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。会导致幻读，InnoDB和Falcon存储引擎通过多版本并发控制机制解决了该问题。

幻读(Phantom Read):

在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

举个例子，当我拿着工资卡去消费时，一旦系统开始读取工资卡信息，这个时候事务开始，我的女朋友就不可能对该记录进行修改，也就是我的女朋友不能在这个时候转账。这就避免了不可重复读。假设我的女朋友在银行部门工作，她时常通过银行内部系统查看我的工资卡消费记录。有一天，她正在查询到我当月信用卡的总消费金额（select sum(amount) from transaction where month = 本月）为80元，而我此时正好在外面胡吃海喝后在收银台买单，消费1000元，即新增了一条1000元的消费记录（insert transaction … ），并提交了事务，随后我的女朋友把我当月工资卡消费的明细打印到A4纸上，却发现消费总额为1080元，我女朋友很诧异，以为出现了幻觉，幻读就这样产生了。

Serializable（可串行化）（更高级别隔离，避免脏读，避免不可重复读，避免幻读）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

ACID底层实现原理

原子性底层实现原理

A(原子性)，要么全部完成，要么完全不起作用。底层实现是通过undo log日志去实现的，当这个事务对数据库进行修改的时候，innodb 生成对应undo log，undolog有多个版本，并且存放的是与上一个版本相反的操作，他会记录这个SQL执行的相关信息，如果SQL执行失败发生回滚，innodb 根据这个undo log内容去做相反的工作，比如说我执行了一个insert 操作，那么回滚的时候，就会执行一个相反的操作，就是delete,对应update，回滚的时候也是执行相反的update。这就是原子性的底层实现。

一致性实现原理

一旦事务完成，不管成功还是失败，数据处于一致的状态，而不会是部分完成，部分失败。事务执行前后，数据库的完整约束没有遭受破坏，事务执行前后都是合法的一个数据状态。事务的AID是数据库的特征，也就是依赖数据库的具体实现。而唯独这个C，实际上它依赖于应用层，也就是依赖于开发者。这里的一致性，是指数据从一种正确的状态，跳转到另一种正确的状态。

举例：账户A转1000到账户B，A转账的金额，必须小于等于自己的账户余额，即事务提交时，A的账户余额不能为负数，可以通过数据库约束，保证账户金额的字段值大于等于0。

持久性底层实现原理

一旦事务完成，无论发生什么系统错误，它的结果都不会受到影响，事务的结果被写到持久化存储器中。

底层实现原理是：redo log机制去实现的，mysql 的数据是存放在这个磁盘上的，但是每次去读数据都需要通过这个磁盘io，效率就很低，使用 innodb 提供了一个缓存 buffer，这个 buffer 中包含了磁盘部分数据页的一个映射，作为访问数据库的一个缓冲，从数据库读取一个数据，就会先从这个 buffer 中获取，如果 buffer 中没有，就从这个磁盘中获取，读取完再放到这个 buffer 缓冲中，当数据库写入数据的时候，也会首先向这个 buffer 中写入数据，定期将 buffer 中的数据刷新到磁盘中，进行持久化的一个操作。如果 buffer 中的数据还没来得及同步到这个磁盘上，这个时候 MySQL 宕机了，buffer 里面的数据就会丢失，造成数据丢失的情况，持久性就无法保证了。使用 redolog 解决这个问题，当数据库的数据要进行新增或者是修改的时候，除了修改这个 buffer 中的数据，还会把这次的操作写入到这个 redolog 中，如果 msyql 宕机了，就可以通过 redolog 去恢复数据，redolog 是预写式日志，会先将所有的修改写入到日志里面，然后再更新到 buffer 里面，保证了这个数据不会丢失，保证了数据的持久性，redolog 属于记录修改的操作，主要为了提交或者恢复数据使用！

事务隔离性由之前讲述的锁来实现。redo log称为重做日志，用来保证事务的持久性。redo通常是物理日志，记录的是页的物理修改操作。重做日志用来实现事务的持久性，即事务ACID中的D。其由两部分组成：一是内存中的重做日志缓冲，是易丢失的；二是重做日志文件，是持久的。

MySQL中把对底层页面的一次原子访问的过程称之为一个Mini-Transaction（MTR），这里的原子操作，指的是要么全部成功，要么全部失败，不存在中间状态。
Mini-Transaction一般遵循三条原则：
1、the fix rules：修改一个数据页，需要获得这个数据页的x-latch；访问一个页是需要获得s-latch或者x-latch；持有该页的latch直到修改或者访问该页的操作完成才释放。（latch是一种轻量级的锁，它锁定的时间特别短，在innodb中，latch又可以分为mutex(互斥量)和rwlock(读写锁)2种，它的目的在于保证并发线程操作临界资源的正确性。）
2、WAL：持久化一个数据页之前，需要将内存中响应的日志页先持久化
3、force-log-at-commit：在事务提交的时候，其产生的所有MTR日志都要刷到持久化设备中，从而保证崩溃恢复的逻辑。

InnoDB是事务的存储引擎，通过Force Log at Commit机制实现事务的持久性。当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的COMMIT操作完成才算完成。为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB存储引擎都需要调用一次fsync操作。

由于重做日志文件打开并没有使用O_DIRECT选项，因此重做日志缓冲先写入文件系统缓存。为了确保重做日志写入磁盘，必须进行一次fsync操作。由于fsync的效率取决于磁盘的性能，因此磁盘的性能决定了事务提交的性能，也就是数据库的性能。

隔离性实现原理（一致性非锁定读（MVCC的原理））

多事务会同时处理相同的数据，因此每个事务都应该与其他事务隔离开来，防止数据损坏。
底层实现原理：
写-写操作：通过加锁，原理和 java 里面的锁机制是一样的。
写-读操作：MVCC多版本并发控制，对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥。

一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链。

在可重复读隔离级别，事务开启的时候，执行任何查询sql会生成当前事务的一致性视图read-view，也就是第一次select生成一个版本，read-view视图在事务结束之前都不会变化。

如果是读已提交隔离级别，在每次执行查询sql时都会重新生成视图read-view，也就是每次select生成一个版本。

执行查询时，从对应版本链里的最新数据开始逐条跟read-view做比，会拿着当前事务的id和readview视图数组里面的已创建的最小事务id和已创建的最大事务id进行比较，这里面分为三种情况：
第一种，当前事务的id小于数组里面最小的id，说明这个版本是已提交的事务生成的，表示这个数据可见。
第二种，当前事务比已创建的最大事务id还要大，说明这个版本还没开启事务，表示不可见。
第三种，如果刚好在这个区间，被访问的事务id在最小事务id与最大事务id之间，又有二种情况：

第一种，这个版本是由还没提交的事务生成的，不可见，
第二种，表示这个版本是已经提交了的事务生成的，可见。
做比对，得到最终的快照结果，通过这种机制保证了隔离性。

对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的 trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被 删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数 据。

注意：begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句， 事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。

总结： MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。

BufferPool缓存机制

mysql 的数据是存放在磁盘上的，但是每次去读数据都需要通过这个磁盘io，效率就很低，使用 innodb 提供了一个缓存 buffer，这个 buffer 中包含了磁盘部分数据页的一个映射，作为访问数据库的一个缓冲，从数据库读取一个数据，就会先从这个 buffer 中获取，如果 buffer 中没有，就从这个磁盘中获取，读取完再放到这个 buffer 缓冲中，当数据库写入数据的时候，也会首先向这个 buffer 中写入数据，定期将 buffer 中的数据刷新到磁盘中，进行持久化的一个操作。

为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了？
因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差。
因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。 Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。 更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。 正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干的读写请求。

重做日志刷新到磁盘的策略

通过参数innodb_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略。
该参数的默认值为1，表示事务提交时必须调用一次fsync操作。
0表示事务提交时不进行写入重做日志操作，这个操作仅在master thread中完成，而在master thread中每1秒会进行一次重做日志文件的fsync操作。
2表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行fsync操作。在这个设置下，当MySQL数据库发生宕机而操作系统不发生宕机时，并不会导致事务的丢失。而当操作系统宕机时，重启数据库后会丢失未从文件系统缓存刷新到重做日志文件那部分事务。
举例：逐条插入50万条数据。
innodb_flush_log_at_trx_commit = 1时： 用时 2 分 13 秒。50 万次写入重做日志；fsync操作50万次。
innodb_flush_log_at_trx_commit = 0时： 用时 23 秒。约23次写如重做日志；fsync操作约23次。
innodb_flush_log_at_trx_commit = 2时： 用时 35 秒。50万次写入重做日志（仅缓存）；fsync操作0次。

虽然用户可以通过设置参数innodb_flush_log_at_trx_commit为0或2来提高事务提交的性能，但是需要牢记的是，这种设置方法丧失了事务的ACID特性。而针对上述存储过程，为了提高事务的提交性能，应该在将50万行记录插入表后进行一次的COMMIT操作，而不是在每插入一条记录后进行一次COMMIT操作。这样做的好处是还可以使事务方法在回滚时回滚到事务最开始的确定状态。

正确方法：innodb_flush_log_at_trx_commit = 1，将50万条数据在一个事务或者多个事务中分派提交，减少fsync次数。

filesort的过程

filesort的过程是这样的：
第一步先根据表的索引或者全表扫描，读取所有满足条件的记录。
第二步，存储每一行排序列，就是order by用到的列值，还有行记录指针，就是指向该行数据的行指针，把这二个存储到缓冲区。
第三步，当缓冲区满后，运行一个快速排序来将缓冲区中数据排序，将排序完的数据存储到一个临时文件，保存一个存储块的指针，当然如果缓冲区不满，则不会重建临时文件了。直到将所有行读完，建立相应有序的临时文件。
第四步，对块级进行排序，这个类似归并排序算法，只通过两个临时文件的指针来不断交换数据，最终达到两个文件，都是有序的，直到所有的数据都排序完毕。
第五步，采取顺序读的方式，将每行数据读入内存，取出数据传到客户端。

为什么要说这个filesort呢？
举二个场景，第一个，如果order by的条件不在索引列上会产生filesort，第二个，排序的字段不在where的条件中，没有办法走索引排序Index，而是走的文件排序filesort 。

这种概率其实还是挺高的。这个时候就需要看文件排序用的是单路排序还是双路排序，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。

mysql优化器使用双路排序还是单路排序是有自己的算法判断的，如果查询的列字段大于max_length_for_sort_data变量，则会使用双路排序，反之则会使用单路排序，单路排序速度是更快的，不过比较占据内存，如果在内存空间允许的情况下想要使用单路排序的话，可以增加max_length_for_sort_data变量的大小，max_length_for_sort_data变量默认为1024字节。

离散读

在某些情况下，当执行EXPLAIN命令进行SQL语句的分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。

这种情况多发生于范围查找、JOIN链接操作等情况下。

假设表：t_index 。其中 id 为主键；c1 与 c2 组成了联合索引（c1，c2）；此外，c1还是一个单独索引。

进行如下查询操作：SELECT * FROM t_index WHERE c1 ＞ 1 and c1 ＜ 100000;
可以看到表t_index有（c1，c2）的联合主键，此外还有对于列c1的单个索引。

上述这句SQL显然是可以通过扫描OrderID上的索引进行数据的查找。然而通过EXPLAIN命令，用户会发现优化器并没有按照OrderID上的索引来查找数据。在最后的索引使用中，优化器选择了PRIMARY id 聚集索引，也就是表扫描，而非c1辅助索引扫描。

这是因为如果强制使用c1索引，就会造成离散读。具体原因在于用户要选取的数据是整行信息，而c1作为辅助索引不能覆盖到我们要查询的信息，因此在对c1索引查询到指定数据后，还需要一次书签访问来查找整行数据的信息。

虽然c1索引中数据是顺序存放的，但是再一次进行书签查找的数据则是无序的，因此变为了磁盘上的离散读操作。如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是20%左右），优化器会选择通过聚集索引来查找数据。

优化器如何优化离散读？你是如何避免离散读的

MySQL 5.6之前，优化器在进行离散读决策的时候，如果数据量比较大，会选择使用聚集索引，全表扫描。

MySQL5.6版本开始支持Multi-Range Read（MRR）优化。

Multi-Range Read优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来性能极大的提升。

Multi-Range Read优化可适用于range，ref，eq_ref类型的查询。

MRR优化有以下几个好处：
❑MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
❑减少缓冲池中页被替换的次数。（顺序查找可以对一个页进行顺序查找，无需离散加载数据页）
❑批量处理对键值的查询操作。对于InnoDB和MyISAM存储引擎的范围查询和JOIN查询操作

MRR的工作方式如下：
❑将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
❑将缓存中的键值根据RowID进行排序。
❑根据RowID的排序顺序来访问实际的数据文件。

举例说明：SELECT * FROM salaries WHERE salary ＞10000 AND salary＜ 40000;
salary上有一个辅助索引idx_s，因此除了通过辅助索引查找键值外，还需要通过书签查找来进行对整行数据的查询。

Multi-Range Read还可以将某些范围查询，拆分为键值对，以此来进行批量的数据查询。这样做的好处是可以在 拆分过程中，直接过滤一些不符合查询条件的数据
例如：SELECT * FROM t WHERE key_part1 ＞=1000 AND key_part1 ＜ 2000 AND key_part2 = 10000;
表t有（key_part1，key_part2）的联合索引，因此索引根据key_part1，key_part2的位置关系进行排序。

若没有Multi-Read Range，此时查询类型为Range，SQL优化器会先将key_part1大于1000且小于2000的数据都取出，即使key_part2不等于1000。待取出行数据后再根据key_part2的条件进行过滤。这会导致无用数据被取出。如果有大量的数据且其key_part2不等于1000，则启用Mulit-Range Read优化会使性能有巨大的提升。

倘若启用了Multi-Range Read优化，优化器会先将查询条件进行拆分，然后再进行数据查询。就上述查询语句而言，优化器会将查询条件拆分为（1000，10000），（1001，10000），（1002，10000），…，（1999，10000），最后再根据这些拆分出的条件进行数据的查询。

我是如何优化的：

• 在非必要的情况下，拒绝使用 select * ；
• 在必须 select * 的情况下，尽量使用MySQL5.6+的版本开启MRR；
• 在必须 select * 的情况下且MySQL 小于 5.6版本下，可以根据数据量进行离散读和聚集索引两种情况下的性能进行对比，必要时采用force index语句强制指定索引。

ICP优化

和Multi-Range Read一样，Index Condition Pushdown同样是MySQL 5.6开始支持的一种根据索引进行查询的优化方式。

之前的MySQL数据库版本不支持Index Condition Pushdown，当进行索引查询时，首先根据索引来查找记录，然后再根据WHERE条件来过滤记录。

在支持Index Condition Pushdown后，MySQL数据库会在取出索引的同时，判断是否可以进行WHERE条件的过滤，也就是将WHERE的部分过滤操作放在了存储引擎层。

在某些查询下，可以大大减少上层SQL层对记录的索取（fetch），从而提高数据库的整体性能。

Index Condition Pushdown优化支持range、ref、eq_ref、ref_or_null类型的查询，当前支持MyISAM和InnoDB存储引擎。

当优化器选择Index Condition Pushdown优化时，可在执行计划的列Extra看到Usingindexcondition提示。

全文检索

例：SELECT * FROM blog WHERE content like '%xxx%'
根据B+树索引的特性，上述SQL语句即便添加了B+树索引也是需要进行索引的扫描来得到结果。

类似这样的需求在互联网应用中还有很多。例如，搜索引擎需要根据用户输入的关键字进行全文查找，电子商务网站需要根据用户的查询条件，在可能需要在商品的详细介绍中进行查找，这些都不是B+树索引所能很好地完成的工作。

全文检索（Full-Text Search）是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。

在之前的MySQL数据库中，InnoDB存储引擎并不支持全文检索技术。大多数的用户转向MyISAM存储引擎，这可能需要进行表的拆分，并将需要进行全文检索的数据存储为MyISAM表。这样的确能够解决逻辑业务的需求，但是却丧失了InnoDB存储引擎的事务性，而这在生产环境应用中同样是非常关键的。

从InnoDB 1.2.x版本开始，InnoDB存储引擎开始支持全文检索，其支持MyISAM存储引擎的全部功能，并且还支持其他的一些特性。InnoDB存储引擎从1.2.x版本开始支持全文检索的技术，其采用full inverted index的方式。

在InnoDB存储引擎中，将(DocumentId，Position)视为一个“ilist”。因此在全文检索的表中，有两个列，一个是word字段，一个是ilist字段，并且在word字段上有设有索引。

全文检索通常使用倒排索引来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。
这通常利用关联数组实现，其拥有两种表现形式：
❑inverted file index，其表现形式为{单词，单词所在文档的ID}
❑full invertedindex，其表现形式为{单词，(单词所在文档的ID，在具体文档中的位置)}

MySQL数据库通过MATCH()…AGAINST()语法支持全文检索的查询，MATCH指定了需要被查询的列，AGAINST指定了使用何种方法去进行查询。

• NATURALLANGUAGEMODE全文检索通过MATCH函数进行查询，默认采用Natural Language模式，其表示查询带有指定word的文档。
• BOOLEANMODEMySQL数据库允许使用IN BOOLEAN MODE修饰符来进行全文检索。当使用该修饰符时，查询字符串的前后字符 会有特殊的含义，例如下面的语句要求查询有字符串Pease但没有hot的文档，其中+和-分别表示这个单词必须出现，或者一定不存在。
• WITH QUERY EXPANSION 或 IN NATURAL LANGUAGE MODE WITHQUERYEXPANSIONMySQL数据库还支持全文检索的扩展查询。这种查询通常在查询的关键词太短，用户需要impliedknowledge（隐含知识）时进行。例如，对于单词database的查询，用户可能希望查询的不仅仅是包含database的文档，可能还指那些包含MySQL、Oracle、DB2、RDBMS的单词。而这时可以使用Query Expansion模式来开启全文检索的impliedknowledge。

行锁、表锁、间隙锁、死锁

行锁的算法

InnoDB存储引擎有3种行锁的算法，其分别是：
❑Record Lock：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在读已提交、可重复读隔离级别下都支持。
❑Gap Lock：间隙锁，锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在可重复读隔离级别下支持。
❑Next-Key Lock∶锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在可重复读隔离级别下支持。

行锁的表现

将mysql数据库改为手动提交
步骤1：
打开窗口1：更新数据，update test_innodb_lock set a = 1 where b = 2;然后查询select * from test_innodb_lock where b = 2;，发现a已经改为1了。

由于还没有提交事务，所以b=2这行数据还是被update持有锁，对于其他事务是不可见的，避免了脏读。

打开窗口2：查询select * from test_innodb_lock where b = 2;发现a的值还是没变。更新数据，update test_innodb_lock set a = 2 where b = 2;发现一直阻塞，没有继续往下执行。

由于第一个会话持有了这一行的锁，第二个窗口的会话就对这一行进行修改。

步骤2：
窗口1：提交事务
窗口2：查询select * from test_innodb_lock where b = 2;发现a的值改为1了。更新数据，update test_innodb_lock set a = 2 where b = 2;发现可以更新成功。

表锁的表现

将mysql数据库改为手动提交
步骤1：
窗口1：更新数据，update test_innodb_lock set b = 0 where a = 1 or a = 2
窗口2：更新数据，update test_innodb_lock set b = 3 where a = 3，发现阻塞，没有继续往下执行

由于还没有提交事务，并且使用了or导致索引失效，行级锁升级为表锁，窗口1只要没有提交事务，那么窗口2任何对test_innodb_lock表的操作都会阻塞，直到窗口1提交事务，窗口2才可以继续执行下去。

间隙锁的表现

假设有一张表，test_innodb_lock表有a和b二个字段，a字段里面的数据缺了2,4,6,8，这些就是间隙，这个间隙引发的锁就叫做间隙锁，一般发生在范围查询里面。

将mysql数据库改为手动提交
步骤1：
窗口1：更新数据，update test_innodb_lock set b = 5 where a >1 and a < 9
窗口2：更新数据，insert into test_innodb_lock values(4,4)发现阻塞了，没有继续往下执行。

窗口1进行了一个范围查询，会把a >1 and a < 9加上锁，窗口2这个会话想插入2,4,6,8是无法插入的，因为它已经被窗口1的会话持有了锁。

死锁

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。

解决死锁问题最简单的方式是不要有等待，将任何的等待都转化为回滚，并且事务重新开始。但是在线上环境中，这可能导致并发性能的下降，甚至任何一 个事务都不能进行。而这所带来的问题远比死锁问题更为严重，因为这很难被发现并且浪费资源。

解决死锁问题的另一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。

在InnoDB存储引擎中，参数innodb_lock_wait_timeout用来设置超时的时间。 超时机制虽然简单，但是其仅通过超时后对事务进行回滚的方式来处理，或者说其是根据FIFO的顺序选择回滚对象。但若超时的事务所占权重比较大，如事务操作更新了很多行，占用了较多的undo log，这时采用FIFO的方式， 就显得不合适了，因为回滚这个事务的时间相对另一个事务所占用的时间可能会很多。

因此，除了超时机制，当前数据库还都普遍采用wait-for graph（等待图）的方式来进行死锁检测。较之超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB存储引擎也采用的这种方式。wait-for graph要求数据库保存以下两种信息： 锁的信息链表和事务等待链表。

wait-for graph是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说InnoDB存储引擎选择回滚undo量最小的事务。

主键自增长实现原理

自增长在数据库中是非常常见的一种属性，也是很多DBA或开发人员首选的主键方式。在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器。

当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，执行如下的语句来得到计数器的值：

SELECT MAX(auto_inc_col)FROM t FOR UPDATE;

插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称做AUTO-INC Locking。

这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。

虽然AUTO-INCLocking从一定程度上提高了并发插入的效率，但还是存在一些性能上的问题。

首先，对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入的完成（虽然不用等待事务的完成）。

其次，对于 INSERT…SELECT的大数据量的插入会影响插入的性能，因为另一个事务中的插入会被阻塞。

从MySQL5.1.22版本开始，InnoDB存储引擎中提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能。并且从该版本开始，InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式，该参数的默认值为1。

innodb_autoinc_lock_mode有三个选项：

0：是mysql5.1.22版本之前自增长的实现方式，通过表锁的AUTO-INCLocking方式实现的。

1：是默认值，对于简单的插入(插入之前就可以确定插入的行数)，这个值会用互斥量去对内存中的计数器进行累加操作。对于批量插入（插入之前就不确定插入的行数），还是通过表锁的AUTO-INCLocking方式实现。在这种配置下，如果不考虑回滚操作，对于自增值的列，它的增长还是连续的，而且statemment-based方式的replication还是很好的工作。但是如果使用了AUTO-INCLocking方式去产生自增长的值，这个时候再进行简单插入操作，就需要等待AUTO-INCLocking释放。

2：在这个模式下，对于所有的插入的语句，它自增长值的产生都是通过互斥量，不是通过AUTO-INCLocking方式，这是性能最高的方式，但是如果是并发插入，在每次插入的时候，自增长的值就不是连续的，而且基于statemment-based replication会出现问题，所以在这个模式下，任何时候都要用row-base replication，这样才可以保证最大的并发性能和replication主从数据的一致。

statemment-based replication（SBR）和row-base replication（RBR）是主从复制的方式。

使用mysql自增长的坏处：

• 强依赖DB。不同数据库语法和实现不同，数据库迁移的时候、多数据库版本支持的时候、或分表分库的时候需要处理，会比较麻烦。当DB异常时整个系统不可用，属于致命问题。

• 单点故障。在单个数据库或读写分离或一主多从的情况下，只有一个主库可以生成。有单点故障的风险。

• 数据一致性问题。配置主从复制可以尽可能的增加可用性，但是数据一致性在特殊情况下难以保证。主从切换时的不一致可能会导致重复发号。

• 难于扩展。在性能达不到要求的情况下，比较难于扩展。ID发号性能瓶颈限制在单台MySQL的读写性能。

索引数据结构

磁盘存储

mysql是从磁盘读取数据到内存的，是以磁盘块为基本单位的，位于同一磁盘块中的数据会被一次性读取出来，不是按需读取。以InnoDB存储引擎来说，它使用页作为数据读取单位，页是其磁盘管理的最小单位，默认大小是16kb。系统的一个磁盘块的存储空间往往没有这么大，所以InnoDB每次申请磁盘空间时都会是多个地址连续磁盘块来达到页的大小16KB。假设一行数据的大小是 1k，那么一个页可以存放 16 行这样的数据。那如果想查找某个页里面的一个数据的话，得首先找到他所在的页。

在查询数据时一个页中的每条数据都能定位数据记录的位置，这会减少磁盘 I/O 的次数，提高查询效率。InnoDB存储引擎在设计时是将根节点常驻内存的，力求达到树的深度不超过 3，也就是说I/O不超过3次。

树形结构的数据可以让系统高效的找到数据所在的磁盘块，这里就可以说一下这个b树和b+树了

B树的结构是每个节点中有key也有value，而每一个页的存储空间是16kb，如果数据较大时将会导致一页能存储数据量的数量很小。

B+Tree的结构是将所有数据记录节点按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的key 值数量，降低B+Tree的高度。B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。 B+索引在数据库中有一个特点是高扇出性，因此在数据库中，B+树的高度一般都在2～4层，这也就是说查找某一键值的行记录时最多只需要2到4次IO，这倒不错。因为当前一般的机械磁盘每秒至少可以做100次IO， 2～4次的IO意味着查询时间只需0.02～0.04秒。 数据库中的B+树索引可以分为聚集索引和辅助索引。

假设每条sql信息为1kb，主键ID为bigint型，一颗高度为2，3，4高度的B+树分别可以存储多少行数据?

因为单个页的大小为 16kb，而一行数据的大小为 1kb，也就是说一页可以存 放 16 行数据。然后因为非叶子节点的结构是：“页指针 + 键值”，我们假设主键ID 为 bigint 类型，长度为 8 字节（byte），而指针大小在 InnoDB 源码中设置为 6 字节（byte），这样一共 14 字节（byte），因为一个页可以存放 16k 个 byte，所以一个页可以存放的指针个数为 16384/14=1170 个。因此一个两层的 B + 树可 以存放的数据行的个数为：1170*16=18720（行）。

也就是说第一层的页，即根页可以存放 1170 个指针，然后第二层的每个页也可以 存放 1170 个指针。这样一共可以存放 1170*1170 个指针，所以一共可以存放 1170*1170*16=21902400（2千万 左右） 行记录。也就是说一个三层的 B + 树就可以存放千万级别的数据了。

高度为4的B+树则是 1170*1170*1170*16 约等于 2000万*1000，1000个 2000 万就是 200亿行的数据了。

为什么选用B+树做索引而不选用二叉树或者B树?

b 树和 b + 树应用在数据库索引，可以认为是 m 叉的多路平衡查找树，但是从理论上讲，二叉树查找速度和比较次数都是最小的，为什么不用二叉树呢？ 因为我们要考虑磁盘 IO 的影响，它相对于内存来说是很慢的。数据库索引是存储在磁盘上的，当数据量大时， 就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少 IO 次数，对于树来说，IO 次数就是树的高度，而 “矮胖” 就是 b 树的特征之一，它的每个节点最多包含 m 个孩子， m 称为 b 树的阶。 为什么不用B树呢？ b + 树，是 b 树的一种变体，查询性能更好。 b + 树相比于 b 树的查询优势：

1.b + 树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更 “矮胖”。B 树不管叶子节点还是非叶子节 点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况 下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低；

2.b + 树查询必须查找到叶子节点，b 树只要匹配到即可直接返回。因此 b + 树查找更稳定（并不慢），必须查 找到叶子节点；而B树，如果数据在根节点，最快，在叶子节点最慢，查询效率不稳定。

3.对于范围查找来说，b + 树只需遍历叶子节点链表即可，并且不需要排序操作，因为叶子节点已经对索引进行 了排序操作。b 树却需要重复地中序遍历，找到所有的范围内的节点。

为什么用 B+ 树做索引而不用哈希表做索引？

1、模糊查找不支持：哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置， 这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个 表。而 B + 树则可以通过最左前缀原则快速找到对应的数据。

2、范围查找不支持：如果我们要进行范围查找，例如查找 ID 为 100 ~ 400 的人，哈希表同 样不支持，只能遍历全表。

3、哈希冲突问题：索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的 哈希码的话，那么形成的索引结构将会是一条很长的链表，这样的话，查找的时间就会大大增加。

SQL优化

• 针对SQL进行调整，在写SQL的时候遵循最左前缀原则，向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，范围列可以用到索引，但是范围列后面的列无法用到索引。

• like以通配符%开头索引失效会变成全表扫描的操作。

• 如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描。

• 只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。

• 不要使用select *，改用select加字段名称，因为select *走的聚集索引，会进行全表扫描，如果一定要使用select *的话，mysql至少使用5.6版本，这个版本有一个离散读的优化，离散读的优化是将离散度大的列放到联合索引的前面，举个例子，select * from user where staff_id = 2 and customer_id = 584，这个时候索引优化会将customer_id放到前面，因为它的离散度更高，可以通过select count(distinct customer_id),count(distinct staff_id) from user查看列的离散度。

5.6版本有一个ICP的优化，以往根据索引查找记录，再根据WHERE条件来过滤记录。使用ICP优化后，会在取出索引的同时，直接根据WHERE条件过滤，将WHERE的部分过滤操作放在了存储引擎层。在某些查询下可以大大减少上层SQL层对记录的索取，从而提高性能。

5.6版本还有一个MRR优化，是批量处理对键值的查询操作ICP优化，减少缓冲池中页被替换的次数，使数据访问变得较为顺序。辅助索引查询得到书签后，先对主键进行排序，再按序进行查找。

• 另外在写sql的时候，尽量使用它的一个执行计划，去看我们的索引是不是失效了。

索引失效的几种情况

• 如果条件中有or，即使其中有部分条件带索引也不会使用。

• 对于复合索引，如果不使用前列，后续列也将无法使用。

• like以%开头。列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引。

• where中索引列有运算，有函数的，不使用索引。

• 如果mysql觉得全表扫描更快的时候，数据少的情况下，不使用索引。

聚集索引

InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引（clusteredindex）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。每个数据页都通过一个双向链表来进行链接由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，它对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户所要查询的数据如：用户需要查询一张注册用户的表，查询最后注册的10位用户，由于B+树索引是双向链表的，用户可以快速找到最后一个数据页，并取出10条记录SELECT * FROM Profile ORDER BY id LIMIT 10;虽然使用ORDER BY对主键id记录进行排序，但是在实际过程中并没有进行所谓的filesort操作，而这就是因为聚集索引的特点。另一个是范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。如：SELECT * FROM Profile where id>1 and id < 100;

辅助索引

对于辅助索引（Secondary Index，也称非聚集索引），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。举例来说，如果在一棵高度为3的辅助索引树中查找数据，那需要对这棵辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问以得到最终的一个数据页。

覆盖索引，什么情况下优化器会选择使用覆盖索引

InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询的记录（此时不能够使用select * 操作，只能对特定的索引字段进行select），而不需要查询聚集索引中的记录。使用覆盖
索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。对于InnoDB存储引擎的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为（primarykey1，primary key2，…，key1，key2，…）。例如，下列语句都可仅使用一次辅助联合索引来完成查询：

SELECT key2 FROM table WHERE key1=xxx；
SELECT primary key2,key2 FROM table WHERE key1=xxx；
SELECT primary key1,key2 FROM table WHERE key1=xxx；
SELECT primary key1,primary key2,key2 FROM table WHERE key1=xxx；

CREATETABLEbuy_log( 
userid INT UNSIGNED NOT NULL,
buy_date DATE 
)ENGINE=InnoDB; 
ALTER TABLE buy_log ADD KEY (userid);
ALTER TABLE buy_log ADD KEY (userid,buy_date);

覆盖索引的另一个好处是对某些统计问题而言的。还是对于上题创建的表buy_log，要进行举例说明。
SELECT COUNT(*) FROM buy_log;InnoDB存储引擎并不会选择通过查询聚集索引来进行统计。由于buy_log表上还有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少IO操作。

在通常情况下，诸如（a，b）的联合索引，一般是不可以选择列b中所谓的查询条件。但是如果是统计操作，并且是覆盖索引的，则优化器会进行选择，如下述语句：SELECT COUNT(*)FROM buy_log WHERE buy_date＞=‘2011-01-01’ANDbuy_date＜’2011-02-01’
表buy_log有（userid，buy_date）的联合索引，这里只根据列b进行条件查询，一般情况下是不能进行该联合索引的，但是这句SQL查询是统计操作，并且可以利用到覆盖索引的信息，因此优化器会选择该联合索引.

联合索引

联合索引是指对表上的多个列进行索引。

CREATE TABLE buy_log(
userid INT UNSIGNED NOT NULL,
buy_date DATE
)ENGINE=InnoDB;
ALTER TABLE buy_log ADD KEY (userid);
ALTER TABLE buy_log ADD KEY (userid,buy_date);

以上代码建立了两个索引来进行比较。两个索引都包含了userid字段。

情况1： 如果只对于userid进行查询，如：SELECT * FROM buy_log WHERE userid=2;索引选择：优化器最终的选择是索引userid，因为该索引的叶子节点包含单个键值，所以理论上一个页能存放的记录应该更多。

情况2：SELECT * FROM buy_log WHERE userid=1 ORDER BY buy_date DESC LIMIT 3；索引选择：优化器使用了（userid，buy_date）的联合索引userid_2，因为在这个联合索引中buy_date已经排序好了。根据该联合索引取出数据，无须再对buy_date做一次额外的排序操作。

情况 3：假如三个字段的联合索引。如：对于联合索引（a，b，c）来说，下列语句同样可以直接通过联合索引得到结果，不需要filesort的排序操作：

SELECT...FROM TABLE WHERE a=xxx ORDER BY b 
SELECT...FROM TABLE WHERE a=xxx AND b=xxx ORDER BY c

但是对于下面的语句，联合索引不能直接得到结果，其还需要执行一次filesort排序操作，因为索引（a，c）并未排序：

SELECT...FROM TABLE WHERE a=xxx ORDER BY c

索引总结

聚集索引的叶子节点称为数据页，每个数据页通过一个双向链表来进行链接，而且数据页按照主键的顺序进行排列。每个数据页上存放的是完整的行记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。如果定义了主键，InnoDB会自动使用主键来创建聚集索引。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替主键。如果没有唯一的非空索引，InnoDB会隐式定义一个主键来作为聚集索引。

辅助索引它叶子节点中没有行记录的全部数据，叶子节点除了包含键值以外，每个叶子节点的索引行还包含了一个书签，该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。

覆盖索引先遍历辅助索引，再遍历聚集索引，而如果要查询的字段值在辅助索引上就有，就不用再查聚集索引了，这显然会减少IO操作。

联合索引，它是对表上的多个列进行索引，键值都是排序的，通过叶子节点可以顺序的读出所有数据，联合索引的好处在于能起到"一个顶三个"的作用。比如建了一个(a,b,c)的复合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引，每多一个索引，都会增加写操作的开销和磁盘空间的开销，对于大数据的表，这是不小的开销。另外它还可以避免filesort排序，因为filesort的过程，一行数据会被读两次，第一次是where条件过滤时，第二个是排完序后还得用行指针去读一次。

redo log 和bin log有什么区别？binlog做什么用的

在MySQL数据库中有一种二进制日志（binlog），其用来进行POINT-IN-TIME（PIT）的恢复及主从复制（Replication）环境的建立。从表面上看其和重做日志非常相似，都是记录了对于数据库操作的日志。然而，从本质上来看，两者有着非常大的不同。首先，重做日志是在InnoDB存储引擎层产生，而二进制日志是在MySQL数据库的上层产生的，并且二进制日志不仅仅针对于InnoDB存储引擎，MySQL数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。其次，两种日志记录的内容形式不同。MySQL数据库上层的二进制日志bin log是一种逻辑日志，其记录的是对应的SQL语句。而InnoDB存储引擎层面的重做日志是物理格式日志，其记录的是对于每个页的修改。

什么是undolog，有什么用？

重做日志记录了事务的行为，可以很好地通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作，这时就需要undo。因此在对数据库进行修改时，InnoDB存储引擎不但会产生redo，还会产生一定量的undo。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。redo存放在重做日志文件中，与redo不同，undo存放在数据库内部的一个特殊段（segment）中，这个段称为undo段（undo segment）。undo段位于共享表空间内。可以通过py_innodb_page_info.py工具来查看当前共享表空间中undo的数量除了回滚操作，undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。最后也是最为重要的一点是，undo log会产生redo log，也就是undo log的产生会伴随着redolog的产生，这是因为undo log也需要持久性的保护。

分布式事务

InnoDB存储引擎提供了对XA事务的支持，并通过XA事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源（transactional resources）参与到一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对于事务原有的ACID要求又有了提高。另外，在使用分布式事务时，InnoDB存储引擎的事务隔离级别必须设置为SERIALIZABLE。XA事务允许不同数据库之间的分布式事务，如一台服务器是MySQL数据库的，另一台是Oracle数据库的，又可能还有一台服务器是SQL Server数据库的，只要参与在全局事务中的每个节点都支持XA事务。分布式事务可能在银行系统的转账中比较常见，如用户David需要从上海转10 000元到北京的用户Mariah的银行卡中：

#Bank@Shanghai：
UPDATE account SET money=money-10000WHEREuser='David';
#Bank@Beijing
UPDATE account SET money=money+10000WHEREuser='Mariah';

在这种情况下，一定需要使用分布式事务来保证数据的安全。如果发生的操作不能全部提交或回滚，那么任何一个结点出现问题都会导致严重的结果。要么是David的账户被扣款，但是Mariah没收到，又或者是David的账户没有扣款，Mariah却收到钱了。

XA事务由一个或多个资源管理器（Resource Managers）、一个事务管理器（TransactionManager）以 及一个应用程序（ApplicationProgram）组成。❑资源管理器：提供访问事务资源的方法。通常一个数据库就是一个资源管理器。❑事务管理器：协调参与全局事务中的各个事务。需要和参与全局事务的所有资源管理器进行通信。❑应用程序：定义事务的边界，指定全局事务中的操作。在MySQL数据库的分布式事务中，资源管理器就是MySQL数据库，事务管理器为连接MySQL服务器的客户端。

分布式事务使用两段式提交（two-phasecommit）的方式。在第一阶段，所有参与全局事务的节点都开始准备（PREPARE），告诉事务管理器它们准备好提交了。在第二阶段，事务管理器告诉资源管理器执行ROLLBACK还是COMMIT。如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。可见与本地事务不同的是，分布式事务需要多一次的PREPARE操作，待收到所有节点的同意信息后，再进行COMMIT或是ROLLBACK操作。

最为常见的内部XA事务存在于binlog与InnoDB存储引擎之间。由于复制的需要，因此目前绝大多数的数据库都开启了binlog功能。在事务提交时，先写二进制日志，再写InnoDB存储引擎的重做日志。对上述两个操作的要求，也是原子的，即二进制日志和重做日志必须同时写入。若二进制日志先写了，而在写入InnoDB存储引擎时发生了宕机，那么slave可能会接收到master传过去的二进制日志并执行，最终导致了主从不一致的情况。

CAP理论

分布式环境下（数据分布）要任何时刻保证数据一致性是不可能的，只能采取妥协的方案来保证数据最终一致性。这个也就是著名的CAP定理。
C一致性：对于指定的客户端来说，读操作保证能够返回最新的写操作结果。
A可用性：非故障的节点在合理的时间内返回合理的响应（不是错误和超时的响应）。
P分区容错性：当出现网络分区后，系统能够继续“履行职责”。

对于一个分布式系统而言，网络失效一定会发生，分区容错性P其实就是每个服务都会有多个节点（一般都是主从），这样就可以保证此服务的一个节点挂了之后，此服务的其他节点依然可以响应，其实这就是分区容错性。
但是一个服务有多个节点之后，一个服务的多个节点之间的数据为了保持一致性就要进行的数据复制，在此过程中就会出现数据一致性C（强一致性）的问题。也就是说，分区耐受性是必须要保证的，那么在可用性和一致性就必须二选一。网络不可用的时候，如果选择了一致性，系统就可能返回一个错误码或者干脆超时，即系统不可用。如果选择了可用性，那么系统总是可以返回一个数据，但是并不能保证这个数据是最新的。

BASE理论

BASE 理论是对 CAP 理论的延伸，核心思想是即使无法做到强一致性，但应用可以采用适合的方式达到最终一致性。
基本可用： 基本可用是指分布式系统在出现故障的时候，允许损失部分可用性，即保证核心可用。电商大促时，为了应对访问量激增，部分用户可能会被引导到降级页面，服务层也可能只提供降级服务。这就是损失部分可用性的体现。
软状态： 软状态是指允许系统存在中间状态，而该中间状态不会影响系统整体可用性。分布式存储中一般一份数据至少会有三个副本，允许不同节点间副本同步的延时就是软状态的体现。MySQL Replication 的异步复制也是一种体现。
最终一致性： 最终一致性是指系统中的所有数据副本经过一定时间后，最终能够达到一致的状态。弱一致性和强一致性相反，最终一致性是弱一致性的一种特殊情况。

应用场景：
Erueka:erueka是SpringCloud系列用来做服务注册和发现的组件，作为服务发现的一个实现，在设计的时候就更考虑了可用性，保证了AP。
Zookeeper:Zookeeper在实现上牺牲了可用性，保证了一致性（单调一致性）和分区容错性，也即：CP。所以这也是SpringCloud抛弃了zookeeper而选择Erueka的原因。

具体根据各自业务场景所需来制定相应的策略而选择适合的产品服务等。例如：支付订单场景中，由于分布式本身就在数据一致性上面很难保证，从A服务到B服务的订单数据有可能由于服务宕机或其他原因而造成数据不一致性。因此此类场景会酌情考虑：AP，不强制保证数据一致性，但保证数据最终一致性。
分布式事务指事务的操作位于不同的节点上，需要保证事务的 AICD 特性，在下单场景下，库存和订单如果不在同一个节点上，就涉及分布式事务。

两阶段提交（2PC）

第一阶段：协调者询问参与者事务是否执行成功，参与者发回事务执行结果。这一阶段的协调者有超时机制，假设因为网络原因没有收到某参与者的响应或某参与者挂了，那么超时后就会判断事务失败，向所有参与者发送回滚命令。
第二阶段：如果事务在每个参与者上都执行成功，事务协调者才发送通知让参与者提交事务；否则，协调者发送通知让参与者回滚事务。这一阶段的协调者的没法超时，只能不断重试。

协调者是一个单点，存在单点故障问题。
假设协调者在发送准备命令之前挂了，还行等于事务还没开始。
假设协调者在发送准备命令之后挂了，这就不太行了，有些参与者等于都执行了处于事务资源锁定的状态。不仅事务执行不下去，还会因为锁定了一些公共资源而阻塞系统其它操作。
假设协调者在发送回滚事务命令之前挂了，那么事务也是执行不下去，且在第一阶段那些准备成功参与者都阻塞着。
假设协调者在发送回滚事务命令之后挂了，这个还行，至少命令发出去了，很大的概率都会回滚成功，资源都会释放。但是如果出现网络分区问题，某些参与者将因为收不到命令而阻塞着。
假设协调者在发送提交事务命令之前挂了，这个不行，傻了！这下是所有资源都阻塞着。
假设协调者在发送提交事务命令之后挂了，这个还行，也是至少命令发出去了，很大概率都会提交成功，然后释放资源，但是如果出现网络分区问题某些参与者将因为收不到命令而阻塞着。

存在的缺点：
同步阻塞 所有事务参与者在等待其它参与者响应的时候都处于同步阻塞状态，无法进行其它操作。
单点问题 协调者在 2PC 中起到非常大的作用，发生故障将会造成很大影响。特别是在阶段二发生故障，所有参与者会一直等待状态，无法完成其它操作。
数据不一致 在阶段二，如果协调者只发送了部分 Commit 消息，此时网络发生异常，那么只有部分参与者接收到 Commit 消息，也就是说只有部分参与者提交了事务，使得系统数据不一致。
太过保守 任意一个节点失败就会导致整个事务失败，没有完善的容错机制。

三阶段提交（3PC）

相比于 2PC 它在参与者中也引入了超时机制，并且新增了一个阶段使得参与者可以利用这一个阶段统一各自的状态，3PC 包含了三个阶段，分别是准备阶段、预提交阶段和提交阶段
准备阶段的变更成不会直接执行事务，而是会先去询问此时的参与者是否有条件接这个事务，因此不会一来就干活直接锁资源，使得在某些资源不可用的情况下所有参与者都阻塞着。
而预提交阶段的引入起到了一个统一状态的作用，它像一道栅栏，表明在预提交阶段前所有参与者其实还未都回应，在预处理阶段表明所有参与者都已经回应了。
假如你是一位参与者，你知道自己进入了预提交状态那你就可以推断出来其他参与者也都进入了预提交状态。

缺点：
多引入一个阶段也多一个交互，因此性能会差一些，而且绝大部分的情况下资源应该都是可用的，这样等于每次明知可用执行还得询问一次。

和2PC对比：
2PC 是同步阻塞的，协调者挂在了提交请求还未发出去的时候是最伤的，所有参与者都已经锁定资源并且阻塞等待着，提交阶段 2PC 协调者和某参与者都挂了之后新选举的协调者不知道当前应该提交还是回滚

改进的优势：
新协调者来的时候发现有一个参与者处于预提交或者提交阶段，那么表明已经经过了所有参与者的确认了，所以此时执行的就是提交命令。
所以说 3PC 就是通过引入预提交阶段来使得参与者之间的状态得到统一，也就是留了一个阶段让大家同步一下。
但是这也只能让协调者知道该如果做，但不能保证这样做一定对，这其实和上面 2PC 分析一致，因为挂了的参与者到底有没有执行事务无法断定。
所以说 3PC 通过预提交阶段可以减少故障恢复时候的复杂性，但是不能保证数据一致，除非挂了的那个参与者恢复。

总结一下
3PC 相对于 2PC 做了一定的改进：引入了参与者超时机制，并且增加了预提交阶段使得故障恢复之后协调者的决策复杂度降低，但整体的交互过程更长了，性能有所下降，并且还是会存在数据不一致问题。
2PC 和 3PC 都不能保证数据100%一致，因此一般都需要有定时扫描补偿机制

补偿事务（TCC）

针对每个操作，都要注册一个与其对应的确认和补偿（撤销）操作。它分为三个阶段：

Try 阶段主要是对业务系统做检测及资源预留
Confirm 阶段主要是对业务系统做确认提交，Try阶段执行成功并开始执行 Confirm阶段时，默认 Confirm阶段是不会出错的。即：只要Try成功，Confirm一定成功。
Cancel 阶段主要是在业务执行错误，需要回滚的状态下执行的业务取消，预留资源释放。

举个例子，假入 Bob 要向 Smith 转账，思路大概是：我们有一个本地方法，里面依次调用
首先在 Try 阶段，要先调用远程接口把 Smith 和 Bob 的钱给冻结起来。
在 Confirm 阶段，执行远程调用的转账的操作，转账成功进行解冻。
如果第2步执行成功，那么转账成功，如果第二步执行失败，则调用远程冻结接口对应的解冻方法 (Cancel)。
优点： 跟2PC比起来，实现以及流程相对简单了一些，但数据的一致性比2PC也要差一些
缺点： 缺点还是比较明显的，在2,3步中都有可能失败。TCC属于应用层的一种补偿方式，所以需要程序员在实现的时候多写很多补偿的代码，在一些场景中，一些业务流程可能用TCC不太好定义及处理

MQ 事务消息

有一些第三方的MQ是支持事务消息的，比如RocketMQ，他们支持事务消息的方式也是类似于采用的二阶段提交，但是市面上一些主流的MQ都是不支持事务消息的，比如 RabbitMQ 和 Kafka 都不支持。
第一阶段Prepared消息，会拿到消息的地址。第二阶段执行本地事务，第三阶段通过第一阶段拿到的地址去访问消息，并修改状态。也就是说在业务方法内要想消息队列提交两次请求，一次发送消息和一次确认消息。如果确认消息发送失败了RocketMQ会定期扫描消息集群中的事务消息，这时候发现了Prepared消息，它会向消息发送者确认，所以生产方需要实现一个check接口，RocketMQ会根据发送端设置的策略来决定是回滚还是继续发送确认消息。这样就保证了消息发送与本地事务同时成功或同时失败。

优点： 实现了最终一致性，不需要依赖本地数据库事务。
缺点： 实现难度大，主流MQ不支持，RocketMQ事务消息部分代码也未开源。

最大努力通知

其实我觉得本地消息表也可以算最大努力，事务消息也可以算最大努力。
就本地消息表来说会有后台任务定时去查看未完成的消息，然后去调用对应的服务，当一个消息多次调用都失败的时候可以记录下然后引入人工，或者直接舍弃。这其实算是最大努力了。
事务消息也是一样，当半消息被commit了之后确实就是普通消息了，如果订阅者一直不消费或者消费不了则会一直重试，到最后进入死信队列。其实这也算最大努力。
所以最大努力通知其实只是表明了一种柔性事务的思想：我已经尽力我最大的努力想达成事务的最终一致了。适用于对时间不敏感的业务，例如短信通知。

各个场景对比的解决方案：
2PC 和 3PC 是一种强一致性事务，不过还是有数据不一致，阻塞等风险，而且只能用在数据库层面。
而 TCC 是一种补偿性事务思想，适用的范围更广，在业务层面实现，因此对业务的侵入性较大，每一个操作都需要实现对应的三个方法。
本地消息、事务消息和最大努力通知其实都是最终一致性事务，因此适用于一些对时间不敏感的业务。

SQL的执行流程

第一步，先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。客户端如果长时间不发送command到Server端，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

第二步：查询缓存。MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个value就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。大多数情况查询缓存就是个鸡肋，为什么呢？因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。这个鸡肋也有地方可以去使用它，比如说不会改变的表数据，极少更新的表，像一些系统配置表、字典表，全国的省份之类的表，这些表上的查询适合使用查询缓存。MySQL提供了这种“按需使用”的方式，可以将my.cnf参数query_cache_type 设置成2，query_cache_type有3个值：0代表关闭查询缓存，1代表开启，2代表当sql语句中有SQL_CACHE关键词时才缓存。确定要使用查询缓存的语句，用 SQL_CACHE显式指定，比如，select SQL_CACHE * from user where ID=5；

第三步，如果没有命中查询缓存，就要开始真正执行语句了。MySQL 需要知道你要做什么，需要对 SQL 语句做解析。
分析器先会做“词法分析”，你输入的一条 SQL 语句，MySQL需要识别出里面的字符串分别是什么，代表什么。MySQL从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“user”识别成“表名 user”，把字符串“ID”识别成“列 ID”。做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL语句是否满足MySQL语法。如果你的语句不对，就会收到“您的SQL语法有错误”的错误提醒。语句正确之后，会丢到分析机里面执行分析，语法分析由Bison生成，经过bison语法分析之后，会生成一个语法树。比如，你的操作是select还是insert，你需要对那些字段进行操作，作用在哪张表上面，条件是什么。

经过了分析器，MySQL就知道这些字符串代表什么，要做什么了。在开始执行之前，还要先经过优化器的处理。

第四步，优化器，在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联的时候，优化器可以决定各个表的连接顺序，同一条多表查询的sql，执行的方案会有多种，比如，select * from user1 join user2 on user1.id = user2.id where user1.name=liaozhiwei and user2.name=haoshuai;既可以先从表user1 里面取出 name=liaozhiwei的 ID 值，再根据 ID 值关联到表user2，再判断user2 里面 name的值是否等于liaozhiwei。也可以先从表user2 里面取出 name=haoshuai的 ID 值，再根据 ID 值关联到user1，再判断user1 里面 name 的值是否等于haoshuai。这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。执行方案就确定下来了，然后进入执行器阶段。

第五步，开始执行的时候，要先判断一下你对这个表有没有执行查询的权限，如果没有，就会返回没有权限的错误。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。比如，我有一条sql:select * from user where id=10;执行器调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是10，如果不是则跳过， 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行，如果是将这行保存在结果集中。执行器将遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。到这一步，这个语句就执行完成了。

Mysql调优

第一步，从初期的一个需求规划，也就是对表的设计就开始了，先从底层开始说吧，mysql底层的页的大小是16kb，假如，我有一张表对单行数据量就有16kb，那么这张表就只能存储1条数据了，这是非常恐怖的。mysql是从磁盘读取数据到内存的，是以磁盘块为基本单位的，位于同一磁盘块中的数据会被一次性读取出来，不是按需读取。以InnoDB存储引擎来说，它使用页作为数据读取单位，页是其磁盘管理的最小单位，默认大小是16kb。系统的一个磁盘块的存储空间往往没有这么大，所以InnoDB每次申请磁盘空间时都会是多个地址连续磁盘块来达到页的大小16KB。在查询数据时一个页中的每条数据都能定位数据记录的位置，这会减少磁盘 I/O 的次数，提高查询效率。InnoDB存储引擎在设计时是将根节点常驻内存的，力求达到树的深度不超过 3，也就是说I/O不超过3次。树形结构的数据可以让系统高效的找到数据所在的磁盘块，这里就可以说一下这个b树和b+树了，B树的结构是每个节点中有key也有value，而每一个页的存储空间是16kb，如果数据较大时将会导致一页能存储数据量的数量很小。B+Tree的结构是将所有数据记录节点按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的key 值数量，降低B+Tree的高度。所以通过mysql的底层存储的原理和数据结构，我们在设计表的时候，尽量减少单行数据的大小，字段的宽度设得尽可能小，尽可能不用text、Blob、Clob等类型，它会增加存储空间的占用，读取速度较慢。能用数字型字段就不要设计为字符型，因为字符型锁占的存储空间更大，比如，性别这个字段不用男女进行存储，改为0/1的方式，这样不仅可以控制数据量的大小，增加了同一高度下的B+树容纳的数据量，还能提高检索速度。尽量使用varchar/nvarchar代替char/nchar，因为变长字段空间小，可以节省存储空间。不在数据库中存储图片、文件等大数据，可以通过第三方云存储，存放图片或者文件地址。金额用decimal，注意长度和精度。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小树分开存储。尽可能不要给数据库留null值，尤其是时间、整数等类型，可以在建表的时候就给非空设置，NULL的列会使用更多的存储空间，在Mysql中也需要特殊处理，为NULL的列会使索引统计和值比较都更复杂，当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

第二步，就是建索引，当对表中的数据进⾏增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度，所以不是所有字段都适合创建索引的，比如要频繁更改数据不建议使⽤索引，又或者对于关于区分度不高的字段，比如性别，状态字段，就不适合创建索引，只有几种取值的字段，建了索引，数据库也不一定会用，只会白白增加索引维护的额外开销，因为索引也是需要存储的，所以插入和更新的写入操作，需要插入和更新这个字段的索引的。一般需要查询排序，分组和联合操作的字段适合建⽴索引，另外索引多，数据更新表越慢，所以尽量使⽤字段值不重复的字段创建索引。
mysql有聚簇索引，辅助索引，覆盖索引。聚集索引的叶子节点称为数据页，每个数据页通过一个双向链表来进行链接，而且数据页按照主键的顺序进行排列。每个数据页上存放的是完整的行记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。如果定义了主键，InnoDB会自动使用主键来创建聚集索引。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替主键。如果没有唯一的非空索引，InnoDB会隐式定义一个主键来作为聚集索引。辅助索引它叶子节点中没有行记录的全部数据，叶子节点除了包含键值以外，每个叶子节点的索引行还包含了一个书签，该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。覆盖索引先遍历辅助索引，再遍历聚集索引，而如果要查询的字段值在辅助索引上就有，就不用再查聚集索引了，这显然会减少IO操作。除了这三种索引，还有一种联合索引，它是对表上的多个列进行索引，键值都是排序的，通过叶子节点可以顺序的读出所有数据，联合索引的好处在于能起到"一个顶三个"的作用。比如建了一个(a,b,c)的复合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引，每多一个索引，都会增加写操作的开销和磁盘空间的开销，对于大数据的表，这是不小的开销。另外它还可以避免filesort排序，因为filesort的过程，一行数据会被读两次，第一次是where条件过滤时，第二个是排完序后还得用行指针去读一次。filesort的过程是这样的：第一步先根据表的索引或者全表扫描，读取所有满足条件的记录。第二步，存储每一行排序列，就是order by用到的列值，还有行记录指针，就是指向该行数据的行指针，把这二个存储到缓冲区。第三步，当缓冲区满后，运行一个快速排序来将缓冲区中数据排序，将排序完的数据存储到一个临时文件，保存一个存储块的指针，当然如果缓冲区不满，则不会重建临时文件了。直到将所有行读完，建立相应有序的临时文件。第四步，对块级进行排序，这个类似归并排序算法，只通过两个临时文件的指针来不断交换数据，最终达到两个文件，都是有序的，直到所有的数据都排序完毕。第五步，采取顺序读的方式，将每行数据读入内存，取出数据传到客户端。为什么要说这个filesort呢？举二个场景，第一个，如果order by的条件不在索引列上会产生filesort，第二个，排序的字段不在where的条件中，没有办法走索引排序Index，而是走的文件排序filesort 。这种概率其实还是挺高的。这个时候就需要看文件排序用的是单路排序还是双路排序，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。mysql优化器使用双路排序还是单路排序是有自己的算法判断的，如果查询的列字段大于max_length_for_sort_data变量，则会使用双路排序，反之则会使用单路排序。单路排序速度是更快的，不过比较占据内存，如果在内存空间允许的情况下想要使用单路排序的话，可以增加max_length_for_sort_data变量的大小，max_length_for_sort_data变量默认为1024字节。另外sort_buffer_size参数修改为2M，减少在排序过程中对须要排序的数据进行分段，尽量不使用临时表来进行交换排序。这个参数如果超过 2M 的时候，就会使用 mmap()，而不是 malloc() 来进行内存分配，会导致效率降低。这个参数如果过小的话，再加上max_length_for_sort_data变大，一次返回的条数过多，那么很可能就会分很多次进行排序，然后最后将每次的排序结果再串联起来，这样就会更慢。最后read_buffer_size参数也可以根据实际情况调整，它是MySQL读入缓冲区大小，对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小，默认是1M。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

第三步，针对SQL进行调整，在写SQL的时候遵循最左前缀原则，向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，范围列可以用到索引，但是范围列后面的列无法用到索引。like以通配符%开头索引失效会变成全表扫描的操作。如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描。只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。不要使用select *，改用select加字段名称，因为select *走的聚集索引，会进行全表扫描，如果一定要使用select *的话，mysql至少使用5.6版本，这个版本有一个离散读的优化，离散读的优化是将离散度大的列放到联合索引的前面，举个例子，select * from user where staff_id = 2 and customer_id = 584，这个时候索引优化会将customer_id放到前面，因为它的离散度更高，可以通过select count(distinct customer_id),count(distinct staff_id) from user查看列的离散度。有一个ICP的优化，以往，根据索引查找记录，再根据WHERE条件来过滤记录。使用ICP优化后，会在取出索引的同时，直接根据WHERE条件过滤，将WHERE的部分过滤操作放在了存储引擎层。在某些查询下可以大大减少上层SQL层对记录的索取，从而提高性能。还有一个MRR优化，它是减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，在查询辅助索引时，首先根据得到的查询结果，将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的，然后按照主键进行排序，并按照主键排序的顺序进行书签查找。顺序查找可以对一个页进行顺序查找，无需离散加载数据页，可以减少缓冲池中页被替换的次数，能够批量处理对键值的查询操作。另外在写sql的时候，尽量使用它的一个explain执行计划，去看我们的索引是不是失效了。索引失效有这么几种情况，上面也提到过几个，如果条件中有or，即使其中有部分条件带索引也不会使用。对于复合索引，如果不使用前列，后续列也将无法使用。like以%开头。列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引。where中索引列有运算，有函数的，不使用索引。如果mysql觉得全表扫描更快的时候，数据少的情况下，不使用索引。

第四步，随着数据量的增涨之后，会考虑一个数据的分区，Partition分区，表分区必须在表建立的时候创建规则，而已经存在的没有创建过表分区规则的表需要重新做导入处理。如果想修改有规则的表分区，只能新增，不要随意删除，删除表分区会造成该表分区内部数据也一起被删除掉。除此之外，分区也需要结合实际场景进行分区，而且Partition分区也是有一个局限性，因为单台的mysql服务器支持1024个分区，一旦达到这个分区上限，考虑垂直拆分和水平拆分了。垂直分区，它是将单表变多表，这样也是有些优点的，一条数据存储的数据量越小，三层b+所容纳的数据量是更多的，这样一个分区就可以承载多个数据，多个分区它承载的数据就会更多，这是一个累加的效果，不过也有缺点，就是进行表的垂直划分的时候，还需要考虑它的一个关联性，在进行sql查询的情况下，需要反复测试，考虑它的一个性能问题，最好的结果就是拆分出来的表还是能够支持铁定的业务线。比如一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。随着数据量持续的增涨，这个时候就需要考虑水平分区了，水平分区有多种模式，Range（范围）模式：允许DBA将数据划分不同范围。比如DBA可以将一个表通过年份划分成三个分区，80年代的数据，90年代的数据以及任何在2000年之后的数据。Hash（哈希）模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区，比如DBA可以建立一个，对表的主键进行分区的表。List（预定义列表）模式：允许系统通过DBA定义列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年，2005年，2006年值对应数据。Composite（复合模式），就是多模式的组合使用，在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。

第五步，就是冷热备份，对于一些无用的数据，这个时候根据实际的需求，对数据进行一个实时的备份，保证MySQL的数据保持在一个比较稳定的情况。

分库分表

对于分库分表，目前主流的是ShardingSphere，起源于当当网内部的应用框架。
shardingJDBC定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。它使⽤客户端直连数据库，以 jar 包形式提供服务，⽆需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

ShardingProxy定位为透明化的数据库代理端，提供封装了数据库⼆进制协议的服务端版本，⽤于完成对异构语⾔的⽀持。⽬前提供 MySQL 和 PostgreSQL 版本，它可以使⽤任何兼容 MySQL/PostgreSQL 协议的访问客⼾端。

ShardingJDBC只是客户端的一个工具包，可以理解为一个特殊的JDBC驱动包，所有分库分表逻辑均由业务方自己控制，所以他的功能相对灵活，支持的数据库也非常多，但是对业务侵入大，需要业务方自己定制所有的分库分表逻辑。而ShardingProxy是一个独立部署的服务，对业务方无侵入，业务方可以像用一个普通的MySQL服务一样进行数据交互，基本上感觉不到后端分库分表逻辑的存在，但是这也意味着功能会比较固定，能够支持的数据库也比较少。这两者各有优劣。

数据分片主要流程

sql解析整体结构

路由引擎

改写引擎

执行引擎

归并引擎

源码流程

分库分表的的分片算法

提供一个分片键和一个分片表达式来制定分片算法

NoneShardingStrategy

• 不分片。这种严格来说不算是一种分片策略了。只是ShardingSphere也提供了这么一个配置。

InlineShardingStrategy

最常用的分片方式

• 配置参数： inline.shardingColumn 分片键；inline.algorithmExpression 分片表达式
• 实现方式： 按照分片表达式来进行分片。

StandardShardingStrategy

只支持单分片键的标准分片策略。

• 配置参数：standard.sharding-column 分片键；standard.precise-algorithm-class-name 精确分片算法类名；standard.range-algorithm-class-name 范围分片算法类名

• 实现方式：shardingColumn指定分片算法。preciseAlgorithmClassName 指向一个实现了io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm接口的java类名，提供按照 = 或者 IN 逻辑的精确分片 示例：com.roy.shardingDemo.algorithm.MyPreciseShardingAlgorithm。rangeAlgorithmClassName 指向一个实现了 io.shardingsphere.api.algorithm.sharding.standard.RangeShardingAlgorithm接口的java类名，提供按照Between 条件进行的范围分片。示例：com.roy.shardingDemo.algorithm.MyRangeShardingAlgorithm

• 说明： 其中精确分片算法是必须提供的，而范围分片算法则是可选的。

ComplexShardingStrategy

支持多分片键的复杂分片策略。

• 配置参数：complex.sharding-columns 分片键(多个); complex.algorithm-class-name 分片算法实现类。

• 实现方式：shardingColumn指定多个分片列。algorithmClassName指向一个实现了org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm接口的java类名。提供按照多个分片列进行综合分片的算法。示例：com.roy.shardingDemo.algorithm.MyComplexKeysShardingAlgorithm

HintShardingStrategy

不需要分片键的强制分片策略。这个分片策略，简单来理解就是说，他的分片键不再跟SQL语句相关联，而是用程序另行指定。对于一些复杂的情况，例如select count(*) from (select userid from t_user where userid in (1,3,5,7,9)) 这样的SQL语句，就没法通过SQL语句来指定一个分片键。这个时候就可以通过程序，给他另行执行一个分片键，例如在按userid奇偶分片的策略下，可以指定1作为分片键，然后自行指定他的分片策略。

• 配置参数：hint.algorithm-class-name 分片算法实现类。

• 实现方式：algorithmClassName指向一个实现了org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm接口的java类名。 示例：com.roy.shardingDemo.algorithm.MyHintShardingAlgorithm。在这个算法类中，同样是需要分片键的。而分片键的指定是通过HintManager.addDatabaseShardingValue方法(分库)和HintManager.addTableShardingValue(分表)来指定。使用时要注意，这个分片键是线程隔离的，只在当前线程有效，所以通常建议使用之后立即关闭，或者用try资源方式打开。

而Hint分片策略并没有完全按照SQL解析树来构建分片策略，是绕开了SQL解析的，所有对某些比较复杂的语句，Hint分片策略性能有可能会比较好(情况太多了，无法一一分析)。
但是要注意，Hint强制路由在使用时有非常多的限制：
– 不支持UNION SELECT * FROM t_order1 UNION SELECT * FROM t_order2 INSERT INTO tbl_name (col1, col2, …) SELECT col1, col2, … FROM tbl_name WHERE col3 = ?
– 不支持多层子查询 SELECT COUNT(*) FROM (SELECT * FROM t_order o WHERE o.id IN (SELECT id FROM t_order WHERE status = ?))
– 不支持函数计算。ShardingSphere只能通过SQL字面提取用于分片的值 SELECT * FROM t_order WHERE to_date(create_time, ‘yyyy-mm-dd’) = ‘2019-01-01’;

从这里也能看出，即便有了ShardingSphere框架，分库分表后对于SQL语句的支持依然是非常脆弱的。

分库分表带来的问题

​ 1、分库分表，其实围绕的都是一个核心问题，就是单机数据库容量的问题。我们要了解，在面对这个问题时，解决方案是很多的，并不止分库分表这一种。但是ShardingSphere的这种分库分表，是希望在软件层面对硬件资源进行管理，从而便于对数据库的横向扩展，这无疑是成本很小的一种方式。

​ 2、一般情况下，如果单机数据库容量撑不住了，应先从缓存技术着手降低对数据库的访问压力。如果缓存使用过后，数据库访问量还是非常大，可以考虑数据库读写分离策略。如果数据库压力依然非常大，且业务数据持续增长无法估量，最后才考虑分库分表，单表拆分数据应控制在1000万以内。

​ 当然，随着互联网技术的不断发展，处理海量数据的选择也越来越多。在实际进行系统设计时，最好是用MySQL数据库只用来存储关系性较强的热点数据，而对海量数据采取另外的一些分布式存储产品。例如PostGreSQL、VoltDB甚至HBase、Hive、ES等这些大数据组件来存储。

​ 3、从ShardingJDBC的分片算法中我们可以看到，由于SQL语句的功能实在太多太全面了，所以分库分表后，对SQL语句的支持，其实是步步为艰的，稍不小心，就会造成SQL语句不支持、业务数据混乱等很多很多问题。所以，实际使用时，我们会建议这个分库分表，能不用就尽量不要用。

​ 如果要使用优先在OLTP场景下使用，优先解决大量数据下的查询速度问题。而在OLAP场景中，通常涉及到非常多复杂的SQL，分库分表的限制就会更加明显。当然，这也是ShardingSphere以后改进的一个方向。

​ 4、如果确定要使用分库分表，就应该在系统设计之初开始对业务数据的耦合程度和使用情况进行考量，尽量控制业务SQL语句的使用范围，将数据库往简单的增删改查的数据存储层方向进行弱化。并首先详细规划垂直拆分的策略，使数据层架构清晰明了。而至于水平拆分，会给后期带来非常非常多的数据问题，所以应该谨慎、谨慎再谨慎。一般也就在日志表、操作记录表等很少的一些边缘场景才偶尔用用。

主从复制

主从复制（replication）的工作原理

1）主服务器（master）把数据更改记录到二进制日志（binlog）中。
2）从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relaylog）中。
3）从服务器重做中继日志中的日志，把更改数据到自己的数据库上，以达到数据的最终一致性。
复制的工作原理并不复杂，其实就是一个完全备份加上二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上实时在进行中。这里特别需要注意的是，复制不是完全实时地进行同步，而是异步实时。这中间存在主从服务器之间的执行延时，如果主服务器的压力很大，则可能导致主从服务器延时较大。

主从复制bin log 日志有几种记录方式，说说各自的优缺点

Replication之所以能够工作，主要还是归结于binlog（binary log），所以在 Replication 模式下必须开启binlog功能。slave从masters 上增量获取 binlog 信息，并在本地应用日志中的变更操作，变更操作根据选择的格式类型写入binlog文件，目前支持三种格式：
statement-based Replication（SBR） ：master将SQL statements语句写入binlog，slave 也将statements 复制到本地执行；简单而言，就是在 master 上执行的 SQL 变更语句，也同样在 slaves 上执行。SBR 模式是 MySQL 最早支持的类型，也是Replication默认类型。
row-based Replication（RBR）： master将每行数据的变更信息写入binlog，每条 binlog 信息表示一行（row）数据的变更内容，对于 slaves 而言将会复制 binlog 信息，然后单条或者批量执行变更操作；
mix-format Replication：混合模式，在这种模式下，master将根据根据存储引擎、变更操作类型等，从SBR、RBR中来选择更合适的日志格式，默认为 SBR；具体选择那种格式，这取决于变更操作发生的存储引擎、statement 的类型以及特征，优先选择“数据一致性” 最好的方式（RBR），然后才兼顾性能，比如 statement 中含有 “不确定性” 方法或者批量变更，那么将选择RBR方式，其他的将选择 SBR 以减少binlog 的大小。我们建议使用mix方式。SBR 和 RBR 都有各自的优缺点，对于大部分用而言，mix 方式在兼顾数据完整性和性能方面是最佳的选择。

SBR的优点：

• 因为 binlog 中只写入了变更操作的statements，所以日志量将会很小；
• 当使用 SQL 语句批量更新、删除数据时，只需要在 binlog 中记录 statement 即可，可以大大减少log文件对磁盘的使用
• 当然这也意味着 slave 复制信息量也更少，以及通过binlog恢复数据更加快速；

SBR的缺点 ：有些变更操作使用 SBR 方式会带来数据不一致的问题，一些结果具有不确定性的操作使用SBR将会引入数据不一致的问题。

• statement 中如果使用了 UDF(User Defination Fuction)，UDF 的计算结果可能依赖于SQL 执行的时机和系统变量，这可能在 slave 上执行的结果与 master 不同，此外如果使用了trigger，也会带来同样的问题；
• statement 中如果使用了如下函数的（举例）：UUID()，SYSDATE()，RAND() 等，不过NOW() 函数可以正确的被Replication（但在 UDF 或者触发器中则不行）；这些函数的特点就是它们的值依赖于本地系统，RAND() 本身就是随机所以值是不确定的。如果 statement 中使用了上述函数，那么将会在日志中输出warning信息；
• 对于 “INSERT … SELECT” 语句，SBR 将比 RBR 需要更多的行锁。（主要是为了保障数据一致性，需要同时锁定受影响的所有的行，而RBR则不必要）；
• 对于 InnoDB，使用 “AUTO_INCREMENT” 的 insert 语句，将会阻塞其他 “非冲突” 的INSERT。（因为AUTO_INCREMENT，为了避免并发导致的数据一致性问题，只能串行，但 RBR 则不需要）；
• 对于复杂的SQL语句，在 slaves 上仍然需要评估（解析）然后才能执行，而对于RBR，SQL 语句只需要直接更新相应的行数据即可；在 slave 上评估、执行 SQL 时可能会发生错误，这种错误会随着时间的推移而不断累加，数据一致性的问题或许会不断增加。

RBR的优点：

• 所有的变更操作，都可以被正确的Replication，这事最安全的方式；
• 对于 “INSERT … SELECT”、包含 “AUTO_INCREMENT” 的 inserts、没有使用索引的UPDATE/DELETE，相对于SBR 将需要更少的行锁。（意味着并发能力更强）；
  RBR的缺点：
• 最大的缺点：就是 RBR 需要更多的日志量。任何数据变更操作都将被写入log，受影响的每行都要写入日志，日志包含此行所有列的值（即使没有值变更的列）；因此RBR 的日志条数和尺寸都将会远大于SBR，特别是在批量的 UPDATE/DELETE 时，可能会产生巨大的 log 量，反而对性能带来影响，尽管这确实保障了数据一致性，确导致Replication的效率较低；

主从复制有几种方式？

异步复制

MySQL主从集群默认采用的是一种异步复制的机制。Master处理事务过程中，将其写入Binlog就会通知Dumpthread线程处理，然后完成事务的提交，不会关心是否成功发送到任意一个slave中。主服务在执行用户提交的事务后，写入binlog日志，然后就给客户端返回一个成功的响应了。而binlog会由一个dump线程异步发送给Slave从服务，由于这个发送binlog的过程是异步的。主服务在向客户端反馈执行结果时，是不知道binlog是否同步成功了的。这时候如果主服务宕机了，而从服务还没有备份到新执行的binlog，那就有可能会丢数据。

半同步复制

半同步复制机制是一种介于异步复制和全同步复制之前的机制。主库在执行完客户端提交的事务后，并不是立即返回客户端响应，而是等待至少一个从库接收并写到relay log中，才会返回给客户端。MySQL在等待确认时，默认会等10秒，如果超过10秒没有收到ack，就会降级成为异步复制。

这种半同步复制相比异步复制，能够有效的提高数据的安全性。但是这种安全性也不是绝对的，他只保证事务提交后的binlog至少传输到了一个从库，并且并不保证从库应用这个事务的binlog是成功的。另一方面，半同步复制机制也会造成一定程度的延迟，这个延迟时间最少是一个TCP/IP请求往返的时间。整个服务的性能是会有所下降的。而当从服务出现问题时，主服务需要等待的时间就会更长，要等到从服务的服务恢复或者请求超时才能给用户响应。

Master 处理事务过程中，提交完事务后，必须等至少一个Slave 将收到的binlog写入relaylog返回ack 才能继续执行处理用户的事务。相关配置rpl_semi_sync_master_wait_point=AFTER_COMMIT【这里MySQL5.5并没有这个配置，MySQL5.7 为了解决半同步的问题而设置的】rpl_semi_sync_master_wait_for_slave_count=1 （最低必须收到多少个slave的ack）rpl_semi_sync_master_timeout=100（等待ack的超时时间）

半同步复制需要基于特定的扩展模块来实现。mysql从5.5版本开始，往上的版本都默认自带了这个模块。这个模块包含在mysql安装目录下的lib/plugin目录下的semisync_master.so和semisync_slave.so两个文件中。需要在主服务上安装semisync_master模块，在从服务上安装semisync_slave模块。

增强半同步复制

增强半同步和半同步不同是，等待ACK时间不同rpl_semi_sync_master_wait_point=AFTER_SYNC（唯一区别）半同步的问题是因为等待ACK的点是Commit 之后，此时Master已经完成数据变更，用户已经可以看到最新数据，当Binlog 还未同步到Slave时，发生主从切换，那么此时从库是没有这个最新数据的，用户又看到老数据。增强半同步将等待ACK的点放在提交Commit 之前，此时数据还未被提交，外界看不到数据变更，此时如果发送主从切换，新库依然还是老数据，不存在数据不一致的问题。

读写分离

为了保证数据一致，通常会需要保证数据只在主服务上写，而从服务只进行数据读取。这个功能就是读写分离。mysql主从本身是无法提供读写分离的服务的，需要由业务自己来实现，这也是ShardingSphere的一个重要功能。

在MySQL主从架构中，是需要严格限制从服务的数据写入的，一旦从服务有数据写入，就会造成数据不一致。并且从服务在执行事务期间还很容易造成数据同步失败。如果需要限制用户写数据，我们可以在从服务中将read_only参数的值设为1( set global read_only=1; )。这样就可以限制用户写入数据。

read_only=1设置的只读模式，不会影响slave同步复制的功能。限定的是普通用户进行数据修改的操作，但不会限定具有super权限的用户的数据修改操作。

如果需要限定super权限的用户写数据，可以设置super_read_only=0。另外如果要想连super权限用户的写操作也禁止，就使用"flush tables with read lock;"，这样设置也会阻止主从同步复制！

读写分离延时导致的数据不一致解决方案

数据同步

主从数据同步方案，一般都是采用的异步方式同步给备库。
我们可以将其修改为同步方案，主从同步完成，主库上的写才能返回。

• 业务系统发起写操作，数据写主库；
• 写请求需要等待主从同步完成才能返回；
• 数据读从库，主从同步完成就能读到最新数据。

缺点：主库写需要等待主从完成，写请求的时延将会增加，吞吐量将会降低

中间件路由

写请求，中间件将会发到主库，同时记录一下此时写请求的 key（操作表加主键等）；
读请求，如果此时 key 存在，将会路由到主库；
一定时间后（经验值），中间件认为主从同步完成，删除这个 key，后续读将会读从库。

缺点：系统架构增加了一个中间件，整体复杂度变高，业务开发也变得复杂，学习成本也比较高

缓存路由

写请求发往主库，同时缓存记录操作的 key，缓存的失效时间设置为主从的延时；
读请求首先判断缓存是否存在：

• 若存在，代表刚发生过写操作，读请求操作主库；

• 若不存在，代表近期没发生写操作，读请求操作从库。

优缺点：成本较低，但是呢我们此时又引入一个缓存组件，所有读写之间就又多了一步缓存操作

mysql数据库集群高可用

使用MySQL自身的功能来搭建的集群，这样的集群，不具备高可用的功能。即如果是MySQL主服务挂了，从服务是没办法自动切换成主服务的。常见的MySQL集群方案有三种: MMM、MHA、MGR。

对主从复制集群中的Master节点进行监控。
自动的对Master进行迁移，通过VIP。
重新配置集群中的其它slave对新的Master进行同步。

MMM

MMM是一套由Perl语言实现的脚本程序，可以对mysql集群进行监控和故障迁移。他需要两个Master，同一时间只有一个Master对外提供服务，可以说是主备模式。​他是通过一个VIP(虚拟IP)的机制来保证集群的高可用。整个集群中，在主节点上会通过一个VIP地址来提供数据读写服务，而当出现故障时，VIP就会从原来的主节点漂移到其他节点，由其他节点提供服务。
优点：

提供了读写VIP的配置，使读写请求都可以达到高可用
工具包相对比较完善，不需要额外的开发脚本
完成故障转移之后可以对MySQL集群进行高可用监控

缺点：

故障简单粗暴，容易丢失事务，建议采用半同步复制方式，减少失败的概率
目前MMM社区已经缺少维护，不支持基于GTID的复制

适用场景：

读写都需要高可用的
基于日志点的复制方式

MHA

由日本人开发的一个基于Perl脚本写的工具。这个工具专门用于监控主库的状态，当发现master节点故障时，会提升其中拥有新数据的slave节点成为新的master节点，在此期间，MHA会通过其他从节点获取额外的信息来避免数据一致性方面的问题。MHA还提供了mater节点的在线切换功能，即按需切换master-slave节点。MHA能够在30秒内实现故障切换，并能在故障切换过程中，最大程度的保证数据一致性。在淘宝内部，也有一个相似的TMHA产品。

MHA是需要单独部署的，分为Manager节点和Node节点，两种节点。其中Manager节点一般是单独部署的一台机器。而Node节点一般是部署在每台MySQL机器上的。 Node节点得通过解析各个MySQL的日志来进行一些操作。

Manager节点会通过探测集群里的Node节点去判断各个Node所在机器上的MySQL运行是否正常，如果发现某个Master故障了，就直接把他的一个Slave提升为Master，然后让其他Slave都挂到新的Master上去，完全透明。

优点：

MHA除了支持日志点的复制还支持GTID的方式
同MMM相比，MHA会尝试从旧的Master中恢复旧的二进制日志，只是未必每次都能成功。如果希望更少的数据丢失场景，建议使用MHA架构。

缺点：

MHA需要自行开发VIP转移脚本。
MHA只监控Master的状态，未监控Slave的状态

MGR

MGR：MySQL Group Replication。 是MySQL官方在5.7.17版本正式推出的一种组复制机制。主要是解决传统异步复制和半同步复制的数据一致性问题。

由若干个节点共同组成一个复制组，一个事务提交后，必须经过超过半数节点的决议并通过后，才可以提交。引入组复制，主要是为了解决传统异步复制和半同步复制可能产生数据不一致的问题。MGR依靠分布式一致性协议(Paxos协议的一个变体)，实现了分布式下数据的最终一致性，提供了真正的数据高可用方案(方案落地后是否可靠还有待商榷)。

支持多主模式，但官方推荐单主模式：
多主模式下，客户端可以随机向MySQL节点写入数据
单主模式下，MGR集群会选出primary节点负责写请求，primary节点与其它节点都可以进行读请求处理.

优点：

基本无延迟，延迟比异步的小很多
支持多写模式，但是目前还不是很成熟
数据的强一致性，可以保证数据事务不丢失

缺点：

仅支持innodb，且每个表必须提供主键。
只能用在GTID模式下，且日志格式为row格式。