执行一条 select 语句，期间发生了什么？

MySQL 执行流程是怎样的？

查询语句执行流程

可以看到，MySQL 的架构共分为两层：Server 层和存储引擎层

  • Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。
  • 存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始，InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

第一步：连接器

如果你在 Linux 操作系统里要使用 MySQL，那你第一步肯定是要先连接 MySQL 服务，然后才能执行 SQL 语句。连接的过程需要先经过 TCP 三次握手，因为 MySQL 是基于 TCP 协议进行传输的。

当你的账号或密码不正确时，会报 Access denied for user 错误，连接成功如果后续没有任何操作，那么这个连接就处于空闲状态，到达一定时间后它便会断开连接，这个时间一般是 8 小时，是由 wait_timeout 参数控制的。

第二步：查询缓存

连接器的工作完成后，客户端就可以向 MySQL 服务发送 SQL 语句了，MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。

如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（Query Cache）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。

如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。

这么看，查询缓存还挺有用，但是其实查询缓存挺鸡肋的。

对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据，还没被使用的时候，刚好这个表有更新操作，查询缓冲就被清空了，相当于缓存了个寂寞。

所以，MySQL 8.0 版本直接将查询缓存删掉了，也就是说 MySQL 8.0 开始，执行一条 SQL 查询语句，不会再走到查询缓存这个阶段了。

这里说的查询缓存是 server 层的，也就是 MySQL 8.0 版本移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer pool。

第三步：解析 SQL

在正式执行 SQL 查询语句之前，MySQL 会先对 SQL 语句做解析，这个工作交由「解析器」来完成。

解析器

解析器会做如下两件事情。

第一件事情，词法分析。MySQL 会根据你输入的字符串识别出关键字出来，构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、where 条件等等。

第二件事情，语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

如果我们输入的 SQL 语句语法不对，就会在解析器这个阶段报错。

但是注意，表不存在或者字段不存在，并不是在解析器里做的，《MySQL 45 讲》说是在解析器做的，但是经过我和朋友看 MySQL 源码（5.7 和 8.0）得出结论是解析器只负责构建语法树和检查语法，但是不会去查表或者字段存不存在。

那到底谁来做检测表和字段是否存在的工作呢？别急，接下来就是了。

第四步：执行 SQL

经过解析器后，接着就要进入执行 SQL 查询语句的流程了，每条SELECT 查询语句流程主要可以分为下面这三个阶段：

  • prepare 阶段，也就是预处理阶段；
  • optimize 阶段，也就是优化阶段；
  • execute 阶段，也就是执行阶段；

预处理器

我们先来说说预处理阶段做了什么事情。

  • 检查 SQL 查询语句中的表或者字段是否存在；
  • 将 select * 中的 * 符号，扩展为表上的所有列；

优化器

经过预处理阶段后，还需要为 SQL 查询语句先制定一个执行计划，这个工作交由「优化器」来完成的。

优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。

当然，我们本次的查询语句（select * from product where id = 1）很简单，就是选择使用主键索引。

要想知道优化器选择了哪个索引，我们可以在查询语句最前面加个 explain 命令，这样就会输出这条 SQL 语句的执行计划，然后执行计划中的 key 就表示执行过程中使用了哪个索引，比如下图的 key 为 PRIMARY 就是使用了主键索引。

如果查询语句的执行计划里的 key 为 null 说明没有使用索引，那就会全表扫描（type = ALL），这种查询扫描的方式是效率最低档次的。

这张 product 表只有一个索引就是主键，现在我在表中将 name 设置为普通索引（二级索引）。

这时 product 表就有主键索引（id）和普通索引（name）。假设执行了这条查询语句：

select id from product where id > 1  and name like 'i%';

这条查询语句的结果既可以使用主键索引，也可以使用普通索引，但是执行的效率会不同。这时，就需要优化器来决定使用哪个索引了。

很显然这条查询语句是覆盖索引，直接在二级索引就能查找到结果（因为二级索引的 B+ 树的叶子节点的数据存储的是主键值），就没必要在主键索引查找了，因为查询主键索引的 B+ 树的成本会比查询二级索引的 B+ 的成本大，优化器基于查询成本的考虑，会选择查询代价小的普通索引。

在下图中执行计划，我们可以看到，执行过程中使用了普通索引（name），Exta 为 Using index，这就是表明使用了覆盖索引优化。

img

执行器

经历完优化器后，就确定了执行方案，接下来 MySQL 就真正开始执行语句了，这个工作是由「执行器」完成的。在执行的过程中，执行器就会和存储引擎交互了，交互是以数据行为单位的。

接下来，用三种方式执行过程，跟大家说一下执行器和存储引擎的交互过程。

  • 主键索引查询
  • 全表扫描
  • 索引下推

主键索引查询

以本文开头查询语句为例，看看执行器是怎么工作的。

select * from product where id = 1;

这条查询语句的查询条件用到了主键索引，而且是等值查询，同时主键 id 是唯一，不会有 id 相同的记录，所以优化器决定选用访问类型为 const 进行查询，也就是使用主键索引查询一条记录，那么执行器与存储引擎的执行流程是这样的：

  • 执行器第一次查询，会调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为 InnoDB 引擎索引查询的接口，把条件 id = 1 交给存储引擎，让存储引擎定位符合条件的第一条记录。
  • 存储引擎通过主键索引的 B+ 树结构定位到 id = 1 的第一条记录，如果记录是不存在的，就会向执行器上报记录找不到的错误，然后查询结束。如果记录是存在的，就会将记录返回给执行器；
  • 执行器从存储引擎读到记录后，接着判断记录是否符合查询条件，如果符合则发送给客户端，如果不符合则跳过该记录。
  • 执行器查询的过程是一个 while 循环，所以还会再查一次，但是这次因为不是第一次查询了，所以会调用 read_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为一个永远返回 - 1 的函数，所以当调用该函数的时候，执行器就退出循环，也就是结束查询了。

至此，这个语句就执行完成了。

全表扫描

举个全表扫描的例子：

select * from product where name = 'iphone';

这条查询语句的查询条件没有用到索引，所以优化器决定选用访问类型为 ALL 进行查询，也就是全表扫描的方式查询，那么这时执行器与存储引擎的执行流程是这样的：

  • 执行器第一次查询，会调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 all，这个函数指针被指向为 InnoDB 引擎全扫描的接口，让存储引擎读取表中的第一条记录；
  • 执行器会判断读到的这条记录的 name 是不是 iphone，如果不是则跳过；如果是则将记录发给客户端（是的没错，Server 层每从存储引擎读到一条记录就会发送给客户端，之所以客户端显示的时候是直接显示所有记录的，是因为客户端是等查询语句查询完成后，才会显示出所有的记录）。
  • 执行器查询的过程是一个 while 循环，所以还会再查一次，会调用 read_record 函数指针指向的函数，因为优化器选择的访问类型为 all，read_record 函数指针指向的还是 InnoDB 引擎全扫描的接口，所以接着向存储引擎层要求继续读刚才那条记录的下一条记录，存储引擎把下一条记录取出后就将其返回给执行器（Server 层），执行器继续判断条件，不符合查询条件即跳过该记录，否则发送到客户端；
  • 一直重复上述过程，直到存储引擎把表中的所有记录读完，然后向执行器（Server 层）返回了读取完毕的信息；
  • 执行器收到存储引擎报告的查询完毕的信息，退出循环，停止查询。

至此，这个语句就执行完成了。

索引下推

在这部分非常适合讲索引下推（MySQL 5.6 推出的查询优化策略），这样大家能清楚的知道，「下推」这个动作，下推到了哪里。

索引下推能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了。

举一个具体的例子，方便大家理解，这里一张用户表如下，我对 age 和 reward 字段建立了联合索引（age，reward）：

现在有下面这条查询语句：

select * from t_user  where age > 20 and reward = 100000;

联合索引当遇到范围查询 (>、<) 就会停止匹配，也就是 age 字段能用到联合索引，但是 reward 字段则无法利用到索引。具体原因这里可以看这篇：索引常见面试题

那么，不使用索引下推（MySQL 5.6 之前的版本）时，执行器与存储引擎的执行流程是这样的：

  • Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；
  • 存储引擎根据二级索引的 B+ 树快速定位到这条记录后，获取主键值，然后进行回表操作，将完整的记录返回给 Server 层；
  • Server 层在判断该记录的 reward 是否等于 100000，如果成立则将其发送给客户端；否则跳过该记录；
  • 接着，继续向存储引擎索要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，然后回表操作，将完整的记录返回给 Server 层；
  • 如此往复，直到存储引擎把表中的所有记录读完。

可以看到，没有索引下推的时候，每查询到一条二级索引记录，都要进行回表操作，然后将记录返回给 Server，接着 Server 再判断该记录的 reward 是否等于 100000。

而使用索引下推后，判断记录的 reward 是否等于 100000 的工作交给了存储引擎层，过程如下：

  • Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；
  • 存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列（reward 列）的条件（reward 是否等于 100000）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给 Server 层。
  • Server 层在判断其他的查询条件（本次查询没有其他条件）是否成立，如果成立则将其发送给客户端；否则跳过该记录，然后向存储引擎索要下一条记录。
  • 如此往复，直到存储引擎把表中的所有记录读完。

可以看到，使用了索引下推后，虽然 reward 列无法使用到联合索引，但是因为它包含在联合索引（age，reward）里，所以直接在存储引擎过滤出满足 reward = 100000 的记录后，才去执行回表操作获取整个记录。相比于没有使用索引下推，节省了很多回表操作。

当你发现执行计划里的 Extra 部分显示了“Using index condition”，说明使用了索引下推。


HAVING 子句是什么？

HAVING 子句可以让我们筛选分组后的各组数据。

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

  • Having子句的作用和WHERE子句差不多，都是用来做条件筛选的；

  • 只是Having子句需要写在GROPY BY子句的后面；

  • GROPY BY 子句执行完毕之后，才会轮到Having子句去执行；自然Having子句的执行顺序是在WHERRE子句之后的，所以在Having子句中可以使用聚合函数做条件判断。

having 和 where 区别

  • having是对一个表的数据进行分组之后，对组信息进行相应条件筛选
  • having筛选时，只能根据select子句中可出现的字段（数据）来进行条件设定
  • having子句与where子句一样，都是用于条件判断
  • where是判断数据从磁盘读入内存的时候
  • having是判断分组统计之前的所有条件
  • having子句中可以使用字段别名，而where不能使用
  • having能够使用统计函数，而where不能使用

having 和 where 应用

在 MySQL 中，HAVING 和 WHERE 是用于筛选数据的两个关键字，它们有以下用法：

用途：

  • WHERE：WHERE 子句用于在执行查询之前筛选行。它可用于过滤 SELECT、UPDATE、DELETE 语句中的行，根据指定的条件选择要操作的数据行。

  • HAVING：HAVING 子句用于在执行聚合查询后筛选结果集。它仅能在包含聚合函数（如 SUM、COUNT、AVG 等）的 SELECT 语句中使用，并在查询结果进行聚合操作后，对聚合结果进行筛选。

位置：

  • WHERE：WHERE 子句通常出现在 SELECT、UPDATE、DELETE 语句的 FROM 子句之后、GROUP BY 子句之前。

  • HAVING：HAVING 子句通常出现在 GROUP BY 子句之后、ORDER BY 子句之前。

过滤条件：

  • WHERE：WHERE 子句用于指定过滤条件，其中可以使用比较操作符（如 =, <>, <, >, BETWEEN, IN 等）和逻辑操作符（如 AND, OR, NOT）。

  • HAVING：HAVING 子句用于指定对聚合结果的过滤条件，其中可以使用比较操作符和逻辑操作符，也可以使用聚合函数。

示例： 假设有一个名为 orders 的表，其中包含 customer_id（客户ID）和 total_price（总价格）字段。我们希望查找每个客户的总价格大于 100 的订单。

SELECT customer_id, SUM(total_price) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(total_price) > 100;

MySQL 一行记录是怎么存储的？

MySQL 的数据存放在哪个文件？

大家都知道 MySQL 的数据都是保存在磁盘的，那具体是保存在哪个文件呢？

MySQL 存储的行为是由存储引擎实现的，MySQL 支持多种存储引擎，不同的存储引擎保存的文件自然也不同。

InnoDB 是我们常用的存储引擎，也是 MySQL 默认的存储引擎。所以，本文主要以 InnoDB 存储引擎展开讨论。

先来看看 MySQL 数据库的文件存放在哪个目录？

mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

我们每创建一个 database（数据库）都会在 /var/lib/mysql/ 目录里面创建一个以 database 为名的目录，然后保存表结构和表数据的文件都会存放在这个目录里。

比如，我这里有一个名为 my_test 的 database，该 database 里有一张名为 t_order 数据库表。

然后，我们进入 /var/lib/mysql/my_test 目录，看看里面有什么文件？

[root@xiaolin ~]#ls /var/lib/mysql/my_test
db.opt  
t_order.frm  
t_order.ibd

可以看到，共有三个文件，这三个文件分别代表着：

  • db.opt，用来存储当前数据库的默认字符集和字符校验规则。
  • t_order.frm，t_order 的表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。
  • t_order.ibd，t_order 的表数据会保存在这个文件。表数据既可以存在共享表空间文件（文件名：ibdata1）里，也可以存放在独占表空间文件（文件名：表名字.ibd）。这个行为是由参数 innodb_file_per_table 控制的，若设置了参数 innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从 MySQL 5.6.6 版本开始，它的默认值就是 1 了，因此从这个版本之后，MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件。

好了，现在我们知道了一张数据库表的数据是保存在「表名字.ibd」的文件里的，这个文件也称为独占表空间文件。

表空间文件的结构是怎么样的？

表空间由段（segment）、区（extent）、页（page）、行（row）组成，InnoDB 存储引擎的逻辑存储结构大致如下图：

下面我们从下往上一个个看看。

行（row）

数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。

后面我们详细介绍 InnoDB 存储引擎的行格式，也是本文重点介绍的内容。

页（page）

记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

因此，InnoDB 的数据是按「页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。

默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间。

页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

页的类型有很多，常见的有数据页、undo 日志页、溢出页等等。数据表中的行记录是用「数据页」来管理的，数据页的结构这里我就不讲细说了，之前文章有说过，感兴趣的可以去看这篇文章：换一个角度看 B+ 树

总之知道表中的记录存储在「数据页」里面就行。

区（extent）

我们知道 InnoDB 存储引擎是用 B+ 树来组织数据的。

B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机 I/O，随机 I/O 是非常慢的。

解决这个问题也很简单，就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。

那具体怎么解决呢？

在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。

段（segment）

表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。

  • 索引段：存放 B + 树的非叶子节点的区的集合；
  • 数据段：存放 B + 树的叶子节点的区的集合；
  • 回滚段：存放的是回滚数据的区的集合，之前讲事务隔离的时候就介绍到了 MVCC 利用了回滚段实现了多版本查询数据。

好了，终于说完表空间的结构了。接下来，就具体讲一下 InnoDB 的行格式了。

之所以要绕一大圈才讲行记录的格式，主要是想让大家知道行记录是存储在哪个文件，以及行记录在这个表空间文件中的哪个区域，有一个从上往下切入的视角，这样理解起来不会觉得很抽象。

InnoDB 行格式有哪些？

行格式（row_format），就是一条记录的存储结构。

InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic 和 Compressed 行格式。

  • Redundant 是很古老的行格式了，MySQL 5.0 版本之前用的行格式，现在基本没人用了。
  • 由于 Redundant 不是一种紧凑的行格式，所以 MySQL 5.0 之后引入了 Compact 行记录存储方式，Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact。
  • Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。

Redundant 行格式我这里就不讲了，因为现在基本没人用了，这次重点介绍 Compact 行格式，因为 Dynamic 和 Compressed 这两个行格式跟 Compact 非常像。

所以，弄懂了 Compact 行格式，之后去了解其他行格式，很快也能看懂。

COMPACT 行格式长什么样？

先跟 Compact 行格式混个脸熟，它长这样：

可以看到，一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个部分。

记录的额外信息

记录的额外信息包含 3 个部分：变长字段长度列表、NULL 值列表、记录头信息。

1. 变长字段长度列表

varchar(n) 和 char(n) 的区别是什么，相信大家都非常清楚，char 是定长的，varchar 是变长的，变长字段实际存储的数据的长度（大小）不固定的。

所以，在存储数据的时候，也要把数据占用的大小存起来，存到「变长字段长度列表」里面，读取数据的时候才能根据这个「变长字段长度列表」去读取对应长度的数据。其他 TEXT、BLOB 等变长字段也是这么实现的。

为了展示「变长字段长度列表」具体是怎么保存「变长字段的真实数据占用的字节数」，我们先创建这样一张表，字符集是 ascii（所以每一个字符占用的 1 字节），行格式是 Compact，t_user 表中 name 和 phone 字段都是变长字段：

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL,
  `name` VARCHAR(20) DEFAULT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

现在 t_user 表里有这三条记录：

接下来，我们看看看看这三条记录的行格式中的「变长字段长度列表」是怎样存储的。

先来看第一条记录：

  • name 列的值为 a，真实数据占用的字节数是 1 字节，十六进制 0x01；
  • phone 列的值为 123，真实数据占用的字节数是 3 字节，十六进制 0x03；
  • age 列和 id 列不是变长字段，所以这里不用管。

这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放（等下会说为什么要这么设计），所以「变长字段长度列表」里的内容是「03 01」，而不是「01 03」。

同样的道理，我们也可以得出第二条记录的行格式中，「变长字段长度列表」里的内容是「04 02」，如下图：

第三条记录中 phone 列的值是 NULL，NULL 是不会存放在行格式中记录的真实数据部分里的，所以「变长字段长度列表」里不需要保存值为 NULL 的变长字段的长度。

为什么「变长字段长度列表」的信息要按照逆序存放？

这个设计是有想法的，主要是因为「记录头信息」中指向下一个记录的指针，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。

「变长字段长度列表」中的信息之所以要逆序存放，是因为这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。

同样的道理，NULL 值列表的信息也需要逆序存放。

NULL 值列表

表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL 值列表中。

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

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

另外，NULL 值列表必须用整数个字节的位表示（1 字节 8 位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。

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

我们把三条记录的 NULL 值列表都填充完毕后，它们的行格式是这样的：

每个数据库表的行格式都有「NULL 值列表」吗？

NULL 值列表也不是必须的。

当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。

所以在设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以至少节省 1 字节的空间（NULL 值列表至少占用 1 字节空间）。

「NULL 值列表」是固定 1 字节空间吗？如果这样的话，一条记录有 9 个字段值都是 NULL，这时候怎么表示？

「NULL 值列表」的空间不是固定 1 字节的。

当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的「NULL 值列表」，以此类推。

记录头信息

记录头信息中包含的内容很多，我就不一一列举了，这里说几个比较重要的：

  • delete_mask：标识此条数据是否被删除。从这里可以知道，我们执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。
  • next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。在前面我也提到了，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
  • record_type：表示当前记录的类型，0 表示普通记录，1 表示 B+树非叶子节点记录，2 表示最小记录，3 表示最大记录

记录的真实数据

记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为：row_id、trx_id、roll_pointer，我们来看下这三个字段是什么。

  • row_id

如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id 不是必需的，占用 6 个字节。

  • trx_id

事务 id，表示这个数据是由哪个事务生成的。trx_id 是必需的，占用 6 个字节。

  • roll_pointer

这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

如果你熟悉 MVCC 机制，你应该就清楚 trx_id 和 roll_pointer 的作用了，如果你还不知道 MVCC 机制，可以看完这篇文章，一定要掌握，面试也很经常问 MVCC 是怎么实现的。

varchar(n) 中 n 最大取值为多少？

我们要清楚一点，MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。

也就是说，一行记录除了 TEXT、BLOBs 类型的列，限制最大为 65535 字节，注意是一行的总长度，不是一列。

知道了这个前提之后，我们再来看看这个问题：「varchar(n) 中 n 最大取值为多少？」

varchar(n) 字段类型的 n 代表的是最多存储的字符数量，并不是字节大小哦。

要算 varchar(n) 最大能允许存储的字节数，还要看数据库表的字符集，因为字符集代表着，1 个字符要占用多少字节，比如 ascii 字符集，1 个字符占用 1 字节，那么 varchar(100) 意味着最大能允许存储 100 字节的数据。

单字段的情况

前面我们知道了，一行记录最大只能存储 65535 字节的数据。

那假设数据库表只有一个 varchar(n) 类型的列且字符集是 ascii，在这种情况下，varchar(n) 中 n 最大取值是 65535 吗？

一行数据的最大字节数 65535，其实是包含「变长字段长度列表」和「NULL 值列表」所占用的字节数的。所以，我们在算 varchar(n) 中 n 最大值时，需要减去 storage overhead 占用的字节数。

这是因为我们存储字段类型为 varchar(n) 的数据时，其实分成了三个部分来存储：

  • 真实数据
  • 真实数据占用的字节数
  • NULL 标识，如果不允许为 NULL，这部分不需要

当然，我上面这个例子是针对字符集为 ascii 情况，如果采用的是 UTF-8，varchar(n) 最多能存储的数据计算方式就不一样了：

  • 在 UTF-8 字符集下，一个字符串最多需要三个字节，varchar(n) 的 n 最大取值就是 65532/3 = 21844。

上面所说的只是针对于一个字段的计算方式。

行溢出后，MySQL 是怎么处理的？

MySQL 中磁盘和内存交互的基本单位是页，一个页的大小一般是 16KB，也就是 16384字节，而一个 varchar(n) 类型的列最多可以存储 65532字节，一些大对象如 TEXT、BLOB 可能存储更多的数据，这时一个页可能就存不了一条记录。这个时候就会发生行溢出，多的数据就会存到另外的「溢出页」中。

如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。在一般情况下，InnoDB 的数据都是存放在「数据页」中。但是当发生行溢出时，溢出的数据会存放到「溢出页」中。

当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。大致如下图所示。

上面这个是 Compact 行格式在发生行溢出后的处理。

Compressed 和 Dynamic 这两个行格式和 Compact 非常类似，主要的区别在于处理行溢出数据时有些区别。

这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中。


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

在 MySQL 8.4 版本一共提供了 10 个引擎，每种引擎都有其特定的特性和用途，但相对而言比较常见的是以下几个：

特性 MyISAM Memory InnoDB Archive NDB
B+树索引 Yes Yes Yes No No
备份/按时间点恢复 Yes Yes Yes Yes Yes
集群数据库支持 No No No No Yes
聚簇索引 No No Yes No No
压缩数据 Yes No Yes Yes No
数据缓存 No N/A Yes No Yes
加密数据 Yes Yes Yes Yes Yes
外键支持 No No Yes No Yes
全文检索 Yes No Yes No No
地理空间数据类型支持 Yes No Yes Yes Yes
地理空间索引支持 Yes No Yes No No
哈希索引 No Yes No No Yes
索引缓存 Yes N/A Yes No Yes
锁的粒度 Table Table Row Row Row
MVCC No No Yes No No
复制支持 Yes Limited Yes Yes Yes
存储限制 256TB RAM 64TB None 384EB
T-tree 索引 No No No No Yes
事务 No No Yes No Yes

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

MyISAM

1）MyISAM 是基于 ISAM 引擎而来的，支持全文检索、数据压缩、空间函数，不支持事务和行级锁，只有表级别锁，它适用于 OLAP 场景，也就是分析类的，基本上都是读取，不会有什么写入动作的场景。

2）MyISAM 的索引也是 B+ 树，只是不像 InnoDB 那种叶子节点会存储完整的数据，MyISAM 的数据是独立于索引单独存储的，所以主键和非主键索引差别不大。

3）MyISAM 不支持崩溃后的安全恢复，而 InnoDB 有个 redolog 可以支持安全恢复。

4）MyISAM 写入性能差。因为锁的粒度太粗了，不支持行锁，只有表锁，所以写入的时候会对整张表加锁。不过有个并发插入的开关，开启之后当数据中间没有空洞的时候，也就是插入的新数据是从末尾插入时，读取数据是不会阻塞的。

InnoDB（MySQL 默认引擎）

1）InnoDB 支持事务，实现了四种标准的隔离级别，利用 MVCC 来支持高并发，默认事务隔离级别为可重复读，支持行锁，利用行锁+间隙锁提供可重复读级别下防止幻读的能力，支持崩溃后的数据安全恢复。

2）支持外键，不过一般互联网项目都不会用外键的，性能太差，利用业务代码来实现约束即可。

3）由于 InnoDB 使用行级锁定和支持事务，因此在并发性能方面表现较好，特别是在多个用户同时对数据库进行读写操作时。

4）InnoDB 的主键索引称为聚簇索引，也就是数据和索引是放在一起的，这与 MyISAM 有所不同，并且它的辅助索引(非主键索引)只存储索引值与主键，因此当辅助索引不能覆盖查询的列时，需要通过找到的主键再去聚簇索引查询数据，这个过程称之为回表。

MyISAM 和 InnoDB 适用场景

InnoDB 更适合需要高并发、事务处理和数据完整性保证的场景，例如电商平台、金融系统和社交网络等。

而 MyISAM 更适合读操作远多于写操作且对数据完整性要求不高的场景，例如内容管理系统、博客平台和报表系统等。


为什么在 MySQL 中不推荐使用多表 JOIN？

这里的多表往往指的是超过三个表才是多表，正常两个表 join 是没问题的！（但是也需要评估下量级和是否命中索引）

阿里的 Java 规范手册里也有一句话：“超过三个表禁止使用 Join”。

这是为什么呢？

其实数据量小都无所谓。但当数据量大的时候，影响就被放大了。如果让数据库来承担这个复杂的关联操作，需要对联接的每个表进行扫描、匹配和组合，消耗大量的 CPU 和内存资源。让复杂的关联操作占用了大量的数据库资源，会影响其他查询修改操作。

数据库往往是我们系统的弱点，很多情况下性能瓶颈都在数据库，因此我们需要尽量避免把压力放在数据库上。


MySQL 中 DELETE、DROP 和 TRUNCATE 的区别是什么？

DELETE

Delete 用于删除行数据，但保留表结构和相关的对象。

本质上这个删除其实就是给数据行打个标记，并不实时删除，因此 delete 之后，空间的大小不会变化。

而且 delete 操作会生成 binlog、redolog 和 undolog，所以如果删除全表使用 delete 的话，性能会比较差！ 但是它可以回滚。

DROP

Drop 用于完全删除数据库表，包括数据和结构。

在 InnoDB 中，每张表数据内容和索引都存储在一个以 .ibd 后缀的文件中，drop 就是直接把这个文件给删除了！还有一个 .frm 后缀的文件也会被删除，这个文件包含表的元数据和结构定义。

文件都删了，所以这个操作无法回滚，表空间会被回收，但是如果表存在系统共享表空间，则不会回收空间。

默认创建的表会有独立表空间，把 innodb_file_per_table 的值改为 OFF 后，就会被放到共享表空间中，即统一的 ibdata1 文件中。

TRUNCATE

Truncate 只删除数据，不会删除表结构和索引等其他结构。

Truncate 会对整张表的数据进行删除，且不会记录回滚等日志，所以它无法被回滚。

并且主键字段是自增的，使用 Truncate 删除后自增重新从 1 开始。


MySQL 中 DATETIME 和 TIMESTAMP 类型的区别是什么？

存储格式

  • DATETIME：存储的是具体的日期和时间，格式为 YYYY-MM-DD HH:MM:SS。它不依赖于时区信息，存储的是原始数据。
  • TIMESTAMP：存储的是自 1970-01-01 00:00:01 UTC 以来的秒数，格式为 YYYY-MM-DD HH:MM:SS。它依赖于时区信息，存储的是 UTC 时间。

时间范围

  • DATETIME：可以表示从 1000-01-01 00:00:00 到 9999-12-31 23:59:59 的时间范围。
  • TIMESTAMP：可以表示从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC 的时间范围（32 位整数表示的限制）。

时区处理

  • DATETIME：不考虑时区，它存储的是输入的日期和时间值，不进行任何时区转换。
  • TIMESTAMP：考虑时区，它存储的是 UTC 时间，插入和检索时会根据当前时区进行转换。这意味着在不同的时区中，TIMESTAMP 类型的数据会自动调整以显示本地时间。

默认值和自动更新

  • DATETIME：在 MySQL 5.6 及更早版本中，DATETIME 列不能有自动更新的默认值。在 MySQL 5.6 及以后版本中，可以使用 DEFAULT 和 ON UPDATE 子句来指定自动初始化和更新行为，但不像 TIMESTAMP 那么直观。
  • TIMESTAMP：在 MySQL 5.6 及更高版本中，TIMESTAMP 列可以有默认的当前时间戳 CURRENT_TIMESTAMP，并且可以使用 ON UPDATE CURRENT_TIMESTAMP 使其在行更新时自动更新为当前时间戳。这使得 TIMESTAMP 非常适合记录行的创建和修改时间。

数据库的三大范式是什么？

数据库的三大范式是数据库设计中常用的规范，它们的目的是减少数据冗余，提高数据的完整性和一致性，使得表的设计更清晰。

第一范式（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的基础上，消除非主键字段对主键的传递依赖。

不过现在业务上的表设计基本都是反范式的。当然不是说完全不遵守范式，而是适当的进行调整。

比如业务上经常需要冗余字段，减少联表查询，提升性能，特别是业务量比较大的公司，这种冗余是很有必要的！


MySQL 中 AUTO_INCREMENT 列达到最大值时会发生什么？

先说结论：在 MySQL 中，如果表定义的自增 ID 到达上限后，再申请下一个 ID，得到的值不变！

例如 int 是 4 个字节，上限是 2 的 32 次方 -1，即 2147483647，当到达这个值的之后，下次插入得到的还是这个值。

再列举下 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（无符号）。

在 MySQL 中存储金额数据，应该使用什么数据类型？

在数据库中业界常用两种类型来存储金额：bigint 和 decimal。

1）bigint 代码中用 long。

  • 范围：可以存储的整数范围为 -2^63 到 2^63 - 1（在 MySQL 中为 64 位有符号整数）
  • 存储空间：占用 8 字节（64 位）
  • 精度：精确存储整数，但不支持小数部分，存储的金额单位是分

2）decimal 代码中使用 BigDecimal。

  • 范围：可以存储的数字范围和小数位数由定义的精度和标度决定
  • 存储空间：存储空间取决于定义的精度和标度，存储较大数值时会占用更多空间
  • 精度：支持高精度的小数运算，精确存储定点数，一般用 decimal(18,6)，18 是总位数，6 是小数

long 类型保存到分，使得小数位（厘）的数据不好处理（需要手动处理，比较麻烦），因此精度不够高。

在高精度金额计算场景下不合适，例如有些三方支付系统是需要抽成的，例如千分之三、万分之一等等，这类的抽成要求精度比较高，例如 会得到 1234.5678 这样的金额，乘以 100 后仍然无法存储为精确整数，还是小数，存储则会丢失精度。

0.0078 看似很少，但抽成的笔数一多，比如一天 30 万笔，每笔少个 0.0078，每天的差额也得 2340 元！一年不得 85 万！

所以高精度的计算不推荐使用 bigint。

BigDecimal 则很适用于高精度金额场景，且非常灵活，只不过相对于 long 性能会差一些，但是在大部分业务上我个人认为可以忽略这个性能问题，除非是特殊场景要求极端性能，所以一般情况下我推荐使用 decimal。


什么是数据库的视图？

视图是一种虚拟表，它是基于 SQL 查询结果的，它本身并不存储数据，而是存储 SQL 查询。

视图可以包含一个或多个表的数据，并且可以对这些数据进行筛选、计算、排序等操作。

它的作用如下：

  • 简化复杂查询：视图可以将复杂的查询封装成一个简单的视图，使得用户在查询数据时更加方便。
  • 安全性：通过视图可以限制用户访问特定的表和列，保护敏感数据。例如，只允许用户查看某些列而不是整个表。
  • 数据抽象：视图提供了一种数据抽象层，用户可以通过视图获取需要的数据，而不必关心底层表的结构和关系。
  • 可重用性：定义一次视图，可以在多个查询中重复使用，减少代码冗余。

MySQL 中 EXISTS 和 IN 的区别是什么？

exists 和 in 都用于子查询，exists 检查子查询是否返回结果集，返回布尔值。in 检查某个值是否在子查询返回的结果集中，返回具体的值。

在性能上，由于两者机制的问题，在不同场景性能有所差异。

exists 的查询机制是循环外表，通过外表的每行数据去内表查询是否有匹配的值，一旦找到符合条件的记录，此次内表子查询就会停止执行。然后再通过下一个外表的值来查询，如此循环。所以它适合子查询中表比外表大且有索引的场景。

而 in 子查询在执行时会先执行子查询并生成结果集，然后将结果集与外部查询的列进行比较，所以它适合子查询记录少，且主查询表大有索引的场景。

简单总结：外层查询表量级小于子查询表，则用 exists，外层查询表量级大于子查询表，则用 in ，如果外层和子查询表差不多，则都行。建议具体情况还是以 explain 分析为主。


参考链接

执行一条 select 语句，期间发生了什么？ | 小林coding (xiaolincoding.com)

MySQL 一行记录是怎么存储的？ | 小林coding (xiaolincoding.com)

数据库的高级查询四：Having子句（Having子句需要和GROUP BY子句结合，才能使用）_sql having必须和group by-CSDN博客