存储引擎及架构

数据库的三大范式

  1. 第一范式：字段必须是原子性，不能再分
  2. 第二范式：每个非主属性必须完全依赖于主键，消除部分依赖
  3. 第三范式：非主键之间不能有依赖关系，消除传递依赖

关系型数据库区别

  1. 关系型数据库：复杂的数据结构归结为简单二维表格形式
    • MySQL、Oracle
  2. 非关系型数据库：将数据存储为键值对集合，其中键作为唯一标识符
    • Redis、MongoDB

SQL四大类语言

  • DDL：建库建表、改结构，典型语句：CREATE / ALTER / DROP / TRUNCATE
  • DML：增删改数据行，典型语句：INSERT / UPDATE / DELETE
  • DQL：查数据，典型语句：SELECT
  • DCL：权限/账户控制，CREATE USER / DROP USER

内部架构及作用

image-20251121101513870

  1. 连接器：不同语言与数据库进行交互，如java，php
  2. 系统管理和控制工具：涵盖：运维监控、数据维护、性能优化、安全管控四大核心场景
  3. 连接层

连接时会优先加载全局权限和数据库级权限

连接过程是一个TCP长连接，MySQL服务器和客户端通讯是“半双工”，只能单方面发送请求，不能同时发送

TCP长连接带来的问题 ：占用内存，内存涨的特别快；如何解决？

  • 定期断开长连接
  • 使用Druid会定期检测空闲连接，超过设定时间的连接会被释放
  1. SQL接口：接收用户的SQL命令，并返回用户查询的结果
  2. 解析器

在执行语句之前，会解析查询的语言，在过程中会判断语法，将查询字段、表、条件封装到内部的数据结构形成解析树

image-20251121101745611

  1. 查询缓存

MySQL8将这块删除了，因为缓存匹配条件严格，需要SQL完全一致

  1. 查询优化器

通过语法解析，MySQL知道真实意图了，但是写的SQL语句不够高效，MySQL会给语句做优化，比如使用了哪个索引？最左匹配原则？

  1. 执行：判断是否有权限才可以接着执行
  2. 存储引擎接口
  3. 文件系统层

一条SQL请求的过程

  1. SQL客户端与服务器建立连接，该请求发送到连接器
  2. 通过MySQL关键字将语法解析成一个内部解析树，再判断有没有错误语法
  3. 如果命中缓存，直接返回结果
  4. 基于成本优化SQL语句
  5. 调用存储引擎接口执行

主流的存储引擎

  1. InnoDB（默认）
    • 支持事务处理，具备ACID特性（原子性、一致性、隔离性、持久性）
    • 采用行级锁，高并发下减少锁冲突
    • 支持外键约数，便于维护表之间关系
  2. MyISAM
    • 读取速度快
    • 不支持事务和行级锁
  3. Memory
    • 数据存储在内存中
    • 支持哈希索引

对于一张表来说可以将A引擎切换为B引擎

一条查询语句的关键字执行顺序

逻辑执行顺序：

FROM

ON

JOIN

WHERE

GROUP BY

HAVING

SELECT

DISTINCT

ORDER BY

LIMIT / OFFSET

【例子】

image-20251121102422090

  1. FROM：拿到emp、dept两张表的原始行
  2. ON：按照 d.id = e.dept_id 先做匹配
  3. JOIN：把匹配成功的行拼在一起
  4. WHERE：丢掉 salary <= 10000 的行
  5. GROUP BY：按照 d.name 分组
  6. HAVING：保留组内数据 ≥ 3 的组
  7. SELECT：算出 dept 别名、cnt 聚合列
  8. ORDER BY：按 cnt 排序
  9. LIMIT：取前 5 行

InnoDB的三大特性

  1. “外在能力”
    • 事务（ACID）
    • 行级锁
    • MVCC
  2. “内部机制” / 核心优化特性、机制
  • 插入缓存

往二级索引里插一条记录，目标叶子页如果不在内存中，需要将那一页随机读进来，修改，再写回去，很慢

InnoDB优化：先不读那页，将要插入的数据、插到哪一页，写到另一个 B+ 树里（这棵树叫 Change Buffer），等以后那一页正好进内存或者后台空闲，再写入数据

叶子页：

image-20251121103021360

想要插入0036这条数据，就需要将 【0035, 0065, 0088】这个叶子页的数据全部拿出来

  • 双写

页（Page）写入磁盘分为两步：先写到共享双写区；再落目标数据文件

目的：发生意外情况，可以从副本恢复这页数据

  • 自适应哈希索引

热点范围自动创建哈希加速等值查找，省去多层B+树跳转

锁

image-20251121103231205

大致介绍

  1. 兼容性

共享锁：共享锁之间是兼容的，共享锁和排他锁是互斥的

排他锁：排他锁和任何锁都是不兼容的

  1. 粒度

全局锁：锁住数据库中的所有表，即锁住库

表级锁：每次操作要锁住整张表，锁定粒度大，发生锁冲突概率最高

  • 表锁
  • 元数据锁
  • 意向锁

行级锁：每次操作要锁住对应的行，发生锁冲突概率最低

  • 行锁（记录锁）
  • 间隙锁
  • 临建锁

全局锁

加锁后整个实例处于只读状态，后续的所有DML、DDL都会被阻塞，但可以执行DQL语句

使用场景：用于从数据库备份数据，获得一致性的数据

image-20251121103519448

全局锁特点：

  1. 如果在主库上备份，备份期间不能执行更新，业务被暂停
  2. 如果在从库上备份，备份期间不能执行主库同步的二进制文件（binlog），导致从数据延迟
  3. 【解决方式】使用快照读备份，不加全局锁

表级锁

  1. 表锁
  • 表共享读锁（read lock，读锁，S锁）：读锁不会阻塞其他客户端的读，但是会阻塞写
  • 表独占写锁（write lock，写锁，X锁）：写锁会阻塞其他客户端的读和写
  1. 元数据锁：元数据锁会在执行增删改的时候自动加上，事务提交后会被自动释放
  2. 意向锁

想上表级别读锁，首先需要确保没有行级别写锁，如果有，需要等到行级别写锁释放。
想上表级别写锁，首先需要确保没有行级别读锁和行级别写锁，如果有，需要等到行级别读锁和行级别写锁释放。

怎么快速知道有没有行级锁，就需要用到意向锁

  • 意向共享锁（IS锁）锁粒度：表级锁，当事务准备在某条记录上加行级读锁时，需要先在表级别加一个意向共享锁
  • 意向排他锁（IX锁）锁粒度：表级锁，当事务准备在某条记录上加行级写锁时，需要先在表级别加一个意向排他锁

如果没有意向锁，系统每次想加表锁，都得翻遍整张表看有没有行锁，效率低下

有了意向锁，只需要查看是否存在**意向共享锁（IS锁）、意向排他锁（IX锁）**即可

执行查询语句时，避免行锁和表锁的冲突，不用检查每行数据

行级锁

  1. 行锁（记录锁）

锁定单个行记录的锁，针对索引加锁，防止其他事务进行update和delete操作

InnoDB有两种类型行锁

  • 共享锁（S-share）：允许多个事务去读一行，用于并发读取
  • 排他锁（X-exclude）：加锁后只允许当前事务进行 读/写 操作，其他事务不能进行 读/写操作
  1. 间隙锁

锁“空隙”，不锁行本身，用来阻止别人往这个空隙里插入新行，从而避免幻读或违反唯一性

image-20251121104307380

  1. 临键锁

左开右闭 ( ]

记录锁 (Record Lock) ＋ 右侧间隙锁 (Gap Lock)
既把命中的那一行锁住，又把它右边那段空档一并锁上，防止别人插队产生幻读

image-20251121104356835

  1. 区别

假设有 id 为 {1，3，6，7，9} 这五个数据

  • 行锁：命中存在值，查询id = 6的数据；锁住 id = 6
  • 间隙锁：命中不存在值，查询 id = 5 的数据；锁住 id 在 (3, 6) 之间的数据，防止插入5
  • 临键锁：查询范围触及6，查询 id >= 6 的数据；锁住 id 在 (3, 6]、(6, 7]、(7, 9] 数据；右边 ] 都是真实数据

查询 id > 5 and id < 8 的数据

1 3 【 6 7 】 9

  • 行锁：锁住 id = 6, id = 7
  • 临键锁：锁住 (3, 6]、(6, 7]
  • 间隙锁：锁住右侧边界间隙 (7, 9)

死锁的检测及处理策略

  1. 检测：MySQL使用等待图（Wait-for Graph）算法来检测死锁，通过检查事务之间的依赖关系来判断是否存在死锁，MySQL自带的死锁检测

image-20251121104848996

  1. 死锁处理的策略
  • 回滚事务：发现死锁后，可以回滚其中资源持有最少得一个，解除死锁
  • 返回错误信息
  • 超时设置和重试机制
  1. 死锁的预防和避免
  • 尽早提交事务：避免在事务中执行长时间计算、减少锁持有的时间
  • 拆分大事务：减少事务持有锁的时间
  • 使用合适的索引：减少锁定的数据量，提高查询效率
  • 优化查询语句：使用精确条件限制查询范围，避免长时间持有锁定的行
  • 使用乐观锁：通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出；如果当前版本号 > 读取的版本号，认为更新成功，顺带将版本号 + 1

索引

什么是索引？

索引就像数据库里的加速目录，相当于告诉你“第123页是第5章”，记录了【关键值 ——> 数据物理位置】映射，查询时不需要将整本书翻一遍

个人理解的索引相关知识

image-20251121110254417

每一个索引对应一颗独立的B+树

对于主键索引，它的叶子节点存的是整行数据

对于二级索引（普通索引/唯一索引），它的叶子节点存的是索引列数据 和 主键值（PK值），根据这个主键值回主键索引查找数据

如果有二级索引，没有主键索引怎么办？

  • InnoDB中必须有一个聚簇索引
  • 如果有主键索引，那么聚簇索引就是主键索引
  • 如果没有主键索引，但有一个NOT NULL 且 UNIQUE 的索引，聚簇索引就用它
  • 如果都没有，InnoDB会生成一个隐藏的6字节row_id，作为聚簇索引的key

为什么要有索引？

  1. 极速查找
  2. 减少IO
  3. 约数 & 唯一性 ：比如保证邮箱不重复
  4. 支持排序和分组：B+树天生按键排序

索引类型

image-20251121110539051

Hash索引

image-20251121110600151

hash索引存储了索引字段的hash值和数据所在磁盘文件指针，如果来了一句sql：

select * from tab where id= 1 那么mysql会怎么去执行这条sql语句：
a、将id = 1 做一次hash运算得到hash为123
b、拿到hash为123在hash索引中去找123的节点
c、节点所对应的数据就是数据内容所在磁盘文件的指针
d、通过一次磁盘I/O得到所有的内容，即id=1，name=张三。
e、最后返回结果。

适用场景：

  • 不存储字段值
  • 无法进行排序
  • 不支持部分索引列匹配查找
  • 不支持任何范围查询

B树索引特点

B树叫做多路平衡查找树，多路就是说一个结点下面可以有多个子节点。

image-20251121110732653

B+树索引特点

B+树就是B树的一种变种，非叶子节点存放索引，叶子节点存放数据

B+树叶子节点使用的是双向链表

image-20251121110801244

B树和B+树的区别

  1. B树的所有节点即存放键（key），也存放值（value）；B+树只有叶子结点存放key和value，非叶子结点只存放key
  2. B树的叶子节点是独立的；B+树的叶子节点有一条链表指向相邻叶子节点。
  3. B树的检索过程不稳定，可能还没有到达叶子节点，检索就结束了。但是B+树每次检索数据都要检索到叶子节点，查找比较稳定。
  4. B+树的范围查询只需要对链表进行遍历即可。

为什么用B+树而不是红黑树？

  1. 树高

红黑树是一种特殊的平衡树，想要覆盖大量键，树高很容易升高，在磁盘上就多一层随机I/O

B+ 树每层能存放几百上千指针，树高很小，随机 I/O 很低

  1. 单位

红黑树的单位是一个对象，想要获取连续的数据，需要随机读

B+ 树的单位是页，一次读取一个节点，就等于读取这一页的数据

  1. 范围查询

红黑树的范围查询需要不断的进行中序遍历，耗时

B+ 树叶子节点之间有双向链表，对于范围查询很有帮助

  1. 维护成本

红黑树每次插入/删除都可能触发旋转和颜色翻转

B+树的插入/删除是以页为单位进行的，能够批量移动

索引的分类

  1. 按照逻辑作用

主键索引、唯一索引、普通索引、联合索引、全文索引

  1. 数据结构/算法

空间索引、B+树、Hash索引

  1. 物理索引

聚簇索引、非聚簇索引、覆盖索引

为什么B+树三层能存储2000万数据？

po_diagram (1)

po_diagram (2)

Innodb&MyISAM在存储结构的区别？

Innodb—聚簇索引：叶子结点包含了完整的数据记录

MyISAM—非聚簇索引：叶子节点存的是主键值，通过主键值去聚簇索引找到完整数据

索引分类

  1. 主键索引

每个表只能有一个主键索引

特性

  • 强制唯一性约束，不允许NULL值
  • 是表的主标识符

应用场景

  • 作为表的行标识符
  • 需要快速通过主键值检索单行数据
  • 建立表与表之间的关系
  1. 唯一索引

确保索引列或列组合的值唯一的索引

特性

  • 允许NULL值（通常只能有一个NULL值）
  • 可以创建在多个列上
  • 不同于主键索引，一个表可以有多个唯一索引

应用场景

  • 需要确保业务数据唯一性的列（身份证、邮箱等）

  • 代替主键索引

  1. 普通索引

最基本的索引类型，没有唯一性约束

特性

  • 仅用于加速查询，不强制数据唯一性
  • 可以包含NULL值
  • 创建和维护成本相对较低

应用场景

  • 常用于WHERE条件、JOIN条件和ORDER BY子句中的列
  • 查询频繁但不需要唯一约束的列
  1. 联合索引

建立在多个列上的索引，也称为复合索引，其本质是一个二级索引

特性

  • 索引顺序很重要（遵循最左前缀原则）

“最左前缀原则”（Leftmost Prefix Rule）指的是：

当你为表建立了联合索引（例如 INDEX (a, b, c)），MySQL 在检索、排序、分组时，只能从最左边开始，连续地利用索引里的列。也就是它能用上 a，或 a,b，或 a,b,c，但跳过中间某列就不行。

  • 可以是唯一索引或普通索引
  • 比单列索引能覆盖更复杂的查询条件

应用场景

  • 多列组合查询频繁的情况
  • 需要覆盖索引优化查询性能时
  • 示例：INDEX idx_name_age (name, age)
  1. 全文索引

通过关键字的匹配来进行查询过滤,就是全文索引

只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

和常用的模糊匹配使用 like + % 不同，全文索引有自己的语法格式，使用 match 和 against 关键字

应用场景

  • 文本内容的模糊匹配和关键词搜索
  • 替代效率低的LIKE '%keyword%'查询
  • 搜索引擎类应用

索引的利弊

优点

  1. 保证建立索引的列唯一性，生成唯一的rowId
  2. 有效缩短数据的检索时间
  3. 加快表与表之间的连接
  4. 加快分组和排序顺序

缺点

  1. 创建、维护时间成本大
  2. 空间成本大
  3. 影响增删改的效率

什么情况下需要建立索引？

  1. 数据量大
  2. 需要排序的字段
  3. 表与表连接用于多表查询的约束条件建立索引

回表查询、索引覆盖、索引下推、索引跳跃

  1. 回表查询

假设执行 select name, age from user where name=‘X’;
通过where name=‘X’ 看有没有对应的name索引，或者包含name的索引
找到二级索引 idx_name(name)
但它的叶子页只保存 name + 主键 PK，不包含 age。
于是优化器用 idx_name 找到主键，再回到聚簇索引页取出整行获取 age

说白了：查询的字段，二级索引没有包含

image-20251121123727501

  1. 索引覆盖

想要查询通过name查询age，由于二级索引idx_name_age包含了name，通过name定位到叶子结点，查询的age字段索引也包含，直接将结果返回

说白了：你要根据条件查找的列，索引里全都有，于是直接在索引里把结果拿走，不用再回到主键树里取整行数据，需要说明的是如果有索引idx_name，当select name from user where name = 'jasonqian'这也属于索引覆盖，因为你要根据条件查找的列，索引里全都有

image-20251121123751141

  1. 索引下推

说白了：在索引的时候，在索引里判断把不合格的记录排除，再去回表拿剩下的少数几行。这样回表次数大幅减少，查询更快。

image-20251121123814756

  1. 索引跳跃

若复合索引为 (age, gender)，而查询只给了 gender = ‘m’ 没有 age 条件，
优化器就会启用 索引跳跃 (Skip Scan)：
先枚举可能的 age 值（例如 18、20），
分别在 (age, *) 区间内利用 gender = ‘m’ 做范围查找，
从而避免全表扫描。

image-20251121123832998

复合索引 (A, B) 下，只给了 B 的条件时，优化器会按 A 的每个不同取值 逐个做 小范围扫描（A=某值 AND B 满足条件），再把结果合并，这样可以避免全表扫描

**【问题】**查询age = 11，不是已经满足索引覆盖的条件了吗？age在复合索引(gender, age)中了

【答】：因为没有满足最左侧条件

image-20251121124103365

索引失效的场景

  1. 不满足最左匹配原则
  2. 使用了SELECT *
  3. 索引列参与运算
  4. 索引列使用了函数
  5. 使用了错误的LIKE语句
  6. 类型隐式转换
  7. 使用OR且OR左右其中一列没有索引
  8. 不等于比较
  9. ORDER BY导致索引失效

索引越多越好吗？

  1. 每次增删改操作都要维护所有相关索引结构（更新B+树），写入时间上升
  2. 空间占用大

日志

执行一条 update 语句，期间发生了什么？

image-20251121124335208

三个日志的区别

  • undo log 记录的是数据修改前的状态。
  • Redo Log记录的是对数据库页的物理修改操作，即每次事务对数据页进行更改后，都会将变更以“redo record”的形式写入Redo Log。
  • bin log 记录的是数据库的逻辑修改事件（可以是 SQL 语句，也可以是行级别的修改记录，取决于格式），且只记录已提交的事务。

undo log

undo log 记录的是数据修改前的状态，例如

  • 执行 UPDATE 语句时，undo log会记录修改之前的数据
  • 执行 DELETE 语句时，undo log 会记录被删除行的完整数据（以便回滚时恢复）
  • 执行 INSERT 时，undo log 会记录插入的行信息（回滚时直接删除这行）

应用场景

  1. 回滚事务：当事务需要被回滚时，通过Undo Log可以恢复到事务开始前的数据状态。
  2. MVCC（多版本并发控制）：InnoDB利用Undo Log来提供不同事务之间的一致性读视图，使得事务可以看到其他事务未提交之前的旧版本数据，从而避免锁竞争，提高并发性能。

image-20251121124520502

undo log版本链

我们每条数据其实都有两个隐藏字段，一个是trx_id，一个是roll_pointer

  • trx_id：当前更新这条数据的事务ID
  • roll_pointer指向上一个版本的指针

image-20251121124613676

事务A id为50进行操作

事务B id为58进行操作

事务C id为69进行操作

redo log

Redo Log记录的是对数据库页的物理修改操作，即每次事务对数据页进行更改后，都会将变更以“redo record”的形式写入Redo Log。

应用场景

  • 数据库崩溃恢复：当系统发生异常重启或宕机时，通过重放Redo Log，能够确保已提交事务的修改不会丢失，保证了事务的持久性。
  • 避免频繁刷盘：InnoDB采用WAL（Write-Ahead Logging）策略，先写日志再修改磁盘数据，这样可以在一定程度上减少磁盘I/O，提升写入性能。

image-20251121124825110

buffer pool

image-20251121124845909

redo log刷盘时机

Redo log刷盘策略，innodb_flush_log_at_trx_commit，取值（0/1/2）

  • 0：每隔一秒把log buffer刷到文件系统中
  • 1：每次事务提交的时候，都把log buffer刷到文件系统中，立即刷新到磁盘上去
  • 2：每次事务提交的时候，都把log buffer刷到系统文件中，但不会立即写入磁盘

image-20251121125024743

日志文件组

image-20251121125114385

在这个日志文件组中还有两个重要的属性，分别是 write pos、checkpoint

  • write pos 是当前记录的位置，一边写一边后移
  • checkpoint 是当前要擦除的位置，也是往后推移，检查点的推进依赖于脏页被成功刷新到磁盘的数据页上

当日志环将满时，InnoDB 会把最早那批未落盘的脏页刷进表空间文件，使 checkpoint 前移、Redo 日志重新腾出空间，再继续写后面的改动

两阶段提交

MySQL 为了避免出现redo log和bin log之间的逻辑不一致的问题，使用了「两阶段提交」来解决

两阶段提交是将单个事务的提交拆分成了2个阶段，【准备阶段】、【提交阶段】

image-20251121125257258

  • 【prepare阶段】：将XID（内部XA事务的ID）写入redo log中
    同时将redo log对应的事务状态设置为prepare
    将redo log持久化到磁盘
  • 【commit阶段】：把XID写入binlog，然后将binlog持久化到磁盘，
    调用引擎的提交事务接口，将redo log状态设置为commit

在两阶段提交的不同时刻，MySQL 异常重启会出现什么现象？

image-20251121125358234

image-20251121125413731

redo log思考

只要每次把修改后的数据页直接刷盘不就好了，还有 redo log 什么事？

【答】因为数据页大小为16KB，刷盘比较耗时间，如果写redo log，一行记录可能就占几十Byte，刷盘速度很快

bin log

bin log 记录的是数据库的逻辑修改事件（可以是 SQL 语句，也可以是行级别的修改记录，取决于格式），且只记录已提交的事务

作用

  • 主从复制：主库把自己的 Binlog 推送给从库，从库按顺序 replay，保证数据一致
  • 数据恢复：通过重放 Binlog 恢复数据到某个时间点或事务状态

三种格式

  1. Statement
    • 记录每一条SQL语句
    • 优点：日志量小
    • 缺点：对于非等幂操作（NOW(), UUID()）可能导致数据不一致
  2. Row
    • 记录每一行数据的变化
    • 更加精确，可避免语句级问题
    • 缺点：日志量大
  3. Mixed
    • 混合模式，看实际操作选择Statement、Row

bin log刷盘时机

对于 InnoDB 存储引擎而言，只有在事务提交时才会记录bin log ，此时记录还在内存中，那么 bin log是什么时候刷到磁盘中的呢？

bin log刷盘策略，sync_binlog，取值（0/1/N）

  • 0：依赖操作系统定期同步，性能最佳，但崩溃时可能丢失多个事务
  • 1：每次事务提交后同步，确保崩溃时最多丢失一个事务，安全性最高，但性能耗损最大
  • N：每提交N个事务后同步一次，平衡性能和数据安全

中继日志

用于暂存从主库（Master）接收到的二进制日志事件，然后在本地执行的中间存储，确保数据同步的可靠性和持久性

image-20251121125948423

慢查询日志

开启慢查询日志后，设定long_query_time，只要超过这个时间的SQL语句就会被记录下来

事务

事务是逻辑上的一组操作，要么都执行，要么都不执行

分布式事务

  • 2PC：优点是一致性强，缺点是阻塞问题、协调者单点故障，适用于数据一致性要求极高的场景（如金融核心交易）；

  • TCC：优点是性能好、无锁，缺点是业务侵入性强，适用于高并发、短事务场景（如电商支付）；

  • SAGA：优点是支持长事务，缺点是一致性弱（最终一致），适用于长事务场景（如订单履约流程）。

事务的开启方式

  1. 隐式事务：事务自动开启、提交、回滚，比如使用insert、update等语句
  2. 显式事务：需要手动开启、提交、回滚。命令BEGIN，COMMIT，ROLLBACK

事务ACID属性

  1. 原子性（Atomicity）：事务是最小执行单位，要么全部完成，要么全部失败，【由undo log日志来实现】
  2. 一致性（Consistency）：执行事务前后，数据保持一致，【由另外三个特性维持】
  3. 隔离性（Isolation）：并发访问数据库时，事务不会被其他事务干扰【锁和MVCC机制来实现】
  4. 持久性（Duration）：事务提交后，对数据库中数据改变时持久的【redo log日志来实现】

并发事务处理带来的问题

  1. 脏读：一个事务读取了另一个事务未提交的数据
  2. 不可重复读：一个事务内，前后读取的数据不一致。（修改）
  3. 在一个事务中，相同查询条件，返回发结果集的数量不一致（新增）

InnoDB 通过 临键锁 在当前读/写下避免RR ，而普通快照读在 RR 下就看不到新插入。

隔离级别

image-20251121140026797

  1. 读未提交：直接绕过Read View
  2. 读已提交：每一条查询操作都会创建一次Read View

RC只有记录锁（行锁）

  1. 可重复读：第一次查询操作的时候创建一次Read View

RR有临键锁（记录锁+间隙锁）

  1. 串行化：在可重复读的情况下，将Select 语句替换为 Select … lock in share mode，同时也会给读到的区间加锁

RC和RR的锁有什么区别？

  1. 读已提交（RC）

RC保证了读已提交的数据，只看其他事务已经提交的东西，其他事务还在编辑、没提交的内容，看不到

RC通常使用行锁或者MVCC的方式，保证事务之间的隔离性

  1. 可重复读（RR）

RR保证了第一次快照读创建的Read View会贯穿整个事务，所以多次普通 Select 读取结果是一致的，解决了不可重复读问题

MVCC机制

快照读和当前读

image-20251121140316679

  1. 快照读

使用MVCC实现

不加锁

  1. 当前读

使用临键锁（记录锁 + 间隙锁）实现

加锁

直接读物理行的当前版本，如果有并发写锁着它，就等待，等到对方提交并释放锁，再读最新已提交的版本

RR隔离级别完全解决了幻读吗？

没有，比如下面这种情况：

  1. 事务A使用快照读读取了3条数据
  2. 事务B插入ID = 100的数据并保存
  3. 此时事务A使用当前读就会读取到4条数据

这就产生了幻读现象

  • 解决方法：在事务A的时候加间隙锁，此时事务B就不能插入数据了

MVCC(Multi-Version Concurrency Control)多版本并发控制

可以做到读写不阻塞，且避免了类似脏读这样的问题，主要通过undo Log版本链和Read View读视图来实现。在读取数据的时候通过ReadView四个参数和Undo Log版本链中的值进行比较，判断哪一个trx_id的数据可以读取。

Read View读视图

image-20251121140545390

image-20251121140559582

【理解】

image-20251121140628710

当使用快照读的时候，会生成一个Read View，给这个Read View分配一个creator_trx_id，同时会记录下这一瞬间仍在跑的事务id列表。

判断一行数据能不能从redo log中看见，根据（当前行由事务Id X进行修改）

  1. 如果X == creator_trx_id，代表是由当前事务自身进行修改的，可以查看
  2. 如果X < min_trx_id，代表X事务不在活跃列表之前，已经被提交了，可以查看
  3. 如果X ≥ max_trx_id，这行来自拍照之后还未开启/未提交的事务 → 这张快照里永远不可见
  4. 如果min ≤ X < max，看X事务Id是否属于m_ids，① 在 → 不可见（还没提），②不在 → 可见（已提）

在可重复读（RR）隔离级别下，只会在第一次执行快照读获取一次read view

在读已提交（RC）隔离级别下，同一个事务里面，每一次执行快照读都会产生一个新的Read View副本，这样就可能造成同一个事务里前后读取数据可能不一致的问题（不可重复读并发问题）

Read View什么时候会消失？

  • 在读已提交（RC）隔离级别下，语句结束，Read View就消失了
  • 在可重复度（RR）隔离级别下，事务结束，Read View就消失了

MySQL调优

框架

以下内容是挡在MySQL前面的，也就是说读取数据的时候先走它们，再走MySQL

  • 读写分离
  • 主从复制
  • Redis缓存
  • Java中的数据结构：在项目启动的时候，先从数据库里查询常用的数据存储到，如List，Map中，使用的时候直接获取
  • 数据量大——>TIDB，数据量更大——>分库分表（数据量大的时候分表，并发高的时候分库）

硬件

  1. 内存大于32g

MySQL存在 buffer pool ，对于数据修改，先修改内存中的数据，再将内存中的数据刷入磁盘中

buffer pool 占MySQL内存的 75% ~ 80% 之间

  1. CPU

MySQL的连接数（Druid连接池），最优情况下：CPU核心数 * 2（如果是固态硬盘还可以 + 1）

SQL优化

  1. 禁止使用 Select *

会读取多余字段

失去覆盖索引的优化机会，查询缓慢

  1. 共享数据

比如在一个业务中两个接口都同时查询一条数据，并且A接口要调用B接口，此时可以只在B接口查询，将结果通过参数传递给A

  1. 深分页的问题如何解决？

什么是深分页？

使用如下SQL语句查询满足update_time条件的数据，跳过前100000条，取出10条数据

1
select * from account where update_time >= '2025-09-23 12:30:59' order by update_time limit 100000, 10;

由于这条SQL语句执行顺序是 from -> where -> select -> order by -> limit，在分页之前，比如满足 update_time >= ‘2025-09-23 12:30:59’ 的数据有80万条，此时就会触发80万次回表查询，再将查询到的结果进行limit分页，这样做很耗时间。

【解决方法】

  • 选择上 / 下一页

【注意】id需要为整形，并且自增的

前端需要将最后一条数据的id发过来，以及查询的条数就可以加快查询了

1
select * from account where id >= 100000 limit 10;
  • 手动选择第 N 页

子查询优化

【注意】需要一个查询条件，且有索引（update_time）

1
select * from (select id from account o where o.update_time >= '2025-09-23 12:30:59' order by o.update_time limit 100000, 10) as temp inner join account on temp.id = account.id;

在子查询中通过二级索引查找满足条件的10条数据，不用进行回表查询

在连表查询中，通过匹配 id 获取满足条件的10条数据

此时只需要经历10次回表查询

  1. 更新什么就改什么，而不是将全部字段update回去

update的时候，将一条数据所有信息查询出来放在一个对象中，只更新其中一个数据，再将整个对象update到数据库中，在高并发场景下，会造成数据不同步问题

比如，我第一个方法查出一条数据所有信息为a = 1，b = 1，我只修改b的值，a = 1， b = 2；这个时候另一个方法也查出这条数据的所有信息为a = 1，b = 1，此时它只修改a的值为a = 2，b = 1；第一个方法先update，将b的值改变；
第二个方法再update，a的值改变，同时将b的值恢复，这就会造成数据不同步

  1. 日期类型选择
  • datetime：8字节（占用大）
  • timestamp：4字节（可以显示1970-1-1到2038的时间戳，推荐）
  • data：3字节（显示时间不完全）
  1. 字符类型选择
  • 数字范围很小用tinyint占1个字节，相比较于int占4个字节
  • 如果确定字符数量长度，用char(1)性别、char(11)电话号码

系统配置

  1. Redo log刷盘策略，innodb_flush_log_at_trx_commit取值（0/1/2）
  • 0：每隔一秒把log buffer刷到文件系统中
  • 1：每次事务提交的时候，都把log buffer刷到文件系统中，立即刷新到磁盘上去
  • 2：每次事务提交的时候，都把log buffer刷到系统文件中，但不会立即写入磁盘
  1. bin log刷盘策略，sync_binlog，取值（0/1/N）
  • 0：依赖操作系统定期同步，性能最佳，但崩溃时可能丢失多个事务
  • 1：每次事务提交后同步，确保崩溃时最多丢失一个事务，安全性最高，但性能耗损最大
  • N：每提交N个事务后同步一次，平衡性能和数据安全

慢SQL查询日志

  1. slow_query_log
  2. explain字段
1
explain select * from orders where status = '1';

看查询语句的效率，有如下几个参数

  • key：实际使用的索引名，如果是NULL就没有索引
  • key_len：使用索引的长度
  • rows：预计扫描的行数
  • type：访问类型

访问层级：System > Const > Eq_ref > Ref > Range > Index > All

  1. System：表中只有一行数据，直接返回
  2. const：查询命中主键或唯一索引的全部列，性能非常好，只会读取一次索引

比如select name from user where id = 3;

  1. eq_ref：只会出现在多表连接（JOIN）时，而且是被驱动表（也叫第二张表、右表）用主键或唯一索引进行等值匹配，并且这些索引列都不能为 NULL。

比如Select * from user join orders on user.id = orders.user_id;o.user_id是唯一索引

  1. ref：查询通过非唯一索引（普通 KEY/INDEX）、唯一索引的非全部列，或联合索引的最左前缀列做等值匹配（= 条件）

  2. range：索引范围查找，条件是 <, >, between, like ‘abc%’ 等，使用索引列的范围条件（遵守最左前缀原则）

比如：SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

  1. index：扫描整个索引树，而不是表的所有行，查询只用到索引列（覆盖索引），不必回表

比如：select id from orders;，如果id是主键，这就是index扫描

  1. all：没有任何可用索引，直接从磁盘扫描
  • Extra：额外信息
  1. Using where：查询走了索引，但是仍要在取出的数据上做一次where查询

使用了索引跳跃，在索引层做了条件判断

1
2
3
4
5
6
7
8
9
10
user (  
    id primary key,  
    name,  
    age,  
    index idx_age(age)
)

select * from user where age > 20 and name = 'tom';

# age走了索引，name = 'tom'取出数据后过滤 —> Using where
  1. Using index：使用了覆盖索引
1
2
3
4
5
6
7
8
user (  
    id primary key,  
    name,  
    age,  
    index idx_name(name)
)

select name from user where name = 'jasonqian';
  1. Using where; Using index;
1
2
3
4
select age, name from user where age > 20

# 索引idx_age_name覆盖了age和name —> Using index
# 条件age > 20需要范围扫描，进行过滤 —> Using where
  1. Using filesort：排序没法完全靠索引顺序完成，必须额外做排序操作
1
2
3
4
5
6
7
8
# 触发场景
# order by 列没有用上索引
index idx_age(age)
select * from user order by name;

# order by 列不符合最左前缀原则
index idx_name_age(name, age)
select * from user order by age

索引

  1. 最左前缀原则
  2. 索引覆盖
  3. 索引下推
  4. 主键索引尽量使用自增主键，减少页分裂

因为InnoDB索引底层数据结构为B+树，它需要保证内部有序，通过主键自增的方式，让b+树增加新的数据时，永远加在最后一个叶子结点尾部

  1. 不使用like
  2. 版本8.0之前，在使用order by的时候，不要既使用asc，也用desc

在8.0之前，索引排序方向必须一致，一旦order by混用了asc和desc，没法用B+树索引顺序，只能走filesort

  1. 索引列不做函数运算、不计算
  2. or两边其中一个没有索引就会导致全表扫描

在第一个事务的update中，如果where 后面的字段没有索引，会导致第二个事务update锁表吗？

第一个事务中：update table set name = 'tmo' where num = 1;

第二个事务中：update table set name = 'tmo' where num = 1;

这要分情况：

  • 如果是读已提交RC隔离级别下，不会锁表；
  • 如果是可重复读RR隔离级别下，会锁表

零碎知识点

内连接和外连接的区别

内连接保留两张表拥有的数据

外连接分为左外连接、有外连接、全外连接，匹配不到的地方用NULL填充

MySQL六大约束

  1. 主键约束
  2. 非空约束
  3. 唯一约束
  4. 默认值约束
  5. 外键约束