系统架构设计师 数据库篇
数据库 📚
🌐 一个系统化的数据集合，它允许用户存储、检索和管理数据。数据库通常由表格组成，这些表格中存储了结构化的数据。每个表格由行（记录）和列（字段）组成，它们分别对应数据项和数据特征。
数据库的主要特点包括：
- 组织化 📈：数据以表格的形式组织，使得数据之间的关系清晰，便于管理。
- 可访问性 🔍：用户可以通过查询语言（如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特性是确保事务可靠性的关键，而隔离级别则在并发控制中起到了平衡性能与一致性的作用。正确的索引设计和查询优化可以显著提升数据库的性能，同时，合理的备份与恢复策略是数据安全的重要保障。
记忆口诀 🎓
- 原子性：所有操作，要么全做，要么不做。
- 一致性：数据状态，始终如一。
- 隔离性：事务之间，互不干扰。
- 持久性：一旦提交，永不丢失。
趣味记忆互联网案例故事 🌐
想象一下，你在一家全球连锁的咖啡馆，点了一杯咖啡，这就像是开始一个事务。原子性就像是你要么拿到整杯咖啡，要么就不拿；一致性就像是无论你在哪家分店，咖啡的味道和品质都是一致的；隔离性就像是每个顾客都有自己的独立空间，不受其他顾客影响；持久性则像是你已经付款的咖啡，即使出了店门，你依然拥有这杯咖啡。而并发控制就像是咖啡馆的服务员，确保每个顾客的订单都能正确无误地完成，即使在高峰时段，也能保持秩序和质量。
参与点评
读者朋友们,如果您在阅读过程中,对文章的质量、易理解性有任何建议,欢迎在评论区指出,我会认真改进。