Java面试题（数据库篇）

数据库

在面试的过程中数据库基本上每个面试官都会问到，作为一名程序员，数据库是必须要掌握的。数据库的一些基本SQL语句在这里就不过多赘述了，请读者自行百度。下买就简单的说一下我在面试过程中遇到的与数据库有关的面试题，希望对大家有所帮助。

数据库优化？

1.选择正确的存储引擎

一般常用的是MyISAM与INNODB。（myisam不支持事务和外键，只支持表级锁，存储快，效率高，没有事务日志，故障恢复数据较麻烦；innodb支持事务、外键、行级锁、表级锁，有事务日志，恢复数据较方便，它需要花费资源去处理事务，所以效率比myisam低，储存比较慢。）基于myisam和innodb各自的特点，需要事务支持，更新操作较多的项目，我们选用innodb；而查询操作较多的，我们选择myisam，比如说新闻门户网站。

2.数据表的设计

尽量采用三范式来减少冗余字段，根据业务需求也可以在适当的地方采用反三范式的设计，采用空间换时间来提升查询效率

分库分表有垂直切分和水平切分两种

垂直切分：

垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。
与"微服务治理"的做法相似，每个微服务使用单独的一个数据库。

垂直分表是基于数据库中的"列"进行，某个表字段较多，可以新建一张扩展表，将不经常用或字段长度较大的字段拆分出去到扩展表中。

垂直切分的优点：

• 解决业务系统层面的耦合，业务清晰
• 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
• 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

缺点：

• 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度
• 分布式事务处理复杂
• 依然存在单表数据量过大的问题（需要水平切分）

水平切分：

当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了。

水平切分分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。

库内分表只解决了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很大，大家还是竞争同一个物理机的CPU、内存、网络IO，最好通过分库分表来解决。

水平切分的优点：

• 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力
• 应用端改造较小，不需要拆分业务模块

缺点：

• 跨分片的事务一致性难以保证
• 跨库的join关联查询性能较差
• 数据多次扩展难度和维护量极大

3.创建合适的索引

根据业务需要，在更新较少，辨识度较高，查询较多等的列上建立合适的索引：常见的索引有主键索引、普通索引、唯一索引、组合索引、全文索引等。

索引的数据结构有B+树索引和hash索引：B+树索引是innodb引擎默认使用的数据结构，它通过一定的算法，将mysql表中数据分磁盘存放，少量的磁盘读取即可做到大量数据的遍历；Hash索引根据hash算法将数据，精确的存放，来查找某个数据的时候，根据相同的算法一次就能找到，等值查询，效率非常高，但hash索引只能等值查询，而且容易发生hash碰撞。

4.优化SQL语句

常见的索引失效：

1）在类型为数字索引列做算术运算；

2）like前面有%（解决：使用全文索引）；

3）避免使用is not null判断（解决：在设计表的字段时，尽量避免null字段，通常使用特殊的数据进行占位，比如int not null default 0、string not null default ‘ ’）；

4）or条件判断（or的两个条件都有索引的话能够命中索引；其中一个条件没有索引，那么or会引起索引失效）；

5）避免使用select * ；

6）exists优于in；

7）避免在字符串或者日期类型上进行函数运算；

8）最左前缀原则；

5.读写分离，主从复制

当项目并发量、数据量到达一定数量以后，单数据库的IO瓶颈，优化提升的效率就不是那么明显，这时候就需要采用其他的办法，比如集群然后负载均衡(nginx反向代理与负载均衡)，比较主流的解决方案是读写分离，主从复制。采用多个数据库来完成负载高并发 。主流思路是：一主多从(主库用于增删改，从库用于查询)。

它的运行流程如下：

1、Java应用对数据库进行增删改，主库将操作记录到binary（二进制文件）；

2、每个从库有一个IO线程去读取主库的日志，将执行的日志文件缓存到relay.log（中继日志）；

3、从库中SQL线程读取本库的中继日志，并执行，完成数据的复制。

数据库的三范式是什么？

第一范式：每列只存储一个数据

第二范式：每个表只存储一类信息

第三范式：从表的外键要存储主表的主键

什么是反三范式？

通过增加冗余数据或数据分组来提高数据库读性能。

说一下 ACID 是什么？

**Atomicity（原子性）：**一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。

**Consistency（一致性）：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。A:2500 B:2500 A+B: 5000 在这个事务中，无论如何，5000是不会变的。

**Isolation（隔离性）：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读已提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

**Durability（持久性）：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

mysql 的内连接、左连接、右连接有什么区别？

内连接关键字：inner join；左连接：left join；右连接：right join。

内连接是把匹配的关联数据显示出来；左连接是左边的表全部显示出来，右边的表显示出符合条件的数据；右连接正好相反。

mysql 索引是怎么实现的？

索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。

具体来说 MySQL 中的索引，不同的数据引擎实现有所不同，但目前主流的数据库引擎的索引都是 B+ 树实现的，B+ 树的搜索效率，可以到达二分法的性能，找到数据区域之后就找到了完整的数据结构了，所有索引的性能也是更好的。

说一下数据库的事务隔离？

READ-UNCOMMITTED：读未提交，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。
READ-COMMITTED：提已交读，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。
REPEATABLE-READ：可重复读，默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。
SERIALIZABLE：串行化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。

脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。

不可重复读 ：是指在一个事务内，多次读同一数据。

幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

SELECT语句完整的执行执行顺序？

FROM、WHERE、GROUP BY、 HAVING、计算所有的表达式、ORDER BY、SELECT、输出

乐观锁和悲观锁

乐观锁

每次获取数据的时候，都不会担心数据被修改，所以每次获取数据的时候都不会加锁，但是在更新数据的时候需要判断该数据是否被别人修改过。如果数据被其他线程修改，则不进行数据更新，如果数据没有被其他线程修改，则进行数据更新，由于数据没有进行加锁，期间该数据可以被其他线程进行读写操作。

乐观锁：比较适合读取操作比较频繁的场景，如果出现了大量的写入操作，数据发生冲突的可能性会增大，为保证数据的一致性，应用层需要不断的重新获取数据，这样会增加大量的查询操作，降低了系统的吞吐量。

一条查询SQL的执行流程和底层原理？

1.发送SQL请求：

（1）客户端按照Mysql通信协议将SQL发送到服务端，SQL到达服务端后，服务端会单起一个线程执行SQL。

（2）执行时Mysql首先判断SQL的前6个字符是否为select。并且语句中是否带有SQL_NO_CACHE关键字，如果没 有则进入查询缓存。

2.查询缓存：

查询缓存说白了就是一个哈希表，将执行过的语句及其结果以键值对的格式缓存到内存中。其中key是一个哈希值，由查询SQL、当前要查询的数据库、客户端协议版本等生成的，value就是查询结果。如果要绕过查询缓存，可以在SQL中加SQL_NO_CACHE字段，如：

SELECT SQL_NO_CACHE * FROM table

注：Mysql8.0版本开始取消查询缓存

3.解析器：

解析器执行流程分为两个阶段，词法解析和语法解析

（1）首先对SQL词法进行分析，将SQL从左到右一个字符、一个字符地输入，然后根据构词规则识别单词。将会生成4个Token，如下所示：

（2）然后对SQL语法进行解析，判断客户端传入的SQL语句是否满足Mysql语法。此时会生成一颗语法树，如下所示：

如果语法不对，将会收到如下提示

You have an error in your SQL syntax

如果解析器顺利生成语法树，就会将SQL送发到预处理器

5.预处理器：

（1）首先判断SQL语句中的列名是否存在于数据表中，再看看表名是否正确，如果不对，将返回如下错误提示

Unknown column xxx in 'where clause'

(2）预处理器对SQL进行权限验证，判断SQL是否有操作这个表的权限，若没有，则会返回如下错误信息

ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table 'xxx'

一切验证通过后将语法树传递给优化器

6.优化器：

优化器的任务就是对SQL语句进行优化，达到最快的执行效果，优化器对SQL优化完成后会将SQL变成一个执行计划交给执行器

7.查询SQL执行流程之执行器：

执行器就是根据执行计划来进行执行查询， 根据SQL的指令，逐条调用底层存储引擎，逐步执行。

MySQL定义了一系列抽象存储引擎API，以支持插件式存储引擎架构。Mysql实现了一个抽象接口层，叫做 handler(sql/handler.h)，其中定义了接口函数，比如：ha_open, ha_index_end, ha_create等等，存储引擎需要实现这些接口才能被系统使用。