# 数据库基础 ## 主键、索引、外键 **1、什么是主键** 主键是一列，其值可以**唯一标识**表中的每一行数据，每个表只能有一个主键，而且主键的值不能重复，也不能包含NULL值，通常用来保证数据的唯一性和用于在表中查找特定的行。 **2、主键、外键、索引的区别** **定义：** 主键：唯一标识一条记录，不允许重复，不允许为空 外键：外键是一个表中的字段，其值是另一个表的主键，用于建立两个表之间的关系。 索引：没有重复值，但可以有一个空值 ，用于快速查询到数据。 **作用：** 主键：用于唯一标识表中每一行的字段 外键：主要用于和其它表建立联系 索引：为了提高查询排序的速度 **区别：** 外键是一个表中的字段，它与另一个表的主键形成关联，用于建立表之间的关系。 主键和外键通常都与索引有关，但索引不一定是主键或外键。 ## 一条SQL查询语句是如何执行的？ #### 简要回答 1. **连接阶段**：由服务器端的**连接器**组件负责，在客户端与 MySQL 服务器之间建立连接，并验证用户权限。 2. **查询缓存**（仅限 MySQL 8.0 前）：检查**是否命中缓存**，若有完全相同且有效的查询结果可以直接返回。 3. **解析与预处理**：解析 SQL 语法，检查语法是否正确，并生成抽象语法树，然后，**预处理器**进行一些语义检查，验证表和字段是否存在。 4. **优化器**：基于统计信息和成本模型，考虑多种执行方案，并选择最优执行计划（如索引选择、JOIN 顺序）。 5. **执行器**：根据选择的执行计划，调用存储引擎接口，并按执行计划读取数据并处理（排序、聚合等）。 6. **存储引擎**（如 InnoDB）：负责从磁盘或内存读取数据，返回给执行器。 7. **返回结果**：执行器进行必要的处理（如过滤、排序）后，将结果集返回客户端，可能分批次传输。 ------ #### 详细回答 1. 连接阶段： - 当我们在**客户端**（如命令行工具、应用程序）输入并执行一条 SQL 查询时，首先需要与**数据库服务器**建立一个网络连接。 - 这个过程包括 **TCP/IP 协议**的三次握手，以及**数据库层面的认证**，比如验证用户名和密码。 - 连接成功后，服务器会为这个连接分配一个**独立的线程**来处理后续的请求。 2. 查询缓存（MySQL 8.0 前）： - 服务器接收到 SQL 语句后，会先检查 查询缓存。这是一个位于**内存中**的区域，存储了之前执行过的查询语句及其结果。 - 如果当前查询与缓存中的某个查询**完全一致**（包括 SQL 语句本身、连接的数据库、客户端的协议版本等），并且缓存**仍然有效**（比如涉及的表没有被修改），那么服务器会直接从缓存中返回结果，无需执行后续的解析、优化和执行过程。 - **需要注意的是，在 MySQL 8.0 及更高版本中，查询缓存功能已经被移除。** 这是因为在并发写入场景下，查询缓存的维护成本很高，容易成为性能瓶颈。因此，在现代数据库系统中，通常不再依赖查询缓存进行优化。 3. 解析与预处理： - 如果查询缓存未命中，服务器会将 SQL 查询语句发送给解析器。**解析器**会对 SQL 语句进行**词法分析**（将语句分解成一个个词法单元，如关键字、标识符、操作符等） 和 **语法分析**（根据 SQL 语法规则检查语句是否合法，生成一个抽象语法树 AST）。 - 如果语法有错误，**解析器**会直接返回错误信息。如果语法没问题，**预处理器**根据抽象语法树，进一步检查 SQL 语句的合法性，例如，检查表名、字段名是否存在，是否有权限执行该查询等，它还会进行一些语义上的检查和转换。 4. 优化器： - 优化器的目标是找到执行查询的**最优执行计划**。它会考虑多种可能的执行方式，并**评估**它们的**成本**（如 I/O 次数、CPU 消耗等）。 - 优化器会利用**统计信息**（如表的大小、索引的选择性等）来做出决策。 - **常见的优化策略包括**： ① 选择合适的索引。 ② 决定表的连接顺序。 ③ 选择合适的连接算法（如嵌套循环连接、哈希连接、合并排序连接）。 ④ 改写查询语句，使其更高效。 - 最终，优化器会生成一个最优的**执行计划**（Execution Plan），它描述了如何执行查询的步骤。 5. 执行器： - 执行器根据优化器生成的执行计划，**调用存储引擎的接口**来执行查询。 - 执行器会按照执行计划的步骤，**从存储引擎获取数据**，进行过滤、排序、连接等操作。例如，如果执行计划指示使用某个索引进行查找，执行器就会调用存储引擎的索引查找接口。 - 执行器会逐步处理数据，并将结果返回给客户端。 6. 存储引擎（以 InnoDB 为例）： - 存储引擎是数据库系统中负责**数据存储和检索**的核心组件。不同的存储引擎有不同的特点和优势（如 InnoDB、MyISAM 等）。 - 执行器通过存储引擎的 API 来访问和操作数据文件。 - 存储引擎负责数据的读取、写入、更新、删除以及事务管理、锁机制等。 7. 返回结果： - 执行器将最终的查询结果返回给客户端。 - 客户端应用程序接收到结果后，可以进行进一步的处理和展示。 ------ #### 知识拓展 1. MySQL执行一条SQL查询语句的流程示意图（以MySQL8.0为例） ，如下所示： ![SQL_execution_order.jpg](http://cdn.notes.kamacoder.com/a52a80da-cb2e-4d2d-a7b2-dd3b733a4d47.jpg) 2. 面试官可能的追问1—为什么 MySQL 8.0 移除了查询缓存？ - **答**：缓存失效频繁（表更新即失效），维护成本高且命中率低。现代优化器能生成更高效执行计划，且应用层缓存（如 Redis）更灵活。 3. 面试官可能的追问2—优化器如何决定使用某个索引？ - **答**：基于索引的选择性（唯一性）、统计信息（基数）和查询条件。可通过 `EXPLAIN` 查看 `possible_keys` 和 `key` 字段。 4. 面试官可能的追问3—如何分析一条慢 SQL 的执行瓶颈？ - **答**： ① 使用 `EXPLAIN` 查看执行计划（索引使用、扫描行数）。 ② 开启慢查询日志（`slow_query_log`）捕获耗时操作。 ③ 检查锁竞争（`SHOW ENGINE INNODB STATUS`）。 ## 解释一下SQL中的JOIN操作？ ### **简要回答** SQL中的`JOIN`操作用于将来自两个或多个表的数据根据某些条件结合在一起。常见的`JOIN`类型有： 1. **INNER JOIN**：返回两个表中匹配的记录。 2. **LEFT JOIN（或LEFT OUTER JOIN）**：返回左表所有记录以及右表中匹配的记录，右表没有匹配的则用NULL填充。 3. **RIGHT JOIN（或RIGHT OUTER JOIN）**：返回右表所有记录以及左表中匹配的记录，左表没有匹配的则用NULL填充。 4. **FULL JOIN（或FULL OUTER JOIN）**：返回左表和右表中所有记录，匹配的部分显示实际数据，未匹配的部分显示NULL。 5. **CROSS JOIN**：返回两个表的笛卡尔积，即每一行与另一表的每一行进行组合。 ## 解释一下数据库的三大范式？ ### **简要回答** 数据库的三大范式（1NF、2NF、3NF）是为了减少数据冗余和避免异常操作而设计的规范。它们分别是： 1. **第一范式（1NF）**：要求数据表中的每个字段只能包含原子值。 2. **第二范式（2NF）**：要求满足 1NF，并且所有非主属性完全依赖于主键。 3. **第三范式（3NF）**：要求满足 2NF，并且每个非主属性直接依赖于主键，而不通过其他非主属性间接依赖。 ------ ### **详细回答** #### **1. 第一范式（1NF）** - **定义**：数据库表中的每列必须是原子性的，即每个字段的值不可再分。 - 要求： - 每个字段的数据都是不可再分的基本数据项（如字符串、整数等）。 - 表格的每个字段都应该包含一个单一的值（不允许出现集合、数组等复杂数据类型）。 - 例子： - 如果一个表格有一个字段“电话号码”，这个字段中不能存储多个电话号码（如`1234567890, 9876543210`），而应该将其拆分为多个字段或多行。 **不符合 1NF 的例子**： |用户ID|用户名|电话号码| | :-: | :-: | :-: | |1|张三|1234567890, 9876543210| |2|李四|1122334455| **符合 1NF 的例子**： |用户ID|用户名|电话号码| |:-:|:-:|:-:| |1|张三|1234567890| |1|张三|9876543210| |2|李四|1122334455| #### **2. 第二范式（2NF）** - **定义**：数据库表满足 1NF，并且所有非主属性完全依赖于主键。也就是说，消除部分依赖。 - 要求： - 表格的所有非主键列必须完全依赖于主键，而不是依赖于主键的一部分。 - 对于复合主键（即主键由多个字段组成的情况），如果某些非主属性只依赖于主键的部分字段，则违反了 2NF。 - 例子： 假设有一个表格存储学生和课程的成绩，其中表格的主键是 `（学生ID，课程ID）`，如果学生的姓名也出现在表格中，那么姓名只依赖于学生ID，而不依赖于课程ID，这就是部分依赖，违反了 2NF。 不符合 2NF 的例子： | 学生ID | 课程ID | 姓名 | 成绩 | | :---: | :----: | :--: | :--: | | 1 | 101 | 张三 | 90 | | 1 | 102 | 张三 | 80 | | 2 | 101 | 李四 | 85 | | 2 | 103 | 李四 | 88 | 这里，“姓名”字段仅依赖于`学生ID`，而与`课程ID`无关，因此“姓名”是部分依赖。 **符合 2NF 的例子**（拆分为两个表）： - 学生表： | 学生ID | 姓名 | | :----: | :--: | | 1 | 张三 | | 2 | 李四 | - 成绩表： | 学生ID | 课程ID | 成绩 | | :----: | :----: | :--: | | 1 | 101 | 90 | | 1 | 102 | 80 | | 2 | 101 | 85 | | 2 | 103 | 88 | #### **3. 第三范式（3NF）** - **定义**：数据库表满足 2NF，并且所有非主属性不依赖于其他非主属性（即消除传递依赖）。 - 要求： - 表格中的非主属性不应该依赖于其他非主属性，而应直接依赖于主键。 - 如果存在某个非主属性A依赖于另一个非主属性B，并且B依赖于主键，那么B不应出现在同一个表中。 - 例子： 假设有一个表格存储员工的ID、部门ID、部门名称、员工姓名和工资。如果部门名称依赖于部门ID，那么它是间接依赖于员工ID（主键），违反了 3NF。 不符合 3NF 的例子： |员工ID| 员工姓名 | 部门ID | 部门名称 | 工资 | | :---: | :------: | :----: | :--------: | :--: | |1| 张三 | 101 | 人力资源部 | 5000 | |2| 李四 | 102 | 技术部 | 6000 | |3| 王五 | 101 | 人力资源部 | 5500 | 这里，`部门名称`依赖于`部门ID`，而`部门ID`又依赖于`员工ID`，因此存在传递依赖，违反了 3NF。 符合 3NF 的例子（拆分为三个表）： - 员工表： | 员工ID | 员工姓名 | 部门ID | 工资 | | :----: | :------: | :----: | :--: | | 1 | 张三 | 101 | 5000 | | 2 | 李四 | 102 | 6000 | | 3 | 王五 | 101 | 5500 | - 部门表： | 部门ID | 部门名称 | | :----: | :--------: | | 101 | 人力资源部 | | 102 | 技术部 | ## 什么是数据库的分片和分区？有什么区别？ **分片（Sharding）**： - **概念**：分片是将数据水平切分到多个独立的数据库实例或服务器上，每个分片存储数据的一个子集。 - **应用场景**：适用于大规模分布式系统，提升数据库的扩展性和性能。 - 特点 ：- 数据分布在不同的物理节点上。 - 每个分片通常有独立的存储和计算资源。 **分区（Partitioning）**： - **概念**：分区是将单个数据库表的数据按照某种规则划分成多个部分，每个部分称为一个分区，所有分区仍然在同一个数据库实例中。 - **应用场景**：适用于管理和查询大表，提升查询性能和维护效率。 - 特点 ：- 分区在同一个数据库实例内。 - 分区之间共享数据库的资源。 **区别**： - **范围**：分片涉及多个数据库实例或服务器，分区则是在单个数据库实例内。 - **目的**：分片主要用于扩展数据库的容量和处理能力，分区主要用于优化大表的管理和查询性能。 - **复杂性**：分片通常比分区更复杂，需要处理数据分布、路由等问题，而分区则相对简单。 ## 什么是数据库的连接池？为什么要使用连接池？ 数据库连接池是管理数据库连接的技术，当需要建立数据库连接时，会从连接池中获取可用的连接，而不直接创建连接。 - 降低频繁创建销毁数据库连接带来的开销 - 数据库连接池可以设置最大连接数量，能够限制应用程序对数据库系统的并发访问，提高系统稳定性 ## 什么是数据库的外键？有什么作用？ 数据库的外键是一个表中的字段，它引用了另一个表的主键。 外键的主要作用是维护数据库中表之间的数据一致性，确保引用关系的完整性。 **外键的性能影响**：外键约束会增加数据库的管理和检查开销，尤其是在进行插入、更新或删除操作时，会增加额外的检查操作。因此，在高性能要求的系统中，外键有时会被禁用。 ## MySQL和Redis的区别？ mysql是关系型数据库，基于磁盘存储，底层是表、行、列结构，事务性完备ACID， redis是键值对数据库，基于内存，底层有多种数据结构：String、hash、LIst、set、zset ，事务功能一般； mysql持久化机制主要靠redolog实现，基于innoDB存储引擎，适用于处理复杂查询、事务处理、大量数据集的场景 redis持久化机制主要靠AOF和RDB快照实现，读写速度效率很高， ## 解释一下数据库的备份和恢复策略？ 数据库的备份是指将数据库的数据复制并存储在安全的地方，以防止数据丢失或损坏。恢复策略则是在数据丢失或损坏后，使用备份来还原数据库到某个一致的状态。 备份策略通常包括： 全备份：定期复制整个数据库。 增量备份：只复制自上次全备份或增量备份后有变化的数据。 差异备份：复制自上次全备份后有变化的数据。 恢复策略通常包括： 完全恢复：使用全备份和所有相关的增量备份来还原数据库。 部分恢复：只恢复受损的部分数据。 时间点恢复：将数据库还原到特定的时间点。 简单来说，备份策略是确保数据安全，恢复策略是在数据受损时能够快速恢复数据。 ## 可以对数据库表做那些优化？ 1. 合理使用数据库分表 对于一些特别大的表，可以考虑将其拆分成多个子表，从而更好地管理数据。 1. 建立索引 在经常被查询的列上建立索引，提高查询性能。但是也要注意过多的索引影响插入、更新和删除的性能。 1. 避免使用 `Select *` 只选择需要的列而不是使用`SELECT *` 1. 选择合适的数据类型 - 尽量使用TINYINT，SMALLINT，MEDIUM_INT替代INT类型，如果是非负则加上UNSIGNED - `VARCHAR`的长度只分配真正需要的空间 - 尽量使用整数或者枚举替代字符串类型 - 时间类型尽量使用`TIMESTAMP`而非`DATETIME ` - 单表不要放太多字段 - 尽量少使用NULL，很难查询优化而且占用额外索引空间 ## 了解MongoDB嘛，它和MySQL有哪些区别 数据库模型 MySQL：关系型数据库，数据以表格的形式存储，每个表包含多个行和列，其中每一行是一个记录。 MongoDB：非关系型数据库(文档型数据库)，数据以BSON（Binary JSON）文档的形式存储，文档可以包含嵌套结构和数组。每个文档都有一个唯一的_id字段作为主键。 查询语句 MySQL：使用结构化查询语言（SQL）进行查询 MongoDB：使用MongoDB自己的查询方式 模式设计 MySQL： MySQL是有模式的数据库，表结构需要在设计时明确定义，包括字段名、数据类型。 MongoDB： MongoDB是无模式的数据库，文档可以根据需要动态添加字段，没有固定的表结构。 存储引擎 MySQL：MySQL支持多种存储引擎，如InnoDB、MyISAM等，每个引擎有不同的特性和适用场景。 MongoDB： MongoDB使用存储引擎WiredTiger，默认提供高性能的读写能力和压缩特性。 适用场景 MySQL： 适用于需要处理结构化数据，支持事务处理和复杂查询的应用。 MongoDB： 适用于需要处理大量非结构化或半结构化数据 扩展性 MySQL： 通常使用主从复制和垂直分区来实现扩展性。 MongoDB： 具有较好的横向扩展性，通过分片可以在多台机器上分布数据。 总结：如果数据是结构化的且需要复杂的事务支持，MySQL更适合；如果数据是非结构化或半结构化的，且需要横向扩展性，MongoDB更适合。