Loading

Java面试题15 数据库

自己尝试通过打字来回答一些网上常见的面试题，答案仅代表我自己的观点

说下SQL优化

首先我没做过很大的项目，所以SQL优化方面的经验甚少，但我也看过一些像数据库系统概念啊、InnoDB存储引擎啊还有高性能MySQL这些书，所以我可以结合我的学习经验来从几方面谈谈SQL优化。

不要查询额外的数据，这是第一点，可以从两个层面来说。一是从应用层面，你的后端应用不要向数据库查询你需要之外的数据，比如网上常说的什么不要SELECT *，但实际的问题不出在SELECT *上，如果你查询出的关系足够小，全部列都是你需要的，那么SELECT *没问题，而就算你不SELECT *，假设你使用Mybatis的RowBounds分页，那么它会查询出你要查的整个关系，然后缓存起它需要的页区间，剩下的直接丢弃，这也是一种资源浪费。

从服务器层面来说，一条SQL语句要查询出多少条数据？实际查了多少条数据？实际查询的数据中有多少是必须要查的？有时候，我们一条SQL语句可能只需要查出1条数据，而实际查了几万条数据，造成它的原因有很多，比如你没有建立合适的索引，或者MySQL的优化器选择了错误的或预期外的执行计划，比如MySQL5.7之前对子查询的优化很差，所以我们经常用外连接替代子查询，或者使用延迟加载技术，而在MySQL8中这个问题已经被解决了，我们无需再为其做额外的努力了。

建立合适的索引，这是第二点，上一点中也提到过，这不仅仅在匹配查询时有用，在DISTINCT、GROUP BY和ORDER BY时仍然有用。索引本身的有序性让我们可以快速的检索到其中有用的数据，并且在一些需要排序的场景中（比如上面说的三个）省去外部文件排序的步骤，大大降低磁盘访问次数。

小心的组织WHERE条件，这是第三点，很多时候，优化器能重组你的WHERE条件，让它在当前具有的索引下最优，但有些时候它也不能。所以你应该思考你现在有什么索引，根据这个答案来组织你的WHERE条件，让索引都被用上。这部分的技巧比如以最左前缀原则组织，或者使用in改写范围查询。

反范式和冗余，范式是减少数据库维护者工作量和可能发生的错误的最佳实践，但范式的存在却给表设计增加了很多约束，比如我们要完全遵循范式设计，那么一个简单的查询可能需要连接多个表，这其中是多重for循环构成的笛卡尔积，在表很大的情况下性能很差，所以根据业务需求，我们可以大胆的对一些数据进行反范式设计，在表中建立冗余列或者建立统计表。但在应用层我们腰围数据的安全存储做更多的努力。

外键虽然能对表中可以出现的数据做一定的约束，但也引入了插入时必须进行额外查询的性能消耗，如果我们可以在应用层保证数据不会出现问题，那么我们可以不用外键。

分库分表，主从复制：大型系统或分布式系统中的常见做法，即把压力分布到多个数据库、多个表或多个数据库服务器实例上

限制更新频率：OLTP型应用大部分都是查询大于增删改的，也是关系型数据库比较适用的场景。关系型数据库中的更新操作很耗性能，它可能会需要对B+树进行多次的节点分裂，并且对于每一个相关的索引都要被更新。而且在使用应用层缓存的场景下，所有缓存都将失效。所以，一些应用会限制某些不太重要的业务的更新频率，比如微博限制1年更新一次用户名。

频繁更新的数据使用NoSQL：难免有一些频繁更新的数据，比如评论、点赞数据，这时我们可以使用NoSQL数据库代替。

164. 数据库的三范式是什么？

我不知道什么三范式（这里装纯），不过我看过一本书叫《数据库系统概念》（这里装逼）知道数据库的几种范式，这里我说一下吧

首先范式的英文是Normal Form，也就是关系数据库设计的一些通用模式，你可以遵守也可以不遵守，而且现在是分布式应用的天下，为了获得更好的并发性，反范式的设计经常出现。

  1. 第一范式：第一范式要求所有的字段都是简单字段。用数据库系统概念这本书中的一个例子来说就是，课程ID这个列你不能设计成系名+课程号，这样就是一个复合字段了，需要客户代码对课程ID分解才能获取到系名和真正的课程号。而如果课程ID中的系名并不作为该课程的所在系的参考的话，而是有其它的字段表示课程的所在系，那么这个字段就是一个简单字段了，符合第一范式。
  2. 第二范式我不知道，但我知道有一个叫B什么的范式（Boyce-Codd，BCNF）。如果用书里函数依赖来讲的话有点...复杂，简单来说就是在列上你找不到任何显式的冗余。比如一个学生表，通过dept_id可以找到它的所在系，但学生表中还保存了dept_name——即所在系的名字。这完全可以通过dept_id去系的表中查询到，这种信息是冗余的。
  3. 第三范式比B什么的范式放宽了一点要求，它允许轻微的冗余，即非候选码的列可以确定候选码中的列。考虑导师关系表，假设一个导师只能在一个部门从教，那么学生ids_id和部门iddept_id就能确定导师idi_id，而i_id也能确定部门iddept_id，这不符合B什么的范式，但是dept_id是候选码中的列，候选码就是s_id和dept_id。所以符合第三范式。如果用函数依赖的话来讲，第三范式可以让某些约束发生变化时函数依赖得以保持。
  4. 还有第四范式，第四范式能解决行冗余。

165. 一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几？

自增ID的计算在MySQL中是存储引擎来做的，所以不能给出准确的答案，不过我可以说InnoDB的行为

  1. 对于InnoDB引擎来讲，它只在内存里记录自增ID的信息，所以重启了自增ID就会重新计算，再插入就是6
  2. 对于MyISAM引擎来讲，它将这个ID信息记录到磁盘中，所以重启也会保留这个最大ID，再插入就是8

166. 如何获取当前数据库版本？

select version();

167. 说一下 ACID 是什么？

ACID是关系型数据库最重要的事务特性，非关系型数据库往往不提供这些特性。ACID是四个特性，它们分别对数据库中的数据提供一些保护，而且它们所提供的保护之间是有交集的。

  1. A是原子性，它所保证的是一个事务中不管有多少行SQL语句，它都应该被看成一个整体，这个整体要么执行成功，要么执行失败，不会出现执行一半儿，另一半没执行的情况。
  2. C是一致性，它所保证的是在事务的执行开始前的一致性约束在事务结束后依然满足。举个例子，比如转账事务的一致性约束是转账前后两个人的财产综合不变，转帐前A和B的财产总和是2000，那么转账操作结束后，A和B的财产总和仍然是2000。为什么会出现不一致现象呢？考虑转账一半，数据库宕机了，A的钱扣了，B的钱还没加，或者两个事务同时执行，读写发生交错，在某些情况下也会出现不一致的现象，这就不展开说了，如果面试官想听我可以给您简单画个例子。
  3. I是隔离性，隔离性是指，一个事务不会感受到另一个事务在它执行的中间穿插执行，当然，那就成串行执行了，所以隔离性只是保证看起来，一个事务不会在另一个事务间穿插执行。实现的原理就是使用各种方法生成可串行化调度，这其中包括锁、MVCC多版本控制机制、真正的串行调度等。
  4. D是持久性，持久性是指，即使数据库宕机数据库状态也不会不一致，数据库会安全的保存那些未完成的事务，等重启进行恢复执行或者回滚操作。

ACID特性说了这么多，就是从四个不同的方面来保证数据库中数据的一致性。

168. char 和 varchar 的区别是什么？

首先从定义上来说，char是定长字段，就算你保存的数据没到指定的长度，存储空间也会被用掉，varchar是变长字段，当你保存的数据没到指定长度时，存储空间不会被占用。

但数据类型如何存储是存储引擎来实现的，所以我们仍然没法脱离存储引擎来具体讨论二者的差别，即使是具体到存储引擎，InnoDB也提供了四种行记录的格式，它们使用完全不同的形式存储数据。而且高版本的InnoDB存储引擎中，字符串类型的数据的长度代表字符长度而非字节长度，有一些字符集就是不定长的，所以CHAR的实际存储和VARCHAR的实际存储几乎一致。

169. float 和 double 的区别是什么？

  1. float是单精度浮点数，使用4字节存储，也就是32位浮点数
  2. double使用8字节存储，也就是64位浮点数

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

  1. 内连接必须是参与连接的两个表的条件都满足的行才会在返回结果中
  2. 左连接以左表条件为准，右表如果不符合条件显示NULL
  3. 右连接以右表条件为准，左表如果不符合条件显示NULL

171. mysql 索引是怎么实现的？

从宏观上说，索引只不过也是一个文件。

MySQL的索引是存储引擎来实现的，不同的存储引擎有不同的索引实现方式，我只研究过InnoDB引擎的索引实现方式，那种通过观察比对ibd文件来了解索引是如何形成的感觉挺有趣（这句是对面试官装个逼），下面我简单说说：

首先，不管是什么索引，InnoDB都采用B+树来实现，当然除了自动生成的自适应哈希索引。

对于主索引，InnoDB采用的是聚簇索引，即把表中的数据和主索引保存在一起，叶子节点中保存的就是一整行数据。对于辅助索引，InnoDB保存了索引列和主键列，所以查询辅助索引时还要有一次主索引的查询。

自适应哈希索引是InnoDB自己建立的，用于加速热数据的查询，你可以看作是一个内部的缓存。

InnoDB还实现了全文索引，但我并没有研究过全文索引的实现原理。

172. 怎么验证 mysql 的索引是否满足需求？

MySQL提供了explain语句来查看语句执行的细节，通过其中的Using index列可以看到是否使用了索引。

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

MySQL提供SQL标准的四种隔离级别，安全性从低到高分别是：

  1. READ UNCOMMITED：可以读到其它事务未提交的数据
  2. READ COMMITED：可以读到其它事务已提交的数据
  3. REPEATABLE READ：在一个事务中多次读到的数据是相同的，不管其它事务是否修改并提交了数据
  4. SERIALIZABLE：可串行化，所有事物完全以冲突可串行化的方式执行，它们的执行结果和串行执行后的结果是一致的。

网上很多的文章、博客说“XXX隔离级别能消除脏读，可串行化隔离级别才能消除脏读、幻读、不可重复读的问题”，包括我之前刷面试题的时候的所谓的“标准答案”也是这么写的（这里和面试官装逼，因为它手里拿的肯定是这份标准答案）。一看到这些言论我就很无语，SQL标准确实规范了不同隔离级别该提供什么样的保护，但各家有各家的实现，MySQL中事务隔离级别提供怎样的保护完全是由存储引擎来决定的，没法一概而论，比如InnoDB在REPEATABLE READ级别就能解决脏读、幻读和不可重复读的问题，已经能提供全部ACID特性了，甚至有的存储引擎根本不支持某些隔离级别。

（这里再度装逼）我们应该去阅读正在使用的存储引擎的文档而不是自己还没搞懂就在网上发一些错误的言论，我们的社区里已经有太多错误的离谱的言论了，CSDN是重灾区。

下面说说什么是脏读，幻读，不可重复读：

  1. 脏读指可以读到其它事务未提交的数据
  2. 幻读是在一个事务增加或删除行时产生，这样其它的事务在读取时有可能第一次读到n行第二次就读到n+1行，这些多出来或少掉的行称为幻影行。在SQL标准中，SERIALIZABLE才能解决幻读问题，但在InnoDB中通过Gap Lock（间隙锁）和Next-Key Locking技术，在REPEATABLE READ级别中就能解决这个问题了。
  3. 不可重复读指一个事务两次读取的值不一样

174. 说一下 mysql 常用的引擎？

  1. InnoDB：它是为了我们最常编写的事务型应用设计的，这种应用的特点是数据量大，但每次操作都只是操作其中的一条或一小部分数据。它具有如下特性
    1. 支持完整ACID特性
    2. 最小锁粒度可以支持到行级别
    3. 提供SQL标准的4种隔离级别
    4. 使用B+树组织索引
    5. 支持自适应哈希索引
    6. 新版本已经支持全文索引
    7. 插入缓冲/二次写/预读等高级特性且一直在持续优化
  2. MyISAM：它适合统计型应用，这种应用的特性是每次读出大量的数据进行统计计算分析。
    1. 不支持事务
    2. 表级别锁
    3. 不支持全文索引

175. 说一下 mysql 的行锁和表锁？

表级锁就是加锁在表上呗，一个表只能同时被一个事务所访问，行级锁就是加锁在行上，一个表可以同时被多个事务所访问，但一个行不可以。

行锁粒度小，所以不易产生冲突，相较于表级锁更容易产生死锁，但死锁带来的影响较小，可以有更好的并发性能，同时为每一行维护一个锁开销大。它适合OLTP（在线事务应用）

表级锁和行级锁相反，粒度大，易产生冲突，不会出现死锁，开销小，并发性能低。比较适合OLAP（在线分析应用）

176. 说一下乐观锁和悲观锁？

乐观锁就是常说的CAS，或称为无锁。它的思路像一个乐天派，它会假设我的此次操作不会有人跟我竞争，那么我就不用加锁保护。然后它进行操作时会检测是否真的有冲突，如果有，操作失败，你可以再次操作或放弃操作。

悲观锁就是常说的锁了，它的思路像一个悲观的人，无论是否真的有人和它竞争，只要有竞争的可能性，它就使用锁对临界资源进行保护。

乐观锁的开销小，不会产生线程挂起，适合高并发场景，悲观锁的开销大，往往还要涉及到几次系统调用和线程挂起，不太适合高并发场景。

对于乐观锁的优化，往往都是重试次数过多转为悲观锁，对于悲观锁的优化往往都是先使用乐观锁，当碰到冲突时再转化为悲观锁。我说的是JVM里的做法。

177. mysql 问题排查都有哪些手段？

  1. 通过各种运行日志查看系统的异常
  2. 通过慢查询日志找出消耗时间过多的查询
  3. 通过explain观察语句执行的情况

178. 如何做 mysql 的性能优化？

优化是一个很深的话题，深入理解InnoDB存储引擎、高性能MySQL那么厚的两本书也没有面面俱到，不过有一些通用的原则可以说说：

  1. 合理建立索引
  2. 设法将引擎和MySQL服务器层面的数据交换量更小，比如只查询需要的字段
  3. 选择合适的存储引擎
  4. 合理设计表结构，合理利用冗余
  5. 分库分表
  6. 主从复制

MySQL特点总结

1. Insert Buffer / Change Buffer

意义是避免随机磁盘写。

对非聚集索引（也就是辅助索引）的插入操作，如果插入的页没有被缓冲，就先放到Insert Buffer中。然后以一定的频率将Insert Buffer中的插入按顺序合并，此时首先，Insert Buffer中所有处于同一页的插入都只需要一次磁盘读写了，其次，刷新Insert Buffer的线程（Master Thread）可以以顺序的形式进行插入，比如先插入磁盘块1中的数据，再插入磁盘块2中的数据。

限制：

  1. 必须是辅助索引
  2. 必须不能唯一（因为检查唯一性需要磁盘读）

Change Buffer是Insert Buffer的升级，它不仅针对插入，还针对删改。

2. Double Write 两次写

两次写发生在刷新内存中的脏页到磁盘页时。

如果没有两次写，在将脏页刷新到磁盘过程中如果服务器崩溃，那么磁盘中的文件将损坏（因为可能有坏损的数据结构）。

两次写将脏页先写到共享表空间文件，当这个写入完成后，再将脏页写入到真正的表空间文件中。此时，即使写入失败，表空间文件受损，也可以用共享表空间来恢复。如果共享表空间的写入出现问题，真正的表空间中的数据没有被损坏，再次启动服务器时会读取redolog，将那些没刷回磁盘的修改执行一遍。

3. 自适应哈希索引（AHI）

对热点数据在内存中建立基于Hash的缓存。

4. 异步IO（AIO）

异步IO操作在高并发情况下比同步IO能获得更好的并发量，而且异步IO也允许引擎在合适的时间进行IO Merge。

innodb_use_native_aio参数可以看到当前是使用系统原生的AIO还是InnoDB模拟的AIO。

5. 刷新邻接页

刷新脏页时将处于一个区的脏页也一并刷新，这样也许能减少未来的随机磁盘访问。

binlog / redolog / undolog 区别

暂时还没复习到undolog，先说前两个。

binlog是MySQL本身记录的，它记录的是事务对数据库的更改，有几种更改模式(ROW, STATEMENT, MIXED)，但都是按行进行记录的。binlog主要用于宕机后的人工数据恢复。

redolog是InnoDB存储引擎的文件，它记录的是对InnoDB表的更改，它记录的不是行，而是磁盘页的物理情况。redolog是InnoDB在崩溃后重启时会自动读取并执行修复操作的。

InnoDB对索引的优化

1. 覆盖索引

InnoDB的辅助索引的索引项格式是(索引值， 主索引值)，如果你SELECT语句要求返回的列中没有辅助索引项之外的列，就免去了通过主索引值去查询聚集索引这一步了。

2. 大批数据不走索引

当你在查询辅助索引时，要返回的数据量大概占表的20%时，InnoDB可能选择不用索引。

因为对于返回数据的每一条，InnoDB都得使用辅助索引中保存的主索引值去查询聚集索引，这是一笔可观的随机磁盘搜索开销。所以InnoDB在这种时候可能更加倾向于使用没有随机磁盘访问的全表扫描，全表扫描的性能可能更高。

3. Multi-Range-Read MRR优化

对于上面一条，MySQL高版本中给了一种优化办法。

即，将通过索引列扫描出来的所有主键值排序，然后按顺序去查找聚集索引。

4. Index Condition Pushdown ICP优化

将WHERE子句中的一些条件下放到存储引擎层面，在存储引擎层过滤掉尽可能多的无用数据，降低存储引擎和MySQL之间的数据交换量，提高性能。

posted @ 2022-06-24 16:28  yudoge  阅读(40)  评论(0)  编辑  收藏  举报