MySQL系统化学习

学习 MySQL 是掌握现代 Web 开发、数据分析和后端服务的关键技能。以下是系统化的学习路径，从基础安装到高级优化，帮助你成为高效的 MySQL 使用者。

一、MySQL 基础入门

1. 安装与配置

1-1：安装方式

Windows：使用 MySQL Installer（包含图形化工具）

Linux：

# Ubuntu/Debian
sudo apt install mysql-server
sudo systemctl start mysql

# CentOS/RHEL
sudo yum install mysql-server
sudo systemctl start mysqld

Mac：brew install mysql

1-2：安全初始化

sudo mysql_secure_installation
# 设置root密码、移除匿名用户等

2. 基础操作

连接MySQL

mysql -u root -p

基本命令

-- 显示所有数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE mydb;

-- 使用数据库
USE mydb;

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入数据
INSERT INTO users (username, email) 
VALUES ('john_doe', 'john@example.com');

-- 查询数据
SELECT * FROM users WHERE id = 1;

-- 更新数据
UPDATE users SET email = 'new@example.com' WHERE id = 1;

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

二、SQL 核心语法

1. 查询进阶

-- 条件查询
SELECT * FROM products WHERE price > 100 AND stock > 0;

-- 排序
SELECT * FROM users ORDER BY created_at DESC;

-- 分页
SELECT * FROM products LIMIT 10 OFFSET 20;  -- 第3页，每页10条

-- 聚合函数
SELECT 
    COUNT(*) as total_users,
    AVG(age) as avg_age
FROM users;

-- 分组统计
SELECT 
    department, 
    COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

2. 表关系与连接

-- 一对多关系
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 多表连接查询
SELECT 
    u.username, 
    o.order_date, 
    o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'USA';

-- 多对多关系（通过中间表）
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

三、数据库设计与优化

1. 索引优化

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

-- 复合索引
CREATE INDEX idx_name_age ON employees(last_name, age);

-- 查看索引使用情况
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

2. 数据类型选择

数据类型	使用场景
INT	整数ID、年龄等
VARCHAR(n)	变长字符串（用户名、地址）
TEXT	长文本（文章内容）
DECIMAL(m,n)	精确小数（金额）
DATETIME	日期时间（精确到秒）
BOOLEAN	使用TINYINT(1)替代

3. 范式与反范式

第三范式（3NF）：消除传递依赖
反范式设计：适当冗余提升查询性能（如订单表存储用户名）

四、高级特性

1. 存储过程与函数

DELIMITER //
CREATE PROCEDURE get_employee_count(IN dept VARCHAR(50), OUT count INT)
BEGIN
    SELECT COUNT(*) INTO count FROM employees WHERE department = dept;
END //
DELIMITER ;

-- 调用
CALL get_employee_count('Sales', @count);
SELECT @count;

2. 触发器

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON products
FOR EACH ROW
SET NEW.updated_at = NOW();

3. 事务控制

START TRANSACTION;
INSERT INTO orders (user_id, amount) VALUES (1, 99.99);
UPDATE accounts SET balance = balance - 99.99 WHERE user_id = 1;
COMMIT;  -- 或 ROLLBACK 回滚

五、性能调优

1. 配置优化

# my.cnf 关键配置
[mysqld]
innodb_buffer_pool_size = 4G  # 通常设为物理内存的70-80%
innodb_log_file_size = 256M
max_connections = 200
query_cache_size = 0  # MySQL 8.0已移除

2. 慢查询分析

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

-- 查看慢查询
SELECT * FROM mysql.slow_log;

3. 分库分表策略

垂直分表：将大字段拆分到单独表
水平分表：按时间/ID范围拆分（如users_2023）

六、安全与备份

1. 用户权限管理

-- 创建用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';

-- 授权
GRANT SELECT, INSERT ON mydb.* TO 'app_user'@'%';

-- 撤销权限
REVOKE DELETE ON mydb.* FROM 'app_user'@'%';

2. 数据备份与恢复

# 逻辑备份（导出SQL）
mysqldump -u root -p mydb > backup.sql

# 物理备份（InnoDB）
# 使用Percona XtraBackup或MySQL Enterprise Backup

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

七、学习资源推荐

官方文档

MySQL 8.0官方文档

书籍

《高性能MySQL》（第4版）
《MySQL技术内幕：InnoDB存储引擎》

在线练习

八、学习建议

从命令行开始：先掌握mysql命令行工具
理解执行计划：学会使用EXPLAIN分析查询
动手实验：在测试环境尝试索引、事务等特性
监控生产环境：使用Percona PMM或MySQL Enterprise Monitor

通过系统学习+实战练习，2-3个月可掌握MySQL核心技能，6个月左右能处理复杂优化场景。坚持在真实项目中实践，你会成为数据库领域的专家！ 💪