内容概览

模块	核心内容
01 别名与视图	表别名、列别名、视图创建与应用
02 存储引擎	InnoDB vs MyISAM、独立表空间迁移实战
03 事务机制	ACID特性、隔离级别、脏读/不可重复读/幻读

---

01 数据库表的别名与视图功能

1.1 表别名（Table Alias）

作用：简化表名，特别是在多表连接查询时

-- 不使用别名
select student.sno, student.sname, avg(sc.score)
from sc
join student on sc.sno = student.sno
where sc.score > 60
group by student.sno;

-- 使用别名（a = sc, b = student）
select b.sno, b.sname, avg(a.score)
from sc a
join student b on a.sno = b.sno
where a.score > 60
group by b.sno;

1.2 列别名（Column Alias）

作用：让查询结果的列名更清晰、易读

select 
    b.sno as '学号',
    b.sname as '学生名',
    avg(a.score) as '大于60分学生的平均成绩'
from sc a
join student b on a.sno = b.sno
where a.score > 60
group by b.sno;

---

1.3 视图（View）

作用：将复杂的多表查询封装为"虚拟表"，简化后续查询

-- 创建视图：整合学生、成绩、课程、老师信息
create view oldboy as 
select 
    student.sno, student.sname, student.sage, student.ssex,
    sc.cno, sc.score,
    course.cname, course.tno,
    teacher.tname
from student
join sc on student.sno = sc.sno
join course on sc.cno = course.cno
join teacher on course.tno = teacher.tno;

-- 使用视图查询（就像查普通表一样简单）
-- 查询每门课的最高分和最低分
select cno, cname, max(score), min(score)
from oldboy
group by cno;

视图优点	说明
简化查询	复杂连接一次定义，多次使用
逻辑封装	隐藏底层表结构
安全性	可限制用户访问特定列

注意：视图不存储数据，本质是保存的SQL语句

---

02 数据库存储引擎知识

2.1 什么是存储引擎？

存储引擎是MySQL中负责管理数据存储和读取的底层组件，决定了数据的存储方式、并发能力、事务支持等。

存储引擎决定了数据如何存储、如何索引、以及支持哪些功能（如事务、外键、行级锁等）。

面试重点：一条SQL语句的执行过程（参见QQ群图）
客户端 → 连接器 → 查询缓存 → 解析器 → 优化器 → 执行器 → 存储引擎 → 磁盘

---

2.2 常见存储引擎对比

特性	InnoDB（默认，5.6+）	MyISAM（早期默认）
事务	✅ 支持（ACID）	❌ 不支持
锁粒度	行级锁（高并发写入）	表级锁（适合读多写少）
外键	✅ 支持	❌ 不支持
MVCC	✅ 支持（快照读）	❌ 不支持
灾难恢复	✅ CR机制	❌ 较弱
索引类型	聚簇索引（高效查询）	非聚簇索引
备份影响	热备份（业务无感）	全局锁（影响业务）

修改存储引擎

-- 建表时指定
create table t1 (id int) engine = 'MEMORY';

-- 修改已有表
alter table t1 engine = 'MyISAM';

-- 查看所有可用引擎
show engines;

---

2.3 独立表空间迁移（InnoDB）

适用场景：数据库故障无备份、无主从，但原始的 .ibd 文件还在

文件	内容
.frm / .sdi（8.0）	表结构
.ibd	表数据 + 索引

迁移步骤

# 步骤1：备份表结构
mysqldump -uroot -p123456 -B oldboy --no-data > /backup/backup.sql

# 步骤2：模拟故障（略）

# 步骤3：搭建新实例
mkdir -p /data/3307/data && chown -R mysql.mysql /data/3307/
# 配置 /etc/my.cnf 指向新目录 /data/3307/data
mysqld --defaults-file=/etc/my.cnf --initialize-insecure
mysqld --defaults-file=/etc/my.cnf &

# 步骤4：恢复表结构
mysql -uroot -S /tmp/mysql.sock < /backup/backup.sql

# 步骤5：导入数据
use oldboy;
alter table t100w discard tablespace;        # 删除新表的.ibd
cp -a /data/3306/data/oldboy/t100w.ibd /data/3307/data/oldboy/  # 复制旧数据文件
alter table t100w import tablespace;         # 导入数据

原理：InnoDB中每张表的 t1.ibd 是独立存储的，可以直接复制

---

03 数据库事务知识

3.1 什么是事务？

事务是一组DML操作的逻辑单元，要么全部成功，要么全部失败，保证数据安全。

官方文档：MySQL ACID

---

3.2 事务的触发方式

-- 查看当前模式
select @@autocommit;

模式	autocommit值	说明
自动提交	1	每条DML自动提交，无需手动commit
手动提交	0	需begin开始，commit结束

-- 手动事务示例
begin;
insert into t1 values (1, 'xiaoA');
update t1 set name = 'xiaoB' where id = 1;
commit;   -- 或 rollback 回滚

---

3.3 事务四大特性（ACID）—— 面试重点

特性	作用	实现机制
原子性（Atomicity）	事务中操作要么全成功，要么全失败	undo log（回滚日志）
一致性（Consistency）	数据库异常重启后数据一致	redo log（重做日志）
隔离性（Isolation）	并发事务间互不干扰	隔离级别 + 锁 + MVCC
持久性（Durability）	事务提交后数据永久保存	双写缓冲 + redo log

经典案例：银行转账

begin;
update 账户表 set 余额 = 余额 - 50 where 账户 = 'A';
update 账户表 set 余额 = 余额 + 50 where 账户 = 'B';
commit;

• 原子性：两条update要么都成功，要么都失败
• 一致性：转账前后总金额不变（100 → 100）
• 隔离性：其他事务看不到转账过程中的中间状态
• 持久性：commit后数据永久保存

---

3.4 隔离级别与并发问题 —— 面试重点

三种并发读问题

问题	定义	示例
脏读	读到其他事务未提交的数据	读到A还没转账成功的余额
不可重复读	同一事务内两次读取结果不一致	第一次读到100，第二次读到50
幻读	批量操作时，新增数据影响结果	统计100人，操作时又插入1人

四种隔离级别

级别	脏读	不可重复读	幻读	并发能力	实现方式	生产使用
RU（读未提交）	✅	✅	✅	最高	无锁	❌
RC（读已提交）	❌	✅	✅	较高	快照读	✅ 推荐
RR（可重复读）	❌	❌	❌	中等	临键锁 + MVCC	✅ MySQL默认
SR（可串行化）	❌	❌	❌	最低	表级锁	❌ 性能差

设置隔离级别

-- 查看当前级别
select @@transaction_isolation;

-- 设置级别（需重新连接生效）
set global transaction_isolation = 'READ-COMMITTED';
set global transaction_isolation = 'REPEATABLE-READ';

为什么MySQL默认RR，但很多公司用RC？

维度	RR（默认）	RC
优点	避免幻读，数据一致性高	并发性能好，锁竞争少
缺点	临键锁有一定性能损耗	可能出现不可重复读
适用	金融、对数据一致性要求极高	互联网高并发业务

生产建议：业务允许少量不可重复读时，推荐RC提升并发性能