mysql

1. SQL执行顺序

1、FROM：选择FROM后面跟的表，产生虚拟表1。

2、ON：ON是JOIN的连接条件，符合连接条件的行会被记录在虚拟表2中。

3、JOIN：如果指定了LEFT JOIN，那么保留表中未匹配的行就会作为外部行添加到虚拟表2中，产生虚拟表3。如果有多个JOIN链接，会重复执行步骤1~3，直到处理完所有表。

4、WHERE：对虚拟表3进行WHERE条件过滤，符合条件的记录会被插入到虚拟表4中。

5、GROUP BY：根据GROUP BY子句中的列，对虚拟表4中的记录进行分组操作，产生虚拟表5。

6、AGG_FUNC：常用的 Aggregate 函数包涵以下几种：（AVG：返回平均值）、（COUNT：返回行数）、（FIRST：返回第一个记录的值）、（LAST：返回最后一个记录的值）、（MAX： 返回最大值）、（MIN：返回最小值）、（SUM： 返回总和）。

7、WITH 对虚拟表5应用ROLLUP或CUBE选项，生成虚拟表 6。

8、HAVING：对虚拟表6进行HAVING过滤，符合条件的记录会被插入到虚拟表7中。

9、SELECT：SELECT到一步才执行，选择指定的列，插入到虚拟表8中。

10、UNION：UNION连接的两个SELECT查询语句，会重复执行步骤1~9，产生两个虚拟表9，UNION会将这些记录合并到虚拟表10中。

11、DISTINCT 将重复的行从虚拟表10中移除，产生虚拟表 11。DISTINCT用来删除重复行，只保留唯一的。

12、ORDER BY: 将虚拟表11中的记录进行排序，虚拟表12。

13、LIMIT：取出指定行的记录，返回结果集。

2. 基础概念概述

客户端，服务端（tcp/ip连接），查询优化器，存储引擎，系统变量（会话级别全局级别），字符编码（utf8mb3 mb4 一个占3字节，一个4，emoji，varchar存汉字大小除以3-4，2w多字（null值列表是否删除都占空间），排序规则，文件目录结构（表空间，数据存储的文件机构），这边也略过了。

配置文件（永久）和启动配置项（临时）：配置项优先级高

数据库 表字符编码，存储引擎建议一致

创建数据库，表，可指定存储引擎，行格式，字符编码，字符排序规则

3. 存储引擎

myisam-不支持事务，数据和索引分开，索引存的地址偏移量，回表速度快（但是要行格式定长下），只有表锁

innodb存储结构-默认使用，支持事务，B+树索引，行锁表锁间隙，插入意向锁等，事务MVCC，

4. InnoDB存储结构

记录 行格式

基础compact，5.7默认dynamic（行溢出时不存部分数据而是所有数据都甩出去，指针引用），一行记录指针在中间，往右是隐藏列和真实字段数据，往左是记录头，null值列表，变长字段列表（逆序）。

• 隐藏列：

  • row_id，没主键或唯一字段，加一个row_id
  • 事务id，标识是哪个事务在用
  • 回滚指针：事务回滚用

• 记录头：

  • 标记是否被删除，被删除数据放到垃圾链表，其他数据新增时覆盖
  • 非叶子节点最小记录，非叶节点索引指向用
  • 记录数，统计，页目录分组用
  • 堆位置信息
  • 记录类型，0普通记录，1索引记录，2最小3最大，区别数据是非叶节点还是叶子节点
  • 下一条记录位置，记录之间形成单链表

• 行溢出：一个行数据过大，行溢出，多余数据放到别的页，指针引用指向

页（基本存储单位，一页16kb）

• 文件头：描述文件信息，校验文件

  • 校验和

  • 当前页号

  • 上一页号

  • 下一页号

  • 日志LSN码（log sequeceNumber）

  • 页的类型

  • 文件至少被刷新到了的LSN位置（系统表空间）

  • 页属于哪个表空间

• 页头，数据页专有信息

  • 页目录槽数量

  • 空闲空间地址

  • 记录数（全部）

  • 删除数据链表头

  • 已删数据占用空间

  • 最后插入记录地址

  • 记录插入方向

  • 连续同向插入的记录数

  • 存活记录数量(不计入虚拟记录和被删除记录)

  • 修改当前页的最大事务id

  • 索引id

  • 叶子字段头信息（Root）

  • 非叶字段头信息（Root）

• 最大记录、最小记录，虚拟记录，指向实际最大最小记录

• 用户记录，存放记录

  • 记录之间经过排序，用行格式文件头信息的next_record形成单链表

• 空闲记录，空闲空间

• 页目录，对记录分组，根据记录的头信息的n_owned字段对记录分组，便于二分

• 文件尾：与文件头对应，校验文件完整性

  • 前4字节：校验和（与文件头对应，写入完成时两者一致说明写成功）
  • 后4字节：最后修改时的LSN（配合文件头的LSN，双重校验磁盘IO完整性）

页内查找过程（两步走）

1. 二分查找定位槽：通过页目录的槽（slot）进行二分查找，快速定位到目标记录所在的分组
2. 链表遍历：通过记录的next_record属性，在分组内遍历找到目标记录

页目录分组规则（n_owned）

• 最小记录所在分组：只能有 1 条记录
• 最大记录所在分组：1~8 条记录
• 其他普通分组：4~8 条记录

文件头串联多页

多个数据页通过文件头的「上一页号」和「下一页号」组成双向链表，这是B+树同层节点遍历的基础。非叶节点之间、叶子节点之间各自形成独立的双向链表。

表空间

当前版本，一个表有表级独立表空间，也有系统表空间

段

对区中的叶子节点，非叶节点分开存储，便于遍历，提高查询效率

区

顺序的一片空间（64个页，1m大小）256个区（256M）一组，避免创建数据页时随机io，保证顺序io

碎片区

以及其他段，独属表空间，属于系统表空间，用于放零碎数据（避免空间浪费）

5. 索引原理

索引

空间换时间，提高查询效率，新增删除需要维护数据。

聚簇索引（数据即索引，存全部数据，根据主键排序）

核心思想：B+树本质上是页结构的逐层分组扩展。

页通过文件头标识类型，大致分根节点，非叶节点（记录页），叶子节点（数据页）

• 根节点独有，第一个数据先创建根节点，根节点不变

• 根据二分实现插入时排序，依赖文件头的插入方向，连续插入位置等

• 数据扩容形成叶子节点（数据页），每页记录由链表存储，页目录对记录分组，又一次二分

• 叶子节点之间形成双向链表（基于文件头的上下页号，遍历用）

• 叶子节点过多，不好遍历时，扩容形成非叶节点（记录页）

  • 不存数据，存kv值，k是最小记录主键值，v是页号
  • 同理，记录页的页目录维护的也是主键值--页号这样的kv，对其分组
  • 同理，记录页之间也形成双向链表（遍历用）

• 非叶节点过多，再扩展一层，索引非叶节点。

非叶节点与叶子节点使用相同的页结构，区别在于：

• 非叶节点的记录 record_type=1（叶子节点 record_type=0）
• 非叶节点只存储「主键 + 页号」，不存真实数据
• 非叶节点的页目录分组的也是「主键 → 页号」这样的kv对

B+树形成过程（从页结构理解）：

1. 一个数据页内，记录通过页目录分组 → 页内二分查找
2. 数据页多了，需要非叶节点把多个页的主键摘出来再分组 → 页间二分查找
3. 非叶节点也多了，再加一层 → 多层B+树

本质上就是：分组 → 再分组 → 再再分组（套娃），每一层都是一次二分。

B树：平衡多路搜索树，叶子和非叶节点都存储数据

• 插入时为了维护数据要左旋右旋，经常变更结构
• 只适合随机检索，不适合顺序检索
• 查询时间复杂度O(n)，树高影响

B+树：在B树基础上，非叶节点只存索引，不存数据，且同级叶子非叶节点通过双向链表链接

• 不须B树那么频繁改变树结构（也要维护）
• 适合随机和顺序检索，范围检索（因为只存索引及双向链表，顺序遍历更方便）
• 查询时间复杂度O(log(n))，相对稳定，树的高度稳定（扁长）

总结：innodb中的索引就是数据，默认一个聚簇索引，B+树结构（复用页结构），依据主键对数据实现了排序，B+树树高稳定，查询稳定，有双向链表的原因，顺序和范围遍历方便，每页的记录之间又形成单链表，又由页目录分组，又进行一次二分，进一步提高效率。

核心：B+树，二分查找，双向链表，进一步二分，单链表

在B+树中查找一条记录的完整过程：

1. 从根节点开始，在非叶节点的页目录中二分查找 → 定位到某个子节点（页号）
2. 进入子节点，继续在页目录中二分查找 → 定位到分组
3. 在分组内通过next_record链表遍历 → 找到目标记录
4. 如果子节点还是非叶节点，重复步骤1-2，直到叶子节点

二级索引

显式创建的索引，只存放部分数据，记录中只有主键-部分数据，用到二级索引提高对特定字段的检索速度

二级索引除了全文索引外都要回表，（回到聚簇索引中取其他字段的值），

联合索引

对多个字段按顺序排序的索引，使用该索引要注意最左前缀，否则失效或者只能用一部分

全值匹配

搜索条件（select中）使用到的字段全是联合索引的字段，不用回表。

最左前缀

查询条件（where后边，包括group和order）字段顺序必须要和联合索引的顺序一致。从左到右依次，否则失效或只能用部分，或者干脆走全表扫描

匹配列前缀

字符串like '%abc%'时，左边带通配符%无法使用索引，只有确定前缀顺序abc%才走

匹配范围值

索引天然有序，所以范围查询是很快的，

1. 联合索引只有对最左边的索引范围查找才生效
2. 联合索引可以先对左边的索引等值查询再对右边的值范围查找。例如name="aaa" and birthday between ‘1980-01-01’ and '2000-12-31'

排序

索引有序，根据索引排序很快

1. 联合索引需要排序字段顺序和索引顺序一致才能使用

2. 同上，匹配联合索引最左边的列就可以生效右边的列

3. 不能asc desc混用

4. where字句出现非索引字段，失效

5. 排序列包含非索引字段，失效

6. 排序列使用复杂表达式，失效

分组

和排序一个道理一个规则

回表

二级索引拿到匹配值的id回到聚簇索引去获取全部数据（回表时是随机io，效率慢）

回表记录数越多，代价越大，导致可能直接去走全表扫描

覆盖索引

要查询的字段全是索引中的字段，这样就不用回表去聚簇索引找数据了

索引注意事项

1. 只为where，group，order中的字段建立索引
2. 考虑列的基数，基数小没必要建索引，例如性别就不适合索引，二分也只能分出来男女
3. 索引类型尽量小，省空间，同时减少遍历和比对的时间（例如比对varchar）
4. 字符串最左前缀 abc%
5. 主键用顺序自增插入，随机id插入时要改变索引结构，消耗太大（- 这就是为什么建议用自增或有序的雪花算法id 不用uuid
6. 索引列不要用表达式或函数（索引无法确定未知的结果，也就无法拿到确定的结果去索引里找
7. 不要建过多或重复冗余的索引，例如name和name_age

还有更多的细节注意项，例如

• 字段强制类型转换，varchar类型的字段条件查询必需加''号
• not 、!=
• or 改成in，o是O(n) in 是 O(log(n))
• 字段默认值null值
• join时两边字段类型要相同
• join被驱动表的连接列加上索引

其他sql优化这里略过

6. 查询优化

Explain

使用explain查看执行计划，根据字段分析查询优化器采用什么路线执行查询，例如是否走索引，可能走哪些索引，访问方法，排序使用索引还是文件排序等

访问方法

单点区间

const：常数级，等值精确查询，where id = 2 确定只返回一条记录

ref：等值范围，where name = '张三' 确定返回了3天记录

ref_or_null：等值范围或null，等值范围可能出现null值的情况

范围区间

range：范围匹配，where age in (16,17,18,19) or (age >35 and age < 40)；返回一个范围值

index：直接遍历二级索引且不用回表，例如

#有对part1,part2,part3的联合索引，索引覆盖了，不用回表,条件也不是最左前缀，就直接在二级索引上遍历了
select part1,part2,part3 from t_part where part3 like 'cc%';

all：全表扫描，聚簇索引上一条一条遍历

index_merge：索引合并，多个索引取交集，快于all

• intersection，多个二级索引取交集（建议用联合索引替代intersection）
  • 索引列要等值匹配
  • 主键可以范围匹配
• union
  • 并集，条件or的情况
• sort-union
  • 取并集前排序

阿里规范sql至少达到range级别。

explain format=json ,可以让执行计划以json形式输出，可以看到查询优化器预估的执行成本

执行完后可以show warnings \g 来查看警告，code为1003时的message，是优化器重写后的sql

完整的使用 optimizer trace 功能的步骤总结如下：

# 1. 打开optimizer trace功能 (默认情况下它是关闭的):

SET optimizer_trace="enabled=on";

# 2. 这里输入你自己的查询语句

SELECT ...;

# 3. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程

SELECT * FROM information_schema.OPTIMIZER_TRACE;

# 4. 可能你还要观察其他语句执行的优化过程，重复上边的第2、3步

...

# 5. 当你停止查看语句的优化过程时，把optimizer trace功能关闭

SET optimizer_trace="enabled=off";

join连接原理

内连接的on 和 where等价

外连接on是连接条件 where过滤条件，一个是连接时筛选，应该是连接后过滤

2.1 Index Nested-Loop Join（索引嵌套循环连接）

驱动表访问一次，一条一条去被驱动表找，避免笛卡尔积

Index Nested-Loop Join其优化的思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。

2.2 Block Nested-Loop Join（块嵌套循环连接）

驱动表访问一次，一块一块去被驱动表找，数据暂存join buffer pool

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录再加载到内存匹配，这样周而复始，大大增加了IO的次数。为了减少被驱动表的IO次数，就出现了Block Nested-Loop Join的方式。

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列（大小受join buffer的限制）缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。

2.3 Hash Join

计算散列值等值连接匹配。

从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join

• Nested Loop：对于被连接的数据子集较小的情况下，Nested Loop是个较好的选择。
• Hash Join是做大数据集连接时的常用方式，优化器使用两个表中较小（相对较小）的表利用Join Key在内存中建立散列值，然后扫描较大的表并探测散列值，找出与Hash表匹配的行。
  • 这种方式适用于较小的表完全可以放入内存中的情况，这样总成本就是访问两个表的成本之和。
  • 在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高I/O的性能。
  • 它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。Hash Join只能应用于等值连接，这是由Hash的特点决定的。

关于优化

慢查询，开启慢查询日志，慢查询工具

避免用子查询，用join代替

排序，分组同索引那块，排序分组出来的数据尽量不超过1000条，排序分组到程序做

分页：

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a
WHERE t.id = a.id;

7. BufferPool缓冲区

1. 磁盘太慢，用内存作为缓存很有必要。

2. Buffer Pool 本质上是 InnoDB 向操作系统申请的一段连续的内存空间，可以通过

   innodb_buffer_pool_size 来调整它的大小。

3. Buffer Pool 向操作系统申请的连续内存由控制块和缓存页组成，每个控制块和缓存页都是一一对应的，在

   填充足够多的控制块和缓存页的组合后， Buffer Pool 剩余的空间可能产生不够填充一组控制块和缓存页，

   这部分空间不能被使用，也被称为 碎片 。

4. InnoDB 使用了许多 链表 来管理 Buffer Pool 。5. free链表 中每一个节点都代表一个空闲的缓存页，在将磁盘中的页加载到 Buffer Pool 时，会从 free链

   表 中寻找空闲的缓存页。

5. 为了快速定位某个页是否被加载到 Buffer Pool ，使用 表空间号 + 页号 作为 key ，缓存页作为 value ，

   建立哈希表。

6. 在 Buffer Pool 中被修改的页称为 脏页 ，脏页并不是立即刷新，而是被加入到 flush链表 中，待之后的某

   个时刻同步到磁盘上。

7. LRU链表 分为 young 和 old 两个区域，可以通过 innodb_old_blocks_pct 来调节 old 区域所占的比例。首

   次从磁盘上加载到 Buffer Pool 的页会被放到 old 区域的头部，在 innodb_old_blocks_time 间隔时间内访

   问该页不会把它移动到 young 区域头部。在 Buffer Pool 没有可用的空闲缓存页时，会首先淘汰掉 old 区

   域的一些页。

8. 我们可以通过指定 innodb_buffer_pool_instances 来控制 Buffer Pool 实例的个数，每个 Buffer Pool 实

   例中都有各自独立的链表，互不干扰。

9. 自 MySQL 5.7.5 版本之后，可以在服务器运行过程中调整 Buffer Pool 大小。每个 Buffer Pool 实例由若

   干个 chunk 组成，每个 chunk 的大小可以在服务器启动时通过启动参数调整。

10. 可以用下边的命令查看 Buffer Pool 的状态信息：

    SHOW ENGINE INNODB STATUS\G

8. 事务

四个特性

• 原子性：操作原子，要么都成功要么都失败（undo）
• 一致性：执行前后的数据保持一致（undo）
• 隔离性：多个事务之间隔离，不能相互影响（mvcc,锁机制）
• 持久性：事务执行后的影响永久存在（redo保证）

五个状态

• 活动的：事务处于执行状态
• 部分提交的：事务执行完毕数据还没刷盘
• 失败的：事务执行过程出现错误
• 终止的：事务执行回滚，撤销
• 提交的：事务提交成功

开启事务

显示

begin | start transaction

commit | rollback | rollback to [savepoint]

• savepoint 保存点名称
• release savepoint 保存点名称

隐式

开启系统变量auto_commit = on

ddl语音

• 隐式修改或使用mysql的表

• 事务控制语句，例如lock tables手动上锁等

• 加载数据的语句，mysql复制语句或其他语句

关闭事务：

1. 系统变量 auto_commit = off
2. 显式开启一个事务

数据并发问题

两个事务，Session A 和 Session B

• A两次查询事务期间，B删除一些数据导致A第二次查询少了某些数据，属于不可重复读。（不可重复强调前后数据不一致，幻读强调读到之前没有的记录）

事务隔离级别

隔离级别越高，效率越低

使用：

set [global|session] transaction_isolation = 'read-uncommitted | read-committed | repeatable-read | serializable'

或

set [global | session] transaction isolation level read uncommitted | read committed | repeatable read | serializable

global 只对后续的会话生效

session只对当前会话后续事务生效

启动项设置：

transaction-isolation=xxx

9. 事务日志

Redo Log

保证持久性，用于宕机恢复

• min-transaction mtr：每个事务由若干sql语句组成，每个语句由若干mtr组成（新增时可能分配数据页，页分裂，申请资源等）

• redolog记录修改过的值的偏移量，有一个type记录区分各个大小的修改数据，类型为1时代表只有一条redolog。

• 当redolog需要刷盘时，先把多个redolog按照mtr分成一组redolog buffer中，然后才刷新到磁盘中。

1. 事务提交时，数据不立马刷盘，生成redolog，redolog按照mtr分组被刷到redolog buffer中
2. redolog buffer是一片连续的空间，用于作缓存，redolog buffer把数据追加写到redolog file中
3. redolog file可有多个，写满时从头开始追加。（日志文件组）
4. redolog file有checkpoint机制，分别有几个变量记录新追加的位置，刷盘成功的数据位置（可以被擦除了），当前记录的位置，（二者中间就是还未刷盘的数据）
5. 当需要追加的位置干到了checkpoint时，就需要停下等他写入，checkpoint后移才能继续追加。
6. redo log刷盘时会把修改过的页追加到flush链表，更新buffer pool的缓存。

redo log 刷盘策略

1. 0：每次提交不立马同步redolog，完全交给后台线程做，（系统默认1秒同步一次page cache）
2. 1：每次事务提交时都把redolog同步到磁盘，（默认值）
3. 2：只把redo log buffer 同步到page cache，不落盘，系统决定何时落盘，（系统挂了数据丢失）

Undo log

保证原子性，用于事务回滚，MVCC

逻辑上的日志，本质是多个undo链表，记录要撤销的信息，利用隐藏列的trc_id，依次对数据进行回滚操作

每个undo链表都在一个undo段中，undo链表通过记录该记录的前后节点指向来确定要撤销记录的信息。

不同事务执行的undolog分配到不同的undo链表中（为了提高并发）

不同的undo链表可以重用（节省资源）

undo链表可分为insert undo链表 和update undo链表

1. insert undo链表，提交后旧数据没用，可直接覆盖以重用。
2. update undo链表，为保证mvcc机制，不能直接覆盖，再同一个页面写入多组undo日志。

回滚段：

方便管理多个undo链表，对其分组，有多个回滚段，默认128个

• redolog是物理日志，确保事务的数据最终落盘。持久性。（追加写文件，刷盘策略，redobuffer）
• undolog是逻辑日志，确保错误或事务回滚时数据修改能撤销。一致性。（undo链表，trc_id，回滚段，insert_ update链表复用，mvcc）

10. MVCC

mvcc（mutil version concurrent control），多版本并发控制，为了解决事务中的幻读问题。

本质是基于记录里的隐藏列trx_id和rollbackpoint，undolog链表，和readview读视图，共同形成一个版本链。

• trx_id：当对数据进行改动时，把该事务的事务id赋值给它

• roll_point：当数据改动时，把undo操作写入undo日志中，把对应的point记录到该值，记录该次改动的版本。

• readview：事务启动时生成当前事务的快照。

1. 快照读：在进行数据读取时读取的是快照，不保证是最新的数据，基于mvcc的readview，不用加锁，提高性能
2. 当前读：进行数据读取时永远读取的最新数据，通过锁机制实现，加锁的select，增删改操作都会加锁。

readview执行流程：

1. 获取自己的版本号
2. 获取readview
3. 查询得到的数据与readview事务版本号比较
4. 如果不符合readview规则，就从undo log中获取数据
5. 返回符合规则的数据

read_uncommited:其他事务读取时直接读取最新版本记录，不需要mvcc。（事务之间的读取操作受影响）

read_commited:该隔离级别下，readview在每次读取数据前都生成一个readview，readview在读取时会进行校验，判断是否返回对应版本的历史记录

repeated-read:该隔离级别下，readview只有在第一次读取数据前才会生成一个readview，当后续读取时都只读到事务前的数据，也就保证了可重复读。不完全解决幻读。

读已提交和可重复读的本质区别就是readview生成的时机不同来保证。

mysql中如何在可重复读级别下解决不可重复读？

解决不可重复读有两种方式

1. 读写加锁
2. 读走mvcc，写加锁

不可重复读是同一个事物在两处查询中，别的事物对其修改了导致数据变化了，而mvcc在可重复读级别下，第一次查询前生产一个readview，之后的每次查询都会复用该readview，就避免了不可重复读。

mysql怎么解决不同隔离级别下的并发问题

脏写：写操作加锁

脏读：mvcc，每次读取前生成readview读视图，确保可以读取到已经提交的数据，或者读取数据前加锁，防止期间其他事务修改数据

不可重复读：mvcc，查询是读取第一次生成的readview读视图，之后的每次查询都是复用该视图，别人修改新增数据不影响

幻读：分为当前读和快照读

• 当前读：采用间隙锁和行锁的方法，select for update，新增时加间隙锁，解决幻读
• 快照读：mvcc，读的快照，保证事务期间看不见新增数据

特别注意：对于修改的语句，有时还是会出现幻读。

• 例：事务1查询age为18的用户，事务2新增了一个age为18的用户，事务1此时修改age为18的用户昵称都叫暴龙战士，再查询，此时会把事务2新增的用户查出来，也就出现了幻读。

其实幻读解决到这个层面上对业务影响不大了。

11. 锁

实现事务各个隔离级别，解决最基本的脏写。

并发情况：

读-读情况：并发事务相继进行读操作，不会影响记录

写-写情况：对相同的数据进行写操作，需要加锁排队执行

读-写情况：一个事务在读取时，另一个事务同时对它读取的数据进行写操作，此时会出现脏读，不可重复读，幻读问题。

锁的分类：

按数据类型：

读锁（共享锁）S锁：多个请求可以同时读，但是不能同时修改

写锁（排他锁）X锁：一个资源获取到锁，只有该资源可以进行写入，其他资源获取时阻塞

select 。。。lock in share mode | for share (查询加读锁)

select。。。for update（查询加写锁）

5.7之前：x锁会阻塞等待到timeout

8.0可在for update之后指定 no wait 和skip locked

1. no wait 阻塞时不等待直接报错返回
2. skip locked 只返回没加锁的行，跳过锁住的记录

按锁粒度：

表级锁

不依赖存储引擎，锁住整个表，锁粒度大，竞争大，并发冲突大，开销最小

• 当执行ddl（alter drop)时其他事务并发执行增删改查语句时，会发生阻塞，反之同理

• server层的元数据锁实现

• MyISAM查询时默认是表级读锁，增删改是写锁

• 一般情况下不会用到

  • lock 表名 t read;
  • lock 表名 t write;

• 意向锁：向更大一级粒度标注该表是否有其他事务使用，避免一个一个判断各页或各行是否加锁。

• 存储引擎自己维护

• 自增锁：自增时会加上特殊的锁

  1. 简单插入：事先确定了要插入的行数

  2. 批量插入：事先不知道要插入的行数

  3. 混合模式插入：在插入时需要上自增锁，其他事务无法执行新增操作，innodb_autoinc_lock_mode

     innodb_autoinc_lock_mode:

     1. 0：传统模式：所有insert语句都会获得一个表级锁，多个insert会争夺锁
     2. 1：连续锁定模式：批量插入时用自增锁，简单插入时用轻量锁的控制下获取等量的自增值
     3. 2：交错锁定模式：mysql8.0默认，所有insert语句都不使用表级自增锁，在binlog日志恢复时可能会出现问题，主键值不连续。

• 元数据锁（MDL，meta data lock）：保证数据正确性的表级锁，增删改查加读锁，ddl操作加写锁。

页级锁

• 页的粒度上加的锁，开销介于表锁和行锁之间，并发效率一般。会出现死锁

行锁

存储引擎层面实现，行级别的锁，粒度为记录

-锁的粒度小，锁的冲突概率低，并发效率高

-锁的开销大，加锁慢，容易出现死锁

• 记录锁

  单条记录级别的锁，分S锁和X锁

• 间隙锁(gap锁)

  锁住某个范围，专门用于解决幻读（第一种方式是mvcc，第二种方式则是间隙锁）

• 临键锁（innodb默认）

  gap锁的一种，锁住某条记录，以及其之前的记录，作用是在插入时避免别人再他前面的间隙插入新记录

• 插入意向锁

  在插入一条记录前，有其他的gap锁，此时该条记录会产生一条插入意向锁，代表该条记录想要插入，但是前边有gap锁阻塞

12. 其他日志

1. 慢查询日志（slow query log）：开启慢查询，超过指定时间的sql标记为慢查询，用于分析sql，sql优化
2. 通用查询日志（general query log）：作sql的统计，所有连接的起始时间和终止时间
3. 错误日志（error log）：记录mysql启动运行或者停止时出现的问题
4. 二进制日志（bin log）：所有更改数据的语句，可用于主从同步（中间件mycat），（分布式事务也是基于此），数据备份和故障恢复
5. 中继日志（delay log）：主从架构，主从同步时中继主库的binlog，从库读取delay log来同步主库操作
6. 数据定义语句日志（DDL log）：记录数据定义语句执行的元数据操作

bin log 日志

是一个二进制文件，记录所有的数据更改信息，可用于主从同步

刷盘：

事务执行过程中，数据放入bin log cache，事务提交时，再把binlog cache写入bin log file中（事务要确保一次性写入）

刷盘时机：

sync_binlog:

• 0(默认)，每次提交事务只写入，系统决定何时刷盘（宕机丢失）
• 1，每次提交事务都刷盘
• N，每N个事务提交后刷盘

两阶段提交

事务提交时，binlog和redolog都会写入，二者刷盘时机不一样，（binlog一次性，redolog边写边擦），为了保证二者数据同步，使用两阶段提交的方式

redolog -》prepare

binlog -》写入

redolog -》commitmysql

1. SQL执行顺序

1、FROM：选择FROM后面跟的表，产生虚拟表1。

2、ON：ON是JOIN的连接条件，符合连接条件的行会被记录在虚拟表2中。

3、JOIN：如果指定了LEFT JOIN，那么保留表中未匹配的行就会作为外部行添加到虚拟表2中，产生虚拟表3。如果有多个JOIN链接，会重复执行步骤1~3，直到处理完所有表。

4、WHERE：对虚拟表3进行WHERE条件过滤，符合条件的记录会被插入到虚拟表4中。

5、GROUP BY：根据GROUP BY子句中的列，对虚拟表4中的记录进行分组操作，产生虚拟表5。

6、AGG_FUNC：常用的 Aggregate 函数包涵以下几种：（AVG：返回平均值）、（COUNT：返回行数）、（FIRST：返回第一个记录的值）、（LAST：返回最后一个记录的值）、（MAX： 返回最大值）、（MIN：返回最小值）、（SUM： 返回总和）。

7、WITH 对虚拟表5应用ROLLUP或CUBE选项，生成虚拟表 6。

8、HAVING：对虚拟表6进行HAVING过滤，符合条件的记录会被插入到虚拟表7中。

9、SELECT：SELECT到一步才执行，选择指定的列，插入到虚拟表8中。

10、UNION：UNION连接的两个SELECT查询语句，会重复执行步骤1~9，产生两个虚拟表9，UNION会将这些记录合并到虚拟表10中。

11、DISTINCT 将重复的行从虚拟表10中移除，产生虚拟表 11。DISTINCT用来删除重复行，只保留唯一的。

12、ORDER BY: 将虚拟表11中的记录进行排序，虚拟表12。

13、LIMIT：取出指定行的记录，返回结果集。

2. 基础概念概述

客户端，服务端（tcp/ip连接），查询优化器，存储引擎，系统变量（会话级别全局级别），字符编码（utf8mb3 mb4 一个占3字节，一个4，emoji，varchar存汉字大小除以3-4，2w多字（null值列表是否删除都占空间），排序规则，文件目录结构（表空间，数据存储的文件机构），这边也略过了。

配置文件（永久）和启动配置项（临时）：配置项优先级高

数据库 表字符编码，存储引擎建议一致

创建数据库，表，可指定存储引擎，行格式，字符编码，字符排序规则

3. 存储引擎

myisam-不支持事务，数据和索引分开，索引存的地址偏移量，回表速度快（但是要行格式定长下），只有表锁

innodb存储结构-默认使用，支持事务，B+树索引，行锁表锁间隙，插入意向锁等，事务MVCC，

4. InnoDB存储结构

记录 行格式

基础compact，5.7默认dynamic（行溢出时不存部分数据而是所有数据都甩出去，指针引用），一行记录指针在中间，往右是隐藏列和真实字段数据，往左是记录头，null值列表，变长字段列表（逆序）。

• 隐藏列：

  • row_id，没主键或唯一字段，加一个row_id

  • 事务id，标识是哪个事务在用

  • 回滚指针：事务回滚用

• 记录头：

  • 标记是否被删除，被删除数据放到垃圾链表，其他数据新增时覆盖

  • 非叶子节点最小记录，非叶节点索引指向用

  • 记录数，统计，页目录分组用

  • 堆位置信息

  • 记录类型，0普通记录，1索引记录，2最小3最大，区别数据是非叶节点还是叶子节点

  • 下一条记录位置，记录之间形成单链表

• 行溢出：一个行数据过大，行溢出，多余数据放到别的页，指针引用指向

页（基本存储单位，一页16kb）

• 文件头：描述文件信息，校验文件

  • 校验和

  • 当前页号

  • 上一页号

  • 下一页号

  • 日志LSN码（log sequeceNumber）

  • 页的类型

  • 文件至少被刷新到了的LSN位置（系统表空间）

  • 页属于哪个表空间

• 页头，数据页专有信息

  • 页目录槽数量

  • 空闲空间地址

  • 记录数（全部）

  • 删除数据链表头

  • 已删数据占用空间

  • 最后插入记录地址

  • 记录插入方向

  • 连续同向插入的记录数

  • 存活记录数量(不计入虚拟记录和被删除记录)

  • 修改当前页的最大事务id

  • 索引id

  • 叶子字段头信息（Root）

  • 非叶字段头信息（Root）

• 最大记录、最小记录，虚拟记录，指向实际最大最小记录

• 用户记录，存放记录

  • 记录之间经过排序，用行格式文件头信息的next_record形成单链表

• 空闲记录，空闲空间

• 页目录，对记录分组，根据记录的头信息的n_owned字段对记录分组，便于二分

• 文件尾：与文件头对应，校验文件完整性

  • 前4字节：校验和（与文件头对应，写入完成时两者一致说明写成功）

  • 后4字节：最后修改时的LSN（配合文件头的LSN，双重校验磁盘IO完整性）

页内查找过程（两步走）

1. 二分查找定位槽：通过页目录的槽（slot）进行二分查找，快速定位到目标记录所在的分组

2. 链表遍历：通过记录的next_record属性，在分组内遍历找到目标记录

页目录分组规则（n_owned）

• 最小记录所在分组：只能有 1 条记录

• 最大记录所在分组：1~8 条记录

• 其他普通分组：4~8 条记录

文件头串联多页

多个数据页通过文件头的「上一页号」和「下一页号」组成双向链表，这是B+树同层节点遍历的基础。非叶节点之间、叶子节点之间各自形成独立的双向链表。

表空间

当前版本，一个表有表级独立表空间，也有系统表空间

段

对区中的叶子节点，非叶节点分开存储，便于遍历，提高查询效率

区

顺序的一片空间（64个页，1m大小）256个区（256M）一组，避免创建数据页时随机io，保证顺序io

碎片区

以及其他段，独属表空间，属于系统表空间，用于放零碎数据（避免空间浪费）

5. 索引原理

索引

空间换时间，提高查询效率，新增删除需要维护数据。

聚簇索引（数据即索引，存全部数据，根据主键排序）

核心思想：B+树本质上是页结构的逐层分组扩展。

页通过文件头标识类型，大致分根节点，非叶节点（记录页），叶子节点（数据页）

• 根节点独有，第一个数据先创建根节点，根节点不变

• 根据二分实现插入时排序，依赖文件头的插入方向，连续插入位置等

• 数据扩容形成叶子节点（数据页），每页记录由链表存储，页目录对记录分组，又一次二分

• 叶子节点之间形成双向链表（基于文件头的上下页号，遍历用）

• 叶子节点过多，不好遍历时，扩容形成非叶节点（记录页）

  • 不存数据，存kv值，k是最小记录主键值，v是页号

  • 同理，记录页的页目录维护的也是主键值--页号这样的kv，对其分组

  • 同理，记录页之间也形成双向链表（遍历用）

• 非叶节点过多，再扩展一层，索引非叶节点。

非叶节点与叶子节点使用相同的页结构，区别在于：

• 非叶节点的记录 record_type=1（叶子节点 record_type=0）

• 非叶节点只存储「主键 + 页号」，不存真实数据

• 非叶节点的页目录分组的也是「主键 → 页号」这样的kv对

B+树形成过程（从页结构理解）：

1. 一个数据页内，记录通过页目录分组 → 页内二分查找

2. 数据页多了，需要非叶节点把多个页的主键摘出来再分组 → 页间二分查找

3. 非叶节点也多了，再加一层 → 多层B+树

本质上就是：分组 → 再分组 → 再再分组（套娃），每一层都是一次二分。

B树：平衡多路搜索树，叶子和非叶节点都存储数据

• 插入时为了维护数据要左旋右旋，经常变更结构

• 只适合随机检索，不适合顺序检索

• 查询时间复杂度O(n)，树高影响

B+树：在B树基础上，非叶节点只存索引，不存数据，且同级叶子非叶节点通过双向链表链接

• 不须B树那么频繁改变树结构（也要维护）

• 适合随机和顺序检索，范围检索（因为只存索引及双向链表，顺序遍历更方便）

• 查询时间复杂度O(log(n))，相对稳定，树的高度稳定（扁长）

总结：innodb中的索引就是数据，默认一个聚簇索引，B+树结构（复用页结构），依据主键对数据实现了排序，B+树树高稳定，查询稳定，有双向链表的原因，顺序和范围遍历方便，每页的记录之间又形成单链表，又由页目录分组，又进行一次二分，进一步提高效率。

核心：B+树，二分查找，双向链表，进一步二分，单链表

在B+树中查找一条记录的完整过程：

1. 从根节点开始，在非叶节点的页目录中二分查找 → 定位到某个子节点（页号）

2. 进入子节点，继续在页目录中二分查找 → 定位到分组

3. 在分组内通过next_record链表遍历 → 找到目标记录

4. 如果子节点还是非叶节点，重复步骤1-2，直到叶子节点

二级索引

显式创建的索引，只存放部分数据，记录中只有主键-部分数据，用到二级索引提高对特定字段的检索速度

二级索引除了全文索引外都要回表，（回到聚簇索引中取其他字段的值），

联合索引

对多个字段按顺序排序的索引，使用该索引要注意最左前缀，否则失效或者只能用一部分

全值匹配

搜索条件（select中）使用到的字段全是联合索引的字段，不用回表。

最左前缀

查询条件（where后边，包括group和order）字段顺序必须要和联合索引的顺序一致。从左到右依次，否则失效或只能用部分，或者干脆走全表扫描

匹配列前缀

字符串like '%abc%'时，左边带通配符%无法使用索引，只有确定前缀顺序abc%才走

匹配范围值

索引天然有序，所以范围查询是很快的，

1. 联合索引只有对最左边的索引范围查找才生效

2. 联合索引可以先对左边的索引等值查询再对右边的值范围查找。例如name="aaa" and birthday between ‘1980-01-01’ and '2000-12-31'

排序

索引有序，根据索引排序很快

1. 联合索引需要排序字段顺序和索引顺序一致才能使用

2. 同上，匹配联合索引最左边的列就可以生效右边的列

3. 不能asc desc混用

4. where字句出现非索引字段，失效

5. 排序列包含非索引字段，失效

6. 排序列使用复杂表达式，失效

分组

和排序一个道理一个规则

回表

二级索引拿到匹配值的id回到聚簇索引去获取全部数据（回表时是随机io，效率慢）

回表记录数越多，代价越大，导致可能直接去走全表扫描

覆盖索引

要查询的字段全是索引中的字段，这样就不用回表去聚簇索引找数据了

索引注意事项

1. 只为where，group，order中的字段建立索引

2. 考虑列的基数，基数小没必要建索引，例如性别就不适合索引，二分也只能分出来男女

3. 索引类型尽量小，省空间，同时减少遍历和比对的时间（例如比对varchar）

4. 字符串最左前缀 abc%

5. 主键用顺序自增插入，随机id插入时要改变索引结构，消耗太大（- 这就是为什么建议用自增或有序的雪花算法id 不用uuid

6. 索引列不要用表达式或函数（索引无法确定未知的结果，也就无法拿到确定的结果去索引里找

7. 不要建过多或重复冗余的索引，例如name和name_age

还有更多的细节注意项，例如

• 字段强制类型转换，varchar类型的字段条件查询必需加''号

• not 、!=

• or 改成in，o是O(n) in 是 O(log(n))

• 字段默认值null值

• join时两边字段类型要相同

• join被驱动表的连接列加上索引

其他sql优化这里略过

6. 查询优化

Explain

使用explain查看执行计划，根据字段分析查询优化器采用什么路线执行查询，例如是否走索引，可能走哪些索引，访问方法，排序使用索引还是文件排序等

访问方法

单点区间

const：常数级，等值精确查询，where id = 2 确定只返回一条记录

ref：等值范围，where name = '张三' 确定返回了3天记录

ref_or_null：等值范围或null，等值范围可能出现null值的情况

范围区间

range：范围匹配，where age in (16,17,18,19) or (age >35 and age < 40)；返回一个范围值

index：直接遍历二级索引且不用回表，例如

#有对part1,part2,part3的联合索引，索引覆盖了，不用回表,条件也不是最左前缀，就直接在二级索引上遍历了
select part1,part2,part3 from t_part where part3 like 'cc%';

all：全表扫描，聚簇索引上一条一条遍历

index_merge：索引合并，多个索引取交集，快于all

• intersection，多个二级索引取交集（建议用联合索引替代intersection）

  • 索引列要等值匹配

  • 主键可以范围匹配

• union

  • 并集，条件or的情况

• sort-union

  • 取并集前排序

阿里规范sql至少达到range级别。

explain format=json ,可以让执行计划以json形式输出，可以看到查询优化器预估的执行成本

执行完后可以show warnings \g 来查看警告，code为1003时的message，是优化器重写后的sql

完整的使用 optimizer trace 功能的步骤总结如下：

1. 打开optimizer trace功能 (默认情况下它是关闭的):

SET optimizer_trace="enabled=on";

2. 这里输入你自己的查询语句

SELECT ...;

3. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程

SELECT * FROM information_schema.OPTIMIZER_TRACE;

4. 可能你还要观察其他语句执行的优化过程，重复上边的第2、3步

...

5. 当你停止查看语句的优化过程时，把optimizer trace功能关闭

SET optimizer_trace="enabled=off";

join连接原理

内连接的on 和 where等价

外连接on是连接条件 where过滤条件，一个是连接时筛选，应该是连接后过滤

2.1 Index Nested-Loop Join（索引嵌套循环连接）

驱动表访问一次，一条一条去被驱动表找，避免笛卡尔积

Index Nested-Loop Join其优化的思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。

2.2 Block Nested-Loop Join（块嵌套循环连接）

驱动表访问一次，一块一块去被驱动表找，数据暂存join buffer pool

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录再加载到内存匹配，这样周而复始，大大增加了IO的次数。为了减少被驱动表的IO次数，就出现了Block Nested-Loop Join的方式。

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列（大小受join buffer的限制）缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。

2.3 Hash Join

计算散列值等值连接匹配。

从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join

• Nested Loop：对于被连接的数据子集较小的情况下，Nested Loop是个较好的选择。

• Hash Join是做大数据集连接时的常用方式，优化器使用两个表中较小（相对较小）的表利用Join Key在内存中建立散列值，然后扫描较大的表并探测散列值，找出与Hash表匹配的行。

  • 这种方式适用于较小的表完全可以放入内存中的情况，这样总成本就是访问两个表的成本之和。

  • 在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高I/O的性能。

  • 它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。Hash Join只能应用于等值连接，这是由Hash的特点决定的。

关于优化

慢查询，开启慢查询日志，慢查询工具

避免用子查询，用join代替

排序，分组同索引那块，排序分组出来的数据尽量不超过1000条，排序分组到程序做

分页：

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a
WHERE t.id = a.id;

7. BufferPool缓冲区

1. 磁盘太慢，用内存作为缓存很有必要。

2. Buffer Pool 本质上是 InnoDB 向操作系统申请的一段连续的内存空间，可以通过

   innodb_buffer_pool_size 来调整它的大小。

3. Buffer Pool 向操作系统申请的连续内存由控制块和缓存页组成，每个控制块和缓存页都是一一对应的，在

   填充足够多的控制块和缓存页的组合后， Buffer Pool 剩余的空间可能产生不够填充一组控制块和缓存页，

   这部分空间不能被使用，也被称为 碎片 。

4. InnoDB 使用了许多 链表 来管理 Buffer Pool 。5. free链表 中每一个节点都代表一个空闲的缓存页，在将磁盘中的页加载到 Buffer Pool 时，会从 free链

   表 中寻找空闲的缓存页。

5. 为了快速定位某个页是否被加载到 Buffer Pool ，使用 表空间号 + 页号 作为 key ，缓存页作为 value ，

   建立哈希表。

6. 在 Buffer Pool 中被修改的页称为 脏页 ，脏页并不是立即刷新，而是被加入到 flush链表 中，待之后的某

   个时刻同步到磁盘上。

7. LRU链表 分为 young 和 old 两个区域，可以通过 innodb_old_blocks_pct 来调节 old 区域所占的比例。首

   次从磁盘上加载到 Buffer Pool 的页会被放到 old 区域的头部，在 innodb_old_blocks_time 间隔时间内访

   问该页不会把它移动到 young 区域头部。在 Buffer Pool 没有可用的空闲缓存页时，会首先淘汰掉 old 区

   域的一些页。

8. 我们可以通过指定 innodb_buffer_pool_instances 来控制 Buffer Pool 实例的个数，每个 Buffer Pool 实

   例中都有各自独立的链表，互不干扰。

9. 自 MySQL 5.7.5 版本之后，可以在服务器运行过程中调整 Buffer Pool 大小。每个 Buffer Pool 实例由若

   干个 chunk 组成，每个 chunk 的大小可以在服务器启动时通过启动参数调整。

10. 可以用下边的命令查看 Buffer Pool 的状态信息：

    SHOW ENGINE INNODB STATUS\G

8. 事务

四个特性

• 原子性：操作原子，要么都成功要么都失败（undo）

• 一致性：执行前后的数据保持一致（undo）

• 隔离性：多个事务之间隔离，不能相互影响（mvcc,锁机制）

• 持久性：事务执行后的影响永久存在（redo保证）

五个状态

• 活动的：事务处于执行状态

• 部分提交的：事务执行完毕数据还没刷盘

• 失败的：事务执行过程出现错误

• 终止的：事务执行回滚，撤销

• 提交的：事务提交成功

开启事务

显示

begin | start transaction

commit | rollback | rollback to [savepoint]

• savepoint 保存点名称

• release savepoint 保存点名称

隐式

开启系统变量auto_commit = on

ddl语音

• 隐式修改或使用mysql的表

• 事务控制语句，例如lock tables手动上锁等

• 加载数据的语句，mysql复制语句或其他语句

关闭事务：

1. 系统变量 auto_commit = off

2. 显式开启一个事务

数据并发问题

两个事务，Session A 和 Session B

• A两次查询事务期间，B删除一些数据导致A第二次查询少了某些数据，属于不可重复读。（不可重复强调前后数据不一致，幻读强调读到之前没有的记录）

事务隔离级别

隔离级别越高，效率越低

使用：

set [global|session] transaction_isolation = 'read-uncommitted | read-committed | repeatable-read | serializable'

或

set [global | session] transaction isolation level read uncommitted | read committed | repeatable read | serializable

global 只对后续的会话生效

session只对当前会话后续事务生效

启动项设置：

transaction-isolation=xxx

9. 事务日志

Redo Log

保证持久性，用于宕机恢复

• min-transaction mtr：每个事务由若干sql语句组成，每个语句由若干mtr组成（新增时可能分配数据页，页分裂，申请资源等）

• redolog记录修改过的值的偏移量，有一个type记录区分各个大小的修改数据，类型为1时代表只有一条redolog。

• 当redolog需要刷盘时，先把多个redolog按照mtr分成一组redolog buffer中，然后才刷新到磁盘中。

1. 事务提交时，数据不立马刷盘，生成redolog，redolog按照mtr分组被刷到redolog buffer中

2. redolog buffer是一片连续的空间，用于作缓存，redolog buffer把数据追加写到redolog file中

3. redolog file可有多个，写满时从头开始追加。（日志文件组）

4. redolog file有checkpoint机制，分别有几个变量记录新追加的位置，刷盘成功的数据位置（可以被擦除了），当前记录的位置，（二者中间就是还未刷盘的数据）

5. 当需要追加的位置干到了checkpoint时，就需要停下等他写入，checkpoint后移才能继续追加。

6. redo log刷盘时会把修改过的页追加到flush链表，更新buffer pool的缓存。

redo log 刷盘策略

1. 0：每次提交不立马同步redolog，完全交给后台线程做，（系统默认1秒同步一次page cache）

2. 1：每次事务提交时都把redolog同步到磁盘，（默认值）

3. 2：只把redo log buffer 同步到page cache，不落盘，系统决定何时落盘，（系统挂了数据丢失）

Undo log

保证原子性，用于事务回滚，MVCC

逻辑上的日志，本质是多个undo链表，记录要撤销的信息，利用隐藏列的trc_id，依次对数据进行回滚操作

每个undo链表都在一个undo段中，undo链表通过记录该记录的前后节点指向来确定要撤销记录的信息。

不同事务执行的undolog分配到不同的undo链表中（为了提高并发）

不同的undo链表可以重用（节省资源）

undo链表可分为insert undo链表 和update undo链表

1. insert undo链表，提交后旧数据没用，可直接覆盖以重用。

2. update undo链表，为保证mvcc机制，不能直接覆盖，再同一个页面写入多组undo日志。

回滚段：

方便管理多个undo链表，对其分组，有多个回滚段，默认128个

• redolog是物理日志，确保事务的数据最终落盘。持久性。（追加写文件，刷盘策略，redobuffer）

• undolog是逻辑日志，确保错误或事务回滚时数据修改能撤销。一致性。（undo链表，trc_id，回滚段，insert_ update链表复用，mvcc）

10. MVCC

mvcc（mutil version concurrent control），多版本并发控制，为了解决事务中的幻读问题。

本质是基于记录里的隐藏列trx_id和rollbackpoint，undolog链表，和readview读视图，共同形成一个版本链。

• trx_id：当对数据进行改动时，把该事务的事务id赋值给它

• roll_point：当数据改动时，把undo操作写入undo日志中，把对应的point记录到该值，记录该次改动的版本。

• readview：事务启动时生成当前事务的快照。

1. 快照读：在进行数据读取时读取的是快照，不保证是最新的数据，基于mvcc的readview，不用加锁，提高性能

2. 当前读：进行数据读取时永远读取的最新数据，通过锁机制实现，加锁的select，增删改操作都会加锁。

readview执行流程：

1. 获取自己的版本号

2. 获取readview

3. 查询得到的数据与readview事务版本号比较

4. 如果不符合readview规则，就从undo log中获取数据

5. 返回符合规则的数据

read_uncommited:其他事务读取时直接读取最新版本记录，不需要mvcc。（事务之间的读取操作受影响）

read_commited:该隔离级别下，readview在每次读取数据前都生成一个readview，readview在读取时会进行校验，判断是否返回对应版本的历史记录

repeated-read:该隔离级别下，readview只有在第一次读取数据前才会生成一个readview，当后续读取时都只读到事务前的数据，也就保证了可重复读。不完全解决幻读。

读已提交和可重复读的本质区别就是readview生成的时机不同来保证。

mysql中如何在可重复读级别下解决不可重复读？

解决不可重复读有两种方式

1. 读写加锁

2. 读走mvcc，写加锁

不可重复读是同一个事物在两处查询中，别的事物对其修改了导致数据变化了，而mvcc在可重复读级别下，第一次查询前生产一个readview，之后的每次查询都会复用该readview，就避免了不可重复读。

mysql怎么解决不同隔离级别下的并发问题

脏写：写操作加锁

脏读：mvcc，每次读取前生成readview读视图，确保可以读取到已经提交的数据，或者读取数据前加锁，防止期间其他事务修改数据

不可重复读：mvcc，查询是读取第一次生成的readview读视图，之后的每次查询都是复用该视图，别人修改新增数据不影响

幻读：分为当前读和快照读

• 当前读：采用间隙锁和行锁的方法，select for update，新增时加间隙锁，解决幻读

• 快照读：mvcc，读的快照，保证事务期间看不见新增数据

特别注意：对于修改的语句，有时还是会出现幻读。

• 例：事务1查询age为18的用户，事务2新增了一个age为18的用户，事务1此时修改age为18的用户昵称都叫暴龙战士，再查询，此时会把事务2新增的用户查出来，也就出现了幻读。

其实幻读解决到这个层面上对业务影响不大了。

11. 锁

实现事务各个隔离级别，解决最基本的脏写。

并发情况：

读-读情况：并发事务相继进行读操作，不会影响记录

写-写情况：对相同的数据进行写操作，需要加锁排队执行

读-写情况：一个事务在读取时，另一个事务同时对它读取的数据进行写操作，此时会出现脏读，不可重复读，幻读问题。

锁的分类：

按数据类型：

读锁（共享锁）S锁：多个请求可以同时读，但是不能同时修改

写锁（排他锁）X锁：一个资源获取到锁，只有该资源可以进行写入，其他资源获取时阻塞

select 。。。lock in share mode | for share (查询加读锁)

select。。。for update（查询加写锁）

5.7之前：x锁会阻塞等待到timeout

8.0可在for update之后指定 no wait 和skip locked

1. no wait 阻塞时不等待直接报错返回

2. skip locked 只返回没加锁的行，跳过锁住的记录

按锁粒度：

表级锁

不依赖存储引擎，锁住整个表，锁粒度大，竞争大，并发冲突大，开销最小

• 当执行ddl（alter drop)时其他事务并发执行增删改查语句时，会发生阻塞，反之同理

• server层的元数据锁实现

• MyISAM查询时默认是表级读锁，增删改是写锁

• 一般情况下不会用到

  • lock 表名 t read;

  • lock 表名 t write;

• 意向锁：向更大一级粒度标注该表是否有其他事务使用，避免一个一个判断各页或各行是否加锁。

• 存储引擎自己维护

• 自增锁：自增时会加上特殊的锁

  1. 简单插入：事先确定了要插入的行数

  2. 批量插入：事先不知道要插入的行数

  3. 混合模式插入：在插入时需要上自增锁，其他事务无法执行新增操作，innodb_autoinc_lock_mode

     innodb_autoinc_lock_mode:

     1. 0：传统模式：所有insert语句都会获得一个表级锁，多个insert会争夺锁

     2. 1：连续锁定模式：批量插入时用自增锁，简单插入时用轻量锁的控制下获取等量的自增值

     3. 2：交错锁定模式：mysql8.0默认，所有insert语句都不使用表级自增锁，在binlog日志恢复时可能会出现问题，主键值不连续。

• 元数据锁（MDL，meta data lock）：保证数据正确性的表级锁，增删改查加读锁，ddl操作加写锁。

页级锁

• 页的粒度上加的锁，开销介于表锁和行锁之间，并发效率一般。会出现死锁

行锁

存储引擎层面实现，行级别的锁，粒度为记录

-锁的粒度小，锁的冲突概率低，并发效率高

-锁的开销大，加锁慢，容易出现死锁

• 记录锁

  单条记录级别的锁，分S锁和X锁

• 间隙锁(gap锁)

  锁住某个范围，专门用于解决幻读（第一种方式是mvcc，第二种方式则是间隙锁）

• 临键锁（innodb默认）

  gap锁的一种，锁住某条记录，以及其之前的记录，作用是在插入时避免别人再他前面的间隙插入新记录

• 插入意向锁

  在插入一条记录前，有其他的gap锁，此时该条记录会产生一条插入意向锁，代表该条记录想要插入，但是前边有gap锁阻塞

12. 其他日志

1. 慢查询日志（slow query log）：开启慢查询，超过指定时间的sql标记为慢查询，用于分析sql，sql优化

2. 通用查询日志（general query log）：作sql的统计，所有连接的起始时间和终止时间

3. 错误日志（error log）：记录mysql启动运行或者停止时出现的问题

4. 二进制日志（bin log）：所有更改数据的语句，可用于主从同步（中间件mycat），（分布式事务也是基于此），数据备份和故障恢复

5. 中继日志（delay log）：主从架构，主从同步时中继主库的binlog，从库读取delay log来同步主库操作

6. 数据定义语句日志（DDL log）：记录数据定义语句执行的元数据操作

bin log 日志

是一个二进制文件，记录所有的数据更改信息，可用于主从同步

刷盘：

事务执行过程中，数据放入bin log cache，事务提交时，再把binlog cache写入bin log file中（事务要确保一次性写入）

刷盘时机：

sync_binlog:

• 0(默认)，每次提交事务只写入，系统决定何时刷盘（宕机丢失）

• 1，每次提交事务都刷盘

• N，每N个事务提交后刷盘

两阶段提交

事务提交时，binlog和redolog都会写入，二者刷盘时机不一样，（binlog一次性，redolog边写边擦），为了保证二者数据同步，使用两阶段提交的方式

redolog -》prepare

binlog -》写入

redolog -》commit