mysql总结

未

索引

数据结构

B+树：B+树是由B树和索引顺序访问方法演化而来。B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树种，所有记录的节点都是按照顺讯存放在同一层的叶子节点中，并由各叶子节点进行连接。插入删除需要做拆分和合并操作。
B+树索引：B+树索引在数据库中特点是高扇出性(子节点多)，从而高度就会相应的降低。查询一次数据只需要2-4次io。

聚集/非聚集索引

聚集索引&非聚集索引：区别是叶子节点是否存放的是一整行信息。
聚集索引：Innodb存储引擎表是索引组织表，即表中数据是按主键顺序存放。聚集索引就是按照每张表的主键构造的一棵B+树，同时叶子节点存放的即为整张表的行记录数据，所以叶子节点也叫数据页。
为什么聚集索引只能有一个：因为实际的数据页只能按照一棵B+树进行排序，即数据页只能存放在一棵索引树上，因此一张表只能有一个聚集索引。
为什么查询优先使用聚集索引：因为数据在聚集索引的叶子节点上，而且聚集索引的叶子节点定义了数据的逻辑顺序，所以聚集索引特别适合范围查询。
有人说数据是按照索引物理顺序存放的：错误，物理存放的维护成本太高了，需要多大的空闲磁盘。另外叶子节点都是用指针相连的，没必要物理存放。即是在页的内部，记录之间也是指针相连的。
非聚集索引：叶子节点不包含数据的行记录信息，叶子节点除包含键值外，还包含该索引对应的聚集索引键。每张表可以有多个非聚集索引，查询方式为先在非聚集索引找到键值，通过对应的指针找到聚集索引，然后取回数据。所以聚集索引会慢一些。
什么字段适合建立索引：高选择性
联合索引：最左原则
覆盖索引：如果索引的叶子节点包含了要查询的数据，那么就不用回表查询了，也就是说这种索引包含（亦称覆盖）所有需要查询的字段的值，我们称这种索引为覆盖索引

锁

解决了事务的隔离性

锁的类型

行级锁:共享锁，排它锁。共享锁跟共享锁兼容。排它锁跟任何锁都不兼容。
表锁：在Innodb引擎中，意向锁就是表锁(其他引擎不一定).分为意向共享锁和意向排它锁。意向锁意味着事务希望在更加细粒度上加锁

一致性非锁定读

定义：Innodb存储引擎通过行多版本控制的方式来读取当前时间数据库中的行数据。如果当前行有X锁，读取操作不会等待锁释放，相反，它会读取一个快照版本。
原理：通过undo段来实现，undo用来在事务中回滚数据，因此快照数据本身没有额外的开销。因为读不加锁，所以极大的提高了数据的并发性。
rc&rr:在rc和rr隔离级别下，Innodb使用mvcc。但是在这两种隔离级别下，每种隔离级别对快照的定义不一样。在rc下总是读取快照的最新一份数据，即如果一个事务修改完数据提交了，rc立马能读到，这个特性其实破坏了ACID中的I，隔离性。在rr下，总是读去事务开始时候的快照。所以rr解决了不可重复读。例子可以参考MYSQL技术内幕中6.3.2节。
rc&rr原理上区别：rc相比于rr没有next-key锁，所以解决不了不可重复读和幻读问题

一致性锁定读

场景：用户需要在某种场景下对数据库读操作进行加锁，以保证数据逻辑性一致。
select ... for update：加x锁
select ... in share mode：加s锁
要求：上述语句必须在一个事务中。

锁的算法

行锁算法

3种算法：record lock, 单个行锁。gap lock,锁定一个范围，不包括记录本身。next-key lock,锁定一个范围，包括记录本身。
record lock:总是会锁住索引记录，如果没有索引，默认使用主键。
next-key lock:是gap lock+ record lock的结合体。在这种算法下，Innodb对行的查询都会采用这种锁定。比如有列1 3 5，则锁定范围为(负无穷 1] (1 3] (3 5] (5 无穷]
next-key lock优化但是当查询的索引有唯一属性时，Innodb引擎会对next-lock进行优化，降级为record lock.即锁住索引本身，而不是范围。如果唯一索引是多列，查询条件是其中的某一列，依然是范围，而不会降级。降级的好处是提高了并发性。
但是如果查询的索引不具有唯一属性，则锁定的还是范围，比如有两列(a,b), a是主键，b是普通索引。值：(1,1)(3,1)(5,3)(7,6)(10,8)那么对于语句select * from table where b=3 for update语句依然使用的是next-key lock.锁定的范围是(1 3),特别需要注意的是Innodb还会对索引的下一个键值加上gap lock，即(3 6)也会锁定。
关闭gap lock：1. 事务隔离级别降为rc。2.显示设置参数。
next-key作用：解决幻读/不可重复读问题。需要注意的是，事务隔离级别在rc下只有record lock。

锁问题

脏读：在不同的事务下，当前事务读到了其他事务未提交的数据。脏读需要在RU隔离级别下才能复现。
不可重复读&幻读：当前事务还没有结束，其他事务也访问同一个数据集，并做了写操作并提交了，那么当前事务连续两次读取读到的数据不一样。不可重复读需要在RC隔离级别下复现。Innodb存储引擎在RR隔离级别下，通过next-key lock解决了不可重复读问题。
丢失更新：理论上存在的问题，因为即使在RU隔离级别下，也会有锁。

死锁

解决方案：1.超时回滚。2. 通过wait-for graph主动监测。
超时回滚：超时回滚本质上是一种FIFO的方式，即后来的事务会因为等待前面的事务而回滚。带来的问题是，如果后来的事务很大，则这个事务回滚会造成很多资源浪费。
wait-for graph：等待图中的节点是事务，边是事务A指向事务B，含义有两点：1.事务A等待事务B占用的资源。2.事务A将要等待事务B占用的资源，即事务A和事务B在等待相同的资源，但是事务A在时间上后与事务B执行。如果图中存在环，则说明有死锁发生。要想生成wait-for图，需要数据库保存锁的信息
对比：两种方案模式不同，超时是被动的，图是主动检测的，而且检测到死锁后会优先回滚undo量最小的事务。

事务

概述

定义：事务是访问并更新数据库中各项数据的一个执行单元。
目的：事务会把数据库从一种状态转为另一种状态。在数据库提交工作时，可以确保要么所有的修改都保存了，要么所有的修改都没保存。
acid: a,Atomicity原子性，指的是整个数据库事务是不可分割的单位。事务中所有的操作都执行成功，才能算事务执行成功。c,consisency一致性，事务从一种一致性状态转为下一个一致性状态，在事务开始前和开始后，数据库的完整性约束没有被破坏。i,isolation隔离性，还可以叫做并发控制，可串行化，锁等。要求每个读写事务的对象对其他操作事务的对象相互分离。一般通过锁来实现。d,durability事务一旦提交就是永久性的，即时发生宕机故障，数据库也能恢复。需要注意的是，只能从事务本身的角度保证结果的永久性能。如果数据库本身挂了就不太行了。

分类

扁平事务：所有操作处于同一层次，由begin 开始，commit/rollback结束。缺点是，不支持回滚一部分。
带保存点的扁平事务：允许事务执行过程中，回滚到同一事务较早的一个状态。执行过程中，通过save work来设置保存点。
链事务:保存点模式的一个变种，可以看做，把一个大的事务拆成了很多小事务。带保存点的事务能回滚到任意正确的保存点，链事务只能回滚到最近的事务。
嵌套事务：感觉没有什么卵用

事务的实现

概念：事务的隔离性有锁实现，原子性，一致性，持久性由redo log和undo log实现。 redo log称作重做日志，用来保证事务的原子性和持久性，undo log用来保证一致性。
redo vs undo:undo不是redo的逆操作，redo恢复事务提交的页操作。undu回滚行记录到某个特定的版本。redo记录的是页的物理修改操作，undo是逻辑日志，根据没行记录进行操作。

redo

redo: Innodb听过force log at commit保证事务的持久性。即在事务提交前必须保证事务日志写入了磁盘然后才commit才算完成。 而事务日志写入磁盘有两步，1 数据的事务日志缓存写入操作系统缓存。2. 执行fsync操作，将操作系统缓存吸入磁盘。 所以这里涉及三种持久化策略：1. (默认)每次commit都必须调用fsync，2. 事务提交不进行重做日志的持久化(操作系统缓存也不管), 由master thread每秒进行一次fsync。3. 每次commit操作只写入操作系统缓存，不fsync。fsync由操作系统保证。很显然，模式1是最损耗性能的，磁盘瓶颈体现的很明显。但是后两种模式损失了事务的是就行
redo log vs binlog：binlog用来做point in time的恢复和主从环境的建立。本质上两者是非常不一样的。
1. redo log是innodb存储引擎产生的。而binlog是在数据库的上层产生的。并且binlog跟存储引擎无关，任何存储引擎对数据库的个更改binlog都会记录。
2. 记录的内容形式不一样，binlog是一种逻辑日志，记录的是对应的sql语句。redo log是物理格式日志，记录的是对于每个页的修改。
3. 磁盘写入时间点不同，binlog每次提交后进行一次写入，redo log的写入工作可能贯穿整个事务期间。
redo log block：重做日志都是以512字节为单位进行存储的。因此缓存，文件都是以块的形式保存的，称为重做日志块。日志块有三部分组成，头，内容，尾部。头12字节，尾8字节。 问题来了，为什么是512，因为磁盘扇区的最小写入单位是512字节。
redo log file:太尼玛底层了
redo log group
redo log 格式
lsn:log sequence number,日志序列号。

undo

概念：redo log很好的记录了事务的行为，可以通过redo log对页进行重做操作。undo用来支持事务回滚。
undo segment：undo log不存在文件里，而是在undo segemnt里，undo segment位于共享表空间内。
恢复误区：undo是逻辑日志，所以undo是逻辑的将事务恢复到某个点，而不是物理的。比如用户insert了10万条数据，表空间肯定增大，rollback后，内容会被删除，但是表空间并不会收缩。
逻辑方式：对于insert记录delete，对于delete记录insert,对于update记录相反的update.
mvcc：Innodb中的mvcc是通过undo实现的。
undo持久性：undo也会产生redo log, 保证undo的持久性。

purge

概念:delete和update操作可能并不会直接删除原始数据，而是在对应的索引上记录del flag，这样做的原因是为了支持mvcc，因为可能有其他事务在读以前的版本。

隔离级别

概念：SQL标准制定的隔离级别：ru,rc,rr,ser, Innodb默认的是rr，标准隔离级别情况下，rr并没有解决幻读问题，但是Innodb使用了next-key lock技术，所以它在rr下就完成了隔离性。

2PL

两阶段加锁协议，用于单机事务中事务的一致性和隔离性。
定义:在一个事务里，分为加锁阶段和解锁阶段，也即所有的lock操作都在unlock操作之前。
为什么需要2PL：2PL是为了保证事务的隔离性，即多个事务在并发的情况下等同于串行执行。
S2PL：strict-2PL, 意思是只有在commit的时候和rollback的时候解锁，其他时候都是加锁。
性能考虑：从性能考虑讲，越热点的记录，越要放到事务最后，可以保证热点记录锁定的时间尽可能短，这样可以显著的提高吞吐量。简单的讲，越热点的记录，离事务的zhong'dian

分布式事务

概念

简单分布式事务: 单一服务访问不同的数据库资源。service->(Resource1,Resource2);
复杂分布式事务: Service1->(Resource1, Service2->(Resource3, Resource4))

分布式事务模型

XA协议(X/Open Distributed Transaction Processing)DTP模型

DTP模型中包含一个全局事务管理器(TM)和多个资源管理器(RM，其实就是数据库管理器).全局事务管理器负责全局事务状态和参与的资源，协同资源一起提交或者回滚，资源管理器负责具体的资源操作。
XA协议其实描述了TM与RM之间的接口，允许多个资源在同一个事务中访问。

DTP模型的执行流程如下：
1，应用程序向TM申请一个全局事务
2. 针对要操作的RM，应用程序向TM注册，TM通过XA接口函数通知相应的RM开启分布式事务的子事务，然后应用程序对RM进行操作。
3. 当AP对RM操作完毕后，通知TM提交或者回滚改全局事务，TM通过XA接口函数通知各RM完成操作。TM会先要求各子事务做预提交，所有RM返回成功后，在要求各RM做正式提交。XA协议要求，一单RM预提交成功，则后续的正式提交也必须能够成功。如果任何一个RM预提交失败，则TM通知RM回滚。
4. 所有RM提交完毕或者回滚完毕，则事务结束。

原子性

2PC：Two Phase Commit,两阶段提交，提交过程分为两个阶段，准备阶段和提交阶段；
准备阶段：TM想每个RM发送准备消息，如果RM的本地事务操作执行成功，则返回成功，否则返回失败。
提交阶段：如果TM收到了所有RM回复的成功消息，则向RM发送提交消息，否则发送回滚消息。RM根据TM的执行执行提交或者回滚本地事务，释放所有事务处理过程中使用的锁资源。
原子性：XA协议使用2PC原子提交协议来保证事务的原子性。

隔离性

XA协议没有描述如何实现分布式事务的隔离性，但是要求每个RM要实现本地事务。也就是说全局事务的隔离性，是由每个RM本地事务来保证的。当所有的子事务都有隔离性后，那么整体的全局性事务也就有隔离性了。

一致性

含义: 有两层含义：1.数据库从一个状态转为另一个状态，2.事务执行过程中的中间状态不能被观察到。
实现方式：第一层语义，通过原子性，隔离性和子事务的一致性可以保证。第二层语义XA协议保证不了，MYSQL的实现方式是使用Serializable.当然也可以实现全局的MVCC(参考mysql的mvcc)。

总结

XA协议通常实现在数据库资源层，直接作用在资源管理器上。因此基于XA协议实现的分布式产品，不论是分布式数据库，还是分布式服务框架，对业务没有侵入。

TCC(try-confirm-cancel)模型

相比XA协议，tcc作用在服务层。
含义不依赖资源管理，而是通过业务逻辑来实现分布式事务。
条件：tcc要求，一个服务需要提供三个接口，try接口，confirm接口，cancel接口。
1.try操作，完成所有的业务检查，预留必须的业务资源。
2.confirm操作，执行正在的业务逻辑，不做业务资源检查，只使用try阶段预留的业务资源。因此只要try成功，confirm必须成功。另外confirm需要完成幂等。
3.cancel，释放try阶段预留的业务资源。也需要幂等。
组成：主服务，从服务，业务活动管理器(记录TCC全局事务状态，从事务状态，执行confirm操作，cancel操作)
过程
1.主服务开启本地事务
2.主服务想管理器申请分布式事务
3.主服务向管理器注册从业务活动，然后调用try接口，申请资源。
4.当所有从服务返回成功，主服务提交本地事务，是失败回滚本地事务。
5.主服务提交本地事务后，管理器调用从服务的confirm接口，否则调用cancel接口。
6.提交或者取消后，事务je'k
acid
1.原子性，使用2PC保证原子性，try对应小XA的准备阶段。confirm对应XA的commit阶段，cancel对应XA的rollback阶段。
2.隔离性，隔离性有从业务保证，从业务的隔离性由业务逻辑保证。
3.一致性，在服务层，其实一致性不太重要。。。妈的等于什么都没说。

BASE理论

BA:Basic Availability,基本业务可用性
S:Soft State,柔性状态
E:Eventual consistency 最终一致。
base理论认为，为了可用性，性能和降级服务需要，可以适当降低一点一致性的要求，即"基本可用，最终一致"。

总结

TCC 模型通过 2PC 原子提交协议保证分布式事务的的原子性，把资源层的隔离性上升到业务层，交给业务逻辑来实现。TCC 的每个操作对于资源层来说，就是单个本地事务的使用，操作结束则本地事务结束，规避了资源层在 2PC 和 2PL 下对资源占用导致的性能低下问题。
同时，TCC 模型也可以根据业务需要，做一些定制化的功能，比如交易异步化实现削峰填谷等。
但是，业务接入 TCC 模型需要拆分业务逻辑成两个阶段，并实现 Try、Confirm、Cancel 三个接口，定制化程度高，开发成本高。

复制

作用：1.数据分布2.负载均衡3.背负4.高可用&容错
简单步骤：1. master记录而二进制日志。2.slave读取master的二进制日志，copy自己的中继日志中。3.slave重做中继日志中的事件，将改变反应到自己的数据中。
详细描述：
过程中第一步就是master记录二进制日志。每个事务更新数据完成之前，master需要记录这些改变，mysql将事务串行写入日志。在事件写入日志完成后，底层的存储引擎提交事务。第二个过程是slave将master的binlog拷贝到自己的中继日志，首先slave需要开启一个工作线程-I/O线程，I/O线程在master上打开一个普通的连接，然后binlog dump. 如果发现自己已经是最新的数据则等待一会儿。否则I/O线程将这些事件写入中继日志。 SQL slave thread处理最后一步，从中继日志中读取事件，更新slave的数据，跟master保持一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所有中继日志开销比较小。此外master中也会开启一个工作线程，和其他mysql链接一样，slave在master中打开一个链接，也会使得master开始一个线程。复制过程有一个很重要的限制-复制在slave上是串行化的，也就是master上的并行更新操作不能在slave上并行操作。

高可用

MHA

含义：master high availability,可以故障切换，主从提升。
部件: MHA manager, MHA node, manager通常部署在单独的机器上，管理多个master-slave集群。node则运行在每台mysql服务器上。
原理:manager会定时探测集群中的master节点，当master节点出现故障时，它可以将最新数据的slave提升至master，并把其他slave指向新的master。整个故障转义过程对应用层透明。在故障转义过程中，MHA试图从宕机的服务器上保存二进制日志，最大成都的保证数据不丢失，但是不一定可行，比如硬件故障或者ssh失败。如果跟半自动同步配合，可以大大降低丢失的风险。MHA跟半自动结合起来，如果一台slave收到了最新的日志，mha可以将最新的日志应用于其他所有的slave机器上，因此可以保证所有节点的一致性。
条件：至少需要3台数据库服务器，一台master，一台备用master，一台从。
过程
1.从宕机的master上保存二进制日志
2.识别含有最新的slave
3.应用差异的中继日志到其他的slave
4.应用从master保存的二进制日志
5.提升一个slave为新的master
6.使其他的slave连接新的master