MySQL 面试

发表于 更新于 分类于 ， ， 本文字数： 28k 阅读时长 ≈ 26 分钟

MySQL 面试

关系数据库综合

【基础】什么是范式？什么是反范式？

:::details 要点

数据库规范化，又称“范式”，是数据库设计的指导理论。范式的目标是：使数据库结构更合理，消除存储异常，使数据冗余尽量小，增进数据的一致性。

根据约束程度从低到高有：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）等等。

  • 1NF 要求所有属性都不可再分解。
  • 2NF 要求不存在部分依赖。
  • 3NF 要求不存在传递依赖。

反范式，顾名思义，与范式的目标正好相反。范式的目标是消除冗余；反范式的目标是冗余以提高查询效率。

范式并非越严格越好，现代数据库设计，一般最多满足 3NF。范式越高意味着表的划分更细，一个数据库中需要的表也就越多，用户不得不将原本相关联的数据分摊到多个表中。当用户同时需要这些数据时只能通过关联表的形式将数据重新合并在一起。同时把多个表联接在一起的花费是巨大的，尤其是当需要连接的两张或者多张表数据非常庞大的时候，表连接操作几乎是一个噩梦，这严重地降低了系统运行性能。因此，有时为了提高查询效率，有必要适当的冗余数据，以达到空间换时间的目的——这就是“反范式”。

:::

:::details 细节

第一范式 (1NF)

1NF 要求所有属性都不可再分解。

第二范式 (2NF)

2NF 要求记录有唯一标识，即实体的唯一性，即不存在部分依赖。

假设有一张 student 表，结构如下：

1
2
-- 学生表
student（学号、课程号、姓名、学分、成绩）

举例来说，现有一张 student 表，具有学号、课程号、姓名、学分等字段。从中可以看出，表中包含了学生信息和课程信息。由于非主键字段必须依赖主键，这里学分依赖课程号，姓名依赖学号，所以不符合 2NF。

不符合 2NF 可能会存在的问题：

  • 数据冗余 - 每条记录都含有相同信息。
  • 删除异常 - 删除所有学生成绩，就把课程信息全删除了。
  • 插入异常 - 学生未选课，无法记录进数据库。
  • 更新异常 - 调整课程学分，所有行都调整。

根据 2NF 可以拆分如下：

1
2
3
4
5
6
-- 学生表
student（学号、姓名）
-- 课程表
course（课程号、学分）
-- 学生课程关系表
student_course（学号、课程号、成绩）

第三范式 (3NF)

如果一个关系属于第二范式，并且在两个（或多个）非主键属性之间不存在函数依赖（非主键属性之间的函数依赖也称为传递依赖），那么这个关系属于第三范式。

3NF 是对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖。

假设有一张 student 表，结构如下：

1
2
-- 学生表
student（学号、姓名、年龄、班级号、班主任）

上表属于第二范式，因为主键由单个属性组成（学号）。

因为存在依赖传递：（学号） → （学生）→（所在班级） → （班主任） 。

可能会存在问题：

  • 数据冗余 - 有重复值；
  • 更新异常 - 有重复的冗余信息，修改时需要同时修改多条记录，否则会出现数据不一致的情况 。

可以基于 3NF 拆解：

1
2
student（学号、姓名、年龄、所在班级号）
class（班级号、班主任）

:::

【基础】为什么不推荐使用存储过程？

:::details 要点

存储过程的优点：

  • 执行效率高：一次编译多次使用。
  • 安全性强：在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。
  • 可复用：将代码封装，可以提高代码复用。
  • 性能好
    • 由于是预先编译，因此具有很高的性能。
    • 一个存储过程替代大量 T_SQL 语句 ，可以降低网络通信量，提高通信速率。

存储过程的缺点：

  • 可移植性差：存储过程不能跨数据库移植。由于不同数据库的存储过程语法几乎都不一样，十分难以维护（不通用）。
  • 调试困难：只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。
  • 版本管理困难：比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
  • 不适合高并发的场景：高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。

_综上，存储过程的优缺点都非常突出，是否使用一定要慎重，需要根据具体应用场景来权衡_。

:::

MySQL CRUD

扩展阅读：

【中级】如何避免重复插入数据？

:::details 要点

在 mysql 中，当存在主键冲突或唯一键冲突的情况下，根据插入策略不同，一般有以下三种避免方法：

  • INSERT IGNORE INTO：若无则插入，若有则忽略
  • REPLACE INTO：若无则插入，若有则先删除后插入
  • INSERT INTO ... ON DUPLICATE KEY UPDATE：若无则插入，若有则更新

下面结合示例来说明三种方式的效果。

下面是示例的初始化准备：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 建表
CREATE TABLE `user` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` VARCHAR(255) NOT NULL COMMENT '名称',
  `age` INT(3) DEFAULT '0' COMMENT '年龄',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name`(`name`)
) DEFAULT CHARSET = utf8mb4;

-- 测试数据
INSERT INTO `user`
VALUES (1, '刘备', 30);
INSERT INTO `user`
VALUES (2, '关羽', 28);

INSERT IGNORE INTO

INSERT IGNORE INTO 会根据主键或者唯一键判断，忽略数据库中已经存在的数据：

  • 若数据库没有该条数据，就插入为新的数据，跟普通的 INSERT INTO 一样
  • 若数据库有该条数据，就忽略这条插入语句，不执行插入操作
1
2
3
4
5
6
7
8
9
10
11
INSERT IGNORE INTO user (name, age)
VALUES ('关羽', 29), ('张飞', 25);

-- 最终数据
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 刘备   |   30 |
|  2 | 关羽   |   28 |
|  3 | 张飞   |   25 |
+----+--------+------+

REPLACE INTO

REPLACE INTO 会根据主键或者唯一键判断：

  • 若表中已存在该数据，则先删除此行数据，然后插入新的数据，相当于 delete + insert
  • 若表中不存在该数据，则直接插入新数据，跟普通的 insert into 一样
1
2
3
4
5
6
7
8
9
10
11
12
REPLACE INTO user(id, name, age)
VALUES (2,  '关羽', 29), (4,  '赵云', 22);

-- 最终数据
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 刘备   |   30 |
|  2 | 关羽   |   29 |
|  3 | 张飞   |   25 |
|  4 | 赵云   |   22 |
+----+--------+------+

INSERT … ON DUPLICATE KEY UPDATE

INSERT ... ON DUPLICATE KEY UPDATE 会根据主键或者唯一键判断：

  • 若数据库已有该数据，则直接更新原数据，相当于 UPDATE
  • 若数据库没有该数据，则插入为新的数据，相当于 INSERT
1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT INTO user(id, name, age)
VALUES (2, '关羽', 27)
ON DUPLICATE KEY UPDATE name=values(name), age=values(age);

-- 最终数据
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 刘备   |   30 |
|  2 | 关羽   |   27 |
|  3 | 张飞   |   25 |
|  4 | 赵云   |   22 |
+----+--------+------+

:::

【基础】EXISTS 和 IN 有什么区别？

:::details 要点

EXISTS 和 IN 区别如下：

  • 功能
    • EXISTS 用于判断子查询的结果集是否为空。
    • IN 用于判断某个值是否在指定的集合中。
  • 性能
    • EXISTS 先外后内 - 先对外表进行循环查询，再将查询结果放入 EXISTS 的子查询中进行条件比较，一旦找到匹配记录，则终止内表子查询。
    • IN 先内后外 - 先查询内表，将内表的查询结果作为条件，提供给外表查询语句进行比较。
  • 应用
    • 如果查询的两个表大小相当，那么 EXISTS 和 IN 差别不大。
    • EXISTS 适合外表小而内表大的场景。
    • IN 适合外表大而内表小的场景。

:::

:::details 细节

EXISTS 和 IN 的对比示例如下：

1
2
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc)

当 A 小于 B 时，用 EXISTS。因为 EXISTS 的实现，相当于外表循环，实现的逻辑类似于：

1
2
3
for i in A
    for j in B
        if j.cc == i.cc then ...

当 B 小于 A 时用 IN，因为实现的逻辑类似于：

1
2
3
for i in B
    for j in A
        if j.cc == i.cc then ...

哪个表小就用哪个表来驱动，A 表小就用 EXISTS，B 表小就用 IN；如果两个表大小相当，则使用 EXISTS 和 IN 的区别不大。

:::

【基础】UNION 和 UNION ALL 有什么区别？

:::details 要点

UNION 和 UNION ALL 都是将两个结果集合并为一个，两个要联合的 SQL 语句字段个数必须一样，而且字段类型要“相容”（一致）。

  • UNION 需要进行去重扫描，因此消息较低；而 UNION ALL 不会进行去重。
  • UNION 会按照字段的顺序进行排序；而 UNION ALL 只是简单的将两个结果合并就返回。

:::

【基础】JOIN 有哪些类型？

:::details 要点

**在 SELECT, UPDATE 和 DELETE 语句中，“连接”可以用于联合多表查询。连接使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE**。

连接可以替换子查询，并且一般比子查询的效率更快。

JOIN 有以下类型：

  • 内连接 - 内连接又称等值连接，用于获取两个表中字段匹配关系的记录，使用 INNER JOIN 关键字。在没有条件语句的情况下返回笛卡尔积。
    • 笛卡尔积 - “笛卡尔积”也称为交叉连接（CROSS JOIN），它的作用就是可以把任意表进行连接，即使这两张表不相关。
    • 自连接（=） - “自连接（=）”可以看成内连接的一种，只是连接的表是自身而已。
    • 自然连接（NATURAL JOIN） - “自然连接”会自动连接所有同名列。自然连接使用 NATURAL JOIN 关键字。
  • 外连接
    • 左连接（LEFT JOIN） - “左外连接”会获取左表所有记录，即使右表没有对应匹配的记录。左外连接使用 LEFT JOIN 关键字。
    • 右连接（RIGHT JOIN） - “右外连接”会获取右表所有记录，即使左表没有对应匹配的记录。右外连接使用 RIGHT JOIN 关键字。

SQL JOIN

:::

【中级】为什么不推荐多表 JOIN？

扩展阅读：https://www.cnblogs.com/eiffelzero/p/18608160

:::details 要点

《阿里巴巴 Java 开发手册》 中强制要求超过三个表禁止 join。这是为什么呢？

主要原因如下：

  • 性能问题
    • 查询效率低：当涉及多个表进行 JOIN 操作时，MySQL 需要执行多次扫描，尤其是在没有合适索引支持的情况下，性能可能会大幅下降。每增加一个表的 JOIN，查询的复杂度呈指数增长。
    • 临时表的创建：MySQL 在执行复杂的多表 JOIN 时，通常会创建临时表来存储中间结果。如果数据量很大，临时表可能会溢出到磁盘，导致磁盘 I/O 操作增加，从而显著影响查询性能。
  • 索引的作用有限
    • 在多表 JOIN 的操作中，虽然每个表可以使用索引加速查询，但是当涉及到多个表的连接时，MySQL 必须在这些表之间执行 JOIN 操作，这时索引的效果会大大降低。特别是在没有合适索引的情况下，JOIN 查询会导致全表扫描，极大地降低了查询效率。
  • 数据冗余
    • 在多表 JOIN 时，如果一个表中的一行数据与另一个表中的多行数据进行匹配，结果会产生数据冗余。例如，假设有两个表：A 和 B，A 中有 10 条记录，B 中有 5 条记录。如果在 A 和 B 上做 JOIN 操作，且匹配条件满足 2 条记录，那么最终的结果会有 20 条记录（10 * 2）。这会导致数据量急剧增加，浪费存储空间。
  • 可读性和可维护性
    • 多表 JOIN 的 SQL 查询通常比较复杂，尤其是当涉及多个表、多个连接条件以及嵌套查询时，查询语句的可读性会下降，增加了维护的难度。
    • 复杂的查询可能让开发者和运维人员难以理解和优化，从而增加了错误的风险。
  • 可能引发死锁
    • 在进行多个表 JOIN 操作时，如果涉及到多张表的锁定，可能会导致死锁。特别是在高并发的环境下，频繁执行 JOIN 操作容易导致多个事务之间相互等待，最终导致死锁问题。
  • 优化器的作用有限
    • MySQL 的优化器对多表 JOIN 的优化能力相对有限，尤其在处理非常复杂的查询时，可能无法有效选择最优的执行计划，从而导致性能瓶颈。
    • 虽然 MySQL 使用了 查询缓存 和 索引优化，但对于多表 JOIN 的优化仍然受到很多限制，导致性能不如预期。

:::

【中级】DELETE、DROP 和 TRUNCATE 有什么区别？

:::details 要点

  • DROP 删除数据表，包括数据和结构。在 InnoDB 中，表数据存于 .ibd 文件；表结构元数据存于 .frm 文件。DROP 本质上是就是直接删除 .ibd 和 .frm 文件。
  • DELETE 删除数据，但保留表结构。执行 DELETE 后，空间大小不会立刻变化。这是因为，DLETE 操作实际上只是标记，被写入 biglog、redo log 和 undo log。
  • TRUNCATE 会删除全部表数据，且不会记录日志，因此无法回滚。TRUNCATE 执行后，自增主键重新从 1 开始。

:::

【中级】哪种 COUNT 性能最好？

:::details 要点

先说结论：按照效率排序的话，COUNT（字段） < COUNT（主键 id) < COUNT(1) ≈ COUNT(*)。推荐采用 COUNT(*) 。

对于 COUNT（主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于 COUNT(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

单看这两个用法的差别的话，你能对比出来，COUNT(1) 执行得要比 COUNT（主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

对于 COUNT（字段） 来说：

  • 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
  • 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

但是 COUNT(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。COUNT(*) 肯定不是 null，按行累加。

:::

:::details 细节

InnoDB 和 MyISAM 的 count(*) 实现方式有什么区别？

不同的 MySQL 引擎中，COUNT(*) 有不同的实现方式：

  • MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 COUNT(*) 的时候会直接返回这个数，效率很高；
  • 而 InnoDB 引擎就麻烦了，它执行 COUNT(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

为什么 InnoDB 不跟 MyISAM 一样，也维护一个计数器？

因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 COUNT(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。

  • MyISAM 表虽然 COUNT(*) 很快，但是不支持事务；
  • show table status 命令虽然返回很快，但是不准确；
  • InnoDB 表直接 COUNT(*) 会遍历全表，虽然结果准确，但会导致性能问题。

如何优化查询计数？

  • 可以使用 Redis 保存计数，但存在数据丢失和逻辑不一致的问题。
  • 可以使用数据库其他表保存计数，利用事务的原子性和隔离性，可以避免数据丢失和逻辑不一致的问题。

:::

MySQL 数据类型

扩展阅读：

【基础】CHAR 和 VARCHAR 的区别是什么？

:::details 要点

CHAR 和 VARCHAR 的主要区别在于：CHAR 是定长字符串，VARCHAR 是变长字符串。

  • 长度限制
    • CHAR(M) 和 VARCHAR(M) 的 M 都代表能够保存的字符数的最大值，无论是字母、数字还是中文，每个都只占用一个字符。
  • 占用空间
    • CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；
    • VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。
      • 字符长度超过 255，使用 2 个字节
      • 字符长度未超过 255，使用 1 个字节
  • 应用
  • CHAR 适合存储长度较短或长度固定多的字符串。例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码；
  • VARCHAR 适合存储长度不确定的字符串。例如用户昵称、文章标题等。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

:::

【基础】金额数据用什么类型存储？

:::details 要点

MySQL 中有 3 种类型可以表示浮点数，分别是 FLOAT、DOUBLE 和 DECIMAL。

采用 FLOAT 和 DOUBLE 类型会丢失精度。数据的精确度取决于分配给每种数据类型的存储长度。由于计算机只能存储二进制，所以浮点型数据在存储的时候，必须转化成二进制。

  • 单精度类型 FLOAT 存储空间为 4 字节，即 32 位。
  • 双精度类型 DOUBLE 存储空间为 8 字节，即 64 位。

如果存储的数据转为二进制后，超过存储的位数，数据就被截断，因此存在丢失精度的可能。

更重要的是，从 MySQL 8.0.17 版本开始，当创建表用到类型 Float 或 Double 时，会抛出下面的警告：MySQL 提醒用户不该用上述浮点类型，甚至提醒将在之后版本中废弃浮点类型。

1
Specifying number of digits for floating point data types is deprecated and will be removed in a future release

【示例】丢失精度案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建表
CREATE TABLE `test` (
  `value` FLOAT(10,2) DEFAULT NULL
);

mysql> insert into test value (131072.32);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+-----------+
| value     |
+-----------+
| 131072.31 |
+-----------+
1 row in set (0.02 sec)

说明：示例中，使用 FLOAT 类型，明明保留了两位小数。但是写入的数据却从 131072.32 变成了 131072.31 。

DECIMAL 类型是 MySQL 官方唯一指定能精确存储的类型。因此，对于不允许丢失精度的场景（如金额数据），可以使用 DECIMAL 类型。

然而，在海量并发的互联网业务中使用，金额字段的设计并不推荐使用 DECIMAL 类型，而更推荐使用 BIGINT 整型类型。这里会用到一个巧思：将资金类型的数据用分为单位存储，而不是用元为单位存储。如 1 元在数据库中用整型类型 100 存储。

为什么更推荐用 BIGINT 存储金钱数据？因为 DECIMAL 是个变长字段，若要定义金额字段，则定义为 DECIMAL(8,2) 是远远不够的。这样只能表示存储最大值为 999999.99，百万级的资金存储。用户的金额至少要存储百亿的字段，而统计局的 GDP 金额字段则可能达到数十万亿级别。用类型 DECIMAL 定义，不好统一。另外重要的是，类型 DECIMAL 是通过二进制实现的一种编码方式，计算效率远不如整型来的高效。因此，推荐使用 BIGINT 来存储金额相关的字段。

扩展阅读：MySQL 如何选择 float, double, decimal

:::

【基础】如何存储 emoji 😃？

:::details 要点

在表结构设计中，除了将列定义为 CHAR 和 VARCHAR 用以存储字符以外，还需要额外定义字符对应的字符集，因为每种字符在不同字符集编码下，对应着不同的二进制值。常见的字符集有 gbk、utf8，通常推荐把默认字符集设置为 utf8。

随着移动互联网的飞速发展，**推荐把 MySQL 的默认字符集设置为 utf8mb4**，否则，某些 emoji 表情字符无法在 UTF8 字符集下存储。

【示例】设置表的字符集为 utf8mb4

1
ALTER TABLE test CHARSET utf8mb4;

注意：上述修改只是将表的字符集修改为 utf8mb4，下次新增列时，若不显式地指定字符集，新列的字符集会变更为 utf8mb4，但对于已经存在的列，其默认字符集并不做修改。

【示例】设置表的默认字符集为 utf8mb4

正确设置 utf8mb4 字符集方法如下：

1
ALTER TABLE test CONVERT TO CHARSET utf8mb4;

:::

【基础】时间数据选择 DATETIME 还是 TIMESTAMP？

:::details 要点

表结构设计时，对时间字段的存储，通常会有 3 种选择：DATETIME、TIMESTAMP、INT。

DATETIME、TIMESTAMP、INT 数据表示范围：

  • DATETIME 占用 8 个字节，可表示范围为：1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999
  • TIMESTAMP 占用 4 个字节，可表示范围为：'1970-01-01 00:00:01.000000' UTC 到 '2038-01-09 03:14:07.999999' UTC。表示从 1970-01-01 00:00:00 到现在的毫秒数。
  • INT 类型就是直接存储 ‘1970-01-01 00:00:00’ 到现在的毫秒数，本质和 TIMESTAMP 一样，因此用 INT 不如直接使用 TIMESTAMP。

此外，TIMESTAMP 还存在潜在的性能问题。虽然从毫秒数转换到类型 TIMESTAMP 本身需要的 CPU 指令并不多，这并不会带来直接的性能问题。但是如果使用默认的操作系统时区，则每次通过时区计算时间时，要调用操作系统底层系统函数 __tz_convert()，而这个函数需要额外的加锁操作，以确保这时操作系统时区没有修改。所以，当大规模并发访问时，由于热点资源竞争，会产生两个问题。

  • 性能不如 DATETIME： DATETIME 不存在时区转化问题。
  • 性能抖动： 海量并发时，存在性能抖动问题。

为了优化 TIMESTAMP 的使用，强烈建议使用显式的时区，而不是操作系统时区。比如在配置文件中显示地设置时区，而不要使用系统时区

综上，由于 TIMESTAMP 存在时间上限和潜在性能问题，所以推荐使用 DATETIME 类型来存储时间字段。

:::

MySQL 存储

扩展阅读：

【中级】MySQL 支持哪些存储引擎？

:::details 要点

存储引擎层负责数据的存储和提取。MySQL 的存储引擎采用了插拔式架构，可以根据需要替换。

MySQL 内置了以下存储引擎：

  • InnoDB - InnoDB 是 MySQL 5.5 版本以后的默认存储引擎。
    • 优点：支持事务，支持行级锁，支持外键约束等，并发性能不错且支持自动故障恢复。
  • MyISAM - MyISAM 是 MySQL 5.5 版本以前的默认存储引擎。
    • 优点：速度快，占用资源少。
    • 缺点：不支持事务，不支持行级锁，不支持外键约束，也不支持自动故障恢复功能。
  • Memory - 使用系统内存作为存储介质，以便得到更快的响应速度。不过，如果 mysqld 进程崩溃，则会导致所有的数据丢失。因此，Memory 引擎常用于临时表。
  • NDB - 也被称为 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。
  • Archive - Archive 存储引擎有很好的压缩机制，非常适合用于归档数据。
    • Archive 存储引擎只支持 INSERT 和 SELECT 操作。
    • Archive 存储引擎采用 zlib 算法压缩数据，压缩比可达到 1: 10。
  • CSV - 可以将 CSV 文件作为 MySQL 的表来处理，但这种表不支持索引。

:::

【中级】InnoDB 和 MyISAM 有哪些差异？

:::details 要点

对比项 MyISAM InnoDB
外键 不支持 支持
事务 不支持 支持四种事务隔离级别
锁粒度 支持表级锁 支持表级锁、行级锁
索引 采用 B+ 树索引（非聚簇索引） 采用 B+ 树索引（聚簇索引）
表空间 小 大
关注点 性能 事务
计数器 维护了计数器，SELECT COUNT(*) 效率为 O(1) 没有维护计数器，需要全表扫描
自动故障恢复 不支持 支持（依赖于 redo log）

:::

【中级】如何选择存储引擎？

:::details 要点

  • 大多数情况下，使用默认的 InnoDB 就够了。如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 就是比较靠前的选择了。
  • 如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
  • 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中。MySQL 中使用该引擎作为临时表，存放查询的中间结果。
  • 如果存储归档数据，可以使用 ARCHIVE 引擎。

使用哪一种引擎可以根据需要灵活选择，因为存储引擎是基于表的，所以一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。

:::

【中级】MySQL 有哪些物理存储文件？

:::details 要点

MySQL 不同存储引擎的物理存储文件是不一样的。

InnoDB 的物理文件结构为：

  • .frm 文件：与表相关的元数据信息都存放在 frm 文件，包括表结构的定义信息等。
  • .ibd 文件或 .ibdata 文件： 这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。
    • 独享表空间存储方式使用.ibd文件，并且每个表一个.ibd文件
    • 共享表空间存储方式使用.ibdata文件，所有表共同使用一个.ibdata文件（或多个，可自己配置）

MyISAM 的物理文件结构为：

  • .frm文件：与表相关的元数据信息都存放在 frm 文件，包括表结构的定义信息等。
  • .MYD (MYData) 文件：MyISAM 存储引擎专用，用于存储 MyISAM 表的数据。
  • .MYI (MYIndex) 文件：MyISAM 存储引擎专用，用于存储 MyISAM 表的索引相关信息。

:::

【中级】什么是 Buffer Pool？

:::details 要点

Buffer Pool（缓冲池）是 MySQL InnoDB 存储引擎的核心组件之一，它是数据库系统中的内存缓存区域，主要用于缓存表和索引的数据。

主要作用：

  1. 减少磁盘 I/O：将频繁访问的数据页缓存在内存中，避免每次查询都要从磁盘读取
  2. 提高查询性能：内存访问速度远快于磁盘访问
  3. 写缓冲：对数据的修改先在内存中进行，再通过后台线程定期刷新到磁盘

工作原理：

  • Buffer Pool 以页 (page) 为单位存储数据，默认每页 16KB
  • 使用 LRU （最近最少使用）算法管理内存页
  • 包含”年轻代”和”老年代”两个区域，防止全表扫描污染缓存

:::

【中级】什么是 Change Buffer？

:::details 要点

Change Buffer 是 InnoDB 存储引擎中的一种关键优化机制，主要用于提高非唯一二级索引的写操作性能。

Change Buffer 是一种特殊的内存数据结构，用于缓存对非唯一二级索引页的修改操作（INSERT、UPDATE、DELETE），当这些索引页不在缓冲池 (Buffer Pool) 中时，避免立即从磁盘读取索引页。

工作原理：

  • 写操作发生时：当修改非唯一二级索引的数据时，InnoDB 会检查目标索引页是否在 Buffer Pool 中。

    • 如果在：直接修改
    • 如果不在：将修改操作记录到 Change Buffer

  • 后续读取时：当需要读取该索引页时，InnoDB 会将 Change Buffer 中的修改与从磁盘读取的原始页合并。

  • 后台合并：有专门的线程定期将 Change Buffer 中的变更合并到磁盘上的索引页。

优势

  • 减少磁盘 I/O：避免为写入操作立即读取索引页
  • 提高吞吐量：多个变更可以合并执行
  • 减少随机 I/O：将随机写入转为顺序写入

适用场景：

  • 适用于写多读少的非唯一二级索引
  • 特别适合大量 DML 操作但索引不常被查询的业务场景

不适用场景：

  • 唯一索引（需要立即检查唯一性约束）
  • 索引被频繁查询（会导致频繁合并操作）

相关配置：

  • innodb_change_buffer_max_size：Change Buffer 最大占 Buffer Pool 的比例（默认 25%）
  • innodb_change_buffering：指定缓冲的变更类型（all/none/inserts/deletes 等）

:::

MySQL 日志

【基础】MySQL 有哪些类型的日志？

:::details 要点

MySQL 日志文件有很多，包括 ：

  • 错误日志（error log）：错误日志文件对 MySQL 的启动、运行、关闭过程进行了记录，能帮助定位 MySQL 问题。
  • 慢查询日志（slow query log）：慢查询日志是用来记录执行时间超过 long_query_time 这个变量定义的时长的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。
  • 一般查询日志（general log）：一般查询日志记录了所有对 MySQL 数据库请求的信息，无论请求是否正确执行。
  • 二进制日志（bin log）：关于二进制日志，它记录了数据库所有执行的 DDL 和 DML 语句（除了数据查询语句 select、show 等），以事件形式记录并保存在二进制文件中。

还有两个 InnoDB 存储引擎特有的日志文件：

  • 重做日志（redo log）：重做日志至关重要，因为它们记录了对于 InnoDB 存储引擎的事务日志。
  • 回滚日志（undo log）：回滚日志同样也是 InnoDB 引擎提供的日志，顾名思义，回滚日志的作用就是对数据进行回滚。当事务对数据库进行修改，InnoDB 引擎不仅会记录 redo log，还会生成对应的 undo log 日志；如果事务执行失败或调用了 rollback，导致事务需要回滚，就可以利用 undo log 中的信息将数据回滚到修改之前的样子。

:::

【基础】bin log 和 redo log 有什么区别？

:::details 要点

  • bin log 会记录所有与数据库有关的日志记录，包括 InnoDB、MyISAM 等存储引擎的日志；而 redo log 只记 InnoDB 存储引擎的日志。
  • 记录的内容不同，bin log 记录的是关于一个事务的具体操作内容，即该日志是逻辑日志。而 redo log 记录的是关于每个页（Page）的更改的物理情况。
  • 写入的时间不同，bin log 仅在事务提交前进行提交，也就是只写磁盘一次。而在事务进行的过程中，却不断有 redo ertry 被写入 redo log 中。
  • 写入的方式也不相同，redo log 是循环写入和擦除，bin log 是追加写入，不会覆盖已经写的文件。

:::

【基础】redo log 如何刷盘？

:::details 要点

redo log 的写入不是直接落到磁盘，而是在内存中设置了一片称之为 redo log buffer 的连续内存空间，也就是 redo 日志缓冲区。

在如下的一些情况中，log buffer 的数据会刷入磁盘：

  • log buffer 空间不足时：log buffer 的大小是有限的，如果不停的往这个有限大小的 log buffer 里塞入日志，很快它就会被填满。如果当前写入 log buffer 的 redo 日志量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。
  • 事务提交时：在事务提交时，为了保证持久性，会把 log buffer 中的日志全部刷到磁盘。注意，这时候，除了本事务的，可能还会刷入其它事务的日志。
  • 后台线程输入：有一个后台线程，大约每秒都会刷新一次log buffer中的redo log到磁盘。
  • 正常关闭服务器时
  • 触发 checkpoint 规则

重做日志缓存、重做日志文件都是以 块（block） 的方式进行保存的，称之为、重做日志块（redo log block）, 块的大小是固定的 512 字节。我们的 redo log 它是固定大小的，可以看作是一个逻辑上的 log group，由一定数量的 log block 组成。

它的写入方式是从头到尾开始写，写到末尾又回到开头循环写。

其中有两个标记位置：

write pos是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到磁盘。

当write_pos追上checkpoint时，表示 redo log 日志已经写满。这时候就不能接着往里写数据了，需要执行checkpoint规则腾出可写空间。

所谓的 checkpoint 规则，就是 checkpoint 触发后，将 buffer 中日志页都刷到磁盘。

:::

【中级】日志为什么要两阶段提交？

:::details 要点

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。

  1. 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。
    • 但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。
    • 然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
  2. 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

:::

【中级】什么是 WAL？

:::details 要点

WAL（Write-Ahead Logging）是一种数据库事务日志管理技术，确保在修改数据之前先将修改记录写入日志。它的关键点就是 先写日志，再写磁盘。

WAL 是一种通用技术，被广泛应用于各种数据库，但实现各有不同。在 InnoDB 中，redo log 就是 WAL 的实现。

大致流程为：

  • 事务开始时，修改记录到重做日志缓冲区。
  • 重做日志缓冲区的数据周期性刷新到磁盘上的 redo log 文件。
  • 事务提交时，确保 redo log 已写入磁盘，然后将数据页的修改写入数据文件。
  • 系统崩溃时，通过 redo log 重新应用未完成的事务，恢复数据库到一致状态。

::

【中级】什么是 Log Buffer？

:::details 要点

Log Buffer 用于缓冲 redo log 的写入，减少频繁刷盘 fsync 的开销，将多次写入优化为一次批量写入。

redo log 是 InnoDB 的重做日志，用于崩溃恢复，确保数据正确性。redo log 采用 WAL 机制：先写日志，再写磁盘数据，将随机写入转换为顺序写入。

Log Buffer 的刷盘时机

  • 事务提交时：事务产生的多条 redo log 会先缓存在 Log Buffer，提交时一次性写入文件（受配置参数控制）。
  • 容量触发：当 Log Buffer 超过总容量的一半（默认 16MB）时自动刷盘。
  • 后台线程：每隔 1 秒定时刷盘。

配置参数innodb_flush_log_at_trx_commit

  • 0：事务提交不刷盘，依赖后台线程每秒刷盘。性能最佳，但可能丢失 1 秒数据。
  • 1（默认）：事务提交时同步刷盘（写 OS cache 并调用 fsync）。数据最安全，性能最差。
  • 2：事务提交时仅写 OS cache，后台线程每秒调用 fsync。性能折中，服务器宕机可能丢失 1 秒数据。

:::

MySQL 复制

【中级】MySQL 如何实现主从同步？

:::details 要点

复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。一台主库的数据可以同步到多台从库上，从库本身也可以被配置成另外一台服务器的主库。主库和从库之间可以有多种不同的组合方式。

MySQL 复制采用主从同步，基于 binlog（二进制日志） 实现。其流程大致为：

  • 主库记录 DML/DDL 操作到 binlog。
  • 从库获取 binlog 并重放，保持数据同步。

MySQL 支持三种复制方式：同步、异步、半同步。下面是三种方式的对比：

模式 机制 优点 缺点
异步复制（默认） 主库不等待从库响应 高性能 数据一致性弱（可能丢失）
同步复制 主库等待所有从库确认 强一致性 性能差，延迟高
半同步复制 主库等待至少一个从库确认 平衡性能与一致性 比异步略慢

异步复制

MySQL 异步复制可以分为三个步骤，分别由三个线程完成：

  • binlog dump 线程 - 主库接收事务请求，更新数据，并即时响应客户端（不等待从库）。主库上有一个特殊的 binlog dump 线程，负责将主服务器上的数据更改写入 binlog 中。
  • I/O 线程 - 从库上有一个 I/O 线程，负责从主库上读取 binlog，并写入从库的中继日志（relay log）中。
  • SQL 线程 - 从库上有一个 SQL 线程，负责重放中继日志（relay log），更新从库数据。

需要注意的是，采用异步复制有丢失数据的风险，主库崩溃时，未同步的 binlog 可能丢失（弱一致性）。

同步复制

主库必须等待所有从库完成 binlog 同步后才响应客户端。

特点：

  • 数据强一致性（所有节点完全同步）
  • 性能极差（延迟高，吞吐量低）
  • 生产环境基本不使用

半同步复制

MySQL 5.7 引入了半同步复制：主库只需等待至少 N 个从库（可配置）确认即返回。

特点：

  • 性能与可靠性的平衡（比全同步快，比异步安全）。
  • 仅当主库和所有已确认从库同时崩溃时可能丢数据。

:::

【中级】如何处理 MySQL 主从同步延迟？

:::details 要点

主从延迟的常见解决方案

  • 二次查询（兜底策略）：从库查不到时，再查主库。缺点是：恶意查询可能导致主库压力增大。
  • 强制写后读走主库：写入后立即读的操作绑定走主库。缺点是：代码耦合，灵活性差。
  • 关键业务读写主库，非关键业务读写分离
  • 使用缓存：主库写入后同步缓存，查询优先查缓存。缺点是：引入缓存后，新增了一致性问题。
  • 提升从库配置：优化从库硬件（CPU、内存、磁盘等），提高同步效率。

MySQL 主从延迟的常见原因及优化方案

原因 优化方案
从库单线程复制 启用 并行复制（多线程同步）。
网络延迟 优化网络，缩短主从物理距离。
从库性能不足 升级硬件（CPU、内存、存储）。
长事务 减少主库长事务，优化 SQL。
从库数量过多 合理控制从库数量，避免主库同步压力过大。
从库查询负载高 增加从库实例，优化慢查询。

小结

  • 主从延迟 无法完全避免，只能优化降低延迟时间。
  • 业务层面应结合 缓存、读写分离策略、关键业务走主库 等方式综合解决。
  • 技术层面可优化 并行复制、网络、硬件 等。

:::

【中级】如何实现 MySQL 读写分离？

:::details 要点

读写分离的基本原理是：主服务器用来处理写操作以及实时性要求比较高的读操作，而从服务器用来处理读操作。

为何要读写分离？

  • 有效减少锁竞争 - 主服务器只负责写，从服务器只负责读，能够有效的避免由数据更新导致的行锁竞争，使得整个系统的查询性能得到极大的改善。
  • 提高查询吞吐量 - 通过一主多从的配置方式，可以将查询请求均匀的分散到多个数据副本，能够进一步的提升系统的处理能力。
  • 提升数据库可用性 - 使用多主多从的方式，不但能够提升系统的吞吐量，还能够提升数据库的可用性，可以达到在任何一个数据库宕机，甚至磁盘物理损坏的情况下仍然不影响系统的正常运行。

读写分离的实现是根据 SQL 语义分析，将读操作和写操作分别路由至主库与从库。

读写分离有两种实现方式：代码封装、中间件。以下是两种方案的对比：

方案 实现方式 优点 缺点
代码封装 业务层通过代理类路由读写请求（读走从库，写走主库）。 简单灵活，可定制化 - 适合业务特定需求 主从切换需修改配置并重启 - 多语言需重复开发
中间件 独立代理服务（如 MySQL-Proxy、ShardingSphere），客户端无感知。 屏蔽多语言差异，统一管理数据源 有额外维护成本，可能成为性能瓶颈

结论：代码封装适合简单架构，但扩展性差；中间件适合复杂架构，但需维护。

常见的读写分离中间件

  • MySQL-Proxy（官方）
  • Atlas（360）
  • ShardingSphere（Apache）
  • Mycat

:::

MySQL 分库分表

【中级】什么是分库分表？为何要分库分表？

:::details 要点

什么是分库分表？

分库分表是一种数据库水平拆分方案，用于解决单机数据库的存储瓶颈和性能瓶颈问题。

  • 分库：将数据分散到不同的数据库实例（如 DB1、DB2）。
  • 分表：将数据分散到同一数据库的不同表（如 order_1、order_2）。

为何要分库分表？

分库分表主要基于以下理由：

  • 并发连接 - 单库超过每秒 2000 个并发时，而一个健康的单库最好保持在每秒 1000 个并发左右，不要太大。
  • 磁盘容量 - 磁盘容量占满，会导致服务器不可用。
  • SQL 性能 - 单表数据量过大，会导致 SQL 执行效率低下。一般，单表超过 1000 万条数据，就可以考虑分表了。
# 分库分表前 分库分表后
并发支撑情况 MySQL 单机部署，扛不住高并发 MySQL 从单机到多机，能承受的并发增加了多倍
磁盘使用情况 MySQL 单机磁盘容量几乎撑满 拆分为多个库，数据库服务器磁盘使用率大大降低
SQL 执行性能 单表数据量太大，SQL 越跑越慢 单表数据量减少，SQL 执行效率明显提升

:::

【中级】分库分表有哪些策略？

:::details 要点

分库分表策略主要有两种：

  • 根据数值范围划分
  • 根据 Hash 划分
  • 路由表

数值范围路由

数值范围路由，就是根据 ID、时间范围 这类具有排序性的字段来进行划分。例如：用户 Id 为 1-9999 的记录分到第一个库，10000-20000 的分到第二个库，以此类推。

按这种策略划分出来的数据，具有数据连续性。

  • 优点：数据迁移很简单。
  • 缺点：容易产生热点问题，大量的流量都打在最新的数据上了。

Hash 路由

典型的 Hash 路由，如根据数值取模，当需要扩容时，一般以 2 的幂次方进行扩容（这样，扩容时迁移的数据量会小一些）。例如：用户 Id mod n，余数为 0 的记录放到第一个库，余数为 1 的放到第二个库，以此类推。

一般采用 预分区 的方式，提前根据 数据量 规划好 分区数，比如划分为 512 或 1024 张表，保证可支撑未来一段时间的 数据容量，再根据 负载情况 将 表 迁移到其他 数据库 中。扩容时通常采用 翻倍扩容，避免 数据映射 全部被 打乱，导致 全量迁移 的情况。

  • 优点：数据离散分布，不存在热点问题。
  • 缺点：数据迁移、扩容麻烦（之前的数据需要重新计算 hash 值重新分配到不同的库或表）。当节点数量变化时，如扩容或收缩节点，数据节点映射关系需要重新计算，会导致数据的 重新迁移。

路由表

这种策略，就是用一张独立的表记录路由信息。

  • 优点：简单、灵活，尤其是在扩容、迁移时，只需要迁移指定的数据，然后修改路由表即可。
  • 缺点：每次查询，必须先查路由表，增加了 IO 开销。并且，如果路由表本身太大，也会面临性能瓶颈，如果想对路由表再做分库分表，将出现死循环式的路由算法选择问题。

:::

【高级】分库分表存在哪些问题？

:::details 要点

分库分表主要存在以下问题：

  • 分布式 ID 问题
  • 分布式事务问题
  • 跨节点 Join 和聚合
  • 跨分片的排序分页

分布式 ID 问题

一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的 ID 无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得 ID，以便进行 SQL 路由。

分布式 ID 的解决方案详见：分布式 ID

分布式事务问题

跨库事务也是分布式的数据库集群要面对的棘手事情。 合理采用分表，可以在降低单表数据量的情况下，尽量使用本地事务，善于使用同库不同表可有效避免分布式事务带来的麻烦。在不能避免跨库事务的场景，有些业务仍然需要保持事务的一致性。 而基于 XA 的分布式事务由于在并发度高的场景中性能无法满足需要，并未被互联网巨头大规模使用，他们大多采用最终一致性的柔性事务代替强一致事务。

分布式事务的解决方案详见：分布式事务

跨节点 Join 和聚合

分库分表后，无法直接跨节点 join 、count、order by、group by 以及聚合。

针对这类问题，普遍做法是二次查询。

  • 在第一次查询时，获取各个节点上的结果。

  • 在程序中将这些结果进行合并、筛选。

跨分片的排序分页

一般来讲，分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。如下图所示：

img

上面图中所描述的只是最简单的一种情况（取第一页数据），看起来对性能的影响并不大。但是，如果想取出第 10 页数据，情况又将变得复杂很多，如下图所示：

img

有些读者可能并不太理解，为什么不能像获取第一页数据那样简单处理（排序取出前 10 条再合并、排序）。其实并不难理解，因为各分片节点中的数据可能是随机的，为了排序的准确性，必须把所有分片节点的前 N 页数据都排序好后做合并，最后再进行整体的排序。很显然，这样的操作是比较消耗资源的，用户越往后翻页，系统性能将会越差。

那如何解决分库情况下的分页问题呢？有以下几种办法：

如果是在前台应用提供分页，则限定用户只能看前面 n 页，这个限制在业务上也是合理的，一般看后面的分页意义不大（如果一定要看，可以要求用户缩小范围重新查询）。

如果是后台批处理任务要求分批获取数据，则可以加大 page size，比如每次获取 5000 条记录，有效减少分页数（当然离线访问一般走备库，避免冲击主库）。

分库设计时，一般还有配套大数据平台汇总所有分库的记录，有些分页查询可以考虑走大数据平台。

:::

【高级】如何实现迁库和扩容？

:::details 要点

停机迁移/扩容（不推荐）

停机迁移/扩容是最暴力、最简单的迁移、扩容方案。

img

停机迁移/扩容流程：

  1. 预估停服时间，发布停服公告；停服，不允许数据访问。
  2. 编写临时的数据导入程序，从老数据库中读取数据。
  3. 将数据写入中间件。
  4. 中间件根据分片规则，将数据分发到分库（分表）中。
  5. 应用程序修改配置，重启。

停机迁移/扩容方案分析：

  • 优点：简单、无数据一致性问题。
  • 缺点：
    • 停服时间长（数据量大时可能需数小时）。
    • 风险高，失败后难以回滚。

结论：代价过高，不推荐使用。

双写迁移

双写迁移方案核心思想：

  • 新旧库同时写入，通过开关控制读写状态（只写旧库、只写新库、双写）。
  • 逐步切换读请求到新库，确保数据一致性。

双写迁移方案关键步骤：

  1. 双写阶段：先写旧库，再写新库，以旧库结果为准。记录旧库成功但新库失败的日志，用于补偿。
  2. 数据校验：运行对比程序，检查新旧库数据差异并修复。
  3. 灰度切换读请求：逐步将读流量切至新库，观察稳定性。
  4. 最终切换：读写全部切至新库，清理旧库冗余数据。

img

双写迁移流程：

  1. 修改应用程序配置，将数据同时写入老数据库和中间件。这就是所谓的双写，同时写俩库，老库和新库。
  2. 编写临时程序，读取老数据库。
  3. 将数据写入中间件。如果数据不存在，直接写入；如果数据存在，比较时间戳，只允许新数据覆盖老数据。
  4. 导入数据后，有可能数据还是存在不一致，那么就对数据进行校验，比对新老库的每条数据。如果存在差异，针对差异数据，执行（3）。循环（3）、（4）步骤，直至数据完全一致。
  5. 修改应用程序配置，将数据只写入中间件。
  6. 中间件根据分片规则，将数据分发到分库（分表）中。

双写迁移方案分析：

优点：

  • 无需停服，业务影响小。
  • 可灰度验证，风险可控。

缺点：

  • 实现复杂，需处理双写一致性和补偿逻辑。

主从替换

生产环境的数据库，为了保证高可用，一般会采用主从架构。主库支持读写操作，从库支持读操作。

img

由于主从节点数据一致，所以将从库升级为主节点，并修改分片配置，将从节点作为分库之一，就实现了扩容。

img

主从替换方案流程：

  1. 解除主从关系，从库升级为主库。
  2. 应用程序，修改配置，读写通过中间件。
  3. 分库分表中间，修改分片配置。将数据按照新的规则分发。
  4. 编写临时程序，清理冗余数据。比如：原来是一个单库，数据量为 400 万。从节点升级为分库之一后，每个分库都有 400 万数据，其中 200 万是冗余数据。清理完后，进行数据校验。
  5. 为每个分库添加新的从库，保证高可用。

主从替换方案分析：

  • 无需停机，无需全量数据迁移。
  • 利用现有从库资源，节省成本。

三种方案对比

方案 适用场景 优点 缺点
停机迁移 小规模数据，容忍停服 简单，无一致性问题 停服时间长，风险高
双写迁移 大规模数据，要求高可用 无停服，灰度可控 复杂，需补偿机制
主从替换 已有主从架构 无需迁移数据，快速扩容 依赖现有从库，清理冗余复杂

推荐选择：

  • 优先双写迁移：适合大多数业务，平衡风险与复杂度。
  • 主从升级：适合已有主从且数据量适中的场景。
  • 避免停机迁移：除非数据量极小且可接受停服。

:::

MySQL 优化

【基础】如何发现慢 SQL？

:::details 要点

慢 SQL 的监控主要通过两个途径：

  • 慢查询日志：开启 MySQL 的慢查询日志，再通过一些工具比如 mysqldumpslow 去分析对应的慢查询日志，当然现在一般的云厂商都提供了可视化的平台。
  • 服务监控：可以在业务的基建中加入对慢 SQL 的监控，常见的方案有字节码插桩、连接池扩展、ORM 框架过程，对服务运行中的慢 SQL 进行监控和告警。

:::

【基础】什么是执行计划？

:::details 要点

“执行计划”是对 SQL 查询语句在数据库中执行过程的描述。 如果要分析某条 SQL 的性能问题，通常需要先查看 SQL 的执行计划，排查每一步 SQL 执行是否存在问题。

很多数据库都支持执行计划，MySQL 也不例外。在 MySQL 中，用户可以通过 EXPLAIN 命令查看优化器针对指定 SQL 生成的逻辑执行计划。

【示例】MySQL 执行计划示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain select * from user_info where id = 2
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

执行计划返回结果参数说明：

  • id - SELECT 查询的标识符。每个 SELECT 都会自动分配一个唯一的标识符。
  • select_type - SELECT 查询的类型。
    • SIMPLE - 表示此查询不包含 UNION 查询或子查询。
    • PRIMARY - 表示此查询是最外层的查询。
    • UNION - 表示此查询是 UNION 的第二或随后的查询。
    • DEPENDENT UNION - UNION 中的第二个或后面的查询语句，取决于外面的查询。
    • UNION RESULT - UNION 的结果。
    • SUBQUERY - 子查询中的第一个 SELECT。
    • DEPENDENT SUBQUERY - 子查询中的第一个 SELECT, 取决于外面的查询。即子查询依赖于外层查询的结果。
  • table - 查询的是哪个表，如果给表起别名了，则显示别名。
  • partitions - 匹配的分区。
  • type - 表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标。执行效率由高到低依次为：
    • system/const - 表中只有一行数据匹配。此时根据索引查询一次就能找到对应的数据。如果是 B+ 树索引，我们知道此时索引构造成了多个层级的树，当查询的索引在树的底层时，查询效率就越低。const 表示此时索引在第一层，只需访问一层便能得到数据。
    • eq_ref - 使用唯一索引扫描。常见于多表连接中使用主键和唯一索引作为关联条件。
    • ref - 非唯一索引扫描。还可见于唯一索引最左原则匹配扫描。
    • range - 索引范围扫描。比如 <，>，between 等操作。
    • index - 索引全表扫描。此时遍历整个索引树。
    • ALL - 表示全表扫描。需要遍历全表来找到对应的行。
  • possible_keys - 此次查询中可能选用的索引。
  • key - 此次查询中实际使用的索引。如果这一项为 NULL，说明没有使用索引。
  • ref - 哪个字段或常数与 key 一起被使用。
  • rows - 显示此查询一共扫描了多少行，这个是一个估计值。
  • filtered - 表示此查询条件所过滤的数据的百分比。
  • extra - 额外的信息。
    • Using index - 使用覆盖索引，无需回表。
    • Using where - 服务器在存储引擎检索后过滤。
    • Using temporary - 使用临时表。MySQL 在对查询结果排序时使用临时表，常见于排序 ORDER BY 和分组查询 GROUP BY。效率低，要避免这种问题的出现。
    • Using filesort - 额外排序。无法利用索引完成排序时，就不得不将查询匹配数据进行排序，甚至可能会通过文件进行排序，效率很低。
    • Using join buffer - 使用连接缓冲

更多内容请参考：MySQL 性能优化神器 Explain 使用分析

:::

【基础】如何分析执行计划？

:::details 要点

执行计划关键字段

  • type - 按性能从高到低排序：system > const > eq_ref > ref > range > index > ALL。目标应尽可能避免 ALL（全表扫描）。
  • possible_keys - 可能使用的索引。
  • key - 实际使用的索引。
  • rows - 预估需要检查的行数，值越小越好。
  • Extra - 包含重要补充信息。

执行计划分析步骤：

  1. 查看 type - 确保访问类型为 const 、 eq_ref 、 ref 或 range ，避免 ALL 。
  2. 查看 key - 确认是否使用了合适的索引。若 key 为 NULL 表示未使用索引，需优化。
  3. 查看 rows - 扫描的行数越少越好。
  4. 查看 Extra - 避免 Using temporary（使用临时表） 和 Using filesort （额外排序）。

对应优化：

  • 如果 type 为 ALL，考虑为 WHERE 条件列添加索引。
  • 如果 Extra 包含 Using filesort ，优化 ORDER BY 或 GROUP BY 。
  • 如果 rows 过大，检查索引是否有效。

:::

【中级】如何优化 SQL？

:::details 要点

避免不必要的列

这个是老生常谈，但还是经常会出的情况，SQL 查询的时候，应该只查询需要的列，而不要包含额外的列，像slect * 这种写法应该尽量避免。

分页优化

在数据量比较大，分页比较深的情况下，需要考虑分页的优化。

例如：

1
select * from table where type = 2 and level = 9 order by id asc limit 190289,10;

优化方案：

  • 延迟关联

先通过 where 条件提取出主键，在将该表与原数据表关联，通过主键 id 提取数据行，而不是通过原来的二级索引提取数据行

例如：

1
2
3
select a.* from table a,
 (select id from table where type = 2 and level = 9 order by id asc limit 190289,10 ) b
 where a.id = b.id
  • 书签方式

书签方式就是找到 limit 第一个参数对应的主键值，根据这个主键值再去过滤并 limit

例如：

1
2
select * from table where id >
(select * from table where type = 2 and level = 9 order by id asc limit 190

索引优化

合理地设计和使用索引，是优化慢 SQL 的利器。

利用覆盖索引

InnoDB 使用非主键索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引

例如对于如下查询：

1
select name from test where city='上海'

我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取

1
alter table test add index idx_city_name (city, name);

低版本避免使用 or 查询

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题。

避免使用 != 或者 <> 操作符

SQL 中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即使比较的字段上有索引

解决方法：通过把不等于操作符改成 or，可以使用索引，避免全表扫描

例如，把column<>’aaa’，改成 column>’aaa’ or column<’aaa’，就可以使用索引了

适当使用前缀索引

适当地使用前缀所云，可以降低索引的空间占用，提高索引的查询效率。

比如，邮箱的后缀都是固定的“@xxx.com”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引

1
alter table test add index index2(email(6));

PS: 需要注意的是，前缀索引也存在缺点，MySQL 无法利用前缀索引做 order by 和 group by 操作，也无法作为覆盖索引

避免列上函数运算

要避免在列字段上进行算术运算或其他表达式运算，否则可能会导致存储引擎无法正确使用索引，从而影响了查询的效率

1
2
select * from test where id + 1 = 50;
select * from test where month(updateTime) = 7;

正确使用联合索引

使用联合索引的时候，注意最左匹配原则。

JOIN 优化

优化子查询

尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大

小表驱动大表

关联查询的时候要拿小表去驱动大表，因为关联的时候，MySQL 内部会遍历驱动表，再去连接被驱动表。

比如 left join，左表就是驱动表，A 表小于 B 表，建立连接的次数就少，查询速度就被加快了。

1
select name from A left join B ;

适当增加冗余字段

增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略

避免使用 JOIN 关联太多的表

《阿里巴巴 Java 开发手册》规定不要 join 超过三张表，第一 join 太多降低查询的速度，第二 join 的 buffer 会占用更多的内存。

如果不可避免要 join 多张表，可以考虑使用数据异构的方式异构到 ES 中查询。

排序优化

利用索引扫描做排序

MySQL 有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的

但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机 IO，通常会比顺序全表扫描还慢

因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行

例如：

1
2
--建立索引（date,staff_id,customer_id）
select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;

只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序

UNION 优化

条件下推

MySQL 处理 union 的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在 union 查询中都会失效，因为它无法利用索引

最好手工将 where、limit 等子句下推到 union 的各个子查询中，以便优化器可以充分利用这些条件进行优化

此外，除非确实需要服务器去重，一定要使用 union all，如果不加 all 关键字，MySQL 会给临时表加上 distinct 选项，这会导致对整个临时表做唯一性检查，代价很高。

:::

【中级】MySQL 中如何解决深分页问题？

:::details 要点

深分页 (Deep Pagination) 是指当数据量很大时，查询靠后的分页数据（比如第 1000 页）性能急剧下降的问题。

解决方案有以下几种：

（1）使用索引覆盖+延迟关联

1
2
3
4
5
6
7
8
9
10
-- 原始深分页查询（性能差）
SELECT * FROM large_table ORDER BY id LIMIT 100000, 10;

-- 优化后的查询
SELECT * FROM large_table
INNER JOIN (
    SELECT id FROM large_table
    ORDER BY id
    LIMIT 100000, 10
) AS tmp USING(id);

（2）使用游标分页（记录上一页最后一条记录）

1
2
3
4
5
6
7
8
9
-- 第一页
SELECT * FROM large_table ORDER BY id LIMIT 10;

-- 获取上一页最后一条记录的 id=12345
-- 下一页查询
SELECT * FROM large_table
WHERE id > 12345
ORDER BY id
LIMIT 10;

（3）使用子查询优化

1
2
3
4
SELECT * FROM large_table
WHERE id >= (SELECT id FROM large_table ORDER BY id LIMIT 100000, 1)
ORDER BY id
LIMIT 10;

:::

MySQL 架构

【中级】SQL 查询语句的执行顺序是怎么样的？

:::details 要点

所有的查询语句都是从 FROM 开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。

执行顺序

1
2
3
4
5
6
7
8
9
(8) SELECT (9)DISTINCT<Select_list>
(1) FROM <left_table> (3) <join_type>JOIN<right_table>
(2) ON<join_condition>
(4) WHERE<where_condition>
(5) GROUP BY<group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING<having_condtion>
(10) ORDER BY<order_by_list>
(11) LIMIT<limit_number>

扩展阅读：SQL 的书写顺序和执行顺序

:::

【高级】一条 SQL 查询语句是如何执行的？

:::details 要点

MySQL 整个查询执行过程，总的来说分为 6 个步骤：

  1. 连接器 - 客户端和 MySQL 服务器建立连接；连接器负责跟客户端建立连接、获取权限、维持和管理连接。
  2. 查询缓存 - MySQL 服务器首先检查查询缓存，如果命中缓存，则立刻返回结果。否则进入下一阶段。MySQL 缓存弊大于利，因为失效非常频繁——任何更新都会清空查询缓存。
  3. 分析器 - MySQL 服务器进行 SQL 解析：语法分析、词法分析。
  4. 优化器 - MySQL 服务器用优化器生成对应的执行计划，根据策略选择最优索引。
  5. 执行器 - MySQL 服务器根据执行计划，调用存储引擎的 API 来执行查询。
  6. 返回结果 - MySQL 服务器将结果返回给客户端，同时缓存查询结果。

:::

【高级】一条 SQL 更新语句是如何执行的？

:::details 要点

更新流程和查询的流程大致相同，不同之处在于：更新流程还涉及两个重要的日志模块：

  • redo log（重做日志）
    • InnoDB 存储引擎独有的日志（物理日志）
    • 采用循环写入
  • bin log（归档日志）
    • MySQL Server 层通用日志（逻辑日志）
    • 采用追加写入

为了保证 redo log 和 bin log 的数据一致性，所以采用两阶段提交方式更新日志。

::

【高级】MySQL 如何选择执行计划？

:::details 要点

MySQL 选择执行计划的过程涉及多个步骤，主要依赖于查询优化器来决定最有效的执行方式。以下是 MySQL 选择执行计划的一些关键步骤和考虑因素：

  1. 解析 SQL 语句：首先，MySQL 会解析输入的 SQL 语句，检查语法是否正确，并将其转换为内部表示形式。
  2. 查询重写：在这一阶段，MySQL 可能会对原始查询进行一些优化或重写，例如常量传播、子查询优化等，以简化后续处理。
  3. 统计信息收集：MySQL 会使用存储在数据字典中的表和索引统计信息来估计不同执行路径的成本。这些统计信息包括但不限于表的大小、索引的选择性（即索引值的唯一性）、数据分布情况等。
  4. 生成候选执行计划：基于查询结构和可用索引，MySQL 的查询优化器会生成一个或多个可能的执行计划。这包括选择合适的连接算法（如嵌套循环连接、哈希连接或排序-合并连接）、访问路径（全表扫描、索引扫描等）以及连接顺序等。
  5. 估算成本：对于每一个候选执行计划，MySQL 都会估算其执行成本，这通常基于 I/O 操作次数、CPU 时间、内存使用等因素。成本模型是基于一系列假设和统计信息构建的。
  6. 选择最低成本的执行计划：最后，MySQL 会选择那个被认为具有最低成本的执行计划作为最终执行计划。
  7. 缓存执行计划：为了减少重复计算的成本，MySQL 会将某些查询的执行计划缓存起来，以便下次遇到相同的查询时可以直接使用已有的执行计划。

值得注意的是，虽然 MySQL 尽力选择最优的执行计划，但并不总是能够做到这一点，特别是在面对复杂查询或统计信息不准确的情况下。因此，在实践中，数据库管理员和开发人员可能需要通过分析查询执行计划（使用EXPLAIN命令），调整索引策略或者手动改写查询来帮助优化器做出更好的决策。此外，参数设置（比如optimizer_switch）也会影响优化器的行为。

:::

【高级】order by 是怎么工作的？

:::details 要点

用 explain 命令查看执行计划时，Extra 这个字段中的“Using filesort”表示的就是需要排序。

全字段排序

1
select city,name,age from t where city='杭州' order by name limit 1000;

这个语句执行流程如下所示 ：

执行流程：

  • 初始化 sort_buffer，确定放入需要排序的字段（如 name、city、age）。
  • 从索引中找到满足条件的记录，取出对应的字段值存入 sort_buffer。
  • 对 sort_buffer 中的数据按照排序字段进行排序。
  • 返回排序后的结果。

内存与磁盘排序：

  • 如果排序数据量小于 sort_buffer_size，排序在内存中完成。
  • 如果数据量过大，MySQL 会使用临时文件进行外部排序（归并排序）。MySQL 将需要排序的数据分成 N 份，每一份单独排序后存在这些临时文件中。然后把这 N 个有序文件再合并成一个有序的大文件。

优化器追踪：通过 OPTIMIZER_TRACE 可以查看排序过程中是否使用了临时文件（number_of_tmp_files）。

rowid 排序

  • 执行流程：
    • 当单行数据过大时，MySQL 会采用 rowid 排序，只将排序字段（如 name）和主键 id 放入 sort_buffer。
    • 排序完成后，根据 id 回表查询其他字段（如 city、age）。
  • 性能影响：rowid 排序减少了 sort_buffer 的内存占用，但增加了回表操作，导致更多的磁盘 I/O。

全字段排序 VS rowid 排序

  • 内存优先：
    • 如果内存足够大，MySQL 优先使用全字段排序，以减少磁盘访问。
    • 只有在内存不足时，才会使用 rowid 排序。
  • 设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

并不是所有的 order by 语句，都需要排序操作的。MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。如果查询的字段和排序字段可以通过联合索引覆盖，MySQL 可以直接利用索引的有序性，避免排序操作。

:::

参考资料