系统架构设计师数据库篇

数据库 📚

🌐 一个系统化的数据集合，它允许用户存储、检索和管理数据。数据库通常由表格组成，这些表格中存储了结构化的数据。每个表格由行（记录）和列（字段）组成，它们分别对应数据项和数据特征。

数据库的主要特点包括：

组织化 📈：数据以表格的形式组织，使得数据之间的关系清晰，便于管理。
可访问性 🔍：用户可以通过查询语言（如SQL）来访问和操作数据。
一致性 🔄：数据库管理系统（DBMS）确保数据的准确性和一致性。
安全性 🔐：数据库提供了用户认证、权限控制等安全机制，以保护数据不被未授权访问。
冗余减少 🗂️：通过数据规范化，数据库减少了数据冗余，提高了数据的一致性。
灵活性 🛠️：数据库保证了数据的持久存储，即使在系统故障的情况下也能恢复数据。
持久性 💾：数据结构可以根据需要进行调整，以适应不断变化的需求。

数据库类型：

关系型数据库 📊

如MySQL、PostgreSQL、Oracle和SQL Server，它们使用表格、行和列来组织数据，并使用SQL作为查询语言。

非关系型数据库（NoSQL）🌐

如MongoDB、Cassandra和Redis，它们不依赖于表格模型，而是使用其他存储数据的方式，如键值对、文档或图形数据库。

数据库并发

在多用户共享系统中，许多事务可能同时对同一数据进行操作。
DBMS控制子系统负责协调并发事务的执行，保证数据库的完整性。

事务的基本概念

事务：用户定义的数据库操作序列，要么全做，要么全不做，是一个不可分割的工作单位。

事务的ACID特性

🔴 原子性（Atomicity）：要么全做，要么全不做，不能部分完成。
🔁 一致性（Consistency）：事务必须保证数据库从一个一致性状态转移到另一个一致性状态。
🏠 隔离性（Isolation）：并发执行的事务之间不会互相影响。
💾 持久性（Durability）：一旦事务提交，所做的修改会永久保存在数据库中。

数据不一致问题

📝 丢失修改：两个事务修改同一数据，后提交的事务覆盖前一个事务的结果，导致修改丢失。
🚫 脏数据：事务读取到未提交的修改数据，若修改事务回滚，将导致读取脏数据。
🔄 不可重复读：同一事务两次读取数据，另一个事务在两次读取间修改数据，导致数据不一致。

事务隔离级别

SQL Server 中的事务隔离级别

👀 READ UNCOMMITTED（读取未提交）
- 允许读取未提交的数据，可能导致脏读。
- 无法保证数据一致性，但可以提高性能。
- 适用于对性能要求较高、对一致性要求较低的场景。
🔒 READ COMMITTED（读取已提交）
- 只能读取已提交的更改，避免脏读。
- 默认隔离级别，适当平衡一致性和性能。
- 可能发生不可重复读和幻读。
🔄 REPEATABLE READ（可重复读）
- 事务在读取数据后，锁定读到的数据行，防止其他事务修改。
- 防止脏读和不可重复读，但可能发生幻读。
- 适用于需要多次读取相同数据，并且该数据不能被其他事务修改的场景。
🔓 SNAPSHOT（快照）
- 提供事务开始时数据的一致性视图，防止脏读、不可重复读和幻读。
- 使用版本控制技术，可以在不加锁的情况下提供一致性读。
- 提高并发性能，适用于只读操作较多的场景。
🚧 SERIALIZABLE（可串行化）
- 最高的隔离级别，完全锁定读到的数据，防止脏读、不可重复读和幻读。
- 所有事务按顺序执行，确保一致性。
- 对性能影响较大，适用于对数据一致性要求极高的场景。

Oracle 中的事务隔离级别

🔒 READ COMMITTED（读取已提交）
- 默认的隔离级别。
- 只能读取已提交的更改，防止脏读。
- 使用锁机制防止不可重复读和幻读。
🔄 SERIALIZABLE（可串行化）
- 与 SQL Server 的 SERIALIZABLE 类似。
- 提供最高的隔离级别，确保事务按顺序执行。
- 防止脏读、不可重复读和幻读，但可能导致性能降低。

PostgreSQL 中的事务隔离级别

👀 READ UNCOMMITTED（读取未提交）
- 实际上在 PostgreSQL 中等同于 READ COMMITTED。
- 不允许脏读。
🔒 READ COMMITTED（读取已提交）
- 默认隔离级别。
- 只能读取已提交的更改，防止脏读。
- 在同一事务中多次读取数据时，可能会看到其他事务提交的修改。
🔄 REPEATABLE READ（可重复读）
- 提供一致性的视图，防止脏读和不可重复读。
- 事务在读取数据后，保证后续读取的相同数据保持一致。
- 使用多版本控制，可能会出现幻读。
🔓 SERIALIZABLE（可串行化）
- 提供最高的隔离级别，确保事务按顺序执行。
- 使用乐观并发控制，检测并防止冲突。
- 防止脏读、不可重复读和幻读，但可能导致事务被回滚。

MySQL 中的事务隔离级别

👀 READ-UNCOMMITTED（读取未提交）
- 允许读取尚未提交的数据，可能会导致脏读。
🔒 READ-COMMITTED（读取已提交）
- 只能读取已提交的数据，防止脏读，但可能发生不可重复读或幻读。
🔄 REPEATABLE-READ（可重复读）
- 多次读取同一数据结果一致，可防止脏读和不可重复读，但可能发生幻读。
🔓 SERIALIZABLE（可串行化）
- 最高隔离级别，完全服从ACID，防止脏读、不可重复读和幻读。

封锁协议

处理并发控制的主要方法是采用封锁技术，主要有X封锁和S封锁：

🚫 排他型封锁（X 封锁）
- 事务对数据对象实现X封锁，只有该事务可以读取和修改数据。
🔄 共享型封锁（S封锁）
- 事务对数据对象实现S封锁，只允许读取数据，不允许修改。

并发控制技术

Oracle

多种封锁技术，包括数据锁（DML锁）和字典锁。
通过回滚段保证不读“脏”数据和可重复读。
提供有效的死锁检测机制。

伪代码示例 (Oracle)

-- 显式锁定表
LOCK TABLE employees IN EXCLUSIVE MODE;

-- 事务中使用FOR UPDATE来隐式锁定选定的行
BEGIN
  -- 开启事务
  SELECT * FROM employees WHERE employee_id = 123 FOR UPDATE;

  -- 执行更新操作
  UPDATE employees SET salary = salary + 1000 WHERE employee_id = 123;

  -- 提交事务
  COMMIT;
END;

SQL Server

使用行级锁、页级锁、表级锁以及意向锁（Intent Locks）。
支持多种事务隔离级别。
自动检测死锁并终止其中一个事务。

伪代码示例 (SQL Server)

-- 使用SELECT WITH (UPDLOCK)来锁定行
BEGIN TRAN
  SELECT * FROM Employees WITH (UPDLOCK)
  WHERE EmployeeID = 1;

  -- 执行更新操作
  UPDATE Employees
  SET Salary = Salary + 1000
  WHERE EmployeeID = 1;

  -- 提交事务
  COMMIT TRAN

PostgreSQL

使用多版本并发控制（MVCC）处理并发，提高性能。
支持多种事务隔离级别，包括共享锁（S锁）和排他锁（X锁）。

伪代码示例 (PostgreSQL)

-- PostgreSQL中使用SELECT FOR UPDATE来锁定行
BEGIN;
  SELECT * FROM employees WHERE employee_id = 123 FOR UPDATE;

  -- 执行更新操作
  UPDATE employees SET salary = salary + 1000 WHERE employee_id = 123;

  -- 提交事务
  COMMIT;

MySQL

InnoDB支持MVCC，允许读不受写操作影响。
使用间隙锁和多版本数据避免幻读。
实现行级锁和表级锁，保证事务串行化调度。

伪代码示例 (MySQL)

-- 使用SELECT FOR UPDATE来锁定行
START TRANSACTION;
  SELECT * FROM employees WHERE employee_id = 123 FOR UPDATE;

  -- 执行更新操作
  UPDATE employees SET salary = salary + 1000 WHERE employee_id = 123;

  -- 提交事务
COMMIT;

索引优化策略

📈 选择性索引：为经常作为查询条件且不常更新的属性创建索引。
🚫 限制索引数量：索引过多会影响UPDATE、INSERT和DELETE的性能。
🕒 分析查询频度：分析每个重要查询的使用频度，建立必要索引。
📉 小数据量无需索引：对于数据量小的关系，不必建立索引。
📈 选择性索引：避免在重复值多的列上建立索引。
🔗 复合索引：常涉及多个列的查询，考虑创建复合索引。
🛠️ 索引维护：定期使用REINDEX命令重建索引，减少碎片。
🕵️ 监控索引性能：通过系统表监控索引使用情况，移除低使用率索引。

避免索引失效的操作

🚫 避免使用函数：WHERE子句中使用函数可能使索引失效。
🚫 避免使用不等式：如!=或<>可能导致全表扫描。
📝 使用参数化查询：避免在WHERE子句中直接使用变量。

查询优化

🏗️ 物化视图：建立物化视图或减少多表查询。
🔄 替换子查询：用不相干子查询替代相干子查询。
📑 仅检索必要属性：只检索需要的属性。
🔐 优化条件子句：用IN子句替换OR子句。
🔄 及时提交：经常提交以尽早释放锁。

数据库的优化策略

SQL Server 优化策略

🔍 使用执行计划分析工具
🛠️ 索引优化
📈 调整表结构和数据类型
🕒 减少锁定
⚙️ 缓存和内存管理

Oracle 优化策略

🔍 使用执行计划分析
🛠️ 索引优化
📊 统计信息和优化器
🕒 减少锁定和提高并发
💽 内存和 I/O 优化

PostgreSQL 优化策略

🔍 使用 EXPLAIN 分析查询计划
🛠️ 索引优化
📊 内存和配置优化
🕒 VACUUM 和 ANALYZE
🧩 表分区和数据归档

MySQL 优化策略

🔍 使用 EXPLAIN 分析查询计划
🛠️ 索引优化
📊 使用慢查询日志
📈 调整缓存和内存设置
🕒 分区表和优化表结构
⚙️ 减少锁定和提高并发

备份与恢复技术

备份方式

💾 物理备份
📜 逻辑备份

物理备份类型

🧊 冷备份
🔥 热备份

按数据量

🔒 完全备份
📈 增量备份
🔄 差异备份

日志文件

事务日志：记录数据库的所有操作，将结果保存在独立文件中，称为日志文件。

🔙 undo log（回滚日志）：用于事务回滚和MVCC。
🔄 redo log（重做日志）：用于故障恢复，确保事务持久性。
🌐 binlog（归档日志）：用于数据备份和主从复制。

总结 🧐

在数据库系统中，为了保持数据的一致性和完整性，必须对并发事务进行有效的控制。ACID特性是确保事务可靠性的关键，而隔离级别则在并发控制中起到了平衡性能与一致性的作用。正确的索引设计和查询优化可以显著提升数据库的性能，同时，合理的备份与恢复策略是数据安全的重要保障。

记忆口诀 🎓

原子性：所有操作，要么全做，要么不做。
一致性：数据状态，始终如一。
隔离性：事务之间，互不干扰。
持久性：一旦提交，永不丢失。

趣味记忆互联网案例故事 🌐

想象一下，你在一家全球连锁的咖啡馆，点了一杯咖啡，这就像是开始一个事务。原子性就像是你要么拿到整杯咖啡，要么就不拿；一致性就像是无论你在哪家分店，咖啡的味道和品质都是一致的；隔离性就像是每个顾客都有自己的独立空间，不受其他顾客影响；持久性则像是你已经付款的咖啡，即使出了店门，你依然拥有这杯咖啡。而并发控制就像是咖啡馆的服务员，确保每个顾客的订单都能正确无误地完成，即使在高峰时段，也能保持秩序和质量。

参与点评
读者朋友们,如果您在阅读过程中,对文章的质量、易理解性有任何建议,欢迎在评论区指出,我会认真改进。