MySQL面试题

105.什么是数据库事务？讲一下事务的 ACID 特性？

数据库事务是指数据库管理系统（DBMS）中的一个操作序列，这些操作必须作为一个不可分割的单元执行，即要么全部执行成功，要么全部失败回滚。事务通常涉及到对数据库中的数据进行读写操作。

事务的 ACID 特性指四个关键特征：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

1. 原子性（Atomicity）：事务是一个原子操作，要么全部提交，要么全部回滚。当一个事务执行期间发生故障，操作系统会自动将其回滚到事务执行之前的状态，保证数据的一致性。
2. 一致性（Consistency）：事务执行结束后，数据必须保持一致性状态。在事务执行期间，数据库中的数据可以处于中间状态，但在事务完成时必须保证数据的一致性。
3. 隔离性（Isolation）：数据库系统必须保证事务之间相互隔离，不会互相干扰。隔离级别不同，会影响到事务的并发性和数据一致性，比如出现脏读、不可重复读、幻读等问题。
4. 持久性（Durability）：一旦事务提交，其所做的修改必须永久保存到数据库中。即使系统发生故障或宕机，数据也能够保持不变。

ACID 特性是保证事务正确性和数据一致性的重要手段。在设计数据库应用程序时，应该根据具体的业务需求和数据安全性要求，选择合适的隔离级别和事务提交策略，保证事务的可靠性和数据的一致性。

106.MySQL 中的日志类型有哪些？binlog、redo log 和 undo log 的作用和区别是什么？

MySQL 是一款流行的关系型数据库，其日志是其关键功能之一。MySQL 包括三种类型的日志，分别是 binlog、redolog 和 undolog，它们分别有不同的作用和特点。

• binlog ，binlog（Binary log）是 MySQL 中的二进制日志文件，用于记录 MySQL 服务器上的所有更新和修改操作。它可以记录所有的 DDL（Data Definition Language）和 DML（Data Modification Language）操作，包括对表结构的更改、数据的插入、修改、删除等等。binlog是在事务提交后生成的，因此可以用于恢复数据库。
• redolog ，redolog（Redo log）用于恢复数据，保证数据的一致性和持久性。当 MySQL 发生修改时，redolog 会将这些操作记录下来，并写入磁盘。这样，当 MySQL 发生宕机或崩溃时，通过重放 redolog 就可以恢复数据。
• undolog，undolog（Undo log）用于回滚操作。当 MySQL 发生数据新增或修改时，undolog 会记录这些操作并将其写入磁盘。这样，当 MySQL 需要回滚时，通过重放 undolog 就可以回滚事务。

区别：

• binlog 和 redolog 都是 MySQL 中的二进制日志，但是它们的作用和实现方式有所不同。binlog 是 MySQL 记录所有的操作，而 redolog 则是用于保证数据的一致性和持久性。此外，binlog 是逻辑日志，redolog 是物理日志。binlog 记录的是SQL语句，而 redolog 记录的是数据页的修改，所以 binlog 可以跨平台使用，而 redolog 不能。undolog 和 redolog 的区别是，undolog 是用于回滚操作的，而 redolog 是用于恢复数据的。

107.什么是数据库的索引？索引的作用是什么？索引适用于哪些场景？

数据库索引是一种数据结构，用于提高数据库表的查询效率。

索引可以帮助数据库快速定位和检索存储在表中的数据，从而加快数据查询的速度。在数据量比较大时，使用索引可以极大地提高数据检索的效率。

索引的作用是通过构建一个额外的数据结构（B-tree、哈希表等）来加速数据的检索。它是在数据库表上创建的一种数据结构，它包含一些指向表中数据的指针，可以快速地定位到满足查询条件的数据行，从而提高查询效率。索引可以包含一个或多个列，可以使用单列索引、组合索引、全文索引等多种方式来创建。

适合使用索引的场景包括：

• 频繁查询的列，如主键、外键等。
• 经常作为查询条件的列，如 WHERE、ORDER BY、GROUP BY 等语句中的列。
• 经常需要连接的列，如多表联合查询时的列。
• 数据量较大的表，通过索引可以加快数据检索速度。

索引的优点是可以提高数据库的查询速度，缩短数据检索的时间，提高系统的性能。但是索引也有一些缺点，包括：

• 占用额外的存储空间，增加了存储成本。
• 建立索引需要时间，增加了系统的开销。
• 数据库的更新操作（增删改）会导致索引的重建，影响系统的性能。

因此，需要根据实际情况进行索引的创建和使用，避免过度索引导致系统性能下降。

鱼皮补充：这题如果能举例你自己是如何在项目中应用索引的（比如检索用户消息）、或者说什么情况下你没有选择用索引的（比如性别字段），会很加分

108.如何在 MySQL 中进行数据备份？如何恢复半个月前的数据？

MySQL 数据备份是一个非常重要的工作，保证数据的安全性和可靠性。备份数据的方式有很多种，以下是其中一种基本的备份和恢复方式：

一、备份 MySQL 数据库

我们可以使用 mysqldump 工具来备份 MySQL 数据库，该工具可以生成 SQL 脚本文件，包含数据库中所有表和数据的语句。在终端中运行以下命令：

1

mysqldump -u [username] -p [database_name] > [backup_file].sql

其中，[username] 是 MySQL 用户名，[database_name] 是需要备份的数据库名称，[backup_file].sql 是备份的文件名。

该命令会将 SQL 脚本文件导出到当前目录下。

二、恢复 MySQL 数据库

如果需要恢复之前备份的数据，可以运行以下命令：

1

mysql -u [username] -p [database_name] < [backup_file].sql

其中，[username] 是 MySQL 用户名，[database_name] 是需要恢复的数据库名称，[backup_file].sql 是备份的文件名。

该命令会将备份文件中的 SQL 语句执行，从而将数据恢复到指定的数据库中。

如果需要恢复半个月前的数据，可以选择备份文件中的某个时间点之前的数据，并使用以上方法进行恢复。

此外，还有其他的备份方式，如使用 MySQL 自带的 mysqlbinlog 工具进行增量备份，或使用第三方备份软件进行备份。根据实际需求选择合适的备份方式，并将备份文件存放在可靠的位置。

鱼皮补充：mysql 备份操作是可以写在简历上的，推荐大家自己实践试试

111.MySQL 事务的隔离级别有哪些？每个隔离级别的特点是什么？MySQL 默认的隔离级别是什么？

回答重点

MySQL 事务有四种隔离级别：

1）读未提交（Read Uncommitted）：事务可以读取未提交的数据，可能会读到脏数据，会导致幻读、不可重复读、脏读等问题；

2）读已提交（Read Committed）：只能读取已经提交的数据，可以避免脏读问题，但是可能会遇到不可重复读、幻读问题；

3）可重复读（Repeatable Read）：保证同一个事务中多次读取同一数据的结果是一致的，避免了脏读和不可重复读问题，但是可能会遇到幻读问题；

4）序列化（Serializable）：最高的隔离级别，串行化执行操作，可以避免所有并发问题，但是并发性能非常低，开销很大。

MySQL 的默认隔离级别是可重复读（Repeatable Read）。

其中，脏读指一个事务读到了另一个事务未提交的数据，不可重复读指同一个事务多次读取同一数据得到不同结果，幻读指同一个事务前后读取的数据集合不一致。

在实际使用中，应该根据具体情况选择合适的隔离级别，权衡数据的一致性和并发性能。

扩展知识

• [604.MySQL 中的 MVCC 是什么？](#604MySQL 中的 MVCC 是什么？)

112.MySQL 中的意向锁是什么？作用是什么？它是表级锁还是行级锁？

意向锁是一种 MySQL 数据库中的锁，用于表级锁协调多个行级锁的使用。在表级锁定一个表之前，MySQL 需要先获得一个意向锁，以表明要获取的锁类型（读锁或写锁），避免其他事务锁定整个表或锁定一部分表时引发死锁。

意向锁是一种轻量级锁，它不会影响其他事务的读操作，只有在某个事务要对表进行写操作时才会加上意向锁，而其他事务在读取表时只需要获取读锁，不需要等待意向锁的释放。

意向锁可以提高数据库并发性能，防止死锁的发生。它是表级锁，而不是行级锁。

114.MySQL 中的覆盖索引和联合索引是什么？索引的最左前缀匹配原则是什么？

覆盖索引和联合索引是数据库中常见的两种索引类型。

覆盖索引是指一个包含了所有查询需要的列的索引，查询时可以直接从索引中取到需要的数据，而不需要再回到表中查找，从而可以提高查询效率。

联合索引是指使用多个列组合起来作为一个索引，可以同时查询多个列，以提高查询效率。联合索引可以包含多个列，但是查询时只能使用前缀列进行查询，即只有在查询中使用了联合索引的前几个列，才能利用联合索引进行查询。如果查询中没有使用前缀列，那么联合索引就不能发挥作用，需要使用单独的索引或全表扫描。

最左前缀匹配原则是指如果一个联合索引包含了多个列，那么在查询时只能使用前面的列进行匹配。

例如，一个联合索引包含了 A、B、C 三列，那么查询时只能使用 A、AB 或 ABC 进行匹配，而不能只使用 B 或 C 进行匹配。这是因为如果查询时使用的列不是最左前缀列，那么 MySQL 就无法使用索引进行查询，会导致全表扫描，从而降低查询效率。

在实际的应用中，覆盖索引和联合索引可以结合使用，以提高查询效率。同时，使用最左前缀匹配原则可以让我们更加合理地设计索引，从而提高查询性能。

115.什么是 MySQL 的执行计划？如何获取执行计划并对其进行分析？

MySQL 执行计划是指 MySQL 查询优化器生成的一份详细的查询执行计划，它展示了 MySQL 在执行查询时所采取的具体执行计划，包括表的访问顺序、数据读取方式、使用的索引、使用的排序方式等等。通过分析执行计划，可以帮助我们找出查询性能瓶颈所在，进而进行优化，提高查询效率。

要获取执行计划，可以在执行 SQL 语句时在前面添加 explain 关键字，例如：

1

explain select * from table where id = 1;

这样，MySQL 会输出该查询语句的执行计划。 执行计划中的各个字段含义如下：

• id：每个 Select 子句或者是一个操作符或者是一个查询语句。
• select_type：查询类型，表示查询的类型（简单查询、联合查询、子查询等等）。
• table：查询涉及的表。
• partitions：匹配的分区。
• type：访问类型，表示 MySQL 在表中找到所需行的方式。
• possible_keys：表示查询可能使用到的索引。
• key：实际使用到的索引。
• key_len：使用的索引长度。
• ref：列与索引的比较。
• rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
• filtered：返回结果的行数占总行数的比例。
• Extra：包含 MySQL 解决查询的详细信息。

分析执行计划时，需要注意以下几个方面：

• 扫描行数：rows 字段，表示查询所需扫描的行数，如果该值过大，说明查询效率不高，需要优化。
• 使用索引：key 字段，表示查询所使用的索引，如果没有使用索引或者使用的不是最优索引，需要考虑优化。
• 排序：Extra 字段，如果查询需要使用 filesort 排序，说明查询效率不高，需要优化。
• 嵌套循环：如果查询类型是 nested loop，说明查询中包含嵌套循环，也需要考虑优化。

通过分析执行计划，可以确定查询优化的方向和方法，提高查询效率。

鱼皮补充：这里大家不用去记忆执行计划中各字段的含义，你可以给面试官举个例子，你是怎么使用查询计划来分析定位一次慢查询的。

116.MySQL 支持哪些存储引擎？默认使用哪个？MyISAM 和 InnoDB 引擎有什么区别，如何选择？

MySQL 支持多种存储引擎，包括 InnoDB、MyISAM、MEMORY、CSV 等。默认情况下，MySQL 使用的存储引擎是 InnoDB。

MyISAM 和 InnoDB 是 MySQL 中 最常用 的两种存储引擎，它们有以下区别：

1. 锁定方式不同：MyISAM 使用表级锁定，而 InnoDB 使用行级锁定。在并发访问时，InnoDB 的锁定方式更加精细，可以避免锁定整个表，提高了并发性能。
2. 数据完整性不同：MyISAM 不支持事务和外键约束，而 InnoDB 支持事务和外键约束，可以保证数据的完整性和一致性。
3. 读写性能不同：MyISAM 的读性能相对较高，适合于读密集型应用；而 InnoDB 的写性能相对较高，适合于写密集型应用。
4. 空间利用率不同：MyISAM 不支持行级别的存储，存储空间利用率较低；而 InnoDB 支持行级别的存储，存储空间利用率更高。

在选择 MyISAM 和 InnoDB 引擎时，需要考虑应用场景和需求：

1. 如果应用主要是读操作，可以考虑选择 MyISAM 引擎，以提高读取性能。
2. 如果应用主要是写操作或需要支持事务和外键约束，可以考虑选择 InnoDB 引擎，以保证数据的完整性和一致性。
3. 如果需要高性能和高可用性，可以考虑选择使用 MySQL 集群或使用多个副本实例，并将数据分布在不同的节点上。

589.详细描述一条 SQL 语句在 MySQL 中的执行过程。

回答重点

1. 先通过连接器校验权限
2. 利用分析器进行 SQL 语句的词法分析和语法分析，构建解析树
3. 使用优化器选择合适的索引和表连接顺序，最终选择一个最佳的执行计划
4. 利用执行器，调用引擎层查询数据，返回结果集给客户端

扩展知识

详细流程分析

我们以一条查询 SQL 为例：select * from user where id=1；

在这条查询语句中，大体可以分为两个层面，Server 层和存储引擎层。

Server 层涉及到一系列的业务的组件，而存储引擎层则是负责存储发送过来的数据、提供读写接口等功能（补充：MySQL 的存储引擎是插件式的，一个数据库里面的不同表可以用不同的存储引擎）。

具体流程如下：

1）当客户端的 SQL 发送到 MySQL 时，首先是到达 Server 层的连接器，连接器会对你此次发起的连接进行权限校验，以此来获取你这个账号拥有的权限。当你的账号或密码不正确时，会报 Access denied for user 错误，相信大家对这个错误也并不陌生。连接成功如果后续没有任何操作，那么这个连接就处于空闲状态，到达一定时间后它便会断开连接，这个时间一般是 8 小时，是由 wait_timeout 参数控制的。

2）查询缓存（在 MySQL 8.0 之后就被砍了）具体做法就是将一个查询语句作为 key ，将上一次请求的结果作为 value，存储在缓存组件中，当同样的语句来查询的时候即可立马返回结果，不需要经历词法、语法分析等以下的步骤。只要表有数据改动缓存就失效了，在我们常见的联机事务处理（OLTP） 场景下是个鸡肋。

3）接下来就到了分析器来进行语法分析、词法分析。MySQL 会首先对你的语句进行 ”词法分析“ ，来判断你的语句是什么类型以及携带什么参数等。比如：MySQL 会将输入语句的 select 提取出来，判断出这是一条查询语句、将 from 后面的 user 提取出来作为查询的表名、把 id 提取出来作为列名等。做完这些 MySQL 将会进行 ”语法分析“ 来判断你的语句的语法是否有误、是否满足 MySQL 的语法。如果语法有问题，那这个错误相信大家都不陌生：You have an error in your SQL syntax; check the manual......。

4）经过分析器就到了优化器，它会对你的语句进行优化判断。比如你的表中有多个索引，优化器会帮你选择使用哪个索引、你使用了 join 多表连接，优化器会帮你调整表的连接顺序。我们平日里用的 explain 其实就是让 MySQL 告诉我们它的优化决定策略是怎样的。

5）最后会到达执行器，它先会判断你对这个 user 表是否有权限查询，如果没有权限它将会拒绝本次查询，返回错误信息。如果有权限，它将会根据表的存储引擎提供的接口进行数据查询将重复遍历表的行数据，判断 id 字段是否等于 1。直到遍历完整个表将符合条件的数据作为结果集返回给客户端。

590.MySQL 的存储引擎有哪些？它们之间有什么区别？

回答重点

MySQL 的主要存储引擎包括：

1）InnoDB（重点）：

• 支持事务、行级锁和外键。
• 提供高并发性能，适用于高负载的 OLTP 应用。
• 数据以聚集索引的方式存储，提高检索效率。

2）MyISAM（重点）：

• 不支持事务和外键，使用表级锁。
• 适合读取多、更新少的场景，如数据仓库。
• 具有较高的读性能和较快的表级锁定。

3）MEMORY：

• 数据存储在内存中，速度快，但数据在服务器重启后丢失。
• 适用于临时数据存储或快速缓存。

4）NDB (NDBCluster)：

• 支持高可用性和数据分布，适合大规模分布式应用
• 提供行级锁和自动分区。

5）ARCHIVE：

• 用于存储大量历史数据，支持高效的插入和压缩。
• 不支持索引，适合日志数据存储。

实际上我们仅需重点了解 InnoDB 和 MyISAM 即可，其它仅需有点印象即可。

扩展知识

MySQL 8.4 版本一共提供了 10 个引擎

有兴趣的小伙伴可以点击这里，访问官网查看其他引擎详细介绍。

扩展 MySQL 的 InnoDB 存储引擎和 MyISAM 存储引擎有何不同？

MySQL 中 InnoDB 存储引擎与 MyISAM 存储引擎的区别是什么？

591.MySQL 的索引类型有哪些？

回答重点

从数据结构角度来看，MySQL 索引可以分为以下几类：

• B+树索引
• 哈希索引
• 倒排索引（即全文索引 Full-Text）
• R-树索引（多维空间树）

从常见的基于 InnoDB B+ 树索引角度来看，可以分为：

• 聚簇索引（Clustered Index）
• 非聚簇索引（Non-clustered Index）

从索引性质的角度来看，可以分为：

• 普通索引（二级索引、辅助索引）
• 主键索引
• 联合索引
• 唯一索引
• 全文索引
• 空间索引

扩展知识

进一步分析基于数据结构的索引分类

• B+树索引：通过树形结构存储数据，适用于范围查询（如 BETWEEN）和精确查询（如 =），支持有序数据的快速查找、排序和聚合操作。是 MySQL 默认的索引类型，常用于 InnoDB 和 MyISAM 引擎。
• 哈希索引：基于哈希表的结构，适用于等值查询（如 =），查询速度非常快，但不支持范围查询（如 >、<）。哈希索引不存储数据的顺序，常用于 Memory 引擎。
• 倒排索引（Full-Text）：用于全文搜索，将全文分词，通过存储词与文档的映射，支持模糊匹配和关键字搜索。特别适合用于大文本字段，如 TEXT 类型的列，用于查找包含特定词语的记录。
• R-树索引：专为多维空间数据（如地理坐标）设计，适用于空间查询（例如，计算地理位置的最近距离、区域查询等）。常用于存储和查询地理信息系统（GIS）中的空间数据。

进一步分析基于 InnoDB B+ 树索引分类

• 聚簇索引：InnoDB 中主键索引就是聚簇索引。它基于主键排序存储。之所以叫聚簇索引是因为索引的叶子节点存储完整数据行数据。
• 非聚簇索引：指的是 InnoDB 中非主键索引的索引，之所以称之为非聚簇是因为这个索引的叶子节点仅保存索引字段和主键的值。如果要查询完整的数据行中的数据，需要再从聚簇索引即主键索引中通过主键查询，一个表可以有多个非聚簇索引。

进一步分析基于索引性质的分类

• 普通索引：一般指非主键索引且非唯一索引。
• 主键索引：表中的每一行数据都有唯一的主键。每个表只能有一个主键索引，且主键值不能为 NULL。InnoDB 中主键索引是聚簇索引结构实现的。
• 联合索引：由多个列组成的索引，适用于多列的查询条件，能够提高包含多个条件的查询的性能。联合索引中的列是按照指定顺序排列的。
• 唯一索引：保证索引列中的值是唯一的，可以有效防止重复数据的插入。唯一索引允许 NULL 值，但一个列中可以有多个 NULL。
• 全文索引：用于全文搜索，支持对长文本字段（如 TEXT 类型）进行关键字查找，支持自然语言处理、模糊匹配等操作。适用于需要对文本内容进行复杂搜索的场景。
• 空间索引：用于空间数据（如地图上的经纬度坐标等）查询。通常使用 R-树结构，适合多维数据的查询，如区域查询和最近距离查询，主要用于 MyISAM 和 InnoDB 存储引擎中的地理信息数据。

索引建立 SQL 示例

主键索引：

1
2
3
4
5
6

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50),
    PRIMARY KEY(id)
);

唯一索引：

1

CREATE UNIQUE INDEX idx_username ON users(username);

普通索引

1

CREATE INDEX idx_username ON users(username);

全文索引：

1

CREATE FULLTEXT INDEX idx_content ON articles(content);

联合索引：

1

CREATE INDEX idx_username_email ON users(username, email);

哈希索引：

1

CREATE INDEX idx_username_hash ON users(username) USING HASH;

空间索引

1

CREATE SPATIAL INDEX idx_location ON places(location);

592.MySQL InnoDB 引擎中的聚簇索引和非聚簇索引有什么区别？

回答重点

聚簇索引：

• 索引叶子节点存储的是数据行，可以直接访问完整数据。
• 每个表只能有一个聚簇索引，通常是主键索引，适合范围查询和排序。

非聚簇索引：

• 索引叶子节点存储的是数据行的主键和对应的索引列，需通过主键才能访问完整的数据行。
• 一个表可以有多个非聚簇索引（称之为非主键索引、辅助索引、二级索引），适用于快速查找特定列的数据。

扩展知识

进一步分析

在 InnoDB 里聚簇索引就是主键索引，非聚簇索引指的是非主键索引（辅助索引、二级索引），默认情况下它们都是 B+树实现的。

以下为一个简单的 B+ 树示例图：

如果是主键索引：

• 非叶子节点存储主键和页号
• 叶子节点存储完整的数据
• 叶子节点之间有双向链表链接，便于范围查询
• 叶子节点内部有页目录，内部记录是单链表链接，通过页目录二分再遍历链表即可得到对应记录。
• B+ 树只能帮助快速定位到的是页，而不是记录。
• 页大小默认 16k，是按照主键大小排序的，所以无序的记录插入因为排序会插入到页中间，又因为容量有限会导致页分裂存储，性能比较差，所以主键要求有序。

如果是非聚簇索引：

• 和主键索引的差别就在于叶子节点仅存储索引列和主键，不包含完整行的数据。

例如一个表有 id、姓名、性别、地址等等字段，本身包含聚簇索引，此时再给姓名建立一个索引，此时的索引叶子节点存储如下图所示：

所以说不要所有查询都用 select * ，因为如果本来只需要查询索引列数据的话，直接利用二级索引就能得到所要的数据。

例如通过姓名仅查询 id 的话，直接利用姓名索引就能得到数据，通过如果图方便直接用 select * ，那就不得不通过 id 再去主键索引查找不需要的性别和地址，浪费资源，且可能伴随着很多随机 I/O。

通过二级索引查找得到 id ，再去主键索引查询的这个过程叫回表。

593.MySQL 中的回表是什么？

回答重点

“回表” 是指在使用二级索引（非聚簇索引）作为条件进行查询时，由于二级索引中只存储了索引字段的值和对应的主键值，无法得到其它数据。如果要查询数据行中的其它数据，需要根据主键去聚簇索引查找实际的数据行，这个过程被称为回表。

扩展知识

进一步理解回表

举个例子：select * from user where age = 20;(user 包含 name、age、id 三个字段。假设 MySQL 优化器确定使用 age 索引)

1）根据二级索引（age）找到 age = 20 的 主键键值：

2）由于查询条件是 select * ，需要 name、age、id 三个字段，而 age 索引内没有 name 字段，因此还需要再根据主键去查找整行的数据：

最终得到 2、小鱼、20 三个数据。

回表其实不仅仅只是多查一次，还会带来随机 I/O。

因为通过 id 去主键索引查询的时候，id 肯定是不连续的（例如 age 为 20 的人很多，age 索引中年龄是有序的，但是 id 无序，且是不连续的），所以去主键索引频繁查询会造成大量随机 I/O ，我们都知道顺序 I/O 查询快，而随机 I/O 慢，所以频繁回表效率很低。

因此，不要因为图方便，在代码里都用 select * ，从而引发不必要的回表操作。

594.MySQL 索引的最左前缀匹配原则是什么？

回答重点

MySQL 索引的最左前缀匹配原则指的是在使用联合索引时，查询条件必须从索引的最左侧开始匹配。如果一个联合索引包含多个列，查询条件必须包含第一个列的条件，然后是第二个列，以此类推。

底层原理：因为联合索引在 B+ 树中的排列方式遵循“从左到右”的顺序，例如联合索引 (first_name, last_name, age) 会按照 (first_name, last_name, age) 的顺序在 B+ 树中进行排序。

MySQL 在查找时会优先使用 first_name 作为匹配依据，然后依次使用 last_name 和 age。因此，组合索引能够从左到右依次高效匹配，跳过最左侧字段会导致无法利用该索引。

按照 (first_name, last_name, age) 的顺序在 B+ 树中的排列方式(大致的示意图)如下

1
2
3
4
5

                          (Alice, Black, 35)
                              |
      -------------------------------------------------------------
      |                |                     |                |
    (Alice, Brown, 25)  (Alice, Smith, 30) (Bob, Smith, 20) (Bob, White, 19)   

扩展知识

举例说明

假设当前表有一个联合索引（a,b,c）。

以下查询条件 符合 最左匹配原则：

1
2
3

where a=1;
where a=1, b=2;
where a=1, b=2, c=3;

以下查询条件 不符合 最左匹配原则：

1
2
3

where b=2;
where c=3;
where b=2, c=3;

再来看下这个查询：

1

where a=1, c=3;

这个查询可能可以用上联合索引（a,b,c），在 5.6版本之前，即使用上也只能利用 a=1 这个条件来过滤。但是在 MySQL 5.6 版本后有个优化，即索引下推，可以在引擎层面，查询得到 a=1 的数据之后，利用 c 过滤掉不符合的数据，再返回给 server 层。

• [596. MySQL 的索引下推是什么？](#596MySQL 的索引下推是什么)

注意范围查询

在理解范围查询不同点之前，需要理解联合索引的顺序。

例如联合索引（a,b,c），在 B+树的中排序是：先按 a 的值排序，如果 a 的值相同，则按 b 的值排序，如果 b 的值相同，再按 c 的值排序。

类似下图的排序：

如果遇到范围查询(>、<)，就会停止匹配。

1

where a>1, b=2, c=3;

所谓的停止匹配指的是：a 可以用上联合索引，但是 b 和 c 却不行。因为 a 需要先经过范围查询，此时经过筛选得到 b 和 c 的数据是无序的。比如 a 为 1 和 a 为 2 数据之间 b 和 c 是无序的，因此无法利用索引查询。

如果遇到如 >=、<=、BETWEEN、前缀like（xx%）的范围查询，则不会停止匹配。因为这些查询包含一个等值判断，可以直接定位到某个数据，然后往后扫描即可。

例如：

1

where a>=1, b=2, c=3;

查询可以定位到 a=1 这条数据，然后往后遍历即可，而定位到的 a=1 数据内，b 和 c 是有序的，因此可以利用索引查询。

而以下的查询则可以全部用上联合索引：

1

where a=1, b=2, c>3;

因为 a 和 b 是等值查询的情况下，c 是有序的，有序就可以用上索引。

补充说明：拿 where a>=1, b=2，再来理解一下。因为 a = 1 这个条件，再加上 b = 2，mysql 可以直接定位到 a=1 and b=2 这条数据，然后再往后遍历，因此说用上了 b（因为确实利用了 b 的条件快速定位到第一条记录）。但是要注意往后遍历即 扫描 a > 1 的情况是用不到 b 的。

MySQL 8 都不需要最左匹配就能用上联合索引了？

以下实验基于 MySQL8.0.26 版本

为了故事顺利的发展，我们先建个表。

1
2
3
4
5
6
7

CREATE TABLE `t1` (
  `f1` int NOT NULL AUTO_INCREMENT,
  `f2` int NOT NULL,
  `f3` int NOT NULL,
  PRIMARY KEY (`f1`),
  KEY `idx_f2_f3` (`f2`,`f3`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

很简单的一张表，一个主键索引，一个联合索引idx_f2_f3。

此时插入一波数据

1
2
3
4
5
6
7

INSERT INTO t1 (f2,f3) VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1(f2,f3) SELECT f2, f3 + 5 FROM t1;
INSERT INTO t1(f2,f3) SELECT f2, f3 + 10 FROM t1;
INSERT INTO t1(f2,f3) SELECT f2, f3 + 20 FROM t1;
INSERT INTO t1(f2,f3) SELECT f2, f3 + 40 FROM t1;

这个时候表里一共有160条数据，f2 的值基数很低，只有1、2这两个值，基数为2。

数据准备完了，咱们可以开始操作一下了，此时执行：

先执行了一下ANALYZE TABLE t1; 更新了一下表的统计信息，防止 MySQL 误判。

简单执行了一个 f2 为条件的联合索引查询，且结果只要求返回 f2 和 f3，所以从这个联合索引可以直接得到值，不需要回表。

这个查询的 explain 结果相信大家也都能理解，用上了 idx_f2_f3，且 type 是ref，说明用上了这个索引查询。

现在我们换一个查询条件，让 f3 作为条件来查询，这个时候就不是最左匹配原则了，讲道理应该用不上索引查询。

看看这结果，好像不太对？type 是 range，这是基于索引的范围扫描，所以用上了索引！

但是我的查询条件 f3=1 明明是等值呀？怎么就用上了范围扫描呢？

还有 Extra 里面显示的Using index for skip scan又是啥？

Using index for skip scan

让我们直接进行一个官方文档的查询！

在优化里面的范围优化里确实有Skip Scan Range Access Method，它就是产生上述 Extra 的原因。

好了，可以忘了官网这个截图了，基于我展示的例子我来解释下。

根据我们所认知的最左匹配原则，执行

• where f3 =1

是无法在idx_f2_f3上利用 type 是 ref 的查询方式来查询的。

因为只有带上索引左边的查询条件才能用上索引，例如：

• where f2=1 and f3 =1

那如果无法利用索引进行查询，最终只有一个选择，全表扫描：要么全表扫描主键索引，要么全表扫描二级索引。

MySQL 就意识到这效率确实低，能不能找个优化点呢？

所以就在 8.0.13 版本引入了Skip Scan Range Access Method，它在一定条件下，利用了范围扫描来替代了全表扫描的发生。

具体原理如下：

1. 先统计一下索引最左字段 f2 的基数即唯一值，这里一共有 1 和 2 两个值。
2. 然后将 f2 的值拼入查询条件中构造 where f2=1 and f3=1 和 where f2=2 and f3=1这样的条件进行查询。

这波是不是小秀？

你 SQL 不带索引最左的条件是吧，我自个儿给你拼上去！

所以一条查询就变成了多次查询，所以 type 就变成了 range 了。

因此最终还是没有逃出最左匹配原则，只是 MySQL 隐式的构造了查询条件，使得看起来好像不需要最左匹配原则而已。

这个优化的中文名叫：跳过扫描范围访问(我直译的，哈哈哈)

跳过扫描范围访问的局限性

不要高兴的太早，这个优化其实有很大的局限性！

它要求最左条件的基数很低，因为它需要列举最左条件的值来构造查询，假如最左条件有上万个，那还不如直接进行全表扫描了对吧。

其实也不需要上万个，我来改一下表里的数据来做一次实验。

我直接把 f2 的值随机化了，这样 f2 的基数就变大了，可以看到此时再执行查询语句就用不上了Using index for skip scan了。

变成了一个基于二级索引的全表扫描。

除了上面这个关键点之外，还有很多局限性，我根据官网的内容做了一下翻译(和一点便于理解的小修改)：

• 需要联合索引
• 查询不能跨表
• 查询不能使用 GROUP BY 或者 DISTINCT
• 查询只能用一个索引，即索引需要覆盖查询的值，不能回表
• 查询条件必须是常量，包括 IN() 运算符。

还有两条我不翻译了，我觉得讲的是废话…有点兴趣的自己去看看吧，文末会放链接。

总而言之，能用上这个优化的场景很有限，就基数很低这一个限制我就觉得很大了。

所以这也不是什么万能药，就当一个知识点咱们知晓下，到时候遇到特殊场景能用上的时候装一下，或者在面试官问你最左匹配的时候，提一下，我知道那个 MySQL 8.0.13 版本啊，它做了个优化…..

595.MySQL 的覆盖索引是什么？

回答重点

MySQL 的覆盖索引（Covering Index）是指二级索引中包含了查询所需的所有字段，从而使查询可以仅通过访问二级索引而不需要访问实际的表数据（主键索引）。

扩展知识

覆盖索引的优点

• 减少I/O操作：因为查询可以直接从索引中获取所有需要的数据，避免了访问实际表的数据页，从而减少了I/O操作。
• 提高查询速度：索引比表数据更紧凑，因此从索引中读取数据比从表中读取要快。
• 减少内存占用：只需要读取索引页而不是表数据页，可以减少内存占用。

覆盖索引示例

比如，目前有一张表：

1
2
3
4
5
6
7
8

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE,
    department_id INT,
    INDEX idx_name_hiredate (last_name, first_name, hire_date)
);

我们需要获取员工的姓、名和雇佣日期：

1
2
3

SELECT last_name, first_name, hire_date 
FROM employees 
WHERE last_name = '鸭' AND first_name = '面试';

对于这个查询，idx_name_hiredate 就是一个覆盖索引，因为它包含了查询所需的所有列 last_name、first_name 和 hire_date，MySQL 可以直接从这个索引中获取结果，而无需访问实际的主键索引的数据。

如果需求变化，要多查找一个 department_id 信息：

1
2
3

SELECT last_name, first_name, hire_date, department_id 
FROM employees 
WHERE last_name = '鸭' AND first_name = '面试';

此时就无法利用覆盖索引了，因为 idx_name_hiredate 上并不包含 department_id，所以需要从主键索引中获得数据。

596.MySQL 的索引下推是什么？

回答重点

索引下推（Index Condition Pushdown, ICP）是一种减少回表查询，提高查询效率的技术。它允许 MySQL 在使用索引查找数据时，将部分查询条件下推到存储引擎层过滤，从而减少需要从表中读取的数据行，减少了 IO（本该由 Server 层做操作，交由存储引擎层因此叫做 “下推” ） 。

注意：索引下推是应用在联合索引上的。

扩展知识

官网示例说明

假设当前 people 表有索引 INDEX (zipcode, lastname, firstname) ，当执行以下查询时：

1
2
3
4

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

如果没有索引下推，当前的联合索引只能用上 zipcode='95054' 这个条件，引擎层用不上 lastname 这个条件过滤，只能得到所有符合 zipcode='95054' 记录，传递给 server 层过滤。

有了索引下推之后，引擎层在得到符合zipcode='95054'的数据后，可以直接通过 lastname 条件过滤数据，不符合条件的不会返回给 server 层。

索引下推注意点

1）索引下推在 MySQL 5.6 及以后的版本支持，InnoDB 和 MyISAM 这两个存储引擎都生效。

2）如果查询中引用了子查询索引下推可能不会生效，具体看 explain。

3）使用了函数或表达式索引下推也不能生效，这个和是否能命中索引的条件是一样的。

4）使用了聚簇索引（主键）查询，索引下推也不会生效，因为其是对于非聚簇索引来进行减少回表次数。

597.在 MySQL 中建索引时需要注意哪些事项？

回答重点

简单总结了以下六点：

1）不能盲目建立索引，索引并不是越多越好，索引会占用空间，且每次修改的时候可能都需要维护索引的数据，消耗资源。

2）对于字段的值有大量重复的不要建立索引。比如说：性别字段，在这种重复比例很大的数据行中，建立索引也不能提高检索速度。但是也不绝对，例如定时任务的场景，大部分任务都是成功，少部分任务状态是失败的，这时候通过失败状态去查询任务，实际上能过滤大部分成功的任务，效率还是可以的。

3）对于一些长字段不应该建立索引。比如 text、longtext 这种类型字段不应该建立索引。因为占据的内存大，扫描的时候大量加载至内存中还耗时，使得提升的性能可能不明显，甚至可能还会降低整体的性能，因为别的缓存数据可能因为它被踢出内存，下次查询还需要从磁盘中获取。

4）当数据表的修改频率远大于查询频率时，应该好好考虑是否需要建立索引。因为建立索引会减慢修改的效率，如果很少的查询较多的修改，则得不偿失。

5）对于需要频繁作为条件查询的字段应该建立索引。在 where 关键词后经常查询的字段，建立索引能提高查询的效率，如果有多个条件经常一起查询，则可以考虑联合索引，减少索引数量。

6）对经常在 order by、group by、distinct 后面的字段建立索引。这些操作通常需要对结果进行排序、分组或者去重，而索引可以帮助加快这些操作的速度。

扩展知识

MySQL 索引的最左前缀匹配原则

• [594. MySQL 索引的最左前缀匹配原则是什么？](#594MySQL 索引的最左前缀匹配原则是什么)

MySQL 的索引类型

• [591. MySQL 的索引类型有哪些？](#591MySQL 的索引类型有哪些)

598.MySQL 中使用索引一定有效吗？如何排查索引效果？

回答重点

索引不一定有效。

例如查询条件中不包含索引列、低基数列索引效果不佳，或查询条件复杂且不匹配索引的顺序。

对于一些小表，MySQL可能选择全表扫描而非使用索引，因为全表扫描的开销可能更小。

最终是否用上索引是根据 MySQL 成本计算决定的，评估 CPU 和 I/O 成本最终选择用辅助索引还是全表扫描。有时候确实是全表扫描成本低所以没用上索引。但有时候由于一些统计数据的不准确，导致成本计算误判，而没用上索引。

排查索引效果的方法：使用 EXPLAIN 命令，通过在查询前加上EXPLAIN，可以查看 MySQL 选择的执行计划，了解是否使用了索引、使用了哪个索引、估算的行数等信息。

主要观察 EXPLAIN 结果以下几点：

• type（访问类型）：这个属性显示了查询使用的访问方法，例如 ALL、index、range 等。当查询使用索引时，这个属性通常会显示为 index 或 range ，表示查询使用了索引访问。如果这个值是 ALL ，则表示查询执行了全表扫描，没有使用索引。
• key（使用的索引）：这个属性显示了查询使用的索引，如果查询使用了索引，则会显示索引的名称。如果这个值是 NULL，则表示查询没有使用索引。
• rows（扫描的行数）：这个属性显示了查询扫描的行数，需要评估下扫描量。

扩展知识

确定索引真的生效了吗？

索引失效的场景有很多，也是面试官经常喜欢问的，可以根据具体场景进行排查，典型场景可以分为以下几点（实际索引的选择会根据 mysql 优化器的成本评定，答案最后会提到）：

1）使用了联合索引却不符合最左前缀

举个例子：小鱼对 user 表建立了一个联合索引为 name_age_id 的联合索引。

他使用以下 SQL 查询select * from user where age = 10 and id = 1;

这样的写法恰恰不满足最左前缀原则，索引就失效啦。

2）索引中使用了运算

例如这个 SQL select * from user where id + 3 = 8。这样会导致全表扫描计算 id 的值再进行比较，使得索引失效。

3）索引上使用了函数也会失效

例如：select * from user where LOWER(name) like 'cong%';。这样也会导致索引失效，索引参与了函数处理，会导致去全表扫描。

4） like 的随意使用

例如：select * from user where name like '%cong%';因为索引是从左到右来进行排序查找的，占位符直接放在了最左边开头，可能会导致直接全表扫描，这种情况就会导致索引失效。

5）or 的随意使用

user 当前只有一个索引 name 。此时执行以下SQL ：select * from user where name= 'cong' or age = 18; 这可能也会导致索引失效，因为 age 没有索引。

6）随意的字段类型使用

不小心将 varchar 类型的 name 条件匹配了 int 类型字段。SQL 是这样的 select * from user where name = 1;，在代码中涉及 隐式转换 ！等于 select * from user where CAST(name AS signed int) = 1;，这就变成了第三条索引上使用了函数，导致索引失效。

除此之外还有隐式字符编码转换的问题，即联表查询的时候，如果不同表之间的关联字段字符编码不一致，也会导致隐式转换编码，等于变相用上了函数，使得索引失效。

7）不同的参数也会导致索引失效

这个就是我在回答重点里面说的“是否用上索引是根据 MySQL 成本计算决定的”。不同的参数 MySQL 评估成本不一致，有时候会选择使用索引，有时候会选择全表扫描，特别是在复杂查询（联表、子查询、需要回表等）的情况下。

比如根据商品从订单表查询，收集商品对应的所有买家的订单信息。如果传入的商品 id 是个热点商品，占据这家店铺 80% 的销量，那么本次查询对订单表很可能是全表查询，如果是冷门商品，则很可能是走索引查询。

8）表中两个不同字段进行比较

例如这样的SQL :select * from user where id > age; ，将 id 跟 age 字段做了比较，索引失效。

9）使用了 order by

当 order by 后面跟的 不是主键 或者 不是覆盖索引 会导致不走索引。

为什么索引生效了反而查询变慢了呢？

确认是否选对了索引！MySQL 根据优化器会评估成本选择对应的索引，但有时候 MySQL 因为估计值不准确，导致选错了索引，因此查询速度反而更慢。

关于使用索引的进一步认识

为了故事可以顺利的发展，先来一张表， DDL 如下：

现在要执行这个语句SELECT * FROM yes.t1 where f3 = 11;

你觉得用的是哪个索引？

很多同学可能会觉得虽然有 idx_f2_f3 这个联合索引，但是查询条件是 f3，没有 f2，不符合最左匹配原则，所以很显然只能进行全表扫描。

我们来 explain 一下，看看：

可以看到结果，key 是 idx_f2_f3 ，所以这个查询用上了 idx_f2_f3 这个索引！这里可能有人就理解不了，怎么不符合最左索引原则也能用上联合索引了呢？

从 explain 结果来看，确实用上的联合索引，但是它的 type 是 index，所以它不是你认为的那种用上索引，它等于全扫描的联合索引来获取结果。现在不太理解没事，听下面好好解释。

到底什么叫用了索引？

我们都知道 InnoDB 是基于聚簇索引来构建数据的，在没有创建其它二级索引的前提下，你查询数据就必须且只能用聚簇索引，因为数据就只在这个索引上，那你找数据不用这个索引那用什么呢？

如果你建起了其他二级索引，那么查找数据就可能不用聚簇索引，而是只用上二级索引就能直接得到想要的数据(就是上面查询的例子)。

综上所述，你在 InnoDB 查找任何数据，都只能用索引。

那为什么平日里有人说这个不走索引，走了全表扫描呢？

我们都知道默认 InnoDB 的索引结构是 B+ 树，这个树有个特性，它的叶子节点之间是通过链表相连的。

所以只要我们找到主键索引最左边的叶子结点，然后往右顺序遍历扫描，就能得到这张表的所有记录，这样的扫描方式就叫全表扫描。

小贴士：我不清楚 MySQL 是记录了最左叶子节点的位置还是根据根节点查找到最左叶子节点的，反正意思就是那个意思，不影响我们理解全表扫描。

那什么叫用上索引？

看红色的查询路线没，能利用树的快速查找能力来定位到数据所在的页，这样的查询才能算用上索引。

那从主键索引反推到二级索引，其实是一样的。能利用二级索引树的特性来查找数据，就是用上了索引。

但是如果用不上二级索引树的快速查找，是否有像全表扫描这样，直接扫描二级索引的所有叶子节点的操作呢？

有的，就是我们上面的例子，我再把执行结果的截图搬下来：

这次查询 MySQL 就是全扫描二级索引 idx_f2_f3 来直接返回所要的数据。

原因就是二级索引已经包含了查询所要到全部数据:f1、f2、f3，所以直接全扫描二级索引即可。

可以看到 explain 的 type=index，即全扫描二级索引。

这也叫用上了索引。

此时我们换用 f2 来作为查询条件，这样就符合最左匹配原则了，我们来看看 explain 有什么区别：

可以看到还是用了 idx_f2_f3 ，只是 type 变成 ref ，这个 ref 就表明可以与常量进行等值匹配时来快速查询得到结果。

这也叫用上了索引。

好了，我们来总结一下到底什么叫用了索引：

• 利用主键索引快速查找
• 利用二级索引快速查找
• 全扫描二级索引进行查找

以上这三种就是我们平日里所说的用上了索引，然后全扫描主键索引就是我们所说的全表扫描（等价于没用上索引，但是我们知道它其实是用上了主键索引对吧？）。

至此，我想你应该已经清楚，到底什么叫用了索引了。

小疑惑

关于 MySQL 索引的一些认识都讲完了，不过看了上面的例子，有些同学可能会有点疑惑：

既然都要全扫描，为什么要扫描二级索引，而不是主键索引？

因为主键索引存储了所有的数据，包括数据行相关的事务ID、回滚指针等等。

而二级索引不包含这些，它只有主键值和索引列的值。

所以相对而言直接读取二级索引开销会更小一些，所以在索引能覆盖返回值的时候，一般都会选择二级索引来查找。

比如我把上面的表再加个字段：f4。

现在我们再来执行SELECT * FROM yes.t1 where f3 = 11;

可以看到，这个时候就没用上二级索引了，而是直接全表扫描了。原因就是二级索引无法覆盖返回值，因为多了个 f4 的字段。

如果用了 idx_f2_f3 这个索引，想要得到 f4 的值，还需要再得到的主键再去主键索引里面进行查找，然后才能得到 f4 的值，这样回表的效率就低了。

当然其实结果还不一定，有时候也有可能 MySQL 的统计信息有误，用错了索引。因为 MySQL 是基于成本来选择执行计划的，有 I/O 成本和 CPU 成本，哪个索引成本低就用哪个索引。

MySQL 如何选择执行计划(I/O 成本和 CPU 成本，即如何选择合适的索引)？

• 2640. MySQL 的查询优化器如何选择执行计划？

599.MySQL 中的索引数量是否越多越好？为什么？

回答重点

索引并不是越多越好。因为索引不论从时间还是空间上都是有一定成本的

1）从时间上

每次对表中的数据进行增删改(INSERT、UPDATE 或 DELETE)的时候，索引也必须被更新，这会增加写入操作的开销。例如删除了一个 name 为面试鸭的记录，不仅主键索引上需要修改，如果 name 字段有索引，那么 name 索引也需要修改，所以索引越多需要修改的地方也就越多，时间开销就大了，并且 B+ 树可能会有页分裂、合并等操作，时间开销就会更大。

还有一点需要注意：MySQL 有个查询优化器，它需要分析当前的查询，选择最优的计划，这过程就需要考虑选择哪个索引的查询成本低。如果索引过多，那么会导致优化器耗费更多的时间在选择上，甚至可能因为数据的不准确而选择了次优的索引。

2）从空间上

每建立一个二级索引，都需要新建一个 B+ 树，默认每个数据页都是 16KB，如果数据量很大，索引又很多，占用的空间可不小。

扩展知识

• [597. 在 MySQL 中建索引时需要注意哪些事项？ ](#597在 MySQL 中建索引时需要注意哪些事项)

600.请详细描述 MySQL 的 B+ 树中查询数据的全过程

回答重点

1）数据从根节点找起，根据比较数据键值与节点中存储的索引键值（用了二分），确定数据落在哪个区间，从而确定分支，从上到下最终定位到叶子节点

2）叶子节点存储实际的数据行记录，但是一页有 16KB 大小，存储的数据行不止一条

3）叶子节点中数据行以组的形式划分，利用页目录结构，通过二分查找可以定位到对应的组

4）定位组后，利用链表遍历就可以找到对应的数据行

扩展知识

详细流程解析

数据从根节点找起，根据比较数据键值与节点中存储的索引键值（非叶子节点也有页目录，也能用二分查找，详细看下面叶子节点的分析），确定数据落在哪个区间，从而确定分支，从上到下最终定位到叶子节点。

定位到叶子节点后，因为一片叶子默认有 16KB 大小，所以理论上可以存多条记录。叶子节点的实际构造如下图所示：

从上图可以知晓，叶子节点有页目录结构（非叶子节点也有，当前拿叶子节点分析，结构是一样的），它其实就是一个索引，通过它可以快速找到记录。

页目录分为了多个槽，每个槽都指向对应一个分组内的最大记录，每个分组内都会包含若干条记录。

通过二分查询，利用槽就能直接定位到记录所在的组，从而就能获取到对应的记录。

举个例子，现在有 5 个槽，如果想查找主键为 3 的记录，此时的流程是：

1）通过二分得到槽的中间位置，low = 0，high = 4，（0+4)/2 = 2; 2）通过槽定位到第二个分组中的主键为 4 的记录，4 大于 3，low = 0 不变，high = 2; 3）继续二分 (0+2)/2 = 1; 槽 1 中主键 2 小于 3，low = 1，high = 2; 4）此时 high - low = 1，可以确定值在 high 即槽 2 中，但是槽 2 只能定位到主键为 4 的记录，又因为槽之间是挨着的，所以可以得到槽 1 的位置，从槽 1 入手拿到 主键 2 的记录，然后因为记录是通过单向链表串起来的，往下遍历即可定位到主键 3 的记录。

以上就是利用二分查询的定位流程。通过槽可找到对应记录所在的组，或能直接定位到记录，或还需通过链表遍历找到对应的数据。

实际上，每个分组的记录数是有规定的，图中做了省略只画了两条，InnoDB 规定：

• 第一个分组只有一条记录
• 中间的分组 4-8 条记录
• 最后一个分组 1-8 条记录

因此不必担心遍历很长的链表导致性能问题。

这题的重点是先简单提下从根节点遍历到子节点的过程，然后提到每页节点默认大小为 16KB ，所以理论上能存储很多记录，从而引出页目录，再通过二分查找才能对应记录。

601.为什么 MySQL 选择使用 B+ 树作为索引结构？

回答重点

B+ 树在数据库系统中具有以下几个显著优势：

1）高效的查找性能：

B+ 树是一种自平衡树，每个叶子节点到根节点的路径长度相同，B+ 树在插入和删除节点时会进行分裂和合并操作，以保持树的平衡，但它又会有一定的冗余节点，使得删除的时候树结构的变化小，更高效。

查找、插入、删除等操作的时间复杂度为 O(log n)，能够保证在大数据量情况下也能有较快的响应时间。

2）树的高度增长不会过快，使得查询磁盘的 I/O 次数减少：

B+ 树不像红黑树，数据越多树的高度增长就越快。它是多叉树，非叶子节点仅保存主键或索引值和页面指针，使得每一页能容纳更多的记录，因此内存中就能存放更多索引，容易命中缓存，使得查询磁盘的 I/O 次数减少。

3）范围查询能力强：

B+ 树特别适合范围查询。因为叶子节点通过链表链接，从根节点定位到叶子节点查找到范围的起点之后，只需要顺序扫描链表即可遍历后续的数据，非常高效。

扩展知识

B+ 树和 B 树区别

1）B 树每个节点都存储了完整的数据，而 B+ 树非叶子节点仅存储 key 和指针，完整数据存储在叶子节点。这使得 B+ 树可以在内存中存放更多索引页，减少磁盘查询次数。

2）B+ 树叶子组成了链表，便于区间查找，而 B 树只能每一层遍历查找。

3）B+ 树查询时间更平均、稳定，都需要从根节点扫描到叶子节点。而 B 树则在非叶子节点就可能找到对应的数据返回。

602.MySQL 是如何实现事务的？

回答重点

MySQL 主要是通过：锁、Redo Log 、Undo Log、MVCC 来实现事务。

MySQL 利用锁（行锁、间隙锁等等）机制，使用数据并发修改的控制，满足事务的隔离性。

Redo Log（重做日志），它会记录事务对数据库的所有修改，当 MySQL 发生宕机或崩溃时，通过重放 redolog 就可以恢复数据，用来满足事务的持久性。

Undo Log（回滚日志），它会记录事务的反向操作，简单地说就是保存数据的历史版本，用于事务的回滚，使得事务执行失败之后可以恢复之前的样子。实现原子性和隔离性

MVCC（多版本并发控制），满足了非锁定读的需求，提高了并发度，实现了读已提交和可重复读两种隔离级别，实现了事务的隔离性。

看到这有小伙伴问一致性呢？

其实事务主要是为了实现一致性，具体是通过 AID，即原子性、隔离性和持久性来达到一致性的目的。

扩展知识

binlog、redo log 和 undo log

• [106. MySQL 中的日志类型有哪些？binlog、redo log 和 undo log 的作用和区别是什么？](#106MySQL 中的日志类型有哪些binlog、redo log 和 undo log 的作用和区别是什么)
• 1534.什么是 Write-Ahead Logging (WAL) 技术？它的优点是什么？MySQL 中是否用到了 WAL？

MySQL 锁

609.MySQL 中有哪些锁类型？

Log buffer

628. MySQL 中的 Log Buffer 是什么？它有什么作用？

Undo Log 和 MVCC

[604.MySQL 中的 MVCC 是什么？](#604MySQL 中的 MVCC 是什么)

603.MySQL 中长事务可能会导致哪些问题？

回答重点

1）长时间的锁竞争，阻塞资源：

• 长事务持有锁的时间较长，容易导致其他事务在尝试获取相同锁时发生阻塞，从而增加系统的等待时间和降低并发性能。
• 业务线程也会因为长时间的数据库请求等待而阻塞，部分业务的阻塞可能还会影响到别的服务，导致产生雪崩，最终使得服务全面崩盘，导致非常严重的线上事故。

2）死锁风险：

• 长事务更容易产生死锁，因为多个事务可能在互相等待对方释放锁，导致系统无法继续执行。

3）主从延迟：

• 主库需要长时间执行，然后传输给从库，从库又要重放好久，期间可能有很长一段时间数据是不同步的。

4）回滚导致时间浪费：

• 如果长事务执行很长一段时间，中间突发状况导致抛错，使得事务回滚了，之前做的执行都浪费了。

扩展知识

长事务的 SQL 如何处理？

比如，你现在需要删除一张一共有 5 亿数据的表里面的 2021 年数据，假设这张表叫 yes。

我相信你脑子在 1s 内肯定会蹦出这条 SQL :

1

delete from yes where create_date > " 2020-12-31" and create_date < "2022-01-01";

当时由于数据量太大，这就会产生上面所说的长事务问题。如何解决呢？

答案就是拆！怎么拆呢？想必很多小伙伴会觉得很简单，按日期拆不就完事了？

delete from yes where create_date > "2020-12-31" and create_date < "2021-02-01";

delete from yes where create_date >= "2021-02-01" and create_date < "2021-03-01"; ……

这当然可以，恭喜你，你已经拆分成功了，没错就这么简单。

但是，如果 create_date 没有索引怎么办？

没索引的话，上面这就全表扫描了啊？

影响不大，没有索引我们就给他创造索引条件，这个条件就是主键。

我们直接一个 select min(id)… 和 select max(id)…. 得到这张表的主键最小值和最大值，假设答案是233333333 和 666666666。

然后我们就可以开始操作了：

delete from yes where (id >= 233333333 and id < 233433333) and create_date > "2020-12-31" and create_date < "2022-01-01";

delete from yes where (id >= 233433333 and id <233533333) and create_date > "2020-12-31" and create_date < "2022-01-01";

……

delete from yes where (id >= 666566666 and id <=666666666) and create_date > "2020-12-31" and create_date < "2022-01-01";

当然你也可以再精确些，通过日期筛选来得到 maxId，这影响不大（不满足条件的 SQL 执行很快，不会耗费很多时间）。

这样一来 SQL 就满足了分批的操作，且用得上索引。

如果哪条语句执行出错，只会回滚小部分数据，我们重新排查下就好了，影响不大。

而且拆分 SQL 之后还可以并行提高执行效率。

当然并行可能有锁竞争的情况，导致个别语句等待超时。不过影响不大，只要机器状态好，执行的快，因为锁竞争导致的等待并不一定会超时，如果个别 SQL 超时的话，重新执行就好了。

删除的思路转换

关于大表删除有时候要转换思路，把删除转成插入。

假设还是有一张 5 亿的数据表，此时你需要删除里面 4.8 亿的数据，那这时候就不要想着删除了，要想着插入。

道理很简单，删除 4.8 亿的数据，不如把要的 2000W 插入到新表中，我们后面业务直接用新表就好了。

这两个数据量对比，时间效率差异不言而喻了吧？

具体操作也简单：

1. 创建一张新表，名为 yes_temp
2. 将 yes 表的 2000W 数据 select into 到 yes_temp 中
3. 将 yes 表 rename 成 yes_233
4. 将 yes_temp 表 rename 成 yes

狸猫换太子，大功告成啦！

之前有个记录表我们就是这样操作的，就 select into 近一个月的数据到新表中，以前老数据就不管了，然后 rename 一下，执行的非常快，1 分钟内就搞定了。

这种类似的操作是有工具的，比如 pt-online-schema-change 等，不过我没用过，有兴趣的小伙伴可以自己去看看，道理是一样的，多了几个触发器，这里不多赘述了。

604.MySQL 中的 MVCC 是什么？

回答重点

MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种并发控制机制，允许多个事务同时读取和写入数据库，而无需互相等待，从而提高数据库的并发性能。

在 MVCC 中，数据库为每个事务创建一个数据快照。每当数据被修改时，MySQL 不会立即覆盖原有数据，而是生成新版本的记录。每个记录都保留了对应的版本号或时间戳。

多版本之间串联起来就形成了一条版本链，这样不同时刻启动的事务可以无锁地获得不同版本的数据(普通读)。此时读(普通读)写操作不会阻塞。

写操作可以继续写，无非就是会创建新的数据版本（但只有在事务提交后，新版本才会对其他事务可见。未提交的事务修改不会影响其他事务的读取），历史版本记录可供已经启动的事务读取。

（为保持简短，简化了SQL语句，下面的内容也同样简化）

扩展知识

Undo Log

Undo Log 是 MySQL InnoDB 中用于支持事务的回滚操作的一种日志机制。它记录了数据修改的历史信息，使得在事务失败或需要撤销某些操作时，可以将数据恢复到先前的状态。

实际上 MVCC 所谓的多版本不是真的存储了多个版本的数据，只是借助 undolog 记录每次写操作的反向操作，所以索引上对应的记录只会有一个版本，即最新版本。只不过可以根据 undolog 中的记录反向操作得到数据的历史版本，所以看起来是多个版本。

拿上面的 insert （1，XX）这条语句举例，成功插入之后数据页的记录上不仅存储 ID 1，name XX，还有 trx_id 和 roll_pointer 这两个隐藏字段：

• trx_id：当前事务ID。
• roll_pointer：指向 undo log 的指针。

从图中可以得知此时插入的事务 ID 是 1，此时插入会生成一条 undolog ，并且记录上的 roll_pointer 会指向这条 undolog ，而这条 undolog 是一个类型为TRX_UNDO_INSERT_REC的 log，代表是 insert 生成的。

里面存储了主键的长度和值(还有其他值，不提)，所以 InnoDB 可以根据 undolog 里的主键的值，找到这条记录，然后把它删除来实现回滚(复原)的效果。因此可以简单地理解 undolog 里面存储的就是当前操作的反向操作，所以认为里面存了个 delete 1 就行。

此时事务1提交，然后另一个 ID 为 5 的事务再执行 update NO where id 1 这个语句，此时的记录和 undolog 就如下图所示：

没错，之前 insert 产生的 undolog 没了，insert 的事务提交了之后对应的 undolog 就回收了，因为不可能有别的事务会访问比这还要早的版本了，访问插入之前的版本？访问个寂寞吗？

而 update 产生的 undolog 不一样，它的类型为 TRX_UNDO_UPD_EXIST_REC。

此时事务 5 提交，然后另一个 ID 为 11 的事务执行update Yes where id 1 这个语句，此时的记录和 undolog 就如下图所示：

没错，update 产生的 undolog 不会马上删除，因为可能有别的事务需要访问之前的版本，所以不能删。这样就串成了一个版本链，可以看到记录本身加上两条 undolog，这条 id 为 1 的记录共有三个版本。

readView

版本链搞清楚了，这时候还需要知道一个概念 readView，这个 readView 就是用来判断哪个版本对当前事务可见的，这里有四个概念：

• creator_trx_id，当前事务ID。
• m_ids，生成 readView 时还活跃的事务ID集合，也就是已经启动但是还未提交的事务ID列表。
• min_trx_id，当前活跃ID之中的最小值。
• max_trx_id，生成 readView 时 InnoDB 将分配给下一个事务的 ID 的值（事务 ID 是递增分配的，越后面申请的事务ID越大）

对于可见版本的判断是从最新版本开始沿着版本链逐渐寻找老的版本，如果遇到符合条件的版本就返回。

判断条件如下：

• 如果当前数据版本的 trx_id == creator_trx_id 说明修改这条数据的事务就是当前事务，所以可见。
• 如果当前数据版本的 trx_id < min_trx_id，说明修改这条数据的事务在当前事务生成 readView 的时候已提交，所以可见。
• 如果当前数据版本的 trx_id 大小在 min_trx_id 和 max_trx_id 之间，此时 trx_id 若在 m_ids 中，说明修改这条数据的事务此时还未提交，所以不可见，若不在 m_ids 中，表明事务已经提交，可见。
• 如果当前数据版本的 trx_id >= max_trx_id，说明修改这条数据的事务在当前事务生成 readView 的时候还未启动，所以不可见(结合事务ID递增来看)。

来看一个简单的案例，练一练上面的规则。

读已提交隔离级别下的 MVCC

现在的隔离级别是读已提交。

假设此时上文的事务1已经提交，事务 5 已经执行，但还未提交，此时有另一个事务在执行update YY where id 2，也未提交，它的事务 ID 为 6，且也是现在最大的事务 ID。

现在有一个查询开启了事务，语句为select name where id 1，那么这个查询语句：

• 此时 creator_trx_id 为 0，因为一个事务只有当有修改操作的时候才会被分配事务 ID。
• 此时 m_ids 为 [5，6]，这两个事务都未提交，为活跃的。
• 此时 min_trx_id，为 5。
• 此时 max_trx_id，为 7，因为最新分配的事务 ID 为 6，那么下一个就是7，事务 ID 是递增分配的。

由于查询的是 ID 为 1 的记录，所以先找到 ID 为 1 的这条记录，此时的版本如下：

此时最新版本的记录上 trx_id 为 5，不比 min_trx_id 小，在 m_ids 之中，表明还是活跃的，未提交，所以不可访问，根据 roll_pointer 找到上一个版本。

于是找到了图上的那条 undolog，这条log上面记录的 trx_id 为 1，比 min_trx_id 还小，说明在生成 readView 的时候已经提交，所以可以访问，因此返回结果 name 为 XX。

然后事务 5 提交。

此时再次查询 select name where id 1，这时候又会生成新的 readView。

• 此时 creator_trx_id 为 0，因为还是没有修改操作。
• 此时 m_ids 为 [6]，因为事务5提交了。
• 此时 min_trx_id，为 6。
• 此时 max_trx_id，为 7，此时没有新的事务申请。

同样还是查询的是 ID 为 1 的记录，所以还是先找到 ID 为 1 的这条记录，此时的版本如下(和上面一样，没变)：

此时最新版本的记录上 trx_id 为 5，比 min_trx_id 小，说明事务已经提交了，是可以访问的，因此返回结果 name 为 NO。

这就是读已提交的 MVCC 操作，可以看到一个事务中的两次查询得到了不同的结果，所以也叫不可重复读。

可重复读隔离级别下的MVCC

现在的隔离级别是可重复读。

可重复读和读已提交的 MVCC 判断版本的过程是一模一样的，唯一的差别在生成 readView 上。

上面的读已提交每次查询都会重新生成一个新的 readView ，而可重复读在第一次生成 readView 之后的所有查询都共用同一个 readView 。

也就是说可重复读只会在第一次 select 时候生成一个 readView ，所以一个事务里面不论有几次 select ，其实看到的都是同一个 readView 。

套用上面的情况，差别就在第二次执行select name where id 1，不会生成新的 readView，而是用之前的 readView，所以第二次查询时：

• m_ids 还是为 [5，6]，虽说事务 5 此时已经提交了，但是这个readView是在事务5提交之前生成的，所以当前还是认为这两个事务都未提交，为活跃的。
• 此时 min_trx_id，为 5。

(对于判断过程有点卡顿的同学可以再拉上去看看，判断版本的过程和读已提交一致)。

所以在可重复级别下，两次查询得到的 name 都为 XX，所以叫可重复读。

可重复读能完全避免幻读的发生吗？

不能。

首先了解下快照读和当前读这两个概念。

快照读（Snapshot Read）

快照读是指事务在执行查询时，不直接读取当前最新的数据，而是读取数据的历史版本（快照）。MySQL InnoDB 通过多版本并发控制（MVCC）来实现快照读。快照读只会返回在事务开始时可见的数据，即使其他事务在之后修改了这些数据，快照读也不会受影响。

当前读（Current Read）

当前读是指读取数据的最新版本，并且会加锁以确保数据的一致性。即使其他事务对这个数据的修改在当前读的事务之后，这个修改也会反映在当前读的结果中（如果事务 2 开始时间发生在事务 1 之后，那么正常情况下事务 2 的修改对事务 1 不可见，但是事务 1 的当前读可见这个修改）。

当前读出现在带有锁的查询中，如 SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE以及 UPDATE、DELETE等 DML 语句。

当前读需要获取最新版本应该不难理解，例如别的事务已经 delete 一条语句了，当前事务要执行 update 不得先去看看当前最新的数据情况，如果都删除了还 update 成功那不就乱了吗？

当前读会通过加锁（如 Next-Key Locking）锁定读范围内的所有记录和间隙，防止其他事务在该范围内插入新记录。

再回到可重复读和幻读的问题上来，我们来看下这个场景：

由于当前读的特性，即使在可重复读隔离级别下，也产生了幻读的场景。

如果要避免这个幻读的产生，那么在事务开始的时候，直接就使用 SELECT ... FOR UPDATE，加锁了后其他事务就新增不了数据了，也就避免了幻读的发生。

605.如果 MySQL 中没有 MVCC，会有什么影响？

回答重点

如果没有 MVCC，系统必须频繁地对读写操作进行加锁来保证数据的正确性，因为增加了锁的获取和释放的开销，会导致整体系统响应速度变慢，这种实现叫 LBCC (Lock-Based Concurrent Control)。

扩展知识

LBCC (Lock-Based Concurrent Control)

想象一下有一个事务 1 正在执行，此时一个事务 2 修改了记录 A，还未提交事务。

此时事务 1 要读取记录 A，因为事务 2 还未提交，所以事务 1 无法读取最新的记录 A，不然万一事务 2 回滚了，这不就产生脏读了吗？

那让事务 1 读记录 A 被事务 2 修改之前的数据？但是数据已经被事务 2 改了啊，事务 1 去哪里找之前的数据？

所以怎么办呢？

事务 1 只能用锁阻塞等待事务 2 的提交，这种实现叫 LBCC (Lock-Based Concurrent Control)。

如果有多版本的话，就不一样了。

事务 2 修改的记录 A，还未提交，但是记录 A 被修改之前的版本还在，此时事务 1 就可以读取之前的版本数据，这样读写之间就不会阻塞啦，所以说MVCC 提高了事务的并发度，提升数据库的性能。

606.MySQL 中的事务隔离级别有哪些？

回答重点

在 MySQL 中，事务隔离级别主要有以下四种：

1）读未提交（READ UNCOMMITTED）：

• 这是最低的隔离级别，在该级别下，一个事务可以看到另一个事务尚未提交的数据修改。这可能会导致脏读问题，即读取到其他事务未提交的数据。

2）读已提交（READ COMMITTED）：

• 在这个级别下，一个事务只能看到已经提交的其他事务所做的修改。这可以避免脏读问题，但是可能会引发不可重复读问题，即在同一个事务中，相同的查询可能返回不同的结果。

3）可重复读（REPEATABLE READ）：

• 在这个级别下，确保在一个事务中的多个查询返回的结果是一致的。这可以避免不可重复读问题，但是可能会引发幻读问题，即在同一个事务中，多次查询可能返回不同数量的行（MySQL 默认的隔离级别）。

4）串行化（SERIALIZABLE）：

• 并发 SQL 事务在 SERIALIZABLE 隔离级别下的执行被保证是可串行化的。可串行化执行被定义为：并发执行的 SQL 事务的操作，其效果与这些 SQL 事务按某种顺序串行执行的效果相同。串行执行是指每个 SQL 事务在下一个 SQL 事务开始之前完成其全部操作 来源 SQL92定义。
• 这是最高的隔离级别，在这个级别下，保证事务间的操作结果相当于一个按顺序执行的单线程操作。这可以避免所有的并发问题，但是会大大降低并发性能。

扩展知识

隔离级别的影响

较低的隔离级别（如读未提交）提高了并发性，但可能导致数据不一致；较高的隔离级别（如串行化）保证数据一致性，但降低了并发性。因此，在设计应用时，需要在性能和数据一致性之间找到平衡。

一般互联网大厂会选择读已提交隔离级别。

• 1535. 你们生产环境的 MySQL 中使用了什么事务隔离级别？为什么？

脏读、不可重复读、幻读的概念

• 608. 数据库的脏读、不可重复读和幻读分别是什么？

补充说明 MySQL 的串行化（SERIALIZABLE）

根据官网所述，MySQL 的 SERIALIZABLE 级别类似于 REPEATABLE READ。但是如果关闭了自动提交事务，那么 SERIALIZABLE 级别下的普通 SELECT 会被隐式转成 SELECT ... FOR SHARE，即对读加共享锁确保其他事务无法修改正在读取的数据，实现了读的一致性。

这个操作就避免了REPEATABLE READ隔离级别下由于快照读和当前读混用形成的幻读问题。

607.MySQL 默认的事务隔离级别是什么？为什么选择这个级别？

回答重点

MySQL 默认的隔离级别是可重复读（ Repeatable Read ），即 RR。

原因是为了兼容早期 binlog 的 statement 格式问题，如果是使用读已提交、读未提交等隔离级别，使用了 statement 格式的 binlog 会导致主从（备）数据库数据不一致问题。

扩展知识

进一步分析 binlog statement 格式和可重复级别的影响

接下来我们来展开讲解下，便于大家理解。

MySQL 在使用过程中，为了避免单台故障，需要使用主从（备）机制：

而 MySQL 的主从（备）涉及 binlog 的复制，即从（备）库的数据是通过 binlog 从主库复制过来的。

在早期，MySQL binlog 仅支持 statement 格式，这个格式其实存的就是原先的SQL 语句，这就使得在读未提交（ru）和读提交（rc）两种隔离级别下会出问题。

我们来实际看个例子：

例如有两个事务 A 和 B ，以下图的时间顺序执行：

在读已提交隔离级别且手动提交事务的情况下， 插入的 5 这条记录会被保留。

但是由于事务 B 先提交，所以它会先被记录在 binlog 中，这个操作就导致了问题。

binlog 的记录顺序是：

使得从库同步 binlog 重放的时候，先执行了 insert 再执行 delete，这就使得从库的数据与主库不一致了！， 5 这条记录也会被删除了。

所以，为了避免这个问题，只能默认为可重复读级别。因为这个隔离级别下有间隙锁（Gap Locks）、临键锁（Next-Key Locks），所以事务 B 是无法先提交的，会被事务 A 阻塞，因此 binlog 的记录只能是先 delete 再 insert ，这样就没问题了。

608.数据库的脏读、不可重复读和幻读分别是什么？

回答重点

1）脏读（Dirty Read）：

• 一个事务读取到另一个事务未提交的数据。如果该未提交事务最终被回滚，那么第一个事务读取的数据就是不一致的（脏的）。

2）不可重复读（Non-repeatable Read）：

• 在同一事务中，读取同一数据两次，但由于其他事务的提交，读取的结果不同。例如，事务 A 读取了一行数据，事务 B 修改并提交了这行数据，导致事务 A 再次读取时得到不同的值。

3）幻读（Phantom Read）：

• 在同一事务中，执行相同的查询操作，返回的结果集由于其他事务的插入而发生变化。例如，事务 A 查询符合某条件的记录，事务 B 插入了新记录并提交，导致事务 A 再次查询时看到不同的记录数量。

扩展知识

几种读与隔离级别的关系：

脏读、不可重复读和幻读是不同隔离级别下可能发生的问题：

• 读未提交允许脏读。
• 读已提交防止脏读，但可能出现不可重复读。
• 可重复读防止脏读和不可重复读，但仍可能出现幻读。
• 串行化防止所有三种问题，但性能开销较大。

注意不可重复读与幻读的区别

幻读是指在事务期间，对于数据总量的突然增加或减少，将别的事务提交的读取到了（针对于数据的数量），不可重复读是指对于同一条数据，在事务执行期间，里面的字段内容发生了变化，读取到了别的事务提交修改的数据。（针对于数据的内容）。

609.MySQL 中有哪些锁类型？

回答重点

在 MySQL 中，主要有以下几种锁类型：

1）行级锁（Row Lock）（重点）：

• 仅对特定的行加锁，允许其他事务并发访问不同的行，适用于高并发场景。

2）表级锁（Table Lock）（重点）：

• 对整个表加锁，其他事务无法对该表进行任何读写操作，适用于需要保证完整性的小型表。

3）意向锁（Intention Lock）：

• 一种表锁，用于表示某个事务对某行数据加锁的意图，分为意向共享锁（IS）和意向排它锁（IX），主要用于行级锁与表级锁的结合。

4）共享锁（Shared Lock）（重点）：

• 允许多个事务并发读取同一资源，但不允许修改。只有在释放共享锁后，其他事务才能获得排它锁。

5）排它锁（Exclusive Lock）（重点）：

• 只允许一个事务对资源进行读写，其他事务在获得排它锁之前无法访问该资源。

6）元数据锁（Metadata Lock, MDL）：

• 用于保护数据库对象（如表和索引）的元数据，防止在进行 DDL 操作时其他事务对这些对象进行修改。

7）间隙锁（Gap Lock）（重点）：

• 针对索引中两个记录之间的间隙加锁，防止其他事务在这个间隙中插入新记录，以避免幻读。间隙锁不锁定具体行，而是锁定行与行之间的空间。

8）临键锁（Next-Key Lock）（重点）：

• 是行级锁和间隙锁的结合，锁定具体行和其前面的间隙，确保在一个范围内不会出现幻读。常用于支持可重复读的隔离级别。

9）插入意向锁（Insert Intention Lock）：

• 一种等待间隙的锁，用于指示事务打算在某个间隙中插入记录，允许其他事务进行共享锁，但在插入时会阻止其他的排它锁。

10）自增锁（Auto Increment Lock）：

• 在插入自增列时，加锁以保证自增值的唯一性，防止并发插入导致的冲突。通常在插入操作时被使用，以确保生成的自增 ID 是唯一的。

扩展知识

这个扩展知识比较长，也是从演进的角度来讲解 MySQL 中比较关键的几个锁，这样记忆和理解会更深刻，减少出现看过没多久就忘了的情况。

共享锁和排他锁

在 MySQL 中锁可以分为两大类，分别是 shared (S) locks 和 exclusive (X) locks。

• S锁，称为共享锁，事务在读取记录的时候获取 S 锁，它允许多个事务同时获取 S 锁，互相之间不会冲突。
• X锁，称为独占锁（排他锁），事务在修改记录的时候获取 X 锁，且只允许一个事务获取 X 锁，其它事务需要阻塞等待。

S 锁之间不冲突，X 锁则为独占锁，所以 X 之间会冲突， X 和 S 也会冲突。

SELECT ... LOCK IN SHARE MODE; 这种读取需要对记录上 S 锁。

SELECT ... FOR UPDATE; 需要对记录上 X 锁。

MyISAM 引擎仅支持表锁，而 Innodb 即支持表锁也支持行锁。

• LOCK TABLES yes READ 是对 yes 这个表上 S 锁。
• LOCK TABLES yes WRITE 是对 yes 这个表上 X 锁。

不过一般情况下，我们不会使用表锁，因为平日的 UPDATE 、SELECT 要用也是用行锁了，不可能用粒度粗的表锁，不然性能太低。

唯一能想到用上表锁的就是 DDL 语句了，比如 ALTER TABLE 的时候，应该锁定整个表，防止查询和修改。除此之外 MySQL 还提供了一个叫 MDL 的锁，即 Metadata Locks（元数据锁）。

元数据锁

元数据锁也分为读锁和写锁：

1）读锁 (MDL_SHARED)：

当一个事务需要读取表的元数据时（如执行 SELECT 操作），会获取读锁。 多个事务可以同时持有读锁，不会互相阻塞。

2）写锁 (MDL_EXCLUSIVE)：

当一个事务需要修改表的元数据时（如执行 ALTER TABLE 操作），会获取写锁。 写锁会阻塞其他任何读锁和写锁，确保独占访问。

元数据锁的主要作用如下：

1）防止并发的 DDL 操作和 DML 操作冲突：

• 当一个事务对表进行结构性更改（如 ALTER TABLE），元数据锁（写锁）会阻止其他事务对该表进行操作，直到结构更改完成。
• 当一个事务对表进行数据操作（如 SELECT、INSERT、UPDATE、DELETE），元数据锁（读锁）会阻止其他事务对该表进行结构性更改。

2）保护元数据一致性：

• 确保在执行 DDL 操作（如 CREATE TABLE、DROP TABLE、ALTER TABLE）时，元数据不会被其他事务同时修改。

假设业务上真用到了表锁，那么表锁和行锁之间肯定会冲突，当 InnoDB 加表锁的时候，如何判断表里面是否已经有行锁？难道得一条记录一条记录遍历过去找吗？

显然这样的效率太低了！

意向锁

所以有了个叫意向锁（Intention Locks）的东西。

• IS（Intention Shared Lock），共享意向锁
• IX（Intention Exclusive Lock），独占意向锁。

这两个锁是表级别的锁，当需要对表中的某条记录上 S 锁的时候，先在表上加个 IS 锁，表明此时表内有 S 锁。当需要对表中的某条记录上 X 锁的时候，先在表上加个 IX 锁，表明此时表内有 X 锁。

这样操作之后，如果要加表锁，就不需要遍历所有记录去找了，直接看看表上面有没有 IS 和 IX 锁。

比如，此时要上表级别的 S 锁，如果表上没有 IX ，说明表中没有记录有独占锁，其实就可以直接上表级 S 锁。

如果此时要上表级别的 X 锁，如果表上没有 IX 和 IS ，说明表中的所有记录都没加锁，其实就可以直接上表级 X 锁。

因此 IS 和 IX 的作用就是在上表级锁的时候，可以快速判断是否可以上锁，而不需要遍历表中的所有记录。

所以 IS 和 IX 互相之间是不会冲突的，因为它们的作用只是打个标记，来丰富一下上面的表格：

至此，已经理解了共享锁、独占锁、表锁相关的知识，接下来再来看看 MySQL 中的行锁有哪几种。

主要有三种：

• 记录锁（Record Locks）
• 间隙锁（Gap Locks）
• 临键锁（Next-Key Locks）

记录锁

记录锁顾名思义就是锁住当前的记录，它是作用到索引上的。我们都知道 innodb 是肯定有索引的，即使没有主键也会创建隐藏的聚簇索引，所以记录锁总是锁定索引记录。

比如，此时一个事务 A 执行 SELECT * FROM yes WHERE name = 'xx' FOR UPDATE; 那么 name = xx 这条记录就被锁定了，其他事务无法插入、删除、修改 name = xx 的记录。

此时事务 A 还未提交，另一个事务 B 要执行 insert into yes (name) values ('xx')，此时会被阻塞，这个很好理解。

但是，如果另一个事务 C 执行了 insert into yes (name) values ('aa')，这个语句会被阻塞吗？

看情况。

在可重复读隔离级别下，如果 name 没有索引。前面提到记录锁是加到索引上的，但是 name 没索引啊，那只能去找聚簇索引，但聚簇索引上面能通过 name 快速找到数据吗？它只能通过全表扫描一个一个看过去，所以咋办？都锁了呗！

因此，如果 name 没有索引，那么事务 C 会被阻塞，如果有索引，则不会被阻塞！

所以这里要注意，没索引的列不要轻易的锁，不要以为有行锁就可以为所欲为，并不是这样的。

间隙锁和临键锁

前面说了，记录锁需要加到记录上，但是如果要给此时还未存在的记录加锁怎么办？也就是要预防幻读的出现！

这时候间隙锁就派上用场了，它是给间隙加上锁。

比如此时有 1、3、5、10 这四条记录，之前的文章分析过，数据页中还有两条虚拟的记录，分别是 Infimum 和 Supremum。

可以看到，记录之前都有间隙，那间隙锁呢，锁的就是这个间隙！

比如我把 3 和 5 之间的间隙锁了，此时要插入 id = 4 的记录，就会被这个间隙锁给阻塞了，这样就避免了幻读的产生！也就实现了锁定未插入的记录的需求！

还有个 Next-Key Locks 就是记录锁+间隙锁，像上面间隙锁的举例，只能锁定(3,5) 这个区间，而 Next-Key Locks 是一个前开后闭的区间(3,5]，这样能防止查询 id=5 的这个幻读。

间隙锁之间不会冲突，间隙锁的唯一目的就是防止其他事务插入数据到间隙中 ，所以即使两个间隙锁要锁住相同的间隙也没有关系，因为它们的目的是一致的。

间隙锁可以显式禁用，它是在事务隔离级别为可重复读的时候生效的，如果将事务隔离级别更改为 READ COMMITTED，就会禁用了，此时，间隙锁对于搜索和索引扫描是禁用的，仅用于外键约束检查和重复键检查。

插入意向锁

插入意向锁，即 Insert Intention Locks，它也是一类和间隙相关锁，但是它不是锁定间隙，而是等待某个间隙。比如上面举例的 id = 4 的那个事务 C ，由于被间隙锁给阻塞了，所以事务 C 会生成一个插入意向锁，表明等待这个间隙锁的释放。

并且插入意向锁之间不会阻塞，因为它们的目的也是只等待这个间隙被释放，所以插入意向锁之间没有冲突。

它的目的不在于锁定资源防止别人访问，我个人觉得更像是为了遵循 MySQL 的锁代码实现而为之。

锁其实就是内存里面的一个结构，每个事务为某个记录或者间隙上锁就是创建一个锁对象来争抢资源。

如果某个事务没有抢到资源，那也会生成一个锁对象，只是状态是等待的，而当拥有资源的事务释放锁之后，就会寻找正在等待当前资源的锁结构，然后选一个让它获得资源并唤醒对应的事务使之得以执行。

所以按照这么个逻辑，那些在等待间隙锁的插入事务，也需要对应的建立一个锁结构，然后锁类型是插入意向锁。

这样一来，间隙锁的事务在释放间隙锁的时候，才能得以找到那些等待插入的事务，然后进行唤醒，而由锁的类型也可以得知是插入意向锁，之间不需要阻塞，所以可以一起执行插入。

Auto-Inc Lock

Auto-Inc Lock 是一个特殊的表级锁，用于自增列插入数据时使用。 在插入一条数据的时候，需要在表上加个 Auto-Inc Lock，然后为自增列分配递增的值，在语句插入结束之后，再释放 Auto-Inc Lock。

在 MySQL 5.1.22 版本之后，又弄了个互斥量来进行自增减的累加。互斥量的性能高于 Auto-Inc Lock，因为 Auto-Inc Lock是语句插入完毕之后才释放锁，而互斥量是在语句插入的时候，获得递增值之后，就可以释放锁，所以性能更好。

但是我们还需要考虑主从的情况，由于并发插入的情况，基于 statement -based binlog 复制时，自增的值顺序无法把控，可能会导致主从数据不一致。

所以 MySQL 有个 innodb_autoinc_lock_mode 配置，一共有三个值：

• 0，只用 Auto-Inc Lock。
• 1，默认值，对于插入前已知插入行数的插入，用互斥量，对于插入前不知道具体插入数的插入，用 Auto-Inc Lock，这样即使基于 statement -based binlog 复制也是安全的。
• 2，只用互斥量。

扩展学习

还有一个谓词锁，Predicate Locks，这个比较少见，大家了解下就好了。

InnoDB 是支持空间数据的，所以有空间索引，为了处理涉及空间索引的操作的锁定，next-key locking 不好使，因为多维数据中没有绝对排序的概念，因此不清楚 “下一个” key 在哪。

所以为了支持具有空间索引的表的隔离级别，InnoDB 使用谓词锁。

空间索引包含最小边界矩形（MBR）值，因此 InnodB 通过在用于查询的 MBR 值上设置谓词锁定，使得 InnoDB 在索引上执行一致性读， 其他事务无法插入或修改与查询条件匹配的行。

610.MySQL 的乐观锁和悲观锁是什么？

回答重点

悲观锁（Pessimistic Locking）：

• 假设会发生冲突，因此在操作数据之前就对数据加锁，确保其他事务无法访问该数据。常见于对数据一致性要求较高的场景。
• 实现方式：使用行级锁或表级锁，例如可以使用 SELECT ... FOR UPDATE 或 LOCK IN SHARE MODE 语句来加锁。

乐观锁（Optimistic Locking）：

• 假设不会发生冲突，因此在操作数据时不加锁，而是在更新数据时进行版本控制或校验。如果发现数据被其他事务修改，则会拒绝当前事务的修改，需重新尝试。
• 实现方式：通常通过版本号或时间戳来实现，每次更新时检查版本号或时间戳是否一致。

扩展知识

适用场景

1）乐观锁适合并发冲突少，读多写少的场景，不用通过加锁只需通过比较字段版本号（或时间戳）是否发生改变的形式，无锁操作，吞吐量较高。

数据库实现乐观锁的例子（版本号）：

1
2
3
4
5
6
7
8

-- 假设有一张用户表 users，包含 id、name 和 version 字段
-- 读取数据
SELECT id, name, version FROM users WHERE id = 1;

-- 更新数据时检查版本号
UPDATE users
SET name = 'new_name', version = version + 1
WHERE id = 1 AND version = current_version;

2）悲观锁适合并发冲突多，写多读少的场景。通过每次加锁的形式来确保数据的安全性，吞吐量较低。

数据库实现悲观锁的例子：

1
2
3
4
5

-- 读取数据并加锁
SELECT id, name FROM users WHERE id = 1 FOR UPDATE;

-- 执行更新操作
UPDATE users SET name = 'new_name' WHERE id = 1;

611.MySQL 中如果发生死锁应该如何解决？

回答重点

自动检测与回滚：

• MySQL 自带死锁检测机制（innodb_deadlock_detect），当检测到死锁时，数据库会自动回滚其中一个事务，以解除死锁。通常会回滚事务中持有最少资源的那个。
• 也有锁等待超时的参数（innodb_lock_wait_timeout），当获取锁的等待时间超过阈值时，就释放锁进行回滚。

手动 kill 发生死锁的语句：

• 可以通过命令，手动快速地找出被阻塞的事务及其线程 ID，然后手动 kill 它，及时释放资源。

扩展知识

死锁日志

如果线上遇到死锁的情况，可以通过 SHOW ENGINE INNODB STATUS 来获取死锁的日志信息，从而定位到死锁发生的原因。

我从网上找了一个死锁日志，供大家参考学习，有个大致印象即可：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

------------------------
LATEST DETECTED DEADLOCK
------------------------
170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** WE ROLL BACK TRANSACTION (1)

简单分析一下，上半部分在解释事务 1 即 (1) TRANSACTION 的情况，它执行delete from test where a = 2 在申请索引 a 的 X 锁（index a of table … lock_mode X waiting）

下半部分在解释事务 2 即 (2) TRANSACTION，它持有索引 a 的 X 锁，又因为 insert into test (id,a) values (10,2) 这个插入语句在申请 S 锁（表中的 a 建立了唯一索引，因此需要 S 锁来进行重复 key 判断）。

所以事务 1 排着队等事务 2 释放 X 锁，而事务 2 又需要 S 锁，申请 S 锁的时候发现等锁的队伍前还有个 X 锁，这样一来就形成了相互依赖。

通过日志分析我们就能知晓死锁的原因，然后通过一定的修改来避免死锁的产生或降低死锁的概率。

常见避免死锁或降低死锁的手段

1）避免大事务。大事务占据锁的时间长，将大事务拆分成多个小事务快速释放锁，可降低死锁产生的概率和避免冲突。

2）调整申请锁的顺序。在更新数据的时候要保证获得足够的锁，举个例子：先获取影响范围大的锁，比如说修改操作，先将排他锁获取到，再获取共享锁。或固定顺序访问数据，这样也能避免死锁的情况。

3）更改数据库隔离级别。可重复读比读已提交多了间隙锁和临键锁，利用读已提交替换之可降低死锁的情况。

4）合理建立索引，减少加锁范围。如果命中索引，则会锁对应的行，不然就是全表行都加锁，这样冲突大，死锁的概率就高了。

5）开启死锁检测，适当调整锁等待时长。

手动 kill 语句步骤

1）查找当前的事务和锁信息：

使用以下命令查看当前正在执行的事务和相关的锁信息：

1

SHOW ENGINE INNODB STATUS;

这个命令会输出 InnoDB 的状态，包括死锁信息和当前的活动事务。你可以在输出中找到被阻塞的事务及其线程 ID。

除此之外，也可以使用 INFORMATION_SCHEMA 中的 INNODB_LOCKS 和 INNODB_LOCK_WAITS 表，查看当前锁和锁等待情况，得到事务ID。

1
2

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

再通过 INFORMATION_SCHEMA 的 innodb_trx 可以找到事务ID和线程 ID 的对应关系。

2）手动终止该事务：

一旦确定了需要 KILL 的线程 ID，使用以下命令终止该事务：

1

KILL <thread_id>;

将 <thread_id> 替换为你找到的实际线程 ID。

INNODB_LOCKS 、 INNODB_LOCK_WAITS 和 INNODB_TRX 示例

INNODB_LOCKS 查询当前锁的信息

1

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

示例结果

字段解释：

• LOCK_ID: 唯一标识每个锁。
• LOCK_MODE: 锁的类型（X：排它锁，S：共享锁）。
• LOCK_TYPE: 锁的作用对象类型（如 RECORD 表示行锁）。
• LOCK_TABLE: 被锁定的表。
• LOCK_INDEX: 相关索引。
• LOCK_OWNER: 拥有该锁的事务 ID。
• LOCK_DATA: 被锁定的数据行的主键值。

INNODB_LOCK_WAITS 查询锁等待情况

1

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

示例结果

字段解释

• REQUESTING_TRX_ID: 请求锁的事务 ID。
• HOLDING_TRX_ID: 当前持有锁的事务 ID。
• LOCK_ID: 与 INNODB_LOCKS 表中的锁相对应。
• LOCK_TYPE: 请求的锁类型。

INNODB_TRX 查询线程 ID 示例

1
2
3

SELECT trx_state, trx_started, trx_mysql_thread_id, trx_query, trx_id 
FROM INFORMATION_SCHEMA.INNODB_TRX 
WHERE trx_id = '123456';

示例结果

字段解释：

• trx_state: 事务的当前状态。在此示例中，LOCK_WAIT 表示该事务正在等待锁。
• trx_started: 事务开始的时间。在此示例中，事务于 2024-09-25 10:00:00 开始。
• trx_mysql_thread_id: 与该事务关联的 MySQL 线程 ID，可以用来查找该事务的更多信息。
• trx_query: 当前事务正在执行的查询。在此示例中，它正在执行一个简单的 SELECT 查询。
• trx_id: 事务的唯一标识符。在此示例中，事务 ID 为 123456。

612.如何使用 MySQL 的 EXPLAIN 语句进行查询分析？

回答重点

explain 主要用来 SQL 分析，它主要的属性详解如下：

• id：查询的执行顺序的标识符，值越大优先级越高。简单查询的 id 通常为 1，复杂查询（如包含子查询或 UNION）的 id 会有多个。
• select_type（重要）：查询的类型，如 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
• table：查询的数据表。
• type（重要）：访问类型，如 ALL（全表扫描）、index（索引扫描）、range（范围扫描）等。一般来说，性能从好到差的顺序是：const > eq_ref > ref > range > index > ALL。
• possible_keys：可能用到的索引。
• key（重要）：实际用到的索引。
• key_len：用到索引的长度。
• ref：显示索引的哪一列被使用。
• rows（重要）：估计要扫描的行数，值越小越好。
• filtered：显示查询条件过滤掉的行的百分比。一个高百分比表示查询条件的选择性好。
• Extra（重要）：额外信息，如 Using index（表示使用覆盖索引）、Using where（表示使用 WHERE 条件进行过滤）、Using temporary（表示使用临时表）、Using filesort（表示需要额外的排序步骤）。

type 详解：

• system：表示查询的表只有一行（系统表）。这是一个特殊的情况，不常见。
• const：表示查询的表最多只有一行匹配结果。这通常发生在查询条件是主键或唯一索引，并且是常量比较。
• eq_ref：表示对于每个来自前一张表的行，MySQL 仅访问一次这个表。这通常发生在连接查询中使用主键或唯一索引的情况下。
• ref：MySQL 使用非唯一索引扫描来查找行。查询条件使用的索引是非唯一的（如普通索引）。
• range：表示 MySQL 会扫描表的一部分，而不是全部行。范围扫描通常出现在使用索引的范围查询中（如 BETWEEN、>, <, >=, <=）。
• index：表示 MySQL 扫描索引中的所有行，而不是表中的所有行。即使索引列的值覆盖查询，也需要扫描整个索引。
• all（性能最差）：表示 MySQL 需要扫描表中的所有行，即全表扫描。通常出现在没有索引的查询条件中。

扩展知识

实际 explain 例子

1）创建 employees 表

1
2
3
4
5
6
7
8
9

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2),
    hire_date DATE,
    INDEX (department_id)
);

我们要执行以下查询来查找部门 ID 为 5 且薪水在 50000 到 100000 之间的员工，并按薪水降序排序：

1
2
3
4

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 5 AND salary BETWEEN 50000 AND 100000
ORDER BY salary DESC;

2）我们先使用 explain 分析计划进行分析：

1
2
3
4

EXPLAIN SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 5 AND salary BETWEEN 50000 AND 100000
ORDER BY salary DESC;

输出结果如下：

3）分析执行计划

从执行计划中看出，type 为 ref，表示使用了 department_id 索引，这是个非唯一索引。key 为 department_id 这个索引，而且 rows 为 5000，表示扫描了 5000 行数据。从 Extra 看出在应用 WHERE 条件后，还需要进行文件排序来满足 ORDER BY 子句。

4）找出问题

尽管查询使用了索引，但由于索引不完全覆盖查询的条件和排序，查询需要进行额外的文件排序。这可能会导致性能瓶颈，特别是在结果集较大时。

5）优化解决它！

创建复合索引

创建一个包含 department_id 和 salary 的复合索引，这样可以覆盖查询的 WHERE 和 ORDER BY 条件：

1

CREATE INDEX idx_department_salary ON employees (department_id, salary);

复合索引可以使查询在扫描 department_id 列时，同时按 salary 列排序，避免额外的文件排序。

再次执行计划分析

优化后的 EXPLAIN 输出如下：

6）分析优化后的结果

从新的 EXPLAIN 输出中可以看出：

• type: range，表示使用范围扫描，这是个相对高效的访问类型。
• key: idx_department_salary，表示实际使用了复合索引。
• rows: 500，估计扫描的行数减少了，因为索引更精确地覆盖了查询条件。
• Extra: 仅显示 Using where，不再需要文件排序，因为索引已经覆盖了排序需求。

613.MySQL 中 count(*)、count(1) 和 count(字段名) 有什么区别？

回答重点

在 MySQL 中，count(*) 、count(1)和count(字段名) 都是用来统计行数的聚合函数，但它们有些许的区别：

功能上：

1）count(*) 会统计表中所有行的数量，包括 null 值（不会忽略任何一行数据）。由于只是计算行数，不需要对具体的列进行处理，因此性能通常较高。

2）count(1) 和 count(*) 几乎没差别，也会统计表中所有行的数量，包括 null 值。

3）count(字段名) 会统计指定字段不为 null 的行数。这种写法会对指定的字段进行计数，只会统计字段值不为 null 的行。

效率上：

1）count(1) 和 count(*) 效率一致，网上其实众说纷纭，实际上当然得看官网怎么说！

There is no performance difference. 没有差异的！

2）count(字段) 的查询就是全表扫描(如果对应的字段没有索引，如果有索引则用索引)，正常情况下它还需要判断字段是否是 null 值，因此理论上会比 count(1) 和 count(*) 慢。

但是如果字段不为 null，例如是主键，那么理论上也差不多，而且本质上它们的统计功能不一样，在需要统计 null 的时候，只能用 count(1) 和 count(*)，不需要统计 null 的时候只能用count(字段)，所以也不用太纠结性能问题。

扩展知识

在 MyISAM 中，由于只有表锁，因此它把每张表的总数单独记录维护（表锁使得对表的修改是串行，因此能维护总数），所以 count(*) 非常快，因为等于直接返回一个字段。当然，前提是不需要条件过滤而是直接返回整表数据。

而 InnoDB 由于支持行锁，所以会有很多并发来修改表的数据，因此无法维护记录总数，但是 InnoDB 对count(*)和count(1)也做了一定的优化。

count 的字段如果是主键，由于主键索引保存的是整行记录，占据的空间和内存都比较大，此时表中如果还有其它二级索引，那么成本会更低，因此 InnoDB 会评估这个成本选择合适的索引扫描。当然，这个前提也是不能有对应的条件过滤等功能。

614.MySQL 中 int(11) 的 11 表示什么？

回答重点

在 MySQL 中，int(11) 中的 11 表示显示宽度，并不影响存储的大小或数值范围。具体来说：

• 显示宽度：当使用 ZEROFILL 属性时，int(11) 表示如果数值的位数少于 11 位，则会在前面填充零。例如，数值 42 将显示为 00000000042。不使用 ZEROFILL 时，显示结果是 42（前面有九个空格）
• 存储大小：int 类型始终占用 4 字节（32 位），可以表示的范围是从 -2,147,483,648 到 2,147,483,647。

所以，int(11) 中的数字与整数的大小无关，只是显示格式的定义。这个配置主要是为了在查询结果中保持一致的显示格式，方便阅读和理解，但实际业务上基本用不到这个。

扩展知识

MySQL 8.0 显示宽度的变化

MySQL 8.0 版本中，整数类型的显示宽度（例如 INT(11) 中的 11）被正式弃用。即定义的显示宽度不再影响任何存储或比较操作，显示宽度的设置被忽略。

虽然显示宽度本身被弃用，但仍然可以使用 ZEROFILL 属性来实现零填充效果。使用 ZEROFILL 时，数字会在左侧填充零以达到特定的宽度。

如何使用 ZEROFILL

直接在数据类型后加上 ZEROFILL 属性即可。

1
2
3
4

CREATE TABLE example (
    id INT(5) ZEROFILL,
    quantity INT(3) ZEROFILL
);

615.MySQL 中 varchar 和 char 有什么区别？

回答重点

CHAR 和 VARCHAR 是两种用于存储字符串的列类型，它俩最大的不同就是一个是固定长度，一个是可变长度。

• CHAR(n)：固定长度的字符串。CHAR 列的长度是固定的，即使存储的字符串长度小于定义的长度，MySQL 也会在字符串的末尾填充空格以达到指定长度（如果 char 类型的字符串后面有空格的话，innodb 会忽略）。
• VARCHAR(n)：可变长度的字符串。VARCHAR 列的长度是可变的，存储的字符串长度与实际数据长度相等，并且在存储数据时会额外增加 1 到 2 个字节（字符长度超过 255，则使用两个字节）用于存储字符串的长度信息。

理论上来说 CHAR 会比 VARCHAR 快，因为 VARCHAR 长度不固定，处理需要多一次运算，但是实际上这种运算耗时微乎其微，而固定大小在很多场景下比较浪费空间，除非存储的字符确认是固定大小或者本身就很短，不然业务上推荐使用 VARCHAR。

下面以表格方式总结方便横向对比：

扩展知识

MySQL 执行 orderby 排序的时候，会利用 sort_buffer。

假设 a、b、c 都为 varchar 类型，当前要执行select a,b,c from t1 where a = '面试鸭' order by b;

MySQL 计算 a b c 总长度比较长，sort_buffer 可能放不下，就会使用双路排序，即 sort_buffer 里存放需要排序的字段 b 和 id 进行排序，待排完后，再通过 id 回表查询得到a、b、c 字段。这样就多了回表的一步，性能比较差。

如果 select 字段长度少，那么就可以使用单路排序，即将 select 的数据都放入到 sort_buffer 中，排完序后直接返回给客户端。

这里计算 a、b、c 长度依据的就是 varchar(n) 中的 n，所以如果 n 设置很大，虽然占用空间是动态的，但是会隐性影响排序的性能。

更多排序细节可查看面试鸭《[MySQL 是如何实现数据的排序的？](#617MySQL 中的数据排序是怎么实现的)》这个面试题。

VARCHAR 支持的最大长度

mysql column length is within the maximum length of 65,535 bytes

因为最大行长度有限，所以要计算 VARCHAR 支持最大长度，仅当一行只有一个 VARCHAR 字段时，这个 VARCHAR 字段能达到最大长度（没有别的字段来占用空间）。

最大行长度是 65535 字节，如果值允许为 null，则需要额外 1bit 标记是否为 null（mysql 最对于 null 值是额外用一个 null值列表存储的。当前只有一个 VARCHAR 字段，需要用 1 个 bit 标记它的 null 值组成 null 值列表，mysql 要求 null 值列表最少需要一个字节，所以需要占用 1 个字节），又因为 VARCHAR 列的长度是可变的，需要 1 到 2 个字节（字符长度超过 255，则使用两个字节）用于存储字符串的长度信息。

所以支持的最大长度是 65535 - 2 = 65533，如果允许为 null 则是 65532。

以上是字节数，实际的字符又取决于使用的字符集。

• UTF-8 字符集：每个字符最大占用 3 字节（但也可以是 1 字节、2 字节或 3 字节，具体取决于字符的实际编码）。因此，最大字符数会受限于字符集的编码方式。如果使用 UTF-8，最大字符数大约是 21844 字符（65533 ÷ 3）。
• UTF-16 字符集：每个字符通常占用 2 字节。最大字符数大约是 32766 字符（65533 ÷ 2）。
• Latin1 字符集：每个字符占用 1 字节，所以最大字符数为 65533 字符。

在定义 VARCHAR(n) 时，n 代表的是字符的个数，而不是字节数。

616.MySQL 中如何进行 SQL 调优？

回答重点

平时进行 SQL 调优，主要是通过观察慢 SQL，然后利用 explain 分析查询语句的执行计划，识别性能瓶颈，优化查询语句。

1）合理设计索引，利用联合索引进行覆盖索引的优化，避免回表的发生，减少一次查询和随机 I/O

2）避免 SELECT * ，只查询必要的字段

3）避免在 SQL 中进行函数计算等操作，使得无法命中索引

4）避免使用 %LIKE，导致全表扫描

5）注意联合索引需满足最左匹配原则

6）不要对无索引字段进行排序操作

7）连表查询需要注意不同字段的字符集是否一致，否则也会导致全表扫描

除此之外，还可以利用缓存来优化，一些变化少或者访问频繁的数据设置到缓存中，减轻数据库的压力，提升查询的效率。

还可以通过业务来优化，例如少展示一些不必要的字段，减少多表查询的情况，将列表查询替换成分页分批查询等等。

扩展知识

慢 SQL

这是 MySQL 自带的日志记录，默认关闭，通过 set global slow_query_log = 'ON' 即可开启。

通过 show variables like '%slow_query_log%' 即可查询当前慢日志是否开启，以及存储的路径。

通过 set global long_query_time = 3 即可设置慢 SQL 的阈值，3 就是 3 秒，当一个 SQL 执行的时间操作 3 秒，就会被记录到慢日志中。

explain

具体 explain 的分析，可以看：[如何使用 MySQL 的 EXPLAIN 语句进行查询分析？](#612如何使用 MySQL 的 EXPLAIN 语句进行查询分析)

617.MySQL 中的数据排序是怎么实现的？

回答重点

排序过程中，如果排序字段命中索引，则利用索引排序。

反之，使用文件排序。

文件排序中，如果数据量少则在内存中排序，具体是使用单路排序或者双路排序。

如果数据大则利用磁盘文件进行外部排序，一般使用归并排序。

扩展知识

根据 explain 的结果来看，MySQL 可以分为索引排序和 filesort。

索引排序

如果查询中的 ORDER BY 子句包含的字段已经在索引中，并且索引的排列顺序和 ORDER BY 子句一致，则可直接利用索引进行排序。这种方式效率最高，因为索引有序。

filesort

当使用 explain 分析 SQL 发现执行计划的 extra 中包含 using filesort 的时候，说明它无法应用索引的顺序，而主动排序了。

如果需要排序的数据比较少，则直接在内存中通过 sort_buffer 就能排了。具体是通过 sort_buffer_size 参数来控制 sort_buffer 的大小，如果需要排序的数据量小于 sort_buffer 则直接在内存中排序，反之需要利用磁盘临时文件排序，性能就比较差了。

内存排序

在 sort_buffer 中排序有一些细节需要知晓一下。

双路排序

有一个叫 max_length_for_sort_data 参数，默认是 4096 字节，如果 select 列的数据长度超过它，则 MySQL 采用row_id 排序，即把 row_id(有主键就是主键)+排序字段放置到 sort_buffer 中排序。

我们来举例理解下双路排序。

比如现在要执行：

select a,b,c from t1 where a = '面试鸭' order by b;

假设select 列的数据长度超过了 max_length_for_sort_data，为了节省排序占用的空间，此时 sort_buffer 只会有放置 id 和 b 来排序。

排序后，再通过 id 回表查询得到 a、b、c ，最终将最后的结果集返回给客户端。

所以排序需要多个回表的过程，等于需要两次查询，也叫双路排序（Two-Pass Sort）。

单路排序

假设 select 列的数据没有超过 max_length_for_sort_data，则可以进行单路排序（Single-Pass Sort），就是将 select 的字段都放置到 sort_buffer 中。

排序后直接得到结果集，返回给客户端即可，相比双路排序它减少了回表的动作，因此效率更高。

一开始 MySQL 只有双路排序，后续优化推出了单路排序。

磁盘文件临时排序

前面提到，如果查询的数据超过 sort_buffer，说明内存放不下了，因此需要利用磁盘文件进行外部排序，一般会使用归并排序，简单理解就是将数据分为很多份文件，单独对文件排序，之后再合并成一个有序的大文件。

利用磁盘排序效率会更低，针对一些情况可以调大 sort_buffer_size，避免磁盘临时文件排序。

618.如何在 MySQL 中避免单点故障？

回答重点

一般会使用主从架构来避免单点故障，主数据库处理写操作，从数据库处理读操作，主数据库故障时可以切换到从数据库。

同时会对数据进行定期备份并存储在不同的物理位置，以便在发生故障时能够快速恢复数据。

并且需要建立监控系统，实时监控数据库的健康状态，并在发生故障时及时告警。

扩展知识

MySQL 主从，主备或者主主架构介绍

主备架构

主备架构就是主机和备机。备机是不干活的，也就是不对外提供服务，只是默默地在同步主机的数据，然后等着某一天主机挂了之后，它取而代之！

至于切换的话主要有两种方式：

• 人工切换，得知主机挂了之后手动把备机切成主机，缺点就是慢。
• 利用 keepalived 或者自己写个脚本来作监控，然后自动切换。

主从架构

主从架构就是主机和从机。从机和备机的区别在于，它是对外提供服务的，一般而言主从就是读写分离，写请求指派到主机，读请求指派到从机

主主架构

主主就是两个都是主机

一般情况下都不会有主主的架构。

当同时有两个写请求达到分别打到两个主库同一张表的时候，则会同时创建一条记录，这条记录的 ID 是一样的，这样数据同步之后其中有一条就会被覆盖了，这会出问题的。

619.如何在 MySQL 中实现读写分离？

回答重点

做法一：代码封装

讲白了就是代码层面抽出一个中间层，由中间层来实现读写分离和数据库连接。

利用个代理类，对外暴露正常的读写接口，里面封装了逻辑，将读操作指向从库的数据源，写操作指向主库的数据源。

• 优点：简单，并且可以根据业务定制化变化，随心所欲。
• 缺点：如果数据库宕机了，发生主从切换了之后，就得修改配置重启。如果系统是多语言的话，需要为每个语言都实现一个中间层代码，重复开发。

做法二：使用中间件

中间件一般而言是独立部署的系统，客户端与这个中间件的交互是通过 SQL 协议的。

所以在客户端看来连接的就是一个数据库，通过 SQL 协议交互也可以屏蔽多语言的差异。

缺点就是整体架构多了一个系统需要维护，并且可能成为性能瓶颈，毕竟交互都需要经过它中转。

常见的开源数据库中间件有：官方的 MySQL-Proxy、360 的 Atlas、ShardingSphere、Mycat 等。

扩展知识

什么是读写分离

1）读写分离就是读操作和写操作从以前的一台服务器上剥离开来，将主库压力分担一些到从库。本质上是因为访问量太大，主库的压力过大，单机数据库无法支撑并发读写。然后一般而言读的次数远高于写，因此将读操作分发到从库上，这就是常见的读写分离。

2）读写分离还有个操作就是主库不建查询的索引，从库建查询的索引。因为索引是需要维护的，比如你插入一条数据，不仅要在聚簇索引上面插入，对应的二级索引也得插入，修改也是一样的。所以将读操作分到从库了之后，可以在主库把查询要用的索引删了，减少写操作对主库的影响。

620.什么是 MySQL 的主从同步机制？它是如何实现的？

回答重点

MySQL 的主从同步机制是一种数据复制技术，用于将主数据库（Master）上的数据同步到一个或多个从数据库（Slave）中。

主要是通过二进制日志（Binary Log，简称 binlog）实现数据的复制。主数据库在执行写操作时，会将这些操作记录到 binlog 中，然后推送给从数据库，从数据库重放对应的日志即可完成复制。

扩展知识

MySQL 主从复制类型

MySQL 支持异步复制、同步复制、半同步复制。

• 异步复制：主库不需要等待从库的响应（性能较高，数据一致性低）。
• 同步复制：主库同步等待所有从库确认收到数据（性能差，数据一致性高）。
• 半同步复制：主库等待至少一个从库确认收到数据（性能折中，数据一致性较高）。

异步复制

MySQL 默认是异步复制，具体流程如下：

主库：

• 接受到提交事务请求
• 更新数据
• 将数据写到binlog中
• 给客户端响应
• 主库推送 binlog 变更事件到从库中
• 从库接收到事件去主库拉取数据

从库：

• 由 I/O 线程将同步过来的 binlog 写入到 relay log 中。
• 由 SQL 线程从 relay log 重放事件，更新数据
• 给主库返回响应。

用一句话概括一下：主库提交事务会写 binlog，会由一个 dump 线程监听 binlog 文件的变更，如果 binlog 有更新则推送更新事件给从库，从库接收到事件后拉取数据，会有一个 I/O 线程将其写到 relay log 中，慢慢消化，由 SQL 线程来重放更新数据。

异步复制有数据丢失风险，例如数据还未同步到从库，主库就给客户端响应，然后主库挂了，此时从库晋升为主库的话数据是缺失的。

同步复制

主库需要将 binlog 复制到所有从库，等所有从库响应了之后才会给客户端响应，这样的话性能很差，一般不会选择同步复制。

半同步复制

MySQL 5.7 之后搞了个半同步复制，有个参数可以选择“成功同步几个从库就返回响应。”

比如一共有 3 个从库，我参数配置 1，那么只要有一个从库响应说复制成功了，主库就直接返回响应给客户端，不会等待其他两个从库。

这样的话性能就比较好，并且数据可靠性也增强了，只有当那个从库和主库同时都挂了，才会缺失数据。

并行复制

以前从库是通过一个 SQL 线程按照顺序逐条执行主库的 binlog 日志指令（即从 relay log 重放事件）。对于主库的高并发写入操作，这种串行执行的方式会导致从库的复制速度跟不上主库，从而产生主从延迟问题。

所以 MySQL 引入了并行复制，说白了就是通过 多个 SQL 线程 来并发执行重放事件。MySQL 提供了以下几种并行复制模式。

MySQL 5.6 基于库级别的并行复制

假设主库有多个数据库（如 db1 和 db2），在从库上，db1 的事务和 db2 的事务可以同时执行。即将不同数据库上的事务分配到不同的 SQL 线程中执行。如果主库大部分事务集中在一个数据库上，这个就没啥用了。

MySQL 5.7 基于组提交（Group Commit）事务的并行复制

进一步细化了并行的颗粒度，从库级别细化到组提交级别。即 MySQL 会将组提交的事务视为彼此独立的事务，可以在从库并行重放。

如果主库开启了组提交，且事务之间没有冲突。那么这些事务都可以由多线程并行执行。

binlog 中如果两个事务的 last_committed 相同，说明这两个事务是在同一个 Group 内提交的。但是粒度还是不够，如果大部分事务都集中在一张表上？

那么只有相同 last_committed 可以并发，即使有些数据的更改和当前事务是不冲突的，也无法并发。

MySQL 5.7 基于逻辑时钟（LOGICAL_CLOCK）的并行复制

LOGICAL_CLOCK 是基于 Group Commit 的并行复制，引入了时间标记的概念。

基于所有在主库同时处于 prepare 阶段且未提交的事务不会存在锁冲突的情况（就是这些事务在从库执行时都可以并行执行），将这些事务都打上一个时间标记（实际实现用的是上一个提交事务的 sequence_number，即上面提到的 binlog 中的 last_committed）。

这样从库在识别到这些事务时，可以并行，进一步的提高并发度。

所以提升主库的组提交事务数可以让从库复制的并行度更高，所以 MySQL 5.7 引入了两个参数：

• binlog_group_commit_sync_delay：组提交等待延迟提交的时间。即每次 binlog 组提交时等待一段时间再 fsync。让进入 group 的事务更多，提高并行度。
• binlog_group_commit_sync_no_delay_count：组提交时允许的最大事务数量，达到该数量时立即触发组提交，而无需等待延迟时间（binlog_group_commit_sync_delay）。即达到期望的并行度后立即提交，缩小等待时长。

MySQL 8.0 基于 WriteSet 的并行复制

在 MySQL 5.7 中，为了提升从库的事务回放速度，需要在主库提高事务的并行度。主库上的事务越多线程并行提交，备库就能在更大程度上实现并行回放。然而，这种方式依赖于主库的并行提交情况，当主库事务是串行提交时，备库的回放效率会显著下降。

所以 MySQL 8.0 引入了 基于 WriteSet 的并行复制，即使主库上的事务是串行提交的，只要事务之间没有冲突，备库也可以并行回放这些事务，提升复制效率。

WriteSet 是事务更新行的集合，通过哈希算法对主键或唯一索引生成标识，记录在二进制日志中。即通过 WriteSet 判定事务之间的冲突，如果两个事务的 WriteSet 没有冲突，则它们可以并行回放。

621.如何处理 MySQL 的主从同步延迟？

回答重点

首先需要明确一个点延迟是必然存在的，无论怎么优化都无法避免延迟的存在，只能减少延迟的时间。

常见解决方式有以下几种：

• 二次查询。如果从库查不到数据，则再去主库查一遍，由 API 封装这个逻辑即可，算是一个兜底策略，比较简单。不过等于读的压力又转移到主库身上了，如果有不法分子故意查询必定查不到的查询，这就对主库产生冲击了。
• 强制将写之后立马读的操作转移到主库上。这种属于代码写死了，比如一些写入之后立马查询的操作，就绑定在一起，写死都走主库。不推荐，比较死板。
• 关键业务读写都走主库，非关键还是读写分离。比如上面我举例的用户注册这种，可以读写主库，这样就不会有登陆报该用户不存在的问题，这种访问量频次应该也不会很多，所以看业务适当调整此类接口。
• 使用缓存，主库写入后同步到缓存中，这样查询时可以先查询缓存，避免了延迟的问题，不过又引入了缓存数据一致性的问题。

除此之外，也可以提一提配置问题，例如主库的配置高，从库的配置太低了，可以提升从库的配置等。如果面试官对 MySQL 比较熟，可能会追问一些偏 DBA 侧的问题，例如并行复制等。

• [MySQL 并行复制](#620什么是 MySQL 的主从同步机制它是如何实现的)

扩展知识

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

再强调一下主从延迟是必然存在的，无论怎么优化都无法避免延迟的存在，只能减少延迟的时间。例如即使启用多线程复制就一定可以避免延迟吗？所以业务上的解决方案还是以回答重点内的几个解决方式为准。

622.什么是分库分表？分库分表有哪些类型（或策略）？

回答重点

分库分表是数据库性能优化的一种方法，通过将数据分散存储在多个数据库或表中，来提高系统的可扩展性、性能和可用性。

分库分表的类型（或策略） 包括：

1）水平分表：

• 将同一张表的数据按行划分，分散到多个表中。例如，可以按用户 ID 的范围将数据分为多个表（如 user_1、user_2）。

2）垂直分表：

• 将一张表的不同列拆分到多个表中，以减少每张表的字段数量和提高查询效率。例如，用户表可以分为基本信息表和详细信息表。

3）水平分库：

• 将相同的表结构复制一份到另一个库中，每个库的表结构是一样的，可以减少单一数据库的读写压力，在大数量的情况下提高读写性能。例如，database1、 database2。

4）垂直分库：

• 将数据分散到不同的数据库实例中。可以根据业务功能或模块进行分库，如将用户数据、订单数据分别存储在不同的数据库中。

扩展知识

为什么需要分库分表

随着用户量的激增和时间的堆砌，存在数据库里面的数据越来越多，此时的数据库就会产生瓶颈，出现资源报警、查询慢等场景。

首先单机数据库所能承载的连接数、I/O及网络的吞吐等都是有限的，所以当并发量上来了之后，数据库就渐渐顶不住了。

再则，如果单表的数据量过大，查询的性能也会下降。因为数据越多 B+ 树就越高，树越高则查询 I/O 的次数就越多，那么性能也就越差。

因为上述的原因，不得已就得上分库分表了。

把以前存在一个数据库实例里的数据拆分成多个数据库实例，部署在不同的服务器中，这是分库。

把以前存在一张表里面的数据拆分成多张表，这是分表。

一般而言：

• 分表：是为了解决由于单张表数据量多大，而导致查询慢的问题。大致三、四千万行数据就得拆分，不过具体还是得看每一行的数据量大小，有些字段都很小的可能支持更多行数，有些字段大的可能一千万就顶不住了。
• 分库：是为了解决服务器资源受单机限制，顶不住高并发访问的问题，把请求分配到多台服务器上，降低服务器压力。

分库分表的优点

• 提高性能：分库后，通过减少单个数据库的负载，提高读写性能。
• 可扩展性：可以通过增加新的数据库或表来扩展系统。
• 容错性：某个库或表的故障不会影响整个系统。

分库分表的缺点

• 复杂性：数据的查询、维护和事务管理变得复杂，增加了开发和运维的成本。
• 事务处理：跨库或跨表的事务处理复杂，需要额外的处理机制。
• 数据一致性：需要额外机制来保证数据的一致性和完整性。

分库图示

一般分库都是按照业务划分的，比如订单库、用户库等等。

有时候会针对一些特殊的库再作切分，比如一些活动相关的库都做了拆分。

因为做活动的时候并发可能会比较高，怕影响现有的核心业务，所以即使有关联，也会单独做拆分。

分表图示

分表其实有两种：

• 垂直分表
• 水平分表

垂直分表，来看个图，很直观：

垂直分表就是把一些不常用的大字段剥离出去。

像上面的例子：用户名是很常见的搜索结果，性别和年龄占用的空间又不大，而地址和个人简介占用的空间相对而言就较大，我们都知道一个数据页的空间是有限的，把一些无用的数据拆分出去，一页就能存放更多行的数据。

内存存放更多有用的数据，就减少了磁盘的访问次数，性能就得到提升。

水平分表，则是因为一张表内的数据太多了，我们知道数据越多 B+ 树就越高，访问的性能就差，所以进行水平拆分。

其实不管这些，浅显的理解下，在一百个数据里面找一个数据快，还是在一万个数据里面找一个数据快？

即使有索引，那厚的书目录多，翻目录也慢~

623.如果组长要求你主导项目中的分库分表，大致的实施流程是？

回答重点

1）先分析业务需求：

• 确定数据量及增长趋势，评估分库分表的必要性。（需要一定的预判但是不要过度设计）

2）设计分库分表方案：

• 选择适合的分库和分表策略（水平、垂直、哈希、范围等），并规划分库分表的结构。

3）实现数据路由：

• 根据分库分表策略设计数据路由机制，一般通过应用层代码或数据库中间件来实现，将请求路由到相应的数据库或表。

4）数据迁移：

• 将现有数据迁移到新的分库分表结构中，可以通过批量导入、ETL 工具或脚本来完成。

扩展知识

一些注意点

考虑事务处理：

• 实施分库分表后，事务管理变得复杂，可能需要使用分布式事务协议来保证数据一致性。

数据分片的均衡性：

• 确保分片策略能够均匀分布数据，以防止单一节点负载过高。可定期审查分布情况并调整策略。

文档与规范：

• 制定详细的文档和规范，确保团队成员了解分库分表的结构、数据路由逻辑和操作方式，以减少维护难度。

选择合适的分库分表中间件

使用数据库中间件（如 Sharding-JDBC、MyCat、Cobar）可以简化分库分表的实现，提供数据路由、负载均衡等功能。

Sharding-JDBC 简单介绍

Sharding-JDBC 是一个开源的 Java 库，通过在应用层实现数据库分片（分库分表），使得开发者无需修改现有的数据库或代码。它提供了一个透明的分库分表解决方案。

特点：

• 简单易用：开发者只需在配置文件中指定分片规则，无需进行复杂的中间件配置。
• 支持多种数据库：兼容 MySQL、PostgreSQL、Oracle 等多种数据库。
• 分片策略灵活：支持范围分片、哈希分片、复合分片等多种分片策略。
• 事务支持：支持分布式事务，可以确保数据的一致性。
• 支持读写分离：通过配置可以实现读写分离，提升系统性能。

使用场景： 适用于需要快速实现分库分表且对现有系统影响较小的场景。

MyCat 简单介绍

MyCat 是一款基于 Java 的开源数据库中间件，专注于数据的分库分表和读写分离。它可以作为数据库的代理服务器，处理数据请求并实现路由。

特点：

• 高可用性：支持负载均衡和高可用性，能够处理大量并发请求。
• 强大的 SQL 解析能力：支持复杂的 SQL 语句解析和优化，能够处理跨库查询。
• 动态配置：可以通过管理界面动态调整分片规则、路由规则等配置。
• 监控与管理：提供监控工具，可以实时查看数据库的性能和状态。

使用场景： 适合大规模分库分表场景，尤其是需要跨库查询和复杂 SQL 处理的场景。

Cobar 简单介绍

Cobar 是一款开源的数据库中间件，主要用于解决大规模数据的分库分表问题，最初由阿里巴巴开发。虽然其发展较慢，但仍被一些项目使用。

特点：

• 灵活的分片策略：支持多种分片策略，可以根据业务需求灵活配置。
• 透明的路由能力：能够在应用层对请求进行路由，支持读写分离。
• 简化的操作：隐藏了复杂的数据库操作，提供简单的接口供开发者使用。

使用场景： 适用于需要基础分库分表功能的项目。

哈希分表（Hash Sharding）

哈希分表是通过对某个字段（通常是主键或ID）进行哈希运算，将数据均匀分配到多个表中。

实现方式：

• 使用哈希函数计算一个字段的哈希值，然后将其对表的数量取模，决定数据存储在哪个表中。
• 例如，用户ID经过哈希运算后可能被存储在表1或表2中。

优点：

• 可以有效避免数据倾斜，保证数据在各个表中的均匀分布。
• 简化了数据分布的复杂性。

缺点：

• 由于哈希分表的方式，无法进行范围查询。
• 迁移数据时可能需要重新哈希，导致复杂性增加。

范围分表（Range Sharding）

范围分表是按照某个字段的范围将数据划分到不同的表中，通常用于时间戳、ID 等连续性字段。

实现方式：

• 根据某个字段的值，将数据划分为不同的范围。例如，按日期分表，2023年1月的数据放在表1，2月的数据放在表2。

优点：

• 易于理解和实现，适合对时间敏感的数据。
• 方便进行范围查询，性能较好。

缺点：

• 数据分布可能不均匀，某些表可能存储大量数据，而其他表则相对较少。
• 可能需要定期调整范围分配，增加维护成本。

624.对数据库进行分库分表可能会引发哪些问题？

回答重点

1）首先是事务的问题。

我们使用关系型数据库，有很大一点在于它保证事务的完整性。

而分库之后单机事务就用不上了，必须使用分布式事务来解决，而分布式事务相对而言就比较重了，而且大部分的分布式事务只能保证最终一致性，所以业务上会存在数据不一致的场景。

2）连表 JOIN 问题

在一个库中的时候我们还可以利用 JOIN 来连表查询，而跨库了之后就无法使用 JOIN 了。

此时的解决方案就是在业务代码中进行关联，也就是先把一个表的数据查出来，然后通过得到的结果再去查另一张表，然后利用代码来关联得到最终的结果。

这种方式实现起来稍微比较复杂，不过也是可以接受的。

还有可以适当的冗余一些字段。比如以前的表就存储一个关联 ID，但是业务时常要求返回对应的 Name 或者其他字段。这时候就可以把这些字段冗余到当前表中，来去除需要关联的操作。

或者通过宽表的形式查询，比如将数据全量存储至 ES 中，利用 ES 来查询数据。

3）全局 ID 唯一性问题

以前单库单表直接使用数据库的自增 ID 即可，但是分库分表之后，使用自增 ID 会导致重复主键的情况，此时需要利用雪花算法或者其他全局唯一 ID 发号器来生成唯一主键。

4）排序问题

单表直接通过 order by 进行排序即可，分库分表后直接利用数据库是无法实现排序的。要么利用分库分表中间件的能力进行汇总排序，要么自己在业务代码中排序，要么利用 ES 存储全量数据排序查询。

5）count 问题

其实和排序问题类似，单表可以直接 count，分库分表后无法支持，只能多表 count 然后业务代码中累加，或者单独搞一个地方来维护总数，要么还是利用 ES。

625.从 MySQL 获取数据，是从磁盘读取的吗？（buffer pool）

回答重点

在 MySQL 中，获取数据并不总是直接从磁盘读取。MySQL 使用缓存机制，比如 InnoDB 存储引擎，会将常用的数据和索引缓存在内存中，以提高读取性能。当查询数据时，系统首先会检查缓存（如缓冲池），如果数据存在于内存中，则直接从内存中读取；如果不在，则会从磁盘读取并加载到缓存中。

扩展知识

MySQL 中的缓存

MySQL 从缓存中读取所指的缓存，实际上包含了两个缓存：

1）查询缓存（MySQL 8.0 已废除）：在 MySQL 8.0 之前，MySQL 提供了查询缓存功能，用于缓存查询结果。如果相同的查询（同一个查询 SQL）再次执行，并且表没有发生任何变化（这个条件比较苛刻，所以后续废除了），则 MySQL 可以直接从查询缓存中返回结果，而无需重新执行查询。具体的实现类似用一个 map 存储了之前的结果，key 是 SQL，value 为结果，SQL 执行的时候，先去这个 map 看看通过 key 是否能找到值，如果找到则直接返回。

2）InnoDB 缓冲池（buffer pool）：这是 InnoDB 存储引擎的核心缓存组件。缓冲池缓存了数据页、索引页和其他相关信息。查询数据时，MySQL 首先在缓冲池中查找，如果找到则直接返回数据，否则从磁盘读取数据页并将其缓存到缓冲池中。

查询缓存和 buffer pool 大致的结构关系如下：

数据页和索引页

InnoDB 存储引擎将表数据和索引以页为单位存储，每页通常为 16KB。当需要读取某条记录时，MySQL 会加载包含该记录的整个数据页到缓冲池中，从而减少频繁的磁盘 I/O 操作。

所以要记住，MySQL 是以页为单位加载数据的，而不是记录行为单位。

从磁盘读取数据

当数据不在内存缓存中时，MySQL 需要从磁盘读取数据。注意是以页为单位从磁盘获取数据，这里还有个额外的知识点，因为以页为单位，使得顺序遍历数据的速度更快，因为后面的数据已经被加载到缓存中了！

这也符合空间局部性。

buffer pool 知识

其实 buffer pool 就是内存中的一块缓冲池，用来缓存表和索引的数据。

我们都知道 mysql 的数据最终是存储在磁盘上的，但是如果读存数据都直接跟磁盘打交道的话，这速度就有点慢了。

所以 innodb 自己维护了一个 buffer pool，在读取数据的时候，会把数据加载到缓冲池中，这样下次再获取就不需要从磁盘读了，直接访问内存中的 buffer pool 即可。

包括修改也是一样，直接修改内存中的数据，然后到一定时机才会将这些脏数据刷到磁盘上。

看到这肯定有小伙伴有疑惑：直接就在内存中修改数据，假设服务器突然宕机了，这个修改不就丢了？

别怕，有个 redolog 的存在，它会持久化这些修改，恢复时可以读取 redolog 来还原数据，这个我们后面的面试题再详盘，今天的主角是 buffer pool 哈。

回到 buffer pool，其实缓冲池维护的是页数据，也就是说，即使你只想从磁盘中获取一条数据，但是 innodb 也会加载一页的数据到缓冲池中，一页默认是 16k。

当然，缓冲池的大小是有限的。按照 mysql 官网所说，在专用服务器上，通常会分配给缓冲池高达 80% 的物理内存，不管分配多少，反正内存大小正常来说肯定不会比磁盘大。

也就是说内存放不下全部的数据库数据，那说明缓冲池需要有淘汰机制，淘汰那些不常被访问的数据页。

按照这个需求，我们很容易想到 LRU 机制，最近最少使用的页面将被淘汰，即维护一个链表，被访问的页面移动到头部，新加的页面也加到头部，同时根据内存使用情况淘汰尾部的页面。

通过这样一个机制来维持内存且尽量让最近访问的数据留在内存中。

看起来这个想法不错，但 innodb 的实现并不是朴素的 LRU，而是一种变型的 LRU。

从图中我们可以看出 buffer pool 分为了老年代（old sublist）和新生代（new sublist）。

老年代默认占 3/8，当然，可以通过 innodb_old_blocks_pct 参数来调整比例。

当有新页面加入 buffer pool 时，插入的位置是老年代的头部，同时新页面在 1s 内再次被访问的话，不会移到新生代，等 1s 后，如果该页面再次被访问才会被移动到新生代。

这和我们正常了解的 LRU 不太一样，正常了解的 LRU 实现是新页面插入到头部，且老页面只要被访问到就会被移动到头部，这样保证最近访问的数据都留存在头部，淘汰的只会是尾部的数据。

那为什么要实现这样改造的 LRU 呢？

innodb 有预读机制，简单理解就是读取连续的多个页面后，innodb 认为后面的数据也会被读取，于是异步将这些数据载入 buffer pool 中，但是这只是一个预判，也就是说预读的页面不一定会被访问。所以如果直接将新页面都加到新生代，可能会污染热点数据，但是如果新页面是加到老年代头部，就没有这个问题。

同时大量数据的访问，例如不带 where 条件的 select 或者 mysqldump 的操作等，都会导致同等数量的数据页被淘汰，如果简单加到新生代的话，可能会一次性把大量热点数据淘汰了，所以新页面加到老年代头部就没这个问题。

那 1s 机制是为了什么呢？

这个机制其实也是为了处理大量数据访问的情况，因为基本上大数据扫描之后，可能立马又再次访问，正常这时候需要把页移到新生代了，但等这波操作结束了，后面还有可能再也没有请求访问这些页面了，但因为这波扫描把热点数据都淘汰了，这就不太美丽了。

于是乎搞了个时间窗口，新页面在 1s 内的访问，并不会将其移到新生代，这样就不会淘汰热点数据了，然后 1s 后如果这个页面再次被访问，才会被移到新生代，这次访问大概率已经是别的业务请求，也说明这个数据确实可能是热点数据。

经过这两个改造， innodb 就解决了预读失效和一次性大批量数据访问的问题。

至此，对 buffer pool 的了解就差不多了。

626.MySQL 的 Change Buffer 是什么？它有什么作用？

回答重点

Change Buffer 是 MySQL InnoDB 存储引擎中的一个机制，用于暂存对二级索引的插入和更新操作的变更，而不立即执行这些操作，随后，当 InnoDB 进行合适的条件时（如页被读取或 Flush 操作），会将这些变更写入到二级索引中。

作用：

• 提高写入性能：通过将对二级索引的变更暂存，可以减少对磁盘的频繁写入，提升插入和更新操作的性能。
• 批量处理：Change Buffer 可以在后续的操作中批量处理这些变更，减少了随机写入的开销。

扩展知识

进一步理解 Change Buffer

我们来看一下官网的一张图：

从上面的图我们可以看到， buffer pool 里面其实有一块内存是留给 change buffer 用的。

1）那 change buffer 具体是个什么东西呢？

如果当前表针对 name 有一个二级索引。假设我们执行一条 update table set name = 'yes' where id = 1（这条语句需要修改 name 这个二级索引中的数据），此时 buffer pool 并没有对应二级索引的索引页数据。

这个时候需要把索引页加载才内存中立即执行修改吗？

不是的，这时候 change buffer 就上场了。

如果当前二级索引页不在 buffer pool 中，那么 innodb 会把更新操作缓存到 change buffer 中，当下次访问到这条数据后，会把索引页加载到 buffer pool 中，并且应用上 change buffer 里面的变更，这样就保证了数据的一致性。

上述 SQL 中，change buffer 中会存储 name 字段的旧索引值删除操作和新索引值插入操作。

2）所以 change buffer 有什么好处？

当二级索引页不在 buffer pool 中时，change buffer 可以避免立即从磁盘读取对应索引页导致的昂贵的随机I/O ，对应的更改可以在后面当二级索引页读入 buffer pool 时候被批量应用。

看到我加粗的字体没，二级索引页，没错 change buffer 只能用于二级索引的更改，不适用于主键索引，空间索引以及全文索引。

还有，唯一索引也不行，因为唯一索引需要读取数据然后检查数据的一致性。

3）更改先缓存在 change buffer 中，假如数据库挂了，更改不是丢了吗？

别怕，change buffer 也是要落盘存储的，从上图我们看到 change buffer 会落盘到系统表空间里面，然后 redo log 也会记录 chang buffer 的修改来保证数据一致性。

至此，想必你对 change buffer 已经有一定了解了吧。它主要用来避免于二级索引页修改产生的随机I/O。如果你的内存够大能装下所有数据，或者二级索引很少，或者你的磁盘是固态的对随机访问影响不大，其实可以关闭 change buffer，因为它也增加了复杂度，当然最终还是得看压测结果。

Change Buffer 的大小

• Change Buffer 的大小可以通过系统变量 innodb_change_buffer_max_size 进行配置，默认值为 25% 的 InnoDB 缓冲池大小，最大值可以设置为 50%。合理配置可以提升性能，但过大可能导致内存不足。

627.MySQL 的 Doublewrite Buffer 是什么？它有什么作用？

回答重点

MySQL 的 Doublewrite Buffer 是 InnoDB 存储引擎中的一个机制，用于确保数据的安全性和一致性。其作用是将数据首先写入一个内存缓冲区（双写缓冲区），然后再将其写入数据文件。这种方式可以防止在写入过程中因崩溃或故障导致数据损坏，确保数据的一致性和完整性。

工作原理简述：

• 写入流程：当事务提交时，InnoDB 首先将数据写入 Doublewrite Buffer，再从该缓冲区将数据写入磁盘的实际数据文件。
• 恢复机制：在崩溃恢复时，InnoDB 会使用 Doublewrite Buffer 中的数据来修复损坏的页，保证数据不丢失。

扩展知识

进一步理解 Doublewrite Buffer

我们都知道 innodb 默认一页是 16K，而操作系统 Linux 内存页是 4K，那么一个 innodb 页对应 4 个系统页。

所以 innodb 的一页数据要刷盘等于需要写四个系统页，假设 innodb一页数据落盘的时候，只写了一个系统页就断电了，那 innodb 一页数据就坏了，这就完了，不好恢复不了。

即产生了部分页面写问题，因为写 innodb 的一页无法保证原子性，所以引入了 Doublewrite Buffer。

其实就是当 innodb 要将数据落盘的时候，先将页数据拷贝到 Doublewrite Buffer 中，然后 Doublewrite Buffer 再刷盘到 Doublewrite Buffer Files，这时候等于数据已经落盘备份了。

然后再将数据页刷盘到本该到文件上。

从这个步骤我们得知，数据是写了两次磁盘，所以这东西叫 double write。

之所以这样操作就是先找个地方暂存这次刷盘的完整数据，如果出现断电这种情况导致的部分页面写而损坏原先的完整页，可以从 Doublewrite Buffer Files 恢复数据。

但虽然是两次写，性能的话也不会低太多，因此数据拷贝到 Doublewrite Buffer 是内存拷贝操作，然后写到 Doublewrite Buffer Files 也是批量写，且是顺序写盘，所以对整体而已，性能损失不会太多。

有了这个 buffer，在崩溃恢复的时候，如果发现页损坏，就可以从 Doublewrite Buffer Files 里面找到页副本，然后恢复即可。

Doublewrite Buffer 工作原理简述：

1）写入 Doublewrite Buffer

当 InnoDB 需要将脏页（dirty page，即已被修改但尚未写入磁盘的页）写入磁盘时，首先将这些数据页写入到 Doublewrite Buffer 中。

2）写入实际数据文件：

将数据页写入 Doublewrite Buffer 和落盘后，InnoDB 将这些数据页从 Doublewrite Buffer 写入到实际的数据文件中（如 .ibd 文件）。

3）如果发生部分写的情况：

数据页写入一半断电了，在崩溃恢复的时候，InnoDB 会检查 Doublewrite Buffer 中的数据页。如果在系统崩溃前数据页已经成功写入 Doublewrite Buffer，那么这些数据页是完整和一致的。

从 Doublewrite Buffer 中将完整的页重新写入实际数据文件，修复受影响的页。

Doublewrite Buffer 通常在系统表空间文件（ibdata1）中，分为两个 1MB 的区域，共 2MB，每个区域可存储 64 个 16KB 的页。

628.MySQL 中的 Log Buffer 是什么？它有什么作用？

回答重点

MySQL 中的 Log Buffer 是一个内存区域，用于暂时存储事务日志（redo log）的数据。在 InnoDB 存储引擎中，它的主要作用是提高性能，通过批量写入操作将日志数据从内存中写入磁盘，减少磁盘 I/O 操作的频率。

扩展知识

进一步理解 Log Buffer

我们来看一下官网的一张图：

我们看看 Log Buffer。从上面的图我们可以得知，它是 redo log 做缓冲用的。

redo log 我们都知道是重做日志，用来保证崩溃恢复数据的正确性，innodb 写数据时是先写日志，再写磁盘数据，即 WAL (Write-Ahead Logging)，把数据的随机写入转换成日志的顺序写。

但，即使是顺序写 log ，每次都调用 write 或者 fsync 也是有开销的，毕竟也是系统调用，涉及上下文切换。

于是乎，搞了个 Log Buffer 来缓存 redo log 的写入。

即写 redo log 先写到 Log Buffer 中，等一定时机再写到 redo log 文件里。

我们每次事务可能涉及多个更改，这样就会产生多条 redo log，这时会都先写到 Log Buffer 中，等事务提交的时候，一起将这些 redo log 写到文件里。

或者当 Log Buffer 超过总量的一半（默认总量是 16mb），也会将数据刷到 redo log 文件中。

也有个后台线程，每隔 1s 就会将 Log Buffer 刷到 redo log 文件中。

从上面这些我们得知，Log Buffer 其实就是一个写优化操作，把多次 write 优化成一次 write，一次处理多数据，减少系统调用。

看到这肯定有小伙伴说，数据先写 Log Buffer 而不刷盘，这数据不会丢吗？

innodb 其实给了个配置，即 innodb_flush_log_at_trx_commit 来控制 redo log 写盘时机。

• 当值为 0，提交事务不会刷盘到 redo log，需要等每隔一秒的后台线程，将 log buffer 写到操作系统的 cache，并调用 fsync落盘，性能最好，但是可能会丢 1s 数据。
• 当值为 1，提交事务会将 log buffer 写到操作系统的 cache，并调用 fsync 落盘，保证数据正确，性能最差，这也是默认配置。
• 当值为 2，提交事务会将 log buffer 写到操作系统的 cache，但不调用 fsync，而是等每隔 1s 调用 fsync 落盘，性能折中，如果数据库挂了没事，如果服务器宕机了，会丢 1s 数据。

具体如何配置看你的业务了。至此，想必你应该了解 Log Buffer 是干啥用了的吧。

629.为什么在 MySQL 中不推荐使用多表 JOIN？

回答重点

性能问题：

• 多表 JOIN 可能导致查询性能下降，尤其是在处理大数据集时，JOIN 操作的计算复杂度会显著增加，需要进行大量的数据扫描和匹配，增加了内存和CPU的消耗，导致响应时间变长。

可读性和维护性：

• 多表 JOIN 的查询语句较为复杂，降低了 SQL 的可读性和可维护性。复杂的语句可能会增加错误发生的概率，使得后续的调试和优化更加困难。

扩展知识

多表 JOIN

这里的多表往往指的是超过三个表才是多表，正常两个表 join 是没问题的！（但是也需要评估下量级和是否命中索引）

阿里的 Java 规范手册里也有一句话：“超过三个表禁止使用 Join”。

这是为什么呢？

其实数据量小都无所谓。但当数据量大的时候，影响就被放大了。如果让数据库来承担这个复杂的关联操作，需要对联接的每个表进行扫描、匹配和组合，消耗大量的 CPU 和内存资源。让复杂的关联操作占用了大量的数据库资源，会影响其他查询修改操作。

数据库往往是我们系统的弱点，很多情况下性能瓶颈都在数据库，因此我们需要尽量避免把压力放在数据库上。

所以不推荐多表 Join。

Join 时候的注意点

Join 的时候，需要关注被驱动表的查询是否能命中索引，不然就会导致全表扫描。

并且尽量让小表做驱动表，因为驱动表需要全表扫描，而被驱动表是通过索引查询的。

我们简单看下面的例子来理解一下：

例如执行 select * from mianshiya1 straight_join mianshiya2 on (mianshiya1.a=mianshiya2.a);

straight_join 是指定的链接，让 mianshiya1 作为驱动表，mianshiya2 作为被驱动表

这条语句就需要全表扫描 mianshiya1 ，然后将其中的 a 字段作为查询条件去 mianshiya2 进行搜索，所以我们要求 mianshiya2 的 a 字段有索引，这样查询的比较快。

那为什么要让小表作为驱动表呢？

假设驱动表 mianshiya1 的数据量为 A，被驱动表为 B，全表扫描驱动表的行数为 A ，每次扫描被驱动表 mianshiya2 命中二级索引，是搜索树，理论上查询一次的时间复杂度是 log2B ，可能还需要回表查询主键索引，也是搜索树，那么需要乘 2，所以是 2log2B。

一共要查询 A 次，因此整体的执行复杂度是 A+2log2B*A。

很显然，A 的值越大，时间复杂度越高，所以我们得让 A 小，所以选择小表驱动！

630.MySQL 中如何解决深度分页的问题？

回答重点

优化方式可以有三种：

1）子查询

比如 select * from mianshiya where name = ’yupi‘ limit 99999990，10； 这样的一条查询语句，可以优化成：

1
2
3
4
5

 select * from mianshiya where 
 name = 'yupi' 
 and id >= 
 (select id from mianshiya where name = 'yupi' order by id limit 99999990，1)
 order by id limit 10;

name 有索引的情况下，这样的查询直接扫描 name 的二级索引，二级索引的数据量少，且在子查询中能直接得到 id 不需要回表。将子查询得到的 id 再去主键索引查询，速度很快，数据量也小。

如果直接扫描主键索引的话，数据量就比较大，因为主键索引包含全部的数据。

当然上面的 SQL 改成 Join 也行，本质上是一样的。

1
2
3
4

 select * from mianshiya 
 inner join
 (select id from mianshiya where name = 'yupi' order by id limit 99999990，10)
 as mianshiya1 on mianshiya.id = mianshiya1.id

2）记录 id

每次分页都返回当前的最大 id ，然后下次查询的时候，带上这个 id，就可以利用 id > maxid 过滤了。

这种查询仅适合连续查询的情况，如果跳页的话就不生效了。

3）elasticsearch

可以考虑用搜索引擎来解决这个问题，不过 es 也会有深度分页的问题，所以如果对 es 不熟，面试就不要这样答了，防止面试官问 es 的深度分页如何解决。

扩展知识

深度分页

所谓的深度分页是指数据量很大的时候，按照分页访问后面的数据，例如 limit 99999990，10，这会使得数据库扫描前面的 99999990 条数据，才能得到最终的 10 条数据，大批量的扫描数据会增加数据库的负载，影响性能。

631.如何在 MySQL 中监控和优化慢 SQL？

回答重点

可以利用 MySQL 自带的 slow_query_log 来监控慢 SQL，它是 MySQL 提供的一个日志功能，用于记录执行时间超过特定阈值的 SQL 语句。

对于慢查询，再使用 EXPLAIN 分析执行计划，查看查询的执行顺序、使用的索引、扫描的行数等，以识别潜在的性能瓶颈。

基于 EXPLAIN 再进行针对性的优化，常见的优化方向有：

• 根据 EXPLAIN 的结果，检查是否有合适的索引。若缺失索引，则添加（特别是在 WHERE、JOIN 和 ORDER BY 子句中使用的列上）
• 将复杂的 JOIN 查询拆分成多个简单查询，尽量小表驱动大表
• 避免 SELECT * ，仅选择需要的字段
• 等等(更多可参考扩展中的 SQL 调优)

扩展知识

MySQL SQL 调优

• [616. MySQL 中如何进行 SQL 调优？ ](#616MySQL 中如何进行 SQL 调优)

MySQL 索引失效场景汇总

• [598. MySQL 中使用索引一定有效吗？如何排查索引效果？](#598MySQL 中使用索引一定有效吗如何排查索引效果)

MySQL 慢 SQL 配置

在 MySQL 配置文件（通常是 my.cnf 或 my.ini）中添加或修改以下配置项：

1
2
3
4
5

[mysqld]
slow_query_log = 1                    ## 启用慢查询日志
slow_query_log_file = /var/log/mysql/mysql-slow.log  ## 指定慢查询日志文件路径
long_query_time = 2.0                 ## 设置慢查询的阈值时间（单位：秒）
log_queries_not_using_indexes = 1     ## 记录未使用索引的查询

其中的 2.0 就是 2 秒，当一个 SQL 执行的时间超过 2 秒，就会被记录到慢日志中。

也可以通过 SQL 命令动态设置：

1
2
3
4

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 2.0;
SET GLOBAL log_queries_not_using_indexes = 'ON';

632.MySQL 中 DELETE、DROP 和 TRUNCATE 的区别是什么？

回答重点

1. Delete 用于删除行数据，但保留表结构和相关的对象。
2. Drop 用于完全删除数据库表，包括数据和结构。
3. Truncate 只删除数据，不会删除表结构和索引等其他结构。

从性能来看，Drop > Truncate > Delete

扩展知识

Delete

本质上这个删除其实就是给数据行打个标记，并不实时删除，因此 delete 之后，空间的大小不会变化。

而且 delete 操作会生成 binlog、redolog 和 undolog，所以如果删除全表使用 delete 的话，性能会比较差！ 但是它可以回滚。

Drop

在 InnoDB 中，每张表数据内容和索引都存储在一个以 .ibd 后缀的文件中，drop 就是直接把这个文件给删除了！还有一个 .frm 后缀的文件也会被删除，这个文件包含表的元数据和结构定义。

文件都删了，所以这个操作无法回滚，表空间会被回收，但是如果表存在系统共享表空间，则不会回收空间。

默认创建的表会有独立表空间，把 innodb_file_per_table 的值改为 OFF 后，就会被放到共享表空间中，即统一的 ibdata1 文件中。

Truncate

Truncate 会对整张表的数据进行删除，且不会记录回滚等日志，所以它无法被回滚。

并且主键字段是自增的，使用 Truncate 删除后自增重新从 1 开始。

633.MySQL 中 INNER JOIN、LEFT JOIN 和 RIGHT JOIN 的区别是什么？

回答重点

INNER JOIN：

• 只返回两个表中匹配的行。如果没有匹配，则该行不会出现在结果集中。
• 适用于只关心交集数据的场景。

LEFT JOIN（或 LEFT OUTER JOIN）：

• 返回左表中的所有行，即使右表中没有匹配的行。如果右表没有匹配，则结果中的右侧列会显示为NULL。
• 适用于需要保留左表所有数据的场景。

RIGHT JOIN（或 RIGHT OUTER JOIN）：

• 返回右表中的所有行，即使左表中没有匹配的行。如果左表没有匹配，则结果中的左侧列会显示为NULL。
• 适用于需要保留右表所有数据的场景。

扩展知识

代码示例

INNER JOIN：

1
2
3

SELECT *
FROM table1
INNER JOIN table2 ON table1.id = table2.id;

LEFT JOIN：

1
2
3

SELECT *
FROM table1
LEFT JOIN table2 ON table1.id = table2.id;

RIGHT JOIN：

1
2
3

SELECT *
FROM table1
RIGHT JOIN table2 ON table1.id = table2.id;

应用场景

• INNER JOIN 适合用在只需要共同数据的结果集中。
• LEFT JOIN 在需要保留主表所有数据的报表中非常有用，比如在客户（左表）和订单表（右表）中查找所有客户及其订单信息，即使有些客户没有订单。
• RIGHT JOIN 则在需要保留右表所有数据的情况下使用，例如在处理员工（左表）和部门表（右表）时，确保显示所有部门，即使某些部门没有员工。

NULL 值处理

• 使用 LEFT JOIN 或 RIGHT JOIN 时，需注意处理结果集中可能出现的 NULL 值，以确保后续的数据处理逻辑正确。

图示所有 JOIN

1214.MySQL 中 LIMIT 100000000, 10 和 LIMIT 10 的执行速度是否相同？

回答重点

速度差很多，limit 10 快很多！

原因如下：

1）LIMIT 100000000, 10 需要先处理（通常是读取并跳过）前 100000000 条记录，然后再获取到需要的 10 条记录，开销成本很大，因为需要扫描 100000000 数据才能得到后面的 10 条数据，会导致大量的磁盘 I/O。

2）LIMIT 10 从结果集第一个记录开始扫描直接返回前 10 条记录。

通常面对LIMIT 100000000, 10这种大分页的情况，可以先使用条件过滤掉，比如使用主键 ID 来进行范围过滤，然后再 limit 10。

示例代码如下：

1

SELECT * FROM users WHERE id > 100000000 ORDER BY id LIMIT 10;

不过这种方式要运用在有递增关系的列条件上。

扩展知识

• [630. MySQL 中如何解决深度分页的问题？ ](#630MySQL 中如何解决深度分页的问题)

1217.MySQL 中 DATETIME 和 TIMESTAMP 类型的区别是什么？

回答重点

存储方式：

• DATETIME：以字符串形式存储，范围为 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'，占用 8 字节。
• TIMESTAMP：以 Unix 时间戳形式存储，范围为 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC，占用 4 字节。

时区处理：

• DATETIME：不受时区影响，存储的时间是具体的日期和时间，不会进行自动转换。
• TIMESTAMP：受时区影响，存储时会转换为 UTC，取出时会根据连接的时区进行转换，适合处理跨时区的数据。

扩展知识

默认值和自动更新

• DATETIME：在 MySQL 5.6 及更早版本中，DATETIME 列不能有自动更新的默认值。在 MySQL 5.6 及以后版本中，可以使用 DEFAULT 和 ON UPDATE 子句来指定自动初始化和更新行为，但不像 TIMESTAMP 那么直观。
• TIMESTAMP：在 MySQL 5.6 及更高版本中，TIMESTAMP 列可以有默认的当前时间戳 CURRENT_TIMESTAMP，并且可以使用 ON UPDATE CURRENT_TIMESTAMP 使其在行更新时自动更新为当前时间戳。这使得 TIMESTAMP 非常适合记录行的创建和修改时间。

1218.为什么阿里巴巴的 Java 手册不推荐使用存储过程？

回答重点

1）可移植性差

• 数据库依赖性：存储过程是在数据库服务器上执行的，通常使用数据库特定的 SQL 方言和功能，这会导致应用程序的数据库依赖性增加，迁移到其他数据库系统时有成本。
• 跨平台问题：不同的数据库系统实现存储过程的方式和支持的功能不完全相同，维护比较复杂。

2）调试困难

• 调试工具有限：相比应用层代码，数据库层的存储过程缺乏良好的调试工具和环境。常规的代码调试方法（如设置断点、逐步执行）在存储过程中无法直接应用，导致复杂业务场景，不容易定位错误。

3）维护复杂

• 存储过程通常与应用程序代码分离，维护起来需要同时管理数据库层和应用层的逻辑，增加了代码管理的复杂性。

扩展知识

什么是存储过程

存储过程是存储在数据库中的一段预编译的 SQL 代码，封装了一组操作来执行特定的业务逻辑。

例如我们想创建一个存储过程，方便通过员工 ID 获取员工的姓名和薪资：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

DELIMITER $$

CREATE PROCEDURE GetEmployeeInfo(IN emp_id INT, OUT emp_name VARCHAR(100), OUT emp_salary DECIMAL(10, 2))
BEGIN
    SELECT name, salary
    INTO emp_name, emp_salary
    FROM employees
    WHERE id = emp_id;
END $$

DELIMITER ;

直接利用 CALL 就可以调用存储过程：

1
2
3
4
5
6
7
8
9

-- 定义变量用于存储输出参数
SET @emp_name = '';
SET @emp_salary = 0;

-- 调用存储过程
CALL GetEmployeeInfo(1, @emp_name, @emp_salary);

-- 查看结果
SELECT @emp_name AS EmployeeName, @emp_salary AS EmployeeSalary;

它的优点是预编译提升执行速度且减少网络通信开销、提高代码重用性。

1219.数据库的三大范式是什么？

回答重点

第一范式（1NF）：

• 确保每个列的值都是原子值，表中的每个字段只能包含单一的数据项，不允许重复的列和多值字段。

第二范式（2NF）：

• 在满足第一范式的基础上，确保表中的每个非主键字段完全依赖于主键，而不是部分依赖。即，非主键字段必须依赖于整个主键。

第三范式（3NF）：

• 在满足第二范式的基础上，确保非主键字段之间不依赖，即消除传递依赖。所有非主键字段只能依赖于主键，不应相互依赖。

扩展知识

三大范式进一步理解

数据库的三大范式是数据库设计中常用的规范，它们的目的是减少数据冗余，提高数据的完整性和一致性，使得表的设计更清晰。

第一范式（1NF）：规范化

目的：确保数据表的每一列都是单一值，消除重复的列，从而保证数据的原子性。

例如地址作为一个字段，实际上可以拆分成省、市、区等，所以这就不符合第一范式。

第二范式（2NF）：消除部分依赖

目的：消除非主键字段对主键部分依赖，从而避免数据冗余和更新异常。

例如 【员工ID、员工姓名、部门ID、部门名】 为一张表，员工 ID 为主键。此时，员工姓名依赖员工ID，部门名依赖部门ID，这就违反了第二范式。

符合范式的设计是：员工表【员工ID、员工姓名、部门ID】，部门表【部门ID、部门名】。

第三范式（3NF）：消除传递依赖

目的：消除非主键字段对主键的传递依赖，从而进一步减少数据冗余和更新异常。

例如 【员工ID、员工姓名、部门ID、部门名、经理ID】 为一张表，员工 ID 为主键。此时，经理 ID 依赖部门 ID，部门 ID 依赖员工ID，这说明有依赖传递，违反了第三范式。

符合范式的设计是：员工表【员工ID、员工姓名、部门ID】，部门表【部门ID、部门名、经理ID】。

小结

• 第一范式（1NF）：确保每一列都是原子值，即是不可分割的基础数据项。
• 第二范式（2NF）：在满足1NF的基础上，消除非主键字段对主键部分依赖。
• 第三范式（3NF）：在满足2NF的基础上，消除非主键字段对主键的传递依赖。

不过现在业务上的表设计基本都是反范式的。当然不是说完全不遵守范式，而是适当的进行调整。

比如业务上经常需要冗余字段，减少联表查询，提升性能，特别是业务量比较大的公司，这种冗余是很有必要的！

1376.在 MySQL 中，你使用过哪些函数？

回答重点

这道题目被问的几率不是很大，大家可以熟悉下，可以在实际开发中使用。

以下列举一些常用的函数类别：

1）字符串函数，用于处理文本数据：

• CONCAT：连接字符串。
• SUBSTRING：提取子字符串。
• LENGTH：返回字符串的长度。
• REPLACE：替换字符串中的子字符串。
• UPPER 和 LOWER：将字符串转换为大写或小写。
• TRIM：去除字符串两端的空格。
• LEFT 和 RIGHT：返回字符串左边或右边的字符。

2）数学函数，用于处理数字运算：

• ABS：返回绝对值。
• CEIL 和 FLOOR：返回大于或等于/小于或等于指定数的最小整数/最大整数。
• MOD：返回除法的余数。
• POWER：返回一个数的指定次幂。

3）日期函数，用于处理日期和时间：

• NOW：返回当前日期和时间。
• DATE_ADD 和 DATE_SUB：日期加上或减去一个时间间隔。
• DATEDIFF：计算两个日期之间的差异。
• YEAR、MONTH、DAY：提取日期的年份、月份、日期。
• STR_TO_DATE：将字符串转换为日期。

4）聚合函数，用于汇总数据：

• COUNT：计算行数。
• SUM：计算总和。
• AVG：计算平均值。
• MAX 和 MIN：返回最大值和最小值。

5）条件函数，用于实现条件逻辑：

• IF：条件判断函数。
• IFNULL：返回第一个非 NULL 值。
• CASE：条件选择函数。

1377.MySQL 中 TEXT 类型最大可以存储多长的文本？

回答重点

TEXT 是非标准字符串类型，除了它还有 TINYTEXT、MEDIUMTEXT 和 LONGTEXT，一般我们存储一些不确定的较长文本才会使用到它们，例如文章之类的。

它们的存储的文本长度汇总如下：

1）TINYTEXT：最大长度为 255 字节。

2）TEXT：最大长度为 65,535 字节（约 64 KB）。

3）MEDIUMTEXT：最大长度为 16,777,215 字节（约 16 MB）。

4）LONGTEXT：最大长度为 4,294,967,295 字节（约 4 GB）。

1378.MySQL 中 AUTO_INCREMENT 列达到最大值时会发生什么？

回答重点

在 MySQL 中，如果表定义的自增 ID 到达上限后，再申请下一个 ID，得到的值不变！ 因此会导致报重复值的错误。

扩展知识

自增 ID 上限实验

例如 int 是 4 个字节，上限是 2 的 32 次方 -1，即 2147483647，当到达这个值的之后，下次插入得到的还是这个值，来看一下实验：

1
2
3
4
5
6

create table mianshiya(
    id int auto_increment primary key
)  auto_increment=2147483647;
insert into mianshiya values(null);

insert into mianshiya values(null);

执行以上的 SQL，第一条 insert 成功插入数据，第二条则报重复值的错误！

实践出真知哈！

再列举下 AUTO_INCREMENT 列不同数据类型的最大值：

• 对于 TINYINT（8位），最大值是 127（有符号）或 255（无符号）。
• 对于 SMALLINT（16位），最大值是 32,767（有符号）或 65,535（无符号）。
• 对于 MEDIUMINT（24位），最大值是 8,388,607（有符号）或 16,777,215（无符号）。
• 对于 INT（32位），最大值是 2,147,483,647（有符号）或 4,294,967,295（无符号）。
• 对于 BIGINT（64位），最大值是 9,223,372,036,854,775,807（有符号）或 18,446,744,073,709,551,615（无符号）。

如果 InnoDB 表没有配置主键，有最大值上限吗？

如果我们在 InnoDB 表中不配置主键，那么默认 InnoDB 会创建一个不可见的长度为 6 个字节的 row_id。

InnoDB 在全局维护了一个 dict_sys.row_id 值，所有需要用到 row_id 的表，每次插入一行数据，都会获取这个值，然后将其+1。

这个值的范围是 0~2^48-1。如果这个值达到上限后，又会从 0 开始，然后继续循环。如果插入的新数据的 row_id 在表中已存在，那么老的数据会被这个新数据覆盖，不会产生有任何报错。

如果要验证这个情况，可以通过 gbd 动态地将 mysql 的 dict_sys.row_id 修改，执行多次插入就能发现覆盖的问题。

一般生产环境中不会有表没有设置主键的情况，这个仅做了解即可。

1379.在 MySQL 中存储金额数据，应该使用什么数据类型？

回答重点

在数据库中业界常用两种类型来存储金额：bigint 和 decimal。

1）bigint 代码中用 long。

• 范围：可以存储的整数范围为 -2^63 到 2^63 - 1（在 MySQL 中为 64 位有符号整数）
• 存储空间：占用 8 字节（64 位）
• 精度：精确存储整数，但不支持小数部分，存储的金额单位是分

2）decimal 代码中使用 BigDecimal。

• 范围：可以存储的数字范围和小数位数由定义的精度和标度决定
• 存储空间：存储空间取决于定义的精度和标度，存储较大数值时会占用更多空间
• 精度：支持高精度的小数运算，精确存储定点数，一般用 decimal(18,6)，18 是总位数，6 是小数

扩展知识

项目中到底选择 Long 还是 BigDecimal？

long 类型保存到分，使得小数位（厘）的数据不好处理（需要手动处理，比较麻烦），因此精度不够高。

在高精度金额计算场景下不合适，例如有些三方支付系统是需要抽成的，例如千分之三、万分之一等等，这类的抽成要求精度比较高，例如 会得到 1234.5678 这样的金额，乘以 100 后仍然无法存储为精确整数，还是小数，存储则会丢失精度。

0.0078 看似很少，但抽成的笔数一多，比如一天 30 万笔，每笔少个 0.0078，每天的差额也得 2340 元！一年不得 85 万！

所以高精度的计算不推荐使用 bigint。

BigDecimal 则很适用于高精度金额场景，且非常灵活，只不过相对于 long 性能会差一些，但是在大部分业务上我个人认为可以忽略这个性能问题，除非是特殊场景要求极端性能，所以一般情况下我推荐使用 BigDecimal。

1418.什么是数据库的视图？

回答重点

数据库的视图是一个虚拟表，它并不存储实际的数据，而是通过查询其他表的数据来生成的。视图可以简化复杂的查询、增强数据安全性（限制访问某些数据）以及提供数据的不同表示方式。

它可以包含一个或多个表的数据，并且可以对这些数据进行筛选、计算、排序等操作。

扩展知识

视图的作用

• 简化复杂查询：视图可以将复杂的查询封装成一个简单的视图，使得用户在查询数据时更加方便。
• 安全性：通过视图可以限制用户访问特定的表和列，保护敏感数据。例如，只允许用户查看某些列而不是整个表。
• 数据抽象：视图提供了一种数据抽象层，用户可以通过视图获取需要的数据，而不必关心底层表的结构和关系。
• 可重用性：定义一次视图，可以在多个查询中重复使用，减少代码冗余。

下面是一个实际的视图示例，展示如何创建和使用视图来简化查询。

视图使用示例

假设有一个数据库中有两个表：employees（员工表）和 departments（部门表）。

employees 表结构：

departments 表结构：

创建视图

可以创建一个视图 employee_salaries，用于展示员工的全名及其对应的部门和薪资。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE VIEW employee_salaries AS
SELECT 
    e.employee_id,
    CONCAT(e.first_name, ' ', e.last_name) AS full_name,
    d.department_name,
    e.salary
FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.department_id;

使用视图

现在，可以通过查询视图来获取员工的全名、部门和薪资信息，而不需要每次都写复杂的 JOIN 查询：