本文主要是对目前工作中使用到的DB相关知识点的总结，应用开发了解到以下深度基本足以应对日常需求，再深入下去更偏向于DB本身的理论、调优和运维实践。
不在本文重点关注讨论的内容（可能会提到一些）：
- 具体的DQL、DML、DDL、DCL等语法
- 基础性的概念，如主键、索引、存储过程（注：阿里巴巴规范中禁止使用存储过程）等
- 联合查询，我个人不太喜欢在应用中写过于复杂的SQL，性能和后续维护容易出现问题
- 可能会用到的具体DB特性，如oracle的DATA GUARD
有一些属于基础知识或语法但是常用的信息，也会列一下，如join的用法。
一、基础
1. ACID
DB的四大特性，这里简单概括下不具体展开。
- 原子性（Atomicity）：事务操作中的多条SQL，要么全部成功要么全部失败，失败后回滚不对原有数据造成任何影响。
- 一致性（Consistency）:事务开始前和结束后，数据库的完整性没有被破坏。如触发器、约束、级联回滚
- 隔离性（Isolation）：多个事务支持并发读写。具体隔离级别见后文。
- 持久性（Durability）：事务结束后，修改是永久的，不丢失。
2. 范式
这里展开讲比较复杂，实践中很少用到，一般满足1NF即可。
高一级必满足低一级。
- 1NF：每个属性都不可再分，即表的列是最原子的
- 2NF：在1NF基础上，消除非主属性对键的部分依赖。这里不解释非主属性和键的含义，可以简单认为是指不存在列A可以通过列B来获取，如“学生姓名-学号”这种y=f(x)的函数关系。
- 3NF：在2NF的基础之上，消除了非主属性对于码的传递函数依赖
- 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()可以用来表示各种代码写法。两个方法的事务（如果有）是否回滚，需要判断：
- 当前方法是否在事务中。如果在，自己的异常会导致这个事务回滚。
- innerMethod()和outerMethod()所属的事务是否是同一个。如果是，即使innerMethod()的异常被捕获，outerMethod()也会回滚。同理outerMethod()回滚时会导致nnerMethod()回滚
异常在事务传播场景下，容易造成困惑的原因是，innerMethod()和outerMethod()两个事务的关系，被innerMethod()的异常干扰：
即使innerMethod()是REQUIRED_NEW，如果抛出异常且outerMethod()没有捕获处理，导致的结果是两个方法都被回滚，和预期的事务挂起不符，需要做额外的操作（捕获并处理）才能达到预期效果。
而且，这里的范式还是最简单的场景，即使如此分析起来也花费了大量的时间。如果有更多层的嵌套，或者事务方法A()依次调用B()、C()两个事务方法，场景会更加复杂。
这种复杂的特性也是我工作中只用REQUIRED级别的原因，让所有操作都在一个事务内，简化逻辑判断。同时，将开启事务的方法集中在应用的同一分层中，避免嵌套。
其他代码实例理解
再谈事务回滚与Spring
Spring声明式事务管理默认只对非检查型异常进行事务回滚，而对检查型异常则不进行回滚操作。
非检查型异常即运行时异常和它的继承类，检查型异常则为除了非检查型异常以外其他所有的Exception及继承类。
这样就很容易理解为什么平时应用开发时，推荐在内部逻辑中统一抛RuntimeException或系统封装的BizException extends RuntimeException了。
https://www.iteye.com/blog/tommy-lu-2401180
三、性能与优化
5. 执行计划
确认SQL在实际执行时的执行情况，如是否走上索引、走了哪个索引、扫描行数、执行顺序（如多个select级联查询）
查看方式
explain XXX
解读
MySql: MySQL_执行计划详细说明
6. 索引相关
6.1 聚集/非聚集索引
- 聚集索引：逻辑上和物理上都是连续的，如主键，一般一个表只有一个聚集索引
- 非聚集索引：逻辑上是连续的但物理上不是
以Mysql的InnoDB为例：
主键是聚集索引。
唯一索引、普通索引、前缀索引等都是二级索引（辅助索引）。
结合B+树的知识，对于聚集索引，索引数据和存储数据是在一起的，比如id-age这个记录。
对于非聚集索引，只有索引数据，定位具体的记录需要通过索引来找，也即通过索引找到id，再通过id找到id-age这条记录。
6.2 覆盖索引
查询条件和结果全部在一个索引中，MySql不需要通过二级索引查到主键后再查一遍数据就可以返回查询数据。覆盖索引可以大大提升查询效率，举例
select a, b from table_x where c = XXX order by d;
其中a、b、c、d全部在索引中，那么这就是覆盖索引。
对于做不到覆盖索引的查询，查到主键后还要回到数据表中把数据查询出来，则称为__回表__。
6.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等。
更完整的可以参考mysql组合索引的有序性
6.4 创建了索引但没有走上的原因
- 使用了<、>、!=、IN、LIKE等（非最左的like，也即like 'xxx%'是可以的）
- 使用or连接查询子句
- 预期使用联合索引，但实际上没有按照最左前缀原理排序（见上文7.3节）
- 字符串类型没有使用引号
- 全表扫描比走索引快
- where子句中包含了函数或表达式
为什么你创建的数据库索引没有生效，索引失效的条件！
7. 行锁和表锁
select...for update，走上索引（含主键）是行锁，没走上就是表锁。但是如果索引匹配过多，也会变成表锁。
[转载&整理&链接]mysql 通过测试'for update'，深入了解行锁、表锁、索引
8. 索引的B+树
https://www.cnblogs.com/tiancai/p/9024351.html
https://www.jianshu.com/p/9bd572b0a0d4
https://www.jianshu.com/p/23524cc57ca4
简单概括一下：
B树的中间节点和叶子节点都有不止一个关键字(key)。B树出现的目的是减少磁盘臂移动的开销从而，尽量减少读写的次数。
B+树与B树的不同在于，B+树的数据都在叶子节点上，中间件节点没有数据。
应用：由于B树最左前缀匹配的特性，如果用左模糊查询(like "%xxx")是走不上索引的。
四、应用开发
9. 分页查询
查询第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
10. Join
10.1 语法
SELECT Table1.Row1, Table1.Row2, Table2.Row1
FROM Table1
INNER JOIN Table2
ON Table1.Row2 = Table2.Row2
ORDER BY Table1.Row1
10.2 种类
inner join( = join)，都匹配才返回
left join，左表全返回不管右表有没有匹配
right join，右表全返回不管左表有没有匹配
full join，全返回，左表右表无论对方匹配都返回所有行
11. MyBatis缓存
MyBatis缓存分为两级：一级缓存，SqlSession级别；二级缓存，SqlSessionFactory级别。和通常命名习惯相反，二级缓存的作用范围大于一级缓存，原因是，SqlSession是由SqlSessionFactory创建的。
MyBatis默认开启一级缓存，不开启二级缓存。一级缓存生效于同一个SqlSession，当这个session没有做任何update操作且查询完全相同时，会返回一样的数据。
此时，在并发环境下，很有可能会发生这种情况：在一台服务器A上连续查询两次，两次属于同一个SqlSession；中间另一个服务器B对表做了更新，A看到的第二次查询结果仍然是旧的。
关于缓存的细节，如如何判断“同一次查询”、缓存有效期、SqlSession原理，可以自行查阅。推荐mybatis中文官网，有很多原理的介绍。
在实践中，spring和mybatis整合以后每次查询都会刷新sqlSession，即一级缓存是无效的。
MyBatis缓存系列
单独提一下，二级缓存的readOnly默认为false，同一条数据在内存中每个对象都是独立的，可修改相互不影响。可参考如何理解Mybatis二级缓存配置中的readOnly？
12. mybatis和hibernate
我在工作中绝大多数时间都用mybatis+spring/springboot写持久层，只有一个应用因为使用SpringDataJPA才对hibernate才做了一些了解。
看了一些资料，了解到二者在写法以外，性能的差别主要在于多表查询这个场景，hibernate会比mybatis慢一些，原因是
hibernate为了保证POJO的数据完整性，需要将关联的数据加载，需要额外地查询更多的数据。
MyBatis和Hibernate相比，优势在哪里？ - 郑沐兴的回答 - 知乎
此外，JPA如果想运行原生sql，可以使用EntityManager。
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算法来决定新的主库。
在做读写分离前，可以先考虑缓存是否能解决当前场景的问题。
五、运维
14. binlog
记录DB操作（不含查询）及其他执行信息的二进制日志。
可以参考下面两篇文章简单了解下。
【原创】研发应该懂的binlog知识(上)
【原创】研发应该懂的binlog知识(下)
六、其他话题
15. 零碎的话题
想起来就补一些。
15.1 列的默认值
对于有默认值的非空列，如果在insert语句中指明了这一列且值为null，插入仍然会报错，此时不会取默认值。让该列取默认值的方式是，不让该列出现在insert语句中。
15.2 索引下推
MySql5.6做的优化之一，可以在like查询中提高性能。利用查询子句中能确定的查询条件，减少一次查询匹配到的索引，从而减少回表查询的数据。
16. 延伸话题
可以自行研究的话题，限于笔者接触范围和篇幅，不展开来写。
- 索引建立实践，是否越多越好，应该怎么选择索引列
- hibernate和mybaits的区别，最大区别是mybatis需要手写sql，用一定的工作量更大的灵活性，利于优化和多表联合查询
- redo log、undo log，与DB本身的分离
- 以下内容可能被滥用，我在实际工作中几乎没有用到，有兴趣可以自行了解。
- 触发器
- union
- 视图
- 全表扫描时发生的filesort原理
附：“点评“ 《阿里巴巴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字段，只是不设置成为外键即可，更新由应用层来做。