数据库中的面试题你能接几招

数据库中的面试题你能接几招

（附答案，不带答案的面试题都是耍流氓）

1. 事务的特性

ACID:

​ 原子性， 一致性， 隔离性， 持久性

2. innodb如何结果幻读

在不可重复读的隔离级别下使用间隙锁

3. 什么是间隙锁

InnoDB采用MVCC来支持高并发，并且实现了4个标准的隔离级别。其默认的隔离级别是可重复读。当隔离级别是可重复读的时候，是会发生幻读的问题的。那么MySQL如何解决这个问题呢？

在可重复读隔离级别下，MySQL通过间隙锁策略来防止幻读的出现。间隙锁使得InnoDB不仅锁定查询锁涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。但是间隙锁更容易产生死锁问题。

4. 索引的结构

hash, B树， B+ 树

原文地址：https://zhuanlan.zhihu.com/p/98148305

​ 索引一词相信大家都很熟悉，很多人都知道mysql中的索引主要以B+树为主，但是为什么呢？

​ 索引是一种数据结构，用于帮助我们在大量数据中快速定位到我们想要查找的数据，索引最形象的比喻就是图书的目录了。注意这里的大量，数据量大了才显得有意义，如果我们的数据量很少，直接对全数据检索也很快，没有必要费力气建立索引再去查找，索引在mysql数据库中分为三类：B+树索引，Hash索引，全文索引。本次我们主要介绍innodb存储引擎中的B+tree索引。B+树索引需要了解二叉树和平衡二叉树。这里的内容请大家自行了解。

​ 平衡二叉树保证了树的构造是平衡的，当我们插入活删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。平衡二叉树相对于二叉树来说，查询效率更稳定，总体的查找速度也更快。

B树：

​ 因为内存的易失性，一般情况下，我们都会选择将User表中的数据和索引存储在磁盘这种外围设备中，但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以我们应当尽量减少从磁盘中读取数据的次数，另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条读的。如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。如果我们用树这种数据结构作为索引的数据结构，那我们没查找一次就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，我们都知道平衡二叉树可是每个节点只能存储一个键值和数据，那说明什么？说明每个磁盘块仅仅存储一个键值和数据。那我们要存储海量数据呢？可以想象到二叉树的节点非常多，高度可会及其高，我们查找数据时也会进行很多次的磁盘IO，我们查找数据的效率将会极低。

​ 为了解决平衡二叉树的这个弊端，我们应该寻找一种单个节点可以存储多个键值和数据的平衡树，也就是我们接下来要说的B树。

​ B树就是平衡树的意思（Balance Tree),

在这里插入图片描述

​ 图中的p节点为指向自己点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。途中的每个节点称为页，页就是我们上面说的磁盘块，在mysql中数据读取的基本单位都是页，所以我们这里叫页更符合mysql中索引的底层数据结构。从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值和数据，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。

​ 基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树搞很多。假设我们要查找id=28的用户，那么我们在上图B树中查找的流程如下：

​ 先找到根节点也就是页1，判断28在键值17和35之间，那么我们根据页1中的P2指针找到页3

​ 将38和页面中的键值比较，28在26和30之间，我们根据页3中的p2指针找到页8

​ 将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用于信息为（28，bv）

B+树：

​ B+树是对B树的进一步优化，先来看一下结构图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kDUueSXC-1593145457913)(../../Desktop/md/assets/1582513238372.png)]

​ 根据上图我们来看一下和B树有什么不同：

1. B+树的非叶子节点是不存储数据的，仅存储键值，而B树节点中不仅存储键值也会存储数据。之所以这么做是因为在数据库中页的大小是固定的，innodb中页的大小默认是16k,如果不存储数据，那么就会存储更多的键值，相当于数据阶数（节点的子节点数）就会更大，树就会更矮，如此一来，我们查找数据进行的磁盘io操作次数会再次减少，查询的效率会更快，另外B+树的阶数是等于键值数量的，如果我们的B+树一个节点可以存储1000个键值，那么三层B+树可以存储1000x1000x1000=10亿个数据。一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘的操作。

1. 因为B+树索引的所有数据均存储在叶子节点上，而且数据时按照顺序排列的，那么B+树使得范围查找，排序查找，分组查找一级去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。其实B+树中各个页之间是通过双向链表链接的，叶子节点中的数据时通过单向链表链接的。其实上面的B树我们也可以对各个节点加上链表，其实这些不是他们之间的区别，是因为在mysql的innodb存储引擎中，索引就是这样存储的。也就是说图上的B+树索引就是innodb中B+树索引真正的实现方式，准确的说，应该是聚集索引。

通过上图我们可以看到，在innodb中，我们通过数据页之间通过双向链表连接，以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

​ MYISAM中的B+树索引实现和innodb中的略有不同， 在MYISAM中，B+树索引的叶子节点并不存储数据，而是存储数据的文件地址。

5. 聚集索引和非聚集索引

在mysql中，B+树索引按照存储方式的不同分为聚集索引和非聚集索引。

聚集索引： 以innodb作为存储引擎的表，表中的数据都会有一个主键，即使你不想创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，二B+树的键值就是主键，在B+树的叶子节点中，存储了表中所有的数据，这种以主键作为B+树索引的键值二构建的B+树索引，我们称之为聚集索引

非聚集索引：以主键以外的列主作为键值构建的B+树索引，我们称之为非聚集索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

举例： 根据聚集索引查找数据：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hJ1M4dFn-1593145457915)(../../Desktop/md/assets/1582524182456.png)]

假设我们要查找id>=18并且id<40的数据，对应的sql为select * from user where id>=18 and id <40 其中id为主键，具体的查找过程如下：

1. 一般根节点都是常驻内存的，也就是说页1已经在内存中了，因此不需要到磁盘中读取数据，直接从内存中读取即可。从内存中读取到页1，要查找这个id>=18 and id <40的范围值，我们首先需要找到id=18的键值，从页1中我们可以找到键值18，此时我们需要根据指针p2，定位到页3.
2. 要从页3中查找数据，我们就需要拿着p2指针去磁盘中进行读取页3，从磁盘中读取页3后降页3放入内存中，然后进行查找，我们可以找到键值18，然后在拿到页3中的指针p1，定位到页8
3. 同样的页8也不在内存中，我们需要再去磁盘中奖页8也读取到内存中，因为页中的数据是链表进行连接的，而且键值是按照顺序存放的，此时可以根据二分查找法定位到键值18.此时因为已经找到数据页了，此时我们已经找到一条满足条件的数据了，就是键值18对应的数据。因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页8中的数据依次遍历查找并匹配满足条件的数据。我们可以一直找到键值为22的数据，然后页8中就没有数据了，此时我们需要拿着页8中的p指针去读取页9中的数据。
4. 因为页9不在内存中，就又会加载页9到内存中，并通过和页8一样的方式进行数据的查找，知道将页12加载到内存中，发现41大于40，此时不满足条件，那么查找到此为止。最终我们查到了所有满足条件的数据。

在这里插入图片描述

利用非聚集索引查找：

在这里插入图片描述

读者看到这张图的时候可能会蒙，这是啥东西啊？怎么都是数字。 如果有这种感觉，请仔细看下图中红字的解释。什么？还看不懂？那我再来解释下吧。首先，这个非聚集索引表示的是用户幸运数字的索引（为什么是幸运数字？一时兴起想起来的:-)），此时表结构是这样的。 idnameluckyNum1zs232ls7 在叶子节点中，不在存储所有的数据了，存储的是键值和主键。 对于叶子节点中的x-y，比如1-1。左边的1表示的是索引的键值，右边的1表示的是主键值。如果我们要找到幸运数字为33的用户信息，对应的sql语句为select * from user where luckNum=33。 查找的流程跟聚集索引一样，这里就不详细介绍了。我们最终会找到主键值47，找到主键后我们需要再到聚集索引中查找具体对应的数据信息，此时又回到了聚集索引的查找流程。 下面看下具体的查找流程图：

在MyISAM中，聚集索引和非聚集索引的叶子节点都会存储数据的文件地址。

6. 如何显示的给innodb添加读写锁

select… lock in share mode;

select …for update;

7. 什么是脏读，不可重复读，幻读，丢失修改

• 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
• 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
• 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

8. 不可重复读和幻读的区别：

不可重复读的重点是修改，幻读的重点在于新增或者删除。

例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导 致A再读自己的工资时工资变为 2000；这就是不可重复读。

例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读。

9. Mvcc和 事务隔离级别的关系

原文：https://www.zhihu.com/question/279538775

早期数据库不论读取还是写入，都用锁来实现。但是锁会带来性能的问题。人们尝试各种优化方案。写入和读取的优化方式不同。

对于数据库写入操作，没有特别好的办法，因为无论如何要避免并发修改一个数据，就得靠锁。不同的数据库对于写入操作都会加悲观锁（比如MySQL是X锁）。为了避免X锁带来的性能问题，人们在合适的场合会选择用乐观锁来优化。有的数据库内建乐观锁，但是有的没有（比如MySQL就没有），所以需要开发人员自己在数据表里加version列，自己写业务代码实现。

顺便提一句，乐观锁并不一定总是比悲观锁性能表现更好，这要看竞争的程度。如果数据访问竞争的非常厉害，乐观锁只会让CPU和IO白白浪费而已。

对于读取，优化就是MVCC。现在主流的商业数据库都是基于MVCC，如MySQL InnoDB和Postgres。MVCC的意思用简单的话讲就是对数据库的任何修改的提交都不会直接覆盖之前的数据，而是产生一个新的版本与老版本共存，使得读取时可以完全不加锁。这样读某一个数据时，事务可以根据隔离级别选择要读取哪个版本的数据。过程中完全不需要加锁。

这样，实现两个隔离级别就非常容易：

• Read Committed - 一个事务读取数据时总是读这个数据最近一次被commit的版本
• Repeatable Read - 一个事务读取数据时总是读取当前事务开始之前最后一次被commit的版本（所以底层实现时需要比较当前事务和数据被commit的版本号）。

举个简单的例子：

1. 一个事务A（txnId=100）修改了数据X，使得X=1，并且commit了
2. 另外一个事务B（txnId=101）开始尝试读取X，但是还X=1。但B没有提交。
3. 第三个事务C（txnId=102）修改了数据X，使得X=2。并且提交了
4. 事务B又一次读取了X。这时

• 如果事务B是Read Committed。那么就读取X的最新commit的版本，也就是X=2
• 如果事务B是Repeatable Read。那么读取的就是当前事务（txnId=101）之前X的最新版本，也就是X被txnId=100提交的版本，即X=1。

注意，这里B不论是Read Committed，还是Repeatable Read，都不会被锁，都能立刻拿到结果。这也就是MVCC存在的意义。

在基于MVCC的数据库实现中，根本就不需要出现Read Uncommitted这种情况。Read Uncommitted是早期数据库，读写都基于锁进行实现的产物。在实际业务中Read Uncommitted毫无意义（如果真有意义，你咋不去用NoSQL数据库？）因此：

• 对于Postgres，Read Committed和Read Uncommitted是一样的。
• 对于Oracle，压根就没有Read Uncommitted这个级别。

所以从实际的角度出发，我想所有人都忘记有“Read Uncommitted”这件事。

MVCC并不是万灵药。大量的业务问题的关键点在于，你在提交一个事务那一刹那，你提交事务的所有修改依赖的读取是否都还有效。对于这种场景，无论是Read Committed还是Repeatable Read都没有什么卵用。比如扣库存就是这样典型的业务场景。

在这种场景下

• 在MySQL InnoDB，使用者会使用select ... for update手工加锁。或者干脆用Serializable隔离级别。
• 在Postgres，Postgres的Repeatable Read在提交时会提供一个“提交的修改的依赖是否被修改“的检测（好绕口，但就是这个意思）。如果依赖已经被改掉了，当前事务提交一定会失败。

10. mysql中的数据库引用有哪几种

MyISAM: 不支持外键； 表锁，插入数据时锁整个表，查表总行数时，不需要全表扫描

Innodb： 支持外键，行锁， 查表总行数时，全表扫描

11. 索引覆盖与回表

我们上面提到了，数据库的索引分为了聚集索引和非聚集索引，聚集索引一般都是我们的主键索引，通过主键索引，在b+树的叶子节点上可以直接得到数据。 而非聚集索引的叶子节点上存储的并不是行数据本身，而是聚集索引（主键）。所以当我们使用非聚集索引查询数据的时候，率先得到的是数据的聚集索引（主键），然后通过聚集索引再去定位数据项，这个过程就叫做回表。所以聚集索引查询数据是不需要回表的，查询效率更高。

索引覆盖： 可以简单理解成我们要查的数据直接就是索引， 不需要再去回表。

比如表中age,name 都是普通索引。

我们查询 select age, name from table; 只需要扫描索引树就完全可以得到结果，也不需要获取整条数据，所需要的数据在索引中全部包括了，这就是索引覆盖。

12. 索引的优缺点，什么时候使用索引，什么时候不能使用索引

索引最大的好处是提高查询速度， 缺点是更新数据时效率低，因为要同时更新索引 对数据进行频繁查询进建立索引，如果要频繁更改数据不建议使用索引

13. 索引的底层实现（B+树，为何不采用红黑树，B树）重点

树 区别 红黑树 增加，删除，红黑树会进行频繁的调整，来保证红黑树的性质，浪费时间 B树也就是B-树 B树，查询性能不稳定，查询结果高度不致，每个结点保存指向真实数据的指针，相比 B+树每一层每屋存储的元素更多，显得更高一点。 B+树 B+树相比较于另外两种树,显得更矮更宽，查询层次更浅

14. 索引最左前缀问题：

如果对三个字段建立联合索引，如果第二个字段没有使用索引，第三个字段也使用不到索引了

15. 索引分类及索引失效条件

普通索引：最基本的索引，没有任何限制

唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

主键索引：它是一种特殊的唯一索引，不允许有空值。

前文索引: 针对较大的数据，生成全文索引很耗时好空间。

组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则

索引失效条件：

​ 条件是or,如果还想让or条件生效，给or每个字段加个索引 ​ like查询，以%开发 ​ 内部函数 ​ 对索引列进行计算 ​ is null不会用，is not null 会用

16. char 和 varchar区别

Char: 用于字符长度固定的，比如性别， 手机号

varchar：用于字符串长度经常变化的

17. 数据库三范式

强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。

非主键属性，完全依赖于主键属性。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。

任何非主属性不依赖于其它非主属性。非主键属性无传递依赖

18. sql优化

大原则： 尽量命中索引，避免全表扫描。

1. 联合索引要注意最左前缀的原则
2. 索引上不要进行任何操作（计算，函数，类型转换），否则会导致全表扫描
3. 尽量使用索引覆盖（只访问索引的查询），减少select*
4. like以通配符开始，索引会失效，导致全表扫描。如 name like “%abc”
5. 字符串查询要使用单引号，否则导致索引失效 如where name = 1000
6. 少用in或or, 使用时不一定会命中索引，mysql内部会进行评估，看是否使用索引
7. 查询尽量不要使用select * 而是select 具体字段
8. 如果知道查询结果只有一条或者只要最大/最小一条，建议limit 1
9. 尽量避免在where字句中使用or连接条件（可使用union all）
10. Limit 优化： order by + 索引 + limit
11. 使用where条件查询，避免返回多余的行
12. 不要在索引上使用内置函数，会导致索引失效
13. 使用关联的时候，保证主表的数据量尽量小
14. 尽量避免在where字句中使用！= 或 <>

19. 介绍一下悲观锁和乐观锁

乐观锁：对加锁持有一种乐观的态度，即先进行业务操作，不到最后一步不进行加锁，"乐观"的认为加锁一定会成功的，在最后一步更新数据的时候在进行加锁。乐观锁的实现方式一般为每一条数据加一个版本号，update时判断这个版本号是否和数据库里的一致，提交数据前后是否冲突。update是单线程的，及如果一个线程对一条数据进行update操作，会获得锁，其他线程如果要对同一条数据操作会阻塞，直到这个线程update成功后释放锁。

悲观锁：悲观锁对数据加锁持有一种悲观的态度。因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制。备注，在MySQL中使用悲观锁，必须关闭MySQL的自动提交，set autocommit=0。MySQL默认使用自动提交autocommit模式，也即你执 行一个更新操作，MySQL会自动将结果提交

20. 介绍一下排它锁和共享锁

首先说明：数据库的增删改操作默认都会加排他锁，而查询不会加任何锁。都是行级锁 共享锁：对某一资源加共享锁，自身可以读该资源，其他人也以读该资源（也可以再继续加共享锁，即 共享锁可多个共存），但无法修改。要想修改就必须等所有共享锁都释放完之后。语法为： select * from table lock in share mode

排他锁：对某一资源加排他锁，自身可以进行增删改查，其他人无法进行任何操作。语法为： select * from table for update

21. redolog 和 undolog

1. redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
2. undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。