数据库原理与MySQL vs PostgreSQL

数据库基础

什么是数据库？

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。数据库管理系统（DBMS）是管理和操作数据库的软件。

数据库的主要特点：

• 数据持久化：数据可以长期保存
• 数据共享：多个用户可以同时访问数据
• 数据独立性：数据与应用程序相对独立
• 数据一致性：保证数据的一致性和完整性
• 数据安全性：提供访问控制和数据保护

数据库类型

关系型数据库（RDBMS）：

• MySQL：开源，广泛使用
• PostgreSQL：开源，功能强大
• Oracle：商业数据库，企业级
• SQL Server：微软的数据库
• SQLite：轻量级嵌入式数据库

非关系型数据库（NoSQL）：

• MongoDB：文档数据库
• Redis：键值数据库
• Cassandra：列式数据库
• Neo4j：图数据库

数据库模型

1. 层次模型（Hierarchical Model）

• 树形结构
• 一对多关系

2. 网状模型（Network Model）

• 图结构
• 多对多关系

3. 关系模型（Relational Model）

• 表结构
• 基于数学集合理论
• 最常用的模型

4. 对象模型（Object Model）

• 面向对象
• 对象关系映射（ORM）

SQL 基础

SQL 语言分类

1. DDL（Data Definition Language，数据定义语言）

• CREATE：创建数据库、表、索引
• ALTER：修改表结构
• DROP：删除数据库、表、索引
• TRUNCATE：清空表数据

2. DML（Data Manipulation Language，数据操作语言）

• SELECT：查询数据
• INSERT：插入数据
• UPDATE：更新数据
• DELETE：删除数据

3. DCL（Data Control Language，数据控制语言）

• GRANT：授予权限
• REVOKE：撤销权限

4. TCL（Transaction Control Language，事务控制语言）

• COMMIT：提交事务
• ROLLBACK：回滚事务
• SAVEPOINT：设置保存点

基本 SQL 语句

创建表：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
);

插入数据：

INSERT INTO users (username, email, password) 
VALUES ('john', 'john@example.com', 'password123');

查询数据：

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

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

-- 聚合查询
SELECT COUNT(*) as total, 
       AVG(age) as avg_age 
FROM users 
WHERE age > 18;

-- 分组查询
SELECT department, COUNT(*) as count 
FROM employees 
GROUP BY department 
HAVING count > 5;

更新数据：

UPDATE users 
SET email = 'newemail@example.com' 
WHERE id = 1;

删除数据：

DELETE FROM users WHERE id = 1;

SQL 连接（JOIN）

内连接（INNER JOIN）：

SELECT u.username, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

左连接（LEFT JOIN）：

SELECT u.username, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

右连接（RIGHT JOIN）：

SELECT u.username, o.order_id, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

全连接（FULL OUTER JOIN）：

-- MySQL 不支持 FULL OUTER JOIN，可以使用 UNION
SELECT u.username, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.username, o.order_id, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

自连接（Self Join）：

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

子查询

标量子查询：

SELECT * FROM users 
WHERE age > (SELECT AVG(age) FROM users);

关联子查询：

SELECT * FROM orders o1
WHERE o1.amount > (
    SELECT AVG(o2.amount) 
    FROM orders o2 
    WHERE o2.user_id = o1.user_id
);

EXISTS 子查询：

SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id
);

IN 子查询：

SELECT * FROM users 
WHERE id IN (
    SELECT DISTINCT user_id FROM orders
);

数据库设计原则

数据库范式

第一范式（1NF）：

• 每个字段都是不可分割的原子值
• 每个字段都有唯一名称
• 每个记录都是唯一的

示例（不符合 1NF）：

用户表
id | 姓名 | 联系方式
1  | 张三 | 电话:123, 邮箱:zhang@example.com

符合 1NF：

用户表
id | 姓名 | 电话 | 邮箱
1  | 张三 | 123  | zhang@example.com

第二范式（2NF）：

• 满足 1NF
• 非主键字段完全依赖于主键（不能部分依赖）

示例（不符合 2NF）：

订单详情表
订单号 | 产品ID | 产品名称 | 数量 | 单价
001    | P1     | 产品A   | 2    | 100

符合 2NF：

订单详情表
订单号 | 产品ID | 数量 | 单价

产品表
产品ID | 产品名称
P1     | 产品A

第三范式（3NF）：

• 满足 2NF
• 非主键字段不能传递依赖于主键

示例（不符合 3NF）：

员工表
员工ID | 姓名 | 部门ID | 部门名称 | 部门位置
1      | 张三 | D1     | 技术部  | 北京

符合 3NF：

员工表
员工ID | 姓名 | 部门ID

部门表
部门ID | 部门名称 | 部门位置
D1     | 技术部  | 北京

BCNF（Boyce-Codd 范式）：

• 满足 3NF
• 每个决定因素都是候选键

反范式化（Denormalization）：

• 为了提高查询性能，故意违反范式
• 需要权衡数据一致性和查询性能

ER 模型（实体-关系模型）

实体（Entity）：

• 具有独立存在意义的对象
• 例如：用户、订单、产品

属性（Attribute）：

• 实体的特征
• 例如：用户的姓名、邮箱

关系（Relationship）：

• 实体之间的联系
• 例如：用户和订单是一对多关系

ER 图示例：

用户（1）------< 订单（N）
  |                  |
  |                  |
  属性：             属性：
  - 用户ID           - 订单ID
  - 姓名            - 用户ID（外键）
  - 邮箱            - 订单日期

数据库设计步骤

1. 需求分析

   • 了解业务需求
   • 确定数据实体和关系

2. 概念设计

   • 绘制 ER 图
   • 识别实体、属性、关系

3. 逻辑设计

   • 将 ER 图转换为表结构
   • 规范化设计
   • 设计索引

4. 物理设计

   • 选择存储引擎
   • 设计分区策略
   • 优化性能

5. 实施和维护

   • 创建数据库和表
   • 建立索引
   • 持续优化

MySQL 原理

MySQL 架构

MySQL 架构层次：

1. 连接层：处理客户端连接
2. 服务层：SQL 解析、优化、执行
3. 存储引擎层：数据存储和检索
4. 文件系统层：数据文件存储

MySQL 存储引擎

InnoDB（默认引擎）：

• 特点：
  • 支持事务（ACID）
  • 支持外键
  • 支持行级锁
  • 支持崩溃恢复
• 适用场景：事务性应用，需要高并发
• 存储：表数据和索引存储在 .ibd 文件中

MyISAM：

• 特点：
  • 不支持事务
  • 不支持外键
  • 支持表级锁
  • 查询速度快
• 适用场景：只读应用，日志记录
• 存储：.frm（表结构）、.MYD（数据）、.MYI（索引）

Memory（HEAP）：

• 特点：
  • 数据存储在内存中
  • 速度快但数据易丢失
• 适用场景：临时表，缓存

对比：

特性	InnoDB	MyISAM
事务支持	✓	✗
外键支持	✓	✗
锁粒度	行级锁	表级锁
崩溃恢复	✓	✗
全文索引	5.6+	✓
查询速度	较慢	较快
写入速度	较慢	较快

MySQL 索引

什么是索引？
索引是数据库中用于快速查找数据的数据结构，类似于书籍的目录。

索引的类型：

1. B-Tree 索引（默认）：

-- 创建普通索引
CREATE INDEX idx_name ON users(username);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 创建复合索引
CREATE INDEX idx_name_age ON users(username, age);

2. 哈希索引：

• Memory 引擎支持
• 只支持等值查询
• 不支持范围查询

3. 全文索引：

-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 使用全文索引查询
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('search keyword');

4. 空间索引（R-Tree）：

• 用于地理空间数据
• MyISAM 和 InnoDB 5.7+ 支持

索引的优势：

• 加快查询速度
• 加快排序和分组
• 唯一索引保证数据唯一性

索引的劣势：

• 占用存储空间
• 降低写入速度（需要维护索引）
• 索引过多会影响性能

索引使用原则：

1. 经常查询的列：WHERE、JOIN、ORDER BY 子句中的列
2. 选择性高的列：不同值较多的列
3. 避免在小表上建索引：小表全表扫描可能更快
4. 避免在频繁更新的列上建索引：维护索引开销大
5. 使用复合索引时注意顺序：最左匹配原则

索引失效的情况：

-- 1. 使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;  -- 索引失效

-- 2. 类型转换
SELECT * FROM users WHERE id = '1';  -- 如果 id 是数字类型，可能失效

-- 3. 使用 NOT、!=、<>
SELECT * FROM users WHERE id != 1;  -- 索引可能失效

-- 4. 使用 LIKE 时以 % 开头
SELECT * FROM users WHERE username LIKE '%john';  -- 索引失效

-- 5. 复合索引未使用最左前缀
-- 索引 (username, age)
SELECT * FROM users WHERE age = 25;  -- 索引失效（未使用 username）

MySQL 事务

事务的特性（ACID）：

1. 原子性（Atomicity）：

• 事务中的所有操作要么全部成功，要么全部失败
• 通过 undo log 实现

2. 一致性（Consistency）：

• 事务执行前后数据库保持一致状态
• 通过应用层逻辑和约束保证

3. 隔离性（Isolation）：

• 并发事务之间相互隔离
• 通过锁机制实现

4. 持久性（Durability）：

• 事务提交后，数据永久保存
• 通过 redo log 实现

事务隔离级别：

1. READ UNCOMMITTED（读未提交）：

• 最低隔离级别
• 可能读取到未提交的数据（脏读）

2. READ COMMITTED（读已提交）：

• 只能读取已提交的数据
• 可能出现不可重复读
• Oracle、SQL Server 默认级别

3. REPEATABLE READ（可重复读）：

• 同一事务中多次读取结果一致
• 可能出现幻读
• MySQL InnoDB 默认级别（通过 MVCC 解决幻读）

4. SERIALIZABLE（串行化）：

• 最高隔离级别
• 完全隔离，避免所有问题
• 性能最差

隔离级别对比：

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	✓	✓	✓
READ COMMITTED	✗	✓	✓
REPEATABLE READ	✗	✗	✓
SERIALIZABLE	✗	✗	✗

设置隔离级别：

-- 查看当前隔离级别
SELECT @@transaction_isolation;

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

并发问题：

1. 脏读（Dirty Read）：

事务 A：UPDATE users SET balance = balance - 100 WHERE id = 1;  -- 未提交
事务 B：SELECT balance FROM users WHERE id = 1;  -- 读取到未提交的数据

2. 不可重复读（Non-Repeatable Read）：

事务 A：SELECT balance FROM users WHERE id = 1;  -- 读取 1000
事务 B：UPDATE users SET balance = 900 WHERE id = 1; COMMIT;
事务 A：SELECT balance FROM users WHERE id = 1;  -- 读取 900（不一致）

3. 幻读（Phantom Read）：

事务 A：SELECT COUNT(*) FROM users WHERE age > 25;  -- 返回 10
事务 B：INSERT INTO users (age) VALUES (30); COMMIT;
事务 A：SELECT COUNT(*) FROM users WHERE age > 25;  -- 返回 11（出现新行）

MySQL 锁机制

锁的类型：

1. 按粒度分类：

• 表级锁：锁住整个表（MyISAM）
• 行级锁：锁住特定行（InnoDB）
• 页级锁：锁住一页数据

2. 按性质分类：

• 共享锁（S 锁，读锁）：允许其他事务读取，不允许写入
• 排他锁（X 锁，写锁）：不允许其他事务读取和写入

3. 按意图分类：

• 意向共享锁（IS 锁）
• 意向排他锁（IX 锁）

行锁的类型：

1. Record Lock（记录锁）：

• 锁住单行记录

2. Gap Lock（间隙锁）：

• 锁住索引记录之间的间隙
• 防止幻读

3. Next-Key Lock（临键锁）：

• Record Lock + Gap Lock
• 锁住记录和前面的间隙

死锁：

死锁示例：

-- 事务 A
BEGIN;
UPDATE users SET name = 'A' WHERE id = 1;  -- 锁定 id=1
UPDATE users SET name = 'B' WHERE id = 2;  -- 等待 id=2

-- 事务 B
BEGIN;
UPDATE users SET name = 'C' WHERE id = 2;  -- 锁定 id=2
UPDATE users SET name = 'D' WHERE id = 1;  -- 等待 id=1（死锁）

死锁预防：

1. 按相同顺序访问资源
2. 尽量减少事务时间
3. 使用较低的隔离级别
4. 为表添加合理的索引

死锁检测：

-- 查看死锁日志
SHOW ENGINE INNODB STATUS;

MySQL 日志

1. 错误日志（Error Log）：

• 记录 MySQL 运行错误
• 位置：/var/log/mysql/error.log

2. 查询日志（Query Log）：

• 记录所有 SQL 语句
• 用于调试和审计

3. 慢查询日志（Slow Query Log）：

• 记录执行时间超过阈值的查询

  -- 开启慢查询日志
  SET GLOBAL slow_query_log = 'ON';
  SET GLOBAL long_query_time = 2;  -- 2 秒

4. 二进制日志（Binlog）：

• 记录所有数据变更操作
• 用于主从复制和数据恢复

  -- 查看 binlog
  SHOW BINARY LOGS;
  SHOW BINLOG EVENTS IN 'mysql-bin.000001';

5. 重做日志（Redo Log）：

• InnoDB 特有的日志
• 记录事务的物理变更
• 用于崩溃恢复

6. 撤销日志（Undo Log）：

• InnoDB 特有的日志
• 记录事务的逻辑变更
• 用于回滚和 MVCC

MVCC（多版本并发控制）

什么是 MVCC？
MVCC 通过保存数据的历史版本，实现读操作不加锁，提高并发性能。

实现原理：

• 版本链：每行数据有多个版本，通过指针链接
• ReadView：事务读取时创建快照，决定能看到哪些版本
• undo log：存储历史版本数据

优势：

• 读操作不加锁，提高并发
• 读写不冲突
• 减少死锁

示例：

时间线：
T1: 事务 A 插入 id=1, name='Alice', trx_id=100
T2: 事务 B 更新 id=1, name='Bob', trx_id=200
T3: 事务 C 读取 id=1（创建 ReadView，只能看到 trx_id<200 的版本）
     结果：name='Alice'（读取到历史版本）

MySQL 查询优化

执行计划（EXPLAIN）：

EXPLAIN SELECT * FROM users WHERE username = 'john';

-- 关键字段：
-- type: 连接类型（system > const > eq_ref > ref > range > index > ALL）
-- key: 使用的索引
-- rows: 扫描的行数
-- Extra: 额外信息（Using index, Using where, Using filesort）

优化技巧：

1. 使用索引：

-- 创建合适的索引
CREATE INDEX idx_username ON users(username);

-- 使用覆盖索引
SELECT id, username FROM users WHERE username = 'john';  -- 索引包含所有字段

*2. 避免 SELECT ：

-- 不好的做法
SELECT * FROM users;

-- 好的做法
SELECT id, username, email FROM users;

3. 使用 LIMIT：

SELECT * FROM users LIMIT 10;

4. 优化 JOIN：

-- 确保 JOIN 条件有索引
CREATE INDEX idx_user_id ON orders(user_id);

SELECT u.*, o.* 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id;

5. 使用 EXISTS 代替 IN：

-- 如果子查询结果集很大，使用 EXISTS 可能更快
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

6. 避免在 WHERE 子句中使用函数：

-- 不好的做法
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 好的做法
SELECT * FROM users 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

PostgreSQL 原理

PostgreSQL 特点

PostgreSQL 的优势：

• 标准兼容性：高度符合 SQL 标准
• 功能丰富：支持复杂数据类型、自定义函数、触发器
• 扩展性：支持多种扩展
• 并发控制：使用 MVCC 实现高并发
• 可靠性：数据一致性和完整性保证强
• 开源：完全开源，无许可证限制

PostgreSQL vs MySQL：

特性	PostgreSQL	MySQL
SQL 标准	高度兼容	部分兼容
数据类型	丰富（数组、JSON、几何类型等）	相对简单
事务支持	完整	InnoDB 支持
全文搜索	内置	MyISAM 支持
窗口函数	支持	8.0+ 支持
JSON 支持	原生支持	5.7+ 支持
性能	复杂查询性能好	简单查询性能好
扩展性	扩展插件丰富	相对较少

PostgreSQL 数据类型

数值类型：

SMALLINT      -- 2 字节
INTEGER       -- 4 字节
BIGINT        -- 8 字节
DECIMAL(10,2) -- 精确数值
NUMERIC(10,2) -- 精确数值（同 DECIMAL）
REAL          -- 单精度浮点
DOUBLE PRECISION -- 双精度浮点

字符串类型：

CHAR(10)      -- 固定长度
VARCHAR(100)  -- 可变长度
TEXT          -- 无限长度

日期时间类型：

DATE          -- 日期
TIME          -- 时间
TIMESTAMP     -- 日期时间
INTERVAL      -- 时间间隔

布尔类型：

BOOLEAN       -- true/false

数组类型：

CREATE TABLE users (
    id INT PRIMARY KEY,
    hobbies TEXT[]  -- 文本数组
);

INSERT INTO users VALUES (1, ARRAY['reading', 'swimming']);

JSON 类型：

CREATE TABLE products (
    id INT PRIMARY KEY,
    attributes JSONB  -- JSON 二进制格式
);

INSERT INTO products VALUES (1, '{"color": "red", "size": "large"}');

-- 查询 JSON
SELECT attributes->>'color' FROM products WHERE id = 1;

PostgreSQL 索引

索引类型：

1. B-Tree 索引（默认）：

CREATE INDEX idx_username ON users(username);

2. 哈希索引：

CREATE INDEX idx_email_hash ON users USING HASH(email);

3. GiST 索引（通用搜索树）：

• 用于全文搜索、几何数据

4. GIN 索引（广义倒排索引）：

• 用于数组、全文搜索、JSON

  CREATE INDEX idx_content_gin ON articles USING GIN(to_tsvector('english', content));

5. BRIN 索引（块范围索引）：

• 用于大型表，按块存储

部分索引：

-- 只对满足条件的行创建索引
CREATE INDEX idx_active_users ON users(username) WHERE active = true;

表达式索引：

-- 对表达式结果创建索引
CREATE INDEX idx_lower_username ON users(LOWER(username));

PostgreSQL 并发控制

PostgreSQL 使用 MVCC：

• 与 MySQL InnoDB 类似，但实现方式不同
• 使用元组（tuple）版本管理
• 通过事务 ID（XID）判断可见性

事务隔离级别：

-- 查看隔离级别
SHOW transaction_isolation;

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

PostgreSQL 隔离级别：

1. READ UNCOMMITTED：实际上等同于 READ COMMITTED
2. READ COMMITTED：默认级别
3. REPEATABLE READ：通过快照实现
4. SERIALIZABLE：真正的串行化

PostgreSQL 高级特性

窗口函数：

-- 计算每个部门的平均工资
SELECT 
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as avg_dept_salary
FROM employees;

公共表表达式（CTE）：

WITH RECURSIVE tree AS (
    -- 基础查询
    SELECT id, parent_id, name 
    FROM categories 
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归查询
    SELECT c.id, c.parent_id, c.name
    FROM categories c
    INNER JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

数组操作：

-- 数组元素操作
SELECT ARRAY[1,2,3] || ARRAY[4,5];  -- 连接数组

-- 数组查询
SELECT * FROM users WHERE 'reading' = ANY(hobbies);

JSON 操作：

-- JSON 查询
SELECT attributes->>'color' as color FROM products;

-- JSON 路径查询
SELECT jsonb_path_query(attributes, '$.color') FROM products;

自定义函数：

-- PL/pgSQL 函数
CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER AS $$
DECLARE
    count INTEGER;
BEGIN
    SELECT COUNT(*) INTO count FROM users;
    RETURN count;
END;
$$ LANGUAGE plpgsql;

触发器：

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

数据库设计原则

命名规范

表命名：

• 使用复数形式或明确含义：users, orders, order_items
• 使用下划线分隔：user_profiles
• 避免使用关键字：不使用 user, order 等 SQL 关键字

字段命名：

• 使用小写字母和下划线：user_id, created_at
• 布尔字段使用 is_, has_ 前缀：is_active, has_permission
• 外键使用 表名_id 格式：user_id, order_id

索引命名：

• 普通索引：idx_字段名，如 idx_username
• 唯一索引：uk_字段名，如 uk_email
• 主键索引：pk_表名，如 pk_users
• 外键索引：fk_表名_字段名，如 fk_orders_user_id

字段设计原则

1. 选择合适的数据类型：

• 数值类型：根据范围选择 INT、BIGINT
• 字符串类型：固定长度用 CHAR，可变长度用 VARCHAR
• 日期时间：使用 TIMESTAMP 或 DATETIME
• 布尔值：使用 BOOLEAN 或 TINYINT(1)

2. 使用 NOT NULL 约束：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NULL  -- 允许为空
);

3. 设置默认值：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

4. 使用 AUTO_INCREMENT：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ...
);

主键和外键设计

主键选择：

• 自增主键：简单、高效（推荐）
• UUID：分布式系统，但性能较差
• 业务主键：有意义的值，但不推荐

外键设计：

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);

外键约束选项：

• ON DELETE CASCADE：删除父记录时自动删除子记录
• ON DELETE SET NULL：删除父记录时将外键设为 NULL
• ON DELETE RESTRICT：禁止删除有子记录的父记录
• ON UPDATE CASCADE：更新父记录主键时同步更新子记录

索引设计原则

1. 主键自动创建索引：

PRIMARY KEY (id)  -- 自动创建主键索引

2. 外键创建索引：

FOREIGN KEY (user_id) REFERENCES users(id);  -- 应该在 user_id 上创建索引
CREATE INDEX idx_user_id ON orders(user_id);

3. 经常查询的字段创建索引：

CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email);

4. 复合索引注意顺序：

-- 使用最左匹配原则
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 可以使用的查询：
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';

-- 不能使用的查询：
SELECT * FROM orders WHERE status = 'pending';  -- 未使用 user_id

5. 避免过多索引：

• 每个索引都需要维护，过多索引会影响写入性能
• 一般建议每个表不超过 5-10 个索引

表设计最佳实践

1. 避免大宽表：

• 将不常用的字段拆分到扩展表
• 提高查询效率

2. 合理使用垂直拆分：

用户基础表（user_basic）
- id
- username
- email

用户详情表（user_profile）
- user_id
- bio
- avatar
- address

3. 合理使用水平拆分（分表）：

• 按时间分表：orders_2024, orders_2025
• 按哈希分表：users_0, users_1, users_2

4. 使用软删除：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    deleted_at TIMESTAMP NULL,  -- 软删除标记
    INDEX idx_deleted_at (deleted_at)
);

5. 添加审计字段：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT,
    updated_by INT
);

常见面试题

1. 数据库三范式是什么？

答案：

• 第一范式（1NF）：每个字段都是原子值，不可再分
• 第二范式（2NF）：满足 1NF，且非主键字段完全依赖于主键
• 第三范式（3NF）：满足 2NF，且非主键字段不能传递依赖于主键

示例：

不符合 3NF：
订单表（订单号，客户ID，客户姓名，客户地址，订单金额）
客户姓名和客户地址依赖于客户ID，而不是订单号

符合 3NF：
订单表（订单号，客户ID，订单金额）
客户表（客户ID，客户姓名，客户地址）

2. 事务的 ACID 特性是什么？

答案：

• 原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败
• 一致性（Consistency）：事务执行前后数据库保持一致状态
• 隔离性（Isolation）：并发事务之间相互隔离
• 持久性（Durability）：事务提交后数据永久保存

3. MySQL 的隔离级别有哪些？分别解决了什么问题？

答案：

1. READ UNCOMMITTED：可能读取未提交数据（脏读）
2. READ COMMITTED：只能读取已提交数据，解决脏读
3. REPEATABLE READ：同一事务多次读取结果一致，解决不可重复读
4. SERIALIZABLE：完全隔离，解决所有问题，但性能最差

MySQL InnoDB 默认是 REPEATABLE READ，通过 MVCC 解决了幻读问题。

4. 索引的类型有哪些？索引什么时候会失效？

答案：
索引类型：

• B-Tree 索引（默认）
• 哈希索引
• 全文索引
• 空间索引

索引失效情况：

1. 在 WHERE 子句中使用函数：WHERE YEAR(date) = 2024
2. 类型转换：WHERE id = '1'（id 是数字类型）
3. 使用 NOT、!=、<>
4. LIKE 以 % 开头：WHERE name LIKE '%john'
5. 复合索引未使用最左前缀
6. 使用 OR 连接条件（除非所有列都有索引）

5. MySQL InnoDB 和 MyISAM 的区别？

答案：

特性	InnoDB	MyISAM
事务支持	✓	✗
外键支持	✓	✗
锁粒度	行级锁	表级锁
崩溃恢复	✓	✗
全文索引	5.6+	✓
查询缓存	✗	✓
存储文件	.ibd	.frm, .MYD, .MYI
适用场景	事务性应用	只读应用

6. 什么是 MVCC？它是如何实现的？

答案：
MVCC（Multi-Version Concurrency Control）是多版本并发控制。

实现原理：

• 版本链：每行数据有多个版本，通过指针链接
• ReadView：事务读取时创建快照，决定能看到哪些版本
• undo log：存储历史版本数据

优势：

• 读操作不加锁，提高并发
• 读写不冲突
• 减少死锁

7. 什么是死锁？如何避免死锁？

答案：
死锁是指两个或多个事务相互等待对方释放资源，导致无法继续执行。

避免方法：

1. 按相同顺序访问资源
2. 尽量减少事务时间
3. 使用较低的隔离级别
4. 为表添加合理的索引
5. 使用 SELECT ... FOR UPDATE 时指定超时

8. 如何优化慢查询？

答案：

1. 使用 EXPLAIN 分析执行计划
2. 创建合适的索引
3. 避免 SELECT *
4. 优化 JOIN 查询：确保 JOIN 条件有索引
5. 使用 LIMIT 限制结果集
6. 避免在 WHERE 子句中使用函数
7. 使用覆盖索引
8. 合理使用分页：使用游标分页代替 OFFSET

9. 数据库分库分表的策略？

答案：
垂直拆分：

• 按业务模块拆分：用户库、订单库
• 按表字段拆分：基础表、扩展表

水平拆分：

• 按时间分表：orders_2024, orders_2025
• 按哈希分表：user_id % 4，分成 4 张表
• 按范围分表：user_id 1-1000000 一张表

分库分表中间件：

• ShardingSphere
• MyCat
• TDDL

10. 主从复制原理？

答案：
MySQL 主从复制流程：

1. 主库将数据变更记录到 binlog
2. 从库的 I/O 线程连接主库，读取 binlog
3. 从库将 binlog 写入 relay log
4. 从库的 SQL 线程读取 relay log，重放 SQL 语句
5. 从库数据与主库同步

复制方式：

• 异步复制：主库不等待从库确认
• 半同步复制：至少一个从库确认
• 全同步复制：所有从库确认

11. 数据库连接池的作用？

答案：
连接池用于管理和复用数据库连接，避免频繁创建和销毁连接。

优势：

• 减少连接建立时间
• 控制连接数量
• 提高性能
• 资源复用

常用连接池：

• Java：HikariCP、Druid、C3P0
• Python：SQLAlchemy、pymysql
• Node.js：mysql2、pg

12. PostgreSQL 和 MySQL 的主要区别？

答案：

特性	PostgreSQL	MySQL
SQL 标准	高度兼容	部分兼容
数据类型	丰富（数组、JSON、几何类型）	相对简单
全文搜索	内置支持	MyISAM 支持
窗口函数	完整支持	8.0+ 支持
JSON 支持	原生 JSONB	5.7+ 支持
扩展性	扩展插件丰富	相对较少
性能	复杂查询性能好	简单查询性能好
事务	MVCC 实现	InnoDB MVCC
许可证	PostgreSQL 许可证	GPL/商业

13. 如何设计一个高可用的数据库架构？

答案：

1. 主从复制：一主多从，读写分离
2. 双主复制：两个主库互相复制（需解决冲突）
3. 分库分表：水平扩展
4. 缓存层：Redis 缓存热点数据
5. 负载均衡：读写分离，负载均衡
6. 监控告警：实时监控数据库状态
7. 备份恢复：定期备份，测试恢复流程

14. 索引的底层数据结构是什么？

答案：
B-Tree 索引：

• MySQL InnoDB 使用 B+Tree
• 非叶子节点存储键值，叶子节点存储数据
• 支持范围查询和排序

哈希索引：

• 使用哈希表
• 只支持等值查询
• O(1) 查询时间复杂度

B+Tree vs B-Tree：

• B+Tree 非叶子节点不存储数据，只存储键值
• B+Tree 叶子节点通过指针连接，方便范围查询
• B+Tree 更适合磁盘存储（减少 I/O）

15. 如何设计一个订单表？

答案：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL UNIQUE COMMENT '订单号',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    total_amount DECIMAL(10,2) NOT NULL COMMENT '订单总额',
    pay_amount DECIMAL(10,2) NOT NULL COMMENT '实付金额',
    status TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态：0-待支付，1-已支付，2-已发货，3-已完成，4-已取消',
    pay_time TIMESTAMP NULL COMMENT '支付时间',
    ship_time TIMESTAMP NULL COMMENT '发货时间',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_user_id (user_id),
    INDEX idx_order_no (order_no),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) COMMENT='订单表';

CREATE TABLE order_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL COMMENT '订单ID',
    product_id BIGINT NOT NULL COMMENT '商品ID',
    product_name VARCHAR(200) NOT NULL COMMENT '商品名称',
    product_price DECIMAL(10,2) NOT NULL COMMENT '商品单价',
    quantity INT NOT NULL COMMENT '购买数量',
    subtotal DECIMAL(10,2) NOT NULL COMMENT '小计',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) COMMENT='订单明细表';

总结

核心要点：

1. 数据库基础：

   • 理解数据库基本概念和类型
   • 掌握 SQL 语言（DDL、DML、DCL、TCL）
   • 熟悉数据库设计原则和范式

2. MySQL：

   • 存储引擎（InnoDB、MyISAM）
   • 索引原理和使用
   • 事务和隔离级别
   • 锁机制和 MVCC
   • 查询优化

3. PostgreSQL：

   • 高级数据类型（数组、JSON）
   • 丰富的索引类型
   • 窗口函数和 CTE
   • 自定义函数和触发器

4. 数据库设计：

   • 遵循范式化设计
   • 合理的索引设计
   • 主键和外键设计
   • 分库分表策略

面试重点：

• 数据库三范式
• ACID 特性
• 事务隔离级别和并发问题
• 索引原理和优化
• MySQL InnoDB 和 MyISAM 区别
• MVCC 原理
• 死锁和避免方法
• 慢查询优化
• 主从复制原理
• 分库分表策略

实际应用：

在实际项目中：

• 选择合适的数据库：根据业务需求选择 MySQL 或 PostgreSQL
• 设计合理的表结构：遵循范式，考虑扩展性
• 创建合适的索引：提高查询性能
• 优化慢查询：使用 EXPLAIN 分析，创建索引
• 保证数据一致性：使用事务，设置合适的隔离级别
• 设计高可用架构：主从复制，读写分离，分库分表

---

参考资料：

• 《高性能 MySQL》
• 《PostgreSQL 即学即用》
• 《数据库系统概念》（Database System Concepts）
• MySQL 官方文档
• PostgreSQL 官方文档