MySQL面试题笔记

1.MySQL 中的数据排序是怎么实现的？

1.排序方法：索引排序和文件排序 (filesort)

2.索引排序：如果order by xxx的字段为索引字段，则利用索引进行排序。效率最高，索引默认有序。

3.文件排序 (filesort)：内存排序(单路排序和双路排序)和磁盘排序，具体取决于排序数据的大小。其中，内存排序使用单路排序或双路排序，取决于max_length_for_sort_data(默认为4096个字节)

4.双路排序：取row_id(如果有主键，则为主键)和select a,b,c order by xxx的xxx字段放入sort_buffer(排序缓存)中，将排序后的row_id回表查询a,b,c

5.单路排序: 直接把要查的所有字段放入sort_buffer里，排序后直接得到结果集合

6.磁盘排序（归并排序）:将数据分为多份文件，单独对文件进行排序，然后合并成一个有序的大文件

2. MySQL 的 Change Buffer 是什么？它有什么作用？

1.ChangeBuffer定义：Change Buffer是InnoDB缓冲当中的一块缓存区，用于暂存二级索引的修改，避免二级索引页修改产生的随机IO 2.ChangeBuffer注意事项：只能用于二级索引，不能用于其他任何索引，包括主键索引和唯一索引都不行。 3.如果ChangeBuffer挂了，更改操作未执行，是否会出现脏数据？ 首先，ChangeBuffer也会保存在磁盘空间里面，redo log会记录Change Buffer当中的修改操作，确保数据一致性。

知识拓展1：一级索引和二级索引区别

一级索引（聚簇索引）：数据表的主键索引，数据和索引存储在同一B+树的叶子节点中。每个表只能有一个一级索引。

二级索引（非聚簇索引）：除主键外的其他索引，叶子节点存储索引列的值和对应的主键值。通过二级索引查询时，需要先通过二级索引获取主键值，再通过主键值查询数据，这个过程称为“回表”。

知识拓展2: MySQL中有哪些常见索引？都有什么区别？

在MySQL中，索引是提高查询效率的关键工具。常见的索引类型包括主键索引、唯一索引、普通索引、全文索引和空间索引。

1. 主键索引（Primary Key Index）

• 定义：主键索引是一种特殊的唯一索引，用于唯一标识表中的每一行数据。每个表只能有一个主键索引，且主键列的值不能为空。
• 特点：主键索引的叶子节点存储完整的数据行，因此查询效率高。在InnoDB存储引擎中，主键索引是聚簇索引，数据存储与索引结构合并。

2. 唯一索引（Unique Index）

• 定义：唯一索引确保索引列的每个值都是唯一的，但允许有空值。与主键索引类似，不同之处在于唯一索引允许列值为NULL。
• 特点：唯一索引的叶子节点存储索引列的值和对应的主键值。在InnoDB中，唯一索引是非聚簇索引，数据存储与索引结构分开。

3. 普通索引（Index）

• 定义：普通索引是最基本的索引类型，没有任何限制。索引列的值可以重复，也可以为NULL。
• 特点：普通索引的叶子节点存储索引列的值和对应的主键值。在InnoDB中，普通索引是非聚簇索引，数据存储与索引结构分开。

4. 全文索引（Fulltext Index）

• 定义：全文索引用于对文本数据进行全文搜索，适用于MyISAM存储引擎。它允许对文本字段进行复杂的搜索，如查找包含特定单词的记录。
• 特点：全文索引的叶子节点存储文档的词项信息。在MyISAM中，全文索引是非聚簇索引，数据存储与索引结构分开。

5. 空间索引（Spatial Index）

• 定义：空间索引用于对地理空间数据进行索引，支持空间数据类型的快速查询。它适用于存储地理位置、地图等空间数据的表。
• 特点：空间索引的叶子节点存储空间数据的索引信息。在MyISAM中，空间索引是非聚簇索引，数据存储与索引结构分开。

总结：

• 主键索引：用于唯一标识每一行数据，值不能为空。
• 唯一索引：确保索引列的值唯一，但允许有空值。
• 普通索引：最基本的索引类型，允许重复和空值。
• 全文索引：用于对文本数据进行全文搜索，适用于MyISAM存储引擎。
• 空间索引：用于对地理空间数据进行索引，支持空间数据类型的快速查询。

3. 详细描述一条 SQL 语句在 MySQL 中的执行过程。

1. 连接器判断用户是否成功建立连接，数据库连接的权限校验
2. 连接器会查询缓存，key 是 SQL 语句，value 是查询结果。如果命中，直接返回查询结果。(MySQL 8.0之后，就移除这个功能了)。
3. 分析器分析SQL语法和词法是否有误
4. 优化器生成SQL的执行计划，确定使用的索引和调整where的执行顺序（包括连表顺序）
5. 执行器判断当前用户是否有权限查询该表，然后执行该SQL语句

[参考文献] 执行一条 select 语句，期间发生了什么？

[补充] 3. MySQL 日志：undo log、redo log、binlog 有什么用？

undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。 redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复； binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

直接看参考文献当中的七个问题和其解决方案

[参考文献] MySQL 日志：undo log、redo log、binlog 有什么用？

4. MySQL 的存储引擎有哪些？它们之间有什么区别？

InnoDB : 支持事务、行锁、外键; 高并发性能、支持高负载的OLTP应用 (银行交易、电子商务订单、库存管理等); 聚集索引存储，检索效率高

MyISAM: 表锁、不支持事务和外键; 适用于读多写少的场景(数据仓库); 较高读性能和j较快的表级锁定

MEMORY: 存储在内存中，速度快，重启后数据丢失; 适用于临时数据存储和快速存储

5. MySQL 的索引类型有哪些？

6. MySQL InnoDB 引擎中的聚簇索引和非聚簇索引有什么区别？

聚簇索引：就像是图书馆里按照书籍主题顺序摆放的书架。在这个书架（也就是聚簇索引）上，每本书（也就是数据库中的行数据）都是按照某个主题（通常是主键）来排列的。所以，当你想要找某一主题的书时，只要知道主题名（主键值），就能很快在书架上找到它，而且相邻主题的书也是挨在一起的，找起来很方便。但是，这种方式的缺点是，如果你想要改变某本书的主题（更新主键），可能就需要移动整本书到新的位置，甚至可能需要重新整理整个书架（数据页），这样就比较麻烦了。

非聚簇索引：则更像是图书馆里的一个索引卡片箱。在这个卡片箱里，每张卡片（也就是非聚簇索引的节点）上都写着书籍的主题（索引列的值）和书籍在书架上的位置（主键值或ROWID）。当你想要找一本书时，可以先在卡片箱里找到对应的卡片，然后根据卡片上的位置信息去书架上找书。这种方式的好处是灵活，你可以为不同的书籍主题制作多张卡片，方便从不同的角度查找书籍。但是，坏处是每次找书都需要两步：先在卡片箱里找卡片，再去书架上找书，这样可能会比直接在书架上找书要慢一些。

总的来说，聚簇索引和非聚簇索引的主要区别在于它们如何存储数据和索引，以及它们如何影响数据的查询和更新操作。聚簇索引将数据直接存储在索引上，查询效率高，但更新操作可能较复杂；而非聚簇索引则通过索引指向数据，提供了更多的灵活性，但查询时可能需要额外的步骤。在选择使用哪种索引时，需要根据具体的应用场景和查询需求来决定。

• 聚簇索引 (Clustered Index)：它的叶子节点存储了 完整的用户数据行。可以理解为“数据就是索引，索引就是数据”。

• 非聚簇索引 (Non-Clustered Index)，也叫二级索引(Secondary Index)：它的叶子节点存储的是 索引列的值 以及对应数据行的 主键值。

graph LR
    subgraph "非聚簇索引 (name)"
        A[B+树根节点] --> B{非叶子节点};
        B --> C[叶子节点
name='Tom', id=18];
    end

    subgraph "聚簇索引 (id/主键)"
        D[B+树根节点] --> E{非叶子节点};
        E --> F[叶子节点
id=18, name='Tom', age=25, ...];
    end

    C -- "1. 查name索引拿到主键id=18" --> G((回表过程));
    G -- "2. 用主键id=18查聚簇索引" --> F;
    F -- "3. 获取整行数据,返回age=25" --> H((查询结果));

    style C fill:#f9f,stroke:#333,stroke-width:2px
    style F fill:#ccf,stroke:#333,stroke-width:2px

7. MySQL 中的回表是什么？

1. 回表：指使用非聚簇索引（二级索引）查询时，由于索引中只存储了索引列的值和主键值，当需要获取其他列的数据时，必须通过主键再次访问聚簇索引（主键索引）来获取完整行数据的过程。

   graph LR
       subgraph "客户端查询"
           A[SELECT age FROM user WHERE name = 'Tom']
       end
   
       subgraph "二级索引(name索引)"
           B["B+树根节点
   (name索引)"]
           C["非叶子节点"]
           D["叶子节点
   name='Tom', id=18"]
       end
   
       subgraph "聚簇索引(主键索引)"
           E["B+树根节点
   (主键索引)"]
           F["非叶子节点"]
           G["叶子节点
   id=18, name='Tom', age=25, address='北京'"]
       end
   
       subgraph "回表过程"
           H["步骤1: 查找name索引"]
           I["步骤2: 获取主键id=18"]
           J["步骤3: 用主键查聚簇索引"]
           K["步骤4: 返回完整行数据"]
       end
   
       A --> B
       B --> C
       C --> D
       D --> H
       H --> I
       I --> J
       J --> E
       E --> F
       F --> G
       G --> K
       K --> L["返回: age=25"]
   
       style D fill:#ffb3ba
       style G fill:#bae1ff
       style H fill:#ffffba
       style I fill:#ffffba
       style J fill:#ffffba
       style K fill:#ffffba
   

2. 为什么需要回表：使用非聚簇索引的二级索引查询时，只能查到索引列的值和其主键值，无法获取其他数据

3. 回表的缺点：回表会带来随机I/O, 频繁回表会导致效率非常低。所以不推荐使用 select *

4. 回表的其他场景：当查询的部分列没有包含在索引中时，即便使用了索引，也需要会去获取缺失的列数据，称为覆盖索引缺失。

5. 覆盖索引缺失发送场景：select 语句当中包含了非索引列; 索引的类型为 Hash 和 full-text 索引 （不存储列的值），不支持覆盖索引。

6. 如何减少回表：开启索引下推、使用覆盖索引、延迟关联

   • 覆盖索引：只查询索引中包含的字段 （完全避免回表操作）

   • 索引下推：在索引层将数据尽可能多的过滤掉，避免将数据查出来之后再过滤（减少回表的数据量）

   • 延迟关联：手动先获取所有的二级索引的主键，然后一次性通过主键批量化获取数据 （减少回表次数）

   优化策略	适用场景	优点	缺点	实现难度
   覆盖索引	查询字段固定且较少	完全避免回表，性能最佳	索引存储空间增大，维护成本高	简单
   索引下推	复合条件查询	自动优化，减少回表数量	需要MySQL 5.6+支持	无需额外实现
   延迟关联	大数据量分页查询	显著减少回表次数	查询逻辑复杂，需要两次查询	中等
   选择合适字段	所有查询场景	减少不必要的数据传输	需要明确业务需求	简单

   graph TD
       A["避免回表的策略"] --> B["覆盖索引"]
       A --> C["索引下推"]
       A --> D["延迟关联"]
   
   
       B --> B1["创建包含所需列的复合索引"]
       B --> B2["只查询索引中包含的字段"]
       B --> B3["完全避免回表"]
   
       C --> C1["MySQL 5.6+ 默认开启"]
       C --> C2["在索引层面过滤数据"]
       C --> C3["减少回表的数据量"]
   
       D --> D1["先通过索引获取主键"]
       D --> D2["再用主键批量获取数据"]
       D --> D3["减少回表次数"]
   
       style B fill:#e1f5fe
       style C fill:#f3e5f5
       style D fill:#e8f5e8
   

8. MySQL索引的最左前缀匹配原则是什么?

1. 最左前缀匹配原则的定义：使用联合索引的时候，查询的条件必须从索引的最左侧开始匹配。如果联合索引包含多个列，查询条件必须包含第一个列，然后是第二个列，以此类推。
2. 最左前缀匹配原则的原理：联合索引在B+树中的排列方式遵循从左到右的原则，例如联合索引(a, b, c)，在查询时，首先按照a的值进行排序，如果a的值相同，再查b的值，以此类推。
3. 常见场景：= 、>= 、<= 、 BETWEEN 、like (xx%) 都包含等值的情况，可以定位到某个数，然后进行范围扫描，不会出现停止匹配的现象。但是 > 和 < 则不行。
4. 部分不符合最左前缀匹配原则也能使用索引的原因：MySQL8当中引入了 Skip Can Range Access Method, 将缺失的左边的值查出来，如果左边缺失的列数据量少，则拼凑左边的索引，让SQL符合最左前缀匹配原则。

9. MySQL的覆盖索引是什么？

1. 覆盖索引定义：查询的所有字段都是二级索引，从而使查询可以直接访问二级索引二不需要访问实习的表数据(主键索引)。
2. 覆盖索引优点：减少I/O操作 ; 提高查询速度 (索引比表数据更加紧凑); 减少内存占用 (读取的索引页面而不是表数据页面)

graph LR
    A[客户端发起查询] --> B{分析查询语句}
    B --> C[解析索引使用情况]
    C --> D{是否为覆盖索引?}
    
    D -->|是| E[直接在二级索引中获取数据]
    E --> F[返回查询结果]
    
    D -->|否| G[在二级索引中查找主键ID]
    G --> H[使用主键ID回表查询]
    H --> I[在聚簇索引中查找完整行数据]
    I --> J[返回查询结果]
    
    F --> K[查询完成]
    J --> K
    
    style E fill:#90EE90
    style F fill:#90EE90
    style H fill:#FFB6C1
    style I fill:#FFB6C1

10. MySQL的索引下推 (ICP) 是什么?

1. 索引下推(ICP)定义: 减少回表查询，提高查询效率的行为。允许MySQL使用索引查找数据的时候，将部分查询条件下推到存储引擎层进行过滤，从而减少需要从表中读取的数据行，减少I/O。

2. 应用场景：比如当前表建了一个联合索引(a, b, c)，使用where条件的时候，由于b用得是 like '%xxx%' 需要回表查询 (like 'xx%' 不需要)。即先查询 a = '1' 的数据， 然后回表查询，最后进行where条件的过滤。如果使用索引下推之后 (MySQL 5.6)，在查完a = '1'的数据之后，可以先由存储引擎层进行where条件过滤，然后再回表查询， 减少回表查询的次数。

   SELECT * FROM people
     WHERE a='1'
     AND b LIKE '%123%'
   

   如联合索引index_name_age，假设数据库中有数据（张三，18）、（张三，28）、（张三，48）、（张三，8)

   【没有索引下推】查询name=‘张三’和age>30的数据时，会先匹配有四条数据name=‘张三’匹配成功，回表四次查询出带有name=‘张三’的四条数据，然后再根据age>30对这四条数据进行范围查找

   【使用索引下推】查询name=‘张三’和age>30的数据时，会先匹配有四条数据name=‘张三’匹配成功，然后age>30的数据，过滤完成后，再用主键索引去进行一次回表操作

graph TB
    subgraph "无索引下推 (MySQL 5.6之前)"
        A1[存储引擎层] --> A2[根据索引条件查找记录]
        A2 --> A3[返回所有符合索引条件的主键]
        A3 --> A4[Server层]
        A4 --> A5[逐条回表获取完整记录]
        A5 --> A6[在Server层应用WHERE条件过滤]
        A6 --> A7[返回最终结果]
    end
    
    subgraph "有索引下推 (MySQL 5.6+)"
        B1[存储引擎层] --> B2[根据索引条件查找记录]
        B2 --> B3[在存储引擎层直接应用WHERE条件]
        B3 --> B4{记录是否满足
下推条件?}
        B4 -->|是| B5[添加主键到结果集]
        B4 -->|否| B6[丢弃该记录]
        B6 --> B3
        B5 --> B7[返回过滤后的主键列表]
        B7 --> B8[Server层]
        B8 --> B9[回表获取完整记录]
        B9 --> B10[返回最终结果]
    end
    
    style B3 fill:#90EE90
    style B4 fill:#90EE90
    style A5 fill:#FFB6C1
    style A6 fill:#FFB6C1

11. MySQL建索引需要注意哪些事项？

【索引适合场景】

1. 频繁使用where 、order by 、group by、distinct 的字段 (加快操作速度)
2. 关联字段 (如果没有索引，连接的过程中，每个只都会进行一次全表扫描)

【不适合场景】

1. 字段频繁更新 (更新除了修改数据外，还需要维护索引信息 => 调整B+树会降低性能)
2. 字段值重复率高（区分度低，建立索引更加消耗资源）
3. 参与列计算的字段 (索引会失效)
4. 长字段 (text、 longtext) ：长字段占据的内存大，提升性能不明显。

【注】索引不是越多越好，因为每次修改都需要维护索引数据，消耗资源

12. MySQL中使用索引一定有效吗？如何排查索引效果？

【索引失效的情况】

• 联合索引不符合最左匹配原则
• 对索引列使用了运算(where id + 3 = 8)、函数 (lower()、count())、like '%xx%' 等操作
• 对索引列和非索引列使用 or 操作 (where name = "swimmingliu" or age = 34)
• 索引列类型不匹配导致的强制转换 (where name = 1 ==> where CAST(name AS signed int) = 1)

graph TD
    A[MySQL索引失效场景] --> B[查询条件问题]
    A --> C[索引结构问题]
    A --> D[数据类型问题]
    A --> E[优化器选择问题]
    
    B --> B1[最左前缀原则违反]
    B --> B2[函数和运算操作]
    B --> B3[通配符问题]
    B --> B4[范围查询问题]
    B --> B5[OR操作符问题]
    
    C --> C2[索引覆盖失效]
    
    D --> D1[数据类型不匹配]
    D --> D2[隐式类型转换]
    
    E --> E1[数据分布不均匀]
    E --> E2[表数据量过小]
    E --> E3[查询成本评估]

【如何查看失效】

利用explain命令 (前面最好加上analyse table xxx)

EXPLAIN 的 type 表示查询的访问类型，影响查询的效率。常见的值：

1. ref: 使用索引，查找匹配某个单一列的值（比如通过外键查找）。比 range 更高效。
2. range: 使用索引扫描某个范围内的值，适用于 BETWEEN、> < 等条件。
3. index: 全索引扫描，扫描整个索引结构，不读表数据，通常效率比全表扫描好。
4. all: 全表扫描，没有使用索引

总结：ref > range > index > all。

13. MySQL的索引数是否越多越好？why?

索引不是越多越好，因为对索引字段进行更新操作，需要调整B+树的结构，会导致数据库增加开销。

【注】阿里巴巴规范上表示索引一般不超过16个

**【时间开销】**进行增删改操作的时候，索引也必须更新。索引越多，需要修改的地方就越多，时间开销大。B+树可能会出现页分裂、合并等操作，时间开销更大。

【空间开销】 建立二级索引，都需要新建一个B+树，每个数据页面都是16KB。如果数据大，索引又多，占用的空间不小。

14. 为什么 MySQL 选择使用 B+ 树作为索引结构？

【B+树结构】

graph TD
    subgraph "B+树整体结构"
        Root["根节点
索引页
[20, 50, 80]"]
        
        subgraph "第二层-非叶子节点"
            Node1["非叶子节点1
[5, 10, 15]"]
            Node2["非叶子节点2
[25, 35, 45]"]
            Node3["非叶子节点3
[55, 65, 75]"]
            Node4["非叶子节点4
[85, 90, 95]"]
        end
        
        subgraph "第三层-叶子节点(数据页)"
            Leaf1["叶子节点1
key: 1,3,5
data: 行记录1,3,5"]
            Leaf2["叶子节点2
key: 8,10,12
data: 行记录8,10,12"]
            Leaf3["叶子节点3
key: 15,18,20
data: 行记录15,18,20"]
            Leaf4["叶子节点4
key: 22,25,28
data: 行记录22,25,28"]
            Leaf5["叶子节点5
key: 32,35,38
data: 行记录32,35,38"]
            Leaf6["叶子节点6
key: 42,45,48
data: 行记录42,45,48"]
            Leaf7["叶子节点7
key: 52,55,58
data: 行记录52,55,58"]
            Leaf8["叶子节点8
key: 62,65,68
data: 行记录62,65,68"]
            Leaf9["叶子节点9
key: 72,75,78
data: 行记录72,75,78"]
            Leaf10["叶子节点10
key: 82,85,88
data: 行记录82,85,88"]
            Leaf11["叶子节点11
key: 90,92,95
data: 行记录90,92,95"]
            Leaf12["叶子节点12
key: 97,98,99
data: 行记录97,98,99"]
        end
        
        Root --> Node1
        Root --> Node2
        Root --> Node3
        Root --> Node4
        
        Node1 --> Leaf1
        Node1 --> Leaf2
        Node1 --> Leaf3
        Node2 --> Leaf4
        Node2 --> Leaf5
        Node2 --> Leaf6
        Node3 --> Leaf7
        Node3 --> Leaf8
        Node3 --> Leaf9
        Node4 --> Leaf10
        Node4 --> Leaf11
        Node4 --> Leaf12
        
        Leaf1 -.-> Leaf2
        Leaf2 -.-> Leaf3
        Leaf3 -.-> Leaf4
        Leaf4 -.-> Leaf5
        Leaf5 -.-> Leaf6
        Leaf6 -.-> Leaf7
        Leaf7 -.-> Leaf8
        Leaf8 -.-> Leaf9
        Leaf9 -.-> Leaf10
        Leaf10 -.-> Leaf11
        Leaf11 -.-> Leaf12
    end

【B+树的优势】

• 高效的查找性能：B+树是一种自平衡树，每个叶子结点到根节点的路径长度相同。增删改查的事件复杂度都是O(logn)，且具有一定的冗余节点，删除节点的时候，树的结构变化较小。
• I/O次数相对较少：首先，B+树不会像红黑树一样，随着数据的增多树变得越来越高，它是多叉树。计算机访问数据时，往往具有局部性原理。当读取一个节点时，B树和B+树会将多个相关的数据加载到内存中，后续直接从内存反问，减少了磁盘的I/O。另外，相较于B树来说， B+树所有的数据都存放在叶子节点，而不像B树会在非叶子节点存储数据。B+树的非叶子节点仅存储索引值/主键和页面指针。
• 对范围查询友好：B+树的叶子节点之间通过链表链接。当使用between语句时，会从根节点找到满足条件的起始记录。然后从起始记录，沿着叶子结点的链表进行顺序遍历。

【B+树存在的部分缺点】

当插入和删除节点，会触发分裂和合并操作，保持树的平衡，有一定的开销。

【跳表】

跳表其实就是一个多级链表，为了让链表更高效的查询。在不同的部分插入高级索引，让其能够缩小查找范围。有一种二分的思想在里面。其中，Redis的有序集合(sorted set)底层的结构就是跳表结构。

【为什么MySQL不用跳表而用B+树】

• 跳表的I/O效率低：B+树通常只有3~4层，可以存储海量的数据。B+树的节点大小设计适配磁盘页的大小，磁盘页能够顺序存储大量数据。一次磁盘I/O操作就能读取节点的数据，减少I/O。跳表是多级索引的结构，虽然可以加速查找，但是其查找的过程当中会涉及到多次随机的I/O。
• 范围查询： B+树的叶子节点是有序链表，在采用between时，能够找从叶子结点按照链表顺序遍历即可。跳表虽然支持范围查询，但是实现起来很复杂， 而且其多层的索引结构，范围查询时不能像B+树那样直接高效。
• 跳表维护成本高：B+树在增删改的时候，有高效的算法平衡树结构，确保性能稳定。而跳表在新增和删除操作的时候，涉及多层链表的调整，开销较大，容易出现性能波动。
• 跳表内存占用大：B+树的节点紧凑，非叶子节点只存储索引项和页面指针。而跳表除了每个节点存储数据以外，还需要额外的开销存储多层索引。相同数据量下，跳表的开销比B+树大得多。

【B+树索引的SQL执行过程】

sequenceDiagram
    participant Client as SQL客户端
    participant Parser as SQL解析器
    participant Optimizer as 查询优化器
    participant Engine as 存储引擎
    participant Buffer as 缓冲池
    participant Disk as 磁盘

    Client->>Parser: SELECT * FROM users WHERE id = 45
    Parser->>Optimizer: 解析后的查询树
    Optimizer->>Engine: 选择索引访问路径
    
    Note over Engine: 开始B+树索引查找
    
    Engine->>Buffer: 检查根节点页面是否在内存
    alt 根节点不在内存
        Buffer->>Disk: 读取根节点页面
        Disk-->>Buffer: 返回根节点数据
    end
    Buffer-->>Engine: 根节点: [20, 50, 80]
    
    Note over Engine: 45 >= 20 && 45 < 50, 选择第二个指针
    
    Engine->>Buffer: 检查中间节点页面是否在内存
    alt 中间节点不在内存
        Buffer->>Disk: 读取中间节点页面
        Disk-->>Buffer: 返回中间节点数据
    end
    Buffer-->>Engine: 中间节点: [25, 35, 45]
    
    Note over Engine: 45 >= 45, 选择第三个指针
    
    Engine->>Buffer: 检查叶子节点页面是否在内存
    alt 叶子节点不在内存
        Buffer->>Disk: 读取叶子节点页面
        Disk-->>Buffer: 返回叶子节点数据
    end
    Buffer-->>Engine: 叶子节点: [42, 45, 48] + 对应行数据
    
    Note over Engine: 在叶子节点中找到key=45对应的完整行记录
    
    Engine-->>Optimizer: 返回查询结果
    Optimizer-->>Parser: 格式化结果
    Parser-->>Client: 返回查询结果集

15. MySQL 三层 B+ 树能存多少数据？

【三层B+树存储数据计算】

nodesCount = 16 * 1024 / (6 + 8) = 1170 // 每个节点可以存多少个子节点
recordCount = 16KB / 1KB = 16 // 每个节点可以存多少条数据记录
dataCount = nodesCount * nodesCount * recordCount = 1170 * 1170 * 16 = 21,902,400 
所以如果一条数据为1KB大小，B+树大约能存2000w条数据

【拓展】

MySQL的InnoDB引擎中，B+树m每个节点的数据页大小可以通过调整innodb_page_size来修改 (一般为 4KB / 8KB / 16KB)

16. MySQL如何进行SQL调优

分为预防和解决慢查询两个角度阐述。总结起来就三点，命中索引、减少回表、减少I/O.

【预防】

• 合理设计索引，减少回表次数，减少I/O
• 避免 select * 操作。因为正常情况下，部分字段是没有二次索引的，它会用主键id或者rowid 进行回表查询，会增加系统的I/O。
• 避免让索引失效，比如对索引字段进行计算、聚合函数、非同类型比较 (强制转换)和范围查询 (>、<、like %xxx%)。还有联合索引不匹配最左前缀原则
• 避免对非索引字段，使用group by、order by、dinstinct等函数
• 连表查询的是否需要保持不同字段的字符集一致，不然也会导致全表扫描。比如A表用utf-8，B表用latin1，查询的是否需要进行字符集转换，需要额外的计算，不能使用索引。

【解决慢查询】

• 开启慢SQL日志记录功能，使用set global slow_query_log = "ON"， 默认是关闭的。设置一个查询延迟的阈值，把超过规定时间的SQL查询找出来。
• 利用explain关键字分析慢SQL的原因，比如看看是否有索引失效、select *等情况

graph LR
    subgraph "MySQL性能调优方法体系"
        A["SQL层面优化"] --> A1["索引优化"]
        A --> A2["SQL语句优化"]
        A --> A3["表结构优化"]
        
        A1 --> A11["创建合适索引"]
        A1 --> A12["避免索引失效"]
        A1 --> A13["覆盖索引"]
        A1 --> A14["索引下推ICP"]
        
        A2 --> A21["避免SELECT *"]
        A2 --> A22["优化JOIN查询"]
        A2 --> A23["合理使用分页"]
        A2 --> A24["批量操作"]
        
        A3 --> A31["字段类型选择"]
        A3 --> A32["范式反范式权衡"]
        A3 --> A33["垂直分表"]
        
        B["架构层面优化"] --> B1["读写分离"]
        B --> B2["分库分表"]
        B --> B3["缓存机制"]
        B --> B4["数据冷热分离"]
        
        B1 --> B11["主从复制"]
        B1 --> B12["负载均衡"]
        B2 --> B21["水平分片"]
        B2 --> B22["垂直分片"]
        B3 --> B31["Redis缓存"]
        B3 --> B32["查询缓存"]
        
        C["系统层面优化"] --> C1["硬件配置"]
        C --> C2["参数调优"]
        C --> C3["连接池配置"]
        
        C1 --> C11["SSD硬盘"]
        C1 --> C12["增加内存"]
        C2 --> C21["innodb_buffer_pool_size"]
        C2 --> C22["max_connections"]
        C3 --> C31["连接池大小"]
        C3 --> C32["超时设置"]
        
        D["监控诊断"] --> D1["慢查询日志"]
        D --> D2["EXPLAIN分析"]
        D --> D3["Performance Schema"]
        D --> D4["监控工具"]
    end

[补充] 16.1 MySQL中的慢SQL如何排查 （完整排查链路）

【慢SQL完成排查流程】

flowchart LR
    A["发现慢SQL"] --> B{"慢SQL来源"}
    
    B --> B1["慢查询日志"]
    B --> B2["监控告警"]
    B --> B3["应用响应慢"]
    
    B1 --> C["开启慢查询日志
long_query_time=1"]
    B2 --> C
    B3 --> C
    
    C --> D["定位具体SQL语句"]
    D --> E["使用EXPLAIN分析执行计划"]
    
    E --> F{"执行计划分析"}
    F --> F1["type: ALL/index
(全表扫描)"]
    F --> F2["key: NULL
(未使用索引)"]
    F --> F3["rows: 很大
(扫描行数多)"]
    F --> F4["Extra: Using filesort
Using temporary"]
    
    F1 --> G["索引优化"]
    F2 --> G
    F3 --> G
    F4 --> G
    
    G --> H{"优化策略选择"}
    
    H --> H1["创建索引"]
    H --> H2["SQL重写"]
    H --> H3["表结构调整"]
    H --> H4["分库分表"]
    
    H1 --> I1["单列索引
复合索引
覆盖索引"]
    H2 --> I2["JOIN优化
子查询优化
分页优化"]
    H3 --> I3["字段类型优化
冗余字段
垂直分表"]
    H4 --> I4["水平分片
垂直分片
读写分离"]
    
    I1 --> J["执行优化方案"]
    I2 --> J
    I3 --> J
    I4 --> J
    
    J --> K["再次EXPLAIN验证"]
    K --> L{"性能是否满足要求"}
    
    L --> L1["是"] 
    L --> L2["否"]
    
    L1 --> M["持续监控"]
    L2 --> N["继续深度优化"]
    
    N --> N1["查看Profile"]
    N --> N2["分析锁等待"]
    N --> N3["优化参数配置"]
    
    N1 --> H
    N2 --> H  
    N3 --> H
    
    M --> O["优化完成"]

17. 如何使用MySQL的EXPLAIN语句进行查询分析?

【EXPLAIN查询结果解释】

【type说明】

• system: 表明查询的表只有一行 (系统表)

• const : 表明查询的表最多只有一行匹配结果。通常是查询条件为主键或唯一索引， 并且是常量比较。

• eq_ref: 表明对于每个来自钱一张表的行，MySQL只访问一次该表，通常发生在链接查询中使用主键或唯一索引的情况下。

• ref：MySQL 使用非唯一索引查询。查询的条件是非唯一的

• range: MySQL 会扫描表的一部分，不是全部行。通常出现在索引的范围查询中 (比如>=、<=、BETWEEN)

• index: 表示MySQL扫描索引中的所有行，但不是扫描表的所有行。

• all：表示需要扫描表的所有行，全表扫描。一般出现在没有索引的查询条件中。

18. 请详细描述 MySQL 的 B+ 树中查询数据的全过程

【B+树查询过程】

可以类比成去电影院 (4号厅 ) 找位置 的过程

1. 买票进门，从根节点(Page 20)出发，主键值为4, 范围在[1,5)中间，需要到 Page 2 非叶子节点查询
2. 进入 Page 2 非叶子节点，主键值大于3，需要到Page 5 的叶子节点查询
3. 进入Page 5 的叶子节点，通过Page Directory 内的槽查找记录，使用二分法快速定位查询记录在那个槽。
4. 定位到槽之后遍历所有的记录，找到主键为 4 的记录

【Page Directory 页目录查找过程】

假如页目录当中有5个槽，现在需要查找主键值为3的记录。查找过程如下：

1. 二分查找定位到槽2
2. 槽2的最大记录是4，记录二分查找定位到槽1
3. 槽1的最大记录是2，因为3 > 2， 直接向前遍历查询到主键值为 3 的记录

【B+树数据页的结构】

InnoDB 当中B+树的每个节点以数据页(Page)为单位存储，每页默认大小为16KB。

• 文件头： 记录叶子节点的上下页 (因为叶子节点是双向链表连接起来的)
• 最大和最小记录：表示页面当中最小的记录和最大的记录 （虚拟的记录） 在真实行记录的两侧
• 页目录: 数据页被分为若干个组，每个组对应一个槽 (Slot)。页目录内记录这些槽的位置，实现基于当前数据也的二分查找的快速定位。

【B+树的优势】

参见问题14. 为什么 MySQL 选择使用 B+ 树作为索引结构？

19. MySQL 中 count(*)、count(1) 和 count(字段名) 有什么区别？

【效率层面】 count(*) ≈ count(1) > count(唯一索引) > count(主键) > count(其他字段)

【具体区别】

20. MySQL 中 varchar 和 char 有什么区别？

【主要区别】

【注意事项】

1. varchar 长度不要太大：因为MySQL在利用order by排序的过程当中，会用到 sort_buff。如果varchar所设定的长度过大，就会使用双路排序。而双路排序在对排序字段排序之后，只能拿到主键值和索引列的值。需要使用主键值再进行回表查询操作，会增加系统的I/O，降低系统性能。
2. varchar(n) 当中的n 表示的是字符数，而不是字节数。通常最大行长度是 65535 字节，如果允许未null， 需要额外一个字节标注是否未null。 而varchar 需要1~2个字节来标注字段的长度。所以，支持的最大长度为65535-2 = 65533 字节。一般情况下，UTF-8字符集占用3个字节。所以，最大字符数n 为 65533 / 3 = 21844 个字符

21. MySQL 是如何实现事务的？

【事务四个特性 - AIDC】

• 原子性：事务要么全部执行成功，要么全部执行失败
• 隔离性：并发的事务之间相互是不干扰的，可见性由隔离级别进行控制。MySQL的默认隔离级别是RR，可重复读
• 持久性：事务一旦提交，确保修改的数据会被永久保存
• 一致性：事务执行前后，数据库要保持一直的状态，所有的业务规则、约束和触发器的规则必须满足。

【如何实现事务】

实现事务其实就是要确保满足事务的四个特性，如何满足呢？

• 原子性：通过Uodo Log 实现，从事务开始的时候，Undo Log 里面会存储事务的反向操作。就是保存数据的历史版本把，用于事务的回滚，让事务执行失败之后可以恢复到之前的样子。

• 隔离性: 通过锁和MVCC 多版本并发控制来实现的，主要是控制不同隔离级别下事务间的方法，确保事务之间不相互干扰。

• 持久性：通过Redo Log来实现的，Redo Log会记录事务对数据库的所有修改操作。当MySQL发送宕机或崩溃的时候，可以根据Redo Log 里面的记录来恢复数据。满足事务的持久性。

• 一致性： 其实事务的一致性就是AID实现的，也就是说事务是通过原子性、隔离性、持久性来满足一致性的。

22. MySQL有哪些锁的类型?

【按粒度分类】

1. 全局锁: 对整个数据库进行加锁，处于只读的状态，一般用于数据库逻辑备份。这个时候所有的数据操作(增删改)和表结构操作(ALTER 和 DROP)都会被阻塞。
2. 表级锁: 锁的是整张表。实现比较简单，资源消耗低。
3. 行级锁：锁的是某一行。粒度最小，支持高并发。但是加锁的开销大，可能导致死锁。

【按功能分类】

1. 共享锁 (S 锁, share Lock): 读锁，顾名思义是共享的，所以可以共享锁之间可以兼容，一般用于事务读取数据的时候
2. 排他锁 (X 锁, exclusive lock)：写锁，顾名思义是拒绝别人的，所以不允许多个事务同时获取，排他锁之间不兼容。一般用于事务修改记录的时候。

-- 添加共享锁
SELECT ... LOCK IN SHARE MODE;	
-- 共享锁
SELECT ... FOR SHARE 		# MySQL 8.x 版本
-- 排他锁
SELECT .... FOR UPDATE;

【全局锁】

直接锁住整个数据库，处于只读模式。业务只能读取数据，不能更新数据。

FLUSH TABLES WITH READ LOCK

【表级锁】

1. 表锁

   • 表级共享锁：阻止其他会话对表的写操作，当前会话只能读该表，不能访问其他表
   • 表级排他锁：阻止其他会话对标进行任何操作（读和写），当前会话只能读该表，不能访问其他表

   # 添加表级共享锁
   lock tables user read;
   # 添加表级别排它锁
   lock tables user write;
   

2. 元数据锁：事务执行SELECT 的时候，其他线程的DDL操作(ALTER、DROP)操作会被阻塞，直到事务提交

3. 意向锁

   • 意向共享锁 (IS)：表明有意向对该表某些记录添加共享锁 (S 锁)
   • 意向排他锁 (IX)：表明有意向对该表某些记录添加排他锁 (X 锁)

   意向锁之间相互兼容，不会和行级别的共享锁和排他锁发生冲突。但是，意向排他锁和共享锁、排他锁之间是冲突的。

   锁名称	S	X	IS	IX
   S	✅	❌	✅	❌
   X	❌	❌	❌	❌
   IS	✅	❌	✅	✅
   IX	❌	❌	✅	✅

4. 自增锁

   用于主键自增的一种锁。事务向有自增列的表插入数据是会先获取自增锁，拿不到锁就被阻塞。但是可以通过修改innodb_autoinc_lock_mode自增锁模式进行调整，自增锁的具体实现方式：

   自增锁模式	介绍	说明
   0	传统模式	采用AUTO-INC 锁，语句执行结束释放
   1	连续模式	对普通insert，自增锁申请后马上释放。对于批量插入，等语句执行结束之后释放
   2	交错模式	申请自增主键后马上释放，无需等待语句执行完

【行级锁】

1. 记录锁

   • 事务对某条记录加S锁，其他记录也可以加，但是不能加X锁
   • 事务对某条记录加X锁，其他事务既不能加S锁也不能加X锁

   BEGINE;
   # 针对主键 id 为 2 的这条记录添加 X 型的记录锁；其他事务就无法对这条记录进修改
   SELECT * FROM user WHERE id = 2 FOR UPDATE;
   

2. 间隙锁

   防止在可重复读的隔离级别下，出现幻读问题。

   比如，事务A开始读取数据, 发现是3条数据。然后，事务B加了一条数据进去。事务A在读去数据,发现是4条数据, 前后数据总数不一致就是幻读。

3. 临键锁：记录锁 + 间隙锁的组合，既可以锁住记录，也可以防止幻读

4. 插入意向锁

   意向锁用于快速判断是否可以对某张表加表锁，而无需判断表中具体行的锁定情况。

   插入意向锁的作用：

   • 标记插入意向图：事务告诉InnoDB，它计划在某个间隙范围内插入新数据。
   • 允许多个事务并发插入不同位置：也就是说如果插入的范围不同，插入意向锁之间互不从突。

   【注意】

   • 一个事务有间隙锁时，另外一个事务不能在相同范围内加插入意向锁
   • 一个事务有插入意向锁是，另外一个事务不能在相同范围内假如间隙锁

23. MySQL 中的 MVCC 是什么？

【当前读和快照读】

• 当前读：select ... lock in share mode、select ... for update、insert/delete/upate 有锁，会阻塞其他事务。当前读不会生成ReadView， 只会加上临键锁next-key lock (记录锁+间隙锁)
• 快照读：直接 select，普通的查询操作，不加任何锁，不会阻塞其他事务。会生成ReadView，不会有幻行

【当前读和快照读事务执行流程区别】

sequenceDiagram
    participant T1 as 事务1
    participant T2 as 事务2
    participant DB as 数据库
    participant MVCC as MVCC版本链
    
    Note over T1, MVCC: 场景1：快照读
    T1->>DB: BEGIN
    T1->>MVCC: SELECT * FROM users WHERE id=1 (快照读)
    MVCC-->>T1: 返回快照版本数据: name='Alice'
    
    T2->>DB: BEGIN
    T2->>DB: UPDATE users SET name='Bob' WHERE id=1
    T2->>DB: COMMIT
    
    T1->>MVCC: SELECT * FROM users WHERE id=1 (快照读)
    MVCC-->>T1: 仍返回快照版本: name='Alice'
    T1->>DB: COMMIT
    
    Note over T1, MVCC: 场景2：当前读
    T1->>DB: BEGIN
    T1->>DB: SELECT * FROM users WHERE id=1 FOR UPDATE (当前读)
    DB-->>T1: 获取排他锁，返回最新数据: name='Bob'
    
    T2->>DB: BEGIN
    T2->>DB: SELECT * FROM users WHERE id=1 FOR UPDATE (当前读)
    Note over T2, DB: 等待T1释放锁
    
    T1->>DB: UPDATE users SET name='Charlie' WHERE id=1
    T1->>DB: COMMIT
    
    DB-->>T2: 获取锁成功，返回最新数据: name='Charlie'
    T2->>DB: COMMIT

【隔离级别】

不同的隔离级别分别解决了脏读、不可重复读、幻读的问题。

【注意】只有读已提交 RC 和可重复读 RR 才会用到快照读

• 可重复读 RR，快照会在事务开始时生成，对数据进行更改才修改快照
• 读已提交 RC ，每次读取都会重新生成快照，总是读取行的最新版本，所以不可重复读

【MVCC】

MVCC多版本控制并发主要是用来解决 读-写并发 所引起的问题的

1. 隐藏字段：
   • db_row_id: 如果没有创建主键，就用这个字段来创建聚簇索引
   • db_trx_id：对该记录左最新一次修改的事务的ID
   • db_roll_ptr: 回滚指针，指向当前行记录的上一个版本，也就是指向undo log当中上一个版本的快照地址

2. Read View: 隐藏字段和 undo log版本决定的是返回的数据，但是具体返回哪个版本，由read view 和版本链返回规则可见性算法控制

   • trx_ids : 表示生成readview是,当前系统中活跃的读写事务的事务ID列表
   • low_limit_id：应该分配给下一个事务的id值 (最大事务id + 1)
   • up_limit_id: 未提交的事务中最小的事务id (最小事务id)
   • creator_trx_id: 创建该readview的事务id

   

   什么情况是可以看见的? trx_id == creator_trx_id (当前事务修改的)、trx_id < up_limit_id (事务已提交)，up_limit_id < trx_id < low_limit_id (如果trx_id 不在 trx_ids 里面，说明不是这条数据不是存活的事务掌控的，数据已经提交了) 都是可见的。 trx_id > low_limit_id 是不可以访问的

   如果发现当前的记录是不可见的，那么就需要找undo log日志的历史快照了，如果找不到，则返回空。

   

   【不同隔离版本ReadView的产生时机】

   • 读已提交 RC，每次select 都会获取一次Read View
   • 读未提交 RR， 只有第一次select才会获取Read View

   【二级索引在索引覆盖通过的时候可以用MVCC吗？】

   已知如果查询字段包含了所有的二级索引，那么就会走索引覆盖，而不会回表用主键或row_id去读主键索引的页记录。但是，版本链的头节点在主键索引当中 ( 版本链包含row_id ), 通过二级索引的记录没法儿直接找到版本链。这种情况如何用MVCC？

   二级索引中，用一个额外的page_max_trx_id 来记录修改过该页的最大事务id

   1. 如果查询到的readview 的最小未提交的事务id > page_max_trx_id， 说明在创建该readview时，最后一次更新二级索引的事务已经提交了，也就是说对当前查询是可见的，如果二级索引的记录没有被删除，就直接走索引覆盖。
   2. 如果最小未提交的事务id <= page_max_trx_id， 意味着数据可能被修改了。不能直接查询，需要回表，用聚簇索引进行查询。聚簇索引中，叶子结点行记录包含了版本链，可以用MVCC。

   【可重复读RR隔离级别是否可以解决幻读】

   RR隔离机制不能完全解决幻读的现象，虽然它用了间隙锁，在一定程度上可以解决幻度。

   但是，如果存在下面这种情况就不行。

   1. 事务A进行快照读, 然后事务B插入了一条记录并提交。此时，事务A是可以update 这条语句的，这样就出现了幻读。
   2. 当事务中先执行快照读，再执行当前读时，可能因读取最新数据而触发幻读

   -- 事务A（RR隔离级别）
   BEGIN;
   SELECT * FROM users WHERE age > 20; -- 快照读，返回空结果
   -- 事务B插入 age=25 的记录并提交
   SELECT * FROM users WHERE age > 20 FOR UPDATE; -- 当前读，返回事务B插入的记录
   

下面是快速图解版

【MVCC 结构】

graph TB
    subgraph "数据表"
        T[账户表] --> R1["记录1: 余额=100万"]
        R1 --> H1["db_trx_id: 事务ID"]
        R1 --> H2["db_roll_ptr: 回滚指针"]
        R1 --> H3["db_row_id: 行ID"]
    end
    
    subgraph "Undo Log版本链"
        U1["[版本1]
余额 = 100万
trx_id = 1
roll_ptr → U2"]
        U2["[版本2]
余额 = 80万
trx_id = 2
roll_ptr → U3"]
        U3["[版本3]
余额 = 50万
trx_id = 3
roll_ptr → NULL"]
        U1 --> U2
        U2 --> U3
    end
    subgraph "Read View 结构"
        RV["Read View"]
        RV --> TV["trx_ids: [活跃事务ID列表]"]
        RV --> LT["low_limit_id: 下一个事务ID"]
        RV --> UT["up_limit_id: 最小活跃事务ID"]
        RV --> CT["creator_trx_id: 创建者事务ID"]
    end
    
    subgraph "MVCC判断流程"
        J1["判断 trx_id == creator_trx_id?"]
        J2["判断 trx_id < up_limit_id?"]
        J3["判断 trx_id >= low_limit_id?"]
        J4["判断 trx_id 在 trx_ids 中?"]
        
        J1 -->|是| V1["可见"]
        J1 -->|否| J2
        J2 -->|是| V2["可见"]
        J2 -->|否| J3
        J3 -->|是| NV1["不可见"]
        J3 -->|否| J4
        J4 -->|在| NV2["不可见"]
        J4 -->|不在| V3["可见"]
    end
    
    H2 -.->|指向| U1
    RV -.->|查找版本链| U1

【MVCC - 执行过程】

sequenceDiagram
    participant T as 事务线程
    participant RV as Read View
    participant UL as Undo Log版本链
    participant R as 记录版本
    
    Note over T: 事务开始读取操作
    T->>RV: 创建/获取Read View
    RV-->>T: 返回Read View
(trx_ids, up_limit_id, low_limit_id, creator_trx_id)
    
    T->>UL: 获取当前记录版本
    UL-->>T: 返回最新版本记录
    
    loop 遍历版本链直到找到可见版本
        T->>R: 获取当前版本的trx_id
        R-->>T: 返回版本的trx_id
        
        Note over T,RV: 开始可见性判断
        
        T->>RV: 判断 trx_id == creator_trx_id?
        alt trx_id 等于 creator_trx_id
            RV-->>T: 可见(自己修改的记录)
            Note over T: 返回当前版本数据
        else 不等于 creator_trx_id
            T->>RV: 判断 trx_id < up_limit_id (最小未提交的事务id)? 
            alt trx_id < up_limit_id
                RV-->>T: 可见(已提交的旧事务)
                Note over T: 返回当前版本数据
            else trx_id >= up_limit_id
                T->>RV: 判断 trx_id >= low_limit_id (下一个待分配的事务id)?
                alt trx_id >= low_limit_id
                    RV-->>T: 不可见(未来事务)
                    T->>UL: 通过roll_ptr获取上一版本
                    UL-->>T: 返回上一版本记录
                else trx_id < low_limit_id
                    T->>RV: 判断 trx_id 是否在 trx_ids 中?
                    alt trx_id 在 trx_ids 中
                        RV-->>T: 不可见(活跃未提交事务)
                        T->>UL: 通过roll_ptr获取上一版本
                        UL-->>T: 返回上一版本记录
                    else trx_id 不在 trx_ids 中
                        RV-->>T: 可见(已提交事务)
                        Note over T: 返回当前版本数据
                    end
                end
            end
        end
    end
    
    Note over T: 找到可见版本或到达版本链末尾

[补充] 23.1 MySQL事务的四大隔离级别实现原理？

• 读未提交：可以读到未提交的数据，所以直接读最新的数据即可
• 读已提交和可重复读：是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。
  • 读已提交：每个语句执行前， 都会重新生成一个 Read View 。可以确保只读到已经提交过的事务，如果没提交在Read View里面是看不到的。
  • 可重复读：启动事务时，生成一个 Read View，整个事务期间都在用这个 Read View 。所以，可以确保读取到的都是同一个事务内修改过的数据。即便事务执行过程中有其他已经事务提交，也看不到其他事务修改过的数据记录。
• 串行化：通过加读写锁的方式来避免并行访问

【案例分析】

假设有一张账户余额表，里面有一条账户余额为 100 万的记录。然后有两个并发的事务，事务 A 只负责查询余额，事务 B 则会将我的余额改成 200 万，下面是按照时间顺序执行两个事务的行为：

【分析】

• 可重复读：下面事务并发的执行过程，利用MVCC的快照读解决了不可重复读的问题。（因为在事务开始的时候，就生成了一个ReadView。确保读取到的数据一定是当前事务和之前已提交事务的数据记录，后面的记录都读取不到）
• 读已提交：如果是读已提交，每次执行都会生成一个ReadView。当事务A第二次读区的时候，就可以读区到事务B提交的数据了。

sequenceDiagram
    participant DB as 数据库记录
    participant UA as 事务A的Undo Log
    participant UB as 事务B的Undo Log
    participant TA as 事务A(trx_id=100)
    participant TB as 事务B(trx_id=101)
    
    Note over DB: 初始状态: 余额=100万, trx_id=99
    
    Note over TA: 1. 事务A启动
    TA->>TA: 生成Read View A
up_limit_id=100
low_limit_id=102
trx_ids=[100]
creator_trx_id=100
    
    Note over TA: 2. 事务A首次读取
    TA->>DB: SELECT 余额
    DB-->>TA: 返回100万
(trx_id=99 < up_limit_id=100, 可见)
    
    Note over TB: 3. 事务B启动
    TB->>TB: 生成Read View B
up_limit_id=100
low_limit_id=102
trx_ids=[100,101]
creator_trx_id=101
    
    Note over TB: 4. 事务B首次读取
    TB->>DB: SELECT 余额
    DB-->>TB: 返回100万
(trx_id=99 < up_limit_id=100, 可见)
    
    Note over TB: 5. 事务B更新数据
    TB->>UB: 创建Undo Log记录
old_value=100万, trx_id=99
    TB->>DB: UPDATE 余额=200万, trx_id=101
    Note over DB: 当前记录: 余额=200万, trx_id=101
roll_ptr→Undo Log(100万, trx_id=99)
    
    Note over TA: 6. 事务A第二次读取(V1)
    TA->>DB: SELECT 余额
    Note over TA,DB: 使用事务A的Read View判断可见性
    Note over TA: trx_id=101 在 trx_ids=[100] 中? 不在
trx_id=101 >= low_limit_id=102? 否
但 trx_id=101 > up_limit_id=100
    TA->>UB: 通过roll_ptr访问Undo Log
    UB-->>TA: 返回100万
(trx_id=99 < up_limit_id=100, 可见)
    
    Note over TB: 7. 事务B提交
    TB->>DB: COMMIT
    Note over DB: 事务B提交，记录持久化
    
    Note over TA: 8. 事务A第三次读取(V2)
    TA->>DB: SELECT 余额
    Note over TA: 仍使用原来的Read View A
(RR级别下Read View不变)
    TA->>UB: 通过roll_ptr访问Undo Log
    UB-->>TA: 返回100万
(可重复读保证)
    
    Note over TA: 9. 事务A提交
    TA->>DB: COMMIT
    
    Note over DB: 10. 最终读取(V3)
    Note over DB: 新事务读取会看到200万
(事务B的修改已提交)

[补充] 23.2 MySQL事务的四大特性的底层原理？

【事务的四大特性定义】

• 原子性（Atomicity）：事务中的所有操作要么全部成功执行并提交，要么全部失败并回滚，不存在部分执行的情况。就像转账操作，扣款和加款必须同时成功或同时失败。

• 隔离性（Isolation）：并发执行的多个事务之间相互隔离，一个事务的执行不应该被其他事务干扰。通过不同的隔离级别来控制并发访问。

• 持久性（Durability）：事务一旦提交，其对数据库的修改就是永久性的，即使系统崩溃也不会丢失。

  【为什么需要持久化？】

  graph LR
      A[用户发起转账请求
  转账1000元] --> B[MySQL处理事务]
      B --> C[在内存中修改数据
  账户A: 5000→4000
  账户B: 3000→4000]
      C --> D[返回成功响应给用户
  用户认为转账完成]
      D --> E{系统是否崩溃?}
  
      E -- 正常运行 --> F[数据最终写入磁盘
  数据安全保存]
      E -- 突然断电/崩溃 --> G[内存数据丢失
  修改全部消失]
  
      G --> H[严重后果]
      H --> I[用户金钱损失
  账户A已扣款
  账户B未到账]
      H --> J[银行信誉受损
  监管合规问题]
      H --> K[数据不一致
  总金额不守恒]
  
      F --> L[数据一致性保证
  业务正常运行]
  
      style A fill:#e1f5fe
      style G fill:#ffebee
      style H fill:#ffcdd2
      style I fill:#ffcdd2
      style J fill:#ffcdd2
      style K fill:#ffcdd2
      style L fill:#e8f5e8
  

• 一致性（Consistency）：事务执行前后，数据库必须处于一致性状态。所有数据完整性约束都得到满足，不会出现数据不一致的情况。

【四大特性的关系】

graph TB
    subgraph "MySQL事务ACID特性"
        A["原子性 Atomicity
事务要么全部成功
要么全部失败"]
        I["隔离性 Isolation
并发事务之间
相互独立"]
        D["持久性 Durability
事务提交后
永久保存"]
        C["一致性 Consistency
数据库状态
保持一致"]
    end
    
    A --> C
    I --> C  
    D --> C
    
    style A fill:#e1f5fe
    style I fill:#f3e5f5
    style D fill:#e8f5e8
    style C fill:#fff3e0

【四大特性实现原理】

1. 原子性：原子性主要通过 Undo Log （回滚日志）来实现，Undo Log 的生命周期有三个阶段。

   • 操作记录阶段：当事务对数据进行修改时，MySQL会先将原始数据记录到 Undo Log 中，然后再执行实际的修改操作。如果是修改操作，Undo Log 存的是数据、trx_id、row_id、rollback_pt (MVCC 记录)。如果是新增操作，会记录一条对应的删除操作，用于后续回滚。

   • 回滚处理阶段：如果事务需要回滚（主动ROLLBACK或系统异常），MySQL会读取 Undo Log 中记录的原始数据，并使用这些数据将数据库恢复到事务开始前的状态。

   • 提交处理阶段：如果事务成功提交，Undo Log 中的数据会被标记为可清理状态，等待后台进程回收。

   sequenceDiagram
       participant Client as 客户端
       participant Server as MySQL服务器
       participant UndoLog as Undo Log
       participant Buffer as 缓冲池
       participant Storage as 存储引擎
   
       Client->>Server: BEGIN TRANSACTION
       Server->>UndoLog: 创建事务ID和Undo Log空间
   
       Client->>Server: UPDATE users SET balance=1000 WHERE id=1
       Server->>UndoLog: 记录原始数据
   (id=1, balance=500)
       Server->>Buffer: 修改缓冲池数据
   (id=1, balance=1000)
   
       Client->>Server: INSERT INTO orders VALUES(1, 'item1')
       Server->>UndoLog: 记录插入操作的反向删除记录
   (DELETE FROM orders WHERE id=1)
       Server->>Buffer: 在缓冲池中插入新记录
   
       alt 事务提交成功
           Client->>Server: COMMIT
           Server->>Storage: 将缓冲池数据刷新到磁盘
           Server->>UndoLog: 标记Undo Log为可清理状态
           Server-->>Client: 事务提交成功
       else 事务回滚
           Client->>Server: ROLLBACK (或系统异常)
           Server->>UndoLog: 读取Undo Log中的历史数据
           Server->>Buffer: 使用Undo Log恢复原始数据
   (id=1, balance=500)
           Server->>Buffer: 删除已插入的订单记录
           Server-->>Client: 事务回滚完成
       end
   

2. 持久性：主要通过 Redo log 、双阶段提交、双写缓存区来确保事务的持久性

   • Redo Log机制：MySQL使用WAL（Write-Ahead Logging）策略，先写日志再写数据。即使系统崩溃，也能通过重放Redo Log恢复数据。

   • 两阶段提交：协调Redo Log和Binary Log的写入顺序，确保事务的一致性和持久性。

   • 双写缓冲区：防止页面写入过程中的部分写问题，确保数据页的完整性。

   【持久化机制流程图】

   graph LR
   		 subgraph "崩溃恢复机制"
   		 				direction LR
               Y[系统重启] --> Z[读取Redo Log]
               Z --> AA[从检查点开始重放]
               AA --> BB[恢复未刷盘的修改]
               BB --> CC[数据完整性验证]
        end
   
       subgraph "二阶段提交"
       		direction LR
           DD[Phase 1: Prepare] --> EE[写入Prepare状态]
           EE --> FF[刷新Redo Log]
           FF --> GG[写入Binary Log]
           GG --> HH[Phase 2: Commit]
           HH --> II[写入Commit状态]
           II --> JJ[事务完成]
       end
   
       subgraph "用户层"
           A[客户端应用] --> B[事务提交请求]
       end
   
       subgraph "MySQL服务器层"
           B --> C[SQL解析器]
           C --> D[事务管理器]
           D --> E[存储引擎接口]
       end
   
       subgraph "InnoDB存储引擎"
           E --> F[缓冲池 Buffer Pool]
   
           subgraph "内存结构"
               F --> G[数据页缓存]
               F --> H[索引页缓存]
               F --> I[Undo页缓存]
   
               J[Log Buffer] --> K[Redo Log Records]
           end
   
           subgraph "磁盘持久化机制"
               L[Redo Log Files] --> M[ib_logfile0]
               L --> N[ib_logfile1]
   
               O[数据文件] --> P[表空间 .ibd]
               O --> Q[系统表空间 ibdata]
   
               R[双写缓冲区] --> S[Double Write Buffer
   连续的磁盘区域]
           end
   
           subgraph "WAL写入流程"
               G --> T{数据页修改}
               T --> U[1. 记录到Redo Log Buffer]
               U --> V[2. 写入Redo Log Files
   fsync强制刷盘]
               V --> W[3. 标记页面为脏页]
               W --> X[4. 后台异步刷脏页]
           end
       end
   
       K --> U
       V --> L
       X --> R
       R --> O
   
       style F fill:#e3f2fd
       style L fill:#fff3e0
       style R fill:#f3e5f5
       style DD fill:#e8f5e8
   

   【持久化机制-执行过程详细时序图】

   sequenceDiagram
       participant Client as 客户端
       participant Server as MySQL服务器
       participant Buffer as 缓冲池
       participant RedoLog as Redo Log
       participant BinLog as Binary Log
       participant Disk as 磁盘存储
       participant DoubleWrite as 双写缓冲区
   
       Client->>Server: BEGIN TRANSACTION
   
       Client->>Server: UPDATE users SET balance=1000 WHERE id=1
   
       Note over Server: 开始两阶段提交过程
   
       Server->>Buffer: 在缓冲池中修改数据页
   (balance=1000)
   
       Server->>RedoLog: 写入Redo Log记录
   (LSN=1001, 页面修改详情)
       RedoLog->>RedoLog: 将日志写入Log Buffer
   
       Client->>Server: COMMIT
   
       Note over Server: 第一阶段：准备提交
   
       Server->>RedoLog: 写入Prepare状态的Redo Log
   (XID=12345, state=PREPARE)
       RedoLog->>Disk: 强制刷新Redo Log到磁盘
   (fsync调用)
   
       Server->>BinLog: 写入Binary Log记录
   (XID=12345, SQL语句)
       BinLog->>Disk: 强制刷新Binary Log到磁盘
   (fsync调用)
   
       Note over Server: 第二阶段：最终提交
   
       Server->>RedoLog: 写入Commit状态的Redo Log
   (XID=12345, state=COMMIT)
       RedoLog->>Disk: 刷新Commit记录到磁盘
   
       Server-->>Client: 返回提交成功响应
   
       Note over Server: 后台异步刷脏页过程
   
       par 脏页刷新
           Server->>DoubleWrite: 将脏页先写入双写缓冲区
           DoubleWrite->>Disk: 刷新双写缓冲区到磁盘
           DoubleWrite-->>Server: 双写完成确认
           Server->>Disk: 将脏页写入实际数据文件位置
       and 检查点处理
           Server->>RedoLog: 推进检查点LSN
           Server->>RedoLog: 清理已刷新脏页对应的Redo Log
       end
   
       Note over Server: 崩溃恢复场景
       alt 系统崩溃后重启
           Server->>RedoLog: 从检查点开始读取Redo Log
           Server->>Buffer: 根据Redo Log重放未刷盘的修改
           Server->>Disk: 重新应用所有已提交事务的修改
           Note over Server: 持久性得到保证，数据不会丢失
       end
   

3. 隔离性: 主要通过锁和MVCC两大机制来保证并发事务之间，事务能够相互独立。

   sequenceDiagram
       participant T1 as 事务1
   (Transaction ID: 100)
       participant T2 as 事务2
   (Transaction ID: 101)
       participant LockManager as 锁管理器
       participant MVCC as MVCC引擎
       participant UndoLog as Undo Log
       participant DataPage as 数据页
   
       Note over T1, T2: 假设两个并发事务同时操作用户余额
   
       T1->>LockManager: BEGIN TRANSACTION
       LockManager->>T1: 分配事务ID: 100
   
       T2->>LockManager: BEGIN TRANSACTION  
       LockManager->>T2: 分配事务ID: 101
   
       T1->>LockManager: 请求修改用户ID=1的数据
   UPDATE users SET balance=1000 WHERE id=1
       LockManager->>T1: 获得行级写锁(X锁)
       T1->>UndoLog: 记录原始数据(balance=500)
       T1->>DataPage: 修改数据页
   (balance=1000, trx_id=100)
   
       T2->>MVCC: 读取用户ID=1的数据
   SELECT balance FROM users WHERE id=1
       MVCC->>UndoLog: 检查当前活跃事务列表
   发现事务100未提交
       MVCC->>UndoLog: 通过Undo Log找到事务100之前的版本
       MVCC->>T2: 返回快照读结果(balance=500)
   基于ReadView机制
   
       T2->>LockManager: 尝试修改同一数据
   UPDATE users SET balance=800 WHERE id=1
       LockManager->>T2: 等待锁释放
   (阻塞状态)
   
       alt 事务1提交
           T1->>LockManager: COMMIT
           T1->>DataPage: 将修改写入磁盘
           LockManager->>T2: 释放行级写锁
   唤醒等待的事务2
           T2->>LockManager: 获得行级写锁
           T2->>DataPage: 修改数据页
   (balance=800, trx_id=101)
           T2->>LockManager: COMMIT
       else 事务1回滚
           T1->>LockManager: ROLLBACK
           T1->>UndoLog: 恢复原始数据(balance=500)
           LockManager->>T2: 释放行级写锁
           T2->>LockManager: 获得行级写锁
           T2->>DataPage: 基于原始值修改
   (balance=800, trx_id=101)
       end
   

24. MySQL 中的日志类型有哪些？binlog、redo log 和 undo log 的作用和区别是什么？

1. binlog 二进制日志: binlog是MySQL的二进制文件，用于记录所有的增删改操作 (包括表结构和数据的操作)。binlog是在事务提交后生成的，可以用于恢复数据库和备份数据库。(一般MySQL都有主库+从库两个数据库，防止单台故障，binlog就是为了同步主库和从库的)
2. redo log 重做日志: redo log使用来恢复数据的，保证数据的一致性和持久性。当MySQL发生修改是，redolog会将这些操作记录下来，并写入磁盘。当数据库宕机时，可以通过重放redo log恢复数据
3. undo log 回滚日志: undo log是用于回滚操作的。当MySQL开始事务的时候，undo log会记录这些操作的反向操作。当需要回滚的时候，通过执行相反的操作，就可以回滚事务。

【区别】

【undo log 结构图】

【Redo Log + Undo log 结构图】

25. MySQL隔离级别有哪些?

MySQL的隔离级别包括四类: 读未提交 RU、读已提交 RC、可重复读 RR、串行化

• 读未提交 RU : 顾名思义，如果有两个事务，事务A会在执行过程中读取,事务B还没有提交的修改数据。会出现脏读的情况， 就是读取了其他事务还没提交的数据。
• 读已提交 RC: 顾名思义，如果有两个事务，事务A会在执行过程中，读取事务B提交之后的数据，若未提交不会读取。但是会出现不可重复读的现象，过程如下。
  1. 事务A第一次select name where id = 1读取的数据为 小邓
  2. 事务B update user set name = '小刘' 并提交
  3. 事务A再次select name where id = 1读取的数据为 小刘 ，结果发生了变化 (你**的究竟是谁)
• 可重复读 RR: 为了解决不可重复读的现象，RR 隔离级别下，事务A会只用第一次 select (快照读)的时候，生成read view。如果事务B修改同一行的数据并提交。事务A第二次select (快照读)的时候，会用第一次的查询结果。但是，它会出现幻读的现象，过程如下。
  • 事务A第一次select count(*) 读取的数据为 10， 采用的快照读
  • 事务B insert xxx 新增了一条数据并提交
  • 事务A第二次用select count(*) for update，采用当前读。读出来的数据为11条
• 串行化： 可以理解成把RR隔离级别下，所有的快照读都替换成当前读。当前读的状态下，其他事务不能修改正在读取的数据，实现了读的一致性，避免了幻读。 但是并发性能很低。

【不同隔离级别的特性】

【RR 隔离级别幻读的解决方案】

只采用下面的某一种方式进行读，就不会出现幻读

1. 快照读 (MVCC机制)：利用MVCC多版本控制，不会出现幻读。
2. 当前读 (加锁查询)：通过临键锁Next-key Lock (记录锁 + 间隙锁)，避免其他事务修改数据，防止幻读。其实就是串行化隔离级别。

26. 数据库的脏读、不可重复读和幻读分别是什么？

整活版解释参见ID为 小明 的天才选手

27. MySQL 默认的事务隔离级别是什么？为什么选择这个级别？

MySQL默认的事务隔离级别是可重复读 RR 。

【为什么选 RR 隔离级别】

因为MySQL当中一般是有主库 + 从库两个数据库，为了避免一个库突然g了，数据库就全g了。主库和从库之间是采用binlog进行备份的，如果binlog是statement格式，在RU和RC的隔离级别下，主库和从库就会出现数据不一致的问题。

【binlog 格式】

【RU 和 RC 导致主从不一致】

此时，binlog里面记录的如下，执行顺序显然和原始的不一样，从库里面age = 5 这条数据肯定没了

1. insert into user value(10,...)
2. delete from user where age < 10

【为什么 RR 不会出现主从数据不一致】

因为 RR 隔离级别不仅会对更新的数据行添加行级的记录锁， 还会添加间隙锁和临键锁。如果有这两个锁的话，在事务B执行insert的时候，会被阻塞的。

【为什么大厂一般用 RC 】

先来对比一下RC 和 RR 隔离级别的区别

用RC的原因有两个:

1. 提高并发：因为相较于RR，RC 的并发率更高
2. 减少死锁：因为RR 当中的间隙锁和临键锁会使得锁的粒度变大，死锁的几率会变大。

【 RC 如何解决不可重复读问题】

如果只是单纯的不可重复读，其实还好，只要后面修改数据不基于这个值。所以，在修改核心表的时候，增加乐观锁的标记。更新的时候带上乐观锁进行版本判断之后，再更新。

28. MySQL 事务的二阶段提交是什么？/ MySQL里面的 Redolog 和 BinLog 怎么保持一致?

首先，事务的二阶段提交就是为了让MySQL中的 binlog 和 redo log 保持一致。

【为什么需要两阶段提交】

如果没有两阶段提交，可能会导致binlog和redo log不一致，可以参考下面两种情况

• **情况一：**先写完 redo log，再写binlog：如果写完redo log后，MySQL突然宕机了，binlog还没写入数据。此时，MySQL重启后，根据 redo log 恢复事务的修改，但是binlog没有本次事务提交的数据。所以通过binlog恢复的时候，这次事务的修改就丢了。

• **情况二：**先写完binlog，再写redo log：如果写完binlog之后，突然MySQL宕机了，redo log还没写入数据。重启后因为redo log里面没有记录，所以没法儿恢复事务的修改。但是binlog记录了本次事务提交的数据，后续用binlog恢复数据的时候，就导致和原库不一样了。(binlog是用来给从库复制的)

为了避免上面的两种情况发生，就把单个事务的提交拆分为2个阶段：准备阶段(prepare) + 提交阶段(commit)

【事务的二阶段提交过程】

1. prepare 准备阶段: InnoDB 将内部事务id XID 写入redo log，并将其标记为 prepare 状态。然后将redo log 持久化到磁盘或者写入redo log buffer，具体取决于 innodb_flush_log_at_trx_commit 参数
2. commit 提交阶段：将内部事务id XID写入到binlog，调用write()函数写入到文件系统的Page Cache。当binlog写入磁盘成功就认为事务就是执行完成了，就算redo log 还是prepare状态也没事儿。

如何解决的上面提到的两种情况呢？

• 情况一： 写完 redo log 之后，还处于prepare状态，还没写入binlog， 突然宕机了。
  1. MySQL重启后，会顺序扫描redo log文件，如果还处于prepare状态，就查看redo log当中的内部事务IDXID在binlog中是否存在
  2. 如果binlog不存在内部事务idXID，表明redolog已经刷盘(写入磁盘了)，但是binlog还没有刷盘，直接回滚事务，就当这条事务执行失败
• 情况二： 写完bin log之后，还处于prepare状态，还没commit， 突然宕机了。
  1. MySQL重启后，会顺序扫描redo log文件，如果还处于prepare状态，就查看redo log当中的内部事务IDXID在binlog中是否存在。 (一般都是先扫描redolog，再看binlog)
  2. 如果binlog里面有当前内部事务idXID，说明redolog和binlog都刷盘了，直接提交事务就好了。

【两阶段提交有没有什么问题】

两阶段提交确实会导致磁盘I/O次数增高和锁的竞争变得激烈

1. 磁盘I/O的次数增高: 每次事务提交都会进行两次写入磁盘 fsync，一次redolog刷盘，一次binlog刷盘
2. 锁竞争激烈：为了保证单事务的两个日志内容一致，所以需要在提交流程上，添加锁保证两阶段的原子性。确保日志里面的顺序，不受多事务提交的影响。

【优化二阶段提交：组提交】

为了减少二阶段提交的I/O次数和锁的竞争，MySQL新增了组提交机制，可以让多个事务提交时合并多个binlog，只进行一次刷盘操作。组提交版本的二阶段提交只有commit提交部分有些变化：

1. flush阶段：多个事务按照顺序将binlog从Cache写入到文件 (不刷盘)， 为了支撑redo log组提交
2. sync同步阶段：对binlog进行写入磁盘fsync操作，多个事务的binlog一并写入磁盘，为了支撑binlog的组提交
3. commit阶段: 所有事务按照顺序进行commit提交操作

每个阶段都有队列维护，锁针对队列进行保护，减小锁的范围的同时，提高效率。

【binlog刷盘时间】

1. 事务执行过程中，线写日志到binlog cache (Server层的cache)
2. 事务提交的时候，从binlog cache 写入到 binlog文件。单个事务的binlog不能拆开，只能一次性写入。

​ MySQL分配了一片内存用于缓冲binlog ，就是binlog cache。可以用binlog_cache_size修改它的大小。

29. 什么是 Write-Ahead Logging (WAL) 技术？它的优点是什么？MySQL 中是否用到了 WAL？

WAL 是用来确保在修改真正的数据之前，先将修改记录写入日志的技术。为了当系统宕机的时候，通过日志也可以恢复数据，MySQL的redo log就是依靠的 WAL技术。它的核心就是, 先写日志，再写数据

MySQL事务从开启到提交的过程，大致如下：

开启事务 -> 查询数据到内存 -> 记录undo log -> 记录redo log(prepare阶段) -> 更新内存 -> 记录binlog -> 记录redo log (commit之后)

graph LR
    subgraph "传统写入方式（无WAL）的问题"
        A1[用户提交事务] --> B1[直接修改数据文件]
        B1 --> C1{数据文件写入完成?}
        C1 -- 写入中断电 --> D1[数据文件损坏
原始数据丢失
新数据不完整]
        C1 -- 写入成功 --> E1[数据安全]
        D1 --> F1[❌ 数据无法恢复]
    end
    
    subgraph "WAL策略的优势"
        A2[用户提交事务] --> B2[1. 先写Redo Log
记录：在页面X的偏移Y
将值从A改为B]
        B2 --> C2[2. 强制刷新Redo Log到磁盘
fsync系统调用]
        C2 --> D2[3. 返回用户事务成功]
        D2 --> E2[4. 后台异步刷新数据页]
        
        subgraph "崩溃恢复场景"
            F2[系统崩溃重启] --> G2[读取Redo Log]
            G2 --> H2[重放所有已提交事务的修改]
            H2 --> I2[✅ 数据完整恢复]
        end
        
        E2 --> J2{系统崩溃?}
        J2 -- 正常运行 --> K2[数据最终写入数据文件]
        J2 -- 崩溃 --> F2
    end
    
    subgraph "WAL性能优势"
        L1[顺序写入Redo Log
高性能] --> M1[随机写入数据文件
可异步执行]
        N1[小尺寸日志记录
快速I/O] --> O1[大尺寸数据页面
批量写入]
    end
    
    style D1 fill:#ffcdd2
    style F1 fill:#ffcdd2
    style I2 fill:#e8f5e8
    style L1 fill:#e3f2fd
    style N1 fill:#e3f2fd

30. MySQL 中如果发生死锁应该如何解决？

【如何处理MySQL死锁】

1. 设置MySQL死锁自动检测机制

   MySQL自带死锁检测机制innodb_deadlock_detect，开启即可。如果检查到死锁的发生，数据库会自动回滚一个持有资源较少的事务，然后另一个事务就可以执行了。

   -- 查看主动死锁检测是否开启
   show variable like '%innodb_deadlock_detect%'
   -- 开启主动死锁检测 (默认为ON)
   set global innodb_deadlock_detect='ON'
   

2. 设置锁等待超时参数

   可以设置获取锁的等待时间(默认为50s)，如果超过了这个时间，就会主动释放锁，让事务回滚

   -- 事务等待锁的超时时间 (默认为50s)
   show variable like '%innodb_lock_wait_timeout%'
   

3. KILL死锁事务

   如果MySQL已经上线了，且没有设置那些检测，可以直接把死锁的事务kill掉。kill之前，需要查看一下执行的事务和表信息，用show engine innodb status

   -- 查看死锁日志
   -- 查看正在执行的事务, 和相关的表信息
   SHOW ENGINE INNODB STATUS
   -- 通过线程ID, 手动KILL死锁事务
   kill 线程ID
   

【如何避免死锁的发生】

1. 避免大事务: 大事务占用的时间比较长，容易导致死锁发生。可以把大事务拆解成多个小事务，就可以降低死锁的发生概率。
2. 更改数据库的隔离级别：MySQL的默认隔离级别是RR，它包含间隙锁和临键锁。如果改成RC，可以减少死锁的概率。
3. 合理加索引，减少加锁范围：命中索引会对该行加上行锁，没有命中则会对整张表加上表锁。表锁的冲突概率比较大，容易导致死锁。

31. MySQL 中如何解决深度分页的问题？

1. 深度分页问题定义：深度分页是指当用户需要查询很久以前的数据，比如早年某个范围的订单。 SQL语句当中的 limit 偏移量变得非常大，MySQL性能直线下降的现象。

2. 为什么会性能下降: 因为MySQL会选择全表扫描，而不用索引扫描，导致效率低下。当 limit 偏移量偏大的时候，查询流程如下：

   1. 扫描偏移量之前的1000000行，丢弃不符合条件的结果
   2. 每一次查询都需要用 age 列查到的主键值去回表，效率很低。(MySQL优化器就选择了，全表扫描 + 文件排序)
   3. 返回符合条件的最终记录

   select * from user  where sex = '女' order by age  limit 1000000, 10 
   

【如何解决深度分析带来的性能问题】

1. 记录上一次的最大ID，修改为范围查询 (如果能够保证 id 连续递增)

   查询的过程中，会走主键索引，加快查询速度。但是高并发的情况下，可能出现数据重复或者遗漏的情况。

   # 可以通过记录上次查询结果的最后一条记录进行下一页的查询
   SELECT * FROM user WHERE id > 1000000 LIMIT 10;
   

2. 子查询

   通过子查询来获取 id 的起始值，把 limit 2000000 的条件转移到子查询。 查询过程如下：

   1. 子查询语句利用id的主键索引快速找到这条记录，然后定位到 1000001 这条记录的主键
   2. 主查询语句将子查询返回的起始 ID 作为过滤条件，然后使用查询条件过滤掉前面的数据

   可以减少全表扫描，提高性能。但是，子查询会生成临时表，复杂场景会导致性能下降。

   SELECT * FROM user WHERE id >= (
       SELECT id FROM user order by id limit 1000000,1
     ) LIMIT 10;
   

3. 延迟关联

   和子查询类似，将limit 操作转移到主键索引上，让其减少回表次数来优化查询 (只查询id不用回表)。然后将子查询中的结果合并到主查询当中，避免创建临时表。整体性能比子查询好。查询过程如下:

   1. 子查询语句利用 id 的主键索引来快速找到符合条件的前10条记录的id
   2. 通过inner join 内连接将id 和 主表进行关联，获取完整记录

    select user.* from user t1
    inner join
    (SELECT id FROM user order by id limit 1000000, 10) t2
    on t1.id = t2.id
   

4. 覆盖索引：

   覆盖索引包含所有需要查询的字段(都是索引的，可以避免回表操作

   -- 覆盖索引查询
   SELECT id, name FROM user by id limit 1000000, 10
   

【如果出现表分页怎么办】

假如出现表分页，比如现在有表1和表2。表1中按score字段排序为100,90,80，表2中按score字段排序为95,85,75。然后适用select score from student_info limit 1, 2 查询出来的是 90 (表1) 和 85 (表2)的合并结果。

解决方案：将分页条件改写为limit 0, 3，取出所有前两页数据，再结合排序条件计算出正确的数据。如果遇到表分页的情况，必须从offset = 0的地方开始查询，避免漏掉数据。

32. 什么是 MySQL 的主从同步机制？它是如何实现的？

1. 主从同步机制: 将主数据库上的数据同步到多个从数据库中的技术
2. 为什么会有主从同步?: 因为如果MySQL只有一个数据库，当数据库文件损坏了，所有的数据都没了。为了防止这种单台故障，就有了主从数据库。主从数据库之间为了保持数据一致，就有了主从同步。

【主从同步的流程】

1. 从服务器创建线程: 从服务器开启主从复制之后，创建I/O线程和SQL线程
2. 从服器和住服务器建立连接：从服务器的I/O线程和主服务器建立连接，主服务器的binlog dump 线程和从服务器进行交互
3. 从服务器告知同步位置：从服务器的I/O线程会告知住服务器的 dump 线程从哪里开始接受 binlog。
4. **主服务器更新binlog：**主服务器把所有的更新记录从Page Cache 写入binlog 文件 (有三种格式：statement、row、mixed)
5. dump线程控制binlog传输： 主服务器的dump线程检测到binlog变化，从指定位置读取。从服务器的I/O线程开始拉取binlog 文件，采用拉取模式有利于从库管理同步进度和处理延迟
6. 中继存储到relay log： 从服务器的I/O线程将接收到的来自binlog中的内容，存储到relay log
7. 重放relay log，写入数据：从服务器的SQL线程读取relay log 内容，解析成具体操作之后写入到对应的表中

【主从同步的三种方式】

下面图片就是半同步复制/同步复制的过程，半同步复制可以设置检查从库的个数

【从数据库的并行复制】

从数据库默认是按照顺序逐条执行binlog的日志指令(也就是重放relay log)，但是串行执行可能导致从库的复制数据赶不上主库，所以就出现了下面的几种并行复制模式

33. 如何处理 MySQL 的主从同步延迟？

首先，MySQL的主从同步是一定存在延迟的。主从同步延迟是指主库更新完成之后，从库还没来得及更新，导致主从数据不一致。这种延迟对一些实时数据需求高的业务场景(比如金融系统)会造成影响。

【为什么有主从同步延迟】

从整体上看，有下面两个原因：

1. relay log赶不上binlog: 从库接受binlog的速度跟不上主库写入binlog的速度，从库的redo log就会比主库的binlog滞后
2. SQL执行赶不上relay log: 从库SQL线程执行relay log的速度比不上I/O线程接受binlog的速度，导致从库滞后relay log

导致上面两个情况发送的原因可能是：

1. 从库性能不足：CPU、内存、磁盘I/O比主库差一些，同步速度慢
2. 从库读请求多：要分配一部分资源去满足读请求，影响同步的效率
3. 主库提交太多大事务：从库去同步一个大事务需要较长的时间
4. 从库数量过多：主库推送binlog开销大，导致延迟
5. 网络延迟：主库和从库之间的网络延迟比较大，导致同步速度受限制
6. 复制模式：默认采用异步复制模式，主库不等待从库完成复制，肯定有延迟

【避免延迟的方法】

1. 强行把写入后的读请求交给主库处理 （不推荐）: 把写入后的读请求给主库处理，可以避免主从延迟，但是主库承受的压力也会增大

2. 用半同步复制：半同步复制可以保证至少有一个从库复制完成了

   SET GLOBAL rpl_semi_sync_master_enabled = 1;
   SET GLOBAL rpl_semi_sync_slave_enabled = 1;
   

3. 优化主从结构

   • 提升从库性能：配好一点的CPU、内存、磁盘I/O
   • 减少从库数量：减少主库的同步开销
   • 拆分读流量：把读请求负载均衡到多个从库上

4. sleep方案：假设主从库的延迟为1s，可以每次执行一个 select sleep(1)， 保证拿到最新的数据。

34. MySQL中的长事务可能会导致哪些问题？

1. 长时间的锁竞争，阻塞资源：长事务持有的锁时间比较长，容易导致其他事务再获取相同锁的时候，发送阻塞，增加系统的等待时间和降低并发性能。业务线程会因为长时间的数据库请求而被阻塞，部分业务的阻塞会影响到其他的业务，导致产生雪崩。最终可能会让服务全面崩盘，导致严重的线上事故。
2. 死锁风险：长事务更容易产生死锁，因为可能存在多个事务在互相等待对方释放锁，导致系统死锁。
3. 主从延迟：长事务容易导致主从延迟，因为长事务需要主库花更长的时间执行，然后通过binlog传给从库。从库读取relay log的时候，重发操作又需要一长段时间，可能导致一大段时间数据是不同步的。
4. 回滚导致时间浪费：如果事务执行了很长一段时间，突然执行出错，需要事务回滚，之前的执行都浪费了，耗费时间。
5. 版本链过长：假如事务A对某条数据执行了10000次修改操作，在没有提交之前，事务B进行select 操作，会需要耗费很长的时间。

【长事务的SQL如何处理】

1. 拆分长事务SQL: 把单条SQL拆分为多条短事务SQL

   # 假如需要删除2021年的数据(4.8亿条)，共5亿条数据
   delete from yes where create_date > " 2020-12-31" and create_date < "2022-01-01";
   # 按照日期进行拆分成多条事务
   delete from yes where create_date > " 2020-12-31" and create_date < "2021-02-01";
   ....
   delete from yes where create_date > " 2021-11-31" and create_date < "2022-01-01";
   

2. 反向操作减轻事务时间：把需要旧表删除的数据转成新增到新的表，然后用新表替换旧表就可以了。