数据库面试常考知识点

为什么不使用哈希表来设计索引？

1、进行范围查找时要遍历每个元素
2、哈希碰撞：数据散列不均匀而且浪费存储空间
3、需要大量的连续内存空间；存储元素多也需要额外的内存空间。

设计一个索引系统需要考虑哪些问题？

数据结构、I/O(同时考虑IO量和IO的次数）

ACID是靠什么保证的？

1、原子性是由undolog日志保证的，它记录了需要回滚的历时版本数据，撤销已经执行成功的sql语句。
2、隔离性是由MVCC保证（多版本并发控制）
3、持久性是由redlog保证。mysql修改数据时会在redlog中记录一份日志，就算数据没有保存成功，只要日志保存成功数据就不会丢失
4、一致性是由其他三大特性保证的

什么是MVCC？

多版本并发控制。在读取数据时通过一种类似快照的方式将数据保存下来，这样写锁和读锁不冲突。不过只在读已提交和可重复读两个级别下工作。主要维护了事物id和版本控制链。访问数据时，获取数据中的事物id若id小于readview的最小值，则说明已经提交，可以被用户获取；若在readview中则通过版本控制链roll_pointer去到合适的版本显示。

为什么使用索引？

1、大大加快检索速度
2、帮助服务器避免排序和生成临时表
3、将随机的IO变成顺序IO

MySQL如何执行一条SQL语句？

1、客户端请求
2、连接器（验证用户身份给予权限）
3、查询缓存（存在于缓存中则直接返回）
4、分析器（对语法和词法进行控制）
5、主要对执行的SQL选择最优方案执行（优化器）
6、执行器（操作引擎，返回结果）
服务层
7、存储引擎（存储数据，提供读写接口）引擎层

delete、drop、truncate的区别？

1、删除力度：drop>truncate>delete:drop删除整个表（结构+数据）而truncate是删除表的所有数据，不删除结构，约束，索引信息还在；而delete是每次删除一行，并且在事务日志中记录，以方便回滚。

数据库索引为什么使用B+树？

1、相对于B树，B+树的非叶子节点没有存储指向关键字的具体信息，意味着每个节点能容纳的关键字越多，查找的关键字就越多，IO读写次数更少效率更高。
2、在范围查找时，B树需要一次中序遍历，在中序遍历的过程中会扫描很多额外的节点，而B+树的数据在叶子节点中升序排列，顺序扫描即可。
3、由于B+树每次查找都是从根开始，到叶子节点结束，因此查询更稳定。

MySQL关系型数据库的范式？

使用范式带来的好处主要是减少数据的冗余。但范式并不是越高越好。范式越高意味着表越多，多表联合查询的几率越大，SQL查询的效率就越低。
第一范式：每一列保持原子特性。如果不符合第一范式不能称之为关系型数据库
第二范式：针对于联合主键来说的，属性完全依赖主键，如果不完全依赖应该拆分出来
第三范式：属性不依赖其他非主属性
BC范式：每个表只有一个候选键（每行的值不同）
第四范式：消除表的多值依赖

讲一下SQL的索引和优化问题？

流程：获取运行时间长，耗性能的IO—>用EXPLAIN去分析
步骤：设置慢查询日志为开启状态再设定一个时间阈值---->根据超过时间阈值的SQL，使用EXPAIN进行分析。

MySQL的锁机制？

• 表级锁：开销小、加锁快、不会死锁
• 行级锁：开销大、加锁满、会出现死锁
• 排他锁：X锁、写锁 for updata
• 共享锁：S锁、读锁
  InnoDB的行级锁是加在索引项上面的，是给索引加锁而不是给行记录加锁。如果过滤条件中没有索引的话。使用的是表锁而不是行锁。**（行锁要结合索引使用）**辅助索引树也是通过映射到之间的索引树进行加行锁。

什么是脏读、幻读、丢弃修改、不可重复读？

• 脏读：A事务读取未提交的数据X后，数据发生回滚。此时A事务读取的数据是脏数据。
  第一个事务首先读取var变量为50，接着准备更新为100的时，并未提交，第二个事务已经读取var为100，此时第一个事务做了回滚。最终第二个事务读取的var和数据库的var不一样

• 幻读：A事务读取了一段范围数据Y，但该段数据内又插入了新的数据，此时事务A读取的就是幻读
  T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

• 丢弃修改：两个事务AB同时对数据X=0递增，但是B覆盖A事务的命令导致X=1而不是2.
  T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。例如：事务1读取某表中的数据A=50，事务2也读取A=50，事务1修改A=A+50，事务2也修改A=A+50，最终结果A=100，事务1的修改被丢失。

• 不可重复读：T2读取一个数据，然后T1对该数据做了修改。T2再去读，前后读取的内容不一致。
  T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

串行化隔离级别怎么解决幻读问题？

通过间隙锁来解决。满足过滤条件但不在表内中的范围会被加锁。

undo log回滚日志的作用？

• 提供发生错误时回滚
• 提供了MVCC的非锁定读（快照读）
  里面的数据增加了事务id.指针（指向修改前的数据）
  新增加的数据指向的指针未空

已提交读和可重复读的实现？

由MVCC实现。
快照读（非锁定读）：读的是记录的可见版本（之前拍的照片）selecet时产生
当前读取的是记录的最新版本。
已提交读：使用MVCC的快照读实现，每次select都会对已提交的数据进行一次快照
可重复读：只有第一次select会产生数据快照，后续就算有事务修改了数据但是依然查看最初的快照数据

为什么可重复读时部分解决幻读？

数据虽然在另一个事务提交了，但还是select不到，因为读的是快照
事务能看见自己修改的数据，因此被事务修改后的数据满足筛选条件，那么就又幻读了。

什么是意向锁？意向共享锁？意向排他锁？

意向共享锁：事务计划给行加行共享锁，必须先获得该表的IS锁
意向排他锁：事务计划给行加排他锁，必须先获得该表的IX锁
正对表锁，更快获取表锁，因为获取表锁前要检查谁被锁需要扫描主要目的是显示事务正在锁定某行或者试图锁定某行，为了更高效的获取锁

如何避免死锁？

多个事务/线程获取多个相同资源锁时，应按照同样的顺序获取，但是mysql又超时时间，并执行事务的回滚。

redo log(重做日志）什么是重做日志？

在事务开始时就开始工作。
commit时将log buffer写入磁盘
如果在刷盘时发生异常、端点也视为事务没有commite成功
buffer pool里面的脏数据，不需要加commit同步
因此事务最重要的是写redo log，而不是写数据redolog是物理日志，而undo log存储sql语句是逻辑日志

sql备份

mysqldump备份 source恢复