数据库面试

关系型数据库

基于表格模型组织数据的数据库系统。

表与表之间通过可以通过字段（列）联系起来。

mysql有哪些优点

1. 开源且维护
2. 强大的sql
3. 可靠
4. 引擎优化
5. 底层优化
6. 全平台适用，方便移植
7. 功能支持
   • 事物
   • 视图
   • 函数
   • 存储过程
   • 触发器

sql的分类

DDL 数据定义语言 表结构、视图、索引

DQL 数据查询语言 查

DML 数据操纵语言 增、删、改

DCL 数据控制功能 权限控制

存储引擎

InnoDB、MyISAM

• InnoDB支持事务、外键；MyISAM不支持
• InnoDB是聚集索引；数据文件和索引绑在一起，必须要有主键，通过主键索引效率很高 MyISAM是非聚集索引，数据文件是分离的；索引保存的是数据文件的指针，主键索引和辅助索引是独立的
• InnoDB不支持全文索引；而MyISAM支持全文索引，查询效率后者高
• InnoDB不保存表的具体行数；MyISAM用一个变量保存整个表的行数
• InnoDB支持行级锁和表级锁，默认行级锁；MyISAM采用表级锁

约束

• Not null 字段内容不为空
• Unique 字段内容不可重复
• PrimaryKey 字段内容不能重复
• ForeignKey 预防破坏表之间连接的动作，防止非法数据插入外键列，它必须是它指向的那个表中的值之一(在⼀个表中存在的另⼀个表的主键称此表的外键)

三大范式

1. 第一范式：列的原子性，每个列不可再分。比如地址信息可以划分成省、市、区
2. 第二范式：满足第一范式的基础上，每个非主键列完全依赖于主键。比如拆分冗余的订单表成订单表和订单商品表
3. 第三范式：满足第二范式的基础上，每个非主键字段直接依赖于主键，消除传递依赖。比如部门表，主键为部门ID，却冗余了部门负责人的性别和年龄

索引

索引类型

• 普通索引：⼀个索引只有单个列，⼀个表可以有多个单列索引

• 唯⼀索引：索引列的值必须唯⼀，但允许有空值

• 复合索引：⼀个索引由多列值组成，专⻔⽤于组合搜索，其效率⼤于索引合并

• 聚簇索引(也叫聚集索引、主键索引)：并不是⼀种单独的索引类型，⽽是⼀种数据存储⽅式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同⼀个结构中保存了B-Tree索引(技术上来说是B+Tree)和 数据⾏。

• ⾮聚簇索引：不是聚簇索引，就是⾮聚簇索引

• 从存储结构：BTree索引(B-Tree或B+Tree索引)，Hash索引，full-index全⽂索引

• 从应⽤层次：普通索引，唯⼀索引，复合索引

• 从数据的物理顺序与键值的逻辑(索引)顺序关系: 聚簇索引，⾮聚簇索引

聚簇索引和非聚簇索引

• 聚簇索引：数据行存储在叶子节点，索引与数据一起存储。
• 非聚簇索引：存储主键值的索引，在查询时需要通过主键值再去聚簇索引查找完整数据（回表）。

这个过程涉及到额外的磁盘 I/O 操作，因此回表会比直接使用聚簇索引更慢，尤其是在数据量较大的时候。

第⼀次索引⼀般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使⽤全表扫描。

通常情况下，主键索引(聚簇索引)查询只会查⼀次，⽽⾮主键索引(⾮聚簇索引)需要回表查询多次。当然，如果是覆盖索引的话，查⼀次即可。

注意：MyISAM⽆论主键索引还是⼆级索引都是⾮聚簇索引，⽽InnoDB的主键索引是聚簇索引，⼆级索引是⾮聚簇索引。我们⾃⼰建的索引基本都是⾮聚簇索引。

非聚簇索引一定会回表吗

• 非聚簇索引不一定会回表，只有在查询的字段没有完全命中索引时，才需要回表
• 覆盖索引可以避免回表，因为索引中已经包含了查询所需的所有字段

联合索引与最左前缀原则

MySQL可以使⽤多个字段同时建⽴⼀个索引，叫做联合索引。

MySQL的联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，否则⼤概率⽆法命中索引。

前缀索引

有可能索引的字段⾮常⻓，这既占内存空间，也不利于维护。所以，如果只把很⻓字段的前⾯的⼀部分作为⼀个索引，并且索引区分度很⾼的话，就会产⽣不错的均衡的效果。

索引结构

索引存放在硬盘上，在硬盘上查询会产⽣I/O操作，索引的查找次数也就是I/O次数，I/O次数多会影响查询性能，所以索引的数据结构要在满⾜各种查找需求前提下，尽量减少I/O次数。

为什么不用b树而要用b+树

• B+ tree的⾮叶⼦节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相⽐存储即存索引⼜存记录的B-tree，B+ tree的⾮叶⼦节点可以存放更多的索引，因此B+ tree可以⽐B-tree更「矮胖」，查询底层节点的磁盘 I/O次数会更少。
• 双向链表
• B+ tree叶⼦节点才会存放实际数据（索引+记录），⾮叶⼦节点只会存放索引，这些冗余索引让
• B+ tree树在插⼊、删除的效率都更⾼，⽐如删除根节点的时候，不会像B-tree那样会发⽣复杂的树的变化。
• B+ tree叶⼦节点之间⽤链表连接了起来，有利于范围查询，⽽B-tree要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如B+ tree。

为什么不用哈希

Hash虽然可以快速定位，但是没有顺序，IO复杂度⾼。

基于Hash表实现，只有Memory存储引擎显式⽀持哈希索引，或是InnoDB的⾃适应hash。

Hash适合等值查询，如=、in，不⽀持范围查询；因为不是按照索引值顺序存储的，就不能像B+Tree索引⼀样利⽤索引完成排序。Hash索引在查询等值时⾮常快。

因为Hash索引始终索引的所有列的全部内容，所以不⽀持部分索引列的匹配查找。

如果有⼤量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

为什么不用二叉树

• 二叉树每个节点⾄多拥有两个⼦节点，数据多的话会增加树⾼，IO代价⾼。
• 极端情况下会退化成链表，相当于全表扫描

为什么不用红黑树

红⿊树依旧会有树的⾼度随着数据量增加⽽增加的问题，IO代价⾼。

索引下推

索引下推的基本思想：将查询的过滤条件尽量提前到索引扫描阶段，避免了在读取数据后再进行过滤。

传统的查询流程是：数据库首先利用索引找到满足条件的行，然后在获取到的数据中再进行条件过滤。

而在索引下推优化中，过滤条件会在索引扫描时就提前应用，从而减少了需要访问的数据量，提高了查询效率。

使用索引的代价

• 不能超过
• 读写操作
• 维护成本

优化，表设计

explain

底层

• b+树，b树，2-3树，哈希表，平衡树，红黑树
• 树的高度

SQL语句

join、group、having

能不能多表查询

反范式

事务

事物隔离级别

解决并发安全问题。

隔离级别：读未提交、读已提交、可重复读、串行

安全问题：脏读、不可重复读、幻读（第一次读和第二次读数据量不对）

Read Uncommitted(读取未提交内容)

在该隔离级别，所有事务都可以看到其他未提交事务的执⾏结果。本隔离级别很少⽤于实际应⽤，因为它的性能也不⽐其他级别好多少。

读取未提交的数据，也被称之为脏读(Dirty Read)

Read Committed(读取提交内容)

这是⼤多数数据库系统的默认隔离级别(但不是MySQL默认的)。它满⾜了隔离的简单定义:⼀个事务只能看⻅已经提交事务所做的改变。

这种隔离级别会导致所谓的不可重复读(NonRepeatable Read)，因为同⼀事务的其他实例在该实例处理其间可能会有新的 commit，所以同⼀select可能返 回不同结果。

Repeatable Read(可重读读)

这是MySQL的默认事务隔离级别，它确保同⼀事务的多个实例在并发读取数据时，会看到同样的数据⾏。

不过理论上，这会导致另⼀个棘⼿的问题：幻读 (Phantom Read)。

Serializable(串⾏化)

通过强制事务排序，使之不可能相互冲突，从⽽解决幻读问题。简⾔之，它是在每个读的数据⾏上加上共享锁。

在这个级别，可能导致⼤量的超时现象和锁竞争。

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使⽤的是MVCC(多版本并发控制)，通过保存修改的旧版本信息来⽀持并发⼀致性读和回滚等特性。

acid

原子性、一致性、隔离性、持久性

怎么保证原子性

• undo log 日志保证的 MVCC 多版本控制系统

怎么保证隔离性

• 锁
• MVCC 多版本控制系统 rp rc 版本id

怎么保证持久性

• redo log 读写操作 缓存空间 顺序写

脏读、幻读、不可重复读

• 脏读是指⼀个事务读取到了其他事务没有提交的数据
• 不可重复读是指⼀个事务内多次根据同⼀个查询条件查询出来的同⼀⾏记录的值不⼀样（update）
• 幻读是指⼀个事务内多次根据同个条件查出来的记录⾏数不⼀样（insert：插⼊意向锁；delete：record lock）

innoDB在RR级别下怎么解决幻读

• 在快照读的情况下，使用mvcc
• 在当前读的情况下，使用临键锁

事物实现原理

事务是基于重做⽇志（redo log）和回滚⽇志（undo log）实现的。

每提交⼀个事务必须先将该事务的所有⽇志写⼊到重做⽇志进⾏持久化，数据库就可以通过重做⽇志来保证事务的原⼦性和持久性。

每当有修改事务时，还会产⽣undo log，如果需要回滚，则根据undo log的反向语句进⾏逻辑操作。

⽐如insert⼀条记录就delete⼀条记录。undo log主要实现数据库的⼀致性。

log

• undo log 回滚
• redo log 什么时候刷盘，写回硬盘。数据写到文件里。缓存区数据比较大时。提交事物的时候。
• bin log 记录有数据修改的情况，顺序写。全部数据。恢复数据库。主从复制。

redo log实现事务的持久性，undo log实现事务原⼦性。

锁

锁粒度

在关系型数据库中，可以为⾏级锁(InnoDB引擎)、表级锁(MyISAM引擎)和⻚级锁(BDB引擎)。

MyISAM采⽤表级锁(table-level locking)。InnoDB⽀持⾏级锁(row-level locking)和表级锁，默认为⾏级锁。

⾏级锁

⾏级锁是MySQL中锁定粒度最细的⼀种锁，表示只针对当前操作的⾏进⾏加锁。 ⾏级锁能⼤⼤减少数据库操作的冲突。其加锁粒度最⼩，但加锁的开销也最⼤。

⾏级锁分为共享锁和排他锁。共享锁多个事物只能读，排他锁只能一个事物改。

开销⼤，加锁慢；会出现死锁；锁定粒度最⼩，发⽣锁冲突的概率最低，并发度也最⾼。

例如，事务A锁定了行1，事务B锁定了行2，随后事务A请求锁行2，而事务B请求锁行1，这就导致了死锁。

表级锁

表级锁是MySQL中锁定粒度最⼤的⼀种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被⼤部分MySQL引擎⽀持。最常使⽤的MyISAM与InnoDB都⽀持表级锁定。

表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)。

开销⼩，加锁快；不会出现死锁；锁定粒度⼤，发出锁冲突的概率最⾼，并发度最低。

⻚级锁

⻚级锁是MySQL中锁定粒度介于⾏级锁和表级锁中间的⼀种锁。表级锁速度快，但冲突多，⾏级冲突 少，但速度慢。所以取了折衷的⻚级，⼀次锁定相邻的⼀组记录。BDB⽀持⻚级锁，开销和加锁时间界 于表锁和⾏锁之间，会出现死锁。锁定粒度界于表锁和⾏锁之间，并发度⼀般。

乐观锁和悲观锁

乐观并发控制(乐观锁)和悲观并发控制(悲观锁)是并发控制主要采⽤的技术⼿段。

• 悲观锁：假定会发⽣并发冲突，屏蔽⼀切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现⽅式：使⽤数据库中的锁机制
• 乐观锁：假设不会发⽣并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的⽅式来进⾏锁定。

从上⾯对两种锁的介绍，我们知道两种锁各有优缺点，不可认为⼀种好于另⼀种，像乐观锁适⽤于写⽐较少的情况下(多读场景)，即冲突真的很少发⽣的时候，这样可以省去了锁的开销，加⼤了系统的整个吞吐量。但如果是多写的情况，⼀般会经常产⽣冲突，这就会导致上层应⽤会不断的进⾏retry，这样反倒是降低了性能，所以⼀般多写的场景下⽤悲观锁就⽐较合适。

共享锁和排他锁

• 共享锁: ⼜叫做读锁。当⽤户要进⾏数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。
• 排他锁: ⼜叫做写锁。当⽤户要进⾏数据的写⼊时，对数据加上排他锁。排他锁只可以加⼀个，他和其他的排他锁，共享锁都相斥。

⽤上⾯的例⼦来说就是⽤户的⾏为有两种：⼀种是来看房，多个⽤户⼀起看房是可以接受的。⼀种是真正的⼊住⼀晚，在这期间，⽆论是想⼊住的还是想看房的都不可以。

意向锁

场景：事务A锁住表中的⼀⾏（写锁），事务B锁住整个表（写锁）

有了意向锁之后，前⾯例⼦中的事务A在申请⾏锁的时候，数据库会⾃动先给事务A申请表的意向排他锁。当事务B去申请表的写锁时就会失败，因为表上有意向排他锁之后，事务B申请表的写锁时会被阻塞。

意向锁是表级别锁的原因是：当我们需要给⼀个表加上表锁的时候，需要根据意向锁去判断表中有没有数据⾏被锁定，以确定是否能加锁成功。如果意向锁是⾏锁，那么我们就得遍历表中所有数据⾏来判断。如果意向锁是表锁，则直接判断⼀次就知道表中是否有数据⾏被锁定了。另外，意向锁间是相互兼容的。

表锁示例：update语句中的where条件没有索引。

间隙锁

间隙锁，Gap Lock，锁定⼀个范围，但是不包含记录本身。

间隙锁只存在于可重复读隔离级别，⽬的是为了解决可重复读隔离级别下幻读的现象。

假设，表中有⼀个范围id为（3，5）间隙锁，那么其他事务就⽆法插⼊id=4这条记录了，这样就有效的防⽌幻读现象的发⽣。

Next-Key锁

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定⼀个范围，并且锁定记录本身。

假设，表中有⼀个范围id为（3，5）的 next-key lock，那么其他事务即不能插⼊id=4记录，也不能修改id=5这条记录。

所以，next-key lock 即能保护该记录，⼜能阻⽌其他事务将新纪录插⼊到被保护记录前⾯的间隙中。

next-key lock 是包含间隙锁+记录锁的，如果⼀个事务获取了X型的 next-key lock，那么另外⼀个事务在获取相同范围的X型的 next-key lock 时，是会被阻塞的。

⽐如，⼀个事务持有了范围为（1, 10）的 X 型的 next-key lock，那么另外⼀个事务在获取相同范围的X型的 next-key lock 时，就会被阻塞。 虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑X型与S型关系，X型的记录锁与X型的记录锁是冲突的。

另外，唯⼀索引加锁的时候，next-key lock 会退化为记录锁。

插⼊意向锁

• 插⼊意向锁是⼀种特殊的间隙锁（⾏锁），不是意向锁，在insert操作时产⽣
• 插⼊意向锁不会阻⽌任何锁，对于插⼊的记录会持有⼀个记录锁
• 插⼊意向锁会被间隙锁阻⽌
• 插⼊意向锁之间互不排斥，所以即使多个事务在同⼀区间插⼊多条记录，只要记录本身（主键、唯⼀索引）不冲突，那么事务之间就不会出现冲突等待。插⼊意向锁的作⽤是为了提⾼并发插⼊的性能

⾃增锁

锁优化

• 设计索引，尽量使⽤索引去访问数据，加锁更加精确，从⽽减少锁冲突
• 选择合理的事务⼤⼩，给记录显示加锁时，最好⼀次性请求⾜够级别的锁。例如，修改数据的话，最好申请排他锁，⽽不是先申请共享锁，修改时在申请排他锁，这样会导致死锁
• 不同的程序访问⼀组表的时候，应尽量约定⼀个相同的顺序访问各表，对于⼀个表⽽⾔，尽可能的固定顺序的获取表中的⾏，这样⼤⼤的减少死锁的机会
• 尽量使⽤相等条件访问数据，这样可以避免间隙锁对并发插⼊的影响
• 数据查询的时候不是必要，不要使⽤加锁
• MySQL的MVCC可以实现事务中的查询不⽤加锁
• 对于特定的事务，可以使⽤表锁来提⾼处理速度活着减少死锁的可能

分布式

分库分表

⾸先单机数据库所能承载的连接数、I/O及⽹络的吞吐等都是有限的，所以当并发量上来了之后，数据库就渐渐顶不住了；再则，如果单表的数据量过⼤，查询的性能也会下降。因为数据越多B+树就越⾼，树越⾼则查询I/O的次数就越多，那么性能也就越差。

因为上述的原因，不得已就得上分库分表了。

把以前存在⼀个数据库实例⾥的数据拆分成多个数据库实例，部署在不同的服务器中，这是分库。

把以前存在⼀张表⾥⾯的数据拆分成多张表，这是分表。

• 分库：是为了解决服务器资源受单机限制，顶不住⾼并发访问的问题，把请求分配到多台服务器上，降低服务器压⼒。
• 分表：是为了解决由于单张表数据量多⼤，⽽导致查询慢的问题。⼤致三、四千万⾏数据就得拆分，不过具体还是得看每⼀⾏的数据量⼤⼩，有些字段都很⼩的可能⽀持更多⾏数，有些字段⼤的可能⼀千万就顶不住了。

分库

⼀般分库都是按照业务划分的，⽐如订单库、⽤户库等等。 有时候会针对⼀些特殊的库再作切分，⽐如⼀些活动相关的库都做了拆分。 因为做活动的时候并发可能会⽐较⾼，怕影响现有的核⼼业务，所以即使有关联，也会单独做拆分。

事务问题

我们使⽤关系型数据库，有很⼤⼀点在于它保证事务完整性。 ⽽分库之后单机事务就⽤不上了，必须使⽤分布式事务来解决。

连表 JOIN 问题

在⼀个库中的时候我们还可以利⽤ JOIN 来连表查询，⽽跨库了之后就⽆法使⽤ JOIN 了。 此时的解决⽅案就是在业务代码中进⾏关联，也就是先把⼀个表的数据查出来，然后通过得到的结果再去查另⼀张表，然后利⽤代码来关联得到最终的结果。 这种⽅式实现起来稍微⽐较复杂，不过也是可以接受的

还有可以适当的冗余⼀些字段。⽐如以前的表就存储⼀个关联ID，但是业务时常要求返回对应的Name或者其他字段。这时候就可以把这些字段冗余到当前表中，来去除需要关联的操作。

分表

垂直分表

就是把⼀些不常⽤的⼤字段剥离出去。

举个例⼦：⽤户名是很常⻅的搜索结果，性别和年龄占⽤的空间⼜不⼤，⽽地址和个⼈简介占⽤的空间相对⽽⾔就较⼤，但⼀个数据⻚的空间是有限的，把⼀些⽆⽤的数据拆分出去，⼀⻚就能存放更多⾏的数据。

内存存放更多有⽤的数据，就减少了磁盘的访问次数，性能就得到提升。

⽔平分表

⼀张表内的数据太多了，上⽂也提到了数据越多B+树就越⾼，访问的性能就差，所以进⾏⽔平拆分。

分表的问题

垂直分表还好，就是需要关联⼀下，⽽⽔平分表就比较麻烦。

排序、count、分⻚问题

如果⼀个⽤户的数据被拆分到多个表中，那查询结果分⻚就不像以前单张表那样直接就能查出来了，像 count 操作也是⼀样的。 只能由业务代码来实现或者⽤中间件将各表中的数据汇总、排序、分⻚然后返回。 像count操作的结果其实可以缓存下来，然后每次数据增删都更新计数。

分库分表中间件

主从同步

主从同步主要依赖的就是 binlog，MySQL 默认是异步复制，具体流程如下：

• 主库：
  • 接受到提交事务请求
  • 更新数据
  • 将数据写到binlog中
  • 给客户端响应
  • 推送binlog到从库中
• 从库：
  • 由 I/O 线程将同步过来的 binlog 写⼊到 relay log 中
  • 由 SQL 线程从 relay log 重放事件，更新数据
  • 给主库返回响应

主从同步的时延

从上图的流程就可以得知，时延是必然存在的。

时延过⼤的话就有可能出现⼀个⽤户刚注册，然后登陆报该⽤户不存在的....

因为数据是写到主库中的，查询⾛从库有可能还未来同步完毕，导致查不到这个⽤户，这就⾮常不友好了。

常⻅解决⽅式有以下⼏种：

• ⼆次查询。如果从库查不到数据，则再去主库查⼀遍，由API封装即可，算是⼀个兜底策略，⽐较简单。不过等于读的压⼒⼜转移到主库身上了，如果有不法分⼦估计搞⼀下必定查不到的查询，这就难受了。
• 强制将写之后⽴⻢读的操作转移到主库上。这种属于代码写死了，⽐如⼀些写⼊之后⽴⻢查询的操作，就绑定在⼀起，写死都⾛主库。不推荐，太僵硬了。
• 关键业务读写都⾛主库，⾮关键还是读写分离。⽐如上⾯我举例的⽤户注册这种，可以读写主库，这样就不会有登陆报该⽤户不存在的问题，这种访问量频次应该也不会很多，所以看业务适当调整此类接⼝。

分布式ID生成策略

分布式 id ⽣成⽅案主要有：

• UUID
• 数据库⾃增 ID
• 基于雪花算法（Snowflake）实现
• 百度 （UidGenerator）
• 美团（Leaf）

MySql优化

视图

存储过程

原理

InnoDB存储原理

• ⻚：（16k）最⼩io单元
• 区：（1m）64个⻚，存储引擎空间申请的最⼩单位
• 段：每个段有32个碎⽚⻚，段中的空间⾸先保存在这32个⻚中，超出容量后再以区的⽅式申请空间。段的这种⻚和区混合管理的⽅式，是出于对存储空间尽量节约的⻆度考虑
• 表空间：存储引擎逻辑结构最⾼层，由各个段组成，数据段，索引段，回滚段；碎⽚⻚从碎⽚区中申请，碎⽚区不属于任何段。

MySQL页的读取

• 物理读取：将磁盘中的⻚读取到缓冲池中。
• 逻辑读取：从缓冲池中读取指定的⻚，若逻辑读取的⻚不在缓冲池中，则⾸先通过物理读取将磁盘中的⻚加载到缓冲池中。
• 随机预读：判断某个区域内的⻚是否为热点数据，若满⾜条件则认为该区域内的⻚都可能需要被访问，提前进⾏读取操作，因其实顺序读取，可提⾼数据库读取性能。区域默认32个⻚，阈值默认为9，即32个⻚中的9个⻚为热点数据，根据LRU position来判断。
• 线性预读：访问的⻚是区域边界（32个⻚的第⼀个或最后⼀个）且第⼀次被访问，且该32个⻚中的12个⻚都已经被顺序地访问，则触发线性预读，顺序地读取之后或之前的32个⻚。

区别

char和varchar的区别

• char：定长，长度不够时，采取右补空哥
• varchar：不定长，更节省空间

in和exists的区别

drop、delete、truncate的区别

union all和union的区别

• 返回结果 union all直接返回全部，可能重复；union取唯一值，记录没有重复
• 排序 union all简单合并并返回；union按照字段的顺序进行全量排序