【mysql面试题】mysql复习之常见面试题（一）

 本站以分享各种运维经验和运维所需要的技能为主

《python零基础入门》：python零基础入门学习

《python运维脚本》： python运维脚本实践

《shell》：shell学习

《terraform》持续更新中：terraform_Aws学习零基础入门到最佳实战

《k8》从问题中去学习k8s

《docker学习》暂未更新

《ceph学习》ceph日常问题解决分享

《日志收集》ELK+各种中间件

《运维日常》运维日常

《linux》运维面试100问

《DBA》db的介绍使用（mysql、redis、mongodb...）

思考一下问题：

1.数据库三范式是什么?

2.有哪些数据库优化方面的经验?

参考答案：

1.数据库三范式是什么?
数据库的三范式（Normal Forms，简称 NF）是关系数据库设计的基础，它们的目的是减少数据冗余、提高数据完整性，并支持数据库的可扩展性。下面是三范式的定义和解释：

### 第一范式（1NF）
第一范式要求表的每一列都是不可分割的基本数据项，即表中的所有字段值都是原子性的。此外，每一行都要有唯一性，通常通过引入一个唯一的行标识符，即主键，来实现。1NF 是设计关系数据库的最基本要求。

**示例**：
假设有一个包含学生信息的表，其中一个字段是学生参加的课程。如果这个字段包含多个课程（例如，“数学, 英语”），那么这个表就不符合第一范式。为了符合 1NF，每个课程应该在新的行中单独列出。

### 第二范式（2NF）
第二范式建立在第一范式的基础上，要求表必须是第一范式，且所有非主键字段都必须完全依赖于主键。如果一个表的主键是由多个字段组成的复合主键，那么表中的每个非主键字段都必须依赖于整个复合主键，而不是依赖于复合主键的一部分。

**示例**：
假设有一个表，其主键由两部分组成：学生ID和课程ID。如果表中还有一个字段是教师名称，而教师名称只依赖于课程ID，而不是学生ID，那么这个设计就不符合第二范式。为了符合 2NF，教师名称应该分离到另一个表中，其中课程ID是主键。

### 第三范式（3NF）
第三范式也建立在第一范式的基础上，并且要求表必须首先满足第二范式。此外，第三范式要求所有的非主键字段不仅要完全依赖于主键，而且还必须直接依赖于主键，不能通过其他字段间接依赖（消除传递依赖）。

**示例**：
继续上面的例子，假设现在有一个字段是教师的办公室号码，这个字段只依赖于教师名称而不是主键（学生ID和课程ID）。这种设计不符合第三范式，因为教师的办公室号码是通过教师名称这个非主键字段间接依赖于主键的。为了符合 3NF，教师的办公室号码应该移动到教师表中。

这三个范式是数据库设计中最常用的范式，它们帮助设计者创建既能避免数据冗余又能提高数据完整性的数据库结构。尽管存在更高级的范式（如 BCNF、4NF 和 5NF），在实际应用中，大多数数据库设计通常满足到第三范式就足够了。


2.有哪些数据库优化方面的经验?
数据库优化是一个涉及多个层面的复杂过程，旨在提高数据库系统的性能和效率。优化策略可以从查询优化、索引设计、系统配置、架构设计等多个角度进行。以下是一些常见的数据库优化经验：

### 1. 索引优化
- **创建有效的索引**：为常用的查询列和查询条件创建索引可以显著提高查询速度。关键是选择合适的列进行索引，如经常出现在 WHERE 子句、JOIN 条件或ORDER BY 子句中的列。
- **避免过多索引**：虽然索引可以加快查询速度，但每个额外的索引都会增加写操作（INSERT、UPDATE 和 DELETE）的成本。因此，需要平衡读取和写入操作的性能需求。
- **使用覆盖索引**：尽可能使用包含所有查询所需数据的索引，这样可以避免访问表的数据页，减少I/O操作。

### 2. 查询优化
- **优化SQL语句**：简化查询逻辑，避免复杂的子查询和不必要的表连接。使用合适的聚合策略和选择性高的条件先行过滤。
- **使用参数化查询**：避免SQL注入攻击的同时，可以帮助数据库重用执行计划，提高查询效率。
- **利用缓存**：对于重复查询相同结果的情况，可以使用查询缓存来避免重复的数据库访问。

### 3. 数据库架构优化
- **规范化与反规范化**：根据应用的查询和更新的特点，适当选择规范化（减少冗余、提高数据完整性）和反规范化（提高查询效率，减少JOIN操作）。
- **分区表**：对于非常大的表，可以考虑分区来提高查询性能和数据管理效率。
- **使用适当的数据类型**：选择合适的数据类型不仅可以减少存储空间，还可以加快查询处理速度。

### 4. 系统配置优化
- **内存和存储优化**：确保数据库有足够的内存，这对于提高缓存效率和整体性能至关重要。同时，使用高性能的存储解决方案，如 SSD。
- **配置数据库参数**：根据具体的工作负载调整数据库服务器的配置参数，如连接池大小、缓冲区大小、日志级别等。

### 5. 性能监控与调整
- **定期监控**：使用工具监控数据库的性能指标，如查询响应时间、锁等待时间、I/O操作等。
- **分析慢查询**：定期分析并优化执行时间长的查询，这些往往是性能瓶颈的来源。
- **容量规划**：根据监控数据进行容量规划，适时扩展硬件资源或优化系统架构。

通过实施这些策略，可以显著提高数据库的响应速度和处理能力，从而提高整个应用系统的性能和用户体验。

具体更具体的一些优化：
### 1. 使用 `PreparedStatement` 提高性能

`PreparedStatement` 相比于 `Statement` 通常能提供更好的性能和安全性，原因包括：

- **预编译**：`PreparedStatement` 允许数据库引擎预先编译 SQL 语句，这样当相同的 SQL 语句（只是参数不同）被多次执行时，可以重用已有的执行计划，从而节省了编译时间。
- **减少 SQL 注入风险**：使用 `PreparedStatement` 可以通过绑定参数的方式来插入变量值，这比拼接字符串的方式更安全，有效防止 SQL 注入攻击。
- **提高缓存效率**：由于 SQL 语句在结构上保持不变，只是参数在变化，因此数据库能够更有效地缓存这些语句。

### 2. 外键约束对性能的影响

外键约束确保了数据库的引用完整性，但它们确实会对插入和删除操作的性能产生影响：

- **插入性能**：每次插入数据时，数据库系统需要检查外键约束，确保引用的数据存在。
- **删除性能**：删除操作可能涉及到多个表的级联删除或更新，这增加了操作的复杂性和执行时间。
- **设计选择**：如果应用逻辑能够确保数据的完整性，可以考虑在数据库设计阶段去掉外键约束，以提高性能。但这样做需要非常小心，以避免数据不一致的问题。

### 3. 允许适当的冗余

在某些情况下，适当的数据冗余可以显著提高查询性能：

- **减少 JOIN 操作**：例如，存储回复数量和最后回复时间可以避免每次显示帖子时都要计算这些值。
- **提高数据读取速度**：直接从一个表中读取所有所需信息，而不需要多表查询。

### 4. `UNION` vs `UNION ALL`

- **去重与性能**：`UNION` 默认去除重复记录，这需要额外的计算来检查和排除重复项，而 `UNION ALL` 不进行去重处理，因此性能通常更高。
- **排序问题**：`UNION` 在合并结果集时可能会进行排序，而 `UNION ALL` 则简单地追加结果集，处理速度更快。

在设计和优化数据库时，选择使用 `UNION` 还是 `UNION ALL` 应基于是否需要去重和排序的实际需求。如果数据自然不重复或者应用逻辑确保了数据的唯一性，那么 `UNION ALL` 是一个更高效的选择。

总的来说，这些优化策略的选择和应用需要根据具体的业务需求和数据特性来定。理解每种策略的优势和潜在的缺点可以帮助您做出更合适的决策。