面试准备系列之数据库

数据抽象

• 物理层
• 逻辑层
• 视图层

数据模型

• 关系模型
  关系模型用表的集合来表示数据间的联系。每个表有多列，每列有唯一的列名。
  超码：一个或多个属性的集合，这些属性的组合可以使我们在一个关系中唯一的标识一个元祖。
  候选码：任意真子集都不是超码的超码被称为候选码。
  主码：数据库中实际用来区分元组的候选码。
  外码：属性中包含另一个关系模式r2的主码，这个属性在本关系模型上称为参照r2的外码。
• 实体-联系模型
• 基于对象的数据模型
• 半结构化数据模型 XML

数据库语言

• 数据定义语言 DDL

• 数据操纵语言 DML

  • 过程化DML
  • 声明式DML

数据库设计

规范化 ： 生成一个关系模式集合，减少冗余，检索方便。

• 第一范式：所有属性的域都是原子的
• 第二范式：数据表中必须有一个主键，并且不包含在主键中的列必须完全依赖于主键，而不能只依赖主键的一部分。
• 第三范式：要求不存在包含非主属性的传递依赖
• 函数依赖：给定一个关系实例r，我们说这个实例满足函数依赖α->β的条件是：对于实例中任意一对元组t1和t2，如果t1[α] = t2[α]，则t1[β] = t2[β]。
• BCNF 所有非平凡的函数依赖左侧的属性集是超码，即在3NF的基础上要求不存在包含主属性的传递依赖。

存储和文件结构

物理存储介质概述

• cache
• 主存
• flash（固态硬盘）
• 磁盘存储
• ...

磁盘的物理特性:主轴、盘片（-磁道-扇区）、磁盘臂、驱动马达（恒定速率）

• 所有盘片的第i条磁道合在一起称为第i个柱面
• 磁盘控制器（驱动、校验和）
• RAID

磁盘性能度量

• 访问时间（磁盘臂寻道时间2~30ms + 等待扇区出现在读写头下所花费的时间：旋转等待时间4~11ms/2 ≈ 8 ~ 20ms）
• 数据传输率（25~100MB/s，根据接口不同）
• 可靠性

RAID

独立磁盘冗余阵列，目的是提高性能（并行读写）和可靠性
通过镜像提高可靠性
通过块级拆分执行并行

• RAID 0级 块级拆分+无冗余
• RAID 1级 块级拆分+镜像
• RAID 2级 比特级拆分+奇偶校验位
• RAID 3级 位交叉的奇偶校验
• RAID 4级 块交叉的奇偶校验，在一张独立的磁盘上为其他N张磁盘上对应的块保留一个奇偶校验块。
• RAID 5级 在RAID4的基础上改进，将数据和奇偶校验位都分布到所有的N+1张磁盘中。只要保证奇偶校验块和数据块不能存储在同一张磁盘上即可。
• RAID 6级 在RAID5的基础上存储了额外的冗余信息，不使用奇偶校验码，使用Reed-Solomon码之类的纠错码。

索引与散列

加速查询

• 顺序索引
  b+树

查询处理

• 语法分析与翻译
• 优化
• 执行

查询优化：从许多策略中找出最有效的查询执行计划的一种处理过程。
给定一个关系代数表达式，查询优化器的任务是产生一个查询执行计划，该计划能获得与原关系表达式相同的结果，并且得到结果集的执行代价最小。

等价规则：

• 合取选择运算可以分解为单个选择运算的序列
• 选择运算满足交换律
• 一系列投影运算只有最后一个运算是必须的，其余的可以省略。
• 选择操作可与笛卡尔积以及θ连接相结合
• θ连接运算满足交换律
• ...

执行计划的选择：
基于代价的优化器
启发式优化：

• 尽早执行选择和投影运算

嵌套子查询的优化
位于where子句中的嵌套子查询处理成传入参数并且返回一个值的集合的函数。参数是嵌套子查询中用到的外层查询的变量。

• 优化器尽可能地试图将嵌套子查询转换成连接的形式，避免随机IO。
• 建立包含嵌套查询的临时表，然后将其与外层查询进行连接。

事务管理

事务的ACID 原子性、一致性、隔离性、持久性。

• 存储：

  • 易失性存储器：主存、缓存
  • 非易失性存储器：磁盘、闪存
  • 稳定性存储器：对外表现信息不丢失，理想化概念。
• 原子性和持久性：
  维护一个日志，每个事务对数据库的修改都首先会记录到日志中，记录事务标识符、修改的数据项标识符、旧值、新值。维护日志提供了redo功能，保证了原子性和持久性；提供了undo功能保证事务终止时回退。
• 隔离性：
  多个事务并发执行，提高吞吐量和资源利用率。减少等待时间。

恢复系统

update log record => tid, did, od, nd.
检查点：在执行检查点的过程中将所有的日志和更新过得缓冲块都输出到磁盘，向日志中写入<checkpoint L>，其中L是执行检查点时正活跃的事务列表。
检查点的目的是提高恢复过程的效率。在检查点之前完成的事务已经写入数据库中，因此不必再回放。只对L中的事务和checkpoint之后开始的事务执行撤销或者回放即可。
缓冲区管理：wal规则（保证数据修改能被undo和redo）
模糊检查点：在所有缓冲块刷盘前开始更新，可能导致检查点是不完全的。
逻辑undo：逻辑日志，记录新值和旧值的是物理日志。

• 物理日志的作用是保持数据库上数据处于某个一致的状态，物理日志一般需要保证幂等。
• 逻辑日志的作用是在一致状态的基础上提供更高级别的原子性和约束，逻辑日志可能不是幂等的。逻辑日志执行过程中也会产生物理日志。
• LSN标识日志记录减少重做

并发控制系统

冲突可串行化：如果一个调度S与一个串行调度冲突等价，则称S是冲突可串行化的。通过构造调度S的优先图来判断是否满足冲突可串行化。
无级联调度：避免回滚导致撤销大量工作。
隔离级别：

• 可串行化（serializable）
• 可重复读
• 读已提交
• 读未提交

都不允许脏写。
隔离级别的实现方法：

• 基于锁的方法
  共享锁、排它锁
  死锁问题：死锁预防（对加锁请求排序、抢占与事务回滚、锁超时）、死锁检测（等待图）、死锁恢复
  （严格）两阶段封锁协议：保证可串行性
• 时间戳
  系统时钟or逻辑时钟
  每个数据项Q需要与两个时间戳值相关联：W-ts，R-ts
  保证无死锁、可串行化，但可能会导致事务饿死现象。
• 基于有效性检查的协议（乐观锁思想）
• 多版本和快照隔离
  快照隔离对读友好，但是在更新写入数据库前需要处理并发更新的冲突，有可能导致回滚：先提交者获胜、先更新者获胜。
  快照隔离不能保证串行化。
  write skew：一对事务相互读取对方写的数据，但是不存在二者同时写的数据
• 索引结构中的并发

B树和B+树的区别，为什么mysql选用b+树作为索引结构？

B+树非叶节点仅存储导航信息，具体数据都存储在叶节点中，并且所有叶子节点和相连的结点使用链表相连，便于区间查找和遍历。
因此IO次数更少，遍历更加方便。
mysql选用b+树作为索引结构的原因就是磁盘读写代价更低，查询效率更加稳定，更便于遍历，便于基于范围的查询

最左匹配原则：https://www.cnblogs.com/yeyuz...

binlog、redo log和undo log

• binlog
  是用于记录数据库执行的写入性操作的信息，以二进制的形式保存在磁盘中，逻辑日志，Server层记录（不管你使用的是什么存储引擎）。主要用来实现主从复制和数据恢复。
  sync_binlog参数控制binlog刷盘时机，可以通过将值调大牺牲一定的一致性来获取更好的性能。
• redo log
  （实现了事务的持久性）每次事务提交的时候，本应该把所有变更刷盘，但是会带来严重的性能问题。（随机io、以页为单位和磁盘交互）解决办法就是将变更按照日志的形式刷盘，然后可以延后批量将变更涉及的数据刷盘，成功刷盘之后记录的日志过期，如果在日志写入后、变更刷盘前机器down了，重启后可以通过重放redo日志保证变更不会丢失。
  redo log本身也可以在内存中缓冲，redo log刷盘后才能认为对应的变更被commit了。并且redo log不需要保存所有的变更操作，check point之前的日志不需要保存。（因为check point之前的变更都已经成功刷盘）
• undo log
  实现事务的原子性，一个事务只有commit日志被写入undo log，才能认为是成功提交的，否则崩溃重启后，会回滚没有commit日志的事务。
  undo log有两个作用，一个如上提供回滚操作，第二个是支持MVCC的实现。（这也是为什么事务提交时，innodb不能立即删除undo log）
  数据表的两个隐藏列：trx_id和roll_ptr，分别为事务编号和上个版本的指针。我们来考虑某个事务对主键id=1的记录进行update操作过程：
  1.对id=1的记录加互斥锁
  2.将旧值拷贝到undo log中，然后对当前值进行更新：主要是事务编号的更新和roll_ptr的更新。

mysql引入了一个ReadView的概念来支持选择可见版本，而也是利用ReadView支持了RR（事务开启的时候生成一个活跃事务表，后续的读都以这个表为准，因此可重复读）和RC（每次读的时候生成当前情况下的ReadView，因此两次读取的活跃事务表可能不同）两种隔离级别。

参考：https://www.cnblogs.com/rongd...

mysql调优

1. 索引相关
   尽量使用覆盖索引，减少回表所需的时间，因此尽量不要使用select *，而是指明对应的列
   联合索引，尽量将区分度最高的放在左边，并考虑最左匹配原则。最左索引碰到范围查询会停止使用（因为范围查询后面的丧失了有序性了）。
   参考：这篇文档
2. 写压力很大、或者加了索引读的压力还是很大，考虑主从架构
   读写分离，同binlong保持异步一致。
3. 分库分表
   垂直分表-将一个表按照字段分成多个表
   垂直分库-按照业务将表进行分类，分布到不同的数据库上。

水平分表-把同一个表的数据按照规则拆到多个表中。
过程：增量双写，存量分步迁移，校验，开启双读（灰度），读流量全切。