基于极客时间中的MySQL实战45讲以及小林coding,可以查看极客时间文档,相关github仓库it-ebooks-0/geektime-books: :books: 极客时间电子书和zkep/my-geektime: 👏 极客时间下载器 & 在线文档

基础

基础架构

目前mysql版本基本大于8.0,而经典老版本5.7一些功能在新版本中已经移除(比如查询缓存).

MySQL 的架构共分为两层：Server 层和存储引擎层，

Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。
存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

连接器

连接器用于客户端建立连接,获取权限,维持和管理连接.

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数wait_timeout控制的，默认值是8小时。

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

建立连接的过程通常是比较复杂的，所以建议在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

但是全部使用长连接后，你可能会发现，有些时候MySQL占用内存涨得特别快，这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了。

怎么解决这个问题呢？你可以考虑以下两种方案。

定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
如果你用的是MySQL 5.7或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

连接建立完成后,就可以执行语句了。执行逻辑就会来到第二步：查询缓存。

MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端.

对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据，还没被使用的时候，刚好这个表有更新操作，查询缓冲就被清空了，相当于缓存了个寂寞。

需要注意的是，MySQL 8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了。

分析器

进行词法分析,语法、语义解析

如果输入的 SQL 语句语法不对，就会在分析器这个阶段报错

经过分析器后，接着就要进入执行 SQL 查询语句的流程了，每条SELECT 查询语句流程主要可以分为下面这三个阶段：

prepare 阶段，也就是预处理阶段；
optimize 阶段，也就是优化阶段；
execute 阶段，也就是执行阶段；

在优化器之前会有个有预处理阶段,会检查表不存在或者字段不存在的情况

检查 SQL 查询语句中的表或者字段是否存在；
将 select * 中的 * 符号，扩展为表上的所有列；

优化器

优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。

执行器

执行器与存储引擎交互得到多条记录.

以下面查询语句为例，看看执行器是怎么工作的。

1	select * from product where id = 1;

这条查询语句的查询条件用到了主键索引，而且是等值查询，同时主键 id 是唯一，不会有 id 相同的记录，所以优化器决定选用访问类型为 const 进行查询，也就是使用主键索引查询一条记录，那么执行器与存储引擎的执行流程是这样的：

执行器第一次查询，会调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为 InnoDB 引擎索引查询的接口，把条件 id = 1 交给存储引擎，让存储引擎定位符合条件的第一条记录。
存储引擎通过主键索引的 B+ 树结构定位到 id = 1的第一条记录，如果记录是不存在的，就会向执行器上报记录找不到的错误，然后查询结束。如果记录是存在的，就会将记录返回给执行器；
执行器从存储引擎读到记录后，接着判断记录是否符合查询条件，如果符合则发送给客户端，如果不符合则跳过该记录。
执行器查询的过程是一个 while 循环，所以还会再查一次，但是这次因为不是第一次查询了，所以会调用 read_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为一个永远返回 - 1 的函数，所以当调用该函数的时候，执行器就退出循环，也就是结束查询了。

索引下推能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了。

执行查询语句流程

执行一条 SQL 查询语句，期间发生了什么？

连接器：建立连接，管理连接、校验用户身份；
查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
执行 SQL：执行 SQL 共有三个阶段：
- 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
- 优化阶段：基于查询成本的考虑，选择查询成本最小的执行计划；
- 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

一行记录是如何存储的

MySQL 存储的行为是由存储引擎实现的，MySQL 支持多种存储引擎，不同的存储引擎保存的文件自然也不同。

一个数据库database包含哪些文件?假设包含一个表t_order

共有三个文件，这三个文件分别代表着：

db.opt，用来存储当前数据库的默认字符集和字符校验规则。
t_order.frm ，t_order 的表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。
t_order.ibd，t_order 的表数据会保存在这个文件。表数据既可以存在共享表空间文件（文件名：ibdata1）里，也可以存放在独占表空间文件（文件名：表名字.ibd）。这个行为是由参数 innodb_file_per_table 控制的，若设置了参数 innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从 MySQL 5.6.6 版本开始，它的默认值就是 1 了，因此从这个版本之后， MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件。

一张数据库表的数据是保存在” 表名字.ibd “的文件里的，这个文件也称为独占表空间文件

表空间由段（segment）、区（extent）、页（page）、行（row）组成，InnoDB存储引擎的逻辑存储结构

数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构.

记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

因此，InnoDB 的数据是按「页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间。

页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

页的类型有很多，常见的有数据页、undo 日志页、溢出页等等。数据表中的行记录是用「数据页」来管理的

B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，随机 I/O 是非常慢的。

解决这个问题也很简单，就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。

那具体怎么解决呢？

在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。

表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。

索引段：存放 B + 树的非叶子节点的区的集合；
数据段：存放 B + 树的叶子节点的区的集合；
回滚段：存放的是回滚数据的区的集合，之前讲事务隔离时候就介绍到了 MVCC 利用了回滚段实现了多版本查询数据。

行格式

InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。

Redundant 是很古老的行格式了， MySQL 5.0 版本之前用的行格式，现在基本没人用了。
由于 Redundant 不是一种紧凑的行格式，所以 MySQL 5.0 之后引入了 Compact 行记录存储方式，Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact。
Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。

一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个部分。

记录的额外信息包含 3 个部分：变长字段长度列表、NULL 值列表、记录头信息。

varchar(n) 和 char(n) 的区别是什么，相信大家都非常清楚，char 是定长的，varchar 是变长的，变长字段实际存储的数据的长度（大小）不固定的。

所以，在存储数据的时候，也要把数据占用的大小存起来，存到「变长字段长度列表」里面，读取数据的时候才能根据这个「变长字段长度列表」去读取对应长度的数据。其他 TEXT、BLOB 等变长字段也是这么实现的。

记录的额外信息

变长字段列表

变长字段长度列表存储变长字段的真实数据占用的字节数会按照列的顺序逆序存放

这部分专门用于存储变长字段（如 VARCHAR, VARBINARY, TEXT, BLOB）的实际长度。

顺序： 这些长度值是按照字段在表中定义的逆序存储的。例如，如果表有 c1 VARCHAR(10), c2 VARCHAR(20)，那么长度列表会先存储 c2 的长度，再存储 c1 的长度。
大小： 每个长度值根据字段实际长度的不同，可能占用 1 字节或 2 字节。
变长字段字节数列表不是必须的。

当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了，因为没必要，不如去掉以节省空间。

所以「变长字段长度列表」只出现在数据表有变长字段的时候。

逆序存储原因

主要是因为「记录头信息」中指向下一个记录的指针，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。

「变长字段长度列表」中的信息之所以要逆序存放，是因为这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。

同样的道理， NULL 值列表的信息也需要逆序存放。

空值列表

这部分用于标记哪些列的值为 NULL。

按位表示： 它是一个位图，每个位对应一个允许为 NULL 的列。如果某个位是 1，则表示对应的列值为 NULL；如果是 0，则表示不为 NULL。
节省空间： 这样可以节省存储 NULL 值的实际空间。如果列不允许为 NULL（NOT NULL），则不会在这个位图中占用空间。
顺序： 同样是按照列在表中定义的逆序存储，但只包含允许为 NULL 的列

表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。

如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。

二进制位的值为1时，代表该列的值为NULL。
二进制位的值为0时，代表该列的值不为NULL。

另外，NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。

NULL 值列表也不是必须的。

当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。

所以在设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以至少节省 1 字节的空间（NULL 值列表至少占用 1 字节空间）。

记录头信息

比较重要的：

delete_mask ：标识此条数据是否被删除。从这里可以知道，我们执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。
next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。在前面我也提到了，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录

记录的真实数据

这是存储所有列的实际值的地方。

定长字段： 定长字段（如 INT, CHAR(N)）会直接存储其固定长度的值。
变长字段： 变长字段则存储其真实的数据内容。
溢出页存储 (Off-page Storage)： 对于 TEXT 和 BLOB 这种非常大的变长字段，如果其数据超过了数据页的存储限制（大约 8KB），InnoDB 会将其部分或全部数据存储到独立的溢出页（Overflow Pages）中。在实际数据区，只存储指向这些溢出页的 20 字节指针。这就是 DYNAMIC 和 COMPRESSED 行格式的主要优化之处，它们对于溢出数据的存储方式更灵活。COMPACT 和 REDUNDANT 会尽可能将数据存储在行内，只有在无法存储时才溢出。

还有三个隐藏字段，分别为：row_id、trx_id、roll_pointer.

row_id 如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节。
trx_id事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。
roll_pointer记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

varchar(n)含义以及n最大取值

VARCHAR(n) 的存储空间由以下两部分组成：

实际数据长度： 存储字符串的实际字节数。
长度前缀： 用于记录字符串实际长度的字节数。
- 如果字符串的实际长度小于或等于 255 字节，需要 1 个字节来存储长度。
- 如果字符串的实际长度大于 255 字节，但小于或等于 65535 字节，需要 2 个字节来存储长度。
  
  NULL 标识，如果不允许为NULL，这部分不需要
  
  n 代表的是该 VARCHAR 列能够存储的最大字符数。
例如，VARCHAR(255) 表示这个列最多可以存储 255 个字符。

MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。

也就是说，一行记录除了 TEXT、BLOBs 类型的列，限制最大为 65535 字节，注意是一行的总长度，不是一列。varchar(n) 字段类型的 n 代表的是最多存储的字符数量,因此,要算 varchar(n) 最大能允许存储的字节数，还要看数据库表的字符集，因为字符集代表着，1个字符要占用多少字节，比如 ascii 字符集， 1 个字符占用 1 字节，那么 varchar(100) 意味着最大能允许存储 100 字节的数据。

一行数据的最大字节数 65535，其实是包含「变长字段长度列表」和「NULL 值列表」所占用的字节数的。所以，我们在算 varchar(n) 中 n 最大值时，需要减去 storage overhead 占用的字节数。

因为我们存储字段类型为 varchar(n) 的数据时，其实分成了三个部分来存储：

真实数据
真实数据占用的字节数
NULL 标识，如果不允许为NULL，这部分不需要

varchar(n) 中 n 最大值时，需要减去 变长字段长度列表和 NULL 值列表所占用的字节数的。所以，在数据库表只有一个 varchar(n) 字段且字符集是 ascii 的情况下，varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532。

行溢出问题

一个页默认16KB,也就16384字节,如果包含的一条记录行总大小超过了16KB会怎样.

这时一个页可能就存不了一条记录。这个时候就会发生行溢出，多的数据就会存到另外的「溢出页」中。

如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到溢出页中。在一般情况下，InnoDB 的数据都是存放在数据页中。但是当发生行溢出时，溢出的数据会存放到溢出页中。

在compact行格式中,当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在“溢出页”中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。

Compressed 和 Dynamic 这两个行格式和 Compact 非常类似，主要的区别在于处理行溢出数据时有些区别。这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中

执行更新语句流程

查询语句的那一套流程，更新语句也是同样会走一遍：

客户端先通过连接器建立连接，连接器自会判断用户身份；
因为这是一条 update 语句，所以不需要经过查询缓存，但是表上有更新语句，是会把整个表的查询缓存清空的，所以说查询缓存很鸡肋，在 MySQL 8.0 就被移除这个功能了；
解析器会通过词法分析识别出关键字 update，表名等等，构建出语法树，接着还会做语法分析，判断输入的语句是否符合 MySQL 语法；
预处理器会判断表和字段是否存在；
优化器确定执行计划，因为 where 条件中的 id 是主键索引，所以决定要使用 id 这个索引；
执行器负责具体执行，找到这一行，然后更新。

不过，更新语句的流程会涉及到 undo log（回滚日志）、redo log（重做日志）、binlog （归档日志）这三种日志：

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

具体更新一条记录 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下:

执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：
- 如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
- 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。
执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：
- 如果一样的话就不进行后续更新流程；
- 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；
开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log。
InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术，MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上。
至此，一条记录更新完了。
在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。
事务提交（为了方便说明，这里不说组提交的过程，只说两阶段提交）：
- prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
- commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）；
至此，一条更新语句执行完成。

日志模块

Undo log

Undo Log 是一种逻辑日志，它记录了数据在被修改前的样子。 简单来说，它记录的是“如何撤销一个操作”的信息。当事务对数据进行修改时，InnoDB 不会直接覆盖旧数据，而是会把旧数据的版本写入到 Undo Log 中。

undo log 是一种用于撤销回退的日志。在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚。

当一个事务执行过程中遇到错误，或者用户显式地发出 ROLLBACK 命令时，InnoDB 会利用 Undo Log 中记录的信息，将所有对数据库的修改撤销，使数据回到事务开始之前的状态。

这确保了事务的“要么全部成功，要么全部失败”的特性。

它记录了回滚（Undo）操作所需的信息,每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里。如果你执行了一个 INSERT、UPDATE 或 DELETE 操作：

对于 INSERT 操作：Undo Log 记录的是该行数据的 主键信息，当需要回滚时，通过主键将新插入的行删除。
对于 DELETE 操作：Undo Log 记录的是被删除行的 完整数据，当需要回滚时，通过这些数据将行重新插入。
对于 UPDATE 操作：Undo Log 记录的是被更新行的旧值（修改前的数据），当需要回滚时，通过这些旧值将数据恢复。

这些 Undo Log 存储在回滚段（Rollback Segment）中，位于共享表空间（System Tablespace）或单独的 undo tablespace 文件中。

在发生回滚时，就读取 undo log 里的数据，然后做原先相反操作。比如当 delete 一条记录时，undo log 中会把记录中的内容都记下来，然后执行回滚操作的时候，就读取 undo log 里的数据，然后进行 insert 操作。

针对 delete 操作和 update 操作会有一些特殊的处理：

delete操作实际上不会立即直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。
update分为两种情况：update的列是否是主键列。
- 如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。
- 如果是主键列，update分两部执行：先删除该行，再插入一行目标行。

一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：

通过 trx_id 可以知道该记录是被哪个事务修改的；
通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链

对于「读提交」和「可重复读」隔离级别的事务来说，它们的快照读（普通 select 语句）是通过 Read View + undo log 来实现的，它们的区别在于创建 Read View 的时机不同：

「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。

这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列（trx_id 和 roll_pointer）」的比对，如果不满足可见行，就会顺着 undo log 版本链里找到满足其可见性的记录，从而控制并发事务访问同一个记录时的行为，这就叫 MVCC(多版本并发控制).

undo log 两大作用：

实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

undo log 是如何刷盘（持久化到磁盘）的？
undo log 和数据页的刷盘策略是一样的，都需要通过 redo log 保证持久化。
buffer pool 中有 undo 页，对 undo 页的修改也都会记录到 redo log。redo log 会每秒刷盘，提交事务时也会刷盘，数据页和 undo 页都是靠这个机制保证持久化的

1.什么是 Undo Log？它的主要作用是什么？

回答要点： 逻辑日志，记录数据修改前的状态。两大作用是实现事务的原子性（回滚） 和 隔离性（MVCC）。

Undo Log 是如何实现事务回滚的？请举例说明。

回答要点：
- Insert 回滚： 记录主键，回滚时删除新插入的行。
- Delete 回滚： 记录被删除的完整行数据，回滚时重新插入。
- Update 回滚： 记录更新前的旧值，回滚时用旧值覆盖新值。

Undo Log 和 Redo Log 有什么区别和联系？

回答要点：
- Undo Log： 逻辑日志，记录修改前的数据（旧版本），用于回滚和MVCC。保证原子性和隔离性。
- Redo Log： 物理日志，记录修改后的数据（新版本）以及操作类型，用于崩溃恢复（Crash Recovery）和持久性。保证持久性。
- 联系： 它们都是事务日志，都是为了保证事务的 ACID 特性。Redo Log 保证已提交事务的持久性（即使系统崩溃也能恢复），Undo Log 保证未提交事务的原子性（回滚到事务开始前）。

Undo Log 是如何实现 MVCC 的？（核心问题）

回答要点：
- 隐藏列： 每行数据都有两个隐藏列：DB_TRX_ID（最近一次修改该行的事务 ID）和 DB_ROLL_PTR（指向该行在 Undo Log 中上一个版本的指针）。
- Read View： 每个事务开始时，会生成一个 Read View（视图），其中包含当前活跃的事务 ID 列表。
- 版本链： 当数据被修改时，旧版本的数据会记录在 Undo Log 中，并通过 DB_ROLL_PTR 形成一个版本链。
- 可见性判断： 事务查询数据时，会根据 DB_TRX_ID 和 Read View 来判断当前版本是否可见。如果不可见，就沿着 DB_ROLL_PTR 链回溯到 Undo Log 中查找更早的版本，直到找到对当前事务可见的那个版本。
- 快照读： MVCC 主要支持快照读（Snapshot Read）。

Undo Log 会被清除吗？什么时候清除？

回答要点：
- 会清除。 Undo Log 并不是永久保存的。
- 当所有活跃事务都不再需要某个 Undo Log 版本时，这个 Undo Log 就会被标记为可清除。
- InnoDB 有一个后台线程会定期回收这些不再需要的 Undo Log 空间。
- 如果存在长时间运行的事务（例如一个很大的查询或一个未提交的事务），它可能会长时间持有 Read View，导致大量的 Undo Log 无法被及时清除，从而占用大量磁盘空间，甚至导致数据库性能问题（例如 history list length 过长）。

为什么长事务会导致数据库性能问题？

回答要点：
- Undo Log 无法回收： 长事务会长时间持有 Read View，导致它所开启之前的 Undo Log 记录都无法被清理，占用大量磁盘空间。
- 查询性能下降： 其他事务查询旧版本数据时，需要回溯更长的 Undo Log 链，增加 I/O 和 CPU 开销。
- 刷新脏页受阻： Undo Log 过多也会影响脏页的刷新，导致内存中的脏页无法及时写入磁盘。

Undo Log 的数据存储在 回滚段（Rollback Segment） 中。

文件类型：
- 在 MySQL 5.7 之前，回滚段位于共享表空间（ibdata1）中。
- 在 MySQL 5.7 及以后的版本，推荐将 Undo Log 独立出来，存储在单独的 Undo 表空间文件中，通常命名为 undo001, undo002 等。这个配置由 innodb_undo_tablespaces 参数控制。

关键点： * 默认情况下在 ibdata1 中。

推荐配置为独立文件，便于管理和回收空间。

Redo log

Redo Log 是一种物理日志，它记录了对数据页的修改。 简单来说，它记录的是“数据被修改成了什么样子”。当事务对数据进行修改时，InnoDB 会先将修改操作写入到 Redo Log 中，而不是直接写入数据文件.Redo Log 记录的是数据页的物理修改，例如“对页号 X 的偏移量 Y 处的数据从 A 变为 B”。它不关心业务逻辑，只关心数据块的字节变化。

为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。

后续，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术。

InnoDB 遵循 WAL (Write-Ahead Logging) 策略，即“先写日志，再写磁盘”。

当一个事务需要修改数据时，InnoDB 会先将这些修改操作（比如“将数据页 X 的偏移量 Y 处的值从 A 改为 B”）写入到 Redo Log Buffer (内存中的一块区域)。
Redo Log Buffer 中的数据会根据一定策略（比如事务提交、Buffer Pool 刷盘等）刷入到磁盘上的 Redo Log 文件 (通常是 ib_logfile0, ib_logfile1 等)。
只有当 Redo Log 记录成功写入到磁盘（或至少同步到操作系统的文件缓存）后，事务才会被确认为“提交成功”。
实际的数据页的修改（从 Buffer Pool 刷写到数据文件）可以稍后进行，即使系统在数据页刷盘前崩溃，也可以通过 Redo Log 来恢复。

被修改 Undo 页面，需要记录对应 redo log 吗？

需要。开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。

不过，在内存修改该 Undo 页面后，也是需要记录对应的 redo log，因为undo log也要实现持久性的保护。

写入 redo log 的方式使用了追加操作，所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。

磁盘的「顺序写」比「随机写」高效的多，因此 redo log 写入磁盘的开销更小。

针对「顺序写」为什么比「随机写」更快这个问题，可以比喻为你有一个本子，按照顺序一页一页写肯定比写一个字都要找到对应页写快得多。

可以说这是 WAL 技术的另外一个优点：MySQL 的写操作从磁盘的「随机写」变成了「顺序写」，提升语句的执行性能。这是因为 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上。

至此，针对为什么需要 redo log 这个问题我们有两个答案：

实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；
将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。

产生的 redo log 是直接写入磁盘的吗？

不是的。

实际上，执行一个事务的过程中，产生的 redo log 也不是直接写入磁盘的，因为这样会产生大量的 I/O 操作，而且磁盘的运行速度远慢于内存。

所以，redo log 也有自己的缓存—— redo log buffer，每当产生一条 redo log 时，会先写入到 redo log buffer.redo log buffer 默认大小 16 MB，可以通过 innodb_log_Buffer_size 参数动态的调整大小，增大它的大小可以让 MySQL 处理「大事务」是不必写入磁盘，进而提升写 IO 性能。

redo log刷盘时机

缓存在 redo log buffer 里的 redo log 还是在内存中，它什么时候刷新到磁盘？

主要有下面几个时机：

MySQL 正常关闭时；
当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘.这个策略可由 innodb_flush_log_at_trx_commit 参数控制

什么是 Redo Log？它的主要作用是什么？

回答要点： 物理日志，记录数据页的修改（新版本）。核心作用是实现事务的持久性，用于崩溃恢复。

Redo Log 和 Undo Log 有什么区别和联系？

回答要点：
- Redo Log： 物理日志，记录修改后的数据状态，用于崩溃恢复，保证持久性。
- Undo Log： 逻辑日志，记录修改前的数据状态，用于事务回滚和 MVCC，保证原子性和隔离性。
- 联系： 它们都是事务日志，都是为了保证事务的 ACID 特性。Redo Log 保证已提交事务的持久性，Undo Log 保证未提交事务的原子性。两者配合，构成了 InnoDB 事务处理的核心机制。

为什么 Redo Log 可以实现崩溃恢复？

回答要点： 基于 WAL (Write-Ahead Logging) 策略。事务提交时，只需确保 Redo Log 写入磁盘，而无需等待数据页真正写入磁盘。即使系统崩溃，重启时可以通过 Redo Log 重放那些已经提交但未刷盘的操作，确保数据不丢失。

Redo Log 是如何保证事务提交的“快速性”和“持久性”的？

回答要点：
- 快速性（缓冲）： 事务提交时，不需要立即将脏页从 Buffer Pool 刷到磁盘，而是先将操作记录到 Redo Log Buffer，然后刷到 Redo Log 文件。由于 Redo Log 是顺序写入的，效率很高。
- 持久性（同步）： 通过 innodb_flush_log_at_trx_commit 参数控制 Redo Log 刷盘策略，可以保证在事务提交时，Redo Log 记录已经同步到磁盘，从而确保持久性。
  - 1：每次事务提交时，Redo Log 都写入文件并同步到磁盘，最安全，但性能开销大。
  - 0：每秒将 Redo Log 写入文件并同步到磁盘，性能高，但可能丢失 1 秒的数据。
  - 2：每次事务提交时，Redo Log 写入文件但不立即同步到磁盘，由操作系统负责同步，性能和安全折中。

Redo Log 文件的大小和数量对性能有什么影响？

回答要点：
- 文件大小（innodb_log_file_size）： 越大，checkpoint 刷脏的频率越低，可以减少磁盘 I/O，提高写入性能。但恢复时间会变长。
- 文件数量（innodb_log_files_in_group）： 通常为 2 个或更多，形成一个环形缓冲区。数量通常不影响性能太多，主要受大小影响。
- 环形写入： Redo Log 是以循环覆盖的方式写入的，当写到最后一个文件末尾时，会回到第一个文件开头继续写。

什么是 Checkpoint？它与 Redo Log 有何关系？

回答要点：
- Checkpoint (检查点)： 是一个时间点或位置，表示在某个时间点之前，所有已记录在 Redo Log 中的数据页修改，都已经被成功写入到磁盘上的数据文件了。
- 关系： Checkpoint 的作用是缩短恢复时间和刷新脏页。在崩溃恢复时，InnoDB 只需从最近的 Checkpoint 开始重放 Redo Log，而不需要从 Redo Log 的最开始。Checkpoint 机制通过将脏页刷到磁盘，使得 Redo Log 中对应的那部分空间可以被重用。

Redo Log 是物理日志还是逻辑日志？为什么？

回答要点：
- 物理日志： Redo Log 记录的是数据页的物理修改，例如“对页号 X 的偏移量 Y 处的数据从 A 变为 B”。它不关心业务逻辑，只关心数据块的字节变化。
- 为什么是物理日志： 因为恢复时直接根据这些物理修改信息操作数据页，效率更高。而 Undo Log 记录的是逻辑操作（如 INSERT 了一行，DELETE 了一行），恢复时需要逆向执行逻辑操作。

存储位置： Redo Log 存储在 MySQL 数据目录下的两个或多个文件中。

文件类型： 这些文件通常以 ib_logfile 开头，例如 ib_logfile0, ib_logfile1。
工作方式： Redo Log 文件构成一个环形写入的日志组。当一个文件写满后，会切换到下一个文件，写到最后一个文件末尾后，再回到第一个文件开头继续写入，循环覆盖。

关键点： * 文件名固定，通常为 ib_logfileX。

以循环方式写入，所以文件数量和大小是固定的。

Bin log

undo log 和 redo log 这两个日志都是 Innodb 存储引擎生成的。MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写入 binlog 文件binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：
- STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；
- ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；
- MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；
redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；
binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志

存储位置： Binlog 存储在 MySQL 数据目录下。

文件类型：

索引文件：binlog.index，记录所有 Binlog 文件的列表。
数据文件：binlog.000001, binlog.000002 等，每个文件记录一段时期的 Binlog 事件。

工作方式： 当 Binlog 文件达到一定大小（由 max_binlog_size 控制）或者重启 MySQL 服务时，会进行日志切换（Rotate），生成一个新的 Binlog 文件。

Bin Log 主要用于以下两个核心功能：

数据库复制 (Replication)

这是 Bin Log 最主要的作用。

原理： 在主从复制架构中，主服务器会将 Bin Log 的内容实时同步给所有从服务器。
作用： 从服务器接收到 Bin Log 的事件后，会按顺序重放 (Replay) 这些事件，从而保证主从数据库的数据最终是一致的。这使得主从架构成为可能，为高可用和读写分离提供了基础。

数据恢复 (Point-in-Time Recovery)

原理： 当数据库发生意外，比如误删数据后，可以通过 Bin Log 将数据恢复到指定的时间点。
作用： 你可以先用全量备份文件将数据库恢复到某个备份点，然后再利用备份时间点之后的 Bin Log 事件，将数据逐一重放，直到恢复到误操作发生前的状态。

主从复制

MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。

MySQL 主从复制是一个异步过程，核心是二进制日志（Binlog）。整个过程可以分为三个关键步骤：

写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
回放 Binlog：回放 binlog，并更新存储引擎中的数据

这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成。

主库记录（Binlog Dump）
- 当主库上的数据发生任何修改（INSERT, UPDATE, DELETE, DDL 等），这些操作都会被记录到主库的二进制日志（Binlog） 中。
- 从库启动复制后，会连接到主库。主库会为每个从库创建一个Binlog Dump 线程，该线程负责将 Binlog 中的事件（Events）发送给从库。
从库接收（I/O Thread）
- 从库有一个I/O 线程，它负责连接主库，并请求主库的 Binlog Dump 线程发送 Binlog。
- 从库的 I/O 线程接收到 Binlog 事件后，会将这些事件顺序地写入到本地的一个文件，这个文件被称为中继日志（Relay Log）。
从库应用（SQL Thread）
- 从库还有一个SQL 线程，它负责读取中继日志中的事件。
- SQL 线程会按顺序重放（Replay） 这些事件，将主库上执行过的操作，在从库上也重新执行一遍，从而保证主从数据的一致性

事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中。

在事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 文件中，并清空 binlog cache.

sync_binlog 参数用于控制 MySQL 在事务提交时，将 Binlog 从内存缓存（Binlog Cache）同步到磁盘文件的频率。它有三个可选值：

sync_binlog = 0 (默认值，不推荐)
- 时机： 事务提交后，MySQL 仅仅将 Binlog 写入文件系统缓存。至于什么时候从缓存刷到磁盘，完全依赖于操作系统自身的调度。
- 性能： 最高。因为减少了磁盘 I/O，写入操作非常快。
- 风险： 最高。一旦数据库服务器或操作系统崩溃，内存缓存中的 Binlog 数据会全部丢失。如果主从复制依赖这些 Binlog，就会导致主从数据不一致，甚至数据丢失。
sync_binlog = 1
- 时机： 每次事务提交时，MySQL 都会立即将 Binlog 从缓存写入文件，并强制同步到磁盘。
- 性能： 最低。因为每次提交都伴随着一次昂贵的磁盘 I/O 操作，在高并发场景下性能开销非常大。
- 风险： 最低。这是最安全、最可靠的设置，能够保证 Binlog 的持久性，确保在任何情况下（包括操作系统崩溃或断电）都不会丢失已提交的事务数据。这也是实现半同步复制和数据恢复的基础。
sync_binlog = N (N > 1)
- 时机： 每提交 N 次事务后，MySQL 才将 Binlog 刷入磁盘。
- 性能： 介于 0 和 1 之间，是性能与安全性的折中方案。
- 风险： 如果在第 N 次刷盘前发生崩溃，可能会丢失最多 N-1 次事务的 Binlog 数据

在MySQL中系统默认的设置是 sync_binlog = 0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦主机发生异常重启，还没持久化到磁盘的数据就会丢失。而当 sync_binlog 设置为 1 的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使主机发生异常重启，最多丢失一个事务的 binlog，而已经持久化到磁盘的数据就不会有影响，不过就是对写入性能影响太大。

如果能容少量事务的 binlog 日志丢失的风险，为了提高写入的性能，一般会 sync_binlog 设置为 100~1000 中的某个数值。

Buffer Pool

Innodb引擎设计了缓冲池,当需要读取的数据不在缓冲池中,就从磁盘中加入到缓冲池,当修改的数据在缓冲池中,就直接修改并标记为脏页.

有了 Buffer Poo 后：

当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘

InnoDB 会把存储的数据划分为若干个「页」，以页作为磁盘和内存交互的基本单位，一个页的默认大小为 16KB。因此，Buffer Pool 同样需要按「页」来划分。

在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。

所以，MySQL 刚启动的时候，你会观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，申请物理内存，接着将虚拟地址和物理地址建立映射关系。

Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，修改缓存、自适应哈希索引、锁信息等等。

两阶段提交

redo log与bin log数据不一致性

假设一个事务对数据进行了修改。

如果先写 Redo Log，再写 Binlog：
1. Redo Log 写入成功，事务提交，但 Binlog 写入失败（例如 MySQL 崩溃）。
2. 此时，数据库会认为事务已提交，恢复后数据会存在。
3. 但 Binlog 中没有这条记录。如果这是一个主从架构，从库将不会收到这条修改，导致主从数据不一致。
如果先写 Binlog，再写 Redo Log：
1. Binlog 写入成功，但 Redo Log 写入失败（例如 MySQL 崩溃）。
2. 此时，Binlog 中有这条记录，从库会执行这个操作，数据存在。
3. 但主库 Redo Log 没提交，恢复后会进行回滚，数据丢失。同样导致主从数据不一致。

MySQL 为了避免出现两份日志之间的逻辑不一致的问题，使用了「两阶段提交」来解决，两阶段提交其实是分布式事务一致性协议，它可以保证多个逻辑操作要不全部成功，要不全部失败，不会出现半成功的状态。

两阶段提交过程

两阶段提交把单个事务的提交拆分成了 2 个阶段，分别是「准备（Prepare）阶段」和「提交（Commit）阶段」，每个阶段都由协调者（Coordinator）和参与者（Participant）共同完成。注意，不要把提交（Commit）阶段和 commit 语句混淆了，commit 语句执行的时候，会包含提交（Commit）阶段。

在 MySQL 的 InnoDB 存储引擎中，开启 binlog 的情况下，MySQL 会同时维护 binlog 日志与 InnoDB 的 redo log，为了保证这两个日志的一致性，MySQL 使用了内部 XA 事务，内部 XA 事务由 binlog 作为协调者，存储引擎是参与者。

当客户端执行 commit 语句或者在自动提交的情况下，MySQL 内部开启一个 XA 事务，分两阶段来完成 XA 事务的提交.

事务的提交过程有两个阶段，就是将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，具体如下：

prepare 阶段：将 XID（内部 XA 事务的 ID）写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 持久化到磁盘（innodb_flush_log_at_trx_commit = 1 的作用）；
commit 阶段：把 XID 写入到 binlog，然后将 binlog 持久化到磁盘（sync_binlog = 1 的作用），接着调用引擎的提交事务接口，将 redo log 状态设置为 commit，此时该状态并不需要持久化到磁盘，只需要 write 到文件系统的 page cache 中就够了，因为只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功；

不管是时刻 A（redo log 已经写入磁盘， binlog 还没写入磁盘），还是时刻 B （redo log 和 binlog 都已经写入磁盘，还没写入 commit 标识）崩溃，此时的 redo log 都处于 prepare 状态。

在 MySQL 重启后会按顺序扫描 redo log 文件，碰到处于 prepare 状态的 redo log，就拿着 redo log 中的 XID 去 binlog 查看是否存在此 XID：

如果 binlog 中没有当前内部 XA 事务的 XID，说明 redolog 完成刷盘，但是 binlog 还没有刷盘，则回滚事务。对应时刻 A 崩溃恢复的情况。
如果 binlog 中有当前内部 XA 事务的 XID，说明 redolog 和 binlog 都已经完成了刷盘，则提交事务。对应时刻 B 崩溃恢复的情况。

可以看到，对于处于 prepare 阶段的 redo log，即可以提交事务，也可以回滚事务，这取决于是否能在 binlog 中查找到与 redo log 相同的 XID，如果有就提交事务，如果没有就回滚事务。这样就可以保证 redo log 和 binlog 这两份日志的一致性了。

所以说，两阶段提交是以 binlog 写成功为事务提交成功的标识，因为 binlog 写成功了，就意味着能在 binlog 中查找到与 redo log 相同的 XID。

处于 prepare 阶段的 redo log 加上完整 binlog，重启就提交事务，MySQL 为什么要这么设计?

binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。

所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。

事务没提交的时候，redo log 会被持久化到磁盘吗？

会,事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些缓存在 redo log buffer 里的 redo log 也会被「后台线程」每隔一秒一起持久化到磁盘。

也就是说，事务没提交的时候，redo log 也是可能被持久化到磁盘的。

有的同学可能会问，如果 mysql 崩溃了，还没提交事务的 redo log 已经被持久化磁盘了，mysql 重启后，数据不就不一致了？

放心，这种情况 mysql 重启会进行回滚操作，因为事务没提交的时候，binlog 是还没持久化到磁盘的。

所以， redo log 可以在事务没提交之前持久化到磁盘，但是 binlog 必须在事务提交之后，才可以持久化到磁盘

两阶段提交问题

两阶段提交虽然保证了两个日志文件的数据一致性，但是性能很差，主要有两个方面的影响：

磁盘 I/O 次数高：对于“双1”配置，每个事务提交都会进行两次 fsync（刷盘），一次是 redo log 刷盘，另一次是 binlog 刷盘。
锁竞争激烈：两阶段提交虽然能够保证「单事务」两个日志的内容一致，但在「多事务」的情况下，却不能保证两者的提交顺序一致，因此，在两阶段提交的流程基础上，还需要加一个锁来保证提交的原子性，从而保证多事务的情况下，两个日志的提交顺序一致。

磁盘I/O次数高原因

binlog 和 redo log 在内存中都对应的缓存空间，binlog 会缓存在 binlog cache，redo log 会缓存在 redo log buffer，它们持久化到磁盘的时机分别由下面这两个参数控制。一般我们为了避免日志丢失的风险，会将这两个参数设置为 1：

当 sync_binlog = 1 的时候，表示每次提交事务都会将 binlog cache 里的 binlog 直接持久到磁盘；
当 innodb_flush_log_at_trx_commit = 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘；

可以看到，如果 sync_binlog 和当 innodb_flush_log_at_trx_commit 都设置为 1，那么在每个事务提交过程中，都会至少调用 2 次刷盘操作，一次是 redo log 刷盘，一次是 binlog 落盘，所以这会成为性能瓶颈。

MySQL 引入了 binlog 组提交（group commit）机制，当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 I/O 的次数，如果说 10 个事务

组提交是一种优化技术，它旨在将多个事务的提交操作批量化处理。在没有组提交的情况下，每个事务的提交都可能需要独立的磁盘 I/O，这在高并发场景下会成为严重的性能瓶颈。

通过组提交，MySQL 将多个事务的提交请求收集在一起，然后用一次磁盘 I/O 操作完成所有这些事务的日志写入，从而有效地减少了磁盘的等待时间。依次排队刷盘的时间成本是 10，那么将这 10 个事务一次性一起刷盘的时间成本则近似于 1。

引入了组提交机制后，prepare 阶段不变，只针对 commit 阶段，将 commit 阶段拆分为三个过程：

flush 阶段：多个事务按进入的顺序将 binlog 从 cache 写入文件（不刷盘）；
sync 阶段：对 binlog 文件做 fsync 操作（多个事务的 binlog 合并一次刷盘）；
commit 阶段：各个事务按顺序做 InnoDB commit 操作；

每个阶段都有一个队列，每个阶段有锁进行保护，因此保证了事务写入的顺序，第一个进入队列的事务会成为 leader，leader领导所在队列的所有事务，全权负责整队的操作，完成后通知队内其他事务操作结束。

对每个阶段引入了队列后，锁就只针对每个队列进行保护，不再锁住提交事务的整个过程，可以看的出来，锁粒度减小了，这样就使得多个阶段可以并发执行，从而提升效率。

prepare阶段

prepare阶段主要做的是设置redo log 的XID(事务id)与prepare状态,然后进行redo log的刷盘.

在 MySQL 5.6 的组提交逻辑中，每个事务各自执行 prepare 阶段，也就是各自将 redo log 刷盘，这样就没办法对 redo log 进行组提交。

所以在 MySQL 5.7 版本中，做了个改进，在 prepare 阶段不再让事务各自执行 redo log 刷盘操作，而是推迟到组提交的 flush 阶段，也就是说 prepare 阶段融合在了 flush 阶段。

这个优化是将 redo log 的刷盘延迟到了 flush 阶段之中，sync 阶段之前。通过延迟写 redo log 的方式，为 redolog 做了一次组写入，这样 binlog 和 redo log 都进行了优化。

flush阶段

第一个事务会成为 flush 阶段的 Leader，此时后面到来的事务都是 Follower. 在redo log组提交下,获取队列中的事务组，由事务组的 Leader 对 redo log 做一次 write + fsync，即一次将同组事务的 redolog 刷盘。

完成了 prepare 阶段后，将这一组事务执行过程中产生的 binlog 写入 binlog 文件（调用 write，不会调用 fsync，所以不会刷盘，binlog 缓存在操作系统的文件系统中）。flush 阶段队列的作用是用于支撑 redo log 的组提交。如果在这一步完成后数据库崩溃，由于 binlog 中没有该组事务的记录，所以 MySQL 会在重启后回滚该组事务。

sync阶段

一组事务的 binlog 写入到 binlog 文件后，并不会马上执行刷盘的操作，而是会等待一段时间，这个等待的时长由 Binlog_group_commit，_sync_delay 参数控制，目的是为了组合更多事务的 binlog，然后再一起刷盘，在等待的过程中，如果事务的数量提前达到了 Binlog_group_commit_sync_no_delay_count 参数设置的值，就不用继续等待了，就马上将 binlog 刷盘。可以知道 sync 阶段队列的作用是用于支持 binlog 的组提交。

如果想提升 binlog 组提交的效果，可以通过设置下面这两个参数来实现：

binlog_group_commit_sync_delay= N，表示在等待 N 微妙后，直接调用 fsync，将处于文件系统中 page cache 中的 binlog 刷盘，也就是将「 binlog 文件」持久化到磁盘。
binlog_group_commit_sync_no_delay_count = N，表示如果队列中的事务数达到 N 个，就忽视binlog_group_commit_sync_delay 的设置，直接调用 fsync，将处于文件系统中 page cache 中的 binlog 刷盘。

commit阶段

进入 commit 阶段，调用引擎的提交事务接口，将 redo log 状态设置为 commit。commit 阶段队列的作用是承接 sync 阶段的事务，完成最后的引擎提交，使得 sync 可以尽早的处理下一组事务，最大化组提交的效率。

在早期的 MySQL 版本中，通过使用 prepare_commit_mutex 锁来保证事务提交的顺序，在一个事务获取到锁时才能进入 prepare 阶段，一直到 commit 阶段结束才能释放锁，下个事务才可以继续进行 prepare 操作。

通过加锁虽然完美地解决了顺序一致性的问题，但在并发量较大的时候，就会导致对锁的争用，性能不佳。

特性	老版本 (5.6 之前)	新版本 (5.7 / 8.0)
并发策略	使用全局互斥锁 `prepare_commit_mutex` 将提交过程串行化	使用队列和组提交机制,减小了锁粒度，实现了并行提交
Binlog 写入	每个事务独立竞争锁，然后写入 Binlog，导致频繁 I/O	多个事务排队，由专门线程批量写入 Binlog，减少 I/O
性能	高并发写入场景下，`prepare_commit_mutex` 成为瓶颈	显著提高了并发提交的性能，`prepare_commit_mutex` 不再是瓶颈

事务在提交的时候，需要将 binlog 和 redo log 持久化到磁盘，那么如果出现 MySQL 磁盘 I/O 很高的现象，可以通过控制以下参数，来 “延迟” binlog 和 redo log 刷盘的时机，从而降低磁盘 I/O 的频率：

设置组提交的两个参数： binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，延迟 binlog 刷盘的时机，从而减少 binlog 的刷盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但即使 MySQL 进程中途挂了，也没有丢失数据的风险，因为 binlog 早被写入到 page cache 了，只要系统没有宕机，缓存在 page cache 里的 binlog 就会被持久化到磁盘。
将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000），表示每次提交事务都 write，但累积 N 个事务后才 fsync，相当于延迟了 binlog 刷盘的时机。但是这样做的风险是，主机掉电时会丢 N 个事务的 binlog 日志。
将 innodb_flush_log_at_trx_commit 设置为 2。表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存，然后交由操作系统控制持久化到磁盘的时机。但是这样做的风险是，主机掉电的时候会丢数据。

事务

事务特性

原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，而且事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样，就好比买一件商品，购买成功时，则给商家付了钱，商品到手；购买失败时，则商品在商家手中，消费者的钱也没花出去。
一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。比如，用户 A 和用户 B 在银行分别有 800 元和 600 元，总共 1400 元，用户 A 给用户 B 转账 200 元，分为两个步骤，从 A 的账户扣除 200 元和对 B 的账户增加 200 元。一致性就是要求上述步骤操作后，最后的结果是用户 A 还有 600 元，用户 B 有 800 元，总共 1400 元，而不会出现用户 A 扣除了 200 元，但用户 B 未增加的情况（该情况，用户 A 和 B 均为 600 元，总共 1200 元）。
隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。也就是说，消费者购买商品这个事务，是不影响其他消费者购买的。
持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

持久性是通过 redo log （重做日志）来保证的；
原子性是通过 undo log（回滚日志）来保证的；
隔离性是通过 MVCC（多版本并发控制）或锁机制来保证的；
一致性则是通过持久性+原子性+隔离性来保证；

并行事务遇到的问题

MySQL 服务端是允许多个客户端连接的，这意味着 MySQL 会出现同时处理多个事务的情况。

那么在同时处理多个事务的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题。

脏读:如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。

不可重复读:在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了现象

幻读:在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了幻读现象.

事务隔离级别

SQL 标准提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率就越低

第一种是读取未提交（READ UNCOMMITTED），在这个隔离级别下，事务可以读取其他事务尚未提交的数据，可能会发生脏读、不可重复读、幻读。当你在执行一个查询时，如果一个事务正在修改数据，但尚未提交，其他事务仍然可以看到这个未提交的数据。这虽然提供了最高的并发性，但也带来了数据一致性的风险，比如读取到不一致的数据。

第二种是读取已提交（READ COMMITTED），在这个隔离级别下，事务只能读取其他事务已提交的数据，可能会发生不可重复读、幻读。当你在执行一个查询时，只有那些已经提交的事务对当前事务可见。然而，不可重复读问题依然存在：如果在同一个事务中多次查询相同的数据，可能会得到不同的结果，因为其他事务可能在查询间修改了数据。

第三种是可重复读（REPEATABLE READ），在这个隔离级别下，事务在执行期间会锁定查询的数据行，确保该数据在事务完成前不会被其他事务修改。当你在执行一个查询时，同一事务中的查询结果不会变化，即使其他事务修改了数据，当前事务也看不到变化的数据。然而，这个级别仍然存在幻读问题，即在查询过程中，其他事务可能会插入新的数据行，导致当前事务查询的数据集发生变化。

第四种是可串行化（SERIALIZABLE），在这个隔离级别下，事务的执行会像是串行执行的，即一个事务执行完成后，另一个事务才能开始。当你在执行一个查询时，不仅当前查询的数据不会被修改，其他事务也不能插入新的数据行。这个级别提供了最高的数据一致性，但代价是性能的显著下降，因为它限制了并发操作。

MySQL 在可重复读隔离级别下，可以很大程度上避免幻读现象的发生（注意是很大程度避免，并不是彻底避免），所以 MySQL 并不会使用串行化隔离级别来避免幻读现象的发生，因为使用串行化隔离级别会影响性能。

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象，解决的方案有两种：

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

这四种隔离级别具体是如何实现的呢？

对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View \来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。*「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View*。

执行「开始事务」命令，并不意味着启动了事务。在 MySQL 有两种开启事务的命令，分别是：

第一种：begin/start transaction 命令；
第二种：start transaction with consistent snapshot 命令；

这两种开启事务的命令，事务的启动时机是不同的：

执行了 begin/start transaction 命令后，并不代表事务启动了。只有在执行这个命令后，执行了第一条 select 语句，才是事务真正启动的时机；
执行了 start transaction with consistent snapshot 命令，就会马上启动事务。

MVCC如何实现

MVCC 的核心思想是：

读不阻塞写，写不阻塞读： 允许多个事务同时读写数据，而不会因为读写操作相互加锁而产生阻塞。
多版本： 当一个事务修改一行数据时，InnoDB 不会直接覆盖旧数据，而是会创建该行的一个新版本。旧版本的数据会通过 Undo Log 链表连接起来，形成一个历史版本链。
快照读（Snapshot Read）： 普通的 SELECT 语句（不加锁的读）会读取一个基于事务开始时的快照，从而看到一致性的数据。
当前读（Current Read）： SELECT ... FOR SHARE 或 SELECT ... FOR UPDATE 这样的语句会读取最新的数据版本，并对数据加锁。

Read View 本质上是一个事务在某一刻开启时，所有活跃（未提交）事务的列表。它记录了“我（当前事务）开始时，世界上有哪些事务还在跑”。这个视图是用来判断查询到的数据版本是否对当前事务可见的关键。

当你执行一个普通的 SELECT 语句时，InnoDB 会检查该行数据（的某个版本）的事务 ID（DB_TRX_ID）以及其他信息，并与 Read View 中的活跃事务列表进行比较，从而判断这个版本的数据是否应该被当前事务看到。

Read View是什么

一个 Read View 主要包含以下几个核心组成部分：

m_ids (活跃事务 ID 列表)： 一个列表中，包含了在创建 Read View 时，所有当前活跃（即已启动但尚未提交或回滚）的事务的 ID。
min_trx_id (最小活跃事务 ID)： m_ids 列表中最小的事务 ID。比这个 ID 小的所有事务都已经提交了。
max_trx_id (下一个可分配的事务 ID)： 一个未来的值，表示系统即将分配给下一个新事务的 ID。比这个 ID 大的事务（或者说，max_trx_id 及之后的所有事务）都是在 Read View 创建之后才启动的。这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；
creator_trx_id (创建者事务 ID)： 创建这个 Read View 的事务本身的 ID

此外,使用 InnoDB 存储引擎的数据库表，它的聚簇索引记录中都包含下面两个隐藏列：

trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

因此通过min_trx_id,m_ids以及max_trx_id将read view分成不同情况.

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
如果记录的 trx_id 值在 Read View 的min_trx_id和max_trx_id

之间，需要判断 trx_id 是否在 m_ids 列表中：
- 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
- 如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

这种通过版本链来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）

可重复读如何工作

可重复读隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。

MVCC 是实现可重复读的核心。当一个事务（我们称之为 T1）开启并执行第一次快照读（SELECT 语句，不加锁的读）时，InnoDB 会为这个事务创建一个 Read View（读视图）。

这个 Read View 就像一个时间戳，记录了 T1 事务开始时，数据库中所有活跃（未提交）事务的 ID 列表。

Read View 的生命周期：

在 Repeatable Read 隔离级别下，一个事务的 Read View 是在该事务第一次执行快照读时创建，并贯穿该事务的整个生命周期，直到事务提交或回滚。

查询可见性判断：

当 T1 事务执行后续的 SELECT 查询时，它会拿着这个固定的 Read View 去判断每个数据行版本是否可见。判断规则如下：

如果一个数据版本是由比 Read View 中最小活跃事务 ID（min_trx_id）更小的事务 ID 创建的（也就是说，这个版本在 T1 事务开始前就已存在并提交），那么这个版本对 T1 可见。
如果一个数据版本是由 Read View 中活跃事务 ID 列表（m_ids）中的任何一个事务创建的（也就是说，这个版本在 T1 事务开始时正在被修改，且未提交），那么这个版本对 T1 不可见。
如果一个数据版本是由比 Read View 中最大事务 ID（max_trx_id）更大的事务 ID 创建的（也就是说，这个版本是在 T1 事务开始之后才创建的），那么这个版本对 T1 不可见。
如果数据版本是由 T1 事务自己创建的，则可见。

通过这种机制，T1 事务在整个过程中，每次查询都会看到一个固定的、一致的数据快照，即使其他事务在这期间修改或删除了数据并提交，T1 也看不到这些变更。

例子：

事务 A 启动。
事务 A 第一次执行 SELECT * FROM accounts WHERE id = 10; (假设读到 balance = 100)。此时，事务 A 的 Read View 被创建。
事务 B 启动，将 id = 10 的 balance 修改为 50，并提交。
事务 A 第二次执行 SELECT * FROM accounts WHERE id = 10;。
- 因为事务 A 的 Read View 在第一次查询时就已经固定了，事务 B 的修改发生在事务 A 的 Read View 创建之后且事务 B 事务 ID 大于min_trx_id，所以事务 A 依然看到的是 balance = 100。事务 B 的修改对事务 A 是不可见的。
也就是说可重复读会沿着undo log链中数据行的roll_pointer找到合适的trx_id进行读取.

在可重复读隔离级别中，普通的 select 语句就是基于 MVCC 实现的快照读，也就是不会加锁的。而 select .. for update 语句就不是快照读了，而是当前读了，也就是每次读都是拿到最新版本的数据，但是它会对读到的记录加上 next-key lock 锁。

读提交隔离级别是在每次读取数据时，都会生成一个新的 Read View,这个read view中的m_ids以及min_trx_id等记录跟同一个事务之前的read view很可能不同.

也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。

幻读如何解决

幻读指的是在同一个事务中，两次执行相同的范围查询（例如 SELECT ... WHERE condition），但第二次查询的结果集显示，满足查询条件的行数增加了或减少了。

同一个查询在不同的时间产生不同的结果集时，事务中就会出现所谓的幻象问题。例如，如果 SELECT 执行了两次，但第二次返回了第一次没有返回的行

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了），解决的方案有两种：

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

快照读情况下

普通select进行快照读,也就是按照隔离级别读取对应版本数据.

可重复读隔离级是由 MVCC（多版本并发控制）实现的，实现的方式是开始事务后（执行 begin 语句后），在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，是查询不出来这条数据的，所以就很好了避免幻读问题

当前读情况下

MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。

另外，select ... for update 这种查询语句是当前读，每次执行的时候都是读取最新的数据。

Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题，就引出了间隙锁。

可重复读隔离级别下虽然很大程度上避免了幻读，但是还是没有能完全解决幻读

例如,在可重复读隔离级别下，事务 A 第一次执行普通的 select 语句时生成了一个 ReadView，之后事务 B 向表中新插入了一条 id = 5 的记录并提交。接着，事务 A 对 id = 5 这条记录进行了更新操作，在这个时刻，这条新记录的 trx_id 隐藏列的值就变成了事务 A 的事务 id，之后事务 A 再使用普通 select 语句去查询这条记录时就可以看到这条记录了，于是就发生了幻读。

除了上面这一种场景会发生幻读现象之外，还有下面这个场景也会发生幻读现象。

T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。
T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；
T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录

索引

索引原理

索引（Index）是一种用于加速数据库查询操作的数据结构，它的核心作用是提升查询的速度。从本质上来说，索引的作用就是帮助快速定位有序双向链表中的元素，从而减少数据扫描的范围，提高查询效率。接下来，我会详细讲述索引的基本概念和原理。

第一个是索引的基本概念，

索引是数据库中一种独特的数据结构，它并不直接存储表中的数据，而是通过创建一个新的数据结构来指向数据表中的具体记录。可以把它类比为字典的目录：当我们查字典时，首先会根据单词的起始字母找到目录页，然后通过目录页中的页码快速定位到具体的单词位置。在这个过程中，目录页就相当于索引表，而目录项就是索引本身。

然而，索引比字典目录更加复杂，因为数据库需要处理动态的数据操作，比如插入、删除和更新等操作。这些操作会导致索引发生变化，因此数据库需要维护索引的一致性和高效性。

第二个是索引的原理，

当你在MySQL中创建一个索引时，首先，MySQL会选择一种数据结构来存储索引，最常见的结构是B+树。B+树是一种自平衡的树形结构，叶子节点存储所有数据，而非叶子节点存储索引信息。它的每个节点包含多个键值对，每个键值对指向一个数据块。然后，索引会根据数据列的值进行排序，将相应的数据行指向叶子节点。

当你执行一个查询时，首先，MySQL会利用索引的树结构，根据查询条件快速定位到数据的范围，而无需扫描全表。通过从根节点开始，逐层向下遍历B+树，最终可以找到符合条件的数据。其次，如果索引指向的列已经包含查询条件，MySQL可以直接从索引中获取数据，避免了全表扫描，提高了查询速度。最后，如果查询条件涉及多个列，MySQL会使用复合索引来进一步提高查找效率，通过联合多个列的索引来加速多条件查询。

按照四个角度来分类索引。

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类：单列索引、联合索引。

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

主键索引的 B+Tree 和二级索引的 B+Tree 区别如下：

主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

回表查询

回表查询是指数据库在执行查询时，先通过索引找到对应的“行 ID”（例如 InnoDB 的主键值），然后再根据这个行 ID 到原始数据表中（聚簇索引/B+树的叶子节点）获取所有完整的行数据。

简单来说，就是查询所需的所有列数据不在索引本身中，需要“回到”数据表（或叫聚簇索引的叶子节点）去取。

覆盖索引

覆盖索引是指查询语句所需要的所有数据列（包括 SELECT 子句中的列和 WHERE、ORDER BY、GROUP BY 子句中使用的列）都可以在索引中直接找到，而无需再回到数据表（聚簇索引）进行回表查询。

索引下推

现在我们知道，对于联合索引（a, b），在执行 select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引，那在联合索引的 B+Tree 找到第一个满足条件的主键值（ID 为 2）后，还需要判断其他条件是否满足（看 b 是否等于 2），那是在联合索引里判断？还是回主键索引去判断呢？

在 MySQL 5.6 之前，只能从 ID2 （主键值）开始一个个回表，到「主键索引」上找出数据行，再对比 b 字段值。
而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

当你的查询语句的执行计划里，出现了 Extra 为 Using index condition，那么说明使用了索引下推的优化

索引区分度

另外，建立联合索引时的字段顺序，对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高，实际开发工作中建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。

区分度就是某个字段 column 不同值的个数「除以」表的总行数.性别的区分度就很小，不适合建立索引或不适合排在联合索引列的靠前的位置，而 UUID 这类字段就比较适合做索引或排在联合索引列的靠前的位置。

因为如果索引的区分度很小，假设字段的值分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比（惯用的百分比界线是”30%”）很高的时候，它一般会忽略索引，进行全表扫描。

聚簇索引与二级索引

索引又可以分成聚簇索引和非聚簇索引（二级索引），它们区别就在于叶子节点存放的是什么数据：

聚簇索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚簇索引的叶子节点；
二级索引的叶子节点存放的是主键值，而不是实际数据。

因为表的数据都是存放在聚簇索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚簇索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个。

InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

如果有主键，默认会使用主键作为聚簇索引的索引键；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；

一张表只能有一个聚簇索引，那为了实现非主键字段的快速搜索，就引出了二级索引（非聚簇索引/辅助索引），它也是利用了 B+ 树的数据结构，但是二级索引的叶子节点存放的是主键值，不是实际数据。

什么时候需要索引

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

需要占用物理空间，数量越大，占用空间越大；
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护

什么时候适用索引？

字段有唯一性限制的，比如商品编码；
经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

什么时候不需要创建索引？

WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
表数据太少的时候，不需要创建索引；
经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

InnoDB如何存储数据

InnoDB 的数据是按「数据页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。

数据页包括文件头,文件尾,页头以及页目录等信息

数据页中的用户记录按照「主键」顺序组成单向链表，单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。

因此，数据页中有一个页目录，起到记录的索引作用，就像我们书那样，针对书中内容的每个章节设立了一个目录，想看某个章节的时候，可以查看目录，快速找到对应的章节的页数，而数据页中的页目录就是为了能快速找到记录。

页目录创建的过程如下：

将所有的记录划分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录；
每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段（上图中粉红色字段）
页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。

从图可以看到，页目录就是由多个槽组成的，槽相当于分组记录的索引。然后，因为记录是按照「主键值」从小到大排序的，所以我们通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到对应的记录，无需从最小记录开始遍历整个页中的记录链表。

如何使用B+树进行查询

InnoDB 采用了 B+ 树作为索引。磁盘的 I/O 操作次数对索引的使用效率至关重要，因此在构造索引的时候，我们更倾向于采用“矮胖”的 B+ 树数据结构，这样所需要进行的磁盘 I/O 次数更少，而且 B+ 树更适合进行关键字的范围查询。InnoDB 里的 B+ 树中的每个节点都是一个数据页.

只有叶子节点（最底层的节点）才存放了数据，非叶子节点（其他上层节）仅用来存放目录项作为索引。
非叶子节点分为不同层次，通过分层来降低每一层的搜索量；
所有节点按照索引键大小排序，构成一个双向链表，便于范围查询；

再看看 B+ 树如何实现快速查找主键为 6 的记录，以上图为例子：

从根节点开始，通过二分法快速定位到符合页内范围包含查询值的页，因为查询的主键值为 6，在[1, 7)范围之间，所以到页 30 中查找更详细的目录项；
在非叶子节点（页30）中，继续通过二分法快速定位到符合页内范围包含查询值的页，主键值大于 5，所以就到叶子节点（页16）查找记录；
接着，在叶子节点（页16）中，通过槽查找记录时，使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到主键为 6 的记录。

可以看到，在定位记录所在哪一个页时，也是通过二分法快速定位到包含该记录的页。定位到该页后，又会在该页内进行二分法快速定位记录所在的分组（槽号），最后在分组内进行遍历查找

为什么使用B+树作为索引的数据类型

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

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

对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。

在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。

而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。

但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。

B树,红黑树与B+树的特别点以及区别

B+树、B树和红黑树是常见的平衡树数据结构，尤其在数据库、文件系统和内存中应用的十分广泛。

第一个方面是数据存储位置。B+树将所有数据存储在叶子节点，非叶子节点只存储索引键；B树则将数据存储在叶子节点和非叶子节点中，所有节点都存储数据；而红黑树则在每个节点中都存储数据。

第二个方面是叶子节点结构。B+树通过链表连接叶子节点，这使得范围查询和顺序遍历非常高效；B树的叶子节点不一定通过链表连接，缺乏直接支持范围查询的结构；红黑树没有专门的叶子节点结构，所有节点通过指针连接。

第三个方面是索引查找效率。B+树的查找操作最终都在叶子节点完成，查找路径统一，效率较高；B树的查找操作可能在非叶子节点完成，查找路径不统一，效率稍低；红黑树的查找路径统一，时间复杂度为O(log n)，每次操作通过旋转和重新染色来保持平衡。

第四个方面是树的高度。B+树由于扇出较高（每个节点存储多个键），树的高度通常较低，查询效率较高；B树由于非叶子节点也存储数据，扇出较小，树的高度较高；红黑树作为二叉查找树，树的高度较高，每个节点最多有两个子节点，树的深度较大。

第五个方面是顺序访问效率。B+树通过链表连接叶子节点，支持高效的顺序访问，尤其在范围查询时表现出色；B树没有直接的顺序访问机制，顺序访问效率较低；红黑树也缺乏顺序访问机制，顺序遍历效率较低。

第六个方面是磁盘I/O效率。B+树由于非叶子节点只存储索引，扇出高，可以减少磁盘访问次数，因此磁盘I/O效率非常优秀；B树稍逊于B+树，因为非叶子节点也存储数据，导致扇出较小，磁盘访问次数略多；红黑树的磁盘I/O效率较差，因为树的高度较高，每次查找可能需要频繁访问磁盘。

第七个方面是适用场景。B+树常用于数据库索引、文件系统索引，适合大规模数据的存储和检索，尤其在需要高效范围查询时；B树适用于数据库索引和文件系统索引，但相比B+树，查找效率稍低；红黑树适用于内存中的数据结构，如Java中的TreeMap和TreeSet，适合存储符号表、集合、关联数组等内存数据。

第八个方面是平衡性。B+树将所有叶子节点置于同一层，平衡性非常好；B树平衡性较好，但查找路径不统一，效率稍逊；红黑树通过旋转和染色来保持平衡，查找路径统一，操作保持平衡。

第九个方面是插入/删除操作。B+树的插入和删除操作可能会引发节点分裂和合并，操作较复杂；B树的插入和删除操作也可能引发节点分裂和合并，操作较复杂；红黑树通过旋转和染色保持平衡，操作相对简单。

第十个方面是实现复杂度。B+树的实现较复杂，尤其是在处理链表结构和节点分裂/合并时；B树的实现也较复杂，涉及节点分裂/合并，但相对较为简单；红黑树的实现相对简单，通过旋转和染色来保持平衡。

第十一个方面是空间利用率。B+树的空间利用率较高，非叶子节点只存储索引，存储效率较好；B树的空间利用率较低，非叶子节点存储数据和索引，存储效率较低；红黑树的空间利用率较高，所有节点都存储数据，且由于树的平衡性，内存利用效率较好。

MySQL 是会将数据持久化在硬盘，而存储功能是由 MySQL 存储引擎实现的，所以讨论 MySQL 使用哪种数据结构作为索引，实际上是在讨论存储引使用哪种数据结构作为索引，InnoDB 是 MySQL 默认的存储引擎，它就是采用了 B+ 树作为索引的数据结构。

要设计一个 MySQL 的索引数据结构，不仅仅考虑数据结构增删改的时间复杂度，更重要的是要考虑磁盘 I/O 的操作次数。因为索引和记录都是存放在硬盘，硬盘是一个非常慢的存储设备，我们在查询数据的时候，最好能在尽可能少的磁盘 I/O的操作次数内完成。

二分查找树虽然是一个天然的二分结构，能很好的利用二分查找快速定位数据，但是它存在一种极端的情况，每当插入的元素都是树内最大的元素，就会导致二分查找树退化成一个链表，此时查询复杂度就会从 O(logn)降低为 O(n)。

为了解决二分查找树退化成链表的问题，就出现了自平衡二叉树，保证了查询操作的时间复杂度就会一直维持在 O(logn) 。但是它本质上还是一个二叉树，每个节点只能有 2 个子节点，随着元素的增多，树的高度会越来越高。

而树的高度决定于磁盘 I/O 操作的次数，因为树是存储在磁盘中的，访问每个节点，都对应一次磁盘 I/O 操作，也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数，所以树的高度越高，就会影响查询性能。

B 树和 B+ 都是通过多叉树的方式，会将树的高度变矮，所以这两个数据结构非常适合检索存于磁盘中的数据。

但是 MySQL 默认的存储引擎 InnoDB 采用的是 B+ 作为索引的数据结构，原因有：

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。
B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；
B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

优化索引方法

常见优化索引的方法：

前缀索引优化；

使用某个字段中字符串的前几个字符建立索引，那我们为什么需要使用前缀来建立索引呢？

使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

不过，前缀索引有一定的局限性，例如：order by 就无法使用前缀索引；无法把前缀索引用作覆盖索引

覆盖索引优化；

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。

主键索引最好是自增的；

InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

因此，在使用 InnoDB 存储引擎时，如果没有特别的业务需求，建议使用自增字段作为主键。

另外，主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子节点越小（二级索引的叶子节点存放的数据是主键值），这样二级索引占用的空间也就越小

防止索引失效

索引失效情况

发生索引失效的情况：

当使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
当查询中使用了 NOT 或 != 操作符时，索引可能会失效。例如，WHERE column != ‘value’ 或 WHERE NOT column = ‘value’ 这样的查询条件通常会导致数据库放弃使用索引，因为这类操作需要扫描大量数据来排除不符合条件的记录

当 MySQL 使用复合索引时，它会从左到右匹配索引列。

一旦遇到一个范围查询（>、<、BETWEEN、LIKE '前缀%' 等）的列，索引的查找功能（Index Seek）就会停止在这个列上。

范围查询后面的索引列，虽然在物理上依然存在于索引中，但它们无法再用于进一步的索引匹配来缩小扫描范围。它们可能仍然用于索引覆盖（避免回表）或帮助进行 filesort 优化，但不再是查询优化器选择的索引查找路径的一部分。

索引使用左或左右模糊匹配

具体来说,当使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。因为索引 B+ 树是按照”索引值”有序排列存储的，只能根据前缀进行比较。

对索引使用函数、表达式、隐式类型转换

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。

不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。

举个例子，对 length(name) 的计算结果建立一个名为 idx_name_length 的索引。

1	alter table t_user add key idx_name_length ((length(name)));

然后再用下面这条查询语句，这时候就会走索引了。

mysql的隐式类型转换规则:在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较.

联合索引非最左前缀匹配

对主键字段建立的索引叫做聚簇索引，对普通字段建立的索引叫做二级索引。

那么多个普通字段组合在一起创建的索引就叫做联合索引，也叫组合索引。

创建联合索引时，需要注意创建时的顺序问题，因为联合索引 (a, b, c) 和 (c, b, a) 在使用的时候会存在差别。联合索引要能正确使用需要遵循最左匹配原则

如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

where b=2；
where c=3；
where b=2 and c=3；

有一个比较特殊的查询条件：where a = 1 and c = 3 ，符合最左匹配吗？

这种其实严格意义上来说是属于索引截断，不同版本处理方式也不一样。

MySQL 5.5 的话，前面 a 会走索引，在联合索引找到主键值后，开始回表，到主键索引读取数据行，Server 层从存储引擎层获取到数据行后，然后在 Server 层再比对 c 字段的值。

从 MySQL 5.6 之后，有一个索引下推功能，可以在存储引擎层进行索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，再返还给 Server 层，从而减少回表次数。

索引下推的大概原理是：截断的字段不会在 Server 层进行条件判断，而是会被下推到「存储引擎层」进行条件判断（因为 c 字段的值是在 (a, b, c) 联合索引里的），然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据，无需再回表读取数据来进行判断，减少回表次数，从而提升了性能。

where字句中的OR

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

这是因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

这里介绍的六种索引失效情况:

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
当我们在查询条件中对索引列使用函数，就会导致索引失效。
当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。
MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

explain执行计划

explain查看执行计划

对于执行计划，参数有：

possible_keys 字段表示可能用到的索引；
key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
key_len 表示索引的长度；
rows 表示扫描的数据行数。
type 表示数据扫描类型，我们需要重点看这个

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

All（全表扫描）；
index（全索引扫描）；
range（索引范围扫描）；
ref（非唯一索引扫描）；
eq_ref（唯一索引扫描）；
const（结果只有一条的主键或唯一索引扫描）。

all 是最坏的情况，因为采用了全表扫描的方式。index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。所以，要尽量避免全表扫描和全索引扫描。

range 表示采用了索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。

ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。

const 类型表示使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。

需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。

除了关注 type，也要关注 extra 显示的结果。

这里说几个重要的参考指标：

Using filesort ：当查询语句中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。
Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错。

count(*) 和 count(1) 有什么区别？哪个性能最好？

count() 是一个聚合函数，函数的参数不仅可以是字段名，也可以是其他任意表达式，该函数作用是统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。

count作用原理

使用 MyISAM 引擎时，执行 count 函数只需要 O(1 )复杂度，这是因为每张 MyISAM 的数据表都有一个 meta 信息有存储了row_count值，由表级锁保证一致性，所以直接读取 row_count 值就是 count 函数的执行结果。

InnoDB 不像 MyISAM 那样维护一个精确的行数计数器。这是因为 InnoDB 遵循 MVCC（多版本并发控制） 机制。

在 MVCC 下，同一个表在不同事务中可能看到不同的行数（因为有些行可能只对当前事务可见，而对其他事务不可见）。
维护一个精确的实时计数器会与 MVCC 冲突，并导致非常高的并发开销，因为每次查询都需要在不同事务版本之间进行复杂的判断。

没有 WHERE 条件的 COUNT(\*)： InnoDB 会选择一个最小的非聚簇索引（二级索引） 来进行全索引扫描，只遍历索引的叶子节点来计数。如果表上没有二级索引，它就会遍历聚簇索引（主键索引）。这个过程仍然需要扫描 B+Tree 的部分或全部叶子节点。

有 WHERE 条件的 COUNT(\*)： MySQL 会根据 WHERE 条件选择最合适的索引（或者进行全表扫描）来定位符合条件的行，然后逐一计数。

COUNT() 主要有以下几种用法：

COUNT(*)：
- 统计表中所有行的数量，无论行中的列是否包含 NULL 值。
- 这是最常见的用法，也是 SQL 标准中推荐的计算行数的方法。
COUNT(expression)：
- 统计 expression（通常是一个列名，例如 COUNT(column_name)）不为 NULL 的行数量。
- 如果 expression 是一个具体的列名，且该列允许 NULL 值，那么包含 NULL 值的行将不会被计数。
- 如果 expression 是一个常量（例如 COUNT(1)），则它的作用与 COUNT(*) 相同，都会统计所有行。
COUNT(DISTINCT expression)：
- 统计 expression 不为 NULL 且唯一的值的数量。

count(1) count(*) count(主键) count(字段)

count(主键)

在通过 count 函数统计有多少个记录时，MySQL 的 server 层会维护一个名叫 count 的变量。

server 层会循环向 InnoDB 读取一条记录，如果 count 函数指定的参数不为 NULL，那么就会将变量 count 加 1，直到符合查询的全部记录被读完，就退出循环。最后将 count 变量的值发送给客户端。InnoDB 是通过 B+ 树来保存记录的，根据索引的类型又分为聚簇索引和二级索引，它们区别在于，聚簇索引的叶子节点存放的是实际数据，而二级索引的叶子节点存放的是主键值，而不是实际数据。如果表里只有主键索引，没有二级索引时，那么，InnoDB 循环遍历聚簇索引，将读取到的记录返回给 server 层，然后读取记录中的 id 值，就会 id 值判断是否为 NULL，如果不为 NULL，就将 count 变量加 1。

这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引。

当你执行 COUNT(主键列名) 时：

根据 COUNT(expression) 的定义，数据库需要检查主键列的值是否为 NULL。
然而，由于主键的非空性，我们知道主键列的任何值都不可能是 NULL。

因此，数据库的查询优化器非常聪明，它会识别出这种特殊情况：

当 COUNT() 的参数是主键列时，优化器会知道这个列永远不为 NULL。
所以，它会优化掉“检查是否为 NULL”这一步，直接将 COUNT(主键列) 的行为等同于 COUNT(*) 或 COUNT(1)。它会去计算所有行的数量，因为它知道主键列的每一个值都是有效的、非空的

count(1)

如果表里只有主键索引，没有二级索引时。那么，InnoDB 循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值，因为 count 函数的参数是 1，不是字段，所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL，因此 server 层每从 InnoDB 读取到一条记录，就将 count 变量加 1。

可以看到，count(1) 相比 count(主键字段) 少一个步骤，就是不需要读取记录中的字段值，所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点。

但是，如果表里有二级索引时，InnoDB 循环遍历的对象就二级索引了

count(*)

count(\*) 其实等于 count(0)，也就是说，当你使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理。

count(*) 执行过程跟 count(1) 执行过程基本一样的，性能没有什么差异。

而且 MySQL 会对 count(*) 和 count(1) 有个优化，如果有多个二级索引的时候，优化器会使用key_len 最小的二级索引进行扫描。

只有当没有二级索引的时候，才会采用主键索引来进行统计

count(普通字段)

count(字段) 的执行效率相比前面的 count(1)、 count(*)、 count(主键字段) 执行效率是最差的。

count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。

所以，如果要执行 count(1)、 count(*)、 count(主键字段) 时，尽量在数据表上建立二级索引，这样优化器会自动采用 key_len 最小的二级索引进行扫描，相比于扫描主键索引效率会高一些。

再来，就是不要使用 count(字段) 来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数，建议给这个字段建立一个二级索引。。

如何优化count(*)

如果业务对于统计个数不需要很精确，比如搜索引擎在搜索关键词的时候，给出的搜索结果条数是一个大概值

可以使用show table status或EXPLAIN SELECT COUNT(*) FROM table_name输出结果中的 rows 字段会给出优化器预估的行数。这个值通常不精确，但对于快速获取一个大概的数字很有用。

对于允许少量延迟的计数需求，维护一个独立的计数器是最高效的方法。将这个计数值保存到单独的一张计数表中。当在数据表插入一条记录的同时，将计数表中的计数字段 + 1。也就是说，在新增和删除操作时，我们需要额外维护这个计数表。

原理：
- 缓存： 将 COUNT(*) 的结果缓存到 Redis、Memcached 等缓存系统中，并设置过期时间。
- 计数器表： 创建一个专门的表来存储各个实体的计数。例如 product_counts (product_id INT PRIMARY KEY, view_count INT, sales_count INT)。
实现：
- 异步更新： 通过应用程序代码或消息队列，在数据发生增删改时异步更新计数器。
- 触发器（不推荐用于高并发）： 可以在数据库层面使用触发器来自动更新计数器表，但触发器本身会增加写入开销，在高并发场景下可能成为瓶颈。
优点： 极高的读取性能，因为直接从内存缓存或小型计数器表获取。
缺点： 计数可能存在短暂的延迟或不一致性；增加了系统的复杂性。
适用场景： 网站访客数、商品浏览量、论坛帖子数等对实时性要求不那么高的场景。

MySQL分页优化

1	select * from table order by id limit 0, 10;

mysql内部分为server层和存储引擎层。一般情况下存储引擎都用innodb。

server层有很多模块，其中需要关注的是执行器是用于跟存储引擎打交道的组件。

执行器可以通过调用存储引擎提供的接口，将一行行数据取出，当这些数据完全符合要求（比如满足其他where条件），则会放到结果集中，最后返回给调用mysql的客户端.

基于主键索引的分页

1	select * from table order by id limit 6000000, 10;

server层会调用innodb的接口，由于offset=6000000，会在innodb里的主键索引中获取到第0到（6000000 + 10）条完整行数据，返回给server层之后根据offset的值挨个抛弃，最后只留下最后面的size条，也就是10条数据，放到server层的结果集中，返回给客户端。

可以看出，当offset非0时，server层会从引擎层获取到很多无用的数据，而当select后面是*号时，就需要拷贝完整的行信息，拷贝完整数据跟只拷贝行数据里的其中一两个列字段耗时是不同的，这就让原本就耗时的操作变得更多.

因为前面的offset条数据最后都是不要的，就算将完整字段都拷贝来了又有什么用呢，所以可以将sql语句修改成下面这样。

1	select * from table where id >=(select id from table order by id limit 6000000, 1) order by id limit 10;

上面这条sql语句，里面先执行子查询 select id from page order by id limit 6000000, 1, 这个操作，其实也是将在innodb中的主键索引中获取到6000000+1条数据，然后server层会抛弃前6000000条，只保留最后一条数据的id。

但不同的地方在于，在返回server层的过程中，只会拷贝数据行内的id这一列，而不会拷贝数据行的所有列，当数据量较大时，这部分的耗时还是比较明显的。

在拿到了上面的id之后，假设这个id正好等于6000000，那sql就变成了

1	select * from table where id >=(6000000) order by id limit 10;

这样innodb再走一次主键索引，通过B+树快速定位到id=6000000的行数据，时间复杂度是lg(n)，然后向后取10条数据。

基于非主键索引的分页

1	select * from page table by user_name limit 0, 10;

server层会调用innodb的接口，在innodb里的非主键索引中获取到第0条数据对应的主键id后，回表到主键索引中找到对应的完整行数据，然后返回给server层，server层将其放到结果集中，返回给客户端。非主键索引的limit过程，比主键索引的limit过程，多了个回表的消耗

当limit offset过大时如果优化器分析回表操作次数过多会使用全表扫描

这种情况也能通过一些方式去优化。比如

1	select * from page t1, (select id from page order by user_name limit 6000000, 100) t2 WHERE t1.id = t2.id;

通过select id from page order by user_name limit 6000000, 100。先走innodb层的user_name非主键索引取出id，因为只拿主键id，不需要回表.

深度分页问题

当offset变得超大时，比如到了百万千万的量级就导致了深度分页.

通过limit offset size分页的形式去分批获取，刚开始都是好的，等慢慢地，哪天数据表变得奇大无比，就有可能出现前面提到的深度分页问题。

取出全表数据或下一页连续滚动加载

可以将所有的数据根据id主键进行排序，然后分批次取，将当前批次的最大id作为下次筛选的条件进行查询。

这种方法的核心思想是记住上次查询的位置，而不是通过偏移量来跳过数据。它适用于可以找到一个唯一且有序的列（通常是主键或唯一索引列）作为“书签”的场景。

原理： 不使用 OFFSET，而是记录上一页最后一条记录的 ID（或其他有序列的值），然后查询下一页时从这个 ID 之后开始。
示例：
- 第一页：SELECT * FROM products ORDER BY id ASC LIMIT 10; (假设最后一条记录 id 是 10)
- 第二页：SELECT * FROM products WHERE id > 10 ORDER BY id ASC LIMIT 10;
- 第 N 页：SELECT * FROM products WHERE id > [上一页最大ID] ORDER BY id ASC LIMIT 10;
优点： 无论页码多深，查询效率都非常高，因为它总是从一个已知点开始，进行固定数量的扫描。
缺点： 不支持“跳到第 X 页”的需求，只支持“下一页/上一页”或连续滚动加载的场景。

给用户做分页展示

优化 LIMIT offset, pageSize 的子查询

这种方法适用于仍然需要支持“跳到第 X 页”的场景，但尝试减少主查询的回表成本。

原理： 先通过一个子查询只查出目标页的主键 ID，这通常能利用索引快速完成。然后，再通过这些 ID 回表查询完整的数据。
示例： SELECT p.* FROM products p INNER JOIN (SELECT id FROM products ORDER BY id ASC LIMIT 100000, 10) AS sub ON p.id = sub.id;
优点：
- 子查询 (SELECT id FROM products ORDER BY id ASC LIMIT 100000, 10) 只扫描和排序 id 这一列（如果 id 是主键，可以走聚簇索引的索引部分或覆盖索引），这比扫描所有列要快得多。
- INNER JOIN 后，回表操作是基于精确的 ID 列表，通常效率较高。
缺点： 仍然需要扫描和排序 offset + pageSize 个 ID。当 offset 非常大时，子查询本身还是会有性能问题。但在某些情况下，尤其是有覆盖索引支持子查询时，比直接 SELECT * LIMIT OFFSET 要好。

如果实现翻页需求,类似谷歌搜索时看到的翻页功能,不需要offset过多数据,一般十几页就够了.

如果要做搜索或筛选类的页面的话，就使用elastic search，并且也需要控制展示的结果数，比如一万以内，这样不至于让分页过深.

如果因为各种原因，必须使用mysql。那同样，也需要控制下返回结果数量，比如数量1k以内。这样就能勉强支持各种翻页，跳页（比如突然跳到第6页然后再跳到第106页）。

但如果能从产品的形式上就做成不支持跳页会更好，比如只支持上一页或下一页。这样就可以使用上面提到的start_id方式，采用分批获取，每批数据以start_id为起始位置。这个解法最大的好处是不管翻到多少页，查询速度永远稳定

limit offset, size 比 limit size 要慢，且offset的值越大，sql的执行速度越慢。
当offset过大，会引发深度分页问题，目前不管是mysql还是es都没有很好的方法去解决这个问题。只能通过限制查询数量或分批获取的方式进行规避。
遇到深度分页的问题，多思考其原始需求，大部分时候是不应该出现深度分页的场景的，必要时多去影响产品经理。
如果数据量很少，比如1k的量级，且长期不太可能有巨大的增长，还是用limit offset, size 的方案吧，整挺好，能用就行。

锁

根据加锁的范围，可以分为全局锁、表级锁和行锁三类。

全局锁

1	flush tables with read lock

执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：

对数据的增删改操作，比如 insert、delete、update等语句；
对表结构的更改操作，比如 alter table、drop table 等语句。

如果要释放全局锁，则要执行这条命令：

1	unlock tables

当会话断开了，全局锁会被自动释放。

加全局锁主要用在数据备份的时候,使得数据只读,避免备份时的数据不一致性问题.如果数据库里有很多数据，备份就会花费很多的时间，关键是备份期间，业务只能读数据，而不能更新数据，这样会造成业务停滞。

既然备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免？

有的，如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。

InnoDB 存储引擎默认的事务隔离级别正是可重复读，因此可以采用这种方式来备份数据库。

但是，对于 MyISAM 这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法

表级锁

MySQL 里面表级别的锁有这几种：

表锁；
元数据锁（MDL）;
意向锁；
AUTO-INC 锁；

表锁

1 2	lock tables table_name read; lock tables table_name write;

read表锁是共享读锁,允许当前会话读取被锁定的表但阻止其他会话对这些表进行写操作.

write表锁是独占写锁,允许当前会话读写被锁定的表但阻止其他会话对这些表进行任何操作.

表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

当会话退出后，也会释放所有表锁。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式，不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 实现了颗粒度更细的行级锁

元数据锁

对数据库表进行操作时，会自动给这个表加上 MDL：

对一张表进行 CRUD 操作时，加的是 MDL 读锁；
对一张表做结构变更操作的时候，加的是 MDL 写锁；

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

当有线程在执行 select 语句（加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（释放 MDL 读锁）。

反之，当有线程对表结构进行变更（加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（释放 MDL 写锁）。

元数据锁是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

那如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没提交），那在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：

首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；
然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，

那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

意向锁

在使用 InnoDB 引擎的表里对某些记录加上”共享锁”之前，需要先在表级别加上一个「意向共享锁」；
在使用 InnoDB 引擎的表里对某些纪录加上”独占锁”之前，需要先在表级别加上一个「意向独占锁」；

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。

普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下：

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

表锁和行锁是满足读读共享、读写互斥、写写互斥的。

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

在加上独占表锁时就会检查表上是否有意向独占锁,不用去遍历每个记录是否加行锁.

意向锁的目的是为了快速判断表里是否有记录被加锁

AUCO-INC锁

表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。

之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。

在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。

但是， AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

在老版本,自增字段通过表级锁AUTO-INC锁执行插入语句后再释放.

在新版本,AUTO-INC 锁的工作机制在不同版本和模式下有所不同：

传统模式（innodb_autoinc_lock_mode=0）

这是最悲观的模式，也是最早的实现方式。

加锁机制：在执行 INSERT 语句时，无论是单行插入还是批量插入，都会对整个表加一个 AUTO-INC 表锁。
并发性：当一个事务持有这个锁时，其他所有试图插入数据的事务都会被阻塞，直到锁被释放。这使得自增值严格连续，但并发性能非常差。

连续模式（innodb_autoinc_lock_mode=1）

这是MySQL 5.1之后引入的默认模式，旨在提高并发性。

加锁机制：
- 对于单行插入：MySQL只在分配自增值时加一个轻量级锁，分配完后立即释放，锁定的时间非常短。
- 对于批量插入：为了保证批量插入的自增值是连续的，MySQL会像传统模式一样加一个AUTO-INC表锁，直到语句执行完毕才释放。
并发性：这种模式在单行插入时有很高的并发性。对于批量插入，虽然性能有所牺牲，但保证了自增值的连续性。

交错模式（innodb_autoinc_lock_mode=2）

这是MySQL 8.0的推荐模式。

加锁机制：完全移除了AUTO-INC表锁。所有插入操作都只在分配自增值时加一个轻量级的锁，分配完后立即释放。
并发性：并发性能最高，自增值的生成完全不会阻塞其他事务。
问题：由于不同事务的自增值是交错分配的，不能保证自增值的连续性。当一个事务回滚时，其已经分配的自增值会形成“空洞”，永远不会被使用。

当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，在「主从复制的场景」中会发生数据不一致的问题。

如果 innodb_autoinc_lock_mode = 2，意味着「申请自增主键后就释放锁，不必等插入语句执行完」。那么就可能出现这样的情况：

session B 先插入了两个记录，(1,1,1)、(2,2,2)；
然后，session A 来申请自增 id 得到 id=3，插入了（3,5,5)；
之后，session B 继续执行，插入两条记录 (4,3,3)、 (5,4,4)。

可以看到，session B 的 insert 语句，生成的 id 不连续。

当「主库」发生了这种情况，binlog 面对 t2 表的更新只会记录这两个 session 的 insert 语句，如果 binlog_format=statement，记录的语句就是原始语句。记录的顺序要么先记 session A 的 insert 语句，要么先记 session B 的 insert 语句。

但不论是哪一种，这个 binlog 拿去「从库」执行，这时从库是按「顺序」执行语句的，只有当执行完一条 SQL 语句后，才会执行下一条 SQL。因此，在从库上「不会」发生像主库那样两个 session 「同时」执行向表 t2 中插入数据的场景。所以，在备库上执行了 session B 的 insert 语句，生成的结果里面，id 都是连续的。这时，主从库就发生了数据不一致。

要解决这问题，binlog 日志格式要设置为 row，这样在 binlog 里面记录的是主库分配的自增值，到备库执行的时候，主库的自增值是什么，从库的自增值就是什么。

所以，当 innodb_autoinc_lock_mode = 2 时，并且 binlog_format = row，既能提升并发性，又不会出现数据一致性问题。

行级锁

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

前面也提到，普通的 select 语句是不会对记录加锁的，因为它属于快照读。如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读。

//对读取的记录加共享锁
select ... lock in share mode;

//对读取的记录加独占锁
select ... for update;

上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin、start transaction 或者 set autocommit = 0

普通的 select 语句是不会对记录加锁的（除了串行化隔离级别），因为它属于快照读，是通过 MVCC（多版本并发控制）实现的。

记录锁

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：

当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

插入、删除以及更新的DML语句会加互斥锁,select xx for update也会加互斥锁

而select xx for share会加共享锁.当事务执行 commit 后，事务过程中生成的锁都会被释放

间隙锁

Gap Lock 称为间隙锁，存在于可重复读隔离级别和串行化隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

Next-Key Lock

记录锁 +间隙锁的组合，锁定一个范围，并且锁定记录本身。

对于记录锁，要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的

插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。如果说间隙锁锁住的是一个区间，那么插入意向锁锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。

Mysql如何加行级锁

行级锁加锁规则比较复杂，不同的场景，加锁的形式是不同的。

加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。

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

唯一索引等值查询

当我们用唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同：

当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。
当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」

如果是用二级索引（不管是不是非唯一索引，还是唯一索引）进行锁定读查询的时候，除了会对二级索引项加行级锁（如果是唯一索引的二级索引，加锁规则和主键索引的案例相同），而且还会对查询到的记录的主键索引项上加「记录锁」

可以通过 select * from performance_schema.data_locks\G; 这条语句，查看事务执行 SQL 过程中加了什么锁.加锁的对象是针对索引，因为这里查询语句扫描的 B+ 树是聚簇索引树，即主键索引树，所以是对主键索引加锁。将对应记录的主键索引加记录锁后，就意味着其他事务无法对该记录进行更新和删除操作了。

唯一索引范围查询

当唯一索引进行范围查询时，会对每一个扫描到的索引加 next-key 锁，然后如果遇到下面这些情况，会退化成记录锁或者间隙锁：

情况一：针对「大于等于」的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中，那么该记录的索引中的 next-key 锁会退化成记录锁。
情况二：针对「小于或者小于等于」的范围查询，要看条件值的记录是否存在于表中：
- 当条件值的记录不在表中，那么不管是「小于」还是「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。
- 当条件值的记录在表中，如果是「小于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁；如果「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引 next-key 锁不会退化成间隙锁。其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。

非唯一索引等值查询

当我们用非唯一索引进行等值查询的时候，因为存在两个索引，一个是主键索引，一个是非唯一索引（二级索引），所以在加锁时，同时会对这两个索引都加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁。

针对非唯一索引等值查询时，查询的记录存不存在，加锁的规则也会不同：

当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。
当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。

非唯一索引范围查询

非唯一索引范围查询，索引的 next-key lock 不会有退化为间隙锁和记录锁的情况，也就是非唯一索引进行范围查询时，对二级索引记录加锁都是加 next-key 锁。

没有使用索引的加锁

如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

不只是锁定读查询语句不加索引才会导致这种情况，update 和 delete 语句如果查询条件不加索引，那么由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。

因此，在线上在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题

InnoDB 存储引擎的默认事务隔离级别是「可重复读」，但是在这个隔离级别下，在多个事务并发的时候，会出现幻读的问题，所谓的幻读是指在同一事务下，连续执行两次同样的查询语句，第二次的查询语句可能会返回之前不存在的行。

因此 InnoDB 存储引擎自己实现了行锁，通过 next-key 锁（记录锁和间隙锁的组合）来锁住记录本身和记录之间的“间隙”，防止其他事务在这个记录之间插入新的记录，从而避免了幻读现象。当我们执行 update 语句时，实际上是会对记录加独占锁（X 锁）的，如果其他事务对持有独占锁的记录进行修改时是会被阻塞的。另外，这个锁并不是执行完 update 语句就会释放的，而是会等事务结束时才会释放。

在 InnoDB 事务中，对记录加锁带基本单位是 next-key 锁，但是会因为一些条件会退化成间隙锁，或者记录锁。加锁的位置准确的说，锁是加在索引上的而非行上。

比如，在 update 语句的 where 条件使用了唯一索引，那么 next-key 锁会退化成记录锁，也就是只会给一行记录加锁。

因为事务 A的 update 语句中 where 条件没有索引列，触发了全表扫描，在扫描过程中会对索引加锁，所以全表扫描的场景下，所有记录都会被加锁，也就是这条 update 语句产生了 4 个记录锁和 5 个间隙锁，相当于锁住了全表。在 update 语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。

可以设置sql_safe_updates=1,update 语句必须满足如下条件之一才能执行成功：

使用 where，并且 where 条件中必须有索引列；
使用 limit；
同时使用 where 和 limit，此时 where 条件中可以没有索引列；

delete 语句必须满足以下条件能执行成功：

同时使用 where 和 limit，此时 where 条件中可以没有索引列；

如果 where 条件带上了索引列，但是优化器最终扫描选择的是全表，而不是索引的话，我们可以使用 force index([index_name]) 可以告诉优化器使用哪个索引，以此避免有几率锁全表带来的隐患。

Mysql发生死锁

Innodb 引擎为了解决「可重复读」隔离级别下的幻读问题，就引出了 next-key 锁，它是记录锁和间隙锁的组合。

Record Lock，记录锁，锁的是记录本身；
Gap Lock，间隙锁，锁的就是两个值之间的空隙，以防止其他事务在这个空隙间插入新的数据，从而避免幻读现象。

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

当发生超时后，就出现下面这个提示：

开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

当检测到死锁后，就会出现下面这个提示：

上面这个两种策略是「当有死锁发生时」的避免方式。

我们可以回归业务的角度来预防死锁，对订单做幂等性校验的目的是为了保证不会出现重复的订单，那我们可以直接将 order_no 字段设置为唯一索引列，利用它的唯一性来保证订单表不会出现重复的订单，不过有一点不好的地方就是在我们插入一个已经存在的订单记录时就会抛出异常。

内存

MySQL 的数据是存储在磁盘里的，但是也不能每次都从磁盘里面读取数据，这样性能是极差的。要想提升查询性能，加个缓存就行了嘛。所以，当数据从磁盘中取出后，缓存内存中，下次查询同样的数据的时候，直接从内存中读取。

为此，Innodb 存储引擎设计了一个缓冲池（*Buffer Pool*），来提高数据库的读写性能。

innodb引擎使用了Buffer Pool提升读取和修改效率.有了缓冲池后：

当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，最后由后台线程将脏页写入到磁盘。

所以，MySQL 刚启动的时候，你会观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，接着将虚拟地址和物理地址建立映射关系。

Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 undo 页，change buffer缓存、自适应哈希索引、锁信息等等。

为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括「缓存页的表空间、页号、缓存页地址、链表节点」等等。

控制块也是占有内存空间的，它是放在 Buffer Pool 的最前面，接着才是缓存页.

管理空闲页

Buffer Pool 是一片连续的内存空间，当 MySQL 运行一段时间后，这片连续的内存空间中的缓存页既有空闲的，也有被使用的。为了避免遍历整个buffer pool,为了快速找到空闲的缓存页，可以使用链表结构，将空闲缓存页的「控制块」作为链表的节点，这个链表称为 Free 链表（空闲链表）

Free 链表上除了有控制块，还有一个头节点，该头节点包含链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。

Free 链表节点是一个一个的控制块，而每个控制块包含着对应缓存页的地址，所以相当于 Free 链表节点都对应一个空闲的缓存页。

有了 Free 链表后，每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的控制块从 Free 链表中移除。

管理脏页

设计 Buffer Pool 除了能提高读性能，还能提高写性能，也就是更新数据的时候，不需要每次都要写入磁盘，而是将 Buffer Pool 对应的缓存页标记为脏页，然后再由后台线程将脏页写入到磁盘。

那为了能快速知道哪些缓存页是脏的，于是就设计出 Flush 链表，它跟 Free 链表类似的，链表的节点也是控制块，区别在于 Flush 链表的元素都是脏页。有了 Flush 链表后，后台线程就可以遍历 Flush 链表，将脏页写入到磁盘。

LRU链表提高缓冲命中率

Buffer Pool 的大小是有限的，对于一些频繁访问的数据我们希望可以一直留在 Buffer Pool 中，而一些很少访问的数据希望可以在某些时机可以淘汰掉，从而保证 Buffer Pool 不会因为满了而导致无法再缓存新的数据，同时还能保证常用数据留在 Buffer Pool 中。

要实现这个，最容易想到的就是 LRU（Least recently used）算法。

该算法的思路是，链表头部的节点是最近使用的，而链表末尾的节点是最久没被使用的。那么，当空间不够了，就淘汰最久没被使用的节点，从而腾出空间。

简单的 LRU 算法的实现思路是这样的：

当访问的页在 Buffer Pool 里，就直接把该页对应的 LRU 链表节点移动到链表的头部。
当访问的页不在 Buffer Pool 里，除了要把页放入到 LRU 链表的头部，还要淘汰 LRU 链表末尾的节点。

Buffer Pool 里有三种页和链表来管理数据。

Free Page（空闲页），表示此页未被使用，位于 Free 链表；
Clean Page（干净页），表示此页已被使用，但是页面未发生修改，位于LRU 链表。
Dirty Page（脏页），表示此页「已被使用」且「已经被修改」，其数据和磁盘上的数据已经不一致。当脏页上的数据写入磁盘后，内存数据和磁盘数据一致，那么该页就变成了干净页。脏页同时存在于 LRU 链表和 Flush 链表。

普通LRU链表缺点

预读失效；

程序是有空间局部性的，靠近当前被访问数据的数据，在未来很大概率会被访问到。

所以，MySQL 在加载数据页时，会提前把它相邻的数据页一并加载进来，目的是为了减少磁盘 IO。但是可能这些被提前加载进来的数据页，并没有被访问，相当于这个预读是白做了，这个就是预读失效。如果使用简单的 LRU 算法，就会把预读页放到 LRU 链表头部，而当 Buffer Pool空间不够的时候，还需要把末尾的页淘汰掉。

如果这些预读页如果一直不会被访问到，就会出现一个很奇怪的问题，不会被访问的预读页

要避免预读失效带来影响，最好就是让预读的页停留在 Buffer Pool 里的时间要尽可能的短，让真正被访问的页才移动到 LRU 链表的头部，从而保证真正被读取的热数据留在 Buffer Pool 里的时间尽可能长。

MySQL 改进了 LRU 算法，将 LRU 划分了 2 个区域：old 区域和 young 区域。young 区域在 LRU 链表的前半部分，old 区域则是在后半部分.old 区域占整个 LRU 链表长度的比例可以通过 innodb_old_blocks_pct 参数来设置，默认是 37.划分这两个区域后，预读的页就只需要加入到 old 区域的头部，当页被真正访问的时候，才将页插入 young 区域的头部。如果预读的页一直没有被访问，就会从 old 区域移除，这样就不会影响 young 区域中的热点数据。

Buffer Pool 污染；

当某一个 SQL 语句扫描了大量的数据时，在 Buffer Pool 空间比较有限的情况下，可能会将 Buffer Pool 里的所有页都替换出去，导致大量热数据被淘汰了，等这些热数据又被再次访问的时候，由于缓存未命中，就会产生大量的磁盘 IO，MySQL 性能就会急剧下降，这个过程被称为 Buffer Pool 污染。

注意， Buffer Pool 污染并不只是查询语句查询出了大量的数据才出现的问题，即使查询出来的结果集很小，也会造成 Buffer Pool 污染。

在一个数据量非常大的表，执行了这条语句：

1	select * from t_user where name like "%xiaolin%";

可能这个查询出来的结果就几条记录，但是由于这条语句会发生索引失效，所以这个查询过程是全表扫描的，接着会发生如下的过程：

从磁盘读到的页加入到 LRU 链表的 old 区域头部；
当从页里读取行记录时，也就是页被访问的时候，就要将该页放到 young 区域头部；
接下来拿行记录的 name 字段和字符串 xiaolin 进行模糊匹配，如果符合条件，就加入到结果集里；
如此往复，直到扫描完表中的所有记录。

经过这一番折腾，原本 young 区域的热点数据都会被替换掉。

像前面这种全表扫描的查询，很多缓冲页其实只会被访问一次，但是它却只因为被访问了一次而进入到 young 区域，从而导致热点数据被替换了。

LRU 链表中 young 区域就是热点数据，只要我们提高进入到 young 区域的门槛，就能有效地保证 young 区域里的热点数据不会被替换掉。

MySQL 是这样做的，进入到 young 区域条件增加了一个停留在 old 区域的时间判断。

具体是这样做的，在对某个处在 old 区域的缓存页进行第一次访问时，就在它对应的控制块中记录下来这个访问时间：

如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该缓存页就不会被从 old 区域移动到 young 区域的头部；
如果后续的访问时间与第一次访问的时间不在某个时间间隔内，那么该缓存页移动到 young 区域的头部；

这个间隔时间是由 innodb_old_blocks_time 控制的，默认是 1000 ms。

也就说，只有同时满足「被访问」与「在 old 区域停留时间超过 1 秒」两个条件，才会被插入到 young 区域头部，这样就解决了 Buffer Pool 污染的问题。

另外，MySQL 针对 young 区域其实做了一个优化，为了防止 young 区域节点频繁移动到头部。young 区域前面 1/4 被访问不会移动到链表头部，只有后面的 3/4被访问了才会

也就是说上面全索引读取到的数据页放在old区后,对页中的数据进行访问的时候,

脏页刷盘时机

引入了 Buffer Pool 后，当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，但是磁盘中还是原数据。

因此，脏页需要被刷入磁盘，保证缓存和磁盘数据一致，但是若每次修改数据都刷入磁盘，则性能会很差，因此一般都会在一定时机进行批量刷盘。

可能大家担心，如果在脏页还没有来得及刷入到磁盘时，MySQL 宕机了，不就丢失数据了吗？

这个不用担心，InnoDB 的更新操作采用的是 Write Ahead Log 策略，即先写日志，再写入磁盘，通过 redo log 日志让 MySQL 拥有了崩溃恢复能力。

下面几种情况会触发脏页的刷新：

当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
MySQL 认为空闲时，后台线程会定期将适量的脏页刷入到磁盘；
MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；

在我们开启了慢 SQL 监控后，如果你发现「偶尔」会出现一些用时稍长的 SQL，这可能是因为脏页在刷新到磁盘时可能会给数据库带来性能开销，导致数据库操作抖动。

如果间断出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小

Innodb 存储引擎设计了一个缓冲池（*Buffer Pool*），来提高数据库的读写性能。

Buffer Pool 以页为单位缓冲数据，可以通过 innodb_buffer_pool_size 参数调整缓冲池的大小，默认是 128 M。

Innodb 通过三种链表来管理缓页：

Free List （空闲页链表），管理空闲页；
Flush List （脏页链表），管理脏页；
LRU List，管理脏页+干净页，将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出去。；

InnoDB 对 LRU 做了一些优化，我们熟悉的 LRU 算法通常是将最近查询的数据放到 LRU 链表的头部，而 InnoDB 做 2 点优化：

将 LRU 链表分为young 和 old 两个区域，加入缓冲池的页，优先插入 old 区域；页被访问时，才进入 young 区域，目的是为了解决预读失效的问题。
当「页被访问」且「 old 区域停留时间超过 innodb_old_blocks_time 阈值（默认为1秒）」时，才会将页插入到 young 区域，否则还是插入到 old 区域，目的是为了解决批量数据访问，大量热数据淘汰的问题。

可以通过调整 innodb_old_blocks_pct 参数，设置 young 区域和 old 区域比例。

在开启了慢 SQL 监控后，如果你发现「偶尔」会出现一些用时稍长的 SQL，这可因为脏页在刷新到磁盘时导致数据库性能抖动。如果在很短的时间出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小。

其他问题

删除表中部分数据为什么文件没有减小以及表重建

共享表空间模式（ibdata1）

如果你使用的是共享表空间模式（即 innodb_file_per_table = OFF），那么所有 InnoDB 表的数据、索引以及系统元数据都存放在一个或少数几个名为 ibdataX 的文件中。

工作原理：当你删除表或数据时，这些空间并不会被立即释放。它们会被标记为“可重用”，并留在 ibdata1 文件内部。
为什么不缩小：ibdata1 文件只会不断增大，而不会自动收缩。这是为了避免频繁的系统调用和 I/O 开销，因为收缩文件是一个复杂且昂贵的操作。
如何回收空间：要回收这部分空间，你需要执行一个非常繁琐的操作：
1. 备份所有数据库。
2. 删除所有数据文件（包括 ibdata* 文件）。
3. 重启 MySQL，让它重新创建新的、干净的 ibdata1 文件。
4. 恢复所有备份

当你删除数据库中的表时，如果发现数据库文件大小没有变化，这通常是因为 MySQL 的存储引擎（尤其是 InnoDB）没有立即将释放的空间归还给操作系统。

共享表空间模式（ibdata1）

工作原理：当你删除表或数据时，这些空间并不会被立即释放。它们会被标记为“可重用”，并留在 ibdata1 文件内部。
为什么不缩小：ibdata1 文件只会不断增大，而不会自动收缩。这是为了避免频繁的系统调用和 I/O 开销，因为收缩文件是一个复杂且昂贵的操作。
如何回收空间：要回收这部分空间，你需要执行一个非常繁琐的操作：
1. 备份所有数据库。
2. 删除所有数据文件（包括 ibdata* 文件）。
3. 重启 MySQL，让它重新创建新的、干净的 ibdata1 文件。
4. 恢复所有备份。

独占表空间模式（.ibd）

如果你使用的是独占表空间模式（即 innodb_file_per_table = ON），那么每个 InnoDB 表都有一个独立的 .ibd 文件。

工作原理：当你删除一张表时，MySQL 会直接删除对应的 .ibd 文件。这种情况下，磁盘空间会立即被操作系统回收。
为什么有时也不缩小：
- 碎片化：如果你只是删除了表中的部分数据（例如 DELETE FROM table;），而没有删除表本身，那么这些空间同样会被标记为“可重用”。虽然这些空间位于独立的 .ibd 文件中，但文件本身并不会自动缩小。
- 如何回收空间：在这种情况下，你需要对表进行重建（Rebuilding），才能真正回收空间。你可以使用以下命令：
  1
  ALTER TABLE your_table_name ENGINE=InnoDB;
  或者
  1
  OPTIMIZE TABLE your_table_name;
  这些命令会重新整理表数据，清除碎片，从而减小 .ibd 文件的大小。

数据库文件大小没有变化，最常见的原因是文件碎片化。

如果你使用的是共享表空间，那么即使删除了表，空间也无法自动回收。这是该模式最大的缺点。
如果你使用的是独占表空间，那么删除表文件本身会回收空间。但如果你只是删除了数据，则需要重建表才能回收碎片化空间。

表重建（Table Rebuilding）是一种数据库操作，指的是通过重新创建一张新的表来替换原有的表，以达到优化存储结构、回收空间和提升性能的目的。

为什么需要表重建？

在数据库的日常操作中，尤其是对于 InnoDB 存储引擎，随着时间的推移，表数据会因为频繁的增删改（INSERT, UPDATE, DELETE）操作而产生碎片（Fragmentation）。

删除操作：当你删除一行数据时，它占据的空间并不会立即被操作系统回收，而是被标记为“可重用”。这些“洞”散布在数据文件中。
更新操作：当 UPDATE 导致数据行变长时，新数据可能无法存放在原来的位置，需要移动到新的位置，并在原位置留下碎片。

这种碎片化会导致：

磁盘空间浪费：被标记为可重用的空间无法被操作系统回收，导致数据文件持续增大。
I/O 效率降低：数据不再是连续存储，查询时需要更多的磁盘 I/O 操作来获取数据，影响查询性能。

表重建的本质是一个“先复制，后替换”的过程。它通常包含以下步骤：

创建新表：在后台创建一个新的、结构相同的空表。
复制数据：将旧表中的数据以连续、紧凑的方式复制到新表中。在这个过程中，所有碎片都会被清除。
Copy data: Copy data from an old table to a new table in a continuous, compact manner. In this process, all debris is removed.
重新构建索引：在新表中，所有索引都会被重新构建，同样是连续、紧凑的。
原子替换：用一个原子的操作（例如，通过 RENAME TABLE），将旧表和新表进行互换。新表接管旧表的名称和所有权，旧表被删除。

这个过程会暂时占用额外的磁盘空间，因为新旧两份表的数据会同时存在。

如何进行表重建？

在 MySQL 中，进行表重建有几种常见的方法：

OPTIMIZE TABLE

这是最简单、最直接的表重建方式。

1	OPTIMIZE TABLE your_table_name;

工作原理：对于 InnoDB 引擎，OPTIMIZE TABLE 实际上就是执行了一次表重建操作。它会重新整理数据，回收碎片空间。
缺点：在执行期间，表会被锁定，导致无法进行写操作（INSERT, UPDATE, DELETE），在高并发场景下会影响业务。

ALTER TABLE

ALTER TABLE 语句也可以用来触发表重建，通常会与 ENGINE=InnoDB 结合使用。

1	ALTER TABLE your_table_name ENGINE=InnoDB;

工作原理：即使你没有修改表的存储引擎，执行这条命令也会强制 MySQL 以新的、紧凑的格式重新创建表，达到表重建的目的。
缺点：同样会锁定表，影响并发。

在线表重建（Online DDL）

从 MySQL 5.6 开始，InnoDB 引入了在线 DDL（Online DDL） 功能，这是一种不锁定表、不影响读写操作的表重建方式。

1	ALTER TABLE your_table_name ENGINE=InnoDB;

工作原理：MySQL 会在后台创建一个临时表并复制数据。在这个过程中，它会记录所有对旧表的修改，并在复制完成后，将这些修改应用到新表上，最后原子性地进行表替换。整个过程几乎不会中断业务。
优点：不阻塞表的读写操作，对高并发业务友好。
缺点：这个过程会消耗额外的 CPU 和 I/O 资源，并且可能会在表替换的瞬间有短暂的锁等待。

长事务问题以及如何查询长事务

长事务是指那些持续时间较长、长时间未提交的事务。它们通常被认为是数据库性能的“杀手”，因为它会占用大量资源，影响系统的稳定性和并发能力。

长事务引发的问题

资源占用与阻塞：长事务在执行期间会持有锁资源，这会阻塞其他需要访问相同数据或资源的事务，导致大量锁等待。如果一个长事务占用的锁是关键资源，整个数据库的吞吐量都会急剧下降。
Undo Log 空间膨胀：事务在修改数据时，会将旧数据版本写入 Undo Log。Undo Log 的空间只有在事务提交后才能被清理。长事务会长时间占用 Undo Log，导致 Undo Log 文件持续增大，占用大量的磁盘空间。
影响 MVCC 性能：长事务会长时间持有 Read View。其他事务在进行快照读（Snapshot Read）时，如果需要读取早于该长事务开启时的数据，就需要沿着 Undo Log 的版本链进行回溯，这会增加大量的 I/O 开销和 CPU 消耗。
死锁风险增加：长事务由于持有锁的时间长，与其他事务发生循环等待的几率也会增加，从而导致死锁。
使用 information_schema 数据库

information_schema 提供了关于数据库服务器的元数据信息，我们可以通过查询其中的 innodb_trx 表来获取当前所有正在运行的事务信息。

SELECT
    trx_id,
    trx_state,
    trx_started,
    trx_mysql_thread_id,
    trx_query,
    trx_operation_state,
    (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(trx_started)) AS trx_running_time
FROM
    information_schema.innodb_trx
WHERE
    trx_state = 'RUNNING'
ORDER BY
    trx_running_time DESC;

trx_id: 事务的唯一ID。
trx_started: 事务开始的时间。
trx_mysql_thread_id: 事务所在的 MySQL 线程ID，可以配合 SHOW PROCESSLIST 使用。
trx_query: 事务正在执行的 SQL 语句。
trx_running_time: 事务已经运行的时长（秒），这是判断长事务的关键指标。

使用 performance_schema 数据库

performance_schema 提供了更详细和实时的性能数据，包括事务和会话信息。

SELECT
    t.thread_id,
    t.processlist_id,
    t.processlist_user,
    t.processlist_host,
    t.processlist_db,
    p.event_name AS lock_type,
    p.duration AS lock_duration
FROM
    performance_schema.events_transactions_current t
JOIN
    performance_schema.events_waits_current p
    ON t.thread_id = p.thread_id
WHERE
    t.state = 'ACTIVE'
ORDER BY
    lock_duration DESC;

events_transactions_current: 提供当前活跃事务的信息。
events_waits_current: 提供线程正在等待的事件（例如锁等待）信息，可以帮助你定位长事务的原因。

使用 SHOW PROCESSLIST 命令

SHOW PROCESSLIST 可以快速查看所有正在运行的线程，这对于识别长事务非常有帮助。

1	SHOW FULL PROCESSLIST;

ID: 线程ID，对应 innodb_trx 表中的 trx_mysql_thread_id。
Time: 线程状态持续的时间（秒），如果这个值很大，可能是一个长事务。
State: 线程的当前状态，例如 Locked、Waiting for table metadata lock 等状态可能表明有长事务在阻塞其他操作。
Info: 线程正在执行的 SQL 语句。

双写缓冲与change buffer

核心目的：保证数据页的原子性写入，解决部分写失败（Partial Write Failure）问题。

在 InnoDB 中，数据最终是存储在磁盘的数据文件中的。一个数据页（通常为 16KB）写入磁盘不是一个原子操作，而是分多次进行的。如果在写入过程中，比如只写了 4KB，而数据库突然崩溃（如断电），就会导致数据页损坏。

双写缓冲机制就是为了防止这种情况：

第一次写：当 InnoDB 准备将脏页（Dirty Page，内存中被修改的数据页）刷新到磁盘时，它不会直接写入数据文件，而是先写入到双写缓冲区。这个缓冲区是一个位于系统表空间 ibdata1 中的连续存储区域。
第二次写：双写缓冲区中的数据被一次性地刷新到磁盘。
最终写：如果双写缓冲区中的数据写入成功，InnoDB 才会将脏页真正写入到数据文件的正确位置。

崩溃恢复

如果数据库在写入过程中崩溃，InnoDB 会进行以下恢复操作：

如果最终写失败：数据库重启后，会检查双写缓冲区中的数据和数据文件中的数据。如果发现数据文件中的数据页损坏，它会从双写缓冲区中找到完整的数据页副本，将其恢复到数据文件中。
如果第一次写失败：双写缓冲区中的数据也未写入，不会影响数据文件。

双写缓冲牺牲了一些性能（多了一次写操作），但极大地增强了数据页写入的可靠性。

Change Buffer（变更缓冲）

核心目的：提高数据库的写入性能，尤其是针对非唯一二级索引的插入、更新和删除操作。

当对一个表进行 INSERT、UPDATE 或 DELETE 操作时，如果涉及修改非唯一二级索引，InnoDB 的默认行为是：

修改内存中的数据页。
修改内存中的非唯一二级索引页。
将这两个脏页异步刷新到磁盘。

但如果非唯一二级索引页不在内存中，InnoDB 必须先从磁盘将索引页加载到内存，然后再进行修改。这个过程会产生大量的随机 I/O，极大地影响性能。

Change Buffer 的作用就是：

当需要修改的非唯一二级索引页不在内存中时，InnoDB 不会立即去磁盘加载，而是将这个“变更操作”记录到 Change Buffer 中**。
Change Buffer 中的数据会在后台（或在数据库关闭时）被合并（Merge）到真正的索引页中。

Change Buffer 中的内容是持久化的。数据库重启后，InnoDB 会执行恢复操作，将 Change Buffer 中的变更应用到磁盘上的索引页。

Change Buffer 主要适用于写多读少的业务场景，比如日志系统、电商秒杀系统等。因为这些场景下，非唯一二级索引的写入操作远多于读取。

注意：

Change Buffer 只对非唯一二级索引有效。对于主键索引和唯一二级索引，由于需要检查唯一性，InnoDB 必须立即将对应的索引页加载到内存中，无法使用 Change Buffer。
Change Buffer 在读多写少的场景下可能适得其反，因为大量的读取操作会频繁触发 Change Buffer 的合并操作，反而增加了开销。

临时表与sort buffer

MySQL 优化器在执行某些复杂查询时，为了提高效率，会在内部自动创建和管理临时表。

触发条件：

GROUP BY 或 ORDER BY：当这些操作的列无法被索引覆盖时。
UNION：在执行 UNION 操作时。
子查询：在处理某些复杂的子查询时。
DISTINCT：在对大量数据进行去重时。

性能开销：临时表会占用磁盘空间和内存。如果临时表数据量非常大，可能会导致 I/O 频繁，影响性能。

内存 vs. 磁盘：MySQL 会优先在内存中（tmp_table_size 和 max_heap_table_size 变量控制）创建内部临时表。如果数据量超过阈值，它会自动转换为磁盘上的 InnoDB 或 MyISAM 表，这会带来额外的 I/O 开销。

innodb_temp_data_file_path：在 InnoDB 存储引擎中，所有的用户创建的临时表和内部临时表数据都存放在一个共享的临时表空间中。你可以在 MySQL 5.7+ 中通过这个参数配置临时表空间文件。

Sort Buffer（排序缓冲区）是 MySQL 在内存中为每个需要排序的线程分配的一块缓冲区。它的主要作用是存储查询结果，然后在内存中进行排序。

当 MySQL 执行一个查询时，如果需要对结果集进行排序（例如使用了 ORDER BY 或 GROUP BY），并且无法通过索引来完成这个排序操作时，就会使用 Sort Buffer。

Using filesort 是一个明显的性能警示，它告诉我们：

查询使用了 ORDER BY 或 GROUP BY。
MySQL 无法利用索引来完成排序。
查询将使用 Sort Buffer，并且可能需要进行磁盘上的外部排序。

慢查询优化

致慢查询的原因有很多，但归根结底，都可以归结为两个核心问题：数据量大和数据访问效率低。

索引问题

这是最常见、最核心的慢查询原因。

缺少索引： 当 WHERE 子句中的条件列没有索引时，数据库为了找到符合条件的行，不得不进行全表扫描（Full Table Scan）。这意味着它会逐行检查表中的每一条记录，直到找到所有匹配的行，这在高数据量下是灾难性的。
索引失效： 即使创建了索引，也可能因为一些不当的 SQL 写法导致索引无法被使用。例如：
- 在索引列上使用函数：WHERE YEAR(order_date) = 2023。
- 在索引列上进行类型转换：WHERE user_id = '12345'（user_id 是整型）。
- 模糊查询以 % 开头：WHERE name LIKE '%john%'。
选择了错误的索引： 数据库优化器可能会因为统计信息不准确等原因，选择了不是最优的索引，导致查询性能不佳。

SQL 语句本身的问题

复杂的 JOIN： 当一个查询涉及到多个表的 JOIN 操作时，如果没有正确的索引和优化，可能会生成非常大的中间结果集，导致查询效率低下。
大批量的数据返回： SELECT * 这种查询如果返回大量不必要的列，会增加网络传输和内存开销。
不合理的 ORDER BY 和 GROUP BY： 如果 ORDER BY 或 GROUP BY 的列没有索引，数据库需要对结果集进行额外的文件排序（Using filesort） 或创建临时表（Using temporary），这会消耗大量的 CPU 和内存资源。

数据表和数据库设计问题

表结构不合理： 如果一个表设计得非常“宽”（即包含太多列），或者字段类型选择不当（例如，使用 VARCHAR 存储日期），都会影响查询性能。
没有进行分库分表： 当单表数据量达到千万甚至上亿级别时，即使有索引，也会因为索引树过大而影响查询性能。此时，分库分表是解决性能瓶颈的有效手段。

数据库配置和硬件问题

数据库配置不当： 比如 innodb_buffer_pool_size 设置过小，导致数据库无法在内存中缓存足够的热点数据，频繁地进行磁盘 I/O。
硬件瓶颈： 磁盘 I/O 速度慢、CPU 核心数不足、内存不足等硬件问题，都会直接导致查询变慢。

并发与锁问题

锁等待： 如果一个查询需要访问的行被其他长事务锁住，它就会进入等待状态，导致查询变慢。
死锁： 在复杂的并发场景下，如果发生死锁，事务会被回滚，同样会影响性能和用户体验

开启慢查询日志

首先，你需要让 MySQL 记录下这些慢查询。慢查询日志（Slow Query Log）是 MySQL 自带的诊断工具，可以记录所有执行时间超过 long_query_time 的 SQL 语句。

在 my.cnf 或 my.ini 配置文件中添加或修改以下配置：

[mysqld]
# 开启慢查询日志
slow_query_log = ON

# 设置慢查询日志文件路径
slow_query_log_file = /var/lib/mysql/mysql-slow.log

# 设置慢查询阈值（单位：秒），这里设置为 1 秒
long_query_time = 1

# 记录没有使用索引的查询
log_queries_not_using_indexes = ON

配置完成后，重启 MySQL 服务，它就会开始记录慢查询。

2.有了慢查询日志后，你需要工具来分析它。日志文件通常包含大量信息，直接阅读非常困难。使用 mysqldumpslow：MySQL 自带的工具，可以对慢查询日志进行汇总和排序，帮助你快速找出最频繁、平均执行时间最长或锁定时间最长的慢查询。

3.当你找到一个需要优化的慢查询后，下一步就是分析它的执行计划。EXPLAIN 命令可以模拟优化器执行 SQL 查询，并告诉你数据库将如何处理这个查询，包括使用了哪些索引、扫描了多少行、是否使用了临时表等。

1	EXPLAIN SELECT * FROM users WHERE age > 25 AND city = 'Shanghai';

重点关注 EXPLAIN 结果中的几个关键字段：

type：这是最重要的指标，它表示 MySQL 查找数据的方式。
- const、eq_ref：非常高效，通常是主键或唯一索引查找。
- ref、range：较好，使用了索引。
- index：全索引扫描，通常比全表扫描好。
- ALL：全表扫描，性能最差，需要重点优化。
key：实际使用的索引。如果为 NULL，表示没有使用索引。
rows：MySQL 估计要扫描的行数，值越小越好。
Extra：额外信息，其中一些值需要特别注意：
- Using filesort：表示需要额外的排序操作，通常可以创建合适的索引来避免。
- Using temporary：表示需要创建临时表，这通常是性能瓶颈。

根据 EXPLAIN 的分析结果，可以采取以下策略进行优化：

创建或优化索引：
- 为 WHERE、ORDER BY、GROUP BY 子句中使用的列创建索引。
- 使用联合索引来覆盖查询中的多个列，避免 Using filesort 和全表扫描。
- 覆盖索引（Covering Index）：如果查询的所有列都包含在索引中，MySQL 可以直接从索引中返回数据，而无需回表（访问聚簇索引），这会显著提高性能。
优化 SQL 语句：
- 避免全表扫描：检查 EXPLAIN 的 type 字段，确保没有 ALL。
- 优化 JOIN：确保 JOIN 语句的连接条件上有索引，并且小表驱动大表。
- 避免使用函数：不在索引列上使用函数（例如 WHERE YEAR(date_column) = 2023），这会导致索引失效。
- 使用 LIMIT：在查询只需要部分结果时，使用 LIMIT 限制返回行数。
更改数据库或表结构：
- 分库分表：当单表数据量过大时，考虑垂直拆分（按列）或水平拆分（按行）。
- 冗余字段：适当增加冗余字段，减少 JOIN 操作。
硬件和系统层面：
- 增加缓存：使用 Redis、Memcached 等缓存系统来缓存热点数据，减少数据库查询压力。
- 升级硬件：升级 CPU、内存或使用更快的 SSD 硬盘。
1.慢查询的解决方案

解决MySQL慢查询问题的方案可以按照资源消耗从少到多的顺序排列，像金字塔一样逐步提升。以下是从资源消耗少到多的常见优化方式：

(1)SQL优化

合理使用索引：确保查询字段使用了索引。对于WHERE、JOIN、ORDER BY、GROUP BY等操作的字段，应该创建相应的索引。

(2)索引优化

创建复合索引：对于多个字段联合查询，创建复合索引（注意索引顺序）。

删除冗余索引：定期清理无用索引，减少索引维护的负担。

避免索引覆盖不必要的字段：有时候一个大字段（如TEXT或BLOB）放入索引会增加存储开销，应该避免。

更新统计信息：定期更新表的统计信息，帮助优化器选择更合适的执行计划。

(3)数据库配置优化

调整缓存设置：增加innodb_buffer_pool_size，确保更多的数据能够缓存到内存中。调整query_cache_size，如果适用，启用查询缓存（对于更新频繁的应用不推荐）。

调整连接设置：如增加max_connections，但要注意数据库承载能力。

调整临时表大小：如果临时表经常写入磁盘，可以通过调整tmp_table_size和max_heap_table_size来避免此问题。

增加排序缓存：增加sort_buffer_size来提高ORDER BY和GROUP BY操作的效率。

(4)架构优化

分库分表：对于单表数据量过大的情况，使用分库分表策略，将数据分散到不同的数据库或表中，减少每个查询的负载。

读写分离：通过主从复制，减少主库的查询压力，读请求分发到从库。

数据库集群：使用分布式数据库系统，解决单机性能瓶颈。

(5)硬件升级

增加内存：通过增加内存，提高缓存命中率，减少磁盘IO。

更换更快的磁盘：使用SSD代替传统的硬盘，提升磁盘IO性能。

增加CPU处理能力：提升CPU性能，减少数据库查询的CPU瓶颈。

Mybatis

与传统的JDBC相比，MyBatis的优点

基于 SQL 语句编程，相当灵活，不会对应用程序或者数据库的现有设计造成任何影响，SQL 写在 XML 里，解除 sql 与程序代码的耦合，便于统一管理；提供 XML 标签，支持编写动态 SQL 语句，并可重用。
与 JDBC 相比，减少了 50%以上的代码量，消除了 JDBC 大量冗余的代码，不需要手动开关连接；
很好的与各种数据库兼容，因为 MyBatis 使用 JDBC 来连接数据库，所以只要 JDBC 支持的数据库 MyBatis 都支持。
能够与 Spring 很好的集成，开发效率高
提供映射标签，支持对象与数据库的 ORM 字段关系映射；提供对象关系映射标签，支持对象关系组件维护。

MyBatis 在 SQL 灵活性、动态 SQL 支持、结果集映射和与 Spring 整合方面表现卓越，尤其适合重视 SQL 可控性的项目。

SQL 与代码解耦，灵活可控：MyBatis 允许开发者直接编写和优化 SQL，相比全自动 ORM（如 Hibernate），MyBatis 让开发者明确知道每条 SQL 的执行逻辑，便于性能调优。
动态 SQL 的强大支持：比如可以动态拼接SQL，通过 <if>, <choose>, <foreach> 等标签动态生成 SQL，避免 Java 代码中繁琐的字符串拼接。
自动映射与自定义映射结合：自动将查询结果字段名与对象属性名匹配（如驼峰转换）。
插件扩展机制：可编写插件拦截 SQL 执行过程，实现分页、性能监控、SQL 改写等通用逻辑。
与 Spring 生态无缝集成：通过 @MapperScan 快速扫描 Mapper 接口，结合 Spring 事务管理，配置简洁高效。

Mybatis里的 # 和 $ 的区别

Mybatis 在处理 #{} 时，会创建预编译的 SQL 语句，将 SQL 中的 #{} 替换为 ? 号，在执行 SQL 时会为预编译 SQL 中的占位符（?）赋值，调用 PreparedStatement 的 set 方法来赋值，预编译的 SQL 语句执行效率高，并且可以防止SQL 注入，提供更高的安全性，适合传递参数值。
Mybatis 在处理 ${} 时，只是创建普通的 SQL 语句，然后在执行 SQL 语句时 MyBatis 将参数直接拼入到 SQL 里，不能防止 SQL 注入，因为参数直接拼接到 SQL 语句中，如果参数未经过验证、过滤，可能会导致安全问题。