learnNote

主题

MySQL 学习计划 ​

📋 学习目标 ​

系统掌握 MySQL 数据库的核心概念、SQL 语法、性能优化与架构原理，能够独立完成数据库的设计、开发与运维。

第一阶段：基础入门（第 1 周） ​

1.1 MySQL 核心概念 ​

  • [ ] 什么是关系型数据库？MySQL 的定位与特点
  • [ ] MySQL 架构概览：连接层、服务层、引擎层、存储层
  • [ ] 核心概念：数据库（Database）、表（Table）、行（Row）、列（Column）
  • [ ] MySQL 客户端与服务端（C/S 架构）
  • [ ] 字符集与排序规则（utf8mb4、collation）

1.2 环境安装与配置 ​

  • [ ] Linux 下安装 MySQL 8.0（APT / YUM / Docker）
  • [ ] MySQL 配置文件 my.cnf 详解
  • [ ] 初始化与安全配置：mysql_secure_installation
  • [ ] 连接 MySQL：mysql CLI、MySQL Workbench、DBeaver
  • [ ] 用户管理与权限：CREATE USER、GRANT、REVOKE

1.3 基础 SQL 操作 ​

  • [ ] 数据库操作：CREATE DATABASE、USE、SHOW DATABASES、DROP DATABASE
  • [ ] 数据类型：整数、浮点、字符串、日期时间、JSON
  • [ ] 表操作：CREATE TABLE、ALTER TABLE、DROP TABLE、DESCRIBE
  • [ ] 数据操作（CRUD）：INSERT、SELECT、UPDATE、DELETE
  • [ ] 条件查询：WHERE、AND/OR、IN、BETWEEN、LIKE、IS NULL
  • [ ] 排序与分页：ORDER BY、LIMIT、OFFSET

练习任务：

创建一个图书管理数据库，包含 books、authors、categories 表，完成基本的增删改查操作。

第二阶段：SQL 进阶（第 2 周） ​

2.1 聚合与分组 ​

  • [ ] 聚合函数：COUNT、SUM、AVG、MAX、MIN
  • [ ] 分组查询：GROUP BY、HAVING
  • [ ] 去重：DISTINCT
  • [ ] 聚合与 NULL 值的处理

2.2 多表查询（JOIN） ​

  • [ ] 内连接：INNER JOIN
  • [ ] 左/右外连接：LEFT JOIN、RIGHT JOIN
  • [ ] 自连接与交叉连接
  • [ ] 多表连接的执行顺序与优化
  • [ ] UNION 与 UNION ALL

2.3 子查询 ​

  • [ ] 标量子查询、行子查询、列子查询、表子查询
  • [ ] 相关子查询 vs 非相关子查询
  • [ ] EXISTS 与 NOT EXISTS
  • [ ] IN vs EXISTS 的性能差异
  • [ ] 子查询改写为 JOIN 的技巧

2.4 常用函数 ​

  • [ ] 字符串函数：CONCAT、SUBSTRING、TRIM、LENGTH、REPLACE
  • [ ] 数值函数：ROUND、CEIL、FLOOR、MOD、ABS
  • [ ] 日期函数：NOW、DATE_FORMAT、DATEDIFF、DATE_ADD
  • [ ] 流程控制：IF、CASE WHEN、IFNULL、COALESCE
  • [ ] 窗口函数（MySQL 8.0+）：ROW_NUMBER、RANK、DENSE_RANK、LAG、LEAD

练习任务：

  1. 使用 JOIN 查询每个作者出版的书籍数量及总价格
  2. 使用窗口函数对书籍按价格排名，并找出每个分类中最贵的书

第三阶段：索引与性能优化（第 3-4 周） ​

3.1 索引基础 ​

  • [ ] 什么是索引？索引的底层数据结构（B+ 树、Hash）
  • [ ] 索引类型：主键索引、唯一索引、普通索引、全文索引、前缀索引
  • [ ] 聚簇索引 vs 非聚簇索引（InnoDB 的索引组织表）
  • [ ] 复合索引与最左前缀原则
  • [ ] 覆盖索引与索引下推（ICP）

3.2 EXPLAIN 执行计划 ​

  • [ ] EXPLAIN 各字段详解：type、key、rows、Extra
  • [ ] 执行计划的 type 等级：const > eq_ref > ref > range > index > ALL
  • [ ] 慢查询日志的开启与分析
  • [ ] SHOW PROFILE 与 Performance Schema

3.3 SQL 优化技巧 ​

  • [ ] 索引失效的常见场景（函数、类型转换、OR、!=）
  • [ ] 大表分页优化：延迟关联、游标分页
  • [ ] COUNT(*) 性能分析
  • [ ] ORDER BY 与 GROUP BY 优化
  • [ ] 批量插入与批量更新优化
  • [ ] 避免 SELECT *，只查需要的列

练习任务：

  1. 创建一个百万级数据的测试表，对比有/无索引的查询速度
  2. 使用 EXPLAIN 分析慢查询，通过添加合适的索引优化到毫秒级

第四阶段：事务与锁（第 5 周） ​

4.1 事务基础 ​

  • [ ] 什么是事务？ACID 特性详解
  • [ ] 事务控制：BEGIN、COMMIT、ROLLBACK、SAVEPOINT
  • [ ] 自动提交模式：autocommit
  • [ ] 并发问题：脏读、不可重复读、幻读
  • [ ] 事务隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE
  • [ ] MySQL 默认隔离级别与 MVCC 实现原理

4.2 锁机制 ​

  • [ ] 全局锁、表级锁、行级锁
  • [ ] InnoDB 锁类型：共享锁（S）、排他锁（X）、意向锁（IS/IX）
  • [ ] 记录锁、间隙锁、临键锁（Next-Key Lock）
  • [ ] 死锁的产生与排查：SHOW ENGINE INNODB STATUS
  • [ ] 乐观锁 vs 悲观锁（应用层实现）

4.3 MVCC 多版本并发控制 ​

  • [ ] Undo Log 与版本链
  • [ ] Read View 的创建规则
  • [ ] 当前读 vs 快照读
  • [ ] MVCC 如何解决不可重复读和幻读

练习任务：

  1. 开启两个终端模拟并发事务，观察不同隔离级别下的读取行为
  2. 模拟死锁场景并使用 SHOW ENGINE INNODB STATUS 排查

第五阶段：MySQL 架构与存储引擎（第 6 周） ​

5.1 MySQL 整体架构 ​

  • [ ] 连接管理与线程模型
  • [ ] SQL 执行流程：连接器 → 查询缓存（8.0 已移除）→ 解析器 → 优化器 → 执行器
  • [ ] InnoDB 内存结构：Buffer Pool、Change Buffer、Log Buffer
  • [ ] InnoDB 磁盘结构：表空间、数据字典、Redo Log、Undo Log

5.2 存储引擎对比 ​

  • [ ] InnoDB vs MyISAM 的区别与选择
  • [ ] InnoDB 特性：行级锁、事务支持、外键、MVCC
  • [ ] MEMORY、CSV、ARCHIVE 等引擎的适用场景
  • [ ] 如何选择存储引擎

5.3 日志系统 ​

  • [ ] Redo Log（重做日志）— 保证持久性
  • [ ] Undo Log（回滚日志）— 保证原子性和 MVCC
  • [ ] Binlog（二进制日志）— 主从复制和数据恢复
  • [ ] Redo Log 与 Binlog 的两阶段提交
  • [ ] 慢查询日志与通用查询日志

5.4 数据库设计 ​

  • [ ] 范式理论：1NF、2NF、3NF、BCNF
  • [ ] 反范式设计与冗余字段
  • [ ] E-R 模型与数据库建模
  • [ ] 命名规范与字段类型选择最佳实践

练习任务：

  1. 使用 SHOW VARIABLES 和 SHOW STATUS 查看 MySQL 运行状态
  2. 设计一个电商数据库（用户、商品、订单、支付），完成 E-R 图和建表 SQL

第六阶段：运维与进阶（第 7-8 周，可选） ​

6.1 备份与恢复 ​

  • [ ] 逻辑备份：mysqldump、mysqlpump
  • [ ] 物理备份：xtrabackup（Percona）
  • [ ] 基于 Binlog 的增量恢复与时间点恢复
  • [ ] 备份策略：全量 + 增量 + 定时任务

6.2 主从复制 ​

  • [ ] 主从复制原理：Binlog → Relay Log → SQL Thread
  • [ ] 异步复制、半同步复制、组复制（Group Replication）
  • [ ] 读写分离方案
  • [ ] 主从延迟的原因与解决

6.3 高可用方案 ​

  • [ ] MHA（Master High Availability）
  • [ ] MySQL InnoDB Cluster
  • [ ] ProxySQL / MySQL Router 中间件
  • [ ] 分库分表基础概念（ShardingSphere）

📚 推荐学习资源 ​

官方文档 ​

书籍 ​

  • 《高性能 MySQL》（第4版）— Baron Schwartz 等
  • 《MySQL 是怎样运行的：从根儿上理解 MySQL》— 小孩子4919
  • 《MySQL 技术内幕：InnoDB 存储引擎》— 姜承尧

在线资源 ​

实用工具 ​

  • MySQL Workbench — 官方 GUI 管理工具
  • DBeaver — 通用数据库管理工具（开源）
  • pt-query-digest — Percona 慢查询分析工具
  • Navicat — 商业数据库管理工具

⏱ 学习时间建议 ​

阶段内容建议时间重点
第一阶段基础入门1 周理解核心概念，掌握 CRUD 操作
第二阶段SQL 进阶1 周JOIN 多表查询、子查询、窗口函数
第三阶段索引与优化2 周B+ 树原理、EXPLAIN、SQL 调优
第四阶段事务与锁1 周ACID、隔离级别、MVCC、死锁排查
第五阶段架构与引擎1 周InnoDB 架构、日志系统、数据库设计
第六阶段运维进阶（可选）2 周备份恢复、主从复制、高可用

💡 建议：每天投入 1-2 小时，SQL 语法以刷题为主（LeetCode 数据库题），原理部分结合源码和实验理解。每完成一个阶段后，做一个小项目来巩固所学内容。