数据库面试知识点Q&A【推荐】_面试数据库知识书籍推荐-CSDN博客

本文深入探讨数据库设计、索引优化、事务管理及锁机制，涵盖数据库范式、索引类型、事务特性、锁类型等内容，助您掌握数据库性能调优关键。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据库知识图谱

一、数据库范式

1NF

每一列都是不可分割的原子数据项

2NF

记录唯一依赖于主键，消除部分函数依赖,(x,y),z,x->z

3NF

任何非主属性不依赖非主属性，消除部分函数依赖,(x,y,z),x->z

BCNF

非主属性不能对主键子集依赖，消除对主码子集的依赖

二、数据库索引

索引类型

主键索引：InnoDB引擎，数据表无主键，会隐式生成一个6字节的rowid
唯一索引
普通索引
联合索引
全文索引
聚簇索引：叶子结点存储具体的数据，如主键索引
非聚簇索引：叶子结点没有存储和具体数据，如普通索引

索引实现

哈希表
- hash索引仅满足“=”、“IN”和“<=>”查询，不能使用范围查询，时间复杂度O(1)，取决于产生冲突多少
有序数组
- 支持范围查找，需要所有元素有序，适用于静态数据，例如年度帐单；时间复杂度为O(logn)
B+树
- 非叶子结点只保存索引结点
- 叶子结点保存所有数据，前后有指针链接，数据有序，可以快速范围查询
- 树高约3-4层，磁盘I/O次数少，效率高
- 叶子结点通过指针链接，前后节点可以快速访问，符合局部性原理：如果一个存储器的位置被引用，那么将来它附近的位置也会被引用
B树
- 所有结点都保存该行所有数据
- 磁盘I/O次数更多，当查找两个节点一个在根左边、一个在根右边，查找左边的结点过后需要回到根节点再查找右边的节点，磁盘I/O对比B+树增加了一倍
- 取多条数据时需要局部中序遍历，可能要跨层访问；B+树叶子结点有指针可以直接取多条；

三、数据库优化

回表

普通索引B+树的叶子结点存放是主键ID和索引本身，首先根据普通索引到叶子结点查询到主键ID，然后根据主键ID到主键索引B+树查询到该主键的全部数据；

覆盖索引

如果只查询普通索引B+树叶子结点上的数据，如主键ID，可以采用**select id from xx where index=?**避免回表操作；

联合索引+最左匹配

数据表建立联合索引（A，B）时，SQL语句应该匹配索引A，B的顺序；

谓词下推

计算向数据移动，select t1.name,t2.name from t1 join t2 on t1.id=t2.id,先把用到的字段name,id查询出来再关联ID，如果该表数据列有100列，只用取出2列

索引下推

计算向数据移动，mysql5.6及以后如果select * from t where t.name=? and t.age=?会根据name和age的值筛选，将筛选过后的值返回；5.6以前先查询所有满足name的值，在返回的值再进行一次age值筛选，建议用5.6及以后版本

三、数据库锁机制

锁类型

Innodb
- 共享锁/读锁
  - 将对象数据变为只读模式，不能进行更新删除操作
- 排它锁/写锁
  - 当执行insert/update/delete时，其它事务不能读取
- 行锁/排它锁
  - 事务A获取行锁，事务B不能读取该行数据，但是可以去redo log读取MvcC快照版本的数据
- 表锁
自增锁
- 为自增列设置一个表级锁
间隙锁
- 保证某个间隙内的数据在锁定情况下不会发生任何变化；间隙中有数据行，对这些数据行加行锁，否则就是间隙锁，不允许增加数据到该间隙；
  - select * from t where id = 10 for update;// 注意：普通查询是快照读，不需要加锁
    如果，上面语句中id列没有建立索引或者是非唯一索引时，则语句会产生间隙锁；
    如果，搜索条件里有多个查询条件(即使每个列都有唯一索引)，也是会有间隙锁的；
MyISAM
- 表共享读锁:其他线程操作可以读，但不能写
- 表独占写锁:其他线程操作不能读取，也不能写

MyIASM和Innodb

主要区别
- 1、MyIASM是非事务安全的，而InnoDB是事务安全的
- 2、MyIASM锁的粒度是表级的，而InnoDB支持行级锁
- 3、MyIASM支持全文类型索引，而InnoDB不支持全文索引
- 4、MyIASM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyIASM
- 5、MyIASM表保存成文件形式，跨平台使用更加方便
应用场景
- MyIASM管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量select操作，应该选择MyIASM
- InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，应该选择InnoDB

四、数据库事务

原子性(Atomicty)

一个事务要么发生，要么不发生；通过undo log实现，在操作任何数据之前，先将数据备份到undo log，再操作。如果出现Error或者Rollback,对undo log数据进行反向操作来恢复，undo log是逻辑日志，更新的页中个别数据；

一致性(Consistency)

事务操作前后的状态保持一致

隔离性(Isolation)

并发事务不会相互影响，事务隔离级别
- 读未提交(Read UnCommitted)：脏读、不可重复读、幻读问题
  - 读取时不加锁，写入时加行级共享锁；
  - 例如事务A读取某行记录时，事务B也能对这行记录读取更新，当事务B更新记录时，事务A读取到事务B修改的版本，即使事务B未提交
- 读已提交(Read Committed))：不可重复读、幻读问题
  - 读取时加行级共享锁，读完就释放共享锁；写入时加行级排它锁，事务完成再释放锁；
  - 例如事务A负责读，事务B负责写，A读完数据后释放共享锁，B更新数据，事务还未结束，A再读，两次得到数据不一样，产生不可重复读的问题
- 可重复读(Read Reaptable)：幻读问题
  - 读取时加行级共享锁，事务完成后再释放；写入时加行级排它锁，事务完成后释放；
  - 例如事务A负责读，事务B负责写，A读完数据后等事务结束才释放共享锁，B更新数据，直到事务结束，A再读，两次得到数据均为A第一次读到的数据，解决不可重复读的问题
- 串行化(Serializable)：一次只能有一个线程操作
  - 读取时加表级共享锁，写入加表级排它锁；
隔离出现的问题
- 脏读
  - 事务A可以读取事务B未提交的数据，事务B回滚事务A读取的数据成为脏数据；
- 不可重复读
  - 事务A第一次读取数据为X，此时事务B修改数据为Y，事务A再次读数据为Y，两次读取结果不一致；
- 丢失更新
  - 事务A和事务B同时对一个数据进行修改，事务A修改为X，事务B再修改为Y，A写入的数据被B覆盖；
- 幻读
  - 事务A第一次查询数据X条，此时事务B新增一条，事务A再次读取数据为X+1条

持久性(Durability)

事务提交过后数据就不会再改变；redo log记录数据备份，事务提交前只需要将redo log持久化，系统可以根据redo log来恢复数据；写入redo log的三种方式
写入流程
- 事务–>LogBuffer-(每秒写入并调用fsync方法)->OS Buffer–>redo log
- 事务-(每次写入，每次调用fsync方法)->OS buffer–>redo log
- 事务-(每次写入，每秒调用fsync方法)->OS buffer–>redo log，最多丢失一个OS Buffer

写入redo.log

SQL语句执行

执行过程

连接器
- 验证用户身份和权限
缓存
- 如果有缓存查询缓存，缓存中存在直接返回，mysql8以后取消缓存
分析器
- 对SQL语句执行语法分析、词法分析
优化器
- 按照最优的方案执行，如何选择索引，如何进行表关联等
执行器
- 校验用户权限，有权限就调用存储引擎的接口获取结果

查询语句

用户拥有权限，先查询缓存，缓存存在直接返回；然后提取关键字段进行词法分析和语法分析，优化器再选择最优的方案，执行器调用引擎接口获取数据；

更新语句：两阶段提交

InnoDB引擎保存数据到内存并记录redo log日志，状态设置为prepare
通知执行器，执行器收到通知后记录bin log日志
bin log日志记录后调用存储引擎接口修改redo log日志状态为commit

•先写 redo log 直接提交，然后写 binlog：假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 bingog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
•先写 binlog，然后写 redo log：假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。