MySQL 数据库核心知识详解

一、MySQL 简介

MySQL 是目前应用最广泛的关系型数据库之一（RDBMS），主要用于存储结构化数据。

MySQL 基于：

• SQL（Structured Query Language）
• 关系模型
• 事务机制
• 索引结构

实现高效的数据管理。

常见应用：

• 用户系统
• 电商系统
• 内容管理系统
• 企业业务系统
• 后端服务数据存储

二、数据库表结构设计

数据库设计是业务系统开发中非常重要的一部分。

优秀的表结构设计需要考虑：

• 数据存储效率
• 数据一致性
• 查询性能
• 后期扩展能力

1. 表设计基本原则

1.1 一个表描述一个业务实体

例如：

用户表：

1
user

负责存储用户信息：

不要把多个业务混在一张表中。

错误设计：

1
用户表
2

3
用户信息
4
订单信息
5
商品信息
6
地址信息

这样会导致：

• 数据冗余
• 更新困难
• 查询复杂

三、字段类型选择

MySQL 提供多种数据类型：

• 数值类型
• 字符串类型
• 时间类型

合理选择字段类型可以减少存储空间，提高查询效率。

1. 数值类型

INT

常用于：

• 主键 ID
• 数量
• 状态值

例如：

1
id INT

BIGINT

适用于：

数据量较大的系统。

例如：

1
用户ID BIGINT

大型互联网项目通常使用 BIGINT。

2. 字符串类型

VARCHAR

最常用字符串类型。

例如：

1
username VARCHAR(50)

特点：

• 可变长度
• 节省空间
• 查询效率较高

适合：

• 用户名
• 邮箱
• 地址

CHAR

固定长度字符串。

例如：

1
gender CHAR(1)

适合：

固定长度数据：

• 性别
• 状态码

3. 时间类型

常见：

例如：

1
create_time DATETIME

用于记录：

• 创建时间
• 更新时间

四、主键与外键设计

1. 主键（Primary Key）

主键：

唯一标识一条记录的字段。

例如：

用户表：

1
CREATE TABLE user(
2

3
 id BIGINT PRIMARY KEY,
4

5
 username VARCHAR(50)
6

7
);

特点：

• 唯一
• 非空
• 查询效率高

2. 外键（Foreign Key）

外键用于维护表之间的关系。

例如：

订单表：

1
user表
2

3
id
4
1
5

6
↓
7

8
order表
9

10
user_id
11
1

订单属于某个用户。

关系：

1
用户 1 -------- N 订单

外键作用：

• 保证数据一致性
• 防止无效数据

例如：

不能存在：

1
订单 user_id=999
2

3
但是用户表不存在999

五、数据库范式设计

范式（Normal Form）用于减少数据冗余，提高数据一致性。

1. 第一范式（1NF）

要求：

字段必须是不可再分的数据。

错误：

用户表：

name 包含多个值。

正确：

拆分：

2. 第二范式（2NF）

要求：

满足第一范式，并且：

非主键字段完全依赖主键。

例如：

订单商品表：

错误：

商品名称依赖商品ID。

应该拆分：

订单表：

1
order
2

3
id

商品表：

1
product
2

3
id
4
name

3. 第三范式（3NF）

要求：

非主键字段不能依赖其他非主键字段。

例如：

用户表：

错误：

城市编号依赖城市。

应该拆分：

用户表：

1
id
2
city_id

城市表：

1
id
2
name

六、反范式设计

虽然范式可以减少冗余，但是：

过度拆分会导致：

• 查询需要大量 JOIN
• 查询性能下降

因此实际开发中会采用：

适度反范式设计

例如：

订单表：

严格范式：

1
order
2

3
user_id

查询订单需要：

1
order
2

3
JOIN
4

5
user

反范式：

直接保存：

1
order
2

3
user_id
4
username

减少 JOIN。

适用场景：

• 查询频繁
• 数据变化少
• 对性能要求高

例如：

电商订单：

保存：

1
订单创建时用户名

即使用户修改用户名：

历史订单仍保持原信息。

七、MySQL 索引优化

索引类似：

数据库中的目录。

没有索引：

1
逐行扫描

有索引：

1
快速定位数据

1. 索引类型

普通索引

1
CREATE INDEX idx_name
2
ON user(name);

唯一索引

保证字段唯一：

1
CREATE UNIQUE INDEX idx_email
2
ON user(email);

主键索引

主键自动创建索引：

1
PRIMARY KEY(id)

2. 索引底层结构

MySQL InnoDB 默认使用：

1
B+Tree

结构：

1
        根节点
2

3
          ↓
4

5
      中间节点
6

7
          ↓
8

9
      叶子节点

优势：

• 查询效率高
• 范围查询快
• 支持排序

3. 索引失效情况

1. 使用函数

例如：

1
WHERE YEAR(create_time)=2024

可能导致索引失效。

2. 使用 LIKE 左模糊查询

错误：

1
LIKE '%abc'

原因：

无法定位索引起点。

3. 字段类型隐式转换

例如：

字段：

1
id VARCHAR

查询：

1
WHERE id=123

可能导致索引失效。

八、慢查询分析

慢查询：

执行时间超过指定阈值的 SQL。

1. 开启慢查询日志

配置：

1
slow_query_log=ON

设置时间：

1
long_query_time=2

表示：

超过2秒记录。

2. 使用 EXPLAIN 分析 SQL

例如：

1
EXPLAIN
2
SELECT *
3
FROM user
4
WHERE username='Tom';

重点关注：

九、MySQL 事务 ACID

事务：

一组操作，要么全部成功，要么全部失败。

例如转账：

1
A账户减少100
2

3
↓
4

5
B账户增加100

必须保证一致。

ACID 四大特性

1. Atomicity 原子性

事务不可分割：

成功：

1
全部执行

失败：

1
全部回滚

2. Consistency 一致性

事务前后：

数据保持合法状态。

例如：

转账：

1
总金额不变

3. Isolation 隔离性

多个事务同时执行：

互不影响。

4. Durability 持久性

事务提交后：

数据永久保存。

十、事务隔离级别

MySQL 有四种隔离级别：

1. Read Uncommitted

读取未提交数据。

问题：

可能产生：

1
脏读

2. Read Committed

只能读取提交的数据。

解决：

1
脏读

Oracle 默认级别。

3. Repeatable Read

可重复读。

MySQL InnoDB 默认隔离级别。

解决：

• 脏读
• 不可重复读

通过：

MVCC

实现。

4. Serializable

最高隔离级别。

特点：

• 强一致
• 性能最低

通过加锁实现。

十一、MVCC 多版本并发控制

MySQL InnoDB 使用：

1
MVCC

实现高性能事务。

核心：

• Undo Log
• Read View
• 隐藏字段

作用：

让读操作不用加锁。

提高并发能力。

十二、MySQL 面试重点总结

必须掌握

表设计

• 字段类型选择
• 主键设计
• 外键关系
• 一对多、多对多设计

数据库设计

• 三大范式
• 反范式应用场景

索引

• B+Tree
• 索引优化
• 索引失效

SQL优化

• 慢查询分析
• EXPLAIN

事务

• ACID
• 隔离级别
• MVCC

面试版回答

如果面试官问：

你对 MySQL 有哪些了解？

可以回答：

MySQL 是常用的关系型数据库。在项目开发中，我会根据业务进行表结构设计，包括字段类型选择、主键和外键设计，同时遵循三范式减少数据冗余，并根据查询场景进行适度反范式优化。在性能方面，会通过合理建立索引、使用 EXPLAIN 分析 SQL 执行计划、定位慢查询问题进行优化。另外熟悉 MySQL 事务 ACID 特性以及 InnoDB 的事务隔离级别和 MVCC 机制，能够保证数据一致性和并发访问性能。

总结

MySQL 学习重点：

1
表设计
2
  ↓
3
字段类型
4
  ↓
5
主外键关系
6
  ↓
7
范式设计
8
  ↓
9
索引优化
10
  ↓
11
慢查询分析
12
  ↓
13
事务ACID
14
  ↓
15
隔离级别
16
  ↓
17
MVCC

掌握这些内容，基本覆盖后端开发中 MySQL 的核心知识点。