Mysql：总结一-Aet

Bingliaolong 2026-03-15 23:59 Aet | 隐藏边栏 | 抢沙发 | 7 0

文章评分 2 次，平均分 5.0 ：

概述

数据库（Database）
1. 数据的容器，一个 MySQL 服务可以有多个数据库
表（Table）
1. 数据库中存储数据的基本单位，由行（记录）和列（字段）组成
字段（Column）
1. 数据类型的约束，比如 INT、VARCHAR、DATE 等

数据类型

整数类

TINYINT, INT, BIGINT。INT 最常用，BIGINT 用于存 ID 或大数字

字符串类

CHAR(n) 定长，VARCHAR(n) 变长
1. 短且固定的用 CHAR（如性别），长度不定的用 VARCHAR（如用户名）
TEXT 用于存大段文字

时间类

DATE（日期）、DATETIME（日期+时间）、TIMESTAMP（时间戳，会自动转时区）

浮点类

FLOAT, DOUBLE 有精度问题，涉及金额建议用 DECIMAL(m,d)

数据库和表的管理

常规操作

-- 创建数据库

CREATE DATABASE mydb DEFAULT CHARSET utf8mb4;

-- 使用数据库

USE mydb;

-- 创建表

CREATE TABLE users (

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(50) NOT NULL,

email VARCHAR(100) UNIQUE,

age INT DEFAULT 0,

created_at DATETIME DEFAULT CURRENT_TIMESTAMP

);

-- 查看表结构

DESC users;

-- 修改表：加字段

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 修改表：改字段类型

ALTER TABLE users MODIFY COLUMN name VARCHAR(100);

-- 删表 / 删库（慎用）

DROP TABLE users;

DROP DATABASE mydb;

关键约束

PRIMARY KEY — 主键，唯一且非空，一般配合 AUTO_INCREMENT
NOT NULL — 不允许为空
UNIQUE — 唯一，不能重复
DEFAULT — 默认值
FOREIGN KEY — 外键，关联另一张表
1. 实际开发中很多团队选择在应用层维护，不在数据库层加外键

增

-- 插入单条

INSERT INTO users (name, email, age) VALUES ('张三', 'zhangsan@qq.com', 25);

-- 插入多条（批量插入性能远优于逐条插入）

INSERT INTO users (name, email, age) VALUES

('李四', 'lisi@qq.com', 30),

('王五', 'wangwu@qq.com', 28);

-- 插入时忽略冲突（主键或唯一索引冲突时跳过）

INSERT IGNORE INTO users (name, email, age) VALUES ('张三', 'zhangsan@qq.com', 25);

-- 插入或更新（存在则更新，不存在则插入）

INSERT INTO users (name, email, age) VALUES ('张三', 'zhangsan@qq.com', 26)

ON DUPLICATE KEY UPDATE age = VALUES(age);

删

注意
1. 写 DELETE 之前，先把 DELETE 换成 SELECT 跑一遍，确认要删的数据是对的，再改回 DELETE 执行

-- 按条件删除

DELETE FROM users WHERE id = 3;

-- 删除全部数据（逐行删，可回滚，慢）

DELETE FROM users;

-- 清空表（直接截断，不可回滚，快，自增ID会重置）

TRUNCATE TABLE users;

改

注意
1. UPDATE 一定要带 WHERE 条件。不带 WHERE 会更新整张表，这是生产环境的经典事故

-- 基本更新

UPDATE users SET age = 26 WHERE name = '张三';

-- 同时更新多个字段

UPDATE users SET age = 26, email = 'new@qq.com' WHERE id = 1;

-- 基于计算更新

UPDATE users SET age = age + 1; -- 所有人年龄加1

查

基础查询

-- 查所有

SELECT * FROM users;

-- 查指定字段（推荐，避免 SELECT *）

SELECT name, age FROM users;

-- 条件查询

SELECT * FROM users WHERE age > 25;

SELECT * FROM users WHERE name = '张三' AND age > 20;

SELECT * FROM users WHERE age IN (25, 28, 30);

SELECT * FROM users WHERE name LIKE '张%'; -- 以"张"开头

SELECT * FROM users WHERE email IS NULL; -- 判断空值用 IS NULL，不能用 = NULL

-- 去重

SELECT DISTINCT age FROM users;

-- 别名

SELECT name AS 姓名, age AS 年龄 FROM users;

排序与分页

-- 排序（ASC 升序，DESC 降序）

SELECT * FROM users ORDER BY age DESC;

-- 分页（跳过前10条，取10条）

SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;

-- 简写：LIMIT 10, 10（第一个是偏移量，第二个是数量）

聚合函数

SELECT COUNT(*) FROM users; -- 总数

SELECT MAX(age) FROM users; -- 最大值

SELECT MIN(age) FROM users; -- 最小值

SELECT AVG(age) FROM users; -- 平均值

SELECT SUM(age) FROM users; -- 求和

分组

-- 按年龄分组，统计每个年龄有多少人

SELECT age, COUNT(*) AS cnt FROM users GROUP BY age;

-- 分组后筛选（WHERE 在分组前过滤，HAVING 在分组后过滤）

SELECT age, COUNT(*) AS cnt FROM users GROUP BY age HAVING cnt > 2;

多级联查JOIN

-- 内连接：只返回两表都能匹配上的数据

SELECT u.name, o.amount

FROM users u

INNER JOIN orders o ON u.id = o.user_id;

-- 左连接：返回左表全部 + 右表匹配的（没匹配到的补 NULL）

SELECT u.name, o.amount

FROM users u

LEFT JOIN orders o ON u.id = o.user_id;

-- 右连接：与左连接相反，实际中左连接更常用

子查询

-- 查询下过单的用户

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- 查询年龄大于平均年龄的用户

SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

`SQL` 执行顺序

写 SQL 的顺序和数据库实际执行的顺序不一样，理解这个很重要
1. 这就是为什么 WHERE 里不能用 SELECT 中定义的别名，但 ORDER BY 可以

1	FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

实用小技巧

如果字段为 NULL，给一个默认值：

1	`SELECT IFNULL(phone, '未填写') FROM users;`

范围查询

1	WHERE age BETWEEN 20 AND 30

拼接字符串

1	SELECT CONCAT(name, '-', age) FROM users;

当前时间

1	INSERT INTO users (name, created_at) VALUES ('test', NOW());

`MySQL` 整体架构

概述

MySQL 采用的是分层架构，从上到下大致分三层
1. 连接层
2. 服务层
3. 存储引擎层

连接层

概述
1. 客户端通过 TCP 连接到 MySQL 服务端，连接层负责认证（用户名、密码、权限校验）和连接管理
2. 每个连接会分配一个线程来处理请求
3. 频繁创建销毁连接开销大，所以生产中一般会用连接池（如 HikariCP、Druid）来复用连接

服务层

MySQL 的"大脑"，与存储引擎无关的逻辑都在这一层，包含几个关键组件：
解析器（Parser）：
1. 将 SQL 文本做词法分析和语法分析，生成一棵解析树（Parse Tree）
2. 如果语法错误，就在这一步报错
优化器（Optimizer）：
1. 拿到解析树后，优化器决定"怎么执行最快"，比如选择用哪个索引、JOIN 的顺序、是否用临时表等
2. 同一条 SQL 可能有多种执行方案，优化器基于成本模型（Cost-Based Optimization）选出代价最小的那个
执行器（Executor）：
1. 按照优化器给出的执行计划，调用存储引擎的接口逐步执行，拿到数据后返回给客户端
2. 执行前会再做一次权限校验

存储引擎层

MySQL 的一大特色是存储引擎可插拔
1. 不同的表可以用不同的存储引擎
InnoDB（默认，最重要）：
1. 支持事务、行级锁、外键、MVCC、崩溃恢复
MyISAM：
1. 不支持事务和行锁，但查询快、全文索引支持好，老系统中常见
Memory：
1. 数据存在内存中，重启丢失，适合临时表
Archive：
1. 只支持插入和查询，适合归档日志类数据

完整流程

1	客户端 → 连接层(认证) → 解析器(语法检查) → 优化器(选执行计划) → 执行器 → 存储引擎(读写数据) → 返回结果

`InnoDB` 存储引擎原理

概述

InnoDB 是 MySQL 最核心的存储引擎

数据结构

InnoDB 的数据是以页（Page）为基本单位存储在磁盘上的，每页默认大小 16KB
层级
1. 一张表的数据存在一个 .ibd 文件中（独立表空间模式下）
2. 读写数据时，MySQL 不是一行一行读磁盘的，而是一次读一整页到内存中，这是因为磁盘 IO 的最小单位是页

1	表空间（Tablespace）→ 段（Segment）→ 区（Extent，连续64个页，1MB）→ 页（Page，16KB）→ 行（Row）

索引结构：`B+` 树

InnoDB 的索引用的是 B+ 树，这是理解 MySQL 底层最关键的数据结构
为什么是 B+ 树而不是其他结构
1. 二叉树 / 红黑树：层数太高，每一层就是一次磁盘 IO，数据量大时性能差
2. B 树：每个节点都存数据，导致每个节点能存的 key 数量少，树更高
3. B+ 树的优势：非叶子节点只存 key 不存数据，所以一个节点可以存更多 key，树更矮更胖，IO 次数少
  叶子节点之间用双向链表串联，范围查询（BETWEEN、ORDER BY）非常高效
实际数据
1. 一棵 3 层的 B+ 树大约可以存两千万行数据
2. 也就是说，查两千万行的表，最多只需要 3 次磁盘 IO

两种索引

聚簇索引Clustered Index
1. 就是主键索引
2. InnoDB 中表数据本身就是按主键组织的 B+ 树，叶子节点存的是完整的行数据
3. 所以 InnoDB 的表本质上就是一棵 B+ 树
二级索引Secondary Index
1. 非主键索引
2. 叶子节点存的不是行数据，而是主键值
3. 通过二级索引查到主键后，还要回到聚簇索引去取完整数据，这个过程叫做回表

`Buffer Pool`（缓冲池）

磁盘 IO 是数据库最大的性能瓶颈，所以 InnoDB 在内存中维护了一个 Buffer Pool
工作原理
1. 读数据时，先看 Buffer Pool 里有没有对应的页，有就直接从内存读（快几个数量级）
  没有就从磁盘读进来放到 Buffer Pool 中
2. 写数据时也是先写 Buffer Pool 中的页（变成"脏页"），然后由后台线程异步刷到磁盘
Buffer Pool 用的是改良的 LRU（Least Recently Used）算法来管理页的淘汰
1. 为什么要"改良"？
2. 因为如果执行一个全表扫描，会一次性把大量冷数据加载进来，把真正的热数据挤出去
3. 所以 InnoDB 把 LRU 链表分成了年轻代和老年代两段
  新读入的页先放老年代
  只有在老年代待够一定时间后再次被访问才会晋升到年轻代
生产环境中 innodb_buffer_pool_size 一般设为物理内存的 60%~80%

日志系统

InnoDB 的可靠性和性能都靠日志系统支撑
redo log（重做日志）—— 崩溃恢复的保障
1. 问题：
  数据修改先写在 Buffer Pool 内存中，如果这时候 MySQL 崩溃了，内存中的脏页还没刷到磁盘，数据不就丢了？
2. 解决：
  每次修改数据时，先把"做了什么修改"写到 redo log 中（顺序写磁盘，极快），再修改 Buffer Pool
  这样即使崩溃，重启后也能从 redo log 中恢复
  这就是 WAL（Write-Ahead Logging，先写日志再写数据）机制
3. redo log 是 InnoDB 引擎层的日志，大小固定，循环写入（写满了就把对应的脏页刷盘腾出空间）
undo log（回滚日志）—— 事务回滚 + MVCC
1. 每次修改数据前，先把修改前的旧值记到 undo log 中
2. 作用有两个：
  事务回滚时，用 undo log 把数据恢复原样
  MVCC 中用来构造数据的历史版本，让不同事务能读到不同时刻的快照
binlog（归档日志）—— 主从复制 + 数据恢复
1. binlog 是 Server 层（不是 InnoDB 层）的日志，记录所有对数据有修改的 SQL
2. 它的作用是主从复制（从库通过 binlog 重放主库的操作）和数据恢复（配合备份 + binlog 可以恢复到任意时间点）
redo log 和 binlog 的两阶段提交：
1. 一次事务提交的流程：
2. 为什么要两阶段？
  为了保证 redo log 和 binlog 的一致性
  如果在第 2 步崩溃，重启后发现 redo log 是 prepare 但 binlog 没写完，就回滚
  如果 binlog 写完了但第 3 步没执行，就提交。这保证了主从数据一致

1. 写 redo log（prepare 状态）

2. 写 binlog

3. 将 redo log 改为 commit 状态

事务与 `MVCC`

事务的四大特性（ACID）：
1. 原子性（Atomicity）：靠 undo log 实现，要么全做要么全不做
2. 一致性（Consistency）：是最终目标，靠其他三个特性保证
3. 隔离性（Isolation）：靠锁和 MVCC 实现
4. 持久性（Durability）：靠 redo log 实现
MVCC（多版本并发控制）的核心原理：
1. 每一行数据有两个隐藏字段：trx_id（最后修改该行的事务 ID）和 roll_pointer（指向 undo log 中旧版本的指针）
  多次修改会形成一个版本链
2. 当一个事务执行 SELECT 时，InnoDB 会生成一个 Read View（读视图），里面记录了当前活跃的事务 ID 列表
  通过对比 trx_id 和 Read View，决定该行的哪个版本对当前事务"可见"
3. 这样读操作不用加锁，读写互不阻塞，大大提升了并发性能

锁机制

行锁：
1. InnoDB 默认的锁粒度，只锁住涉及的行
间隙锁（Gap Lock）：
1. 锁住索引记录之间的"间隙"，防止其他事务在间隙中插入新行，用于解决幻读
临键锁（Next-Key Lock）：
1. 行锁 + 间隙锁的组合，是 InnoDB 在可重复读隔离级别下的默认加锁方式
表锁：
1. 粒度大，MyISAM 用的就是表锁

其他

一条 `UPDATE` 语句的完整执行流程

在 InnoDB 中的执行过程：
1. 连接层：验证权限
2. 解析器：解析 SQL，生成解析树
3. 优化器：选择用主键索引查找
4. 执行器：调用 InnoDB 接口
5. InnoDB：在 Buffer Pool 中查找 id=1 的页，没有则从磁盘读入
6. 将修改前的旧值写入 undo log（用于回滚和 MVCC）
7. 在 Buffer Pool 中修改数据（此时数据页变为脏页）
8. 将修改操作写入 redo log（prepare 状态）
9. 将修改操作写入 binlog
10. 将 redo log 标记为 commit
11. 返回"更新成功"
12. 后台线程会在合适时机将脏页刷到磁盘

1	UPDATE users SET age = 26 WHERE id = 1;

学习

全连接和交叉连接(笛卡尔积)的区别

条件
1. 全连接有ON条件
2. 交叉连接没有条件
结果行数
1. 全连接：小于等于A+B
2. 交叉连接：AxB
NULL处理
1. 全连接：无匹配补NULL
2. 交叉连接：不涉及补NULL
用途
1. 全连接：保留两表所有记录
2. 交叉连接：生成所有组合

主键

举例
1. 去办事，工作人员要找你的档案。叫"张三"的人太多了，但你的身份证号全国唯一，一找一个准
特点
1. 唯一：不能有两行的主键值相同
2. 非空：不能为 NULL
3. 一张表只能有一个主键（但主键可以由多列组合）

CREATE TABLE 学生 (

学号 INT PRIMARY KEY, -- 主键

姓名 VARCHAR(20),

年龄 INT

);

作用
1. 唯一标识每一行，防止重复
2. 自动建索引，加速查询
  主键背后 MySQL 自动建了一棵 B+ 树索引，所以按主键查数据非常快

外键

举例
1. 学校有"学生表"和"班级表"。学生表里存了一个"班级ID"，这个 ID 指向班级表的主键
2. 这个"班级ID"就是外键
外键
1. 就是在 A 表里，存了一个指向 B 表主键的字段，用来建立两张表之间的关联

CREATE TABLE 班级 (

班级ID INT PRIMARY KEY,

班级名 VARCHAR(20)

);

CREATE TABLE 学生 (

学号 INT PRIMARY KEY,

姓名 VARCHAR(20),

班级ID INT,

FOREIGN KEY (班级ID) REFERENCES 班级(班级ID) -- 外键

);

作用
1. 外键的核心作用是数据完整性约束——防止出现"孤儿数据"
2. 插入时校验：你引用的数据必须存在
3. 删除时校验：被引用的数据不能随便删
主外键关联使用

班级表学生表课程表选课表

-------- -------- -------- --------

班级ID(PK) 学号(PK) 课程ID(PK) 选课ID(PK)

班级名姓名课程名学号(FK→学生)

班级ID(FK→班级) 课程ID(FK→课程)

-- 1. 先建"被引用"的表（父表）

CREATE TABLE 班级 (

班级ID INT PRIMARY KEY,

班级名 VARCHAR(20)

);

-- 2. 再建"引用别人"的表（子表）

CREATE TABLE 学生 (

学号 INT PRIMARY KEY,

姓名 VARCHAR(20),

班级ID INT,

FOREIGN KEY (班级ID) REFERENCES 班级(班级ID)

);

-- 3. 多对多关系用中间表

CREATE TABLE 选课 (

选课ID INT PRIMARY KEY,

学号 INT,

课程ID INT,

FOREIGN KEY (学号) REFERENCES 学生(学号),

FOREIGN KEY (课程ID) REFERENCES 课程(课程ID)

);

-- 查询每个学生的姓名和所在班级名

SELECT 学生.姓名, 班级.班级名

FROM 学生

JOIN 班级 ON 学生.班级ID = 班级.班级ID;

-- ↑ 子表外键 ↑ 父表主键

主键的底层原理

B+ 树索引
1. MySQL（InnoDB）的表数据本身就是按主键排好序、存在一棵 B+ 树里的（叫做"聚簇索引"）
2. 查 学号 = 1001：沿树走几步就找到，O(log n) 效率
3. 插入数据：按主键值插入树的对应位置，自动保持有序

[1001, 1050]

/ | \

[1001..1020] [...] [1040..1050]

主键为什么要求唯一且非空
1. 因为树的节点靠这个值来定位，重复或空值会让树无法正确工作

外键的原理

外键本质上是 MySQL 在增删改时自动执行的约束检查

插入子表一行

↓

MySQL 自动去父表查：这个外键值存在吗？

├── 存在 → 允许插入 ✅

└── 不存在 → 拒绝插入 ❌

删除父表一行

↓

MySQL 自动查：有子表引用这行吗？

├── 没有 → 允许删除 ✅

└── 有 → 取决于你设置的策略

外键还支持级联策略，控制父表被删/改时子表怎么处理：

FOREIGN KEY (班级ID) REFERENCES 班级(班级ID)

ON DELETE CASCADE -- 父表删了，子表跟着删

ON DELETE SET NULL -- 父表删了，子表该字段变NULL

ON DELETE RESTRICT -- 父表有子表引用，禁止删（默认）

索引

没有索引时会发生什么
1. MySQL 的做法：从第一行翻到最后一行，逐行比对
2. 这叫全表扫描
举例
1. 新华字典。想找"张"字，你不会从第1页翻到最后，而是先查目录（拼音索引），直接翻到对应页
2. 目录就是索引
索引的底层结构：B+ 树
1. InnoDB 引擎的索引，底层是一棵 B+ 树
2. 假设有8个数，普通查找最多比8次。但如果组织成二叉搜索树，只需3次。数据量越大，优势越明显。

/ \

2 6

/ \ / \

1 3 5 7

B+ 树长什么样?
1. 只有叶子节点存真实数据，上面的节点只存"路标"
2. 所有叶子节点用链表连接，方便范围查询
3. 树很矮（通常只有3-4层），查任何数据最多走3-4步

[30, 60] ← 根节点（只存键值，不存数据）

/ | \

[10,20] [40,50] [70,80] ← 中间节点（只存键值）

/ | \

[1-9] [10-19] [20-29] ... ← 叶子节点（存真实数据行）

↕ ↕ ↕

（叶子节点之间用链表串联）→→→→→→→→

为什么是 B+ 树而不是其他结构？


结构	问题
数组	插入/删除要移动大量数据，慢
普通二叉树	极端情况退化成链表，变成`O(n)`
哈希表	只能等值查询，不支持范围查询（`>`、`<`、`BETWEEN`）
`B` 树	每个节点都存数据，导致树更高，范围查询要回溯
`B+` 树	矮胖、叶子链表支持范围查询、非常适合磁盘`IO`

索引的常见类型
1. 复合索引的"最左前缀"原则
  复合索引 (姓名, 年龄) 的存储是先按姓名排序，姓名相同再按年龄排序

-- 普通索引

CREATE INDEX idx_name ON 学生(姓名);

-- 唯一索引（值不能重复）

CREATE UNIQUE INDEX idx_id_card ON 学生(身份证号);

-- 复合索引（多列组合）

CREATE INDEX idx_name_age ON 学生(姓名, 年龄);

-- 主键索引（建表时自动创建）

PRIMARY KEY (学号)

索引什么时候会失效？

-- ❌ 对索引列做函数运算

WHERE YEAR(create_time) = 2024 -- 应改为范围查询

-- ❌ 隐式类型转换（字段是varchar，传了int）

WHERE 手机号 = 13800138000 -- 应该加引号

-- ❌ LIKE 左模糊

WHERE 姓名 LIKE '%张' -- 左边不确定，无法走树

-- ✅ LIKE 右模糊可以走索引

WHERE 姓名 LIKE '张%'

-- ❌ OR 连接了没有索引的列

WHERE 姓名 = '张三' OR 地址 = '北京' -- 地址没索引，整体失效

两种核心索引

聚簇索引（主键索引）
1. 数据本身就按主键顺序存在 B+ 树的叶子节点里
  索引即数据，数据即索引
2. 每张表只有一棵聚簇索引（因为数据只能按一种顺序存）
3. 按主键查询，一次 B+ 树查找直接拿到完整数据行
非聚簇索引（二级索引）
1. 给非主键字段建的索引
2. 叶子节点存的不是完整数据，而是该字段值 + 对应的主键值

比如给"姓名"建索引：

叶子节点存：姓名 + 主键

[姓名=李四 | 学号=1002]

[姓名=王五 | 学号=1003]

[姓名=张三 | 学号=1001] ← 按姓名排序

SELECT * FROM 学生 WHERE 姓名 = '张三';

-- ①走姓名索引的 B+ 树 → 找到"张三"对应主键 = 1001

-- ②拿着 1001 再去主键索引的 B+ 树查一次 → 拿到完整数据行

这个第②步叫做"回表"

回表 vs 覆盖索引
1. 覆盖索引是一种重要的性能优化手段，能减少一次 B+ 树查找

1 2	-- 需要回表（SELECT * 要取所有字段） SELECT * FROM 学生 WHERE 姓名 = '张三';

-- 不需要回表（查询的字段索引里都有）

SELECT 学号, 姓名 FROM 学生 WHERE 姓名 = '张三';

-- 姓名索引叶子节点里已经有"学号+姓名"，直接返回，无需回表

-- 这就叫"覆盖索引"

视图

为什么需要视图？
1. 如果业务里有一个超高频查询——"查每个学生的姓名、班级名、所有课程成绩"
2. 这段 SQL 又长又复杂，每次用都要写一遍，而且每个同事写的可能还不一样

SELECT

s.姓名, c.班级名, co.课程名, sc.成绩

FROM 学生 s

JOIN 班级 c ON s.班级ID = c.班级ID

JOIN 选课 sc ON s.学号 = sc.学号

JOIN 课程 co ON sc.课程ID = co.课程ID

WHERE s.年龄 > 18;

视图就是为了解决这个问题
1. 类比：经常要去某个地方，每次都要输完整地址导航太麻烦。于是把这个地址保存成"收藏"，下次直接点收藏就行
2. 视图就是 SQL 查询的"收藏夹"
什么是视图？
1. 视图是一张虚拟表
2. 它本身不存数据，只是把一段 SELECT 语句存起来起了个名字，用的时候像查真实表一样去查它

-- 把复杂查询存成视图

CREATE VIEW 学生成绩单 AS

SELECT

s.姓名, c.班级名, co.课程名, sc.成绩

FROM 学生 s

JOIN 班级 c ON s.班级ID = c.班级ID

JOIN 选课 sc ON s.学号 = sc.学号

JOIN 课程 co ON sc.课程ID = co.课程ID

WHERE s.年龄 > 18;

-- 当成普通表查就行

SELECT * FROM 学生成绩单;

SELECT * FROM 学生成绩单 WHERE 姓名 = '张三';

SELECT * FROM 学生成绩单 WHERE 成绩 > 90;

视图是怎么起作用的？
1. 很多人以为视图是把查询结果"复制"了一份存起来，其实不是
2. 具体看下面视图工作流程
视图的工作流程：
1. 你查询视图
2. MySQL 把视图的定义（那段SELECT）取出来
3. 和你外层的查询合并，展开成一个完整SQL
4. 去真实的表里执行
5. 把结果返回给你

-- 你写的

SELECT * FROM 学生成绩单 WHERE 成绩 > 90;

-- MySQL 实际执行的（视图被展开）

SELECT s.姓名, c.班级名, co.课程名, sc.成绩

FROM 学生 s

JOIN 班级 c ON s.班级ID = c.班级ID

JOIN 选课 sc ON s.学号 = sc.学号

JOIN 课程 co ON sc.课程ID = co.课程ID

WHERE s.年龄 > 18 AND sc.成绩 > 90; ← 条件被合并进去了

视图能做什么？
1. 简化复杂查询
2. 数据权限控制
3. 数据格式加工
4. 兼容旧接口

-- 员工表有：姓名、工资、身份证号、部门

CREATE TABLE 员工 (

员工ID INT PRIMARY KEY,

姓名 VARCHAR(20),

工资 DECIMAL(10,2), -- 敏感！

身份证 VARCHAR(18), -- 敏感！

部门 VARCHAR(20)

);

-- 创建一个"脱敏视图"，只暴露安全字段

CREATE VIEW 员工公开信息 AS

SELECT 员工ID, 姓名, 部门

FROM 员工;

-- 普通员工只给他查视图的权限，看不到工资和身份证

GRANT SELECT ON 员工公开信息 TO '普通用户';

-- 把原始数据加工成更友好的格式

CREATE VIEW 订单概览 AS

SELECT

订单ID,

用户名,

FORMAT(金额, 2) AS 金额,

CASE 状态

WHEN 1 THEN '待付款'

WHEN 2 THEN '已付款'

WHEN 3 THEN '已发货'

END AS 订单状态,

DATE_FORMAT(创建时间, '%Y年%m月%d日') AS 下单日期

FROM 订单 JOIN 用户 ON 订单.用户ID = 用户.用户ID;

-- 旧表叫 user，被拆分成了 user_base 和 user_detail

-- 但老代码还在 SELECT * FROM user

-- 用视图模拟出一张"旧表"

CREATE VIEW user AS

SELECT b.id, b.name, b.email, d.phone, d.address

FROM user_base b

JOIN user_detail d ON b.id = d.user_id;

-- 老代码完全不用改，照样能跑

SELECT * FROM user WHERE id = 1;

视图能不能增删改？
1. 视图虽然是虚拟表，某些情况下可以对它进行写操作，MySQL 会把操作转到真实表上
2. 实际开发中，视图主要用来查，写操作直接操作原始表更清晰

-- 简单视图（来自单张表，没有聚合）可以UPDATE

UPDATE 员工公开信息 SET 姓名 = '李四' WHERE 员工ID = 1;

-- MySQL 实际去改的是员工表里的数据 ✅

-- ❌ 包含 JOIN（涉及多表，MySQL不知道改哪张）

-- ❌ 包含聚合函数 SUM/COUNT/AVG 等

-- ❌ 包含 DISTINCT、GROUP BY、HAVING

-- ❌ 包含子查询

视图的管理

-- 创建视图

CREATE VIEW 视图名 AS SELECT ...;

-- 修改视图

CREATE OR REPLACE VIEW 视图名 AS SELECT ...; -- 不存在就创建，存在就替换

-- 查看视图定义

SHOW CREATE VIEW 视图名;

-- 删除视图

DROP VIEW 视图名;

-- 查看当前数据库所有视图

SHOW FULL TABLES WHERE Table_type = 'VIEW';

事务

为什么需要事务？
1. 银行转账，张三给李四转 1000 元，需要两步：
2. 如果第①步执行完，服务器突然宕机，第②步没执行：
  张三：少了1000块
  李四：没收到钱
  1000块人间蒸发
3. 事务就是为了解决这个问题

1 2	UPDATE 账户 SET 余额 = 余额 - 1000 WHERE 姓名 = '张三'; -- 第①步 UPDATE 账户 SET 余额 = 余额 + 1000 WHERE 姓名 = '李四'; -- 第②步

类比：
1. 网购下单流程——扣库存、扣余额、生成订单，这三步要么全成功，要么全不做
2. 不能扣了钱却没生成订单
3. 事务就是给这一组操作套上一个"要么全做，要么全不做"的保险
什么是事务？
1. 事务是一组 SQL 操作的逻辑单元
2. 这组操作要么全部成功，要么全部回滚，不存在中间状态

-- 开启事务

START TRANSACTION;

UPDATE 账户 SET 余额 = 余额 - 1000 WHERE 姓名 = '张三';

UPDATE 账户 SET 余额 = 余额 + 1000 WHERE 姓名 = '李四';

-- 全部成功，提交

COMMIT;

-- 如果中途出错，回滚（撤销所有操作）

ROLLBACK;

加了事务之后：
1. 情况一：两步都成功 → COMMIT → 数据永久保存 ✅
2. 情况二：第①步后宕机 → 重启后自动ROLLBACK → 两步都没发生 ✅
3. 永远不会出现"只做了一半"的状态
事务的四大特性（ACID）
1. 原子性（Atomicity）
  事务里的操作，要么全做，要么全不做。不可分割，像原子一样
2. 一致性（Consistency）
  事务前后，数据必须符合业务规则，不能出现"不合理"的状态
3. 隔离性（Isolation）
  多个事务同时执行时，互相不干扰，每个事务感觉自己是"独占"数据库的
4. 持久性（Durability）
  事务一旦提交（COMMIT），数据就永久保存了。就算立刻断电，重启后数据还在
事务是怎么起作用的？
1. 回滚靠的是：Undo Log（撤销日志）
  每次修改数据前，MySQL 先把原来的值记录到 Undo Log：
2. 持久性靠的是：Redo Log（重做日志）
  COMMIT 时，MySQL 不是直接把数据写磁盘（太慢），而是先写 Redo Log
  把"做了什么操作"写入 Redo Log（速度极快，顺序写）
  返回"提交成功"给用户
  之后再找机会把数据刷入真正的数据文件

执行：UPDATE 账户 SET 余额 = 0 WHERE 姓名 = '张三'

MySQL先记日志：

Undo Log → "张三的余额原来是1000"

再改数据：

张三余额 → 0

如果需要ROLLBACK：

读取Undo Log → 把张三余额改回1000 ✅

事务隔离

概述
1. 多个事务并发执行时，隔离得"越严"，越安全，但性能越差。
2. MySQL 提供四个级别：
先认识三种并发问题：
1. 脏读：读到了另一个事务还没提交的数据
2. 不可重复读：同一事务里，两次读同一行，结果不一样
3. 幻读：同一事务里，两次查询，第二次多出（或少了）几行

事务A：把张三余额改成0（还没提交）

事务B：读到张三余额=0 ← 脏数据！

事务A：ROLLBACK，张三余额恢复1000

事务B：拿着错误的0去做后续操作 💥

事务B：第一次读张三余额=1000

事务A：把张三余额改成0并提交

事务B：第二次读张三余额=0 ← 同一事务里读到不同值！

事务B：查询余额>500的账户，得到3条

事务A：新插入一个余额=800的账户并提交

事务B：再查余额>500的账户，得到4条 ← 多出来一行！

四个隔离级别
1. MySQL 默认是 REPEATABLE READ，通过 MVCC 机制基本解决了幻读问题，是性能和安全的最佳平衡点


隔离级别	脏读	不可重复读	幻读	性能
`READ UNCOMMITTED`（读未提交）	❌有	❌有	❌有	最快
`READ COMMITTED`（读已提交）	✅无	❌有	❌有	较快
`REPEATABLE READ`（可重复读）	✅无	✅无	⚠️基本无	适中
`SERIALIZABLE`（串行化）	✅无	✅无	✅无	最慢

-- 查看当前隔离级别

SELECT @@transaction_isolation;

-- 修改隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

隔离性的底层原理：`MVCC`

概述
1. MVCC（多版本并发控制）是 MySQL 实现隔离性的核心机制，让读写操作互不阻塞
核心思想：
1. 不同时刻的读，看到的是数据的不同"历史快照"
每行数据背后，MySQL 隐藏了两个字段
1. trx_id：最后修改这行的事务ID
2. roll_pointer：指向 Undo Log 里的历史版本
比如张三的账户数据，历史版本链：
1. 事务开始时，生成一个快照（ReadView），记录"此刻哪些事务还没提交"
2. 读数据时，沿着版本链找到对自己可见的那个版本
3. 事务B（ID=150）开启快照：我能看到ID≤150的已提交数据
4. 事务A（ID=200）把余额改成0，但还没提交
5. 事务B读张三余额：
  → 看到余额=0，是事务200改的，200>150，对我不可见
  → 往历史版本找
  → 看到余额=1000，是事务100改的，100<150且已提交，可见！
  → 返回1000 ✅
6. 事务B读到的是干净的历史快照，完全不受事务A影响
7. 类比：git 的提交历史。你 checkout 到某个历史 commit，看到的是那个时间点的代码，别人在 main 分支上改什么你都看不到

当前版本：余额=0 （事务ID=200修改的）

↓

历史版本：余额=1000 （事务ID=100修改的）

↓

更早版本：余额=500 （事务ID=50修改的）

事务的实际使用

手动事务

START TRANSACTION;

UPDATE 账户 SET 余额 = 余额 - 1000 WHERE 姓名 = '张三';

-- 检查张三余额是否足够

-- 假设用程序判断，余额不够就回滚

IF 余额 < 0 THEN

ROLLBACK; -- 撤销

ELSE

UPDATE 账户 SET 余额 = 余额 + 1000 WHERE 姓名 = '李四';

COMMIT; -- 提交

END IF;

保存点（部分回滚）

START TRANSACTION;

UPDATE 账户 SET 余额 = 余额 - 1000 WHERE 姓名 = '张三';

SAVEPOINT step1; -- 设置保存点

UPDATE 账户 SET 余额 = 余额 + 1000 WHERE 姓名 = '李四';

-- 假设这步出错了

ROLLBACK TO step1; -- 只回滚到保存点，第①步保留

-- 可以重试第②步，不用从头来

注意：MySQL 默认自动提交

-- MySQL 默认每条SQL都是一个独立事务，自动提交

UPDATE 账户 SET 余额 = 0 WHERE 姓名 = '张三';

-- 执行完立刻提交，无法回滚！

-- 关闭自动提交

SET autocommit = 0;

-- 此后需要手动 COMMIT 或 ROLLBACK

概述
数据类型
整数类
字符串类
时间类
浮点类
数据库和表的管理
常规操作
关键约束
增
删
改
查
SQL 执行顺序
实用小技巧
MySQL 整体架构
概述
连接层
服务层
存储引擎层
完整流程
InnoDB 存储引擎原理
概述
数据结构
索引结构：B+ 树
两种索引
Buffer Pool（缓冲池）
日志系统
事务与 MVCC
锁机制
其他
一条 UPDATE 语句的完整执行流程
学习
全连接和交叉连接(笛卡尔积)的区别
主键
外键
主键的底层原理
外键的原理
索引
两种核心索引
视图
事务
事务隔离
隔离性的底层原理：MVCC
事务的实际使用

声明：本文为原创文章，版权归Aet所有，欢迎分享本文，转载请保留出处！

Bingliaolong 关注：0 粉丝：0 最后编辑于：2026-03-18

Everything will be better.

Mysql：总结一

概述

数据类型

整数类

字符串类

时间类

浮点类

数据库和表的管理

常规操作

关键约束

增

删

改

查

SQL 执行顺序

实用小技巧

MySQL 整体架构

概述

连接层

服务层

存储引擎层

完整流程

InnoDB 存储引擎原理

概述

数据结构

索引结构：B+ 树

两种索引

Buffer Pool（缓冲池）

日志系统

事务与 MVCC

锁机制

其他

一条 UPDATE 语句的完整执行流程

学习

全连接和交叉连接(笛卡尔积)的区别

主键

外键

主键的底层原理

外键的原理

索引

两种核心索引

视图

事务

事务隔离

隔离性的底层原理：MVCC

事务的实际使用

你可能也喜欢

热评文章

发表评论

标签云集

`SQL` 执行顺序

`MySQL` 整体架构

`InnoDB` 存储引擎原理

索引结构：`B+` 树

`Buffer Pool`（缓冲池）

事务与 `MVCC`

一条 `UPDATE` 语句的完整执行流程

隔离性的底层原理：`MVCC`