03-MySql高阶篇-阿里云开发者社区

## 一.Mysql 基础与体系结构

1.什么是 Mysql

MySQL 是最流行的开源 SQL 数据库管理系统，由 Oracle 公司开发、分发和支持。Mysql 网站

MySQL 是一种关系型数据库管理系统，其全称为“My Structured Query Language”。

MySQL 是一个数据库管理系统;
MySQL 数据库是关系型数据库;
MySQL 是一种开源软件，可以在多个操作系统下使用；
MySQL 数据库服务器非常快速、可靠、可扩展且易于使用;
MySQL 支持多种存储引擎，如 MyISAM 和 InnoDB 等；
MySQL 具有强大的安全性和可靠性，可以通过 SSL 等加密方式进行数据传输。

2.内部结构和便携性

用 C 和 C++ 编写。
适用于许多不同的平台。
采用多层服务器设计，模块独立。
提供事务性和非事务性存储引擎。
使用非常快的 B 树磁盘表 ( MyISAM) 和索引压缩。
实现内存中的哈希表，用作临时表。
使用高度优化的类库实现 SQL 函数，该类库应该尽可能快。

3.Mysql8.0 新增功能

MySQL 8.0 是 MySQL 数据库管理系统的一个重要版本，它在 2018 年 4 月发布。以下是 MySQL 8.0 中的一些主要新增功能和改进：

支持窗口函数：引入了窗口函数（Window Functions），允许在查询中进行更复杂的数据分析和聚合操作。
CTE（Common Table Expressions）：引入了公共表表达式，允许在查询中创建临时结果集，并在后续查询中引用它们。
新增降序索引，可以支持降序的索引。
增强的安全性：引入了更多的安全功能，如更强大的密码验证策略，支持 TLSv1.3 加密协议等。
更好的 JSON 支持：增加了更多的 JSON 函数和操作符，使得在存储和查询 JSON 数据更加方便和高效。
调优器改进：优化了查询优化器，使得一些查询在 MySQL 8.0 上执行更加高效。
新的数据类型：新增了几种数据类型，例如 GEOMETRY 类型的空间数据支持，以及更好的 UUID 支持。
InnoDB 存储引擎改进：提供了更好的性能和可伸缩性，包括通过数据字典提高元数据的效率。
新的字符集和排序规则：增加了新的 Unicode 字符集和排序规则，提供更好的全球化支持。
自适应哈希索引：InnoDB 引擎中的自适应哈希索引可以提高特定查询的性能。
Group Replication：引入了 MySQL Group Replication，提供了原生的多主复制特性，使得多个 MySQL 实例可以组成一个高可用性和容错性的集群。

4.数据库与实例?

数据库与实例:

数据库：物理操作系统文件或其他形式文件类型的集合。在 MySQL 数据库中，数据库文件可以是 frm、MYD、MYI、ibd 结尾的文件。当使用 NDB 引擎时，数据库的文件可能不是操作系统上的文件，而是存放于内存之中的文件，但是定义仍然不变。
实例：MySQL 数据库由后台线程以及一个共享内存区组成。共享内存可以被运行的后台线程所共享。需要牢记的是，数据库实例才是真正用于操作数据库文件的。

从概念上来说，数据库是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合合；

数据库实例是程序，是位于用户与操作系统之间的一层数据管理软件，用户对数据库数据的任何操作，包括数据库定义、数据查询、数据维护、数据库运行控制等都是在数据库实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。

5.mysql 体系结构

从下图可以看出， MySQL 主要由下面几部分构成：

连接器： 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器： 按照 MySQL 认为最优的方案去执行。
执行器： 执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
插件式存储引擎 ：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

网络异常，图片无法展示

6.其他存储引擎?

innodb:支持行锁，支持事务
myisam:不支持行锁，支持表锁，不支持事务
NDB:集群存储引擎，将数据全部放在内存中，不是磁盘中
Memory:数据存放在内存，适合纬度表，使用 hash 索引
Archive:只支持 insert 和 update 操作.高速的插入和压缩
Maria:myisam 的后续版本

7.数据库语言

① 数据查询语言（Data Query Language，DQL）：

DQL 主要用于数据的查询，其基本结构是使用 SELECT 子句，
FROM 子句和 WHERE 子句的组合来查询一条或多条数据。

② 数据操作语言（Data Manipulation Language，DML）：

DML 主要用于对数据库中的数据进行增加、修改和删除的操作，其主要包括：
INSERT：增加数据
UPDATE：修改数据
DELETE：删除数据

③ 数据定义语言（Data Definition Language，DDL）：

DDL 主要用针对是数据库对象（表、索引、视图、>触发器、存储过程、函数、表空间等）进行创建、修改和删除操作。其主要包括：
CREATE：创建数据库对象
ALERT：修改数据库对象
DROP：删除数据库对象

④ 数据控制语言（Data Control Language，DCL）：

DCL 用来授予或回收访问数据库的权限，其主要包括：
GRANT：授予用户某种权限
REVOKE：回收授予的某种权限

⑤ TCL 用于数据库的事务管理。其主要包括：

START TRANSACTION：开启事务
COMMIT：提交事务
ROLLBACK：回滚事务
SET TRANSACTION：设置事务的属性

SQL 语言共分为以下几大类：查询语言 DQL，控制语言 DCL，操纵语言 DML，定义语言 DDL。事务控制 TCL.

DQL（Data QUERY Languages）语句：即数据库定义语句，用来查询 SELECT 子句，FROM 子句，WHERE 子句组成的查询块，比如：select–from–where–grouop by–having–order by–limit
DDL（Data Definition Languages）语句：即数据库定义语句，用来创建数据库中的表、索引、视图、存储过程、触发器等，常用的语句关键字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。增删改表的结构
DML（Data Manipulation Language）语句：即数据操纵语句，用来查询、添加、更新、删除等，常用的语句关键字有：SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE,包括通用性的增删改查。增删改表的数据
DCL（Data Control Language）语句：即数据控制语句，用于授权/撤销数据库及其字段的权限（DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.）。常用的语句关键字有：GRANT,REVOKE。
TCL（Transaction Control Language）语句：事务控制语句，用于控制事务，常用的语句关键字有：COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION。

8.什么是异步 IO?

为了提高磁盘操作性能，当前的数据库系统都采用异步 IO(Asynchronous Input/Output，AIO)的方式来处理磁盘操作。InnoDB 存储引擎亦是如此。与 AIO 对应的是 Sync IO，即每进行一次 IO 操作，需要等待此次操作结束才能继续接下来的操作。

异步IO:用户可以在发出一个 IO 请求后立即再发出另一个 IO 请求，当全部请求发送完毕后，等待所有 IO 操作的完成，这就是 AIO。

AIO 的另一个优势是可以进行 IO Merge 操作，也就是将多个 IO 合并为 1 个 IO，这样可以提高 IOPS 的性能。例如用户需要访问页的(space，page_no)为以下页，每个页的大小为 16KB，那么同步 IO 需要进行 3 次 IO 操作。而 AIO 会判断到这三个页是连续的(显然可以通过(space，page_no)得知。因此 AIO 底层会发送一个 IO 请求，从(8，6)开始，读取 48KB 的页。

(8，6)、(8，7)，(8，8)

9.QPS 和 TPS

QPS（Queries Per Second）和 TPS（Transactions Per Second）是衡量系统性能的指标，用于描述系统每秒处理的查询或事务数量。虽然它们在很多情况下是类似的概念，但在某些情况下有一些细微的区别。

QPS（Queries Per Second）：QPS 是指系统每秒处理的查询请求数量。这个指标通常用于描述数据库、Web 服务器、缓存服务器等系统的查询吞吐量。在数据库中，一个查询可以是一个 SELECT、UPDATE、INSERT 或 DELETE 操作。
TPS（Transactions Per Second）：TPS 是指系统每秒处理的事务数量。这个指标通常用于描述事务性应用程序（如银行交易、在线支付等）的处理能力。在这种情况下，一个事务通常涉及多个查询和更新操作，因此 TPS 通常比 QPS 更小。

需要注意的是，QPS 和 TPS 都是衡量系统处理能力的指标，但它们不能独立地表示系统性能的好坏。实际上，系统的性能取决于很多因素，包括硬件配置、软件优化、网络延迟等等。

10.数据库三大范式

数据库三大范式是关系数据库设计中的规范化原则，旨在减少数据冗余和提高数据的一致性。这些范式被命名为第一范式（1NF）、第二范式（2NF）和第三范式（3NF）。以下是它们的简要介绍：

第一范式（1NF）：

数据表中的每个列都应该包含原子性的值，即每个列不能包含多个值或重复的值。
每个表必须有一个主键来唯一标识每一行。

第二范式（2NF）：

在 1NF 的基础上，要求每个非主键列完全依赖于整个主键，而不是部分依赖。
如果一个表的主键由多个列组成，则每个非主键列必须依赖于所有主键列，而不是只依赖于部分主键列。

第三范式（3NF）：

在 2NF 的基础上，要求每个非主键列之间没有传递依赖关系。
换句话说，如果一个非主键列依赖于另一个非主键列，那么这两个非主键列都应该成为一个新的表，并且非主键列与主键之间建立关联。

通过遵循这些范式，可以保持数据库结构的合理性和一致性，避免冗余数据和数据更新异常，提高数据库的性能和可维护性。需要注意的是，在实际设计数据库时，有时需要根据具体的业务需求和查询优化等因素来权衡是否严格遵循三大范式。有时会允许部分冗余数据以提高查询性能或简化查询操作。

11.什么是视图?

在 MySQL 数据库中，视图(View)是一个命名的虚表，它由一个 SQL 查询来定义，可以当做表使用。与持久表(permanent table)不同的是，视图中的数据没有实际的物理存储。起到安全层的作用，不用关心原表的全部字段。

可更新视图:视图是可以被更新的.根据视图更新基本表。

物化视图:存在物理存储。

12.常见约束

在 MySQL 中，常见的约束用于限制表中数据的有效性和完整性。以下是 MySQL 中常见的约束类型：

主键约束（Primary Key Constraint）：用于唯一标识表中的每一行。主键字段的值必须是唯一的，且不能为 NULL。一个表只能有一个主键。
唯一约束（Unique Constraint）：确保一个字段或一组字段的值在表中是唯一的，但允许字段为空值（NULL）。
外键约束（Foreign Key Constraint）：用于确保表中的数据与另一个表中的数据保持引用完整性。外键约束通常用于创建表之间的关系。
非空约束（NOT NULL Constraint）：确保字段不允许为空值（NULL）。
默认约束（Default Constraint）：指定字段的默认值，如果在插入数据时没有提供该字段的值，则使用默认值。
检查约束（Check Constraint）：用于指定数据必须满足的条件。只有满足条件的数据才能被插入或更新到表中。

约束和索引的区别，约束更是是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。

13.drop,truncate,delete 区别?

DROP：

DROP 删除表和数据
DROP是用于删除数据库对象（表、视图、索引、存储过程等）的操作。
例如，使用DROP TABLE语句可以删除一个表，以及与该表相关的数据、索引和约束等。
DROP操作是一个 DDL（Data Definition Language）命令，因此会立即提交事务，并且不能回滚。执行后数据和对象会永久删除。

TRUNCATE：

TRUNCATE 清空表中的数据,会释放空间,重置主键
TRUNCATE用于快速删除表中的所有数据，但保留表结构。
与DELETE相比，TRUNCATE操作通常更快，因为它不会逐行删除数据，而是直接删除表中的所有数据。
TRUNCATE操作是一个 DDL 命令，也会立即提交事务，并且不能回滚。执行后数据会永久删除，但表结构仍然保留。

DELETE：

DELETE 仅仅只是删除表中的数据
DELETE用于从表中删除特定的行，可以根据条件删除满足条件的数据。
DELETE是一个 DML（Data Manipulation Language）命令，因此可以包含在一个事务中，并且可以回滚。
DELETE操作逐行删除数据，并在删除过程中生成事务日志，因此相对较慢，特别是对于大量数据的删除。

14.SQL 运行顺序?

sql 语句执行过程

#编写过程

select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

#解析过程

from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

查询 sql 的关键字书写顺序

select from where group by having order by limit

查询 sql 的关键字执行顺序

from where group by having select order by limit

编写技巧根据需求挑选关键字，按照书写顺序依次排列关键字按照关键字的执行顺序填空

15.JDBC 操作流程?

加载数据库驱动类
打开数据库连接
执行 sql 语句
处理返回结果
关闭资源

二.Innodb 引擎特性

1.innodb 概述?

InnoDB 存储引擎支持事务，其设计目标主要面向在线事务处理(OLTP)的应用。其特点是行锁设计、支持外键，并支持类似于 Oracle 的非锁定读，即默认读取操作不会产生锁。从 MySQL 数据库 5.5.8 版本开始，InnoDB 存储引擎是默认的存储引擎。innodb 将数据放在一个独立的表空间，索引和数据一起存储在表独立的 idb 文件中.

InnoDB 通过使用多版本并发控制 (MVCC) 来获得高并发性，并且实现了 SQL 标准的 4 种隔离级别，默认为 REPEATABLE 级别。同时，使用一种被称为 next-key locking 的策略来避免幻读(phantom）现象的产生。除此之外，InnoDB 储存引擎还提供了插入缓冲 (insert buffer)、二次写 (double write）、自适应哈希索引(adaptive hash index）、预读 (read ahead）等高性能和高可用的功能。

对于表中数据的存储，InnoDB 存储引擎采用了聚集 (clustered）的方式，因此每张表的存储都是按主键的顺序进行存放。如果没有显式地在表定义时指定主键，InnoDB 存储引擎会为每一行生成一个6 字节的 ROWID，并以此作为主键。

2.InnoDB 的关键特性?

插入缓冲(Insert Buffer)
两次写(Double Write)
自适应哈希索引(Adaptive Hash Index)
异步 IO(Async IO)
刷新邻接页(Flush Neighbor Page)
行锁设计
高并发性 MVCC

3.innodb 和 myisam 的区别

MyISAM:MyISAM 引擎是 MySQL5.5.8 及之前版本的默认引擎，它的特点是:

不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁，不支持事务，不支持外键
不支持崩溃后的安全恢复
在表有读取查询的同时，支持往表中插入新纪录
支持 BLOB 和 TEXT 的前 500 个字符索引，支持全文索引。支持延迟更新索引，极大提升写入性能
对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用
存储数据使用 MYD(数据文件)和 MYI(索引文件)

InnoDB:InnoDB 在 MySQL5.5.8 后成为默认引擎，它的特点是:

支持行锁,也支持表锁，默认为行级锁
采用 MVCC 来支持高并发，支持事务，支持外键
支持崩溃后的安全恢复。
使用 idb 文件存储索引和数据

4.表锁和行锁的区别?

数据即索引,索引即数据,行锁的实现是依赖于 innodb 存储引擎的索引设计,并且与记录锁,间隙锁,临键锁等都有一定的关系。本质来说,锁的还是索引。

在 MySQL 的 InnoDB 存储引擎中，表锁和行锁是两种不同的锁机制，用于控制并发访问数据库中的数据。它们在锁定粒度和底层实现上有一些区别。

表锁（Table Locks）：

锁定粒度： 表锁是对整张表进行锁定，意味着当一个事务获取了表锁后，其他事务无法对该表进行任何读写操作，即使是不涉及锁定行的操作。
适用场景： 表锁适用于少量的、相对稳定的数据访问模式，或者当需要执行大规模的数据维护操作时，可以快速获取整张表的锁来保护数据完整性。
底层实现： InnoDB 存储引擎中的表锁是通过在数据字典中设置一个标志来实现的。这种锁定方式效率较低，因为它会导致大量并发事务之间的阻塞。

行锁（Row Locks）：

锁定粒度： 行锁是针对表中的单行数据进行锁定，允许多个事务在同一时刻同时访问表的不同行。
适用场景： 行锁适用于高并发的数据访问模式，其中只有少数行会被同时访问，这可以减少并发事务之间的竞争，提高系统的并发性能。
底层实现： InnoDB 存储引擎实现行锁的方式是通过在每一行数据的存储上增加两个隐藏的字段，记录了这行数据的锁信息。这使得 InnoDB 能够在仅锁定需要的行时避免不必要的锁竞争，从而提高了并发性。

行锁相对于表锁具有更细粒度的控制能力，能够更好地支持高并发的访问模式。然而，行锁也可能引入一些额外的开销，如死锁的风险和锁管理的复杂性。因此，在选择使用表锁还是行锁时，需要根据具体的应用场景和性能需求进行权衡。在 InnoDB 存储引擎中，默认情况下会使用行级锁定来支持更好的并发控制。

5.什么是插入缓冲?

InnoDB 存储引擎开创性地设计了 Insert Buffer，对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入;若不在，则先放入到一个 Insert Buffer 对象中，好似欺骗。数据库这个聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引叶子节点的 merge(合并)操作，这时通常能将多个插入合并到一个操作中(因为在一个索引页中)，这就大大提高了对于非聚集索引插入的性能。

然而 Insert Buffer 的使用需要同时满足以下两个条件:

索引是辅助索引(secondary index)，聚集索引是唯一的，不合适
索引不是唯一(unique)的，因为如果是唯一的，需要校验将要插入的数据是否唯一，性能较低.

当满足以上两个条件时，InnoDB 存储引擎会使用 Insert Buffer，这样就能提高插入操作的性能了。

辅助索引不能是唯一的，因为在插入缓冲时，数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生，从而导致 Insert Buffer 失去了意义。

6.插入缓冲的实现

Insert Buffer 的数据结构是一棵 B+树。在 MySQL4.1 之前的版本中每张表有一棵 Insert Buffer B+树。而在现在的版本中，全局只有一棵 Insert Buffer B+树，负责对所有的表的辅助索引进行 Insert Buffer。而这棵 B+树存放在共享表空间中，默认也就是 ibdata1 中。

非叶节点存放的是查询的 search key(键值)，其构造如图所示。

search key 一共占用 9 个字节，其中 space 表示待插入记录所在表的表空间 id，在 InnoDB 存储引擎中，每个表有一个唯一的 space id 可以通过 space id 查询得知是哪张表。space 占用 4 字节。marker 占用 1 字节，它是用来兼容老版本的 Insert Buffer。offset 表示页所在的偏移量，占用 4 字节。

当一个辅助索引要插入到页(space，offset)时，如果这个页不在缓冲池中，那么 InnoDB 存储引擎首先根据上述规则构造一个 search key，接下来查询Insert Buffer这棵 B+树，然后再将这条记录插入到 Insert Buffer B+树的叶子节点中。对于插入到 Insert Buffer B+树叶子节点的记录(如图 2-4 所示)，并不是直接将待插入的记录插入，而是需要根据如下的规则进行构造:

space、marker、offset 字段和之前非叶节点中的含义相同，一共占用 9 字节。第 4 个字段 metadata 占用 4 字节，其存储的内容如表 2-2 所示。

IBUF_REC_OFFSET_COUNT 是保存两个字节的整数，用来排序每个记录进入 Insert Buffer 的顺序。因为从 InnoDB1.0.x 开始支持 Change Buffer，所以这个值同样记录进入 Insert Buffer 的顺序。通过这个顺序回放(replay)才能得到记录的正确值。从 Insert Buffer 叶子节点的第 5 列开始，就是实际插入记录的各个字段了。因此较之原插入记录，Insert Buffer B+树的叶子节点记录需要额外 13 字节的开销。

因为启用 Insert Buffer 索引后，辅助索引页(space，page_no)中的记录可能被插入到 Insert Buffer B+树中，所以为了保证每次 Merge Insert Buffer 页必须成功，还需要有一个特殊的页用来标记每个辅助索引页(space，page_no)的可用空间。这个页的类型为 Insert Buffer Bitmap。每个 Insert Buffer Bitmap 页用来追踪 16384 个辅助索引页，也就是 256 个区(Extent)。每个 Insert Buffer Bitmap 页都在 16384 个页的第二个页中。

每个辅助索引页在 Insert Buffer Bitmap 页中占用 4 位(bit).

7.什么是 merge insert buffer?

将 insert buffer 中的数据合并到辅助索引页中.

概括地说，Merge_Insert_Buffer 的操作可能发生在以下几种情况下:

辅助索引页被读取到缓冲池时;
Insert Buffer Bitmap 页追踪到该辅助索引页已无可用空间时:
Master Thread.

Insert Buffer Bitmap 页用来追踪每个辅助索引页的可用空间，并至少有 1/32 页的空间。若插入辅助索引记录时检测到插入记录后可用空间会小于 1/32 页，则会强制进行一个合并操作，即强制读取辅助索引页，将 Insert Buffer B+树中该页的记录及待插入的记录插入到辅助索引页中。这就是上述所说的第二种情况

8.什么是 Change Buffer?

Change Buffer（变更缓冲区）是数据库引擎中的一种优化技术，用于提高数据修改操作（如更新或删除）的性能。类似于 Insert Buffer（插入缓冲区），Change Buffer 也是用于减少磁盘写入操作，从而提高数据库的写入性能。

当进行数据修改操作时，数据库引擎需要在磁盘中定位到对应的数据页（page），然后进行修改并将修改后的数据写回磁盘。在高并发的写入操作场景中，频繁的磁盘写入操作可能成为性能瓶颈。为了减少这种开销，数据库引擎引入了 Change Buffer。

Change Buffer 的工作原理如下：

当进行更新或删除操作时，引擎会将数据的修改操作暂时存储在 Change Buffer 中，而不是直接写回磁盘。
Change Buffer 是一个内存中的数据结构，它存储了待修改的数据页（page）中发生的变更信息，例如需要更新的数据行和更新后的值，或者需要删除的数据行的标识。
当 Change Buffer 达到一定大小或者满足一定条件时，数据库引擎将会把 Change Buffer 中的修改操作合并到对应的数据页上，并将合并后的数据页写回磁盘。这个过程通常在后台进行，不会阻塞当前的写入操作。

对一条记录进行 delete 操作可能分为两个过程:

将记录标记为已删除;
真正将记录删除。

因此 Delete Buffer 对应 UPDATE 操作的第一个过程，即将记录标记为删除。Purge Buffer 对应 UPDATE 操作的第二个过程，即将记录真正的删除。同时，InnoDB 存储引擎提供了参数 innodb_change_buffering，用来开启各种 Buffer 的选项。该参数可选的值为:inserts、deletes、purges、changes、allnone。 inserts、deletes purges 就是前面讨论过的三种情况。changes 表示启用 inserts 和 deletes，all 表示启用所有，none 表示都不启用。该参数默认值为 all。

innodb_change_buffer_max_size 来控制 ChangeBuffer 最大使用内存的数量:

innodb_change_buffermax_size 值默认为 25，表示最多使用 1/4 的缓冲池内存空间。而需要注意的是，该参数的最大有效值为 50，最大使用 1/2 的缓冲池内存空间.

9.什么是二次写?

关于 IO 的最小单位：

数据库 IO 的最小单位是 16K(MySQL 默认，oracle 是 8K)
文件系统 IO 的最小单位是 4K(也有 1K 的)
磁盘 IO 的最小单位是 512 字节

因此，存在 IO 写入导致 page 损坏的风险

如果说 Insert Buffer 带给 InnoDB 存储引擎的是性能上的提升，那么 double write(两次写)带给 InnoDB 存储引擎的是数据页的可靠性。当发生数据库宕机时，可能 InnoDB 存储引擎正在写入某个页到表中，而这个页只写了一部分，比如 16KB 的页，只写了前 4KB，之后就发生了宕机，这种情况被称为部分写失效(partial page write)。

在应用(apply)重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是 double write。在 InnoDB 存储引擎中 double write 的体系架构如图

double write 由两部分组成，一部分是内存中的 double write buffer，大小为 2MB，另一部分是物理磁盘上共享表空间中连续的 128 个页，即 2 个区(extent)，大小同样为 2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过 memcpy 函数将脏页先复制到内存中的 double write buffer，之后通过 double write buffer 再分两次，每次 1MB 顺序地写入共享表空间的物理磁盘上，然后马上调用 fsync 函数，同步磁盘，避免缓冲写带来的问题。在这个过程中，因为 double write 页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成 double write 页的写入后，再将 double write buffer 中的页写入各个表空间文件中，此时的写入则是离散的。

如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB 存储引擎可以从共享表空间中!的 double write 中找到该页的一个副本，将其复制到表空间文件，再应用重做日志。

10.什么是自适应哈希索引?

哈希(hash)是一种非常快的查找方法，在一般情况下这种查找的时间复杂度为 O(1)，即一般仅需要一次查找就能定位数据。

InnoDB 存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index AHI)。AHI 是通过缓冲池的 B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB 存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

AHI 有一个要求，即对这个页的连续访问模式必须是一样的。访问模式一样指的是查询的条件一样，若交替进行上述两种查询
以该模式访问了 100 次
页通过该模式访问了 N 次，其中 N=页中记录*1/16

自适应哈希索引采用哈希表的方式实现。不同的是，这仅是数据库自身创建并使用的，DBA 本身并不能对其进行干预。自适应哈希索引经哈希函数映射到一个哈希表中，因此对于字典类型的查找非常快速，如

SELECT * FROM TABLE WHERE index_col='xxx';

但是对于范围查找就无能为力了。通过命令可以看到当前自适应哈希索引的使用状况

SHOW ENGINE INNODB STATUS;

哈希索引只能用来搜索等值的查询

11.什么是刷新邻接页?

InnoDB 存储引擎还提供了 Flush Neighbor Page(刷新邻接页)的特性。

其工作原理为:当刷新一个脏页时，InnoDB 存储引擎会检测该页所在区(extent)的所有页，如果是脏页，那么一起进行刷新。这样做的好处显而易见，通过 AIO 可以将多个 IO 写入操作合并为一个 IO 操作，故该工作机制在传统机械磁盘下有着显著的优势。机械硬盘建议启用该特性，而对于固态硬盘有着超高 IOPS 性能的磁盘，则建议将该参数设置为 0，即关闭此特性。

参数 innodb_flush_neighbors，用来控制是否启用该特性。

12.什么是 MRR 优化?

MySQL5.6 版本开始支持 Multi-Range Read(MRR)优化。Multi-Range Read 优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问。

MRR 优化是数据库中的一种优化技术，全称为 "Multi-Range Read Optimization"（多范围读取优化）。它是用于改善数据库查询性能的一种方法，特别适用于处理范围查询（Range Query）操作。

范围查询是指根据某个范围条件（例如：日期范围、数值范围等）来检索数据库中的数据。执行范围查询时，数据库引擎需要定位到满足条件的多个数据行，并将这些数据行返回给查询请求。在高并发或大规模数据量的情况下，范围查询可能会导致性能下降，因为需要大量的磁盘访问和数据检索操作。

MRR 优化通过改进数据的访问方式，使得范围查询的性能得到提升。它的主要原理如下：

数据排序：MRR 优化首先会对查询的数据进行排序，以确保它们在物理存储上是连续的或者相邻的。这种排序可以减少随机磁盘访问的次数，从而提高范围查询的效率。
批量读取：MRR 优化将范围查询的结果数据按照较大的块（例如页或者块）批量读取到内存中，而不是单个数据行一个接一个地读取。这样可以减少磁盘访问的次数，提高数据读取的效率。
并行处理：MRR 优化还可以通过并行处理的方式来加速范围查询。它可以将查询结果分成多个子任务，并同时处理这些子任务，从而更好地利用多核处理器的计算能力。

13.什么是 ICP 优化?

ICP 优化是数据库查询中的一种优化技术，全称为 "Index Condition Pushdown"（索引条件下推）。它是用于改善数据库查询性能的一种方法，特别适用于复杂的查询操作，涉及多个条件和多个索引的情况。

禁用 ICP 时，存储引擎会通过遍历索引定位基表中的行，然后返回给 Server 层，再去为这些数据行进行 where 条件的过滤。
启用 ICP 时，如果 where 条件可以使用索引，MySQL 会把这部分过滤操作放到存储引擎层，存储引擎通过索引过滤，把满足的行从表中读取出。 ICP 可以减少存储引擎必须访问基表的次数以及 Server 曾必须访问存储引擎的次数。

和 Multi-Range Read 一样，Index Condition Pushdown 同样是 MySQL5.6 开始支持的一种根据索引进行查询的优化方式。之前的 MySQL 数据库版本不支持 Index Condition Pushdown，当进行索引查询时，首先根据索引来查找记录，然后再根据 WHERE 条件来过滤记录。在支持 Index Condition Pushdown 后，MySQL 数据库会在取出索引的同时，判断是否可以进行 WHERE 条件的过滤，也就是将 WHERE 的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层 SQL 层对记录的索取(fetch)，从而提高数据库的整体性能。

Index Condition Pushdown 优化支持 range、ref、eq_ref 、ref_or_null 类型的查询，当前支持 MyISAM 和 InnoDB 存储引擎。当优化器选择 Index Condition Pushdown 优化时，可在执行计划的列 Extra 看到 Using index condition 提示。

14.ICP 的限制条件?

ICP（Index Condition Pushdown）优化在数据库查询中是一个有用的技术，但是它也有一些限制条件，这些限制条件可能影响它的适用性和效果。以下是一些常见的 ICP 优化的限制条件：

范围条件：ICP 优化主要适用于等值条件，即对于索引列的查询条件是等于某个值的情况。对于范围条件（如大于、小于、区间等），ICP 优化的效果可能较差，因为范围条件的处理通常需要在数据层级进行，无法完全下推到索引层级。
多列条件：ICP 优化通常对于多列条件的处理有限。当查询涉及多个条件，并且这些条件之间没有简单的逻辑关系时，ICP 优化的效果可能不明显。
联合索引：对于联合索引，ICP 优化只能应用于最左前缀，即只有查询条件包含联合索引的最左列时才能进行优化。如果查询条件涉及联合索引的非最左列，ICP 优化无法生效。
NULL 值：对于包含 NULL 值的索引列，ICP 优化可能受到限制。由于 NULL 值的特殊性，它的处理可能需要额外的检查和判断，可能无法完全下推到索引层级。
引用外部表：如果查询涉及到引用外部表的数据，ICP 优化可能无法生效。因为外部表的数据可能需要额外的访问和处理，无法直接下推到索引层级。
子查询和复杂查询：对于包含子查询或者复杂查询逻辑的情况，ICP 优化可能有限或者无法应用。

尽管 ICP 优化有上述的限制条件，但它在很多情况下仍然是非常有用的优化技术，特别是对于单列等值查询的情况。数据库引擎通常会根据查询的复杂性和数据分布来自动选择是否应用 ICP 优化。同时，通过合理的索引设计和查询优化，可以进一步提高 ICP 优化的效果和数据库查询性能。

15.如何避免离散读?

MySQL5.6 之前，优化器在进行离散读决策的时候，如果数据量比较大，会选择使用聚集索引，全表扫描。

MySQL5.6 版本开始支持 Multi-Range Read（MRR）优化。Multi-Range Read 优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于 IO-bound 类型的 SQL 查询语句可带来性能极大的提升。Multi-Range Read 优化可适用于 range，ref，eq_ref 类型的查询。

MRR 优化有以下几个好处：

MRR 使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
减少缓冲池中页被替换的次数。（顺序查找可以对一个页进行顺序查找，无需离散加载数据页）
批量处理对键值的查询操作。
对于 InnoDB 和 MyISAM 存储引擎的范围查询和 JOIN 查询操作，MRR 的工作方式如下：
将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
将缓存中的键值根据 RowID 进行排序。
根据 RowID 的排序顺序来访问实际的数据文件。

举例说明：

SELECT * FROM salaries WHERE salary＞10000 AND salary＜40000;

salary 上有一个辅助索引 idx_s，因此除了通过辅助索引查找键值外，还需要通过书签查找来进行对整行数据的查询。当不启用 Multi-Range Read 特性时，看到的执行计划如图：

若启用 Mulit-Range Read 特性，则除了会在列 Extra 看到 Using index condition 外，还会看见 Using MRR 选项

Multi-Range Read 还可以将某些范围查询，拆分为键值对，以此来进行批量的数据查询。这样做的好处是可以在拆分过程中，直接过滤一些不符合查询条件的数据，例如：

SELECT * FROM t WHERE key_part1 ＞=1000 AND key_part1 ＜ 2000 AND key_part2=10000;

表 t 有（key_part1，key_part2）的联合索引，因此索引根据 key_part1，key_part2 的位置关系进行排序。若没有 Multi-Read Range，此时查询类型为 Range，SQL 优化器会先将 key_part1 大于 1000 且小于 2000 的数据都取出，即使 key_part2 不等于 1000。待取出行数据后再根据 key_part2 的条件进行过滤。这会导致无用数据被取出。如果有大量的数据且其 key_part2 不等于 1000，则启用 Mulit-Range Read 优化会使性能有巨大的提升。

倘若启用了 Multi-Range Read 优化，优化器会先将查询条件进行拆分，然后再进行数据查询。就上述查询语句而言，优化器会将查询条件拆分为（1000，10000），（1001，10000），（1002，10000），…，（1999，10000），最后再根据这些拆分出的条件进行数据的查询。

在非必要的情况下，拒绝使用 select _；在必须 select _ 的情况下，尽量使用 MySQL5.6+的版本开启 MRR；在必须 select * 的情况下且 MySQL 小于 5.6 版本下，可以根据数据量进行离散读和聚集索引两种情况下的性能进行对比，必要时采用 force index 语句强制指定索引。

16.说说 purge 操作?

purge 用于最终完成 delete 和 update 操作。这样设计是因为 InnoDB 存储引擎支持 MVCC，所以记录不能在事务提交时立即进行处理。这时其他事物可能正在引用这行，故 InnoDB 存储引擎需要保存记录之前的版本。而是否可以删除该条记录通过 purge 来进行判断。若该行记录已不被任何其他事务引用，那么就可以进行真正的 delete 操作。可见，purge 操作是清理之前的 delete 和 update 操作，将上述操作“最终”完成。而实际执行的操作为 delete 操作，清理之前行记录的版本。

delete 和 update 操作可能并不直接删除原有的数据。例如，

DELETE FROM t WHERE a=1;

表 t 上列 a 有聚集索引，列 b 上有辅助索引。对于上述的 delete 操作，仅是将主键列等于 1 的记录 delete flag 设置为 1，记录并没有被删除，即记录还是存在于 B+树中。其次，对辅助索引上 a 等于 1，b 等于 1 的记录同样没有做任何处理。而真正删除这行记录的操作其实被“延时”了，最终在 purge 操作中完成。

17.二进制和事务提交一致性?

MySQL5.6 采用了 Binary Log Group Commit (BLGC)。MySQL5.6 BLGC 的实现方式是将事务提交的过程分为几个步骤，在 MySQL 数据库上层进行提交时首先按顺序将其放入一个队列中，队列中的第一个事务称为 leader，其他事务称为 follower，leade 控制着 follower 的行为。BLGC 的步骤分为以下三个阶段:

Flush 阶段，将每个事务的二进制日志写入内存中。
Sync 阶段，将内存中的二进制日志刷新到磁盘，若队列中有多个事务，那么仅一次 fsync 操作就完成了二进制日志的写入，这就 BLGC。
Commit 阶段，leader 根据顺序调用存储引擎层事务的提交 InnoDB 存储引擎本就支持 group commit，因此修复了原先由于 prepare_commit_mutex 导致 group commit 失效的问题。

因为备份及恢复的需要，例如通过工具 xtrabackup 或者 ibbackup 进行备份，并用来建立 replication，如下图所示。

可以看到若通过在线备份进行数据库恢复来重新建立 replication，事务 T1 的数据会产生丢失。因为在 InnoDB 存储引擎层会检测最后一次的事务 T3 在上下两层都完成了提交，不需要再进行恢复，故认为之前的 T1，T2 也都完成了提交。

因此通过锁 prepare_commit_mutex 以串行的方式来保证顺序性，然而这会使 group commit 无法生效

18.InnoDB 的后台线程?

核心线程如下：

Master Thread：是一个非常核心的后端线程，主要负责将缓冲池中的数据异步刷新到磁盘中，保证数据一致性。包括脏页的刷新、insert buffer、undo 页的回收等。
IO Thread： InnoDB 引擎使用了大量的异步 IO 来处理写 IO 请求，这样可以极大的提高数据库的性能。而 IO Thread 线程主要就是这些 IO 请求的一个回调处理。
Purge Thread：当事务被提交之后，用于回收可能不再需要的 undo log 所使用的页。
Page Cleaner Thread：为了提高 InnoDB 引擎的性能，在 1.2x 版本引入该线程，主要用于将之前版本中脏页的刷新操作放入单独线程，目的为了减轻原 Master Thread 线程的压力。

19.innodb 内存分配?

读取数据是基于页的，会首先判断页是否存在缓冲池中，如果存在，直接使用，不存在从磁盘读取.

可以有多个缓冲池实例，默认为 1
使用 check point 机制刷新到磁盘

缓冲池

数据页
索引页
插入缓冲
锁信息
自适应哈希索引
数据字典信息

重做日志缓冲
额外内存池:缓冲控制对象等需要从额外内存池分配内存.

20.LRU、Free、Flush 区别?

LRU List(LRU 列表):在缓冲池中，使用了 LRU 算法，存储缓冲池中的页，缓冲池中页的大小默认为 16kb，但是并不是插入到首位置，而是有个 midpoint 的概念，使用 innodb_old_blocks_pct 参数进行设置，默认为 37，插入在距离末尾 37%的位置，midpoint 之后的数据为 old 数据，之前的位置为 new 数据，也是热点数据.这样做的优点是防止热点数据被刷出缓存池.并且通过 innodb_old_blocks_time 参数，表示等待多久加入到缓冲池的热端.同样是防止热点数据不被刷出.
Free List:刚启动的时候，LRU 列表是空的，页是存放在 free 列表中的，需要时从 free 列表划分到 LRU 列表.
Flush list:脏页列表，缓存行中的页数据和磁盘不一致.脏页既存在 Flush 列表，也存在于 LRU 列表，LRU 列表用于页的可用性，Flush 列表用于刷新到磁盘.

21.热点数据不被冲掉?

针对全表扫描时，短时间内访问大量使用频率非常低的页面情况的优化，在进行全表扫描时，虽然首次被加载到 Buffer Pool 的页被放到了 old 区域的头部，但是后续会被马上访问到，每次进行访问的时候又会把该页放到 young 区域的头部，这样仍然会把那些使用频率比较高的页给顶下去。全表扫描有一个特点，那就是它的执行频率非常低，谁也不会没事儿老在那写全表扫描的语句玩，而且在执行全表扫描的过程中，即使某个页面中有很多条记录，也就是去多次访问这个页面所花费的时间也是非常少的。

所以我们只需要规定，在对某个处在 old 区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问时间，如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该页面就不会被从 old 区域移动到 young 区域的头部，否则将它移动到 young 区域的头部。上述的这个间隔时间是由系统变 innodb_old_blocks_time 控制的.这个 innodb_old_blocks_time 的默认值是 1000，它的单位是毫秒，也就意味着对于从磁盘上被加载到 LRU 链表的 old 区域的某个页来说，如果第一次和最后一次访问该页面的时间间隔小于 1s (很明显在一次全表扫描的过程中，多次访问一个页面中的时间不会超过 1s)，那么该页是不会被加入到 young 区域的.如果我们把 innodb_old_blocks_time 的值设置为 0，那么每次我们访问一个页面时就会把该页面放到 young 区域的头部。

22.压缩页的表?

首先，在 unzip LRU 列表中对不同压缩页大小的页进行分别管理。其次，通过伙伴算法进行内存的分配。例如对需要从缓冲池中申请页为 4KB 的大小，其过程如下:

检查 4KB 的 unzip_LRU 列表，检查是否有可用的空闲页;
若有，则直接使用;
否则，检查 8KB 的 unzip_LRU 列表;
若能够得到空闲页，将页分成 2 个 4KB 页，存放到 4KB 的 unzip_LRU 列表;
若不能得到空闲页，从 LRU 列表中申请一个 16KB 的页，将页分为 1 个 8KB 的页、2 个 4KB 的页，分别存放到对应的 unzip_LRU 列表中。

23.什么是 checkpoint?

为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了 Write Ahead log 策略即当事务提交时，先写重做日志，再修改页。当由干发生宕机而导致数据丢失时，通过重做日志来完成数据的恢复。这也是事务 ACID中 D(Durability 持久性)的要求。

checkpoint 的作用

缩短数据库的恢复时间;
缓冲池不够用时，将脏页刷新到磁盘;Lru 列表不够用时，强制刷新脏页
重做日志不可用时，刷新脏页。

对于 InnoDB 存储引擎而言，其是通过 LSN(Log Sequence Number)来标记版本的。而 LSN 是 8 字节的数字，其单位是字节。每个页有 LSN，重做日志中也有 LSN，Checkpoint 也有 LSN。

有两种 Checkpoint，分别为:

Sharp Checkpoint
Fuzzy Checkpoint

Sharp Checkpoint 发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数 innodb_fast_shutdown=1

但是若数据库在运行时也使用 Sharp Checkpoint，那么数据库的可用性就会受到很大的影响。故在 InnoDB 存储引擎内部使用 Fuzzy Checkpoint进行页的刷新，即只刷新一部分脏页，而不是刷新所有的脏页回磁盘。在 InnoDB 存储引擎中可能发生如下几种情况的 Fuzzy Checkpoint:

Master Thread Checkpoint 固定频率刷新到磁盘
FLUSH LRU LIST Checkpoint 缓存池中页的数量不足
Async/Sync Flush Checkpoint 保证重做日志的循环使用
Dirty Page too much Checkpoint 脏页太多，innodb_max_dirty_pages_pct，默认 90%

三.Mysql 文件

1.mysql 文件有哪些?

参数文件:告诉 MvSQL 实例启动时在哪里可以找到数据库文件。并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置，还会介绍各种参数的类型。
日志文件:用来记录 MySQL 实例对某种条件做出响应时写入的文件，如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等。

错误日志(error log)
二进制日志(bin log)
慢查询日志(slow query log) long_query_time 默认是 10s
查询日志(log)

socket 文件:当用 UNIX 域套接字方式进行连接时需要的文件。
pid 文件:MySQL 实例的进程 ID 文件。
MySQL 表结构文件:用来存放 MySQL 表结构定义文件，文件后缀为 frm。

索引文件:

存储引擎文件:因为 MySQL 表存储引擎的关系，每个存储引擎都会有自己的文件来保存各种数据。这些存储引擎真正存储了记录和索引等数据。

MyISAM

myd 文件：存放表数据文件
myi 文件：存放表索引文件

InnoDB

.ibd 文件:存放表数据和表索引文件

2.什么是物理日志和逻辑日志?

物理日志和逻辑日志在存储内容上有很大区别，存储内容是区分它们的最重要手段。

物理日志:

存储内容：存储数据库中特定记录的变更，通常是 oriented page，即描述具体某一个 page 的修改操作；
例子：一条更新请求对应的初始值（original value）以及更新值（after value）；

举例

"Page 42:image at 367，2; before:'ke';after:'ca'”

Page 42 用于说明更新操作作用的 page；
367：用于说明更新操作相对于 page 的 offset；
2：用于说明更新操作的作用长度，即 length，2 代表仅仅修改了两个字符；
before:‘Ke’：这里表示 undo information，也可以称为 undo log；
after:‘ca’：这里表示 redo information，也可以称为 redo log；

逻辑日志:

存储内容：存储事务中的一个操作；
例子：事务中的 UPDATE、DELETE 以及 INSERT 操作。

3.mysql 的配置文件

MySQL 数据库是按如下顺序读取配置文件的。

/etc/my.cnf > /etc/mysql/my.cnf > /usr/local/mysql/etc/my.cnf > ~/my.cnf

系统级配置文件：MySQL 首先读取系统级配置文件，这通常是安装 MySQL 时指定的主配置文件。在 Unix/Linux 系统中，它通常是位于/etc/my.cnf 或/etc/mysql/my.cnf。
用户级配置文件：接下来，MySQL 会读取用户级配置文件。在 Unix/Linux 系统中，用户级配置文件通常是位于用户的主目录下的 my.cnf 文件。在 Windows 系统中，它位于用户主目录下的 my.ini 文件。
当前工作目录配置文件：如果在启动 MySQL 时指定了--defaults-file 选项，并且该选项指定了一个配置文件路径，则 MySQL 会读取该配置文件作为当前工作目录配置文件。
内置默认值：如果上述配置文件都不存在或未设置某些选项，MySQL 将使用内置的默认值来设置这些选项。
如果几个配置文件中都有同一个参数，MySQL 数据库会以读取到的最后一个配置文件中的参数为准。

4.表空间文件?

共享表空间:所有表的信息放在一个文件
独立表空间:每个表单独放在一个文件

5.二进制日志的作用?

二进制文件作用:

记录更改:二进制日志(binary log)记录了对 MySQL 数据库执行更改的所有操作，但是不包括 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改。然而，若操作本身并没有导致数据库发生变化，那么该操作可能也会写入二进制日志。单个二进制文件最大为 1G.如果用户想记录 SELECT 和 SHOW 操作，那只能使用查询日志，而不是二进制日志。
恢复(recovery):某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point-in-time 的恢复。
复制(replication):其原理与恢复类似，通过复制和执行二进制日志使一台远程的 MySQL 数据库(一般称为 slave)与一台 MySQL 数据库(一般称为 master 或 primary)进行实时同步。
审计(audit):用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

6.binlog 的刷盘时机

mysql 通过 sync_binlog 参数控制 biglog 的刷盘时机，取值范围是 0-N：

0：不去强制要求，由系统自行判断何时写入磁盘；
1：每次 commit 的时候都要将 binlog 写入磁盘；
N：每 N 个事务，才会将 binlog 写入磁盘。

7.说说 binlog_format 参数?

binlog_format 参数十分重要，它影响了记录二进制日志的格式。在 MySQL5.1 版本之前，没有这个参数。所有二进制文件的格式都是基于 SQL 语句(statement)级别的。同时，对于复制是有一定要求的。如在主服务器运行 rand、uuid 等函数，又或者使用触发器等操作，这些都可能会导致主从服务器上表中数据的不一致(not sync)。

另一个影响是，会发现 InnoDB 存储引擎的默认事务隔离级别是 REPEATABLE READ。这其实也是因为二进制日志文件格式的关系，如果使用 READ COMMITTED 的事务隔离级别(大多数数据库，如 Oracle， SQLServer 数据库的默认隔离级别)，会出现类似丢失更新的现象，从而出现主从数据库上的数据不一致。

MySQL5.1 开始引入了 binlog_format 参数，该参数可设的值有 STATEMENT、ROW 和 MIXED。

STATEMENT、ROW 的思想类似于 redis 的 RDB 和 AOF.

STATEMENT 格式和之前的 MySQL 版本一样，二进制日志文件记录的是日志的逻辑 SQL 语句。
在 ROW 格式下，二进制日志记录的不再是简单的 SQL 语句了，而是记录表的行更改情况。同时，对上述提及的 Statement 格式下复制的问题予以解决。从 MySQL 5.1 版本开始，如果设置了 binlog_format 为 ROW，可以将InnoDB 的事务隔离基本设为 READ COMMITTED，以获得更好的并发性。
在 MIXED 格式下，MySQL 默认采用 STATEMENT 格式进行二进制日志文件的记录，但是在一些情况下会使用 ROW 格式，可能的情况有:

表的存储引擎为 NDB，这时对表的 DML 操作都会以 ROW 格式记录。
使用了 UUIDO)、USERO、CURRENT USERO)、 FOUND ROWS()、ROW COUNT()等不确定函数。
使用了 INSERTDELAY 语句。
使用了用户定义函数(UDF)。
使用了临时表(temporarytable)。

8.什么是 redo log?

MySQL 的Redo Log（重做日志）是一种持久化日志机制，用于确保即使在数据库崩溃或突然重启等异常情况下，事务的持久性（Durability，ACID 属性的一个组成部分）仍然能得到保证。

具体来说，当一个事务进行更改（例如，INSERT、UPDATE、DELETE 等操作）时，这些更改首先会被写入到 Redo Log 中。这个过程通常是非常快的，因为只需要写入到磁盘的顺序部分，而不需要直接更新磁盘上的数据文件。一旦 Redo Log 被安全地写入，事务就可以被认为是“提交”的，从而满足持久性的要求。

Redo Log 通常分为两部分：

In-memory Redo Log Buffer：这是内存中的一个缓冲区，用于暂存即将写入磁盘的 Redo Log 记录。
On-disk Redo Log Files：这些是磁盘上的物理文件，用于持久化存储 Redo Log 数据。这些文件通常是循环使用的（也就是说，当达到一定大小后，旧的日志记录会被覆盖）。

在数据库恢复（Recovery）过程中，Redo Log 用于“重做”事务，以确保所有提交的事务都得到应用，而所有未提交的事务都被回滚，从而达到一致性的状态。

使用 Redo Log 有多个好处：

提高性能：由于 Redo Log 是顺序写入的，因此具有更高的 I/O 性能。
数据安全性：即使在系统崩溃的情况下，也能通过 Redo Log 恢复数据，保证数据的持久性。
简化恢复过程：由于 Redo Log 包含了如何从一个一致状态转变到另一个一致状态的所有信息，因此数据库恢复变得相对简单和快速。

MySQL 的 Redo Log 是一种关键的持久化机制，用于保证事务的持久性并提高系统的整体性能和可靠性。

9.重做日志块?

在 InnoDB 存储引擎中，重做日志都是以 512 字节进行存储的。这意味着重做日志缓存、重做日志文件都是以块(block)的方式进行保存的，称之为重做日志块(redo log block)，每块的大小为 512 字节。若一个页中产生的重做日志数量大于 512 字节，那么需要分割为多个重做日志块进行存储。此外，由于重做日志块的大小和磁盘扇区大小一样，都是 512 字节，因此重做日志的写入可以保证原子性，不需要 double write 技术。

重做日志块除了日志本身之外，还由日志块头(log block header)及日志块尾(log block tailer)两部分组成。重做日志头一共占用 12 字节，重做日志尾占用 8 字节。故每个重做日志块实际可以存储的大小为 492 字节(512-12-8)。

日志块头(log block header)由四部分组成.

log buffer 是由 log block 组成，在内部 log buffer 就好似一个数组，因此 LOG_BLOCK_HDR_NO 用来标记这个数组中的位置。其是递增并且循环使用的，占用 4 个字节，但是由于第一位用来判断是否是 flush bit，所以最大的值为 2G。

LOG_BLOCK_HDR_DATA_LEN 占用 2 字节，表示 log block 所占用的大小。当 log block 被写满时，该值为 0x200，表示使用全部 log block 空间，即占用 512 字节。

LOG_BLOCK_FIRST_REC_GROUP 占用 2 个字节，表示 log block 中第一个日志所在的偏移量。如果该值的大小和 LOG_BLOCK_HDR_DATA_LEN 相同，则表示当前 log block 不包含新的日志。

LOG_BLOCK_CHECKPOINT_NO 最后被写入的检查点的位置.占用 4 个字节.

10.重做日志缓冲?

InnoDB 存储引擎的内存区域除了有缓冲池外，还有重做日志缓冲(redo log buffer)。InnoDB 存储引擎首先将重做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置得很大，因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。该值可由配置参数 innodb_log_buffer_size 进行设置，8.0 版本默认是 16M.

重做日志缓冲刷新到磁盘的情况

Master Thread 每一秒将重做日志缓冲刷新到重做日志文件;
每个事务提交时会将重做日志缓冲刷新到重做日志文件;
当重做日志缓冲池剩余空间小于 1/2 时，重做日志缓冲刷新到重做日志文件。

11.重做日志文件?

在默认情况下，在 InnoDB 存储引擎的数据目录下会有两个名为ib_logfile0 和 ib_logfile1的文件。是重做日志文件(redo log file)。它们记录了对于 InnoDB 存储引擎的事务日志。当实例或介质失败(media failure)时，重做日志文件就能派上用场。例如，数据库由于所在主机掉电导致实例失败，InnoDB 存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。

每个 InnoDB 存储引擎至少有 1 个重做日志文件组(group)，每个文件组下至少有 2 个重做日志文件，如默认的 ib_logfile0 和 ib_logfile1。为了得到更高的可靠性，用户可以设置多个的镜像日志组(mirrored log groups)，将不同的文件组放在不同的磁盘上，以此提高重做日志的高可用性。在日志组中每个重做日志文件的大小一致，并以循环写入的方式运行。InnoDB 存储引擎先写重做日志文件 1，当达到文件的最后时，会切换至重做日志文件 2

innodb_log_fle_size:每个重做日志文件的大小最大 512G
innodb_log_flesin_group:组中重做日志文件的数量默认为 2
innodb_mirrored_log_groups:日志镜像文件组的数量，默认为 1
innodb_log_group_home_dir:日志文件组所在路径
一组两个重做日志文件的默认大小通常是 48MB，即每个文件大小为 48MB。

12.重做日志块与重做日志文件

重做日志（Redo Log）是数据库管理系统中的一种重要机制，用于保证事务的持久性和数据恢复。在 MySQL 等数据库管理系统中，重做日志通常由多个重做日志文件组成，每个文件由多个重做日志块组成。

重做日志块（Redo Log Block）：

重做日志块是重做日志的最小单位，通常是一个固定大小的数据块。
它记录了数据库中发生的每个事务所进行的修改操作，如更新、插入和删除等。
当事务执行修改操作时，先将修改记录到重做日志块，然后再将修改应用到内存中的数据页上。
重做日志块是顺序写入的，因为它需要保证记录的顺序与事务的执行顺序一致。
一旦事务将修改记录到重做日志块并提交，数据库就可以认为该事务的修改已经持久化，并可以在必要时进行恢复。

重做日志文件（Redo Log File）：

重做日志文件是由多个重做日志块组成的逻辑文件，用于存储重做日志的内容。
重做日志文件是一个循环的文件组，当写满一个重做日志文件后，数据库会切换到下一个重做日志文件，以保证循环使用。
在数据库运行期间，重做日志文件会不断地写入新的重做日志块，并且不会删除已经写入的内容，这是为了确保发生故障时可以进行数据恢复。
当数据库发生崩溃或意外停机时，重做日志文件中的内容将被用于恢复未提交的事务和未写入磁盘的数据页，以保证数据的一致性。

总结:

重做日志块是重做日志的最小记录单位，记录每个事务的修改操作。
重做日志文件是由多个重做日志块组成的逻辑文件，用于存储重做日志的内容，确保数据的持久性和一致性，以及在数据库崩溃时进行恢复。

13.重做日志的格式?

在 InnoDB 存储引擎中，对干各种不同的操作有着不同的重做日志格式。到 InnoDB1.2.x 版本为止，总共定义了 51 种重做日志类型。虽然各种重做日志的类型不同，但是它们有着基本的格式，表 3-2 显示了重做日志条目的结构:

redo_log_type 占用 1 字节，表示重做日志的的类型
space 表示表空间的 ID，但采用压缩的方式，因此占用的空间可能小于 4 字节
page_no 表示页的偏移量，同样采用压缩的方式
redo_log_body 表示每个重做日志的数据部分，恢复时需要调用相应的函数进行解析

14.重做日志刷盘机制

从重做日志缓冲往磁盘写入时，是按 512 个字节，也就是一个扇区的大小进行写入。因为扇区是写入的最小单位，因此可以保证写入必定是成功的。因此在重做日志的写入过程中不需要有 double write.

从重做日志缓冲写入磁盘上的重做日志文件是按一定条件进行的，

在主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件中，不论事务是否已经提交。
另一个触发写磁盘的过程是由参数 innodb_flush_log_at_trx_commit 控制，表示在提交(commit)操作时，处理重做日志的方式。参数 innodb_flush_log_at_trx_commit 的有效值有 0 1 2。

0 代表当提交事务时，并不将事务的重做日志写入磁盘上的日志文件，而是等待主线程每秒的刷新。
1 表示在执行 commit 时将重做日志缓冲同步写到磁盘，即伴有 fsync 的调用。
2 表示将重做日志异步写到磁盘，即写到文件系统的缓存中。因此不能完全保证在执行 commit 时肯定会写入重做日志文件，只是有这个动作发生。

因此为了保证事务的 ACID 中的持久性，必须将 innodb_flush_log_at_trx_commit 设置为 1，也就是每当有事务提交时，就必须确保事务都已经写入重做日志文件。那么当数据库因为意外发生宕机时，可以通过重做日志文件恢复，并保证可以恢复已经提交的事务。而将重做日志文件设置为 0 或 2，都有可能发生恢复时部分事务的丢失。不同之处在于，设置为 2 时，当 MySQL 数据库发生宕机而操作系统及服务器并没有发生宕机时，由于此时未写入磁盘的事务日志保存在文件系统缓存中，当恢复时同样能保证数据不丢失。

15.什么是 LSN?

LSN（Log Sequence Number）是数据库中用于标识事务日志（重做日志）顺序的一种递增的序列号。LSN 在数据库中扮演着非常重要的角色，用于管理事务的提交和回滚、故障恢复以及数据一致性等方面。

LSN 的作用：

标识事务日志的顺序：每当一个事务进行数据修改时，相关的重做日志（Redo Log）记录会被生成，并分配一个唯一的 LSN 号。这样，数据库系统就可以通过 LSN 来确定事务日志的顺序，从而保证修改的顺序与事务的执行顺序一致。
事务的提交和回滚：数据库系统使用 LSN 来跟踪和管理事务的提交和回滚。当一个事务提交时，其对应的 LSN 会被记录在持久化存储中，以确保在故障恢复时，可以正确地重新执行已提交的事务。而在事务回滚时，数据库可以利用 LSN 来撤销事务所做的修改。
故障恢复：在数据库系统发生崩溃或异常停机时，通过 LSN 可以确定在故障发生前已经提交的事务，以及还未提交的事务。通过重做日志和 LSN，数据库可以重新执行已提交的事务，确保数据的持久性和一致性。
数据一致性：在数据库的恢复过程中，LSN 可以确保已提交事务的修改在崩溃后重新执行，从而维护数据的一致性。

总结： LSN（Log Sequence Number）是数据库中用于标识事务日志顺序的递增序列号。通过 LSN，数据库系统可以管理事务的提交和回滚，实现故障恢复，并保证数据的一致性。LSN 在数据库的可靠性和恢复方面起着重要的作用。

16.binlog 和重做日志区别?

在 MySQL 数据库中还有一种二进制日志(binlog)，其用来进行 POINT-IN-TIME(PIT)的恢复及主从复制(Replication)环境的建立。从表面上看其和重做日志非常相似，都是记录了对于数据库操作的日志。然而，从本质上来看，两者有着非常大的不同。

首先，重做日志是在 InnoDB 存储引擎层产生，而二进制日志是在 MySQL 数据库的上层产生的，并且二进制日志不仅仅针对于 InnoDB 存储引擎，MySQL 数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。

其次，两种日志记录的内容形式不同。MySQL 数据库上层的二进制日志是一种逻辑日志，其记录的是对应的 SQL 语句。而 InnoDB 存储引擎层面的重做日志是物理格式日志，其记录的是对于每个页的修改。

此外，两种日志记录写入磁盘的时间点不同，二进制日志只在事务提交完成后进行一次写入。而 InnoDB 存储引擎的重做日志在事务进行中不断地被写入，这表现为日志并不是随事务提交的顺序进行写入的。

二进制日志仅在事务提交时记录，并且对于每一个事务，仅包含对应事务的一个日志。而对于 InnoDB 存储引擎的重做日志，由于其记录的是物理操作日志，因此每个事务对应多个日志条目，并且事务的重做日志写入是并发的，并非在事务提交时写入，故其在文件中记录的顺序并非是事务开始的顺序。

17.什么是 undo log?

Undo Log（撤销日志）是数据库管理系统中的另一个关键机制，用于支持事务的回滚和并发控制。在 MySQL 等数据库中，每个事务执行期间所做的修改操作除了被记录到重做日志（Redo Log）中，还会被记录到 Undo Log 中。

undo log 叫做回滚日志，用于记录数据被修改前的信息。他正好跟前面所说的重做日志所记录的相反，重做日志记录数据被修改后的信息。undo log 主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

Undo Log 的作用：

支持事务的回滚：当一个事务需要回滚（撤销）时，数据库可以利用 Undo Log 中的信息将事务所做的修改操作逆向执行，从而撤销该事务对数据所做的改变。
实现并发控制：当一个事务在执行期间，可能会读取其他事务正在修改的数据。为了确保事务的读取操作能够得到一致性的数据，数据库会将其他事务正在修改的数据版本记录到 Undo Log，这样在读取时，可以根据 Undo Log 中的数据版本来获取一个一致性的数据视图。

Undo Log 的工作原理：

在事务执行期间，如果进行了数据的修改（例如插入、更新、删除等），数据库会先将修改前的数据记录到 Undo Log 中，保留原始数据的一个副本。
当事务提交时，Undo Log 中的记录被保留，用于可能的回滚操作，以确保事务的原子性和一致性。
如果事务执行过程中发生错误，或者事务被回滚，数据库可以利用 Undo Log 中的信息将事务的修改操作逆向执行，从而回滚事务。
并发控制方面，当一个事务需要读取数据时，数据库会检查其他正在执行的事务是否已经修改过该数据。如果是，数据库可以通过 Undo Log 中的数据版本来提供给读取操作一个一致性的数据视图。

总结:Undo Log 是数据库中用于支持事务回滚和实现并发控制的机制。它记录了事务执行过程中所做的数据修改操作的原始值，以便在事务回滚或提供一致性读取时使用。通过 Undo Log，数据库可以保证事务的原子性、一致性和隔离性。

除了回滚操作，undo log 的另一个作用是 MVCC，即在 InnoDB 存储引擎中 MVCC 的实现是通过 undo log 来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过 undo log 读取之前的行版本信息，以此实现非锁定读取。

18.举例说明重做日志的作用

DB_TRX_ID：记录创建这条记录/最后一次修改该记录的事务 ID
DB_ROW_ID: 隐含的自增 ID
DB_ROLL_PTR: 指向这条记录的上一个版本

19.undo log 和 redo log?

事务隔离性由锁来实现。原子性、一致性、持久性由数据库的 redo log 和 undo log 来完成。

redo log 称为重做日志来保证事务的原子性和持久性。

undo log 用来保证事务的一致性。

undo 不是 redo 的逆过程。redo log 和 undo log 的作用都可以视为是一种恢复操作，redo 恢复提交事务修改页操作，而 undo 回滚行记录到某个特定版本。因此两者记录的不同，redo 通常是物理日志，记录的是页的物理修改操作。undo 逻辑日志，根据每行记录进行记录。

在 MySQL 中，重做日志（Redo Log）是用于保证事务的持久性（Durability）的一种机制。它由两部分组成：重做日志缓冲（Redo Log Buffer）和重做日志文件（Redo Log File）。

重做日志缓冲是一个内存区域，用于暂存事务执行过程中对数据的修改操作。当事务提交时，重做日志缓冲中的内容会被刷写到重做日志文件中。

重做日志文件是一个磁盘文件，用于记录所有的事务修改操作。当数据库发生宕机等异常情况时，重做日志文件可以用来恢复数据。重做日志文件的大小是固定的。重做日志的作用是保证事务的持久性和可恢复性。

redo log 存放在重做日志文件中，与 redo log 不同，undo 存放在数据库内部的一个特殊段（segment）中，这个段称为 undo 段（undo segment）。undo 段位于共享表空间内。可以通过 py_innodb_page_info.py 工具来查看当前共享表空间中 undo 的数量。

最后也是最为重要的一点是，undo log 会产生 redo log，也就是 undo log 的产生会伴随着 redo log 的产生，这是因为 undo log 也需要持久性的保护。

20.慢查询日志

程序中定位一个执行慢的 SQL 可以根据慢查询日志，默认情况下，慢查询日志禁用，因为开启慢查询日志或多或少的会对 mysql 的性能产生一些影响。在慢查询日志功能开启时，只有 SQL 执行时间超过 long_query_time 参数值的的语句才会在慢查询日志中记录。long_query_time 参数，最小值和默认值分别为 0 10，单位为秒。

slow_query_log:是否开启慢查询日志
long_query_time:查询阈值，超过了该阈值则记录到慢查询日志中
log_output:如何存储慢查询日志，可选项：FILE 或者 TABLE
slow_query_log_file:以 FILE 类型存储慢查询日志时的存储位置

set global slow_query_log=on;

mysql> select sleep(11);

四.Mysql 表数据结构

1.mysql 的索引数据结构?

数据结构特点:

底层使用的数据结构是 b+树
查询时间复杂度是 O(logN)
叶子节点存储数据
非叶子节点不存储数据

B+树是为磁盘或其他直接存取设备设计的一种平衡查找树。在 B+树中，所有记录节点都是按照键值的大小顺序存放在同一层的叶子节点上，通过各叶子节点指针进行连接。

B+树索引在数据库中有一个特点是高扇出性，因此在数据库中，B+树的高度一般都在 2 ～ 4 层，这也就是说在查找某一键值的行记录时最多只需要 2 到 4 次 IO，这倒不错。因为当前一般的机械磁盘每秒至少可以做 100 次 IO， 2 ～ 4 次的 IO 意味着查询时间只需 0.02 ～ 0.04 秒。

2.聚集索引和辅助索引

数据库中的 B+树索引可以分为聚集索引(clustered index)和辅助索引(secondary index)，但是不管是聚集索引还是辅助索引，其内部都是 B+树的结构，即高度平衡的，叶子节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

聚集索引，是按表中的主键顺序存放数据的，叶子节点也称为数据页，每个数据页通过双向链表和其他页进行关联。

对于辅助索引(SecondaryIndex，也称非聚集索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据。由于 InnoDB 存储引擎表是索引组织表，因此 InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

3.高度为 4 的 B+树能存储的数据?

假设每条 SQL 是 1kb，主键 id 是 bigint 类型，一棵高度为 4 的 b+树能存储多少数据。

在 innodb 存储引擎里面，最小的存储单元是页(page)，一个页的大小是 16KB。

查看页的大小语句:这就说明了一个页的大小为 16384B，也就是 16kb。

show variables like 'innodb_page_size';

#innodb_page_size 16384

一个页的大小为 16kb 假设一行数据的大小是 1kb，那么一个页可以存放 16 行这样的数据.那如果想查找某个页里面的一个数据的话，得首先找到他所在的页.innodb 存储引擎使用 B+树的结构来存储数据。如果是在主键上建立的索引就是聚簇索引，即只有在叶子节点才存储行数据，而非叶子节点里面的内容其实是键值和指向数据页的指针。

因此，我们首先解决一个简单一点的问题：如果是 2 层的 B+树，最多可以存储多少行数据？如果是 2 层的 B+树，即存在一个根节点和若干个叶子节点，那么这棵 B+树的存放总记录数为：根节点指针数单个叶子节点记录行数。因为单个页的大小为 16kb，而一行数据的大小为 1kb，也就是说一页可以存放 16 行数据。然后因为非叶子节点的结构是：“页指针+键值”，我们假设主键 ID 为 bigint 类型，长度为 8 字节（byte），而指针大小在 InnoDB 源码中设置为 6 字节（byte），这样一共 14 字节（byte），因为一个页可以存放 16k 个 byte，所以一个页可以存放的指针个数为 16384/14=1170 个。因此一个两层的 B+树可以存放的数据行的个数为：

一页可以存放1170个指针，一页可以存放16行的数据，1170x16=18720(行)

那么对于高度为 3 的 B+树呢？也就是说第一层的页，即根页可以存放 1170 个指针，然后第二层的每个页也可以存放 1170 个指针。这样一共可以存放 1170x1170 个指针，所以一共可以存放 1170x1170x16=21902400（2 千万左右）行记录。也就是说一个三层的 B+树就可以存放千万级别的数据了。

而每经过一个节点都需要 IO 一次，把这个页数据从磁盘读取到缓存，也就是说读取一个数据只需要三次 IO。继续来说，高度为 4 的 B+树呢？1170x1170x1170x16 约等于 200 亿。

4.为什么选用 B+树做索引?

众多的数据结构在逻辑层面可分为：线性结构和非线性结构。

线性结构有：数组、链表，基于它们衍生出的有哈希表（哈希表也称散列表）、栈、队列等。
非线性结构有：树、图。
还有其他数据结构如：跳表、位图也都由基础数据结构演化而来，不同的数据结构存在即都是为了解决某些场景问题。

索引虽然存储在磁盘上，但使用索引查找数据时，可以从磁盘先读取索引放到内存中，再通过索引从磁盘找到数据；再然后将磁盘读取到的数据也放到内存里。索引就让磁盘和内存强强联手.

b 树（balance tree）和 b+树应用在数据库索引，可以认为是 m 叉的多路平衡查找树，但是从理论上讲，二叉树查找速度和比较次数都是最小的，为什么不用二叉树呢？因为我们要考虑磁盘 IO 的影响，它相对于内存来说是很慢的。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少 IO 次数，对于树来说，IO 次数就是树的高度，而“矮胖”就是 b 树的特征之一，它的每个节点最多包含 m 个孩子，m 称为 b 树的阶。为什么不用 B 树呢？B+树，是 B 树的一种变体，查询性能更好。B+树相比于 B 树的查询优势：

B+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”。B 树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低；
B+树查询必须查找到叶子节点，B 树只要匹配到即可直接返回。因此 B+树查找更稳定（并不慢），必须查找到叶子节点；而 B 树，如果数据在根节点，最快，在叶子节点最慢，查询效率不稳定。
对于范围查找来说，B+树只需遍历叶子节点链表即可，并且不需要排序操作，因为叶子节点已经对索引进行了排序操作。B 树却需要重复地中序遍历，找到所有的范围内的节点。

总结选用 B+树的原因:

要尽少在磁盘做 I/O 操作。
要能尽快的按照区间高效地范围查找。

等值查询：哈希、跳表，不适合范围查询。
范围查询：树结构，但是会带来磁盘 IO 的情况，也会造成树过高的问题，所以衍生出 B 树，
而 B 树又因为范围查询的情况下，会一直到根节点再到叶子节点这样查询。所以延伸出了 B+树，解决范围查询的情况。
查询效率，B 树的话不稳定，O(1-logN)之间，而 B+树的话可以稳定在 O(logN)

5.为什么不用 hash 表做索引?

为什么用 B+树做索引，而不用 hash 表做索引

模糊查找不支持：哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表。而 B+树则可以通过最左前缀原则快速找到对应的数据。
范围查找不支持：如果我们要进行范围查找，例如查找 ID 为 100~400 的人，哈希表同样不支持，只能遍历全表。
哈希冲突问题：索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码的话，那么形成的索引结构将会是一条很长的链表，这样的话，查找的时间就会大大增加。

6.什么是索引组织表?

在 InnoDB 存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table)在 InnoDB 存储引擎表中，每张表都有个主键(Primary Key)，如果在创建表时没有显式地定义主键，则 InnoDB 存储引擎会按如下方式选择或创建主键:

首先判断表中是否有非空的唯一索引(Unique NOT NULL)，如果有，则该列即为主键。
如果不符合上述条件，InnoDB 存储引擎自动创建一个6 字节大小的指针。

当表中有多个非空唯一索引时，InnoDB 存储引警将选择建表时第一个定义的非空唯一索引为主键。这里需要非常注意的是，主键的选择根据的是定义索引的顺序，而不是建表时列的顺序。

7.innodb 逻辑存储结构

从 InnoDB 存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间(tablespace)。表空间又由段(segment)、区(extent)、页(page)组成。页在一些文档中有时也称为块(block)，InnoDB 存储引擎的逻辑存储结构大致如图所示。

8.共享表空间

在默认情况下 InnoDB 存储引擎有一个共享表空间 ibdata1，即所有数据都存放在这个表空间内。如果用户启用了参数 innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内。

如果启用了 innodb_file_per_table 的参数，需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲 Bitmap 页，其他类的数据，如回滚(undo)信息，插入缓冲索引页、系统事务信息，二次写缓冲(Double write buffer)等还是存放在原来的共享表空间内。这同时也说明了另一个问题:即使在启用了参数 innodb_fle_per_table 之后，共享表空间还是会不断地增加其大小。

9.innodb 中的段

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB 存储引擎表是索引组织的(index organized)，因此数据即索引，索引即数据。

数据段即为 B+树的叶子节点(Leaf node segment)
索引段即为 B+树的非索引节点(Non-leaf node segment)

10.innodb 中的区

区是由连续页组成的空间，在任何情况下每个区的大小都为 1MB。为了保证区中页的连续性，InnoDB 存储引擎一次从磁盘申请 4~5 个区。在默认情况下，InnoDB 存储引擎页的大小为 16KB，即一个区中一共有 64 个连续的页。

InnoDB1.0.x 版本开始引入压缩页，即每个页的大小可以通过参数 KEY_BLOCK_SIZE 设置为 2K、4K、8K，因此每个区对应页的数量就应该为 512、256、128。这是因为区的大小固定为 1M

InnoDB1.2x 版本新增了参数 innodb_page_size，通过该参数可以将默认页的大小设置为 4K、8K，但是页中的数据库不是压缩。这时区中页的数量同样也为 256、128。总之，不论页的大小怎么变化，区的大小总是为 1M。

11.innodb 中的页

同大多数数据库一样，InnoDB 有页(Page)的概念(也可以称为块)，页是 InnoDB 磁盘管理的最小单位。在 InnoDB 存储引擎中。默认每个页的大小为 16KB。而从 InnoDB1.2x 版本开始，可以通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K。若设置完成，则所有表中页的大小都为 innodb_page_size，不可以对其再次进行修改。除非通过 mysql dump 导入和导出操作来产生新的库。

在 InnoDB 存储引擎中，常见的页类型有:

数据页(B-tree Node)
undo 页(undo Log Page)
系统页(System Page)
事务数据页(Transaction system Page)
插入缓冲位图页(Insert Buffer Bitmap)
插入缓冲空闲列表页(Insert Buffer Free List)
未压缩的二进制大对象页(Uncompressed BLOB Page)
压缩的二进制大对象页(compressed BLOB Page)

12.innodb 中的行

InnoDB 存储引擎是面向行的(row-oriented)，也就说数据是按行进行存放的。每个页存放的行记录也是有硬性定义的，最多允许存放 16KB/2~200 行的记录，即 7992 行记录。这里提到了 row-oriented 的数据库，也就是说，存在有 column-oriented 的数据库。比如 ClockHouse，这对于数据仓库下的分析类 SQL 语句的执行及数据压缩非常有帮助。

行格式

Compact: 在 MySQL5.1 版本中，默认设置为 Compact 紧凑行格式
Redundant: Redundant 格式是为兼容之前版本而保留的
Dynamic: 动态行格式

用户可以通过命令查看当前表使用的行格式。其中 row_format 属性表示当前所使用的行记录结构类型。如:

SHOW TABLE STATUS LIKE 'mysql_user';

13.Compact 行格式

Compact 行记录是在 MySQL5.0 中引入的，其设计目标是高效地存储数据。简单来说，一个页中存放的行数据越多，其性能就越高。

Compact 行格式结构

变长字段长度列表	NULL 标志位	记录头信息	列 1 数据	列 2 数据	列数据 xxxx

Compact 行记录格式的首部是一个非 NULL 变长字段长度列表，并且其是按照列的顺序逆序放置的，其长度为:

若列的长度小于 255 字节，用 1 字节表示;
若大于 255 个字节，用 2 字节表示。

变长字段的长度最大不可以超过 2 字节，这是因在 MySQL 数据库中，VARCHAR 类型的最大长度限制为 65535。变长字段之后的第二个部分是 NULL 标志位，该位指示了该行数据中是否有 NULL 值，有则用 1 表示。该部分所占的字节应该为 1 字节。接下来的部分是记录头信息(record header)，固定占用 5 字节(40 位)，每位的含义见表 4-1。

最后的部分就是实际存储每个列的数据。需要要特别注意的是，NULL 不占该部分任何空间，即 NULL 除了占有 NULL 标志位，实际存储不占有任何空间。另外有一点需要注意的是，每行数据除了用户定义的列外，还有两个隐藏列，事务 ID 列和回滚指针列，分别为 6 字节和 7 字节的大小。若 InnoDB 表没有定义主键，每行还会增加一个 6 字节的 rowid 列。

SQL 中的NULL值认为是列中最小的值，放在 b+树的最左边.

IS NULL、IS NOT NULL、!=不一定不用索引，需要看优化器的优化情况.

14.Redundant 行格式

Redundant 行格式结构

Redundant 行格式是为了兼容以前版本的页格式，Redundant 是冗余的意思

字段长度偏移列表	记录头信息	列 1 数据	列 2 数据	列数据 xxxx

不同于 Compact 行记录格式，Redundant 行记录格式的首部是一个字段长度偏移列表，同样是按照列的顺序逆序放置的。若列的长度小于 255 字节，用 1 字节表示;若大于 255 字节，用 2 字节表示。第二个部分为记录头信息(record header)，不同于 Compact 行记录格式，Redundant 行记录格式的记录头占用 6 字节(48 位)，每位的含义见表 4-2。从表 4-2 中可以发现， n_felds 值代表一行中列的数量，占用 10 位。同时这也很好地解释了为什么 MySQL数据库一行支持最多的列为 1023。2 的 10 次方为 1024.

对于 VARCHAR 类型的 NULL 值，Redundant 行记录格式同样不占用任何存储空间，而 CHAR 类型的 NULL 值需要占用空间。

15.Compressed 和 Dynamic

InnoDB1.0.x 版本开始引入了新的文件格式(file format，用户可以理解为新的页格式)，以前支持的 Compact 和 Redundant 格式称为 Antelope 文件格式，新的文件格式称为 Barracuda 文件格式。Barracuda 文件格式下拥有两种新的行记录格式:Compressed 和 Dynamic

新的两种记录格式对于存放在 BLOB 中的数据采用了完全的行溢出的方式，如图下图所示，在数据页中只存放 20 个字节的指针，实际的数据都存放在 Off Page 中，而之前的 Compact 和 Redundant 两种格式会存放 768 个前缀字节。

Compressed 行记录格式的另一个功能就是，存储在其中的行数据会以 zlib 的算法进行压缩，因此对于 BLOB、TEXT、VARCHAR 这类大长度类型的数据能够进行非常有效的存储。

Compressed和Dynamic行格式:

Compact 和 Redundant行格式:

16.行溢出数据

在 MySQL 中一行除了 blob 及 text 类的大字段之外，其余字段的长度之和不能超过 65535，最大支持 65535 字节，实际执行会报错，实际支持 65532 字节.使用 latin1 字符集时是 65532 字节，使用 utf8 编码时，utf8 编码占 3 位，最大支持 21844 字节.

mysql 理论支持的Row Size Limit 是 65535 为什么实际是 65532

这是因为 MySQL 需要额外的字节来存储一些内部数据，例如行格式信息和 NULL 位图。

具体来说，每个行都需要几个字节来存储行格式信息和 NULL 位图。行格式信息用于指示行的格式，例如是否使用动态行格式或静态行格式。NULL 位图用于表示行中哪些列具有 NULL 值。这些额外的字节会占用行的空间，从而减少了实际可用的行大小。NULL 位图占一个字节

任何存储引擎都不能超过65535

mysql> CREATE TABLE t (a VARCHAR(10000)， b VARCHAR(10000)，

c VARCHAR(10000)， d VARCHAR(10000)， e VARCHAR(10000)，

f VARCHAR(10000)， g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;

ERROR 1118 (42000): Row size too large. The maximum row size for the used

table type， not counting BLOBs， is 65535. This includes storage overhead，

check the manual. You have to change some columns to TEXT or BLOBs

mysql> CREATE TABLE t (a VARCHAR(10000)， b VARCHAR(10000)，

c VARCHAR(10000)， d VARCHAR(10000)， e VARCHAR(10000)，

f VARCHAR(10000)， g VARCHAR(6000)) ENGINE=MyISAM CHARACTER SET latin1;

ERROR 1118 (42000): Row size too large. The maximum row size for the used

table type， not counting BLOBs， is 65535. This includes storage overhead，

check the manual. You have to change some columns to TEXT or BLOBs

非空可以创建成功:

CREATE TABLE t2

(c1 VARCHAR(65533) NOT NULL)

ENGINE = InnoDB CHARACTER SET latin1;

可以为空的场景:

CREATE TABLE t2222

(c1 VARCHAR(65532) DEFAULT NULL)

ENGINE = InnoDB CHARACTER SET latin1;

65535 是指所有 varchar 列的总和，不是单个 varchar 的字节数量

一个页为 16kb，为 16384 字节，如何存储 65535 字节的呢?

但是当发生行溢出时，数据存放在项类型为 Uncompress BLOB 页中。

# 执行报错

CREATE TABLE tmp_max_length_one_field

(

a VARCHAR(65535)

) CHARSET = latin1

ENGINE = InnoDB;

# 可以执行

CREATE TABLE tmp_max_length_one_field

(

a VARCHAR(65532)

) CHARSET = latin1

ENGINE = InnoDB;

# 报错

CREATE TABLE tmp_max_length_mut_field

(

a VARCHAR(22222)，

b VARCHAR(22222)，

c VARCHAR(22222)，

d VARCHAR(22222)

) CHARSET = latin1

ENGINE = InnoDB;

17.字段个数

Column Count Limits

Column Count Limits 表字段最大限制.官方文档的内容如下，主要意思是字段个数限制达不到理想的 4096 个，且和字段类型有关。

MySQL 中 innodb 引擎的字段上限是 1017。
MySQL 中 MyISAM 引擎表最多可以存 2598 个字段。

受影响的因素:

存储引擎
表的单行最大行影响
单个列的存储要求

18.行最大字节

Row Size Limits

Row Size Limits 行的最大字节数.MySQL 表的内部表示具有 65535 字节的最大行大小限制，即使存储引擎能够支持更大的行。BLOB 和 TEXT 列只占行大小限制的 9 到 12 个字节，因为它们的内容与行的其余部分分开存储。

对于 4KB、8KB、16KB 和 32KB InnoDB_page_size 设置，应用于本地存储在数据库页面中的数据的 InnoDB 表的最大行大小略小于页面的一半。例如，对于默认的 16KB InnoDB 页面大小，最大行大小略小于 8KB。对于 64KB 的页面，最大行大小略小于 16KB。

如果包含可变长度列的行超过了 InnoDB 的最大行大小，InnoDB 会选择可变长度列用于外部页外存储，直到该行符合 InnoDB 的行大小限制。对于页外存储的可变长度列，本地存储的数据量因行格式而异。

不同的存储格式使用不同数量的页眉和尾部数据，这会影响行的可用存储量。

19.数据页对行大小限定

在 InnoDB 中，每个数据页（database page）的大小可以通过设置 innodb_page_size 参数进行配置，常见的大小包括 4KB、8KB、16KB 和 32KB，还有更大的 64KB 页。

在 InnoDB 中，行数据存储在数据库页内部。然而，为了保证数据库的性能和效率，InnoDB 对于每个页内的数据行大小有一定的限制。对于 4KB、8KB、16KB 和 32KB 的页面大小设置，InnoDB 将数据行大小限制在略小于半个数据库页的大小。这意味着，对于 4KB 页，数据行大小将略小于 2KB，对于 8KB 页，数据行大小将略小于 4KB，以此类推。

对于 64KB 的页面大小设置，InnoDB 将数据行大小限制在略小于 16KB。换句话说，对于 64KB 页，数据行大小将略小于 16KB。

这些限制是为了确保在数据库页内存储的数据行不会过大，从而避免内存浪费和性能下降。同时，这些限制还有助于在处理和查询数据时提高效率，因为较小的数据行大小可以提高数据访问速度和内存利用率。

这些限制是为了优化 InnoDB 存储引擎的性能和效率，同时避免过大的数据行带来的问题。

20.innodb 的页结构

页的总体结构图:

21.数据页的结构

InnoDB为了不同的目的而设计了许多种不同类型的页。常见的页类型有数据页(索引页)、Undo 页、系统页、事务数据页等。InnoDB 数据页由以下 7 个部分组成

File Header(文件头)
Page Header(页头)
Infimun 和 Supremum Records
User Records(用户记录，即行记录)
Free Space(空闲空间)
Page Directory(页目录)
File Trailer(文件结尾信息)

字段名	中文名	大小	简单描述
File Header	文件头部	38 字节	页的一些通用信息
Page Header	页面头部	56 字节	数据页专有的一些信息
Infimum + Supremum	最小记录和最大记录	26 字节	两个虚拟的行记录
User Records	用户记录	不确定	实际存储的行记录内容（大小不确定）
Free Space	空闲空间	不确定	页中尚未使用的空间
Page Directory	页面目录	不确定	页中的某些记录的相对位置
File Trailer	文件尾部	8 字节	校验页是否完整

22.File Header

File Header 用来记录页的一些头信息，由表表 4-3 中 8 个部分组成，共占用 38 字节。

FILE_PAGE_TYPE 页类型:

23.Page Header

接着 File Header 部分的是 Page Header，该音部分用来记录数据页的状态信息，由 14 个部分组成，共占用 56 字节，如表 4-5 所示。

字段名	大小	描述
PAGE_N_DIR_SLOTS	2 字节	页目录中的槽数量
PAGE_HEAP_TOP	2 字节	未使用的空间最小地址，即 Free Space 之后的地址
PAGE_N_HEAP	2 字节	本页中的记录数量（包括最小、最大记录和删除的记录）
PAGE_FREE	2 字节	第一个已标记为删除的记录地址
PAGE_GARBAGE	2 字节	已删除记录占用的字节数
PAGE_LAST_INSERT	2 字节	最后插入记录的位置
PAGE_DIRECTION	2 字节	记录插入的方向
PAGE_N_DIRECTION	2 字节	一个方向连续插入的记录数量
PAGE_N_RECS	2 字节	该页中记录的数量（不包括最小、最大记录和删除的记录）
PAGE_MAX_TRX_ID	8 字节	修改当前页的最大事务 ID
PAGE_LEVEL	2 字节	当前页在 B+树中所处的层级
PAGE_INDEX_ID	8 字节	索引 ID，表示当前页属于哪个索引
PAGE_BTR_SEG_LEAF	10 字节	B+树叶子段的头部信息（仅在 B+树的 Root 页定义）
PAGE_BTR_SEG_TOP	10 字节	B+树非叶子段的头部信息（仅在 B+树的 Root 页定义）

Infimun 和 Supremum Records

在 InnoDB 存储引擎中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。Infimum 记录是比该页中任何主键值都要小的值， Supremum 指比任何可能大的值还要大的值。这两个值在页创建时被建立，并且在任何情况下不会被删除。

24.User Records

存储行格式的地方，行格式的记录头信息

字段名	大小	描述
预留位 1	1 位	没有使用
预留位 2	1 位	没有使用
delete_mask	1 位	标记该记录是否被删除
min_rec_mask	1 位	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4 位	表示当前记录拥有的记录数
heap_no	13 位	表示当前记录在记录堆的位置信息
record_type	3 位	表示当前记录的类型<br />0 表示普通记录<br />1 表示 B+树非叶子节点记录<br />2 表示最小记录<br />3 表示最大记录
next_record	16 位	表示下一条记录的相对位置

delete_mask:删除标记为 1 记录不会立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。

next_record(单链表):表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。比方说第一条记录的 next_record 值为 32，意味着从第一条记录的真实数据的地址处向后找 32 个字节便是下一条记录的真实数据。

注意：

下一条记录指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。而且规定 Infimum 记录（也就是最小记录）的下一条记录就是本页中主键值最小的用户记录

未被删除的记录(delete_mask = 0)会按照主键从小到大的顺序形成了一个单链表

25.Free Space

自己存储的记录会按照我们指定的行格式存储到User Records部分。但是在一开始生成页的时候，其实并没有User Records这个部分，每当我们插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了，这个过程的图示如下：

26.Page Directory(页目录)

页目录的过程:

将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。
每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。
将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方就是所谓的Page Directory，也就是页目录（此时应该返回头看看页面各个部分的图）。页面目录中的这些地址偏移量被称为槽（英文名：Slot），所以这个页面目录就是由槽组成的。

注意

对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。

分组过程

初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。
之后每插入一条记录，都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的n_owned值加 1，表示本组内又添加了一条记录，直到该组中的记录数等于 8 个。
在一个组中的记录数等于 8 个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中 4 条记录，另一个 5 条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量。

查询记录的过程:

过程分为两步：

通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录。
通过记录的 next_record 属性遍历该槽所在的组中的各个记录。

因为各个槽代表的记录的主键值都是从小到大排序的，所以我们可以使用所谓的二分法来进行快速查找。4 个槽的编号分别是：0、1、2、3、4，所以初始情况下最低的槽就是low=0，最高的槽就是high=4。比方说我们想找主键值为6的记录，过程是这样的：

计算中间槽的位置：(0+4)/2=2，所以查看槽2对应记录的主键值为8，又因为8 > 6，所以设置high=2，low保持不变。
重新计算中间槽的位置：(0+2)/2=1，所以查看槽1对应的主键值为4，又因为4 < 6，所以设置low=1，high保持不变。
因为high - low的值为 1，所以确定主键值为5的记录在槽2对应的组中。此刻我们需要找到槽2中主键值最小的那条记录，然后沿着单向链表遍历槽2中的记录。但是我们前面又说过，每个槽对应的记录都是该组中主键值最大的记录，这里槽2对应的记录是主键值为8的记录，怎么定位一个组中最小的记录呢？别忘了各个槽都是挨着的，我们可以很轻易的拿到槽1对应的记录（主键值为4），该条记录的下一条记录就是槽2中主键值最小的记录，该记录的主键值为5。所以我们可以从这条主键值为5的记录出发，遍历槽2中的各条记录，直到找到主键值为6的那条记录即可。由于一个组中包含的记录条数只能是 1~8 条，所以遍历一个组中的记录的代价是很小的。

27.File Trailer

为了检测页是否已经完整地写入磁盘(如可能发生的写入过程中磁盘损坏、机器关机等)，InnoDB 存储引擎的页中设置了File Trailer 部分。

File Trailer 只有一个 FIL_PAGE_END_LSN 部分，占用 8 字节。前 4 字节代表该页的 checksum 值，最后 4 字节和 File Header 中的 FIL_PAGE_LSN 相同。将这两个值与 File Header 中的 FIL_PAGE_SPACE_OR_CHKSUM 和 FIL_PAGE_LSN 值进行比较看是否一致(checksum 的比较需要通过 InnoDB 的 checksum 函数来进行比较，不是简单的等值比较)，以此来保证页的完整性(not corrupted)。

在默认配置下，InnoDB 存储引擎每次从磁盘读取一个页就会检测该页的完整性，即页是否发生损坏，这就是通过 File Trailer 部分进行检测，而该部分的检测会有一定的开销。用户可以通过参数 innodb_log_checksums 来开启或关闭对这个页完整性的检查。默认是开启的。

五.备份与恢复

1.主从复制的原理?

复制(replication)是 MySQL 数据库提供的一种高可用高性能的解决方案，一般用来建立大型的应用。总体来说，replication 的工作原理分为以下 3 个步骤:

主服务器(master)把数据更改记录到二进制日志(binlog)中。
从服务器(slave)把主服务器的二进制日志复制到自己的中继日志(relaylog)中。
从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。

复制的工作原理并不复杂，其实就是一个完全备份加上二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上实时在进行中。这里特别需要注意的是，复制不是完全实时地进行同步，而是异步实时。这中间存在主从服务器之间的执行延时，如果主服务器的压力很大，则可能导致主从服务器延时较大。

2.什么是 SBR 和 RBR?

主从复制 binlog 日志有三种记录方式

Replication 之所以能够工作，主要还是归结于binlog（binary log），所以在 Replication 模式下必须开启 binlog 功能；slave 从 masters 上增量获取 binlog 信息，并在本地应用日志中的变更操作（即重放）。变更操作将根据选定的格式类型写入 binlog 文件，目前支持三种 format：

statement-based Replication（SBR）：master 将 SQL statements 语句写入 binlog，slave 也将 statements 复制到本地执行；简单而言，就是在 master 上执行的 SQL 变更语句，也同样在 slaves 上执行。SBR 模式是 MySQL 最早支持的类型，也是 Replication 默认类型。
row-based Replication（RBR）：master 将每行数据的变更信息写入 binlog，每条 binlog 信息表示一行（row）数据的变更内容，对于 slaves 而言将会复制 binlog 信息，然后单条或者批量执行变更操作；
mix-format Replication：混合模式，在这种模式下，master 将根据存储引擎、变更操作类型等，从 SBR、RBR 中来选择更合适的日志格式，默认为 SBR；具体选择那种格式，这取决于变更操作发生的存储引擎、statement 的类型以及特征，优先选择“数据一致性”最好的方式（RBR），然后才兼顾性能，比如 statement 中含有“不确定性”方法或者批量变更，那么将选择 RBR 方式，其他的将选择 SBR 以减少 binlog 的大小。我们建议使用 mix 方式。

SBR 和 RBR 都有各自的优缺点，对于大部分用而言，mix 方式在兼顾数据完整性和性能方面是最佳的选择。

3.SBR 和 RBR 的优缺点?

SBR 的优点

因为 binlog 中只写入了变更操作的 statements，所以日志量将会很小；
当使用 SQL 语句批量更新、删除数据时，只需要在 binlog 中记录 statement 即可，可以大大减少 log 文件对磁盘的使用
当然这也意味着 slave 复制信息量也更少，以及通过 binlog 恢复数据更加快速；

SBR 的缺点

有些变更操作使用 SBR 方式会带来数据不一致的问题，一些结果具有不确定性的操作使用 SBR 将会引入数据不一致的问题。

statement 中如果使用了 UDF(User Defination Fuction)，UDF 的计算结果可能依赖于 SQL 执行的时机和系统变量，这可能在 slave 上执行的结果与 master 不同，此外如果使用了 trigger，也会带来同样的问题；
statement 中如果使用了如下函数的（举例）：UUID()，SYSDATE()，RAND()等，不过 NOW()函数可以正确的被 Replication（但在 UDF 或者触发器中则不行）；这些函数的特点就是它们的值依赖于本地系统，RAND()本身就是随机所以值是不确定的。如果 statement 中使用了上述函数，那么将会在日志中输出 warning 信息；
对于“INSERT...SELECT”语句，SBR 将比 RBR 需要更多的行锁。（主要是为了保障数据一致性，需要同时锁定受影响的所有的行，而 RBR 则不必要）；
对于 InnoDB，使用“AUTO_INCREMENT”的 insert 语句，将会阻塞其他“非冲突”的 INSERT。（因为 AUTO_INCREMENT，为了避免并发导致的数据一致性问题，只能串行，但 RBR 则不需要）；
对于复杂的 SQL 语句，在 slaves 上仍然需要评估（解析）然后才能执行，而对于 RBR，SQL 语句只需要直接更新相应的行数据即可；在 slave 上评估、执行 SQL 时可能会发生错误，这种错误会随着时间的推移而不断累加，数据一致性的问题或许会不断增加。

RBR 的优点：

所有的变更操作，都可以被正确的 Replication，这是最安全的方式；
对于“INSERT…SELECT”、包含“AUTO_INCREMENT”的 inserts、没有使用索引的 UPDATE/DELETE，相对于 SBR 将需要更少的行锁。（意味着并发能力更强）；

RBR 的缺点：

最大的缺点：就是 RBR 需要更多的日志量。任何数据变更操作都将被写入 log，受影响的每行都要写入日志，日志包含此行所有列的值（即使没有值变更的列）；
因此 RBR 的日志条数和尺寸都将会远大于 SBR，特别是在批量的 UPDATE/DELETE 时，可能会产生巨大的 log 量，反而对性能带来影响，尽管这确实保障了数据一致性，确导致 Replication 的效率较低；

4.主从复制有几种方式?

同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。
异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

Master 处理事务过程中，提交完事务后，必须等至少一个 Slave 将收到的 binlog 写入 relay log 返回 ack 才能继续执行处理用户的事务。

相关配置

#【这里MySQL5.5并没有这个配置，MySQL5.7为了解决半同步的问题而设置的】

rpl_semi_sync_master_wait_point=AFTER_COMMIT

#（最低必须收到多少个slave的ack）

rpl_semi_sync_master_wait_for_slave_count=1

#（等待ack的超时时间）

rpl_semi_sync_master_timeout=100

增强半同步和半同步不同是，等待 ACK 时间不同

rpl_semi_sync_master_wait_point=AFTER_SYNC（唯一区别）

半同步的问题是因为等待 ACK 的点是 Commit 之后，此时 Master 已经完成数据变更，用户已经可以看到最新数据，当 Binlog 还未同步到 Slave 时，发生主从切换，那么此时从库是没有这个最新数据的，用户又看到老数据。

增强半同步将等待 ACK 的点放在提交 Commit 之前，此时数据还未被提交，外界看不到数据变更，此时如果发送主从切换，新库依然还是老数据，不存在数据不一致的问题。

5.主从复制有什么好处?

在从服务器可以执行查询工作，降低主服务器压力；（主库写，从库读，降压）读写分离
对主服务器进行数据备份，避免备份期间影响主服务器服务；容灾
当主服务器出现问题时，可以切换到从服务器。提高可用性

6.主节点自动切换

要让 MySQL 集群自动更换主节点而不被程序感知，可以按照以下步骤进行设置：

配置 MySQL 集群：确保您的 MySQL 集群已正确设置和配置，并且已经正常运行。确保您有一个可用的备用节点。
使用虚拟 IP（VIP）：为 MySQL 集群配置一个虚拟 IP（VIP），它将用作主节点的标识。这个 VIP 将会漂移到新的主节点上。
监控主节点状态：设置一个监控机制来检测主节点的状态。您可以使用一个脚本或者专门的监控工具来监视主节点是否可用。
监控节点之间的心跳：使用一个心跳监控机制，确保集群中的所有节点都能够相互通信。常用的方法是使用软件或硬件心跳。
监测主节点的宕机：当监控机制检测到主节点不可用时，它应该触发一系列动作来进行故障转移。
故障转移操作：在主节点宕机后，您需要执行一系列操作来进行故障转移，将备用节点升级为新的主节点。
a. 在备用节点上启动 MySQL 服务，并确保它已经成为新的主节点。
b. 将 VIP 从原来的主节点迁移到新的主节点上，这样客户端程序就会将请求发送到新的主节点。
c. 更新集群配置信息，确保所有节点都知道新的主节点位置。
更新程序连接信息：在故障转移完成后，您需要更新客户端程序的连接信息，使其连接到新的主节点。这可以通过动态获取主节点位置并更新程序配置来实现，或者使用服务发现机制来自动更新连接信息。

通过以上步骤，您可以实现 MySQL 集群的自动主节点切换，而不需要手动干预并且程序可以无感知地连接到新的主节点。请注意，这是一个高级配置，需要一定的专业知识和经验，确保在进行任何更改之前备份您的数据库以防止数据丢失。

7.主从同步延迟原因

MySQL的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高。Slave的SQL Thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随即的，不是顺序的，成本高很多。另一方面，由于SQL Thread也是单线程的，当主库的并发较高时，产生的 DML 数量超过slave的SQL Thread所能处理的速度，或者当slave中有大型query语句产生了锁等待那么延时就产生了。

常见原因：

Master 负载过高
Slave 负载过高
网络延迟
机器性能太低
MySQL 配置不合理

8.主从延时排查方法

通过监控 show slave status 命令输出的Seconds_Behind_Master参数的值来判断：

NULL，表示 io_thread 或是 sql_thread 有任何一个发生故障；
0，该值为零，表示主从复制良好；
正值，表示主从已经出现延时，数字越大表示从库延迟越严重

9.解决数据丢失问题

解决数据丢失的问题：

半同步复制:从 MySQL5.5 开始，MySQL 已经支持半同步复制了，半同步复制介于异步复制和同步复制之间，主库在执行完事务后不立刻返回结果给客户端，需要等待至少一个从库接收到并写到 relay log 中才返回结果给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一个TCP/IP往返耗时的延迟。
主库配置sync_binlog=1，innodb_flush_log_at_trx_commit=1 sync_binlog的默认值是 0，MySQL 不会将binlog同步到磁盘，其值表示每写多少binlog同步一次磁盘。innodb_flush_log_at_trx_commit为 1 表示每一次事务提交或事务外的指令都需要把日志 flush 到磁盘。

注意:将以上两个值同时设置为 1 时，写入性能会受到一定限制，只有对数据安全性要求很高的场景才建议使用，比如涉及到钱的订单支付业务，而且系统 I/O 能力必须可以支撑！

10.解决从库复制延迟的问题

架构方面

业务的持久化层的实现采用分库架构，mysql 服务可平行扩展，分散压力。
单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。
服务的基础架构在业务和 mysql 之间加入 memcache 或者 redis 的 cache 层。降低 mysql 的读压力。
不同业务的 mysql 物理上放在不同机器，分散压力。
使用比主库更好的硬件设备作为 slave，mysql 压力小，延迟自然会变小。

硬件方面

采用好服务器，比如 4u 比 2u 性能明显好，2u 比 1u 性能明显好。
存储用 ssd 或者盘阵或者 san，提升随机写的性能。
主从间保证处在同一个交换机下面，并且是万兆环境。
总结，硬件强劲，延迟自然会变小。一句话，缩小延迟的解决方案就是花钱和花时间。

mysql 主从同步加速

sync_binlog在 slave 端设置为 0
–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。
直接禁用slave端的 binlog
slave端，如果使用的存储引擎是 innodb，innodb_flush_log_at_trx_commit =2

从文件系统本身属性角度优化master 端修改 linux、Unix 文件系统中文件的 etime 属性，由于每当读文件时 OS 都会将读取操作发生的时间回写到磁盘上，对于读操作频繁的数据库文件来说这是没必要的，只会增加磁盘系统的负担影响 I/O 性能。可以通过设置文件系统的 mount 属性，组织操作系统写 atime 信息，在 linux 上的操作为：打开/etc/fstab，加上 noatime 参数/dev/sdb1 /data reiserfs noatime 1 2 然后重新 mount 文件系统#mount -oremount /data
同步参数调整主库是写，对数据安全性较高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置是需要的而 slave 则不需要这么高的数据安全，完全可以讲sync_binlog设置为 0 或者关闭 binlog，innodb_flushlog也可以设置为 0 来提高 sql 的执行效率

11.mysql 的热备和冷备?

冷备份发生在数据库已经正常关闭的情况下，将关键性文件拷贝到另外位置的一种说法
热备份是在数据库运行的情况下，采用归档方式备份数据的方法

热备 Hot Backup 是指数据库运行中直接备份，对正在运行的数据库操做没有任何的影响。这种方式在 MySQL 官方手册中称为 Online Backup(在线备份)。

冷备 Cold Backup 是指备份操作是在数据库停止的情况下，这种备份最为简单，一般只需要复制相关的数据库物理文件即可。这种方式在 MySQL 官方手册中称为 Offline Backup(离线备份)。对于 InnoDB 存储引擎的冷备非常简单，只需要备份 MySQL 数据库的 frm 文件，共享表空间文件，独立表空间文件(*.ibd)，重做日志文件。另外建议定期备份 MySQL 数据库的配置文件 my.cnf，这样有利于恢复的操作。

温备 Warm Backup 备份同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，如加一个全局读锁以保证备份数据的一致性.

12.冷备优缺点?

冷备优点

是非常快速的备份方法（只需拷贝文件）
容易归档（简单拷贝即可）
容易恢复到某个时间点上（只需将文件再拷贝回去）
能与归档方法相结合，作数据库“最新状态”的恢复。
低度维护，高度安全。

冷备缺点

单独使用时，只能提供到“某一时间点上”的恢复。
在实施备份的全过程中，数据库必须要作备份而不能作其它工作。也就是说，在冷备份过程中，数据库必须是关闭状态。
若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度会很慢。
不能按表或按用户恢复。

13.mysql 备份工具?

常用备份工具

mysql 复制
逻辑备份(mysqldump，mydumper)
物理备份(copy，xtrabackup)

备份工具差异对比

1.mysql 复制相对于其他的备份来说，得到的备份数据比较实时。
2.逻辑备份：分表比较容易。

mysqldump 备份数据时是将所有 sql 语句整合在同一个文件中；
mydumper 备份数据时是将 SQL 语句按照表拆分成单个的 sql 文件，
每个 sql 文件对应一个完整的表。

3.物理备份：拷贝即可用，速度快。

copy:直接拷贝文件到数据目录下，可能引起表损坏或者数据不一致。
xtrabackup 对于 innodb 表是不需要锁表的，对于 myisam 表仍然需要锁表。

14.Canal 原理

主从复制步骤：

将 Master 的 binary-log 日志文件打开，mysql 会把所有的 DDL，DML，TCL 写入 BinaryLog 日志文件中 Master 会生成一个 log dump 线程，用来给从库的 i/o 线程传 binlog 从库的 i/o 线程去请求主库的 binlog，并将得到的 binlog 日志写到中继日志（relaylog）中从库的 sql 线程，会读取 relaylog 文件中的日志，并解析成具体操作，通过主从的操作一致，而达到最终数据一致，而 Canal 的原理就是伪装成 Slave 从 Binlog 中复制 SQL 语句或者数据。

Canal 作为 MySQL binlog 增量获取和解析工具，可将变更记录投递到 MQ 系统中，比如 Kafka/RocketMQ，可以借助于 MQ 的多语言能力，因此我们可以使用下面这种方案来同步数据。

15.Mysql 三种集群模式

MySQL 集群主要有三种模式：

主从复制
多主复制
Galera Cluster

主从复制:

主从复制是 MySQL 集群的最基本形式，主要思想是将一台 MySQL 服务器定义为主服务器，另外一台或多台 MySQL 服务器定义为从服务器。主服务器上的数据修改会同步到从服务器上，从服务器只能读取数据，不具备写入的能力。主从复制的主要原理是通过 binlog 来实现，binlog 是 MySQL 数据库的二进制日志，记录了所有的 SQL 语句以及数据变化的情况，从服务器通过读取主服务器上的 binlog 进行数据同步。

主从复制的使用场景主要是读写分离或者备份。当主服务器承担写入操作时，从服务器可以负责读取操作，从而实现负载均衡；同时，从服务器可以作为备份数据库，主服务器出现故障时可以通过从服务器来恢复数据。

多主复制:

多主复制和主从复制不同的是，多主复制中每个节点都可以进行读写操作，同时节点之间的数据同步也是相互的。当一个节点修改数据后，相应的变化会自动同步到其它节点中。多主复制的主要原理是通过对每个节点的 binlog 进行多点复制，实现数据同步。

多主复制主要的使用场景是业务分区。将不同的业务模块分配给不同的节点，每个节点可以独立地进行数据修改，同时数据也会自动同步到其他节点中。

Galera Cluster:

Galera Cluster 是一种基于 MySQL InnoDB 存储引擎的集群解决方案，是一种完全同步的多主复制技术。它与多主复制最大的不同是采用了同步复制的方式，保证了数据修改的可靠性，同时支持自动分片。

Galera Cluster 的主要实现原理是采用了一种称为”Wating Commit”的机制，当一个数据变化时，该数据不仅要向主节点同步，还要向集群中其它节点同时进行同步，直到所有节点的数据完全一致，提交操作才算成功。

由于 Galera Cluster 中没有单点故障，因此该模式被广泛应用于高可用性的业务场景。同时，全局序列号机制和自动分片功能也使得 Galera Cluster 在处理大规模数据时表现出色。

MySQL 集群的三种模式各具特点，可以根据不同的业务需求进行选择。如果需要进行读写分离或备份操作，主从复制是不错的选择；如果需要进行业务分区，多主复制可以选用；如果需要高可用性和自动分片功能，那么 Galera Cluster 是不二之选。

16.数据迁移方案

数据迁移执行流程，大概有 10 个步骤：

环境准备：线上库配置完成
全量同步：数据迁移工具上新建 2 张表（积分表、明细表）的全量任务
增量同步：全量迁移完成后开启增量(自动回溯全量开始时间，消息多次消费会进行幂等)
数据校验：全量数据校验，查看数据是否一致
切流测试：改造代码预发测试（采集线上流量进行回放，多种 case 跑一下，切流开关等校验），没问题发布上线
二次校验：再次全量进行校验&订正（数据追平）
开启双写：打开双写（保证数据实时性）既写老库，又写新库
开启读灰度：低峰时段，进行灰度切流userId%x，进行验证，逐步流量打开，持续观察
只写新库：写流量切到新库，只写新库，不写老库。完成数据迁移方案
迁移完成：系统稳定运行一段时间，迁移&双写代码下线，老库进行资源释放

六.数据库缓存一致性

1.情况总结

两种更新情况更新缓存的顺序是不确定的
先删缓存读到错误数据插入缓存
后删缓存删除了正确的数据
延迟双删删除缓存两次

2.先更新数据库，再更新缓存

并发场景下，会出现缓存和数据库不一致的情况,请求 A 更新数据库为 1,这时还未更新缓存,请求 B 更新了数据库为 2,并且更新了缓存为 2,然后请求 A 更新了缓存为 1,导致数据库为 2,缓存为 1,数据库和缓存不一致。

3.先更新缓存，再更新数据库

请求 A 更新缓存为 1,这时还未更新数据库,请求 B 更新了缓存为 2,并且更新了数据库为 2,然后请求 A 更新了数据库为 1,导致缓存为 2,数据库为 1,数据库和缓存不一致。

4.先删除缓存，再更新数据库

请求 A 更新的时候先删除缓存,还未更新数据库,此时,请求 B 查询缓存,未查到数据,请求 B 查询数据库,然后更新缓存为 20,然后请求 A 更新数据库为 21,导致缓存和数据库不一致。这种情况是因为更新数据和读数据导致的缓存和数据库一致性问题。前面提到的两种都是覆盖导致的问题。

5.先更新数据库，再删除缓存

请求 A 读取数据时,缓存没有命中,读取数据库的值为 20。此时请求 B 是更新操作,先更新了数据库为 21,然后删除了缓存。然后请求 A 接着操作,写缓存将 20 的数据写入到缓存中,导致数据库与缓存数据不一致。

在更新数据时，不更新缓存，而是删除缓存中的数据。然后，到读取数据时，发现缓存中没了数据之后，再从数据库中读取数据，更新到缓存中。Cache Aside 策略，中文是叫旁路缓存策略。这种策略出现问题的概率最小,因为一般情况下读是比写要快很多的。

从上面的理论上分析，先更新数据库，再删除缓存也是会出现数据不一致性的问题，但是在实际中，这个问题出现的概率并不高。

因为缓存的写入通常要远远快于数据库的写入，所以在实际中很难出现请求 B 已经更新了数据库并且删除了缓存，请求 A 才更新完缓存的情况。

所以，「先更新数据库 + 再删除缓存」的方案，是可以保证数据一致性的.

如何保证 2 个消息成功呢?

「消息队列来重试缓存的删除」
「订阅 MySQL binlog 再操作缓存」

6.延迟双删

#删除缓存

redis.delKey(X)

#更新数据库

db.update(X)

#睡眠

Thread.sleep(N)

#再删除缓存

redis.delKey(X)

加了个睡眠时间，主要是为了确保请求 A 在睡眠的时候，请求 B 能够在这这一段时间完成「从数据库读取数据，再把缺失的缓存写入缓存」的操作，然后请求 A 睡眠完，再删除缓存。所以，请求 A 的睡眠时间就需要大于请求 B 「从数据库读取数据 + 写入缓存」的时间。

但是具体睡眠多久其实是个玄学，很难评估出来，所以这个方案也只是尽可能保证一致性而已，极端情况下，依然也会出现缓存不一致的现象。因此，还是比较建议用「先更新数据库，再删除缓存」的方案。

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

03-MySql高阶篇

1.什么是 Mysql

2.内部结构和便携性

3.Mysql8.0 新增功能

4.数据库与实例?

5.mysql 体系结构

6.其他存储引擎?

7.数据库语言

8.什么是异步 IO?

9.QPS 和 TPS

10.数据库三大范式

11.什么是视图?

12.常见约束

13.drop,truncate,delete 区别?

14.SQL 运行顺序?

15.JDBC 操作流程?

二.Innodb 引擎特性

1.innodb 概述?

2.InnoDB 的关键特性?

3.innodb 和 myisam 的区别

4.表锁和行锁的区别?

5.什么是插入缓冲?

6.插入缓冲的实现

7.什么是 merge insert buffer?

8.什么是 Change Buffer?

9.什么是二次写?

10.什么是自适应哈希索引?

11.什么是刷新邻接页?

12.什么是 MRR 优化?

13.什么是 ICP 优化?

14.ICP 的限制条件?

15.如何避免离散读?

16.说说 purge 操作?

17.二进制和事务提交一致性?

18.InnoDB 的后台线程?

19.innodb 内存分配?

20.LRU、Free、Flush 区别?

21.热点数据不被冲掉?

22.压缩页的表?

23.什么是 checkpoint?

三.Mysql 文件

1.mysql 文件有哪些?

2.什么是物理日志和逻辑日志?

3.mysql 的配置文件

4.表空间文件?

5.二进制日志的作用?

6.binlog 的刷盘时机

7.说说 binlog_format 参数?

8.什么是 redo log?

9.重做日志块?

10.重做日志缓冲?

11.重做日志文件?

12.重做日志块与重做日志文件

13.重做日志的格式?

14.重做日志刷盘机制

15.什么是 LSN?

16.binlog 和重做日志区别?

17.什么是 undo log?

18.举例说明重做日志的作用

19.undo log 和 redo log?

20.慢查询日志

四.Mysql 表数据结构

1.mysql 的索引数据结构?

2.聚集索引和辅助索引

3.高度为 4 的 B+树能存储的数据?

4.为什么选用 B+树做索引?