关系数据库系统基础
|
|
noname
|
111
|
0
|
数据库

11416 字
|
47 分钟

本文是为了本人更好的学习数据库系统，同时给MySQL文章做补充，介绍了关系数据库的一些系统性知识，内容多参考教材《高级数据库系统及其应用》。

1. 数据库系统体系结构

1.1 数据模型

数据模型是数据库系统中描述数据、数据之间关系以及数据约束的核心概念。它是数据库设计和实现的基础，决定了数据的组织方式和操作方式。

1. 数据模型的分类

根据抽象层次的不同，数据模型可以分为以下三类：

  1. 概念模型（Conceptual Model）
    概念模型是对现实世界的抽象，用于描述数据的语义和结构，而不涉及具体的存储细节。常见的概念模型有 ER模型（Entity-Relationship Model） 和 EER模型（Enhanced ER Model）。

  2. 逻辑模型（Logical Model）
    逻辑模型是将概念模型转换为数据库系统能够理解的形式，通常以表（Relation）的形式表示。常见的逻辑模型包括关系模型（Relational Model）和面向对象模型（Object-Oriented Model）。

  3. 物理模型（Physical Model）
    物理模型描述了数据在计算机中的实际存储方式，包括文件结构、索引方式、存储路径等。它关注的是数据的存储效率和访问性能。

2.数据模型的特征

一个良好的数据模型应具备以下特点：

  • 一致性：模型中的所有元素必须遵循统一的规则和规范。
  • 完整性：模型应能完整地描述现实世界的数据及其关系。
  • 可扩展性：模型应支持未来的数据扩展和变化。
  • 简洁性：模型应避免不必要的复杂性，易于理解和维护。
  • 可操作性：模型应支持有效的数据查询、更新和管理操作。

3.常见的数据模型

  • 层次模型（Hierarchical Model）
    以树形结构组织数据，每个节点只能有一个父节点，适用于具有明显层级关系的数据（如组织结构图）。缺点是结构不够灵活。

  • 网状模型（Network Model）
    在层次模型的基础上增加多对多关系，通过指针建立实体之间的连接。虽然灵活性较高，但结构复杂，维护困难。

  • 关系模型（Relational Model）
    最广泛使用的模型，采用二维表结构，具有高度的灵活性和易用性。关系模型支持SQL语言进行数据操作。

  • 对象模型（Object Model）
    支持对象的持久化存储，适用于需要处理复杂数据类型的应用系统（如图形图像、多媒体等）。

1.2 数据库系统体系结构

数据库系统的体系结构是指数据库系统各组成部分的组织方式和相互关系，其目标是提高数据的共享性、安全性、一致性与独立性。

1.2.1 三级模式与两级映像

数据库系统的体系结构通常采用三级模式结构：

  1. 外模式（External Schema）
    外模式也称为用户视图或子模式，是数据库用户能看到和使用的局部数据的逻辑结构。不同的用户可能有不同的外模式，满足各自的需求。

    • 每个外模式对应一个用户的视图，屏蔽了其他用户的数据。
    • 提供了数据的逻辑独立性，即使底层数据结构发生变化，也不会影响用户的视图。

  2. 概念模式（Conceptual Schema）
    概念模式是整个数据库的全局逻辑结构，描述了数据库中所有数据的逻辑关系、数据类型、约束条件等。

    • 是所有外模式的共同基础，保证了不同用户看到的数据是一致的。
    • 具有较高的逻辑独立性，允许在不改变应用程序的情况下修改数据库结构。

  3. 内模式（Internal Schema）
    内模式是数据库的物理结构，描述了数据的存储方式、索引方法、文件组织方式等。

    • 它直接与数据库的物理存储相关，例如磁盘上的文件布局、索引结构等。
    • 提供了数据的物理独立性，用户不需要关心数据是如何存储的。

为了实现数据的独立性，数据库系统引入了两级映像机制，分别是：

  1. 外模式/概念模式映像（External-to-Conceptual Mapping）
    这种映像定义了每个外模式与概念模式之间的对应关系，确保不同用户的视图与全局数据保持一致。如果概念模式发生改变，只需调整该映像即可，不影响外模式。

  2. 概念模式/内模式映像（Conceptual-to-Internal Mapping）
    这种映像定义了概念模式与内模式之间的对应关系，确保数据的逻辑结构与物理存储方式之间的一致性。当存储结构发生变化时，只需修改该映像，不影响概念模式和外模式。

1.2.2 数据库系统的组成与功能

完整数据库系统组成：

  1. 数据库（Database）
    存储数据的集合，是数据库系统的核心部分。数据可以是结构化的（如关系型数据），也可以是半结构化的（如XML、JSON）。

  2. 数据库管理系统（DBMS, Database Management System）
    DBMS是管理数据库的软件系统，负责数据的存储、检索、更新、备份、恢复、安全控制等功能。常见的DBMS包括MySQL、Oracle、SQL Server、MongoDB（NoSQL）。

  3. 数据库应用系统（Database Application System）
    应用系统是通过DBMS访问和操作数据库的程序，可以是Web应用、桌面应用、移动应用等。它提供了用户界面和业务逻辑。

  4. 数据库管理员（DBA, Database Administrator）
    DBA负责数据库的日常维护、性能优化、安全管理、备份与恢复等工作，确保数据库系统的正常运行。

  5. 数据库语言（Database Language）
    包括SQL（Structured Query Language）等用于操作数据库的语言，用于创建、查询、更新和删除数据库中的数据。

数据库系统主要功能：

  1. 数据存储与管理：提供高效的数据存储方式，并支持大规模数据的管理和维护。
  2. 数据查询与操作：支持复杂的查询操作，如选择、投影、连接、分组、排序等。
  3. 事务处理：支持事务的ACID特性，确保数据的一致性和可靠性。
  4. 并发控制：管理多个用户同时访问数据库时的数据一致性问题。
  5. 安全性控制：通过权限管理、加密等方式保护数据的安全。
  6. 数据备份与恢复：提供数据的备份和恢复机制，防止数据丢失。
  7. 数据完整性约束：通过主键、外键、唯一性约束等手段确保数据的准确性和一致性。

2. 关系模型

2.1 关系数据模型

关系数据模型是目前最广泛使用的数据库模型之一。它基于集合论和谓词逻辑，采用二维表结构来表示数据及其关系。

2.1.1 关系模型的基本概念

  1. 关系（Relation）：一个关系是一个由行（元组）和列（属性）组成的二维表。每个关系对应现实世界中的一个实体集或联系集。

  2. 元组（Tuple）：每一行称为一个元组，代表一个具体的实例。例如，在“学生”关系中，一个元组可能表示某位学生的学号、姓名、年龄等信息。

  3. 属性（Attribute）：每一列称为一个属性，表示数据的某一特征。例如，“学号”、“姓名”、“性别”都是“学生”关系中的属性。

  4. 域（Domain）：属性的取值范围称为域。例如，“性别”属性的域可能是“男”或“女”。

  5. 主键（Primary Key）：主键是能够唯一标识一个元组的属性或属性组合。主键不能有重复值，且不能为空（NOT NULL）。

  6. 候选键（Candidate Key）：可以作为主键的属性或属性组合都称为候选键。主键是从候选键中选择出来的。

  7. 外键（Foreign Key）：外键是指一个关系中的属性，它引用了另一个关系的主键。外键用于建立两个关系之间的联系。

  8. 关系模式（Relation Schema）：关系模式是关系的结构描述，通常表示如下
    $$
    R(U, D, F)
    $$
    $R$ 是关系名；$U$ 是属性集合；$D$ 是域的集合；$F$ 是完整性约束条件。

2.1.2 关系模型的特性

关系模型特点：

  1. 结构简单
    所有数据都以二维表的形式存储，结构清晰、易于理解和操作。

  2. 数据独立性高
    数据的物理存储与逻辑结构相互独立，允许在不修改应用程序的前提下调整存储方式。

  3. 具有坚实的数学基础
    基于集合论和谓词逻辑，使得关系模型具有很强的理论支持。

  4. 操作能力强
    支持丰富的查询语言（如SQL），可以进行复杂的数据检索、更新和管理。

  5. 数据一致性高
    通过主键、外键等约束机制，确保数据的完整性和一致性。

  6. 存取路径对用户透明
    用户不需要了解数据如何存储，只需要关注数据的逻辑结构。

关系模型的优点：

  1. 结构简单，易于理解
  2. 支持强大的查询语言（如SQL）
  3. 具有较高的数据独立性
  4. 理论基础牢固

关系模型的缺点：

  1. 对于复杂数据建模能力有限
  2. 难以直接支持对象结构
  3. 对多对多关系处理不够灵活
  4. 性能优化需要依赖索引和查询优化器

2.2 关系操作

关系操作是指对关系进行各种运算，包括查询、插入、删除、更新等。这些操作构成了关系数据库的核心功能，也是SQL语言的基础。

2.2.1 关系代数（Relational Algebra）

关系代数是关系模型中的一种形式化查询语言，用于描述对关系的操作。

  1. 选择（Selection）

    作用：从关系中选取满足条件的元组。

    符号：$\sigma_{\text{条件}}(R)$

    SQL示例：SELECT * FROM 学生 WHERE 年龄 > 20;

  2. 投影（Projection）

    作用：从关系中选出某些属性列。

    符号：$\pi_{\text{属性列表}}(R)$

    SQL示例：SELECT 学号, 姓名 FROM 学生;

  3. 笛卡尔积（Cartesian Product）

    作用：将两个关系的所有元组两两组合。

    符号：$R \times S$

    SQL示例：SELECT * FROM 学生, 选课;

  4. 连接（Join）

    作用：根据某些条件将两个关系的元组连接起来。包括等值连接、自然连接、外连接。

    符号：$R \bowtie_{\text{条件}} S$

    SQL示例：

    SELECT 学生.学号, 课程.课程号 
FROM 学生 
JOIN 选课 ON 学生.学号 = 选课.学号;

  5. 差（Difference）

    作用：求两个关系中不同的元组。

    符号：$R – S$

    SQL示例：

    SELECT * FROM 学生 
MINUS 
SELECT * FROM 教师;

  6. 并（Union）

    作用：合并两个关系中的元组，去重后返回。

    符号：$R \cup S$

    SQL示例：

    SELECT * FROM 学生 
UNION 
SELECT * FROM 教师;

  7. 交（Intersection）

    作用：找出两个关系中共同的元组。

    符号：$R \cap S$

    SQL示例：

    SELECT * FROM 学生 
INTERSECT 
SELECT * FROM 教师;

  8. 除（Division）

    作用：用于处理“所有”类的查询问题。

    符号：$R \div S$

    SQL示例：查找选修了所有课程的学生

    SELECT 学号 FROM 选课 
WHERE 课程号 IN (SELECT 课程号 FROM 课程) 
GROUP BY 学号 
HAVING COUNT(DISTINCT 课程号) = (SELECT COUNT(*) FROM 课程);

2.2.2 关系演算（Relational Calculus）

关系演算是另一种形式化的查询语言，与关系代数不同，它是一种非过程式语言，即只描述要查询什么，而不是如何查询。

关系演算分为两种：

  • 元组关系演算（Tuple Relational Calculus, TRC）
  • 域关系演算（Domain Relational Calculus, DRC）

示例（TRC）：{ t | ∃s ∈ 学生 (t.学号 = s.学号 ∧ s.年龄 > 20) }

2.3 SQL 语言

详细操作指令和函数参照另一篇文章《SQL语言大全》

SQL（Structured Query Language）是用于与关系型数据库交互的标准语言。常见的 SQL 操作可以分为以下几类：

注意所有SQL语句实在MySQL控制台中使用的，SQL关键字虽然不区分大小写，但是建议保持大写，语句结尾要加分号（;）标识语句结束。

2.3.1 DDL（数据定义语言）

用于创建、修改和删除数据库对象（如表、索引等）。

命令 说明 示例
SHOW DATABASE 显示数据库 SHOW DATABASE;
CREATE DATABASE 创建数据库 CREATE DATABASE mydb;
USE 使用该数据库 USE mydb;
SHOW TABLES 显示当前数据库中的所有表 SHOW TABLES;
CREATE TABLE 创建表 CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(50));
ALTER TABLE 修改表结构 ALTER TABLE users ADD COLUMN email VARCHAR(100);
DROP TABLE 删除表 DROP TABLE users;
DESCRIBE或DESC 查看表结构（字段名、类型、是否允许为空等） DESC users;
TRUNCATE TABLE 清空表数据（不可回滚） TRUNCATE TABLE users;
SHOW INDEX 显示表索引 SHOW INDEX FROM users;

2.3.2 DML（数据操作语言）

用于操作数据库中的数据记录。

命令 说明 示例
INSERT INTO 插入数据 INSERT INTO users(id,name) VALUES(1,'张三');
UPDATE 更新数据 UPDATE users SET name='李四' WHERE id=1;
DELETE 删除数据 DELETE FROM users WHERE id=1;
REPLACE INTO 替换数据（不存在则插入） REPLACE INTO users(id,name) VALUES(1,'王五');

2.3.3 DQL（数据查询语言）

用于查询数据库中的数据。

命令 说明 示例
SELECT 查询数据 SELECT * FROM users;
FROM 指定查询表 SELECT name FROM users;
WHERE 查询条件 SELECT * FROM users WHERE age>18;
GROUP BY 分组查询 SELECT age,COUNT(*) FROM users GROUP BY age;
HAVING 分组后筛选 SELECT age,COUNT(*) FROM users GROUP BY age HAVING COUNT(*)>1;
ORDER BY 排序 SELECT * FROM users ORDER BY age DESC;
LIMIT 限制返回记录数 SELECT * FROM users LIMIT 10;
JOIN...ON 表连接 SELECT u.name,o.order_no FROM users u JOIN orders o ON u.id=o.user_id;

2.3.4 DCL（数据控制语言）

用于控制数据库访问权限。

命令 说明 示例
GRANT 授予权限 GRANT SELECT ON mydb.* TO 'user1'@'localhost';
REVOKE 撤销权限 REVOKE SELECT ON mydb.* FROM 'user1'@'localhost';
CREATE USER 创建用户 CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';
ALTER USER 修改用户 ALTER USER 'user1'@'localhost' IDENTIFIED BY 'newpass';
DROP USER 删除用户 DROP USER 'user1'@'localhost';

2.3.5 TCL（事务控制语言）

用于管理数据库事务。

命令 说明 示例
START TRANSACTION 开始事务 START TRANSACTION;
COMMIT 提交事务 COMMIT;
ROLLBACK 回滚事务 ROLLBACK;
SAVEPOINT 设置保存点 SAVEPOINT sp1;
SET TRANSACTION 设置事务隔离级别 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

3. 数据库设计

3.1 ER数据模型

ER（Entity-Relationship）模型是一种用于描述现实世界中实体及其关系的概念模型。它是数据库设计过程中常用的工具，主要用于构建数据库的逻辑结构。

3.1.1 概述

相关概念：

  1. 实体（Entity）

    • 是现实世界中可以区分的、具有独立意义的对象或事物。

    • 例如：学生、课程、教师、部门等。

  2. 属性（Attribute）

    • 是实体的特征或性质，用来描述实体的具体信息。

    • 例如：学生的学号、姓名、性别、年龄等。

  3. 联系（Relationship）

    • 表示两个或多个实体之间的关联关系。

    • 例如：“选修”是学生与课程之间的一种联系。

ER模型的特点：

  • 直观易懂：通过图形化方式展示数据结构，便于理解和交流。
  • 支持复杂关系：能够表达一对一、一对多、多对多等不同类型的关系。
  • 不依赖具体数据库系统：适用于各种数据库设计方法。
  • 可扩展性强：适合后续向关系模型转换。

3.1.2 ER图的符号表示

在ER图中，使用以下图形符号：

  • 矩形框：表示实体（Entity）
  • 椭圆框：表示属性（Attribute）
  • 菱形框：表示联系（Relationship）
  • 连线：连接实体与属性、实体与联系

3.1.3 实体与联系

实体与联系的类型：

  1. 一元联系（Unary Relationship）

    • 实体与其自身之间的联系。
    • 例如：员工与其直接上级之间的“主管”关系。

  2. 二元联系（Binary Relationship）

    • 两个实体之间的联系。
    • 例如：学生与课程之间的“选修”关系。

  3. 多元联系（N-ary Relationship）

    • 多个实体之间的联系。
    • 例如：教师、课程、教室之间的“安排”关系。

联系的度数：

联系的度数是指参与该联系的实体数量

  • 1:1（一对一）
    • 一个实体实例只与另一个实体实例相关联。
  • 1:N（一对多）
    • 一个实体实例可以与多个其他实体实例相关联。
  • M:N（多对多）
    • 多个实体实例可以与多个其他实体实例相关联。

对于多对多联系，通常需要引入一个联系表（junction table）来实现。

3.2 EER数据模型

EER（Enhanced Entity-Relationship）模型是在传统ER模型的基础上进行扩展和增强的模型，能够更准确地描述复杂的现实世界语义，适用于大型或复杂的数据库系统设计。

3.2.1 概述

主要增强的功能：

  1. 继承（Inheritance）

    • 支持类与子类之间的继承关系。
    • 包括泛化（Generalization）和特殊化（Specialization）两种形式。

  2. 聚集（Aggregation）

    • 表示“整体-部分”关系，即一个实体是另一个实体的组成部分。
    • 例如：部门由多个员工组成。

  3. 组合（Composition）

    • 强类型的聚集关系，表示“部分”不能独立存在。
    • 例如：一个订单包含多个订单项，订单项不能脱离订单存在。

  4. 分类（Classification）

    • 允许将某些实体归类到不同的类别中。
    • 例如：员工可以分为“全职员工”或“兼职员工”。

EER模型的优势：

  • 支持复杂语义：能更好地描述现实生活中的多层结构。
  • 提高模型准确性：通过继承、聚集等机制，使模型更贴近实际业务。
  • 便于后期映射：为后续向关系模型转换提供更清晰的结构。

3.2.2 EER模型的符号表示

  • 三角形符号：表示继承关系（泛化/特殊化）。
  • 双线连接：表示聚集关系。
  • 虚线连接：表示组合关系。
  • 带标签的边：表示不同类型的联系。

3.3 映射ER/EER模式到关系模式

在完成ER或EER模型设计后，需要将其映射为关系模型，以便在关系数据库系统中实现。这是数据库设计过程中的关键步骤之一。

3.3.1 映射规则概述

将ER/EER模型转换为关系模式时，遵循以下基本规则：

  1. 每个实体转换为一个关系（表）

    • 实体的属性作为关系的列。
    • 实体的主键作为关系的主键。

  2. 每个联系转换为一个关系（如果有必要）

    • 一元联系：只需考虑主键。
    • 二元联系：
      • 若为1:1或1:N，则可以将外键直接添加到对应的关系中。
      • 若为M:N，则需要创建一个新的关系来存储联系。
    • 多元联系：需要创建一个新关系来存储联系。

  3. 继承关系的处理

    • 单继承（Single Inheritance）：子类关系可包含父类的全部属性。
    • 多继承（Multiple Inheritance）：可能需要引入额外的表或使用合并策略。

  4. 聚集和组合关系的处理

    • 聚集关系：若“整体”与“部分”有独立意义，应分别建表；若无独立意义，则合并到“整体”表中。
    • 组合关系：由于“部分”无法独立存在，通常合并到“整体”表中。

注意事项：

  • 避免冗余：确保每个关系表的数据结构简洁合理。
  • 正确设置主键和外键：确保数据完整性。
  • 保持一致性：所有映射后的关系表应保持逻辑一致。
  • 考虑性能优化：根据查询需求合理设计表结构和索引。

3.3.2 映射示例

示例1：简单实体和联系

ER模型：

  • 实体：学生（学号，姓名，性别）
  • 实体：课程（课程编号，课程名称）
  • 联系：选修（学生 → 课程，M:N）

映射结果：

  • 学生(学号, 姓名, 性别)
  • 课程(课程编号, 课程名称)
  • 选修(学号, 课程编号)

注：选修表作为M:N联系的中间表，其中学号和课程编号分别为外键，共同构成主键。

示例2：继承关系（EER模型）

EER模型：

  • 超类：员工（员工ID，姓名）
  • 子类：教师（教学领域）、管理人员（部门）

映射结果：

  • 员工(员工ID, 姓名)
  • 教师(员工ID, 教学领域) → 员工ID为主键
  • 管理人员(员工ID, 部门) → 员工ID为主键

注：子类通过继承超类的主键，形成各自的表，并添加自己的属性。

示例3：聚集关系

EER模型：

  • 实体：部门（部门编号，部门名称）
  • 聚集关系：员工 → 部门（1:N）

映射结果：

  • 部门(部门编号, 部门名称)
  • 员工(员工ID, 姓名, 所属部门编号)

注：通过外键“所属部门编号”建立联系，表示员工属于某个部门。

4. 数据库索引技术

4.1 概述

在数据库系统中，索引（Index） 是一种辅助数据结构，用于加快对数据库表中数据的检索速度。它类似于书籍的目录，可以快速定位到所需的数据记录。

索引的作用

  • 提高查询效率：通过索引可以避免全表扫描，减少磁盘 I/O 操作。
  • 加速排序和连接操作：在进行 ORDER BY 或 JOIN 操作时，索引能够显著提升性能。
  • 保证唯一性约束：主键索引可以确保每条记录的唯一性。
  • 优化数据访问路径：为数据库管理系统（DBMS）提供更高效的数据访问策略。

索引的类型

根据实现方式和用途，常见的索引类型包括：

类型 说明
B-Tree 索引 最常用的索引类型，适用于范围查询、等值查询和排序操作。
哈希索引（Hash Index） 基于哈希表的索引，只支持等值查询，不支持范围查询。
全文索引（Full-text Index） 用于对文本内容进行搜索，如关键字匹配、模糊匹配等。
位图索引（Bitmap Index） 适用于低基数（Low Cardinality）列，如性别、状态等。
组合索引（Composite Index） 由多个列组成的索引，可以优化多条件查询。

索引的优缺点

优点 缺点
加快查询速度 增加写操作的开销（插入、更新、删除）
提高排序和连接效率 占用额外的存储空间
保证唯一性（主键索引） 索引失效可能影响性能

4.2 各种索引

4.2.1 B-Tree 索引

B-Tree（Balance Tree）是一种自平衡的树结构，广泛用于数据库系统的索引实现中。

  1. 结构组成

    • 根节点（Root Node）

    • 内部节点（Internal Nodes）

    • 叶节点（Leaf Nodes）

    每个节点包含若干个键（Key）和指针（Pointer），键按升序排列，指针指向子节点或实际数据记录。

  2. 特点

    • 高度平衡：B-Tree 的高度较低，确保每次查找的时间复杂度为 O(log n)。

    • 支持范围查询：可以通过遍历树结构找到满足条件的所有记录。

    • 支持插入与删除：在保持平衡的前提下动态维护索引。

  3. 使用场景

    • 等值查询（=）：如 WHERE id = 100

    • 范围查询（>、<、BETWEEN）：如 WHERE age > 30

    • 排序（ORDER BY）：如 ORDER BY name ASC

4.2.2 哈希索引

哈希索引是基于哈希表的索引结构，适用于等值查询，但不适合范围查询。

  1. 原理

    • 使用哈希函数将键转换为一个哈希值，存储在哈希表中。

    • 查询时直接计算哈希值，找到对应的记录。

  2. 优缺点

    优点 缺点
    查询速度快，常用于等值查询 不支持范围查询和排序
    支持高并发访问 哈希冲突处理复杂
    可以优化主键查询 不适合大表

  3. 使用场景

    • 主键查询（如 WHERE id = 100）

    • 高频等值查询的字段

4.2.3 全文索引

全文索引主要用于对文本内容进行搜索，适用于文档、新闻、日志等文本类数据的查询。

  1. 特点

    • 分词处理：将文本拆分为单词或短语。

    • 倒排索引（Inverted Index）：记录每个词出现在哪些文档中。

    • 支持模糊查询：如“contains”、“like”、“match”等。

  2. 实现方式

    • Lucene：开源的全文搜索引擎库。

    • Elasticsearch：基于 Lucene 构建的分布式搜索服务器。

    • MySQL/PostgreSQL 内置全文索引：支持基本的文本搜索功能。

  3. 使用场景

    • 文档内容搜索（如博客文章、产品描述）

    • 日志分析

    • 用户输入关键词搜索

4.2.4 位图索引

位图索引是一种特殊的索引，适用于低基数列，即取值种类少的字段。

  1. 原理

    • 每个不同的值对应一个二进制位。

    • 每条记录用一个位表示是否符合该值。

    例如，对于“性别”字段，只有“男”和“女”两个值，可以表示为：男 → 1010…；女 → 0101…

  2. 优缺点

    优点 缺点
    查询速度快，尤其适用于多条件过滤（如 AND、OR） 不适合高基数字段（如用户ID、姓名等）
    占用空间小，适合海量数据 插入、更新效率较低

  3. 使用场景

    • 状态字段（如订单状态：已发货、已取消等）

    • 性别、地区、等级等分类字段

4.2.5 组合索引

组合索引是由多个列组成的索引，适用于多条件查询。

  1. 规则

    • 最左前缀原则：查询条件必须从索引的最左边开始。

    • 索引顺序影响性能：合理选择列顺序可以提高查询效率。

    例如，定义一个组合索引 (name, age)，那么以下查询会使用该索引：

    • WHERE name = 'Tom' AND age = 25

    • WHERE name = 'Tom'

    但以下查询不会使用该索引：

    • WHERE age = 25

  2. 设计建议

    • 将区分度高的列放在前面。

    • 避免冗余索引，减少维护开销。

    • 根据查询频率和分布情况设计索引。

4.3 索引的设计原则

  1. 选择合适的列创建索引

    • 高频查询的列
    • 用于连接的列
    • 唯一性高的列

  2. 避免过多索引

    • 每个索引都会增加插入、更新和删除的成本。

  3. 遵循最左前缀原则

    • 在使用组合索引时，必须按照索引列的顺序使用查询条件。

  4. 定期优化索引

    • 删除不必要的索引
    • 重建碎片较多的索引

  5. 监控索引使用情况

    • 通过数据库工具（如 MySQL 的 EXPLAIN、PostgreSQL 的 pg_stat_user_indexes）查看索引是否被有效利用。

4.4 索引的维护与优化

索引在数据库中并不是静态不变的，随着数据的不断变化，索引也需要维护。

  1. 索引的维护方式

    • 自动维护：数据库系统在插入、更新、删除数据时自动维护索引。

    • 手动维护：可通过命令（如 REINDEX、OPTIMIZE TABLE）手动重建索引。

  2. 索引的碎片化

    • 索引碎片（Index Fragmentation）：频繁的插入、更新导致索引页不再连续。

    • 碎片化的影响：降低查询性能，增加 I/O 开销。

    • 解决方法：定期执行索引重组（Rebuild）或重新组织（Reorganize）。

  3. 索引的优化技巧

    技巧 说明
    使用覆盖索引（Covering Index） 让查询只需要访问索引，不需要回表查询。
    避免在 WHERE 子句中使用函数 如 WHERE YEAR(date) = 2023，会导致索引失效。
    合理使用索引提示（Index Hints） 在特定情况下强制使用某个索引。
    限制返回的行数（LIMIT） 减少不必要的数据扫描。
    分区表 + 索引 对大数据量表进行分区，并为每个分区建立索引。

5. 事务并发控制

5.1 事务并发执行管理概述

在数据库系统中，事务（Transaction） 是一个逻辑上不可分割的操作序列，它必须满足 ACID 特性（原子性、一致性、隔离性、持久性）。在多用户环境下，多个事务可能会同时对数据库进行读写操作，这会导致并发问题。因此，事务并发控制（Concurrency Control） 是数据库管理系统的重要组成部分。

  1. 事务的基本概念

    • 事务：是一组 SQL 操作的集合，这些操作要么全部成功，要么全部失败。

    • ACID 特性

      中文名称 说明 作用
      原子性（Atomicity） 事务是一个不可分割的操作单元，要么全部执行成功，要么全部失败回滚。 确保在事务执行过程中，如果发生错误或系统崩溃，所有未完成的操作都会被撤销，数据库状态保持不变。
      一致性（Consistency） 事务执行前后，数据库必须保持一致状态，满足所有约束条件。 确保事务不会破坏数据库的完整性规则。
      隔离性（Isolation） 多个事务并发执行时，彼此之间互不干扰，保证数据的隔离性。 防止事务之间的数据冲突，如脏读、不可重复读、幻读等问题。
      持久性（Durability） 一旦事务提交，其对数据库的修改将永久保存，即使系统崩溃也不会丢失。 确保事务的结果不会因为系统崩溃而丢失。

  2. 并发执行的问题

    当多个事务同时访问共享资源时，可能引发以下问题：

    问题 描述
    丢失更新（Lost Update） 两个事务同时更新同一数据，后一个事务覆盖前一个事务的更新。
    脏读（Dirty Read） 一个事务读取了另一个未提交事务的数据。
    不可重复读（Non-Repeatable Read） 一个事务多次读取同一数据，由于其他事务的修改，导致结果不一致。
    幻读（Phantom Read） 一个事务多次查询某一范围的数据，由于其他事务插入或删除记录，导致结果集数量发生变化。

  3. 并发控制的目标

    • 保证事务的隔离性，防止并发操作带来的数据不一致问题。

    • 提高系统的吞吐量和响应速度，允许多个事务并行执行。

    • 确保系统在高并发情况下的稳定性与正确性。

  4. 并发控制的方法分类

    根据实现方式的不同，常见的并发控制方法可分为两类：

    1. 悲观锁（Pessimistic Concurrency Control）

      假设冲突经常发生，事务在操作前先加锁，避免冲突。

      典型实现：两阶段锁协议（Two-Phase Locking, 2PL）

    2. 乐观锁（Optimistic Concurrency Control）

      假设冲突很少发生，事务在提交时检查是否有冲突，若有则回滚。

      典型实现：时间戳排序、版本号控制等

5.2 事务隔离级别

为了控制事务的并发行为，数据库系统通常提供四种标准的事务隔离级别，它们决定了事务之间可以“看到”哪些数据的变化。

不同的数据库系统可能对隔离级别的实现略有差异。

隔离级别 说明 脏读 不可重复读 幻读
读未提交（Read Uncommitted） 最低级别，不保证一致性，性能最好，不需要加锁或等待 会 会 会
读已提交（Read Committed） 保证只能读已提交的数据，避免脏读，是大多数数据库系统的选择 不会 会 会
可重复读（Repeatable Read） 保证重复读一致，但可能有幻读 不会 不会 会
串行化（Serializable） 最高隔离级别，完全隔离，性能最差，完全避免三种问题 不会 不会 不会

5.3 并发控制技术

为了有效管理事务的并发执行，数据库系统提供了多种并发控制机制。以下是一些主要的技术和方法。

方法 是否加锁 是否支持读写并行 是否需要回滚 性能特点
两阶段锁（2PL） 是 读写互斥 否 较稳定，但易死锁
时间戳排序（TO） 否 可读写并行 是 适合少量冲突
MVCC 否 读写并行 是 高并发下性能好
OCC 否 读写并行 是 适合低冲突环境

5.3.1 锁机制（Locking）

锁是事务并发控制中最常用的方法之一，通过限制对共享资源的访问来防止冲突。

  1. 锁的类型

    锁类型 说明
    共享锁（Shared Lock, S-Lock） 允许多个事务同时读取数据，但禁止写入。
    排他锁（Exclusive Lock, X-Lock） 禁止其他事务读取或写入，只允许当前事务操作。

  2. 锁的粒度

    • 表级锁：锁定整个表，开销小但效率低。
    • 行级锁：锁定单条记录，效率高但开销大。
    • 页级锁：锁定一页数据，介于表级与行级之间。

  3. 锁的升级与降级

    • 锁升级：从共享锁升级为排他锁（例如，从只读变为修改）。
    • 锁降级：从排他锁降为共享锁（例如，修改完成后只读）。

  4. 两阶段锁协议（2PL）

    • 第一阶段（扩展阶段）：事务获取所有需要的锁。
    • 第二阶段（收缩阶段）：事务释放所有锁。
    • 优点：保证可串行化，避免死锁。
    • 缺点：可能导致死锁和性能下降。

5.3.2 时间戳排序（Timestamp Ordering）

时间戳排序是一种基于时间戳的并发控制机制，每个事务都有一个唯一的时间戳。

  1. 基本思想

    • 每个事务在开始时被赋予一个唯一的“时间戳”。

    • 在读/写操作时，比较当前事务的时间戳与数据项的“最新时间戳”。

    • 如果当前事务的时间戳较晚，则拒绝操作，并回滚或重试。

  2. 两种策略

    • 按时间戳排序（TO）：按时间顺序处理事务。

    • 多版本并发控制（MVCC）：通过维护多个版本的数据来实现并发控制。

5.3.3 多版本并发控制（MVCC）

MVCC 是一种乐观并发控制机制，通过维护数据的多个版本来避免锁的使用。

  1. 工作原理

    • 每次更新数据时，不直接覆盖原数据，而是创建一个新的版本。

    • 事务可以根据自己的时间戳读取合适版本的数据。

    • 写操作不会阻塞读操作，提高了并发性。

  2. 适用场景

    • 适用于读多写少的数据库环境。

    • 如 PostgreSQL、MySQL 的 InnoDB 引擎采用 MVCC 机制。

  3. MVCC 的优缺点

    优点 缺点
    提高并发性，减少锁竞争 数据存储空间增加
    支持快照读（Snapshot Read） 需要维护多个版本数据

5.3.4 乐观并发控制（OCC）

乐观并发控制假设事务之间冲突较少，在事务提交时才检查冲突。

  1. 工作流程

    1. 事务执行期间，不加锁，仅记录对数据的修改。
    2. 事务提交时，检查是否有冲突（即是否其他事务修改了相同的数据）。
    3. 如果没有冲突，提交；如果有冲突，回滚并重试。

  2. 适用场景

    • 适用于冲突较少的高并发环境。

    • 如分布式系统中的某些事务处理。

  3. 挑战

    • 高并发下冲突频繁时，性能会下降。

    • 需要事务能够重试。

6. 对象数据库

6.1 关系数据库与对象数据库

对象数据库（Object-Oriented Database Management System, OODBMS） 是一种用于存储和管理面向对象数据模型的数据库系统。与传统的关系型数据库（RDBMS） 相比，对象数据库更贴近程序设计中的对象结构，能够直接支持复杂的数据类型和继承机制。

需要注意的是，在实际应用中，许多系统采用“关系数据库 + ORM 框架”的方式来实现面向对象的数据持久化，而不是直接使用对象数据库。

  1. 关系数据库的特点

    • 基于表的结构：数据以行和列的形式组织在表中。
      • 使用 SQL 查询语言：通过 SQL 进行数据的查询、更新和管理。
      • 强调数据的一致性和完整性：通过主键、外键、约束等实现数据控制。
      • 适合结构化数据存储：适用于企业级应用、金融系统、事务处理等。

  2. 对象数据库的特点

    • 基于对象的结构：数据以对象形式存储，每个对象具有属性和方法。

    • 支持面向对象特性：继承、多态、封装、聚合与组合

    • 不需要进行对象-关系映射（ORM）：可直接操作对象，无需转换。

    • 适合复杂数据结构：如图形、音频、视频、文档等非结构化或半结构化数据。

  3. 两者的对比

    特性 关系数据库 对象数据库
    数据模型 表结构（行/列） 对象结构（类/实例）
    查询语言 SQL OQL（Object Query Language）
    面向对象特性 不支持 支持（继承、多态等）
    映射方式 需要 ORM（如 Hibernate） 无需映射，直接操作对象
    适用场景 结构化数据，业务逻辑简单 复杂数据结构，高耦合业务逻辑

6.2 OQL语言

OQL（Object Query Language）是一种用于查询对象数据库的语言，类似于 SQL 的语法，但专门用于对象数据库系统中，支持对对象及其属性、方法和继承结构的查询。

  1. OQL 的基本结构

    OQL 的基本语法与 SQL 类似，主要也由以下几个部分组成：

    • SELECT 子句：选择需要返回的对象或属性。

    • FROM 子句：指定查询的对象集合（类或实例）。

    • WHERE 子句：过滤条件。

    • ORDER BY 和 GROUP BY 子句：排序和分组。

  2. OQL 的特点

    • 支持面向对象查询：可以直接访问对象的属性和方法。
    • 支持继承查询：可以查询所有子类实例。
    • 支持集合操作：如 IN, EXISTS, NOT EXISTS 等。
    • 支持嵌套查询：可以在查询中嵌套其他查询。

  3. OQL 示例

    -- 查询所有年龄大于 30 的员工
SELECT e FROM Employee e WHERE e.age > 30;

-- 查询所有属于 Manager 类的员工，并按姓名排序
SELECT m FROM Manager m ORDER BY m.name;

-- 查询某个部门的所有员工及其所属的部门信息
SELECT e, d FROM Employee e, Department d WHERE e.department = d;

  4. OQL 与 SQL 的区别

    除了应用的数据库不同，OQL的数据模型和查询目标都是对象，而SQL分别是表和行。

6.3 C++语言绑定与 OODBMS 实现

在对象数据库系统中，通常需要提供编程语言接口，以便开发人员能够使用熟悉的编程语言（如 C++）与数据库交互。

6.3.1 C++ 与对象数据库的集成

C++ 是一种广泛使用的面向对象语言，许多对象数据库系统提供了 C++ 的接口（Binding），允许开发人员直接操作对象数据库中的对象。

C++ 接口的设计

  • 对象类定义：使用 C++ 类来表示数据库中的对象。

  • 数据库操作函数：提供插入、更新、删除、查询等操作。

  • 对象生命周期管理：包括对象的创建、加载、保存、销毁等。

示例：C++ 对象数据库操作

假设有一个 Employee 类：

class Employee {
public:
    int id;
    string name;
    int age;
    Department* department;
};

通过对象数据库接口，可以执行如下操作：

// 创建新员工
Employee* emp = new Employee();
emp->id = 1;
emp->name = "John";
emp->age = 35;

// 插入到数据库
db.insert(emp);

// 查询所有年龄大于 30 的员工
vector<Employee*> employees = db.query("SELECT * FROM Employee WHERE age > 30");

// 修改员工信息
emp->age = 36;
db.update(emp);

// 删除员工
db.delete(emp);

6.3.2 OODBMS 的实现

  1. 实现方式

    • 持久化对象（Persistent Objects）：将对象状态保存到磁盘，即使程序结束仍可保留。

    • 对象引用（Object References）：数据库中存储的是对象的指针，而不是值。

    • 事务支持：支持事务操作，保证数据一致性。

    • 查询优化：通过索引和缓存提高查询效率。

  2. 常见的 C++ 对象数据库系统

    • Objectivity/DB：一个高性能的对象数据库系统，支持 C++ 和 Java。

    • Versant：支持 C++ 和 Java，提供丰富的面向对象功能。

    • POOMA：面向科学计算的 C++ 对象数据库库。

    • ODB：一个开源的 C++ 对象数据库工具，支持 SQLite、MySQL 等后端。

6.4 对象-关系数据库系统

对象-关系数据库系统（Object-Relational Database System, ORDBS）是关系数据库系统与面向对象数据库系统的结合体。它在传统关系型数据库的基础上，引入了面向对象的特性，如继承、多态、封装以及对复杂数据类型的支持，从而更好地适应现代软件开发中日益复杂的业务需求。

ORDBS 的目标是弥补关系数据库与面向对象编程之间的鸿沟，使开发者能够更自然地使用面向对象的模型来设计和操作数据库，同时保留关系数据库在性能、可扩展性、事务处理等方面的优势。

C++MySQLSQL
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
															
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																

							
							
						

																

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇