数据库/协同工具类型

MySQL 深度指南

MySQL 的作用定位、何时该用、核心机制（InnoDB/索引/事务/锁）、常见问题排查（慢查询、死锁、乱码、连接数满、主从延迟）、难点解决方案（深分页、并发锁、分库分表、读写分离）与生产要点。

2026年7月19日

MySQL
数据库

这是 MySQL 的完整文档：先从零讲清关系型数据库和 SQL，再带你把 MySQL 跑起来、敲通增删改查，最后是深度参考（作用定位、常见问题排查、难点方案）。在 Nest 应用里用 TypeORM/Prisma 连 MySQL 见 Nest 课程第七章。

一、基础入门（从零开始）

这一节从「什么是数据库」讲到能自己敲 SQL 增删改查，零基础也能跟上；Nest 课程那篇讲的是在应用里用 TypeORM/Prisma 连——两篇不重复。

1. 先搞懂：什么是关系型数据库

数据库（Database）就是按结构存数据的仓库。关系型数据库（RDBMS）把数据存成一张张表，就像 Excel 表格：

id	name	age	email
1	张三	20	zhang@xx.com
2	李四	25	lisi@xx.com

几个最核心的概念（菜鸟教程）：

表（table）：数据的集合，相当于一个 Excel 表。
列（column / 字段）：每一列是一种信息，如 id、name、age。
行（row / 记录）：每一行是一条具体数据，如「张三那条」。
主键（Primary Key）：唯一标识一行的列（如 id），不重复、不为空。
外键（Foreign Key）：关联另一张表的列（如订单表的 user_id 关联用户表）。
索引（index）：加速查询的结构，类似书的目录。
SQL：操作关系数据库的标准语言（下一节讲）。

「关系型」= 数据存在相互关联的多张表里（用户表、订单表、商品表），靠主键/外键/JOIN 连起来，而不是全塞一个大表。

2. SQL 是什么、分哪几类

SQL（Structured Query Language） 是操作关系数据库的标准语言——无论你用 Java、Python 还是 Node 写后端，要存取数据都得通过 SQL（廖雪峰：现代程序离不开关系数据库，要用就必须掌握 SQL）。

按功能分四类，记住这个分类，后面学 SQL 就不会乱：

分类	全称	干什么	代表语句
DDL	数据定义	建表/改结构	`CREATE` / `ALTER` / `DROP`
DML	数据操作	增删改数据	`INSERT` / `UPDATE` / `DELETE`
DQL	数据查询	查数据	`SELECT`
DCL	数据控制	权限控制	`GRANT` / `REVOKE`

日常开发 90% 是 DML + DQL（增删改查）。接下来先把 MySQL 跑起来，再用这四类 SQL 敲一遍。

3. 用 Docker 起一个 MySQL

docker run -d \
  --name mysql \
  -e MYSQL_ROOT_PASSWORD=root \
  -p 3306:3306 \
  mysql:8

-e MYSQL_ROOT_PASSWORD=root：设 root 密码（必填，不设启动会失败）。
-p 3306:3306：把容器端口映射到本机，应用用 localhost:3306 连。
docker logs -f mysql 看到 ready for connections 就算起来了。

4. 连上它

docker exec -it mysql mysql -uroot -proot

看到 mysql> 提示符就连上了。本机装了 mysql 客户端也可以直接 mysql -h 127.0.0.1 -uroot -proot。

5. 最基础的几条命令

mysql> SHOW DATABASES;                -- 看有哪些库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
mysql> CREATE DATABASE demo;          -- 建库
Query OK, 1 row affected (0.00 sec)
mysql> USE demo;                      -- 切到这个库
Database changed

mysql> CREATE TABLE users (           -- 建表
    ->   id BIGINT PRIMARY KEY AUTO_INCREMENT,
    ->   name VARCHAR(50),
    ->   age INT
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO users (name, age) VALUES ('张三', 20), ('李四', 25);
Query OK, 2 rows affected (0.00 sec)          -- 插了 2 条

mysql> SELECT * FROM users;           -- 查全部
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   20 |
|  2 | 李四   |   25 |
+----+--------+------+

mysql> SELECT * FROM users WHERE age > 22;     -- 条件查
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | 李四   |   25 |
+----+--------+------+

mysql> UPDATE users SET age = 21 WHERE name = '张三';   -- 改
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM users WHERE name = '李四';           -- 删
Query OK, 1 row affected (0.00 sec)

记住 CRUD 四件套：INSERT / SELECT / UPDATE / DELETE，外加 CREATE 建表。

6. 数据类型

建表时每个字段都要选类型，常用的分四类（掘金《重学 MySQL 系列(三)》整理）：

数值

类型	字节	用途
`TINYINT`	1	小整数、状态码（0/1）、枚举值
`INT`	4	最常用的整数（计数、外键）
`BIGINT`	8	大整数（自增主键、雪花 id）
`DECIMAL(M,D)`	可变	精确小数，存钱必用（M 总位数、D 小数位）
`FLOAT`/`DOUBLE`	4/8	近似小数，有精度损失，少用

加 UNSIGNED 表示无符号（不允许负数，正数上限翻倍，如 TINYINT UNSIGNED 是 0~255）。钱的场景千万别用 FLOAT/DOUBLE，用 DECIMAL(10,2)。

字符串

类型	用途
`CHAR(n)`	定长，n 个字符，不足补空格（少用）
`VARCHAR(n)`	变长，最常用，n 是最大字符数
`TEXT`/`LONGTEXT`	长文本（文章正文），不能设默认值
`ENUM('a','b')`	枚举，只能取预定义值
`JSON`	5.7+ 原生 JSON，能按字段查（`->`、`->>`）

日期时间

类型	字节	用途
`DATE`	3	日期 `2026-07-19`
`TIME`	3	时间 `12:30:00`
`DATETIME`	8	日期+时间，最常用，范围到 9999 年
`TIMESTAMP`	4	时间戳，带时区、省空间，但只到 2038 年
`YEAR`	1	只存年份

7. 约束与常用 SQL

建表约束（字段上的规则）：

PRIMARY KEY：主键，唯一且非空。
AUTO_INCREMENT：自增（只用于整数主键）。
NOT NULL：非空；DEFAULT：默认值。
UNIQUE：唯一（如手机号）；FOREIGN KEY：外键。

完整建表示例：

CREATE TABLE article (
  id         BIGINT PRIMARY KEY AUTO_INCREMENT,
  title      VARCHAR(100) NOT NULL,
  author_id  BIGINT NOT NULL,
  price      DECIMAL(10,2) DEFAULT 0,
  status     TINYINT DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_title (title)
) CHARACTER SET utf8mb4;

SQL 按功能分四类：

-- DDL（定义结构）
ALTER TABLE article ADD COLUMN summary VARCHAR(200);   -- 加列
ALTER TABLE article MODIFY COLUMN title VARCHAR(200);  -- 改类型
DROP TABLE article;                                    -- 删表

-- DML（操作数据）
INSERT INTO article (title, author_id) VALUES ('第一篇', 1), ('第二篇', 1);
UPDATE article SET status = 0 WHERE author_id = 1;      -- WHERE 必带！
DELETE FROM article WHERE id = 5;

-- DQL（查询）
SELECT title, price FROM article
WHERE price > 50 AND status = 1
ORDER BY created_at DESC
LIMIT 10;

-- 聚合 + 分组（HAVING 过滤分组后的结果）
SELECT author_id, COUNT(*) AS cnt, AVG(price) AS avg_price
FROM article
GROUP BY author_id
HAVING cnt > 2;

-- 多表关联
SELECT a.title, u.name
FROM article a JOIN user u ON a.author_id = u.id;

WHERE 常用条件：= != > < BETWEEN ... AND ... IN (...) LIKE '%词%' IS NULL，以及 AND / OR / NOT。 聚合函数：COUNT SUM AVG MAX MIN。

8. 基本使用规则

统一字符集 utf8mb4：建库建表都加，否则中文/emoji 乱码。
钱用 DECIMAL，绝不用 FLOAT/DOUBLE（浮点有精度误差）。
VARCHAR 长度够用就行，别动辄 VARCHAR(9999)。
时间默认 DATETIME；要用 TIMESTAMP 注意 2038 上限和时区。
选类型三原则（掘金总结）：更小的更好、越简单越好、尽量 NOT NULL 避免 NULL。
UPDATE/DELETE 永远带 WHERE，否则全表改/删（生产事故高发）。
代码里用占位符（?）防 SQL 注入，绝不字符串拼 SQL。

9. 核心词汇速记

术语	一句话
库（database）	表的容器，如 `demo`
表（table）	结构化数据的集合，如 `users`
行 / 字段	一条记录 / 一列（如 `name`）
主键（PK）	唯一标识一行，如 `id`
索引	加速查询的结构，类似书的目录
SQL	操作关系型数据库的语言

二、作用与定位

MySQL 是关系型数据库（RDBMS），核心价值：

结构化存储：表、字段、类型固定，schema 严格。
强事务（ACID）：多条 SQL 要么全成功要么全回滚，保证数据一致。
关系建模：外键、JOIN，适合实体间关系复杂的数据（订单-用户-商品）。
成熟稳定：生态完善、运维资料多、企业默认选择。

一句话：钱、订单、用户这种不能错、关系复杂的数据，放 MySQL。

三、何时该用 / 何时考虑别的

场景	选择
强事务、关系复杂、结构固定（交易、账户、ERP）	MySQL
数据结构多变、嵌套深（日志、动态属性、内容）	MongoDB
海量写入的时序/日志、极致扩展	分库分表或换 ClickHouse/TiDB
纯缓存、高频计数	Redis

别用 MySQL 当万能锤：单表数据量到千万级、写入并发极高时，MySQL 会成为瓶颈，需要分库分表或引入专用存储。

四、核心机制速览

存储引擎用 InnoDB（默认）：支持事务、行锁、外键。MyISAM 不支持事务，别用。
索引是 B+ 树：聚簇索引（主键）存整行数据，二级索引存「索引列 → 主键」，查到主键再回表。
事务隔离级别：默认 REPEATABLE READ（可重复读），用 MVCC 实现。四个级别从「读未提交」到「串行化」，一致性越强并发越弱。
锁：行锁（基于索引，没走索引会升级成表锁）、间隙锁（解决幻读）。

一张图看清一次 select 查询在 InnoDB 内部的流转（连接器 → 解析器 → 优化器 → 执行器 → Buffer Pool）：

用图理解四种事务隔离级别的递进关系（每升一级，就多解决一类读异常）：

flowchart LR A["读未提交 RU"] B["读已提交 RC"] C["可重复读 RR 默认"] D["串行化 Serializable"] A -->|"解决脏读"| B B -->|"解决不可重复读"| C C -->|"解决幻读"| D

五、常见问题与排查

1. 慢查询 / 索引失效

现象：某接口越来越慢，SQL 跑几秒。

排查：EXPLAIN 看执行计划，重点看 type（要 ref/range，不要 ALL 全表扫）、key（实际用的索引）、rows（扫描行数）。

EXPLAIN SELECT * FROM order WHERE user_id = 100;

索引失效的常见原因：

对索引列用函数/运算：WHERE YEAR(create_time) = 2024 → 改成范围 create_time >= '2024-01-01'。
隐式类型转换：列是 VARCHAR 但 WHERE phone = 13800000000（数字）→ 加引号 '13800000000'。
LIKE '%xxx' 左模糊走不了索引（'xxx%' 右模糊可以）。
OR 两边不全有索引 → 拆成 UNION 或确保都有索引。
联合索引没遵循最左前缀：索引 (a,b,c)，查 WHERE b=1 用不上。

2. 死锁

现象：日志报 Deadlock found when trying to get lock; try restarting transaction。

原因：两个事务互相等对方持有的行锁。

解决：

保持事务短小，尽快提交。
多个表/多行加锁时，所有事务按相同顺序加锁（比如都按 id 升序）。
MySQL 检测到死锁会自动回滚其中一个事务（默认 innodb_deadlock_detect=ON），业务里捕获后重试即可。

3. 中文乱码

根因：库/表/连接的字符集不统一。

解决：全部用 utf8mb4（utf8 在 MySQL 里是 3 字节、不支持 emoji）。

-- 建库建表时
CREATE DATABASE db CHARACTER SET utf8mb4;
-- 连接时（Nest 的 TypeORM 配置）
charset: 'utf8mb4'

4. 连接数满（`Too many connections`）

原因：连接没释放（连接泄漏）、或并发太高。

解决：

用连接池（TypeORM / Prisma / mysql2 的 pool），别每次新建连接。
排查泄漏：确保 await 了所有查询、连接用完归还。
调大 max_connections（治标），根本是减少长连接占用。

5. 主从延迟

现象：写入主库后立刻读从库，读不到（延迟几秒）。

主从复制的完整时序（理解延迟从哪来：binlog → relay log → SQL 回放，每一段都可能产生延迟）：

sequenceDiagram autonumber participant App as 应用 participant M as 主库 Master participant IO as 从库 IO 线程 participant R as Relay Log participant SQL as 从库 SQL 线程 App->>M: 写操作 M->>M: 写入 binlog IO->>M: 拉取 binlog M-->>IO: 返回 binlog 事件 IO->>R: 写入 relay log SQL->>R: 读 relay log SQL->>SQL: 回放落盘

解决：

「写后读」走主库（强一致读），其他走从库。
用半同步复制（rpl_semi_sync_master_enabled）降低丢失风险（但不消除延迟）。
大事务会严重拖慢从库，拆小。

6. 大表 DDL 卡住

现象：给千万级大表 ALTER TABLE ADD COLUMN，锁表几小时。

解决：

用在线 DDL 工具：pt-online-schema-change 或 gh-ost，不锁表改结构。
或用 MySQL 8 的即时 DDL（加列、改默认值等很多操作已秒级、不锁）。
低峰期操作。

六、难点与解决方案

1. 深分页性能差

-- 数据量大时极慢（要扫描前 100 万行再丢掉）
SELECT * FROM order ORDER BY id LIMIT 1000000, 20;

方案（游标分页 / 记住上一页末尾 id）：

-- 记住上一页最后一条的 id（比如 1000020），下一页直接从这往后取
SELECT * FROM order WHERE id > 1000020 ORDER BY id LIMIT 20;

WHERE id > last_id 走主键索引，O(1) 跳过，不再扫描。缺点：不能跳页（只能上一页/下一页）。如果不能改交互，用「延迟关联」：先查主键再 JOIN。

2. 高并发下的行锁与热点更新

场景：秒杀扣库存，UPDATE goods SET stock = stock - 1，并发下大量行锁等待。

方案：

库存放 Redis 预扣（DECR 原子），异步落库，避免 DB 行锁排队。
DB 层加乐观锁：UPDATE ... SET stock=stock-1, version=version+1 WHERE id=? AND version=?，失败重试。
拆分热点：一个商品拆成多个库存行，扣减时随机选一行，降低单行锁竞争。

3. 分库分表

单表千万级、单库写不动时拆分：

垂直拆分：按业务拆库（订单库、用户库）。
水平拆分：按哈希/范围把一张表拆成多张（order_0 ~ order_15）。

难点：跨库 JOIN 麻烦、分布式事务难、全局唯一 id（用雪花算法）。一般用中间件（ShardingSphere、Vitess）或先上 TiDB（兼容 MySQL 协议、原生分布式、不用改业务）。

4. 读写分离

主库写、从库读，分摊压力。难点是复制延迟导致「写完读不到」。解决见上文主从延迟。Nest 里可配 TypeORM 的主从连接，或用中间件代理。

七、生产环境要点

备份：定期 mysqldump 逻辑备份 + binlog 增量；定期演练恢复。
监控：慢查询日志（long_query_time）、连接数、缓冲池命中率、主从延迟。
参数：innodb_buffer_pool_size 调到机器内存 60–70%（最关键性能参数）。
不要 SELECT *：只查需要的列，少回表、省带宽。
大表必加索引：但索引不是越多越好（写时要维护索引），按查询建。
开 binlog：不仅是主从复制需要，也是误删数据恢复的救命稻草（按时间点恢复）。

速查：常见报错对照

报错	原因	解决
`Too many connections`	连接数满	用连接池、排查泄漏、调 `max_connections`
`Deadlock found`	死锁	统一加锁顺序、事务短小、重试
`Incorrect string value`	字符集不对	统一 `utf8mb4`
`Lock wait timeout`	行锁等待超时	排查长事务、缩短事务
`Data too long for column`	数据超字段长度	加大 `VARCHAR(n)` 或入库前截断