note4cs/技术/MySQL/基础.md

1. 关系型数据库和非关系型数据库的区别

对比维度	关系型数据库	非关系型数据库
数据存储方式	数据以二维表格形式存储，结构化组织，强调行和列的关系 。	存储方式多样，如键值对、文档（JSON）、列族或图结构，适合非结构化或半结构化数据 。
数据模型	基于关系模型，强调数据的一致性和完整性 。	数据模型灵活，支持分布式架构，适合动态变化的数据需求 。
事务特性	遵循ACID原则（原子性、一致性、隔离性、持久性），确保强一致性 。	基于CAP理论（一致性、可用性、分区容错性），通常牺牲部分一致性以换取高可用性和扩展性 。
扩展性	通常采用垂直扩展（增加硬件性能），扩展性有限 。	支持水平扩展（增加节点），适合大规模分布式系统，扩展性更强 。
查询语言	使用SQL（结构化查询语言），通用性强且易于理解 。	通常使用特定API或查询语言，灵活性更高但学习成本较大 。
适用场景	适合需要复杂查询、事务处理和强一致性的场景，如银行系统、ERP等 。	适合大数据、高并发、实时性要求高的场景，如社交网络、物联网等 。

数据库设计三大范式

数据库设计的三大范式是关系型数据库设计中用于减少数据冗余、提高数据一致性和可维护性的基本原则。以下是三大范式的具体定义和要求：

1. 第一范式（1NF）

第一范式要求确保表中的每列都具有原子性，即字段值不可再分解。换句话说，每一列的值必须是单一的、不可分割的基本数据项，不能包含多个值或复杂结构。

• 目标：消除重复组或多值字段。
• 示例：在一个学生信息表中，如果“联系方式”列存储了多个电话号码，则违反了第一范式。应该将其拆分为单独的行或表以满足原子性。

---

2. 第二范式（2NF）

第二范式建立在第一范式的基础上，要求表中的每一列都必须完全依赖于主键，而不能仅仅依赖于主键的一部分（即消除部分依赖）。这意味着当表的主键是由多个列组成的复合主键时，非主键列必须与整个主键相关联。

• 目标：消除部分依赖。
• 示例：假设有一个订单明细表，主键由“订单ID”和“商品ID”组成。如果表中还存在“客户姓名”这样的列，它只依赖于“订单ID”，而不依赖于“商品ID”，这就违反了第二范式。应将“客户姓名”移到另一个表中。

---

3. 第三范式（3NF）

第三范式建立在第二范式的基础上，要求表中的每一列都必须直接依赖于主键，而不能通过其他非主键列间接依赖于主键（即消除传递依赖）。

• 目标：消除间接依赖。
• 示例：在一个员工信息表中，如果存在“部门ID”和“部门名称”两列，“部门名称”依赖于“部门ID”，而不是直接依赖于主键（如“员工ID”），这就违反了第三范式。应将“部门名称”移到一个独立的部门表中。

---

总结

• 第一范式（1NF）：确保每列保持原子性，字段值不可再分。
• 第二范式（2NF）：确保每列完全依赖于主键，消除部分依赖。
• 第三范式（3NF）：确保每列直接依赖于主键，消除传递依赖。

遵循这三大范式可以有效地减少数据冗余，提高数据库的规范性和一致性。不过，在实际应用中，有时为了性能或其他需求，可能会对范式进行适当的反规范化处理。

2. 为什么我们需要索引

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 帮助服务器避免排序和临时表
• 将随机IO变为顺序IO。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

3. mysql优化了解吗-说一下从哪些方面可以做到性能优化

• 为搜索字段创建索引
• 避免使用 Select *，列出需要查询的字段
• 垂直分割分表
• 选择正确的存储引擎

隔离级别和问题避免

• 脏读：读到其他事务未提交的数据；

• 不可重复读：前后读取的数据不一致；

• 幻读：前后读取的记录数量不一致。

• 读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；

• 读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；

• 可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；

• 串行化（serializable ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED 未提交读	√	√	√
READ-COMMITTED 提交读	×	√	√
REPEATABLE-READ 重复读	×	×	√
SERIALIZABLE 可串行化读	×	×	×

Mysql有哪些日志，简单概括有什么用

MySQL 中有多种日志，每种日志的作用各不相同，以下是它们的简单概括：

1. Binlog（二进制日志） 保证

   • 作用：记录所有对数据库的修改操作（DDL 和 DML 语句），但不包括查询语句（如 SELECT、SHOW）。主要用于数据恢复、主从复制和审计 。
   • 特点：以二进制格式存储，支持 STATEMENT、ROW 和 MIXED 三种模式记录 。

2. Redo Log（重做日志）

   • 作用：保证事务的持久性。记录的是数据页的物理修改，用于在 MySQL 崩溃后恢复未写入磁盘的数据（即“崩溃恢复”）。
   • 特点：循环写入，固定大小，保存未刷入磁盘的脏页日志 。

3. Undo Log（回滚日志）

   • 作用：保证事务的原子性。记录的是事务执行前的数据状态，用于回滚操作或实现 MVCC（多版本并发控制）。
   • 特点：与 Redo Log 配合使用，确保事务的一致性和隔离性。

原子性--> Undo Log 一致性--> 其他几项同时保证 持久性--> Redo Log 隔离性--> MVCC LBCC

执行一条语句操作日志的完整过程

具体更新一条记录 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下:

1. 执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：
   • 如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
   • 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。
2. 执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：
   • 如果一样的话就不进行后续更新流程；
   • 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；
3. 开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log。
4. InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术，MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上。
5. 至此，一条记录更新完了。
6. 在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。
7. 事务提交（为了方便说明，这里不说组提交的过程，只说两阶段提交）：
8. prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
9. commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）；
10. 至此，一条更新语句执行完成。

介绍MVCC的原理

MVCC允许多个事务同时读取同一行数据，而不会彼此阻塞，每个事务看到的数据版本是该事务开始时的数据版本。这意味着，如果其他事务在此期间修改了数据，正在运行的事务仍然看到的是它开始时的数据状态，从而实现了非阻塞读操作。

对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。

• 「读提交」隔离级别是在「每个select语句执行前」都会重新生成一个 Read View；
• 「可重复读」隔离级别是执行第一条select时，生成一个 Read View，然后整个事务期间都在用这个 Read View。

Read View 有四个重要的字段：

• m_ids ：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。
• min_trx_id ：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值。
• max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；
• creator_trx_id ：指的是创建该 Read View 的事务的事务 id。

对于使用 InnoDB 存储引擎的数据库表，它的聚簇索引记录中都包含下面两个隐藏列：

• trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
• roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

在创建 Read View 后，我们可以将记录中的 trx_id 划分这三种情况：

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

• 如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。

• 如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。

• 如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：

  • 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
  • 如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）

Mysql数据类型

以下是MySQL中常用的数据类型分类及其说明的表格，按照类别进行了整理，方便查阅：

---

数值类型

数据类型	存储大小	范围 (有符号)	范围 (无符号)	描述
TINYINT	1 字节	-128 到 127	0 到 255	非常小的整数
SMALLINT	2 字节	-32,768 到 32,767	0 到 65,535	小整数
MEDIUMINT	3 字节	-8,388,608 到 8,388,607	0 到 16,777,215	中等大小的整数
INT 或 INTEGER	4 字节	-2,147,483,648 到 2,147,483,647	0 到 4,294,967,295	标准整数
BIGINT	8 字节	-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807	0 到 18,446,744,073,709,551,615	大整数
FLOAT	4 字节	-3.402823466E+38 到 -1.175494351E-38 和 1.175494351E-38 到 3.402823466E+38	0 和 1.175494351E-38 到 3.402823466E+38	单精度浮点数
DOUBLE	8 字节	-1.7976931348623157E+308 到 -2.2250738585072014E-308 和 2.2250738585072014E-308 到 1.7976931348623157E+308	0 和 2.2250738585072014E-308 到 1.7976931348623157E+308	双精度浮点数
DECIMAL(M,D)	可变	依赖于 M 和 D 的值	依赖于 M 和 D 的值	精确的小数值，M 是总位数，D 是小数位数

日期和时间类型

数据类型	存储大小	范围	格式	描述
DATE	3 字节	'1000-01-01' 到 '9999-12-31'	YYYY-MM-DD	仅存储日期
TIME	3 字节	'-838:59:59' 到 '838:59:59'	HH:MM:SS	仅存储时间
DATETIME	8 字节	'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'	YYYY-MM-DD HH:MM:SS	日期和时间的组合
TIMESTAMP	4 字节	'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC	YYYY-MM-DD HH:MM:SS	时间戳，通常用于记录时间戳
YEAR	1 字节	'1901' 到 '2155'	YYYY	仅存储年份

字符串类型

数据类型	最大长度	存储需求	描述
CHAR(M)	0-255 字符	M × 字符集字节数	固定长度字符串，不足补空格
VARCHAR(M)	0-65,535 字节	实际长度 + 1 或 2 字节	可变长度字符串
TEXT	0-65,535 字节	L + 2 字节	长文本数据
TINYTEXT	0-255 字节	L + 1 字节	小型文本数据
MEDIUMTEXT	0-16,777,215 字节	L + 3 字节	中型文本数据
LONGTEXT	0-4,294,967,295 字节	L + 4 字节	超长文本数据
BLOB	0-65,535 字节	L + 2 字节	二进制大对象
TINYBLOB	0-255 字节	L + 1 字节	小型二进制对象
MEDIUMBLOB	0-16,777,215 字节	L + 3 字节	中型二进制对象
LONGBLOB	0-4,294,967,295 字节	L + 4 字节	超长二进制对象
ENUM	1 或 2 字节	最多 65,535 个值	枚举类型，只能从预定义列表中选择一个值
SET	1、2、3、4 或 8 字节	最多 64 个成员	集合类型，可以选择多个值

空间类型

数据类型	描述
GEOMETRY	几何类型的基类
POINT	表示一个点
LINESTRING	表示一条线
POLYGON	表示一个多边形
MULTIPOINT	表示多个点
MULTILINESTRING	表示多条线
MULTIPOLYGON	表示多个多边形
GEOMETRYCOLLECTION	表示几何对象的集合

JSON 类型

数据类型	描述
JSON	用于存储 JSON 文档

---

注意事项

1. 字符集影响：对于字符串类型（如 CHAR 和 VARCHAR），实际存储大小会受到字符集的影响。例如，使用 UTF-8 编码时，每个字符可能占用 1 到 4 个字节。
2. 存储效率：选择合适的数据类型可以显著提高存储效率和查询性能。例如，如果只需要存储小范围的整数，优先选择 TINYINT 而非 INT。
3. 时间戳与 DATETIME：TIMESTAMP 类型受时区影响，而 DATETIME 不受时区影响。