存储与数据库

存储与数据库是系统架构的核心，直接影响系统的性能和可靠性。本文档系统介绍关系型数据库、NoSQL数据库、数据一致性以及数据安全的知识，包含详细讲解、SQL示例、性能优化和最佳实践。

📋 目录结构

💾 一、关系型数据库

关系型数据库是使用最广泛的数据库类型，基于关系模型存储数据。

1.1 SQL 基础

SQL（Structured Query Language）是关系型数据库的标准查询语言。

1.1.1 数据定义语言（DDL）

创建表：

sql

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

修改表结构：

sql

-- 添加字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 修改字段
ALTER TABLE users MODIFY COLUMN email VARCHAR(200);

-- 删除字段
ALTER TABLE users DROP COLUMN phone;

-- 添加索引
ALTER TABLE users ADD INDEX idx_phone (phone);

-- 删除索引
ALTER TABLE users DROP INDEX idx_phone;

1.1.2 数据操作语言（DML）

插入数据：

sql

-- 单条插入
INSERT INTO users (username, email, password_hash) 
VALUES ('john', 'john@example.com', 'hashed_password');

-- 批量插入
INSERT INTO users (username, email, password_hash) 
VALUES 
    ('alice', 'alice@example.com', 'hash1'),
    ('bob', 'bob@example.com', 'hash2'),
    ('charlie', 'charlie@example.com', 'hash3');

查询数据：

sql

-- 基本查询
SELECT * FROM users WHERE id = 1;

-- 条件查询
SELECT id, username, email FROM users 
WHERE email LIKE '%@example.com' 
AND created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 10;

-- 聚合查询
SELECT 
    DATE(created_at) as date,
    COUNT(*) as user_count
FROM users
GROUP BY DATE(created_at)
HAVING user_count > 100
ORDER BY date DESC;

-- 连接查询
SELECT 
    u.id,
    u.username,
    o.order_id,
    o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.id = 1;

更新数据：

sql

-- 更新单条记录
UPDATE users 
SET email = 'newemail@example.com', updated_at = NOW()
WHERE id = 1;

-- 批量更新
UPDATE users 
SET status = 'active'
WHERE created_at < '2024-01-01' AND status = 'inactive';

删除数据：

sql

-- 删除单条记录
DELETE FROM users WHERE id = 1;

-- 软删除（推荐）
UPDATE users SET deleted_at = NOW() WHERE id = 1;

-- 物理删除
DELETE FROM users WHERE deleted_at IS NOT NULL AND deleted_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

1.2 索引设计

索引是提高查询性能的关键。

1.2.1 索引类型

主键索引：

sql

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,  -- 主键索引
    username VARCHAR(50) NOT NULL,
    -- ...
);

唯一索引：

sql

CREATE UNIQUE INDEX uk_email ON users(email);

普通索引：

sql

CREATE INDEX idx_username ON users(username);

组合索引：

sql

-- 组合索引：查询条件包含多个字段时使用
CREATE INDEX idx_user_status_created ON users(status, created_at);

-- 最左前缀原则
-- 可以使用索引的查询：
SELECT * FROM users WHERE status = 'active';  -- ✅ 使用索引
SELECT * FROM users WHERE status = 'active' AND created_at > '2024-01-01';  -- ✅ 使用索引

-- 不能使用索引的查询：
SELECT * FROM users WHERE created_at > '2024-01-01';  -- ❌ 不能使用索引（不满足最左前缀）

1.2.2 索引优化

索引覆盖：

sql

-- 如果查询的字段都在索引中，可以避免回表查询
CREATE INDEX idx_user_info ON users(id, username, email);

-- 查询可以使用索引覆盖
SELECT id, username, email FROM users WHERE id = 1;  -- ✅ 索引覆盖，无需回表

索引选择性：

sql

-- 选择性高的字段适合建索引
-- 选择性 = 不重复的值数量 / 总记录数

-- 选择性高的字段（如身份证号）
CREATE INDEX idx_id_card ON users(id_card);  -- ✅ 适合建索引

-- 选择性低的字段（如性别）
CREATE INDEX idx_gender ON users(gender);  -- ❌ 不适合建索引（只有男女两个值）

1.3 事务管理

事务是保证数据一致性的重要机制。

1.3.1 ACID 特性

原子性（Atomicity）：

事务中的所有操作要么全部成功，要么全部失败

一致性（Consistency）：

事务执行前后，数据库保持一致性状态

隔离性（Isolation）：

并发事务之间互不影响

持久性（Durability）：

事务提交后，数据永久保存

1.3.2 隔离级别

四种隔离级别：

隔离级别	脏读	不可重复读	幻读	说明
读未提交（Read Uncommitted）	✅	✅	✅	最低隔离级别
读已提交（Read Committed）	❌	✅	✅	只避免脏读
可重复读（Repeatable Read）	❌	❌	✅	MySQL默认
串行化（Serializable）	❌	❌	❌	最高隔离级别

设置隔离级别：

sql

-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

1.3.3 事务示例

sql

-- 转账事务示例
START TRANSACTION;

-- 扣除转出账户余额
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;

-- 增加转入账户余额
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

-- 记录转账日志
INSERT INTO transfer_log (from_account, to_account, amount, created_at)
VALUES (1, 2, 1000, NOW());

-- 提交事务
COMMIT;

-- 如果出错，回滚事务
-- ROLLBACK;

Java 事务示例：

java

@Transactional
public void transfer(Long fromAccountId, Long toAccountId, BigDecimal amount) {
    // 扣除转出账户余额
    Account fromAccount = accountRepository.findById(fromAccountId);
    if (fromAccount.getBalance().compareTo(amount) < 0) {
        throw new InsufficientBalanceException("Insufficient balance");
    }
    fromAccount.setBalance(fromAccount.getBalance().subtract(amount));
    accountRepository.save(fromAccount);
    
    // 增加转入账户余额
    Account toAccount = accountRepository.findById(toAccountId);
    toAccount.setBalance(toAccount.getBalance().add(amount));
    accountRepository.save(toAccount);
    
    // 记录转账日志
    TransferLog log = new TransferLog();
    log.setFromAccountId(fromAccountId);
    log.setToAccountId(toAccountId);
    log.setAmount(amount);
    log.setCreatedAt(LocalDateTime.now());
    transferLogRepository.save(log);
}

1.4 SQL 优化

SQL 优化是提升数据库性能的关键。

1.4.1 查询优化

避免全表扫描：

sql

-- ❌ 不好的查询 - 全表扫描
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- ✅ 好的查询 - 使用索引
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

使用 LIMIT：

sql

-- ❌ 不好的查询 - 返回所有记录
SELECT * FROM users ORDER BY created_at DESC;

-- ✅ 好的查询 - 限制返回数量
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

*避免 SELECT ：

sql

-- ❌ 不好的查询 - 返回所有字段
SELECT * FROM users WHERE id = 1;

-- ✅ 好的查询 - 只返回需要的字段
SELECT id, username, email FROM users WHERE id = 1;

使用 EXISTS 代替 IN：

sql

-- ❌ 不好的查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- ✅ 好的查询
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

1.4.2 分页优化

传统分页问题：

sql

-- ❌ 性能问题：偏移量越大，查询越慢
SELECT * FROM users ORDER BY id LIMIT 100000, 20;  -- 非常慢

优化方案1：使用索引字段分页

sql

-- ✅ 使用上次查询的最后一个ID
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 20;  -- 很快

优化方案2：延迟关联

sql

-- ✅ 先查询ID，再查询详细数据
SELECT u.* FROM users u
INNER JOIN (
    SELECT id FROM users ORDER BY id LIMIT 100000, 20
) AS t ON u.id = t.id;

1.5 数据库设计

良好的数据库设计是系统性能的基础。

1.5.1 范式设计

第一范式（1NF）：

每个字段都是原子值，不可再分

sql

-- ❌ 违反第一范式
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),  -- 包含姓和名
    phone VARCHAR(200)  -- 包含多个电话
);

-- ✅ 符合第一范式
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

CREATE TABLE user_phones (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    phone VARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

第二范式（2NF）：

满足第一范式
非主键字段完全依赖于主键

第三范式（3NF）：

满足第二范式
非主键字段之间没有传递依赖

1.5.2 ER 模型设计

实体关系图示例：

📦 二、NoSQL 数据库

NoSQL 数据库适用于非关系型数据存储场景。

2.1 文档数据库（MongoDB）

特点：

存储 JSON 格式文档
模式灵活
适合文档类数据

MongoDB 示例：

javascript

// 插入文档
db.users.insertOne({
    name: "John",
    email: "john@example.com",
    age: 30,
    address: {
        city: "Beijing",
        street: "Main St"
    },
    tags: ["developer", "java"]
});

// 查询文档
db.users.find({ age: { $gt: 25 } });

// 索引
db.users.createIndex({ email: 1 });
db.users.createIndex({ "address.city": 1 });

2.2 键值数据库（Redis）

特点：

高性能内存存储
支持多种数据结构
适合缓存场景

Redis 数据结构：

redis

# String
SET user:1:name "John"
GET user:1:name

# Hash
HSET user:1 name "John" email "john@example.com"
HGET user:1 name

# List
LPUSH users "John"
LRANGE users 0 -1

# Set
SADD tags "java" "python"
SMEMBERS tags

# Sorted Set
ZADD scores 100 "John"
ZRANGE scores 0 -1 WITHSCORES

2.3 列式数据库（HBase）

特点：

面向列的存储
适合大数据分析
高可扩展性

2.4 图数据库（Neo4j）

特点：

存储节点和关系
适合社交网络、推荐系统
强大的图查询语言

🔄 三、数据一致性

数据一致性是分布式系统的重要挑战。

3.1 强一致性

定义： 所有节点同时看到相同的数据。

实现方式：

两阶段提交（2PC）
三阶段提交（3PC）
Paxos 算法
Raft 算法

3.2 最终一致性

定义： 允许短暂的数据不一致，最终达到一致状态。

实现方式：

向量时钟
CRDT（无冲突复制数据类型）
冲突解决策略

3.3 一致性模型对比

模型	一致性	性能	适用场景
强一致性	最高	较低	金融系统
最终一致性	较低	较高	互联网应用
因果一致性	中等	中等	社交网络

🔒 四、数据安全

数据安全是系统安全的重要组成部分。

4.1 备份策略

备份类型：

全量备份
增量备份
差异备份

备份示例：

bash

# MySQL 备份
mysqldump -u root -p database_name > backup.sql

# 定时备份
0 2 * * * mysqldump -u root -p database_name > /backup/db_$(date +\%Y\%m\%d).sql

4.2 权限控制

用户权限管理：

sql

-- 创建用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';

-- 授予权限
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'app_user'@'%';

-- 撤销权限
REVOKE UPDATE ON database_name.* FROM 'app_user'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

4.3 数据加密

传输加密：

TLS/SSL 连接
加密通道

存储加密：

字段级加密
数据库加密

📝 总结

存储与数据库是架构师必须掌握的核心技能，需要在实践中不断学习和提升。

学习路径

SQL 基础：掌握基本 SQL 语法和操作
性能优化：理解索引、查询优化、分页优化
事务管理：理解 ACID 特性和隔离级别
NoSQL：了解各种 NoSQL 数据库的特点和适用场景

上一章节：OOP 与设计
返回目录：技术能力 | 架构师能力图谱

开发与调试

测试能力

运维与监控

NoSQL

中间件

存储

接入层

操作系统

框架

硬件知识

缓存

品质

经验底蕴

能力

任务拆分

文档能力

协议设计

基本理论

接入层架构

数据层架构

通讯机制

逻辑层架构

存储与数据库 ​

📋 目录结构 ​

💾 一、关系型数据库 ​

1.1 SQL 基础 ​

1.1.1 数据定义语言（DDL） ​

1.1.2 数据操作语言（DML） ​

1.2 索引设计 ​

1.2.1 索引类型 ​

1.2.2 索引优化 ​

1.3 事务管理 ​

1.3.1 ACID 特性 ​

1.3.2 隔离级别 ​

1.3.3 事务示例 ​

1.4 SQL 优化 ​

1.4.1 查询优化 ​

1.4.2 分页优化 ​

1.5 数据库设计 ​

1.5.1 范式设计 ​

1.5.2 ER 模型设计 ​

📦 二、NoSQL 数据库 ​

2.1 文档数据库（MongoDB） ​

2.2 键值数据库（Redis） ​

2.3 列式数据库（HBase） ​

2.4 图数据库（Neo4j） ​

🔄 三、数据一致性 ​

3.1 强一致性 ​

3.2 最终一致性 ​

3.3 一致性模型对比 ​

🔒 四、数据安全 ​

4.1 备份策略 ​

4.2 权限控制 ​

4.3 数据加密 ​

📝 总结 ​

学习路径 ​

存储与数据库

📋 目录结构

💾 一、关系型数据库

1.1 SQL 基础

1.1.1 数据定义语言（DDL）

1.1.2 数据操作语言（DML）

1.2 索引设计

1.2.1 索引类型

1.2.2 索引优化

1.3 事务管理

1.3.1 ACID 特性

1.3.2 隔离级别

1.3.3 事务示例

1.4 SQL 优化

1.4.1 查询优化

1.4.2 分页优化

1.5 数据库设计

1.5.1 范式设计

1.5.2 ER 模型设计

📦 二、NoSQL 数据库

2.1 文档数据库（MongoDB）

2.2 键值数据库（Redis）

2.3 列式数据库（HBase）

2.4 图数据库（Neo4j）

🔄 三、数据一致性

3.1 强一致性

3.2 最终一致性

3.3 一致性模型对比

🔒 四、数据安全

4.1 备份策略

4.2 权限控制

4.3 数据加密

📝 总结

学习路径