MySQL

一、前言

二、基础篇

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

MySQL执行流程是怎样的？

客户端和服务器之间通信的流程

1.连接器

2.查询缓存

3.解析器 词法分析、语法分析 生成语法树

4.预处理器

5.优化器 执行计划

6.执行器

8.存储引擎 Innodb MyISAM

10.文件系统（数据）

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

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

连接器

如果你在Linux操作系统要使用MySQL，那你第一步是肯定要先连接MySQL服务，然后才能执行SQL语句，普遍我们都是使用下面这条命令进行连接：

# -h指定MySQL服务的ip地址，如果是连本地的MySQL服务，则不需要指定ip地址
# -u指定用户名，这里使用root用户
# -p指定密码，这里使用root用户的密码，为了密码安全，建议不要在命令行写密码，就在交互式对话里输入密码
mysql -h$ip -u$user -p

连接的过程需要先经过TCP三次握手，因为MySQL是基于TCP协议进行传输的，如果MySQL服务并没有启动，那么会收到报错。 如果MySQL服务正常运行，完成TCP连接的建立后，连接器就要开始验证你的用户名和密码，如果用户名或密码不对，就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。

如何查看MySQL服务被多少个客户端连接了

show processlist

空闲连接会一直占用吗

show variables like 'wait_timeout'

可以手动断开空闲的连接

kill conection + id

MySQL的连接数有限制吗

show variables like 'max_connections'

MySQL的连接也跟HTTP一样，有短连接和长连接的概念

// 短连接
连接 mysql 服务(TCP三次握手)
执行 sql
断开 mysql 服务(TCP四次挥手)

// 长连接
连接 mysql 服务(TCP三次握手)
执行sql
执行sql
执行sql
...
断开 mysql 服务(TCP四次挥手)

使用长连接的好处就是可以减少建立连接和断开的过程，所以一般都是推荐使用长连接。

但是，使用长连接后可能会占用内存增多，因为MySQL在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才释放。如果长连接累计很多，将导致MySQL服务占用内存太大，有可能会被系统强行杀掉，这样会发生MySQL服务异常重启的现象。

怎么解决长连接占用问题？

1.定期断开长连接，断开连接就会释放占用的内存。 2.客户端主动重置长连接。MySQL5.7版本实现了mysql_reset_connection()函数的接口，注意这是接口函数不是命令，当客户端执行了一个很大的操作后，在代码里调用mysql_reset_connection()函数来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接回复到刚刚创建完时的状态。

连接器的工作总结： 1.与客户端进行TCP三次握手建立连接。 2.校验客户端的用户名和密码，如果用户名或密码不对，则会报错。 3.如果用户名和密码都对了，会读取用户的权限，然后后面的权限逻辑判断都基于此时读到的权限。

查询缓存

连接器的工作完成后，客户端就可以向MySQL服务器发送SQL语句了，MySQL服务收到SQL语句后，就会解析出SQL语句的第一个字段，看看是什么类型的语句。

如果SQL是查询语句（select），MySQL就会先去查询缓存里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以key-value形式保存在内存中的，key为SQL查询语句，value为SQL语句查询的结果。

如果查询语句命中缓存，那么就会返回value给客户端，如果没有命中缓存，就会继续执行后续的步骤。

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

所以MySQL8.0直接将查询缓存删掉了，也就是说MySQL8.0开始，执行一条SQL查询语句，不会再走查询缓存这个阶段了。

对于MySQL8.0之前的版本，如果想关闭查询缓存，我们可以通过将参数query_cache_type设置成DEMAND。

tips:这里说的查询缓存是server层的，也就是MySQL8.0版本移除的是server的查询缓存，并不是InnoDB存储引擎的buffer pool

解析器

在正式执行SQL语句之前，MySQL需要先解析SQL语句。

解析器的工作过程：

1.词法分析：根据SQL语句的语法规则，将SQL语句进行解析，生成一颗对应的语法树。

MySQL会根据你输入的字符串识别出关键字来，例如，SQL语句select username from userinfo，在分析之后会得到4个token，其中2个keyword，分别为select和from：

2.语法分析：根据词法分析的结果，MySQL会判断select语句的语法是否正确。 根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法，如果没问题就会构建出SQL语法树，这样方便后面模块获取SQL类型，表名，字段名，where条件等等。

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

但是注意，表不存在或者字段不存在并不是在解析器做的《MySQL45讲》说是在解析器做的。 据说源码得出结论，解析器只负责检查语法和构建语法树

执行SQL

包括三个阶段

1.预处理阶段（prepare） 2.优化阶段（optimize） 3.执行阶段（execute）

预处理器

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

优化器

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

当然，我们本次的查询语句（select * from product where id = 1）很简单，就是选择使用主键索引。

要想知道优化器选择了哪个索引，我们可以在查询语句前面加个explain命令。这样就会输出这条SQL的执行计划。 然后执行计划中的key就表示执行过程中使用了哪个索引，比如key为primary就是使用了主键索引。

如果查询语句的执行计划key为null说明没有使用索引，那就会全表扫描，type = ALL，这种查询扫描方式的效率是最低的。

覆盖索引：直接在二级索引就查到结果（因为二级索引的B+树的叶子节点的数据存储的是主键值），就没有必要在主键索引查找了，因为查询主键索引的B+树成本会比查询二级索引的B+树成本大，优化器基于查询成本的考虑会选择查询代价小的普通索引。

执行器

经历完优化器后，就确定了执行方案，接下来MySQL就真正开始执行语句了额，这个工作是由执行器完成的。在执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。

执行器和存储引擎交互的过程有三种：

1.主键索引查询 2.全表扫描 3.索引下推

主键索引查询

select * from product where id = 1;

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

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

全表扫描

select * from product where name = 'iphone'

这条查询语句的查询条件没有用到索引，所以优化器决定选用访问类型为ALL进行查询，也就是全表扫描。 执行器与存储引擎的交互过程是这样的：

1. 执行第一次查询，会调用read_first_record函数指针指向的函数，因为优化器选择的访问类型为ALL，这个函数指针被指向为InnoDB引擎索引查询的接口，让存储引擎定位符合条件的第一条记录。
2. 执行器会判断读到的这条记录的name是不是iphone，如果不是则跳过；如果是则将记录发给客户（之所以客户端显示的时候是直接显示所有记录的，是因为客户端时等查询语句完成后，才会显示出所有的记录）。
3. 执行器的过程是一个while循环，所以还会再查一次，会调用read_record函数指针指向的函数，因为优化器选择的访问类型为all，read_record函数指针指向的还是InnoDB引擎全扫描的接口，所以接着向存储引擎层要求继续读刚才那条记录的下一条记录，存储引擎把下一条记录取出后就将其返回给执行器，执行器继续判断条件，不符合条件即跳过该记录，否则发送给客户端。
4. 一直重复上述过程，直到存储引擎把表中的所有记录读完，然后向执行器返回读取完毕的信息。
5. 执行器收到存储引擎报告的查询完毕的信息，退出循环，停止查询。

索引下推

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

举例： 对age和reward建立了联合索引 id name age reward

select * from t_user where age > 20 and reward = 100000;

联合索引当遇到范围查询(> <)就会停止匹配，也就是age字段能用到联合索引，但是reward字段则无法利用到索引。

那么，不使用索引下推（MySQL5.6之前的版本）时，执行器与存储引擎的执行流程是这样的：

• Server层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到age>20的第一条记录；
• 存储引擎根据二级索引的B+树定位到这条记录后，获取主键值，然后进行回表操作，将完整的记录返回给Server层；
• Server层在判断该记录的reward是否等于100000，如果成立则将其发送给客户端；否则跳过该记录。
• 接着继续向存储引擎要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，然后回表操作，将完整的记录返回给Server层；
• 如此往复，知道存储引擎表中所有的记录读完。

可以看到没有索引下推，每查询到一条二级索引记录，都要进行回表操作，然后将记录返回给Server，接着Server再判断该记录的reward是否等于100000。

使用索引下推后，判断记录的reward是否等于100000的工作交给了存储引擎，过程如下：

• Server层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到age>20的第一条记录；
• 在存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列的条件是否成立。如果条件不成立，则直接跳过二级索引。如果成立，则执行回表操作，将完整的记录返回给Server层；
• Server层再判断其他的查询条件（本次查询没有其他条件）是否成立，如果成立则将其发送给客户端，否则跳过该记录，然后向存储引擎索要下一条记录。
• 如此往复，直到存储引擎把表中的所有记录读完。

可以看到，使用了索引下推后，虽然reward列无法使用联合索引，但是因为它包含在联合索引（age，reward）里，所以直接在存储引擎过滤出满足100000的记录后才去执行回表操作获取整个记录。相比于没有使用索引下推，节省了很多回表操作。

当你发现在执行计划的Extra字段中出现了Using index condition，就说明使用了索引下推。

MySQL一行记录是怎么存储的

MySQL的数据存放在哪个文件

主要讨论InnoDB存储引擎

show variables like 'datadir'

/var/lib/mysql/

我们每创建一个database（数据库）都会在/var/lib/mysql/目录里创建一个以database名字的目录，然后保存表结构和表数据的文件都会存放在这个目录里。 比如有一个my_test的databse，该databse里有一张名为t_order数据库表

进入/var/lib/mysql/my_test目录，可以看到有一个t_order.frm文件和一个t_order.ibd文件，还有一个db.opt文件。

• db.opt文件：记录了数据库的一些信息，比如数据库的字符集、校验规则等。
• t_order.frm文件：记录了表的结构信息，比如字段名、类型、长度、是否为空等。
• t_order.ibd文件：记录了表的数据。

表空间文件的结构是怎么样的

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

从下往上看：

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

2.页(page) 记录是按行来存储的，但是数据库的读取并不以行为单位，否则一次读取也就是一次io操作只能处理一行，效率会非常低。

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

默认每个页大大小为16KB，也就是最多能保证16KB的连续存储空间。

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

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

3.区(extent)

我们直到InnoDB存储引擎是用B+树来组织数据的。

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

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

具体怎么解决呢？

在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配，而是按照区（extent）为单位分配。每个区的大小为1MB，也就是说一个区可以存储16KB的连续页，即64个页。这样就使得链表中相邻的五i了位置也相邻，就能使用顺序IO了。

4.段(segment)

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

索引段：存放B+树的非叶子节点的区的集合。 数据段：存放B+树的叶子节点的区的集合 回滚段：存放的是回滚数据的区的集合，MVCC利用回滚段实现了多版本查询数据。

InnoDB行格式有哪些

行格式（row_format），就是一条记录的存储结构

InnoDB提供了4种行格式：

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

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

记录的额外信息包括

变长字段长度列表，NULL值列表，记录头信息

1.变长字段长度列表 varchar(n) 和 char(n)的区别是什么，char是定长的，varchar是变长二点，变长字段实际存储的数据的长度不固定的。 所以在存储数据的时候，也要把数据占用的大小存起来，存在变长字段长度列表里。读取数据的时候才能根据这个变长字段长度列表去读取对应长度的数据。其他TEXT，BLOB等变长字段也是这么实现的。

为什么变长字段长度列表的信息要按照逆序存放

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

这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以在同一个CPU Cache Line中，这样就可以提高CPU Cache的命中率。

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

其实变长字段字节数列表不是必须的。 当数据表没有变长字段的时候，比如全部都是int类型的字段，这时候表里的行格式就不会有

2.NULL值列表

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

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

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

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

3.记录头信息

记录头信息有很多，举例几个重要的：

• delete_mask：删除标记
• next_recoard：指向下一条记录的指针
• record_type：记录类型

记录的真实数据

记录真实数据除了我们定义的字段，还有三个隐藏字段：row_id，trx_id，roll_pointer

row_id： 如果建表制定了主键或唯一约束列，那么就没有row_id隐藏字段。如果没有指定主键，又没有唯一约束，那么InnoDB就会为记录添加row_id隐藏字段。不是必须的，占用6个字节

trx_id: 事务id，表示这个数据是由哪个事务生成的，trx_id是必须的，占用6个字节

roll_pointer: 这条记录上的上一个版本的指针。roll_pointer是必须的，占用7个字节。

varchar(n)中的n最大取值为多少

MySQL规定除了TEXT，BLOB这种大对象类型之外，其他所有列占用的字节（不包括隐藏列和记录头信息）加起来不超过65535个字节 一行数据的大小不能超过65535字节，varchar(n)的n不能超过65532个字节。

varchar(n)中的n指的是最多存储的字符数量，并不是字节大小哦。

asscii 1个字符占用一个字节，varchar(100)意味着最大能允许100字节的数据。

行溢出后，MySQL是怎么处理的

一个页存储不了TEXT,BLOB等大对象，那么就会把大对象单独存储到溢出页中。 当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在溢出页，真实数据处用20字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。

索引篇

索引常见问题

索引分类

按数据结构分类

• B+树索引
• Hash哈希索引
• full-text全文索引

按物理存储分类

• 聚簇索引
• 二级索引

按字段特性分类

• 主键索引
• 唯一索引
• 前缀索引

按字段个数分类

• 单列索引
• 联合索引

主键索引的B+Tree叶子节点存放的是实际数据。 二级索引的B+Tree叶子节点存放的是主键值。

回表 先查二级索引的主键值，再根据主键值查对应的数据。也就是说要查两个B+Tree。

当查询的数据能在二级索引的B+Tree的叶子节点查询到，就不用再查主键索引查，这种二级索引的B+Tree叫覆盖索引。

为什么要选B+Tree作为索引的数据结构？

1.B+Tree vs B Tree B+Tree只在叶子节点存储数据，而B Tree在非叶子节点也存储数据。所以B+Tree单个节点的数据量更小，在相同的磁盘IO次数下，就能查询更多节点，另外，B+Tree叶子节点采用的是双链表连接，适合MySQL中常见的基于范围的顺序查找，而B树无法做到这一点。

2.B+Tree vs 二叉树 对于有N个叶子节点的B+Tree其搜索复杂度为O(logdN)，其中d表示节点允许的最大子节点的个数为d个，在实际应用当中，d值大于100的，这样就保证了，技术数据达到千万级别，B+Tree的高度依旧维持在3~4层，也就是说一次数据查询操作只需要做3~4次磁盘IO操作就能查询到目标数据。

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

3.B+Tree vs Hash Hash在做等值查询的时候效率贼快，搜索复杂度为O(1)。 但是Hash表不支持范围查询，Hash表不支持排序。

联合索引： 通过将多个字段组成一个索引，该索引就称为联合索引

最左匹配原则： 比如： 创建了一个(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;

上面这些查询条件之所以会失效，是因为(a，b，c)联合索引，是先按a排序，在a相同的情况下再b排序，在b相同的情况下再按c排序。所以，b和c是全局无序，局部相对有序的，在没有遵循最左匹配原则的情况下，是无法利用索引的。

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

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

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

什么时候适用索引？

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

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

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

有什么优化方法

• 前缀索引优化
• 覆盖索引优化
• 主键索引最好是自增的
• 防止索引失效

前缀索引优化

前缀索引顾名思义就是适用某个字段中的字符串的前几个字符建立索引，为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时候，适用前缀索引可以帮助减小索引项的大小。 不过前缀索引有一定局限性，例如：

• order by 无法使用前缀索引
• 无法把前缀索引用作覆盖索引

覆盖索引优化

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

主键索引最好是自增的

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

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

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

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

索引最好设置为NOT NULL

为了更好利用索引，索引列要设置为 NOT NULL 约束。

• 第一个原因：索引列存在NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为NULL的列会使索引，索引统计和值比较都更加复杂，比如进行索引统计时候，count会省略为NULL的行。
• 第二个原因：NULL是一个没有意义的值，但是它会占用物理空间，所以会带来存储空间的问题，因为InnoDB存储记录的时候，如果表中存在允许为NULL的字段，那么行格式中至少会用1字节空间存储NULL值列表

防止索引失效

• 最左匹配原则
• 索引列上不能使用函数
• like百分放最右
• 不等空值还有or

口诀：

• 全值匹配我最爱，最左匹配要遵守
• 带头大哥不能死，中间兄弟不能断
• 索引列上少计算，范围之后全失效
• like百分放最右，覆盖索引不写星
• varchar引号不能丢，不等空值还有or

explain

type字段： const表示使用了主键或唯一索引与常量值作比较，比如select name from product where id = 1； const和eq_ref都使用了主键或唯一索引，不过这两个类型有所区别，const是与常量做比较，查询效率快，而eq_ref通常用于多表联查。

除了关注type，也要关注extra

• Using index：表示查询时，只需要使用索引树中的信息，而无需回表查询实际的表数据。
• Using where：表示回表查询，即需要回表查询实际的数据。
• Using filesort：表示需要排序，比如order by语句。
• Using temprary：表示需要创建临时表，比如group by语句。

数据页角度看B+树

InnoDB是如何存储数据的

记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取（也就是一次IO操作）只能处理一行数据，效率会非常低。 InnoDB的数据是按数据页为单位来读写的，以页作为磁盘和内存之间交互的基本单位，页的大小一般为16KB。 读取和写入都是以页为单位，一次读取或者写入的数据页中包含了多个记录。

数据页包括：（16KB）

• 文件头File Header（38字节）：文件头，表示页的信息
• 页头（56字节）：页头，表示页的状态信息
• 行记录
  • 最大最小记录（26字节）：两个虚拟的伪记录，分别表示页中的最小记录和最大记录
  • 用户记录（不确定）：存储行记录内容
• 空闲空间（不确定）：页中还没有被使用的空间
• 页目录（不确定）：存储用户记录的相对位置，对记录起到索引作用
• 文件尾（8）：校验页是否完整

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

数据页中有一个页目录，起到记录的索引作用。

页目录就是由多个槽组成，槽相当于分组记录的索引。通过槽查找记录时候，使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到相应的记录。

B+Tree是符合进行查询的

InnoDB里的B+Tree中每一个节点都是一个数据页

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

B+Tree如何快速查找主键为6的记录：

• 从根节点开始，二分法，6在[1,7)之间，所以到页30中查找更详细目录项
• 在非叶子节点（页30），继续二分法，主键大于5，到叶子节点（页16）查找记录
• 在叶子节点（页16）中，通过槽查找记录，二分法定位在哪个槽，遍历槽内所有记录，找到主键为6的记录

先二分法定位页，定位页后，再二分法定位槽，在分组内遍历查找

聚簇索引和二级索引

索引可以分为聚簇索引和二级索引。 区别在于叶子节点存放的是什么数据：

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

为什么MySQL采用B+Tree作为索引

怎样的索引数据结构是好的

磁盘访问慢，内存访问纳秒级，磁盘访问毫秒级 磁盘读写最小单位是扇区，扇区大小只有512B，操作系统一次性读写多个扇区，操作系统最小读写单位是块（Block），Linux的块大小为4KB，也就是一次磁盘IO操作会直接操作读写8个扇区。

数据库索引保存在磁盘，通过索引查找行数据，需要先从磁盘读取索引到内存，再通过索引从磁盘找到某行数据，然后读入内存，io越多，消耗的时间越多。

所以希望索引的数据结构尽可能少的磁盘IO，另外MySQL支持范围查找，索引索引的数据结构不仅要高效的查询某一条记录，也要高效的执行范围查找。

要设计一个适合MySQL索引的数据结构，需要满足以下要求：

• 尽可能少的磁盘IO操作中完成查询工作
• 能高效的查询某一个记录，也要高效的执行范围查找

什么是二分查找

索引数据最好按顺序排列，这样可以用二分查找法高效定位数据。

什么是二分查找树

用数组来实现线性排序的数据虽然简单好用，但是插入新元素的性能太低

二叉查找树的特点是一个节点左子树的所有都小于这个节点，右子树的所有节点都大于这个节点

当每次插入的元素都是二叉查找树中最大的元素，二叉查找树就会退化成一条链表，查找数据的时间复杂度变成了O（n）

什么是自平衡二叉树

平衡二叉树的特点是左右子树的高度差不超过1，如果插入新元素后，二叉查找树高度差大于1，那么需要调整二叉查找树的顺序。

在二叉查找树的基础上增加了一些条件约束：每个节点的左子树和右子树高度差不能超过1

还有很多自平衡的二叉树，比如红黑树。 通过左旋，右旋达到自平衡

不管平衡二查找树还是红黑树，都会随插入的元素增多，导致树的高度变高，这就意味着磁盘IO操作次数多，会影响整体数据查询效率

当树的节点越多，并且树的分叉树M越大时候，M叉树的高度远小于二叉树的高度。

什么是B树

B树是一种平衡的多路查找树，每个节点最多有M个孩子，M值越大，树的高度越低，IO次数越少。

B树每个节点都包含数据（索引+记录），而用户的记录数据的大小很有可能远远超过了索引数据，这就需要花费更多的磁盘IO操作次数来督导有用的索引数据。

什么是B+树

B+树是B树的变种，B+树每个节点都包含索引和记录，叶子节点之间通过指针连接。 非叶子节点的索引也会同时存在子节点中，并且是在子节点中所有索引的最大或最小值。 非叶子节点中有多少个子节点，就有多少索引

单点查询

B+树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储索引又存记录的B树，B+树的非叶子节点可以存放更多的索引，因此B+树可以比B树更矮胖，查询底层节点的磁盘IO次数会更少。

插入和删除效率

B+树有大量的冗余节点，这样使得删除一个节点的时候，可以直接从叶子节点删除，甚至可以不动非叶子节点，这样删除非常快。

B树则不同，B树没有冗余节点，删除节点的时候非常复杂，比如删除根节点中的数据，可能涉及复杂的树变形

因此B+树的插入和删除效率更高

范围查询

B+树所有叶子节点间还有一个链表进行连接，这种涉及对范围查找非常有帮助。

MySQL中的B+树

InnoDB使用的B+树有一些特别的点：

• B+树的叶子节点是用双向链表进行连接，这样的好处是既能向左遍历，又能向右遍历
• B+树节点内容是数据页，数据页存放了用户的记录以及各种信息，每个数据页默认大小是16KB。

MySQL单表不要超过2000w行，靠谱吗

由树层级和页数据能存的数据量决定的

非叶子节点内指向其他页的数量为 x 叶子节点内能容纳的数据行数为 y B+ 数的层数为 z

Total = x ^ (z - 1) * y

保持相同层级情况下，在行数据大小不同的情况下，其实这个最大建议值也是不同的，而且影响查询性能的还有很多其他因素，比如数据库版本，服务器配置，sql编写

MySQL为了提高性能，会将表的索引装载到内存，在InnoDB buffer size足够的情况下，其能完全加载进内存。

单表数据库达到某个量级上限时，导致内存无法存储其索引，使得之后SQL查询会产生磁盘IO，从而导致性能下降，所以增加硬件配置，可能带来立竿见影的性能提升。

索引失效

全值匹配我最爱，最左匹配要遵守 带头大哥不能四，中间兄弟不能断 索引列上少计算，范围之后全失效 like百分放最右，覆盖索引不写星 varchar引号不能丢，不等空值还有or

MySQL使用like"%x"索引就一定失效吗

最左模糊匹配like '%xx'不一定全表扫描，关键看数据表中的字段，如果是覆盖索引，那么使用索引，否则全表扫描

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

性能排序： count(*) = count(1) > count(主键字段) > count(字段)

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

count(主键字段)执行过程 如果只有主键索引没有二级索引，那么InnoDB循环遍历聚簇索引，将读取到的记录返回给server层，然后读取记录中的id值，判断是否为NULL，不为NULL则累加。 如果表中有二级索引，那么InnoDB会先通过二级索引找到主键值，然后根据主键值去聚簇索引中查找记录。

count(1)执行过程是怎样的 select count(1) from t_order 如果表里只有主键索引，没有二级索引时 那么InnoDB循环遍历聚簇索引（主键索引），将读到的记录返回给server层，但是不会读取记录中的任何字段的值，因为count函数的参数是1，不是字段，所以不需要读取记录中的字段值。参数1很明显不是NULL，所以每从InnoDB读取到一条记录，就count+1. count(1)比count(主键字段)少一个步骤，不需要读取记录中的字段值，通常count(1)执行效率比count(主键字段)高一点。 如果表里有二级索引，InnoDB循环遍历对象就是二级索引了。

count()的执行过程是怎样的 看到这个字符，是不是大家觉得读取记录中的所有字段？ 对于select * 这条语句是这个意思，但是count()中并不是这个意思。 其实count()等于count(0)，也就是说使用count()时，MySQL会将转化为参数0来处理

count(字段)执行过程是怎样的 count(字段)的执行效率是最差的 select count(name) from t_order 会采用全表扫描的方式来计数。

为什么要通过遍历的方式计数

MyISAM有一个meta信息存储了row_count的值，由表所保证一致性，所以直接读取row_count的值即可。 InnoDB是支持事务的，同一时刻多个查询，由于多版本并发控制，InnoDB应该返回多少行是不确定的。

带上where条件语句，MyISAM和InnoDB就没有区别了。

如何优化count(*)

对大表使用count(*)做统计是不好的

第一种，近似值

可以使用show table status 或者 explain

第二种，使用额外表保存计数值

计算这个数字保存到单独的一张计数表中

事务篇

事务的隔离级别是怎么实现的

转账业务所有数据库操作是不可分割的，要么全执行成功，要么全部失败，不允许出现中间状态的数据

事务有哪些特性

原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成 一致性（Consistency）：事务完成时，必须保证数据库处于一致状态 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行 持久性（Durability）：事务完成之后，它对数据库的修改应该永久保存下来

InnoDB如何保证 持久性是通过redo log来保证 原子性是通过undo log来保证 隔离性是通过MVCC和锁机制保证 一致性是通过持久性+原子性+隔离性保证

并行事务会引发什么问题

在同时处理多个事务的时候，可能出现脏读，不可重复读，幻读

脏读 如果一个事务读到了另一个未提交事务的数据，那么这个数据就是脏数据

不可重复度 在一个事务内多次读取同一个数据，出现前后两次读到的数据不一样的情况，就是不可重复读

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

事务的隔离级别有哪些

• 读未提交
• 读已提交
• 可重复度（MySQL InnoDB引擎默认的隔离级别），解决幻读，但并不是完全解决
• 串行化

MySQL InnoDB可重复读

• 针对快照读（普通select语句），通过MVCC解决幻读
• 针对当前读（select ... for update语句，会加上next-key lock）间隙锁

Read View在MVCC里如何工作

• Read View 中四个字段作用
• 聚簇索引记录中两个跟事务有关的隐藏列

Read View是个什么东西？

四个重要字段：

• mids: 指的是在创建Read View时，当前数据库中【活跃事务】的事务id列表，注意是一个列表，“活跃事务”指的是，启动了但还没有提交的事务。
• min_trx_id：指的是在创建Read View时，当前数据库中【活跃事务】的 事务id最小的事务，也就是m_ids的最小值。
• max_trx_id：这个并不是m_ids的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的id指，也就是全局事务中最大的事务id+1
• creator_trx_id：指的是创建该Read View的事务的事务id

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

创建Read View后，可以将记录中的trx_id划分为三种情况：

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

• 如果记录的 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

假设事务A （事务id为51）启动后，紧接着事务B（事务52）也启动了，那么着两个事务创建的Read View如下：

• 事务B读取账户余额记录，读到的余额时100w
• 事务A将账户余额改成200w，并没有提交事务
• 事务B读取账户余额，读到余额还是100w
• 事务A提交事务
• 事务B读取账户余额，读到的余额依然是100w

事务B第一次读账户余额记录，在找到记录后，会先看这条记录的trx_id，此时发现trx_id为50，比事务B的Read View的min_trx_id值（51）海啸，意味着修改这条记录的事务早在事务B启动前提交过了，所以该版本的记录对事务B可见

接着事务A通过update语句将这条记录修改（还未提交事务），将余额改成200w，这时MySQL会记录相应的undo log，并以链表方式串联起来，形成版本链

然后事务B第二次去读取该记录，发现这条记录的trx_id值为51，在事务B的Read View的min_trx_id和max_trx_id之间，需要判断trx_id是否在m_ids范围内，判断的结果是在的，那么说明这条记录是被还未提交的事务修改的，这时事务B并不会读取这个版本的记录，而是沿着undo log链条往下找旧版本的记录，直到找到trx_id小于事务B的ReadView中的min_trx_id值的第一条记录，所以事务B能读到的是trx_id为50的记录，也就是余额为100w的记录

最后，当事务A提交事务后，由于隔离级别为可重复读，所以事务B再次读取记录时，还是基于启动事务时创建的Read View来判断当前版本的记录是否可见。所以，即使事务A将余额改成200w并提交事务，事务B第三次读取记录时，读到的记录都时余额100的记录

读提交是如何工作的

读提交隔离级别是在每次读取数据时，都生成一个新的Read View。

MySQL可重复读隔离级别完全解决幻读了吗

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

快照读是通过MVCC机制 当前读是通过间隙锁

没有完全解决幻读 一、查询不到别人插入的数据，但是可以更新，再次查询就可以看到别的事务插入的记录了。 二、A先快照读，B插入，A再执行for update就会得到4条记录。 要避免这类场景下发生幻读，尽量再开启事务之后，马上执行select ... for update这类当前读的语句。

锁篇

MySQL有哪些锁

全局锁

flush tables with read lock 执行后，整个数据库就处于只读状态了 以下操作都会被阻塞： 对数据的增删改操作：insert，delete，update 对表结构的更改操作：alter table，drop table

unlock tables 释放锁

全局锁的应用场景：全库逻辑备份 业务只能读数据，不能更新数据，会造成业务停滞

可以通过可重复读避免。

mysqldump -single-transaction

表级锁

表锁

// 表级共享锁，读锁 lock tables t_student read;

// 表级独占锁，写锁 lock tables t_student write;

unlock tables

表锁粒度太大，InnoDB牛逼的地方在于实现了颗粒度更细的行级锁。

元数据锁（MDL）

不需要显示使用MDL，用户对数据库表操作时，会自动给这个表加MDL

对一张表CRUD，加的是MDL读锁 对一张表结构变更时，加的是MDL写锁

MDL在事务提交后才释放。

申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁

意向锁

InnoDB引擎的表里对某些记录加上【共享锁】之前，需要现在表级别加上一个【意向共享锁】 InnoDB引擎的表里对某些记录加上【独占锁】之前，需要现在表级别加上一个【意向独占锁】

也就是当执行插入，更新，删除操作，需要先对表加上意向独占锁，然后对该记录加独占锁。

而普通的select是不会加行级锁的，普通select语句是利用MVCC实现一致性的，是无锁的。

不过select也可以对记录加共享锁和独占锁

select ... lock in share mode select ... for update

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁和独占表锁发生冲突。

AUTO-INC锁

表里的主键通常会设置成自增的，通过对主键声明 AUTO_INCREMENT 属性实现的

之后可以在插入数据时候，可以不指定主键值，数据库自动给主键赋值递增的值。

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

在插入数据时，会加一个表级别的 AUTO-INC 锁

MySQL5.1.22之后，InnoDB存储引擎提供了一种轻量级别的锁来实现递增

一样也是在插入数据的时候，会为被AUTO_INCREMENT修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行后才释放锁。

行级锁

InnoDB支持行级锁，MyISAM不支持行级锁

Record Lock

记录锁，锁住的是一条记录，记录锁是有S锁和X锁之分的

• 当一个事务对一条记录加了S锁后，其他事务也可以继续对该记录加S锁，但是不可以对该记录加X锁
• 当一个事务对一条记录加了X锁，其他事务不可以加S锁也不可以加X锁

Gap Lock

间隙锁：只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读现象

Next-Key Lock

临键锁： 是Record Lock + Gap Lock的组合，锁定一个范围，并且锁定记录本身

插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已经被其他事务加了间隙锁，如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的哪个事务提交位置，在此期间会生成一个意向锁，表明有事务想在某个区间插入新纪录，但是现在处于等待状态。

插入意向锁虽然名字有意向锁，但它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。

MySQL是怎么加锁的

MySQL是怎么加行级锁的？

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

next-key lock在一些场景下会退化成记录锁或间隙锁

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

唯一索引等值查询

「唯一索引」是用「主键索引」作为案例说明的，加锁只加在主键索引项上。

然后，误以为如果是二级索引的「唯一索引」，加锁也是只加在二级索引项上。

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

唯一索引范围查询

范围查询和等值查询的加锁规则是不同的。

非唯一索引等值查询

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

非唯一索引范围查询

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

没有索引的查询

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

update没有索引会锁全表？

在 update 语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。

怎么避免 我们可以将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式。

大致的意思是，当 sql_safe_updates 设置为 1 时。

update 语句必须满足如下条件之一才能执行成功： 使用 where，并且 where 条件中必须有索引列； 使用 limit； 同时使用 where 和 limit，此时 where 条件中可以没有索引列；

delete 语句必须满足以下条件能执行成功： 同时使用 where 和 limit，此时 where 条件中可以没有索引列；

MySQL记录锁+间隙锁可以防止删除操作而导致幻读吗

MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读吗？ 答案是可以的。

MySQL死锁了，怎么办

为什么会产生死锁？

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待

怎么避免

• 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。
• 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

加了什么锁，导致死锁的

日志篇

MySQL日志：undo log，redo log，binlog有什么用

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

为什么需要undo log

执行一条“增删改”语句时候，虽然没有输入begin开启事务和commit提交事务，但是MySQL会隐式开启事务，每次在事务执行过程中，都记录下回滚时需要的信息到一个日志里，那么在事务中途发生了MySQL崩溃后，就不用担心无法回滚到事务之前的数据，我们可以通过这个日志回滚到事务之前的数据。

它保证了事务ACID特性中的原子性

另外，undolog还有一个作用，通过Read View + undo log实现MVCC（多版本并发控制）

为什么需要Buffer Pool

MySQL的数据都是存在磁盘中的，那么我们要更新一条记录的时候，要先从磁盘读取该记录，然后在内存中修改这条记录，那修改完这条记录时选择直接写回磁盘还是选择缓存起来呢，当然是缓存起来好，下次有查询语句命中这条记录，直接读取缓存中的记录，就不需要从磁盘获取数据了。

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

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

在MySQL启动的时候，InnoDB会为Buffer Pool申请一片连续的内存空间，然后按照默认的16KB大小划分出一个个页，Buffer Pool中的页就叫做缓存页。

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

为什么需要redo log

Buffer Pool是提高了读写效率没错，但是Buffer Pool是基于内存的，而内存总是不可靠的，万一断电重启，还没来得及罗盘的脏页数据就会丢失。

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

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

WAL技术指的是，MySQL的写操作并不是立刻写道磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

什么是redo log？ redo log是物理日志，记录了某个数据页做了什么修改，比如对XXX表空间中的YYY数据页ZZZ偏移量的地方做了AAA个更新，每执行一个事务就会产生这样的一条或多条物理日志

在事务提交时，只要先将redo log持久化到磁盘即可，可以不需要等到将缓存在Buffer Pool里的脏页数据持久化到磁盘。

当系统崩溃时，虽然脏页数据没有持久化，但是redo log已经持久化，接着MySQL重启后，可以根据redo log的内容，将所有数据恢复到最新的状态。

被修改Undo页需要记录对应redo log吗 需要的

redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值； undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

crash-safe

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

产生的 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 什么时候刷盘？

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

为什么需要 binlog

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志

为什么有了 binlog， 还要有 redo log？ 这个问题跟 MySQL 的时间线有关系。 最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。 而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用 redo log 来实现 crash-safe 能力。

redo log 和 binlog 有什么区别？ 1、适用对象不同 2、文件格式不同 3、写入方式不同： 4、用途不同：

主从复制是怎么实现？

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

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

binlog 什么时候刷盘？

内存篇

揭开 Buffer Pool 的面纱

为什么要有Buffer Pool

虽然说MySQL的数据是存储在磁盘里，但也不能每次都从磁盘里面读取数据，这样性能极差。

要向提升查询性能，加个缓存就行了嘛。所以，当数据从磁盘中取出来后，缓存内存中，下次查询同样的数据的时候，直接从内存中读取。

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

Buffer Pool有多大

Buffer Pool是在MySQL启动的时候，向操作系统申请的一片连续的内存空间，默认配置下Buffer Pool只有128MB。

可以通过调整innodb_buffer_pool_size参数来设置Buffer Pool的大小，一般建议设置成可用物理内存的60% - 80%

Buffer Pool缓存什么？

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

如何管理Buffer Pool

如何管理空闲页

Buffer Pool是一片连续的内存空间。当MySQL运行一段时间后，这片连续的内存空间中缓存页既有空闲的，也有被使用的。

那当我们从磁盘读取数据的时候，总不能通过遍历这一片连续的内存空间来找到空闲的缓存页，这样效率太低了。

脏页什么时候被刷入磁盘

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