面试八股-数据库

1.MySQL原理

1.1.事务

1.1.1.事务基本特性ACID

• A 原子性，要么全成功，要么全失败 由undo log日志保证，其记录需要回滚日志信息，回滚就撤销执行成功的sql。

• C 一致性，事务不提交，就不会应用到数据库 一般由代码层面和其他三大特性共同保证。

• I 隔离性，一个事务修改最终提交前，其他事务不可见 事务之间不相互影响，由锁和MVCC保证

• D 持久化，一旦提交，修改永久存储到数据库 由内存+ redo log保证，修改数据同时在内存和redo log记录，提交通过redo log刷盘，宕机可以从redo log恢复。 redo log是物理操作，undo log是逻辑日志。

1.1.2.事务隔离级别及其MySQL实现

隔离级别

• 读未提交(read uncommit) 读到其他事务未提交的数据 脏读
• 读已提交(read commit) oracle默认级别 两次读取结果不一致 不可重复读
• 可重复读(repeatable read) mysql默认级别 每次读取结果都一样 可能幻读 (MySQL通过Next-key Lock锁算法解决该问题)
• 串行(serializable) 一般不使用 会给每一行读取数据加锁，导致大量超时和锁竞争问题 可以彻底解决幻读问题

MySQL实现

• 读未提交和读已提交，通过Record Lock算法实现行锁
• 可重复读，使用Next-Key Lock算法实现了行锁
• 串行，每个读操作自动加共享锁

1.1.3.事务分类

• 扁平事务：BEGIN开始 COMMIT或者ROLLBACK结束
• 带保存点的事务：有savepoint，可以回滚到之前一个状态
• 链事务：提交事务时释放不需要的数据对象，将必要上下文传给下一个要开始的事务
• 嵌套事务：层次结构框架，顶层事务控制各个层次事务
• 分布式事务：多个节点需要满足ACID

1.1.4.MySQL事务回滚

• ROLLBACK 结束用户所有事务，并且撤销所有未提交更改
• ROLLBACK TO [SAVEPOINT] identifier 配合SAVEPOINT命令一同使用，回滚到之前某个标记点

1.2.范式

• 第一范式：所有字段都是不可再分解的原子值
• 第二范式：属性完全依赖于主键，和主键相关，不能有无关的列
• 第三范式：每一列都和主键直接相关，不能间接相关(推导)

1.3.索引

• 唯一性索引保证数据唯一性
• 加快数据检索速率和表连接效率

1.3.1.索引分类

• 数据结构分类：
  • Hash索引 适合随机查找，不适合排序、顺序查找，存在冲突影响效率，适合大规模查找单个记录。
  • b树和b+树 适合范围查找
  • 全文索引 倒排索引表实现，适合大量文本中检索
• 数据索引情况分类：
  • 聚簇：数据和索引放在一起，数据物理存放顺序和索引顺序一致。单个和范围查询效率高，维护代价大，插入分页会引起索引分裂。
  • 非聚簇：叶子节点不存储数据，只存储地址，之后需要根据地址找数据。维护代价低，不容易分裂，内存较小，对于count等操作效率高。

1.3.2.索引相关数据结构问题

• 红黑树 ：弱平衡树，高度略高于强平衡树，但是插入操作旋转次数少，每次最多旋转三次就可以达到要求的大致平衡。
• B树和B+树对比
  • B树每个叶子存储key和data，B+树只有叶子存储data，B+树可以保证非叶子节点存储更多的key，比B树更加矮胖，效率更快。
  • B+树每个叶子节点保存指向相邻节点的指针，可以支持全表扫描，范围查找等。
• 使用B+树的优势
  • 磁盘读写代价更低，因为内部节点只有key，尺寸更小，一次可以读入更多关键字，减少读入次数。
  • 查询效率稳定，每次都必须到叶子才能获取数据，查询效率相同。
  • 可以通过叶子节点进行全表遍历。

1.3.3.索引分裂

9-1分裂和5-5分裂。

• 5-5: 并发较高，索引无序状态
• 9-1: 并发较低，索引有序

1.3.4.索引使用原则

• 适合建立索引
  • where中的列和连接语句中的列
• 不适合建立索引
  • 基数小的表
  • 更新频繁的列
  • 过多建立索引
  • 重复度高、辨识度低的，比如性别
  • Text，image bit等类型
• 需要酌情考虑的
  • 尽可能建立短索引，如果要长的可以考虑前缀长度

1.3.5.索引失效

• where中使用计算函数
• 大于等于号
• or这样的条件判断有一个字段没走索引
• like的通配符在左边
• 数据过少发现扫描更快
• 组合索引失效(没有最左原则)
• 隐式转换，比如字段为字符串，但是筛选使用了数值

1.3.6.索引重建

时机

1. 表频繁update、delete

2. 发生了alter table… move操作

如何判断

1. 分析索引结构 analyze index … valid structure;
2. 查询index_stats表select height,DEL_LF_ROWS/LF_ROWS from index_stats; height>=4或者比>0.2时要考虑重建

方式

1. drop原索引，创建新的
2. 直接重建 alter index xxx rebuild;alter index xxx rebuild online; 后者会减少重建带来的任何加锁问题，新旧索引会同时存在，结束之后drop原来表，不会阻塞DML操作。会产生大量Redo Log</