MySQL数据库：从入门到优化

MySQL 基础

MySQL是一种开源的关系型数据库管理系统，广泛应用于Web开发和数据存储。

数据库操作

CREATE DATABASE IF NOT EXISTS example_db 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

USE example_db;

DROP DATABASE IF EXISTS example_db;

表操作

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

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'completed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

ALTER TABLE users ADD COLUMN age INT;

ALTER TABLE users MODIFY COLUMN email VARCHAR(200);

DROP TABLE IF EXISTS orders;

SQL 查询

SQL（Structured Query Language）是用于查询和管理数据库的标准语言。

基础查询

SELECT * FROM users;

SELECT id, username, email FROM users;

SELECT username, email FROM users WHERE age > 18;

SELECT * FROM users ORDER BY created_at DESC;

SELECT * FROM users LIMIT 10 OFFSET 20;

SELECT COUNT(*) FROM users;

SELECT AVG(age) FROM users;

SELECT status, COUNT(*) FROM orders GROUP BY status;

SELECT status, COUNT(*) FROM orders GROUP BY status HAVING COUNT(*) > 10;

连接查询

SELECT u.username, o.total_amount, o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

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

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

SELECT u.username, o1.total_amount AS first_order, o2.total_amount AS last_order
FROM users u
JOIN orders o1 ON u.id = o1.user_id
JOIN orders o2 ON u.id = o2.user_id
WHERE o1.created_at = (
    SELECT MIN(created_at) FROM orders WHERE user_id = u.id
) AND o2.created_at = (
    SELECT MAX(created_at) FROM orders WHERE user_id = u.id
);

子查询

SELECT username FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE status = 'paid');

SELECT username FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);

SELECT AVG(total_amount) FROM orders
WHERE user_id IN (SELECT id FROM users WHERE age > 25);

索引优化

索引是提高查询性能的关键，合理使用索引可以大幅提升数据库查询速度。

索引类型

• B-Tree索引：默认索引类型，适用于等值查询和范围查询
• 哈希索引：仅适用于等值查询
• 全文索引：用于全文搜索
• 空间索引：用于地理空间数据

索引创建

CREATE INDEX idx_users_email ON users(email);

CREATE UNIQUE INDEX idx_users_username ON users(username);

CREATE INDEX idx_orders_user_id_status ON orders(user_id, status);

ALTER TABLE users ADD INDEX idx_users_age(age);

DROP INDEX idx_users_email ON users;

索引使用原则

• 在WHERE、JOIN、ORDER BY子句中使用的列上创建索引
• 避免在频繁更新的列上创建索引
• 复合索引遵循最左前缀原则
• 避免过度索引，索引会增加写入开销

执行计划

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

EXPLAIN ANALYZE SELECT u.username, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

数据库设计

良好的数据库设计是构建高性能应用的基础。

范式

• 第一范式(1NF)：列不可再分
• 第二范式(2NF)：非主属性完全依赖于主键
• 第三范式(3NF)：非主属性不依赖于其他非主属性

常用设计模式

• 一对一关系：用户表和用户详情表
• 一对多关系：用户表和订单表
• 多对多关系：学生表和课程表（需中间表）

数据库设计示例

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    category_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    parent_id INT DEFAULT NULL,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

事务与锁

事务确保数据库操作的原子性、一致性、隔离性和持久性。

事务

START TRANSACTION;

UPDATE products SET stock = stock - 1 WHERE id = 1;

INSERT INTO orders (user_id, total_amount) VALUES (1, 99.99);

INSERT INTO order_items (order_id, product_id, quantity, price) 
VALUES (LAST_INSERT_ID(), 1, 1, 99.99);

COMMIT;

ROLLBACK;

隔离级别

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

总结

MySQL是一款功能强大的关系型数据库，掌握SQL查询、索引优化和数据库设计是后端开发的必备技能。合理使用索引和事务可以显著提升应用性能和数据一致性。