MySQL 基础

Java 初级 · 数据与持久化

MySQL 是最流行的关系型数据库。写好 SQL 只是第一步，真正决定性能与正确性的，是存储引擎选型、数据类型、索引（尤其是 B+ 树）与事务隔离级别。本节把这些「底层为什么」讲清，让你不止会写 SELECT，更能回答「为什么加索引快」「为什么 DECIMAL 存金额」「RR 隔离级别怎么做到可重复读」。

一、库、表与存储引擎

数据库（schema）是表的集合；表由行（记录）和列（字段）组成。MySQL 的存储引擎决定数据「怎么存、怎么索、怎么锁」——这是它的核心特性。

InnoDB（默认，必选） vs MyISAM：

InnoDB 支持事务、外键、行级锁、崩溃后靠 redo/undo 日志恢复，是绝大多数业务的选择。
MyISAM 不支持事务、只有表级锁，虽某些读场景快，但已基本被 InnoDB 取代。新项目无脑 InnoDB。

二、数据类型选型：存对才能算对

类型	用途	选型要点
`INT` / `BIGINT`	整数 id、计数	`INT` 约 21 亿上限，`id` 用 `BIGINT` 更稳（或雪花 ID）
`VARCHAR(n)`	变长字符串	按需定长（用户名 50、备注 255/1000），utf8mb4 下 1 字符最多 4 字节
`CHAR(n)`	定长（如邮编）	长度固定时用，省去变长开销
`DECIMAL(m,d)`	金额	精确十进制，m 总位数、d 小数位；绝不用 FLOAT/DOUBLE
`DATETIME` / `TIMESTAMP`	时间	`DATETIME` 无时区、`TIMESTAMP` 受时区影响且有 2038 上限；新项目多用 `DATETIME` + 应用层时区
`BOOLEAN`	真假	MySQL 用 `TINYINT(1)` 实现
`TEXT` / `BLOB`	大文本/二进制	超长内容，不进索引主体

金额绝对不能用 FLOAT/DOUBLE

浮点在二进制里无法精确表示十进制小数（见变量一节 IEEE 754 根因），0.1+0.2 ≠ 0.3。账户余额、订单金额必须用 DECIMAL(10,2) 之类，数据库层面保证精确。否则「对账差一分钱」会让你彻夜难眠。

建库建表示例

sql

CREATE DATABASE IF NOT EXISTS shop CHARACTER SET utf8mb4;
USE shop;

CREATE TABLE user (
    id         BIGINT PRIMARY KEY AUTO_INCREMENT,
    username   VARCHAR(50) NOT NULL UNIQUE,
    age        INT,
    balance    DECIMAL(10,2) DEFAULT 0.00,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

utf8mb4：真正的「完整 UTF-8」，支持 emoji 与所有中文；老 utf8（utf8mb3）最多 3 字节，存不了 emoji。
PRIMARY KEY 主键：唯一标识一行，InnoDB 用它组织数据（见索引）。
NOT NULL / UNIQUE / DEFAULT：约束保证数据质量。

三、索引与 B+ 树：为什么查询快（核心原理）

没有索引时，SELECT ... WHERE username='Tom' 要全表扫描（逐行比对），表越大越慢。索引是一份「排好序的目录」。

为什么是 B+ 树，不是哈希或二叉搜索树？

二叉搜索树（BST）：极端退化为链表，查找 O(n)；平衡二叉搜索树（AVL）高度仍偏高，百万数据树高约 20，且每个节点只存一个 key，磁盘 IO 次数多。
B 树：多路平衡树，一个节点存多个 key，降低树高；但非叶子节点也存数据，范围查询要中序遍历，不够顺。
B+ 树（MySQL 默认索引结构）：
- 非叶子节点只存 key（索引列值）+ 指针，不存数据行，单节点能放更多 key → 树更矮胖，3~4 层就能存千万级数据，查找只需 3~4 次磁盘 IO。
- 所有数据行都在叶子节点，且叶子节点之间用双向链表串联 → 范围查询/排序极快（WHERE id > 100、ORDER BY 直接沿链表扫）。

聚簇索引 vs 二级索引

聚簇索引（Clustered Index）：InnoDB 用主键构建，叶子节点直接存整行数据。一张表只有一个聚簇索引（就是主键）。所以「按主键查」最快——定位到叶子就拿到了整行。
二级索引（Secondary）：普通索引（如 username 上的索引）的叶子节点存的是主键值，不是整行。通过二级索引查到主键后，还要回表去聚簇索引拿整行数据（「回表」有额外 IO）。

最左前缀原则

联合索引 (a, b, c) 本质是「先按 a 排序、a 相同按 b、b 相同按 c」。所以它能加速 WHERE a=?、WHERE a=? AND b=?，但不能跳过 a 直接用 b（索引失效，退化全表扫描）。这就是「最左前缀」。

索引不是越多越好

索引是「用空间换时间、用写性能换读性能」：每加一个索引，INSERT/UPDATE/DELETE 都要同步维护它，写变慢、占存储。只在高频查询、高选择性的列上加索引；避免对「性别」这种低区分度列建索引（命中一半行，优化器往往直接全表扫）。

四、事务与隔离级别：锁与 MVCC（概要）

事务（ACID） 把多个操作打包成「原子整体」。但有并发时，会出三类经典问题：

问题	现象
脏读	读到别的事务未提交的数据（它可能回滚）
不可重复读	同一事务内两次读同一行，值被别的事务改了
幻读	同一事务内两次范围查询，多了/少了行（别的事务插入/删除）

SQL 标准定义 4 个隔离级别：

隔离级别	脏读	不可重复读	幻读
读未提交 READ UNCOMMITTED	❌ 可能	❌ 可能	❌ 可能
读已提交 READ COMMITTED	✅ 避免	❌ 可能	❌ 可能
可重复读 REPEATABLE READ（MySQL 默认）	✅	✅	✅（靠 MVCC+间隙锁基本避免）
串行化 SERIALIZABLE	✅	✅	✅（全程加锁，最慢）

底层怎么实现：锁 + MVCC

锁（Locking）：写操作对行加排他锁（X 锁），别的事务要改这行就得等；InnoDB 还有**间隙锁（Gap Lock）**锁住「范围之间的空隙」，防止别的事务往里插新行，从而抑制幻读。
MVCC（多版本并发控制，核心）：InnoDB 给每行藏了两个隐藏列——事务 ID（DB_TRX_ID） 和回滚指针（DB_ROLL_PTR）。DB_ROLL_PTR 指向 undo log 里该行的历史版本。
- 每次更新，旧版本写入 undo log，新行记上当前事务 ID。
- 一个事务启动时拿到一个读视图（Read View），只「看见」在它开始前已提交的版本；未提交的、或别的事务新提交的，都通过 undo log 链回滚到该事务应见的快照。
- 于是「可重复读」实现了：同一事务内多次读，看到的都是同一份快照，不会被别的事务干扰——读不加锁、读写不阻塞，性能远好于纯锁方案。

为什么 MySQL 默认 RR 还不易幻读

标准 SQL 里 RR 不保证防幻读，但 InnoDB 靠 MVCC（快照读） + 间隙锁（当前读） 组合，在 RR 下就基本杜绝了幻读，所以多数业务直接用默认 RR 即可，不用升到昂贵的 SERIALIZABLE。

五、CRUD 与约束

sql

-- 增
INSERT INTO user (username, age, balance) VALUES ('Tom', 20, 100.00);

-- 查（明确列，避免 SELECT *；大表务必带索引条件）
SELECT id, username, balance FROM user WHERE age > 18 ORDER BY balance DESC;

-- 改
UPDATE user SET balance = balance - 10 WHERE username = 'Tom';

-- 删（务必带 WHERE！）
DELETE FROM user WHERE id = 1;

-- 外键示例
CREATE TABLE orders (
    id      BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    amount  DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES user(id)   -- 保证订单必属存在的用户
);

六、常见误区与注意点

WARNING

用 FLOAT/DOUBLE 存金额：二进制不精确，必须用 DECIMAL。
VARCHAR 设太大或太小：过大浪费存储/影响索引，过小截断；按业务评估（50/255 常见）。
DELETE/UPDATE 不带 WHERE：清空整张表！执行前先 SELECT 验证命中范围。
滥用 SELECT *：返回多余列、不利于索引覆盖、增加 IO，明确列出所需字段。
低区分度列建索引：如「性别」，优化器往往放弃使用，白增开销。
忽视字符集：表/库不统一 utf8mb4，中文/emoji 乱码或插入报错。

七、最佳实践

每个表都有主键（通常 BIGINT AUTO_INCREMENT 或雪花 ID），InnoDB 靠它组织聚簇索引。
金额 DECIMAL、时间 DATETIME、文本 utf8mb4；按需选类型，别无脑 VARCHAR(255)。
高频查询、高区分度列加索引；联合索引遵循最左前缀；索引勿过多（拖累写）。
写 UPDATE/DELETE 先 SELECT 验证 WHERE 命中范围，再执行修改。
业务用 InnoDB + 默认 RR 隔离级别；需要跨多表一致用事务，理解 MVCC 下的「可重复读」快照。

📚 官方文档与参考资料

MySQL 8.0 参考手册：https://dev.mysql.com/doc/refman/8.0/en/
MySQL · InnoDB 索引（B+树）：https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html
MySQL · 事务与隔离级别：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
Java 教程（JDBC）：https://docs.oracle.com/javase/tutorial/jdbc/

✏️ 练习与自测

设计一张 product（商品）表，含 id、名称、价格、库存，写出建表 SQL（注意价格用 DECIMAL、字符集 utf8mb4、InnoDB）。
插入 3 条商品记录，查询价格大于 50 的商品并按价格降序排列；思考这条查询若 price 无索引会如何（全表扫描）。
给 username 加唯一索引，解释为什么「用户名」需要唯一（业务约束 vs 性能）。
解释：InnoDB 在「可重复读（RR）」下，同一事务两次 SELECT 同一行，为什么看到的余额不会因为别的事务 UPDATE 而改变？（提示：Read View + undo log 快照）

本节小结

MySQL 用 InnoDB（支持事务/外键/行锁/MVCC）作默认存储引擎。数据类型选型决定正确性：金额用 DECIMAL（浮点二进制不精确）、文本用 utf8mb4、时间用 DATETIME、id 用 BIGINT。索引核心是 B+ 树：非叶子节点只存 key 使树矮胖（3~4 层扛千万数据）、叶子节点存数据且双向链表串联使范围查询快；聚簇索引叶子存整行（按主键）、二级索引叶子存主键需「回表」；联合索引遵循最左前缀。事务隔离靠锁（X锁/间隙锁）+ MVCC：每行带事务 ID 与 undo 指针，事务启动时取 Read View，只看见已提交快照，故 RR 下「可重复读」且读写不阻塞。索引勿过多（拖累写），UPDATE/DELETE 必带 WHERE 并先 SELECT 验证。

MySQL 基础 ​

一、库、表与存储引擎 ​

二、数据类型选型：存对才能算对 ​

建库建表示例 ​

三、索引与 B+ 树：为什么查询快（核心原理） ​

为什么是 B+ 树，不是哈希或二叉搜索树？ ​

聚簇索引 vs 二级索引 ​

最左前缀原则 ​

四、事务与隔离级别：锁与 MVCC（概要） ​

底层怎么实现：锁 + MVCC ​

五、CRUD 与约束 ​

六、常见误区与注意点 ​

七、最佳实践 ​

📚 官方文档与参考资料 ​

✏️ 练习与自测 ​