MySQL 知识体系导图

一、MySQL基础认知（入门要点）

1. MySQL是什么

基于关系型数据库（RDBMS），开源、轻量、高性能，适配前端对接后端的各类数据存储场景
核心特性：支持 SQL、ACID 事务、索引、多存储引擎、跨平台，是 Web 与全栈开发中最常用的关系库之一
典型场景：接口落库、用户与权限、订单与日志等，与 NestJS / Express 等框架常见搭配
对比其他数据库（要点对照）：
- MySQL vs MongoDB：MySQL是关系型（结构化数据），MongoDB是非关系型（文档型，适合非结构化数据）
- MySQL vs PostgreSQL：PostgreSQL 在复杂 SQL、扩展类型等方面很强；MySQL 生态与云托管极成熟。选型看团队、运维与业务，无绝对优劣。
- MySQL vs SQLite：MySQL支持客户端/服务器模式（多用户访问），SQLite是文件型（单用户、轻量，适合本地存储）

2. MySQL核心优势（前端建议熟悉）

开源免费，部署成本低，生态完善，与前端常用后端框架（NestJS、Express）兼容性好
高性能：支持索引、查询优化，能满足前端对接的中高并发场景（如用户登录、商品列表）
易用性强：SQL语法简洁，学习成本低，前端开发者可快速上手编写基础查询
支持事务和ACID，保证数据一致性（如用户注册、订单提交等核心场景）
多存储引擎可切换，适配不同业务场景（InnoDB、MyISAM等）

二、MySQL核心基础（实战必备）

1. 数据库基本概念

数据库（Database）：存储数据的容器，如user_db（用户数据库）
表（Table）：数据库中数据的组织形式，由行和列组成，如user表、order表
字段（Column）：表的列，对应数据的属性，如user表的id、name、age
行（Row）：表的记录，对应一条完整的数据，如一条用户信息
主键（Primary Key）：唯一标识表中一条记录，非空且唯一，如id（常用自增主键）
外键（Foreign Key）：关联两个表的字段，保证数据完整性，如order表的user_id关联user表的id
索引（Index）：加速查询的数据结构，可类比「目录」（提要）
视图（View）：虚拟表，由查询结果生成，简化复杂查询，不存储实际数据

2. 常用数据类型（对接接口时常用）

数值类型：
- INT：整数（如用户 id、年龄）；MySQL 8.0.17+ 起整数型的 (11) 显示宽度 已弃用含义，新建表写 INT 即可
- BIGINT：大整数（如雪花 id），避免溢出
- DECIMAL(M,D)：高精度小数（如金额，M总位数，D小数位数，如DECIMAL(10,2)）
字符串类型：
- VARCHAR(n)：可变长度字符串（如用户名、邮箱，n为最大长度，节省空间）
- CHAR(n)：固定长度字符串（如手机号、身份证号，长度固定，查询更快）
- TEXT：长文本（如文章内容、备注，长度不限）
日期时间类型：
- DATE：仅日期（如生日，格式 YYYY-MM-DD）
- DATETIME：日期+时间（YYYY-MM-DD HH:MM:SS），不做会话时区转换，表示范围大，业务上最常用
- TIMESTAMP：受 session time_zone 影响、内部以 UTC 存储，表示范围较窄；可配合 DEFAULT / ON UPDATE CURRENT_TIMESTAMP 做自动时间戳。与 DATETIME 勿混用同一语义，上线前约定时区与类型
其他类型：
- BOOLEAN：布尔别名，实际映射为 TINYINT(1)（0/1）
- ENUM：枚举（如性别：'male','female'，限制输入值）

3. 常用 SQL 语句（提要）

（1）DDL：数据定义语言（创建/修改/删除数据库、表）

创建数据库：CREATE DATABASE IF NOT EXISTS user_db DEFAULT CHARSET utf8mb4;
创建表：CREATE TABLE IF NOT EXISTS user (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, age INT, email VARCHAR(100) UNIQUE);
修改表：ALTER TABLE user ADD COLUMN phone VARCHAR(20);（添加字段）
删除表：DROP TABLE IF EXISTS user;
删除数据库：DROP DATABASE IF EXISTS user_db;

（2）DML：数据操作语言（INSERT / UPDATE / DELETE）

新增（INSERT）：INSERT INTO user (name, age, email) VALUES ('张三', 25, 'zhangsan@163.com');
删除（DELETE）：DELETE FROM user WHERE id = 1;（必须加WHERE，否则删除全表）
修改（UPDATE）：UPDATE user SET age = 26 WHERE id = 1;（必须加WHERE，否则更新全表）

（3）DQL：数据查询（SELECT 与聚合 / 连接）

基础查询：
- 全列：SELECT * FROM user;
- 条件：SELECT name, age FROM user WHERE age > 20;
- 排序：SELECT * FROM user ORDER BY age DESC;（DESC 降序，ASC 升序）
- 分页：SELECT * FROM user LIMIT 10 OFFSET 0;
- 去重：SELECT DISTINCT age FROM user;
聚合函数：COUNT（计数）、SUM（求和）、AVG（平均值）、MAX（最大值）、MIN（最小值）
- 示例：SELECT COUNT(*) FROM user;（查询用户总数）
- 示例：SELECT AVG(age) FROM user WHERE gender = 'male';（查询男性平均年龄）
GROUP BY 分组：按指定字段分组，配合聚合函数使用
- 示例：SELECT gender, COUNT(*) FROM user GROUP BY gender;（按性别分组统计人数）
HAVING 筛选：过滤分组后的结果（区别WHERE：WHERE过滤行，HAVING过滤分组）
- 示例：SELECT gender, COUNT(*) FROM user GROUP BY gender HAVING COUNT(*) > 10;（筛选人数大于10的性别分组）
多表连接（常见重点）：
- INNER JOIN（内连接）：只显示两表匹配的记录（最常用）
- LEFT JOIN（左连接）：显示左表所有记录，右表匹配不到显示NULL
- RIGHT JOIN（右连接）：显示右表所有记录，左表匹配不到显示NULL
- 示例：SELECT u.name, o.order_no FROM user u INNER JOIN `order` o ON u.id = o.user_id;（关联用户表和订单表，查询用户名和订单号）
子查询：嵌套在其他查询中的查询，分为相关子查询和非相关子查询
- 示例：SELECT * FROM user WHERE age > (SELECT AVG(age) FROM user);（查询年龄大于平均年龄的用户）

（4）DCL：数据控制语言（权限提要）

MySQL 8+ 推荐：先 CREATE USER 再 GRANT，避免 GRANT … IDENTIFIED BY … 一体写法（老习惯、已不推荐）。
示例（建用户 + 授权）：
- CREATE USER \&\#39;test\&\#39;@\&\#39;localhost\&\#39; IDENTIFIED BY \&\#39;强随机密码\&\#39;;
- GRANT SELECT, INSERT ON user\_db\.user TO \&\#39;test\&\#39;@\&\#39;localhost\&\#39;;
- FLUSH PRIVILEGES;（8.x 多数场景下 GRANT 后已即时生效，保留此行多为兼容旧运维习惯）
撤销权限：REVOKE SELECT ON user\_db\.user FROM \&\#39;test\&\#39;@\&\#39;localhost\&\#39;;
修改密码：ALTER USER \&\#39;test\&\#39;@\&\#39;localhost\&\#39; IDENTIFIED BY \&\#39;新的强随机密码\&\#39;;
删除登录：DROP USER IF EXISTS \&\#39;test\&\#39;@\&\#39;localhost\&\#39;;（下线账号前先确认无会话依赖，并先 REVOKE 清权限更稳妥）

三、MySQL索引（进阶重点）

1. 索引是什么

定义：一种优化查询速度的数据结构（B+树为主），本质是“排好序的快速查找数据结构”
核心作用：减少磁盘I/O次数，避免全表扫描，提升查询效率（类比查字典找拼音目录）
缺点：占用额外存储空间，增删改操作会变慢（需要维护索引结构）

2. 索引类型（常见要点）

主键索引（Primary Key）：
- 唯一标识一条记录，非空且唯一，一张表只能有一个主键索引
- 默认自动创建，如id INT PRIMARY KEY AUTO_INCREMENT
唯一索引（Unique Index）：
- 索引列的值唯一；列允许 NULL 时，多行 NULL 往往可同时存在（与「业务上只能有一个未知」不是一回事，需在表设计或应用层约束）
- 适用场景：邮箱、手机号等不允许重复的字段，如CREATE UNIQUE INDEX idx_user_email ON user(email);
普通索引（Normal Index）：
- 最常用，无唯一性约束，仅用于提升查询速度
- 适用场景：频繁查询的字段（如name、age），如CREATE INDEX idx_user_name ON user(name);
联合索引（Composite Index）：
- 多个字段组合创建的索引，遵循“最左前缀原则”（常见坑点）
- 示例：CREATE INDEX idx_user_name_age ON user(name, age);（查询时必须先使用name字段，索引才生效）
- 适用场景：多字段联合查询（如WHERE name = '张三' AND age = 25）
全文索引（Fulltext Index）：
- 用于模糊查询长文本（如文章内容、备注），支持关键词匹配
- 示例：CREATE FULLTEXT INDEX idx_article_content ON article(content);

3. 索引底层原理（原理进阶）

MySQL默认索引结构：B+树（而非B树）
B+树核心特点：
- 叶子节点存储所有数据，非叶子节点只存储索引值（减少磁盘I/O）
- 叶子节点之间用链表连接，便于范围查询（如WHERE age BETWEEN 20 AND 30）
- 高度低（一般3-4层），即使数据量很大，查询也只需3-4次磁盘I/O
对比B树：B树叶子节点和非叶子节点都存储数据，查询效率低于B+树，不适合范围查询
哈希索引：适合等值查询（如WHERE id = 1），不支持范围查询、排序，MySQL默认不使用

4. 索引使用技巧 & 避坑点（要点对照）

索引生效条件：
- 避免使用函数操作索引列（如WHERE DATE(create_time) = '2024-01-01'，索引失效）
- 避免使用模糊查询前缀%（如WHERE name LIKE '%张三'，索引失效；LIKE '张三%'，索引生效）
- 避免使用 OR 连接差异过大的条件（一侧无索引时，优化器可能放弃好索引）；可改写为 UNION 或保证各分支都能走索引
- 联合索引遵循“最左前缀原则”，不满足则索引失效
不适合创建索引的场景：
- 数据量少的表（如小于1000条，全表扫描比索引查询更快）
- 频繁增删改的字段（维护索引成本高）
- 重复值多的字段（如性别，索引区分度低，优化效果差）
索引优化核心：优先给“频繁查询、区分度高”的字段创建索引，避免过度索引

四、MySQL事务（进阶重点）

1. 事务是什么

定义：一组不可分割的SQL操作，要么全部执行成功，要么全部执行失败（原子性）
前端关联场景：用户注册（创建用户+添加用户角色）、订单提交（扣减库存+创建订单）、转账等

2. 事务ACID特性（建议熟悉）

原子性（Atomicity）：事务是一个不可分割的整体，要么全做，要么全不做（如转账时，扣钱和加钱必须同时成功或同时失败）
一致性（Consistency）：事务执行前后，数据的完整性约束不变（如转账前A有100，B有50；转账后A有80，B有70，总金额不变）
隔离性（Isolation）：多个事务并发执行时，一个事务的执行不会影响其他事务（核心要点）
持久性（Durability）：事务执行成功后，数据会永久保存到数据库，即使数据库崩溃也不会丢失

3. 事务隔离级别（进阶要点）

MySQL默认隔离级别：REPEATABLE READ（可重复读）
四个隔离级别（从低到高，安全性递增，性能递减）：
- READ UNCOMMITTED（读未提交）：
  - 允许读取未提交的事务数据，会出现“脏读”（读取到无效数据）
  - 性能最高，安全性最低，几乎不用
- READ COMMITTED（读已提交）：
  - 只能读取已提交的事务数据，解决“脏读”，但会出现“不可重复读”（同一事务内，两次查询结果不一致）
  - 适用场景：大多数互联网应用（如电商、社交）
- REPEATABLE READ（可重复读）：
  - 同一事务内普通一致性读看到同一份快照，避免典型的不可重复读。
  - 幻读（别事务插入新行导致同条件二次查询行数变多）：InnoDB 对 当前读（SELECT … FOR UPDATE / LOCK IN SHARE MODE 等）用 间隙锁/临键锁 抑制插入；快照读与 MVCC 并不等价于「所有幻读语义都不存在」，关键业务仍要显式加锁或接受 SERIALIZABLE 的成本。
  - InnoDB 默认使用该级别。
- SERIALIZABLE（串行化）：
  - 事务串行执行，完全隔离，解决所有并发问题（脏读、不可重复读、幻读）
  - 性能最低，适合数据一致性要求极高的场景（如金融、支付）
并发问题总结：脏读 → 不可重复读 → 幻读（隔离级别越高，问题越少）

4. 事务操作（常用命令）

开启事务：START TRANSACTION; 或 BEGIN;
提交事务（执行成功）：COMMIT;（数据永久生效）
回滚事务（执行失败）：ROLLBACK;（恢复到事务开启前的状态）
示例（转账场景）：
- START TRANSACTION;
- UPDATE user SET balance = balance - 20 WHERE id = 1;（A扣钱）
- UPDATE user SET balance = balance + 20 WHERE id = 2;（B加钱）
- COMMIT;（全部成功，提交）
- -- 若出现错误，执行 ROLLBACK; 回滚

五、MySQL查询优化（实践要点）

1. 慢查询优化（常见场景）

识别慢查询：开启慢查询日志（slow_query_log = 1），设置慢查询阈值（long_query_time = 1，单位秒）
分析慢查询：使用 EXPLAIN 看执行计划；EXPLAIN ANALYZE（8.0.18+）会真实执行语句，适合测试环境，生产慎用。
- EXPLAIN SELECT * FROM user WHERE age > 20;（查看是否使用索引、是否全表扫描）
- 关键字段：type（查询类型，ALL=全表扫描，ref=索引查询，range=范围查询）、key（使用的索引）、rows（扫描的行数）
慢查询优化技巧：
- 给频繁查询的字段创建索引（避免全表扫描）
- 避免SELECT *，只查询需要的字段（减少数据传输）
- 优化JOIN查询，给关联字段创建索引（避免笛卡尔积）
- 优化分页查询，使用主键索引分页（如SELECT * FROM user WHERE id > 100 LIMIT 10; 比LIMIT 100,10更高效）

2. 其他优化技巧

分表分库（海量数据优化）：
- 水平分表：按数据范围/哈希分表（如user_1、user_2，按用户id分），解决单表数据量过大问题
- 垂直分表：按字段拆分（如user表拆分为user_basic（基础信息）、user_detail（详细信息）），减少表字段数量
缓存优化：将常见查询数据缓存到Redis（如用户信息、商品列表），减少MySQL查询压力
NULL 语义：表示「未知」，与空字符串、0 不同；是否允许列上为 NULL 应结合业务与索引（如 COUNT(*) vs COUNT(col)）权衡，不要一律用默认值替代。
批量操作：批量插入（INSERT INTO user VALUES (...), (...)）、批量更新，减少SQL执行次数
合理使用存储引擎：InnoDB（支持事务、索引，适合大多数场景）、MyISAM（不支持事务，适合只读场景）

六、MySQL存储引擎（基础重点）

1. 常用存储引擎对比（要点对照）

InnoDB（默认）：
- 支持事务、ACID特性、行级锁、外键、索引（B+树）
- 适用场景：需要事务、高并发的场景（如用户、订单、支付）
- 缺点：比MyISAM占用更多存储空间
MyISAM：
- 不支持事务、外键，支持表级锁、全文索引
- 适用场景：只读、常见查询、无事务需求的场景（如日志表、新闻表）
- 优点：查询速度快、占用空间小
其他存储引擎（了解）：
- Memory：内存存储，速度极快，数据重启丢失，适合临时表
- CSV：以CSV格式存储，适合数据导入导出

2. 存储引擎选择原则

需要事务、高并发 → InnoDB
只读、常见查询、无事务 → MyISAM
临时数据、高速查询 → Memory

七、MySQL常见难点&坑点（进阶辨析）

1. 主键索引和唯一索引的区别？
- 主键索引非空且唯一，一张表只能有一个；唯一索引列可声明为可空（与主键不同），一张表可有多个唯一索引
- 主键索引默认是聚簇索引，唯一索引是非聚簇索引
2. 聚簇索引和非聚簇索引的区别？
- 聚簇索引：索引和数据存储在一起，叶子节点存储数据（如主键索引），查询速度快
- 非聚簇索引：索引和数据分开存储，叶子节点存储索引值和主键ID，查询需回表（二次查询）
3. 为什么MySQL默认用B+树作为索引结构？
- B+树高度低，磁盘I/O次数少，查询效率高
- 叶子节点链表连接，支持范围查询
- 非叶子节点只存索引值，节省存储空间
4. 事务隔离级别里，幻读在 InnoDB RR 下怎么处理？
- 快照读：MVCC 提供稳定读视图，避免多数「同读不一致」。
- 当前读：依赖行锁 + 间隙锁/临键锁防止并发插入「幽灵行」；仍要在业务里选对语句与索引，避免长事务放大锁范围。
5. 索引失效的常见场景有哪些？（参见上文「索引避坑」）
6. 分表分库的区别？什么时候需要分表分库？
- 分表：单库多表，解决单表数据量过大问题；分库：多库多表，解决单库压力过大问题
- 无固定行数阈值：以慢查询、磁盘与监控为准；单表千万级仍可能很快，小表也可能因锁与全表扫描拖垮。
7. 行级锁和表级锁的区别？
- 行级锁：锁定单条记录，并发度高（InnoDB支持），适合写操作多的场景
- 表级锁：锁定整个表，并发度低（MyISAM支持），适合读操作多的场景
8. MVCC是什么？作用是什么？
- MVCC（多版本并发控制）：为读提供一致快照，减少读写互斥。
- 与隔离级别配合：缓解不可重复读的典型表现；幻读相关语义还要结合当前读与 InnoDB 锁机制理解，不能简化为「MVCC 消灭一切幻读」。

MySQL 知识体系导图 ​

一、MySQL基础认知（入门要点） ​

1. MySQL是什么 ​

2. MySQL核心优势（前端建议熟悉） ​

二、MySQL核心基础（实战必备） ​

1. 数据库基本概念 ​

2. 常用数据类型（对接接口时常用） ​

3. 常用 SQL 语句（提要） ​

（1）DDL：数据定义语言（创建/修改/删除数据库、表） ​

（2）DML：数据操作语言（INSERT / UPDATE / DELETE） ​

（3）DQL：数据查询（SELECT 与聚合 / 连接） ​

（4）DCL：数据控制语言（权限提要） ​

三、MySQL索引（进阶重点） ​

1. 索引是什么 ​

2. 索引类型（常见要点） ​

3. 索引底层原理（原理进阶） ​

4. 索引使用技巧 &amp; 避坑点（要点对照） ​

四、MySQL事务（进阶重点） ​

1. 事务是什么 ​

2. 事务ACID特性（建议熟悉） ​

3. 事务隔离级别（进阶要点） ​

4. 事务操作（常用命令） ​

五、MySQL查询优化（实践要点） ​

1. 慢查询优化（常见场景） ​

2. 其他优化技巧 ​

六、MySQL存储引擎（基础重点） ​

1. 常用存储引擎对比（要点对照） ​

2. 存储引擎选择原则 ​

七、MySQL常见难点&amp;坑点（进阶辨析） ​

MySQL 知识体系导图

一、MySQL基础认知（入门要点）

1. MySQL是什么

2. MySQL核心优势（前端建议熟悉）

二、MySQL核心基础（实战必备）

1. 数据库基本概念

2. 常用数据类型（对接接口时常用）

3. 常用 SQL 语句（提要）

（1）DDL：数据定义语言（创建/修改/删除数据库、表）

（2）DML：数据操作语言（INSERT / UPDATE / DELETE）

（3）DQL：数据查询（SELECT 与聚合 / 连接）

（4）DCL：数据控制语言（权限提要）

三、MySQL索引（进阶重点）

1. 索引是什么

2. 索引类型（常见要点）

3. 索引底层原理（原理进阶）

4. 索引使用技巧 & 避坑点（要点对照）

四、MySQL事务（进阶重点）

1. 事务是什么

2. 事务ACID特性（建议熟悉）

3. 事务隔离级别（进阶要点）

4. 事务操作（常用命令）

五、MySQL查询优化（实践要点）

1. 慢查询优化（常见场景）

2. 其他优化技巧

六、MySQL存储引擎（基础重点）

1. 常用存储引擎对比（要点对照）

2. 存储引擎选择原则

七、MySQL常见难点&坑点（进阶辨析）