MySQL 面试

扩展

• 《高性能 MySQL》
• 极客时间教程 - MySQL 实战 45 讲
• 图解 MySQL 介绍
• 《SQL 必知必会》 - SQL 的基本概念和语法【入门】
• 《MySQL 必知必会》 - MySQL 的基本概念和语法【入门】

SQL

扩展

• 《SQL 必知必会》 - SQL 的基本概念和语法【入门】
• 《MySQL 必知必会》 - MySQL 的基本概念和语法【入门】

【简单】什么是范式？什么是反范式？⭐

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

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

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

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

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

第一范式（1NF）

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

第二范式（2NF）

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

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

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

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

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

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

根据 2NF 可以拆分如下：

-- 学生表
student（学号、姓名）
-- 课程表
course（课程号、学分）
-- 学生课程关系表
student_course（学号、课程号、成绩）

第三范式（3NF）

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

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

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

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

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

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

可能会存在问题：

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

可以基于 3NF 拆解：

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

【简单】为什么不推荐使用存储过程？

存储过程的优点：

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

存储过程的缺点：

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

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

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

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

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

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

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

-- 建表
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 一样
• 若数据库有该条数据，就忽略这条插入语句，不执行插入操作

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 一样

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

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 有什么区别？⭐

EXISTS 和 IN 区别如下：

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

EXISTS 和 IN 的对比示例如下：

 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 的实现，相当于外表循环，实现的逻辑类似于：

 for i in A
     for j in B
         if j.cc == i.cc then ...

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

 for i in B
     for j in A
         if j.cc == i.cc then ...

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

【简单】UNION 和 UNION ALL 有什么区别？⭐

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

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

【简单】JOIN 有哪些类型？⭐⭐

在 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

《阿里巴巴 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 的优化仍然受到很多限制，导致性能不如预期。

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

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

MySQL 数据类型

扩展

• MySQL 官方文档之数据类型
• MySQL 如何选择 float, double, decimal

【简单】CHAR 和 VARCHAR 的区别是什么？

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，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

【简单】金额数据用什么类型存储？⭐⭐

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

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

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

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

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

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

【示例】丢失精度案例

-- 创建表
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 😃？

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

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

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

ALTER TABLE test CHARSET utf8mb4;

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

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

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

ALTER TABLE test CONVERT TO CHARSET utf8mb4;

【简单】时间数据选择 DATETIME 还是 TIMESTAMP？

表结构设计时，对时间字段的存储，通常会有 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 官方文档之 InnoDB 存储引擎
• MySQL 官方文档之可选的存储引擎

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

存储引擎层负责数据的存储和提取。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 有哪些差异？⭐

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

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

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

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

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

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？⭐

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

主要作用：

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

工作原理：

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

【中等】什么是 Change Buffer？⭐

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

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

原理

• 写操作发生时：当修改非唯一二级索引的数据时，InnoDB 会检查目标索引页是否在 Buffer Pool 中。
  • 如果在：直接修改
  • 如果不在：将修改操作记录到 Change Buffer
• 后续读取时：当需要读取该索引页时，InnoDB 会将 Change Buffer 中的修改与从磁盘读取的原始页合并。
• 后台合并：有专门的线程定期将 Change Buffer 中的变更合并到磁盘上的索引页。

优势

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

适用场景

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

不适用场景

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

相关配置

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

MySQL 日志

【简单】MySQL 有哪些类型的日志？

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 有什么区别？⭐

维度	binlog	redo log
所属层级	MySQL Server 层	InnoDB 存储引擎层
日志性质	逻辑日志 （记录的 SQL 语句/行变化逻辑）	物理日志 （记录数据页的修改）
主要用途	数据复制 （主从同步） & 数据恢复 （任意时间点恢复）	崩溃恢复 （保证事务持久性）
写入时机	事务提交后才一次性写入	事务进行中持续写入 (Write-Ahead Logging)
写入方式	追加写 （文件一直增大）	循环写 （固定大小，循环覆盖）
内容格式	Statement (SQL 语句） / Row （行数据） / Mixed	物理数据页变化 (Page ID + 修改内容）
生命周期	长期保留 （可配置过期时间）	事务提交后可能被覆盖 (crash-safe 后即可复用）

【简单】什么是 WAL？⭐⭐⭐

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

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

【中等】redo log 如何刷盘？⭐

redo log 刷盘是事务持久性的保证。

redo log 刷盘 = 将内存中的日志（redo log buffer）写到磁盘文件（ib_logfile）的过程。

redo log 是 WAL 技术（先写日志，后写数据）在 InnoDB 的实现。

redo log 工作流程

Redo Log 由固定大小的文件组成（如 ib_logfile0、ib_logfile1），循环写入。

写满后触发 Checkpoint，将脏页刷盘，并清理已释放的日志空间。

• 延迟刷盘
  • 事务提交前，修改先写入 redo log 缓冲区（Log Buffer）。
  • Log Buffer 的数据按 innodb_flush_log_at_trx_commit 参数配置的刷盘策略，延迟刷新到磁盘上的 redo log 文件。
• 先写日志，再写磁盘：
  • 事务提交前，先确保 redo log 已写入磁盘
  • 数据写入 binlog 并刷盘
  • 将 redo log 的事务状态改为提交状态
• 故障恢复
  • 数据库崩溃时，通过 redo log 实现故障恢复
  • 找到 redo log 最近一次 Checkpoint 的位置
  • 重放 Checkpoint 之后的所有日志，恢复未刷盘的脏页

刷盘策略

redo log 通过 innodb_flush_log_at_trx_commit 参数决定刷盘策略。

参数值	刷盘时机	数据安全	性能	适用场景
= 1 （默认）	事务提交时，立即 fsync	最高（崩溃最多丢失 1 个事务）	最低（每次提交都有磁盘 IO）	强一致，如金融交易
= 2	事务提交时，写到 OS 页缓存，每秒 fsync	中等（崩溃可能丢失 1 秒数据）	中等（无磁盘 IO，除非 OS 刷新）	折中，通用场景
= 0	事务提交不刷，后台线程每秒 fsync	最低（崩溃可能丢失最多 1 秒数据）	最高（合并 IO，吞吐量大）	可容忍少量丢失，如日志采集

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

由于 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，与原库的值不同。

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

【中等】什么是 Log Buffer？⭐

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 如何实现主从同步？⭐⭐⭐

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

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 主从同步延迟？

主从延迟的常见解决方案

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

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

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

小结

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

MySQL 架构

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

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

执行顺序

(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 查询语句是如何执行的？⭐⭐⭐

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

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

【困难】一条 SQL 更新语句是如何执行的？⭐⭐⭐

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

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

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

【困难】order by 是怎么工作的？⭐⭐

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

全字段排序

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 可以直接利用索引的有序性，避免排序操作。

【困难】如果 select * from 一个有千万级数据的表，内存会飙升么？

通常不会导致 MySQL 服务器内存飙升，但如果客户端处理不当，客户端的内存可能会飙升。

• 服务端：SELECT * FROM huge_table 时，MySQL 的 InnoDB 存储引擎 并不会尝试将全部千万条记录一次性加载到内存中。MySQL 服务端使用固定大小的 Buffer Pool 按需读取数据页，并流式地将结果发送给客户端。
• 客户端：如果客户端尝试一次性拉取全部数据，就会出现内存飙升的情况。

客户端最佳实践

聪明的客户端会使用“流式查询”或“游标”的方式来处理大数据集。

• 服务器端游标（Server-Side Cursor）：MySQL 提供了这种方式（虽然协议上支持，但并非所有驱动都默认开启）。
• 客户端流式读取（Client-Side Streaming）：即使没有真正的服务器端游标，客户端也可以以流的方式处理结果集。它从网络连接中读取一批数据（例如 1000 行），处理完这批数据后，再读取下一批，而不是一次性读取所有。
  • Python (PyMySQL/MySQLdb): 创建游标时使用 SSCursor（cursorclass = pymysql.cursors.SSCursor）。
  • Java (JDBC): 在创建 Statement 对象时设置 fetchSize 为 Integer.MIN_VALUE（或根据驱动文档设置）来启用流式读取。

使用流式处理后，客户端的内存占用将保持在一个很小且恒定的水平（只相当于单批处理数据的大小），而不会随着结果集的大小而增长。

【困难】MySQL 如何选择执行计划？⭐

MySQL 通过优化器（Optimizer）选择执行计划，核心流程如下：

执行计划生成步骤

1. 解析 SQL：生成语法树，检查表/列是否存在
2. 预处理阶段：展开视图、优化子查询
3. 优化器核心工作：
   • 生成候选执行计划（全表扫描、索引扫描、JOIN 顺序等）
   • 成本估算（基于统计信息计算每个计划的 I/O、CPU 消耗）
   • 选择成本最低的计划

影响执行计划的关键因素

因素	说明	示例
统计信息	表大小、索引区分度等	ANALYZE TABLE更新统计
索引情况	可用索引及其选择性	高区分度索引优先
查询复杂度	JOIN/子查询数量	简单查询优先走索引
系统变量	优化器开关配置	optimizer_switch参数
HINT 指令	强制干预优化器	/*+ INDEX(idx_name) */

成本估算模型

优化器主要计算：

• I/O 成本：读取数据页的代价
• CPU 成本：处理数据的计算代价
• 内存成本：排序/临时表消耗

总成本 = （数据页读取数 × 单页 I/O 成本）
       + （扫描行数 × 行 CPU 处理成本）
       + （排序行数 × 排序成本）

查看和干预执行计划

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE age > 20;

-- 强制使用索引（慎用）
SELECT /*+ INDEX(users idx_age) */ * FROM users WHERE age > 20;

-- 更新统计信息
ANALYZE TABLE users;

常见执行计划问题

• 索引失效：函数计算、隐式类型转换

  -- 反例：索引失效
  SELECT * FROM users WHERE YEAR(create_time) = 2023;

• 错误 JOIN 顺序：解决方案：使用STRAIGHT_JOIN强制顺序

• 临时表/文件排序：关注EXPLAIN中的Using temporary/Using filesort

优化建议

• 定期ANALYZE TABLE更新统计信息
• 避免在索引列上使用函数
• 使用覆盖索引减少回表
• 监控performance_schema中的 SQL 执行历史

注意：MySQL 8.0 引入直方图统计（histogram）和代价模型改进，大幅提升复杂查询的计划准确性。

MySQL 优化

【简单】如何发现慢 SQL？

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

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

【简单】什么是执行计划？⭐⭐

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

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

【示例】MySQL 执行计划示例

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 使用分析

【简单】如何分析执行计划？⭐⭐⭐

执行计划关键字段

• 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？⭐⭐

避免不必要的列

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

分页优化

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

例如：

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

优化方案：

• 延迟关联

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

例如：

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

例如：

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

索引优化

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

利用覆盖索引

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

例如对于如下查询：

select name from test where city='上海'

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

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”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引

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

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

避免列上函数运算

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

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

正确使用联合索引

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

JOIN 优化

优化子查询

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

小表驱动大表

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

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

 select name from A left join B ;

适当增加冗余字段

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

避免使用 JOIN 关联太多的表

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

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

排序优化

利用索引扫描做排序

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

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

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

例如：

--建立索引（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 子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序

条件下推

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

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

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

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

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

解决方案有以下几种：

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

-- 原始深分页查询（性能差）
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）使用游标分页（记录上一页最后一条记录）

-- 第一页
SELECT * FROM large_table ORDER BY id LIMIT 10;

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

（3）使用子查询优化

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

【中等】哪种 COUNT 性能最好？⭐⭐

先说结论：按照效率排序的话，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，按行累加。

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

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

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

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

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

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

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

如何优化查询计数？

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

读写分离

扩展

参考：分布式存储面试#读写分离

分库分表

扩展

参考：分布式存储面试#分库分表