数据库三大范式：

1NF：每个字段都只包含一条信息，而非（年龄，性别）

2NF：每个字段都要和主键有关联（直接关联或间接关联）

3NF：每一个字段都要和主键直接关联（无法直接关联的需要另外建表），一般2NF就够用。

• 主键索引的 B+Tree 的叶子节点存放完整数据。
• 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据，需要回表。

B-Tree:

例如5阶的B树:

最大指针为5,最大Key为4 (n-1) (Key即索引),Key指向数据(或其指针).

每当有新的索引试图插入B-Tree,就会与当前节点现存的索引作大小比较:

然后找到合适的区间,区间有一个指针指向子节点,最后插入被指向的节点(重复上述过程),最后新索引插入在叶子节点

这样就完成了索引的B-Tree结构.

当叶子节点的Key(索引)数量到达上限时,新索引插入之后,中间索引向上分裂,原节点从中间分家,成为两个新的节点

以此类推……….

B+Tree

例如:3阶的B+树

底层原理与B树相同,最大的区别是:

当节点存储的索引超过上限分家时,将中间的索引留在当前节点中,再复制一份中间索引向上分裂出去,并且形成一个单向的链表指向下一个叶子节点.

所有数据都在叶子节点中，搜索效率稳定。非叶子不存储数据，仅存键值对和指针，用于指路。

叶子节点存放所有数据，且由双向链表链接，可以范围查询和倒序操作。

B+树相比B树的优点：

• B+树非叶子节点不存数据，能比B树多存放更多的索引，更加矮胖。且查询效率稳定，不会在节点中乱找，IO次数更少。
• 存放数据的叶子节点由链表链接，可以轻松实现范围查询，而非遍历整个树。

Hash索引：

仅适用于等值查询，通过将查询信息进行Hash计算可以快速的找到数据所在的桶，效率接近O（1）。

联合索引:

需要满足最左前缀原则:建立联合索引的二级索引树,是依照最左侧元素而树化的

对(A,B,C)建立联合索引: where A=4 and B>5 and C=3 :

A和B会走联合索引,而C走不了联合索引,但会进行索引下推;

(联合索引的范围查询会终止索引扫描; 索引下推:InnoDB在二级索引树层面就先过滤掉不符合的c,减少回表次数[覆盖索引除外,因为根本不需要回表])

tips:因为二级索引树是按照A的顺序建立的,所以只有A的分页在内存中是连续的,其他元素不连续,所以范围查询会终止索引扫描

而where A=4 and C=3:

A会走联合索引,因为跳过了B所以C走不了索引,但是依旧可以索引下推.

索引失效:

使用like模糊匹配；为遵守最左前缀原则；使用表达式计算

使用函数：包括隐式转换，例如数字和字符串的比较

使用or时有任何条件没有建立索引：因为and是求交集，而or是求并集，如果有参数没有索引就会走全表 扫描

tips：如果是操作类的SQL如update走全表扫描会导致出现表级锁。

索引优化：

前缀索引优化：将长索引字段截取部分内容建立索引，这样可以防止因为索引长度过长导致二级索引树体积巨大，并且可以让每个节点多存放一些索引，降低树高。但必须回表，防止冰山一角。

覆盖索引优化：不回表。

使用规律递增主键：最好使用自增的，次责使用时间戳相关的雪花算法ID

• 如果不适用自增ID，插入数据的时候需要移动叶子节点，甚至导致页分裂同时浪费内存空间

事务：

四大特性：

• 原子性：事务中的所有操作要么都完成，要么都回滚【例如库存扣减和订单生成】
  • InnoDB通过undo Log（回滚日志）保证原子性
• 一致性：事务前后的数据库操作数据必须一致，例如银行转账等强一致性场景
  • InnoDB通过其他三个特性来共同保证一致性
• 隔离性：事务的执行是相互隔离的，并发事务下不允许修改其他事务数据
  • InnoDB通过MVCC并发控制保证隔离性
• 持久性：事务结束后，其修改结果必须是永久性的
  • InnoDB通过redo Log（重做日志）保证持久性

事务的隔离级别：

• 读未提交：事务可以读取到其他事务尚未提交的数据
• 读已提交：事务只可以读取到其他事务已提交的数据
• 可重复读：事务读取的时候创建一个快照，后续的读取都基于此快照（是MySQL默认的隔离级别）
  • 可能导致丢失更新：两个事务同时快照读余额为100的账号，并且都往这个账户+100元，会导致丢失一次100元的更新；解决方法是读操作强制当前读
• 串行化：所有事务加锁，是重级锁，几乎不用

各种读问题：

脏读：事务A修改数据后还未提交，事务B读取了修改后的数据，事务A却回滚了，导致脏读。

不可重复读：事务A读取某一条数据，然后事务B修改了数据，导致事务A再次读取相同数据时发现数据不一致。

幻读：事务A读取name=来财的数据有6条，然后事务B插入一条新的来财数据，事务A再次读取name=来财变成了7条（结果集），导致幻读

• 在InnoDB的优化下，已经可以尽量避免幻读了，但是还是会发生在非常特殊的场景下：
  • 事务A读取全部ID，只看到了ID1-4，此时事务B插入一条ID=5的新数据
  • 事务A发癫起来突然update ID =5，再次读取全部ID发现出现了ID=5的数据，形成幻读
    • tips：不可重复读级别下，所有只读操作全程只看一个快照，但如果进行update等强制当前读操作，会满足MVCC的允许快照读条件：trx_id=cretor_trx_id，所以事务A能够看见新修改的ID=5的数据

!MVCC原理!:控制事务是否允许快照读的多版本并发控制。有快照读的存在就能够保证读操作不相互阻塞

在可重复读级别下：事务只会在首次select的时候读一个快照，且全程只基于这一个快照操作（除规则1）。这就可以防止事务两次select出不同的数据导致不可重复读。而在读已提交级别下：每次select都会读新的快照

快照的四个重要字段：

• 创建快照的事务ID (cretor_trx_id)
• 活跃(尚未提交)的事务ID池 (m_trx_id)
• 最小活跃事务ID (m_min_trx_id)
• 最大活跃事务ID (m_max_trx_id)
  • 每条数据行有两个隐藏字段:
    • 最近提交/修改的事务ID (trx_id)
    • 回滚指针 (roll_pointer)

MVCC允许事务快照读的四条规则:

• I. trx_id = cretor_trx_id :最近事务ID = 创建快照的事务ID [代表修改的事务就是自己本身,当然允许读快照]
• II. trx_id >= m_max_trx_id:最近事务ID >=未来下一个事务ID [ 情况几乎不可能,从前不可能大于未来]
• III. trx_id < m_min_trx_id: 最近事务ID < 最小的活跃的事务ID [代表最近的事务ID不再活跃,已经提交,允许快照读]
• IV. m_min_trx_id<= trx_id < m_max_trx_id : 若trx_id不在活跃池中,则允许快照读,否则不允许

锁:

• 全局锁:将整个数据库设为只读状态,不允许修改。主要用于数据库备份
• 表级锁:
  • 表锁：lock tables语句可以对表加锁，限制所有线程读写（与全表扫描的表锁不同）
  • 元数据锁：防止其他线程修改表结构，当CURD时上的是元数据读锁，修改表结构的时候上的是元数据写锁。
  • 意向锁：当事务获取行锁时，会给表上意向锁。其他事务想上表级锁的时候就可以直接查看是否被上意向锁了，而不需要逐行查看是否有事务在活跃
• 行级锁:
  • 记录锁：对当前数据行上锁
  • 间隙锁：对两个数据行中的空隙整个上锁
    • 只存在可重复读级别，目的是解决幻读现象。
  • 临建锁：相当于记录锁+间隙锁，也是可重复读级别下的默认行级锁

日志：

日志类别：

• Redo Log（重做日志）：用于意外情况的故障恢复
  • 同时，在修改操作时，会在buffer pool中更新，然后在redoLog中记录修改内容，在合适的时候刷盘。
  • 追加写模式（顺序写，而Buffer Pool写入数据是随机写），提高写性能。
  • 提交事务时，将redoLog持久化到硬盘保证持久化。可通过redoLog进行崩溃恢复。
    • 记录事务更新后的数据
• Undo Log（回滚日志）：主要用于事务回滚和MVCC版本链控制
  • 在事务开始之前，将旧数据记录在undo Log日志中以便回滚。
    • 记录事务开始前的数据
• Bin Log（二进制日志）：主要用于数据备份和主从复制
  • 执行修改操作后会生成一条binLog，当事务提交后统一写入binLog文件中
    • binLog是追加写，且不会覆盖以前的binLog
• 慢查询日志：查询慢SQL

事务二阶段提交：

阶段一：Prepare阶段

1. 当事务执行修改操作时，会将具体操作写入Redo Log Buffer中

2. 事务提交后将RedoLogBuffer刷盘到RedoLog，此时Redo Log将标记为prepare阶段，

阶段二：Commit阶段

3. 同一事务执行修改操作时也会将具体操作写入Bin Log Cache中

4. 事务提交后，将Bin LogCache刷盘到BinLog，成功后，将RedoLog的状态修改为commit。

执行数据库恢复时：

• 若Redo Log的状态为commit，代表事务提交已彻B底完成，可以直接通过Redo Log日志进行恢复
• 若Redo Log的状态为Parper，则去查看Bin Log是否完整，
  • 如果Bin Log完整就代表已完成刷盘，只是还没来得及修改RedoLog的状态，可以恢复数据。
  • 如果Bin Log不完整代表事务提交过程失败，根据Undo Log回滚数据

如果没有二阶段提交的话：

数据库写入Redo Log日志而无Bin Log日志的话，会导致恢复数据后从库无法同步更新

反之会导致从库能够恢复数据，而主库却丢失数据。

MySQL是如何保障数据不丢失：

通过RedoLog来实现持久性的。事务在具体操作时，将具体的操作记录在RedoLogBuffer中，在事务提交后刷到RedoLog文件中。即使BufferPool中的脏页刷盘失败，也能通过RedoLog重放以恢复数据。

两次写DoubleWrite：

InnoDB引擎将脏页刷盘是将脏页分成一块一块刷入的，如果中途宕机导致分页结构损坏，RedoLog就无法重放，所以需要两次写DoubleWrite。

• 第一次写：InnoDB将BufferPool中的脏页刷盘前，会先将数据顺序写入到doubleWriteBuffer。
  • 当doubleWriteBuffer空间写满时（默认2MB），InnoDB会将doubleWriteBuffer顺序写入DoubleWrite中以备份。
• 第二次写：当数据安全写入doubleWrite中，才会将脏页刷盘进磁盘中（写入磁盘是随机IO）

MySQL性能调优：

explain命令重点参数：

• type：使用的是数据扫描类型
  • ALL：全表扫描 【最差的情况】
    • 无索引、或者走索引性能差
  • index：全索引扫描 【性能和ALL差不多，走的是全二级索引树扫描】
    • 常见于无where条件的覆盖索引。即查询字段为覆盖索引，但未限制where条件
    • 比起走庞大的聚簇索引树，走更小的二级索引树性能更好
      • 但如果还需要回表，那还不如直接走全表扫描
  • range：索引范围扫描【常见于范围查询】
    • 即查询返回多条数据的情况
  • ref：非唯一索引扫描
    • 即索引建在了非唯一约束字段
  • const：唯一索引扫描
    • 性能最高，精准的只查一条数据，创建于主键查询或唯一索引查询
• key：走的什么索引

慢SQL优化方案:

• 通过命令来查询表是查询SQL多还是更新SQL多，查询多的就合理的新建和优化索引，更新多的就要更精准的建立合理的索引，防止二级索引树频繁变动
• 用explain命令分析SQL执行计划,通过type字段分析慢查询原因。是否有全表扫描的情况发生
• 创建或者优化索引，避免索引失效的情况。
• 尽量通过冗余字段来避免联表查询，如果非联不可尽量使用小表驱动大表。
• 如果单表数据量过大，通过合理精确的分析考虑拆分成数个小表
• 对合理的数据采用Redis进行缓存

架构：

数据库主从复制：

从库通过主库磁盘中的binLog日志进行数据的传输。

过程一般是异步的，分为三个阶段：

• 写入BinLog：主库顺序写入BinLog日志并刷盘
• 同步BinLog：把主库的BinLog写入从库的暂存日志中
• 回放BinLog：更新从库中的数据库数据

最后当完成主从同步后，推荐写操作只写主库，读操作只读从库。这样即使写操作上了行级锁甚至是表级锁，也不影响读操作。

分库分表：

• 分库：
  • 垂直分库：一种专库专用的数据库的技术，数据会根据一定规则各自存储在不同的数据库中。每个数据库职责划分清楚，缓解数据库压力。
  • 水平分库：把同一张表按照一定规则拆分到不同数据库中。是一种解决单库存储量和性能瓶颈的方法，但是会提升业务复杂度。
• 分表：
  • 垂直分表：将多字段的表中相对独立和不常用的字段（例如TEXT大字段）拆分到一个小表中，主表只保留核心字段。
  • 水平分表： 将数据库中的一张表拆分成多张表，每个表存储一部分数据。主要书为了缓解单个数据表过大的问题，但也对多表操作和数据同步带来困难。