02｜存储引擎

1. 说一说执行一条查询 SQL 语句的全过程

#

分析

考察你对 MySQL 整体架构的理解和认识，查询 SQL 的执行大体会经过 Server 层和存储引擎层。

回答

MySQL 执行一条查询 SQL 语句的时候，会经过连接器、查询缓存、解析器、优化器、执行器、存储引擎这些模块。

• 首先 MySQL 的连接器会负责建立连接、校验用户身份、接收客户端的 SQL 语句。
• 第二步 MySQL 会在查询缓存中查找数据，如果命中直接返回数据给客户端，否则就需要继续往下查询。不过查询缓存功能在 MySQL 8.0 版本被删除了，原因是只要对这张表进行了写操作，这张表的查询缓存就会失效，所以在实际场景中，查询缓存的命中率其实不高。
• 第三步 MySQL 的解析器会对 SQL 语句进行词法分析和语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型。
• 第四步 MySQL 的优化器会基于查询成本的考虑，判断每个索引的执行成本，从中选择查询成本最小的执行计划。
• 第五步 MySQL 的执行器会根据执行计划来执行查询语句，从存储引擎读取记录，返回给客户端。

2. MySQL 存储引擎有哪些？

#

分析

MySQL 整体上分为 Server 层和存储引擎层。Server 层负责的部分是连接器、查询缓存、解析器、优化器、执行器，存储引擎层负责数据的读取和存储。存储引擎就像是一个插件，MySQL 可以根据业务场景使用不同的存储引擎，目前 MySQL 支持 InnoDB、MyISAM、Memory、Archive、CSV、NDB Cluster 等多个存储引擎。

面试时只需要说出 InnoDB、MyISAM、Memory 这三种就可以。

回答

MySQL 常见的存储引擎有 InnoDB、MyISAM、Memory。

• 我比较熟悉的是 InnoDB 引擎，它是 MySQL 默认的存储引擎，支持事务和行级锁，具有事务提交、回滚和崩溃恢复功能。
• MyISAM 引擎我没有用过，但是在学习的时候有了解过，它是不支持事务和行级锁的，而且由于只支持表锁，锁的粒度比较大，更新性能比较差，我认为它比较适合读多写少的场景。
• Memory 引擎我了解不多，大概知道它是将数据存储在内存中，所以数据的读写还是比较快的，但是数据不具备持久性，我觉得适用于临时存储数据的场景。

3. MyISAM 和 InnoDB 存储引擎有什么区别？

#

分析

从数据存储、B+ 树结构、锁粒度、事务这四个角度来分析。

• 数据存储：InnoDB 引擎数据存储的方式采用的是索引组织表，在索引组织表中，数据即索引，索引即数据，因此表数据和索引数据都存储在同一个文件中。MyISAM 引擎数据存储的方式采用的是堆表，在堆表的组织结构中，数据和索引分开存储，因此表数据和索引数据会分别放在两个不同的文件中存储。
• 索引组织表优点：
  • 在索引组织表将索引和数据保存在同一个 B+ 树中，因此从聚簇索引中获取数据比非聚簇索引更快，查询数据会更快。
  • 在索引组织表中，二级索引设计有一个非常大的好处。若记录发生了修改，则其他索引无需进行维护。除非记录的主键发生了修改，与堆表的索引实现对比着看，你会发现索引组织表在存在大量变更的场景下，性能优势会非常明显，因为大部分情况下都不需要维护其他二级索引。
• 索引组织表缺点：
  • 插入或更新操作可能导致 B+ 树的页分裂，需要移动大量数据以维持有序性，尤其在高并发场景下性能下降明显。
  • 主键的选择直接影响索引组织表的性能。如果主键选择不当（如随机生成的 UUID），会导致频繁的页分裂和数据迁移。
  • 如果表包含大字段（如 TEXT、BLOB），索引组织表可能会导致索引膨胀，影响性能。
• 堆表优点：
  • 由于不需要维护数据的物理顺序，插入操作只需找到空闲空间写入即可，无需移动其他数据行，所以插入速度会更快。
  • 数据存储更紧凑，尤其是对于频繁更新的表，堆表不需要为后续插入预留空间，聚簇索引表可能需要预留空间以避免页分裂。
• 堆表缺点：
  • 堆表中的索引都是二级索引，哪怕是主键索引也是二级索引，也就是说它没有聚簇索引，每次索引查询都要回表。
  • 由于索引的叶子节点存放了数据在堆表中的地址，当堆表的数据发生改变且位置发生了变更，那么所有索引中的地址都要更新，这非常影响性能。
• B+ 树结构：InnoDB 引擎 B+ 树叶子节点存储索引和数据，MyISAM 引擎 B+ 树叶子节点存储索引和数据地址。
• 锁粒度：InnoDB 引擎支持行级锁，而 MyISAM 不支持行级锁，仅支持表锁。
• 事务：InnoDB 支持事务，而 MyISAM 不支持事务。

回答

InnoDB 引擎和 MyISAM 引擎在数据存储上有很大区别。InnoDB 引擎数据存储的方式采用的是索引组织表，在索引组织表中，数据即索引，索引即数据，因此表数据和索引数据都存储在同一个文件中。MyISAM 引擎数据存储的方式采用的是堆表，在堆表的组织结构中，数据和索引分开存储，因此表数据和索引数据会分别放在两个不同的文件中存储，索引组织表有两个优势：

• 在索引组织表将索引和数据保存在同一个 B+ 树中，相比非聚簇索引每次查询都需要回表，因此从聚簇索引中获取数据比非聚簇索引更快，查询数据会更快。
• 在索引组织表中，如果记录发生了修改，则其他索引无须进行维护，除非记录的主键发生了修改，而当堆表的数据发生改变且位置发生了变更，那么所有索引中的地址都要更新，这非常影响性能。

另外，InnoDB 引擎支持行级锁和事务，而 MyISAM 引擎都不支持，只支持表锁。

4. MySQL 为什么选择 InnoDB 作为默认引擎？

#

分析

最重要原因是 InnoDB 支持事务，其他存储引擎都不支持。

回答

InnoDB 引擎在事务支持、并发性能、崩溃恢复等方面具有优势，因此被 MySQL 选择为默认的存储引擎。

• 事务支持：InnoDB 引擎提供了对事务的支持，可以进行 ACID（原子性、一致性、隔离性、持久性）属性的操作。MyISAM 存储引擎是不支持事务的。
• 并发性能：InnoDB 引擎采用了行级锁定的机制，可以提供更好的并发性能，MyISAM 存储引擎只支持表锁，锁的粒度比较大。
• 崩溃恢复：InnoDB 引擎通过 redolog 日志实现了崩溃恢复，可以在数据库发生异常情况（如断电）时，通过日志文件进行恢复，保证数据的持久性和一致性。MyISAM 是不支持崩溃恢复的。

5. 用 count(*) 哪个存储引擎会更快？

#

分析

InnoDB 引擎执行 count 函数的时候，需要通过遍历的方式来统计记录个数，而 MyISAM 引擎执行 count 函数只需要 \(O(1)\) 复杂度。这是因为每张 MyISAM 的数据表都有一个 meta 信息存储了 row_count 值，由表级锁保证一致性，所以直接读取 row_count 值就是 count 函数的执行结果。

而 InnoDB 存储引擎是支持事务的，同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的，所以无法像 MyISAM 一样，只维护一个 row_count 变量。

回答

如果查询语句没有 WHERE 查询条件的话，用 MyISAM 引擎会比较快，因为 MyISAM 引擎的每张表会用一个变量存储表的总记录个数，执行 count 函数的时候，直接读这个变量就行了；而 InnoDB 引擎执行 count 函数的时候，需要通过遍历的方式来统计记录个数。

如果查询语句有 WHERE 查询条件的话，MyISAM 和 InnoDB 引擎执行 count 函数的时候性能都差不多，都需要根据查询条件一行行地进行统计。

6. NULL 值是如何存储的？

#

分析

MySQL 存储一行数据的时候，会使用行格式进行存储，其中 NULL 值列表就是用来保存 NULL 值的。

如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。

• 二进制位的值为 1 时，代表该列的值为 NULL。
• 二进制位的值为 0 时，代表该列的值不为 NULL。

以 t_user 表的这三条记录作为例子：

我们直接看第三条记录，第三条记录 phone 列和 age 列是 NULL 值，所以对于第三条数据，NULL 值列表用十六进制表示是 0x06。

回答

MySQL 行格式中会用 NULL 值列表来标记值为 NULL 的列。每个列对应一个二进制位，如果列的值为 NULL，就会标记二进制位为 1，否则为 0，所以 NULL 值并不会存储在行格式中的真实数据部分。

NULL 值列表最少会占用 1 字节空间，当表中所有列都定义成 NOT NULL，行格式中就不会有 NULL 值列表，这样可以至少节省 1 字节的空间。

7. char 和 varchar 有什么区别？追问：哪个性能更好？

#

分析

可以从三个角度来分析：

• 语义上的区别：
  • char 类型是一种固定长度的字符串类型，它在数据库中占用固定的存储空间，无论实际存储的数据长度是多少，都会占用定义时指定的固定长度。例如，如果定义一个 char(10) 类型的字段，那么无论实际存储的数据长度是多少，都会占用 10 个字节的存储空间（字符集为 ASCII 的情况下，1 个字符是 1 字节大小，10 个字符就是 10 字节大小）。
  • varchar 类型是一种可变长度的字符串类型，它在数据库中只占用实际存储数据的长度加上一定的额外存储空间。例如，如果定义一个 varchar(10) 类型的字段，并存储了一个长度为 5 的字符串，那么它只会占用 5 个字节的存储空间（字符集为 ASCII 的情况下）加上一定的额外存储空间（存储字符串长度的空间）。
• 存储上的区别：
  • varchar 会占用额外的 1～2 字节来存储字符串长度。如果最大长度超过 255，就需要 2 字节，否则 1 字节。
  • 对于 char(N) 字段，如果实际存储数据小于 N 字节，会填充空格到 N 个字节。
• 性能上的区别：
  • 理论上 CHAR 比 VARCHAR 更快，因为 CHAR 是固定长度的，而 VARCHAR 需要增加一个长度标识，处理时需要多一次运算。
  • 理论上 CHAR 比 VARCHAR 快的根本原因是站在 CPU 的角度来说，但性能是综合各种因素后的最终结果。当 InnoDB buffer pool 小于表大小时，“磁盘读写”成为了性能的关键因素，而 VARCHAR 更短，因此性能反而比 CHAR 高。但是当 InnoDB buffer pool 足够大时，CHAR 和 VARCHAR 性能没有太大的差别。

回答

• char 是固定长度的字符串类型，它在数据库中占用固定的存储空间，无论实际存储的数据长度是多少，都会占用定义时指定的固定长度。如果实际存储的字符串长度小于定义的长度，系统会自动用空格填充。比如如果定义一个 char(10) 类型的字段，即使实际数据只使用 5 字节，也会自动填充 5 字节的空格，使得存储空间固定占用 10 字节。
• varchar 是可变长度的字符串类型，实际存储时只占用实际字符串长度的空间，不会进行空格填充。比如如果定义一个 varchar(10) 类型的字段，并存储了一个长度为 5 的字符串，那么它只会占用 5 字节的存储空间，并且还会额外用 1～2 字节存储“可变长字符串长度”的空间。

追问回答

站在 CPU 角度来看，理论上 CHAR 比 VARCHAR 更快，因为 CHAR 是固定长度的，而 VARCHAR 需要增加一个长度标识，处理时需要多一次运算。

但性能是综合各种因素后的最终结果，当 InnoDB buffer pool 小于表大小时，“磁盘读写”成为了性能的关键因素，而 VARCHAR 更短，因此性能反而比 CHAR 高。但是当 InnoDB buffer pool 足够大时，CHAR 和 VARCHAR 性能没有太大的差别。

8. 假如说一个字段是 varchar(10)，但它其实只有 6 个字节，那它在内存中占的存储空间是多少？在文件中占的存储空间是多少？

#

分析

varchar 是可变长字符串，保存到文件的时候，只会存储实际使用的字符串大小。但是内存会按 varchar 最大值来固定分配大小。

下图《高性能 MySQL》第 4.1.3 小节提到，MySQL 会分配固定大小的内存块来保存字段的值。

回答

内存会占用 10 字节，文件存储空间会占用 6 字节，并且还会额外用 1～2 字节存储“可变长字符串长度”的空间。

9. 如果硬件内存特别大，MySQL 缓存能否替代 Redis？

#

分析

这是一个字节的面试题，这里的 MySQL 缓存代表用于缓存数据页的 buffer pool。所以问题的意思是，假设 buffer pool 无限大，能在内存装下所有数据，是否可以替代 Redis？

问题的回答思路是：要去想 Redis 缓存哪些优点是 MySQL 没有的。

回答

我觉得还是不能替代。

MySQL 所有模块，比如 buffer pool、日志技术、事务并发模块，都是面向磁盘页而设计的，因此其首要目标不是减少内存访问的代价，而是 I/O 代价，所以内存访问代价并不是最优的选择，而 Redis 是面向内存而设计的数据库。

MySQL 在内存查询一个数据页的时候，都需要先查页表，也就是需要走 B+ 树的搜索过程，时间复杂度是 \(O(\log N)\)，而 Redis 提供了很多种的数据类型，比如用 Hash 数据对象的时候，可以在 \(O(1)\) 时间复杂度查到数据。

MySQL 在更新数据的时候，为了保证事务的隔离性，是需要加锁的；而 Redis 更新操作都是不需要加锁的。MySQL 为了保证事务的持久性，还需要刷盘 redolog 日志和 binlog 日志，Redis 可以选择不持久化数据。

因此，即使 buffer pool 无限大，MySQL 缓存的性能还是没有 Redis 好。