MySQL面试题，83道MySQL八股文（5.5万字331张手绘图），面渣逆袭必看👍
前言
5.5 万字 331 张手绘图，详解 83 道 MySQL 面试高频题（让天下没有难背的八股），面渣背会这些 MySQL 八股文，这次吊打面试官，我觉得稳了（手动 dog）。整理：沉默王二，戳转载链接，作者：三分恶，戳原文链接。
亮白版本更适合拿出来打印，这也是很多学生党喜欢的方式，打印出来背诵的效率会更高。
2025 年 02 月 27 日开始着手第二版更新。
- 对于高频题，会标注在《Java 面试指南（付费）》中出现的位置，哪家公司，原题是什么，并且会加🌟，目录一目了然；如果你想节省时间的话，可以优先背诵这些题目，尽快做到知彼知己，百战不殆。
- 区分八股精华回答版本和原理底层解释，让大家知其然知其所以然，同时又能做到面试时的高效回答。
- 结合项目（技术派、pmhub）来组织语言，让面试官最大程度感受到你的诚意，而不是机械化的背诵。
- 修复第一版中出现的问题，包括球友们的私信反馈，网站留言区的评论，以及 GitHub 仓库中的 issue，让这份面试指南更加完善。
- 增加二哥编程星球的球友们拿到的一些 offer，对面渣逆袭的感谢，以及对简历修改的一些认可，以此来激励大家，给大家更多信心。
- 优化排版，增加手绘图，重新组织答案，使其更加口语化，从而更贴近面试官的预期。
由于 PDF 没办法自我更新，所以需要最新版的小伙伴，可以微信搜【沉默王二】，或者扫描/长按识别下面的二维码，关注二哥的公众号，回复【222】即可拉取最新版本。
当然了，请允许我的一点点私心，那就是星球的 PDF 版本会比公众号早一个月时间，毕竟星球用户都付费过了，我有必要让他们先享受到一点点福利。相信大家也都能理解，毕竟在线版是免费的，CDN、服务器、域名、OSS 等等都是需要成本的。
更别说我付出的时间和精力了，大家觉得有帮助还请给个口碑，让你身边的同事、同学都能受益到。
我把二哥的 Java 进阶之路、JVM 进阶之路、并发编程进阶之路，以及所有面渣逆袭的版本都放进来了，涵盖 Java基础、Java集合、Java并发、JVM、Spring、MyBatis、计算机网络、操作系统、MySQL、Redis、RocketMQ、分布式、微服务、设计模式、Linux 等 16 个大的主题，共有 40 多万字，2000+张手绘图，可以说是诚意满满。
展示一下暗黑版本的 PDF 吧，排版清晰，字体优雅，更加适合夜服，晚上看会更舒服一点。
MySQL 基础
0.🌟什么是 MySQL？
MySQL 是一个开源的关系型数据库，现在隶属于 Oracle 公司。是我们国内使用频率最高的一种数据库，我在本地安装的是最新的 8.3 版本。
怎么删除/创建一张表？
可以使用 DROP TABLE 来删除表，使用 CREATE TABLE 来创建表。
创建表的时候，可以通过 PRIMARY KEY 设定主键。
CREATE TABLE users (
id INT AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100),
PRIMARY KEY (id)
);
请写一个升序/降序的 SQL 语句？
在 SQL 中，可以使用 ORDER BY 子句来对查询结果进行升序或者降序。默认情况下，查询结果是升序的，如果需要降序，可以通过 DESC 关键字来实现。
比如说在员工表中，我们要按工资降序，就可以使用 ORDER BY salary DESC 来完成：
SELECT id, name, salary
FROM employees
ORDER BY salary DESC;
如果需对多个字段进行排序，例如按工资降序，按名字升序，就可以 ORDER BY salary DESC, name ASC 来完成：
SELECT id, name, salary
FROM employees
ORDER BY salary DESC, name ASC;
MySQL出现性能差的原因有哪些？
可能是 SQL 查询使用了全表扫描，也可能是查询语句过于复杂，如多表 JOIN 或嵌套子查询。
也有可能是单表数据量过大。
通常情况下，添加索引就能解决大部分性能问题。对于一些热点数据，还可以通过增加 Redis 缓存，来减轻数据库的访问压力。
- Java 面试指南（付费）收录的字节跳动面经同学 1 Java 后端技术一面面试原题：你平时用到的数据库
- Java 面试指南（付费）收录的腾讯云智面经同学 16 一面面试原题：数据库用过哪些，对哪个比较熟？
- Java 面试指南（付费）收录的 360 面经同学 3 Java 后端技术一面面试原题：用过哪些数据库
- Java 面试指南（付费）收录的招商银行面经同学 6 招银网络科技面试原题：了解 MySQL、Redis 吗？
- Java 面试指南（付费）收录的国企零碎面经同学 9 面试原题：数据库用什么多（说了 Mysql 和 Redis）
- Java 面试指南（付费）收录的vivo 面经同学 10 技术一面面试原题：怎么删除/创建一张表和设定主键 ，举例用sql实现升序降序
- Java 面试指南（付费）收录的滴滴面经同学 3 网约车后端开发一面原题：MySQL性能慢的原因
1.两张表怎么进行连接？
可以通过内连接 inner join、外连接 outer join、交叉连接 cross join 来合并多个表的查询结果。
什么是内连接？
内连接用于返回两个表中有匹配关系的行。假设有两张表，用户表和订单表，想查询有订单的用户，就可以使用内连接 users INNER JOIN orders，按照用户 ID 关联就行了。
SELECT users.name, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id;
只有那些在两个表中都存在 user_id 的记录才会出现在查询结果中。
什么是外连接？
和内连接不同，外连接不仅返回两个表中匹配的行，还返回没有匹配的行，用 null 来填充。
外连接又分为左外连接 left join 和右外连接 right join。
left join 会保留左表中符合条件的所有记录，如果右表中有匹配的记录，就返回匹配的记录，否则就用 null 填充，常用于某表中有，但另外一张表中可能没有的数据的查询场景。
假设要查询所有用户及他们的订单，即使用户没有下单，就可以使用左连接：
SELECT users.id, users.name, orders.order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
查询前：
| users | orders | |
|---|---|---|
| id | name | user_id |
| 1 | 王二 | 1 |
| 2 | 张三 | 2 |
| 3 | 李四 | 无 |
查询后：
| id | name | order_id |
|---|---|---|
| 1 | 王二 | 10 |
| 2 | 张三 | 20 |
| 3 | 李四 | null |
右连接就是左连接的镜像，right join 会保留右表中符合条件的所有记录，如果左表中有匹配的记录，就返回匹配的记录，否则就用 null 填充。
什么是交叉连接？
交叉连接会返回两张表的笛卡尔积，也就是将左表的每一行与右表的每一行进行组合，返回的行数是两张表行数的乘积。
假设有 A 表和 B 表，A 表有 2 行数据，B 表有 3 行数据，那么交叉连接的结果就是 2 ✖️ 3 = 6 行。
SELECT A.id, B.id
FROM A
CROSS JOIN B;
笛卡尔积是数学中的一个概念，例如集合 A={a,b}，集合 B={0,1,2}，那么 A✖️B={。
- Java 面试指南（付费）收录的用友面试原题：两张表怎么进行连接
2.内连接、左连接、右连接有什么区别？
MySQL 的连接主要分为内连接和外连接，外连接又可以分为左连接和右连接。
内连接可以用来找出两个表中共同的记录，相当于两个数据集的交集。
左连接和右连接可以用来找出两个表中不同的记录，相当于两个数据集的并集。两者的区别是，左连接会保留左表中符合条件的所有记录，右连接则刚好相反。
拿技术派实战项目的表为例来详细验证下。
有三张表，一张文章表 article，主要存文章标题 title， 一张文章详情表 article_detail，主要存文章的内容 content，一张文章评论表 comment，主要存评论 content，三个表通过文章 id 关联。
先来看内连接：
SELECT LEFT(a.title, 20) AS ArticleTitle, LEFT(c.content, 20) AS CommentContent
FROM article a
INNER JOIN comment c ON a.id = c.article_id
LIMIT 2;
返回至少有一条评论的文章标题和评论内容（前 20 个字符），只返回符合条件的前 2 条记录。
再来看做连接：
SELECT LEFT(a.title, 20) AS ArticleTitle, LEFT(c.content, 20) AS CommentContent
FROM article a
LEFT JOIN comment c ON a.id = c.article_id
LIMIT 2;
返回所有文章的标题和文章评论，即使某些文章没有评论（填充为 NULL）。
最后来看右连：
SELECT LEFT(a.title, 20) AS ArticleTitle, LEFT(c.content, 20) AS CommentContent
FROM comment c
RIGHT JOIN article a ON a.id = c.article_id
LIMIT 2;
- Java 面试指南（付费）收录的腾讯 Java 后端实习一面原题：请说说 MySQL 的内联、左联、右联的区别。
memo：2025 年 2 月 27 日修改至此。给大家看一条球友的面经，基本上都是面渣逆袭中常见的八股，所以只要能把面渣中的高频题拿下，面试 OC 的概率真的很大，真心话。
3.说一下数据库的三大范式？
第一范式，确保表的每一列都是不可分割的基本数据单元，比如说用户地址，应该拆分成省、市、区、详细地址等 4 个字段。
第二范式，要求表中的每一列都和主键直接相关。比如在订单表中，商品名称、单位、商品价格等字段应该拆分到商品表中。
然后新建一个订单商品关联表，用订单编号和商品编号进行关联就好了。
第三范式，非主键列应该只依赖于主键列。比如说在设计订单信息表的时候，可以把客户名称、所属公司、联系方式等信息拆分到客户信息表中，然后在订单信息表中用客户编号进行关联。
建表的时候需要考虑哪些问题？
首先需要考虑表是否符合数据库的三大范式，确保字段不可再分，消除非主键依赖，确保字段仅依赖于主键等。
然后在选择字段类型时，应该尽量选择合适的数据类型。
在字符集上，尽量选择 utf8mb4，这样不仅可以支持中文和英文，还可以支持表情符号等。
当数据量较大时，比如上千万行数据，需要考虑分表。比如订单表，可以采用水平分表的方式来分散单表存储压力。
- Java 面试指南（付费）收录的字节跳动面经同学 13 Java 后端二面面试原题：什么是三大范式，为什么要有三大范式，什么场景下不用遵循三大范式，举一个场景
- Java 面试指南（付费）收录的京东面经同学 5 Java 后端技术一面面试原题：建表考虑哪些问题
4.varchar 与 char 的区别？
varchar 是可变长度的字符类型，原则上最多可以容纳 65535 个字符，但考虑字符集，以及 MySQL 需要 1 到 2 个字节来表示字符串长度，所以实际上最大可以设置到 65533。
latin1 字符集，且列属性定义为 NOT NULL。
char 是固定长度的字符类型，当定义一个 CHAR(10) 字段时，不管实际存储的字符长度是多少，都只会占用 10 个字符的空间。如果插入的数据小于 10 个字符，剩余的部分会用空格填充。
| 值 | CHAR(4) | 存储需求（字节） | VARCHAR(4) | 存储需求（字节） |
|---|---|---|---|---|
| '' | ' ' | 4 | '' | 1 |
| 'ab' | 'ab ' | 4 | 'ab' | 3 |
| 'abcd' | 'abcd' | 4 | 'abcd' | 5 |
| 'abcdefgh' | 'abcd' | 4 | 'abcd' | 5 |
5.blob 和 text 有什么区别？
blob 用于存储二进制数据，比如图片、音频、视频、文件等；但实际开发中，我们都会把这些文件存储到 OSS 或者文件服务器上，然后在数据库中存储文件的 URL。
text 用于存储文本数据，比如文章、评论、日志等。
memo：2025 年 2 月 28 日修改至此。今天有球友反馈拿到了理想汽车的补录 offer， 真的恭喜了！
6.DATETIME 和 TIMESTAMP 有什么区别？
DATETIME 直接存储日期和时间的完整值，与时区无关。
TIMESTAMP 存储的是 Unix 时间戳，1970-01-01 00:00:01 UTC 以来的秒数，受时区影响。
另外，DATETIME 的默认值为 null，占用 8 个字节；TIMESTAMP 的默认值为当前时间——CURRENT_TIMESTAMP，占 4 个字节，实际开发中更常用，因为可以自动更新。
7.in和exists的区别？
当使用 IN 时，MySQL 会首先执行子查询，然后将子查询的结果集用于外部查询的条件。这意味着子查询的结果集需要全部加载到内存中。
而 EXISTS 会对外部查询的每一行，执行一次子查询。如果子查询返回任何行，则 EXISTS 条件为真。EXISTS 关注的是子查询是否返回行，而不是返回的具体值。
-- IN 的临时表可能成为性能瓶颈
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
-- EXISTS 可以利用关联索引
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.amount > 100);
IN 适用于子查询结果集较小的情况。如果子查询返回大量数据，IN 的性能可能会下降，因为它需要将整个结果集加载到内存。
而 EXISTS 适用于子查询结果集可能很大的情况。由于 EXISTS 只需要判断子查询是否返回行，而不需要加载整个结果集，因此在某些情况下性能更好，特别是当子查询可以使用索引时。
NULL值陷了解吗？
IN: 如果子查询的结果集中包含 NULL 值，可能会导致意外的结果。例如，WHERE column IN (subquery)，如果 subquery 返回 NULL，则 column IN (subquery) 永远不会为真，除非 column 本身也为 NULL。
EXISTS: 对 NULL 值的处理更加直接。EXISTS 只是检查子查询是否返回行，不关心行的具体值，因此不受 NULL 值的影响。
memo：2025 年 3 月 1 日修改至此。
8.记录货币用什么类型比较好？
如果是电商、交易、账单等涉及货币的场景，建议使用 DECIMAL 类型，因为 DECIMAL 类型是精确数值类型，不会出现浮点数计算误差。
例如，DECIMAL(19,4) 可以存储最多 19 位数字，其中 4 位是小数。
CREATE TABLE orders (
id INT AUTO_INCREMENT,
amount DECIMAL(19,4),
PRIMARY KEY (id)
);
如果是银行，涉及到支付的场景，建议使用 BIGINT 类型。可以将货币金额乘以一个固定因子，比如 100，表示以“分”为单位，然后存储为 BIGINT。这种方式既避免了浮点数问题，同时也提供了不错的性能。但在展示的时候需要除以相应的因子。
为什么不推荐使用 FLOAT 或 DOUBLE？
因为 FLOAT 和 DOUBLE 都是浮点数类型，会存在精度问题。
在许多编程语言中，0.1 + 0.2 的结果会是类似 0.30000000000000004 的值，而不是预期的 0.3。
9.🌟怎么存储 emoji?
因为 emoji（😊）是 4 个字节的 UTF-8 字符，而 MySQL 的 utf8 字符集只支持最多 3 个字节的 UTF-8 字符，所以在 MySQL 中存储 emoji 时，需要使用 utf8mb4 字符集。
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
MySQL 8.0 已经默认支持 utf8mb4 字符集，可以通过 SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; 查看。
- Java 面试指南（付费）收录的字节跳动面经同学 13 Java 后端二面面试原题：mysql 怎么存 emoji，怎么编码
10.drop、delete 与 truncate 的区别？
DROP 是物理删除，用来删除整张表，包括表结构，且不能回滚。
DELETE 支持行级删除，可以带 WHERE 条件，可以回滚。
TRUNCATE 用于清空表中的所有数据，但会保留表结构，不能回滚。
memo：2025 年 3 月 4 日修改至此。给大家传递一个喜报，一位球友拿到了科大讯飞的 offer，这薪资在合肥真的会很香。
11.UNION 与 UNION ALL 的区别？
UNION 会自动去除合并后结果集中的重复行。UNION ALL 不会去重，会将所有结果集合并起来。
12.count(1)、count(*) 与 count(列名) 的区别？
在 InnoDB 引擎中，COUNT(1) 和 COUNT(*) 没有区别，都是用来统计所有行，包括 NULL。
如果表有索引，COUNT(*) 会直接用索引统计，而不是全表扫描，而 COUNT(1) 也会被 MySQL 优化为 COUNT(*)。
COUNT(列名) 只统计列名不为 NULL 的行数。
-- 假设 users 表：
+----+-------+------------+
| id | name | email |
+----+-------+------------+
| 1 | 张三 | zhang@xx.com |
| 2 | 李四 | NULL |
| 3 | 王二 | wang@xx.com |
+----+-------+------------+
-- COUNT(*)
SELECT COUNT(*) FROM users;
-- 结果：3 （统计所有行）
-- COUNT(1)
SELECT COUNT(1) FROM users;
-- 结果：3 （统计所有行）
-- COUNT(email)
SELECT COUNT(email) FROM users;
-- 结果：2 （NULL 不计入统计）
这里解释一下，假设有这样一张表：
CREATE TABLE t1 (
id INT,
name VARCHAR(50),
value INT
);
插入的数据为：
INSERT INTO t1 VALUES
(1, 'A', 10),
(2, 'B', NULL), -- NULL in value column
(3, 'C', 30),
(4, NULL, 40), -- NULL in name column
(5, 'E', NULL); -- NULL in value column
因为 id 列没有索引，所以 select count(*) 是全表扫描。
然后我们给 id 列加上索引。
alter table t1 add primary key (id);
再来看一下 select count(*)，发现用了索引（MySQL 默认为给主键添加索引）。
另外，MySQL 8.0 官方手册有明确说明，InnoDB 引擎对 SELECT COUNT(*) 和 SELECT COUNT(1) 的处理方式完全一致，性能并无差异。
memo：2025 年 3 月 5 日修改至此。再晒一个喜报给正在刷八股的你，一位球友拿到了咪咕的大模型应用开发，很不错的方向，恭喜了！给你也加加好运🍀buff，你也加把劲。
13.SQL 查询语句的执行顺序了解吗？
了解。先执行 FROM 确定主表，再执行 JOIN 连接，然后 WHERE 进行过滤，接着 GROUP BY 进行分组，HAVING 过滤聚合结果，SELECT 选择最终列，ORDER BY 排序，最后 LIMIT 限制返回行数。
WHERE 先执行是为了减少数据量，HAVING 只能过滤聚合数据，ORDER BY 必须在 SELECT 之后排序最终结果，LIMIT 最后执行以减少数据传输。
| 执行顺序 | SQL 关键字 | 作用 |
|---|---|---|
| ① | FROM | 确定主表，准备数据 |
| ② | ON | 连接多个表的条件 |
| ③ | JOIN | 执行 INNER JOIN / LEFT JOIN 等 |
| ④ | WHERE | 过滤行数据（提高效率） |
| ⑤ | GROUP BY | 进行分组 |
| ⑥ | HAVING | 过滤聚合后的数据 |
| ⑦ | SELECT | 选择最终返回的列 |
| ⑧ | DISTINCT | 进行去重 |
| ⑨ | ORDER BY | 对最终结果排序 |
| ⑩ | LIMIT | 限制返回行数 |
这个执行顺序与编写 SQL 语句的顺序不同，这也是为什么有时候在 SELECT 子句中定义的别名不能在 WHERE 子句中使用得原因，因为 WHERE 是在 SELECT 之前执行的。
LIMIT 为什么在最后执行？
因为 LIMIT 是在最终结果集上执行的，如果在 WHERE 之前执行 LIMIT，那么就会先返回所有行，然后再进行 LIMIT 限制，这样会增加数据传输的开销。
ORDER BY 为什么在 SELECT 之后执行？
因为排序需要基于最终返回的列，如果 ORDER BY 早于 SELECT 执行，计算 COUNT(*) 之类的聚合函数就会出问题。
SELECT name, COUNT(*) AS order_count
FROM orders
GROUP BY name
ORDER BY order_count DESC;
14.介绍一下 MySQL 的常用命令（补充）
2024 年 03 月 13 日增补。
MySQL 的常用命令主要包括数据库操作命令、表操作命令、行数据 CRUD 命令、索引和约束的创建修改命令、用户和权限管理的命令、事务控制的命令等。
说说数据库操作命令？
CREATE DATABASE database_name; 用于创建数据库；DROP DATABASE database_name; 用于删除数据库；SHOW DATABASES; 用于显示所有数据库；USE database_name; 用于切换数据库。
说说表操作命令？
CREATE TABLE table_name (列名1 数据类型1, 列名2 数据类型2,...); 用于创建表；DROP TABLE table_name; 用于删除表；SHOW TABLES; 用于显示所有表；DESCRIBE table_name; 用于查看表结构；ALTER TABLE table_name ADD column_name datatype; 用于修改表。
说说行数据的 CRUD 命令？
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); 用于插入数据；SELECT column_names FROM table_name WHERE condition; 用于查询数据；UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; 用于更新数据；DELETE FROM table_name WHERE condition; 用于删除数据。
说说索引和约束的创建修改命令？
CREATE INDEX index_name ON table_name (column_name); 用于创建索引；ALTER TABLE table_name ADD PRIMARY KEY (column_name); 用于添加主键；ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES parent_table (parent_column_name); 用于添加外键。
说说用户和权限管理的命令？
CREATE USER 'username'@'host' IDENTIFIED BY 'password'; 用于创建用户；GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'host'; 用于授予权限；REVOKE ALL PRIVILEGES ON database_name.table_name FROM 'username'@'host'; 用于撤销权限；DROP USER 'username'@'host'; 用于删除用户。
说说事务控制的命令？
START TRANSACTION; 用于开始事务；COMMIT; 用于提交事务；ROLLBACK; 用于回滚事务。
- Java 面试指南（付费）收录的用友金融一面原题：介绍一下 MySQL 的常用命令
15.MySQL bin 目录下的可执行文件了解吗（补充）
2024 年 03 月 13 日增补
了解的。MySQL 的 bin 目录下有很多可执行文件，主要用于管理 MySQL 服务器、数据库、表、数据等。比如说：
- mysql：用于连接 MySQL 服务器
- mysqldump：用于数据库备份，对数据备份、迁移或恢复时非常有用
- mysqladmin：用来执行一些管理操作，比如说创建数据库、删除数据库、查看 MySQL 服务器的状态等。
- mysqlcheck：用于检查、修复、分析和优化数据库表，对数据库的维护和性能优化非常有用。
- mysqlimport：用于从文本文件中导入数据到数据库表中，适合批量数据导入。
- mysqlshow：用于显示 MySQL 数据库服务器中的数据库、表、列等信息。
- mysqlbinlog：用于查看 MySQL 二进制日志文件的内容，可以用于恢复数据、查看数据变更等。
16.MySQL 第 3-10 条记录怎么查？（补充）
2024 年 03 月 30 日增补
可以使用 limit 语句，结合偏移量和行数来实现。
SELECT * FROM table_name LIMIT 2, 8;
limit 语句用于限制查询结果的数量，偏移量表示从哪条记录开始，行数表示返回的记录数量。
- 2：偏移量，表示跳过前两条记录，从第三条记录开始。
- 8：行数，表示从偏移量开始，返回 8 条记录。
偏移量是从 0 开始的，即第一条记录的偏移量是 0；如果想从第 3 条记录开始，偏移量就应该是 2。
- Java 面试指南（付费）收录的美团面经同学 16 暑期实习一面面试原题：MySQL 第 3-10 条记录怎么查？
17.用过哪些 MySQL 函数？（补充）
2024 年 04 月 12 日增补
用过挺多的，比如说处理字符串的函数：
CONCAT(): 用于连接两个或多个字符串。LENGTH(): 用于返回字符串的长度。SUBSTRING(): 从字符串中提取子字符串。REPLACE(): 替换字符串中的某部分。TRIM(): 去除字符串两侧的空格或其他指定字符。
实测数据：
-- 连接字符串
SELECT CONCAT('沉默', ' ', '王二') AS concatenated_string;
-- 获取字符串长度
SELECT LENGTH('沉默 王二') AS string_length;
-- 提取子字符串
SELECT SUBSTRING('沉默 王二', 1, 5) AS substring;
-- 替换字符串内容
SELECT REPLACE('沉默 王二', '王二', 'MySQL') AS replaced_string;
-- 去除字符串两侧的空格
SELECT TRIM(' 沉默 王二 ') AS trimmed_string;
处理数字的函数：
ABS(): 返回一个数的绝对值。ROUND(): 四舍五入到指定的小数位数。MOD(): 返回除法操作的余数。
实测数据：
-- 返回绝对值
SELECT ABS(-123) AS absolute_value;
-- 四舍五入
SELECT ROUND(123.4567, 2) AS rounded_value;
-- 余数
SELECT MOD(10, 3) AS modulus;
日期和时间处理函数：
NOW(): 返回当前的日期和时间。CURDATE(): 返回当前的日期。
实测数据：
-- 返回当前日期和时间
SELECT NOW() AS current_date_time;
-- 返回当前日期
SELECT CURDATE() AS current_date;
汇总函数：
SUM(): 计算数值列的总和。AVG(): 计算数值列的平均值。COUNT(): 计算某列的行数。
实测数据：
-- 创建一个表并插入数据进行聚合查询
CREATE TABLE sales (
product_id INT,
sales_amount DECIMAL(10, 2)
);
INSERT INTO sales (product_id, sales_amount) VALUES (1, 100.00);
INSERT INTO sales (product_id, sales_amount) VALUES (1, 150.00);
INSERT INTO sales (product_id, sales_amount) VALUES (2, 200.00);
-- 计算总和
SELECT SUM(sales_amount) AS total_sales FROM sales;
-- 计算平均值
SELECT AVG(sales_amount) AS average_sales FROM sales;
-- 计算总行数
SELECT COUNT(*) AS total_entries FROM sales;
逻辑函数：
IF(): 如果条件为真，则返回一个值；否则返回另一个值。CASE: 根据一系列条件返回值。
-- IF函数
SELECT IF(1 > 0, 'True', 'False') AS simple_if;
-- CASE表达式
SELECT CASE WHEN 1 > 0 THEN 'True' ELSE 'False' END AS case_expression;
- Java 面试指南（付费）收录的华为 OD 面经同学 1 一面面试原题：用过哪些 MySQL 函数？
- Java 面试指南（付费）收录的 小公司面经合集好未来测开面经同学 3 测开一面面试原题：知道 MySQL 的哪些函数，如 order by count()
18.说说 SQL 的隐式数据类型转换？（补充）
2024 年 04 月 25 日增补
当一个整数和一个浮点数相加时，整数会被转换为浮点数。
SELECT 1 + 1.0; -- 结果为 2.0
当一个字符串和一个整数相加时，字符串会被转换为整数。
SELECT '1' + 1; -- 结果为 2
隐式转换会导致意想不到的结果，最好通过显式转换来规避。
SELECT CAST('1' AS SIGNED INTEGER) + 1; -- 结果为 2
实际验证结果：
- Java 面试指南（付费）收录的小公司面经合集同学 1 Java 后端面试原题：说说 SQL 的隐式数据类型转换？
memo：2025 年 3 月 6 日修改至此。
19. 说说 SQL 的语法树解析？（补充）
2024 年 09 月 19 日增补
SQL 语法树解析是将 SQL 查询语句转换成抽象语法树 —— AST 的过程，是数据库引擎处理查询的第一步，也是防止 SQL 注入的重要手段。
通常分为 3 个阶段。
第一个阶段，词法分析：拆解 SQL 语句，识别关键字、表名、列名等。
---这部分是帮助大家理解 start，面试中可不背---
比如说：
SELECT id, name FROM users WHERE age > 18;
将会被拆解为：
[SELECT] [id] [,] [name] [FROM] [users] [WHERE] [age] [>] [18] [;]
---这部分是帮助大家理解 end，面试中可不背---
第二个阶段，语法分析：检查 SQL 是否符合语法规则，并构建抽象语法树。
---这部分是帮助大家理解 start，面试中可不背---
比如说上面的语句会被构建成如下的语法树：
SELECT
/ \
Columns FROM
/ \ |
id name users
|
WHERE
|
age > 18
或者这样表示：
SELECT
├── COLUMNS: id, name
├── FROM: users
├── WHERE
│ ├── CONDITION: age > 18
---这部分是帮助大家理解 end，面试中可不背---
第三个阶段，语义分析：检查表、列是否存在，进行权限验证等。
---这部分是帮助大家理解 start，面试中可不背---
比如说执行：
SELECT id, name FROM users WHERE age > 'eighteen';
会报错：
ERROR: Column 'age' is INT, but 'eighteen' is STRING.
---这部分是帮助大家理解 end，面试中可不背---
- Java 面试指南（付费）收录的字节跳动面经同学 21 抖音商城一面面试原题：sql的语法树解析
memo：2025 年 3 月 7 日 修改至此。再晒一个 offer，一位球友拿到了经纬恒润的实习 offer，并且直言面试了很多场，我说超过 5 次的题目基本上都碰到了，啥都别说了，面渣逆袭 YYDS。
数据库架构
20.说说 MySQL 的基础架构？
MySQL 采用分层架构，主要包括连接层、服务层、和存储引擎层。
①、连接层主要负责客户端连接的管理，包括验证用户身份、权限校验、连接管理等。可以通过数据库连接池来提升连接的处理效率。
②、服务层是 MySQL 的核心，主要负责查询解析、优化、执行等操作。在这一层，SQL 语句会经过解析、优化器优化，然后转发到存储引擎执行，并返回结果。这一层包含查询解析器、优化器、执行计划生成器、日志模块等。
③、存储引擎层负责数据的实际存储和提取。MySQL 支持多种存储引擎，如 InnoDB、MyISAM、Memory 等。
binlog写入在哪一层？
binlog 在服务层，负责记录 SQL 语句的变化。它记录了所有对数据库进行更改的操作，用于数据恢复、主从复制等。
- Java 面试指南（付费）收录的字节跳动面经同学 21 抖音商城一面面试原题：mysql分为几层？binlog写入在哪一层
21.🌟一条查询语句是如何执行的？
当我们执行一条 SELECT 语句时，MySQL 并不会直接去磁盘读取数据，而是经过 6 个步骤来解析、优化、执行，然后再返回结果。
第一步，客户端发送 SQL 查询语句到 MySQL 服务器。
第二步，MySQL 服务器的连接器开始处理这个请求，跟客户端建立连接、获取权限、管理连接。
第三步，解析器对 SQL 语句进行解析，检查语句是否符合 SQL 语法规则，确保数据库、表和列都是存在的，并处理 SQL 语句中的名称解析和权限验证。
第四步，优化器负责确定 SQL 语句的执行计划，这包括选择使用哪些索引，以及决定表之间的连接顺序等。
第五步，执行器会调用存储引擎的 API 来进行数据的读写。
第六步，存储引擎负责查询数据，并将执行结果返回给客户端。客户端接收到查询结果，完成这次查询请求。
- Java 面试指南（付费）收录的美团面经同学 2 Java 后端技术一面面试原题：MySQL 执行语句的整个过程了解吗？
- Java 面试指南（付费）收录的美团面经同学 18 成都到家面试原题：mysql一条数据的查询过程
- Java 面试指南（付费）收录的字节跳动面经同学19番茄小说一面面试原题：MySQL中一条SQL的执行流程
memo：2025 年 3 月 8 日修改至此。
22.一条更新语句是如何执行的？
总的来说，一条 UPDATE 语句的执行过程包括读取数据页、加锁解锁、事务提交、日志记录等多个步骤。
拿 update test set a=1 where id=2 举例来说：
在事务开始前，MySQL 需要记录undo log，用于事务回滚。
| 操作 | id | 旧值 | 新值 |
|---|---|---|---|
| update | 2 | N | 1 |
除了记录 undo log，存储引擎还会将更新操作写入 redo log，状态标记为 prepare，并确保 redo log 持久化到磁盘。这一步可以保证即使系统崩溃，数据也能通过 redo log 恢复到一致状态。
写完 redo log 后，MySQL 会获取行锁，将 a 的值修改为 1，标记为脏页，此时数据仍然在内存的 buffer pool 中，不会立即写入磁盘。后台线程会在适当的时候将脏页刷盘，以提高性能。
最后提交事务，redo log 中的记录被标记为 committed，行锁释放。
如果 MySQL 开启了 binlog，还会将更新操作记录到 binlog 中，主要用于主从复制。
以及数据恢复，可以结合 redo log 进行点对点的恢复。binlog 的写入通常发生在事务提交时，与 redo log 共同构成“两阶段提交”，确保两者的一致性。
注意，redo log 的写入有两个阶段的提交，一是 binlog 写入之前prepare 状态的写入，二是 binlog 写入之后 commit 状态的写入。
memo：2025 年 3 月 9 日修改至此。
23.说说 MySQL 的段区页行（补充）
2024 年 04 月 26 日增补
推荐阅读：了解 MySQL的数据行、行溢出机制吗？
MySQL 是以表的形式存储数据的，而表空间的结构则由段、区、页、行组成。
①、段：表空间由多个段组成，常见的段有数据段、索引段、回滚段等。
创建索引时会创建两个段，数据段和索引段，数据段用来存储叶子节点中的数据；索引段用来存储非叶子节点的数据。
回滚段包含了事务执行过程中用于数据回滚的旧数据。
②、区：段由一个或多个区组成，区是一组连续的页，通常包含 64 个连续的页，也就是 1M 的数据。
使用区而非单独的页进行数据分配可以优化磁盘操作，减少磁盘寻道时间，特别是在大量数据进行读写时。
③、页：页是 InnoDB 存储数据的基本单元，标准大小为 16 KB，索引树上的一个节点就是一个页。
也就意味着数据库每次读写都是以 16 KB 为单位的，一次最少从磁盘中读取 16KB 的数据到内存，一次最少写入 16KB 的数据到磁盘。
④、行：InnoDB 采用行存储方式，意味着数据按照行进行组织和管理，行数据可能有多个格式，比如说 COMPACT、REDUNDANT、DYNAMIC 等。
MySQL 8.0 默认的行格式是 DYNAMIC，由COMPACT 演变而来，意味着这些数据如果超过了页内联存储的限制，则会被存储在溢出页中。
可以通过 show table status like '%article%' 查看行格式。
存储引擎
24.🌟MySQL 有哪些常见存储引擎？
MySQL 支持多种存储引擎，常见的有 MyISAM、InnoDB、MEMORY 等。
---这部分是帮助大家理解 start，面试中可不背---
我来做一个表格对比：
| 功能 | InnoDB | MyISAM | MEMORY |
|---|---|---|---|
| 支持事务 | Yes | No | No |
| 支持全文索引 | Yes | Yes | No |
| 支持 B+树索引 | Yes | Yes | Yes |
| 支持哈希索引 | Yes | No | Yes |
| 支持外键 | Yes | No | No |
---这部分是帮助大家理解 end，面试中可不背---
除此之外，我还了解到：
①、MySQL 5.5 之前，默认存储引擎是 MyISAM，5.5 之后是 InnoDB。
②、InnoDB 支持的哈希索引是自适应的，不能人为干预。
③、InnoDB 从 MySQL 5.6 开始，支持全文索引。
④、InnoDB 的最小表空间略小于 10M，最大表空间取决于页面大小。
如何切换 MySQL 的数据引擎？
可以通过 alter table 语句来切换 MySQL 的数据引擎。
ALTER TABLE your_table_name ENGINE=InnoDB;
不过不建议，应该提前设计好到底用哪一种存储引擎。
- Java 面试指南（付费）收录的字节跳动面经同学 1 Java 后端技术一面面试原题：MySQL 支持哪些存储引擎?
- Java 面试指南（付费）收录的用友面试原题：innodb 引擎和 hash 引擎有什么区别
- Java 面试指南（付费）收录的国企零碎面经同学 9 面试原题：MySQL 的存储引擎
- Java 面试指南（付费）收录的京东同学 4 云实习面试原题：mysql的数据引擎有哪些, 区别(innodb,MyISAM,Memory)
- Java 面试指南（付费）收录的阿里系面经同学 19 饿了么面试原题：存储引擎介绍
memo：2025 年 3 月 10 日修改至此。
25.存储引擎应该怎么选择？
大多数情况下，使用默认的 InnoDB 就可以了，InnoDB 可以提供事务、行级锁、外键、B+ 树索引等能力。
MyISAM 适合读多写少的场景。
MEMORY 适合临时表，数据量不大的情况。因为数据都存放在内存，所以速度非常快。
- Java 面试指南（付费）收录的快手同学 2 一面面试原题：MySQL的InnoDB特点？为什么用B+树？而不是B树，区别？
26.InnoDB 和 MyISAM 主要有什么区别？
InnoDB 和 MyISAM 的最大区别在于事务支持和锁机制。InnoDB 支持事务、行级锁，适合大多数业务系统；而 MyISAM 不支持事务，用的是表锁，查询快但写入性能差，适合读多写少的场景。
另外，从存储结构上来说，MyISAM 用三种格式的文件来存储，.frm 文件存储表的定义；.MYD 存储数据；.MYI 存储索引；而 InnoDB 用两种格式的文件来存储，.frm 文件存储表的定义；.ibd 存储数据和索引。
从索引类型上来说，MyISAM 为非聚簇索引，索引和数据分开存储，索引保存的是数据文件的指针。
InnoDB 为聚簇索引，索引和数据不分开。
更细微的层面上来讲，MyISAM 不支持外键，可以没有主键，表的具体行数存储在表的属性中，查询时可以直接返回；InnoDB 支持外键，必须有主键，具体行数需要扫描整个表才能返回，有索引的情况下会扫描索引。
InnoDB的内存结构了解吗？
2025 年 04 月 04 日增补
InnoDB 的内存区域主要有两块，buffer pool 和 log buffer。 buffer pool 用于缓存数据页和索引页，提升读写性能；log buffer 用于缓存 redo log，提升写入性能。
数据页的结构了解吗？
InnoDB 的数据页由 7 部分组成，其中文件头、页头和文件尾的大小是固定的，分别为 38、56 和 8 个字节，用来标记该页的一些信息。行记录、空闲空间和页目录的大小是动态的，为实际的行记录存储空间。
来个表格总结下：
| 名称 | 中文名 | 大小（单位：B） | 描述 |
|---|---|---|---|
| File Header | 文件头部 | 38 | 页的一些通用信息 |
| Page Header | 页面头部 | 56 | 数据页专有的一些信息 |
| Infimum + Supermum | 最小记录和最大记录 | 26 | 两个虚拟的行记录 |
| User Records | 用户真实记录 | 不确定 | 实际存储的行记录内容 |
| Free Space | 空闲空间 | 不确定 | 页中尚未使用的空间 |
| Page Directory | 页面目录 | 不确定 | 页中的某些记录的相对位置 |
| File Trailer | 文件尾部 | 8 | 校验页是否完整 |
真实的记录会按照指定的行格式存储到 User Records 中。
每个数据页的 File Header 都有一个上一页和下一页的编号，所有的数据页会形成一个双向链表。
在 InnoDB 中，默认的页大小是 16KB。可以通过 show variables like 'innodb_page_size'; 查看。
推荐阅读：MySQL之数据页结构
- Java 面试指南（付费）收录的字节跳动面经同学 1 Java 后端技术一面面试原题：MyISAM 和 InnoDB 的区别有哪些？
- Java 面试指南（付费）收录的美团同学 9 一面面试原题：mysql存储的数据都是什么样的？
memo：2025 年 3 月 11 日修改至此。
27. InnoDB 的 Buffer Pool了解吗？（补充）
2024 年 11 月 04 日增补
Buffer Pool 是 InnoDB 存储引擎中的一个内存缓冲区，它会将经常使用的数据页、索引页加载进内存，读的时候先查询 Buffer Pool，如果命中就不用访问磁盘了。
如果没有命中，就从磁盘读取，并加载到 Buffer Pool，此时可能会触发页淘汰，将不常用的页移出 Buffer Pool。
写操作时不会直接写入磁盘，而是先修改内存中的页，此时页被标记为脏页，后台线程会定期将脏页刷新到磁盘。
Buffer Pool 可以显著减少磁盘的读写次数，从而提升 MySQL 的读写性能。
Buffer Pool 的默认大小是多少？
我本机上 InnoDB 的 Buffer Pool 默认大小是 128MB。
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
另外，在具有 1GB-4GB RAM 的系统上，默认值为系统 RAM 的 25%；在具有超过 4GB RAM 的系统上，默认值为系统 RAM 的 50%，但不超过 4GB。
InnoDB 对 LRU 算法的优化了解吗？
了解，InnoDB 对 LRU 算法进行了改良，最近访问的数据并不直接放到 LRU 链表的头部，而是放在一个叫 midpoiont 的位置。默认情况下，midpoint 位于 LRU 列表的 5/8 处。
比如 Buffer Pool 有 100 页，新页插入的位置大概是在第 80 页；当页数据被频繁访问后，再将其移动到 young 区，这样做的好处是热点页能长时间保留在内存中，不容易被挤出去。
----这部分是帮助大家理解 start，面试中可不背----
可以通过 innodb_old_blocks_pct 参数来调整 Buffer Pool 中 old 和 young 区的比例；通过 innodb_old_blocks_time 参数来调整页在 young 区的停留时间。
默认情况下，LRU 链表中 old 区占 37%；同一页再次访问提升的最小时间间隔是 1000 毫秒。
也就是说，如果某页在 1 秒内被多次访问，只会计算一次，不会立刻升级为热点页，防止短时间批量访问导致缓存污染。
----这部分是帮助大家理解 end，面试中可不背----
- Java 面试指南（付费）收录的美团面经同学 15 点评后端技术面试原题：说说 bufferpool
memo：2025 年 3 月 12 日修改至此。继续给大家一个喜报，今天有球友报喜说社招拿到了京东和美团的 offer，后续补充说滴滴也过了，我只能说太强了呀。
日志
28.🌟MySQL 日志文件有哪些？
有 6 大类，其中错误日志用于问题诊断，慢查询日志用于 SQL 性能分析，general log 用于记录所有的 SQL 语句，binlog 用于主从复制和数据恢复，redo log 用于保证事务持久性，undo log 用于事务回滚和 MVCC。
----这部分是帮助大家理解 start，面试中可不背----
①、错误日志（Error Log）：记录 MySQL 服务器启动、运行或停止时出现的问题。
②、慢查询日志（Slow Query Log）：记录执行时间超过 long_query_time 值的所有 SQL 语句。这个时间值是可配置的，默认情况下，慢查询日志功能是关闭的。
③、一般查询日志（General Query Log）：记录 MySQL 服务器的启动关闭信息，客户端的连接信息，以及更新、查询的 SQL 语句等。
④、二进制日志（Binary Log）：记录所有修改数据库状态的 SQL 语句，以及每个语句的执行时间，如 INSERT、UPDATE、DELETE 等，但不包括 SELECT 和 SHOW 这类的操作。
⑤、重做日志（Redo Log）：记录对于 InnoDB 表的每个写操作，不是 SQL 级别的，而是物理级别的，主要用于崩溃恢复。
⑥、回滚日志（Undo Log，或者叫事务日志）：记录数据被修改前的值，用于事务的回滚。
----这部分是帮助大家理解 end，面试中可不背----
请重点说说 binlog？
推荐阅读：带你了解 MySQL Binlog 不为人知的秘密
binlog 是一种二进制日志，会在磁盘上记录数据库的所有修改操作。
如果误删了数据，就可以使用 binlog 进行回退到误删之前的状态。
# 步骤1：恢复全量备份
mysql -u root -p < full_backup.sql
# 步骤2：应用Binlog到指定时间点
mysqlbinlog --start-datetime="2025-03-13 14:00:00" --stop-datetime="2025-03-13 15:00:00" binlog.000001 | mysql -u root -p
如果要搭建主从复制，就可以让从库定时读取主库的 binlog。
MySQL 提供了三种格式的 binlog：Statement、Row 和 Mixed，分别对应 SQL 语句级别、行级别和混合级别，默认为行级别。
从后缀名上来看，binlog 文件分为两类：以 .index 结尾的索引文件，以 .00000* 结尾的二进制日志文件。
binlog 默认是没有启用的。
生产环境中是一定要启用的，可以通过在 my.cnf 文件中配置 log_bin 参数，以启用 binlog。
log_bin = mysql-bin #开启binlog
#mysql-bin.*日志文件最大字节（单位：字节）
#设置最大100MB
max_binlog_size=104857600
#设置了只保留7天BINLOG（单位：天）
expire_logs_days = 7
#binlog日志只记录指定库的更新
#binlog-do-db=db_name
#binlog日志不记录指定库的更新
#binlog-ignore-db=db_name
#写缓冲多少次，刷一次磁盘，默认0
sync_binlog=0
binlog 的配置参数都了解哪些？
log_bin = mysql-bin 用于启用 binlog，这样就可以在 MySQL 的数据目录中找到 db-bin.000001、db-bin.000002 等日志文件。
max_binlog_size=104857600 用于设置每个 binlog 文件的大小，不建议设置太大，网络传送起来比较麻烦。
当 binlog 文件达到 max_binlog_size 时，MySQL 会关闭当前文件并创建一个新的 binlog 文件。
expire_logs_days = 7 用于设置 binlog 文件的自动过期时间为 7 天。过期的 binlog 文件会被自动删除。防止长时间累积的 binlog 文件占用过多存储空间，技术派实战项目所在的项目是丐版服务器，所以这个配置很重要。
binlog-do-db=db_name，指定哪些数据库表的更新应该被记录。
binlog-ignore-db=db_name，指定忽略哪些数据库表的更新。
sync_binlog=0，设置每多少次 binlog 写操作会触发一次磁盘同步操作。默认值为 0，表示 MySQL 不会主动触发同步操作，而是依赖操作系统的磁盘缓存策略。
即当执行写操作时，数据会先写入缓存，当缓存区满了再由操作系统将数据一次性刷入磁盘。
如果设置为 1，表示每次 binlog 写操作后都会同步到磁盘，虽然可以保证数据能够及时写入磁盘，但会降低性能。
可以通过 show variables like '%log_bin%'; 查看 binlog 是否开启。
有了binlog为什么还要undolog redolog？
binlog 属于 Server 层，与存储引擎无关，无法直接操作物理数据页。而 redo log 和 undo log 是 InnoDB 存储引擎实现 ACID 的基石。
binlog 关注的是逻辑变更的全局记录；redo log 用于确保物理变更的持久性，确保事务最终能够刷盘成功；undo log 是逻辑逆向操作日志，记录的是旧值，方便恢复到事务开始前的状态。
另外一种回答方式。
binlog 会记录整个 SQL 或行变化；redo log 是为了恢复“已提交但未刷盘”的数据，undo log 是为了撤销未提交的事务。
以一次事务更新为例：
# 开启事务
BEGIN;
# 更新数据
UPDATE users SET age = age + 1 WHERE id = 1;
# 提交事务
COMMIT;
事务开始的时候会生成 undo log，记录更新前的数据，比如原值是 18：
undo log: id=1, age=18
修改数据的时候，会将数据写入到 redo log。
比如数据页 page_id=123 上，id=1 的用户被更新为 age=26：
redo log (prepare):
page_id=123, offset=0x40, before=18, after=26
等事务提交的时候，redo log 刷盘，binlog 刷盘。
binlog 写完之后，redo log 的状态会变为 commit：
redo log (commit):
page_id=123, offset=0x40, before=18, after=26
binlog 如果是 Statement 格式，会记录一条 SQL 语句：
UPDATE users SET age = age + 1 WHERE id = 1;
binlog 如果是 Row 格式，会记录：
表：users
before: id=1, age=18
after: id=1, age=26
随后，后台线程会将 redo log 中的变更异步刷新到磁盘。
memo：2025 年 3 月 13 日修改至此。有球友报喜，字节二面过了，找暑期顺利的不可思议，八股直接吟唱面渣。
说说 redo log 的工作机制？
当事务启动时，MySQL 会为该事务分配一个唯一标识符。
在事务执行过程中，每次对数据进行修改，MySQL 都会生成一条 Redo Log，记录修改前后的数据状态。
这些 Redo Log 首先会被写入内存中的 Redo Log Buffer。
当事务提交时，MySQL 再将 Redo Log Buffer 中的记录刷新到磁盘上的 Redo Log 文件中。
只有当 Redo Log 成功写入磁盘，事务才算真正提交成功。
当 MySQL 崩溃重启时，会先检查 Redo Log。对于已提交的事务，MySQL 会重放 Redo Log 中的记录。
对于未提交的事务，MySQL 会通过 Undo Log 回滚这些修改，确保数据恢复到崩溃前的一致性状态。
Redo Log 是循环使用的，当文件写满后会覆盖最早的记录。
为避免覆盖未持久化的记录，MySQL 会定期执行 CheckPoint 操作，将内存中的数据页刷新到磁盘，并记录 CheckPoint 点。
重启时，MySQL 只会重放 CheckPoint 之后的 Redo Log，从而提高恢复效率。
redo log 文件的大小是固定的吗？
redo log 文件是固定大小的，通常配置为一组文件，使用环形方式写入，旧的日志会在空间需要时被覆盖。
命名方式为 ib_logfile0、iblogfile1、、、iblogfilen。默认 2 个文件，每个文件大小为 48MB。
可以通过 show variables like 'innodb_log_file_size'; 查看 redo log 文件的大小；通过 show variables like 'innodb_log_files_in_group'; 查看 redo log 文件的数量。
说说 WAL？
WAL——Write-Ahead Logging。
预写日志是 InnoDB 实现事务持久化的核心机制，它的思想是：先写日志再刷磁盘。
即在修改数据页之前，先将修改记录写入 Redo Log。
这样的话，即使数据页尚未写入磁盘，系统崩溃时也能通过 Redo Log 恢复数据。
----这部分是帮助大家理解 start，面试中可不背----
解释一下为什么需要 WAL：
- 数据最终是要写入磁盘的，但磁盘 IO 很慢；
- 如果每次更新都立刻把数据页刷盘，性能很差；
- 如果还没写入磁盘就宕机，事务会丢失。
WAL 的好处是更新时不直接写数据页，而是先写一份变更记录到 redo log，后台再慢慢把真正的数据页刷盘，一举多得。
----这部分是帮助大家理解 end，面试中可不背----
- Java 面试指南（付费）收录的华为面经同学 8 技术二面面试原题：MySQL 中的 bin log 的作用是什么？
- Java 面试指南（付费）收录的美团面经同学 2 Java 后端技术一面面试原题：说说 MySQL 的三大日志？
- Java 面试指南（付费）收录的字节跳动面经同学 21 抖音商城一面面试原题：redolog undolog binlog，有了binlog为什么还要undolog redolog，redolog的工作机制，说说 WAL
memo：2025 年 3 月 14 日修改至此。今天修改简历的时候，碰到一位比赛经历非常丰富的球友，大家在校期间如果有时间，也可以冲一下。
29.binlog 和 redo log 有什么区别？
binlog 由 MySQL 的 Server 层实现，与存储引擎无关；redo log 由 InnoDB 存储引擎实现。
binlog 记录的是逻辑日志，包括原始的 SQL 语句或者行数据变化，例如“将 id=2 这行数据的 age 字段+1”。
redo log 记录物理日志，即数据页的具体修改，例如“将 page_id=123 上 offset=0x40 的数据从 18 修改为 26”。
binlog 是追加写入的，文件写满后会新建文件继续写入，不会覆盖历史日志，保存的是全量操作记录；redo log 是循环写入的，空间是固定的，写满后会覆盖旧的日志，仅保存未刷盘的脏页日志，已持久化的数据会被清除。
另外，为保证两种日志的一致性，innodb 采用了两阶段提交策略，redo log 在事务执行过程中持续写入，并在事务提交前进入 prepare 状态；binlog 在事务提交的最后阶段写入，之后 redo log 会被标记为 commit 状态。
可以通过回放 binlog 实现数据同步或者恢复到指定时间点；redo log 用来确保事务提交后即使系统宕机，数据仍然可以通过重放 redo log 恢复。
- Java 面试指南（付费）收录的美团同学 2 优选物流调度技术 2 面面试原题：redo log、bin log
30.🌟为什么要两阶段提交呢？
为了保证 redo log 和 binlog 中的数据一致性，防止主从复制和事务状态不一致。
为什么 2PC 能保证 redo log 和 binlog 的强⼀致性？
假如 MySQL 在预写 redo log 之后、写入 binlog 之前崩溃。那么 MySQL 重启后 InnoDB 会回滚该事务，因为 redo log 不是提交状态。并且由于 binlog 中没有写入数据，所以从库也不会有该事务的数据。
假如 MySQL 在写入 binlog 之后、redo log 提交之前崩溃。那么 MySQL 重启后 InnoDB 会提交该事务，因为 redo log 是完整的 prepare 状态。并且由于 binlog 中有写入数据，所以从库也会同步到该事务的数据。
伪代码如下所示：
// 事务开始
begin;
// try
{
// 执行 SQL
execute SQL;
// 写入 redo log 并标记为 prepare
write redo log prepare xid;
// 写入 binlog
write binlog xid sql;
// 提交 redo log
commit redo log xid;
}
// catch
{
// 回滚 redo log
innodb rollback redo log xid;
}
// 事务结束
end;
XID 了解吗？
XID 是 binlog 中用来标识事务提交的唯一标识符。
在事务提交时，会写入一个 XID_EVENT 到 binlog，表示这个事务真正完成了。
Log_name | Pos | Event_type | Server_id | End_log_pos | Info
| mysql-bin.000003 | 2005 | Gtid | 1013307 | 2070 | SET @@SESSION.GTID_NEXT= 'f971d5f1-d450-11ec-9e7b-5254000a56df:11' |
| mysql-bin.000003 | 2070 | Query | 1013307 | 2142 | BEGIN |
| mysql-bin.000003 | 2142 | Table_map | 1013307 | 2187 | table_id: 109 (test.t1) |
| mysql-bin.000003 | 2187 | Write_rows | 1013307 | 2227 | table_id: 109 flags: STMT_END_F |
| mysql-bin.000003 | 2227 | Xid | 1013307 | 2258 | COMMIT /* xid=121 */
它不仅用于主从复制中事务完整性的判断，也在崩溃恢复中对 redo log 和 binlog 的一致性校验起到关键作用。
XID 可以帮助 MySQL 判断哪些 redo log 是已提交的，哪些是未提交需要回滚的，是两阶段提交机制中非常关键的一环。
memo：2025 年 3 月 16 日修改至此。
31.🌟redo log 的写入过程了解吗？
InnoDB 会先将 Redo Log 写入内存中的 Redo Log Buffer，之后再以一定的频率刷入到磁盘的 Redo Log File 中。
哪些场景会触发 redo log 的刷盘动作？
比如说 Redo Log Buffer 的空间不足时，事务提交时，触发 Checkpoint 时，后台线程定期刷盘时。
不过，Redo Log Buffer 刷盘到 Redo Log File 还会涉及到操作系统的磁盘缓存策略，可能不会立即刷盘，而是等待一定时间后才刷盘。
innodb_flush_log_at_trx_commit 参数你了解多少？
innodb_flush_log_at_trx_commit 参数是用来控制事务提交时，Redo Log 的刷盘策略，一共有三种。
0 表示事务提交时不刷盘，而是交给后台线程每隔 1 秒执行一次。这种方式性能最好，但是在 MySQL 宕机时可能会丢失一秒内的事务。
1 表示事务提交时会立即刷盘，确保事务提交后数据就持久化到磁盘。这种方式是最安全的，也是 InnoDB 的默认值。
2 表示事务提交时只把 Redo Log Buffer 写入到 Page Cache，由操作系统决定什么时候刷盘。操作系统宕机时，可能会丢失一部分数据。
一个没有提交事务的 redo log，会不会刷盘？
InnoDB 有一个后台线程，每隔 1 秒会把 Redo Log Buffer 中的日志写入到文件系统的缓存中，然后调用刷盘操作。
因此，一个没有提交事务的 Redo Log 也可能会被刷新到磁盘中。
另外，如果当 Redo Log Buffer 占用的空间即将达到 innodb_log_buffer_size 的一半时，也会触发刷盘操作。
memo：2025 年 3 月 17 日修改至此。已经有球友发来喜报，暑期实习拿到恒生电子的暑期实习了。
Redo Log Buffer 是顺序写还是随机写？
MySQL 在启动后会向操作系统申请一块连续的内存空间作为 Redo Log Buffer，并将其分为若干个连续的 Redo Log Block。
那为了提高写入效率，Redo Log Buffer 采用了顺序写入的方式，会先往前面的 Redo Log Block 中写入，当写满后再往后面的 Block 中写入。
于此同时，InnoDB 还提供了一个全局变量 buf_free，来控制后续的 redo log 记录应该写入到 block 中的哪个位置。
buf_next_to_write 了解吗？
buf_next_to_write 指向 Redo Log Buffer 中下一次需要写入硬盘的起始位置。
而 buf_free 指向的是 Redo Log Buffer 中空闲区域的起始位置。
了解 MTR 吗？
Mini Transaction 是 InnoDB 内部用于操作数据页的原子操作单元。
mtr_t mtr;
mtr_start(&mtr);
// 1. 加锁
// 对待访问的index加锁
mtr_s_lock(rw_lock_t, mtr);
mtr_x_lock(rw_lock_t, mtr);
// 对待读写的page加锁
mtr_memo_push(mtr, buf_block_t, MTR_MEMO_PAGE_S_FIX);
mtr_memo_push(mtr, buf_block_t, MTR_MEMO_PAGE_X_FIX);
// 2. 访问或修改page
btr_cur_search_to_nth_level
btr_cur_optimistic_insert
// 3. 为修改操作生成redo
mlog_open
mlog_write_initial_log_record_fast
mlog_close
// 4. 持久化redo，解锁
mtr_commit(&mtr);
多个事务的 Redo Log 会以 MTR 为单位交替写入到 Redo Log Buffer 中，假如事务 1 和事务 2 均有两个 MTR，一旦某个 MTR 结束，就会将其生成的若干条 Redo Log 记录顺序写入到 Redo Log Buffer 中。
也就是说，一个 MTR 会包含一组 Redo Log 记录，是 MySQL 崩溃后恢复事务的最小执行单元。
Redo Log Block 的结构了解吗？
Redo Log Block 由日志头、日志体和日志尾组成，一共占用 512 个字节，其中日志头占用 12 个字节，日志尾占用 4 个字节，剩余的 496 个字节用于存储日志体。
日志头包含了当前 Block 的序列号、第一条日志的序列号、类型等信息。
| 字段 | 作用 |
|---|---|
| LOG_BLOCK_HDR_NO | 当前 Block 的序号，假如把 Redo Log Buffer 看成一个数组，那么 LOG_BLOCK_HDR_NO 就相当于 Block 在 Buffer 中的下标。 |
| LOG_BLOCK_HDR_DATA_LEN | Block 已使用的字节数，初始值为 12，也就是日志头的长度；如果日志体被写满，值增长为 512。 |
| LOG_BLOCK_FIRST_REC_GROUP | 该 Block 中第一个 MTR 起始处的偏移量 |
| LOG_BLOCK_CHECKPOINT_NO | Block 最后被写入时的checkpoint |
日志尾主要存储的是 LOG_BLOCK_CHECKSUM，也就是 Block 的校验和，主要用于判断 Block 是否完整。
Redo Log Block 为什么设计成 512 字节？
因为机械硬盘的物理扇区大小通常为 512 字节，Redo Log Block 也设计为同样的大小，就可以确保每次写入都是整数个扇区，减少对齐开销。
比如说操作系统的页缓存默认为 4KB，8 个 Redo Log Block 就可以组合成一个页缓存单元，从而提升 Redo Log Buffer 的写入效率。
memo：2025 年 3 月 18 日修改至此。
LSN 了解吗？
Log Sequence Number 是一个 8 字节的单调递增整数，用来标识事务写入 redo log 的字节总量，存在于 redo log、数据页头部和 checkpoint 中。
----这部分是帮助大家理解 start，面试中可不背----
MySQL 在第一次启动时，LSN 的初始值并不为 0，而是 8704；当 MySQL 再次启动时，会继续使用上一次服务停止时的 LSN。
在计算 LSN 的增量时，不仅需要考虑 log block body 的大小，还需要考虑 log block header 和 log block tail 中部分字节数。
比如说在上图中，事务 3 的 MTR 总量为 300 字节，那么写入到 Redo Log Buffer 中的 LSN 会增长为 8704 + 300 + 12 = 9016。
假如事务 4 的 MTR 总量为 900 字节，那么再次写入到 Redo Log Buffer 中的 LSN 会增长为 9016 + 900 + 12*2 + 4*2 = 9948。
2 个 12 字节的 log block header + 2 个 4 字节的 log block tail。
----这部分是帮助大家理解 end，面试中可不背----
核心作用有三个：
第一，redo log 按照 LSN 递增顺序记录所有数据的修改操作。LSN 的递增量等于每次写入日志的字节数。
第二，InnoDB 的每个数据页头部中，都会记录该页最后一次刷新到磁盘时的 LSN。如果数据页的 LSN 小于 redo log 的 LSN，说明该页需要从日志中恢复；否则说明该页已更新。
第三，checkpoint 通过 LSN 记录已刷新到磁盘的数据页位置，减少恢复时需要处理的日志。
----这部分是帮助大家理解 start，面试中可不背----
| 场景 | LSN 的作用 |
|---|---|
| 🔁 redo log 记录 | 每条 redo log 对应一个唯一的 LSN |
| 📄 数据页刷盘 | 每个数据页会记录当前刷盘时的 LSN（FIL_PAGE_LSN） |
| ⛳ Checkpoint | 表示“脏页已经刷盘，可以释放 redo”的安全点 |
| 💥 崩溃恢复 | 重启时从 checkpoint LSN 开始重放 redo log |
可以通过 show engine innodb status; 查看当前的 LSN 信息。
- Log sequence number：当前系统最大 LSN（已生成的日志总量）。
- Log flushed up to：已写入磁盘的 redo log LSN。
- Pages flushed up to：已刷新到数据页的 LSN。
- Last checkpoint at：最后一次检查点的 LSN，表示已持久化的数据状态。
----这部分是帮助大家理解 end，面试中可不背----
memo：2025 年 3 月 19 日修改至此。今天有读者问怎么付费购买纸质版面渣逆袭，说看到网友有这个，好羡慕啊。说实话，第一眼看到这个封面，真的觉得挺惊艳（虽然是我设计的）。😄
Checkpoint 了解多少？
Checkpoint 是 InnoDB 为了保证事务持久性和回收 redo log 空间的一种机制。
它的作用是在合适的时机将部分脏页刷入磁盘，比如说 buffer pool 的容量不足时。并记录当前 LSN 为 Checkpoint LSN，表示这个位置之前的 redo log file 已经安全，可以被覆盖了。
MySQL 崩溃恢复时只需要从 Checkpoint 之后开始恢复 redo log 就可以了，这样可以最大程度减少恢复所花费的时间。
redo log file 的写入是循环的，其中有两个标记位置非常重要，也就是 Checkpoint 和 write pos。
write pos 是 redo log 当前写入的位置，Checkpoint 是可以被覆盖的位置。
当 write pos 追上 Checkpoint 时，表示 redo log 日志已经写满。这时候就要暂停写入并强制刷盘，释放可覆写的日志空间。
关于redo log 的调优参数了解多少？
如果是高并发写入的电商系统，可以最大化写入吞吐量，容忍秒级数据丢失的风险。
innodb_flush_log_at_trx_commit = 2
sync_binlog = 1000
innodb_redo_log_capacity = 64G
innodb_io_capacity = 5000
innodb_lru_scan_depth = 512
innodb_log_buffer_size = 256M
如果是金融交易系统，需要保证数据零丢失，接受较低的吞吐量。
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_redo_log_capacity = 32G
innodb_io_capacity = 2000
innodb_lru_scan_depth = 1024
核心参数一览表：
| 参数名 | 控制内容 | 影响点 |
|---|---|---|
| innodb_log_file_size | 每个 redo log 文件大小 | 总 redo 空间、恢复时间 |
| innodb_log_files_in_group | redo log 文件个数 | 配合文件大小决定总容量 |
| innodb_log_buffer_size | redo log buffer 缓冲区大小 | 是否频繁刷盘、写入性能 |
| innodb_flush_log_at_trx_commit | redo 刷盘策略 | 安全性 vs TPS |
| innodb_max_dirty_pages_pct | 脏页比例阈值 | 何时触发刷盘 / Checkpoint |
| innodb_io_capacity | 后台刷盘速度 | 限制 checkpoint 刷盘压力 |
总结：
- 对数据一致性要求高的场景，如金融交易使用
innodb_flush_log_at_trx_commit=1，对写入吞吐量敏感的场景，如日志采集可以使用 =2 或 =0，需要结合 sync_binlog 参数 - sync_binlog 参数控制 binlog 的刷盘策略，可以设置为 0、1、N，0 表示依赖系统刷盘，1 表示每次事务提交都刷盘（推荐与
innodb_flush_log_at_trx_commit=1搭配），N=1000 表示累计 1000 次事务后刷盘 - innodb_redo_log_capacity 动态调整 Redo Log 总容量，可以根据业务负载情况调整，建议设置为 1 小时写入量的峰值（如每秒 10MB 写入则设为 36GB）
- innodb_io_capacity 定义 InnoDB 后台线程的每秒 I/O 操作上限，直接影响脏页刷新速率；机械硬盘建议 200-500，SSD 建议 1000-2000，NVMe SSD 可设为 5000+
- innodb_lru_scan_depth 控制每个缓冲池实例中 LRU 列表的扫描深度，决定每秒可刷新的脏页数量，默认值 1024 适用于多数场景，I/O 密集型负载可适当降低（如 512），减少 CPU 开销。
memo：2025 年 3 月 20 日修改至此。有球友报喜说拿到了滴滴的测开实习 offer，恭喜恭喜！
SQL 优化
32.🌟什么是慢 SQL？
推荐阅读：慢 SQL 优化一点小思路
MySQL 中有一个叫 long_query_time 的参数，原则上执行时间超过该参数值的 SQL 就是慢 SQL，会被记录到慢查询日志中。
----这部分是帮助大家理解 start，面试中可不背----
可通过 show variables like 'long_query_time'; 查看当前的 long_query_time 的参数值。
----这部分是帮助大家理解 end，面试中可不背----
SQL 的执行过程了解吗？
了解。
SQL 的执行过程大致可以分为六个阶段：连接管理、语法解析、语义分析、查询优化、执行器调度、存储引擎读写等。Server 层负责理解和规划 SQL 怎么执行，存储引擎层负责数据的真正读写。
----这部分是帮助大家理解 start，面试中可不背----
来详细拆解一下：
- 客户端发送 SQL 语句给 MySQL 服务器。
- 如果查询缓存打开则会优先查询缓存，缓存中有对应的结果就直接返回。不过，MySQL 8.0 已经移除了查询缓存。这部分的功能正在被 Redis 等缓存中间件取代。
- 分析器对 SQL 语句进行语法分析，判断是否有语法错误。
- 搞清楚 SQL 语句要干嘛后，MySQL 会通过优化器生成执行计划。
- 执行器调用存储引擎的接口，执行 SQL 语句。
SQL 执行过程中，优化器通过成本计算预估出执行效率最高的方式，基本的预估维度为：
- IO 成本：从磁盘读取数据到内存的开销。
- CPU 成本：CPU 处理内存中数据的开销。
基于这两个维度，可以得出影响 SQL 执行效率的因素有：
①、IO 成本，数据量越大，IO 成本越高。所以要尽量查询必要的字段；尽量分页查询；尽量通过索引加快查询。
②、CPU 成本，尽量避免复杂的查询条件，如有必要，考虑对子查询结果进行过滤。
----这部分是帮助大家理解 end，面试中可不背----
如何优化慢 SQL 呢？
首先，需要找到那些比较慢的 SQL，可以通过启用慢查询日志，记录那些超过指定执行时间的 SQL 查询。
也可以使用 show processlist; 命令查看当前正在执行的 SQL 语句，找出执行时间较长的 SQL。
或者在业务基建中加入对慢 SQL 的监控，常见的方案有字节码插桩、连接池扩展、ORM 框架扩展等。
然后，使用 EXPLAIN 查看慢 SQL 的执行计划，看看有没有用索引，大部分情况下，慢 SQL 的原因都是因为没有用到索引。
EXPLAIN SELECT * FROM your_table WHERE conditions;
最后，根据分析结果，通过添加索引、优化查询条件、减少返回字段等方式进行优化。
慢sql日志怎么开启？
编辑 MySQL 的配置文件 my.cnf，设置 slow_query_log 参数为 1。
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2 # 记录执行时间超过2秒的查询
然后重启 MySQL 就好了。
也可以通过 set global 命令动态设置。
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 2;
- Java 面试指南（付费）收录的腾讯云智面经同学 16 一面面试原题：场景题：sql 查询很慢怎么排查
- Java 面试指南（付费）收录的快手面经同学 5 面试原题：慢sql日志怎么开启？
- Java 面试指南（付费）收录的美团面经同学 3 Java 后端技术一面面试原题：如何判断sql的效率，怎样排查效率比较低的sql
- Java 面试指南（付费）收录的作业帮面经同学 1 Java 后端一面面试原题：mysql中如何定位慢查询
- Java 面试指南（付费）收录的同学 1 贝壳找房后端技术一面面试原题：慢查询怎么分析
- Java 面试指南（付费）收录的腾讯面经同学 27 云后台技术一面面试原题：如何优化慢查询语句？
- Java 面试指南（付费）收录的虾皮面经同学 13 一面面试原题：mysql慢查询
memo：2025 年 3 月 21 日修改至此。今天有球友报喜说拿到了 wxg 的实习 offer，阿里云和美团也在进行当中，真的 tql。
33.🌟你知道哪些方法来优化 SQL？
SQL 优化的方法非常多，但本质上就一句话：尽可能少地扫描、尽快地返回结果。
最常见的做法就是加索引、改写 SQL 让它用上索引，比如说使用覆盖索引、让联合索引遵守最左前缀原则等。
如何利用覆盖索引？
覆盖索引的核心是“查询所需的字段都在同一个索引里”，这样 MySQL 就不需要回表，直接从索引中返回结果。
实际使用中，我会优先考虑把 WHERE 和 SELECT 涉及的字段一起建联合索引，并通过 EXPLAIN 观察结果是否有 Using index，确认命中索引。
----这部分是帮助大家理解 start，面试中可不背----
举个例子，现在要从 test 表中查询 city 为上海的 name 字段。
select name from test where city='上海'
如果仅在 city 字段上添加索引，那么这条查询语句会先通过索引找到 city 为上海的行，然后再回表查询 name 字段。
为了避免回表查询，可以在 city 和 name 字段上建立联合索引，这样查询结果就可以直接从索引中获取。
alter table test add index index1(city,name);
----这部分是帮助大家理解 end，面试中可不背----
如何正确使用联合索引？
使用联合索引最重要的一条是遵守最左前缀原则，也就是查询条件需要从索引的左侧字段开始。
----这部分是帮助大家理解 start，面试中可不背----
比如说我们创建了一个三列的联合索引。
CREATE INDEX idx_name_age_sex ON user(name, age, sex);
我们来看一下什么样的查询条件可以用到这个索引：
| 查询条件 | 能否用上 idx_name_age_sex？ | 说明 |
|---|---|---|
| WHERE name = 'itwanger' | ✅ 可以 | 匹配第一列，命中索引 |
| WHERE name = 'itwanger' AND age=20 | ✅ 可以 | 匹配前两列，命中索引 |
| WHERE age = 20 | ❌ 不行 | 第一列没用上，索引失效 |
| WHERE name='itwanger' AND sex='女' | ✅ 部分可用（只用前一列） | age 被跳过，后面的列无法使用 |
| WHERE name LIKE 'it%' | ✅ 可以（前缀匹配） | name 是前缀匹配，不影响使用 |
| WHERE name LIKE '%wanger%' | ❌ 不行 | 通配符在前，不能用索引 |
----这部分是帮助大家理解 end，面试中可不背----
如何进行分页优化？
分页优化的核心是避免深度偏移带来的全表扫描，可以通过两种方式来优化：延迟关联和添加书签。
延迟关联适用于需要从多个表中获取数据且主表行数较多的情况。它首先从索引表中检索出需要的行 ID，然后再根据这些 ID 去关联其他的表获取详细...
3人已点赞
热门评论
7 条评论
回复