【Java校招面试】基础知识（七）——数据库

---

前言

本篇主要介绍数据库的相关内容。

“基础知识”是本专栏的第一个部分，本篇博文是第七篇博文，如有需要，可：

1. 点击这里，返回本专栏的索引文章
2. 点击这里，返回上一篇《【Java校招面试】基础知识（六）——计算机网络》
3. 点击这里，前往下一篇《【Java校招面试】基础知识（八）——Linux服务器》

---

一、数据库索引

01. 如何设计一个数据库？

设计数据库应该分为存储和程序实例两个模块。
前者用于将数据持久化存入磁盘中；

后者又分为以下8个部分：
1) 存储管理模块: 把物理的数据通过逻辑的形式组织和表示出来。数据库以块或也来作为数据的逻辑存储单位。
2) 缓存机制: 如LRU等，根据特定情形提高数据访问效率。
3) SQL解析模块: 编译解析SQL表达式。
4) 日志管理模块: 用于做数据库主从同步和灾难恢复。
5) 权限划分模块: 不同权限的用户只能对数据做自己权限之内的操作。
6) 容灾机制: 数据库崩溃后，应该如何恢复，恢复到什么程度。
7) 索引管理模块: 优化数据查询效率。
8) 锁管理模块: 使数据库支持并发操作。

02. 为什么要使用索引，什么样的信息能成为索引？
使用索引是为了避免全表扫描，提高查询效率。主键、唯一键、普通键等都可以成为索引。

03. 索引的数据结构
1) 二叉查找树

缺点:
 a) 不便于增删数据；
 b) 随着数据规模的增大，树的深度会很大，无法达到优化IO效率的目的.

2) B树（平衡多路查找树）

每个节点最多有m个孩子，则该树就是m阶B树；
① 根节点至少有两个孩子；
② 树中每个节点最多拥有m(m$\ge 2$)个孩子；
③ 除根节点和叶节点外，其他每个节点至少有$ceil(\frac{m}{2}$)个孩子，$ceil$表示向上取整；
④ 所有叶子节点都位于同一层；
⑤ 假设每个非终端节点中包含有n个关键字信息，其中
  a) $K_i(i=1,...,n)$为关键字，且关键字按升序排序；
  b) 关键字的个数n必须满足： $ceil(\frac{m}{2})-1\le n\le m-1$；
  c) 非叶子结点的指针$P_i(i=1,...,m)$，其中$P_1$指向关键字小于$K_1$的子树，$P_m$指向关键字大于$K_{m-1}$的子树，其他$P_i$指向关键字属于$(K_{i-1},K_i)$的子树。

3) B+树

B+树是B树的变体，其定义基本与B树相同，除了:
① 非叶子节点的子树指针与关键字个数相同；
② 非叶子节点的子树指针 $P_i$，指向关键字值在$[K_i,K_{i+1})$的子树；
③ 非叶子节点仅用来索引，数据都存在叶子节点中；
④ 所有叶子节点均有一个链指针指向下一个叶子结点（便于做范围统计）。

结论: B+树更适合用来做存储索引
 a) B+树的磁盘读写代价更低，一次性读入的索引信息更多，降低IO次数；
 b) B+树的查询效率更加稳定，所有关键字值的查找长度基本相同；
 c) B+树更有利于对数据库的扫描。

4) HashMap

缺点:
1) 仅仅能满足“=”、“IN”这类逻辑查询，不能使用范围查询；
2) 无法被用来避免数据的排序操作；
3) 不能利用部分索引键查询；
4) 不能避免表扫描；
5) 遇到大量Hash值相等的情况时性能并不一定比B树索引高。

5) BitMap（位图）

a) 适用于关键字值可枚举的情形（如性别），可用于提高统计效率。目前仅Oracle支持
b) 缺陷: 由于二进制位与行一一对应，不适用于高并发的更新操作。

MySQL中的索引通过B+树实现。

04. 密集索引和稀疏索引的区别

1) 密集索引文件中的每个搜索码值都对应一个索引值，稀疏索引文件只为索引码的某些值建立索引项；
2) 密集索引的叶子节点中不止存储了键，还保存了对应记录的其他属性。密集索引表示了一个表的物理排序，因此一个表只能建立一个密集索引。
3) 稀疏索引的叶子结点仅存储了键，以及对应记录的地址。
4) MySQL中MyISAM引擎只有稀疏索引，InnoDB有密集索引。

05. 如何定位并优化慢查询SQL？
1) 根据慢日志定位慢查询SQL
2) 使用explain等工具分析SQL
3) 修改SQL或尽量让SQL走索引

06. 联合索引的最左匹配原则的成因
1) 最左前缀匹配原则是非常重要的原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。比如a = 3 and b = 4 and c > 5 and d = 6，如果建立(a, b, c, d)顺序的索引，则d用不到索引，如果建立(a, b, d, c)的索引，则都可以用到，索引中a, b, d的顺序可任意调整。
2) =和IN可以乱序，比如a = 1 and b = 2 and c = 3，建立(a, b, c)的索引可以任意顺序，mysql查询优化器会自动优化成索引可以识别的形式。
3) 一个联合索引的例子（索引顺序为(col3, col2)）

07. 索引是建立的越多越好吗？
不是
1) 数据量小的表不需要建立索引，建立会增加额外的索引开销；
2) 数据变更需要维护索引，因此更多的索引意味着更多的维护成本；
3) 更多的索引也需要更多的空间。

---

二、数据库锁

01. MyISAM与InnoDB关于锁方面的区别是什么？
1) MyISAM默认用的是表级锁，不支持行级锁；InnoDB默认用的是行级锁，支持表级锁；
2) InnoDB在SQL没有用到索引时，会使用表级锁；
3) MyISAM在纯检索系统中性能优于InnoDB。

02. MyISAM适用的场景
1) 频繁执行全表count语句，其通过一个变量统计行数，读取速度很快；
2) 对数据进行增删改的频率不高，查询非常频繁；
3) 没有事务。

03. InnoDB适用的场景
1) 数据增删改查都相当频繁；
2) 可靠性要求比较高，要求支持事务。

04. 数据库锁的分类
1) 按粒度: 可分为表级锁、行级锁、页级锁
2) 按锁的级别: 可分为共享锁、排它锁
3) 按加锁方式: 可分为自动锁、显式锁
4) 按锁的操作: 可分为DML锁、DDL锁
5) 按使用方式: 可分为乐观锁、悲观锁

05. 悲观锁与乐观锁

• 悲观锁
  总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。

• 乐观锁
  总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。在Java中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS实现的。

06. 乐观锁常见的两种实现方式
1) 版本号机制
一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

2) CAS(Compare And Swap, 比较与交换)
是一种有名的无锁算法。无锁编程，即不使用锁的情况下实现多线程之间的变量同步，也就是在没有线程被阻塞的情况下实现变量的同步，所以也叫非阻塞同步（Non-blocking Synchronization）。

CAS算法涉及到3个操作数:
  ① 需要读写的内存值 V
  ② 进行比较的值 A
  ③ 拟写入的新值 B
当且仅当 V 的值等于 A时，CAS通过原子方式用新值B来更新V的值，否则不会执行任何操作（比较和替换是一个原子操作）。一般情况下是一个自旋操作，即不断的重试。

07. 乐观锁的缺点
1) ABA问题
如果一个变量V初次读取的时候是A值，并且在准备赋值的时候检查到它仍然是A值，那我们就能说明它的值没有被其他线程修改过了吗？很明显是不能的，因为在这段时间它的值可能被改为其他值，然后又改回A，那CAS操作就会误认为它从来没有被修改过。这个问题被称为CAS操作的 “ABA”问题。

JDK 1.5 以后的 AtomicStampedReference 类可以通过控制变量值的版本，保证CAS的正确性。 其中的 compareAndSet 方法就是首先检查当前引用是否等于预期引用，并且当前标志是否等于预期标志，如果全部相等，则以原子方式将该引用和该标志的值设置为给定的更新值。

2) 循环时间长开销大
自旋CAS（也就是不成功就一直循环执行直到成功）如果长时间不成功，会给CPU带来非常大的执行开销。 如果JVM能支持处理器提供的pause指令那么效率会有一定的提升。

3) 只能保证一个共享变量的原子操作
CAS 只对单个共享变量有效，当操作涉及跨多个共享变量时 CAS 无效。但是从 JDK 1.5开始，提供了AtomicReference类来保证引用对象之间的原子性，你可以把多个变量放在一个对象里来进行 CAS 操作.所以我们可以使用锁或者利用AtomicReference类把多个共享变量合并成一个共享变量来操作。

---

三、数据库事务

01. 数据库事务的特性
数据库事务有严格的定义，它必须同时满足4个特性:
1) 原子性: 组成一个事物的多个数据库操作是一个不可分割的原子单元，只有所有的操作执行成功，整个事务才提交，事务中任何一个数据库操作失败，已经执行的任何操作都必须撤销，让数据库返回到初始状态。

2) 一致性: 实务操作成功后，数据库所处的状态和它的业务规则是一致的，即数据不会被破坏。如从A账户转账100元到B账户，不管操作成功与否，A和B的存款总额是不变的。

3) 隔离性: 在并发数据库操作时，不同的事务拥有各自的数据空间，它们的操作不会对对方产生干扰。准确地说并非要求做到完全无干扰，数据库规定了多种事务隔离级别，不同级别对应不同的干扰程度，隔离级别越高，数据一致性越好，但并发性越弱。

4) 持久性: 一旦事务提交成功后，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证能够通过某种机制恢复数据。

02. 数据库并发操作的问题
1) 脏读: A事务读取B事务尚未提交的更改数据，并在这个数据的基础上操作。如果恰巧B事务回滚，那么A事务读到的数据根本是不被承认的。
2) 不可重复读: A事务读取了B事务提交的更改数据。假设A在取款事务的过程中，B往该账户转账100元，A两次读取账户的余额发生不一致。
3) 幻象读: A事务读取B事务提交的新增数据，这时A事务将出现幻象读的问题。幻象读一般发生在计算统计数据的事务中。

不可重复读和幻象读是两个容易混淆的概念，前者指读到了已经提交事务的更改数据（更改或删除），而后者是指读到了已经提交事务的新增数据。

4) 第一类丢失更新: A事务撤销时，把已经提交的B事务的更新数据覆盖了。
5) 第二类丢失更新: A事务覆盖B事务已经提交的数据，造成B事务所做的操作丢失。

03. 事务隔离级别

隔离级别	脏读	不可重复读	幻象读	第一类丢失更新	第二类丢失更新
READ_UNCOMMITED	允许	允许	允许	不允许	允许
READ_COMMITED	不允许	允许	允许	不允许	允许
REPEATABLE_READ	不允许	不允许	允许	不允许	不允许
SERIALIZABLE	不允许	不允许	不允许	不允许	不允许

04. 当前读和快照读

• 当前读:
  • SELECT … LOCK IN SHARE MODE
  • SELECT … FOR UPDATE
  • UPDATE
  • DELETE
  • INSERT
• 快照读:
  • 在非SERIALIZABLE事务隔离级别下不加锁的非阻塞读SELECT …

05. 数据记录中的隐含字段
1) DB_TRX_ID: 最后一次修改本行的事务ID
2) DB_ROLL_PTR: 回滚指针
3) DB_ROW_ID: 单调递增的行ID

06. Undo 日志
当对记录进行了更新之后，就会生成Undo日志。Undo日志中存储的是更新前的数据。Undo日志分为两种：Insert Undo Log和Update Undo Log。

1) Insert Undo Log: 事务对记录进行Insert操作时产生，事务回滚时需要，并在事务提交后就立即丢弃。
2) Update Undo Log: 事务对记录进行Update或Delete时产生，事务回滚和快照读时都需要，不能随便丢弃。

07. 一个某记录被执行Update操作的例子
1) 第一次修改Field2

2) 在Undo Log还未被丢弃之前，另一个事务开始快照读该条记录，第二次修改Field3

08. Read View
当执行快照读时，针对查询的数据创建Read View，限定当前的事务可以看到的数据版本。

09. InnoDB RR隔离级别下如何避免幻读
通过next-key锁（行锁 + gap锁）

1) gap锁: gap即索引树中插入新记录的空隙，gap锁锁定一个范围，但不包括记录本身。gap锁会用在非唯一索引或者不走索引的当前读中。

2) 非唯一索引

3) 无索引

10. 对主键索引或唯一索引会用gap锁吗？
1) 如果WHERE条件全部命中，则不会加gap锁，只加记录锁。
2) 如果WHERE条件部分命中或者全不命中，则会加gap锁。

11. 常见语法
1) GROUP BY
  ① 满足“SELECT子句中的列名必须为分组列或列函数”；
  ② 列函数对于GROUP BY子句定义的每个组各返回一个结果；

2) HAVING
  ① 通常与GROUP BY子句一起使用；
  ② WHERE过滤行，HAVING过滤组；
  ③ 出现在同一SQL的顺序：WHERE > GROUP BY > HAVING

12. Spring事务传播行为

事务传播行为类型	说明
REQUIRED	如果没有当前事务，就创建一个新的事务；否则加入当前事务。
SUPPORTS	支持当前事务；如果没有当前事务，就以非事务方式执行。
MANDATORY	使用当前事务；如果不存在当前事务，则抛出异常。
REQUIRES_NEW	新建事务；如果存在当前事务，挂起当前事务。
NOT_SUPPORTED	以非事务方式执行；如果存在当前事务，挂起当前事务。
NEVER	以非事务方式执行；如果存在当前事务，则抛出异常。
NESTED	如果存在当前事务，则在嵌套事务内执行；否则，执行与PROPAGATION_REQUIRED类似的操作。

13. 编程式事务管理和声明式事务管理
1) 编程式事务管理的例子

	public class TransactionManagement {
	    @Autowired
	    private UserService userService;
	    @Autowired
	    private TransactionTemplate template;
	    public boolean verifySignIn(String username, String password) {
	        template.execute(new TransactionCallbackWithoutResult(){
	           protected void doInTransactionWithoutResult(TransactionStatus status){
	               return userService.verify(username, password);
	           }
	        });
	    }
	}

2) 区别
声明式事务管理建立在AOP之上的。其本质是对方法前后进行拦截，然后在目标方法开始之前创建或者加入一个事务，在执行完目标方法之后根据执行情况提交或者回滚事务。声明式事务最大的优点就是不需要通过编程的方式管理事务，这样就不需要在业务逻辑代码中掺杂事务管理的代码，只需在配置文件中做相关的事务规则声明(或通过基于@Transactional注解的方式)，便可以将事务规则应用到业务逻辑中。

显然声明式事务管理要优于编程式事务管理，这正是spring倡导的非侵入式的开发方式。 声明式事务管理使业务代码不受污染，一个普通的POJO对象，只要加上注解就可以获得完全的事务支持。和编程式事务相比，声明式事务唯一不足地方是，后者的最细粒度只能作用到方法级别，无法做到像编程式事务那样可以作用到代码块级别。 但是即便有这样的需求，也存在很多变通的方法，比如，可以将需要进行事务管理的代码块独立为方法等等。

14. @Transactional
声明式事务管理编程中使用的注解

• 添加位置: 接口实现类或接口实现方法上，而不是接口类中
• 访问权限: public 的方法才起作用。
• 系统设计: 对需要进行事务管理的方法进行注解，而不是放置在接口实现类上（ 接口中所有方法都需要进行事务管理，但其实并不需要，如只读的接口就不需要事务管理，但是由于配置了@Transactional就需要AOP拦截及事务的处理，影响系统性能）
• 错误使用:
    ① 接口中A、B两个方法，A无@Transactional标签，B有，上层通过A间接调用B，此时事务不生效
    ② 接口中异常（运行时异常）被捕获而没有被抛出。默认配置下，spring只有在抛出的异常为运行时unchecked异常时才回滚该事务，也就是抛出的异常为RuntimeException的子类(Errors也会导致事务回滚)，而抛出checked异常则不会导致事务回滚，可通过@Transactional rollbackFor进行配置。

15. 打上@Transactional注解后发生了什么？

• 在应用系统调用声明了@Transactional的目标方法时，Spring Framework默认使用AOP代理，在代码运行时生成一个代理对象，根据@Transactional的属性配置信息，这个代理对象决定该声明@Transactional的目标方法是否由拦截器TransactionInterceptor来拦截。

• 被TransactionInterceptor拦截后，会在目标方法开始执行之前创建并加入事务，并执行目标方法的业务逻辑, 最后根据执行情况是否出现异常，利用抽象事务管理器 AbstractPlatformTransactionManager操作数据源DataSource提交或回滚事务。

---

四、数据库连接池

01. DBCP连接池、C3P0连接池、Druid连接池
1) DBCP
DBCP(DataBase connection pool)，数据库连接池。是apache上的一个java连接池项目，也是 tomcat 使用的连接池组件。由于建立数据库连接是一个非常耗时耗资源的行为，所以通过连接池预先同数据库建立一些连接，放在内存中，应用程序需要建立数据库连接时直接到连接池中申请一个就行，用完后再放回去。

2) C3P0
C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。

3) Druid
DRUID是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、PROXOOL等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况。

4) 对比
最大连接数控制为10，测试获取10w个连接的时间，单位为ms

数据库连接池	获取10w个连接的时间（ms）
DBCP	8475
C3P0	1621
Druid	655

5) 区别
  ① DBCP没有自动回收空闲连接的功能，C3P0有自动回收空闲连接功能。
  ② 两者主要是对数据连接的处理方式不同！C3P0提供最大空闲时间，DBCP提供最大连接数。
  ③ 前者当连接超过最大空闲连接时间时，当前连接就会被断掉。DBCP当连接数超过最大连接数时，所有连接都会被断开。
  ④ DBCP的原理是维护多个连接对象Connection,在web项目要连接数据库时直接使用它维护的对象进行连接，省去每次都要创建连接对象的麻烦。提高效率和减少内存使用。
  ⑤ C3P0可以自动回收连接，DBCP需要自己手动释放资源。但是DBCP效率比较高。
  ⑥ Druid支持针对Oracle的PSCache（Prepared Statement Cache）优化。支持ExceptionSorter，当数据库抛出一些不可恢复的异常时，抛弃连接。提供了监控平台，且可扩展。

---

后记

以上就是数据库相关的知识点。和计算机网络一样，这部分的完整知识也需要一本书去承载，如果想要从头到尾完整得学习数据库的知识体系，就去找本书来读吧。

另外，数据库这里的实践性也很强，需要多写SQL语句来巩固各种操作的语法，面试中也有面试官会给出几个查询场景，让我们去写SQL语句来实现相关的查询。特别是:

• 联表查询用到的连接关键字：
  • 左连接（LEFT JOIN ... ON ... ）、
  • 右连接（RIGHT JOIN ... ON ... ）、
  • 内连接（JOIN ... ON ... ）
  • …
• 临时表命名：SELECT ... FROM ... AS table1
• 分组关键字：GROUP BY ... ，后面过滤条件必须用HAVING，而不能再用WHERE
• 排序关键字：ORDER BY ... ，避免记成SORT BY，默认增序(ASC)，使用降序要在后面跟DESC
• 一些常用的内置函数：
  • 求和（SUM）
  • 求行数（COUNT）
  • 最大值（MAX）、最小值（MIN）、平均值（AVG）
  • …

总之灵活使用这些关键字可以大幅降低查询的复杂度和执行时间，应多加练习和使用。