MySQL 知识点总结

知识清单图

常见的概念

事务四大特性：原子性，一致性，隔离性，持久性 ACID；

MySQL InnoDB默认隔离级别是【可重复读】；

数据是按【数据页】为单位来读写，默认大小为【16KB】;

理解流程

查询流程

从 select * from st_table where id=1 理解mysql的执行流程

（图片来源，xiaolincoding.com中图解mysql, 侵权删）

概要说明，详细内容参考 [这里](执行一条 select 语句，期间发生了什么？ | 小林coding)

MySQL = Server层 + 存储引擎层

Server 层负责建立连接、分析和执行 SQL；

存储引擎层负责数据的存储和提取；

Server层 = 连接器 + 查询缓存 + 解析SQL + 执行SQL + 存储引擎层交互(API接口)

连接器，基于 TCP 协议(三次握手，四次挥手);

查询缓存，鸡肋(命中率比较低)，MySQL 8.0已删除；

解析SQL，解析器 = 词法分析 + 语法分析，构建 SQL 语法树

只检查语法，如单词拼写错误，把 from 写成了 form；

表不存在或者字段不存在，是在预处理器上检查

执行SQL，

预处理器，a. 检查表或字段是否存在； b. 拓展 select * 中的 * 成表上的所有列

优化器，确定查询语句的执行方案，如主键索引，全表扫描，覆盖索引

执行器，与【存储引擎】交互，返回记录给客户端

更新流程

从 UPDATE t_user SET name = 'xiaolin' WHERE id = 1;

再次理解mysql的流程

Server层

客户端通过连接器，与MySQL建立连接；

update语句，不经过查询缓存；

解析器通过词法分析构建语法树，语法分析检查语法正确性；

预处理器，检查表和字段是否存在；

优化器，确定执行计划，本语句中id是主键索引；

执行器，与存储引擎交换，找到这行，并且更新；

详细解析执行器：

调用接口，通过主键索引，找id=1的记录: ==》找记录

若在 buffer pool 中，直接返回记录给执行器更新；

若不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器更新。

执行器得到聚簇索引记录后，对比更新前后的记录是否一样：==》对比记录

若一样，不进行后续更新流程；

若不一样，把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；

开启事务， InnoDB 层更新记录前，先记录相应的 undo log，生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log。 ==》生成undo log 和 redo log

InnoDB 层开始更新，先更新内存（同时标记为脏页），将记录写到 redo log，这个时候更新就算完成了。使用 WAL 技术，MySQL 的写操作是先写 redo 日志，在合适的时间再将修改的行数据写到磁盘上。 ==》标记脏页和写redo log 涉及 WAL技术

至此，一条记录更新完了。更新完成

一条更新语句完成后，记录对应的 binlog，保存到 binlog cache，不会刷新硬盘的 binlog 文件，在事务提交时才会统一将所有 binlog 刷新到硬盘。

事务提交，【两阶段提交】

更新操作中涉及的日志，undo log（回滚日志）、redo log（重做日志）、binlog （归档日志）

undo log（回滚日志） ： Innodb 存储引擎层生成的日志，实现了事务中的原子性，用于事务回滚和 MVCC。

redo log（重做日志） ： Innodb 存储引擎层生成的日志，实现了事务中的持久性，用于掉电等故障恢复；

binlog （归档日志） ：Server 层生成的日志，用于数据备份和主从复制；

说明：

undo log 记录增删改的命令的"逆命令"(笔者自创)，增=>删除，删=>插入，改=>旧值

redo log， a. 涉及到WAL(Write-Ahead Logging，不立刻写到磁盘，先写日志，之后再合适时间再调用线程写入磁盘)技术; b. 将写操作从【随机写】变成【顺序写】；c. 循环写，类似环形;

binlog，a. 记录所有数据库表结构变更和表数据修改的日志，不记录查询类的操作；b.

undo log 与 redo log

区别	redo log	undo log
时机	事务完成后	事务完成前
记录值	新值	旧值

redo log 与 binlog

区别	redo log	binlog
适用对象	Innodb 存储引擎层	Server层，所有存储引擎都可以用
文件格式	物理日志，记录具体数据页的修改操纵，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新	3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED
写入方式	循环写，会覆盖	追加写，不覆盖
用途	掉电等故障恢复	备份恢复、主从复制

MySQL 集群的主从复制

3个阶段: 1. 写入 binlog； 2. 同步 binlog； 3. 回放binlog；

主要三种模型： a. 同步复制； b. 异步复制；c. 半同步复制；

存储引擎的Buffer Pool

目的：提高数据库的读写性能；默认大小：128MB；

以页为基本单位，存放：数据页、索引页、插入缓存页、undo页、自适应哈希索引、锁信息；

管理空闲页，管理脏页：链表；

提高缓存命中率：简单的 LRU 算法(MySQL为使用，因易预读失败和Buffer Pool污染)，加入young、old的LRU算法；

脏页刷新到磁盘，WAL策略，刷新时机：1. redo log 满了；2. buffer pool 空间不足；3. MySQL认为空闲；4. MySQL关闭前。

两阶段提交

目的：避免redo log 和 binlog 持久化到磁盘中，由于各种原因，导致两份日志逻辑不一致；

两阶段提交：将redo log持久化事务拆分成两步，其中穿插写入binlog

准备阶段

提交阶段

优点：保证了两份日志的一致性；

缺点：1. 磁盘I\O次数高，性能差； 2. 锁竞争激烈；

一行记录的存储格式

以InnoDB为例子，创建表会有三个文件：db.opt, table.frm(表结构), table.idb(表数据)

表空间文件的结构：

Segment 段，索引段，数据段，回滚段

Extent 区，数据量大时，索引按照【区】为单位分配，大小为【1MB】，对于默认页是连续63页为一个区

Page 页，数据以【页】为单位来读写，默认页大小【16KB】

row 行，重点⭐

4种行格式

Redundant

Compact

Dynamic

Compressed

详细说明Compact 图片来源[小林图解MySQL](MySQL 一行记录是怎么存储的？ | 小林coding)

记录额外信息

变长字段长度列表，逆序存放，表中全部没有变长字段就会省略

NULL值列表，逆序存放，表中全为NOT NULL会省略，至少节约1B

记录头信息

记录真实信息

行溢出后如何解决？

数据过多的时候会发生行溢出，多的数据会保存在【溢出页】；保存部分真实数据，然后用20B记录指向溢出页的地址。

索引

目的：提高查找效率；

优点：提高查询速度；缺点：占用物理空间，创建和维护都要耗费时间，降低表的增删改的效率；

需要索引的条件：

字段有唯一性限定，比如身份唯一；

常用于where查询场景很多；

常用于 group by或 order by场景；

不需要索引的条件：

表数据很少

经常更新的表

where group by order by 不经常使用

字段中存在大量重复数据

索引分类

分类角度	具体分类
数据结构	B+tree索引，Hash 索引， Full-text 索引
物理存储	主键索引(聚簇索引)，二级索引(辅助索引)
字段特性	主键索引，唯一索引，普通索引，前缀索引
字段数量	单列索引，联合索引

索引优化

前缀索引优化
覆盖索引优化，二级索引查询，避免了大量回表操作
主键索引最好自增，若是自增就是追加操作
索引最好设置NOT NULL，可以更简单，且省略一个字节空间
防止索引失效

B+树

结构：叶子节点存放数据，非叶子节点存放目录项作为索引；叶子节点间构成有序的双向链表；

特点：a. 单点查询更多；b. 插入、删除效率高；c.可范围查询;

主键索引与二级索引：

主键索引，叶子节点存放实际数据值；

二级索引，叶子节点存放索引+主键值；

B+Tree vs B Tree，单个节点数量更小，能查询更多节点，更广泛的适用场景

B+Tree 只在叶子节点存储数据，而 B 树的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。

另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

B+Tree vs 二叉树，B+Tree磁盘 I/O 次数更少

数据达到千万级别时，B+Tree 的高度依然维持在 3~~4 层左右，即一次数据查询操作只需要做 3~~4 次的磁盘 I/O 操作就能查询到目标数据。

B+Tree vs Hash，更广泛的适用场景

Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。

索引失效

回表和覆盖索引

回表，简单来说，检索两棵B+树；

select * from st_table where name='Alice' //name是二级索引

先检索【二级索引】B+树，叶子节点找到主键值；

再检索【主键索引】B+树，根据主键值，获取数据;

覆盖索引，本来检索两棵，但实际只用检索一棵

select id,name from st_table where name='Alice' //name是二级索引

检索【二级索引】B+树，叶子节点获取全部数据；

索引失效的情况：索引失效，就会变成全表扫描，失去了索引的优势

对索引使用左模糊或左右模糊 %xx, %xx%
对索引使用函数，length(name)=6
对索引使用表达式计算，id+1=10
对索引使用隐式类型转换，phone=100
对联合索引最左匹配， (a, b, c)联合索引 where b=1 and c=3
where子句中的OR，where 索引列 or 非索引列

事务

特性：ACID 原子性，一致性，隔离性，持久性；

原子性 undo log

持久性 redo log

隔离性 MVCC

一致性原子性 + 持久性 + 隔离性

并发事务

可能出现问题：脏读，不可重复读，幻读

隔离级别：读未提交，读提交，可重复读(默认)，串行化；隔离级别越高，运行效率越低。

可重复读和读提交

MVCC：多版本并发控制，通过【版本链】来控制并发事务访问同一个记录的行为；

Read View: 类似快照；

	Read View 生成时机	问题
可重复读	事务启动	幻读
提交	每次读取数据	不可重复读，幻读

可重复读在很大程度上避免了幻读。

不可重复读：在一个事务内多次读取同一个数据

幻读：一个事务内多次查询某个符合查询条件的「记录数量」

区别在于：结果，一个是记录数据，一个结果集

对于快照读，通过MVCC解决幻读；

对于当前读，通过next-key lock(记录锁+间隙锁)解决幻读；

注意，只是很大程度上避免了幻读，但没有全部解决幻读

锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁

按粒度分类：全局锁，表级锁，行级锁(重点)；

行级锁的类型：1. Record Lock 记录锁；2. Gap Lock 间隙锁；3. Next-Key Lock Record Lock + Gap Lock

锁类型	分类	目的	其他
Record Lock	X锁、S锁，彼此不兼容，只有SS兼容	锁住记录
Gap Lock	X 型间隙锁和 S 型间隙锁，彼此兼容	锁定一个范围，防止插入幻影记录	插入意向锁(特殊的间隙锁)
Next-Key Lock	X锁、S锁，彼此不兼容，只有SS兼容	锁定一个范围，并且锁定记录本身

不同场景，对应不同的加锁形式；

加锁的对象是【索引】，加锁的基本单位是【next-key lock】，next-key lock 是前开后闭，而间隙锁是前开后开**。

在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成记录锁或间隙锁。

注意： update 语句的时候，确保 where 条件中带上了索引列，否则可能索引失效，扫描全表，导致给全表加上Next-Key Lock

死锁发生的四大条件：互斥、占有且等待、不可强占用、循环等待

参考资料

小林-图解MySQL

总结MySQL