MySQL

通识

MySQL为CS架构，有连接层、服务层和存储引擎层。连接层负责身份验证，连接管理。服务层负责解析SQL语句、优化查询SQL和各种运维功能。

存储引擎层负责数据的存储和检索，目前采用InnoDB（支持事务和行级锁），5.5之前是MyISAM目前没啥人用，但是了解一下区别也不错，毕竟查询性能还是高一点。

名词解释： 外键约束：通过限制一个表中的字段值必须匹配另一张表的主键值，保证数据的参照完整性，防止出现孤立、无效或不一致的数据关系。 崩溃恢复：能够通过自身的日志机制（如 InnoDB 的 redo/undo log）自动检测并恢复数据到崩溃前的一致状态，避免数据丢失、损坏或不一致，保障数据的持久性和可靠性。 全文索引：针对文本内容的特殊索引类型，用于快速检索字符串类型字段中的关键词，相比传统的LIKE模糊查询，它支持自然语言的分词检索，大幅提升文本搜索的效率和准确性，适用于内容检索类场景。（没啥人用，Elasticsearch爆杀它，除非很轻量化） OLTP（Online Transaction Processing）：大量短小、高频、实时性的事务

SQL vs No SQL

ACID：关系型数据库的事务原则，确保操作要么全做要么全不做，数据时刻保持正确。 e.g. 银行转账：A 向 B 转 100 元。步骤包含“A 扣款”和“B 收款”，这两步必须同时成功或同时失败回滚，绝不能出现 A 钱少了 B 却没收到的情况。 BASE：NoSQL 常用的设计理念，牺牲强一致性换取高可用，允许数据存在中间状态。 e.g. 朋友圈点赞：你点赞后，其他好友可能过几秒才看到红点亮起。系统不保证实时一致，但保证经过一段时间后数据最终同步。适合海量数据、高并发场景。

ACID（关系型数据库事务原则）：

• A (Atomicity) 原子性：事务不可分割，操作要么全成功，要么全失败回滚。

• C (Consistency) 一致性：事务前后，数据必须符合规则（如余额不能为负），保持合法状态。

• I (Isolation) 隔离性：并发事务互不干扰，各干各的，互相看不到中间过程。

• D (Durability) 持久性：事务一旦提交，修改永久生效，宕机重启数据也不丢。

BASE（NoSQL / 分布式设计理念）：

• BA (Basically Available) 基本可用：系统出故障时，允许损失部分功能（如响应变慢、返回旧数据），但保证核心服务不挂。

• S (Soft State) 软状态：允许数据存在中间状态（如副本同步中），不要求实时强一致。

• E (Eventually Consistent) 最终一致性：不保证任意时刻数据一致，但承诺经过一段时间后，数据最终会同步到一致状态。

数据库三大范式

NF = Normal Form

数据库三大范式（1NF / 2NF / 3NF）是设计关系表的一套递进规范：

• 1NF：字段不可再分，每列都是原子值，没有“小表格”、“数组”字段。
• 2NF：在 1NF 基础上，消除“非主属性对主键的部分依赖”，只与主键整体相关。（针对于联合主键而言）
• 3NF：在 2NF 基础上，消除“非主属性对非主属性的传递依赖”，避免冗余和更新异常。

e.g.

1NF : 一个列为联系方式是不好的。分为邮件和电话两列更好。 2NF：若主键为（学号，课程号），存在一个列为姓名是不好的，姓名只依赖学号而不依赖课程号；存在一个列存成绩是正常的。所以就把学生表独立出来，原来的表只留成绩。 3NF：若学生表中有“学号、姓名、系名、系主任”，主键是学号。这里“系主任”依赖“系名”，而“系名”又依赖“学号”，构成了传递依赖（学号 -> 系名 -> 系主任）。这会导致数据冗余（同系的学生重复存储系主任）和更新异常（换系主任要改全表）。应将表拆分为：学生表（学号、姓名、系名）和系表（系名、系主任）。

外键

1
2
3
4
5
6

CREATE TABLE students ( 
id INT PRIMARY KEY,
name VARCHAR(50),
course_id INT,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

不过其实应用层来控制删除日志记录和控制性更好，只要不疏忽

索引

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

mindmap
  root((MySQL索引分类体系))
    按数据结构
      B+tree索引
        :最常用，支持范围查询
      Hash索引
        :等值查询极快，不支持范围
      Full-text索引
        :用于全文搜索
    按物理存储
      聚簇索引
        :数据与索引一体
        :叶子存整行数据
      二级索引
        :叶子存主键值
        :需“回表”查询
    按字段特性
      主键索引
        :唯一且非空
      唯一索引
        :允许NULL值
      普通索引
        :基本类型
      前缀索引
        :节省空间
    按字段个数
      单列索引
        :单个字段
      联合索引
        :多个字段
        :遵循最左前缀

最左匹配：

如果创建了一个 (a, b, c) 联合索引。 可用

1
2

where a = ? and b = ?;
where a = ?;

失效

1
2

where b = ?;
where b = ? and c = ?;

前缀索引是一种特殊索引类型，它仅对文本字段的前N个字符建立索引，而不是对整个字段进行索引。这种方式特别适用于那些字段值很长，但查询时通常只基于字段值前几个字符进行的情况。

CREATE INDEX idx_name ON table_name (column_name(length));

要注意覆盖率和长度的平衡。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 1. 计算完整列的选择性（作为基准）
SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

-- 2. 计算不同前缀长度的选择性
SELECT 
    COUNT(DISTINCT LEFT(column_name, 3)) / COUNT(*) AS sel_3,
    COUNT(DISTINCT LEFT(column_name, 4)) / COUNT(*) AS sel_4,
    COUNT(DISTINCT LEFT(column_name, 5)) / COUNT(*) AS sel_5,
    COUNT(DISTINCT LEFT(column_name, 6)) / COUNT(*) AS sel_6
    -- ...继续测试更多长度
FROM table_name;

找到前缀选择性最接近且不低于全列选择性的最小长度。

MySQL事务的ACID特性是其核心，而InnoDB存储引擎通过Redo Log、Undo Log、Binlog以及MVCC和锁机制共同协作来实现这些特性。下面我将为您详细解析其原理与实现。

事务

ACID是数据库事务正确执行的四个关键特性，它们共同保证数据库操作的可靠性。

核心日志系统

MySQL（InnoDB）通过三类关键日志来实现ACID，它们协同工作，构成了事务可靠性的基石。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

flowchart LR
    subgraph A[事务执行流程]
        direction TB
        A1[事务开始] --> A2[记录Undo Log]
        A2 --> A3[修改Buffer Pool<br>生成脏页]
        A3 --> A4[写入Redo Log Buffer]
        A4 --> A5{事务提交?}
        A5 -- 是 --> A6[刷Redo Log到磁盘]
        A6 --> A7[写Binlog]
        A7 --> A8[提交成功]
        A5 -- 否 --> A9[通过Undo Log回滚]
    end
    subgraph B[崩溃恢复流程]
        direction TB
        B1[数据库重启] --> B2{检查Redo Log}
        B2 -- 有已提交事务 --> B3[重放Redo Log<br>前滚]
        B3 --> B4[数据恢复]
        B2 -- 有未提交事务 --> B5[通过Undo Log回滚]
        B5 --> B4
    end

1. Undo Log（回滚日志）—— 原子性

• 核心作用：记录数据修改前的旧值，用于事务回滚和实现MVCC的多版本读取。
• 工作原理：
  • 当执行 INSERT、UPDATE、DELETE 等操作时，InnoDB会先将修改前的数据写入Undo Log。
  • 例如，对于一条 UPDATE 语句，Undo Log会记录反向的 UPDATE 操作，将数据改回旧值。
  • 如果事务需要回滚，InnoDB就会根据Undo Log中的记录执行逆向操作，将数据恢复到事务开始前的状态。
• 额外作用：Undo Log是InnoDB实现MVCC（多版本并发控制）的关键。它通过版本链将一行数据的历史版本串联起来，使得读操作可以无锁地访问数据的一致性快照。

2. Redo Log（重做日志）—— 持久性的保障

• 核心作用：记录事务对数据页的物理修改，确保事务的持久性，实现崩溃恢复。
• 工作原理：
  • 采用WAL (Write-Ahead Logging) 机制。事务提交时，InnoDB会先将修改操作记录到Redo Log并持久化到磁盘，然后再慢慢将Buffer Pool中的脏页刷入数据文件。
  • 这将原本的随机写（更新数据文件的不同位置）转化为顺序写（追加日志文件），极大地提升了性能。
  • 在系统崩溃重启后，InnoDB会自动检查Redo Log，将已提交但未刷盘的修改重新应用，从而恢复数据。
• 关键配置：innodb_flush_log_at_trx_commit 参数控制Redo Log的刷盘策略。
  • 1：最安全，每次事务提交都刷盘，保证不丢数据（这是默认值）。
  • 0：每秒刷盘一次，可能丢失最后一秒的事务。
  • 2：提交时写入文件系统缓存，由系统决定何时刷盘，性能好但存在风险。

3. Binlog（二进制日志）—— 复制与恢复的桥梁

• 核心作用：记录所有修改数据的SQL语句（逻辑日志），主要用于主从复制和基于时间点的数据恢复。
• 与Redo Log的区别：
  • 产生层级：Redo Log是InnoDB引擎层特有的；Binlog是MySQL Server层实现的。
  • 日志内容：Redo Log记录物理修改（对数据页的改动）；Binlog记录逻辑操作（SQL语句或行变更）。
  • 写入时机：事务提交时，Redo Log采用循环写，Binlog采用追加写。
• 两阶段提交：为保持主从数据一致性，InnoDB在事务提交时采用两阶段提交：先写Redo Log，再写Binlog。

隔离性的实现：锁与MVCC

隔离性主要解决并发事务之间的干扰问题，InnoDB采用锁机制和MVCC相结合的方式实现。

1. 锁机制 InnoDB支持行级锁，通过给数据加锁来避免并发冲突。

• 共享锁(S锁)：允许事务读一行数据，其他事务可以读但不能写。
• 排他锁(X锁)：允许事务删除或更新一行数据，其他事务不能读也不能写。
• 行锁算法：包括Record Lock（记录锁）、Gap Lock（间隙锁）、Next-Key Lock（临键锁），在可重复读隔离级别下，Next-Key Lock用于防止幻读。

2. MVCC（多版本并发控制） MVCC是InnoDB实现高并发的关键，它在很多情况下避免了加锁，实现了非阻塞读。

• 核心思想：为每个事务提供一个数据快照，使得读操作不需要加锁就能看到一致的历史数据。
• 实现元素：
  1. 隐藏字段：每行数据包含 DB_TRX_ID（最近修改事务ID）和 DB_ROLL_PTR（回滚指针）。
  2. Undo Log版本链：通过回滚指针将一行数据的多个版本串联起来。
  3. Read View（一致性视图）：事务开始时生成，包含“当前活跃事务ID列表”和“最大事务ID”，用于判断数据版本对该事务是否可见。
• 不同隔离级别的MVCC：
  • 读已提交 (READ COMMITTED)：每次查询都会生成一个新的Read View，能看到其他事务已提交的修改。
  • 可重复读 (REPEATABLE READ)：事务第一次查询时生成Read View，整个事务期间都使用这个快照，因此能看到事务开始时的一致性状态。

事务执行与崩溃恢复流程

理解ACID特性的实现，关键在于看清楚事务从开始到提交，以及发生崩溃后如何处理。

1. 事务正常提交流程：
   • 事务开始，分配事务ID。
   • 修改数据前，先写入Undo Log。
   • 在Buffer Pool中修改数据页（形成脏页），并将修改记录写入Redo Log Buffer。
   • 事务提交时，根据 innodb_flush_log_at_trx_commit 设置将Redo Log Buffer刷盘。
   • 写入Binlog（两阶段提交的第二个阶段）。
   • 提交完成，返回结果给客户端。
2. 崩溃恢复流程：
   • 数据库重启后，检查Redo Log。
   • 前滚：对于已提交但未刷盘的事务，重新应用Redo Log，恢复数据。
   • 回滚：对于未提交的事务，通过Undo Log撤销所有修改。
   • 最终，数据库恢复到一致状态。

总结

MySQL（InnoDB）的事务特性实现是一个精密协作的体系：

• 原子性：由 Undo Log 实现，提供回滚能力。
• 持久性：由 Redo Log 实现，采用WAL机制保证崩溃恢复。
• 隔离性：由 锁机制 和 MVCC 共同实现，平衡了性能与正确性。
• 一致性：是以上三个特性（A、I、D）共同追求的目标，最终通过数据库的约束机制达成。 这套机制使得MySQL能够在高并发环境下，既保证了数据的可靠性和一致性，又提供了良好的性能。理解这些底层原理，对于数据库优化、故障排查以及高可用架构设计都至关重要。

请配合Java.md中SQL使用。

Redis