跳转至

Mysql 面试题

Mysql 基础

MySQL 的优点⭐️

开源免费 + 生态强大 + 功能全面

  • 开源免费：任何人都可以零成本使用，社区活跃，生态完善（ORM、连接池、中间件等工具链成熟）
  • 功能强大：InnoDB 引擎支持 ACID 事务、外键、行级锁、MVCC、B+ 树索引
  • 架构成熟：主从复制、读写分离、分库分表方案完善，配合中间件可轻松应对高并发场景

Mysql 字段类型

mysql01

  • 数值类型：整型（TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT）、浮点型（FLOAT 和 DOUBLE）、定点型（DECIMAL）
  • 字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等，最常用的是 CHAR 和 VARCHAR。
  • 日期时间类型：YEAR、TIME、DATE、DATETIME 和 TIMESTAMP 等。
类型 占用字节 有符号 (Signed) 范围 无符号 (Unsigned) 范围 常用场景建议
TINYINT 1 字节 -128 ~ 127 0 ~ 255 状态 (0/1)、性别、枚举值
SMALLINT 2 字节 -32,768 ~ 32,767 0 ~ 65,535 小范围 ID、年份、排序序号
MEDIUMINT 3 字节 约 -838万 ~ 838万 0 ~ 16,777,215 中等规模 ID、城市代码
INT 4 字节 约 -21.4亿 ~ 21.4亿 0 ~ 42.9亿 标准自增 ID、用户 UID
BIGINT 8 字节 \(-2^{63}\) ~ \(2^{63}-1\) 0 ~ \(1.84 \times 10^{19}\) 超大规模 ID、雪花算法 ID、毫秒级时间戳

整数类型的 UNSIGNED 属性有什么用？⭐️

UNSIGNED 属性能把 Mysql 中的数值类型转换为无符号数值类型，该类型不表示任何负数，正数的表示范围提升一倍，例如 TinyINT 默认范围是 \([-2^7,2^7 - 1]\) ，但是被UNSIGNED 修饰的 TINYINT表示的范围就是\([0,2^8 - 1]\)

CREATE TABLE test_unsigned (
    id INT AUTO_INCREMENT PRIMARY KEY,
    val_signed TINYINT,          -- 有符号：-128 到 127
    val_unsigned TINYINT UNSIGNED -- 无符号：0 到 255
);

CHAR 和 VARCHAR 的区别是什么？

CHAR 和 VARCHAR 是最常用到的字符串类型，两者的主要区别在于：CHAR 是定长字符串，VARCHAR 是变长字符串。
CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。

VARCHAR(100)和 VARCHAR(10)的区别是什么？

VARCHAR(100)和 VARCHAR(10)都是变长类型，表示能存储最多 100 个字符和 10 个字符。因此，VARCHAR (100) 可以满足更大范围的字符存储需求，有更好的业务拓展性。而 VARCHAR(10)存储超过 10 个字符时，就需要修改表结构才可以。

DECIMAL 和 FLOAT/DOUBLE 的区别是什么?

DECIMAL 和 FLOAT 的区别是：DECIMAL 是定点数，FLOAT/DOUBLE 是浮点数。DECIMAL 可以存储精确的小数值，FLOAT/DOUBLE 只能存储近似的小数值。

DECIMAL 用于存储具有精度要求的小数，例如与货币相关的数据，可以避免浮点数带来的精度损失。

为什么不推荐使用 TEXT 和 BLOB？

在日常开发中，很少使用 TEXT 类型，但偶尔会用到，而 BLOB 类型则基本不常用。如果预期长度范围可以通过 VARCHAR 来满足，建议避免使用 TEXT。

数据库规范通常不推荐使用 BLOB 和 TEXT 类型，这两种类型具有一些缺点和限制，例如：

  • 不能有默认值。
  • 在使用临时表时无法使用内存临时表，只能在磁盘上创建临时表（《高性能 MySQL》书中有提到）。
  • 检索效率较低。
  • 不能直接创建索引，需要指定前缀长度。
  • 可能会消耗大量的网络和 IO 带宽。
  • 可能导致表上的 DML 操作变慢。

“之所以不推荐使用 TEXT/BLOB，核心原因在于它们会‘拖慢整个数据库的运转’**。

首先是性能黑洞：MySQL 处理这类大字段时，无法利用内存临时表，必须在磁盘上读写，查询效率会断崖式下跌。 其次是资源浪费：它们会撑大整行数据，导致 IO 带宽被无效占满，连带着 DML 更新操作也变慢。 最后是限制太多：它们不能设默认值，索引也必须指定前缀，用起来很不灵活。

所以我们的原则是： 只要 VARCHAR 能装下，就绝不用 TEXT；如果数据实在太大，宁愿把内容存到 OSS（对象存储），数据库里只存个 URL 链接。”

TEXT 是什么

TEXT 类型类似于 CHAR（0-255 字节）和 VARCHAR（0-65,535 字节），但可以存储更长的字符串，即长文本数据，例如博客内容。

BLOG 是什么

BLOB 类型主要用于存储二进制大对象，例如图片、音视频等文件。

DATETIME 和 TIMESTAMP 的区别是什么？如何选择？⭐️

  • DATETIME 就像录像机，你存入什么时间，它就永远显示什么时间，与时区无关。它空间占用大（8 字节），但范围广（可到 9999 年），适合存储出生日期、历史记录等静态时间。

  • TIMESTAMP 则是真正的时间戳，它存的是格林威治时间（UTC），读取时会根据当前服务器时区自动转换。它空间利用率高（4 字节），但有‘2038年危机’，适合记录订单创建、数据更新等与系统时间强相关的日志。

特性 DATETIME TIMESTAMP
存储字节 8 字节（5.6.4后为 5 字节） 4 字节（更省空间）
时间范围 1000-01-01 ~ 9999-12-31 1970-01-01 ~ 2038-01-19
时区敏感 无关（存什么读什么） 相关（随数据库时区变化）
默认值 早期不支持自动填充 支持 DEFAULT CURRENT_TIMESTAMP
场景 历史数据、生日、长期计划 日志、修改时间、跨时区业务
  • 首选 DATETIME：现在磁盘空间不值钱，MySQL 5.6 以后 DATETIME 的空间也优化到了 5 字节，且能避免 2038 年失效的问题。
  • 特殊情况选 TIMESTAMP：如果你的业务是全球化的，或者需要频繁利用 ON UPDATE CURRENT_TIMESTAMP 自动更新修改时间。

NULL 和 '' 的区别是什么？

简单来说，NULL 是“没有数据”，而 ''（空字符串）是“有数据，但长度为 0”。

维度 NULL '' (空字符串)
实际含义 未知、不存在、未填充 已知、长度为 0 的空值
空间占用 占用额外 1 bit（NULL 标志位） 不占用空间（长度为 0）
长度计算 LENGTH(NULL) 结果仍是 NULL LENGTH('') 结果是 0
比较运算 必须用 IS NULL 可以用 = ''
对索引影响 影响索引效率，增加计算复杂度 像普通字符串一样正常索引

  • 逻辑判断陷阱： 在 SQL 中，NULL 不等于任何值，甚至 NULL != NULL。如果你写 WHERE col != 'abc'，那么 col 为 NULL 的行会直接被漏掉，不会出现在结果集里。因为与NULL比较返回的逻辑值是 UNKNOWN

  • 聚合函数跳过： COUNT(column) 会自动忽略 NULL 值，但会统计空字符串。这可能导致你的统计报表数额不对。

“在 SQL 中，NULL 代表的是缺失的未知信息。任何对 NULL 的比较运算（无论是 = 还是 !=）都会返回 UNKNOWN。由于 WHERE 只认 TRUE，所以 NULL 行会在任何普通比较中被『无情过滤』。”因此，Mysql 不建议使用 NULL 作为列默认值。

Boolean 类型如何表示？⭐️⭐️

MySQL 中没有专门的布尔类型，而是用 TINYINT(1) 类型来表示布尔值。TINYINT(1) 类型可以存储 0 或 1，分别对应 false 或 true。

-- 1. 创建表，使用三种写法：BOOLEAN, BOOL, TINYINT(1)
CREATE TABLE test_bool (
    id INT AUTO_INCREMENT PRIMARY KEY,
    is_active BOOLEAN,       -- 写法一
    is_deleted BOOL,         -- 写法二
    is_valid TINYINT(1)      -- 写法三（MySQL 最终的形式）
);

-- 2. 查看表结构，你会发现所有类型都变成了 tinyint(1)
DESC test_bool;
-- 也可以看建表语句
SHOW CREATE TABLE test_bool;

TINYINT(1)是什么意思？

“TINYINT(1) 里的 1 代表的是 『显示宽度』，而不是存储精度。它不影响该字段能存多大的数，也不影响占用的物理空间。

它最大的存在意义是 『语义化』：看到 1 我们就知道这个字段是当做布尔值用的。此外，某些编程语言的驱动也会利用这个数字把字段自动映射为 Boolean 类型。但在 MySQL 8.0 之后，这个用法已经不推荐使用了。” 

CREATE TABLE test_width (
    val1 TINYINT(1) ZEROFILL,
    val2 TINYINT(4) ZEROFILL
);

INSERT INTO test_width VALUES (5, 5);

-- 你查出来的结果会是：
-- val1: 5     (宽度为1，够用了，直接显示)
-- val2: 0005  (宽度要求4，不够的部分用0填充)

手机号存储用 INT 还是 VARCHAR?⭐️⭐️⭐️

存储手机号，强烈推荐使用 VARCHAR 类型，而不是 INT 或 BIGINT。主要原因如下： 防止首位 0 丢失：部分地区手机号（或国际长途前缀）以 0 开头。如果用数字类型存储，0138... 会变成 138...，导致号码失效。 支持国际化与特殊符号：未来业务如果涉及国际号码，需要存储 + 号、- 号或空格 （如 +86-138...），数字类型完全无法处理。

非算术性：**手机号虽然看起来是数字，但我们从不对它进行数学运算（比如求和、平均值）。它本质上是一个标识符，更像是一个字符串。用 VARCHAR 更符合其数据性质。

查询灵活性：业务中常常需要根据号段（前缀）进行查询，例如查找所有 "138" 开头的用户。使用 VARCHAR 类型配合 LIKE '138%' 这样的 SQL 查询既直观又高效。

适配加密：于数据安全和隐私合规的要求，手机号这类敏感个人信息通常必须加密存储在数据库中。加密后得到的通常以一个长字符串，所以如果用INT，根本没法存

存手机号，VARCHAR的长度如何选择

明文存储（仅限非敏感环境）：建议 VARCHAR(20) 理由：E.164 国际标准号码最长为 15 位数字，加上国家码（如 +86）、括号或横杠 等格式化符号，20 位是兼顾存储效率与兼容性的“黄金长度”。

加密存储（生产标准做法）：建议 VARCHAR(128) 或更高 出于脱敏和合规（如等保、GDPR）要求，手机号必须加密。加密后的密文长度远大于原文：

Mysql 的基础架构

mysql02

MySQL 的整体架构分为三层：连接层、服务层（Server 层）、存储引擎层。

（1）连接层主要用于处理：客户端的连接请求、身份验证（用户名密码）和权限校验。

（2）服务层则相当于 Mysql 的大脑，该层分别有

  • sql接口：用来接收用户写的sql命令
  • 解析器：去对 sql 做词法，语法，语义分析，确保sql正确
  • 优化器则会生成执行计划，比如选是否用到索引，用哪个索引等。
  • 执行器则是根据执行计划去调用存储引擎接口的。
  • 查询缓存 (Query Cache)：它会把完整的 SQL 语句作为 Key，查询结果作为 Value 存起来。下次如果有一模一样的 SQL 过来，直接在 Server 层就把结果吐出去，根本不走解析、优化和引擎。

（3）而储存引擎层有点像人的四肢，是用来根据大脑的指挥去干活的：它负责数据的读写。

（4）物理层：则是 Mysql 真正存放数据的地方，实际就是操作系统的文件系统。

Mysql 存储引擎

Mysql 支持哪些存储引擎呢？

在 Mysql 中，我们可以通过 show engines 来查看支持的存储引擎，

mysql03

默认的存储引擎是 InnoDB，并且它也是唯一支持 ACID 级别事务的存储引擎。还支持 MyIsam，Memory 等，具体可以通过 show engines 来查看

常见存储引擎以及它们的区别？

InnoDB 的功能是比较强大且均衡的，支持事务、行锁和外键，是高并发、强一致性业务的首选（默认必选）；MyISAM 是纯查询选手，不支持事务但读取快、占用空间小，适合只有读操作的冷数据；Memory 是把数据存内存速度最快，但重启即消失，仅适用于临时缓存。

Mysql 存储引擎架构了解吗？

MySQL 存储引擎采用的是 插件式架构 ，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

⭐️MyISAM 和 InnoDB 有什么区别？

前言：

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎，可谓是风光一时。

虽然，MyISAM 的性能还行，各种特性也还不错（比如全文索引、压缩、空间函数等）。但是，MyISAM 不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。

MySQL 5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

正文：

（1）MyISAM 不支持行级锁，而InnoDB支持行级锁，因此InnoDB的并发性能更好！ （2）MyISAM 不支持事务，而InnoDB 支持 ACID 级别的事务 （3）MyISAM 不支持外键，而 InnoDB 支持。

（4）MyISAM 不支持数据库异常崩溃后的安全恢复，而InnoDB支持,这个恢复过程依赖于 redo log

（5）InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，InnoDB 表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。

MyISAM 和 InnoDB 如何选择?

其实大部分情况都是无脑选择 InnoDB 的，我们总是说MyISAM 性能比 InnoDB 好，但在很多场景下，InnoDB 的速度都是远超于 MyISAM的，所以我像不当什么理由去选择 MyISAM

Mysql 索引 ⭐️⭐️⭐️

索引是什么？

索引实际上就是一种用于快速查询和检索数据的数据结构，在 Mysql，索引其实就是把在磁盘上的数据按照某种数据结构组织起来，从而减少每次查询数据的磁盘 IO 数，因为相比在内存中，一次磁盘读写可是太慢了。

优点： （1）极大的减少了磁盘的 IO 数，从\(O(n)\)优化到了 \(O(logn)\) （2）可以通过唯一索引保证数据的唯一性，比如主键id，邮箱等等。 （3）索引也可以加速排序和分组

缺点： （1）索引需要占用存储空间，维护索引也需要时间代价

用了索引就一定提升查询性能吗？

不一定。 大多数情况下，合理使用索引确实比全表扫描快得多。但也有例外：

  • 数据量太小：如果表里的数据非常少（比如就几百条），全表扫描可能比通过索引查找更快，因为走索引本身也有开销。
  • 查询结果集占比过大：如果要查询的数据占了整张表的大部分（比如超过 20%-30%），优化器可能会认为全表扫描更划算，因为通过索引多次回表（随机 I/O）的成本可能高于一次顺序的全表扫描。
  • 索引维护不当或统计信息过时：导致优化器做出错误判断。

为什么 InnoDB 没有使用哈希作为索引的数据结构？

哈希索引的底层是哈希表。它的优点是，在进行精确的等值查询时，理论上时间复杂度是 O(1) ，速度极快。比如 WHERE id = 123。

但是，它有几个对于通用数据库来说是致命的缺点：

  1. 不支持范围查询: 这是最主要的原因。哈希函数的一个特点是它会把相邻的输入值（比如 id=100 和 id=101）映射到哈希表中完全不相邻的位置。这种顺序的破坏，使得我们无法处理像 WHERE age > 30 或 BETWEEN 100 AND 200这样的范围查询。要完成这种查询，哈希索引只能退化为全表扫描。
  2. 不支持排序: 同理，因为哈希值是无序的，所以我们无法利用哈希索引来优化 ORDER BY 子句。
  3. 不支持部分索引键查询: 对于联合索引，比如(col1, col2)，哈希索引必须使用所有索引列进行查询，它无法单独利用 col1 来加速查询。

为什么 InnoDB 没有使用 B 树作为索引的数据结构？

这个问题也可以转换为 b树和b+树有什么区别，回答逻辑就是由于结构不同，所以导致 1->树的高度 2-> 查询稳定性 3-> 范围查询的支持

我们从本质出发去解答这个问题，首先索引的核心目的是提高查询效率，虽然它们都是性能比较优秀的多路平很搜索树，但是 B 树会有下面的问题：

（1）由于 b 树的非叶子节点不仅仅存指针，还要存数据，所以每个节点能存储的指针数量就会变少，这就会导致树的高度比 B+ 树要高一些。

（2）其次，由于数据会存放在非叶子节点，所以它就没法像 b+ 树那样将所有数据组织成一个双向链表，所以对于范围查询，b树的效率不算高，而 b+ 树则对范围查询非常友好，我们只需要定位到起点，之后就可以通过遍历双向链表去完成范围查询了。

（3）还是因为 B 树的数据会存放到非叶子节点，所以它的查询性能是不稳定的，而我们的 B+ 树的所有数据都在叶子节点，所以查询性能非常稳定。

什么是覆盖索引

如果一个非聚簇索引包含类本次要查询的所有字段，那么就称之为 覆盖索引（Covering Index）。在 InnoDB 引擎中，只有主键索引的叶子节点会存放完整记录，其他二级索引只会存 主键id + 索引字段

什么是联合索引，什么是最左前缀匹配

用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。 

ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name);

最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。

注意点：>, <, 会停止，但是 >= ,<= like,bewteen 不会停止，因为可以通过等值匹配先确定起点，之后 b 这个索引还会参与索引下推优化，所以不会停止匹配。

Select * 会导致索引失效吗？

SELECT * 不会直接导致索引失效（如果不走索引大概率是因为 where 查询范围过大导致的），但它可能会导致回表，从而提升了走索引的开销，当查询的数据很多时，Mysql 优化器就可能选择全表扫描。

哪些字段适合创建索引？⭐️

  • 频繁被查询的字段
  • 区分度高，不为 null 的字段
  • 频繁出现在 where、order by、group by 关键字后面的字段
  • 作为被驱动表，常被用来做关联的字段

索引失效的常见原因⭐️

  • 在查询条件中，联合索引的使用没有遵循最左前缀匹配原则
  • 在索引列上进行了计算，函数，类型转换等操作（索引树记录的是字段的原始值）
  • 做了左模糊查询 （like "%xx"）
  • 查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
  • IN 的取值范围较大或者 where条件查询的结果过大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同);

Mysql 查询缓存⭐️⭐️

MySQL 查询缓存会将 sql 作为键，当前 sql 对应的结果作为值存储到。执行查询语句的时候，会先查询缓存，如果缓存中有对应的查询结果，就会直接返回。

但是，这种缓存机制非常鸡肋，如果 sql 语句有一点点变化，或者结果集有一点点变化，都会导致无法命中缓存，因此，现在的性能担当是存储引擎层的 Buffer Pool（缓冲池） 机制。而是“把热点磁盘数据搬到内存里”。

Buffer Pool 机制⭐️

存储单位：它不存 SQL 语句，而是存 数据页 (Page)。磁盘上的一页默认为 16KB，读到内存里也是 16KB。

读操作：当你查询某行数据时，InnoDB 先看 Buffer Pool 里有没有该行所在的“页”。如果有，直接内存读取（微秒级）；如果没有，再去磁盘读（毫秒级），并顺便把这个页缓存在 Buffer Pool 里。

写操作：当你修改数据时，先修改 Buffer Pool 里的页（此时叫脏页），并记录日志。后台线程会找时机把脏页刷回磁盘。

Mysql 事务 ⭐️⭐️

什么是事务⭐️

何为事务？ 一言蔽之，事务是一组操作的集合，要么都执行，要么都不执行。

什么是数据库事务？

数据库事务其实就是多条 sql 语句构成的一个集合，这些 sql 语句要么全部执行成功，要么全部执行失败。

ACID 特性⭐️

  • 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务内的操作要么全部执行成功，要么读不执行。
  • 一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
  • 隔离性（Isolation）：并发执行的各个事务是相互独立的，互不干扰的！
  • 持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

字节一面：Mysql 原子性的原理是什么？⭐️⭐️⭐️

在 MySQL 的 InnoDB 引擎中，实现原子性的核心机制是 undo log（回滚日志）。

当你执行任何修改操作（INSERT、UPDATE、DELETE）时，InnoDB 都会在修改数据之前，先记录一条对应的“相反”的日志 。

当事务运行过程中出现意外（比如服务器宕机、断电）或者你手动执行了 ROLLBACK 命令时，MySQL 就会利用这些 undo log 将数据“撤销”到事务开始之前的样子。

  • 主动回滚：当用户执行 ROLLBACK 时，InnoDB 会根据事务 ID 找到对应的所有 undo log 记录，按照从后往前的顺序执行逆向操作，从而恢复原始数据。

  • 崩溃恢复（Crash Recovery）：如果数据库在事务提交前宕机，重启后 InnoDB 会扫描事务状态。发现某些事务只有“开始”标识而没有“提交”标识，就会利用 undo log 自动进行回滚。

undo log 与 redo log 的区别？⭐️

简单来说：redo log 是为了放心的“向前走”（保证提交了就不丢），undo log 是为了“向后退”（保证想撤销时能撤回）。

为什么 undo log 是逻辑的，而 redo log 必须是物理的？”⭐️⭐️

undo log 必须是逻辑的：因为回滚时，数据库可能已经发生了其他并发事务。如果 undo log 是物理的（记录某个位置的二进制），直接覆盖回去可能会破坏其他事务已经改好的数据。所以它必须记录“逻辑操作”，通过执行反向 SQL 的方式来恢复，这样更安全。

redo log 必须是物理的：因为物理日志的恢复速度极快。在数据库崩溃恢复（Crash Recovery）时，MySQL 只需要按照物理日志在对应的内存位置“重填”数据即可，不需要像逻辑日志那样重新执行一遍 SQL 引擎的解析、优化和执行过程。

字节一面：为什么断电后重启，任然能通过 undo log 日志实现未提交事务的回滚！？⭐️⭐️⭐️

在 MySQL 中，有一个铁律：日志一定要比数据先写回磁盘。

断电重启后，MySQL 会进入 崩溃恢复（Crash Recovery） 模式。它就像一个侦探，通过两步走来找回真相：

第一步：通过 redo log 恢复物理现场

MySQL 先读取磁盘上的 redo log。不管事务有没有提交，只要 redo log 里记录了的修改，全部重新在内存里做一遍。通过第一步：数据库恢复到断电那一瞬间的状态（哪怕是带着未提交垃圾数据的状态）。

第二步：通过 undo log 清理“未完成任务”

现场恢复后，MySQL 会去查看磁盘上的 事务系统（Transaction System） 状态：

  • 它会扫描所有处于活跃状态的事务 ID。
  • 如果发现某个事务 ID 在断电前只有“开始（TRX_ACTIVE）”标识，没有“提交（TRX_COMMITTED）”标识，那么 MySQL 判定这个事务是失败的。
  • 执行回滚：MySQL 拿着这个事务 ID，去磁盘的 undo log 里找对应的逻辑记录
  • 逻辑抵消：根据 undo log 记录的“反向操作”（比如你之前插了一行，我现在把它删掉），把那些未提交的脏数据彻底抹除。

并发事务带来的问题⭐️

并发事务带来的问题主要有：脏读、丢失修改、不可重复读、幻读等问题。

脏读

A事务读取了B事务修改后的数据，之后B事务回滚了。

不可重复读

在一个事务内，查询相同的记录，得到的结果不同，这是因为会有其他事物对当前记录做修改。

幻读

在一个事务内，相同的查询得到的记录数量不同，可能多了，也可能少了，这是由于其他事务做了插入或者删除操作。 幻读其实可以看作是不可重复读的一种特殊情况，单独把幻读区分出来的原因主要是解决幻读和不可重复读的方案不一样。

并发事务的控制方法有哪些？

MySQL 中并发事务的控制方式无非就两种：锁 和 MVCC。锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模。

锁机制
  • 行级锁（Row Lock）：InnoDB 的杀手锏。只锁住当前操作的行，并发度最高，但开销大，可能产生死锁。
  • 表级锁（Table Lock）：MyISAM 的主要方式。开销小，不会死锁，但一锁锁整张表，并发性能极差。

  • 意向锁（Intention Lock）：一种“打招呼”的锁，用来快速判断表里是否有行被锁住，提高加表锁时的效率。

  • 共享锁（S 锁/读锁）：大家都能读，但谁都不能改。

  • 排他锁（X 锁/写锁）：我改的时候，别人既不能读也不能写。
MVCC

MVCC 的逻辑是：“为了提高读写的并发性能不加锁，我给不同时间点的事务看不同版本的数据快照。”

  • 隐藏列：每行数据后面都有两个隐藏字段：DB_TRX_ID（最后修改该行的事务 ID）和 DB_ROLL_PTR（指向回滚段的指针）。
  • Undo Log（回滚日志）：存储数据的历史版本，串联成一条版本链。
  • ReadView（一致性视图）：事务启动时创建一个快照，根据一套可见性算法判断：版本链中的哪个版本是当前事务可见的。

MVCC 实现两种隔离级别的原理⭐️

读视图

读视图主要是用来去控制对于当前读视图的创建者，数据的那些版本是可见的，哪些版本是不可见的。主要字段有：

  • creator_trx_id：当前read view创建者的事务ID
  • m_ids：当前系统中所有的活跃事务的 id，活跃事务指的是当前系统中开启了事务，但还没有提交的事务;
  • m_low_limit_id：表示在生成ReadView时，当前系统中活跃的读写事务中最小的事务id，即m_ids中的最小值。
  • m_up_limit_id：当前系统中事务的 id 值最大的那个事务 id +1，也就是系统中下一个要生成的事务 id。
可见性判断

  • 如果当前数据版本号小于m_low_limit_id，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；

  • 如果大于m_up_limit_id，表示这个版本是由将来启动的事务生成的，是肯定不可见的；

  • 如果在m_low_limit_id和 m_up_limit_id之间，那就包括两种情况（1）若 row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；（2）若 row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。

读已提交原理

在 Read Committed 级别下，事务中每一次 SELECT 语句执行前，都会重新生成一个新的 ReadView。

可重复读原理

在 Repeatable Read 级别下，事务只在第一次 SELECT 语句执行时生成一个 ReadView，之后整个事务过程中都复用这同一个 ReadView。

由于 ReadView 没变，m_ids 列表还是最初的那一批。即使别的事务提交了，但在你的 ReadView 眼里，它依然被标记为“活跃（未提交）”，所以根据算法，你会顺着版本链去读更早之前的旧版本。

事务的隔离级别

SQL 标准定义了四种事务隔离级别，用来平衡事务的隔离性（Isolation）和并发性能。级别越高，数据一致性越好，但并发性能可能越低。这四个级别是：

  • READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。这种级别在实际应用中很少使用，因为它对数据一致性的保证太弱。
  • READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。这是大多数数据库（如 Oracle, SQL Server）的默认隔离级别。
  • REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。MySQL InnoDB 存储引擎的默认隔离级别正是 REPEATABLE READ。并且，InnoDB 在此级别下通过 MVCC（多版本并发控制） 和 Next-Key Locks（间隙锁+行锁） 机制，在很大程度上解决了幻读问题。
  • SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

Mysql 锁⭐️⭐️

InnoDB 的锁是边找边加的。它每扫描到一行，就必须给这一行加上锁，确保在它处理完之前，这一行不能被别人动。

表级锁和行级锁是什么？有什么区别

首先表级锁、行级锁都是用来解决并发事务带来的问题的。区别就是：表级锁每次会锁整张表，而行级锁只锁一行。它们的并发性能不同，维护锁的开销也不同。

行级锁的使用有什么注意事项？

做需要注意的就是对全表的每一行都加锁，从而退化成了表锁。

InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到，一定要多多注意！！！

InnoDB 有哪几类行锁？

  • 记录锁（Record Lock）：属于单个行记录上的锁。
  • 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。

  • 临键锁（Next-Key Lock）：行锁 + 该行之前的间隙锁，前开后闭，在 RR（可重复读）级别下，彻底消灭“幻读”。

  • 索引上的等值查询，给唯一索引加锁的时候，如果满足条件，next-key lock 退化为行锁。

  • 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

假设表 test 的 id 是主键，目前有以下数据： 1, 5, 10, 15 当执行等值查询： 

WHERE id = 7

为什么 Next-Key lock 有些时候会被优化为记录锁

在我们通过唯一索引或者主键做等值查询时，即使在间隙范围内插入新值，也不影响本次查询语句，例如： 

select * from student where id = 10;

id 是主键索引，所以其它事务天然的就不能再去插入一个 id = 10的记录，而又因为本次就算再（1-10）之间插入一个新的记录，也不会影响本次查询结果，造成幻读，所以可以将 next-key lock 优化为 record lock

共享锁和排他锁？

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

  • 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
  • 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。

由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。不过， 你可以通过以下语句显式加共享锁或排他锁。

# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;
# 排他锁
SELECT ... FOR UPDATE;

意向锁有什么作用呢？

为了让“行锁”和“表锁”能够高效共存。如果没有意向锁，当要给一张表加“表锁”时，效率会低到令人发指。因为 Mysql 必须确认现在这张表中有没有行锁，它只能全表扫描。

而有了意向锁后，我们只需要看当前意向锁的状态就能判断当前表存不存在行锁，如果存在的话，是共享锁呢，还是排他锁呢？

工作机制：你给某一行加锁时：InnoDB 会自动先在表级别插上一面“旗子”（意向锁）。

  • 如果你加的是行级共享锁（S），就先给表加个意向共享锁（IS）。
  • 如果你加的是行级排他锁（X），就先给表加个意向排他锁（IX）。

① 意向锁之间是“兼容”的 ② 意向锁只跟“表锁”冲突

当前读和快照读？

快照读就是最普通的 SELECT 语句，不加锁，而是基于 MVCC 实现读写互不影响的，MVCC 可以实现读已提交和可重复读两种隔离级别！

当前读是指读取数据的最新版本，并且读取时会对数据加锁，保证其他事务不能修改当前记录。

  • 快照读是为了性能：它让读操作不需要等待写操作，实现了“读写并行”。
  • 当前读是为了安全：它保证了在修改数据或需要绝对实时数据时，不会被其他事务干扰。

字节一面：Mysql 有哪些隐式锁呢？

MySQL 的隐式锁是 InnoDB 自动加的，不需要手动 LOCK 语句。主要包括四种：

  • 行锁：增删改时，或者 selete + for update 自动添加
  • 间隙锁：RR 级别范围查询时加，锁住记录之间的空隙
  • Next-Key Lock：前两者的组合，是解决幻读的核心机制
  • 插入意向锁：INSERT 前自动加，保证并发插入安全

我刚才提到的 Next-Key Lock 就是行锁 + 间隙锁的组合，是 MySQL 解决幻读的关键。

总结

  • 普通 SELECT：MVCC 快照读，无锁，高并发。
  • 显式加锁 SELECT：FOR UPDATE（或 LOCK IN SHARE MODE / FOR SHARE） 等，加行锁/间隙锁，用于强一致性读取。
  • 增删改（DML）：自动加排他锁 + 间隙锁（RR级别下），且极度依赖索引来保证只锁住必要的行。

Mysql 性能优化⭐️

如何分析 SQL 性能

我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

具体介绍看：middileware->mysql 高级->索引优化

如何优化 MySQL 性能

1. 抓住核心：慢 SQL 定位与分析

  • 开启慢查询日志，用 mysqldumpslow 分析高频慢 SQL
-- 开启慢查询日志开关
SET GLOBAL slow_query_log = 'ON';
-- 设置慢查询阈值（例如超过 1 秒就算慢查询）
SET GLOBAL long_query_time = 1;
-- （可选）记录没有使用索引的 SQL
SET GLOBAL log_queries_not_using_indexes = 'ON';

# 查看执行时间最慢的前 10 条 SQL（最常用）
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 查看执行次数最多的前 10 条高频慢 SQL
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
  • 用 EXPLAIN 查看执行计划，重点关注 type（是否全表扫描）和 Extra（是否 Using filesort）

2. 索引、表结构和 SQL 优化

  • 加索引：遵循最左前缀原则，覆盖索引减少回表，避免索引失效场景
  • 表结构：字段尽量 NOT NULL，避免冗余列，大字段拆分
  • SQL 写法：只 SELECT 需要的列，避免 SELECT *，IN 替换 OR

3. 进阶方案：架构优化

  • 读写分离：主库写，从库读，通过中间件（ShardingSphere）分发请求
  • 分库分表：单表超 500 万行时水平拆分，降低单表数据量
  • 引入缓存：热点数据前置到 Redis，减少数据库直接压力

4. 其他优化手段