软件开发相关面经5——数据库篇

1、关系型和非关系型数据库的区别（低频）

关系型数据库

优点

1. 复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询，如join。
2. 最大优势：保持数据的一致性（事务处理） 事务支持使得对于安全性能很高的数据访问要求得以实现。关系型数据库把所有的数据都通过行和列的二元表现形式表示出来。
3. 由于以标准化为前提，数据更新的开销很小（相同的字段基本上都只有一处）

缺点：

1.扩展性：关系型数据库有类似join这样的多表查询机制的限制导致扩展很艰难。

2.不擅长如下处理

1). 大量数据的写入处理

2). 为有数据更新的表做索引或表结构（schema）变更

3). 字段不固定时应用

4). 对简单查询需要快速返回结果的处理

非关系型数据库

优点

1. 性能NoSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。
2. 可扩展性同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。
3. 成本：NoSQL数据库简单易部署，基本都是开源软件，不需要像使用oracle那样花费大量成本购买使用，相比关系型数据库价格便宜。
4. 查询速度快：NoSQL数据库将数据存储于缓存之中，关系型数据库将数据存储在硬盘中，自然查询速度远不及nosql数据库。
5. 存储数据的格式：NoSQL的存储格式是key,value形式、文档形式、图片形式等等，所以可以存储基础类型以及对象或者是集合等各种格式，而sql数据库则只支持基础类型。

缺点：

1） 维护的工具和资料有限，因为nosql是属于新的技术，不能和关系型数据库10几年的技术同日而语。
2） 不提供对sql的支持，如果不支持sql这样的工业标准，将产生一定用户的学习和使用成本。
3） 不提供关系型数据库对事物的处理。

---

2、什么是非关系型数据库（低频）

非关系型数据库也叫nosql，采用键值对的形式进行存储。它的读写性能很高，易于扩展。例如 Redis,Mongodb,hbase等等。
适合使用非关系型数据库的场景：
日志系统
地理位置存储
数据量巨大
高可用

---

3、说一下 MySQL 执行一条查询语句的内部执行过程？

连接器：客户端先通过连接器连接到 MySQL 服务器。
缓存：连接器权限验证通过之后，先查询是否有查询缓存，如果有缓存（之前执行过此语句）则直接返回缓存数据，如果没有缓存则进入分析器。
分析器：分析器会对查询语句进行语法分析和词法分析，判断 SQL 语法是否正确，如果查询语法错误会直接返回给客户端错误信息，如果语法正确则进入优化器。
优化器：优化器是对查询语句进行优化处理，例如一个表里面有多个索引，优化器会判别哪个索引性能更好。
执行器：优化器执行完就进入执行器，执行器就开始执行语句进行查询比对了，直到查询到满足条件的所有数据，然后进行返回。

---

4、B树和B+树的区别

B树
每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null。

B+树
只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针。

后来，在B+树上增加了顺序访问指针，也就是每个叶子节点增加一个指向相邻叶子节点的指针，这样一棵树成了数据库系统实现索引的首选数据结构。 原因有很多，最主要的是这棵树矮胖，呵呵。一般来说，索引很大，往往以索引文件的形式存储的磁盘上，索引查找时产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的时间复杂度。树高度越小，I/O次数越少。
参考：https://blog.csdn.net/zhuanzhe117/article/details/78039692

B+树和B具有不同的存储结构，以一个m阶树为例。

1. 关键字的数量不同；B+树中分支结点有m个关键字，其叶子结点也有m个，其关键字只是起到了一个索引的作用，但是B树虽然也有m个子结点，但是其只拥有m-1个关键字。
2. 存储的位置不同；B+树中的数据都存储在叶子结点上，也就是其所有叶子结点的数据组合起来就是完整的数据，但是B树的数据存储在每一个结点中，并不仅仅存储在叶子结点上。
3. 分支结点的构造不同；B+树的分支结点仅仅存储着关键字信息和儿子的指针（这里的指针指的是磁盘块的偏移量），也就是说内部结点仅仅包含着索引信息。
4. 查询不同；B树在找到具体的数值以后，则结束，而B+树则需要通过索引找到叶子结点中的数据才结束，也就是说B+树的搜索过程中走了一条从根结点到叶子结点的路径。

B+树优点：由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引，而B树则常用于文件索引。

5、数据库的索引类型

数据库的索引类型分为逻辑分类和物理分类
1. 逻辑分类：
主键索引 当关系表中定义主键时会自动创建主键索引。每张表中的主键索引只能有一个，要求主键中的每个值都唯一，即不可重复，也不能有空值。
唯一索引 数据列不能有重复，可以有空值。一张表可以有多个唯一索引，但是每个唯一索引只能有一列。如身份证，卡号等。
普通索引 一张表可以有多个普通索引，可以重复可以为空值
全文索引 可以加快模糊查询，不常用
2. 物理分类：
聚集索引（聚簇索引） 数据在物理存储中的顺序跟索引中数据的逻辑顺序相同，比如以ID建立聚集索引，数据库中id从小到大排列，那么物理存储中该数据的内存地址值也按照从小到大存储。一般是表中的主键索引，如果没有主键索引就会以第一个非空的唯一索引作为聚集索引。一张表只能有一个聚集索引。
非聚集索引 数据在物理存储中的顺序跟索引中数据的逻辑顺序不同。非聚集索引因为无法定位数据所在的行，所以需要扫描两遍索引树。第一遍扫描非聚集索引的索引树，确定该数据的主键ID，然后到主键索引（聚集索引）中寻找相应的数据。

MyISAM
data存的是数据地址。索引是索引，数据是地址。索引放在XX.MYI文件中，数据放在XX.MYD文件中，所以也叫非聚集索引。

InnoDB
data存的是数据本身。索引也是数据。数据和索引存在一个XX.IDB文件中，所以也叫聚集索引。

了解了数据结构再看索引，一切都不费解了，只是顺着逻辑推而已。另加两种存储引擎的区别：

1、MyISAM（是非聚簇索引）是非事务安全的，而InnoDB（是聚簇索引）是事务安全的
2、MyISAM锁的粒度是表级的，而InnoDB支持行级锁
3、MyISAM支持全文类型索引，而InnoDB不支持全文索引
4、MyISAM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyISAM
5、MyISAM表保存成文件形式，跨平台使用更加方便
6、MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果在应用中执行大量select操作可选择
7、InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，可选择。
参考：https://blog.csdn.net/zhuanzhe117/article/details/78039692

6、说一下事务是怎么实现的

https://blog.csdn.net/u013256816/article/details/103966510
https://www.cnblogs.com/takumicx/p/9998844.html
事务就是一组逻辑操作的集合。实现事务就是要保证可靠性和并发隔离，或者说，能够满足ACID特性的机制。而这些主要是靠日志恢复和并发控制实现的。
日志恢复：数据库里有两个日志，一个是redo log，一个是undo log。redo log记录的是已经成功提交的事务操作信息，用来恢复数据，保证事务的持久性。undo log记录的是事务修改之前的数据信息，用来回滚数据，保证事务的原子性。
并发控制：并发控制主要靠读写锁和MVCC（多版本并发控制）来实现。读写锁包括共享锁和排他锁，保证事务的隔离性。MVCC通过为数据添加时间戳来实现。

事务的ACID是通过InnoDB日志和锁来保证。事务的隔离性是通过数据库锁的机制实现的，持久性通过redo log（重做日志）来实现，原子性和一致性通过Undo log（回撤日志）来实现。Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了roll back语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。 和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

7、索引的优缺点，什么时候使用索引，什么时候不能使用索引（重点）

https://www.cnblogs.com/wezheng/p/8399305.html
哪些列需要使用索引？
经常搜索的列上建索引
作为主键的列上要建索引
经常需要连接（where子句）的列上
经常需要排序的列
经常需要范围查找的列
哪些列不适合建索引？
很少查询的列
更新很频繁的列
数据值的取值比较少的列（比如性别）

8、索引的底层实现（重点）

数据库的索引是使用B+树来实现的。
（为什么要用B+树，为什么不用红黑树和B树）
B+树是一种特殊的平衡多路树，是B树的优化改进版本，它把所有的数据都存放在叶节点上，中间节点保存的是索引。这样一来相对于B树来说，减少了数据对中间节点的空间占用，使得中间节点可以存放更多的指针，使得树变得更矮，深度更小，从而减少查询的磁盘IO次数，提高查询效率。另一个是由于叶节点之间有指针连接，所以可以进行范围查询，方便区间访问。
而**红黑树是二叉的，它的深度相对B+树来说更大，更大的深度意味着查找次数更多，更频繁的磁盘IO，**所以红黑树更适合在内存中进行查找。
使用B+树而不是B树的原因
B+树节点大小更小，一次IO读入的节点数更多
B+树的数据都在叶子节点中，遍历和区间访问性能大幅提高
B+树查询效率稳定
使用B+树而不是AVL树、红黑树的原因
B+树的树高比AVL树、红黑树低，IO次数少

9、Mysql的优化（高频，索引优化，性能优化）

高频访问：
分表分库：将数据库表进行水平拆分，减少表的长度
增加缓存： 在web和DB之间加上一层缓存层
增加数据库的索引：在合适的字段加上索引，解决高频访问的问题
并发优化：
主从读写分离：只在主服务器上写，从服务器上读
负载均衡集群：通过集群或者分布式的方式解决并发压力

10、数据库中事务的ACID（四大特性都要能够举例说明，理解透彻，比如原子性和一致性的关联，隔离性不好会出现的问题）

数据库事务是指逻辑上对数据的一种操作，这个事务要么全部成功，要么全部失败。
A: atom 原子性
数据库事务的原子性是指：事务是一个不可分割的工作单位，这组操作要么全部发生，要么全部不发生。
C: consistency 一致性
数据库事务的一致性是指：在事务开始以前，数据库中的数据有一个一致的状态。在事务完成后，数据库中的事务也应该保持这种一致性。事务应该将数据从一个一致性状态转移到另一个一致性状态。比如在银行转账操作后两个账户的总额应当不变。
I: isolation 隔离性
数据库事务的隔离性要求数据库中的事务不会受另一个并发执行的事务的影响，对于数据库中同时执行的每个事务来说，其他事务要么还没开始执行，要么已经执行结束，它都感觉不到还有别的事务正在执行。
D：durability 持久性
数据库事务的持久性要求事务对数据库的改变是永久的，哪怕数据库发生损坏都不会影响到已发生的事务。
如果事务没有完成，数据库因故断电了，那么重启后也应该是没有执行事务的状态，如果事务已经完成后数据库断电了，那么重启后就应该是事务执行完成后的状态。

11、什么是脏读，不可重复读和幻读？

详见数据库的事务隔离级别总结
脏读：脏读是指一个事务在处理过程中读取了另一个还没提交的事务的数据。
比如A向B转账100，A的账户减少了100，而B的账户还没来得及修改，此时一个并发的事务访问到了B的账户，就是脏读
**不可重复读：**不可重复读是对于数据库中的某一个字段，一个事务多次查询却返回了不同的值，这是由于在查询的间隔中，该字段被另一个事务修改并提交了。
比如A第一次查询自己的账户有1000元，此时另一个事务给A的账户增加了1000元，所以A再次读取他的账户得到了2000的结果，跟第一次读取的不一样。
不可重复读与脏读的不同之处在于，脏读是读取了另一个事务没有提交的脏数据，不可重复读是读取了已经提交的数据，实际上并不是一个异常现象。
幻读：事务多次读取同一个范围的时候，查询结果的记录数不一样，这是由于在查询的间隔中，另一个事务新增或删除了数据。
比如A公司一共有100个人，第一次查询总人数得到100条记录，此时另一个事务新增了一个人，所以下一次查询得到101条记录。
不可重复读和幻读的不同之处在于，幻读是多次读取的结果行数不同，不可重复读是读取结果的值不同。

避免不可重复读需要锁行，避免幻读则需要锁表。
脏读，不可重复读和幻读都是数据库的读一致性问题，是在并行的过程中出现的问题，必须采用一定的隔离级别解决。

数据库实现事务隔离的方式，基本上可分为以下两种：

一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。

四种隔离级别
为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了4个事务隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同，应用可以根据自己的业务逻辑要求，通过选择不同的隔离级别来平衡 “隔离”与“并发”的矛盾。

4个事务隔离级别：

Read uncommitted (读未提交)：最低级别，以上问题均无法解决。
Read committed (读已提交)：读已提交，可避免脏读情况发生。
Repeatable Read（可重复读）：确保事务可以多次从一个字段中读取相同的值，在此事务持续期间，禁止其他事务对此字段的更新，可以避免脏读和不可重复读，仍会出现幻读问题。
Serializable （串行化）：最严格的事务隔离级别，要求所有事务被串行执行，不能并发执行，可避免脏读、不可重复读、幻读情况的发生。
Oracle的默认隔离级别是读已提交，实现了四种隔离级别中的读已提交和串行化隔离级别
MySQL的默认隔离级别是可重复读，并且实现了所有四种隔离级别
这四种隔离级别，分别有可能产生问题总结如下：

很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。避免不可重复读需要锁行（某一行在select操作时，不允许update与delete）就行，避免幻读则需要锁表。如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，幻读不能通过行锁来避免，==需要Serializable隔离级别 ==，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，**当然隔离级别越高，越能保证数据的完整性和统一性，但是执行效率就越低，**对并发性能的影响也越大。像Serializable这样的级别，就是以锁表的方式（类似于Java多线程中的锁）使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。

参考：https://blog.csdn.net/fuzhongmin05/article/details/91126936

12、数据库连接池技术带来的优势：

1． 资源重用
由于数据库连接得到重用，避免了频繁创建、释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增进了系统运行环境的平稳性（减少内存碎片以及数据库临时进程/线程的数量）。

2． 更快的系统响应速度
数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而缩减了系统整体响应时间。

3． 新的资源分配手段
对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接的配置，实现数据库连接池技术。某一应用最大可用数据库连接数的限制，避免某一应用独占所有数据库资源。

4． 统一的连接管理，避免数据库连接泄漏
在较为完备的数据库连接池实现中，可根据预先的连接占用超时设定，强制收回被占用连接。从而避免了常规数据库连接操作中可能出现的资源泄漏。一个最小化的数据库连接池实现。
参考：https://blog.csdn.net/yangniceyangyang/article/details/87183013

13、Mysql的表空间方式，各自特点

共享表空间：指的是数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在 data 目录下。
独立表空间：每一个表都将会生成以独立的文件方式来进行存储。 优点：当表被删除时这部分空间可以被回收；可以更快的恢复和备份单个表；将单个表复制到另一个实例会很方便；
缺点：mysqld会维持很多文件句柄，表太多会影响性能。如果很多表都增长会导致碎片问题