四、数据库：数据的持久化存储
4.1 关系型数据库与非关系型数据库
4.2 SQL 核心知识
-- 1. 数据库操作
CREATE DATABASE myapp;
USE myapp;
-- 2. 表操作
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 3. 索引（性能优化关键）
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_age ON users(age);
-- 复合索引
CREATE INDEX idx_users_age_email ON users(age, email);
-- 4. 增删改查
-- 插入
INSERT INTO users (username, email, age) VALUES ('zhangsan', 'zs@example.com', 25);
-- 查询
SELECT * FROM users WHERE age > 18 ORDER BY created_at DESC LIMIT 10;
SELECT COUNT(*) FROM users WHERE age BETWEEN 18 AND 30;
SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 5;
-- 更新
UPDATE users SET age = 26 WHERE username = 'zhangsan';
-- 删除
DELETE FROM users WHERE username = 'zhangsan';
-- 5. 多表查询（JOIN）
-- 内连接
SELECT u.username, o.order_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 左连接（即使没有订单的用户也会显示）
SELECT u.username, COALESCE(o.order_amount, 0) as amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 6. 事务（保证数据一致性）
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 如果中间出错，执行 ROLLBACK
COMMIT;
-- 7. 查询优化分析
EXPLAIN SELECT * FROM users WHERE email = 'zs@example.com';
4.3 索引的工作原理
索引类似于书的目录，能大幅提升查询速度，但会降低写入速度。
// 索引底层使用 B+ 树（平衡多路搜索树）
// B+ 树特点：
// 1. 所有数据都在叶子节点
// 2. 叶子节点之间有链表连接（范围查询高效）
// 3. 节点存储多个键值（降低树高度）
// 无索引查询：全表扫描 O(n)
// 有索引查询：B+树查找 O(log n)
// 索引失效的场景
SELECT * FROM users WHERE age + 1 = 26; -- ❌ 对列进行运算
SELECT * FROM users WHERE UPPER(name) = 'ZHANG'; -- ❌ 使用函数
SELECT * FROM users WHERE name LIKE '%三%'; -- ❌ 前导模糊匹配
SELECT * FROM users WHERE age > 18 OR name = '李四'; -- ⚠️ OR 可能失效
4.4 数据库连接池
// Node.js + MySQL 连接池示例
const mysql = require('mysql2');
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
password: 'password',
database: 'myapp',
waitForConnections: true,
connectionLimit: 10, // 最大连接数
queueLimit: 0, // 队列无限制
enableKeepAlive: true,
keepAliveInitialDelay: 0
});
// 使用连接池执行查询
async function query(sql, params) {
const [rows] = await pool.promise().execute(sql, params);
return rows;
}
// 连接池的好处
// 1. 复用连接，避免频繁创建/销毁开销
// 2. 限制并发连接数，防止数据库过载
// 3. 自动管理连接生命周期
4.5 数据库设计原则
三大范式
-- 违反第三范式的示例
CREATE TABLE orders_bad (
order_id INT PRIMARY KEY,
user_id INT,
user_name VARCHAR(50), -- 依赖 user_id，不是直接依赖 order_id
product_id INT,
product_name VARCHAR(50) -- 依赖 product_id
);
-- 符合第三范式
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
product_id INT,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(50)
);
