参考

Java 面试知识点解析(六)——数据库篇
知名互联网公司校招 Java 开发岗面试知识点解析

1. 事务：

什么是事务：

事务就是满足ACID特性的一组操作，或者说是一个独立的工作单元。事务中的语句要么全部执行成功，要么全部执行失败。经典例子：银行转账

/*
 * 我们来模拟A向B账号转账的场景
 *   A和B账户都有1000块，现在我让A账户向B账号转500块钱
 *
 **/
//JDBC默认的情况下是关闭事务的，下面我们看看关闭事务去操作转账操作有什么问题

//A账户减去500块
String sql = "UPDATE a SET money=money-500 ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();

//B账户多了500块
String sql2 = "UPDATE b SET money=money+500";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();

从上面看，我们的确可以发现A向B转账，成功了。可是如果A向B转账的过程中出现了问题呢？下面模拟一下

// A账户减去500块
String sql = "UPDATE a SET money=money-500 ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();

// 这里模拟出现问题
int a = 3 / 0;

String sql2 = "UPDATE b SET money=money+500";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();

显然，上面代码是会抛出异常的，我们再来查询一下数据。A账户少了500块钱，B账户的钱没有增加。这明显是不合理的。
我们可以通过事务来解决上面出现的问题：

    // 开启事务,对数据的操作就不会立即生效。
    connection.setAutoCommit(false);

    // A账户减去500块
    String sql = "UPDATE a SET money=money-500 ";
    preparedStatement = connection.prepareStatement(sql);
    preparedStatement.executeUpdate();

    // 在转账过程中出现问题
    int a = 3 / 0;

    // B账户多500块
    String sql2 = "UPDATE b SET money=money+500";
    preparedStatement = connection.prepareStatement(sql2);
    preparedStatement.executeUpdate();

    // 如果程序能执行到这里，没有抛出异常，我们就提交数据
    connection.commit();

    // 关闭事务【自动提交】
    connection.setAutoCommit(true);

} catch(SQLException e) {
    try {
        // 如果出现了异常，就会进到这里来，我们就把事务回滚【将数据变成原来那样】
        connection.rollback();

        // 关闭事务【自动提交】
        connection.setAutoCommit(true);
    } catch (SQLException e1) {
        e1.printStackTrace();
    }
}

上面的程序也一样抛出了异常，A账户钱没有减少，B账户的钱也没有增加。

注意：当Connection遇到一个未处理的SQLException时，系统会非正常退出，事务也会自动回滚，但如果程序捕获到了异常，是需要在catch中显式回滚事务的。

ACID

原子性（Atomicity）：事务被视为不可分割的最小单元，事务的所有操作要么玩不提交成功，要么全部回滚失败。
一致性（Consistency）：数据库总是从一个一致性状态转换到另一个一致性状态
隔离性（Isolation）：一个事务所做的修改在最终提交之前，对其他事务是不可见的。
持久性（Durability）：一旦事务提交，则其所做的修改将会永远保存在数据库中，即时系统发生崩溃，事务执行的结果也不能丢失，使用重做日志来保证其持久性。

事务并发带来的问题

• 脏读
  一个事物读取了另一个事务未提交的数据
• 不可重复读
  不可重复读的重点是修改，同样的条件两次读取结果不同。也就是说，被读取的数据可以被其他事务修改
• 幻读
  幻读的重点在于新增或者删除，同样条件下，两次读出来的记录数不一样。

事务隔离级别有哪几种，MYSQL默认事务隔离级别是哪个

廖雪峰的官方網站
隔离级别决定了一个事务可能对另一个事务的影响。

• READ UNCOMMITED：未提交读
  最低级别的隔离，通常也称为脏读，在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。

  
  image.png

• READ COMMITED：读已提交
  在一个事务中只允许对其他事务已经commit的记录可见。该隔离级别，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题。。（两次执行相同的查询，可能会得到不一样的结果）。绝大部分数据库的默认设置是该隔离级别（mysql不是哦）

  
  image.png

• REPEATABLE READ ：可重复读
  一个事务可能会出现幻读的问题。
  幻读是，在一个事务中，第一次查询某条记录，发现没有，但是当试图更新这条不存在的记录时，竟然能成功，并且再次读取同一条记录，它就神奇的出现了。（mysql的默认隔离级别）

  
  image.png

• Serialization：可串行化
  最严格的隔离级别，在该隔离级别下，所有事物按照次序一次执行，因此，脏读、不可重复读、幻读都不会出现。
  虽然具有最高的安全性，但是由于事物是串行执行，所以效率大大降低。

2. 乐观锁和悲观锁是什么

数据库管理系统中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时，不破坏事务的隔离性和统一性以及数据库的统一性。

乐观并发控制和悲观并发控制，是并发控制的主要技术手段。

悲观锁

假设会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

悲观锁是一种利用数据库内部机制提供的锁的方式，也就是对更新的数据加锁，这样在并发期间一旦有一个事务持有了数据库记录的锁，其他的线程将不能再对数据进行更新了，这就是悲观锁的实现方式。

Mysql innodb中使用悲观锁：

要使用悲观锁，就必须关闭mysql数据库的自动提交属性，因为mysql默认使用autocommit。

Mysql innodb默认行级锁，行级锁是基于索引的，如果一条sql语句用不到索引，是不会使用行级锁的，会使用表级锁把整张表锁住。

优点和不足：

悲观并发控制实际上是“先取锁，再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据产生额外的开销，还有增加死锁的机会。另外，只读性事务处理中由于不会产生冲突，也没有必要使用锁。这样做只会增加系统的负载，降低系统的并行度。

乐观锁

假设不会发生并发冲突，再是提交操作是检查是否违反数据完整性。

乐观锁是一种不会阻塞其他线程并发的控制，能够提高并发能力。一般的实现乐观锁的方式就是记录数据版本。

数据版本，微数据增加的一种版本标志。当读取数据时，将版本标志一同独处，数据每更新一次，同时对版本标志进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

实现数据版本有两种方式，第一种是使用版本号，第二种是使用时间戳。

优点与不足：

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

关于MVVC

注：与MVCC相对的，是基于锁的并发控制（Lock-Based Concurrency Control）

1. LBCC：Lock-Based Concurrency Control，基于锁的并发控制。

2. MVCC：Multi-Version Concurrency Control，基于多版本的并发控制协议。纯粹基于锁的并发机制并发量低，MVCC是在基于锁的并发控制上的改进，主要是在读操作上提高了并发量。

Mysql InnoDB存储引擎，实际的是基于多版本的并发控制协议-MVCC

Mvcc的好处：读不加锁，读写不冲突，在读多写少的应用中，极大的增加了系统的并发性能。

在MVCC并发控制中，读操作可以分为两类：

1. 快照读：读取的是记录的可见版本（有可能是历史版本），不用加锁（共享读锁s锁也不加，所以不会阻塞其他事务的写）

2. 当前读，读取的是记录的最新版本，并且当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

3. 索引（必考）

什么是索引

索引（在Mysql中也叫作键（key）”）是存储引擎用于快速找到记录的一种数据结构。

B树/B+树

在数据结构中，我们最常见的搜索结构就是二叉搜索树和AVL数（高度平衡的二叉搜索树），然而无论是二叉搜索树还是AVL树，当数据量比较大时，都会由于树的深度过大而造成I/O读写过于频繁，进而导致查询效率低下。因此对于索引而言，多叉树结构称为不二选择。B树的各种操作能使树保持较低的高度，从而保证高效的查找效率。

B树是一种平衡的多路查找树，节点的最大的孩子数目称为B树的阶。

image.png

B树的特性：
1.关键字集合分布在整颗树中；
2.任何一个关键字出现且只出现在一个结点中；
3.搜索有可能在非叶子结点结束；
4.其搜索性能等价于在关键字全集内做一次二分查找；
5.自动层次控制；

image.png

B+的特性：
1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
2.不可能在非叶子结点命中；
3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
4.更适合文件索引系统；

.可以使用B+Tree 索引的查询类型.

• 全值匹配：和索引中所有列进行匹配。
• 匹配最左前缀：只和索引的第一项匹配
• 匹配列前缀：可以只匹配某一列的值的开头部分
• 匹配范围值：如可以查找姓在Allen~Barrymore之间的人
• 精确匹配某一列并范围匹配另外一列
• 只访问索引的查询。
  因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中ORDER BY操作。

关于B+树索引的限制

• 如果不是按照索引的最左列开始查找，则无法使用索引。
• 不能跳过索引中的列
• 如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查找
  所以，索引的顺序是多么重要

哈希索引

1、 哈希索引，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码。
2、 在mysql中，只有memory引擎显式支持哈希索引。而且memory引起支持非唯一哈希索引。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中
3、 优点：结构紧凑，速度非常快
4、 限制：

• 哈希索引质保函哈希值和行指针，而不存储字段值。
• 不是按照顺序存储，所以无法排序
• 不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容计算哈希值
• 哈希索引只支持等值比较查询，不支持任何范围查询，如LIKE、大于
• 如果出现哈希冲突的话，一些所以索引维护操作的代价会非常高

空间数据索引 R树索引

R树索引是一种多级平衡树,它是B树在多维空间上的扩展。在R树中存放的数据并不是原始数据，而是这些数据的最小边界矩形（MBR），空间对象的MBR被包含于R树的叶结点中。在R树空间索引中，设计一些虚拟的矩形目标，将一些空间位置相近的目标，包含在这个矩形内，这些虚拟的矩形作为空间索引，它含有所包含的空间对象的指针。虚拟矩形还可以进一步细分，即可以再套虚拟矩形形成多级空间索引。

索引的优缺点

优点：

• 大大加快数据检索速度

• 在使用分组和排序子句进行检索式，可以显著减少查询的排序的时间

• 将随机IO变成顺序IO

缺点：

• 时间方面：创建索引、维护索引需要好费时间

• 空间方面：索引需要占用物理内存

创建索引时需要注意什么

1. 非空字段，在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较变得更加复杂，应该使用0、特殊字符或者空串在代替null

2. 取值离散大的字段：太多重复值的字段不适合作为索引

3. 索引字段越小越好：数据库的数据存储以页为单位。一页存储的数据越多，一次IO操作获得的数据越大，效率越高

4. 独立的列
   索引不能是表达式的一部分，也不能是函数的参数，否则，索引不起作用

5. 前缀索引和索引选择性
   选择足够长的前缀保证较高的选择性（选择行驶不重复的索引值/数据表的记录总数），同时不能太长（以便节约空间）

6. 多列索引
   把选择性高的列放在坐边
   . MyISAM和Innodb两种引擎所使用的索引的数据结构是什么？

都是B+树！

MyISAM引擎，B+树结构中存储的内容实际上是实际数据的地址值，也就是说它的索引和实际数据的分开的。只不过使用索引指向了实际数据。这种索引的模式，称为非聚簇索引。

Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的是实际的数据，这种索引被称为聚簇索引。下面有详细解说！

聚集索引

聚簇索引并不是单独的索引类型，而是一种数据存储方式。
当表中有聚簇索引时，它的数据行实际上存放在索引的叶子页中，“聚簇”的意思就是数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能由一个聚簇索引。

聚簇索引的数据分布

image.png
一般来说，将通过主键作为聚簇索引的索引项，也就是通过主键聚集数据。

聚簇索引的优点：

1. 聚簇索引将索引和数据行保存在同一个Btree中，直接通过聚簇索引可以直接获取数据，相比费聚簇索引需要第二次查询效率要高。

2. 聚簇索引对于范围查询效率很高，因为数据是按照大小排列的

聚簇索引的缺点：

1.更新代价较高，如果更新了行的聚簇索引列，就需要将数据移动到相应的位置。这可能因为要插入的页已满，导致页分裂**

2.插入速度严重依赖于插入顺序，按照主键进行插入的速度是加载数据到InnoDB中最快方式。如果不是按照主键插入，最好在加载完成后使用OPTIMIZE TABLE命令重新组织一下表。

3.聚簇索引可能导致全表扫描速度变慢。因为可能需要加载物理上相隔较远的页到内存中。（需要耗时的磁盘寻道操作）

非聚簇索引

非聚簇索引，又叫做二级索引。二级索引的叶子节点保存的不是指向行的物理指针，而是行的主键值。当通过二级索引查找行，索引需要在二级索引中找到相应的叶子节点，获得行的主键值，然后使用主键去聚簇索引中查找数据行，这需要两个BTree查找

4. Mysql中MyISAM和InnoDB的区别有哪些

1. InnoDB支持事务，MyISAM不支持。对于InnoDB每一条SQL语言都默认封装成事务，自动提交。这样会影响速度，所以最好把多条sql语言放在beigin和commit之间，组成一个事务。

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转换成MyISAM会失败。

3. InnoDB是聚簇索引，数据文件和索引绑在一起。而MyISAM是非聚簇索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

最主要的区别是：MyISAM 表不支持事务、不支持行级锁、不支持外键。 InnoDB 表支持事务、支持行级锁、支持外键。（可直接回答这个）

如何选择：

1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写也挺频繁，请使用InnoDB。

3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；

4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

5. 数据库范式

思考这样一个例子：

我们现在需要建立一个描述学校教务的数据库，该数据库涉及的对象包括学生的学号（Sno）、所在系（Sdept）、系主任姓名（Mname）、课程号（Cno）和成绩（Grade），假设我们使用单一的关系模式 Student 来表示，那么根据现实世界已知的信息，会描述成以下这个样子：

image.png

这个关系模式存在以下：
数据冗余：系主任名字重复出现，
更新异常**：由于数据冗余，当更新数据库中的数据时，系统要付出巨大代价来维护数据库的完整性。如更换系主任
插入异常：一个系刚成立没有学生的时候，无法把这个系及其系主任的信息存入到数据库。
删除异常：如果一个系的所有学生都毕业了，则在删除该系学生的同时，这个系及其系主任的信息也丢失了

总结：所以我们在设计数据库的 ，就需要满足一定的规范要求。

第一范式：属性不可分

eg:【联系人】（姓名，性别，电话），一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF；

第二范式：有主键，保证完全依赖。每个非主键的属性完全依赖于主键

image.png

该表明显说明了两个事物：学生信息和课程信息；正常的依赖应该是：学分依赖课程号，姓名依赖学号，但这里存在非主键字段对码的部分依赖，即与主键不相关，不满足第二范式的要求。

可能存在的问题：

· 数据冗余：每条记录都含有相同信息；

· 删除异常：删除所有学生成绩，就把课程信息全删除了；

· 插入异常：学生未选课，无法记录进数据库；

· 更新异常：调整课程学分，所有行都调整。

，可以通过分解来满足

image.png

第三范式：无传递依赖（非主键列A依赖于非主键列B，非主键列B依赖于主键的情况）

image.png

很明显，学院电话是一个冗余字段，因为存在依赖传递：（学号）→（学生）→（学院）→（学院电话）

可能会存在的问题：

· 数据冗余：有重复值；

· 更新异常：有重复的冗余信息，修改时需要同时修改多条记录，否则会出现数据不一致的情况 。

正确的做法：

image.png

6.高性能mysql技术

缓存表（汇总表）

假如一个网站23小时发出的消息数，在一个比较忙碌的网站下不可能随时维护一个精准的计数器，代替方案是每小时生成一张汇总表，这样比实时计算高效很多。

计数器表：针对更新计数器时遭遇并发问题，可以用一张独立的表存储计数器，将计数值保存在多行，每次随机进行一行的更新。

7. 数据库中 Where、group by、having 关键字：

答： 关键字作用：

1. where 子句用来筛选 from 子句中指定的操作所产生的的行；

2. group by 子句用来分组 where 子句的输出；

3. having 子句用来从分组的结果中筛选行；

having 和 where 的区别：

1. 语法类似，where 搜索条件在进行分组操作之前应用；having 搜索条件在进行分组操作之后应用；

2. having 可以包含聚合函数 sum、avg、max 等；

3. having 子句限制的是组，而不是行。

当同时含有 where 子句、group by 子句 、having 子句及聚集函数时，执行顺序如下：

1. 执行 where 子句查找符合条件的数据；

2. 使用 group by 子句对数据进行分组；对 group by 子句形成的组运行聚集函数计算每一组的值；最后用 having 子句去掉不符合条件的组。

8. 数据库基础

主键

能唯一区分不同记录的字段，主键一旦插入到表中，最好不要再进行修改。

主键的基本原则是不要带有业务逻辑，而应该使用BIGINT自增或者GUID类型，主键也不允许为NULL.

可以使用多个列作为联合主键，但是联合主键并不常用。

主键是一种特殊的唯一性索引。

外键

关系型数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。

主键就是聚集索引吗？主键和索引有什么区别？

主键是一种特殊的唯一性索引，其可以是聚集索引，也可以是非聚集索引。。InnoDB作为MySQL存储引擎时，默认按照主键进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集。所以，对于聚集索引来说，你创建主键的时候，自动就创建了主键的聚集索引。

9. 视图

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增删改查操作，视图通常是具有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易。

使用视图的场景有以下两种：

1. 不希望访问者获取整个表的信息，只暴露部分字段给访问者，所以就建立一个续表，就是视图。

2. 查询的数据来源于不同的表，而查询者希望已同意的方式查询，这样也可以建立一个试图，把多个表查询结果联合起来，查询者只需要从视图中获取数据，不需考虑数据来源表所带来的差异。

drop,delete与truncate的区别？

Drop 直接删除表、truncate清空表中的数据，再插入时自增ID从1开始； delete删除表中的数据。

· 不再需要一张表的时候，用drop

· 想删除部分数据行时候，用delete，并且带上where子句

· 保留表而删除所有数据的时候用truncate

10. 触发器

触发器是与表相关的数据对象，在满足定义条件时出发，并执行触发器中语句。通过触发器可以协助应用在数据库端确保数据库的完整性。

11.为什么使用自增列作为主键

1. 如果我们定义了主键，那么InnoDB会选择主键作为聚簇索引，如果没有显式定义主键，则InnoDB会选择第一个不包含NULL值的唯一（UNIQUE）索引作为主键，如果也没有这样的唯一索引，那么InnoDB会选择内置6字节长的ROWId作为隐含的聚簇索引。

2. 数据记录本身被存于主索引的叶子节点上，这就要求统一叶子节点内的各条数据按照主键存放。因此，每当有一条新的记录插入式，mysql会根据其主键，将其插入到适当的节点和位置，如果页面达到装载因子，则开辟一个新的页。

3. 如果表使用自增主键，name每次插入新的记录，记录就会顺序添加到当前索引结点的后续位置。当一页写满，就会自动开辟一个新的页。

4. 如果使用费自增主键，（比如身份证号码和学号），由于每次插入主键的值近似于随机，因此，每次新纪录都要被查入党现有索引页的中间某个位置，此时mysql不得不为了将新纪录插入到合适的位置而移动数据，这就增加了所有开销，同时，频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构。后续不得不通过opimize table来重建表并优化填充页面。

13. 超键、候选键、主键、外键分别是什么？

· 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

· 候选键（候选码）：是最小超键，即没有冗余元素的超键。

· 主键（主码）：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

· 外键：在一个表中存在的另一个表的主键称此表的外键。

其他问题

数据库索引在什么情况下是无效的

redis 缓存雪崩
preparedStatement

小结：数据库方面还是事务机制、隔离级别比较重要，当然了数据库索引是必考的问题。偶尔也会给你几个表，让你现场写 SQL 语句，主要考察 group by 和 having 等关键字。