系列：后端路线 · Part 1
数据持久化是后端的基石。本篇目标：能独立写出建表语句、熟练使用 CRUD、理解索引与事务、说清隔离级别，并应付常见面试题。更高级的主题（锁、执行计划、分库分表、主从复制等）留待后续。

0. 先建立直觉

MySQL 是一个关系型数据库，数据按「库 → 表 → 行」组织。你通过 SQL（结构化查询语言） 与它对话，本质是向数据库下达”建什么、存什么、改什么、查什么”的指令。

一条 SQL 请求的旅程（面试常问）：客户端 → 连接器 → 查询缓存（8.0 已移除）→ 分析器（词法/语法）→ 优化器（选执行计划）→ 执行器 → 存储引擎（InnoDB）→ 落盘。

为什么默认引擎是 InnoDB？因为它支持事务、行级锁、外键、崩溃恢复，是绝大多数业务场景的首选。MyISAM 不支持事务和行锁，已逐渐被淘汰。

1. 建表语句（DDL）

DDL（Data Definition Language）负责定义结构：CREATE / ALTER / DROP。

1.1 核心数据类型（够用即可）

类别 常用类型 说明
整数 INT、BIGINT、TINYINT 用户量用 BIGINT 更稳；状态/枚举用 TINYINT
小数 DECIMAL(10,2) 金额必须用 DECIMAL，禁用 FLOAT/DOUBLE（精度丢失）
字符串 VARCHAR(n)、CHAR(n) VARCHAR 变长（<255 常用），CHAR 定长（如手机号/MD5）
文本 TEXT 长文本，不建索引前缀外的索引
时间 DATETIME、TIMESTAMP、DATE DATETIME 与时区无关；TIMESTAMP 受时区影响且范围小
布尔 TINYINT(1) MySQL 无原生 BOOLEAN，用 0/1 模拟

1.2 一张”能打”的建表模板

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `user` (
  `id`          BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username`    VARCHAR(64)  NOT NULL DEFAULT '' COMMENT '用户名',
  `age`         TINYINT      NOT NULL DEFAULT 0  COMMENT '年龄',
  `balance`     DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '账户余额',
  `status`      TINYINT      NOT NULL DEFAULT 1  COMMENT '1=正常 2=禁用',
  `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

1.3 关键约束与写法要点

  • NOT NULL：尽量给字段加，NULL 在索引和比较里是”特殊值”，容易踩坑。
  • DEFAULT：给合理默认值，避免插入时必须填所有字段。
  • AUTO_INCREMENT：自增主键，InnoDB 推荐用无业务含义的自增 id 做主键（避免页分裂、聚集索引紧凑）。
  • COMMENT：建表必写注释，半年后你还会感谢自己。
  • CHARSET=utf8mb4：务必用 utf8mb4，而非 utf8（utf8 最多 3 字节，存不了 emoji 和某些生僻字）。
  • 主键/唯一/普通索引语法：PRIMARY KEY、UNIQUE KEY、KEY（即普通索引）。

1.4 改表（ALTER，生产环境要谨慎）

1
2
3
4
5
6
7
8
-- 加字段
ALTER TABLE `user` ADD COLUMN `email` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '邮箱';
-- 改字段类型
ALTER TABLE `user` MODIFY COLUMN `age` SMALLINT NOT NULL DEFAULT 0;
-- 加索引
ALTER TABLE `user` ADD INDEX `idx_age` (`age`);
-- 删字段（不可逆，慎用）
ALTER TABLE `user` DROP COLUMN `email`;

生产告警：大表 ALTER 可能锁表、复制延迟。大表改结构应使用 pt-online-schema-change 或 MySQL 8.0 的 ALGORITHM=INSTANT/INPLACE。

2. CRUD（增删改查）

2.1 INSERT（增）

1
2
3
4
5
6
-- 全字段插入（顺序需与表结构一致）
INSERT INTO `user` VALUES (NULL, 'zhangsan', 20, 100.00, 1, NOW(), NOW());

-- 指定字段插入（推荐，顺序清晰、易维护）
INSERT INTO `user` (`username`, `age`, `balance`)
VALUES ('zhangsan', 20, 100.00), ('lisi', 22, 50.00);  -- 批量插入，性能优于逐条

经验：优先指定列名；批量插入代替循环单条，能大幅减少网络往返与事务开销。

2.2 SELECT（查）—— 最重要

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 基础查询
SELECT `id`, `username`, `age` FROM `user`;

-- 带条件
SELECT * FROM `user` WHERE `status` = 1 AND `age` >= 18;

-- 模糊查询（前缀模糊才能用索引，'%xx' 前缀通配会失效）
SELECT * FROM `user` WHERE `username` LIKE 'zhang%';

-- 排序 + 分页（LIMIT 偏移量, 条数；深分页用游标代替 offset）
SELECT * FROM `user` WHERE `status` = 1 ORDER BY `id` DESC LIMIT 0, 10;

-- 聚合
SELECT `status`, COUNT(*) AS cnt FROM `user` GROUP BY `status`;

-- 联表查询（JOIN）
SELECT u.`username`, o.`amount`
FROM `user` u
JOIN `order` o ON u.`id` = o.`user_id`
WHERE o.`amount` > 100;

JOIN 速记：

  • INNER JOIN：两表都匹配才返回。
  • LEFT JOIN：左表全保留，右表无匹配补 NULL（最常用）。
  • RIGHT JOIN：与 LEFT 相反。

深分页陷阱：LIMIT 100000, 10 要先扫 10 万行再丢弃。优化：WHERE id > 100000 LIMIT 10（游标分页，需有序主键）。

2.3 UPDATE（改）

1
2
UPDATE `user` SET `balance` = `balance` + 50.00, `updated_at` = NOW()
WHERE `username` = 'zhangsan';

生产最高危操作：UPDATE / DELETE 必须带 WHERE！忘写 WHERE 会更新全表。建议开启 SQL 安全模式（sql_safe_updates），或先用 SELECT 确认影响行数。

2.4 DELETE（删）

1
2
3
4
5
6
7
DELETE FROM `user` WHERE `id` = 123;

-- 删全表（谨慎，逐行删、可回滚）
DELETE FROM `user`;

-- 清空表（DDL 级，不可回滚、自增重置，更快）
TRUNCATE TABLE `user`;

业务上”删除”往往用逻辑删除：加 deleted 字段（0/1），UPDATE deleted=1，而非物理删除，便于审计与恢复。

3. 索引（Index）

索引是排好序的数据结构（InnoDB 用 B+Tree），目的是让查询不必全表扫描，就像书的目录。

3.1 索引类型

类型 说明
主键索引 (PK) 聚簇索引，叶子节点存整行数据；一张表只有一个
唯一索引 (UNIQUE) 值不能重复，可加速查询并约束数据
普通索引 (INDEX) 仅加速查询，允许重复
组合索引 (联合索引) 多列组合，遵循最左前缀原则
覆盖索引 查询字段全在索引中，无需回表，极快

3.2 最左前缀原则（面试必考）

组合索引 INDEX(a, b, c) 相当于建了 (a)、(a,b)、(a,b,c) 三套排序。

1
2
3
4
5
6
7
8
9
10
11
12
-- 能用到索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- 用不到（缺最左列 a）
WHERE b = 2
WHERE b = 2 AND c = 3

-- 部分用到（a 用到，c 在 b 缺失/范围后失效）
WHERE a = 1 AND c = 3
WHERE a = 1 AND b > 2 AND c = 3   -- a,b 用到，c 失效

口诀：组合索引里，等值条件尽量放前面，范围条件放后面。

3.3 什么情况索引会失效？

  • 对索引列做函数/运算：WHERE YEAR(created_at) = 2026 → 失效；应 WHERE created_at >= '2026-01-01'。
  • 隐式类型转换：WHERE phone = 13800138000（phone 是 VARCHAR，传数字）→ 失效。
  • 前导通配符：LIKE '%zhang' → 失效；LIKE 'zhang%' 可用。
  • OR 连接非索引列：部分列无索引时整条可能走全表。
  • 使用 !=、<>、NOT IN、IS NULL 在某些场景会放弃索引。

3.4 回表与覆盖索引

InnoDB 普通索引叶子节点存的是主键值。查到主键后再去主键索引取整行，这叫回表。

1
2
3
4
5
-- 需要回表：查 name 后还要取 age、email
SELECT * FROM `user` WHERE `username` = 'zhang';

-- 覆盖索引：只需 username 和 id（都在索引里）
SELECT `id`, `username` FROM `user` WHERE `username` = 'zhang';

优化思路：尽量让高频查询走覆盖索引（例如建 INDEX(username, age) 来覆盖 SELECT age FROM user WHERE username=?）。

4. 事务（Transaction）

事务把多条 SQL 打包成”要么全成、要么全败”的一个单元。

4.1 四大特性（ACID，面试必背）

特性 含义 MySQL 如何实现
Atomicity 原子性 事务内操作要么全做，要么全不做 undo log（回滚日志）
Consistency 一致性 数据从一个合法状态到另一个合法状态 由应用 + 原子性/隔离性/持久性共同保证
Isolation 隔离性 并发事务互不干扰 锁 + MVCC
Durability 持久性 提交后数据永久保存 redo log（重做日志）

4.2 基本用法

1
2
3
4
5
START TRANSACTION;            -- 或 BEGIN;
UPDATE `user` SET `balance` = `balance` - 100 WHERE `username`='zhangsan';
UPDATE `user` SET `balance` = `balance` + 100 WHERE `username`='lisi';
COMMIT;                       -- 提交，生效
-- ROLLBACK;                  -- 出错时回滚，撤销全部

经典例子：转账。A 扣钱和 B 加钱必须在同一事务里，否则中途崩溃会导致钱”消失”。

4.3 两个关键日志（理解即可）

  • redo log：保证持久性。数据先写 redo log（顺序写、快），再异步刷盘，崩溃后可重放恢复。
  • undo log：保证原子性。记录修改前的值，回滚时反向操作还原。

5. 隔离级别（Isolation Levels）

并发事务同时跑，会产生三类经典问题：

问题 现象
脏读 读到别的事务未提交的数据（它万一回滚了，你读到的就是脏的）
不可重复读 同一事务内，两次读同一行，结果被别的事务修改并提交而不同
幻读 同一事务内，两次范围查询，别的事务插入/删除了行，导致行数变了

不可重复读 vs 幻读：前者是”某行的值变了”，后者是”符合条件的行数变了”。

5.1 四种隔离级别（从低到高）

级别 脏读 不可重复读 幻读 说明
READ UNCOMMITTED 读未提交 ❌ 会 ❌ 会 ❌ 会 几乎不用
READ COMMITTED 读已提交 (RC) ✅ 防 ❌ 会 ❌ 会 多数数据库默认（如 Oracle/PG）
REPEATABLE READ 可重复读 (RR) ✅ 防 ✅ 防 ✅ 防* MySQL InnoDB 默认
SERIALIZABLE 串行化 ✅ 防 ✅ 防 ✅ 防 加锁串行，性能最差

`*MySQL 在 RR 下通过 MVCC + Next-Key Lock（间隙锁）基本解决了幻读。这是 MySQL 默认用 RR 而非 RC 的原因。

5.2 怎么设置 / 查看

1
2
3
4
5
6
7
8
-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置当前会话
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置全局（需重启连接生效）
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

5.3 MVCC 一句话理解

MVCC（多版本并发控制）让”读不加锁、读写不阻塞”：每行数据有隐藏的版本号（事务 ID）和 undo log 链，读事务按自己的”快照”读对应版本。所以 RR 下你能重复读到同样的数据，不会被别人提交干扰。

6. 面试速通题（附要点）

Q1：InnoDB 为什么推荐自增主键？
自增 id 作为聚簇索引，新数据总是追加到索引末尾，页分裂少、写入紧凑；若用无序业务键（如 UUID），插入会分散到各处，导致频繁页分裂、碎片多、性能差。

Q2：CHAR 和 VARCHAR 区别？
CHAR 定长（不足补空格，检索快但费空间），适合长度固定的数据（手机号、MD5）；VARCHAR 变长，省空间，适合长度波动大的字段。

Q3：说说索引为什么快？
索引是 B+Tree 有序结构，查询从 O(n) 全表扫描降为 O(log n)；且 B+Tree 叶子节点成链表，范围查询高效；非叶子节点只存 key，单页能放更多指针，树更矮、IO 更少。

Q4：什么是回表？怎么避免？
普通索引叶子存主键值，查非索引列需回主键索引取整行（回表）。避免方式：建组合索引覆盖查询列（覆盖索引），或只查索引包含的列。

Q5：最左前缀原则是什么？
组合索引 (a,b,c) 按 a→b→c 排序，查询必须从最左列开始连续命中才能用索引；跳过 a 或用 b、c 单独查则用不到。

Q6：事务的 ACID 分别靠什么实现？
原子性靠 undo log，持久性靠 redo log，隔离性靠锁 + MVCC，一致性由前三者 + 应用逻辑共同保证。

Q7：MySQL 默认隔离级别是什么？能解决幻读吗？
默认 REPEATABLE READ。InnoDB 在 RR 下通过 MVCC + Next-Key Lock 基本解决了幻读（快照读靠 MVCC，当前读靠间隙锁）。

Q8：读已提交(RC) 和可重复读(RR) 的区别？
RC 每次读都取最新已提交版本（会有不可重复读）；RR 第一次读建立快照，事务内都读同一快照（可重复读）。RR 还能防幻读。

Q9：为什么不要用 FLOAT 存金额？
FLOAT/DOUBLE 是二进制浮点，存在精度误差（如 0.1+0.2≠0.3），金额必须用 DECIMAL(m,n) 定点数。

Q10：UPDATE 忘写 WHERE 会怎样？如何避免？
会更新全表数据。避免：开启 sql_safe_updates、先用 SELECT 确认影响行数、生产操作经审批与备份。

Q11：DROP、TRUNCATE、DELETE 的区别？
DELETE 是 DML，逐行删、可带 WHERE、可回滚；TRUNCATE 是 DDL，清空全表、不可回滚、重置自增、快；DROP 连表结构一起删。

Q12：覆盖索引的好处？
查询所需字段全在索引中，无需回表访问主键索引，减少 IO，显著提升性能。

7. 本篇自测清单

  • 能独立写出一张带主键、唯一索引、普通索引、注释的完整建表语句
  • 熟练写出 INSERT / SELECT(含 JOIN、GROUP BY、LIMIT) / UPDATE / DELETE
  • 能解释最左前缀原则并判断某条 SQL 能否命中组合索引
  • 能说出索引失效的常见场景
  • 能背出 ACID 并对应到 undo/redo log、锁、MVCC
  • 能讲清四种隔离级别与脏读/不可重复读/幻读的关系
  • 能回答上面 12 道面试题

下一篇预告：Part 2 Redis —— 缓存为什么快、缓存穿透/击穿/雪崩、与 MySQL 的数据一致性。先把本篇 SQL 练熟，再进军内存数据库。