应用开发实践之关系型数据库(以MySql为例)小结

多年开发实践中遇到的DB相关的话题研究和整理，不介绍DB的基本概念，也不过于深入DB原理，以满足日常应用、知其然知其所以然为准。 包含十几个子话题，含事务传播性、索引优化、拆分、FailOver等。

本文主要是对目前工作中使用到的DB相关知识点的总结，应用开发了解到以下深度基本足以应对日常需求，再深入下去更偏向于DB本身的理论、调优和运维实践。
不在本文重点关注讨论的内容（可能会提到一些）：

• 具体的DQL、DML、DDL、DCL等语法
• 基础性的概念，如主键、索引、存储过程（即sql级的编程。注：阿里巴巴规范中禁止使用存储过程）等
• 联合查询，我个人不太喜欢在应用中写过于复杂的SQLsu，性能和后续维护容易出现问题
• 可能会用到的具体DB特性，如oracle的DATA GUARD
  有一些属于基础知识或语法但是常用的信息，也会列一下，如join的用法。

一、基础

1. ACID

DB的四大特性。 (错)
衡量事务的四个维度 (对)

• 原子性（Atomicity）：事务操作中的多条SQL，要么全部成功要么全部失败，失败后回滚不对原有数据造成任何影响。
• 一致性（Consistency）:事务开始前和结束后，数据库的完整性没有被破坏。如触发器、约束、级联回滚
• 隔离性（Isolation）：多个事务支持并发读写。具体隔离级别见后文。
• 持久性（Durability）：事务结束后，修改是永久的，不丢失。

如MySql：

• 原子性通过undo log保证
• 隔离性通过锁、MVCC近似保证
• 持久性通过undo/redo log和binlog保证
• 一致性通过以上三方面及应用层面保证

2. 范式

这里展开讲比较复杂，实践中很少用到，一般满足1NF即可。
高一级必满足低一级。

1. 1NF：每个属性都不可再分，即表的列是最原子的
2. 2NF：在1NF基础上，消除非主属性对键的部分依赖。这里不解释非主属性和键的含义，可以简单认为是指不存在列A可以通过列B来获取，如“学生姓名-学号”这种y=f(x)的函数关系。
3. 3NF：在2NF的基础之上，消除了非主属性对于码的传递函数依赖
4. BCNF：对于关系模式R，如果每一个函数依赖的决定因素都包含键，则R属于BCNF范式
   有兴趣可以参考：范式通俗理解：1NF、2NF、3NF和BNCF

二、事务

3. 事务的隔离级别

3.1 读现象

读现象是伴生于不同的隔离级别出现的。读现象的场景都是在多个事务并发执行的前提下可能出现的：

• 脏读 —— 一个事务读取了另一个未提交事务执行过程中的数据。此时另一个事务可能会由于提交失败而回滚。
• 不可重复读 —— 一个事务执行过程中多次查询同一条数据但返回了不同查询结果。这说明在事务执行过程中，数据被其他事务修改并提交了。
• 幻读 —— 事务1先行查询了某种数据，在修改或插入提交之前，事务2对此类数据进行了插入或删除并提交，导致了事务1对预期结果的数量变化。

3.2 隔离级别

• 未提交读(read uncommited)：允许另外一个事务可以看到这个事务未提交的数据。
• 提交读(read commited)：保证一个事务提交后才能被另外一个事务读取，而不能读取未提交的数据。
• 可重复读(repeatable read)：保持读锁和写锁一直到事务提交，但不提供范围锁，因此不能避免幻读。
• 可序列化(serializable)：代价最高但最可靠的事务隔离级别，事务被处理为顺序执行。

3.3 隔离级别与读现象

不同的隔离级别可以防止读现象。

隔离级别	脏读	不可重复读	幻影读
未提交读	可能发生	可能发生	可能发生
提交读	-	可能发生	可能发生
可重复读	-	-	可能发生
可序列化	-	-	-

注：为什么提交读不能避免不可重复读？假设A事务需要读取两次变量a，第一次读取时a=10，执行过程中a被事务B修改变成了20，那么A第二次读时a与第一次的结果不同。

3.4 查看DB的隔离级别

// 查看当前会话
select @@tx_isolation;
// 查看当前系统
select @@global.tx_isolation;

MySql 5.7.14-ALISQL版默认是提交读。

4. 事务传播性(Spring)

在多个含有事务方法的相互调用时，事务如何在这些方法间传播。
本节使用Spring事务注解的枚举，而不是全称（如PROPAGATION_REQUIRED）。
这里只支持单个库的事务。跨库事务请参考分布式事务章节。

spring支持7种事务传播行为：

• REQUIRED：如果当前没有事务，就新建一个事务；否则加入到这个已有事务中，这是最常见的选择。
• SUPPORTS：支持当前事务，如果没有当前事务，就以非事务方法执行。
• MANDATORY：使用当前事务，如果没有当前事务，就抛出异常。
• REQUIRED_NEW：新建事务，如果当前存在事务，把当前事务挂起。
• NOT_SUPPORTED：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
• NEVER：以非事务方式执行操作，如果当前事务存在则抛出异常。
• NESTED：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与propagation_required类似的操作

Spring默认是REQUIRED。
为了便于理解，将以上几种传播行为分类：

传播性的类型	当前不在事务中	当前在事务中	备注
REQUIRED	新建一个事务	加入到当前事务	最常见的选择
SUPPORTS	非事务执行	加入当前事务
MANDATORY	抛异常	加入当前事务	mandatory: 强制性
REQUIRED_NEW	新建事务	挂起当前事务，新建一个事务	回滚与否和外部的事务脱离关系
NOT_SUPPORTED	非事务执行	挂起当前事务，以无事务运行	自身的异常与外部事务没有直接关系(事务传播性层面上的)
NEVER	非事务执行	抛异常
NESTED	新建事务	嵌套事务内执行	见“事务嵌套”小节

事务挂起

当前方法不再受所属的事务控制直到该方法结束。比如A方法起了一个事务，调用B方法时B挂起A的事务，那么B的所有DB操作都不再受A方法的事务控制，直到B执行结束。期间B如果先做了DB操作再抛异常，B的DB操作不会回滚，A视B的异常有没有被捕获而提交或回滚。

事务嵌套

嵌套的事务可以独立于当前事务提交或回滚，怎么理解呢？
内部事务是外部事物的子事务，进入内部事务时，记录一个savepoint。如果内部事务回滚，外部事物捕获异常后不回滚。但是外部事务回滚时，两者都不committed，一起回滚。
与REQUIRED区别：NESTED内部回滚，外部捕获异常后不会回滚；REQUIRED即使捕获内部异常，外部也要回滚。
与REQUIRED_NEW区别：REQUIRED_NEW的内外事务没有直接的关系，外部回滚内部不会回滚。

事务抛异常与回滚的关系

事务挂起和事务嵌套，在有异常发生的情况下，会变得更复杂。下面根据代码来解释。

@Transactional(propagation = XXXX)
public outerMethod(boolean willThrowException, boolean willInnerThrowException, boolean needCatch) {
      insertOrUpdateTable1Before();
      try {
        innerMethod(willInnerThrowException);
      } catch (Throwable t) {
        if(!needCatch) {
        throw new RuntimeException("innerException not Catch");
      }
      insertOrUpdateTable1Middle();
      if(willThrowException) {
        throw new RuntimeException("outerException");
      }
      insertOrUpdateTable1After();
}

@Transactional(propagation = XXXX)
public innerMethod(boolean willThrowException) {
      insertOrUpdateTable2Before()
      if(willThrowException) {
        throw new RuntimeException("innerException");
      }
      insertOrUpdateTable2After()
}

以上代码中innerMethod()和outerMethod()可以用来表示各种代码写法。两个方法的事务（如果有）是否回滚，需要判断：

1. 当前方法是否在事务中。如果在，自己的异常会导致这个事务回滚。
2. innerMethod()和outerMethod()所属的事务是否是同一个。如果是，即使innerMethod()的异常被捕获，outerMethod()也会回滚。同理outerMethod()回滚时会导致nnerMethod()回滚

异常在事务传播场景下，容易造成困惑的原因是，innerMethod()和outerMethod()两个事务的关系，被innerMethod()的异常干扰：
即使innerMethod()是REQUIRED_NEW，如果抛出异常且outerMethod()没有捕获处理，导致的结果是两个方法都被回滚，和预期的事务挂起不符，需要做额外的操作（捕获并处理）才能达到预期效果。
而且，这里的范式还是最简单的场景，即使如此分析起来也花费了大量的时间。如果有更多层的嵌套，或者事务方法A()依次调用B()、C()两个事务方法，场景会更加复杂。
这种复杂的特性也是我工作中只用REQUIRED级别的原因，让所有操作都在一个事务内，简化逻辑判断。同时，将开启事务的方法集中在应用的同一分层中，避免嵌套。

其他代码实例理解

https://blog.csdn.net/iteye_21202/article/details/82547582?utm_medium=distribute.pc_relevant.none-task-blog-OPENSEARCH-1&depth_1-utm_source=distribute.pc_relevant.none-task-blog-OPENSEARCH-1

再谈事务回滚与Spring

Spring声明式事务管理默认只对非检查型异常(unchecked)进行事务回滚，而对检查型异常则不进行回滚操作。
Java将派生于Error或者RuntimeException的异常称为unchecked异常，所有其他的异常成为checked异常。
因此在应用开发时，推荐在内部逻辑中统一抛RuntimeException或系统封装的BizException extends RuntimeException。

@Transaction注解

使用注解无法回滚的常见原因

1. 内部抛出的异常不是rollbackFor指定的需要回滚的异常，或是noRollbackFor放行的不需要回滚的异常
2. 注解只在public方法生效
3. 被注解的方法不能在同一个类内部调用，且该方法所属的类是通过Spring注入的方式创建的实例（AOP的原因，只有被外部的类调用时才创建动态代理）
4. DB引擎本身不支持事务，如mySqL的myisam。默认的innodb是支持的。

使用注入的方式也可以通过自己注入自己，实现事务式调用内部方法，但是有点复杂，不展开了，可以参考内部方法调用，事务不起作用的原因及解决办法

isolation与DB不一致时，以Spring为准

5. 分布式事务

分布式事务的内容比较多，这里不详细介绍。

CAP定理

在一个分布式系统中，当涉及读写操作时，只能保证一致性（Consistence）、可用性（Availability）、分区容错性（Partition Tolerance）三者中的两个，不可能全部达到。
CAP 定理的含义

常见的分布式事务的协议

• 2PC ： 准备阶段有超时回滚；提交阶段会阻塞，以及发生单点问题
• 3PC ： 将2PC提交阶段拆成了预提交和提交两个阶段，预提交阶段可以超时回滚
• TCC ： 业务层面的分布式事务，如发送短信等，try-confirm-cancel，实际上和2PC差不多

XA与JTA

XA是一个规范，是使用2PC的。
JTA是XA在Java上的一个实现。
个人工作中很少见到用JTA的，因为它性能并不好，而且2PC本身也有自己的固有缺陷。

三、性能与优化

6. 执行计划

确认SQL在实际执行时的执行情况，如是否走上索引、走了哪个索引、扫描行数、执行顺序（如多个select级联查询）

查看方式

explain XXX

解读

MySql: MySQL_执行计划详细说明

7. 索引相关

7.1 聚集/非聚集索引

• 聚集索引：逻辑上和物理上都是连续的，如主键，一般一个表只有一个聚集索引
• 非聚集索引：逻辑上是连续的但物理上不是

以Mysql的InnoDB为例：
主键是聚集索引。
唯一索引、普通索引、前缀索引等都是二级索引（辅助索引）。

结合B+树的知识，对于聚集索引，索引数据和存储数据是在一起的，比如id-age这个记录。
对于非聚集索引，只有索引数据，定位具体的记录需要通过索引来找，也即通过索引找到id，再通过id找到id-age这条记录。

7.2 覆盖索引

查询条件和结果全部在一个索引中，MySql不需要通过二级索引查到主键后再查一遍数据就可以返回查询数据。覆盖索引可以大大提升查询效率，举例

select a, b from table_x where c = XXX order by d;

其中a、b、c、d全部在索引中，那么这就是覆盖索引。

对于做不到覆盖索引的查询，查到主键后还要回到数据表中把数据查询出来，则称为【回表】。

7.3 索引有序性

对于联合索引，建立(a, b, c)相当于建立(a), (a,b), (a,b,c)。
在这个索引下，遵循【最左前缀原理】，即先按a排序，再按b排序，最后按c排序。
如果缺失了前一列，如where b = xxx，则走不上索引。
如果某一列不是等值匹配，如where a>10 and b = 1，则只能部分走上索引，b走不上索引。非等值匹配有<、>、!=、IN、LIKE等。
最左前缀原理不仅限于where后的条件语句，在order by语句中也有效

更完整的可以参考mysql组合索引的有序性。

7.4 索引下推

MySql5.6做的优化之一，可以在like查询中提高性能。利用查询子句中能确定的查询条件，减少一次查询匹配到的索引，从而减少回表查询的数据。
索引下推（5.6版本+）
我个人倾向于把这种情况看作特殊的【最左前缀原理】，即like后也走上联合索引的情况。

7.5 索引没生效的原因

一定走不上

1. 不等于<>、!=、NOT IN
2. where子句中包含了函数
3. 预期使用联合索引，但实际上没有遵循最左前缀原理（见上文7.3节）。注意：查询优化器，where子句排序不影响能否走上索引，如where a=\'2\' and b=\'1\'和where b=\'1\' and a=\'2\'效果是一样的

联合索引只能走部分

1.<、>会导致后面的走不上，比如联合索引a_b_c，对于查询子句where a=5 and b>10 and c=5，只能走上(a,b)

可能会失效

1. 查询语句的数据类型和DB不一致，int到varchar、varbinary走不上，varchar向int的类型能走上
2. or连接的字段，如果全部有索引则可以走上，否则走不上
3. 模糊查询like。只有like \'xxx\' 和最左的like \'xxx%\'可以走上索引
4. in的数据数目，取决于eq_range_index_dive_limit这个mysql环境变量，即大于多少时不走索引，小于这个值时走索引。默认值5.6是10，5.7是200。可以参考mysql in 索引失效？
5. 使用了表达式，where num/2 = 100走不上但是where num = 100*2可以走上。视运算在\'=\'左边右边来判断即可
6. 全表扫描比走索引快，如数据量较小的表。因为读取开销=CPU+IO
7. 使用index hint的场景 8.9.4 Index Hints

• IGNORE INDEX —— 不要用指定的索引，允许为多个
• USE INDEX —— 向优化器推荐某些索引，如果扫表更快优化器会扫表
• FORCE INDEX —— 强制用给定索引，如果用给定的索引查不到则会扫表

为什么你创建的数据库索引没有生效，索引失效的条件！

7.6 null值

可以走上索引但是不建议这么做。
测试结果：MySQL中NULL对索引的影响

7.7 filesort

如果查询内容的排序无法走上索引，且数据量大到超过内存限制，需要借助外部的文件系统排序，即filesort。但是explain里filesort不代表一定使用了磁盘空间辅助排序。
filesort也可能可以做归并排序，比如select * from xx order by b, 索引(a,b)，对于相同的a，b是有序的。
扩大缓冲区sort_buffer_size可以加快排序速度，但是更好的解决方式是加索引。
注意：即使不用order by只用group by，也可能filesort，原因是group by做了隐式的排序。解决方案是添加ORDER BY NULL。
面试官：为什么我们要尽量避免FileSort(文件排序)？

7.8 索引优化

• 使用区分度高的列

8. 锁的种类

8.1 共享锁和排他锁

select ... lock in share mode共享锁，可以并发读，但是无法加排他锁或修改
select...for update 排他锁

8.2 行锁和表锁

select...for update，走上索引（含主键）是行锁，没走上就是表锁。但是如果索引匹配过多，也会变成表锁。
特例：如果没走上索引但是没有对应的数据，也不会表锁。
[转载&整理&链接]mysql 通过测试\'for update\'，深入了解行锁、表锁、索引

MyISAM不支持行锁，而InnoDB支持行锁和表锁。

8.3 间隙锁

适用于【可重复读】的隔离级别，索引的每两个相邻值（左开右闭，包含正负无穷）之间有一个间隙锁，获取一个间隙的锁后，对这个间隙的操作是排他的。
通过for update、lock in share mode来生效，不需要显式指定。
需要调整mysql参数innodb_locks_unsafe_for_binlog来开启。
Gap Locks 不锁定索引本身
Next-Key Locks 锁定索引本身

9. 索引的B+树

https://www.cnblogs.com/tiancai/p/9024351.html
https://www.jianshu.com/p/9bd572b0a0d4
https://www.jianshu.com/p/23524cc57ca4

简单概括一下：
B树是平衡树。
B树的中间节点和叶子节点都有不止一个关键字(key)。B树出现的目的是减少磁盘臂移动的开销从而，尽量减少读写的次数。
B+树与B树的不同在于，B+树的数据都在叶子节点上，中间件节点没有数据。
应用：由于B树最左前缀匹配的特性，如果用左模糊查询(like "%xxx")是走不上索引的。

补充：

• innodb的B+树叶子存放的是主键，MyISAM则是地址
• 文件系统使用B树较多，DB则是B+树
• 相较于hash，因为DB有范围查询的需求，通过B+树的中序遍历很容易用更低的开销实现

四、应用开发

10. 分页查询

物理分页

查询第N页(下标从1开始)数据，每页大小PageSize

// 先获取符合条件的总数
select count(1) from tableA where XXX
// 查询该页
// 偏移量,可选 offset = (pageSize-1) * N
// 行数 rows = pageSize
select row1, ..., rowN from tableA where XXX limit offset, rows

mybatis

可以用RowBounds也可以自己实现Mybatis的分页插件。
RowBounds是逻辑分页，即查出全部数据再舍弃不需要的，因此数据量大时会有性能问题。

11. Join

11.1 语法

SELECT Table1.Row1, Table1.Row2, Table2.Row1
FROM Table1
INNER JOIN Table2
ON Table1.Row2 = Table2.Row2
ORDER BY Table1.Row1

11.2 种类

inner join( = join)，都匹配才返回
left join，左表全返回不管右表有没有匹配
right join，右表全返回不管左表有没有匹配
full join，全返回，左表右表无论对方匹配都返回所有行

12. MyBatis相关

12.1 SqlSessionFactory和SqlSession

• 首先，通过包含DB连接等信息的mybatis-config.xml和对应映射文件,通过SqlSessionFactoryBuilder创建SqlSessionFactory对象。它代表DB的资源池，应该是单例的bean。
• 需要数据库操作时，通过SqlSessionFactory.open()创建SqlSession对象，代表数据库连接，调用Mapper通过JDK动态代理对应的DAO执行具体的数据库操作。SqlSession不是线程安全的。
• SqlSessionTemplate是SqlSession的一个实现
• SqlSession的生命周期：如果使用DAO来操作，有事务则为一个事务，否则为一次执行。否则需要显示的打开和关闭

12.2 缓存

• 一级缓存，SqlSession级，默认开启
• 二级缓存，SqlSessionFactory级，默认不开启

和通常命名习惯相反，二级缓存的作用范围大于一级缓存。

一级缓存生效的session没有做任何update操作且查询完全相同时，会返回一样的数据。
此时，在并发环境下，很有可能会发生这种情况：在一台服务器A上连续查询两次，两次属于同一个SqlSession；中间另一个服务器B对表做了更新，A看到的第二次查询结果仍然是旧的。

关于缓存的细节，如如何判断“同一次查询”、缓存有效期、SqlSession原理，可以自行查阅。推荐mybatis中文官网，有很多原理的介绍。
在实践中，spring和mybatis整合以后每次查询都会刷新sqlSession，即一级缓存是无效的。
MyBatis缓存系列
单独提一下，二级缓存的readOnly默认为false，同一条数据在内存中每个对象都是独立的，可修改相互不影响。可参考如何理解Mybatis二级缓存配置中的readOnly？

12.3 mybatis和hibernate

我在工作中绝大多数时间都用mybatis+spring/springboot写持久层，只有一个应用因为使用SpringDataJPA才对hibernate才做了一些了解。
看了一些资料，了解到二者在写法以外，性能的差别主要在于多表查询这个场景，hibernate会比mybatis慢一些，原因是

hibernate为了保证POJO的数据完整性，需要将关联的数据加载，需要额外地查询更多的数据。

[MyBatis和Hibernate相比，优势在哪里？ - 郑沐兴的回答 - 知乎](MyBatis和Hibernate相比，优势在哪里？ - 郑沐兴的回答 - 知乎
https://www.zhihu.com/question/21104468/answer/92986556)
此外，JPA如果想运行原生sql，可以使用EntityManager。
JPA做update时也会根据字段有没有变化来决定更新的字段，不是全部更新

12.4 延迟加载

在关联查询时，用到某些属性时再查询的特性。如select A.a, B.b from A join B，当代码中读取b属性时才会去加载。
默认是关闭的。原理是通过cglib拦截getB()方法，发现其为空时再用保存好的sql进行查询并填充。
具体怎么用就不展开了，请自行查询，如你真的懂了mybatis延迟加载吗？

12.5 面试相关问题

什么是接口绑定，接口绑定的方式有哪些？

把接口的方法和SQL绑定，比较灵活。
有两种方式：

• xml绑定sqlmap文件中id和接口方法一致
• 注解绑定，在接口方法上用@Select、@Update、@Insert、@Delete等注解，并写上对应的sql。适用于sql简单的情况。

如何实现一对一查询？

join或嵌套。

resultType和resultMap的区别

类名和数据库名相同时，可以直接设置resultType参数为Pojo类，否则使用resultMap。

13. 水平扩展与垂直扩展

13.1 水平扩展——分库分表一般思路

• 按某一字段将一张表分片，如userId。分片方式：
  • 第X位到Y位的值
  • 字段hash值
  • 特殊值特殊处理，如某KA(Key Account关键客户)数据量较大，单独一个分表

13.2 水平扩展——历史库

按日期定时同步迁移及清理线上数据
查询需要根据日期路由到线上库或历史库

13.3 水平扩展——按业务拆表

按业务，已处理数据及未处理数据拆分。如已受理未申请单和已完结申请单分开保存。

13.4 垂直扩展

提供更多、更强、容量更大的硬件资源。

13.5 FailOver

在计算机术语中，故障转移（英语：failover），即当活动的服务或应用意外终止时，快速启用冗余或备用的服务器、系统、硬件或者网络接替它们工作。 故障转移(failover)与交换转移操作基本相同，只是故障转移通常是自动完成的，没有警告提醒手动完成，而交换转移需要手动进行。 ——wiki

FailOver是从应用层面做的，不是单纯DB层面。

13.5.1 背景

单库架构，一旦库挂掉整个服务不可用；
主备架构，切换时有时间延迟；
FailOver从分布上来看仍然是主备架构，但是增加了系统自动切换恢复能力。

13.5.2 思想

和去IOE是一致的，用大量相对廉价的硬件，拆分服务，减少单点，提升整体的可用性。

13.5.3 交互模式

仅举两个最典型的例子，具体场景需要结合硬件能力和应用架构综合分析。

13.5.3.1 记账型

特点：

• 主备准实时同步，Failover库平时不做读写
• 主备库表结构一致，Failover库不一定和主备库的表一致（可能会少一些不需要用到的表）
• 账户型数据保持最终一致性即可

方案：

• 按比列拆表拆库，降低单个库挂掉时影响用户数
• 正常工作时，主备准实时同步，Failover库不读写
• 主库发生异常时，切换到备库读，Failover库记录操作信息。同时，业务操作尽量分流到不依赖相关库到支路上。
• 主库恢复时，不再写入Failover，将Failover库和主库内容做merge，回写主库，主库再同步备库

注：可以采取双写、基于读库（上文中所述，利用oracle的data guard、mysql的replication等）、异步消息等保证主备一致。

13.5.3.2 交易流水型

特点：

• 数据保证创建，不保证推进。即交易下单失败，重新下单
• failover库交易号与主库通过某些位隔离，不重复

方案：

• 和“记账型”类似，Failover库数据推进业务完成即可
• 可以不回写failover期间的数据，依赖中间件读failover库中数据

13.6 读写分离

为了解决读大于多于写的场景下数据库瓶颈的一种架构模式。同样需要结合具体业务不能生搬硬套。
主要是一写多读的架构，在主库挂掉的场景下有可能需要考虑使用【paxos算法】来决定新的主库。
在做读写分离前，可以先考虑缓存是否能解决当前场景的问题。

读写分离和CQRS

CQRS是读写分离的一种形式，指的是 增删改命令 与 查询 的分离。
命令执行后不是实时更新到读库，而是异步化的，读的内容有一定的延迟。由于需要依赖事件机制，建议只在复杂查询场景使用。
可以参考数据库（七），读写分离到CQRS

五、运维

14. binlog和redo/undo log

14.1 binlog

记录DB结构变更和数据操作（不含查询；update数为0时不记录——5.7版本）及其他执行信息的二进制日志。
注意：binlog是由DB的Server产生的，不限于innoDB

分为三种模式，由mysql来判断它自己使用那种方式：

• 行数据变更
• 变更的sql
• 混合模式

有三种用途：恢复、复制（集群之间）、审计（是否被攻击）

可以参考下面两篇文章简单了解下。
【原创】研发应该懂的binlog知识(上)
【原创】研发应该懂的binlog知识(下)

14.2 redo log和undo log

是由InnoDB提供的、在存储引擎执行的。

• undo log 记录数据被修改之前的日志，事务回滚时用于还原
• redo log 用于回放事务committed时的操作，用于异常时重新提交事务。如果异常恢复时事务是abort的则不重新提交。

14.3 redo log/undo log和binlog的比较

	redo log/undo log	binlog
执行位置	存储引擎，更底层	server层
关联的数据引擎	InnoDB	不限
写入方式	是循环写，日志空间大小固定	是追加写，自动拆分文件

binlog和redo/undo log记录的内容有重合的部分。
MySQL redo log 与 binlog 的区别

六、其他话题

16. 零碎的话题

想起来就补一些。

16.1 列的默认值

对于有默认值的非空列，如果在insert语句中指明了这一列且值为null，插入仍然会报错，此时不会取默认值。让该列取默认值的方式是，不让该列出现在insert语句中。

16.2 Paxos算法

摘录自Paxos算法原理和过程解析

面试精简答案：
Paxos算法解决的是一个分布式系统如何就某个值（决议）达成一致。一个典型的场景是，在一个分布式数据库系统中，如果各个节点的初始状态一致，每个节点执行相同的操作序列，那么他们最后能够得到一个一致的状态。为了保证每个节点执行相同的命令序列，需要在每一条指令上执行一个“一致性算法”以保证每个节点看到的指令一致。zookeeper使用的zab算法是该算法的一个实现。在Paxos算法中，有三种角色：Proposer (提议者),Acceptor（接受者），Learners（记录员）
Proposer提议者：只要Proposer发的提案Propose被半数以上的Acceptor接受，Proposer就认为该提案例的value被选定了。
Acceptor接受者：只要Acceptor接受了某个提案，Acceptor就认为该提案例的value被选定了
Learner记录员：Acceptor告诉Learner哪个value就是提议者的提案被选定，Learner就认为哪个value被选定。
Paxos算法分为两个阶段，具体如下：
阶段一 （准leader 确定 ）：
(a) Proposer 选择一个提案编号 N，然后向半数以上的Acceptor 发送编号为 N 的 Prepare 请求。
(b) 如果一个 Acceptor 收到一个编号为 N 的 Prepare 请求，且 N 大于该 Acceptor 已经响应过的所有 Prepare 请求的编号，那么它就会将它已经接受过的编号最大的提案（如果有的话）作为响 应反馈给 Proposer，同时该Acceptor 承诺不再接受任何编号小于 N 的提案。
阶段二 （leader 确认）：
(a) 如果 Proposer 收到半数以上 Acceptor 对其发出的编号为 N 的 Prepare 请求的响应，那么它就会发送一个针对[N,V]提案的 Accept 请求给半数以上的 Acceptor。注意：V 就是收到的响应中编号最大的提案的 value ，如果响应中不包含任何提案，那么V 就由 Proposer 自己决定。
(b) 如果 Acceptor 收到一个针对编号为 N 的提案的 Accept 请求，只要该 Acceptor 没有对编号
大于 N 的 Prepare 请求做出过响应，它就接受该提案。

16.3 Raft算法

从Paxos衍生出的，主要用于选举节点，分为Leader、Candidate、Follower三种角色。
新增了随机的超时时间。
简单根据情况来分析一下，可以参考共识算法：Raft

• 只有一个Follower节点到达超时时间发起选举并成为Candidate，且其他节点Follower都投给它，变成Leader
• 原Leader掉线
  • 有一个节点首先超时发起选举并成为Candidate
  • 成为Leader后，原Leader上线，发现自己选举版本低于新Leader，自动变成Follower
• 同时有两个Follower到达超时时间发起选举并成为Candidate
  • 其他Follower分别对Candidate投票，在之前未收到请求则支持本次拉票，否则反对。此时没有获得所有选票，两个Candidate保持这个身份，重新计算延时
  • Candidate首先超时的先发起选举，获得选票后成为Leader
  • 另一个Candidate发起投票都被拒绝，变回Follower

17. MVVC

虽然这节排的比较靠后，但是并不是不重要。MVVC综合了事务、undo log等知识，更需要花时间来理解。

17.1 定义

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

17.2 简介

在使用了MVCC后，DB才支持了写-写阻塞。在此之前，只有读-读可以并行，读-写、写-写都是阻塞的。
MVCC在 Read Committed 和 Repeatable Read两个隔离级别下工作。
MySQL的InnoDB存储引擎的Repeatable Read是通过MVCC和行级锁实现的，正常时读不加锁，写加锁。

17.3 原理

MVVC依赖三个数据结构：

• 隐藏字段（innoDB实现），标记最近修改该行的事务id、回滚指针指向undo log
• Read View，记录对本事务不可见的其他活跃事务
• undo log，当一个事务需要读取记录行时，如果当前记录行不可见，可以顺着隐藏字段的undo log链找到满足其可见性条件的记录行版本

具体的原理和实例，请参考MySQL中MVCC的正确打开方式（源码佐证）

17.4 快照读和当前读

快照读(snapshot read)：普通的 select 语句(不包括 select ... lock in share mode, select ... for update)
当前读(current read) ：select ... lock in share mode，select ... for update，insert，update，delete 语句

17.5 提交读和可重复读的Read View在innoDB的MVCC下的区别

Repeatable Read下事务第一次select时创建read view
Read Commit下事务每次次select时创建read view

延伸话题

可以自行研究的话题，限于笔者接触范围和篇幅，不展开来写。

• 索引建立实践，是否越多越好，应该怎么选择索引列
• 以下内容可能被滥用，我在实际工作中几乎没有用到，有兴趣可以自行了解。
  • 触发器
  • union
  • 视图（mysql对性能有影响）
• MyISAM与InnoDB 的区别（9个不同点）

附：“点评“ 《阿里巴巴JAVA开发手册》之MySql规范部分

开发中遵守一些事先约定好的规范，有助于提升研发效率（无论是个人还是团队内部或团队之间），避免犯一些重复错误，也有助于后续的维护。对于《阿里巴巴JAVA开发手册》中的规范，原版没有写明原因，本来想MySql规范部分这一部分补一下点评的，但是发现前两天新出的泰山版已经补上很多说明，没必要一一点评，直接下载来看就好：https://files.cnblogs.com/files/wuyuegb2312/《Java开发手册（泰山版）》.pdf.zip

可以看出，前面一部分有很多规范都是和Java OOP相关联的。对于另外的部分条目，是之前没注意到的，单独拉出来点评下。

count(*)和count(1)

【强制】不要使用 count(列名)或 count(常量)来替代 count()，count()是 SQL92 定义的标
准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

官方文档提到，InnoDB下count(*)和count(1)是没有区别的：

InnoDB handles SELECT COUNT() and SELECT COUNT(1) operations in the same way. There is no performance difference.
但考虑到其他实现对count()有优化（如MyISAM，前提是没有WHERE和GROUP BY子句，直接取缓存的总数），再考虑到用其他DB的情况，统一起见一直用count(*)就好了。
更详细的分析可以看 为什么阿里巴巴禁止使用 count(列名)或 count(常量)来替代 count(*)

禁用外键

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

禁止使用外键，在本例中并不是不允许在成绩表中存放student_id字段，只是不设置成为外键即可，更新由应用层来做。

参考文章

可能是全网最好的MySQL重要知识点/面试题总结

TODO

流式查询方案：https://my.oschina.net/u/4301644/blog/4461008