这里是数据库MYSQL的一些八股知识

MySQL默认端口3306

1.基础知识

1.MyISAM与InnoDB区别

存储引擎（Storage Engine）是用来定义数据表如何存储、读取、管理和处理数据的底层机制。换句话说，存储引擎决定了 MySQL 如何在物理层面上存储数据、如何建立索引、如何执行查询，以及支持哪些特性（如事务、锁机制等）。不同的存储引擎适用于不同的应用场景，因此 MySQL 支持多种存储引擎，用户可以根据需求选择合适的存储引擎。

如果没有特别的需求，使用默认的Innodb即可

1、是否支持行级锁

MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。

2、是否支持事务

MyISAM 不提供事务支持。

InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。并且，InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock）。

3、索引结构

MyISAM是非聚簇索引，而InnoDB有聚簇索引，聚簇索引的查找效率更高

4、是否支持数据库异常崩溃后的安全恢复

MyISAM 不支持，而 InnoDB 支持。

使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

2.执行一条SQL请求的过程

Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。

存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。**现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5 版本开始就被当做默认存储引擎了。

MySQL 服务器的连接器开始处理这个请求，主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作
分析器开始对 SQL 语句进行解析，检查语句是否符合 SQL 语法规则，确保引用的数据库、表和列都存在，并处理 SQL 语句中的名称解析和权限验证。
优化器负责确定 SQL 语句的执行计划，这包括选择使用哪些索引，以及决定表之间的连接顺序等。优化器会尝试找出最高效的方式来执行查询。
执行器首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

ps：如果是一条更新语句，略有一点不同：

执行更新的时候肯定要记录日志啦，这就会引入日志模块了，MySQL 自带的日志模块是 binlog（归档日志） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志）

前面的大致都类似
事务：在准备更新记录之前会记录undolog，用于失败回滚和MVCC（支持其它事务读取旧版本）
prepare阶段： 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

第三步和第四步也就是“两阶段提交”

3.三大范式

第一范式：要求数据库表的每一列都是不可分割的原子数据项。
第二范式：在 1NF 的基础上，要求数据库表中的每一列都和主键直接相关，而不能只与主键的某一部分相关（主要针对联合主键）。

比如说在一个订单表中，可能会存在订单编号和商品编号，设计出来的表可能是这样的。

这个订单表中就存在冗余数据，比如说商品名称、单位、商品价格等，应该将其拆分为订单表、订单商品关联表、商品表。

这个订单表中就存在冗余数据，比如说商品名称、单位、商品价格等，应该将其拆分为订单表、订单商品关联表、商品表。
第三范式：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖），第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

4.表连接

内连接：返回两个表中有匹配关系的行。相当于取交集
外连接：仅返回两个表中匹配的行，还返回左表、右表或两者中未匹配的行。
1. 左连接：以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN
2. 右连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN
交叉连接（得到笛卡尔积）：返回第一个表中的每一行与第二个表中的每一行的组合，这种类型的连接通常用于生成笛卡尔积。

5.IN和EXIT的区别

IN 和 EXISTS 都是用来处理子查询的关键词，但它们在功能、性能和使用场景上有各自的特点和区别。

1.基本概念和执行逻辑

IN 用于判断某个字段值是否存在于子查询返回的结果集中，其执行逻辑是先执行子查询，生成一个临时结果集，再与外部查询进行匹配。
EXISTS 则采用半连接（Semi-Join）机制，通过布尔判断检查子查询是否返回至少一行数据，其执行逻辑是逐行遍历外部查询，每次检查子查询是否存在匹配。

2.性能差异和适用场景

当子查询结果集较小时，IN 的性能通常更好，因为临时结果集可以高效缓存（加载内存）。

而当外部查询结果集较小且子查询关联字段有索引时，EXISTS 更具优势，因为它能利用索引快速短路判断（一旦匹配即停止扫描）。

3.NULL 值处理

NULL值处理：IN 能够正确处理子查询中包含NULL值的情况， IN 对 NULL 值的处理遵循三值逻辑（TRUE/FALSE/UNKNOWN），若子查询结果包含 NULL 且未匹配到非 NULL 值，则返回 UNKNOWN（等效于 FALSE）。而EXISTS 不受子查询结果中NULL值的影响，因为它关注的是行的存在性，而不是具体值。

6.NULL和`''`的区别

也可以这样问：为什么 MySQL 不建议使用 NULL 作为列默认值？

NULL的值不确定：NULL 代表一个不确定的值，就算是两个 NULL,它俩也不一定相等。例如，SELECT NULL=NULL的结果为 false，但是在我们使用DISTINCT,GROUP BY,ORDER BY时,NULL又被认为是相等的。
占用空间：''的长度是 0，是不占用空间的，而NULL 是需要占用空间的。
NULL 会影响聚合函数的结果。例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。 COUNT 的处理方式取决于参数的类型。如果参数是 *(COUNT(*))，则会统计所有的记录数，包括 NULL 值；如果参数是某个字段名(COUNT(列名))，则会忽略 NULL 值，只统计非空值的个数。
查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULLl 来判断，而不能使用 =、!=、 <、> 之类的比较运算符。而''是可以使用这些比较运算符的。

7.char和varchar的区别

1.存储方式与空间占用

CHAR 是定长字符串，无论实际存储内容长度如何，都会占用定义时的固定空间（不足部分用空格填充）。
VARCHAR 是变长字符串，仅占用实际内容长度 + 1-2 字节的长度标识位，空间利用率更高。

2.性能差异

CHAR 由于长度固定，读写速度通常略快于 VARCHAR，适合存储长度高度一致的数据（如 MD5 哈希值、固定编码等）
VARCHAR 在频繁更新且长度变化较大的场景下可能产生碎片，但节省存储空间，适合长度波动大的数据（如用户昵称、地址等）。

3.尾部空格处理

CHAR 在存储时会自动填充尾部空格，读取时移除填充的空格。
VARCHAR 保留原始空格，不会自动填充或截断。例如 'abc ' 存入 CHAR(5) 后读取为 'abc'，而存入 VARCHAR(5) 仍为 'abc '。

CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；

8.union和unionAll的区别

如果使用 UNION，会在表链接后筛选掉重复的记录行
如果使用 UNION ALL，不会合并重复的记录行
从效率上说，UNION ALL 要比 UNION 快很多，如果合并没有刻意要删除重复行，那么就使用 UNION All

9.MySQL数据的存储形式

MySQL 是以表的形式存储数据的，而表空间的结构则由段、区、页、行组成。表——段——区——页——行（记录）

10.MySQL查询语句的执行顺序

MySQL 在执行查询时，并不是按照编写顺序一步步执行，而是遵循以下逻辑顺序：

FROM - 确定数据来源，首先加载主表。
JOIN - 执行表关联操作（如 INNER JOIN、LEFT JOIN 等），生成中间结果集。
WHERE - 根据条件过滤行，缩小结果集。
GROUP BY - 将数据按指定列分组。
HAVING - 对分组后的数据进行过滤。
SELECT - 选择要输出的列或计算表达式。ORDER BY - 对结果集进行排序。
LIMIT - 限制最终输出的行数。

FROM——JOIN——WHERE——GROUP BY——HAVING——SELECT——ORDER BY——LIMIT

2.索引

对于这样一张表

它的索引结构如下所示（其中叶子节点是双链表连接）

1.索引的种类

从数据结构角度
- B数索引：MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。
- Hash索引：类似键值对的形式，一次即可定位。 优点是插入很快，缺点是无法做区间查询。适用于只有等值查询的场景
- 数组：优点是查询效率很快，在等值查询和范围查询场景中的性能就都非常优秀，缺点是插入比较麻烦。只适用于静态存储引擎 ，也就是后续数据基本不会变动
从物理存储角度

聚簇索引和非聚簇索引的区别在于索引和数据行的存储方式。聚簇索引将索引和数据行存储在一起，而非聚簇索引将索引和数据行分开存储。
- 聚簇索引：聚簇索引是一种索引组织方式，它将索引和数据行存储在一起，即数据行按照索引的顺序存储在磁盘上。聚簇索引的叶子节点保存的是完整的数据行，因此不需要进行额外的查找操作就可以获取到所需的数据。InnoDB 中的主键索引就属于聚簇索引。
- 非聚簇索引：它将索引和数据行分开存储，即索引保存了指向数据行的指针（通常是行的物理地址或主键值）。非聚簇索引的叶子节点保存的是指向数据行的引用，当查询需要获取数据时，首先根据索引查找到相应的行指针，然后再通过行指针获取数据行。 MyISAM 存储引擎的索引通常是非聚簇索引。
从逻辑角度
- 主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键。
- 唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。关键字UNIQUE
  - 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
- 普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值。
  - 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
- 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
  - 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建联合索引
  - 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一联合索引

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

如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。

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

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

2.回表查询

在使用非聚簇索引查询数据时，如果需要查的数据是主键之外的数据，在查到主键之后然后回到主键索引再查一次，这个就叫回表查询。

对于非聚簇索引，一定会回表查询吗？

不一定。试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。这种情况就称之为索引覆盖。

举个例子，这是二级索引的查找过程

将商品表中的 product_no （商品编码）字段设置为二级索引，那么二级索引的 B+Tree 如下图

其中非叶子的 key 值是 product_no（图中橙色部分），叶子节点存储的数据是主键值（图中绿色部分）。

如果我用 product_no 二级索引查询商品，如下查询语句：

1	select * from product where product_no = '0002';

会先检二级索引中的 B+Tree 的索引值(商品编码，product no)找到对应的叶子节点，然后获取主键值，然后再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。这个过程叫「回表」，也就是说要查两个 B+Tree 才能查到数据。

不过，当查询的数据是能在二级索引的 B+Tree 的叶子节点里查询到，这时就不用再查主键索引查，比如下面这条查询语句：

1	select id from product where product_no = '0002';

这种在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」 ，也就是只需要查一个 B+Tree 就能找到数据。

3.索引底层原理

MySQL的索引底层结构是 B+树。

B+树是一种平衡多路查找树，具有以下特点:

所有关键字保存在叶子节点，并且叶子节点之间通过链表连接，形成一个有序的叶子节点序列。
非叶子节点只存储索引字段的值和子节点的指针，不保存实际的数据。这样可以使得一个节点可以存储更多的关键字，减少了树的高度，加快搜索速度。
叶子节点包含所有索引字段的值和指向对应数据的指针。

在 B+树索引中，每个节点的大小是固定的，与磁盘页的大小相当。节点的大小通常是数据库页的大小例如16KB或 32KB。每个节点可以存储多个关键字和指针。叶子节点的关键字是有序的，且通过链表连接在一起。

索引查询快的原因有以下几点:

路径长度短:B+树具有平衡性，所有叶子节点的深度相同，因此在查询过程中只需要沿着树的高度进行几次磁盘 //O 操作，所以查询速度较快。
顺序访问优势:B+树的叶子节点之间使用链表连接，并且叶子节点的关键字是有序的，因此对于范围查询操作，可以通过顺序扫描叶子节点来获取有序的数据结果，提高查询速度。
最小化磁盘I/0 操作:B+树具有较高的填充因子，每个磁盘页上存储的关键字数量较多，能够减少磁盘 I/0 操作的次数，提高查询效率。

综上所述，B+树的平衡性、有序的叶子节点、顺序访问以及最小化的磁盘!/0 操作是使得索引查询快速的关键因素。通过 B+ 树的数据结构和索引的建立，可以大幅度减少磁盘访问次数，提高数据库查询的效率。

简答：索引底层原理使用了 B+树数据结构，它是一种平衡树，能快速定位和检索数据。B+树的叶子节点存储实际数据，中间节点存储索引，通过减少磁盘I/O来提高查询效率;索引按照值的大小顺序排列，使得范围查询效率更高。

4.为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构？（TODO

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

能在尽可能少的磁盘的 I/O 操作中完成查询工作；
要能高效地查询某一个记录，也要能高效地执行范围查找；

1、B+Tree vs B Tree

在B+树中，数据都存储在叶子节点上，而非叶子节点只存储索引信息；而B树的非叶子节点既存储索引信息也存储部分数据。
另外，B+Tree 叶子节点采用的是双链表连接，适合 MSQL 中常见的基于范围的顺序查找，而B树无法做到这一点。
B+树的查找性能更稳定，每次查找都需要查找到叶子节点;而B树的查找可能会在非叶子节点找到数据，性能相对不稳定。
B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B树更「矮胖」，查询底层节点的磁盘 /O次数会更少。
B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而B树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 /0 操作，范围查询效率不如 B+ 树。

2、B+Tree vs 二叉树

普通二叉树存在退化的情况，如果它退化成链表，就相当于全表扫描。

5.最左匹配原则

MySQL联合索引遵循最左前缀匹配原则，即最左优先，查询的时候会优先匹配最左边的索引。
例如当我们在 (a,b,c)三个字段上创建联合索引时，实际上是创建了三个索引，分别是(a)、(a,b).
(a,b,c)。

mysql会一直向右匹配直到遇到范围查询(>、<)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的

建立(a,b,c,d)的索引，可以支持以下前缀索引的查询

(a)
(a, b)
(a, b, d)
(a, b, d, c)

需要注意的是，因为有查询优化器，对于 c = ? AND d = ? AND b = ? AND a = ? 会被优化器重新排列为abcd的顺序，也就是仍然可以使用索引。查询条件中的列顺序并不重要，查询优化器会自动优化重新排列

比如，将商品表中的 product_no 和 name 字段组合成联合索引(product_no, name) ，它的B+树示意图如下：

可以看到，联合索引的非叶子节点用两个字段的值作为 B+Tree 的 key 值。当在联合索引查询数据时，先按 product no 字段比较，在 product no 相同的情况下再按 name 字段比较。

也就是说，联合索引查询的 B+Tree 是先按 product no 进行排序，然后再 product no 相同的情况再按name 字段排序。

再比如建立（a，b）的索引，它的索引示意图如下：

可以看到，a 是全局有序的（1, 2, 2, 3, 4, 5, 6, 7 ,8），而 b 是全局是无序的（12，7，8，2，3，8，10，5，2）。因此，直接执行where b = 2这种查询条件没有办法利用联合索引的， 利用索引的前提是索引里的 key 是有序的。

只有在 a 相同的情况才，b 才是有序的，比如 a 等于 2 的时候， b 的值为（7，8），这时就是有序的，这个有序状态是局部的，因此，执行where a = 2 and b = 7是 a 和 b 字段能用到联合索引的

接下来说几个范围查询的经典例子

1、select * from t_table where a > 1 and b = 2 。

由于联合索引(二级索引)是先按照a字段的值排序的，所以符合a>1条件的二级索引记录肯定是相邻，于是在进行索引扫描的时候，可以定位到符合a>1条件的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合a>1条件位置，所以a字段可以在联合索引的 B+Tree 中进行索引查询。

但是在符合 a > 1 条件的二级索引记录的范围里，b 字段的值是无序的。比如前面图的联合索引的 B+ Tree 里，下面这三条记录的 a 字段的值都符合 a > 1 查询条件，而 b 字段的值是无序的：

a 字段值为 5 的记录，该记录的 b 字段值为 8；
a 字段值为 6 的记录，该记录的 b 字段值为 10；
a 字段值为 7 的记录，该记录的 b 字段值为 5；

因此，这条查询语句只有 a 字段用到了联合索引进行索引查询，联合索引的最左匹配原则在遇到 a 字段的范围查询（ >）后就停止匹配了 而 b 字段并没有使用到联合索引。

2、select * from t_table where a >= 1 and b = 2

在符合 a>= 1 条件的二级索引记录的范围里，b 字段的值是「无序」的， 但是对于符合 a = 1 的二级索引记录的范围里，b 字段的值是「有序」的 （因为对于联合索引，是先按照 a 字段的值排序，然后在 a 字段的值相同的情况下，再按照 b 字段的值进行排序）。

当二级索引记录的 a 字段值为 1 时，可以通过 b = 2 条件减少需要扫描的二级索引记录范围

这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

3、SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2

在 MySQL 中，BETWEEN 包含了 value1 和 value2 边界值，类似于 >= and =<。

因此 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

4、SELECT * FROM t_user WHERE name like ‘j%’ and age = 22

对于符合 name = j 的二级索引记录的范围里，age字段的值是「有序」的 ，（因为对于联合索引，是先按照 name 字段的值排序，然后在 name 字段的值相同的情况下，再按照 age 字段的值进行排序）。

也就是说，从符合 name = 'j' and age = 22 条件的第一条记录时开始扫描，而不需要从第一个 name 为 j 的记录开始扫描。

这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

综上所述，联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，** **也就是范围查询的字段可以用到联合索引，** **但是在范围查询字段的后面的字段无法用到联合索引。** **注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询， 并不会停止匹配

6.索引下推

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

MySQL 5.6 引入的索引下推优化，允许存储引擎在索引遍历过程中，执行部分 WHERE字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高查询效率。

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

7.索引失效有哪些情况

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。
当我们在查询条件中对索引列使用函数，就会导致索引失效。因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。
当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。因为索引保存的是索引字段的原始值，而不是id +1表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。
当我们在查询条件中对索引列进行隐式类型转换操作 ，背后可能也做了函数运算
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，

8.索引创建的原则（了解

1）最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

9.索引的缺点(了解)

占用存储空间： 每个索引都会占用额外的磁盘空间，尤其在数据量很大的情况下，存储需求会显著增加。

影响写操作性能：

插入、更新、删除时，MySQL需要维护所有相关索引。这会导致写操作变慢，特别是在频繁更新的表中。

复杂的索引选择：

如果索引过多，优化器在执行查询时需要花费额外时间决定使用哪个索引，这可能导致优化器选择不当。

增加维护成本：

开发和维护人员需要理解和管理这些索引，可能导致复杂性增加。

因此，对于写操作多于读操作的场景，尽量减少索引数量。

10.主键索引为什么最好设置为自增的

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

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

举个例子，假设某个数据页中的数据是1、3、5、9，且数据页满了，现在准备插入一个数据7，则需要把数据页分割为两个数据页:

出现页分裂时，需要将一个页的记录移动到另外一个页，性能会受到影响，同时页空间的利用率下降，造成存储空间的浪费。

而如果记录是顺序插入的，例如插入数据11，则只需开辟新的数据页，也就不会发生页分裂：

11.MySQL使用like “%x”，索引一定失效吗？

答：不一定。

如果数据库表中的字段只有主键+二级索引，那么即使使用了左模糊匹配，也不会走全表扫描（type=all），而是走全扫描二级索引树(type=index)。

一个表有2个字段，其中name是索引字段，id 拥有自增主键索引。

1 2	selectfromswhere name like"%xxx' select froms where name like "%xxx%'

这两个也会走索引

执行情况如下：

为什么呢？

首先，这张表的字段没有「非索引」字段， 这个查询的数据都在二级索引的 B+ 树， 因为二级索引的 B+ 树的叶子节点包含「索引值+主键值」，所以查二级索引的 B+ 树就能查到全部结果了，这个就是覆盖索引。

为什么选择全扫描二级索引树，而不扫描聚簇索引树呢？

因为二级索引树的记录东西很少，就只有「索引列+主键值」，而聚簇索引记录的东西会更多，比如聚簇索引中的叶子节点则记录了主键值、事务 id、用于事务和 MVCC 的回滚指针以及所有的剩余列。

补充ai：

在特定场景下仍可能使用索引：

覆盖索引：当查询的列全部包含在索引中时（即使LIKE "%x"），可能通过全索引扫描避免回表
**索引条件下推(ICP)**：MySQL 5.6+ 可能将条件过滤下推到存储引擎层

3.事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

1.事务的ACID特性以及怎么实现的

原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性（Consistency）： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不会丢失。

只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！

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

2.脏读、幻读、不可重复读

脏读（读到其他事务未提交的数据；

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

某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
不可重复读（前后读取的数据不一致；

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

在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新了原有的数据。
幻读（前后读取的记录数量不一致

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

严重性排序：脏读 > 不可重复读 > 幻读

3.事务的隔离级别

隔离水平高低排序如下：串行化 > 可重复读 > 读已提交 > 读未提交

各个隔离级别的定义：

读未提交：指一个事务还没提交时，它做的变更就能被其他事务看到；
读已提交：指一个事务提交之后，它做的变更才能被其他事务看到；
可重复读：指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化：会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

在各个隔离级别下可能发生的问题：

在「读未提交」隔离级别下，可能发生脏读、不可重复读和幻读现象；
在「读提交」隔离级别下，可能发生不可重复读和幻读现象，但是不可能发生脏读现象；
在「可重复读」隔离级别下，可能发生幻读现象，但是不可能脏读和不可重复读现象；
在「串行化」隔离级别下，脏读、不可重复读和幻读现象都不可能会发生。

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

各个级别的适用场景：

读未提交：适合对数据一致性要求极低的场景，比如实时日志统计或临时数据分析。
读已提交：适用于大多数业务场景，比如普通的电商订单查询、库存检查等，要求避免脏读但可以容忍一定程度的不一致。
可重复读：适合需要较高一致性的业务场景，比如财务系统、库存管理、银行账户余额查询等。
适用于对数据一致性要求极高的场景，比如关键金融交易、分布式系统中的强一致性需求。

4.隔离级别的实现原理

MySQL 的事务隔离级别主要依赖 锁机制 和 MVCC 实现。

读已提交和可重复读通过 MVCC 机制中的 ReadView 来实现。

读已提交：每次读取数据前都生成一个 ReadView，保证每次读操作都是最新的数据。
可重复读：只在第一次读操作时生成一个 ReadView，后续读操作都使用这个 ReadView，保证事务内读取的数据是一致的。

串行化是通过锁来实现的：事务在读操作时，必须先加表级共享锁，直到事务结束才释放；事务在写操作时，必须先加表级排他锁，直到事务结束才释放。

以下了解即可

(1) 锁机制

共享锁（S 锁）：允许多个事务并发读取同一数据，但阻止写入。
排他锁（X 锁）：阻止其他事务读取或写入。
隔离级别越高，锁的粒度和范围越大。

5.MVCC

基本概念

当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:
select .. lockin share mode(共享锁)，select.... for update、update、insert、delete(排他锁)都是一种当前读。
快照读

简单的select(不加锁)就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
- RC读已提交：每次select，都生成一个快照读。
- RR可重复读：开启事务后第一个select语句才是快照读的地方
- 串行化：快照读退化为当前读
MVCC

全称 Multi-Version Concurrency Control，多版本并发控制。MVCC 是数据库管理系统中用于实现事务隔离性和并发控制的一种关键技术。它的核心目标是通过维护数据的多个版本，使得并发执行的事务能够在不相互阻塞的情况下读取和写入数据，从而提高数据库的并发性能，同时保证数据的一致性和隔离性。

基本原理

MVCC 的核心思想是：每次写操作会创建一个新的数据版本，而不是直接覆盖旧数据。 通过这种方式，读操作可以访问与事务开始时一致的旧版本数据，而无需等待写操作完成。这种机制避免了读写之间的锁竞争，从而提升了并发性能。

MVCC的具体实现，还需要依赖于数据库记录中个隐式字段、undolog、readView。

实现过程：
- 读取时，根据事务的开始时间或快照，访问对应的旧版本数据。
- 写入时，生成新的版本，旧版本通过 Undo Log 保留。

三个字段

TRX_ID: 最近修改该条数据的事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
ROLL_PTR：回滚指针，指向这条记录的上一个版本，（历史数据存储在undolog中）
ROW_ID：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。有的话就不用这个了。这个不是很重要

undolog

readview

ReadView(读视图)主要是用来做可见性判断。是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id。

四个核心字段：

m_ids：所有活跃事务的 ID 列表，活跃事务是指那些已经开始但尚未提交的事务。
min_trx_id：活跃事务中最小的事务id
max_trx_id：下一个即将生成的事务ID，也就是即将开始的事务
creator_trx_id:：创建该 Read View 的事务的事务 id。

事务可见性示意图

当一个事务读取某条数据时，InnoDB 会根据 ReadView 中的信息来判断该数据的某个版本是否可见。

如果＜ min_trx_id ，则该数据版本在生成 ReadView 之前就已经提交，因此对当前事务是可见的。
如果＞ max_trx_id ，则表示创建该数据版本的事务在生成 ReadView 之后开始，因此对当前事务是不可见的。
如果在二者之间，需要判断该事务是否在活跃事务列表中
- 如果不在，表示创建该数据版本的事务在生成 ReadView 之后已经提交，因此对当前事务也是可见的。
- 在，则表示创建该数据版本的事务仍然活跃，或者在当前事务生成 ReadView 之后开始，因此对当前事务是不可见的。

4.锁(了解)

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

就像酒店的房间，如果大家随意进出，就会出现多人抢夺同一个房间的情况，而在房间上装上锁，申请到钥匙的人才可以入住并且将房间锁起来，其他人只有等他使用完毕才可以再次使用。

1.表级锁和行级锁

二者的区别是锁粒度不同

行级锁 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。

特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

2.共享锁和排他锁

从锁的类别上来讲，有共享锁和排他锁。

共享锁: 又叫做读锁。当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。

排他锁: 又叫做写锁。当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

5.日志

Redo log(重做日志)：是 Innodb 存储引擎层生成的日志，用于保证事务的持久性，它在事务提交前将数据写入磁盘，以防止数据丢失。主要用于掉电等故障恢复；

Undo log(回滚日志)：是 Innodb 存储引擎层生成的日志，用于事务的回滚操作，它记录了事务对数据的修改，以便在事务回滚时进行数据恢复。保证事务的原子性，主要用于事务回滚和 MVCC。

Bin log(二进制日志)：是 Server 层生成的日志，记录了数据库的所有修改操作，包括对数据的增删改操作，主要用于数据备份和主从复制；

其中redo log是物理日志，记录数据页的物理变化，而undo log 和bin log是逻辑日志，分别记录如何撤销事务和记录 SQL 或行变更

1.Undo log

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

每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里。在发生回滚时，就读取 undo log 里的数据，然后做原先相反操作。

在插入一条记录时，要把这条记录的主键值记下来，回滚时只需要把这个主键值对应的记录删掉就好了；
在删除一条记录时，要把这条记录中的内容都记下来，回滚时再把由这些内容组成的记录插入到表中就好了；
在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。

另外，undo log 还有一个作用， 配合ReadView 实现 MVCC（多版本并发控制）。

因此，undo log 两大作用：

实现事务回滚，保障事务的原子性。
实现 MVCC（多版本并发控制）关键因素之一。当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现快照读（非锁定读）

2.Redo Log

redo log（重做日志）是 InnoDB 存储引擎独有的，它让 MySQL 拥有了崩溃恢复能力。是用来实现事务的持久性。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

比如 MySQL 实例挂了或宕机了，重启时，InnoDB 存储引擎会使用 redo log 恢复数据，保证数据的持久性与完整性。

MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。
后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。
更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。
然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

该日志文件由两部分组成：重做日志缓冲(redolog buffer)以及重做日志文件(redolog file)，前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用。

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

为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候， InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。后续，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术。

过程如下图：

顺序写vs随机写

redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举？

写入 redo log 的方式使用了追加操作，所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。磁盘的「顺序写」比「随机写」高效的多，因此 redo log 写入磁盘的开销更小。

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

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

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

不是的。
实际上，执行一个事务的过程中，产生的 redo log 也不是直接写入磁盘的，因为这样会产生大量的 I/0操作，而且磁盘的运行速度远慢于内存。
所以，redo log也有自己的缓存– redo log buffer，每当产生一条 redo log 时，会先写入到 redo logbuffer，后续在持久化到磁盘如下图:

redo log buffer 默认大小 16 MB，可以通过 innodb_log_Buffer_size 参数动态的调整大小，增大它的大小可以让 MySQL 处理「大事务」是不必写入磁盘，进而提升写 IO 性能。

redo log和undo log的区别

这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

redo log 记录了此次事务「修改后」的数据状态，记录的是更新之后的值， 主要用于事务崩溃恢复，保证事务的持久性。
undo log 记录了此次事务「修改前」的数据状态，记录的是更新之前的值， 主要用于事务回滚，保证事务的原子性。

事务提交之前发生了崩溃(这里的崩溃不是宕机崩溃，而是事务执行错误，mysql 还是正常运行的)，重启后会通过 undo log 回滚事务。

事务提交之后发生了崩溃（这里的崩溃是宕机崩溃），重启后会通过 redo log 恢复事务，如下图：

redolog刷盘时机（TODO

3.Bin log

binog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT和 SHOW 操作。

作用：

灾难时的数据恢复;
MySQ4的主从复制。

binlog 有3种格式类型

分别是 STATEMENT(默认格式)、ROW、 MIXED，区别如下:

指定statement，记录的内容是SQL语句原文，比如执行一条update T set update_time=now() where id=1，记录的内容如下。同步数据时，会执行记录的SQL语句，但是有个问题，update_time=now()这里会获取当前系统时间，直接执行会导致与原库的数据不一致。
为了解决这种问题，我们需要指定为row，记录的内容不再是简单的SQL语句了，还包含操作的具体数据，记录内容如下。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量update 语句，更新多少行数据就会产生多少条记录，使 binog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已;
所以就有了一种折中的方案，指定为mixed，记录的内容是前两者的混合。

MySQL 会判断这条SQL语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement格式。

如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？

不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。

因为 redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。

binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据。

redo log和binlog的区别

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

两阶段提交

为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致。

redo log（重做日志）让 InnoDB 存储引擎拥有了崩溃恢复能力。

binlog（归档日志）保证了 MySQL 集群架构的数据一致性。

虽然它们都属于持久化的保证，但是侧重点不同。

在执行更新语句过程，会记录 redo log 与 binlog 两块日志，以基本的事务为单位，redo log 在事务执行过程中可以不断写入，而 binlog 只有在提交事务时才写入，所以 redo log 与 binlog 的写入时机不一样。

先写 redo log ，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

可以看到，在持久化 redo log 和 binlog 这两份日志的时候，如果出现半成功的状态，就会造成主从环境的数据不一致性。这是因为 redo log 影响主库的数据， binlog 影响从库的数据，所以 redo log 和 binlog 必须保持一致才能保证主从数据一致。

两阶段提交的过程在一条更新语句的执行过程那里

4.日志写入顺序⭐️

日志写入顺序

在 MySQL 中，事务的提交过程会严格按照一定的顺序写入日志，以保证数据的一致性和持久性。以下是典型的事务提交过程中三种日志的写入顺序：

步骤 1：写入 undo log

在事务执行过程中，当对数据进行修改时（例如 INSERT、UPDATE、DELETE 操作），InnoDB 首先会将修改前的旧数据记录到 undo log 中。
原因：这是为了支持事务的回滚（Atomicity）和一致性（Consistency）。如果事务失败，undo log 可以用来撤销所有修改。
写入时机：在实际修改数据之前，undo log 必须写入并持久化。

步骤 2：写入 redo log（prepare 阶段）

在事务执行过程中，InnoDB 会将数据修改的物理记录写入 redo log，但此时 redo log 并未真正提交，而是处于 prepare 状态。
原因：这是 WAL 机制的要求，任何数据修改必须先写入 redo log，以保证事务的持久性（Durability）。即使系统崩溃，redo log 可以用来恢复数据。
写入时机：在数据修改后，事务提交（COMMIT）之前，redo log 会被写入并持久化到磁盘（至少写入缓冲区，并根据 innodb_flush_log_at_trx_commit 参数决定是否立即刷盘）。

步骤 3：写入 binlog

当事务提交时，MySQL Server 层会将事务的逻辑操作记录写入 binlog。
原因：binlog 是用于主从复制和数据恢复的日志，必须在事务提交时写入，以保证主从一致性。
写入时机：在 redo log 的 prepare 阶段完成后，binlog 会被写入并持久化到磁盘（根据 sync_binlog 参数决定是否立即刷盘）。

步骤 4：redo log 提交（commit 阶段）

在 binlog 写入成功后，InnoDB 会将 redo log 的状态从 prepare 改为 commit，表示事务正式提交。
原因：这是两阶段提交（Two-Phase Commit, 2PC）机制的一部分，确保 redo log 和 binlog 的一致性。如果 binlog 写入失败，事务会回滚；如果 binlog 写入成功但 redo log 提交失败，MySQL 会根据 binlog 和 redo log 的状态进行恢复。

两阶段提交（2PC）机制

MySQL 使用两阶段提交机制来保证 redo log 和 binlog 的一致性。写入顺序可以总结为：

Prepare 阶段：写入 redo log（标记为 prepare 状态）。
Commit 阶段
- 写入 binlog。
- 将 redo log 标记为 commit 状态。

这种机制确保了即使在崩溃恢复时，redo log 和 binlog 能够保持一致。

写入顺序总结

综合上述步骤，三种日志的写入顺序如下：

undo log：在事务执行过程中，数据修改前写入。
redo log（prepare）：在事务提交前写入，标记为 prepare 状态。
binlog：在事务提交时写入。
redo log（commit）：在 binlog 写入成功后，标记为 commit 状态。

6.SQL优化

0.MySQL常见性能优化手段

慢 SQL 定位与分析
1. 监控工具： 介绍常用的慢 SQL 监控工具，如 MySQL 慢查询日志
2. EXPLAIN 命令： 详细说明 EXPLAIN 命令的使用，分析查询计划、索引使用情况
索引、表结构和 SQL 优化
1. 索引优化： 这是 MySQL 性能优化的重点，可以介绍索引的创建原则、覆盖索引、最左前缀匹配原则等。
2. 表结构优化： 优化表结构设计，包括选择合适的字段类型、避免冗余字段、合理使用范式和反范式设计等等。
3. SQL语句的优化： 避免使用 SELECT *、尽量使用具体字段、使用连接查询代替子查询、合理使用分页查询、批量操作等，都是 SQL 编写过程中需要注意的细节。
架构优化
1. 读写分离： 将读操作和写操作分离到不同的数据库实例，提升数据库的并发处理能力。
2. 分库分表： 将数据分散到多个数据库实例或数据表中，降低单表数据量，提升查询效率。但要权衡其带来的复杂性和维护成本，谨慎使用。
3. 数据冷热分离：根据数据的访问频率和业务重要性，将数据分为冷数据和热数据，冷数据一般存储在存储在低成本、低性能的介质中，热数据高性能存储介质中。
4. 缓存机制： 使用 Redis 等缓存中间件，将热点数据缓存到内存中，减轻数据库压力。这个非常常用，提升效果非常明显，性价比极高！

1.MySQL 慢查询的排除与优化

慢查询日志：在 MySQL中，可以启用慢查询日志功能来记录执行时间超过设定阈值的查询。通过分析这些日志，可以找到执行时间较长的SQL语句。
使用 EXPLAIN分析：对慢SQL语句使用 MySQL的EXPLAIN命令，可以分析查询的执行计划。这有助于了解 MySQL是如何处理你的查询的，包括是否使用了索引、是否进行了全表扫描等。
查询了过多不需要的数据：有时候，查询语句可能会返回比实际需要更多的数据，这会导致查询变慢。例如，使用 SELECT*从多个表中进行关联查询时，可能会取出所有列的数据，但实际上可能只需要其中的一部分列。这种情况下，可以优化SQL语句，只选择需要的列。
如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

2.explain执行计划

如果一条sql执行很慢的话，我们通常会使用mysql自动的执行计划explain来去查看这条sql的执行情况，

比如在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，

第二个，可以通过type字段查看sq!是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，

第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了可以尝试添加索引或修改返回字段来修复

最重要的三个字段，key， type，extra

key 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

key_length 索引长度。联合索引的情况下可结合key_len判断实际使用的索引

type(非常重要，可以看到有没有走索引) 查询执行的类型，描述了查询是如何执行的。

性能从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL。

system，表只有一行，一般是系统表，往往不需要进行磁盘 IO，速度非常快
const、eq_ref、ref：这些类型表示 MySQL 可以使用索引来查找单个行，其中 const 是最优的，表示查询最多返回一行。
range：只检索给定范围的行，使用索引来检索。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。
index：遍历索引树读取。查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
ALL：全表扫描，效率最低。

extra 的信息非常丰富，常见的有：

Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。比如SELECT的列都包含在索引中
Using where：表示MySQL服务器在存储引擎检索行后进行了额外的过滤。一般是查询条件不能完全通过索引筛选时会出现这个，优化方法是检查是否可以通过优化索引来避免额外的过滤，也就是检查WHERE条件是否可以利用索引
Using temporary ：表示使用了临时表来存储中间结果。GROUP BY、ORDER BY或DISTINCT操作无法使用索引时。优化建议：优化查询或添加适当的索引
Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
Using filesort：表示需要额外的排序操作。示例场景：ORDER BY无法使用索引排序时。优化建议：为ORDER BY子句创建合适的索引
中性信息：如Using index、Using index condition，通常表示良好优化
警告信息：如Using temporary、Using filesort，优先考虑索引优化

【推荐】SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是consts最好。
说明：
1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
2） ref 指的是使用普通的索引（normal index）。
3） range 对索引进行范围检索。

3.给你张表，发现查询速度很慢，你有那些解决方案

分析查询语句：使用EXPLAIN命令分析SQL执行计划，找出慢查询的原因，比如是否使用了全表扫描，是否存在索引未被利用的情况等，并根据相应情况对索引进行适当修改。
创建或优化索引：根据查询条件创建合适的索引
避免索引失效： 不符合最左匹配原则
查询优化：避免使用SELECT *，只查询真正需要的列；使用覆盖索引，即索引包含所有查询的字段；
优化数据库表：如果单表的数据超过了千万级别，考虑是否需要将大表拆分为小表，减轻单个表的查询压力。
使用缓存技术：引入缓存层
，如Redis，存储热点数据和频繁查询的结果，

4.主键使用自增ID还是UUID？

推荐使用自增ID，不要使用UUID。

因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序)，如果主键索引是自增ID，那么只需要不断向后排列即可，如果是UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。

总之，在数据量大一些的情况下，用自增主键性能会好一些。

7.架构

1.读写分离

读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。 这样的话，就能够小幅提升写性能，大幅提升读性能。

一般情况下，我们都会选择一主多从，也就是一台主数据库负责写，其他的从数据库负责读。主库和从库之间会进行数据同步，以保证从库中数据的准确性。这样的架构实现起来比较简单，并且也符合系统的写少读多的特点。

如何实现读写分离？

代理方式

我们可以在应用和数据中间加了一个代理层。应用程序所有的数据请求都交给代理层处理，代理层负责分离读写请求，将它们路由到对应的数据库中。

提供类似功能的中间件有 MySQL Router（官方， MySQL Proxy 的替代方案）、Atlas（基于 MySQL Proxy）、MaxScale、MyCat。
组件方式

2.主从复制

原理

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

流程如下：

MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。

在完成主从复制之后，你就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

3.主从延迟

读写分离对于提升数据库的并发非常有效，但是，同时也会引来一个问题：主库和从库的数据存在延迟，比如你写完主库之后，主库的数据同步到从库是需要时间的，这个时间差就导致了主库和从库的数据不一致性问题。这也就是我们经常说的 主从同步延迟 。

主从延迟出现的原因

MySQL 主从同步延时是指从库的数据落后于主库的数据，这种情况可能由以下原因造成：

网络延迟 主库和从库之间如果存在网络传输延迟
主库写入压力大 当主库的写入操作（如INSERT、UPDATE、DELETE）非常频繁时，从库可能无法以同样的速度处理这些更新。
从库负载过高 从库可能正在处理其他查询或任务，导致无法及时应用主库传来的更新日志。

如何在主从延迟时保证读到最新数据

强制读主库 对于需要实时一致性的关键业务，可以配置应用程序直接从主库读取数据，避免从库延迟的影响。
延迟监控与切换
实时监控主从延迟（例如通过Seconds_Behind_Master参数），当延迟超过一定阈值时，自动将读请求切换到主库。

5.分库分表

读写分离主要应对的是数据库读并发，没有解决数据库存储问题。试想一下：如果 MySQL 一张表的数据量过大怎么办?

换言之，我们该如何解决 MySQL 的存储压力呢？

答案之一就是 分库分表。

分库
1. 垂直分库：按业务划分，把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。比如将数据库中的用户表、订单表和商品表分别单独拆分为用户数据库、订单数据库和商品数据库。
2. 水平分库：同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题。
  
  举个例子：订单表数据量太大，你对订单表进行了水平切分（水平分表），然后将切分后的 2 张订单表分别放在两个不同的数据库。
  - 分库有个读扩散问题：见B站视频原神那一集（TODO）
分表
1. 垂直分表：是对数据表列的拆分，把一张列比较多的表拆分为多张表。
  
  举个例子：我们可以将用户信息表中的一些列单独抽出来作为一个表。
  1. 优点：可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。
2. 水平分表：是对数据表行的拆分，把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题。
  
  举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。
常见的分片算法

分片算法主要解决了数据被水平分片之后，数据究竟该存放在哪个表的问题。

常见的分片算法有：
1. 哈希分片：求指定分片键的哈希，然后根据哈希值确定数据应被放置在哪个表中。
2. 范围分片：按照特定的范围区间（比如时间区间、ID 区间）来分配数据，比如将 id 为 1~299999 的记录分到第一个表， 300000~599999 的分到第二个表。
3. 映射表分片：使用一个单独的表（称为映射表）来存储分片键和分片位置的对应关系。
4. 一致性哈希分片：将哈希空间组织成一个环形结构，将分片键和节点（数据库或表）都映射到这个环上，然后根据顺时针的规则确定数据或请求应该分配到哪个节点上，解决了传统哈希对动态伸缩不友好的问题。

6.冷热分离

数据冷热分离是指根据数据的访问频率和业务重要性，将数据分为冷数据和热数据，冷数据一般存储在存储在低成本、低性能的介质中，热数据高性能存储介质中。

冷热分离的思想非常简单，就是对数据进行分类，然后分开存储。

7.深度分页

全表查询通常是顺序 IO。数据库在执行全表扫描时，会按存储顺序从头到尾读取表中的所有行，这种读取方式属于顺序 IO，相比随机 IO 更加高效。

但在深度分页场景中，尽管查询仍是全表扫描，由于 OFFSET 值很大，数据库需要跳过大量数据才能到达目标行。虽然跳过数据的操作仍然是顺序读取，但跳过的过程会增加额外的 IO 开销。特别是在数据量很大时，跳过大量数据会显著影响性能，因此深度分页的效率会下降。

查询偏移量过大的场景我们称为深度分页，这会导致查询性能较低，例如：

1 2	# MySQL 在无法利用索引的情况下跳过1000000条记录后，再获取10条记录 SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10

当查询偏移量过大时，MySQL 的查询优化器可能会选择全表扫描而不是利用索引来优化查询。这是因为扫描索引和跳过大量记录可能比直接全表扫描更耗费资源。

MySQL 的查询优化器采用基于成本的策略来选择最优的查询执行计划。它会根据 CPU 和 I/O 的成本来决定是否使用索引扫描或全表扫描。如果优化器认为全表扫描的成本更低，它就会放弃使用索引。不过，即使偏移量很大，如果查询中使用了覆盖索引（covering index），MySQL 仍然可能会使用索引，避免回表操作。

深度分页解决手段

8.内存

1.BufferPool

这个缓冲池是存储引擎的缓冲池。MySQL的server也有一个缓存，也就是自带的缓存，那个缓存可以对查询语句做哈希，然后以key-value的形式存储查找结果，当查找语句完全命中（必须一模一样），可以直接返回结果集，不过这个功能比较鸡肋，后面就停止了。

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

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

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

有了缓冲池后:

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

Mysql