20240117面试练习题7

1. 用自己的话说一下什么是三范式？为什么要遵循三范式？实际开发中一定要严格遵循三范式吗？为什么？

第一范式(1NF)：每个列都不可以再拆分。
第二范式(2NF)：在第一范式的基础上消除非主键对主键的部分依赖。
第三范式(3NF)：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

为什么遵循三范式：
减少数据冗余: 避免同一数据在多个地方重复存储，节省存储空间，减少数据一致性维护的复杂度。
提高数据完整性: 通过外键约束来维护不同数据表之间的一致性，确保数据库的引用完整性。
简化数据维护: 更新、删除操作更简单，因为数据不重复，更新单个记录即可。

实际开发不一定遵循三范式：
性能提升: 减少了表间关联，可以在某些情况下提高查询性能。
简化数据模型: 对于某些应用来说，简化的数据模型更容易理解和维护。
灵活性提升: 在没有严格的外键约束下，应用程序开发和数据输入可能更加灵活。

---

2. 关系型数据库和非关系数据库有什么区别？它们对应的使用场景分别有哪些？

1、成本: Nosql数据库简单易部署，基本都是开源软件，不需要像使用Oracle那样花费大量成本购买使用，相比关系型数据库价格便宜。
2、查询速度: Nosql数据库将数据存储于缓存之中，而且不需要经过SQL层的解析，关系型数据库将数据存储在硬盘中，自然查询速度远不及Nosql数据库。
3、存储数据的格式: Nosql的存储格式是key,value形式、文档形式、图片形式等等，所以可以存储基础类型以及对象或者是集合等各种格式，而数据库则只支持基础类型。
4、扩展性: 关系型数据库有类似join这样的多表查询机制的限制导致扩展很艰难。Nosql基于键值对，数据之间没有耦合性，所以非常容易水平扩展。
5、持久存储: Nosql不使用于持久存储，海量数据的持久存储，还是需要关系型数据库
6、数据一致性:非关系型数据库一般强调的是数据最终一致性，不像关系型数据库一样强调数据的强一致性，从非关系型数据库中读到的有可能还是处于一个中间态的数据，Nosql不提供对事务的处理。

对于需要处理复杂多表关联、强调数据一致性和事务处理能力的应用，关系型数据库是更好的选择。例如，银行和金融行业的核心业务系统、ERP系统等通常使用关系型数据库。
非关系型数据库则更适合处理大量数据、对实时性要求高的应用。例如，互联网行业的推荐系统、搜索引擎、日志分析等场景，非关系型数据库可以发挥其高并发、分布式和灵活查询的优势。

---

3. MySQL 常用引擎有哪些？

1、InnoDB
InnoDB 是 MySQL 5.1 之后默认的存储引擎，它支持事务、支持外键、支持崩溃修复和自增列。如果对业务的完整性要求较高，比如张三给李四转账，需要减张三的钱，同时给李四加钱，这时候只能全部执行成功或全部执行失败，此时可以通过 InnoDB 来控制事务的提交和回滚，从而保证业务的完整性。

优缺点分析
InnoDB 的优势是支持事务、支持外键、支持崩溃修复和自增列；它的缺点是读写效率较差、占用的数据空间较大。

2、MyISAM
MyISAM 是 MySQL 5.1 之前默认的数据库引擎，读取效率较高，占用数据空间较少，但不支持事务、不支持行级锁、不支持外键等特性。因为不支持行级锁，因此在添加和修改操作时，会执行锁表操作，所以它的写入效率较低。

优缺点分析
MyISAM 引擎保存了单独的索引文件 .myi，且它的索引是直接定位到 OFFSET 的，而 InnoDB 没有单独的物理索引存储文件，且 InnoDB 索引寻址是先定位到块数据，再定位到行数据，所以 MyISAM 的查询效率是比 InnoDB 的查询效率要高。但它不支持事务、不支持外键，所以它的适用场景是读多写少，且对完整性要求不高的业务场景。

3、MEMORY
内存型数据库引擎，所有的数据都存储在内存中，因此它的读写效率很高，但 MySQL 服务重启之后数据会丢失。它同样不支持事务、不支持外键。MEMORY 支持 Hash 索引或 B 树索引，其中 Hash 索引是基于 key 查询的，因此查询效率特别高，但如果是基于范围查询的效率就比较低了。而前面两种存储引擎是基于 B+ 树的数据结构实现了。

优缺点分析
MEMORY 读写性能很高，但 MySQL 服务重启之后数据会丢失，它不支持事务和外键。适用场景是读写效率要求高，但对数据丢失不敏感的业务场景。

---

4. InnoDB 和 MyISAM 有什么区别？

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

2、InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3、InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

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

---

5. 为什么阿里巴巴《Java开发手册》不建议使用物理外键？使用物理外键会带了什么问题？

性能下降: 每次对数据进行DELETE或UPDATE操作都必须考虑外键约束 数据库都会判断当前操作是否违反数据完整性，性能下降。
死锁：使用外键，外键关联的数据查询要去另一张表，获取额外的锁，容易造成死锁。
扩展性问题：触发器 外键 这种依赖于数据库本身的特性 可扩展性较差。 分库分表方便，在水平拆分和分库的情况下，外键是无法生效的。

带来问题：
1、性能问题
额外的数据一致性校验查询。当每一次向订单明细表添加数据时，会检查订单表里是否存在对于的外键约束数据id是否存。

2、并发问题
外键约束会启用行级锁，主表写入时会进入阻塞。每一次在订单明细表插入数据会检查订单明细表的记录，此时会给该订单id添加一个读锁。在并发条件下，如果此时不管什么原因，有个请求需要修改订单表的订单 ID ，此时订单表就会开启一个写锁。那么此时订单明细表就会进入一个阻塞的状态，进而引发系统崩溃。

3、级联删除问题
多层级联删除会让数据变得不可控，触发器也严格被禁用。
订单表里有订单类型的外键，订单明细表里有订单表的外键。如果此时我们想删除订单类型，应为订单表与订单类型表存在外键约束关系，那么对于订单表里的此类型的订单都将会被删除，订单明细表的明细也将会删除。如果存在更多的外键约束关系，则会删除一连串的数据，此时就会变得不可控。这好比一个树，订单类型表是树根，订单表是树枝，订单明细表是树叶，如果删除树根，则树枝和树叶也会被删除。
这些操作都是在数据库层面发生的，都是无法追溯的，是一件很麻烦的事。所以在实际开发中数据库层面的外键约束是严令禁止的。

4.数据耦合和迁移
数据库层面数据关系产生耦合，数据迁移维护困难。

如果某一天，订单明细表的数据量过大，基于 MySQL 本身的性能应用已经不足以支持相对应的业务需求。此时计划将订单明细数据迁移到 HBASE 这样的大数据数据库进行存储。
那么此时是不是需要将原有的主外键约束关系给去掉，之后又如何保证数据的一致性问题呢，此时只能通过程序层面进行约束，这是不是又回到了应用层面。
在实际的项目经验过程中，随着业务发展，有些业务数据就会非常大，因为MySQL关系型数据库本身的性能问题，此时有些数据就会进行一些迁移，这是非常常见的。

---

6. 物理删除和逻辑删除有什么区别？日常开发中会使用哪种删除方式？为什么？

1.数据恢复性的差异
物理删除执行后，数据从数据库中永久删除，除非有备份，否则无法恢复。相比之下，逻辑删除仅标记数据为“已删除”，不实际删除数据，这使得数据可以轻松恢复。

2.对存储空间的影响
物理删除释放了占用的存储空间，有助于维护数据库的高效运行。而逻辑删除虽然隐藏了数据，但实际上并未释放空间，可能导致存储空间的浪费。

3.数据库性能影响
长期使用逻辑删除可能导致数据库性能下降，因为标记为“已删除”的数据仍占用空间，并可能参与某些数据库操作。物理删除通过清除无用数据，有助于保持数据库的整洁和高效。

4.安全性考量
从安全的角度来看，物理删除能更有效地保护数据隐私，因为数据被永久移除。逻辑删除虽然为数据提供了一定的保护，但如果没有适当的安全措施，被标记为“已删除”的数据仍可能被不当访问。

从项目的规模来讲
一般小型系统可以采用物理删除，因为数据恢复的成本，或者说数据的价值，相比较使用逻辑删除的开发、运维付出要少。但是物理删除不是随意删除,要认识到删除操作的风险，重要数据应该设计历史表，删除之前将删除的数据复制到历史表。
中大型项目应该采用逻辑删除,有一句话“数据是无价的”。必须承认，在很多时候，数据的价值是远远高于人工的成本的。

从数据价值来讲
价值比较高的数据，如电商系统的订单之类的，毫无疑问，一般都是只允许逻辑删除的，及时必须要做物理删除,也要通过备份表、备份日志等方式保证数据可以快速恢复。
价值比较低的数据，比如用户操作日志之类，这种数据价值不高，而且数据量很大，在资源有限的情况下，可以考虑物理删除,但是这种危险操作尽量也不要由系统功能去做。

---

7. 内连接和外连接有什么区别？什么是自连接？举例说明一下？

内连接，也被称为自然连接，只有两个表相匹配的行才能在结果集中出现。返回的结果集选取了两个表中所有相匹配的数据，舍弃了不匹配的数据。由于内连接是从结果表中删除与其他连接表中没有匹配的所有行，所以内连接可能会造成信息的丢失。

外连接不仅包含符合连接条件的行，还包含左表(左连接时)、右表(右连接时)或两个边接表(全外连接)中的所有数据行。SQL外连接共有三种类型：左外连接(关键字为LEFT OUTER JOIN)、右外连接(关键字为RIGHT OUTER JOIN)和全外连接(关键字为FULL OUTER JOIN)。外连接的用法和内连接一样，只是将INNER JOIN关键字替换为相应的外连接关键字即可。

两张表结构和数据内容完全一样的表，在做数据处理的时候，我们通常会给它们分别重命名来加以区分，然后进行关联。

1、在同一张表内进行比较

SELECT e1.Name AS employee_name
FROM Employee AS e1, Employee AS e2
WHERE e1.ManagerId=e2.Id
AND e1.Salary>e2.Salary

查找比昨天温度高的所有日期的 Id

SELECT w1.Id
FROM weather w1
JOIN weather w2
ON DATEDIFF(w1.RecordDate,w2.RecordDate)=1
WHERE w1.Temperature>w2.Temperature

2、找出列的组合
查找共用同一车站的所有公交路线

SELECT * FROM route R1, route R2
WHERE R1.stop=R2.stop;

3、查找部分内容重复的记录
查找价格相同但商品名称不同的商品信息

SELECT DISTINCT P1.name, P1.price
FROM Products P1, Products P2
WHERE P1.price = P2.price
AND P1.name != P2.name;

---

8. 创建索引时会锁表吗？为什么？

因为在 MySQL 5.6 之前，创建索引时会锁表，所以，在早期 MySQL 版本中一定要在线上慎用，因为创建索引时会导致其他会话阻塞（select 查询命令除外）。

但这个问题，在 MySQL 5.6.7 版本中得到了改变，因为在 MySQL 5.6.7 中引入了 Online DDL 技术（在线 DDL 技术），它允许在创建索引时，不阻塞其他会话（所有的 DML 操作都可以一起并发执行）。有了 Online DDL 技术之后，在添加索引时，会对原本进行操作，并且允许和 DML（数据操作语言 INSERT、UPDATE、DELETE、SELECT）一起并发执行了。

---

9. 聚簇索引和非聚簇索引有什么区别？

聚簇索引：
找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引，修改聚簇索引其实就是修改主键。

非聚簇索引：
索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。

区别：
聚簇索引和非聚簇索引的根本区别是数据记录的排列顺序和索引顺序是否一致，聚簇索引表记录的排列顺序与索引的排列顺序一致，优点是查询速度快，缺点是对表进行修改速度比较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致

非聚簇索引指定了表中记录的逻辑顺序，数据记录的物理顺序和索引的顺序不一致，聚集索引和非聚集索引都采用了B树的结构，但非聚簇索引的叶子层顺序与实际的数据叶并不相同。在对聚簇索引查询时，聚簇索引的速度一般要比非聚簇索引快。

---

10. 聚簇索引等于主键索引吗？聚簇索引的生成规则是啥？

聚簇索引并不完全等于主键索引，因为一张表从结构上来讲，可以没有主键（索引），如果没有主键（索引），那么聚簇索引就不再是主键索引了。

生成规则：
表中有无有主键索引，如果有，则使用主键索引作为聚簇索引；
如果没有主键索引，则看表中有无唯一索引，那么使用第一个唯一索引；
如果以上两个条件都不满足，InnoDB 则会生成隐藏聚簇索引。

---