面试整理——数据库

发表于 2019-10-29 更新于 2025-02-21 分类于面试整理

本文字数： 103k 阅读时长 ≈ 1:34

整理面试中常问的数据库相关问题，持续更新中。

数据库

一. 综合

1.1 范式

【数据库三范式是哪三个？】

第一范式（1NF）：每个字段不可再分。确保每列的原子性，属性不可再分。
第二范式（2NF）：字段应该与主键有关。非主键列完全依赖于主键 （要求每个表只描述一件事情）。
第三范式（3NF）：非主键字段之间不能有依赖。满足第二范式，并且表中的列不存在对非主键列的传递依赖，确保数据表中的每个字段都只与主键相关。

1.2 数据库连接

【数据库连接池？】

什么是数据库连接池？
- 数据库连接池是一种管理和维护数据库连接的技术，它通过预先创建一定数量的数据库连接并将其保存在池中，以便在需要时重复使用，从而减少了数据库连接的创建和关闭开销。
两个最重要的参数：最小连接数和最大连接数
1. 最小连接数（Minimum Pool Size）： 即使这些连接处于空闲状态，确保应用程序在高负载时仍有足够的连接可用。
2. 最大连接数（Maximum Pool Size）： 超过这个数量的连接请求将等待，直到有连接可用或达到连接超时时间。
流程：
1. 初始化连接池：在应用程序启动时，连接池会被初始化。这包括设置连接池的参数（如最小连接数、最大连接数、连接超时等），创建初始化最小数量的数据库连接，并将这些连接放入连接池中。
2. 连接池管理：
  1. 当前有空闲连接，直接复用。
  2. 当前没有空闲连接，且还未达到最大连接数，则创建新的连接。
  3. 没有空闲，且已大于等于最大，则按最大等待时间等待，超时仍未有空闲资源则返回异常。
3. 连接池关闭：先关闭连接，再释放资源。

二. SQL?????????????????????????????????????????????????????????

2.1 连接

【SQL有哪些表连接方式？】

什么是连接？连接即用 JOIN 连接多个表，条件语句用 ON 代替 Where，用来代替子查询。

几种连接包括：

内连接（INNER JOIN）： 又叫等值连接，内连接是最常见的连接类型。它仅返回两个表之间匹配的行。如果两个表中的行在连接条件上匹配，那么这些行将被返回，否则将被排除。
1
2
3
4
SELECT *
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
// 等价于 SELECT * from table1,table2 where table1.column = table2.column
左连接（LEFT JOIN 或 LEFT OUTER JOIN）： 左连接返回左表的所有行，以及右表中匹配的行。如果右表中没有匹配的行，则结果集中右侧的列将包含 NULL 值。
1
2
3
SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
右连接（RIGHT JOIN 或 RIGHT OUTER JOIN）： 右连接与左连接相反，返回右表的所有行，以及左表中匹配的行。如果左表中没有匹配的行，则结果集中左侧的列将包含 NULL 值。
1
2
3
SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
全连接（FULL JOIN 或 FULL OUTER JOIN）： 全连接返回两个表中所有的行，无论它们是否匹配。如果某一行在一个表中没有匹配的行，则另一个表中相关的列将包含 NULL 值。
1
2
3
SELECT *
FROM table1
FULL JOIN table2 ON table1.column = table2.column;
交叉连接（CROSS JOIN）： 交叉连接返回两个表的笛卡尔积，即两个表中所有行的组合。它不使用任何连接条件。
1
2
3
SELECT *
FROM table1
CROSS JOIN table2;

2.2 组合查询

【组合查询？】

组合查询是指在数据库中执行的一种查询，它结合了多个查询操作，以检索出满足多个条件的数据集。组合查询通常包括多个 SQL 查询语句的组合，这些查询语句通过逻辑运算符（如 UNION、INTERSECT、EXCEPT）或关键字（如 JOIN）来组合：

UNION 操作： UNION 用于合并两个或多个 SELECT 语句的结果集，去除重复的行。每个查询必须包含相同的列、表达式和聚集函数，会去除重复行（使用UNION ALL保留），只能保留一个ORDER BY 于语句末尾。例如：
1
2
3
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
上述查询将返回合并了 table1 和 table2 中 column1 列数据的结果集。
INTERSECT 操作： INTERSECT 用于获取两个查询的交集，即同时存在于两个结果集中的行。例如：
1
2
3
SELECT column1 FROM table1
INTERSECT
SELECT column1 FROM table2;
上述查询将返回同时存在于 table1 和 table2 中 column1 列数据的结果集。
EXCEPT 操作： EXCEPT 用于获取两个查询的差集，即存在于第一个结果集中但不存在于第二个结果集中的行。例如：
1
2
3
SELECT column1 FROM table1
EXCEPT
SELECT column1 FROM table2;
上述查询将返回存在于 table1 中但不存在于 table2 中 column1 列数据的结果集。
JOIN 操作： JOIN 用于在两个或多个表之间建立关联，并获取符合关联条件的数据。例如：
1
2
3
SELECT *
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
上述查询将返回 table1 和 table2 中根据连接条件匹配的行。

2.3 视图

视图是什么？

视图是一种虚拟表，不存储数据，访问视图时从实体表获取数据；
两种实现算法：
- 临时表算法：当用户查询视图时，数据库系统首先创建一个临时表，该临时表包含视图查询的结果集。用户的查询实际上是在这个临时表上执行的，而不是在基本表上执行的。
  - 优点： 可以通过创建临时表来缓存视图的结果，避免了每次查询都重新计算结果的开销。
  - 缺点： 需要额外的存储空间用于存储临时表，而且在视图数据更新时，需要及时更新临时表，维护成本较高。
- 合并算法：通过将视图的定义嵌入到用户的查询中，从而避免创建临时表。查询引擎在执行用户查询时，会将视图的定义与用户查询合并，形成一个新的查询计划，直接操作基本表。
  - 优点： 避免了创建和维护临时表的成本，减少了存储开销。
  - 缺点： 在某些情况下，合并算法可能无法直接转换视图的查询计划，导致性能下降。
使用场景：
- 简化复杂查询，重用查询逻辑：对于那些包含多表关联、聚合函数等复杂条件的查询特别有用。或者某个查询逻辑在多个地方重复使用，可以通过创建视图将该逻辑封装起来，提高代码的可维护性和重用性。
- 隐藏数据： 用于限制用户对表的访问权限，只允许查询视图而不是直接访问底层表。
- 重构Schema时使用视图，修改表结构时不会影响应用运行；
- 使用视图实现基于列的权限控制，不需要真正的在系统创建权限，没有额外开销。

2.4 存储过程

【存储过程？】

存储过程可以看成是对一系列 SQL 操作的批处理，是在数据库中预先编译好的一组SQL语句，

优点：

安全性：
- 代码封装，保证了一定的安全性；
- 是一种代码重用，方便统一业务规则，保证某些行为总是一致，所以也带来了一定的安全性；
- 可以设置权限，只允许特定用户或角色执行，提高了数据的安全性。
性能优化： 由于是预先编译，因此具有很高的性能。
网络减少：服务器内部执行，离数据最近，可以节省带宽和网络延迟；

缺点：

学习成本： 编写存储代码难度较高；
调试困难： 存储代码的实现有很多限制，调试也很困难，难以定位问题。
可移植性差：
- 语法和功能在不同的数据库系统中可能有所不同，这导致了存储过程的可移植性较差，依赖于具体的数据库平台；
- 很难实现复杂逻辑；
维护困难：
- 原本只需部署应用代码和库表结构变更，还需要额外部署内部存储代码；
- 部署在服务器会带来安全隐患，只要破解数据库就将功能和数据全部获取；
- 无法控制存储程序的资源消耗，可能一个错误就把服务器拖死。
扩展困难：存储过程给数据库服务器带来额外的压力，数据库服务器的扩展性要远差于应用服务器。

2.5 聚合函数

【聚合函数？】

聚合函数是一类用于对数据进行聚合操作的SQL函数，通常用于从多行数据中计算出单个值。在一个行的集合（一组行）上进行操作，对每个组给一个结果。通常与 GROUP BY 子句一起使用，以便对每个分组应用聚合操作。

SUM：对指定列中的数值进行求和操作。

1 2	-- 计算订单表中所有订单的总金额 SELECT SUM(amount) AS total_amount FROM orders;

AVG：对指定列中的数值计算平均值。

1 2	-- 计算学生表中所有学生的平均年龄 SELECT AVG(age) AS average_age FROM students;

COUNT：统计指定列中非空值的数量。

1 2	-- 统计客户表中所有非空邮件地址的数量 SELECT COUNT(email) AS email_count FROM customers;

MIN： 找出指定列中的最小值。

1 2	-- 找出产品表中价格最低的产品 SELECT MIN(price) AS min_price FROM products;

MAX：找出指定列中的最大值。

1 2	-- 找出销售表中最高的销售额 SELECT MAX(sales) AS max_sales FROM sales;

中级查询

【HAVING子句？】

HAVING 子句用于对 GROUP BY 分组后的结果进行过滤。它与 WHERE 子句类似，但 WHERE 是在分组之前过滤数据，而 HAVING 是在分组后过滤数据。通常，HAVING 用于与聚合函数（如 COUNT、SUM、AVG、MAX、MIN）一起使用。

示例：

查询每个部门的员工数量大于 10 的部门：

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

解释：

GROUP BY 对员工按部门进行分组。
HAVING 过滤掉员工数小于等于 10 的部门。

【嵌套子查询？】

嵌套子查询是指在一个查询的 SELECT、FROM 或 WHERE 子句中嵌套另一个查询。子查询通常用于获取一个值或一组值，供外部查询使用。子查询可以是标量子查询、列子查询、行子查询或表子查询。

示例 1：在 SELECT 中使用子查询

查询每个员工的姓名及其对应的部门中薪水最高的员工的薪水：

SELECT employee_name,
       (SELECT MAX(salary) 
        FROM employees AS e2 
        WHERE e2.department = e1.department) AS max_salary_in_department
FROM employees AS e1;

解释：

内部查询 (SELECT MAX(salary) ...) 获取每个部门的最高薪资。
外部查询返回每个员工的姓名以及其所在部门的最高薪资。

示例 2：在 WHERE 中使用子查询

查询薪资高于公司平均薪资的员工：

1
2
3

SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

解释：

内部查询 (SELECT AVG(salary) FROM employees) 计算公司的平均薪资。
外部查询返回薪资高于平均薪资的员工。

【LIMIT/OFFSET分页查询？】

LIMIT 和 OFFSET 用于分页查询，LIMIT 限制返回结果的数量，OFFSET 用于跳过前几个结果。

LIMIT 用于限制返回的行数。
OFFSET 用于跳过指定数量的行，常用于分页。

示例 1：简单的分页查询

假设每页显示 10 条记录，查询第 2 页的数据：

1 2	SELECT * FROM employees LIMIT 10 OFFSET 10;

解释：

LIMIT 10 表示每页显示 10 条记录。
OFFSET 10 表示跳过前 10 条记录，即从第 11 条记录开始返回。

示例 2：结合分页的查询

假设每页显示 10 条记录，查询第 3 页的数据：

1 2	SELECT * FROM employees LIMIT 10 OFFSET 20;

解释：

LIMIT 10 表示每页显示 10 条记录。
OFFSET 20 表示跳过前 20 条记录，即从第 21 条记录开始返回。

其它

【防止SQL注入？】

核心是SQL语句与参数分离。SQL注入即在应用层注入恶意的SQL代码，比如在前端提交中附加SQL查询、修改、删除操作，直接获取或修改数据库数据。

JDBC预处理语句：

String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, username);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();

存储过程：

CREATE PROCEDURE GetUserDetails (IN username VARCHAR(255), IN password VARCHAR(255))
BEGIN
  SELECT * FROM users WHERE username = username AND password = password;
END;

CallableStatement stmt = connection.prepareCall("{CALL GetUserDetails(?, ?)}");
stmt.setString(1, username);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();

应用层参数验证和过滤：

public boolean isValidInput(String input) {
    String regex = "^[a-zA-Z0-9_]+$";  // 只允许字母、数字和下划线
    return input.matches(regex);
}

ORM框架如Hibernate、JPA等自动生成SQL查询，且ORM框架通常会自动防止SQL注入，因为它们默认使用参数化查询。

【SQL规范？】TODODODODODODO

三. MySql

3.1 架构和流程

【MySQL的架构？】

顶层连接层：负责处理客户端的连接请求，并进行身份验证和授权。
服务层：负责处理 SQL 语句的解析、优化和执行，并返回查询结果。
存储引擎层：负责数据的存储和管理，不同的存储引擎具有不同的存储格式和访问方式。
文件系统层：负责数据的物理存储。

【MySQL的执行流程？】

1
2

客户端 → 建立连接 → 查询缓存（若命中则返回） → 解析器（语法/词法分析） → 预处理器（语义检查）
 → 优化器（生成执行计划） → 执行器（调用存储引擎） → 返回结果 → 断开连接

连接：客户端通过TCP协议连接到MySQL服务器（三次握手，四次挥手），并进行身份验证和授权。
- 建立连接：客户端通过 TCP 三次握手 与 MySQL 服务器建立连接，通过身份验证（用户名/密码）和权限校验。
- 连接线程：MySQL 为每个连接分配一个线程（或复用线程池中的线程），处理后续请求。
- 断开连接：客户端关闭连接时，触发 TCP 四次挥手。
查询缓存：8.0之前有缓存机制，会将查询结果缓存起来，命中直接返回查询结果。8.0后取消，因为该缓存比较鸡肋，更新操作就会导致缓存清空（此缓存非InnoDB的buffer pool）。
- 缓存机制：若 SQL 语句命中缓存且数据未更新（相同 SQL、同一用户、同一数据库），直接返回结果。
- 失效机制：任何表的数据修改（INSERT/UPDATE/DELETE）都会导致该表相关的缓存失效。
- 移除原因：缓存命中率低、维护成本高，MySQL 8.0 后移除。现在更加依赖于 InnoDB buffer pool 来管理缓存，优化性能。
解析器：解析SQL语句，语法分析和语义分析。
- 解析器（Parser）：
  - 语法分析：检查 SQL 语法是否正确，生成抽象语法树（AST）。
  - 词法分析：识别关键字（如 SELECT、FROM）、表名、列名等。
- 预处理器：
  - 语义分析：验证表、列是否存在，检查用户权限。
  - 展开通配符：将 SELECT * 扩展为所有具体列。
  - 解析查询的逻辑顺序：
    1
    FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
语法分析：识别关键字。语义分析：判断SQL是否正确。

解析顺序：
- FROM：首先确定要查询的表以及是否有 JOIN 连接。还会处理 ON 连接条件。
  
  WHERE：接着确定查询条件，进行条件过滤。
  
  GROUP BY：然后进行分组操作，确定分组依据。
  
  HAVING：对分组后的数据进行过滤。
  
  SELECT：选择要返回的列。
  
  去重（DISTINCT）：去重操作。
  
  ORDER BY：排序操作。
  
  LIMIT：限制查询结果的行数。
预处理器：预处理器会检查 SQL 中的表名、字段名等是否存在，确保语句逻辑上是正确的，并且涉及的表和列在数据库中都是存在的。
优化器：SQL正确则到达优化器得到执行计划。
- 核心任务：生成高效执行计划，最小化查询成本。
- 优化策略：
  - 规则优化：预定义规则（如优先使用索引）。
  - 代价估算：基于统计信息（如索引基数、数据分布）估算不同执行计划的 I/O、CPU 成本。
  - 决策示例：
    - 选择索引扫描（Index Scan）还是全表扫描（Full Table Scan）。
    - 确定多表连接的顺序（如小表驱动大表）。
- 局限性：基于统计信息估算，可能选择非最优计划。
- 计算各个执行计划的代价。
- 根据预定义的规则匹配。
- 通过贪心算法来搜索最优的执行计划。
- 常见的优化方式：
  - 使用索引。
  - 选择合适的连接顺序（例如，最小的表先连接）。
  - 选择合适的执行路径（如选择 Nested Loop Join、Merge Join 等）。
执行器：最后到执行器里执行。
- 结果集生成：执行器将结果集放入网络缓冲区，逐步返回给客户端。
- 流式处理：若结果集较大，采用流式传输（非一次性加载到内存）。

权限校验：分别在连接建立（全局权限）和执行阶段（对象级权限）进行。
统计信息：优化器依赖 INFORMATION_SCHEMA 中的统计信息（如 TABLE_STATISTICS）。
执行计划缓存：某些场景（如 Prepared Statements）会缓存执行计划，减少重复优化开销。

3.1 索引

【索引是什么？索引的适用场景？不适用场景？失效场景？】

首先索引是一块单独的物理结构，类似于书的目录，所以会有维护开销和空间开销。

数据库索引是什么？
- 索引是一块单独的物理结构，类似于书的目录，所以会有维护开销和空间开销。
- 用于快速定位数据库表中的数据，可以根据关键字（索引键）快速找到数据记录的位置。
- 可以加速数据库的查询操作，减少数据库的扫描次数，从而加快数据检索的速度。
索引的适用场景？
- 数据量大的表
- 需要快速查找特定值的字段，例如唯一标识符、经常用于过滤的列，经常出现在Where子句的字段。
- 需要在排序和分组操作中提高性能的列
- 经常做表连接的列
- 经常用于聚合函数的列。
- 复合索引比多个独立索引效率高，但使用时要多考虑，错误的使用可能会索引失效或性能下降，维护成本更高，只适合于特定的联合查询。
不适合使用索引的场景？
- 选择性低或者说字段价值低的：如性别这种选择性低的字段，存在大量重复数据，不建议创建索引。WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，起不到定位的字段通常是不需要创建索引的，索引是会占用物理空间的。
- 数据量很小的表，因为索引的维护开销可能会超过查询优化带来的性能提升。
- 大的文本字段不建议创建索引，因为它们的值通常很大，创建索引可能会占用大量的存储空间。索引多，数据更新表越慢。
- 频繁进行数据操作的表/经常更新的字段不建议创建索引，因为这些操作可能会导致索引失效或增加维护成本，维护索引的成本太高。
- 无用的索引会对执行计划造成负面影响
索引失效的场景？
1. 不支持索引的操作：
  - 以通配符开始的LIKE：使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效（考虑使用全文检索）
  - 函数、计算操作：例如 where year(date_column) = 2023。
  - 否定判断：where xx != 或 <> …
  - 使用is null或is not null： where xx is (not) null （用 union all 来代替）
  - 存在判断：where xx in/not in …（between，exists或join替换in，用not exists替代not in）
2. 不使用索引的列进行查询：
  - 或判断：where xx or … 如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。（用 union all 来代替）
  - DISTINCT 操作：使用 DISTINCT 操作时，如果索引列的组合不完全符合查询条件，可能会导致索引失效，需要全表扫描。
3. 使用了 JOIN 但没有索引或索引不匹配
4. ORDER BY 和 GROUP BY 操作
  - ORDER BY 列没有索引：如果 ORDER BY 使用的列没有索引，数据库可能会进行额外的排序操作，导致性能下降。尽管在某些情况下数据库会根据索引返回已排序的数据，但在其他情况下，排序可能会导致索引失效。
  - GROUP BY 列没有索引：如果 GROUP BY 使用的列没有索引，数据库就会执行全表扫描并进行分组操作，索引失效。
5. 全表扫描：select * （标明具体返回字段代替）
6. （隐式）类型转换：例如 where int_column = '123'。
7. 数据分布不均匀，例如某个值的重复度特别高，导致查询优化器认为使用索引不划算。
8. 数据量较小，数据库优化器认为全表扫描更快速。

【数据库的索引有哪几种？】

数据库索引类型：

数据结构：B+树索引、Hash索引、Full-text全文索引等
物理存储：
- 聚集索引：聚集索引定义了数据在物理存储上的顺序，表中数据的存储顺序与聚集索引的键值顺序一致。在 MySQL 中，聚集索引通常对主键创建，但也可以是唯一索引。但是在一些其他数据库系统中，如Oracle，每个表只能有一个聚集索引，且该索引必须是主键索引。
- 非聚簇索引：索引的顺序与数据存储的物理顺序没有直接关系。
字段特性：
- 唯一索引：确保索引列的值在整个表中是唯一的，允许有一个 NULL 值。唯一索引可以用来实现主键或唯一约束。
- 主键索引：主键索引是一种唯一索引，它是表的主键字段上的索引，用于确保主键列的唯一性。主键索引不允许 NULL 值。
- 非唯一索引：允许索引列中有重复的值，与唯一索引相对。
字段个数：
- 组合索引：指在数据库表中创建的基于多个列的索引，也称为复合索引。
- 单列索引

【聚集索引与非聚集索引？】

什么是聚集索引？
1. 定义了数据在磁盘上物理存储的顺序，并且叶子节点包含了整个数据行而不仅仅是索引键。在聚集索引中，数据的物理顺序与索引的逻辑顺序一致，这意味着索引的键值排序与数据行在磁盘上的物理存储顺序相同。
2. 叶子节点：聚集索引的叶子节点就是实际的数据行，索引的顺序就是数据的物理顺序。聚集索引存储记录是物理上连续存在。
3. 主键索引：在 InnoDB 存储引擎中，主键索引就是聚集索引。若没有主键索引，InnoDB 会选择一个唯一的非空索引作为聚集索引。
4. 优点：
  - 查询效率高：适合通过主键或者聚集索引进行范围查询（如 BETWEEN、>, < 等）。
  - 存储紧凑：由于数据存储在索引的叶子节点，减少了额外的 I/O 操作。
  - 聚簇索引适合排序，非聚簇索引不适合用在排序的场合，因为聚簇索引叶节点本身就是索引和数据按相同顺序放置在一起，索引序即是数据序，数据序即是索引序，所以很快。非聚簇索引叶节点是保留了一个指向数据的指针，索引本身当然是排序的，但是数据并未排序，数据查询的时候需要消耗额外更多的I/O，所以较慢
5. 缺点：
  - 更新和插入性能差：插入或更新数据时，如果需要维护索引的顺序，可能导致性能下降。特别是插入数据时，若插入的位置不符合索引顺序，可能需要移动大量数据。更新聚集索引列的代价很高，因为会强制innodb将每个被更新的行移动到新的位置。
    
    聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多，数据的物理顺序与索引的逻辑顺序一致，插入新数据时需要按照主键值的顺序将数据插入到正确的位置，这可能涉及到移动现有数据行或者调整索引的结构，因此会引起额外的开销和时间消耗。
  - 只能有一个聚集索引：每个表只能有一个聚集索引，因为数据只能按照一个顺序存储。每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放，实际的数据页只能按照一颗 B+ 树进行排序。
6. 聚集索引在 MySQL 中常见于 InnoDB 存储引擎中，而 MyISAM 存储引擎不支持聚集索引。
什么是非聚集索引？
1. 索引的顺序与数据存储的物理顺序没有直接关系。
2. 数据存储顺序与索引无关：非聚集索引的顺序与数据表中的数据存储顺序无关，数据表中的数据行不会按照索引的顺序排列。
3. 叶子节点：非聚集索引的叶子节点包含的是数据行的指针（即数据行的地址），而不是实际的数据。索引的叶子节点并不存储实际的数据行，而是存储指向对应数据行的引用（例如行的地址或主键值）。聚集索引的叶节点就是数据节点，而非聚簇索引的叶节点仍然是索引节点，并保留一个链接指向对应数据块。
4. 多个索引：一个表可以有多个非聚集索引，且每个索引都可以根据不同的列创建。
5. 优点：
  - 多个索引：一个表可以有多个非聚集索引，支持多种查询条件的优化。
  - 不影响数据存储：非聚集索引的建立不会改变数据的物理存储顺序，因此不会影响现有的数据存储方式。聚集索引存储记录是物理上连续存在，非聚集索引是逻辑上的连续。
6. 缺点：
  - 查询效率较低：查询时，非聚集索引存储的是指针，需要通过指针去查找实际的数据行，这通常比聚集索引稍慢。当通过非聚集索引查询数据时，数据库引擎首先根据索引键值定位到相应的叶子节点，然后通过指针或者引用找到对应的数据行。
  - 存储开销大：由于每个非聚集索引都需要存储指向数据行的指针，因此多个非聚集索引会增加存储空间的开销。
7. 非聚集索引可以建立在除了主键以外的任何列上，通常用于加速针对非主键列的查询操作。由于非聚集索引与数据的物理存储位置无关，因此在插入、更新和删除数据时，不会对索引的结构或性能产生较大影响，相对而言插入速度也通常会快于聚簇索引。
聚簇和非聚簇又可细分哪一些？
- 区别在于叶子节点的存储方式。聚簇索引的叶子节点存储的是数据行，而非聚簇索引的叶子节点存储的是指向数据行的引用。
- 聚集索引:物理存储按照索引排序
- 非聚集索引:物理存储不按照索引排序
- 聚集索引在插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入）,但查询数据比非聚集数据的速度快
使用聚集索引为什么查询速度会变快？
- 使用聚集索引查询速度更快是因为数据库引擎可以通过聚集索引直接找到存储在数据页中的数据行，而不需要再进行额外的查找操作。
建立聚集索引有什么需要注意的地方吗？
- 在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置，索引此时会重排，会造成很大的资源浪费
- 还应避免将大型文本字段（如 BLOB 或 TEXT 类型）包含在聚簇索引中，因为这些字段的存储会增加索引的大小，导致索引结构变得更加庞大，影响查询性能。
- 建表时聚簇索引的选择顺序：
  - 如果有主键，默认会使用主键作为聚簇索引的索引键；
  - 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
  - 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；
非聚集索引最多可以有多少个？
- MySQL 中每个表最多可以创建 64 个索引（包括聚集索引和非聚集索引）。非聚簇索引需要大量的硬盘空间和内存。

【组合索引？最左匹配原则？】

什么是组合索引？
- 组合索引是指在数据库表中创建的基于多个列的索引，也称为复合索引。
- 组合索引使用多个字段值作为B+树的KEY值，前个字段相同时再比较后个字段，所以存在最左匹配原则。
- 范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。比如A > 1 AND B = 2，对于符合A > 1的节点，B是无序的，无法根据B=2来进一步利用索引。
  - 在 MySQL 5.6 之前，只能从 ID2 （主键值）开始一个个回表，到「主键索引」上找出数据行，再对比 b 字段值。
  - 而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。对应explain的Extra 为 Using index condition 。
- 越靠前的字段被用于索引过滤的概率越高，实际开发工作中建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。
- 给 status 和 create_time 列建立一个联合索引，根据 status 筛选后的数据就是按照 create_time 排好序的，避免在文件排序，提高了查询效率。
  1
  select * from order where status = 1 order by create_time asc
组合索引和几个单个的索引有什么区别？
- 当查询有多个字段条件时，单列索引只能命中第一个，而组合索引可以命中全部字段。
- 组合索引需要维护多个列的索引结构，因此在数据修改时可能需要更多的索引维护成本。
- 组合索引需要查询条件中的列按照索引的顺序匹配，只有当查询条件依次匹配组合索引中的列时，才能利用索引。

MySQL中的最左匹配原则是指在使用组合索引时，就是按照最左优先的方式进行索引的匹配。

举例来说，如果有一个联合索引 (col1, col2, col3)，那么针对以下查询：
1
SELECT * FROM table WHERE col1 = 'value1' AND col2 = 'value2';
查询可以使用该联合索引，因为查询条件涵盖了索引的最左边的两个列（col1 和 col2）。但是如果查询条件是：
1
SELECT * FROM table WHERE col2 = 'value2' AND col3 = 'value3';
尽管索引覆盖了 col2 和 col3，但查询无法使用联合索引，因为它从索引的中间开始匹配。
最左匹配原则的原因是MySQL索引的底层数据结构是B+树。B+树是一种多叉树，每个节点存储多个键值对。在联合索引中，索引列的顺序决定了B+树的键值顺序。如果查询语句不符合最左匹配原则，则无法利用B+树的键值顺序进行快速查找，只能进行全表扫描。
联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配
为了利用最左匹配原则优化查询，应注意以下几点：
- 尽量将查询条件放在索引列的最左边。
- 如果查询条件涉及范围查询，应将范围查询放在索引列的最右边。
- 如果查询条件涉及多个索引列，应尽量使用最左匹配原则。

范围查询列的影响：当查询涉及到范围查询的列时，范围查询列后面的列不能再完全利用联合索引，因为索引是有序的，对于范围查询列之后的列，数据不再是严格有序的。因此，后续列无法保证索引的有效利用。

比如 A > 1 AND B = 2，在联合索引 (A, B) 中，A 是范围查询列。对于符合 A > 1 的记录，B 列的顺序不再保证，因此无法继续利用索引来优化对 B = 2 的查询。MySQL 会根据 A > 1 找到一个候选集，但对 B = 2 的过滤则会依赖回表操作。

MySQL 5.6 引入的索引下推优化（ICP）：索引下推优化是 MySQL 5.6 中的一项重要优化，它允许 MySQL 在索引扫描过程中进行条件过滤，而不需要等到回表后再做过滤。

在没有 ICP 优化之前，如果你查询条件涉及联合索引的多个字段，MySQL 会首先通过联合索引定位到符合第一个字段条件的记录（如 A > 1），然后会回表通过主键查找数据，再对其他字段（如 B = 2）进行过滤。
引入 ICP 后，MySQL 在遍历联合索引时，能够直接在索引扫描阶段就对联合索引中包含的字段做判断，过滤掉不满足条件的记录，从而减少回表次数，提升查询性能。

**使用 ICP 后的 EXPLAIN**：当启用索引下推时，你会在 EXPLAIN 的 Extra 字段中看到 Using index condition，表示在索引扫描阶段已经做了条件判断。

【索引下推？】

索引下推（Index Condition Pushdown, ICP）是 MySQL 5.6 引入的一项优化，它可以在扫描索引的过程中提前对索引中的条件进行过滤，避免了全表扫描时的一些不必要的开销。它让 MySQL 在 扫描索引 时就对一些查询条件进行过滤，而不必等到回表后再进行判断，从而减少了回表的次数和 I/O 操作，提高了查询效率。

1. 索引下推的原理

在没有索引下推优化之前，当执行查询时，MySQL 会先通过索引查找符合条件的记录，找到记录后再回表获取完整的数据，然后根据 WHERE 子句的其他条件进行数据过滤。这意味着，过滤操作发生在回表之后，会增加不必要的磁盘 I/O 操作。

索引下推优化改变了这一过程。通过索引下推，MySQL 可以直接在 索引扫描阶段 对一些条件进行判断和过滤，避免不必要的回表操作。

2. 索引下推的工作流程

假设我们有以下的表结构：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    status INT,
    total_amount DECIMAL(10, 2),
    create_time TIMESTAMP,
    INDEX idx_status(status, total_amount)
);

查询语句如下：

1
2
3

SELECT * 
FROM orders
WHERE status = 1 AND total_amount > 100;

没有索引下推时，MySQL 的执行流程如下：

MySQL 使用二级索引 idx_status(status, total_amount) 查找符合 status = 1 的记录。
找到所有符合条件的索引项后，MySQL 会使用回表（通过主键）获取每条记录的完整数据。
回表后，MySQL 会根据 total_amount > 100 再对结果进行过滤。

而有了 索引下推 优化后，MySQL 会在扫描索引时直接对 total_amount > 100 条件进行判断：

扫描索引：MySQL 使用索引 idx_status(status, total_amount) 来查找符合 status = 1 的记录，并且 同时对 total_amount > 100 条件进行判断。
过滤数据：只有符合 status = 1 和 total_amount > 100 条件的记录会被返回。MySQL 不会回表那些不符合条件的记录，避免了不必要的磁盘 I/O。

3. 索引下推的优势

减少回表次数：索引下推可以在索引扫描阶段就进行条件过滤，避免回表那些不符合条件的记录，从而减少回表次数。
降低磁盘 I/O 操作：通过在索引扫描时提前过滤数据，减少了需要回表的记录数量，从而降低了磁盘 I/O 操作的开销。
提高查询效率：减少了回表的次数，尤其是当表中的数据量较大时，能够显著提高查询效率。

4. 适用场景

索引下推优化通常适用于以下几种场景：

多条件查询：当 WHERE 子句中包含多个条件，并且这些条件涉及到索引中的字段时，索引下推能够提前对这些条件进行过滤。

示例：
1
2
3
SELECT *
FROM orders
WHERE status = 1 AND total_amount > 100 AND create_time > '2023-01-01';
这个查询中，status、total_amount 和 create_time 都可以用索引下推进行过滤。
范围查询：当查询条件中有范围条件（如 >、<、BETWEEN 等），且这些范围条件涉及到索引字段时，索引下推能够提升查询性能。

示例：
1
2
3
SELECT *
FROM orders
WHERE status = 1 AND create_time BETWEEN '2023-01-01' AND '2023-12-31';
二级索引：当查询条件涉及到二级索引时，如果能利用索引下推，就能够避免回表，减少不必要的 I/O。

5. 例子：使用索引下推优化

假设我们有一个查询，使用了复合索引 (status, total_amount)：

1
2
3

SELECT * 
FROM orders
WHERE status = 1 AND total_amount > 100;

执行计划（没有索引下推）：

1
2
3

EXPLAIN SELECT * 
FROM orders
WHERE status = 1 AND total_amount > 100;

输出结果可能类似于：

1
2
3

| id  | select_type | table  | type  | possible_keys   | key           | key_len | ref  | rows  | Extra          |
| --- | ----------- | ------ | ----- | --------------- | ------------- | ------- | ---- | ----- | -------------- |
| 1   | SIMPLE      | orders | range | idx_status      | idx_status    | 5       | NULL | 1000  | Using where    |

这里的 Extra 显示了 Using where，表示虽然使用了索引，但是查询仍然需要回表并在回表后进行 WHERE 条件过滤。

执行计划（使用索引下推）：

在 MySQL 5.6 及以上版本，如果启用了索引下推优化，执行计划可能会如下所示：

1
2
3

EXPLAIN SELECT * 
FROM orders
WHERE status = 1 AND total_amount > 100;

输出结果可能如下：

1
2
3

| id  | select_type | table  | type  | possible_keys   | key           | key_len | ref  | rows  | Extra                |
| --- | ----------- | ------ | ----- | --------------- | ------------- | ------- | ---- | ----- | -------------------- |
| 1   | SIMPLE      | orders | range | idx_status      | idx_status    | 5       | NULL | 800   | Using index condition |

在这个执行计划中，Extra 显示了 Using index condition，表示 MySQL 已经在索引扫描过程中进行了条件过滤，减少了回表的次数。

6. 适用的索引类型

索引下推优化对于 非唯一索引（如 二级索引）特别有效。在唯一索引中，MySQL 本来就可以通过索引直接定位到唯一的记录，因此索引下推的优化效果相对较小。

7. 注意事项

查询条件的适用性：索引下推并不适用于所有查询条件，只能对索引扫描时可以利用的条件进行下推，某些复杂的条件（如包含子查询、函数等）可能无法被索引下推优化。
版本要求：索引下推优化是从 MySQL 5.6 开始支持的，因此需要 MySQL 5.6 及以上版本才能启用该功能。

【MySql有哪几种索引实现？】

B树索引（B-tree Index）：
- 是一种平衡树结构，适用于范围查询和精确查询，常见于大多数数据库系统中。包括普通B树索引和B+树索引。
- 优点： 支持范围查询和排序，适用于等值查询、范围查询和排序操作。适用于大部分场景，是最常用的索引类型。
- 缺点： 对于前缀查询、模糊查询等特殊场景效率较低。
哈希索引（Hash Index）：
- 使用哈希函数将索引列的值映射到索引表中的存储位置。
- 优点： 高效的等值查询，适用于频繁的等值查询操作。
- 缺点： 不支持范围查询和排序，只能用于精确匹配。并且哈希冲突较多时查询效率会降低。
- 适用场景： 如查询用户ID、唯一编码等。
全文索引（Full-Text Index）：
- 用于对文本字段进行全文搜索，支持自然语言查询，例如MySQL的FULLTEXT索引。
- 优点： 能够高效地处理包含文本的查询。
- 缺点： 只能用于文本字段，并且全文索引占用空间较大。
- 适用场景： 如文章内容、新闻标题等。
空间索引（Spatial Index）：
- 用于在空间数据类型上进行空间查询，例如地理信息系统（GIS）中常用的空间索引。
- 优点： 支持空间数据类型的查询，如点、线、面等。
- 缺点： 只能用于空间数据类型的字段。
- 适用场景： 处理地理信息、地图数据等空间数据类型的查询。
位图索引（Bitmap Index）：
- 使用位图来表示每个索引值的存在情况，适用于低基数列和频繁的多值查询，例如数据仓库中的数据分析查询。
- 优点： 在等值查询和范围查询等情况下有高效的查询性能；可以极大地节省存储空间；支持位运算操作。
- 缺点：当列的基数较高时，位图索引的效率会降低；发生更新（插入、更新、删除）时，位图索引的更新代价很高；不适合应用于频繁变更的列。
- 适用场景：
  1. 稀疏列： 当列的值分布稀疏，基数较低时，位图索引效果更好，可以极大地提升查询性能。
  2. 数据仓库： 位图索引在数据仓库等只读环境下特别有效，因为数据不经常更新，可以充分利用位图索引的查询性能优势。
  3. 复杂查询需求： 当需要进行复杂的查询，涉及多个列的AND、OR、NOT等位运算操作时，位图索引能够提供很好的支持，简化查询语句和提升查询性能。

【MySQL的索引结构的实现B+树】

MySQL的默认索引结构是？Innodb中索引的实现？
- B+树。
B树和B+树的结构分别是？B树和B+树都是一种多路搜索树，常用于数据库和文件系统中的索引结构。B树和B+树都是大的在右边，小的在左边。
- B树（B-Tree）：
  - 节点结构： B树的节点包含关键字和子节点指针，每个节点可以有多个子节点。
  - 数据存储： B树的每个节点同时存储数据，叶子节点包含了数据的完整信息（实际是指针）。
  - 查找方式： 通过节点间的指针进行逐层搜索，每一层的节点可以包含多个关键字，提高了查找效率。
  - 应用场景： 适用于内存空间有限，需要高效进行插入、删除和查找操作的场景，例如文件系统的索引结构。
  - B树是一种自平衡的树，能够保持数据有序，一个节点允许拥有两个以上的子节点。
  - 2-3树、红黑树都是常见的B树。
  - 多路搜索： 平衡二叉树通常是指查找路径只有两种，而B树则不仅仅二叉，所以也叫平衡多路查找树。
  - B树相比平衡二叉树在每个结点所包含的内容更多，在应用到数据库中的时候，充分利用了磁盘块的原理（磁盘数据存储是采用块的形式存储的，每个块的大小为4K，每次IO进行数据读取时，同一个磁盘块的数据可以一次性读取出来）把结点大小限制和充分使用在磁盘块大小范围。所以树的结点能包含更多的内容后，树的层级比原来的二叉树少了，就可以减少数据查找的次数和复杂度。
  - B树相对于B+树的优点是，如果经常访问的数据离根节点很近，而B树的非叶子节点本身存有关键字其数据的地址，所以这种数据检索的时候会要比B+树快。
- B+树（B+Tree）：
  - 节点结构： B+树的内部节点/非叶子节点只包含关键字和子节点指针，叶子节点存储数据，只存储关键字和指向数据的指针。
  - 数据存储： 数据只存储在叶子节点上，叶子节点之间通过两个指针分别指向前一个和后一个节点链接成双向链表，便于范围查询。
  - 查找方式： 通过内部节点的关键字进行搜索，查找到叶子节点后再进行线性查找，提高了范围查询的效率。
  - 应用场景： 适用于需要频繁进行范围查询和顺序遍历的场景，例如数据库中的索引结构。
  - B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。
  - 特征：
    - 平衡性：B+树的每个节点都保持平衡，即每个节点的子节点数量相同或相差1。
    - 多路查找：B+树的每个节点可以存储多个键值对，因此查找效率较高。
    - 顺序访问：B+树的叶子节点通过指针连接在一起，形成一个有序链表，因此可以支持顺序访问。
  - B+ 树元素自底向上插入，这与二叉树恰好相反。
  - B+树的非叶子结点不保存关键字记录的指针，只进行数据索引，使B+树每个非叶子结点所能保存的关键字数大大增加。
  - B+树叶子结点保存了父结点的所有关键字记录的指针，所有数据地址必须到叶子结点才能获取到，所以每次数据查询的次数都相同。
  - B+树叶子结点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针。
  - B+树查找的示例：
    
    假设B+树结构为：
    - 根节点：1、10、20
    - 二层节点：1、4、7，……
    - 叶子节点：1、2、3，4、5、6，……
    一条SQL查询id为5的数据，首先于根节点进行比较，在1和10之间，所以到1连接的第二层节点；再比较后发现位于4和7之间，所以到4连接的叶子节点，最终找到索引值为5的数据。总共经历3个节点，也就是3个I/O操作。所以B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。如果查询条件不是ID，比如走普通索引，则要先查普通索引的B+树找到id，再查主键索引的B+树。若普通索引字段能全部在叶子节点查到，即索引覆盖，无需再查一个B+树。
为什么要用B+树来做索引？而不是选择B树、红黑树、Hash？
- 要求：
  - 数据库的数据和索引都存储在磁盘，查询过程涉及到多次磁盘IO，索引结构要尽量减少IO次数来降低时间小号。
  - 支持高效的进行范围查找。
- B+树对比二叉查找树/自平衡二叉树/红黑树：
  - 二叉查找树（BST）：极端情况下会退化为链表，查询时间复杂度为 O(logn) 到 O(n)。
    - 查找效率：二叉查找树的查找效率依赖于树的平衡性，如果树不平衡，最坏情况下会退化成链表，查找效率变为 O(N)，不再满足数据库的高效查询要求。
    - 磁盘I/O：二叉查找树没有内节点和叶节点的分层结构，所有的节点都是一一连接的，因此很难高效地进行磁盘访问。尤其在内存较小的情况下，二叉查找树需要访问大量的磁盘块，导致 I/O 操作次数增加。
  - 平衡二叉查找树（AVL 树）左右子树高度差不会超过1，红黑树也是一种AVL，都会随着插入元素增多，导致树的高度变高，意味着很多磁盘IO。
    - 查找效率：平衡二叉树通过旋转等操作确保树的平衡，能够提供 O(log N) 的查找效率，但其插入、删除的操作较复杂，尤其是涉及到频繁的旋转操作，效率会下降。
    - 磁盘 I/O：与 B+ 树相比，平衡二叉树的节点存储更多的信息（如左右子树的指针），这使得每个节点在内存中占用更多的空间。当需要频繁地访问磁盘时，平衡二叉树的树高较高，导致磁盘 I/O 的次数增加。
  - 红黑树结构的高度要比B+树高很多，效率明显比B-Tree差很多。即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右。M叉树的高度要远小于二叉树。
- B+树对比B树，查询、插入和删除效率更高：
  1. B+树更矮：B+树的所有索引和数据都存储在叶子节点，而B树的非叶子节点也存储索引值。B+树的非叶子节点不需要存储数据，可以更好地组织树的宽度，从而使得树更加矮胖。检索时的磁盘IO次数更少。
  2. B+树存在冗余节点，删除节点时不会发生复杂的变形，插入也不需要像红黑树那样要复杂的旋转操作。即B+ 树的插入和删除效率更高。
  3. B+树的叶子节点间存在双向链表。双向链表进行范围查询和排序更加简单快速。B+树相邻接点的指针可以大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找。
  4. 更适合SSD等存储介质：SSD的读写速度比传统磁盘快很多，但仍然存在随机读写速度慢的问题。B+树的顺序读写特性可以有效利用SSD的优势，减少随机读写操作，提高查询效率。
- B+树对比Hash：
  - Hash等值查询很快O(1)，但无法做范围查询。
B+树索引的原理？
- 平衡性： B+树是一种平衡树，每个节点的左右子树的高度差不超过1，因此在查找、插入和删除等操作时具有稳定的性能表现。
- 多路搜索： B+树是一种多路搜索树，每个节点可以存储多个键值对，使得每次搜索可以跳过多个节点，从而减少IO次数，提高检索效率。
- 节点结构： B+树的非叶子节点只存储键值和指向子节点的指针，不存储数据，而叶子节点包含了所有的键值和对应的数据。
- 有序性/顺序访问： B+树的叶子节点按照键值的大小顺序排列，这样可以支持范围查询和顺序扫描。
B+树的实现步骤？
1. 插入：当需要向B+树中插入一个新的键值对时，首先找到该键值对应该插入的叶子节点。
2. 分裂：如果叶子节点已满，则需要进行分裂。分裂操作会将叶子节点分成两个新的节点，并将其父节点的键值对更新为指向这两个新的节点的指针。
3. 删除：当需要从B+树中删除一个键值对时，首先找到该键值对所在的叶子节点。
4. 合并：如果删除操作导致叶子节点的键值对数量不足，则需要进行合并。合并操作会将两个相邻的叶子节点合并为一个新的节点，并将其父节点的键值对更新为指向该新的节点的指针。
为什么mongodb的索引用了B树，而mysql用B+树？
- MongoDB: 是文档型的数据库，是一种 nosql，它使用类 Json 格式保存数据。 MongoDB使用B-树，所有节点都有Data域，只要找到指定索引就可以进行访问，无疑单次查询平均快于Mysql。
- Mysql：作为一个关系型数据库，数据的关联性是非常强的，区间访问是常见的一种情况，B+树由于数据全部存储在叶子节点，并且通过指针串在一起，这样就很容易的进行区间遍历甚至全部遍历。

【什么是索引覆盖？如何减少回表？】

索引覆盖是指一个查询的结果可以完全通过索引来获取，而不需要再去主键索引或数据页中查找。在这种情况下，数据库引擎只需要扫描索引而不必去访问实际的数据行，从而减少了I/O操作，提高查询的性能。

索引覆盖通常发生在简单的查询中，例如只查询表的某几个字段，而这些字段恰好都包含在一个索引中。这种情况下，数据库引擎可以直接利用索引的数据来满足查询需求，而无需额外的操作。

1️⃣ 什么是索引覆盖（Covering Index）？

索引覆盖 是指当查询的数据可以完全从索引中获取，而无需回表读取数据行的情况。

在 MySQL 中，InnoDB 存储引擎使用 B+ 树索引，当执行查询时，如果需要的数据（包括 SELECT 列和 WHERE 条件）全部被某个索引包含，MySQL 可以直接从索引树中返回数据，而无需访问表中的记录，这种情况称为 索引覆盖。

✅ 索引覆盖的优势：

提升查询性能：避免回表，减少 I/O 操作，提升查询速度。
减少锁的竞争：索引树更小，数据获取更快，减少行锁竞争。
节省 I/O 开销：索引通常比整行数据小，能更快地从缓存中获取结果。

📌 示例 1：索引覆盖查询

假设有以下 users 表：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    age INT,
    city VARCHAR(50),
    INDEX idx_username_age (username, age) -- 创建复合索引
);

情况 1️⃣：使用索引覆盖

以下查询可以使用 索引覆盖，因为 username 和 age 都在 idx_username_age 索引中：

1
2
3

SELECT username, age
FROM users
WHERE username = 'John';

✅ 执行过程：

根据 WHERE 条件使用 idx_username_age 索引定位数据。

由于 SELECT 中的字段也在索引中，直接从索引中返回数据，无需回表。

情况 2️⃣：未使用索引覆盖

如果查询需要 city 字段，而 city 不在索引中，则无法实现索引覆盖：

1
2
3

SELECT username, age, city
FROM users
WHERE username = 'John';

❌ 执行过程：

根据 idx_username_age 索引定位到记录的主键（或数据页）。

根据主键回到表中，获取 city 字段的值（回表操作）。

2️⃣ 如何减少回表？

回表是指当索引无法满足查询需求时，数据库通过索引定位行数据的主键或地址，再回到主表中获取完整数据。回表会增加 I/O 和 CPU 开销，影响查询性能。

✅ 减少回表的优化策略：

📌 ① 使用覆盖索引

只查询索引中已包含的字段，尽量使用 覆盖索引 来避免回表。

✅ 优化前（需要回表）：

1
2
3

SELECT username, age, city
FROM users
WHERE username = 'John';

✅ 优化后（索引覆盖，避免回表）：如果只需要 username 和 age，可以通过以下方式实现覆盖索引：

1
2
3

SELECT username, age
FROM users
WHERE username = 'John';

📌 ② 使用合适的复合索引

在 WHERE、ORDER BY、GROUP BY 中涉及多列查询时，使用 复合索引 来尽量覆盖所有查询字段，减少回表。

✅ 示例：假设你频繁执行如下查询：

1
2
3

SELECT username, age
FROM users
WHERE city = 'Beijing';

如果只为 city 建立单列索引，仍需回表获取 username 和 age。

✅ 优化方案：创建一个复合索引，覆盖 city、username、age：

1	CREATE INDEX idx_city_user_age ON users(city, username, age);

📌 ③ 覆盖主键字段

如果查询中涉及到主键字段，InnoDB 会使用主键索引，避免回表。

✅ 示例：

1
2
3

SELECT id, username
FROM users
WHERE id = 1001;

由于 id 是主键，InnoDB 直接使用聚簇索引，无需回表。

📌 ④ 适当增加索引字段

在不影响性能和写入速度的前提下，适当扩展索引 以包含查询需要的字段，确保索引能够完全覆盖查询需求。

✅ 示例：

1
2
3

SELECT username, age, city
FROM users
WHERE city = 'Beijing';

可以创建以下复合索引：

1	CREATE INDEX idx_city_user_age ON users(city, username, age);

📌 ⑤ 使用 EXPLAIN 分析执行计划

使用 EXPLAIN 查看 SQL 是否命中了索引，以及是否发生了回表操作。

✅ 示例：

1	EXPLAIN SELECT username, age FROM users WHERE username = 'John';

关键字段解析：

type：如果是 index，表示使用了索引覆盖。
extra：
- Using index：表示使用了索引覆盖，没有回表。
- Using where：使用了索引但未覆盖全部字段，可能会回表。

🎯 3️⃣ 总结

优化策略	作用
使用覆盖索引	确保查询的字段都在索引中，减少回表。
复合索引	针对多列查询使用复合索引，覆盖更多字段。
覆盖主键字段	InnoDB 聚簇索引能避免主键的回表。
扩展索引字段	适当增加索引字段，确保索引覆盖查询需求。
EXPLAIN 分析执行计划	定位是否发生回表，调整索引和查询语句。

🔎 核心思路：

让索引覆盖更多查询字段，尽量减少回表。
合理设计复合索引，避免多次回表读取数据。
定期分析执行计划，监控并优化慢查询。

【表建立索引的原则？】

建立索引是提高数据库查询性能的常见方法，但过多或不恰当的索引会影响数据库的写入性能、占用存储空间，并增加维护开销。以下是一些建立索引的原则：

选择性高的列建立索引
- 高选择性是指该列的数据值非常多，每个数据值都有较强的区分性。例如，身份证号码、用户名等，这些列在表中有较少重复的值。高选择性的列能更有效地利用索引，提升查询性能。
- 例如：WHERE username = 'John' 查询时，用户名通常是唯一的，因此建立索引能够显著提升查询速度。
常用的查询条件列建立索引
- 对于经常出现在 WHERE、JOIN、ORDER BY、GROUP BY 子句中的列，应该考虑建立索引。特别是用于过滤、排序的列。
- 例如：经常查询某个日期范围的记录，可以在日期列上建立索引。
复合索引
- 对于多列经常一起查询的情况，可以考虑创建复合索引。复合索引是指在多个列上创建一个索引，能提高多个条件组合查询的性能。
- 例如：如果查询经常是 WHERE city = 'Beijing' AND age > 30，那么可以创建一个包含 city 和 age 的复合索引。
避免在频繁更新的列上建立索引
- 如果某个列的值经常更新（如计数、状态等），则不建议在该列上建立索引，因为每次更新都需要更新索引，增加写入性能的开销。
避免在低选择性的列上建立索引
- 如果列的值重复度很高，查询时不能很好地利用索引，例如性别、国家等列。低选择性的列（如性别：male、female）往往不能通过索引提高查询效率，甚至可能降低性能。
避免在小表上建立索引
- 如果表非常小（例如，数据量不足几百条记录），通常没有必要建立索引，因为全表扫描的成本非常低，不会对查询性能造成显著影响。
考虑索引的维护成本
- 每次对表进行插入、更新或删除操作时，相关索引也需要更新，这会带来额外的性能开销。在表的写操作频繁时，过多的索引可能会影响数据库的性能。

【为什么不建议使用UUID作为索引/主键？】

UUID 的插入顺序不连续

UUID 是一个全局唯一的标识符，通常是随机生成的。由于 UUID 是随机的，插入的顺序是不可预测的，导致以下问题：

碎片化的 B+ 树结构：InnoDB 存储引擎使用 B+ 树来存储索引数据。如果主键是 UUID，插入的数据不会按照顺序排列，而是分散的，这导致了 索引页的频繁分裂。每次插入新数据时，数据库都需要在索引树中插入一个新的节点，并可能导致重新平衡索引结构，这样会大大增加写入操作的开销。

比较来说，自增主键（如 AUTO_INCREMENT）则是按顺序插入的，这样可以保持索引结构的有序性，避免频繁的索引页分裂。

UUID 的存储空间开销较大

UUID 通常为 128 位（16 字节），而自增主键一般只需要 4 字节（INT 类型）或 8 字节（BIGINT 类型）。由于 UUID 的长度较长，它不仅占用更多的存储空间，还可能导致以下影响：

更大的索引：UUID 作为主键时，索引树中的每个节点都需要存储 16 字节的 UUID，而不是通常的 4 字节或 8 字节。这导致索引占用的空间更大，影响查询性能。
增加内存和磁盘 I/O 的开销：由于索引的大小增加，数据库需要更多的内存和磁盘 I/O 来维护这些索引和存储数据。

查询效率低下

由于 UUID 的随机性，查询基于主键（或索引）的效率会受到影响：

扫描的效率低：传统的 B+ 树索引会根据数据的顺序进行查询和定位，UUID 的随机插入打破了这个顺序，导致查询时可能需要进行更多的页读取和磁盘 I/O。
分页查询变慢：如果你的查询是基于主键进行分页（例如 LIMIT 和 OFFSET），UUID 的随机顺序使得数据库必须跳过许多不相关的记录，从而导致性能下降。

难以与业务需求对接

UUID 是全局唯一的，但在大多数业务场景下，并不需要全局唯一标识符。对于数据库中的主键，自增的整型主键通常更具可读性和简洁性。如果你使用 UUID 作为主键，会增加应用层的复杂性，特别是当你需要频繁操作或生成主键时，UUID 比自增主键要大且不便于调试。

写入性能问题

大量随机写入：由于 UUID 是随机的，每次插入时都会导致数据库在 B+ 树中进行新的位置插入和页分裂，导致 写入性能下降。相比之下，自增主键由于数据按顺序插入，可以避免频繁的树分裂，提高写入效率。

数据库迁移和备份的困难

由于 UUID 是一个全局唯一标识符，可能会让数据迁移和备份变得复杂。比如：

如果你在多个数据库或系统中使用 UUID 作为主键进行数据同步，会增加管理和协调的复杂性。
如果系统数据量庞大，查询时需要多次查询 UUID，增加了查询的复杂度。

无法顺序生成

有些应用程序要求主键具有时间戳或按顺序递增的特点，以确保数据的顺序性。UUID 是完全随机生成的，不能反映记录的插入时间和顺序，这使得一些基于时间排序的操作变得不太方便。

【一条select语句的查询过程？二级索引？】

一条 SELECT 语句的查询过程涉及多个步骤，尤其是当查询使用二级索引时，数据库需要结合索引结构和存储引擎特性进行高效检索。以下是详细流程及二级索引的核心作用：

一、SELECT 语句的通用查询流程（以 InnoDB 为例）

解析与优化
- 语法解析：将 SQL 语句解析为抽象语法树（AST）。
- 逻辑优化：重写查询（如子查询展开、谓词下推）。
- 物理优化：选择最优执行计划（是否走索引、连接顺序等）。
执行阶段
- 访问路径选择：根据 WHERE 条件判断是否使用索引。
- 数据检索：
  - 全表扫描：无可用索引时，逐行扫描数据页。
  - 索引扫描：通过索引定位数据（覆盖索引或回表查询）。
- 结果处理：排序（ORDER BY）、分组（GROUP BY）、过滤（HAVING）等。
返回结果
- 将最终结果返回客户端。

二、二级索引的核心机制

二级索引的结构
- B+树结构：与主键索引（聚簇索引）类似，但叶子节点存储的是：
  - 索引列的值 + 主键值（非完整数据行）。
- 示例：
  1
  2
  3
  4
  5
  6
  CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100),
  INDEX idx_email (email) -- 二级索引
  );
  - idx_email 索引的叶子节点包含 email 值和对应的 id。
查询流程（使用二级索引）
- 步骤 1：索引扫描
  通过二级索引 idx_email 查找 email = 'alice@example.com' 的记录，获取对应的 id。
- 步骤 2：回表查询（Bookmark Lookup）
  使用 id 回主键索引（聚簇索引）查找完整数据行。
- 步骤 3：返回结果
  将最终数据返回。
1
2
-- 示例查询（需回表）
SELECT * FROM users WHERE email = 'alice@example.com';
覆盖索引优化
- 如果查询的列全部包含在二级索引中，则无需回表。
  1
  2
  -- 示例（无需回表）
  SELECT id, email FROM users WHERE email = 'alice@example.com';
- 二级索引 idx_email 的叶子节点已包含 email 和 id，直接返回结果。

三、二级索引的代价与优化

优势
- 减少扫描范围：通过索引快速定位数据，避免全表扫描。
- 排序优化：索引天然有序，可避免 ORDER BY 的额外排序操作。
  1
  SELECT * FROM users WHERE email > 'a' ORDER BY email; -- 利用索引顺序
劣势
- 回表代价：二级索引查询后可能需要回表，增加 I/O 开销。
- 维护成本：索引占用存储空间，且写入时需要更新索引树。
优化策略
- 避免回表：使用覆盖索引或仅查询索引列。
- 索引下推（ICP）：在存储引擎层提前过滤数据，减少回表次数（MySQL 5.6+ 支持）。
- 联合索引：将高频查询列组合为联合索引，减少索引数量。
  1
  CREATE INDEX idx_name_email ON users (name, email);

四、执行计划分析（EXPLAIN）

通过 EXPLAIN 可观察是否使用二级索引及查询类型：

关键字段	说明
type	`ref`（普通二级索引查询）、`index`（索引全扫描）、`range`（范围查询）
key	实际使用的索引名称
rows	预估扫描行数
Extra	`Using index`（覆盖索引）、`Using where`（回表过滤）

示例分析：

1	EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

结果可能为：

type: ref
key: idx_email
Extra: Using index condition（索引下推生效）

五、不同场景下的索引选择

场景	索引策略
等值查询	单列二级索引或联合索引最左前缀
范围查询	联合索引的范围列放在最后（如 `WHERE a=1 AND b>10`，索引 `(a,b)`）
排序 + 过滤	联合索引按过滤列在前、排序列在后创建（如 `WHERE a=1 ORDER BY b`，索引 `(a,b)`）

六、总结

二级索引通过 缩小查询范围 和 避免全表扫描 提升性能，但可能因回表增加开销。
合理设计索引（覆盖索引、联合索引）和优化查询语句（避免 SELECT *）是关键。
使用 EXPLAIN 分析执行计划，结合业务场景权衡读写性能。

3.2 事务

【什么是数据库事务？】

指满足ACID特性的一组数据库操作序列，要么完全执行，要么完全不执行。

【数据库事务有哪些特性？事务特点（ACID）？MySQL如何实现四个特性？】

ACID包括：

原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。以防止并发执行导致的数据不一致性。隔离性通常通过锁机制或多版本并发控制（MVCC）来实现。
持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。持久性通过将事务的改变写入到磁盘上的日志文件来实现，系统发生奔溃可以用重做日志（Redo Log）进行恢复，从而实现持久性。

MySQL如何实现四个特性：

原子性：undo log（回滚日志）
一致性：持久性+原子性+隔离性来保证；MySQL 在执行事务时会检查数据是否符合约束条件，如主键、外键、唯一性约束等。在事务执行前后，数据库会进行一致性检查，确保数据的完整性。若事务过程中违反了某些规则，事务会被回滚，保持数据的一致性。
隔离性：MVCC（多版本并发控制）或锁机制
持久性：redo log （重做日志）

【数据库事务的并发一致性问题有哪些？怎么解决？】

并发一致性问题包括哪几种？

在并发环境下，事务的隔离性很难保证，所以有几种并发一致性问题：

修改丢失：即一个事务的更新被另一个事务的更新替换。
脏读（Dirty Read）：指一个事务读到另一个事务未提交的数据。
不可重复读（Non-Repeatable Read）：指一个事务多次读取同一数据，两次读取数据不一致。可能是因为在此期间另外事务也访问了此集合并做出修改导致。
幻读（Phantom Read）：在同一个事务内，两次查询结果集不一致，可能是因为在两次查询之间有其他事务插入或删除了数据。本质也是不可重复读，前一个事务读取某个范围的数据，另一事务在此范围插入新数据，导致前个事务再次读取结果不一致。区别在要避免幻读需要锁整张表，而避免不可重复读只需锁住行即可。

如何处理并发一致性问题？

设置事务隔离级别。
锁机制。
多版本并发控制MVCC。

【数据库事务的几种隔离级别？】

隔离级别对应不能解决的并发问题：

读未提交->脏读-其他事务未提交的数据
读已提交->不可重复读同一事务两次读取的数据不同
可重复读->幻读同一事务两次读取的记录数量不同
串行化

读未提交（READ UNCOMMITTED）：事务中的修改，即使没有提交，对其它事务也是可见的。允许一个事务读取另一个事务未提交的数据。这是最低的隔离级别，会导致脏读、不可重复读和幻读问题。
读已提交（READ COMMITTED）：一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。这是大多数数据库系统的默认隔离级别。可以解决脏读问题，但依然可能导致不可重复读和幻读问题。
可重复读（REPEATABLE READ）：保证一个事务在读取数据时，不会被其他事务的更新操作影响。保证在同一个事务中多次读取同一数据的结果是一样的。可以解决脏读和不可重复读问题，但可能导致幻读问题。
可串行化（SERIALIZABLE）：强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。最高的隔离级别，确保事务的完全隔离，不允许其他事务同时访问相同的数据。可以解决脏读、不可重复读和幻读问题，但性能开销较大，一般情况下使用较少。

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

MySQL怎么实现这四种隔离级别？

读未提交：因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了
读提交：是在「每个语句执行前」都会重新生成一个 Read View
可重复读：是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View

串行化：通过加读写锁的方式来避免并行访问

隔离级别	脏读	不可重复读	幻读	实现核心机制
READ UNCOMMITTED	可能	可能	可能	直接读取最新数据（无 MVCC 版本控制）
READ COMMITTED	不可能	可能	可能	每次查询生成独立的 ReadView（MVCC），仅读取已提交的数据版本
REPEATABLE READ	不可能	不可能	可能	事务开始时生成全局 ReadView（MVCC），配合间隙锁（Gap Lock）部分解决幻读
SERIALIZABLE	不可能	不可能	不可能	强制所有读操作加共享锁（S Lock），写操作加排他锁（X Lock），完全串行

【你们生产用的是哪种事务隔离级别？为什么？】

MySQL默认隔离级别是？你们用的是哪种事务隔离级别？

MySql默认是可重复读，但很多项目都采用读已提交。

为什么MySql默认是可重复读？

MySql的历史版本中，主从复制基于binlog（记录数据库修改的文件），当时binlog只有 statement 这种模式（记录修改SQL语句），后来还有row（记录每行数据变更）和 mixed（前两种模式混合）总共三种模式。
statement模式在读已提交下进行主从复制有BUG，在master上执行的顺序为先删后插，而binlog的记录顺序为先插后删，所以slave同步binlog后导致执行顺序和master不一致。

该问题的解决方法：

调整隔离级别为可重复读。
更新模式为row（5.1版本后引入）。

为什么选择读已提交？

首先排除读未提交和串行化，前者会出现脏读，后者每次读操作都要加锁。

选读已提交的原因：

可重复读下，存在间隙锁（封锁索引记录中的间隔）导致死锁概率增加：如 SELECT * FROM test WHERE id BETWEEN 5 AND 7，表中有id（1，5，7，10），所以存在5个间隙，上述sql会锁住(5,7]和(7,10]这两个区间，导致区间无法插入数据。而读已提交不存在间隙锁。
可重复读下，条件未命中索引会锁表，而读已提交，只会锁行。
读已提交，因为半一致性读的特性会增加update操作的并发性。一个事务执行完update操作，但尚未提交，另一事务获取锁时发现已占用，InnoDB开启半一致性读返回最新的已提交版本，MySql会重新发起一次读操作，此时可以读到此行的最新版本并加锁，而可重复读这时事务只能等待前一事务提交。
不可重复读问题可以接受，已经提交了数据，即使不一样通常也不会造成问题。

考量点	READ COMMITTED 优势
性能	减少锁竞争，提升并发能力（无间隙锁）
死锁风险	间隙锁可能导致死锁，`READ COMMITTED` 减少间隙锁使用，降低死锁概率
业务需求	允许不可重复读和幻读（可通过业务逻辑或乐观锁补偿）
兼容性	更接近其他数据库（如 Oracle、PostgreSQL）的默认行为，方便迁移

【AUTOCOMMIT？】

AUTOCOMMIT是一个会话级别的系统变量，用于控制事务的自动提交行为。MySQL 默认采用自动提交模式。
即使没有显式使用START TRANSACTION语句来开始一个事务，那么每个查询操作都会被当做一个事务并自动提交。

【MySql中数据库事务是如何实现的？】

InnoDB存储引擎：

通过预写日志（Write-Ahead Logging，WAL）方式实现的。在预写日志中，所有对数据库的修改操作都首先写入日志文件，然后再更新数据库的实际数据文件。Redo和Undo机制是数据库实现事务的基础，包括：Buffer Pool、LogBuffer、Redo Log和Undo Log。
使用了多版本并发控制（MVCC）来实现事务的隔离性和并发控制。这意味着一个事务在执行时不会阻塞其他事务的读操作，也不会被其他事务的写操作所阻塞。
使用了两段锁定（Two-Phase Locking）协议来管理事务并发访问数据的方式。在这种协议下，事务在执行期间会获取锁定，并在适当的时候释放锁定，以确保数据的一致性和完整性。
还使用了自适应哈希索引和自适应复制算法来提高事务处理的性能和可靠性。

一个Update语句的流程：

InnoDB收到SQL后，先根据条件（如主键或其他索引）找到数据所在页，并将这些页从磁盘读取到内存的Buffer Pool中。为了减少磁盘I/O操作的次数，提高数据的访问速度。
执行Update语句，修改的操作会直接在内存中的Buffer Pool中进行，修改Buffer Pool即内存中的数据。
针对Update语句生成一个Redo Log对象，并存入LogBuffer中。Redo Log记录了修改操作的详细信息，包括修改的页号、偏移量和修改前后的数据值等。这样做是为了在系统发生故障或崩溃时，能够通过Redo Log来重新应用修改操作，从而确保数据的一致性和完整性。
针对Update语句生成一个Undo Log对象，用于事务回滚。Undo Log记录了修改操作的反向操作，即撤销对数据的修改。这样做是为了在事务回滚时能够快速地恢复到事务开始之前的状态。
如果事务提交，则把Redo Log对象持久化到磁盘上的Redo Log文件中，后续有其它机制将Buffer Pool修改的数据页持久化到磁盘。
如果事务回滚，则利用Undo Log对象进行回滚。

【七种事务传播行为？】

Propagation.REQUIRED（默认）：如果当前存在事务，则加入该事务，如果当前不存在事务，则创建一个新的事务。这是最常用的传播行为。
Propagation.SUPPORTS：如果当前存在事务，则加入该事务；如果当前不存在事务，则以非事务的方式继续运行。适用于不需要事务保护的方法，但可以利用现有的事务。
Propagation.MANDATORY：如果当前存在事务，则加入该事务；如果当前不存在事务，则抛出异常。确保方法在事务中运行，如果没有事务存在，则抛出异常。
Propagation.REQUIRES_NEW：重新创建一个新的事务，如果当前存在事务，会将当前事务挂起，新建的事务独立执行。适用于需要独立事务运行的情况。
Propagation.NOT_SUPPORTED：以非事务的方式运行，如果当前存在事务，则暂停当前的事务。适用于不需要事务保护的方法，但需要暂时性地禁用事务。
Propagation.NEVER：以非事务的方式运行，如果当前存在事务，则抛出异常。用于确保方法在没有事务的情况下运行，如果存在事务，则抛出异常。
Propagation.NESTED：如果没有事务，则新建一个事务；如果有事务，则在当前事务中嵌套其他事务。类似于REQUIRED，但嵌套事务可以进行局部回滚而不影响外部事务的提交。

3.3 锁

【MySql怎么解决并发一致性问题？】

通过锁机制来解决此问题，通过实现一个由两种类型的锁组成的机制来解决问题。一般为共享锁或排他锁，也叫读锁和写锁。

【MySQL有哪些锁？】

封锁粒度：通过让锁定对象更有选择性来提高共享资源并发性，选择锁粒度要在锁的开销和并发程度之间做出权衡。表锁和行锁满足读读共享、读写互斥、写写互斥。

行级锁：支持并发处理，开销较大，基于索引实现。事务T1和T2依次对数据行进行更新，T2不会覆盖T1的操作，因为会对行加锁。包括：共享行锁、独占行锁、记录锁、间隙锁、临键锁。

-- 开启事务并对数据行加锁
START TRANSACTION;
-- 这里对id为1的数据行进行了加锁，其他事务无法修改该行数据，直到该事务提交或回滚
-- 对读取的记录加独占锁(X型锁)
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 对读取的记录加共享锁(S型锁)
select ... lock in share mode;
-- 对操作的记录加独占锁(X型锁)
update table .... where id = 1;
-- 对操作的记录加独占锁(X型锁)
delete from table where id = 1;

表级锁：开销最小，直接锁定整张表，写锁会阻塞其他用户对表的所有读写操作，读锁互不影响。包括共享表锁、独占表锁、意向锁、AUTO-INC 锁。

-- 开启事务并对整张表加写锁
START TRANSACTION;
-- 这里整张表被锁定，其他事务无法对该表进行读写操作，直到该事务提交或回滚
-- 注意：LOCK TABLES命令会使得其他线程的查询操作堵塞，因此在生产环境中要慎用
LOCK TABLES products WRITE;
LOCK TABLES products READ;

全局锁：

-- 锁定所有表，以便进行备份或复制等操作
flush tables with read lock
-- 释放锁定
unlock tables

封锁类型：

读写锁：同Java一样，写锁持有时其他事务不能获取任何锁，读锁持有时其他事务可以获取读锁，但不能获取写锁。
- 互斥锁/排他锁：简称X锁，又叫写锁。
- 共享锁：简称S锁，又叫读锁。

意向锁：是一种用于表示事务意向锁定的机制，它是为了解决多粒度锁定时的并发控制问题而引入的。意向锁的目的是为了快速判断表里是否有记录被加锁。

是表级锁，不会和行级的共享锁和排他锁发生冲突，意向锁之间也不会冲突，只会和共享表锁、独占表锁发生冲突。
更容易的支持多粒度的封锁，假设没有意向锁，在同时存在行级锁和表级锁的情况下，事务想要对表加独占锁，要首先分别检查是否有其他事务加表锁或行锁，这个过程需要对每一行都进行一次检测，这相当耗时。有了意向锁后，在对记录加独占锁前，都会添加表级别的意向独占锁，而后面加独占表锁时就不用再一行行的检索了。意向锁的作用是快速判断表中是否有记录被加锁。
意向锁在 X/S 锁之上引入了 IX/IS，二者都是表锁，规定事务获取某行的X或S锁前要先获取IX或IS锁。这样当事务想对表加写锁时只需检测是否有其他事务对表加了 X/IX/S/IS 锁，只需做一次检测。（任意 IS/IX 锁之间都是兼容的，因为它们只表示想要对表加锁，而不是真正加锁）
分为两种类型：
- 意向共享锁（Intention Shared Lock，IS）：表示事务打算在表或行级别加共享锁。当一个事务在某个行上加了共享锁时，意向共享锁会在表级别自动加上，表示事务打算在整个表或部分表上加共享锁。
- 意向排他锁（Intention Exclusive Lock，IX）：表示事务打算在表或行级别加排他锁。当一个事务在某个行上加了排他锁时，意向排他锁会在表级别自动加上，表示事务打算在整个表或部分表上加排他锁。

```sql
select … lock in share mode;
select … for update


    * **插入意向锁**：名字里虽然有意向锁这三个字，但是它并不是意向锁，它属于行级锁，是一种特殊的间隙锁**，但不同于间隙锁的是，该锁只用于并发插入操作**。间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。**插入意向锁和间隙锁之间是冲突的**。每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，此时会生成一个插入意向锁，然后锁的状态设置为等待状态（*PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁*），现象就是 Insert 语句会被阻塞。

* 封锁模式：

  * **乐观锁**：认为当前场景下多个事务同时访问同一条数据时，发生冲突的概率低。因此在操作数据时可以不立即锁定，而是提交数据修改结果后再检查是否有其它事务也进行了修改，若没有就提交，若有就回滚事务。
    * 乐观锁在冲突较少的场景下可以获得更高的并发性能，但当冲突较多时，则会导致大量事务回滚。
    * MySQL没有实现乐观锁，但可以通过版本号或时间戳来自行实现。
    * 适合场景：数据并发修改操作不频繁；读多写少；互联网应用，经常读多写少，且不并发修改同一条数据。
  * **悲观锁**：认为数据在并发处理下很可能出现冲突，为了保证数据的完整和一致性，每次读写数据时都要加锁。
    * 适合场景：写操作较多。并发操作频繁。业务数据要求强一致性的系统。

* 算法分类，锁实现类型：可以通过查询data_locks的LOCK_MODE字段来确认锁类型，X为临键锁、X+REC_NOT_GAP为记录锁、X+GAP为间隙锁。

  * **记录锁（Record Lock）**：行级锁，锁的一条记录，记录锁有S和X之分。

  * **间隙锁（Gap Lock）**：只存在于可重复读隔离级别，目的是解决可重复隔离级别下的幻读。间隙锁锁的是一个区间，不仅仅是区间内的一条数据。**间隙锁之间可以共存**。

    为什么间隙锁与间隙锁之间是兼容的？

    * **间隙锁的意义只在于阻止区间被插入**，因此是可以共存的。**一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁**，共享和排他的间隙锁是没有区别的，他们相互不冲突，且功能相同，即两个事务可以同时持有包含共同间隙的间隙锁。
  
    缺点：

    1. 影响性能：阻止其它事务在已经锁定的范围内插入新的行，影响并发性能。
    2. 死锁风险：比如两个事务都想在同一间隙中插入新行，可能发生死锁。
    3. 锁定范围大：事务可能需要锁定的范围很小，但因为间隙锁的特性，导致范围更大的数据被锁定。
  
    ```sql
    -- 在区间(1,10)内的记录行都会被锁住，即id为2到9的数据插入会被阻塞，1和10不会
    select * from user where id between 1 and 10 for update

临键锁（Next-Key Lock）：InnoDB使用临键锁进行搜索和索引扫描，以防止幻读。是记录锁和间隙锁的结合，锁定记录本身和一个范围（左开右闭）。行级锁加锁的对象是索引，加锁的基本单位是 next-key lock。

例如一个索引包含以下值：10, 11, 13, and 20，那么就需要锁定以下区间：
1
2
3
4
5
(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)
MVCC 不能解决幻影读问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成记录锁或间隙锁：
- 唯一索引等值查询：
  - 索引上定位到一条记录后，Next-Key Lock会退化为记录锁（Record Lock）；
  - 记录不存在时，索引树上找到第一条大于的记录后，Next-Key Lock会退化为间隙锁（Gap Lock）。
  1
  select * from user where id = 1 for update
- 唯一索引范围查询：访问到不满足条件的第一个值为止。当唯一索引进行范围查询时，会对每一个扫描到的索引加 next-key 锁，然后如果遇到下面这些情况，会退化成记录锁或者间隙锁：
  - 针对「大于等于」的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中，那么该记录的索引中的 next-key 锁会退化成记录锁。
  - 针对「小于或者小于等于」的范围查询，要看条件值的记录是否存在于表中：
    - 当条件值的记录不在表中，那么不管是「小于」还是「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。
    - 当条件值的记录在表中，如果是「小于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁；如果「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引 next-key 锁不会退化成间隙锁。其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。
  1
  2
  3
  4
  -- Next-Key Lock + Record Lock
  select * from user where id >= 2 for update
  -- Next-Key Lock
  select * from user where id > 2 for update
- 非唯一索引等值查询：查询时存在两个索引，唯一索引和非唯一索引，加锁时会同时对两个索引加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁。针对非唯一索引等值查询时，查询的记录存不存在，加锁的规则也会不同：
  - 当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。
  - 当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。
- 非唯一索引范围查询：非唯一索引和主键索引的范围查询的加锁也有所不同，不同之处在于非唯一索引范围查询，索引的 next-key lock 不会有退化为间隙锁和记录锁的情况，也就是非唯一索引进行范围查询时，对二级索引记录加锁都是加 next-key 锁。
- 没有索引的查询：若锁定查询语句，查询没有走索引，导致扫描是全表，那么每一条记录的索引上都会加上Next-Key Lock，则相当于锁全表，此时其它事务的增删改操作都会被阻塞。所以使用update、delete、select…for update等加锁语句时，一定要避免全表扫描，否则会造成锁表的效果。

【两段锁协议】

两段锁协议（又称为 2PL，Two-Phase Locking Protocol）是一种数据库事务并发控制算法，它是通过在事务执行过程中强制所有访问数据的操作按照特定顺序进行加锁和解锁来保证事务的串行执行的。
通过限制事务锁的获取和释放顺序来保证事务的隔离性。两段锁协议有两个阶段：
1. 增长阶段（Growing Phase）：事务可以不断地请求和获取锁，但不能释放锁。
2. 收缩阶段（Shrinking Phase）：一旦事务释放了任何锁，之后就不能再请求新的锁。
两段锁协议分为两个阶段进行：
- 加锁阶段：在加锁阶段，事务开始执行时，会对所有要访问的数据加上共享锁或独占锁，以保证事务的完整性。
- 解锁阶段：在解锁阶段，事务完成后，会把所有加的锁都解开，以便其他事务能够访问这些数据。

在增长阶段，事务可以随时获取锁，但不能释放锁。

一旦进入收缩阶段，事务就只能释放锁，不能再请求任何新的锁。

如果事务遵循两段锁协议，它可以避免出现不可串行化的并发执行问题，如脏读、不可重复读、幻读等。

如果事务违反了两段锁协议，则可能会导致不一致的结果，比如幻读或死锁等问题。

它能够保证 串行化 的隔离级别，即事务之间的执行顺序与某种串行化的顺序一致，从而避免了并发执行中出现的数据冲突和不一致。

通过两段锁协议，可以确保事务的执行不会违反 ACID 特性中的 隔离性。

它可能会导致死锁，因为一个事务可能因为等待另一个事务释放锁而永远无法完成。

它会限制并发性，因为事务在锁定时会持有锁，直到执行完成，这样可能会造成资源的低效利用。

【三级封锁协议？】

MySQL 默认采用的是可重复读（REPEATABLE READ）的隔离级别，也就是使用三级封锁协议。在这个级别下，事务在读取数据时会对所涉及的数据加共享锁（读锁），在写入数据时会对所涉及的数据加排他锁（写锁）。当事务执行 COMMIT 时，释放相应的锁。这种机制确保了事务的一致性，但也可能导致性能问题，因为锁的竞争可能引起阻塞。

三级封锁协议（Three-Phase Locking, 3PL）

三级封锁协议是对两段锁协议的一个扩展，它不仅要求事务遵循两段锁协议，还引入了锁的协调机制。三级封锁协议的设计目的是为了在多用户并发情况下，进一步提高事务的并发性，同时避免死锁和保持串行化。

三级封锁协议在两段锁协议的基础上增加了一个“锁更新阶段（Lock Upgrade Phase）”，即在一个事务执行过程中，可以动态更新锁的类型，以便在必要时升级锁的粒度。

三级封锁协议：

一级：事务 T 要修改数据 A 时必须加写锁，直到 T 结束才释放锁。可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。
二级：在一级的基础上，要求读取数据 A 时必须加读锁，读取完马上释放读锁。可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。
三级：在二级的基础上，要求读取数据 A 时必须加读锁，直到事务结束了才能释放读锁。可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。

三级封锁协议包括：

一级封锁： 事务在获取锁后，直到事务结束才释放锁。这样可以解决脏读和不可重复读问题，但可能导致幻读。
二级封锁： 事务在获取锁后，只有在事务即将结束时才释放锁。这可以解决脏读和不可重复读问题，但依然可能导致幻读。
三级封锁： 事务在获取锁后，只有在事务提交时才释放锁。这可以解决脏读、不可重复读和幻读问题。

【死锁】

MySQL怎么发生死锁？

事务A插入订单1，事务B插入订单2，插入前都要检查订单是否存在。使用select … for update来检查订单是否存在就会造成相互等待。若使用普通select，可能发生幻读，若两个事务插入相同订单且没有唯一约束就会重复。
select … for update会生成一个表级的意向写锁和一个行级的间隙锁。插入意向锁与间隙锁是冲突的，所以当其它事务持有该间隙的间隙锁时，需要等待其它事务释放间隙锁之后，才能获取到插入意向锁。而间隙锁与间隙锁之间是兼容的，所以所以两个事务中 select ... for update 语句并不会相互影响。

如何避免死锁？

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。
在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：
- 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。
- 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

【MySQL线上给小表加字段导致库挂了，online DDL操作？修改表结构涉及到哪些锁？】

什么是 Online DDL 操作？

Online DDL（在线 DDL，在线数据定义语言）操作指的是在对 MySQL 数据库进行结构修改时，能够 不锁表或锁表时间非常短，从而 不影响查询和写入操作，以实现更高的可用性和更少的停机时间。MySQL 从 5.6 版本开始引入了对大部分 DDL 操作的在线支持。

常见的 Online DDL 操作 包括：

增加/删除列
修改列的数据类型
修改表的索引
重命名表
添加/删除索引

MySQL 修改表结构涉及的锁

修改表结构（即 DDL 操作）时，涉及到的锁通常是 元数据锁（MDL，Metadata Lock）和表锁。根据 MySQL 的版本、表的引擎（如 InnoDB）和操作的类型，具体锁的表现不同。

2.1 锁类型

MDL（Metadata Lock）：MySQL 在执行 DDL 操作时，会加上 Metadata Lock 来防止其他线程修改表的结构或执行其他 DDL 操作。MDL 锁是在修改表结构前后进行的保护性锁，确保其他线程无法改变该表的结构。它通常不会阻塞查询，但可能会影响到其他 DDL 操作。
- 当你执行 ALTER TABLE 操作时，MDL 锁会在开始时被加上，并在 DDL 操作完成后释放。
表锁（Table Lock）：虽然在线 DDL 操作可以避免表级锁，但某些情况下，尤其是 非在线 DDL 操作时，表锁仍然是必须的。具体来说：
- 写锁（Exclusive Lock）：修改表结构时通常需要写锁（例如 ALTER TABLE），这是为了确保在表的结构改变时不会有其他线程同时对其进行读写操作。
- 读锁（Shared Lock）：对于查询操作，通常会加读锁，允许多个线程同时读取数据，但会阻塞修改数据的操作。

2.2 在线 DDL 锁的优化

在 InnoDB 引擎中，MySQL 提供了支持 在线 DDL 操作的优化，它尽量减少对表的锁定时间，让表在执行过程中尽量处于可用状态。具体来说：

添加列：通常是在线操作，不会锁表，MySQL 会在后台逐步完成列的添加。
修改列数据类型、删除列、重命名表等操作：这些操作通常会需要更多的时间，但在 InnoDB 引擎中，MySQL 会尽量避免长时间锁表。

在执行 在线 DDL 操作 时，MySQL 会尽量采用以下两种方式来最小化锁的影响：

加锁时间减少：在执行 DDL 操作时，MySQL 会首先加锁并执行元数据更新，然后再执行实际的数据结构变更，减少锁的持续时间。
在线后台工作：许多变更会在后台完成（例如数据重排、重新构建索引等），因此大多数时间表是可用的，只有少数关键操作会阻塞。

典型的 DDL 操作锁机制

以下是不同类型的 DDL 操作所涉及的锁机制和其对表的影响：

3.1 增加/删除列

增加列：在执行 ALTER TABLE ADD COLUMN 时，MySQL 会加一个 MDL 锁来阻止其他 DDL 操作，同时尽量减少表的加锁时间，通常该操作是 在线的，不会长时间锁定表。
删除列：删除列通常涉及到全表扫描，需要一个 排他锁（Exclusive Lock），这会暂时阻塞其他查询操作。

3.2 修改列数据类型

修改数据类型：如 ALTER TABLE MODIFY COLUMN可能会涉及到在线和离线的混合操作（取决于具体操作和表的数据量）。对于小表，修改数据类型时，可能会暂时加上排他锁。
- 对于在线 DDL 操作，它会通过 元数据锁（MDL） 来保护修改操作，并且通常能保持表的可读性和可写性，减少表的加锁时间。

3.3 添加/删除索引

添加索引：如果使用在线 DDL 操作（即 ALTER TABLE ADD INDEX），MySQL 会尽量避免长时间锁住表。它会在后台进行索引的构建，尽量避免锁定整个表。
- 但是，在某些情况下，如果表中已有的索引冲突或是表非常大，可能还是会短时间加上 排他锁。
删除索引：删除索引通常会比创建索引轻量，不会产生过多的锁定时间。

3.4 表重命名

表重命名：重命名表一般会使用 元数据锁（MDL），不会涉及到 长时间的表锁，该操作是在线的，通常不会影响查询操作。

如何避免在线 DDL 操作造成的挂掉问题？

如果因为某些原因（例如表的大小、锁争用等）执行 ALTER TABLE 操作导致数据库挂掉，可以采取以下策略：

分批执行：对于大表，尽量避免一次性执行大规模的 DDL 操作。如果可能，将操作分解成多个小操作，避免对整个表的影响。
使用更小的操作窗口：选择在低流量时段进行 DDL 操作，或者选择短时间内不会影响业务的操作方式。
选择更高效的索引结构：如果涉及到索引变更，确保所使用的索引结构适合查询需求，避免过多的索引变更操作。
增加资源：确保数据库服务器有足够的资源（如 CPU、内存和磁盘 IO），因为大的 DDL 操作会消耗大量资源，导致数据库响应变慢甚至挂掉。
备份和恢复：在执行重大的 DDL 操作之前，确保有数据库备份，遇到问题时能迅速恢复。

总结

Online DDL 是 MySQL 为了减少修改表结构操作对业务的影响而引入的优化，能够在修改表结构时不锁住表，从而减少业务中断的时间。
修改表结构时，涉及的主要锁是 Metadata Lock (MDL) 和表锁。在线 DDL 操作通常不会长时间锁住表，但某些操作（如删除列、修改列类型）可能仍然需要较长时间的表锁。
如果出现 在线 DDL 操作 导致数据库挂掉的情况，通常需要检查操作的粒度和表的大小，避免大规模的结构修改操作，可以选择分批执行或者在低峰期执行。

3.4 多版本并发控制

【什么是MVCC？MVCC机制了解不？】

什么是MVCC？

背景：MySQL引入锁机制处理并发一致性问题，又因为数据库场景中读远远多于写，所以引入读写锁提高并发能力。MVCC则利用多版本思想，对写操作进行了又一层优化，即写操作更新最新的版本快照，而读操作读旧版本快照，二者无互斥关系。
多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现并发控制以及事务隔离级别的一种具体方式。它通过保存数据的多个版本来实现，从而使读操作不必等待写操作完成，从而提高了数据库的并发度。
核心思想：为每个事务创建一个可见性视图，该视图用于确定事务可以看到的数据版本。当事务执行读操作时，它只会看到在其启动之前已经提交的数据版本，而不会看到正在进行中或尚未提交的其他事务的修改。这样可以保证事务之间不会相互干扰，从而避免了读-写和写-写之间的冲突。
工作原理：
- 当一个事务对数据进行修改时，数据库会创建一个新的数据版本，并将旧版本保存起来。
- 当一个事务读取数据时，数据库会根据事务的隔离级别，选择一个合适的数据版本进行读取。
实现方式：
- 基于时间戳：每个数据版本都有一个时间戳，数据库根据时间戳来判断哪个版本的数据是可见的。
- 基于乐观锁：每个数据版本都有一个版本号，数据库在读取数据时会检查版本号，如果版本号发生变化，则说明数据已经被修改，需要重新读取。

【MVCC与隔离级别的关系？】

读未提交：MVCC 可以通过读取最新的数据版本来实现，而无需加锁。
读已提交：MVCC 可以通过读取已提交事务的数据版本来实现，从而保证数据的一致性。
可重复读：启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。
可串行化：MVCC 可以与加锁机制结合使用来实现，需要对所有读取的行都加锁，从而保证所有事务按照串行化的顺序执行。

【MVCC机制有什么问题？MVCC与幻读？】

不能完整的解决所有隔离级别：不能解决幻读，无法单独实现可串行化的隔离级别。
增加空间开销：MVCC 需要维护多个数据版本，会增加数据库的空间开销。
增加复杂性：MVCC 的实现机制比较复杂，会增加数据库的维护复杂性。

MVCC与幻读？MVCC通过和临键锁组合来解决幻读问题。

针对普通select语句，在可重复读隔离级别下，事务执行过程中看到的数据版本是一致的，避免了幻读情况。
针对select…for update等语句，通过next-key lock来解决幻读，当其它事务在范围内插入记录，会被阻塞从而避免了幻读情况
仍会发生幻读的场景：可重复读隔离级别下虽然很大程度上避免了幻读，但是还是没有能完全解决幻读。
- 事务A查询id=1，未命中。事务B插入id=1。事务A直接更新id=1的字段值，可以更新成功。
- 事务A查询id>1得到3条记录。事务B插入id=2的记录。A再次执行select for update id > 1 得到4条记录。

【MVCC的实现原理和流程？】

MySQL InnoDB 存储引擎使用了一种基于乐观锁的 MVCC 实现方式。其实现主要依靠以下几个机制：

undo log：用于记录数据的更新历史，以便可以回滚到之前的版本。
InnoDB的数据行结构：每行数据都会包含三个隐藏字段
- **trx_id**：表示最近修改该行数据的事务ID，记录创建该行的版本号。当一个事务对数据进行更新时，会生成一个新的版本，并将其与事务 ID 关联起来。
- **roll_pointer**：指向该行之前版本的数据行。每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中。该隐藏字段是一个指向旧版本的指针，该行上所有旧的版本，在undo中都通过链表的形式组织。
- db_row_id：行ID，用于唯一标识一行数据，当表未创建主键或唯一索引时自动生成。
- PK
- other columns
Read View：用于定义事务可见的数据版本，每个事务开始时都会创建。其包含三个主要成员分别指向事务链表的三个部分：
- m_ids：在创建 Read View 时，当前数据库中启动还未提交的事务 id 列表
- min_trx_id：表示该SQL启动时，当前事务链表中最小的事务id编号，也就是当前系统中创建最早但还未提交的事务；
- max_trx_id：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1
- creator_trx_id：创建该view的事务id
- 所以trx_id可以根据大小排布：已提交事务 < min_trx_id <= m_ids(已启动但未提交的事务) <= max_trx_id < 还未启动的事务
事务链表：事务在开始到提交的过程会保存在一个叫trx_sys的事务链表中，提交后从链表移除。在并发情况下，事务链表中保存着还未提交的事务，这些事务的改变哪些对当前事务是可见的由ReadView来判定：
- 所有数据行TRX_ID小于min_trx_id都是已提交完的事务，都对当前事务可见。
- 所有数据行TRX_ID大于max_trx_id说明修改此行数据的事务在当前事务之后，所以对当前事务不可见。
- 位于二者中间的事务是否可见由事务隔离级别决定。读已提交都是可见的，可重复读则都不可见。可重复读整个事务期间都使用一个read view，读已提交则每次读取数据时都会生成一个新的read view。
- ReadView于每个SQL绑定，而非事务。在数据行快照不可使用的情况下，需要沿着 Undo Log 的回滚指针 ROLL_PTR 找到下一个快照，再进行上面的判断。

流程：

读操作:
- 事务开始时，会生成一个 Read View。
- 当事务读取数据时，会根据 Read View 中可见的事务 ID 集合，选择合适的数据版本进行读取。具体来说，会选择满足以下条件的数据版本：
  - 版本号大于等于 read view 中的最低可见事务 ID
  - 版本号小于等于 read view 中的最大可见事务 ID
- 如果要读取的数据已经被其他事务更新，则会从 undo log 中找到该数据的旧版本进行读取。
写操作:
- 事务开始时，会分配一个新的事务 ID。
- 当事务对数据进行修改时，会先创建一个新的数据版本，并将旧版本保存到 undo log 中。
- 新版本的数据中会包含新的事务 ID 和新的 db_row_id。
- 事务提交成功后，会将其事务 ID 添加到 Read View 中，使其对其他事务可见。
事务提交：当事务提交时，会将事务 ID 标记为已提交。
事务回滚：当事务回滚时，会使用 undo log 将数据恢复到之前的版本。

【MVCC 的SELECT操作会加锁吗？】

InnoDB 使用的是乐观锁，所以在读取数据时不会加锁。SELECT 操作是快照中的数据，不需要进行加锁操作。
MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。

3.5 日志

区别	Undo log	Redo log
作用	用于事务回滚	用于数据库恢复
记录内容	事务执行过程中所做的所有操作的相反操作	事务执行过程中所做的所有变更
应用时机	事务提交时清理	事务开始时写入
存储位置	内存和磁盘	磁盘
影响	影响事务的原子性和并发性	影响事务的一致性和持久性

undolog和redolog是存储引擎层日志。

binlog则是服务层日志。主要用于数据备份和主从复制

【Undo Log】

Undo Log记录了事务执行过程中所做的所有操作的相反操作。用于在事务发生错误或需要回滚时，将数据库恢复到事务开始前的状态。

每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里，比如：

在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。
在发生回滚时，就读取 undo log 里的数据，然后做原先相反操作。比如当 delete 一条记录时，undo log 中会把记录中的内容都记下来，然后执行回滚操作的时候，就读取 undo log 里的数据，然后进行 insert 操作。

trx_id可以知道该记录是被哪个事务修改，通过roll_pointer指针可以将undo log串成版本链表。

	id	name	balance	trx_id	roll_pointer
B+树记录	1	zhangsan	200	11	指向下行
Undo日志	1	zhangsan	100	10	~

工作原理：

在事务开始之前，InnoDB 会将数据页的旧版本记录到 undo 日志中。
在事务执行过程中，InnoDB 会对数据页进行更新，并同时将更新操作记录到 undo 日志中。
如果事务成功提交，undo 日志中的记录会被清理。
如果事务发生错误或需要回滚，InnoDB 会使用 undo 日志中的记录来逆向执行所有操作，将数据库恢复到事务开始前的状态。

假设我们有一个表 t，其中有一条记录 (1, 'a')。现在我们执行一个事务，将该记录更新为 (1, 'b')：

事务开始时，InnoDB 会将数据页的旧版本记录到 Undo log 中，即 (1, 'a')。
事务执行过程中，InnoDB 会对数据页进行更新，并将更新操作记录到 Undo log 中，即 (1, 'b')。
如果事务成功提交，Undo log 中的记录会被清理。
如果事务发生错误或需要回滚，InnoDB 会使用 Undo log 中的记录来逆向执行所有操作，将数据库恢复到事务开始前的状态，即 (1, 'a')。

undo log 还有一个作用，通过 ReadView + undo log 实现 MVCC（多版本并发控制）。对于「读提交」和「可重复读」隔离级别的事务来说，它们的快照读（普通 select 语句）是通过 Read View + undo log 来实现的，它们的区别在于创建 Read View 的时机不同：

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

总结：为什么需要Undo log？Undo log的作用是？

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

undo log 是如何刷盘（持久化到磁盘）的？

undo log 和数据页的刷盘策略是一样的，都需要通过 redo log 保证持久化。
buffer pool 中有 undo 页，对 undo 页的修改也都会记录到 redo log。
redo log 会每秒刷盘，提交事务时也会刷盘，数据页和 undo 页都是靠这个机制保证持久化的。
innodb_flush_log_at_trx_commit参数控制事务提交时日志刷盘的频率：
- 值为1：每次事务提交都会将日志写入磁盘，确保最强的一致性保证（最强的持久性）。
- 值为2：每次事务提交时将日志刷新到操作系统缓存，而不是直接写入磁盘。只有每秒才会将操作系统缓存的数据写入磁盘。性能较好，但崩溃恢复时，可能会丢失1秒钟的日志。
- 值为0：表示批量写，每秒钟刷新一次日志，通常用于性能要求非常高的场景。

【Buffer Pool？缓存什么？】

MySQL 的数据都是存在磁盘中的，更新一条记录时，先要从磁盘读取该记录，然后在内存中修改这条记录。修改完缓存起来，下次有查询语句命中了这条记录，直接读取缓存中的记录，不需要从磁盘获取数据。Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

有了 Buffer Pool 后：

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

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

MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。所以，MySQL 刚启动的时候，你会观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，申请物理内存，接着将虚拟地址和物理地址建立映射关系。

Buffer Pool缓存什么？

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

Undo 页是记录什么？

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

查询一条记录，就只需要缓冲一条记录吗？

不是。当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，将页加载到 Buffer Pool 后，再通过页里的「页目录」去定位到某条具体的记录。

Buffer Pool的问题？如何解决？

Buffer Pool 是基于内存的，而内存总是不可靠，万一断电重启，还没来得及落盘的脏页数据就会丢失。当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。后续，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）预写日志技术。WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

【Redo Log】

什么是 redo log？

redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。
在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。
当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

redo：顺序追加到磁盘，缓冲池不会立马刷新到磁盘。写redo log不是一次性写完的，而是分为两个阶段。

prepare准备阶段：首先会将事务的变更记录写入 redo log 缓存。设置为prepare。
commit提交阶段：当 redo log 缓存中的变更达到一定量时，或者事务提交超时时，事务操作持久化到磁盘，redo设置为commit。

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

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

redo log 和 undo log 区别在哪？

redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；
事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务。
所以有了 redo log，再通过 WAL 技术，InnoDB 就可以保证即使数据库发生异常重启，之前已提交的记录都不会丢失，这个能力称为 crash-safe（崩溃恢复）。可以看出来， redo log 保证了事务四大特性中的持久性。

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

写入 redo log 的方式使用了追加操作，所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。磁盘的「顺序写」比「随机写」高效的多，因此 redo log 写入磁盘的开销更小。
可以说这是 WAL 技术的另外一个优点：MySQL 的写操作从磁盘的「随机写」变成了「顺序写」，提升语句的执行性能。这是因为 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上。

总结：为什么需要 redo log ？

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

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

不是。实际上，执行一个事务的过程中，产生的 redo log 也不是直接写入磁盘的，因为这样会产生大量的 I/O 操作，而且磁盘的运行速度远慢于内存。所以，redo log 也有自己的缓存—— redo log buffer，每当产生一条 redo log 时，会先写入到 redo log buffer，后续在持久化到磁盘。
redo log buffer 默认大小 16 MB，可以通过 innodb_log_Buffer_size 参数动态的调整大小，增大它的大小可以让 MySQL 处理「大事务」是不必写入磁盘，进而提升写 IO 性能。

Redo Log什么时候刷盘？主要有下面几个时机：

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

事务提交时：
- 当一个事务提交时，InnoDB会确保这个事务对应的Redo Log已经写入磁盘。
- 这是Redo Log刷盘的最关键时机，因为在事务提交前，所有的修改必须被记录在Redo Log中，确保事务的持久性。
- 事务提交时，Redo Log的刷盘顺序是：
  1. 将日志写入内存中的Redo Log Buffer。
  2. 将Redo Log Buffer中的日志刷新到磁盘上的Redo Log文件。
  3. 在提交事务之前，InnoDB会确保日志文件中的相关日志已被刷新，然后才向客户端返回事务提交成功的响应。
InnoDB的检查点机制（Checkpoint）：
- 检查点是数据库为了控制Redo Log的大小和减少恢复时间而定期触发的机制。检查点时，InnoDB会将所有未刷盘的Redo Log记录写入磁盘。
- 在检查点发生时，Redo Log中的记录会被刷新到磁盘上，确保数据文件中的修改和Redo Log文件的内容是一致的。
- 检查点一般是由系统的**innodb_flush_log_at_trx_commit**配置决定的。
- 在高事务量的系统中，检查点有助于防止Redo Log过大，避免回滚和恢复操作时日志太长。
日志刷新策略（innodb_flush_log_at_trx_commit）：

innodb_flush_log_at_trx_commit是控制Redo Log刷新策略的一个重要参数，它有三种设置方式：
- 1（默认值）：每次事务提交时都会刷新Redo Log到磁盘。这是最安全的方式，保证事务的持久性，但是性能较低。
- 2：每次事务提交时会将Redo Log写入操作系统缓存，但不立刻写入磁盘。系统会定期（通常每秒）将缓存中的日志写入磁盘。这种方式在性能和安全性之间提供了一个平衡。
- 0：Redo Log不会在每次事务提交时立即刷新，而是依赖操作系统的缓存机制，在MySQL关闭时才刷新到磁盘。这种方式会牺牲一定的持久性，但性能最好。
事务日志文件空间不足时（Log Buffer Flush）：
- 当Redo Log的缓冲区（Log Buffer）填满时，为了释放空间，InnoDB会强制将Log Buffer中的内容刷新到磁盘上的Redo Log文件。
- 这通常是为了避免Log Buffer溢出，同时确保新的日志能够继续写入。
数据库停止时（MySQL进程关闭或崩溃恢复）：
- 当数据库正常关闭时，所有未刷盘的Redo Log都会被刷新到磁盘。
- 如果数据库发生崩溃（如MySQL崩溃或服务器断电），在恢复时，Redo Log中的记录会用于恢复崩溃前的所有提交事务。

innodb_flush_log_at_trx_commit 参数控制的是什么？

单独执行一个更新语句的时候，InnoDB 引擎会自己启动一个事务，在执行更新语句的过程中，生成的 redo log 先写入到 redo log buffer 中，然后等事务提交的时候，再将缓存在 redo log buffer 中的 redo log 按组的方式「顺序写」到磁盘。上面这种 redo log 刷盘时机是在事务提交的时候，这个默认的行为。
除此之外，InnoDB 还提供了另外两种策略，由参数 innodb_flush_log_at_trx_commit 参数控制，可取的值有：0、1、2，默认值为 1，这三个值分别代表的策略如下：
- 当设置该参数为 0 时，表示每次事务提交时，还是将 redo log 留在 redo log buffer 中 ，该模式下在事务提交时不会主动触发写入磁盘的操作。
- 当设置该参数为 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不会丢失。
- 当设置该参数为 2 时，表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache（如果你想了解 Page Cache，可以看这篇 (opens new window)），Page Cache 是专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存。

innodb_flush_log_at_trx_commit 为 0 和 2 的时候，什么时候才将 redo log 写入磁盘？

InnoDB 的后台线程每隔 1 秒：
- 针对参数 0 ：会把缓存在 redo log buffer 中的 redo log ，通过调用 write() 写到操作系统的 Page Cache，然后调用 fsync() 持久化到磁盘。所以参数为 0 的策略，MySQL 进程的崩溃会导致上一秒钟所有事务数据的丢失;
- 针对参数 2 ：调用 fsync，将缓存在操作系统中 Page Cache 里的 redo log 持久化到磁盘。所以参数为 2 的策略，较取值为 0 情况下更安全，因为 MySQL 进程的崩溃并不会丢失数据，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

这三个参数的应用场景是什么？

这三个参数的数据安全性和写入性能的比较如下：
- 数据安全性：参数 1 > 参数 2 > 参数 0
- 写入性能：参数 0 > 参数 2> 参数 1
所以，数据安全性和写入性能是熊掌不可得兼的，要不追求数据安全性，牺牲性能；要不追求性能，牺牲数据安全性。
- 在一些对数据安全性要求比较高的场景中，显然 innodb_flush_log_at_trx_commit 参数需要设置为 1。
- 在一些可以容忍数据库崩溃时丢失 1s 数据的场景中，我们可以将该值设置为 0，这样可以明显地减少日志同步到磁盘的 I/O 操作。
- 安全性和性能折中的方案就是参数 2，虽然参数 2 没有参数 0 的性能高，但是数据安全性方面比参数 0 强，因为参数 2 只要操作系统不宕机，即使数据库崩溃了，也不会丢失数据，同时性能方便比参数 1 高。

Redo Log文件写满了怎么办？

默认情况下， InnoDB 存储引擎有 1 个重做日志文件组( redo log Group），「重做日志文件组」由有 2 个 redo log 文件组成，这两个 redo 日志的文件名叫：ib_logfile0 和 ib_logfile1 。
在重做日志组中，每个 redo log File 的大小是固定且一致的，假设每个 redo log File 设置的上限是 1 GB，那么总共就可以记录 2GB 的操作。
重做日志文件组是以循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。
所以 InnoDB 存储引擎会先写 ib_logfile0 文件，当 ib_logfile0 文件被写满的时候，会切换至 ib_logfile1 文件，当 ib_logfile1 文件也被写满时，会切换回 ib_logfile0 文件。
我们知道 redo log 是为了防止 Buffer Pool 中的脏页丢失而设计的，那么如果随着系统运行，Buffer Pool 的脏页刷新到了磁盘中，那么 redo log 对应的记录也就没用了，这时候我们擦除这些旧记录，以腾出空间记录新的更新操作。

redo log 是循环写的方式，相当于一个环形，InnoDB 用 write pos 表示 redo log 当前记录写到的位置，用 checkpoint 表示当前要擦除的位置。
如果 write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此所以针对并发量大的系统，适当设置 redo log 的文件大小非常重要），此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动（图中顺时针），然后 MySQL 恢复正常运行，继续执行新的更新操作。

所以，一次 checkpoint 的过程就是脏页刷新到磁盘中变成干净页，然后标记 redo log 哪些记录可以被覆盖的过程。

【BinLog】

undo log 和 redo log 这两个日志都是 Innodb 存储引擎生成的。MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写入 binlog 文件。

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

BinLog用于记录数据库的所有变更，包括 DDL 和 DML 操作。Binlog 是 MySQL 主从复制和数据恢复的基础。

在主从复制中，主库会将 Binlog 发送给从库，从库根据 Binlog 来更新自己的数据，从而实现主从数据的一致性。
如果数据库发生故障，可以使用 Binlog 将数据库恢复到故障发生之前的状态。
缓存一致性：canal组件监听binlog

为什么有了 binlog，还要有 redo log？

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

BinLog和Redo Log的提交时机：

写入redo log，prepare
写入binlog
提交redo log，commit

若崩溃发生在1和2之间，binlog没写，redo也没交，崩溃恢复后事务回滚，数据没有问题。若崩溃发生在2和3之间，redo log中的事务只有完整的prepare，此时检查binlog，若也存在完整事务则提交，否则回滚。

Redo Log与BinLog的区别？

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

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

不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。
因为 redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。
binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据。

binlog什么时候刷盘？

事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中。
一个事务的 binlog 是不能被拆开的，因此无论这个事务有多大（比如有很多条语句），也要保证一次性写入。这是因为有一个线程只能同时有一个事务在执行的设定，所以每当执行一个 begin/start transaction 的时候，就会默认提交上一个事务，这样如果一个事务的 binlog 被拆开的时候，在备库执行就会被当做多个事务分段自行，这样破坏了原子性，是有问题的。
MySQL 给每个线程分配了一片内存用于缓冲 binlog ，该内存叫 binlog cache，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

什么时候 binlog cache 会写到 binlog 文件？

在事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 文件中，并清空 binlog cache。如下图：
虽然每个线程有自己 binlog cache，但是最终都写到同一个 binlog 文件：
- 图中的 write，指的就是指把日志写入到 binlog 文件，但是并没有把数据持久化到磁盘，因为数据还缓存在文件系统的 page cache 里，write 的写入速度还是比较快的，因为不涉及磁盘 I/O。
- 图中的 fsync，才是将数据持久化到磁盘的操作，这里就会涉及磁盘 I/O，所以频繁的 fsync 会导致磁盘的 I/O 升高。
MySQL提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率：
- sync_binlog = 0 的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；
- sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；
- sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。
在MySQL中系统默认的设置是 sync_binlog = 0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦主机发生异常重启，还没持久化到磁盘的数据就会丢失。

而当 sync_binlog 设置为 1 的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使主机发生异常重启，最多丢失一个事务的 binlog，而已经持久化到磁盘的数据就不会有影响，不过就是对写入性能影响太大。

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

【一个Update语句的执行流程】

当优化器分析出成本最小的执行计划后，执行器就按照执行计划开始进行更新操作。

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

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

【两阶段提交？】

为什么需要两阶段提交？

事务提交后，redo log 和 binlog 都要持久化到磁盘，但是这两个是独立的逻辑，可能出现半成功的状态，这样就造成两份日志之间的逻辑不一致。

举个例子，假设 id = 1 这行数据的字段 name 的值原本是 ‘jay’，然后执行 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 如果在持久化 redo log 和 binlog 两个日志的过程中，出现了半成功状态，那么就有两种情况：

如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入。MySQL 重启后，通过 redo log 能将 Buffer Pool 中 id = 1 这行数据的 name 字段恢复到新值 xiaolin，但是 binlog 里面没有记录这条更新语句，在主从架构中，binlog 会被复制到从库，由于 binlog 丢失了这条更新语句，从库的这一行 name 字段是旧值 jay，与主库的值不一致性；
如果在将 binlog 刷入到磁盘之后， MySQL 突然宕机了，而 redo log 还没有来得及写入。由于 redo log 还没写，崩溃恢复以后这个事务无效，所以 id = 1 这行数据的 name 字段还是旧值 jay，而 binlog 里面记录了这条更新语句，在主从架构中，binlog 会被复制到从库，从库执行了这条更新语句，那么这一行 name 字段是新值 xiaolin，与主库的值不一致性；

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

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

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

举个拳击比赛的例子，两位拳击手（参与者）开始比赛之前，裁判（协调者）会在中间确认两位拳击手的状态，类似于问你准备好了吗？

准备阶段：裁判（协调者）会依次询问两位拳击手（参与者）是否准备好了，然后拳击手听到后做出应答，如果觉得自己准备好了，就会跟裁判说准备好了；如果没有自己还没有准备好（比如拳套还没有带好），就会跟裁判说还没准备好。
提交阶段：如果两位拳击手（参与者）都回答准备好了，裁判（协调者）宣布比赛正式开始，两位拳击手就可以直接开打；如果任何一位拳击手（参与者）回答没有准备好，裁判（协调者）会宣布比赛暂停，对应事务中的回滚操作。

两阶段提交的过程是怎样的两阶段提交的过程是怎样的？

在 MySQL 的 InnoDB 存储引擎中，开启 binlog 的情况下，MySQL 会同时维护 binlog 日志与 InnoDB 的 redo log，为了保证这两个日志的一致性，MySQL 使用了内部 XA 事务（是的，也有外部 XA 事务，跟本文不太相关，我就不介绍了），内部 XA 事务由 binlog 作为协调者，存储引擎是参与者。

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

两阶段提交

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

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

异常重启会出现什么现象？

我们来看看在两阶段提交的不同时刻，MySQL 异常重启会出现什么现象？下图中有时刻 A 和时刻 B 都有可能发生崩溃：

时刻 A 与时刻 B

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

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

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

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

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

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

binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。

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

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

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

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

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

两阶段提交有什么问题？

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

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

为什么两阶段提交的磁盘 I/O 次数会很高？

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

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

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

为什么锁竞争激烈？

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

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

组提交？

MySQL 引入了 binlog 组提交（group commit）机制，当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 I/O 的次数，如果说 10 个事务依次排队刷盘的时间成本是 10，那么将这 10 个事务一次性一起刷盘的时间成本则近似于 1。

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

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

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

每个阶段都有一个队列

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

有 binlog 组提交，那有 redo log 组提交吗？

这个要看 MySQL 版本，MySQL 5.6 没有 redo log 组提交，MySQL 5.7 有 redo log 组提交。

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

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

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

接下来介绍每个阶段的过程，注意下面的过程针对的是“双 1” 配置（sync_binlog 和 innodb_flush_log_at_trx_commit 都配置为 1）。

flush 阶段

第一个事务会成为 flush 阶段的 Leader，此时后面到来的事务都是 Follower ：

接着，获取队列中的事务组，由绿色事务组的 Leader 对 redo log 做一次 write + fsync，即一次将同组事务的 redolog 刷盘：

完成了 prepare 阶段后，将绿色这一组事务执行过程中产生的 binlog 写入 binlog 文件（调用 write，不会调用 fsync，所以不会刷盘，binlog 缓存在操作系统的文件系统中）。

从上面这个过程，可以知道 flush 阶段队列的作用是用于支撑 redo log 的组提交。

如果在这一步完成后数据库崩溃，由于 binlog 中没有该组事务的记录，所以 MySQL 会在重启后回滚该组事务。
sync 阶段

绿色这一组事务的 binlog 写入到 binlog 文件后，并不会马上执行刷盘的操作，而是会等待一段时间，这个等待的时长由 Binlog_group_commit_sync_delay 参数控制，目的是为了组合更多事务的 binlog，然后再一起刷盘，如下过程：

不过，在等待的过程中，如果事务的数量提前达到了 Binlog_group_commit_sync_no_delay_count 参数设置的值，就不用继续等待了，就马上将 binlog 刷盘，如下图：

从上面的过程，可以知道 sync 阶段队列的作用是用于支持 binlog 的组提交。

如果想提升 binlog 组提交的效果，可以通过设置下面这两个参数来实现：
- binlog_group_commit_sync_delay= N，表示在等待 N 微妙后，直接调用 fsync，将处于文件系统中 page cache 中的 binlog 刷盘，也就是将「 binlog 文件」持久化到磁盘。
- binlog_group_commit_sync_no_delay_count = N，表示如果队列中的事务数达到 N 个，就忽视binlog_group_commit_sync_delay 的设置，直接调用 fsync，将处于文件系统中 page cache 中的 binlog 刷盘。
如果在这一步完成后数据库崩溃，由于 binlog 中已经有了事务记录，MySQL会在重启后通过 redo log 刷盘的数据继续进行事务的提交。
commit 阶段

最后进入 commit 阶段，调用引擎的提交事务接口，将 redo log 状态设置为 commit。

commit 阶段队列的作用是承接 sync 阶段的事务，完成最后的引擎提交，使得 sync 可以尽早的处理下一组事务，最大化组提交的效率。

【MySQL磁盘I/O很高，有什么优化办法？】

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

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

3.6 引擎

【MySQL常用的存储引擎？特点与区别?还有应用场景】

最常用的是InnoDB。

Myiasm：
- 设计简单，数据以紧密格式存储。
- 提供了大量的特性，包括压缩表、空间数据索引等。
- 使用的是非聚集索引。
- 不支持数据库事务。
- 不支持行级锁，只能锁表。
- 适用于对性能要求较高、对数据一致性要求不高的场景，如 Web 服务器
InnoDB：默认的存储引擎。
- 支持事务，实现四种隔离级别：默认可重复读，在此级别通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。
- 底层为B+树实现，适合处理多重并发更新操作，普通select都是快照读，快照读不加锁。
- 使用的是聚集索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。
- 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。
- 支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。
- 适用于对数据一致性和完整性要求较高的场景，如 OLTP 系统、订单系统、银行系统等
Memory
- 将数据存储在内存中
- 提供非常高的性能，但数据易丢失
- 适用于对性能要求极高、数据更新频率较低的场景，如缓存
NDB Cluster
- 用于高可用性和高性能的分布式存储引擎
- 适用于需要高可用性和高性能的场景，如金融、电信、互联网应用等

特性	InnoDB	MyISAM	Memory	NDB Cluster
事务	支持	不支持	不支持	支持
锁	行级锁	表级锁	无锁	分布式锁
外键	支持	不支持	不支持	支持
性能	中等	高	高	高
空间	大	小	小	大
一致性	强	弱	弱	强
可用性	高	高	低	高

【InnoDB 的一些特性？】

1. 事务处理：保证数据的完整性和一致性。

2. 行级锁：提高数据库的并发性能。

3. 外键约束：维护数据之间的关系。

4. 崩溃恢复：保证数据在发生崩溃时不会丢失。

5. 其他特性

**多版本并发控制 (MVCC)**：MVCC 是一种用于提高数据库并发性能的技术。它通过保存数据的多个版本来实现，从而使读操作不必等待写操作完成，从而提高了数据库的并发度。
自适应哈希索引：自适应哈希索引是一种可以自动调整的索引结构。它可以根据表的访问模式，自动调整索引的结构，从而提高查询性能。
插入缓冲：插入缓冲是一种用于提高插入性能的技术。它将插入操作先缓存起来，然后再批量写入数据库，从而减少了 I/O 操作，提高了插入性能。
二次写：二次写是一种用于提高数据安全性的技术。它将数据先写入到内存缓冲区，然后再写入到磁盘，从而提高了数据的安全性。

【InnoDB 表对主键的选择顺序？主键生成策略？】

主键的选择顺序？

优先使用用户自定义主键作为主键，
如果用户没有定义主键，则选取一个Unique键作为主键，
如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id隐藏列作为主键。

主键的生成策略：

自增主键（Auto Increment）：最常见的主键生成策略之一。在InnoDB表中，可以使用AUTO_INCREMENT属性来指定一个整数列作为自增主键。每次插入新记录时，系统会自动为该列生成一个唯一的递增值。
UUID主键（Universally Unique Identifier）：UUID是一种全局唯一的标识符，通常以字符串的形式存储。可以使用UUID作为主键，确保主键的全局唯一性。但是UUID相比自增主键会占用更多的存储空间，且不易读写。
复合主键（Composite Key）：复合主键由多个列组合而成，可以确保组合后的值唯一。在某些情况下，使用复合主键可以更好地反映业务逻辑，但也增加了查询的复杂度。
自定义主键生成策略：除了上述常见的策略外，还可以根据具体的业务需求和场景制定自定义的主键生成策略。例如，可以使用哈希算法生成主键，或者根据业务规则生成特定格式的主键。

【AUTO_INCREMENT原理（考察并发情况）？】

AUTO_INCREMENT原理（考察并发情况）？

在 InnoDB 表中，可以将某个列定义为自增主键，该列的类型必须是整数类型。
当插入一条新数据时，数据库会自动为自增主键列生成一个新的值。新的值是根据当前的最大值加 1 来生成的。最大值保存在内存中，并在每次插入操作后进行更新。
在并发情况下，AUTO_INCREMENT 机制需要保证主键值的唯一性。
- 主键值冲突：并发情况下，如果多个事务同时尝试插入数据，则可能会出现主键值冲突的情况。
  - 行锁：InnoDB 使用行锁来控制对行的访问。在插入操作期间，数据库会对自增主键列所在的行的行锁进行加锁，从而防止其他事务对该行进行修改。
  - 锁表（table-level lock）：MySQL 会为 AUTO_INCREMENT 字段提供锁机制，防止并发插入时生成重复的值。为了避免并发插入时多次获取相同的 ID 值，InnoDB 引擎采用了 自增锁（auto-increment lock），确保只有一个事务可以分配下一个 AUTO_INCREMENT 值。
  - 事务：InnoDB 支持事务处理，可以保证数据的完整性和一致性。在事务中，所有操作都作为一个整体来执行，要么全部成功，要么全部失败。这可以保证在事务提交之前，不会出现主键值冲突的情况。
- 主键值间隙：并发情况下，如果事务回滚，则可能会出现主键值间隙的情况。
  - 隐式回滚: 在事务回滚时，InnoDB 会将事务中所有未提交的操作都回滚，包括对自增主键值的修改。
  - 回滚日志: 在事务回滚时，InnoDB 会使用回滚日志来恢复数据库的状态。
- 自增值的跳跃：由于 MySQL 使用了锁机制，并且并发插入时可能会涉及到事务提交、回滚等操作，所以 AUTO_INCREMENT 的值在并发环境下可能会出现跳跃，即并不是每次插入的 AUTO_INCREMENT 值都是连续的。举例来说，事务 A 插入了一条记录，获得了 ID 1；然后事务 B 插入了一条记录，获得了 ID 2。如果事务 A 回滚了，ID 1 也不会被回收，ID 2 仍然是事务 B 插入的 ID。因此，ID 3 会是下一个可用的自增值。这样就会出现 ID 2 跳过的问题。
- MySQL 提供了一些配置来优化并发写入过程中的 AUTO_INCREMENT 行为：
  - innodb_autoinc_lock_mode：该参数控制 AUTO_INCREMENT锁的模式。主要有以下三种模式：
    - 0（传统模式）：每次插入都会锁定整个表。只有一个事务可以在插入数据时获得自增 ID。
    - 1（连续插入模式）：这是默认模式，在并发情况下，每个插入线程将获得一个连续的块来生成自增值，减少了锁竞争。比如每个事务会获取 10 个连续的 ID（例如事务 A 获取 1-10，事务 B 获取 11-20），减少了锁的持有时间。
    - 2（插入时获取）：该模式下，插入操作会在插入前直接获取一个 ID 值，然后才执行插入操作，性能更高，但可能导致 ID 的跳跃。
- 主键值泄露：在某些情况下，主键值可能会泄露一些敏感信息。例如，在订单表中，主键值可能代表订单号。如果主键值是连续的，则攻击者可以通过猜测主键值来获取订单信息。
  - 使用 UUID 或雪花算法等其他方式来生成主键
  - 对主键值进行加密

【InnoDB缓冲池】

用于缓存从磁盘读取的数据页。InnoDB存储引擎将数据页（16KB）为单位进行存储和管理，当需要访问某个数据页时，会先检查缓冲池中是否缓存了该数据页，如果缓存了，则直接从缓冲池中读取数据页，如果未缓存，则从磁盘读取数据页并将其缓存到缓冲池中。

InnoDB缓冲池的管理：

LRU算法：使用LRU算法来管理缓存的数据页，LRU算法会根据数据页的访问频率来决定淘汰哪些数据页。
预读：会预读相邻的数据页，以提高查询效率。
刷新：会定期将脏数据页刷新到磁盘，以保证数据的一致性。

参数：

innodb_buffer_pool_size：设置缓冲池的大小，默认值为128M。
innodb_buffer_pool_instances：设置缓冲池实例的数量，默认值为1。
innodb_lru_scan_depth：设置LRU扫描的深度，默认值为1000。

查询操作可以直接从缓冲池中获取数据，而增删改操作则会先记录到重做日志中，然后再更新缓冲池中的数据。

查询：
- 当 InnoDB 需要访问某个数据页时，会先检查缓冲池中是否缓存了该数据页。
- 如果缓存了，则直接从缓冲池中读取数据页，称为缓冲池命中。
- 如果未缓存，则从磁盘读取数据页并将其缓存到缓冲池中，称为缓冲池未命中。
增删改：
- 当对数据进行增删改操作时，InnoDB 会先将操作记录到重做日志（redo log）中。
- 重做日志用于保证数据的一致性，即使发生系统崩溃，也可以通过重做日志来恢复数据。
- 之后，InnoDB 会将更新后的数据写入缓冲池中。
- 只有在事务提交时，才会将更新后的数据刷新到磁盘中。

【MySQL 的数据存放在哪个文件？】

不同的存储引擎保存的文件不同。对于InnoDB，创建一个数据库会在/var/lib/mysql/目录下创建一个以数据库名为名的目录，表结构和表数据的文件都在目录里。

db.opt ：用来存储当前数据库的默认字符集和字符校验规则。
xxx_db.frm ：xxx_db的表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。
xxx_db.ibd ：xxx_db的表数据会保存在这个文件。

表数据既可以存在共享表空间文件（文件名：ibdata1）里，也可以存放在独占表空间文件（文件名：表名字.ibd）。这个行为是由参数 innodb_file_per_table 控制的，若设置了参数 innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从 MySQL 5.6.6 版本开始，它的默认值就是 1 了，因此从这个版本之后， MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件。

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

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

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

行（row）：数据库表中的记录都是按行进行存放的，每行记录根据不同的行格式，有不同的存储结构。
页（page）：数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。InnoDB 的数据是按「页」为单位来读写的，将其整体读入内存。默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间。页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。页的类型有很多，常见的有数据页、undo 日志页、溢出页等等。
区（extent）：InnoDB 存储引擎是用 B+ 树来组织数据的。B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，随机 I/O 是非常慢的。解决这个问题就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。
段（segment）：表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。
- 索引段：存放 B + 树的非叶子节点的区的集合；
- 数据段：存放 B + 树的叶子节点的区的集合；
- 回滚段：存放的是回滚数据的区的集合，MVCC 利用了回滚段实现了多版本查询数据。

【InnoDB 行格式有哪些？】

InnoDB 提供了 4 种行格式：

Redundant：MySQL 5.0 版本之前用的行格式。
Compact：由于 Redundant 不是一种紧凑的行格式，所以 MySQL 5.0 之后引入了 Compact 行记录存储方式，Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact。
Dynamic：基于 Compact 的改进。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。
Compressed：基于 Compact 的改进。

Compact 行格式：

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

记录的额外信息：
- 变长字段长度列表：varchar(n) 和 char(n) 的区别是char定长、varchar变长，变长字段长度不固定。存储数据时，要把数据占用的大小存起来，读取数据时才能根据该字段去读取对应长度的数据。其他 TEXT、BLOB 等变长字段也是这么实现的。只出现在数据表有变长字段的时候。
- NULL 值列表：如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。
  - 二进制位的值为1时，代表该列的值为NULL。
  - 二进制位的值为0时，代表该列的值不为NULL。
  当数据表的字段都定义成 NOT NULL 的时候，表里的行格式就不会有 NULL 值列表了。在设计数据库表的时候，建议将字段设置为 NOT NULL，可以至少节省 1 字节的空间。NULL 值列表以最小1个字节对应8个字段为最小单位，比如有9个字段允许NULL，就为2字节。
- 记录头信息：
  - delete_mask ：标识此条数据是否被删除。执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。
  - next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
  - record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录
  - ……
记录的真实数据：
- 我们定义的字段
- row_id：隐藏字段，当表没有指定主键或唯一索引时会自动添加，非必需，占用 6 个字节。
- trx_id：隐藏字段，事务ID，表示这个数据是由哪个事务生成的。必需字段，占用 6 个字节。
- roll_pointer：隐藏字段，记录上一个版本的指针。必需字段，占用 7 个字节。

【varchar(n) 中 n 最大取值为多少？】

MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。varchar(n) 字段类型的 n 代表的是最多存储的字符数量，而不是字节大小。要算 varchar(n) 最大能允许存储的字节数，还要看数据库表的字符集，因为字符集代表着，1个字符要占用多少字节，比如 ascii 字符集， 1 个字符占用 1 字节，那么 varchar(100) 意味着最大能允许存储 100 字节的数据。

一行数据的最大字节数是 65535其中包含了 storage overhead，即「变长字段长度列表」和「NULL 值列表」所占用的字节数。存储字段类型为 varchar(n) 的数据时，其实分成了三个部分来存储：

真实数据
真实数据占用的字节数：「变长字段长度列表」所占用的字节数 = 所有「变长字段长度」占用的字节数之和。
- 条件一：如果变长字段允许存储的最大字节数小于等于 255 字节，就会用 1 字节表示「变长字段长度」；
- 条件二：如果变长字段允许存储的最大字节数大于 255 字节，就会用 2 字节表示「变长字段长度」；
字段类型是 varchar(65535) ，字符集是 ascii，所以代表着变长字段允许存储的最大字节数是 65535，符合条件二，所以会用 2 字节来表示「变长字段长度」。只有 1 个变长字段，所以就是2字节。
NULL 标识，如果不允许为NULL，这部分不需要：字段是允许为 NULL 的，所以会用 1 字节来表示「NULL 值列表」。

在数据库表只有一个 varchar(n) 字段且字符集是 ascii 的情况下，varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532。在 UTF-8 字符集下，一个字符最多需要三个字节，varchar(n) 的 n 最大取值就是 65532/3 = 21844。如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。

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

什么是行溢出：MySQL 中磁盘和内存交互的基本单位是页，一个页的大小一般是 16KB，也就是 16384字节，而一个 varchar(n) 类型的列最多可以存储 65532字节，一些大对象如 TEXT、BLOB 可能存储更多的数据，这时一个页可能就存不了一条记录。这个时候就会发生行溢出，多的数据就会存到另外的「溢出页」中。
什么是溢出页：如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。在一般情况下，InnoDB 的数据都是存放在「数据页」中。但是当发生行溢出时，溢出的数据会存放到「溢出页」中。
当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。大致如下图所示。

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

3.7 优化

【常见的数据库优化手段？】

使用 Explain 进行分析
优化数据访问：
- 减少请求的数据量，如只返回所需列和行，利用缓存。
- 使用索引覆盖查询（包含所有满足查询需要的数据的索引）
重构查询方式：
- 切分大型查询，避免阻塞小查询
- 分解复杂连接查询，改为单表查询，在应用程序中再关联，优点：
  - 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
  - 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
  - 减少锁竞争；
  - 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
  - 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效

【mysql索引的执行计划有了解过吗？有用过explain吗？重要的执行参数有哪些？】

MySQL 索引的执行计划 是指 MySQL 查询优化器在执行查询之前，根据查询语句所指定的条件和索引信息，所选择的最优执行路径。
Explain 是 MySQL 提供的一个用于查看执行计划的命令。使用 Explain 可以查看 MySQL 如何执行一条查询语句，以及执行过程中所使用的索引。
Explain 的重要执行参数:
- id：表示查询中每个操作的唯一标识符。当查询涉及多个表或子查询时，id 可以帮助了解各个操作的执行顺序。
- type: 表示访问类型的类型，执行效率从低到高的顺序为：
  - All（全表扫描）
  - index（全索引扫描）：index 和 all 差不多，只不过 index 对索引表进行全扫描。要尽量避免全表扫描和全索引扫描。
  - range（索引范围扫描）：一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。
  - ref（非唯一索引扫描）：返回数据可能是多条，虽然使用了索引，但该索引列的值并不唯一，有重复。并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。
  - eq_ref（唯一索引扫描）：通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引。
  - const（结果只有一条的主键或唯一索引扫描）：表示使用了主键或者唯一索引与常量值进行比较。const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。
- table: 表示查询涉及的表名。
- key: 表示查询实际使用的索引。
- key_len: 表示索引中使用的字节数。
- ref: 表示使用索引的字段或常量。
- rows: 表示查询需要扫描的行数。
- filtered: 表示过滤掉的行的百分比。
- extra: 表示一些额外的信息，如 Using where clause。
  
  Using where：查询使用了 WHERE 子句过滤数据。
  
  Using index：查询只使用了索引（覆盖索引），而没有访问表。
  
  Using temporary：查询使用了临时表（通常是因为使用了 GROUP BY 或 ORDER BY）。
  
  Using filesort：查询需要额外的排序操作，通常会影响性能。
  
  Using join buffer：查询使用了连接缓冲区，可能表示表连接没有使用索引。
  
  Using index condition：表示使用了索引条件下推（index condition pushdown）优化。
  
  Using union：查询使用了 UNION。
  
  Impossible where：查询的 WHERE 条件被优化为永远不成立。

【数据库的大表查询优化了解吗？】

使用合适的索引：索引是数据库中一种用于快速查找数据的数据结构。合理的使用索引可以有效提高查询性能。考虑以下因素：
- 查询条件中包含的字段
- 表的数据量
- 索引的类型
优化 SQL 语句
- 避免使用 SELECT *，只查询需要的字段。
- 避免使用复杂的查询条件，尽量使用简单条件。
- 避免使用子查询，可以使用 JOIN 语句代替。
分表分库：将大表拆分成多个小表，可以分散查询压力，提高查询性能。
使用缓存：将查询结果缓存起来，可以减少对数据库的访问，提高查询性能。
使用数据库读写分离：将数据库读写操作分离到不同的服务器上，可以减轻数据库的压力，提高查询性能。

【mysql慢语句调优做过吗？】

收集SQL执行日志，分析SQL执行计划，尝试优化SQL语法逻辑、优化索引或调整数据库参数，验证优化效果。

【为什么说 MySQL 单表不要超过 2000W 行】

假设

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

Total =x^(z-1) *y 总数会等于 x 的 z-1 次方与 Y 的乘积。

在 B+ 树中，叶子节点和非叶子节点的数据结构是一样的，区别在于，叶子节点存放的是实际的行数据，而非叶子节点存放的是主键和页号。
对于非叶子节点，数据页的大小仍为16K，其中File Header (38 Byte)、Page Header (56 Byte)、Infimum + Supermum（26 Byte）、File Trailer（8 Byte）, 再加上页目录，大概 1k 左右。数据可以用15K，假设主键是Bigint类型（8 Byte），页号是固定的4 Byte，所以索引页中一条数据为12 Byte。即一个数据页中 X = 15 * 1024 / 12 约为 1280 行。
对于叶子节点，存放的是真正的行数据，每行数据越长，页中能存放的数据就越少。假设一行数据1K，一页 Y = 15*1025/1000 = 15
Total =x^(z-1) *y 假设B+树为2层，Total = 19200，树为3层，则Total = 245762000。若一行数据不为1K，其与Y的数量为反比，行数据大N倍，最终树能放下的最大行数小N倍。
索引结构不会影响单表最大行数，2000W 也只是推荐值，超过了这个值可能会导致 B + 树层级更高，影响查询性能。

【MySQL 对于千万级的大表要怎么优化？】?????????????????????????????????????????????????????????

问题分析：
- 数据规模为千万级，但随着增长可能会过亿甚至更多。
问题优化：
- 规范化：
  - 配置规范：
    - 默认使用InnoDB存储引擎
    - 字符集统一使用UTF-8
    - MySQL默认隔离级别未可重复读，改为读提交更适合OLTP业务。
    - 严格控制单表数据量，建议在2000W以内。
    - 数据库建议不超过50个，每个库表数量不超过500。
  - 建表规范：
    - InnoDB禁止使用外键约束，可以通过程序层面保证。
    - 浮点数字段用DECIMAL代替FLOAT和DOUBLE。
    - 整型无需定义宽度，INT而不是INT(4)。
    - 使用TINYINT代替ENUM。
    - 尽量不使用TEXT、BLOB。
    - 建议字段为NOT NULL。
    - 尽量添加建表脚本审核工具，所有建表脚本都要通过审核。
  - 命名规范：
    - 全部小写，以_进行分割。
    - 长度不超过12个字符。
    - 命名简单明了，不需要注释辅助即可理解。
  - 索引规范：
    - 命名如idx_col1_col2、uniq_col1_col2等
    - 索引中的字段数建议不超过5个。
    - 单表的索引个数控制在5个以内。
    - 组合索引中选择性高的字段放在前面。
    - UPDATE、DELETE语句需要根据WHERE条件添加索引。
    - 合理利用索引覆盖来提高查询效率。
  - 应用规范：
    - 避免使用存储过程、触发器、自定义函数等，容易将业务逻辑与数据库耦合起来，成为未来的瓶颈。
    - 没有去重需求时少用union，多用union all（不去重，少了排序操作）。
    - 考虑使用limit N，少用limit M，N，特别是大表或M比较大的时候。
    - 减少或避免排序，如：group by语句中如果不需要排序，可以增加order by null。
    - SQL语句中IN包含的值不应过多。
    - UPDATE、DELETE语句一定要有明确的WHERE条件。
    - SELECT、INSERT语句必须显式的指明字段名称，禁止使用SELECT * 或是INSERT INTO table_name values()。
    - WHERE条件中的字段值需要符合该字段的数据类型，避免MySQL进行隐式类型转化。
- 业务层优化：
  - 业务拆分：
    - 将混合业务拆分为独立业务：业务之间尽量保证相对独立。
    - 将状态和历史数据分离：比如用户余额表与用户金额变更记录表。
  - 数据拆分：
    - 按照日期拆分
      - 按年月日拆分
      - 按照周月拆分
      - 按照季度、年度拆分
    - 采用分区模式：如hash、range等
  - 读多写少优化场景：采用缓存；Redis，将读请求打在缓存层面，降低数据库在热点数据的查询压力。
  - 读少写多优化场景：
    - 采用异步提交模式：减少同步等待，提高性能。
    - 使用队列技术：大量的写请求通过队列扩展，实现批量的数据写入。
    - 降低写入频率：对于数据及时性不太敏感，但更新频率较高的表，可以设定一定的更新频率，而不是每次都回库。
- 架构层优化：
  - 系统水平扩展场景：
    - 采用中间件技术：可以实现数据路由，水平扩展。如MyCAT、ShardingSphere、ProxySQL等
    - 采用读写分离技术：这是针对读需求的扩展，更侧重于状态表，在允许一定延迟的情况下，可以采用多副本的模式实现读需求的水平扩展，也可以采用中间件来实现，如MyCAT、ProxySQL、MaxScale、MySQL Router等
    - 采用负载均衡技术：如LVS技术；如基于域名服务的consul技术
  - 兼顾OLTP+OLAP的业务场景：
    - 采用NewSQL体系
    - 兼容MySQL协议的HTAP技术栈，如TiDB
  - 离线统计的业务场景：
    - 采用NoSQL体系：
      - 适合兼容MySQL协议的数据仓库体系，如Infobright或ColumnStore
      - 基于列式存储，属于异构方向，如HBase技术
    - 采用数仓体系：基于MPP架构，如使用Greenplum统计，如T+1统计
- 数据库优化：
  - 事务优化：
    - 根据业务场景选择事务模型，是否是强事务依赖
    - 事务降维策略：
      - 存储过程调用转换为透明的SQL调用：对于分布式系统来说，存储过程容易成为瓶颈，将其改造为SQL调用。
      - DDL操作转换为DML操作：某些业务常要紧急加一个字段等，该表可能设置有过百个字段。根本原因就是需求的动态变化。可以将这些字段改为配置信息，通过DML的方式更新。
      - Delete操作转换为高效操作：有些业务表要定期清理历史数据，如只保留最近一个月数据，当表数据规模较大时，delete操作的代价过高。有两种优化方案：
        
        根据业务建立周期表，按照日期的维度来设计。
        
        使用MySQL rename操作，拷贝一张新表。
  - SQL优化：
    - SQL语句简化
    - 尽可能避免或杜绝多表复杂关联
    - SQL中尽可能避免反连接：not in、not exists就是反连接。
    - SQL中尽量避免半连接查询：in、exists就是半连接。
  - 索引优化：
    - 必须有主键
    - SQL查询基于索引或者唯一性索引
    - 尽可能杜绝范围数据的查询
- 管理优化：
  - 数据清理如何做：冷热数据分离。把大表的Drop 操作转换为可逆的DDL操作。比如从testdb改为rename到testdb_arch中，后者对业务不可见，等到一定时间确认无用后再进行删除。
  - 数据变更如何做：在线变更，使用pt-osc等工具
  - 尽可能避免数据碎片

【NoSQL？】

NoSQL主要用于与关系型数据库互补：

KV存储：Redis等，有快的多的读写性能。
列式存储数据库：Hbase等，用于离线数据统计的场景。
文档型数据库：MongoDB等，Schema Free 模式自由，字段任意扩展。

数据库大多使用的机械磁盘，机械磁盘的访问方式有两种：

随机 IO：需要花费时间做昂贵的磁盘寻道，读写效率要比顺序 IO 小两到三个数量级，要尽量减少随机 IO
顺序 IO

比如 MySQL 的 InnoDB ，更新 binlog、redolog、undolog 都是在做顺序 IO，而更新 datafile 和索引文件则是在做随机 IO。为了减少随机 IO 的发生，关系数据库做了很多的优化，比如写入时先写入内存，然后批量刷新到磁盘上，但是随机 IO 还是会发生。

索引在 InnoDB 引擎中是以 B+ 树方式来组织的，而 MySQL 主键是聚簇索引（一种索引类型，数据与索引数据放在一起），既然数据和索引数据放在一起，那么在数据插入或者更新的时候，我们需要找到要插入的位置，再把数据写到特定的位置上，这就产生了随机的 IO。而且一旦发生了页分裂，就不可避免会做数据的移动，也会极大地损耗写入性能。

NoSQL 数据库是怎么解决这个问题的呢？

LSM 树（Log-Structured Merge Tree）牺牲了一定的读性能来换取写入数据的高性能，Hbase、Cassandra、LevelDB 都是用这种算法作为存储的引擎。

数据首先会写入到一个叫做 MemTable 的内存结构中，在 MemTable 中数据是按照写入的 Key 来排序的。为了防止 MemTable 里面的数据因为机器掉电或者重启而丢失，一般会通过写 Write Ahead Log 的方式将数据备份在磁盘上。
MemTable 在累积到一定规模时，它会被刷新生成一个新的文件，我们把这个文件叫做 SSTable（Sorted String Table）。当 SSTable 达到一定数量时，我们会将这些 SSTable 合并，减少文件的数量，因为 SSTable 都是有序的，所以合并的速度也很快。
当从 LSM 树里面读数据时，我们首先从 MemTable 中查找数据，如果数据没有找到，再从 SSTable 中查找数据。因为存储的数据都是有序的，所以查找的效率是很高的，只是因为数据被拆分成多个 SSTable，所以读取的效率会低于 B+ 树索引。

互补场景举例：

电商平台提供快速根据商品名称找到对应商品：数据库很难使用索引模糊匹配，使用Elasticsearch的倒排索引
商品的评论系统：数据增长极快，数据库很难扩展，使用MongoDB。

MongoDB的三个扩展性：

其一是 Replica，也叫做副本集，你可以理解为主从分离，也就是通过将数据拷贝成多份来保证当主挂掉后数据不会丢失。同时呢，Replica 还可以分担读请求。Replica 中有主节点来承担写请求，并且把对数据变动记录到 oplog 里（类似于 binlog）；从节点接收到 oplog 后就会修改自身的数据以保持和主节点的一致。一旦主节点挂掉，MongoDB 会从从节点中选取一个节点成为主节点，可以继续提供写数据服务。
其二是 Shard，也叫做分片，你可以理解为分库分表，即将数据按照某种规则拆分成多份，存储在不同的机器上。MongoDB 的 Sharding 特性一般需要三个角色来支持，一个是 Shard Server，它是实际存储数据的节点，是一个独立的 Mongod 进程；二是 Config Server，也是一组 Mongod 进程，主要存储一些元信息，比如说哪些分片存储了哪些数据等；最后是 Route Server，它不实际存储数据，仅仅作为路由使用，它从 Config Server 中获取元信息后，将请求路由到正确的 Shard Server 中。
其三是负载均衡，就是当 MongoDB 发现 Shard 之间数据分布不均匀，会启动 Balancer 进程对数据做重新的分配，最终让不同 Shard Server 的数据可以尽量的均衡。当我们的 Shard Server 存储空间不足需要扩容时，数据会自动被移动到新的 Shard Server 上，减少了数据迁移和验证的成本。

3.8 读写分离和主从复制

【读写分离？】

背景：大部分软件系统的场景都是读多写少，将读写流量分开，方便对读操作进行性能扩展。

实现：数据库拷贝为多分，其中主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

难点：

主从复制：主从库之间数据的拷贝。
屏蔽主从库分离带来的访问数据库方式的变化，降低开发难度。

读写分离能提高性能的原因在于：

主从服务器负责各自的读和写，写请求的锁不影响读请求，极大程度缓解了锁的争用；
从服务器可以扩展，一主多从来应对读流量较大的场景。
从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

【MySQL 的复制机制是什么？】

指将一个 MySQL 数据库服务器（称为主服务器）的数据变更复制到一个或多个其他 MySQL 数据库服务器（称为从服务器）的过程。

【如何保证数据库主从一致性？】

MySQL通过主从复制来保证数据库主从一致性。将主数据库（Master）的数据变更同步到从数据库（Slave）上，从而保持主从数据库之间的数据一致性。通过 binlog（二进制日志）来实现。

【MySQL 的主从复制的特性有？】

数据备份：通过将数据复制到从服务器，可以实现数据的双重或多重备份，从而提高数据的安全性。
读写分离：可以通过将读操作分流到从服务器，减轻主服务器的负载压力，从而提高数据库的整体性能。
高可用性：当主服务器发生故障时，可以将从服务器切换为主服务器，从而保证数据库的持续可用性。

【MySQL 的主从复制的实现原理？主从复制的三个线程？】

复制机制通过binlog实现，binlog以二进制形式的日志文件保存MySQL的数据变化，主从复制就是把binlog从主库异步传输到从库。

主从复制如何实现？

MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成。
MySQL 集群的主从复制过程梳理成 3 个阶段：
- 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
- 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
- 回放 Binlog：回放 binlog，并更新存储引擎中的数据。
具体详细过程如下：
- MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
- 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
- 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。
在完成主从复制之后，你就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

从库是不是越多越好？

不是的。因为从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽。所以在实际使用中，一个主库一般跟 2～3 个从库（1 套数据库，1 主 2 从 1 备主），这就是一主多从的 MySQL 集群结构。

MySQL 主从复制还有哪些模型？

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

主要流程：

主库的更新事件（update、insert、delete）被写到binlog。
主库创建一个binlog dump thread线程，把binlog的内容发送到从库。
从库创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log（MySQL-relay-bin.xxx）。
从库还会创建一个SQL线程，从relay log里面读取内容写入到从库，实现主从一致。实时检测本地Relay Log 中IO线程新增的日志内容，然后及时把Relay LOG 文件中的内容解析成sql语句。

其中：使用dump线程异步传输，避免影响主库正常使用。从库先写入中继日志relay log，避免回库过于耗时导致主从库的数据延迟过大。从库数量增加，连接的IO线程也增多，主库也需要创建相同数量的dump线程处理复制请求，实际使用中通常1个主库最多挂3~5个从库。

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

主节点的binlog dump 线程 ：负责向从节点发送binlog中新写入的数据。在读取binlog时，dump线程会首先获取binlog的锁，并在读取完毕后立刻释放，然后将读取到的数据发送至从节点。
从节点的I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。从节点I/O线程职责为向主节点发送数据同步的请求，接收主节点发送的数据并将其写入relay-log。
从节点的SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

【主从复制方式分类？主从的模式有哪些？实现同步的方式？】

异步复制：（默认使用）主库写入binlog日志后立即返回给客户端，从节点复制binlog的过程异步进行。但是一旦主库宕机，就有可能出现丢失数据的情况。在异步复制模式下，主服务器在将数据变更记录到 binlog 之后，就会立即返回给客户端，而不会等待从服务器是否已经将数据变更应用到自己的数据库中。因此，在异步复制模式下，从服务器的数据可能与主服务器的数据存在一定程度的延迟。
半同步复制（5.5版本之后）：确保从库接收完成主库传递过来的binlog内容已经写入到自己的relay log（传送log）后才会通知主库上面的等待线程。如果等待超时，则关闭半同步复制，并自动转换为异步复制模式，直到至少有一台从库通知主库已经接收到binlog信息为止。在半同步复制模式下，主服务器在将数据变更记录到 binlog 之后，会等待至少一个从服务器将数据变更应用到自己的数据库中，然后再返回给客户端。因此，在半同步复制模式下，从服务器的数据与主服务器的数据延迟非常小，但是主服务器的性能可能会受到一定的影响。

MySQL 主从复制是一种将数据从主服务器复制到从服务器的技术，用于保证数据库的数据一致性。主从复制有两种模式：异步复制和半同步复制。

异步复制：异步复制是 MySQL 主从复制的默认模式。在异步复制模式下，主服务器在将 binlog 事件写入磁盘后，就会立即返回客户端，而无需等待从服务器确认。从服务器会从主服务器接收 binlog，并将其写入中继日志。然后，从服务器的 SQL 线程会从中继日志中读取 binlog 事件，并应用到数据库中。

异步复制的优点是主服务器的性能较好，因为主服务器无需等待从服务器确认即可返回客户端。但是，异步复制的缺点是存在数据延迟，即从服务器的数据库可能与主服务器的数据库存在一定程度的延迟。
半同步复制：半同步复制是一种介于异步复制和完全同步复制之间的复制模式。在半同步复制模式下，主服务器在将 binlog 事件写入磁盘后，会等待至少一个从服务器确认后再返回客户端。

半同步复制的优点是数据延迟较小，因为主服务器至少要等待一个从服务器确认后再返回客户端。但是，半同步复制的缺点是主服务器的性能较低，因为主服务器需要等待从服务器确认。

实现同步的方式：

MySQL 主从复制通过 binlog 来实现同步。binlog 是 MySQL 服务器记录的所有数据库变更的日志。主服务器会将 binlog 发送到从服务器，从服务器会将 binlog 中的变更应用到自己的数据库中。

binlog 的格式有两种：statement 格式和 row 格式。statement 格式记录了完整的 SQL 语句，而 row 格式记录了受 SQL 语句影响的数据行。

主从复制的同步方式包括：

全量复制: 在全量复制模式下，主服务器会将整个数据库的最新状态复制到从服务器。全量复制通常用于初始化从服务器或恢复数据库。
增量复制: 在增量复制模式下，主服务器只会将数据库的变更记录（binlog）发送到从服务器。增量复制是主从复制的默认模式，因为它可以减少网络流量和 I/O 操作。

【主从库的数据延迟怎么应对？】

主从库之间存在数据延迟是正常现象，但过大的延迟会影响数据库的可用性和性能。数据延迟问题在排查问题时容易忽略，从数据库获取不到数据，有时会纠结于是否代码逻辑有问题，有错误的删除操作等，但过段时间又可以查到。

如何查看延迟状态？
- 通过show slave status查看参数，如：其中主要是seconds_behind_master
  - master_log_file: slave中的IO线程正在读取的主服务器二进制日志文件的名称
  - read_master_log_pos: 在当前的主服务器二进制日志中，slave中的IO线程已经读取的位置
  - relay_log_file: sql线程当前正在读取和执行的中继日志文件的名称
  - relay_log_pos: 在当前的中继日志中，sql线程已经读取和执行的位置
  - relay_master_log_file: 由sql线程执行的包含多数近期事件的主服务器二进制日志文件的名称
  - slave_io_running: IO线程是否被启动并成功的连接到主服务器上
  - slave_sql_running: sql线程是否被启动
  - seconds_behind_master: 从属服务器sql线程和从属服务器IO线程之间的事件差距，单位以秒计。表示当前从库延迟了多久时间。
数据延迟产生的原因：
- 从库机器性能比主库差太多，影响数据同步效率。
- 从库承担了过多的读压力：一般写压力都在主库，所以会让从库承担一些读操作。
- 主库有大事务执行，binlog要等待事务完成，后续才是从库同步。
- 从库发生了锁争用。
- 网络延迟，导致数据同步延迟。
- ……
如何解决数据延迟问题？
- 尽量不去从库查询数据：
  - 数据冗余：比如一次写操作，尽量减少再多加几次读操作的行为，适当冗余数据。足够简单但也会让单次操作数据变大。
  - 缓存：同步数据库时，将部分数据存入缓存，后续操作直接读取缓存数据。适合数据新增的场景，数据更新时因为多线程操作可能会造成数据不一致。
  - 查询主库：但要确认查询的量级在主库的承受范围内。
- 架构优化：
  - 持久层采用分库架构，分散从库单体压力。
  - 在业务和MySQL间加入缓存层，减少读压力。但若是数据经常变更，缓存命中率会很低，则不应采用此方案。
- MySQL配置：
  - 修改sync_binlog的参数的值：每个线程都有自己的binlog cache共用一份binlog files。线程通过write将日志写入文件系统的page cache（速度较快），再通过fsync将数据持久化到磁盘。sync_binlog=0表示每次提交事务都只write不fsync、当sync_binlog=1的时候，表示每次提交事务都执行fsync、当sync_binlog=N的时候，表示每次提交事务都write，但积累N个事务后才fsync。一般建议将此参数的值设置为1，因为这样的话能够保证数据的安全性，但是如果出现主从复制的延迟问题，可以考虑将此值设置为100~1000中的某个数值，非常不建议设置为0，因为设置为0的时候没有办法控制丢失日志的数据量，但是如果是对安全性要求比较高的业务系统，这个参数产生的意义就不是那么大了。
  - master_wait_slave_amount ：其控制了主服务器在返回客户端之前需要等待多少个从服务器确认。增大该值可以减少数据延迟，但会降低主服务器的性能。
  - 设置innodb_flush_log_at_trx_commit属性，表示每一次的事务提交是否需要把日志都写入磁盘。一共有三个属性值，分别是0（每次写到服务缓存，一秒钟刷写一次），1（每次事务提交都刷写一次磁盘），2（每次写到os缓存，一秒钟刷写一次），一般情况下我们推荐设置成2，这样就算mysql的服务宕机了，卸载os缓存中的数据也会进行持久化。
- 优化主服务器性能：升级硬件、优化MySQL配置如 innodb_buffer_pool_size、innodb_write_io_threads 等、优化 SQL 语句。
- 优化网络环境：如使用更快的网线、更换路由器等，以减少网络延迟。如果主从服务器位于不同的网络中，可以考虑使用专用网络进行连接。
- 使用半同步复制：半同步复制是一种介于异步复制和完全同步复制之间的复制模式。在半同步复制模式下，主服务器在将 binlog 事件写入磁盘后，会等待至少一个从服务器确认后再返回客户端。这可以有效降低数据延迟，但会增加主服务器的负载。
- 使用多主多从复制：是一种将多个主服务器复制到多个从服务器的复制模式。在多主多从复制模式下，可以将读操作分流到多个从服务器，减轻主服务器的负载，降低数据延迟。
- 使用延迟补偿：延迟补偿是一种通过应用程序逻辑来弥补数据延迟的技术。例如，在一些金融交易系统中，可以使用延迟补偿来确保交易的一致性。
- 使用读写分离：将写操作的负载分散到多个服务器上，降低主服务器的负载，减少数据延迟。
- 监控复制状态：定期监控主从复制的状态，及时发现并解决问题，可以有效防止数据延迟的发生。可以使用 MySQL 提供的 SHOW SLAVE STATUS 命令来查看主从复制的状态。

【MySQL 的 Galera 集群是什么？】

MySQL Galera 集群 是一种用于 MySQL 的高可用性和高性能集群解决方案。它是一个多主集群，这意味着集群中的所有节点都是活动的，可以同时处理读操作和写操作。这使得 Galera 集群成为需要高可用性和可扩展性的应用程序的理想选择。

Galera 集群 基于 同步复制，这意味着所有写入操作都必须应用于集群中的所有节点，然后再进行提交。这确保了集群中的所有节点始终拥有相同的数据。

Galera 集群 使用 paxos 算法 来就哪些写入操作应用于集群达成一致。该算法确保不会出现单点故障，并且即使部分节点出现故障，集群也能继续运行。

Galera 集群 包含在 MariaDB 发行版中，也可作为 MySQL 的插件使用。

Galera 集群的主要功能包括：

同步复制: 所有写入操作都必须应用于集群中的所有节点，然后再进行提交。
多主: 集群中的所有节点都是活动的，可以同时处理读操作和写操作。
无单点故障: 即使部分节点出现故障，集群也能继续运行。
高可用性: 集群可以承受节点故障而不会丢失数据。
高性能: 集群可以扩展以处理大量的读写操作。

Galera 集群的用例包括：

Web 应用: Galera 集群是需要高可用性和可扩展性的 Web 应用的理想选择。
联机事务处理 (OLTP)： Galera 集群是需要高一致性和高性能的 OLTP 应用的理想选择。
数据库复制: Galera 集群可用于在不同数据库之间复制数据。

使用 Galera 集群的好处包括：

提高可用性: Galera 集群可帮助确保即使部分节点出现故障，您的数据库也能始终可用。
改善性能: Galera 集群可通过将读写操作分布在多个节点上，帮助改善数据库性能。
简化管理: Galera 集群可通过简化集群中节点的添加和删除，简化数据库管理。

使用 Galera 集群的挑战包括：

增加复杂性: Galera 集群比传统的复制模式更复杂。
增加成本: Galera 集群比传统的复制模式需要更多硬件。
潜在数据丢失: 如果出现网络分区，可能会丢失部分数据。

总体而言，Galera 集群是一种功能强大且通用的集群解决方案，可适用于各种应用。但是，在决定是否使用 Galera 集群之前，请务必仔细考虑使用 Galera 集群的优势和劣势。

3.9 分库分表

【数据规模不断增长，到单表千万甚至亿级别后如何优化？】?????????????????????????????????????????????????????????

问题：

这种情况即使使用了索引，索引占用的空间也随着数据量的增长而增大，数据库就无法缓存全量的索引信息，那么就需要从磁盘上读取索引数据，影响到查询的性能。
数据量的增加也占据了磁盘的空间，数据库在备份和恢复的时间变长， 如何让数据库系统支持如此大的数据量？
不同模块的数据，比如用户数据和用户关系数据，全都存储在一个主库中，一旦主库发生故障，所有的模块儿都会受到影响， 如何做到不同模块的故障隔离呢？
数据库对于写入性能要弱于数据查询的能力，那么随着系统写入请求量的增长， 数据库系统如何来处理更高的并发写入请求呢？

以下是一些优化策略：

使用缓存： 使用缓存技术，如Redis或Memcached，将频繁查询的数据缓存起来，减少对数据库的访问次数。特别是对于热点数据，缓存可以显著提高访问速度。
数据分片： 将数据按照一定的规则分散到不同的数据库节点上，通过分片技术实现数据的水平拆分，减轻单个数据库的负载压力，提高并行查询能力。
- 分区和分表： 将数据按照一定的规则分散到不同的表或者不同的数据库实例中，可以减少单表的数据量，提高查询效率。时间范围、地理位置等都可以作为分区的依据。
- 垂直拆分与水平拆分： 根据业务需求，对数据库进行垂直拆分（按列拆分）或水平拆分（按行拆分），将数据存储到不同的表或不同的数据库中，从而提高查询效率。
优化查询语句和索引： 重新审视查询语句，确保它们充分利用了索引，避免全表扫描。可以考虑创建覆盖索引、组合索引等来优化查询性能。
定期数据清理和优化： 定期清理无用数据、优化数据库表结构、重新组织索引等操作，保持数据库的良好性能。

【分库分表？】

主从复制是数据全量的拷贝到多个节点，分库分表则每个节点只保存部分数据。既解决了单点数据存储瓶颈，又提升了查询性能。但数据被拆分后，单库单表查询也变成了请求多个数据节点的查询。

实现类别：水平切分和垂直切分
数据分片策略/路由策略
分布式事务与一致性

【水平切分和垂直切分？】

垂直切分：关注业务相关性
- 数据库层面将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。无法解决单个业务模块数据膨胀的问题。
- 表层面将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。
水平切分：又称为 Sharding，将单一数据表按规则拆分到不同库和表中，关注数据的特征。当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

【水平切分的策略？存在的问题？如何保证分库分表后ID的全局唯一性】

切分策略有：

哈希取模：hash(key) % N；适合实体表，比如用户表，根据UserId来拆分。
范围/区分：可以是 ID 范围也可以是时间范围；但存在明显的热点，某些时间数据多，并且要提前建表。
映射表：使用单独的一个数据库来存储映射关系。

存在的问题：

数据访问方式改变：查询前要先确认数据在哪些分库分表中，再进行查询。
事务问题：使用分布式事务来解决，比如 XA 接口。
多表连接：可以将原来的连接分解成多个单表查询，然后在用户程序中进行连接（应用层实现）。聚合类如count操作可以比如单独维护一张计数表或者缓存。
ID唯一性：注意尽量不要使用业务字段，即使用也要斟酌是否能保证唯一且不变
- 使用全局唯一 ID（UUID，Universally Unique Identifier，通用唯一标识码）：
  - 使用场景：一般用于生成 Request ID 来标记单次请求。
  - 缺点：
    1. 随机字符串，不具有业务意义。
    2. 32个16进制组成，比较耗费空间。
    3. 作为分区键其不是有序的，有排序需求时需要额外的字段辅助。
    4. 并且有序ID可以提升写入性能（B+树，无序需要先查询索引位置，并且磁盘顺序写无需寻道）。
- 为每个分片指定一个 ID 范围
- 分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)：
  - 将64bit的二进制数字分成若干部分，每个部分代表特定的含义，如时间戳、机器ID、序列号，比如0（恒定） + 41位时间 + 6位IDC信息（支持 64 个 IDC） + 6位业务信息（支持 64 个业务） + 10位自增信息（每毫秒支持 1024 个号）。
  - 实现方式：
    1. 嵌入到代码中，即分布在业务服务器中。优点是不需要网络调用，但需要更多的机器ID来支持更多业务服务器，同时需要引入 ZooKeeper 等分布式一致性组件来保证每次机器重启时都能获得唯一的机器 ID。
    2. 作为独立的服务部署，即发号器服务。需要网络调用，但内网环境下可以减少机器ID的位数。因为发号器部署实例数有限，那么就可以把机器 ID 写在发号器的配置文件里，这样即可以保证机器 ID 唯一性，也无需引入第三方组件了。 微博和美图都是使用独立服务的方式来部署发号器的，性能上单实例单 CPU 可以达到两万每秒。
    3. 依赖系统的时间戳，当系统时间不准时，发号器暂时拒绝发号。如果请求发号QPS不高，会造成ID的末位永远是1，分配不均匀。可以让时间戳不记录毫秒而是秒，生成的序列号的起始可以随机一下。

四. 分布式事务 todododododododo

CAP 与 ACID？

CAP：在一个分布式的系统中，涉及共享数据问题时，以下三个特性最多只能同时满足其中两个：

一致性（Consistency）：代表数据在任何时刻、任何分布式节点中所看到的都是符合预期的。
可用性（Availability）：代表系统不间断地提供服务的能力，理解可用性要先理解与其密切相关两个指标：可靠性（Reliability）和可维护性（Serviceability）。可靠性使用平均无故障时间（Mean Time Between Failure，MTBF）来度量；可维护性使用平均可修复时间（Mean Time To Repair，MTTR）来度量。可用性衡量系统可以正常使用的时间与总时间之比，其表征为：A=MTBF/（MTBF+MTTR），即可用性是由可靠性和可维护性计算得出的比例值，譬如 99.9999%可用，即代表平均年故障修复时间为 32 秒。
分区容忍性（Partition Tolerance）：代表分布式环境中部分节点因网络原因而彼此失联后，即与其他节点形成“网络分区”时，系统仍能正确地提供服务的能力。

CAP 与 ACID 的区别与联系

在分布式系统和数据库领域，CAP 定理 和 ACID 属性 是两个重要的概念。

1. CAP 定理 (分布式系统中的三大属性)

CAP 定理 指出在分布式系统中，一致性 (Consistency)、可用性 (Availability) 和 分区容错性 (Partition Tolerance) 这三个属性无法同时满足，最多只能满足其中两个。

CAP 三大属性解释:

一致性 (Consistency)
- 数据在多个节点之间保持同步。
- 所有节点的读操作返回相同的数据。
可用性 (Availability)
- 系统在接收请求时，必须给出响应（无论成功或失败）。
- 不能出现长时间无响应的情况。
分区容错性 (Partition Tolerance)
- 系统在遇到网络分区时仍然可以继续工作。
- 网络分区是指节点之间的通信失败。

CAP 不可同时满足的原因:

在 分布式系统中，网络分区是必然的。
为了应对网络分区，系统必须在 一致性 和 可用性 之间进行权衡。

常见系统设计选择:

类型	示例系统	选择策略
CP (强一致性 + 分区容错)	Zookeeper, HBase	保证数据强一致，降低可用性
AP (高可用性 + 分区容错)	Cassandra, DynamoDB	数据可能不一致，保证可用
CA (强一致性 + 高可用性)	理论上无法实现	不容忍网络分区

2. ACID 属性 (数据库事务特性)

ACID 是关系型数据库中用来保证事务执行正确性的四个属性：

ACID 四大属性解释:

原子性 (Atomicity)
- 事务中的所有操作 要么全部执行成功，要么全部回滚。
- 数据库的操作是一个不可分割的最小单元。
一致性 (Consistency)
- 事务执行前后，数据库的数据必须保持一致，满足所有约束规则。
- 比如银行转账，转出与转入总金额必须一致。
隔离性 (Isolation)
- 并发事务之间 相互隔离，避免数据冲突。
- 数据库提供了不同隔离级别，如 READ COMMITTED、REPEATABLE READ、SERIALIZABLE。
持久性 (Durability)
- 事务一旦提交，数据将永久存储在数据库中，即使发生系统崩溃。

示例代码：ACID 属性演示 (事务转账)

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db", "user", "password");

try {
    conn.setAutoCommit(false);  // 关闭自动提交 (支持事务)
    
    // 转账操作
    String withdraw = "UPDATE accounts SET balance = balance - 100 WHERE account_id = 1";
    String deposit = "UPDATE accounts SET balance = balance + 100 WHERE account_id = 2";

    Statement stmt = conn.createStatement();
    stmt.executeUpdate(withdraw);
    stmt.executeUpdate(deposit);

    conn.commit();  // 提交事务

} catch (SQLException e) {
    conn.rollback();  // 出错时回滚
    e.printStackTrace();
}

CAP 与 ACID 的区别与联系

对比项	CAP 定理	ACID 属性
应用场景	分布式系统，微服务架构	关系型数据库，事务处理
核心目标	分布式系统的可用性和一致性	数据库的事务完整性
关注点	数据在多个节点间的同步	数据库单节点事务的操作
属性解释	一致性、可用性、分区容错性	原子性、一致性、隔离性、持久性
设计挑战	网络分区引发的可用性与一致性冲突	高并发事务处理，避免数据冲突
示例系统	Zookeeper, Cassandra	MySQL, Oracle, PostgreSQL

总结:

CAP 定理 是 分布式系统的三大属性，关注节点间的数据同步和高可用性。
ACID 属性 是 数据库事务的四大特性，确保单节点数据库的事务完整性。
在分布式系统中，严格的 ACID 一致性 通常不适用，常采用 BASE 模型 (基本可用、软状态、最终一致性) 来权衡性能与一致性。

【XA方案？】

有一个事务管理器的概念，负责协调多个数据库（资源管理器）的事务不适合高并发场景，严重依赖数据库层面，同步阻塞问题；协调者故障则所有参与者会阻塞

XA 方案（分布式事务中的两阶段提交）

XA 方案 是一种用于 分布式事务管理 的 **两阶段提交协议 (2PC, Two-Phase Commit)**，主要用于保证分布式系统中多个数据库或资源之间的数据一致性。

XA 协议的组成

XA（eXtended Architecture） 是由 X/Open 分布式事务处理模型 (DTP) 定义的标准。它由以下三个主要角色组成：

事务管理器 (TM - Transaction Manager):
- 负责事务的全局管理、协调和提交。
- 典型实现: Atomikos, Bitronix, Narayana。
资源管理器 (RM - Resource Manager):
- 管理实际资源（如数据库、消息队列等）。
- 典型示例: MySQL InnoDB、Oracle、Kafka。
应用程序 (AP - Application Program):
- 调用数据库或资源进行业务操作。
- 应用程序通过 TM 和 RM 之间的接口来协调分布式事务。

XA 两阶段提交流程

XA 使用 两阶段提交 (2PC) 来确保事务一致性，分为两个阶段：

阶段 1: 事务准备 (Prepare Phase)

事务管理器向所有资源管理器发送 Prepare（准备提交） 请求。
各个资源管理器执行事务操作，但 不提交 数据。
如果所有 RM 都返回成功 (即准备就绪)，则进入第二阶段。

阶段 2: 事务提交 (Commit Phase)

如果所有资源管理器返回 准备成功，TM 发送 Commit（提交） 请求。
各个资源管理器正式提交事务，完成操作。
如果有任何一个 RM 返回失败，TM 发送 Rollback（回滚） 请求，回滚所有操作。

示例：XA 事务流程图

              +----------------------+
              |   事务管理器 (TM)    |
              +----------+-----------+
                         |
        +----------------+-------------------+
        |                                    |
+-------+--------+                   +-------+--------+
|  资源管理器1   |                   |  资源管理器2   |
|   (数据库A)    |                   |   (数据库B)    |
+----------------+                   +----------------+

示例代码: Atomikos 实现 XA 事务

import com.atomikos.icatch.jta.UserTransactionManager;
import javax.transaction.UserTransaction;

public class XATransactionExample {
    public static void main(String[] args) {
        UserTransactionManager tm = new UserTransactionManager();
        tm.init();
        
        UserTransaction tx = tm.getUserTransaction();

        try {
            tx.begin();  // 开启全局事务
            
            // 执行多个数据库操作
            dbA.execute("INSERT INTO orders ...");
            dbB.execute("UPDATE inventory ...");
            
            tx.commit();  // 提交事务
            System.out.println("事务提交成功");

        } catch (Exception e) {
            try {
                tx.rollback();  // 出错时回滚
                System.out.println("事务回滚");
            } catch (Exception rollbackEx) {
                rollbackEx.printStackTrace();
            }
        }
    }
}

XA 方案的优缺点

优点:

数据一致性强: 满足强一致性，适用于高可靠性业务。
标准化: 多数主流数据库和消息队列都支持 XA 协议。

缺点:

性能开销大: 由于涉及多个节点，锁占用时间长，事务管理开销较高。
单点故障风险: 事务管理器可能成为单点故障，需设计高可用方案。
阻塞问题: 某些 RM 不响应会导致事务长时间阻塞。

XA 方案的应用场景

金融支付系统: 例如银行转账和跨银行交易。
订单与库存管理: 电商平台订单和库存的同步处理。
业务强一致性场景: 企业级应用如 ERP、CRM 等。

XA 方案的替代方案

由于性能和可用性问题，XA 方案 在互联网分布式系统中使用较少，常用以下替代方案：

替代方案	描述
TCC 模式 (Try-Confirm-Cancel)	应用级别的三阶段补偿事务
本地消息表 (Outbox Pattern)	通过数据库消息表实现事务日志记录
事务消息 (Transactional Messaging)	使用消息队列实现事务提交和回滚
SAGA 模式	长事务拆分为多个小事务，支持补偿机制

总结:

XA 方案 是一种 分布式事务的标准协议，通过 两阶段提交 (2PC) 实现强一致性。
虽然其数据一致性保障非常强，但由于 性能瓶颈 和 单点故障风险，多用于 金融、电商和核心业务系统。
在互联网系统中，更常用 TCC、SAGA 和 事务消息方案 来替代传统 XA 提交，实现 高可用性和最终一致性。

【TCC方案？】

严重依赖代码补偿和回滚，一般银行用，和钱相关的支付、交易等相关的场景，我们会用TCC Try，对各个服务的资源做检测，对资源进行锁定或者预留 Confirm，在各个服务中执行实际的操作 Cancel，如果任何一个服务的业务方法执行出错，那么这里就需要进行补偿，即执行已操作成功的业务逻辑的回滚操作

TCC 方案 (Try-Confirm-Cancel) - 分布式事务解决方案

TCC 模式 是一种 分布式事务补偿机制，用于在多个服务之间 确保数据一致性。TCC 的核心思想是将一个全局事务分解为多个服务的三个操作：

Try (预留资源/检查可用性)
Confirm (确认/提交资源)
Cancel (回滚/释放资源)

TCC 三阶段解释

Try 阶段 (资源预留)
- 检查资源是否满足要求，做出资源预留。
- 不执行实际的业务提交，只预留资源。
Confirm 阶段 (确认操作)
- 如果所有服务的 Try 操作成功，则执行 Confirm 操作。
- 此操作是 **幂等的 (Idempotent)**，可多次执行。
Cancel 阶段 (取消操作)
- 如果某个服务的 Try 操作失败，则调用 Cancel 操作 进行回滚。
- 释放先前预留的资源，操作也必须是 幂等的。

示例场景: 电商订单支付系统

流程描述:

用户下单，系统需要完成 扣减库存、冻结账户余额 和 锁定订单状态 三个步骤。

Try 阶段:
- 检查库存是否足够，预留库存。
- 检查账户余额，冻结余额。
- 锁定订单状态，防止重复支付。
Confirm 阶段:
- 成功扣减库存。
- 从用户账户扣除实际金额。
- 将订单状态更新为“已支付”。
Cancel 阶段:
- 恢复库存数量。
- 解冻账户余额。
- 订单状态恢复为“未支付”。

示例代码 (伪代码)

public class OrderService {
    
    // Try 阶段: 资源预留
    public boolean tryReserveOrder(String orderId) {
        // 检查库存
        if (inventoryService.reserveStock(orderId)) {
            // 冻结账户余额
            return paymentService.freezeAmount(orderId);
        }
        return false;
    }
    
    // Confirm 阶段: 确认提交
    public boolean confirmOrder(String orderId) {
        return paymentService.confirmPayment(orderId) 
               && inventoryService.confirmStock(orderId);
    }
    
    // Cancel 阶段: 回滚
    public boolean cancelOrder(String orderId) {
        paymentService.unfreezeAmount(orderId);
        inventoryService.releaseStock(orderId);
        return true;
    }
}

TCC 的优缺点分析

优点:

强数据一致性: 每个服务单独实现事务，支持强一致性。
灵活性高: 业务逻辑完全由应用程序控制，支持复杂业务场景。
幂等保障: 支持重复执行，避免事务不一致。

缺点:

实现复杂: 需要开发大量业务逻辑，开发成本高。
业务入侵: 每个服务都需要实现 Try、Confirm、Cancel 接口。
补偿失败风险: 在回滚失败时，需要额外的故障恢复机制。
高延迟: 跨服务调用可能引入高延迟，降低系统性能。

TCC 适用场景

金融支付: 如银行转账、账户支付等对一致性要求高的业务。
订单与库存管理: 订单支付与库存扣减的协调。
预订与预约系统: 如酒店、机票等资源预留场景。
电商交易: 高价值订单的创建和支付管理。

与其他分布式事务方案的对比

事务方案	实现机制	数据一致性	性能	适用场景
XA 两阶段提交	数据库级别的两阶段提交	强一致性	较差	银行、核心金融系统
TCC 方案	应用层手动补偿逻辑	强一致性	一般	电商订单、支付系统
SAGA 模式	事务链条 + 异步补偿	最终一致性	优秀	异步操作，订单流程
事务消息	消息队列保障	最终一致性	优秀	异步任务处理，订单支付

总结:

TCC 模式 是一种经典的分布式事务解决方案，通过 资源预留、提交确认 和 回滚补偿 实现了 强数据一致性。
它适用于 高一致性、业务逻辑复杂的系统，但需要较高的开发和维护成本。
在现代微服务架构中，根据不同的业务需求，还可以选择 SAGA 模式、事务消息 等方案，达到更高的 可扩展性与最终一致性。

【可靠消息最终一致性方案？】

本地消息服务本地消息表其实是国外的 ebay 搞出来的这么一套思想。主动方是认证服务，有个消息异常处理系统，mq，还有消息消费端应用系统，还有采集服务；

在我认证返回数据中如果有发票是已经认证的，在处理认证数据的操作与发送消息在同一个本地事务中，业务执行完，消息数据也同时存在一条待确认的数据；

发送消息给mq，mq发送消息给消息消费端服务，同时存一份消息数据，然后发送给采集服务，进行抵账表更新操作；

采集服务逻辑处理完以后反馈给消息消费端服务，其服务删除消息数据，同时通知认证服务，把消息记录改为已确认成功费状态；

对于异常流程，消息异常处理系统会查询认证服务中过期未确认的消息发送给mq，相当于重试

独立消息最终一致性方案： A 主动方应用系统，B消息服务子系统，C消息状态确认子系统，C2消息管理子系统 D 消息恢复子系统，mq ，消息消费端E ，被动系统F

 流程：
A预发送消息给B，然后执行A业务逻辑，B存储预发送消息，A执行完业务逻辑发送业务操作结果给B，B更新预发送消息为确认并发送消息状态同时发送消息给mq，然后被E监听然后发送给F消费掉
C：对预发送消息异常的处理，去查询待确认状态超时的消息，去A中查询进行数据处理，如果A中业务处理成功了，那么C需改消息状态为确认并发送状态，然后发送消息给mq；如果A中业务处理失败了..那么C直接把消息删除即可.
C2 ： 查询消息的页面，对消息的可视化，以及批量处理死亡消息；
D： B给mq放入数据如果失败，，通过D去重试，多次重试失败，消息设置为死亡 
E：确保F执行完成，发送消息给B删除消息
优化建议： 
 （1）数据库：如果用redis，持久化要配置成appendfsync always，确保每次新添加消息都能持久化进磁盘
 （2）在被动方应用业务幂等性判断比较麻烦或者比较耗性能情况下，增加消息日志记录表.用于判断之前有无发送过；

可靠消息最终一致性方案 (Transactional Messaging)

可靠消息最终一致性方案 是一种常用于 分布式事务管理 的解决方案。该方案通过 消息队列 (MQ) 实现事务中不同服务间的数据 最终一致性，适用于对一致性要求较高但对强一致性无硬性需求的场景。

核心思想

通过消息队列保证跨服务的数据最终一致性。
在本地数据库和消息队列之间 **原子操作 (事务操作)**。
接收服务消费消息时，执行相应的操作，确保最终一致性。

方案架构流程

1. 生产者发送消息：本地事务与消息发送绑定

生产者先在 **本地数据库中保存业务数据和消息记录 (Outbox 表)**。
本地事务提交成功后，发送消息到消息队列。
如果消息发送失败，进行重试或补偿。

2. 消息中间件 (MQ)

消息持久化，确保消息可靠投递。
提供 重试机制，确保消息不会丢失。

3. 消费者处理消息：幂等处理与事务操作

消费者从 MQ 拉取消息，执行本地事务操作。
需要支持 幂等机制，确保消息处理不会被重复执行。
消费成功后发送确认响应。

示例场景: 电商订单与支付系统

场景描述:
用户提交订单，系统需要协调 订单服务 与 支付服务 确保订单状态和支付状态的一致性。

示例代码: 本地消息表模式 (Outbox Pattern)

订单服务：生产者端 (发送消息)

@Transactional
public void createOrder(Order order) {
    // 本地事务，保存订单和消息记录
    orderRepository.save(order);  // 保存订单
    messageRepository.save(new Message("order.created", order));  // 保存消息
    
    // 提交事务成功后发送消息
    messageService.sendMessage(order);
}

消息发送服务: MQ 发送消息

public void sendMessage(Order order) {
    try {
        // 将消息发送到消息队列
        mqClient.send("order.created", order);
        
        // 发送成功后删除本地消息记录
        messageRepository.deleteByOrderId(order.getId());
    } catch (Exception e) {
        // 发送失败时，进行重试或记录错误日志
        log.error("消息发送失败，订单ID: " + order.getId(), e);
    }
}

支付服务：消费者端 (消费消息)

@RabbitListener(queues = "order.created")
public void handleOrderCreated(Order order) {
    if (paymentService.isProcessed(order.getId())) {
        return;  // 幂等处理，避免重复消费
    }
    paymentService.processPayment(order);  // 执行支付操作
}

消息可靠性保障机制

消息持久化存储: 在数据库中持久化消息，避免消息丢失。
事务操作原子性: 本地数据库操作与消息发送绑定为一个事务。
重试机制: 消息发送失败时，支持定时重试，避免消息丢失。
消息幂等性: 确保消息的消费不会被重复处理。

方案的优缺点分析

优点:

高可用性: 即使部分服务不可用，数据最终仍会达到一致性。
解耦性: 各个服务通过消息队列解耦，降低耦合度。
扩展性好: 增加新服务时，扩展非常容易。

缺点:

实现复杂: 需要考虑消息持久化、事务管理、重试机制和幂等处理。
延迟性: 一致性是 最终一致性，存在短时间数据不一致的情况。
消息积压: 消息处理速度跟不上时，可能出现消息积压。

适用场景

订单与支付系统: 确保订单和支付状态一致。
库存管理: 确保库存系统和订单系统数据一致。
跨系统交易: 金融系统的转账、提现等操作。
分布式通知: 微服务系统之间的异步通知与消息传递。

与其他分布式事务方案的对比

方案	一致性保证	适用场景	优点	缺点
XA 两阶段提交	强一致性	金融、银行核心系统	数据一致性高	性能低，阻塞严重
TCC 模式	强一致性	电商订单、支付场景	业务逻辑可控	开发成本高，业务侵入
SAGA 模式	最终一致性	异步、长事务流程	高性能，扩展性好	编排难度大
可靠消息方案	最终一致性	消息驱动的微服务	解耦，可靠性强	实现复杂，延迟存在

总结:

可靠消息最终一致性方案 是分布式系统中 高可用性与一致性保障 的重要策略。通过 本地消息表模式 (Outbox Pattern)、事务消息 (Transactional Messaging) 和 消息中间件的可靠性机制，在服务间 异步传递数据，确保 数据最终一致性。

在 分布式架构和微服务系统 中，该方案广泛用于 订单管理、支付系统 和 库存管理，是应对 事务管理与数据一致性问题 的重要实践。

【SAGA 模式？】

SAGA 模式 - 分布式事务管理解决方案

SAGA 模式 是一种用于 分布式系统事务管理 的解决方案。它将 长事务 分解为 **一系列有序的本地事务 (子事务)**，每个子事务独立执行，失败时通过 补偿机制 (Compensation) 实现 最终一致性。

SAGA 模式的核心思想

将全局事务拆分为多个独立的子事务。
每个子事务执行后 **自动提交 (无需全局锁)**。
如果某个子事务失败，按执行顺序反向调用 补偿事务。

SAGA 模式的两种实现方式

编排式 (Orchestration):
- 使用 集中式协调器 (Saga Orchestrator) 管理事务的执行和补偿。
- 优点: 逻辑清晰，集中控制。
- 缺点: 单点故障风险。
事件驱动式/协同式 (Choreography):
- 每个服务通过 事件发布与订阅机制 相互协调。
- 优点: 去中心化，松耦合，扩展性强。
- 缺点: 逻辑复杂，难于监控。

示例场景: 电商订单与支付系统

业务场景描述:

用户下单后，系统需要完成以下步骤：

创建订单
扣减库存
扣款支付

如果任意步骤失败，系统需要回滚之前的操作。

示例代码: 编排式 SAGA 模式

public class OrderService {
    
    private final SagaOrchestrator orchestrator;

    public void createOrder(Order order) {
        try {
            orchestrator.executeSaga(() -> {
                // 执行子事务
                orderRepository.save(order);           // Step 1: 创建订单
                inventoryService.decreaseStock(order); // Step 2: 扣减库存
                paymentService.processPayment(order);  // Step 3: 支付
            });
            System.out.println("订单创建成功");
        } catch (Exception e) {
            System.out.println("订单创建失败，事务回滚");
        }
    }
}

示例代码: 事件驱动式 SAGA 模式 (基于事件总线)

// 创建订单服务
@EventListener(OrderCreatedEvent.class)
public void handleOrderCreated(Order order) {
    try {
        inventoryService.decreaseStock(order);
        eventPublisher.publish(new InventoryDecreasedEvent(order));
    } catch (Exception e) {
        eventPublisher.publish(new OrderCancelledEvent(order));
    }
}

// 扣减库存服务
@EventListener(InventoryDecreasedEvent.class)
public void handleInventoryDecreased(Order order) {
    try {
        paymentService.processPayment(order);
        eventPublisher.publish(new PaymentProcessedEvent(order));
    } catch (Exception e) {
        eventPublisher.publish(new OrderCancelledEvent(order));
    }
}

补偿机制示例代码

// 补偿操作: 回滚订单
@EventListener(OrderCancelledEvent.class)
public void handleOrderCancelled(Order order) {
    inventoryService.rollbackStock(order);
    paymentService.refund(order);
}

SAGA 模式的优缺点分析

优点:

高可用性: 没有全局锁，不受数据库性能瓶颈影响。
灵活性强: 补偿逻辑可以根据业务灵活设计。
松耦合: 服务之间通过事件通信，扩展性强。
去中心化 (事件驱动式): 不需要全局协调器，减少单点故障风险。

缺点:

补偿逻辑复杂: 需要额外设计 补偿操作，开发成本较高。
事件链长: 长链事务可能产生较高延迟，事务完成时间不可预测。
一致性延迟: 只保证 最终一致性，不适合对强一致性要求高的场景。
监控和调试难度大: 难于跟踪多个服务之间的事务流转。

SAGA 模式与其他事务管理方案的对比

事务方案	一致性保证	可用性	适用场景	优缺点
XA 两阶段提交	强一致性	低	银行、核心金融系统	性能低，阻塞严重
TCC 模式	强一致性	一般	电商支付、预订系统	业务侵入严重，补偿复杂
SAGA 模式	最终一致性	高	异步事务、订单系统	异步执行，延迟可接受
可靠消息方案	最终一致性	高	消息驱动系统	解耦，延迟，复杂性高

SAGA 模式的适用场景

电商订单管理: 确保订单、库存和支付的一致性。
金融交易: 在多个银行之间转账的场景。
预订与预约系统: 酒店、机票等需要分布式服务支持的预订操作。
物流系统: 多步骤的订单处理和配送流程。

总结:

SAGA 模式 是一种 高可用、最终一致性 的分布式事务解决方案，适用于 长事务管理 和 异步业务流程。
它通过 分布式子事务拆分与补偿机制 实现 跨服务的数据一致性。
编排式 (Orchestration) 适用于简单业务逻辑，而 事件驱动式 (Choreography) 更适合高度分布式和松耦合的系统。
在设计时，需要充分考虑 补偿逻辑、幂等性、事件链追踪与监控机制，以确保分布式事务的可靠性和稳定性。

【最大努力通知性（定期校对）？】

业务主动方完成业务处理之后，设置时间阶梯型通知规则向业务活动的被动方发送消息，允许消息丢失.
被动方根据定时策略，向主动方查询，恢复丢失的业务消息
被动方的处理结果不影响主动方的处理结果
需增加业务查询，通知服务，校对系统服务的建设成本
适用于对业务最终一致性的时间敏感度低，跨企业的业务通知活动
比如银行通知，商户通知，交易业务平台间商户通知，多次通知，查询校对等

最大努力通知性（定期校对）

最大努力通知性 (Best-Effort Notification) 是分布式系统中的一种事务补偿与通知策略。该策略通过 尽最大努力发送通知，但不强制要求实时或 完全成功，以确保系统的 最终一致性。

核心思想

异步通知: 发送方尽力发送通知，不保证通知一定送达。
定期校对: 接收方会 定期校对数据，在发现不一致时触发补偿机制。
最终一致性: 通过通知与校对机制，确保跨系统的数据最终一致。

适用场景

支付系统: 确保支付成功后，订单状态与支付状态一致。
库存系统: 确保库存与订单系统同步。
分布式账务系统: 确保交易记录在多个系统中对齐。
消息通知: 确保消息在消费系统中完整送达。

实现机制

发送方:
- 在执行成功后，异步通知 接收方。
- 如果发送失败，记录失败日志，定期重试。
接收方:
- 在接收通知时 执行幂等操作。
- 如果长期未收到通知，主动校对 数据源，修复数据不一致。
定期校对:
- 定期运行 校对任务，对比 发送方与接收方的数据状态。
- 发现不一致时 重试通知 或执行 补偿操作。

示例场景: 支付订单处理

场景描述:

用户支付成功后，支付服务通知订单服务更新订单状态。如果通知失败，系统会定期进行订单与支付数据的对账。

示例代码: 最大努力通知与定期校对

支付服务: 异步通知订单服务

public void processPayment(Order order) {
    paymentRepository.save(order);  // 保存支付记录
    try {
        notificationService.notifyOrderService(order);  // 异步通知
    } catch (Exception e) {
        log.error("支付通知失败，订单ID: " + order.getId());
    }
}

订单服务: 接收通知 (幂等处理)

@EventListener(PaymentNotificationEvent.class)
public void handlePaymentNotification(Order order) {
    if (!orderService.isPaymentProcessed(order.getId())) {
        orderService.updateOrderStatus(order.getId(), "PAID");
    }
}

定期校对任务: 自动修复数据

@Scheduled(fixedDelay = 60000)
public void reconcileOrders() {
    List<Order> inconsistentOrders = orderRepository.findInconsistentOrders();
    for (Order order : inconsistentOrders) {
        if (paymentService.isPaymentSuccess(order.getId())) {
            orderService.updateOrderStatus(order.getId(), "PAID");
        }
    }
}

优缺点分析

优点:

高可用性: 系统间的临时通信失败不会导致事务失败。
最终一致性: 通过定期校对确保数据正确。
容错性: 支持重试与补偿，降低了系统间网络问题的影响。

缺点:

一致性延迟: 数据不一致的修复过程存在延迟。
实现复杂度高: 需要补偿机制、重试策略和校对任务。
资源消耗: 定期校对可能会消耗大量系统资源。

最佳实践与注意事项

消息持久化: 持久化未成功的通知，避免消息丢失。
重试策略: 设置重试机制与最大重试次数，避免无限循环。
幂等处理: 确保接收方的操作是幂等的，防止重复执行。
校对任务优化: 优化对账任务频率，减少系统负载。
日志与监控: 记录通知与对账过程，方便问题排查。

与其他分布式事务方案的对比

方案	一致性保障	适用场景	优点	缺点
XA 两阶段提交	强一致性	银行/金融系统	数据一致性强	性能低，阻塞严重
TCC 模式	强一致性	订单/库存等关键场景	可控，灵活补偿	实现复杂，开发成本高
SAGA 模式	最终一致性	异步事务/订单场景	高扩展性，异步执行	延迟存在，补偿复杂
可靠消息模式	最终一致性	消息驱动系统	解耦，持久化强	实现复杂，消息积压风险
最大努力通知性	最终一致性	支付、消息系统等	容错性高，操作灵活	校对任务增加系统负载

总结:

最大努力通知性（定期校对） 是一种 高可用、最终一致性 的事务处理策略，适用于 分布式系统中的异步操作与通知场景。

通过 异步通知与定期校对机制，解决了系统间 短暂通信失败 带来的数据不一致问题。
在 支付系统、订单管理与库存同步 等场景中广泛应用，提供了 容错能力强、实现灵活 的事务管理方案。

要成功实施该方案，需要注意 通知持久化、幂等操作、重试策略与定期校对优化，以确保系统数据的一致性和稳定性。

【Seata（阿里）？】

应用层基于SQL解析实现了自动补偿，从而最大程度的降低业务侵入性；将分布式事务中TC（事务协调者）独立部署，负责事务的注册、回滚；通过全局锁实现了写隔离与读隔离。

MySQL 八股文主要看《高性能 MySQL》就可以了，InnoDB 深入的话再看一下《MySQL技术内幕》，底层数据结构的话可以看一下《算法》中二叉搜索树、AVL、二三树、红黑树、B 树的相关章节。

Seata (阿里巴巴分布式事务解决方案)

Seata (Simple Extensible Autonomous Transaction Architecture) 是阿里巴巴开源的一站式 分布式事务解决方案，用于解决 微服务架构中的分布式事务一致性问题，支持多种事务模式，如 AT (自动补偿事务)、TCC (Try-Confirm-Cancel)、SAGA 和 XA 模式。

Seata 核心架构与组件

TM (Transaction Manager, 事务管理器)
- 负责 全局事务的开始、提交与回滚。
RM (Resource Manager, 资源管理器)
- 负责 分支事务的注册、状态汇报与资源管理。
TC (Transaction Coordinator, 事务协调器)
- 维护 全局事务的运行状态，负责协调各个分支事务的提交与回滚。

Seata 工作流程

TM 启动全局事务，请求 TC 创建事务 ID (XID)。
**业务服务调用分支事务 (RM)**，注册到 TC。
TM 提交/回滚全局事务

：
- 提交时，TC 协调所有分支事务的提交。
- 回滚时，TC 协调所有分支事务的回滚。

Seata 的事务模式

AT 模式 (Automatic Transaction - 自动补偿事务)

基于数据库 Undo Log 实现，适用于 关系型数据库。
应用场景: 数据库事务、订单支付系统等。

示例:

@Transactional
public void createOrder(Order order) {
    orderMapper.insert(order);      // 新增订单
    inventoryService.reduceStock(order); // 扣减库存
}

优点:

自动化管理，无需手工定义补偿逻辑。

缺点:

仅支持关系型数据库，锁资源较多，性能受限。

TCC 模式 (Try-Confirm-Cancel)

应用程序手动定义 Try/Confirm/Cancel 三阶段方法。
应用场景: 电商、预订等需要复杂业务逻辑的场景。

示例:

@TwoPhaseBusinessAction(name = "reserveInventory")
public boolean tryReserve(Order order) {
    // Try: 检查库存并锁定
}

public boolean confirm(Order order) {
    // Confirm: 确认库存已使用
}

public boolean cancel(Order order) {
    // Cancel: 解锁库存
}

优点:

灵活可控，适用于复杂业务场景。

缺点:

业务侵入性高，开发成本大。

SAGA 模式

类似于分布式补偿模式，按事务顺序执行，失败时反向回滚。
应用场景: 长事务操作，如电商订单管理、物流系统等。

XA 模式 (两阶段提交)

遵循 XA 标准，保证数据库 强一致性。
应用场景: 银行、金融等高一致性要求的场景。

Seata 的应用场景与优势

应用场景:

微服务架构中的数据库事务管理
跨服务订单管理 (如电商平台)
库存管理与分布式支付事务
金融系统中的跨账户转账与结算

Seata 的优势:

高性能: AT 模式比传统的两阶段提交性能更优。
灵活性: 支持多种事务模式，适配不同业务场景。
自动化: 数据库模式下的事务提交与回滚自动管理。
开源社区: 活跃的社区支持与丰富的扩展插件。

Seata 与其他分布式事务方案对比

方案	一致性保证	适用场景	优点	缺点
Seata AT 模式	最终一致性	数据库事务、订单处理	自动化，低开发成本	锁资源多，数据库依赖强
Seata TCC 模式	强一致性	复杂业务事务，预订服务	灵活可控，跨服务扩展性强	业务侵入性高，难维护
Seata SAGA 模式	最终一致性	长事务与异步执行场景	高扩展性，异步执行	补偿逻辑复杂，延迟存在
Seata XA 模式	强一致性	金融交易，银行系统	标准化，跨数据库事务支持	性能低，阻塞严重
可靠消息模式	最终一致性	消息驱动系统	解耦，持久化强	实现复杂，消息积压风险

部署与配置注意事项

TC 部署: 高可用部署，避免单点故障。
数据库配置: AT 模式下数据库需开启 Undo Log 功能。
幂等设计: 确保业务操作幂等，防止重复执行。
事务隔离: 使用 Seata 代理数据源，自动控制事务提交与回滚。
日志监控: 配置日志和监控系统，确保事务操作透明化。

总结:

Seata 是一款强大的分布式事务解决方案，适用于多种微服务架构。
支持 AT、TCC、SAGA 与 XA 等多种事务模式，满足从数据库事务到复杂业务流程的不同需求。
在实施时，应根据 业务场景的事务模型要求、性能要求与一致性保证级别，选择适合的事务模式，合理部署与配置。

Seata 在分布式环境下提供了可靠的事务管理方案，是构建微服务系统中 分布式事务一致性管理 的重要工具。

【雪花算法Snowflake】

雪花算法（Snowflake Algorithm） 是一种生成分布式唯一ID的算法，由 Twitter 提出的。它通过将一个64位的二进制数按特定的规则划分为多个部分，保证在分布式系统中，ID的唯一性和高效性。

雪花算法的结构

雪花ID的64位结构通常如下：

1 bit	41 bits	6 bits	6 bits	10 bits
符号位	时间戳（毫秒级）	数据中心ID	工作机器ID	序列号

符号位（1 bit）：
- 保留为0，用于标识ID的符号。
时间戳（41 bits）：
- 41位用于存储时间戳，单位是毫秒，表示从某个固定的时间（称为epoch）开始的毫秒数。41位可以表示约 69年 的时间范围。
- 这个时间戳部分保证了不同时间生成的ID是递增的。
数据中心ID（6 bits）：
- 10位用于存储数据中心ID。在分布式环境中，可能存在多个数据中心，因此需要通过数据中心ID来区分不同数据中心生成的ID。
- 这部分可以表示最多1024个数据中心（210=10242^{10} = 1024）。
工作机器ID（6 bits）：
- 10位用于存储机器ID。每个数据中心内的机器是独立的，因此每台机器需要有一个唯一的标识符。
- 这部分也可以表示最多1024台机器（210=10242^{10} = 1024）。
序列号（10 bits）：
- 10位用于表示同一毫秒内生成的多个ID。这部分能表示每毫秒生成最多4096个ID（212=40962^{12} = 4096）。

雪花算法生成ID的过程

时间戳（毫秒级）：
- 从一个固定的epoch时间（如 1970年1月1日）开始计时，计算当前时间与epoch之间的毫秒数。
- 时间戳是增长的，随着时间的推移，ID会按时间顺序递增。
机器标识符：
- 机器标识符（数据中心ID和机器ID）是在部署集群时由系统分配的，保证每台机器生成的ID是唯一的。
序列号：
- 同一毫秒内生成的多个ID，通过序列号来区分。每次生成ID时，序列号从0开始，每生成一个ID递增，直到达到最大值（4095），然后会等待下一毫秒再重新生成。

ID的唯一性和有序性

唯一性：每个生成的ID包含了时间戳、数据中心ID、机器ID和序列号等信息，因此它能保证在分布式系统中，所有机器生成的ID都是唯一的。
有序性：ID根据时间戳递增生成，因此生成的ID具备时序性，也可以按时间顺序进行排序。每个ID都代表着它生成的时间。

优点：

高效性：雪花算法生成ID的过程非常高效，生成ID是基于计算，不需要访问数据库或外部系统。
全局唯一性：即使在分布式系统中，使用雪花算法生成的ID也是全局唯一的，避免了分布式系统中的ID冲突。
时序性：ID根据时间戳递增，因此生成的ID具有时序性，方便进行排序。
不依赖集中式系统：生成ID的过程完全去中心化，不需要协调中心，避免了中心化系统的瓶颈和单点故障。

缺点：

依赖系统时间：雪花算法生成ID依赖于服务器的系统时间，如果系统时间发生回拨或错误，可能会导致生成重复的ID或ID的不连续。
机器标识的配置问题：雪花算法中机器ID和数据中心ID是由系统配置的，这要求每台机器有唯一的标识符。随着机器数目增多，配置管理变得更加复杂。
64位限制：虽然64位的ID可以提供大量的ID，但若分配的时间过长或机器数量过多，可能会导致ID生成器的设计空间不足，尽管这个问题相对较小。

雪花算法的应用场景

分布式系统：雪花算法特别适用于分布式系统中，需要生成全局唯一且有序的ID的场景，如分布式数据库、分布式缓存等。
高并发系统：在高并发的环境下，使用雪花算法生成ID可以避免性能瓶颈，且无需集中式的ID生成服务。
订单系统：电商、支付、物流等系统需要高并发的唯一ID生成，雪花算法可以在这些场景中有效地提供支持。

总结

雪花算法是一种通过时间戳、机器ID、序列号等组成部分来生成全局唯一的ID的算法，适合用于分布式环境中生成高效、唯一、递增的ID。它具备高效性、全局唯一性和有序性等优点，但也需要关注系统时间和机器标识的管理问题。

【ShardingSphere、ProxySQL + MyCat】

ShardingSphere、ProxySQL、MyCat 对比与选型指南

一、工具概述

1. ShardingSphere

类型：Apache 顶级开源项目，分布式数据库中间件生态圈。
核心模块：
- ShardingSphere-JDBC：轻量级 Java 框架，基于 JDBC 驱动，透明化分库分表、读写分离。
- ShardingSphere-Proxy：独立部署的数据库代理，支持 MySQL/PostgreSQL 协议，功能更全面。
适用场景：
- 分库分表、读写分离、数据加密、影子库压测。
- 适合 Java 技术栈，需代码侵入（JDBC 模式）或独立代理（Proxy 模式）。

2. ProxySQL

类型：高性能 MySQL 中间件代理，专注读写分离与查询路由。
核心功能：
- 读写分离：自动路由 SELECT 到从库，其他操作到主库。
- 连接池管理：复用数据库连接，降低主库压力。
- 查询缓存与重写：灵活控制 SQL 行为。
适用场景：
- 简单读写分离、负载均衡、故障切换。
- 适合 快速部署，无需代码改造。

3. MyCat

类型：早期流行的 数据库分库分表中间件，基于 Proxy 模式。
核心功能：
- 分库分表、读写分离、全局序列生成。
- 支持 MySQL、Oracle 等多种数据库。
适用场景：
- 传统分库分表需求，尤其适合 非 Java 技术栈。
- 社区活跃度下降，逐渐被 ShardingSphere 取代。

二、核心对比

维度	ShardingSphere	ProxySQL	MyCat
架构模式	JDBC（无中心） / Proxy（中心化）	中心化 Proxy	中心化 Proxy
分库分表	✅ 支持（灵活策略）	❌ 不支持	✅ 支持（配置驱动）
读写分离	✅ 支持	✅ 核心功能	✅ 支持
协议兼容性	MySQL/PostgreSQL（Proxy 模式）	MySQL	MySQL/Oracle/SQL Server 等
性能	JDBC 模式性能高（直连数据库）	高（C++ 实现，低延迟）	中（Java 实现，存在 GC 停顿）
学习成本	中（需理解分片规则）	低（配置简单）	中（XML 配置复杂）
社区生态	活跃（Apache 项目）	活跃	维护较少
适用阶段	适合从中小规模到超大规模系统	适合中小规模读写分离	适合传统分库分表场景

三、适用场景与选型建议

1. ShardingSphere

推荐场景：
- 分库分表：需要灵活的分片策略（如哈希、范围、自定义复合分片）。
- 多租户系统：按租户 ID 分库，数据隔离。
- 混合负载：同时需要分库分表、读写分离、数据加密。
典型案例：
- 电商平台：订单表按用户 ID 分库，历史订单按月分表。
- 金融系统：账户表分库，交易流水按时间分表。

2. ProxySQL

推荐场景：
- 读写分离：主从集群流量自动路由。
- 查询缓存：缓存频繁访问的 SELECT 结果。
- 故障转移：主库宕机时自动切换至从库。
典型案例：
- 博客系统：读多写少，通过 ProxySQL 分摊读压力。
- 报表查询：将复杂查询路由到专用分析从库。

3. MyCat

推荐场景：
- 遗留系统改造：已有系统需要快速实现分库分表。
- 非 Java 技术栈：如 PHP、Python 项目通过 MyCat Proxy 接入。
典型案例：
- 传统 ERP 系统：按分公司分库，每个库内分表存储业务数据。

四、配置示例与操作对比

1. ShardingSphere-JDBC 分片配置

# application.yml
spring:
  shardingsphere:
    datasource:
      names: ds0, ds1
      ds0: # 数据源配置
        jdbc-url: jdbc:mysql://db0:3306/db
        username: root
        password: 123456
      ds1:
        jdbc-url: jdbc:mysql://db1:3306/db
        username: root
        password: 123456
    rules:
      sharding:
        tables:
          order:
            actualDataNodes: ds$->{0..1}.order_$->{0..1}
            tableStrategy:
              standard:
                shardingColumn: order_id
                shardingAlgorithmName: order_table_hash
        shardingAlgorithms:
          order_table_hash:
            type: HASH_MOD
            props:
              sharding-count: 2

2. ProxySQL 读写分离配置

-- 添加后端数据库
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES 
  (10, 'master', 3306), 
  (20, 'slave1', 3306), 
  (20, 'slave2', 3306);

-- 定义路由规则
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES 
  (1, 1, '^SELECT', 20, 1), 
  (2, 1, '.*', 10, 1);

-- 加载配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

3. MyCat 分片配置（server.xml）

<schema name="testdb">
  <table name="user" dataNode="dn1,dn2" rule="mod-long"/>
</schema>
<dataNode name="dn1" dataHost="host1" database="db1"/>
<dataNode name="dn2" dataHost="host2" database="db2"/>
<dataHost name="host1" maxCon="1000" balance="1">
  <heartbeat>select user()</heartbeat>
  <writeHost host="master1" url="192.168.0.1:3306" user="root" password="123456"/>
</dataHost>

五、选型决策树

是否需要分库分表？
- 是 → 选择 ShardingSphere（Java 项目）或 MyCat（非 Java 项目）。
- 否 → 进入下一步。
是否需要高性能读写分离？
- 是 → 选择 ProxySQL。
- 否 → 考虑数据库内置功能（如 MySQL Router）。
技术栈偏好？
- Java → 优先 ShardingSphere。
- 其他语言 → 考虑 ProxySQL 或 MyCat。

六、总结

ShardingSphere：全能选手，适合复杂分片需求与 Java 生态深度集成。
ProxySQL：轻量高效，专注 MySQL 读写分离与查询优化。
MyCat：传统方案，适合非 Java 项目或遗留系统改造。

最终建议：

新项目优先选择 ShardingSphere，享受活跃生态与持续更新。
简单读写分离场景用 ProxySQL，5 分钟快速上线。
老系统改造且无研发资源时，可评估 MyCat。

数据库

一. 综合

1.1 范式

【 数据库三范式是哪三个？】

1.2 数据库连接

【数据库连接池？】

二. SQL?????????????????????????????????????????????????????????

2.1 连接

【SQL有哪些表连接方式？】

2.2 组合查询

【组合查询？】

2.3 视图

视图是什么？

2.4 存储过程

【存储过程？】

2.5 聚合函数

【聚合函数？】

中级查询

【HAVING子句？】

【嵌套子查询？】

【LIMIT/OFFSET分页查询？】

其它

【防止SQL注入？】

【SQL规范？】TODODODODODODO

三. MySql

3.1 架构和流程

【MySQL的架构？】

【MySQL的执行流程？】

3.1 索引

【索引是什么？索引的适用场景？不适用场景？失效场景？】

【数据库的索引有哪几种？】

【聚集索引与非聚集索引？】

【组合索引？最左匹配原则？】

【索引下推？】

【MySql有哪几种索引实现？】

【MySQL的索引结构的实现B+树】

【什么是索引覆盖？如何减少回表？】

【表建立索引的原则？】

【为什么不建议使用UUID作为索引/主键？】

【一条select语句的查询过程？二级索引？】

3.2 事务

【什么是数据库事务？】

【数据库事务有哪些特性？事务特点（ACID）？MySQL如何实现四个特性？】

【数据库事务的并发一致性问题有哪些？怎么解决？】

【数据库事务的几种隔离级别？】

【你们生产用的是哪种事务隔离级别？为什么？】

【AUTOCOMMIT？】

【MySql中数据库事务是如何实现的？】

【七种事务传播行为？】

3.3 锁

【MySql怎么解决并发一致性问题？】

【MySQL有哪些锁？】

【两段锁协议】

【三级封锁协议？】

【死锁】

【MySQL线上给小表加字段导致库挂了，online DDL操作？修改表结构涉及到哪些锁？】

3.4 多版本并发控制

【什么是MVCC？MVCC机制了解不？】

【MVCC与隔离级别的关系？】

【MVCC机制有什么问题？MVCC与幻读？】

【MVCC的实现原理和流程？】

【MVCC 的SELECT操作会加锁吗？】

3.5 日志

【Undo Log】

【Buffer Pool？缓存什么？】

【Redo Log】

【BinLog】

【一个Update语句的执行流程】

【两阶段提交？】

【MySQL磁盘I/O很高，有什么优化办法？】

3.6 引擎

【MySQL常用的存储引擎？特点与区别?还有应用场景】

【InnoDB 的一些特性？】

【InnoDB 表对主键的选择顺序？主键生成策略？】

【AUTO_INCREMENT原理（考察并发情况）？】

【InnoDB缓冲池】

【MySQL 的数据存放在哪个文件？】

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

【InnoDB 行格式有哪些？】

【varchar(n) 中 n 最大取值为多少？】

【数据库三范式是哪三个？】

【MySQL 的主从复制的实现原理？主从复制的三个线程？】

【Seata（阿里）？】