Java面试问题汇总(数据库) java

1.数据库三大范式、反模式

第一范式强调属性的原子性约束，要求属性具有原子性，不可再分解
第二范式强调记录的唯一性约束，表必须有一个主键，并且没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分
第三范式强调属性冗余性的约束，即非主键列必须直接依赖于主键

反模式：如果完全按照三大范式来设计表结构，会导致业务涉及表增多，查询数据需要多表联合查询，导致sql复杂，性能变差，不利于维护，也不利于分库分表，比如会在表中冗余存储城市id对应的城市名称
2.Mysql 架构图

文章图片

3.有一个组合索引（A,B,C），可以有哪几种查询方式优: select * from test where a=10 and b>50
差: select * from test where b = 50
优: select * from test order by a
差: select * from test order by b
差: select * from test order by c
优: select * from test where a=10 order by a
优: select * from test where a=10 order by b
【Java面试问题汇总(数据库)】差: select * from test where a=10 order by c
优: select * from test where a>10 order by a
差: select * from test where a>10 order by b
差: select * from test where a>10 order by c
优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b=10 order by b
优: select * from test where a=10 and b=10 order by c
优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b>10 order by b
差: select * from test where a=10 and b>10 order by c
总结：索引最左原则从最左边开始确定值。即a确定条件后再确定b, a b确定条件后再确定c.结合上述例子就可以看懂

最左匹配原则，直到遇到范围查询(>, <, between, like)就停止，比如a = 1 and b = 2 and c >3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，abd的顺序可以任意调整
= 和 in可以乱序，比如a = 1 and b =2 and c = 3建立(a, b, c) 索引可以任意顺序，mysql查询优化器会帮你优化

4.有如下表，找出每个城市的最新一条记录 id 城市人口信息创建时间
1 北京 100 info1 时间戳
2 北京 100 info2 时间戳
3 上海 100 info3 时间戳
4 上海 100 info4 时间戳
select name,MAX(time) from city GROUP BY name
5.InnoDB存储结构逻辑存储单元分为表空间(TableSpace) -> 段(segment) -> 区(extent) -> 页(page)
Mysql 8.0 InnoDB架构图

文章图片

表空间：所有数据都存在表空间中，表空间分系统表空间和独立表空间。

系统表空间
在安装数据库的时候默认会初始化一个以ibdata1命名的系统表空间，存储所有数据的信息以及回滚段信息，ibdata1默认的大小是10MB,在高并发情况下，会有性能影响，建议初始大小调整为1GB。
相关教程参考：https://blog.csdn.net/demonson/article/details/79863166

独立表空间
设置参数innodb_file_per_table = 1，目前MySQL默认都是独立表空间，每个表都有自己的表空间文件，存储对应表的B+树数据、索引和插入缓冲等信息，其余信息还是存储在共享表空间中

撤销表空间
包含撤销日志，初始化的时候会创建两个默认的撤销表空间

通用表空间
可以存储多个表的数据，相比独立表空间更节约元数据的内存开销

临时表空间
分会话临时表空间和全局临时表空间。
- 会话临时表空间，在第一个请求中，会话临时表空间从临时表空间池分配给会话，最多两个临时表空间，一个用于用户创建的临时表，另一个用于优化器创建的内部临时表，当会话断开时，临时表空间将被释放进入临时表空间池中；
- 全局临时表空间，用于存储用户创建的临时表的更改数据，用于回滚，在正常关闭或初始化中止时被删除，并在每次启动服务器时重新创建
段
表空间由段组成，一个表通常有数据段、回滚段、索引段等，每个段由N个区和32个零散的页组成
区
由连续的页组成，每个区大小固定1MB
页
一个区由64个连续页组成，页默认大小16KB

6.存储引擎的 InnoDB与MyISAM区别，优缺点，使用场景 ACID:
原子性（atomicity）、一致性（consistency）、隔离性（isolation）、持久性（durability）

存储引擎	InnoDB	MyISAM
存储文件	.frm表定义文件 .ibd数据文件	.frm表定义文件 .myd数据文件 .myi 索引文件
锁	表锁，行锁	表锁
事务	ACID	不支持
CRUD	读写	读多
count	扫表	专门存储的地方
索引结构	B+Tree	B+Tree

7.建立索引的原则

最左匹配原则，直到遇到范围查询(>, <, between, like)就停止，比如a = 1 and b = 2 and c >3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，abd的顺序可以任意调整
= 和 in可以乱序，比如a = 1 and b =2 and c = 3建立(a, b, c) 索引可以任意顺序，mysql查询优化器会帮你优化
尽量选择区分度高的索引，区分度公式count(distinct col)/count(*) ，表示字段不重复的比例，比例越大我们的扫描记录越少,比例一般是需要join的字段要求是0.1以上，即平均1条扫描10条记录
4. 索引不能参与计算，比如from_unixtime(create_time) = '2014-05-29' 就不能使用到索引，因为b+tree中存的都是数据表中的字段值，但进行检索时，需要把素有元素都应用到函数才能比较，成本大，应该改成create_time = unix_timestamp('2014-05-29')
尽量扩展索引，不要新建索引，比如表中已经有a索引，现在要加(a,b)索引，只需要修改原来的索引即可

8.索引失效情况总结索引底层是B+树实现

遵守最左匹配原则，中间断索引，使用范围查询
在索引列上做计算
索引字段使用 != 或者 < >
索引字段使用 is null 或者 is not null
使用通配符 %开头
索引字段是字符串，查询条件没有使用字符串
索引字段使用or

https://blog.csdn.net/wuseyuk...
9.B+Tree 索引和哈希索引限制 B+Tree索引：
分两类，聚集索引和普通索引
聚集索引，在创建表的时候，会创建一个主键，这个主键就是聚集索引，在索引叶子节点中存放了数据信息。InnoDB会给没有创建主键的表选择第一个不包含null值的唯一索引作为主键，如果唯一索引也没有，就会为该表创建一个6字节的rowid作为主键
普通索引，索引叶子节点并不包含所有行的数据，只保留键值，通过键来查找行数据

全值匹配，和索引中的所有列进行匹配
匹配最左前缀
匹配列前缀，可以只匹配某一列的值开头部分
匹配范围值，如果匹配的列不是主键，只能使用第一个索引来匹配范围，否则不走索引，如果匹配列是主键，可以不按照索引顺序来，走的是主键索引
精确匹配某一个列并范围匹配另外一列

哈希索引：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中行的速度很快
哈希索引数据并不是按照索引值顺序存储的，所以也无法用于排序
哈希索引不支持部分索引列匹配查找，因为哈希索引始终使用索引列的全部内容来计算哈希值
只支持等值比较查询，包括 =、 in()、<=>，不支持范围查询
数据访问速度快，当哈希冲突时，必须遍历链表中的所有行指针，直到查询到符合条件的行
哈希冲突多的话，一些索引维护操作的代代价很高

10.事务隔离级别，设置事务方法

read uncommitted(未提交读) ：可以看到未提交的数据,脏读
read committed (提交读)：只能读取已提交的数据，但多次读取的数据结果可能不一致，导致幻读
repeatable read(可重复读)：默认级别，可以重复读，解决了脏读问题，但会有幻读
serializable(可串行化)：最高隔离级别，强制事务串行执行，避免幻读问题

查询当前会话级别：select @@tx_isolation;
查看系统当前隔离级别：select @@global.tx_isolation;
设置当前会话隔离级别：set session transaction isolatin level repeatable read;
设置系统当前隔离级别：set global transaction isolation level repeatable read;
11.什么是MVCC, MySQL的MVCC原理 MVCC即多版本并发控制，它能在很多情况下避免加锁操作，降低开销，不同的存储引擎实现方式不同，有乐观并发控制和悲观并发控制
MySQL的InnoDB引擎，通过在每行记录后面保存两个隐藏的列来实现，一个列保存了行的创建时间，一个保存了行的过期时间（或删除时间）。实际存储的是系统版本号，每开始一个新的事务，系统版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。该MVCC只使用在repeatable read 和 read committed下
保存这两个额外的系统版本号，使大多数读操作都不用加锁，并且也能保证只会读到符合标准的行。缺点是需要额外的存储空间和维护工作。
12.Mysql死锁死锁是两个或者多个事务在同一资源上互相占用，并请求锁定对方资源，从而导致互相等待的现象。
死锁示例：

#事务1 start transaction; update stockprice set close = 45 where stock_id = 4 and date = '2019-1-1'; update stockprice set close = 20 where stock_id = 3 and date = '2019-1-3'#事务2 start transaction; update stockprice set high = 36 where sockt_id = 3 and date = '2019-1-3'; update stockprice set hight = 60 where stock_id = 4 and date = '2019-1-1';

两个事务分别执行两个更新语句，都执行第一个语句，锁定了该行数据，但该行数据将做为对方事务执行下条语句的条件，所以当事务继续执行第二条语句的时候，因为需要的条件所在行已被另外一个事务锁定，这是死锁现象
避免死锁的方法：

约定以相同的顺序访问表
大事务分小事务
一个事务中，一次锁定资源
锁升级，采用表锁

11.Msyql 执行SQL 过程

文章图片

客户端发送一条查询给服务器
服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
将结果返回给客户端

12.如何优化sql翻页

只让用户一页页翻，不能跳页
确定每页的边界值，通过where条件查询来优化
使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原有表获得需要的行

select name,sex,rating from mysql_test a inner join ( select id from mysql_test where sex='F' order by rating limit 20000,100 ) as x USING(id)

13.如何优化SQL语句

先看表的数据类型是否设计的合理，遵守选取数据类型越简单越小的原则
表中的碎片是否整理，MySQL表的碎片整理和空间回收
表的统计信息是否收集，只有统计信息准确，执行计划才可以帮助我们优化SQL
查看执行计划，检查索引的使用情况，没有用到索引，创建索引
创建索引需要判断这个字段是否适合创建索引，遵守建立索引的原则
创建索引后，通过explain分析，前后性能变化

14.如何分析explain执行计划

先查看type列，如果出现all关键词，就代表sql执行全表扫描
再看key列，如果null代表没有使用索引
再看rows列，如果越大，代表需要扫描的行数越多，相应耗时就长
最后看 extra列，是否有影响性能的 Using filesort 或者 Using temporary

explain 各个字段含义：https://blog.csdn.net/weixin_...
15.slect from a left join b on 条件和 select from a left join b where 条件一样么，为什么不一样，返回的结果不一样。
select * from a left join b on 条件会返回 a 中没有匹配的数据(left join 优先)
select * from a left join b where 条件只返回where中匹配的数据(where优先)
https://www.cnblogs.com/caowe...
16.数据连接池的工作机制 J2EE 服务器启动时会建立一定数量的池连接，并一直维持不少于此数目的池连接。客户端程序需要连接时，池驱动程序会返回一个未使用的池连接并将其表记为忙。如果当前没有空闲连接，池驱动程序就新建一定数量的连接，新建连接的数量由配置参数决定。当使用的池连接调用完成后，池驱动程序将此连接表记为空闲，其他调用就可以使用这个连接。
17.你了解继承映射吗，请简单讲讲你的理解继承关系的映射策略有三种：
① 每个继承结构一张表（table per class hierarchy），不管多少个子类都用一张表。
② 每个子类一张表（table per subclass），公共信息放一张表，特有信息放单独的表。
③ 每个具体类一张表（table per concrete class），有多少个子类就有多少张表。
第一种方式属于单表策略，其优点在于查询子类对象的时候无需表连接，查询速度快，适合多态查询；缺点是可能导致表很大。后两种方式属于多表策略，其优点在于数据存储紧凑，其缺点是需要进行连接查询，不适合多态查询。
18.数据库优化方法（1）选取最适用的字段属性
MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。
另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
（2）使用连接（JOIN）来代替子查询(Sub-Queries)
MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询
（3）使用联合(UNION)来代替手动创建的临时表
MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并到一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。
（4）事务
尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。
19.mysql的主从复制 MySQL主从复制是其最重要的功能之一。主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。
MySQL主从复制的两种情况：同步复制和异步复制，实际复制架构中大部分为异步复制。
复制的基本过程如下：
Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容。
Master接收到来自Slave的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置。
Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”。
Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。
20.讲一讲，数据库ACID的特性原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性指事务前后数据的完整性必须保持一致。
隔离性指多个用户并发访问数据库时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间数据要相互隔离。
持久性是指一个事务一旦提交，它对数据库中数据的改变就是永久性的，即便数据库发生故障也不应该对其有任何影响。
21.数据库乐观锁和悲观锁悲观锁
悲观锁（Pessimistic Lock），顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
Java synchronized 就属于悲观锁的一种实现，每次线程要修改数据时都先获得锁，保证同一时刻只有一个线程能操作数据，其他线程则会被block。
乐观锁
乐观锁（Optimistic Lock），顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
乐观锁一般来说有以下2种方式：
使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
使用时间戳（timestamp）。乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。
22.数据库的隔离级别

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据。
提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)。
可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读。
串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。
23.数据库水平切分与垂直切分垂直拆分就是要把表按模块划分到不同数据库表中（当然原则还是不破坏第三范式），这种拆分在大型网站的演变过程中是很常见的。当一个网站还在很小的时候，只有小量的人来开发和维护，各模块和表都在一起，当网站不断丰富和壮大的时候，也会变成多个子系统来支撑，这时就有按模块和功能把表划分出来的需求。其实，相对于垂直切分更进一步的是服务化改造，说得简单就是要把原来强耦合的系统拆分成多个弱耦合的服务，通过服务间的调用来满足业务需求看，因此表拆出来后要通过服务的形式暴露出去，而不是直接调用不同模块的表，淘宝在架构不断演变过程，最重要的一环就是服务化改造，把用户、交易、店铺、宝贝这些核心的概念抽取成独立的服务，也非常有利于进行局部的优化和治理，保障核心模块的稳定性。
垂直拆分：单表大数据量依然存在性能瓶颈
水平拆分，上面谈到垂直切分只是把表按模块划分到不同数据库，但没有解决单表大数据量的问题，而水平切分就是要把一个表按照某种规则把数据划分到不同表或数据库里。例如像计费系统，通过按时间来划分表就比较合适，因为系统都是处理某一时间段的数据。而像SaaS应用，通过按用户维度来划分数据比较合适，因为用户与用户之间的隔离的，一般不存在处理多个用户数据的情况，简单的按user_id范围来水平切分。
通俗理解：水平拆分行，行数据拆分到不同表中，垂直拆分列，表数据拆分到不同表中。
24.JDBC操作数据库时，如何提升读取数据的性能，以及更新数据的性能要提升读取数据的性能，可以指定通过结果集（ResultSet）对象的setFetchSize()方法指定每次抓取的记录数（典型的空间换时间策略）；要提升更新数据的性能可以使用PreparedStatement语句构建批处理，将若干SQL语句置于一个批处理中执行。
25.Statement 和 PreparedStatement 的区别与Statement相比，①PreparedStatement接口代表预编译的语句，它主要的优势在于可以减少SQL的编译错误并增加SQL的安全性（减少SQL注射攻击的可能性）；②PreparedStatement中的SQL语句是可以带参数的，避免了用字符串连接拼接SQL语句的麻烦和不安全；③当批量处理SQL或频繁执行相同的查询时，PreparedStatement有明显的性能上的优势，由于数据库可以将编译优化后的SQL语句缓存起来，下次执行相同结构的语句时就会很快（不用再次编译和生成执行计划）。
为了提供对存储过程的调用，JDBC API中还提供了CallableStatement接口。存储过程（Stored Procedure）是数据库中一组为了完成特定功能的SQL语句的集合，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。虽然调用存储过程会在网络开销、安全性、性能上获得很多好处，但是存在如果底层数据库发生迁移时就会有很多麻烦，因为每种数据库的存储过程在书写上存在不少的差别。
26.JDBC的反射通过反射com.mysql.jdbc.Driver类，实例化该类的时候会执行该类内部的静态代码块，该代码块会在Java实现的DriverManager类中注册自己,DriverManager管理所有已经注册的驱动类，当调用DriverManager.geConnection方法时会遍历这些驱动类，并尝试去连接数据库，只要有一个能连接成功，就返回Connection对象，否则则报异常。