JAVA面试高频考点之数据库篇_java面试问到数据库

JAVA面试高频考点之数据库篇

因为最近刚刚整理完数据库篇的面试题，就汇总一下，有兴趣的可以看一看。

数据库篇

一.优化

1.定位慢查询MYSQL中，如何定位慢查询？

2.SQL执行计划SQL语句执行慢，如何分析呢？

3.索引了解过索引吗？（什么是索引）

索引的底层数据结构了解过吗？

B树和B+树的区别是什么呢?

什么是聚簇索引什么是非聚簇索引？/什么是聚集索引，什么是二级索引（非聚集索引）？

什么是回表？

知道什么叫覆盖索引吗？

MYSQL超大分页怎么处理？

索引创建原则有哪些？

什么情况下索引会失效？

4.SQL优化谈一谈你对SQL优化的经验

二.其他面试题

1.事务相关

1.1 事务特性：（事务的特性是什么？可以详细说一下吗？）

1.2 隔离级别：并发事务带来哪些问题?怎么解决这些问题呢？MySQL的默认隔离级别是？

1.3 undo log和redo log的区别？

1.4 MVCC：事务中的隔离性是如何保证的？（你解释下MVCC）

2.主从同步原理MySQL同步原理

3.分库分表：解决海量数据存储你们项目用过分库分表吗

数据库篇

一.优化

1.定位慢查询

MYSQL中，如何定位慢查询？

1. 聚合查询
2. 多表查询
3. 表数据量过大查询
4. 深度分页查询

表象：页面加载过慢、接口压测响应时间过长（超过1s）

方案一：开源工具

1. 调试工具：Arthas
2. 运维工具：Prometheus、Skywalking（查看接口的执行情况、时间）

方案二：MySQL自带慢日志

慢查询日志记录了所有执行超过指定参数(long_querey_time，单位秒，默认10秒)的所有SQL语句日志。

1. 若要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

#开启慢查询日志查询开关

slow_query_log=1

#设置慢日志的时间为2秒，SQL语句超过两秒，就会记录慢日志

long_query_time=2

配置完成后，重启mysql服务器测试，可以通过查询Localhost-slow.log文件查看语句执行信息。

回答：

曾经在测试接口的时候发现非常慢，压测的结果大概在5秒左右；

我们系统中当时采用了运维工具(skywalking)，可以检测出哪个接口，最终因为是sql的问题；

在Mysql中开启了慢日志查询，我们设置的时间是2秒，一旦sql超过2秒就会记录到日志中(调试阶段)。

2.SQL执行计划

SQL语句执行慢，如何分析呢？

根据上面的几种查询方式，可以使用MySQL自带的分析工具DESC或EXPLAIN来分析。

1. 通过key和key_len（即sql实际命中的索引以及索引占用的大小）检查是否命中了索引（即索引本身是否失效）
2. 通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描(即index和all)
3. 通过extra建议判断，是否出现了回表情况（using index condition），如果出现了，可以尝试添加索引或修改返回字段来回复。

3.索引

了解过索引吗？（什么是索引）

1. 索引是帮助MySQL高效获取数据的有序数据结构
2. 索引能提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）
3. 通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗

索引的底层数据结构了解过吗？

MySQL的搜索引擎也就是InnoDB引擎默认采取的是B+树的结构存储索引

1. 一方面，它的阶数更多，路径更短（B树是5阶，每个节点最多存储4个Key）
2. 另一方面，B+树的磁盘读写代价更低，非叶子节点只存储指针，叶子节点存储数据
3. 最后一点，B+树便于扫库和区间查询，叶子节点是一个双向链表

B树和B+树的区别是什么呢?

1. 第一：在B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有的数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定
2. 第二：在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表

什么是聚簇索引什么是非聚簇索引？/什么是聚集索引，什么是二级索引（非聚集索引）？什么是回表？

1. 聚簇索引数据与索引放到一块，B+树里的叶子节点里保存整行的数据，有且只有一个，一般都是表的主键
2. 二级索引是数据与索引分开存储，B+树的叶子节点里保存对应的主键，可以有多个

回表是指通过二级索引找到对应的主键，再根据主键通过聚集索引找到整行数据。

知道什么叫覆盖索引吗？

覆盖索引是指返回的列被查询的索引全部包含。

1. 比如典型的根据主键id查询就是覆盖索引，叶子节点包含了整行数据
2. 同时我们应该避免返回的列需要创建索引，因为这样可能会触发回表，降低效率，所以应当避免使用select *

MYSQL超大分页怎么处理？

MySQL超大分页一般出现在使用limit语句对大量数据进行处理和排序，越到后面，查询效率越低。

我们可以通过覆盖索引+子查询来提高查询效率。

具体的做法是先根据id查询作为子查询的结果，再和原表根据id联查，因为id查询是覆盖索引，所以效率会快很多。

原语句：select * from user limit 9000000,10;

优化：selct * form user u,(select id from user order by id limit 9000000,10) t where u.id=t.id

索引创建原则有哪些？

1. 当数据量比较大，查询比较频繁的表，适合建立索引（超过10万）
2. 经常作为查询条件、排序、分组的字段
3. 字段内容区分度高
4. 内容长，使用前缀索引
5. 尽量使用联合索引
6. 控制索引的数量
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束

嗯，这个情况有很多，不过都有一个大前提，就是表中的数据要超过10万以上，我们才会创建索引，并且添加索引的字段是查询比较频繁的字段，一般也是像作为查询条件，排序字段或分组的字段这些。还有就是，我们通常创建索引的时候都是使用复合索引来创建，一条sgl的返回值，尽量使用覆盖索引，如果字段的区分度不高的话，我们也会把它放在组合索引后面的字段。如果某一个字段的内容较长，我们会考虑使用前缀索引来使用，当然并不是所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导致新增改的速度变慢。

什么情况下索引会失效？

索引失效的情况有很多，可以说一些自己遇到过的，不要张口就得得得说一堆背诵好的面试题适当的思考一下，回想一下，更真实)

当使用联合索引的时候

1. 违反最左前缀法则
2. 或者使用%开头进行模糊查询
3. 或者字符串没加单引号，此时发生了类型转换
4. 或者对索引列进行运算操作，比如substring
5. 或者在范围查询右侧的列

嗯，这个情况比较多，我说一些自己的经验，以前遇到过的

比如，索引在使用的时候没有遵循最左匹配法则，第二个是，模糊查询，如果%号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。

我们之前还遇到过一个就是，如果使用了复合索引，中间使用了范围查询，右边的条件索引也会失效所以，通常情况下，想要判断出这条sql是否有索引失效的情况，可以使用explain执行计划来分析

4.SQL优化

谈一谈你对SQL优化的经验

1. 一般在创建表时，可以对相应的字段优化
2. 或者我们在创建索引时，可以考虑对索引进行优化，索引创建原则
3. 有时候，当读数据操作较多时，可以考虑读写分离
4. 如果数据量较大，也可以通过分库分表进行优化
5. 也可以对SQL语句进行优化，避免索引失效，避免使用select * 等等

面试官: sql的优化的经验

候选人:嗯，这个在项目还是挺常见的，当然如果直说sql优化的话，我们会从这几方面考虑，比如建表的时候、使用索引、sql语句的编写、主从复制，读写分离，还有一个是如果量比较大的话，可以考虑分库分表

面试官:创建表的时候，你们是如何优化的呢?

候选人:这个我们主要参考的阿里出的那个开发手册《嵩山版》，就比如，在定义字段的时候需要结合字段的内容来选择合适的类型，如果是数值的话，像tinyint、int、bigint这些类型，要根据实际情况选择。如果是字符串类型，也是结合存储的内容来选择char和varchar或者text类型

面试官:那在使用索引的时候，是如何优化呢?

候选人:[参考索引创建原则 进行描述]

面试官:你平时对sql语句做了哪些优化呢?候选人:嗯，这个也有很多，比如SELECT语句务必指明字段名称，不要直接使用select"，还有就是要注意SOL语句避免造成索引失效的写法;如果是聚合查询，尽量用union all代替union，union会多一层过滤，效率比较低;如果是表关联的话，尽量使用inner join ，不要使用left join right join，如必须用一定要以小表为驱动

二.其他面试题

1.事务相关

1.1 事务特性：（事务的特性是什么？可以详细说一下吗？）

ACID

1. 原子性 atomicity
2. 一致性 consistency
3. 隔离性 isolation
4. 持久性 durability

举一个例子来说明，比如A向B转账，A的账户扣除500，B的账户增加500，原子性操作体现在这个转账过程对AB要么都成功，要么都失败；

在转账的过程中，数据要一致，A和B的账户都发生了500块的变化，A扣了500，B必须增加500；

在转账的过程中，隔离性就是指在A给B转账时不能受其他事务干扰的；

在转账的过程中，持久性体现在事务提交后，要把数据持久化（可以说是落盘操作）。

1.2 隔离级别：并发事务带来哪些问题?怎么解决这些问题呢？MySQL的默认隔离级别是？

带来的问题

1. 脏读：在读取数据的时候读取到了其他事务没提交的数据
2. 不可重复读：两次查询数据获得的结果不一样，第一次查询时不存在，但是在第二次查询前，有另一个事务提交了增删改操作，从而导致第二次查询结果和第一次不一样
3. 幻读：在查询时，数据不存在，但是进行插入操作时，又提示数据已经存在，这时可能是因为事务1在插入操作前，事务2正好插入了数据，但是事务1进行查询时，又发现数据依然不存在，好像出现了幻影

解决办法

对事务进行隔离，常见的几种隔离级别有

1. 未提交读 read uncommitted 三个问题都解决不了
2. 读已提交 read committed可以解决脏读问题
3. 可重复读 repeatable read 解决脏读、不可重复读
4. 串行化 serializable 三个问题都能解决

MySQL采用的是可重复读。虽然序列化可以解决三个问题，但是它也会导致效率变慢，所以一般不考虑使用它。

面试官:并发事务带来哪些问题?候选人:我们在项目开发中，多个事务并发进行是经常发生的，并发也是必然的，有可能导致一些问题:第一是脏读，当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是”脏数据”，依据”脏数据”所做的操作可能是不正确的。第二是不可重复读:比如在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据，那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样，这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。第三是幻读 (Phantom read): 幻读与不可重复读类似。它发生在一个事务(T1) 读取了几行数据接着另一个并发事务 (T2)插入了一些数据时。在随后的查询中，第一个事务 (T1) 就会发现多了些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。。

面试官:怎么解决这些问题呢? MySQL的默认隔离级别是?候选人，解决方案是对事务进行隔离MySOL支持四种需离级别，分别有:第一个是，未提交读 (read uncommitted) 它解决不了刚才提出的所有问题，一般项目中也不用这个。第二个是读已揭交(read committed) 它能解决脏读的问题的，但是解决不了不可重复读和幻读。第三个是可重复读 (repeatable read》 它能解决脏读和不可重复读，但是解决不了幻读，这个也是mysql默认的隔离级别。第四个是串行化(serializable) 它可以解决刚才提出来的所有问题，但是由于让是事务串行执行的，性能比较低。所以，我们一般使用的都是可重复读。

mysq默认的隔离级别:可重复读

1.3 undo log和redo log的区别？

1. redo log记录了数据页的物理变化，当服务器宕机时可以用来同步数据。
2. undo log记录了数据变化的逻辑，当事务回滚时，通过逆操作恢复原来的数据。
3. redo log保证了事务的持久性，undo log保证了事务的原子性和一致性。

面试官: undo log和redo log的区别候选人:好的，其中redo log日志记录的是数据页的物理变化，服务宕机可用来同步数据，而undo log不同，它主要记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据，比如我们删除一条数据的时候，就会在undo log日志文件中新增一条delete语句，如果发生回滚就执行逆操作;redo log保证了事务的持久性，undo log保证了事务的原子性和一致性

1.4 MVCC：事务中的隔离性是如何保证的？（你解释下MVCC）

MVCC是MySQL的多版本并发控制。它是指维护一个数据的多个版本，使得读写操作没有冲突。

MVCC主要依靠三个方面来实现，分别是

1. 隐藏字段
2. trx_id（事务id）：记录每次操作的事务id，是自增的
3. roll_pointer（回滚指针）：用来指向上一个版本的事务版本记录地址
4. undo log
5. 回滚日志，存储老版本数据
6. 版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表
7. readView解决的是一个事务查询选择版本的问题
8. 根据readView的匹配规则和当前的一些事务id判断该访问哪个版本的数据
9. 不同级别的快照读是不一样的，最终的访问结果不一样
10. RC：每次执行快照读时生成readView
11. RR：仅在事务第一次执行快照读时生成readView，后续复用

面试官:事务中的隔离性是如何保证的呢?(你解释一下MVCC)

候选人:事务的隔离性是由锁和mvcc实现的。

其中mvcc的意思是多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，它的底层实现主要是分为了三个部分，第一个是隐藏字段，第二个是undo log日志，第三个是readView读视图。

隐藏字段是指：在MySQL中给每个表都设置了隐藏字段，有一个是trx id(事务id)，记录每一次操作的事务id，是自增的;另一个字段是rollpointer(回滚指针)，指向上一个版本的事务版本记录地址

undo log主要的作用是记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表

readView解决的是一个事务查询选择版本的问题，在内部定义了一些匹配规则和当前的一些事务id判断该访问那个版本的数据，不同的隔离级别快照读是不一样的，最终的访问的结果不一样。如果是rc隔离级别，每一次执行快照读时生成ReadView，如果是rr隔离级别仅在事务中第一次执行快照读时生成Readview，后续复用

2.主从同步原理

MySQL同步原理

MySQL同步原理的核心在于binlog文件，它包含了所有的ddl（数据定义语句）和dml（数据操作语句）语句。

1. 首先，主库在事务提交时，会将数据变更记录在自己的bin log文件中；
2. 然后，从库会通过io thread线程读取bin log文件，将这些变更写入到自己的中继日志relay log中；
3. 最后，从库会根据relay log重做中继日志中的事件，从而改变反映自己的数据。

3.分库分表：解决海量数据存储

你们项目用过分库分表吗

1. 业务介绍
2. 根据自己简历上的项目，像一个数据量较大业务（请求书多或业务累积大）
3. 达到了什么样的量级（单表1000万或超过20G）
4. 具体拆分策略
5. 水平分库，将一个库的数据分到多个库中，解决海量数据存储和高并发问题
6. 水平分表，解决单表存储和性能问题
7. 垂直分库，根据业务进行拆分，高并发下提高磁盘IO和网络连接数
8. 垂直分表，冷热数据分离，多表互不影响

12都会用到中间件sharding-sphere、mycat；2用到的比较少，134用的比较多。