数据库 | Olimi’s Blog

Ref

数据库不同分类

数据库不同分类是否了解。关系型、非关系型、KV等，怎么使用的，如何选择。

关系型数据库：基于关系模型的数据库，例如MySQL、Oracle、SQL Server等。

非关系型数据库：不基于关系模型的数据库，例如MongoDB、Redis、Cassandra等。

选择关系型数据库（RDBMS）还是非关系型数据库（NoSQL）通常取决于你的应用程序的数据需求，这包括数据的结构、数据量、读写操作的比例和频率，以及数据的一致性和可扩展性需求等等。

以下是一些关于何时选择关系型数据库（例如 MySQL, PostgreSQL, Oracle）或非关系型数据库（例如 MongoDB, Cassandra, Redis）的指导原则。

关系型数据库：

结构化数据：如果你的数据结构固定且不易改变，关系型数据库通常是一个很好的选择。关系型数据库使用评估过的表结构（即表的列是预定义的），所以它们最适合于存储结构化数据。

事务性：如果你的应用需要执行复杂的事务，例如银行转账，关系型数据库通常是最佳选择。这是因为它们支持ACID（原子性，一致性，隔离性，持久性）事务。

数据一致性：如果需要严格的数据一致性，关系型数据库可能是最好的选择。例如，在一个电子商务应用中，库存，订单，和支付系统之间的数据必须始终保持一致。

复杂的查询：关系型数据库使用SQL（结构化查询语言）进行数据查询，它支持复杂的查询，包括联接操作，子查询等。

非关系型数据库：

灵活的数据模型：如果你的数据结构经常变化，或者你想避免复杂的数据库模型设计，那么非关系型数据库可能是一个好选择。例如，MongoDB允许你存储灵活的、JSON-like的文档，这些文档可以包含任何类型的数据。

水平可扩展性：如果你有大量的数据（例如，TB或PB级别）或者非常高的读/写负载，非关系型数据库可能是更好的选择。许多非关系型数据库都设计成可以在廉价的硬件集群上水平扩展。

高性能：如果你的应用需要非常高的性能，非关系型数据库可能是一个好选择。例如，Redis是一个在内存中存储数据的键值存储，它可以提供非常高的读写速度。

非结构化数据：如果你的应用需要处理非结构化数据，例如图像，音频，视频，或者半结构化数据，例如JSON或XML文档，非关系型数据库可能是一个好选择。

不过值得注意的是，每个数据库系统都有其特定的优势，一种数据库可能不适合所有的情况。且现在很多应用采用多数据库的策略，例如，他们可能会同时使用关系型数据库和非关系型数据库，以便根据不同的需求选择最合适的工具。

基本概念

SQL（结构化查询语言）是用于管理关系数据库的标准语言。常用的SQL操作可以分为以下几类：数据定义语言（DDL）、数据操作语言（DML）、数据控制语言（DCL）和事务控制语言（TCL）。

数据定义语言（DDL）

DDL用于定义或更改数据库的结构。

CREATE：用于创建新的数据库、表或视图。

ALTER：用于修改现有数据库、表或视图。

DROP：用于删除数据库、表或视图。

数据操作语言（DML）

DML用于在数据库中插入、修改、删除和查询数据。

INSERT：用于在表中插入新的数据行。

UPDATE：用于修改表中的数据。

DELETE：用于从表中删除数据。

SELECT：用于从表中查询数据。

数据控制语言（DCL）

DCL用于控制对数据库的访问。

GRANT：用于赋予用户访问权限。

REVOKE：用于撤销用户的访问权限。

事务控制语言（TCL）

TCL用于管理事务，即一系列的数据操作。

COMMIT：用于保存所有自上一次COMMIT以来的更改。

ROLLBACK：用于撤销自上一次COMMIT以来的所有更改。

SAVEPOINT：用于在事务中创建保存点，以便在稍后回滚到此点。

SET TRANSACTION：用于更改事务的属性。

概念

候选键

候选键（Candidate Key）是数据库中的一个概念，它是指可以用来在关系中唯一标识每个元组（也就是行）的属性集合。

候选键有以下两个重要的特性：

唯一性：候选键的每个值都必须是唯一的，也就是说，不能有两个元组（行）有相同的候选键值。

最小性：候选键是最小的唯一标识符，也就是说，如果我们从候选键中删除任何属性，那么它就不再保持唯一性。

例如，假设我们有一个学生表，其中包含学生ID、姓名、年龄和班级。在这个例子中，学生ID可以作为候选键，因为它可以唯一标识每个学生，并且如果我们删除学生ID，那么我们就不能再唯一标识每个学生。

请注意，一个关系可以有多个候选键。例如，在上述的学生表中，如果每个学生都有一个唯一的电子邮件地址，那么电子邮件地址也可以作为候选键。

最后，主键是候选键的特例。如果一个关系有多个候选键，那么我们可以选择其中一个作为主键。主键的选择可以基于多种因素，如数据稳定性、长度、易于记忆等。

关系数据库

基本操作查询

查询操作是从数据库中提取数据。以下是一些基本的查询操作：

选择（SELECT）：选择操作是从表中选择满足一定条件的行。例如，从员工表中选择所有年龄大于30的员工：

投影（PROJECTION）：投影操作是从表中的行选择特定的列。例如，从员工表中选择所有员工的姓名和薪水：

连接（JOIN）：连接操作是根据两个或更多表中的公共列将它们组合在一起。

等值连接（EQUI JOIN）：等值连接是在两个表的公共列上进行的连接，其中公共列的值必须相等。例如，连接员工表和部门表，其中员工的部门ID等于部门的ID：

自然连接（NATURAL JOIN）：自然连接是一种特殊类型的等值连接，其中公共列的名称必须完全相同，并且结果表中只包含一个公共列。

外连接（OUTER JOIN）：外连接包括左外连接、右外连接和全外连接。在外连接中，即使在一个表中没有匹配的行，也会返回另一个表中的行。

左外连接（LEFT OUTER JOIN）：左外连接返回左表中的所有行，即使在右表中没有匹配的行。

右外连接（RIGHT OUTER JOIN）：右外连接返回右表中的所有行，即使在左表中没有匹配的行。

除（DIVISION）：SQL没有直接提供除操作，但可以通过其他操作组合实现。除操作是找出在一个表中存在，但在另一个表中不存在的行。

并、差、交（UNION, EXCEPT, INTERSECT）：这些集合操作将两个查询结果组合在一起。并（UNION）操作返回两个查询的所有行，差（EXCEPT）操作返回在第一个查询中但不在第二个查询中的行，交（INTERSECT）操作返回两个查询的公共行。

笛卡尔积（CARTESIAN PRODUCT）：笛卡尔积操作是两个表的所有可能的行组合。在SQL中，可以使用CROSS JOIN操作来执行笛卡尔积：

完整性约束

在关系数据库模型中，有三种基本的完整性约束：实体完整性、参照完整性和用户定义的完整性。这些约束确保了数据库中的数据的准确性和一致性。

实体完整性：实体完整性约束确保每个表都有一个唯一的标识符，并且该标识符的值（主键）永远不为空。主键必须是唯一的，以保证我们可以准确地引用表中的每一行。实体完整性约束帮助我们保证所有的实体（在这种情况下，实体是表中的行或记录）都可以被精确地标识。

参照完整性：参照完整性约束涉及到两个表之间的关系，通常是通过外键（foreign key）来维护的。这个约束确保了外键值要么是空值，要么必须对应另一张表的主键值。例如，如果我们有两个表，一个是学生表，一个是课程表，学生表中可能有一个字段是课程ID，这个课程ID就是外键，它必须对应课程表中存在的一个课程的ID（即课程表的主键）。这样，如果试图插入或修改一条记录，使得学生表中的课程ID指向课程表中不存在的课程，操作就会被拒绝。

用户定义的完整性：用户定义的完整性约束是由业务规则或应用程序需求定义的。这些约束可以涵盖多种条件，例如数据的有效性范围（比如，一个人的年龄不能是负数），或者复杂的业务规则（比如，一个公司的员工数量不能超过100人）。这些约束是高度定制的，完全取决于特定的业务需求。

这三种完整性约束共同保证了数据库中数据的准确性、一致性和可靠性。

数据库索引

数据库索引是一种数据结构，用于提高数据查询的速度。它类似于书籍的索引，使得你能够更快地找到所需的信息。根据实现方式的不同，数据库索引可以分为多种类型，如顺序索引、B+树索引和哈希（hash）索引。

顺序索引（Ordered Indexes）：顺序索引是最基本的索引类型，它将索引项按照某种特定的顺序（通常是键值的升序或降序）存储。例如，如果你有一个学生表，其中包含学生的ID和姓名，你可能会按照学生的ID进行排序，创建一个顺序索引。这样，当你需要查询特定ID的学生时，你可以使用二分查找等方法，快速定位到该学生，而不是进行全表扫描。

B+树索引：B+树索引是一种自平衡的树形数据结构，它可以保持数据有序并且提供了对数据的快速访问、顺序访问、插入和删除等操作。B+树索引是大多数数据库管理系统（如MySQL、Oracle等）的默认索引类型。在B+树中，所有的值都存储在叶子节点，并且叶子节点之间通过指针相连，这样可以方便地进行范围查询。例如，如果你需要查询学生ID在100到200之间的所有学生，B+树索引可以帮助你快速定位到第一个满足条件的节点（ID为100的学生），然后通过叶子节点之间的指针，顺序访问所有满足条件的节点。

哈希（Hash）索引：哈希索引是一种根据哈希函数将键值映射到存储位置的索引。哈希索引在处理等值查询（即查询条件是精确的值，如"查询学生ID为100的学生"）时非常高效，因为它可以直接计算出数据的存储位置，而不需要进行比较和搜索。然而，哈希索引并不适合处理范围查询（如"查询学生ID在100到200之间的所有学生"），因为哈希函数不保证哈希值的顺序与键值的顺序一致。

以上就是顺序索引、B+树索引和哈希索引的基本概念和使用场景。在实际使用中，应根据查询类型、数据分布等因素选择合适的索引类型，以提高查询性能。

MySQL 索引背后的数据结构及算法原理（简述）

B树和B+树

B树是一种自平衡的树形数据结构，能够保持数据排序，对数据提供了快速的查找、顺序访问、插入和删除等操作。B树的特点是每个节点可以拥有多于2个的子节点。

B+树是B树的一种变种，主要的区别在于：

在B+树中，所有的数据记录节点都被放置在叶子节点，而非叶子节点只用来存储键值和其子节点的指针。

B+树的叶子节点之间通过指针相连，形成一个链表结构。

这些特点使得B+树在磁盘读写方面有一定的优势，因为磁盘读写数据时是以块的单位进行的，读取单个块的开销远小于多次读取。

为什么MySQL使用B+树索引

在数据库中，数据通常存储在磁盘上，并且数据量通常是巨大的。因此，数据库设计时的一个重要目标是减少磁盘I/O操作。由于B+树的特性，使得在查找数据时可以减少磁盘I/O的次数。

B+树的所有数据记录都存在叶子节点上，查询时只需要从根节点开始，按照索引找到对应的叶子节点即可，减少了I/O次数。

B+树的叶子节点通过指针相连，可以方便地进行范围查询，对于执行大量范围查询的OLTP系统来说，B+树是非常适合的。

InnoDB的B+树索引

在InnoDB存储引擎中，表都是按照主键的顺序进行存储和检索的，这种存储方式就叫做聚簇索引（clustered index）。每张表在磁盘上对应一个B+树，叶子节点就是数据节点。

对于非聚簇索引（即二级索引），叶子节点并不包含行的全部数据，而是包含了对应的主键值。当通过二级索引查找数据时，InnoDB会先找到主键，然后再通过主键查找到完整的行数据，这个过程叫做“回表”。

总的来说，MySQL主要使用B+树作为其索引的数据结构，以优化磁盘I/O操作和提高查询效率。

数据库理论

数据库的完整性和安全性

在数据库管理系统中，数据的完整性和安全性都是非常重要的概念。

完整性 (Integrity)：这是指数据库中的数据必须满足一定的条件或规则，以保证其正确性和一致性。例如，一个规则可能是在学生数据库中，学生的年龄不能为负值。完整性约束可以在数据库设计时定义，并由数据库管理系统(DBMS)在运行时自动强制执行。完整性可以进一步细分为域完整性，实体完整性，参照完整性等。

安全性 (Security)：这是指保护数据库不受未授权访问和恶意攻击的能力。数据库安全性可以通过各种方式实现，包括访问控制、用户身份验证、审计跟踪以及数据加密等。例如，DBMS可能提供一种机制，只允许经过认证的用户访问特定的数据。

触发器 (Trigger)

触发器是数据库管理系统中的一种对象，它可以在数据库表上定义，并在满足某种条件时自动执行或激活。触发器可以感知到各种数据库事件，例如插入、删除和更新操作。一旦这些事件发生，触发器就会自动执行与之关联的SQL语句或程序。

触发器的常见用途包括：

实施复杂的业务规则，这些规则不能通过标准的完整性约束来实施。

维护复杂的完整性约束，在多个表或多个行上。

自动更新派生数据，例如在更新一列的值时，自动更新另一列的值。

审计跟踪，例如记录数据更改的历史。

触发器的运行是透明的，数据库用户通常不会意识到触发器的存在，除非他们直接查看数据库模式或者触发器引发了错误。

数据依赖

在关系数据模型中，数据依赖是一种约束关系，表现为一个或多个属性的值依赖于其他属性的值。数据依赖有多种形式，其中最重要的两种是函数依赖和多值依赖。

函数依赖 (Functional Dependency)：在一个关系中，如果一个属性集合 A 的值决定了另一个属性集合 B 的值，那么就说 B 函数依赖于 A，记作 A->B。例如，在一个学生关系中，学号属性可能决定了学生姓名属性，那么就可以说姓名函数依赖于学号。函数依赖是关系数据库设计中非常重要的概念，它是范式理论的基础，也是数据库设计时用来消除冗余和避免更新异常的主要工具。

多值依赖 (Multivalued Dependency)：在一个关系中，如果一个属性集合 A 的值决定了另一个属性集合 B 的所有可能值的集合，那么就说 B 多值依赖于 A，记作 A->>B。例如，在一个学生选课关系中，学生编号可能决定了他可以选的所有课程，那么就可以说课程多值依赖于学生编号。多值依赖是关系数据库设计中的另一个重要概念，它是第四范式（4NF）的基础，用来进一步消除关系中的冗余。

关系数据库中的数据依赖关系通常由数据库设计者在设计时明确指定，并且可以由数据库管理系统（DBMS）在运行时自动强制执行。数据依赖关系是保证数据完整性和一致性的重要机制。

范式

关系数据库的范式是一组规则，用于确定数据库表的优良设计。范式的目标是减少数据冗余和避免插入、更新和删除操作的异常。以下是对每个范式的详细解释：

第一范式（1NF）：在第一范式中，表的每一列都是不可分的原子值。也就是说，每一列都有一个明确的值，不能包含多个值或列表。例如，如果有一个 "学生" 表，它包含 "姓名" 和 "选修课程" 两列，那么在 1NF 中，"选修课程" 列不能包含一个学生选修的所有课程的列表，而应该为每个课程创建一个新行。

第二范式（2NF）：在第二范式中，除了要满足第一范式的要求，每个非主键属性还必须完全依赖于主键。也就是说，不能有部分依赖关系。例如，如果一个表的主键是学生 ID 和课程 ID 的组合，那么学生姓名就不应该是这个表的一部分，因为它仅依赖于学生 ID。

第三范式（3NF）：在第三范式中，除了必须满足第二范式的要求，任何非主键属性不应依赖于其他非主键属性。也就是说，不能有传递依赖关系。例如，如果一个表有 "学生 ID"、"课程 ID" 和 "课程教师" 三个属性，那么 "课程教师" 就不应该是这个表的一部分，因为它依赖于 "课程 ID" 属性，而不是直接依赖于主键。

鲍耶斯-科德范式（BCNF）：这是第三范式的一个特殊版本，除了满足第三范式的要求，还要求所有的决定者都是候选键（即，不能有非主键属性决定主键属性）。在大多数情况下，满足第三范式的表也满足 BCNF，但也有一些例外。

第四范式（4NF）：在第四范式中，除了满足所有前面的范式要求，还要求消除表中的多值依赖。多值依赖发生在当一个属性的值依赖于另一个属性，而不考虑其他属性的情况下。例如，如果一个 "学生" 表有 "学生 ID"、"语言" 和 "技能" 三个属性，其中 "技能" 依赖于 "语言"，那么这个表就存在多值依赖，应该被分解为两个表，以消除这种依赖。

⌚

数据库QA