本文共 18423 字，大约阅读时间需要 61 分钟。

十二、数据库常见面试题目

12.1 事务的四大特性

ACID特性。

• 原子性(Atomic)： 要么执行，要么不执行；
• 一致性(Consistency)： 事务前后，数据总额一致；
• 隔离性(Isolation)： 所有操作全部执行以前其它会话不能看到过程；
• 持久性(Durability)： 一旦事务提交，对数据的改变就是永久的。

12.2 数据库隔离级别

多个事务读取可能会遇到以下问题：

• 脏读： 事务B读取事务A还没有提交的数据；
• 不可重复读： 一行被检索两次，并且该行中的值在不同的读取之间不同时
• 幻读： 当在事务处理过程中执行两个相同的查询，但第二个查询返回的行集合与第一个查询不同时。

SQL标准定义了四个隔离级别：

• READ-UNCOMMITED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读、或不可重复读；（不加共享锁）
• READ-COMMITED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。（读取的加共享锁，执行完该语句则释放）
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被事务本身自己所修改，可以阻止脏读和不可重复读的发生，但幻读仍有可能发生。（读取加共享锁，事务结束释放锁）
• SERIABLIZABLE(可串行化)： 最高的隔离级别，完全服务ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以阻止脏读、不可重复读以及幻读。（从事务的开始到结束，都加锁，完成则释放）

InnoDB在可重复读隔离级别下是如何避免幻读的？

• 表象：快照读(非阻塞读) --伪MVCC；
• 内在：next-key锁(行锁+gap锁)；

innodb的默认事务隔离级别是rr（可重复读）。它的实现技术是mvcc。基于版本的控制协议。该技术不仅可以保证innodb的可重复读，而且可以防止幻读。但是它防止的是快照读，也就是读取的数据虽然是一致的，但是数据是历史数据。如何做到保证数据是一致的（也就是一个事务，其内部读取对应某一个数据的时候，数据都是一样的），同时读取的数据是最新的数据。innodb提供了一个间隙锁的技术。也就是结合grap锁与行锁，达到最终目的。当使用索引进行插入的时候，innodb会将当前的节点和上一个节点加锁。这样当进行select的时候，就不允许加x锁。那么在进行该事务的时候，读取的就是最新的数据

间隙锁(Gap Lock )

**Gap Lock的唯一目的就是阻止其他事务插入到间隙中。**Gap Lock可以同时存在，不同的事务可以同时获取相同的Gap Lock，并不会互相冲突。

next-key Lock

Record Lock+Gap Lock，如果一个事务在记录R上的某个索引有共享/互斥锁,也会对其前面一个范围加锁

根据索引会形成一个个左开右闭的一个区间，根据查询的条件其所在的区间，并且包括其后的区间。

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：

（1）防止间隙内有新数据被插入

（2）防止已存在的数据，更新成间隙内的数据

为什么说InnoDB的可重复读避免了“幻读”是不准确的？

首先看看什么是幻读

幻读。解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。但是，如果另一个事务同时提交了新数据，本事务再更新时，就会“惊奇的”发现了这些新数据，貌似之前读到的数据是“鬼影”一样的幻觉。

在mysql中，提供了两种事务隔离技术，**第一个是mvcc，第二个是next-key技术。**这个在使用不同的语句的时候可以动态选择。不加lock inshare mode之类的快照读就使用mvcc。否则 当前读使用next-key。mvcc的优势是不加锁，并发性高。缺点是不是实时数据。next-key的优势是获取实时数据，但是需要加锁。

因为默认的查询语句是快照读，而要避免幻读需要使用当前读。可以通过使用当前读来规避幻读问题。

当前读和快照读

• **快照读:**最简单的select操作,属于快照读,不加锁
  select * from table where id = 1;
• **当前读:**特殊的读与增删改操作,属于当前读,会读取数据库原本的数据,加锁
  select * from table where xxx lock in share mode; (共享锁)
  select * from table where xxx for update;
  insert into table values()
  update table set xxx where xxx
  delete form table where xxx

以上语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

实例：

在数据库中存在user表并且有4条数据

1.select快照读(照片):

当你执行select *之后，在A与B事务中都会返回4条一样的数据，这是不用想的，当执行select的时候，innodb默认会执行快照读，相当于就是给你目前的状态找了一张照片，以后执行select 的时候就会返回当前照片里面的数据，当其他事务提交了也对你不造成影响，和你没关系，这就实现了可重复读了，那这个照片是什么时候生成的呢？不是开启事务的时候，是当你第一次执行select的时候，也就是说，当A开启了事务，然后没有执行任何操作，这时候B insert了一条数据然后commit,这时候A执行 select，那么返回的数据中就会有B添加的那条数据……之后无论再有其他事务commit都没有关系，因为照片已经生成了，而且不会再生成了，以后都会参考这张照片。

update、insert、delete 当前读

当你执行这几个操作的时候默认会执行当前读，也就是会读取最新的记录，也就是别的事务提交的数据你也可以看到，这样很好理解啊，假设你要update一个记录，另一个事务已经delete这条数据并且commit了，这样不是会产生冲突吗，所以你update的时候肯定要知道最新的信息啊。在这里介绍一下update的过程吧，首先会执行当前读，然后把返回的数据加锁，之后执行update。加锁是防止别的事务在这个时候对这条记录做什么，默认加的是排他锁，也就是你读都不可以，这样就可以保证数据不会出错了。但注意一点，就算你这里加了写锁，别的事务也还是能访问的，是不是很奇怪？数据库采取了一致性非锁定读，别的事务会去读取一个快照数据。

  innodb默认隔离级别是RR， 是通过MVVC来实现了，读方式有两种，执行select的时候是快照读，其余是当前读，所以，mvvc不能根本上解决幻读的情况

MVCC

MySQL InnoDB存储引擎 ，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。MVCC最大的好处：读不加锁，读写不冲突。 在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

在mysql中，提供了两种事务隔离技术，第一个是mvcc，第二个是next-key技术。这个在使用不同的语句的时候可以动态选择。不加lock inshare mode之类的就使用mvcc。否则使用next-key。mvcc的优势是不加锁，并发性高。缺点是不是实时数据。next-key的优势是获取实时数据，但是需要加锁。同时需要注意几点：1.事务的快照时间点是以第一个select来确认的。所以即便事务先开始。但是select在后面的事务的update之类的语句后进行，那么它是可以获取后面的事务的对应的数据。2.mysql中数据的存放还是会通过版本记录一系列的历史数据，这样，可以根据版本查找数据。

12.2 数据库事务隔离模式 MVCC next-key (InnoDB可重复读隔离级别下如何避免幻读)

四种隔离级别(由低到高)

Read Uncommitted读未提交：可以看到其它事务未提交的内容；

Read Commited读已提交：可以看到其它事务已提交的内容；

Repeatable Read可重复读：事务开始时和事务结束时读到的数据完全相同；

Serializable串行：事务必须逐步执行，后来的会排队。

隔离级别下的问题

前提条件：同时开启A和B两个事务；

脏读：

事务查询id为1的数据，num字段为1

B事务将id为1的数据num更新为2，但未commit 事务查询id为1的数据，num字段变为2 B回滚，则A读到的数据为脏数据

不可重复读：

事务查询id为1的数据，num字段为1

B事务将id为1的数据num更新为2，commit 事务查询id为1的数据，num字段变为2 A事务前后两次读到的同一条记录num字段不同，不可重复读

幻读：

A事务查询id >=1 and id <=3的数据，得到id=1和id=3两条数据（注意：没有id=2的数据）

B事务插入id=2的数据 A事务再查询id >=1 and id <=3的数据，发现多了一条id=2的数据，即两次查询数据的行数不同

各种隔离模式下会出现的问题

读未提交：脏读、不可重复读、幻读；

读已提交：不可重复读、幻读；

可重复读：幻读；

间隙锁(gap-key)

在一个事务中select for update查询某条记录，会锁定该条记录的前后空行，什么叫空行呢，看个例子就知道了

id num

1 2 2 4 3 6 4 7 执行select * from table where num = 4 for update

insert into table (’’, 3)和insert into table (’’, 5)都会被阻塞，即4前后的空行都无法插入

next-key

由于gay-key只能锁定记录之间的间隙，但是我们上面查询num=4的行也不能被更改，索引该行也会被加行锁，此时这种既能加行锁，又能加间隙锁的，称之为next-key。

MVCC

事务A：查询num>=2 and num<=4的记录，但是不加for update！不加for update！不加for update！

事务B：insert into table (’’, 3)，不会被阻塞，不会被阻塞，不会被阻塞！然后commit 事务A：查询num>=2 and num<=4的记录，和之前查询相同 事务A：提交 事务A：查询num>=2 and num<=4的记录，可以查询到num=3的记录，此时返回3条结果 原理：假如事务A在开启的时候版本号为2，当更改或者插入数据后，该条记录的版本号+1，也就是说事务B插入num=3的记录的版本号为3，事务A在提交前的所有select都只能查询到版本号<=2的记录，也就是说不会产生上面说的幻读。

MVCC和next-key

其实，innodb采用next-key + MVCC去解决幻读问题的：

在查询加for update时，会用next-key解决幻读问题，新的insert和update会阻塞

在查询不加for update时，会用MVCC解决幻读问题，新的insert和update不会阻塞

事务

ACID；

隔离级别；

不同隔离级别下的问题；

InnoDB如何避免幻读？

快照读：不加锁 mvcc的机制

当前读：加锁 next-key的机制

12.3 MYSQL的两种存储引擎区别(事务、锁级别等等)，各自的适用场景

MyISAM与InnoDB关于锁方面的区别是什么？

• MyISAM默认用的是表级锁，不支持行级锁；
• InnoDB默认用的是行级锁，也支持表级锁。

MyISAM和InnoDB适合的场景有哪些？

• MyISAM适合的场景
  • 频繁执行全表count语句；
  • 对数据进行增删改的频率不高，查询非常频繁；
  • 没有事务；
• InnoDB适合的场景
  • 数据增删改查都相当频繁；
  • 可靠性要求比较高，要求支持事务；

MyIsam和InnoDB引擎的区别：

• InooDB支持事务，MyISAM不支持事务；
• InnoDB支持外键，MyISAM不支持；
• InnoDB是聚集索引，MyISAM是非聚集索引；
• InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描，而MyISAM用一个变量保存了整个表的行数，执行上述语句只需读出该变量即可，速度很快。
• InooDB最小的锁粒度是行锁，MyISAM最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。

(事务、锁、索引三方面来)

MyISAM索引和InnoDB索引实现：

(1) MyISAM索引实现：

非聚集性索引

a.主键索引

MyISAM引擎使用B+树作为索引结果，叶子结点的data域存放的是数据记录的地址。下图为MyISAM表的主索引，Col1为主键。

b.辅助索引

在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。下图在Col2上建议一个辅助索引。

同样是一棵B+树，data域保存数据记录的地址。因此，MYISAM中索引检索的算法为首先按照B+ Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后 以data域的值为地址，读取相应数据记录。

(2) InnoDB

聚集性索引

a.主键索引

同样是B+树，实现方式却完全不同。InnoDB表数据文件本身就是一个索引结构，树的叶节点data域保存了完整的数据记录，这种索引叫做聚集索引。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键(MyISAM可以没有)，如果没有显式指定，则mysql会自动选择一个可以唯一标识数据记录的列作为主键。如果不存在这种列，则mysql自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

b.辅助索引

nnoDB的所有辅助索引都引用主键作为data域。下图为定义在Col3上的一个辅助索引

因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引也会包含主键列，所以如果主键定义的比较大，其他索引也将很大。InnoDB 不会压缩索引。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

MyIsam和InooDB应用场景

MyISAM：以读写插为主的应用程序，比如博客系统、新闻门户网站；

Innodb：更新(删除)操作频率也高，或者要保证数据的完整性；并发量高、支持事务和外键。比如OA自动化办公系统。

如何选择MyISam和InnoDB引擎？

• 是否要支持事务，如果要选择InnoDB；如果不需要考虑MyISAM
• 如果表中绝大数都只是读查询，可以考虑MyISAM；如果既考虑读也有写，请使用InnoDB。
• 系统崩溃后，MyISAM恢复起来更困难，能否接受；
• MySQL5.5 版本开始Innodb开始成为Mysql的默认引擎。

InnoDB为什么推荐使用自增ID作为主键？

自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂(对比使用UUID)。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

• 如果主键为自增ID的话，MySQL在写满一个数据页的时候，直接申请另一个新数据页接着写就可以了；
• 如果主键是非自增ID，为了确保索引有序，MySQL就需要将每次插入的数据都放到合适的位置上。

INNODB引擎的4大特性

插入缓冲 二次写 自适应哈希索引 预读

12.4 索引有B+索引和hash索引区别？

索引的灵感来自字典。主键、唯一键、普通键等只要有能够区分信息的都可以成为索引。

索引	区别
Hash	hash索引，等值查询效率高，不能排序,不能进行范围查询
B+	数据有序,范围查询

B+树索引和哈希索引的明显区别是：

• 如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值。如果键值不是唯一的，那么就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
• 如果是范围查询检索，哈希索引就毫无用武之地。因为原先是有序的键值，经过哈希算法后，就有可能变成不连续的，就没办法再利用索引完成范围查询检索；
• 并且索引也没办法利用索引完成排序，以及like’xxx%'这样的部分模糊查询
• 哈希索引也不支持多列联合索引的最左匹配规则；
• B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下， 哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

Hash索引对比B树的索引最大问题就是：只能等值查询效率高；而对于数据的范围查询不可以，而且没法利用索引完成排序；

12.5 聚集索引和非聚集索引区别？密集索引和稀疏索引的区别是什么？

聚集索引和非聚集索引的区别：

索引	区别
聚集索引	数据按索引顺序存储，叶子结点中存储了真实的物理数据
非聚集索引	叶子结点中存储指向真实数据行的指针

密集索引和稀疏索引的区别:

• 密集索引文件中的每个搜索码值都对应一个索引值；
• 稀疏索引文件只为索引码的某些值建立索引项；

InnoDB:

• 若一个主键被定义，则该主键作为密集索引；
• 若没有主键被定义，该表的第一个唯一非空索引作为密集索引；
• 若不满足以上条件，innodb内部会生成一个隐藏主键(密集索引)。
• 非主键索引存储相关键位和其对应的主键值，包含两次查找。

12.6 索引的优缺点？什么时候使用索引？什么时候不能使用索引？

索引的好处：提高查询速度；

索引的坏处：更新数据时效率低，因为要同时更新索引；

对数据进行频繁查询时建立索引，如果要频繁更改数据不建议使用索引。

12.7 InnoDB索引和MyISAM索引的区别？

• 主索引的区别：InnoDB索引是聚集性索引，叶节点的数据域保存了完整的数据记录；MyISAM索引是非聚集性索引，叶节点的数据域存放的是数据记录的地址；即InnoDB的数据文件本身就是主索引文件，而MyISAM的主索引和数据是分开的。
• 辅助索引的区别：InnoDB的辅助索引的书记居于存储相应记录主键的值而不是地址。**首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。**而MyISAM的辅助索引和主索引没有多大区别。

12.8 为什么MySQL索引要使用B+树，而不是B树，红黑树？

在Mysql中，无论是Innodb还是MyISAM引擎，都使用了B+树做索引结构(这里先不考虑Hash索引)。那么我们从最普通的二叉树开始，从而说明Mysql为什么选择B+树作为索引结构。

(1) 二叉查找树

二叉查找树(BST,binary search Tree)也叫二叉排序树，在二叉树的基础上满足：任意结点的左子树上的所有结点值不大于根节点的值，任意结点的右子树上所有结点值不小于根节点的值。

但如果采用二叉查找树作为索引，并且把id作为索引且id自增，那么二叉查找树就变成了一个单支树，相当于链表查询。即BST可能长歪而变得不平衡了

(2) 平衡二叉树

为了解决上述二叉搜索树的问题，引入了平衡得到二叉树。

AVL树是严格的平衡二叉树，所有节点的左右子树高度差不能超过1；AVL树查找、插入和删除在平均和最坏的情况下都是O(logn).

AVL实现平衡的关键在于旋转操作：插入和删除可能破坏二叉树的平衡，此时需要通过一次或多次树旋转来重新平衡这棵树。当插入数据时，最多只需要1次旋转(单旋转或双旋转)；但是当删除数据时，会导致树失衡，AVL需要维护从被删除节点到根节点这条路径上所有节点的平衡，旋转的量级为O(lgn)。

由于旋转的耗时，AVL树在删除数据时效率很低。 在删除操作较多时，维护平衡所需的代码可能高于其带来的好处，因此AVL实际应用并不广泛。

(3)红黑树

与AVL树相比，红黑树并不追求严格的平衡，而是大致的平衡：只是确保从根到叶子的最长的可能路径不多于 最短的可能路径的两倍长。 实现上遵守以下规则：

• 节点是红色或黑色；
• 根节点是黑色；
• 所有叶子是黑色的；
• 每个红色节点必须有两个黑色的子节点(从每个叶子到根的所有路径上不能有两个连续的红色节点)；
• 从任一节点到每个结点的所有简单路径都包含相同数目的黑色结点

与AVL树相比 ，红黑树丶查询效率会有所下降 ，这是因为树的平衡性变差，高度更高了。但是红黑树的删除效率大大提高了。此外因为红黑树同时引入了颜色，当插入或删除数据时，只需要进行O(1)次数的旋转以及变色就能保证基本的平衡，不需要像AVL树进行O(logn)次数的旋转。

当然对于数据在内存中的情况如(TreeMap和HashMap)，红黑树的表现是非常优异的。但是对于数据在磁盘等辅助存储设备中的情况(如Mysql等数据库)，红黑树还是并不擅长，因为红黑树还是有点高。因为当数据在磁盘中，磁盘IO会成为最大的性能瓶颈，设计的目标应该是尽量减少IO次数；而树的高度越高，增删改查所需要的IO次数也越多，会严重影响性能。

(4)B树

B树也被成为B-树，是为磁盘等辅存设备设计的多路平衡查找树，与二叉树相比，B树的每个非叶节点可以有多个子树。因此，当总节点数量相同时，B树的高度远远小于AVL树和红黑树，磁盘IO次数大大减少。

对于一棵m阶B树，需要满足以下条件：

B树的优势除了树高小，还有对访问局部性原理的利用。所谓局部性原理，是指当一个数据被使用时，其附近的数据有较大概率在短时间内被使用。B树将键相近的数据存储在同一个节点，当访问其中某个数据时，数据库会将该整个节点读到缓存中；当它临近的数据紧接着被访问时，可以直接在缓存中读取，无需进行磁盘IO；换句话说，B树的缓存命中率更高。

(5)B+树

B+树为B树的一种变形，m阶B+树有如下性质：

• 每个结点的关键字个数与孩子个数相等
• 除根节点之外，每个内部结点有m/2到m个孩子

B+树也是多路平衡查找树，其与B树的区别在于：

• B树中每个节点（包括叶节点和非叶节点）都存储真实的数据，B+树中只有叶子节点存储真实的数据，非叶节点只存储键。在MySQL中，这里所说的真实数据，可能是行的全部数据（如Innodb的聚簇索引），也可能只是行的主键（如Innodb的辅助索引），或者是行所在的地址（如MyIsam的非聚簇索引）。
• B+树的叶节点之间通过双向链表链接。
• B树中的非叶节点，记录数比子节点个数少1；而B+树中记录数与子节点个数相同。
• B树中一条记录只会出现一次，不会重复出现，而B+树的键则可能重复重现，一定会在叶节点出现，也可能在非叶节点重复出现。

优势：

• 更少的IO次数： B+树的非叶节点只包含键，而不包含真实数据，因此每个节点存储的记录个数比B数多很多（即阶m更大），因此B+树的高度更低，访问时所需要的IO次数更少。 此外，由于每个节点存储的记录数更多，所以对访问局部性原理的利用更好，缓存命中率更高。
• 更适于范围查询： 在B树中进行范围查询时，首先找到要查找的下限，然后对B树进行中序遍历，直到找到查找的上限；而B+树的范围查询，只需要对链表进行遍历即可。
• 更稳定的查询效率： B树的查询时间复杂度在1到树高之间(分别对应记录在根节点和叶节点)，而B+树的查询复杂度则稳定为树高，因为所有数据都在叶节点。

总结：

• 二叉查找树(BST)：解决了排序的基本问题，但是由于无法保证平衡，可能退化为链表；
• 平衡二叉树(AVL)：通过旋转解决了平衡的问题，但是旋转操作效率太低；
• 红黑树：通过舍弃严格的平衡和引入红黑节点，解决了AVL旋转效率过低的问题，但是在磁盘等场景下，树仍然太高，IO次数太多；
• B树：通过将二叉树改为多路平衡查找树，解决了树过高的问题；
• B+树：在B树的基础上，将非叶节点改造为不存储数据的纯索引节点，进一步降低了树的高度；此外将叶节点使用指针连接成链表，范围查询更加高效。

• 更少的IO次数；
• 更适用于范围查询；
• 更稳定的查询效果；

面试题目：

问题一、Mysql中存储索引用到的数据结构是B+树，B+树的查询时间跟树的高度有关，是log(n)，如果是用hash存储，那么查询时间是O(1).既然hash比B+树更快，为什么mysql用B+树来存储索引？

答案：

• 从内存角度上来说，数据库中的索引一般都在磁盘上 ，数据量大的情况可能无法一次性装入内存中， B+树的设计可以允许数据分批加载；
• 从业务场景来说，如果是等值查询，只选择一个数据那确实是hash 更快，但是数据库中经常会选中多条数据，那么这时候由于B+树索引有序，并且有链表相连，其查询效率比hash快很多。并且B+树索引可以进行范围查询检索。

Hash索引的缺点：

• j仅仅能满足“=”，“IN”,不能使用范围查询；
• 无法被用来避免数据的排序操作；
• 不能利用部分索引键查询；
• 不能避免表扫描；
• 遇到大量Hash值相等的情况后性能并不一定比B+Tree高

是否还有别的结构呢？BitMap索引。

问题二、为什么不用红黑树或者二叉排序树？

答案：树的查询时间跟树的高度有关，B+树是一棵多路搜索树可以降低树的高度，提高查询后啊效率。

问题3：既然增加树的路数可以降低树的高度，那么无限增加树的路数是不是可以有最优的查找效率？

答：这样会形成一个有序数组，文件系统和数据库的索引都是存在硬盘上的，并且如果数据量大的话，不一定能一次性加载到内存中。有序数组没法一次性加载进内存，这时候B+树的多路存储威力就出来了，可以每次加载B+树的一个结点，然后一步步往下找，

问题4：在内存中，红黑树比B树更优，但是涉及到磁盘操作B树就更优了，那么你能讲讲B+树吗？

B+树是在B树的基础上进行改造，它的数据都在叶子结点，同时叶子结点之间还加了指针形成链表。

问题5：为什么B+树要这样设计？

答：这个跟它的使用场景有关，B+树在数据库的索引中用得比较多，数据库中select数据，不一定只选一条，很多时候会选中多条，比如按照id进行排序后选100条。如果是多条的话，B-树需要做局部的中序遍历，可能要跨层访问。而B+树由于所有数据都在叶子结点不用跨层，同时由于有链表结构，只需要找到首尾，通过链表就能把所有数据取出来了。

问题6：数据库索引为什么要用b+树而不用红黑树？

AVL 树和红黑树这些二叉树结构的数据结构可以达到最高的查询效率这是毋庸置疑的。

那么，数据库索引为什么不用AVL树或者红黑树呢？

AVL 数和红黑树基本都是存储在内存中才会使用的数据结构，那磁盘中会有什么不同呢？

这就要牵扯到磁盘的存储原理了

操作系统读写磁盘的基本单位是扇区，而文件系统的基本单位是簇(Cluster)。

也就是说，磁盘读写有一个最少内容的限制，即使我们只需要这个簇上的一个字节的内容，我们也要含着泪把一整个簇上的内容读完。

那么，现在问题就来了

一个父节点只有 2 个子节点，并不能填满一个簇上的所有内容啊？那多余的内容岂不是要浪费了？我们怎么才能把浪费的这部分内容利用起来呢？哈哈，答案就是 B+ 树。

由于 B+ 树分支比二叉树更多，所以相同数量的内容，B+ 树的深度更浅，深度代表什么？代表磁盘 io 次数啊！数据库设计的时候 B+ 树有多少个分支都是按照磁盘一个簇上最多能放多少节点设计的啊！

12.9 B+树的实现

B树的特点：

• 根节点至少有两个孩子；
• 每个结点中的关键字至少在m/2-1<=<=m-1
• 除根节点外，每个结点的孩子数目在m/2-m之间
• 叶子结点都在同一层

B+树的特点：

• 每个结点中的关键字跟结点的孩子数目一样，都是在m/2-m之间
• 叶子结点有一条链路相连
• 内部结点都只是存了key，不存value值

12.10 为什么要使用B+树？

索引查找过程中就要产生磁盘I/O消耗,主要看IO次数，和磁盘存取原理有关。

根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理， 将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入 局部性原理与磁盘预读

12.11 如何定位并优化慢查询SQL

具体场景具体分析，只提出大致思路

• 根据慢日志定位慢查询sql；

  • show variables like '%quer%'；打开show_query_log为ON；即set global slow_query_log=on和set global long_query_time=1

• 使用explain等工具分析sql；

  • explain对sql语句分析。其中有两列最重要，一个是type; 一个是extra.
    

  

  

• 修改sql或者尽量让sql走索引；

  • select account from person_info_large order by account desc;
  • alter table person_info_large add index idx_name(name);

12.12 索引最左前缀问题

最左优先，在检索数据时从联合索引的最左边开始匹配。以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

最左前缀匹配原则：

在Mysql建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

例子解释：

索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

例子：假如创建一个（a,b)的联合索引，那么它的索引树是这样的

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。

同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

12.13 索引分类

索引类型	概念
普通索引	最基本的索引，没有任何限制
唯一索引	与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引	它是一种特殊的唯一索引，不允许有空值。
全文索引	针对较大的数据，生成全文索引很耗时好空间。
组合索引	为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则

12.14 数据库的主从复制

复制方式	操作
异步复制	默认异步复制，容易造成主库数据和从库不一致,一个数据库为Master, 一个数据库为slave,通过Binlog日志,slave两个线程，一个线程去读master binlog日志，写到自己的中继日志；一个线程解析日志，执行sql, master启动一个线程,给slave传递binlog日志
半同步复制	只有把master发送的binlog日志写到slave的中继日志，这时主库,才返回操作完成的反馈，性能有一定降低
并行操作	slave 多个线程去请求binlog日志

12.15 long_query怎么解决？

设置参数，开启慢日志功能，得到耗时超过一定时间的sql语句。

12.16 varchar和char的使用场景

类型	使用场景
varchar	字符长度经常变的
char	用字符长度固定的

12.17 数据库连接池的作用

• 维护一定数量的连接，减少创建连接的时间
• 更快的响应时间
• 统一的管理

12.18 分库分表 主从复制 读写分离

读写分离，读从库，写主库

spring配置两个数据库，通过AOP（面向切面编程），在写或读方法前面进行判断得到动态切换数据源。

12.19 数据库三范式

级别	概念
1NF	属性不可分
2NF	非主键属性，完全依赖于主键属性
3NF	非主键属性无传递依赖

12.20 关系型数据库和非关系型数据库的区别

关系型数据库

优点

• 容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解；
• 使用方便：通用的SQL语言使得操作关系型数据库非常方便；
• 易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率；
• 支持SQL，可用于复杂的查询。
• 支持事务

缺点

• 为了维护一致性所付出的巨大代价就是其读写性能比较差；
• 固定的表结构；
• 不支持高并发读写需求；
• 不支持海量数据的高效率读写

非关系型数据库

优点

• 使用键值对存储数据；
• 分布式；
• 无需经过sql层的解析，读写性能很高
• 基于键值对，数据没有耦合性，容易扩展 存储数据的格式：
• nosql的存储格式是key,value形式

缺点

• 不提供sql支持

12.21 数据库中的join的left join, right join, inner join, cross join

• 以A，B两张表为例

  A left join B
  选出A的所有记录，B表中没有的以null 代替
  right join 同理

• inner join

  A,B有交集的记录

• cross join (笛卡尔积)

  A中的每一条记录和B中的每一条记录生成一条记录
  例如A中有4条，B中有4条，cross join 就有16条记录

12.22 mysql中的锁

当多个用户同时对数据库并发操作时，会带来数据不一致的问题。所以，锁主要用于多用户环境下保证数据库完整性和一致性。

数据库锁出现的目的：处理并发问题。

数据库锁的分类：

• 按锁的粒度划分，可分为表级锁、行级锁、页级锁；
• 按锁级别划分，可分为共享锁、排它锁；
• 按加锁方式划分，可分为自动锁、显式锁；
• 按操作划分，可分为DML锁、DDL锁；
• 按使用方式划分，可分为乐观锁、悲观锁。

1.悲观锁

悲观锁（Pessimistic Lock）：正如其名，具有强烈的独占和排他特性。它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

悲观锁按照使用性质可划分为：共享锁、排他锁和更新锁。

(1) 共享锁(Share Lock)

S锁，也叫读锁，用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取器锁定的资源。

性质

• 1.多个事务可封锁同一个共享页；
• 2.任何事务都不能修改该页；
• 3.通常是该页被读取完毕，S锁立即被释放。

在SQL Server中，默认情况下，数据被读取后，立即释放共享锁。

例如，执行查询语句“SELECT * FROM my_table”时，首先锁定第一页，读取之后，释放对第一页的锁定，然后锁定第二页。这样，就允许在读操作过程中，修改未被锁定的第一页。

例如，语句“SELECT * FROM my_table HOLDLOCK”就要求在整个查询过程中，保持对表的锁定，直到查询完成才释放锁定。

(2) 排他锁(Exclusive Lock)

X锁，也叫写锁，表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。（某个顾客把试衣间从里面反锁了，其他顾客想要使用这个试衣间，就只有等待锁从里面打开了。）

性质

• 1.仅允许一个事务封锁此页；
• 2.其他任何事务必须等到X锁被释放才能对该页进行访问；
• 3.X锁一直到事务结束才能被释放。

产生排他锁的SQL语句如下：select * from ad_plan for update;

(3)更新锁

U锁，在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。

因为当使用共享锁时，修改数据的操作分为两步：

1.首先获得一个共享锁，读取数据，

2.然后将共享锁升级为排他锁，再执行修改操作。

这样如果有两个或多个事务同时对一个事务申请了共享锁，在修改数据时，这些事务都要将共享锁升级为排他锁。这时，这些事务都不会释放共享锁，而是一直等待对方释放，这样就造成了死锁。

如果一个数据在修改前直接申请更新锁，在数据修改时再升级为排他锁，就可以避免死锁。

性质

• 1.用来预定要对此页施加X锁，它允许其他事务读，但不允许再施加U锁或X锁
• 2.当被读取的页要被更新时，则升级为X锁；
• 3.U锁一直到事务结束时才能被释放。

悲观锁按作用范围划分为：行锁和表锁。

(4)行锁

锁的作用范围是行级别。

(5)表锁

锁的作用范围是整张表。

数据库能够确定那些行需要锁的情况下使用行锁，如果不知道会影响哪些行的时候就会使用表锁。

举个例子，一个用户表user，有主键id和用户生日birthday。

当你使用update … where id=?这样的语句时，数据库明确知道会影响哪一行，它就会使用行锁；

当你使用update … where birthday=?这样的的语句时，因为事先不知道会影响哪些行就可能会使用表锁。

2.乐观锁

顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以，不会上锁。但是在更新的时候会判断一下在此期间别人有没有更新这个数据，可以使用版本号等机制。

乐观锁（ Optimistic Locking ）： 相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。

悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。而乐观锁机制在一定程度上解决了这个问题。

乐观锁，大多是基于数据版本（ Version ）记录机制实现。

数据版本：为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

乐观锁的实现方式

• 版本号（version）

版本号（记为version）：就是给数据增加一个版本标识，在数据库上就是表中增加一个version字段，每次更新把这个字段加1，读取数据的时候把version读出来，更新的时候比较version，如果还是开始读取的version就可以更新了，如果现在的version比老的version大，说明有其他事务更新了该数据，并增加了版本号，这时候得到一个无法更新的通知，用户自行根据这个通知来决定怎么处理，比如重新开始一遍。这里的关键是判断version和更新两个动作需要作为一个原子单元执行，否则在你判断可以更新以后正式更新之前有别的事务修改了version，这个时候你再去更新就可能会覆盖前一个事务做的更新，造成第二类丢失更新，所以你可以使用update … where … and version=”old version”这样的语句，根据返回结果是0还是非0来得到通知，如果是0说明更新没有成功，因为version被改了，如果返回非0说明更新成功。

• 时间戳（使用数据库服务器的时间戳）

  时间戳（timestamp）：和版本号基本一样，只是通过时间戳来判断而已，注意时间戳要使用数据库服务器的时间戳不能是业务系统的时间。

• 待更新字段

  待更新字段：和版本号方式相似，只是不增加额外字段，直接使用有效数据字段做版本控制信息，因为有时候我们可能无法改变旧系统的数据库表结构。假设有个待更新字段叫count,先去读取这个count,更新的时候去比较数据库中count的值是不是我期望的值（即开始读的值），如果是就把我修改的count的值更新到该字段，否则更新失败。java的基本类型的原子类型对象如AtomicInteger就是这种思想。

• 所有字段

  所有字段：和待更新字段类似，只是使用所有字段做版本控制信息，只有所有字段都没变化才会执行更新。

12.23 死锁是什么？死锁怎么解决

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的解决死锁的方法

• 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
• 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

如果业务处理不好可以用分布式事务锁或者使用乐观锁

12.24 最左匹配原则

最左匹配原则是针对索引的

举例来说：两个字段（name,age）建立联合索引，如果where age=12这样的话，是没有利用到索引的， 这里我们可以简单的理解为先是对name字段的值排序，然后对age的数据排序，如果直接查age的话，这时就没有利用到索引了， 查询条件where name=‘xxx’ and age=xx 这时的话，就利用到索引了，再来思考下where age=xx and name=’xxx‘ 这个sql会利用索引吗， 按照正常的原则来讲是不会利用到的，但是优化器会进行优化，把位置交换下。这个sql也能利用到索引了

12.25 语法部分

• GROUP BY
• HAVING
• 统计相关：COUNT, SUM, MAX, MIN, AVG

1.GROUP BY

# 查询所有同学的学号、选课数、总成绩select student_id, count(course_id), sum(score) from score group by student_id

# 查询所有同学的学号、姓名、选课数、总成绩select s.student_id, stud.name, count(s.course_id),sum(s.score) from score s, student stuwhere s.student_id = stu.student_idgroup by s.student_id;

2.HAVING

• 通常与GROUP BY子句一起使用；
• WHERE过滤行，HAVING过滤组；
• 出现在同一sql的顺序：WHERE>GROUP BY>HAVING

# 查询平均成绩大于60分的同学的学号和平均成绩select student_id,avg(score)from scoregroup by student_idhaving avg(score) > 60

# 查询没有学全所有课的同学的学号、姓名select stu.student_id, stu.namefrom student stu,score swhere stu.student_id = s.student_idgroup by s.student_idhaving count(*) < (select count(*) from course)

转载地址：https://codingchaozhang.blog.csdn.net/article/details/115281351 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！