前言：为什么选择 MySQL？

MySQL 作为开源关系型数据库的标杆，凭借轻量、高效、稳定的特性，广泛应用于 Web 开发、大数据存储等场景。本文从零基础入门到高阶优化，整理了系统的学习笔记，适合编程初学者、后端开发工程师快速掌握 MySQL 核心技能。

---

一、MySQL 基础入门（必掌握）

1. 环境搭建与连接

• 安装方式：

• • 本地安装：Windows（官网 MSI 包）、Linux（yum/apt 命令）

• • 容器部署：docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:8.0

• 连接工具：

• • 命令行：mysql -u root -p -h 127.0.0.1 -P 3306

• • 可视化：Navicat、DataGrip、MySQL Workbench

2. 数据库与表操作（DDL）

-- 1. 数据库操作

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

USE test_db; -- 切换数据库

DROP DATABASE IF EXISTS test_db;

-- 2. 表结构操作

CREATE TABLE user (

id INT PRIMARY KEY AUTO_INCREMENT, -- 自增主键

username VARCHAR(50) NOT NULL UNIQUE, -- 唯一非空

age TINYINT UNSIGNED DEFAULT 18, -- 无符号默认值

create_time DATETIME DEFAULT CURRENT_TIMESTAMP -- 自动填充时间

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 修改表结构

ALTER TABLE user ADD COLUMN email VARCHAR(100) AFTER age;

ALTER TABLE user MODIFY COLUMN age INT UNSIGNED;

ALTER TABLE user DROP COLUMN email;

3. 数据增删改查（DML）

-- 插入数据

INSERT INTO user (username, age) VALUES ('zhangsan', 20), ('lisi', 22);

-- 查询数据（重点）

SELECT username, age FROM user WHERE age > 20 ORDER BY age DESC LIMIT 10;

SELECT * FROM user GROUP BY age HAVING COUNT(*) > 1; -- 分组筛选

-- 更新与删除

UPDATE user SET age = 23 WHERE username = 'zhangsan';

DELETE FROM user WHERE age 18;

4. 约束与索引基础

• 约束类型：PRIMARY KEY（主键）、UNIQUE（唯一）、NOT NULL（非空）、FOREIGN KEY（外键）

• 索引创建：

CREATE INDEX idx_username ON user(username); -- 普通索引

CREATE UNIQUE INDEX idx_email ON user(email); -- 唯一索引

---

二、MySQL 进阶核心（提升效率）

1. 复杂查询技巧

• 多表连接：

-- 内连接（取交集）

SELECT u.username, o.order_no FROM user u INNER JOIN order o ON u.id = o.user_id;

-- 左连接（保留左表全部）

SELECT u.username, o.order_no FROM user u LEFT JOIN order o ON u.id = o.user_id;

• 子查询与关联查询：

-- 子查询

SELECT * FROM user WHERE id IN (SELECT user_id FROM order WHERE amount > 100);

-- 关联子查询（逐行匹配）

SELECT * FROM user u WHERE EXISTS (SELECT 1 FROM order o WHERE o.user_id = u.id);

2. 索引优化（性能关键）

• 索引类型：

• • 聚簇索引：InnoDB 默认主键索引，数据与索引存储在一起

• • 非聚簇索引：普通索引，存储主键地址

• 优化原则：

• • 避免索引失效：WHERE age + 1 = 20（函数操作）、WHERE username LIKE '%san'（前缀模糊）

• • 联合索引最左前缀原则：CREATE INDEX idx_age_name ON user(age, username)，优先匹配 age

• 索引分析：EXPLAIN SELECT * FROM user WHERE age = 20;（查看执行计划）

3. 事务与 ACID 特性

• 事务操作：

BEGIN; -- 开启事务

UPDATE user SET balance = balance - 100 WHERE id = 1;

UPDATE order SET status = 1 WHERE user_id = 1;

COMMIT; -- 提交事务

-- ROLLBACK; -- 回滚（异常时）

• ACID 特性：

• • 原子性（Atomicity）：要么全执行，要么全回滚

• • 一致性（Consistency）：事务前后数据完整性不变

• • 隔离性（Isolation）：多事务并发互不干扰

• • 持久性（Durability）：事务提交后数据永久存储

4. 并发控制与隔离级别

• 隔离级别（从低到高）：

• • 读未提交（Read Uncommitted）：可能出现脏读

• • 读已提交（Read Committed）：避免脏读，可能出现不可重复读（MySQL 默认）

• • 可重复读（Repeatable Read）：避免不可重复读，可能出现幻读（InnoDB 通过 MVCC 解决）

• • 串行化（Serializable）：完全隔离，性能最低

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

---

三、高阶实战技巧（生产环境必备）

1. 数据库设计规范

• 三大范式：

• • 1NF：字段原子性（不可拆分）

• • 2NF：消除部分依赖（依赖主键全部字段）

• • 3NF：消除传递依赖（不依赖非主键字段）

• 反范式设计：高频查询字段冗余（如订单表存储用户名，避免联表）

2. 性能优化实战

• SQL 优化：

• • 避免SELECT *，只查询需要字段

• • 大表分页优化：SELECT * FROM user WHERE id > 1000 LIMIT 10;（避免全表扫描）

• 配置优化：

• • 调整连接数：max_connections = 1000（my.cnf）

• • 缓存优化：query_cache_size = 64M（查询缓存，MySQL 8.0 已移除）

3. 备份与恢复

• 逻辑备份（mysqldump）：

mysqldump -u root -p test_db > test_db_backup.sql -- 备份

mysql -u root -p test_db _db_backup.sql -- 恢复

• 物理备份：XtraBackup（适合大数据库，支持增量备份）

4. 分库分表与读写分离

• 分库分表：

• • 水平分表：按用户 ID 哈希（user_1, user_2...）

• • 垂直分表：拆分大字段（如用户表拆分出 user_profile 表）

• 读写分离：

• • 主库（Master）：写操作

• • 从库（Slave）：读操作（通过主从复制同步数据）

---

四、常见问题与避坑指南

1. 中文乱码：确保数据库、表、字段字符集均为 utf8mb4（支持 emoji）

1. 主键自增冲突：分库分表时使用雪花算法生成全局唯一 ID

1. 死锁：避免长事务，合理设计索引，减少锁竞争

1. 慢查询：开启慢查询日志（slow_query_log = ON），分析slow.log优化 SQL

---

总结

MySQL 学习需注重 “基础扎实 + 实战优化”，基础部分重点掌握 SQL 语法、约束与索引；进阶部分聚焦查询优化、事务隔离、性能调优；高阶部分则需结合生产环境场景，理解分库分表、读写分离等架构设计。建议多动手实践，通过EXPLAIN分析执行计划，积累优化经验，逐步从 “会用” 到 “精通”。