CS 教程 · 第 10 章

数据库基础

SQL / MySQL / PostgreSQL

数据是数字世界的石油，数据库是储存和提炼石油的炼油厂。

从个人博客到电商平台，从社交网络到金融系统，几乎所有应用都依赖数据库。本章将带你理解关系型数据库的核心概念，掌握 SQL 基本操作，了解 NoSQL 和非关系型存储的适用场景。

10.1 为什么需要数据库？

文件存储的问题

假设你用文件存储用户数据：

# users.txt
张三,25,北京,zhangsan@email.com
李四,30,上海,lisi@email.com
王五,28,广州,wangwu@email.com

当你要"找出所有年龄大于 25 岁的用户"时，你需要：

1. 读取整个文件

2. 逐行解析

3. 手动过滤

4. 数据量大时极其缓慢

数据库解决了这些问题：

高效的查询（通过索引，毫秒级返回）
数据一致性（事务保障）
并发访问（多个用户同时读写）
数据安全（权限控制、备份恢复）
数据完整性（约束条件，防止脏数据）

10.2 关系型 vs 非关系型数据库

关系型数据库（SQL）

数据以表（Table）的形式组织，表之间通过外键建立关系。

┌─────────────────┐      ┌─────────────────┐
│    users        │      │    orders        │
├─────────────────┤      ├─────────────────┤
│ id (主键)       │◄─────│ user_id (外键)   │
│ name            │      │ product          │
│ email           │      │ amount           │
└─────────────────┘      └─────────────────┘

代表产品： MySQL、PostgreSQL、SQLite、Oracle

特点：

✅ 严格的数据结构（Schema 定义）
✅ ACID 事务保证
✅ 强大的 SQL 查询语言
✅ JOIN 操作连接多表
❌ 水平扩展较困难
❌ 固定的表结构，不够灵活

非关系型数据库（NoSQL）

放弃传统表结构，采用更灵活的数据模型。

类型	代表产品	数据模型	典型场景
键值存储	Redis、Memcached	键 → 值	缓存、会话、计数器
文档数据库	MongoDB、CouchDB	JSON 文档	内容管理、用户画像
列族数据库	Cassandra、HBase	列族	时序数据、日志分析
图数据库	Neo4j	节点+边	社交关系、推荐系统

NoSQL 特点：

✅ 灵活的数据模型（Schema-less）
✅ 水平扩展容易（分布式）
✅ 特定场景性能极高
❌ 不支持 JOIN（通常）
❌ 事务支持较弱（部分产品正在改进）
❌ 没有统一的查询语言

如何选择？

结构化数据 + 需要强一致性 → 关系型数据库
非结构化数据 + 高性能读写 → NoSQL
高频访问的热数据         → Redis 缓存
社交关系、推荐系统       → 图数据库
日志、监控数据           → 时序数据库 / 列族数据库

💡 绝大多数应用是关系型数据库 + Redis 缓存的组合，这也是本章的重点。

10.3 SQL 基础操作

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

准备：创建示例数据库

我们用一家在线书店的场景来演示：

-- 创建用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,          -- 自增主键
    name VARCHAR(50) NOT NULL,      -- 姓名，不能为空
    email VARCHAR(100) UNIQUE,      -- 邮箱，唯一
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建图书表
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100),
    price DECIMAL(10, 2),           -- 价格，两位小数
    stock INT DEFAULT 0
);

-- 创建订单表（关联 users 和 books）
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),   -- 外键，引用 users 表
    book_id INT REFERENCES books(id),   -- 外键，引用 books 表
    quantity INT DEFAULT 1,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SELECT：查询数据

-- 查询所有列
SELECT * FROM users;

-- 查询指定列
SELECT name, email FROM users;

-- 去重
SELECT DISTINCT author FROM books;

-- 条件查询（WHERE）
SELECT * FROM books WHERE price > 50;

-- 多条件
SELECT * FROM books
WHERE price > 30 AND stock > 0;

-- 模糊查询（LIKE）
SELECT * FROM books WHERE title LIKE '%编程%';
-- % 匹配任意字符，_ 匹配单个字符

-- 排序
SELECT * FROM books ORDER BY price DESC;  -- 降序
SELECT * FROM books ORDER BY price ASC;   -- 升序（默认）

-- 限制数量
SELECT * FROM books ORDER BY price DESC LIMIT 5;

-- 分页（跳过前 10 条，取 10 条）
SELECT * FROM books LIMIT 10 OFFSET 10;

-- 聚合函数
SELECT COUNT(*) FROM users;               -- 用户总数
SELECT AVG(price) FROM books;             -- 平均价格
SELECT SUM(price * stock) FROM books;     -- 库存总价值
SELECT MAX(price), MIN(price) FROM books; -- 最高/最低价格

-- 分组统计
SELECT author, COUNT(*) as book_count
FROM books
GROUP BY author
HAVING COUNT(*) > 3;   -- 只显示出书超过3本的作者
-- WHERE 过滤行 → GROUP BY 分组 → HAVING 过滤组

INSERT：插入数据

-- 插入单行
INSERT INTO users (name, email)
VALUES ('张三', 'zhangsan@example.com');

-- 插入多行
INSERT INTO books (title, author, price, stock) VALUES
    ('Python编程：从入门到实践', 'Eric Matthes', 89.00, 50),
    ('算法导论', 'Thomas Cormen', 128.00, 20),
    ('深入理解计算机系统', 'Randal Bryant', 139.00, 15),
    ('设计数据密集型应用', 'Martin Kleppmann', 99.00, 30);

-- 插入并返回生成的值（PostgreSQL）
INSERT INTO users (name, email)
VALUES ('李四', 'lisi@example.com')
RETURNING id, created_at;

UPDATE：更新数据

-- 更新指定行（一定要加 WHERE！）
UPDATE books SET price = 79.00 WHERE id = 1;

-- 同时更新多列
UPDATE books
SET price = price * 0.9, stock = stock - 1
WHERE id = 1;

-- ⚠️ 忘记 WHERE 会更新所有行！
-- UPDATE books SET price = 0;  ← 灾难！

DELETE：删除数据

-- 删除指定行（一定要加 WHERE！）
DELETE FROM orders WHERE id = 5;

-- 删除某用户的所有订单
DELETE FROM orders WHERE user_id = 3;

-- ⚠️ 清空整张表
DELETE FROM orders;               -- 逐行删除，可回滚
TRUNCATE TABLE orders;             -- 直接清空，不可回滚，更快

10.4 JOIN：多表连接查询

JOIN 是关系型数据库最强大的特性之一，可以将多张表的数据关联起来。

-- 示例数据
-- users:  1|张三, 2|李四, 3|王五
-- orders: 1|1|1|2  (张三买了2本书1)
--         2|1|2|1  (张三买了1本书2)
--         3|2|1|1  (李四买了1本书1)

INNER JOIN（内连接）

只返回两表中匹配的行：

-- 查询用户的订单详情
SELECT
    users.name AS 用户名,
    books.title AS 书名,
    orders.quantity AS 数量,
    orders.order_date AS 下单时间
FROM orders
INNER JOIN users ON orders.user_id = users.id
INNER JOIN books ON orders.book_id = books.id;

-- 结果：
-- 张三 | Python编程... | 2 | 2024-01-15
-- 张三 | 算法导论      | 1 | 2024-01-16
-- 李四 | Python编程... | 1 | 2024-01-17
-- (王五没有订单，不出现)

LEFT JOIN（左连接）

左表全部保留，右表无匹配则填 NULL：

SELECT users.name, orders.id AS order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- 结果：
-- 张三 | 1
-- 张三 | 2
-- 李四 | 3
-- 王五 | NULL   ← 王五没有订单，但依然出现

JOIN 类型速查

-- INNER JOIN：只返回匹配的行
-- LEFT JOIN：  左表全部 + 右表匹配
-- RIGHT JOIN： 右表全部 + 左表匹配
-- FULL JOIN：  两表全部（PostgreSQL 支持，MySQL 不支持）
-- CROSS JOIN： 笛卡尔积（每行×每行，慎用）

实际应用示例

-- 畅销书排行榜（统计每本书的销量）
SELECT
    books.title,
    COUNT(orders.id) AS total_orders,
    SUM(orders.quantity) AS total_sold
FROM books
LEFT JOIN orders ON books.id = orders.book_id
GROUP BY books.id, books.title
ORDER BY total_sold DESC;

-- 活跃用户（有订单的用户）消费统计
SELECT
    users.name,
    COUNT(orders.id) AS order_count,
    COALESCE(SUM(books.price * orders.quantity), 0) AS total_spent
FROM users
LEFT JOIN orders ON users.id = orders.user_id
LEFT JOIN books ON orders.book_id = books.id
GROUP BY users.id, users.name
ORDER BY total_spent DESC;

10.5 索引原理

为什么需要索引？

-- 没有索引时
SELECT * FROM users WHERE email = 'zhangsan@example.com';
-- 数据库需要逐行扫描（全表扫描），O(n)

-- 创建索引后
CREATE INDEX idx_users_email ON users(email);
-- 通过 B+Tree 结构，O(log n) 找到目标

索引就像书的目录：不用翻完整个书去找某一章，直接查目录就行。

索引原理（B+Tree）

               [50]
              /    \
        [20, 35]  [65, 80]
       /    |   \   |    \
    [10] [25] [40] [55] [90]
     ↓     ↓    ↓    ↓    ↓
   实际数据行（或指向数据行的指针）

查询 25 的过程：
1. 从根节点 [50] 开始，25 < 50 → 走左边
2. 到 [20, 35]，20 < 25 < 35 → 走中间
3. 到 [25]，找到！
共 3 次查找 vs 全表扫描可能几百次

索引的最佳实践

-- ✅ 为经常查询的列创建索引
CREATE INDEX idx_books_author ON books(author);

-- ✅ 为外键创建索引（JOIN 性能）
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- ✅ 复合索引（多列查询）
CREATE INDEX idx_orders_user_book ON orders(user_id, book_id);

-- ✅ 唯一索引（保证唯一性 + 加速查询）
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- ❌ 不要在小表上建索引（全表扫描可能更快）
-- ❌ 不要为每个列都建索引（写入性能下降）
-- ❌ 不要在频繁更新的列上建太多索引

-- 查看查询是否使用索引
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- PostgreSQL: EXPLAIN ANALYZE  （更详细）

索引的代价

操作	无索引	有索引
查询 SELECT	慢	快 ✅
插入 INSERT	快 ✅	稍慢（需更新索引）
更新 UPDATE	快 ✅	稍慢
删除 DELETE	快 ✅	稍慢
存储空间	小 ✅	更大

💡 核心原则：为查询优化建索引，不要为每个列建索引。索引的速度提升通常远超写入开销。

10.6 事务与 ACID

什么是事务？

事务是一组数据库操作，要么全部成功，要么全部失败（原子性）。

-- 经典的转账场景：张三转 500 元给李四
BEGIN;  -- 开始事务

-- 步骤1：扣减张三余额
UPDATE accounts SET balance = balance - 500 WHERE name = '张三';

-- 步骤2：增加李四余额
UPDATE accounts SET balance = balance + 500 WHERE name = '李四';

-- 如果任何一步失败，所有操作都将回滚
COMMIT;  -- 提交事务（如果失败则 ROLLBACK 回滚）

ACID 四个特性

特性	含义	例子
原子性 (Atomicity)	事务中的所有操作是不可分割的整体	转账两步要么都执行，要么都不执行
一致性 (Consistency)	事务执行前后，数据库保持一致状态	转账前后总金额不变
隔离性 (Isolation)	并发事务互不干扰	两个用户同时转账不会混乱
持久性 (Durability)	事务提交后，数据永久保存	系统崩溃后数据不丢失

并发问题与隔离级别

-- 查看隔离级别（PostgreSQL）
SHOW TRANSACTION_ISOLATION;
-- PostgreSQL 默认：READ COMMITTED

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ... 操作 ...
COMMIT;

隔离级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED	✗	✗	✗	最快
READ COMMITTED	✅	✗	✗	快
REPEATABLE READ	✅	✅	✗	中等
SERIALIZABLE	✅	✅	✅	最慢

三种并发问题：

脏读：读到其他事务未提交的修改
不可重复读：同一事务内两次读取结果不同（其他事务修改了数据）
幻读：同一事务内两次查询的记录数不同（其他事务插入了新数据）

# Python 中使用事务（psycopg2）
import psycopg2

conn = psycopg2.connect("dbname=mydb user=admin")
try:
    cur = conn.cursor()
    cur.execute("UPDATE accounts SET balance = balance - 500 WHERE name = %s", ("张三",))
    cur.execute("UPDATE accounts SET balance = balance + 500 WHERE name = %s", ("李四",))
    conn.commit()  # 提交
    print("转账成功")
except Exception as e:
    conn.rollback()  # 回滚
    print(f"转账失败：{e}")
finally:
    cur.close()
    conn.close()

10.7 PostgreSQL vs MySQL

PostgreSQL

"功能最强大的开源关系型数据库"

-- 特色功能示例

-- 1. 原生 JSON 支持
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    data JSONB   -- JSONB：二进制JSON，支持索引
);
INSERT INTO products (data) VALUES
    ('{"name": "机械键盘", "specs": {"type": "青轴", "layout": "87键"}}');

-- JSON 查询
SELECT data->>'name' FROM products;
SELECT * FROM products WHERE data @> '{"specs": {"type": "青轴"}}';

-- 2. 数组类型
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    tags TEXT[]  -- 直接用数组存标签
);
INSERT INTO posts (tags) VALUES ('{"python", "数据库", "教程"}');
SELECT * FROM posts WHERE 'python' = ANY(tags);

-- 3. 全文搜索
SELECT * FROM books
WHERE to_tsvector('chinese', title) @@ to_tsquery('chinese', '编程 & Python');

优势： 功能丰富、标准兼容性好、扩展性强（PostGIS 地理数据、TimescaleDB 时序数据）

MySQL

"最流行的 Web 应用数据库"

-- MySQL 特点

-- 1. 多种存储引擎
CREATE TABLE logs (
    id INT,
    message TEXT
) ENGINE=InnoDB;   -- 支持事务、行级锁

-- 2. 复制配置成熟（主从复制、组复制）
-- 3. 大量采用的产品（WordPress、Magento 等）

选择建议

场景	推荐	原因
学习 SQL	PostgreSQL	标准兼容、文档优秀
简单 Web 应用	MySQL	生态成熟、托管服务多
复杂查询、数据分析	PostgreSQL	查询优化器更智能
地理信息系统（GIS）	PostgreSQL + PostGIS	地理数据处理的第一选择
需要 JSON 灵活存储	PostgreSQL	JSONB 支持索引
分布式、大规模	MySQL	分库分表方案更成熟

10.8 Redis 缓存入门

为什么需要缓存？

没有缓存：
  用户请求 → Web 服务器 → 数据库（每次都查，慢）
  响应时间：200ms

有缓存：
  用户请求 → Web 服务器 → Redis（查缓存，快）
                         ↓ 缓存未命中
                       数据库 → 写入 Redis → 返回
  响应时间：10ms（命中时）

Redis 是一个高性能的内存键值数据库，常用作缓存、消息队列、计数器。

基本操作

# 启动 Redis 服务
redis-server

# 连接 Redis（另一个终端）
redis-cli

# Python 中使用 Redis
import redis
import json

# 连接
r = redis.Redis(host='localhost', port=6379, decode_responses=True)

# === 字符串（String）===
r.set('user:1:name', '张三')
r.setex('session:abc', 3600, 'active')  # 带过期时间（秒）
name = r.get('user:1:name')
print(name)  # 张三

# === 哈希（Hash）——适合存对象 ===
r.hset('user:1', mapping={
    'name': '张三',
    'age': 25,
    'email': 'zhangsan@example.com'
})
print(r.hget('user:1', 'name'))   # 张三
print(r.hgetall('user:1'))        # 全部字段

# === 列表（List）——适合队列 ===
r.lpush('tasks', '发邮件', '生成报表')  # 左侧添加
task = r.rpop('tasks')                    # 右侧取出
print(task)  # 发邮件

# === 集合（Set）——去重、交并集 ===
r.sadd('user:1:tags', 'python', '数据库', 'web')
r.sadd('user:2:tags', 'python', '前端', 'react')
common = r.sinter('user:1:tags', 'user:2:tags')
print(common)   # {'python'}

# === 有序集合（Sorted Set）——排行榜 ===
r.zadd('leaderboard', {'张三': 100, '李四': 85, '王五': 92})
top = r.zrevrange('leaderboard', 0, 2, withscores=True)
print(top)  # [('张三', 100.0), ('王五', 92.0), ('李四', 85.0)]

缓存策略实战

def get_user(user_id):
    """先从缓存取，取不到再查数据库"""
    # 1. 尝试从 Redis 获取
    cache_key = f"user:{user_id}"
    cached = r.get(cache_key)

    if cached:
        return json.loads(cached)

    # 2. 缓存未命中，查数据库
    user = db.query(f"SELECT * FROM users WHERE id = {user_id}")

    if user:
        # 3. 写入缓存，设置 30 分钟过期
        r.setex(cache_key, 1800, json.dumps(user))

    return user

def update_user(user_id, data):
    """更新用户数据时，同时失效缓存"""
    db.update("users", data, f"id = {user_id}")
    r.delete(f"user:{user_id}")  # 删除缓存

缓存三大问题

问题	描述	解决方案
缓存穿透	查询不存在的数据，每次都穿透到数据库	缓存空值、布隆过滤器
缓存击穿	热点 key 过期，瞬间大量请求打到数据库	互斥锁、永不过期 + 异步更新
缓存雪崩	大量 key 同时过期，数据库压力骤增	过期时间加随机值、多级缓存

# 解决缓存雪崩：过期时间加随机值
import random
random_ttl = 1800 + random.randint(0, 600)  # 1800~2400秒
r.setex(key, random_ttl, value)

本章小结

关系型数据库以表组织数据，通过 SQL 操作，ACID 事务保证一致性
CRUD 四类操作（Create/Read/Update/Delete）覆盖 90% 的日常需求
JOIN 是关系型数据库的强大特性，INNER/LEFT/RIGHT/FULL 各有用途
索引像书的目录，大幅提升查询速度但会拖慢写入
事务保证一组操作的原子性，防止数据不一致
PostgreSQL 功能更强大，MySQL 生态更成熟，没有最好的，只有最合适的
Redis 作为缓存层，可以显著提升系统响应速度

[⬅️ 上一章：09-数据结构与算法](./09-数据结构与算法.html) · [🏠 目录](./README.html) · [➡️ 下一章：11-Web开发基础](./11-Web开发基础.html)