数据库技术

关系型数据库

MySQL

MySQL是最流行的开源关系型数据库管理系统。

基础操作

sql

-- 创建数据库
CREATE DATABASE mydb;

-- 使用数据库
USE mydb;

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(120) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 插入数据
INSERT INTO users (username, email, password_hash) 
VALUES ('john', '[email protected]', 'hashed_password');

-- 查询数据
SELECT * FROM users WHERE username = 'john';

-- 更新数据
UPDATE users SET email = '[email protected]' WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE id = 1;

索引优化

sql

-- 创建索引
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email);

-- 复合索引
CREATE INDEX idx_username_email ON users(username, email);

-- 查看索引
SHOW INDEX FROM users;

-- 分析查询计划
EXPLAIN SELECT * FROM users WHERE username = 'john';

事务处理

sql

-- 开始事务
START TRANSACTION;

-- 执行操作
INSERT INTO users (username, email, password_hash) 
VALUES ('user1', '[email protected]', 'hash1');

UPDATE users SET email = '[email protected]' WHERE username = 'user1';

-- 提交事务
COMMIT;

-- 或者回滚事务
ROLLBACK;

PostgreSQL

PostgreSQL是一个功能强大的开源对象关系数据库系统。

基础操作

sql

-- 创建数据库
CREATE DATABASE mydb;

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(120) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建更新时间触发器
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_updated_at 
    BEFORE UPDATE ON users 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

JSON支持

sql

-- 创建包含JSON字段的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    attributes JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入JSON数据
INSERT INTO products (name, attributes) 
VALUES ('iPhone', '{"color": "black", "storage": "128GB", "price": 999}');

-- 查询JSON字段
SELECT name, attributes->>'color' as color 
FROM products 
WHERE attributes->>'storage' = '128GB';

SQL Server

SQL Server是微软开发的关系型数据库管理系统。

基础操作

sql

-- 创建数据库
CREATE DATABASE mydb;

-- 使用数据库
USE mydb;

-- 创建表
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    username NVARCHAR(100) UNIQUE NOT NULL,
    email NVARCHAR(120) UNIQUE NOT NULL,
    password_hash NVARCHAR(255) NOT NULL,
    created_at DATETIME2 DEFAULT GETDATE(),
    updated_at DATETIME2 DEFAULT GETDATE()
);

-- 创建更新触发器
CREATE TRIGGER tr_users_update
ON users
AFTER UPDATE
AS
BEGIN
    UPDATE users 
    SET updated_at = GETDATE()
    FROM users u
    INNER JOIN inserted i ON u.id = i.id;
END;

NoSQL数据库

MongoDB

MongoDB是一个基于文档的NoSQL数据库。

基础操作

javascript

// 连接数据库
const { MongoClient } = require('mongodb');
const client = new MongoClient('mongodb://localhost:27017');

// 插入文档
async function insertUser() {
    try {
        await client.connect();
        const db = client.db('mydb');
        const collection = db.collection('users');
        
        const user = {
            username: 'john',
            email: '[email protected]',
            password_hash: 'hashed_password',
            created_at: new Date()
        };
        
        const result = await collection.insertOne(user);
        console.log('插入的文档ID:', result.insertedId);
    } finally {
        await client.close();
    }
}

// 查询文档
async function findUser(username) {
    try {
        await client.connect();
        const db = client.db('mydb');
        const collection = db.collection('users');
        
        const user = await collection.findOne({ username: username });
        return user;
    } finally {
        await client.close();
    }
}

// 更新文档
async function updateUser(username, updates) {
    try {
        await client.connect();
        const db = client.db('mydb');
        const collection = db.collection('users');
        
        const result = await collection.updateOne(
            { username: username },
            { $set: { ...updates, updated_at: new Date() } }
        );
        
        return result.modifiedCount > 0;
    } finally {
        await client.close();
    }
}

// 删除文档
async function deleteUser(username) {
    try {
        await client.connect();
        const db = client.db('mydb');
        const collection = db.collection('users');
        
        const result = await collection.deleteOne({ username: username });
        return result.deletedCount > 0;
    } finally {
        await client.close();
    }
}

聚合管道

javascript

// 聚合查询示例
async function aggregateUsers() {
    try {
        await client.connect();
        const db = client.db('mydb');
        const collection = db.collection('users');
        
        const pipeline = [
            // 按城市分组统计用户数量
            { $group: { _id: '$city', count: { $sum: 1 } } },
            // 按数量排序
            { $sort: { count: -1 } },
            // 限制结果数量
            { $limit: 10 }
        ];
        
        const results = await collection.aggregate(pipeline).toArray();
        return results;
    } finally {
        await client.close();
    }
}

Redis

Redis是一个高性能的键值对数据库。

基础操作

javascript

const redis = require('redis');
const client = redis.createClient();

// 字符串操作
async function stringOperations() {
    await client.connect();
    
    // 设置值
    await client.set('user:1:name', 'John');
    await client.set('user:1:email', '[email protected]');
    
    // 获取值
    const name = await client.get('user:1:name');
    const email = await client.get('user:1:email');
    
    // 设置过期时间
    await client.setEx('temp:key', 3600, 'temporary value');
    
    await client.disconnect();
}

// 哈希操作
async function hashOperations() {
    await client.connect();
    
    // 设置哈希字段
    await client.hSet('user:1', {
        'username': 'john',
        'email': '[email protected]',
        'age': '25'
    });
    
    // 获取哈希字段
    const user = await client.hGetAll('user:1');
    
    // 更新单个字段
    await client.hSet('user:1', 'age', '26');
    
    await client.disconnect();
}

// 列表操作
async function listOperations() {
    await client.connect();
    
    // 添加元素到列表
    await client.lPush('user:1:posts', 'post1', 'post2', 'post3');
    
    // 获取列表元素
    const posts = await client.lRange('user:1:posts', 0, -1);
    
    // 弹出元素
    const post = await client.lPop('user:1:posts');
    
    await client.disconnect();
}

// 集合操作
async function setOperations() {
    await client.connect();
    
    // 添加元素到集合
    await client.sAdd('user:1:followers', 'user2', 'user3', 'user4');
    
    // 检查元素是否存在
    const isFollower = await client.sIsMember('user:1:followers', 'user2');
    
    // 获取集合所有元素
    const followers = await client.sMembers('user:1:followers');
    
    await client.disconnect();
}

数据库设计

规范化

sql

-- 第一范式：原子性
-- 错误示例
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    phone_numbers VARCHAR(255) -- 存储多个电话号码
);

-- 正确示例
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

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

-- 第二范式：消除部分依赖
-- 错误示例
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100), -- 部分依赖
    product_id INT,
    product_name VARCHAR(100),  -- 部分依赖
    quantity INT,
    price DECIMAL(10,2)
);

-- 正确示例
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 第三范式：消除传递依赖
-- 错误示例
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT,
    department_name VARCHAR(100), -- 传递依赖
    manager_id INT,
    manager_name VARCHAR(100)     -- 传递依赖
);

-- 正确示例
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT,
    manager_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id),
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

索引策略

sql

-- 单列索引
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email);

-- 复合索引
CREATE INDEX idx_username_email ON users(username, email);

-- 部分索引（PostgreSQL）
CREATE INDEX idx_active_users ON users(username) WHERE is_active = true;

-- 覆盖索引
CREATE INDEX idx_user_cover ON users(username, email, created_at);

-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(title, content);

性能优化

查询优化

sql

-- 使用EXPLAIN分析查询
EXPLAIN SELECT u.username, p.title 
FROM users u 
JOIN posts p ON u.id = p.user_id 
WHERE u.created_at > '2023-01-01';

-- 避免SELECT *
SELECT id, username, email FROM users WHERE is_active = true;

-- 使用LIMIT限制结果
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;

-- 使用索引提示
SELECT * FROM users USE INDEX (idx_username) WHERE username = 'john';

-- 避免在WHERE子句中使用函数
-- 错误
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 正确
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

连接池配置

javascript

// MySQL连接池配置
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydb',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
    acquireTimeout: 60000,
    timeout: 60000,
    reconnect: true
});

// 使用连接池
async function getUser(id) {
    const [rows] = await pool.execute(
        'SELECT * FROM users WHERE id = ?',
        [id]
    );
    return rows[0];
}

数据备份与恢复

MySQL备份

bash

# 完整备份
mysqldump -u root -p mydb > backup.sql

# 增量备份
mysqlbinlog --start-datetime="2023-01-01 00:00:00" \
    --stop-datetime="2023-01-02 00:00:00" \
    /var/lib/mysql/mysql-bin.000001 > incremental_backup.sql

# 恢复数据
mysql -u root -p mydb < backup.sql

MongoDB备份

bash

# 备份数据库
mongodump --db mydb --out /backup/

# 恢复数据库
mongorestore --db mydb /backup/mydb/

Redis备份

bash

# 手动备份
redis-cli BGSAVE

# 自动备份配置
# 在redis.conf中设置
save 900 1
save 300 10
save 60 10000

监控与维护

性能监控

sql

-- MySQL性能查询
SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Queries';

-- 查看慢查询日志
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- 查看当前连接
SHOW PROCESSLIST;

数据库维护

sql

-- 分析表
ANALYZE TABLE users;

-- 优化表
OPTIMIZE TABLE users;

-- 检查表
CHECK TABLE users;

-- 修复表
REPAIR TABLE users;

数据库技术是后端开发的核心，选择合适的数据库类型和优化策略对系统性能至关重要。

数据库技术 ​

关系型数据库 ​

MySQL ​

基础操作 ​

索引优化 ​

事务处理 ​

PostgreSQL ​

基础操作 ​

JSON支持 ​

SQL Server ​

基础操作 ​

NoSQL数据库 ​

MongoDB ​

基础操作 ​

聚合管道 ​

Redis ​

基础操作 ​

数据库设计 ​

规范化 ​

索引策略 ​

性能优化 ​

查询优化 ​

连接池配置 ​

数据备份与恢复 ​

MySQL备份 ​

MongoDB备份 ​

Redis备份 ​

监控与维护 ​

性能监控 ​

数据库维护 ​

数据库技术

关系型数据库

MySQL

基础操作

索引优化

事务处理

PostgreSQL

基础操作

JSON支持

SQL Server

基础操作

NoSQL数据库

MongoDB

基础操作

聚合管道

Redis

基础操作

数据库设计

规范化

索引策略

性能优化

查询优化

连接池配置

数据备份与恢复

MySQL备份

MongoDB备份

Redis备份

监控与维护

性能监控

数据库维护