MySQL进阶：存储引擎｜事务｜索引｜视图｜三范式，一篇吃透面试高频考点

MySQL进阶：存储引擎｜事务｜索引｜视图｜三范式，一篇吃透面试高频考点

前面我们学完MySQL建表、增删改、单表、多表JOIN查询，这些属于CRUD基础。但想真正胜任开发岗位、搞定面试，还需要掌握进阶核心内容

一、存储引擎：数据表的底层存储机制

1.1 什么是存储引擎

存储引擎是MySQL用来存储、读取数据的底层机制。MySQL最大的特点：同一个数据库，不同的表可以使用不一样的存储引擎。

查看当前数据库支持哪些存储引擎：

SHOW ENGINES;

查看当前数据库默认存储引擎：

SHOW VARIABLES LIKE '%default_storage_engine%';

常见引擎：InnoDB、MyISAM、MEMORY、ARCHIVE、CSV。

MySQL5.5之后InnoDB是默认存储引擎。

1.2 InnoDB vs MyISAM 核心对比（面试高频）

对比项	InnoDB	MyISAM
事务支持	✅ 支持事务	❌ 不支持事务
外键约束	✅ 支持外键	❌ 不支持外键
锁机制	行级锁，并发性能高	表级锁，增删改锁住整张表，并发差
索引类型	聚集索引，叶子节点存放真实数据	非聚集索引，叶子节点存放数据指针
count(*)统计	不会缓存总行数，count(*)需要全表扫描，速度慢	缓存表总行数，count(*)速度很快

开发选型建议：

1. 业务系统优先选择 InnoDB，支持事务、行锁，适合绝大多数业务；
2. MyISAM适合只读、查询多几乎不修改的静态历史数据表，新项目不推荐使用MyISAM。

设置表的存储引擎语法：

-- 创建表时指定引擎
CREATE TABLE test(
    id INT PRIMARY KEY
) ENGINE=InnoDB;

-- 修改已有表存储引擎
ALTER TABLE test ENGINE=MyISAM;

二、事务：保证数据不会错乱

2.1 什么是事务

事务是一组DML操作的集合，是不可分割最小工作单元。这一组操作，要么全部执行成功，要么全部执行失败回滚到原始状态。

最经典场景：转账。

A账户扣520元，B账户加520元。
如果扣钱成功，加钱代码中途崩溃，就会出现A钱少了，B钱没到账，数据错乱。事务就是用来解决这个问题。

2.2 事务基础语法

• BEGIN / START TRANSACTION：开启事务
• COMMIT：提交事务，所有修改永久生效
• ROLLBACK：回滚事务，撤销本次所有修改，恢复数据

-- 1.开启事务
START TRANSACTION;

-- 2.执行多条修改操作
UPDATE account SET balance = balance - 520 WHERE name='你';
UPDATE account SET balance = balance + 520 WHERE name='老婆';

-- 3.全部没问题就提交；出现异常执行 rollback回滚
COMMIT;
-- ROLLBACK;

2.3 隐式事务 & 显式事务

1. 隐式事务：默认状态，DML（insert/update/delete）自动提交，执行一条就立刻持久化，没有手动回滚机会。由参数 autocommit 控制。
2. 显式事务：手动start transaction开启，需要手动commit或者rollback。

查看自动提交状态：

SELECT @@autocommit;
-- 1=开启自动提交；0=关闭自动提交

关闭自动提交：

SET autocommit = 0;

2.4 事务四大特性 ACID（必背面试题）

1. 原子性（Atomicity）：事务是最小不可分割单元，操作要么全部成功，要么全部失败回滚。
2. 一致性（Consistency）：事务执行前后，整体数据状态保持合理一致（转账总金额不变）。
3. 隔离性（Isolation）：多个并发事务之间互相隔离，互不干扰。
4. 持久性（Durability）：事务一旦commit提交成功，对数据的改变是永久保存，宕机也不会丢失。

记忆小口诀：原一隔持（原子、一致、隔离、持久）。

三、索引：让查询速度起飞

3.1 索引是什么

类比：新华字典的目录。没有目录需要一页一页全表翻；有目录直接定位页码。

官方定义：索引是帮助MySQL高效获取数据的数据结构。

• 优点：极大提升查询速度；
• 缺点：占用磁盘空间；插入、更新、删除的时候需要维护索引，会降低写性能。

小表不需要建索引，数据量大索引才发挥价值。

无索引：执行select * from user where age=45，全表扫描逐行比对；
有索引：通过B+树索引快速定位数据，不需要遍历全部数据。

3.2 索引的分类

按字段特性划分（开发最常用）

1. 主键索引 PRIMARY KEY：主键自带索引，一张表只能一个；字段不能为null，不能重复。
2. 唯一索引 UNIQUE INDEX：索引字段不能重复，可以为NULL；一张表可以多个唯一索引。
3. 普通索引 INDEX：最基础索引，允许重复，允许null。
4. 全文索引 FULLTEXT：针对长文本，做关键词检索，char/varchar/text字段可用。

其他分类：

• 数据结构：B+Tree索引、Hash索引、全文索引；
• 物理存储：聚集索引、非聚集索引；
• 字段数量：单列索引、联合（复合）索引。

3.3 索引增删改查SQL

-- 查看一张表全部索引
SHOW INDEX FROM user;

-- 1.普通索引 创建
CREATE INDEX idx_user_age ON user(age);

-- 2.唯一索引
CREATE UNIQUE INDEX idx_user_phone ON user(phone);

-- 3.联合索引（多字段一起建索引）
CREATE INDEX idx_user_name_age ON user(name,age);

-- 4.全文索引
CREATE FULLTEXT INDEX idx_user_desc ON user(description);

-- 删除索引
DROP INDEX idx_user_age ON user;

-- 删除主键索引
ALTER TABLE user DROP PRIMARY KEY;

3.4 索引使用原则（避坑重点）

1. 只为经常where、join、order by的字段建立索引，很少查询的字段不要建索引；
2. 数据量很小的表，不要建索引，索引维护开销大于收益；
3. 区分度很低字段不适合建索引，例如性别（只有男/女），大量重复值，索引几乎不起作用；
4. 索引不是越多越好，索引会拖慢insert update delete性能。

误区：建了索引查询就一定快，写法不对索引会失效。

四、视图View：封装SQL，简化查询

4.1 什么是视图

视图是虚拟表，本身不存储真实数据，本质就是一段被保存好的SELECT查询语句。访问视图的时候，才会执行底层SQL，从物理真实表拿到结果。

两大作用：

1. 简化复杂SQL：经常写的多表关联、复杂查询，封装成视图，后续直接select * from 视图，不用重复写一大串SQL；
2. 数据权限控制，隐藏敏感字段：比如员工工资不让普通用户看到，可以创建视图只开放姓名、岗位，屏蔽薪资列。

4.2 视图常用操作SQL

-- 创建视图
CREATE VIEW emp_simple_view AS
SELECT empno,empname,job,deptno FROM emp;

-- 查询视图，像查询普通表一样
SELECT * FROM emp_simple_view;

-- 修改视图
ALTER VIEW emp_simple_view AS
SELECT empno,empname,job FROM emp;

-- 删除视图
DROP VIEW emp_simple_view;

-- 查看视图结构
DESC emp_simple_view;

注意：视图只是查询的封装，不要通过视图做大量增删改，存在很多限制，业务写操作优先操作原始物理表。

五、ER模型与三大范式3NF：设计合理数据表

5.1 ER模型（实体‑关系模型）

ER模型用来做数据库前期设计，把现实业务抽象成数据库表。三个核心要素：

1. 实体（Entity）：现实业务对象，矩形表示，对应数据库一张表。例：学生、课程、订单。
2. 属性（Attribute）：实体的特征，椭圆表示，对应表的字段。例：学生的学号、姓名。
3. 关系（Relationship）：实体和实体之间的联系，菱形表示。一对多、一对一、多对多。

ER图转数据表规则：

1. 一个实体 → 一张数据表；属性 → 表字段；
2. 一对多关系：在多方增加外键，引用一方主键；
3. 多对多关系：必须新建一张中间关系表，保存两边实体主键。

例子：学生 和课程多对多 → 创建选课中间表(学号，课程号)。

5.2 三大范式3NF

范式是数据表设计规范，目的：减少数据冗余，避免数据更新异常。范式等级越高，冗余越少。日常开发重点掌握1NF、2NF、3NF。

✅第一范式 1NF：列原子性

每一列数据不可再拆分，不能一个字段存多个信息。

❌ 反例：address字段存储湖北省-武汉市-武昌区，信息混合在一列。
✅ 优化：拆分为 province、city、district三个独立字段。

✅第二范式 2NF

满足1NF前提下：

1. 表要有主键；
2. 消除部分函数依赖，非主键字段必须完全依赖全部主键，不能只依赖复合主键其中一部分。

❌反例：选课表studentid,courseid,coursename，复合主键（studentid+courseid），课程名字只依赖courseid，属于部分依赖。
✅优化：拆分为选课表（studentid,courseid）、课程表(courseid,coursename)。

✅第三范式 3NF

满足2NF前提下：消除传递依赖。非主键字段不能依赖另外一个非主键字段，所有非主键列直接依赖主键。

❌反例员工表 eid,ename,dept,dept_location。部门地址依赖dept，dept依赖主键eid，形成传递依赖。
✅优化：拆员工表（eid,ename,dept_id），部门表(dept_id,dept,dept_location)。

开发小提示：范式不是教条，为了查询性能，业务中允许适度反范式冗余，不要一味追求最高范式。

六、避坑总结

1. InnoDB为什么适合业务系统？
   支持事务、行锁、外键，并发能力强；MyISAM不支持事务，新项目慎用。
2. ACID分别是什么？
   原子性、一致性、隔离性、持久性，转账案例要能口述。
3. 索引是不是建越多越好？
   不是，索引会消耗磁盘，降低写操作性能；小表、低区分度字段不适合建索引。
4. 视图会保存真实数据吗？
   不会，视图只是封装查询语句，数据仍然来自原始物理表。
5. 三大范式核心：1NF列不可拆分；2NF消除部分依赖；3NF消除传递依赖。
6. 多对多ER设计：一定要建立中间关系表，不能直接在某一张表里存一堆id。

面试的时候大家被问过哪些MySQL进阶问题？欢迎评论区留言交流。

转载自 CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/Zyzui/article/details/166691865