MySQL-知识概览

发表于：
字数： 6178 阅读：≈ 13分钟
总结摘要
MySQL Knowledge Overview

三大核心日志

MySQL 任意一条写SQL（不包括读SQL）的执行都会记录三个核心日志：undo-log、redo-log、bin-log。

Undo Log（撤销日志）

  • 作用：记录SQL操作的撤销日志，用于事务回滚和MVCC
  • 存储机制：
    • 使用共享Buffer Pool中的undo_log_buffer缓冲区
    • 事务写数据前，先将旧数据拷贝到Undo Log。行数据的隐藏字段roll_ptr回滚指针指向Undo Log中的旧数据。
  • 特性：
    • 属于"数据页"，受Redo Log保护，即写入 Undo Log 也会记录 Redo Log。
    • InnoDB引擎特有

Redo Log（重做日志）

  • 作用：记录数据页的物理变化，用于崩溃恢复
  • 存储机制：记录的是“数据页的物理变化”，例如记录"表空间X、页面Y、偏移Z处的值从A改为B"
  • 刷盘控制：
    • 参数innodb_flush_log_at_trx_commit控制刷盘时机
    • 默认值1：每次提交事务时刷盘
  • 特性：InnoDB引擎特有

Bin Log（二进制日志）

  • 作用：记录SQL操作日志，用于主从复制和数据恢复
  • 存储机制：
    • 使用位于线程的工作内存中的缓冲区bin_log_buffer。
    • 追加存储模式，支持保留所有 bin-log。
  • 格式类型：
    • STATEMENT：记录SQL语句（默认）
    • ROW：记录行数据变化
    • MIXED：混合模式
  • 刷盘控制：
    • 参数sync_binlog控制刷盘时机
    • 默认值1：每次提交事务时刷盘
  • 特性：MySQL Server级别，所有引擎可用

崩溃恢复机制

两阶段提交与崩溃恢复

如果redo-log只写一次，那不管redo-log和 bin-log 谁先写，都有可能造成主从同步数据时的不一致问题出现。

为了解决该问题，redo-log 被设计成了两阶段提交模式。

在开启binlog的情况下，MySQL使用内部XA事务保证：

  • Binlog与Redo Log的一致性
  • 两阶段提交：Prepare → Commit

两阶段提交流程

1

Redo Log(prepare) → Bin Log → Redo Log(commit)

崩溃点处理

  1. Redo Log(prepare)阶段崩溃：事务未提交，不影响一致性
  2. Bin Log写入阶段崩溃：重启后根据Redo Log事务ID回滚数据
  3. Redo Log(commit)阶段崩溃：Bin Log已写入，重启后重新提交

InnoDB 保证磁盘中的数据必定存在对应的 Redo-log（及Undo-log）。

LSN（日志序列号）机制

LSN（Log Sequence Number）

  • 定义：单调递增的数字，标记日志记录和数据页
  • 介绍：
    • InnoDB 内部使用一个名为 LSN（Log Sequence Number） 的单调递增的数字来标记每一个日志记录和每一个数据页。
    • 每个数据页（无论是在 Buffer Pool 还是在磁盘上）的头部都记录着一个 LSN，表示最后修改这个数据页的 redo log 记录的 LSN。
  • 强制规则：脏页刷盘前提是该页LSN ≤ 已持久化的Redo Log LSN
  • 作用：保证Redo Log落盘永远先于对应脏页落盘

协作机制

任何数据页的修改，必须在描述这个修改的 redo log 记录持久化到磁盘之后，才能被持久化到磁盘。

InnoDB 严格遵守这一原则。在后台 Page Cleaner 线程要将一个脏页刷盘时，它会首先检查：

  • 这个脏页对应的所有 redo log record 的 LSN（Log Sequence Number）是否已经小于当前已经持久化到磁盘的 redo log 的 LSN（flushed_to_disk_lsn）。
  • 只有确认“是”，即保证描述这个页修改的日志已经安全落盘，InnoDB 才会将这个数据页写入磁盘。

补充说明

如果数据页先落盘，那么该数据页的 LSN 会大于磁盘上 redo log 的 LSN。在恢复时，InnoDB 看到这个“超前”的数据页 LSN，会认为这个页已经包含了所有最新的修改，从而跳过对其的重做。但如果实际上 redo log 中还有后续更新未应用，这个页就是损坏的、过时的。

InnoDB 在刷盘时使用：

  • fsync() 或 fdatasync() 系统调用：这些调用会强制将内核缓冲区中的数据排入磁盘设备队列，并等待磁盘确认写入完成后才返回。
  • 在软件层面，InnoDB 认为 fsync() 成功返回即代表持久化完成。只要硬件和 OS 不“说谎”，在 fsync() 返回成功后，它对应的数据就一定在磁盘上，从而保证了 redo log 一定先于数据页落盘。

MySQL事务实现原理

ACID原则

SQL标准的ACID原则

事务是一个不可分割的数据库操作序列，而 ACID 是衡量事务是否正确执行的四个核心特性标准。可以说：

  • ACID 是事务必须满足的属性要求
  • 事务是 ACID 特性的具体承载载体

ACID 特性详解：

  1. 原子性 (Atomicity) - 事务要么全部完成，要么全部不执行
  2. 一致性 (Consistency) - 事务将数据库从一个一致状态转换到另一个一致状态
  3. 隔离性 (Isolation) - 并发事务相互隔离，互不干扰
  4. 持久性 (Durability) - 事务提交后，修改永久保存

MySQL的ACID实现机制

ACID特性MySQL 实现机制
原子性(Atomicity)Undo Log
持久性(Durability)Redo Log + WAL
隔离性(Isolation)MVCC + 锁机制
一致性(Consistency)前三者共同保障

原子性 (Atomicity) 实现

  • 主要机制：Undo Log（回滚日志）
  • 工作原理：
    • 事务执行前，将修改前的数据镜像写入 undo log
    • 事务回滚时，利用 undo log 恢复原始数据
    • 事务提交后，异步清理不再需要的 undo log

持久性 (Durability) 实现

  • 主要机制：Redo Log（重做日志） + WAL
  • 工作原理：
    • 采用预写日志（WAL）：数据修改先写 redo log，再写数据页
    • redo log 顺序写入，性能高，保证崩溃恢复能力
    • 通过 innodb_flush_log_at_trx_commit 控制刷盘策略

隔离性 (Isolation) 实现

  • 主要机制：MVCC + 锁机制
  • MVCC 组件：
    • 隐藏字段（DB_TRX_ID、DB_ROLL_PTR）
    • Read View（可见性判断）
    • Undo Log 版本链
  • 锁机制：
    • 行级锁、间隙锁、Next-Key Lock
    • 解决写-写冲突

一致性 (Consistency) 实现

  • 综合结果：由原子性、隔离性、持久性共同保证
  • 辅助机制：
    • 外键约束
    • 触发器
    • 业务逻辑校验

隔离性中的隔离级别

隔离级别定义了事务之间的可见性规则，在并发性能和数据一致性之间提供不同级别的权衡：

  • 更严格的隔离级别 → 更高的一致性，更低的并发性能
  • 更宽松的隔离级别 → 更高的并发性能，可能出现数据不一致

SQL 标准中的四种隔离级别

  1. 读未提交：存在脏读、不可重复读、幻读
  2. 读已提交：解决脏读，存在不可重复读、幻读
  3. 可重复读：解决脏读、不可重复读，存在幻读
  4. 串行化：解决所有并发问题

MySQL 遵守 SQL 标准，与 SQL 标准规范基本一致。

MySQL 在 REPEATABLE READ 下通过 Next-Key Locking 也解决了幻读问题。

MySQL事务实现机制概述

MySQL 事务的实现主要基于 WAL（Write-ahead logging，预写式日志）机制和 MVCC（Multi-Version Concurrency Control，多版本并发控制）技术。

WAL 是 MySQL 的核心事务保障机制，遵循“日志先行”原则：所有数据修改必须先写入日志，再应用到实际数据页。这确保了事务的持久性和崩溃恢复能力，主要包含 redo log（重做日志）和 undo log（回滚日志）两部分。

MVCC 是一种并发控制技术，通过在数据库中维护数据的多个版本来实现非阻塞读操作，从而提高数据库的并发性能。MVCC 的实现主要依赖三个核心组件：

  1. 隐藏字段：每条记录包含的系统字段，如事务ID、回滚指针等
  2. Read View：事务在某一时刻生成的数据库快照，用于确定数据可见性
  3. undo log：存储数据的历史版本，通过回滚指针构建版本链

在具体实现中，undo log 负责维护数据的多版本历史，而隐藏字段和 Read View 共同协作实现并发访问控制，确保事务能够读取到适当版本的数据。

MVCC实现机制

MVCC 介绍

MVCC 全称 Multi-Version Concurrency Control，即多版本并发控制，主要是为了提高数据库的并发性能。

核心组件

隐藏字段

每条记录包含三个系统字段：

  • DB_TRX_ID（6字节）：最后修改事务ID
  • DB_ROLL_PTR（7字节）：回滚指针，指向Undo Log
  • DB_ROW_ID（6字节）：行ID（无主键时使用）

Read View（读视图）

事务在执行时生成的数据库快照，包含：

  • <font style="color:rgb(15, 17, 21);background-color:rgb(235, 238, 242);">trx_ids</font>：当前活跃事务ID列表
  • <font style="color:rgb(15, 17, 21);background-color:rgb(235, 238, 242);">low_limit_id</font>：当前最大事务ID+1
  • <font style="color:rgb(15, 17, 21);background-color:rgb(235, 238, 242);">up_limit_id</font>：当前最小活跃事务ID
  • <font style="color:rgb(15, 17, 21);background-color:rgb(235, 238, 242);">creator_trx_id</font>：创建该 Read View 的事务ID

MVCC 工作流程

工作流程

  1. 访问某行数据
  2. 检查 DB_TRX_ID（最近修改事务ID）
  3. 根据 Read View 可见性规则判断：
    • 如果 DB_TRX_ID < up_limit_id → 可见
    • 如果 DB_TRX_ID >= low_limit_id → 不可见
    • 如果 DB_TRX_ID 在活跃事务列表中 → 不可见
    • 其他情况 → 可见
  4. 如果不可见，通过 DB_ROLL_PTR 遍历 undo log 版本链
  5. 找到对当前事务可见的版本数据

可见性判断规则

一个数据版本对当前事务可见，当且仅当：

  1. 版本事务ID 小于 Read View 中的最小活跃事务ID，或
  2. 版本事务ID 是当前事务自身ID，或
  3. 版本事务ID 不在活跃事务列表中且小于最大事务ID

Read View生成时机

  • RC级别：每次select前生成新的Read View
  • RR级别：事务首次select前生成Read View

MVCC幻读问题

  • 场景1：T1快照读 → T2写提交 → T1写 → T1读
  • 场景2：T1快照读 → T2写提交 → T1当前读
  • 解决方案：添加间隙锁(Next-Key Locks)，即锁定读 “select…for update”。

MySQL锁机制

锁类型

  1. 记录锁(Record Lock)
    1. 实际上就是行级锁，只允许一个事务持有
  2. 间隙锁(Gap Lock)
    1. 锁定间隙区域，遵循左右开区间的原则。
    2. 多个事务可同时持有不同间隙锁
  3. 临键锁(Next-Key Lock)
    1. 记录锁 + 间隙锁组合（左开右闭区间）
    2. 默认行锁算法，只允许一个事务持有

在MySQL诸多的存储引擎中，仅有InnoDB引擎支持行锁。

InnoDB的行锁是基于索引实现的，如果SQL能命中索引数据，那就会加行锁，反之则是表锁（具体是全表加行锁）。

InnoDB默认的行锁算法为临键锁。

INSERT锁场景

  • 普通INSERT：当记录已存在时（抛出唯一键错误），获取S锁S,REC_NOT_GAP。
  • INSERT ON DUPLICATE KEY UPDATE：当记录已存在时不会抛出唯一键错误，语句获取记录锁X,REC_NOT_GAP。

函数与存储过程事务性

  • 函数：整个函数的操作是原子性的，函数体内不能使用显式事务。
  • 存储过程：存过体内支持显式事务控制。

锁兼容性

不同锁之间的冲突与兼容关系：

PS：表中横向（行）表示已经持有锁的事务，纵向（列）表示正在请求锁的事务。

行锁

行级锁对比共享临键锁排他临键锁间隙锁插入意向锁
共享临键锁兼容冲突兼容冲突
排他临键锁冲突冲突兼容冲突
间隙锁兼容兼容兼容冲突
插入意向锁冲突冲突冲突兼容

表锁

表级锁对比共享意向锁排他意向锁元数据锁自增锁全局锁
共享意向锁兼容兼容冲突兼容冲突
排他意向锁兼容兼容冲突兼容冲突
元数据锁冲突冲突冲突冲突冲突
自增锁冲突冲突冲突冲突冲突
全局锁兼容冲突冲突冲突冲突

MySQL死锁处理

死锁解决方案

解除死锁的常用方案

  1. 锁超时机制：事务/线程在等待锁时，超出一定时间后自动放弃等待并返回。
  2. 外力介入打破僵局：第三者介入，将死锁情况中的某个事务/线程强制结束，让其他事务继续执行。

MySQL 实现方案

  1. 锁超时机制：默认50秒超时
  2. 死锁检测：高版本默认开启，选择Undo量最小的事务回滚

常见死锁场景

  1. 间隙锁与插入意向锁冲突
    1. 2个事务分别持有间隙锁，insert 语句获取插入意向锁冲突，产生死锁。
  2. index merge与记录锁冲突
    1. 2个事务均使用 index merge, 分别持有 idx1 及对应主键的记录锁，获取 idx2 对应主键的记录锁冲突，产生死锁。

MySQL 内部实现补充

MySQL内部实现中的性能优化方案

  1. 缓存与内存写优化
  2. Redo Log将随机写转为顺序写
  3. MVCC多版本并发控制
  4. 连接池管理

MySQL使用指导

字符集与排序规则

字符集处理流程 

1

character_set_client → character_set_connection → 结果字符集

说明

  1. 服务器将 character_set_client 系统变量视为客户端发送语句的字符集，以 character_set_client 的字符集解析客户端的二进制数据。
  2. 服务器将客户端发送的语句从 character_set_client 转换为 character_set_connection。
  3. character_set_results 系统变量指示服务器以何种字符集将查询结果返回给客户端。这包括列值等结果数据、列名等结果元数据以及错误消息。
  4. 每个字符字符串字面量都有一个字符集和一个排序规则。collation_connection 对于字面量字符串的比较很重要。对于列值字符串的比较， collation_connection 不重要，因为列有自己的排序规则，其排序规则优先级更高。

JDBC 连接初始化规则

  1. JDBC 的 connectionCollation 会覆盖默认值，直接设置会话变量 collation_connection（同时影响 character_set_client/connection/results）。
  2. 优先级：connectionCollation（JDBC） > global collation_connection/collation_server（服务器默认）。
  3. 禁止行为：避免在 JDBC 连接中使用 SET NAMES，因驱动无法感知此修改，仍使用初始配置。

MySQL SELECT 执行顺序

  1. WITH (CTE, COMMON TABLE EXPRESSIONS)
  2. FROM
  3. ON
  4. JOIN
  5. WHERE
  6. GROUP BY
  7. HAVING
  8. SELECT, WINDOW FUNCTION
  9. SELECT DISTINCT
  10. UNION, INTERSECT, EXCEPT
  11. ORDER BY
  12. LIMIT, OFFSET
  13. FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE

辅助日志

辅助日志 error-log、slow-log、relay-log。

  1. 错误日志error-log
    1. 记录MySQL错误信息
  2. 慢查询日志slow-log
    1. 记录所有超时的 SQL 命令
  3. 查询日志general-log
    1. 记录所有收到的 SQL 命令
  4. 中继日志relay-log
    1. 从主机复制过来的bin-log数据放在relay-log日志中，中继日志的作用就跟它的名字一样，仅仅只是作为主从同步数据的“中转站”。

性能优化

性能分析工具

  • EXPLAIN：查询计划估计
  • EXPLAIN ANALYZE：实际执行统计
  • 支持 SELECT/INSERT/UPDATE/DELETE 等语句。

数据类型隐式转换

隐式转换是 MySQL 在比较或操作不同数据类型时自动进行的类型转换，主要发生在比较运算、算术运算和函数处理等场景。

规则

  1. 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
  2. 两个参数都是字符串，会按照字符串来比较，不做类型转换
  3. 两个参数都是整数，按照整数来比较，不做类型转换
  4. 十六进制的值和非数字做比较时，会被当做二进制串
  5. 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
  6. 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
  7. 所有其他情况下，两个参数都会被转换为浮点数再进行比较

MySQL 的隐式转换虽然方便，但容易导致：

  • 性能下降（索引失效）
  • 逻辑错误（意外的匹配结果）
  • 精度丢失（数据截断）
  • 安全风险（SQL注入）

异常场景

1
2
3

-- 字符串与数字比较，字符串转为0
SELECT 'password' = 0;  -- 返回1(true)
SELECT 'password' = 'pwd'; -- 返回0(false)

进程管理

进程终止命令

  • KILL CONNECTION：终止连接及正在执行的语句
  • KILL QUERY：仅终止当前语句，保留连接

官网文档摘录

  1. KILL permits an optional CONNECTION or QUERY modifier KILL 允许可选的 CONNECTION 或 QUERY 修饰符：
  2. KILL CONNECTION is the same as KILL with no modifier: It terminates the connection associated with the given processlist_id, after terminating any statement the connection is executing. KILL CONNECTION 与无修饰的 KILL 相同：它在终止任何正在执行的语句后，终止与给定 processlist_id 关联的连接。
  3. KILL QUERY terminates the statement the connection is currently executing, but leaves the connection itself intact.KILL QUERY 终止连接当前正在执行的语句，但保留连接本身。
  4. Killing a REPAIR TABLE or OPTIMIZE TABLE operation on a MyISAM table results in a table that is corrupted and unusable. Any reads or writes to such a table fail until you optimize or repair it again (without interruption).在 REPAIR TABLE 或 OPTIMIZE TABLE 表上终止操作会导致表损坏且无法使用。对这种表进行的任何读取或写入操作都会失败，直到您再次对其进行优化或修复（无中断）。

官网文档链接

https://dev.mysql.com/doc/refman/9.2/en/kill.html

MySQL线程查找步骤

  1. 获取OS进程ID：top | grep mysql
  2. 获取OS线程ID：top -Hp <pid>
  3. 查询MySQL线程ID：
1
2

SELECT * FROM performance_schema.threads 
WHERE TYPE = 'FOREGROUND' AND THREAD_OS_ID = '<os_thread_id>';

参考资料

全解MySQL数据库

https://juejin.cn/column/7140138832598401054

掘金·竹子爱熊猫

图解MySQL

https://xiaolincoding.com/mysql/

小林coding·小林

MySQL 是怎样运行的：从根儿上理解 MySQL

ISBN: 9787115547057

稀土掘金·MySQL 是怎样运行的：从根儿上理解 MySQL

https://juejin.cn/book/6844733769996304392

网站·MySQL 是怎样运行的：从根儿上理解 MySQL

https://relph1119.github.io/mysql-learning-notes/#

小孩子4919

MySQL 8.0 Reference Manual

https://dev.mysql.com/doc/refman/8.0/en/

END