MySQL 数据在连接缓冲区是怎么存储的?

MySQL 连接缓冲区(Join Buffer)是用于提升多表连接查询的速度的,连接查询的执行简化逻辑如下:

  • 从存储引擎中读取驱动表的中的记录,把满足条件的记录存入连接缓冲区
  • 当连接缓冲区满时,循环从存储引擎读取被驱动表的记录
  • 每读取一条被驱动表的记录,遍历临时存放到连接缓冲区中的驱动表的记录
  • 判断被驱动表驱动表的记录是否匹配,如果匹配,且被驱动表是连接操作的最后一个表,则把匹配的记录发送给客户端(只发送客户端需要的字段)

上面的流程中提到了需要把驱动表的记录存入连接缓冲区,那么记录在连接缓冲区中是怎么存储的呢? 接下来会分为 3 个部分进行说明:

  • 缓冲区链表
  • 缓冲区中一条记录的组成
  • 表中字段内容的存储格式

1. 缓冲区链表

当一条包含 JOIN 的连接语句中只包含 2 个表时,只会有一个缓冲区;如果包含 N 个表(N > 2),则会有 N - 1 个缓冲区,N - 1 个缓冲区会组成缓冲区链表

  • 每个缓冲区都会有 prev cache 和 next cache 指针
  • prev cache 指向前一个缓冲区
  • next cache 指向后一个缓冲区
  • 第一个缓冲区的 prev cache 指针为 NULL
  • 最后一个缓冲区的 next cache 指针为 NULL

缓冲区链表示意图如下:

举个例子说明往连接缓冲区写入数据的过程: 假设有 t1、t5、t4、t3 四个表进行连接查询,执行计划中表的顺序也是 t1、t5、t4、t3,在连接执行过程中,有 3 个连接缓冲区:t5 表的缓冲区、t4 表的缓冲区、t3 表的缓冲区,执行时是把驱动表的记录放入被驱动表的缓冲区,执行流程如下:

  • 从存储引擎读取 t1 表(驱动表)的数据,把符合 WHERE 条件(这一步只会判断 WHERE 条件,不会判断 ON 连接条件)的记录写入 t5 表的连接缓冲区(t5 表相对于 t1 表是被驱动表
  • t5 表的连接缓冲区满(被 t1 表的记录填满了),然后从存储引擎读取 t5 表的记录
  • 每读取一条 t5 表中的记录, 判断该记录是否符合 WHERE 条件,不符合则继续从存储引擎读取 t5 表的下一条记录 如果符合 WHERE 条件,则从 t5 表的连接缓冲区读取一条 t1 表的记录,然后判断 t1 表的记录和 t5 表的记录是否匹配,不匹配则继续读取缓冲区中 t1 表的下一条记录 如果匹配,则把当前读到的 t5 表的记录写入 t4 表的连接缓冲区(t4 表相对于 t5 表是被驱动表
  • 按照上面的步骤循环读取 t5 表中的记录,直到 t4 表的连接缓冲区满(被 t5 表的记录填满了),然后从存储引擎读取 t4 表的记录
  • 每读取一条 t4 表中的记录, 判断该记录是否符合 WHERE 条件,不符合则继续从存储引擎读取 t4 表的下一条记录 如果符合 WHERE 条件,则从 t4 表的连接缓冲区读取一条 t5 表的记录,然后判断 t5 表的记录和 t4 表的记录是否匹配,不匹配则继续读取缓冲区中 t5 表的下一条记录 如果匹配,则把当前读到的 t4 表的记录写入 t3 表的连接缓冲区(t3 表相对于 t4 表是被驱动表
  • 按照上面的步骤循环读取 t4 表中的记录,直到 t3 表的连接缓冲区满(被 t4 表的记录填满了),然后从存储引擎读取 t3 表的记录
  • 每读取一条 t3 表的记录 判断该记录是否符合 WHERE 条件,不符合则继续从存储引擎读取 t3 表的下一条记录 如果符合 WHERE 条件,则从 t3 表的连接缓冲区读取一条 t4 表的记录,然后判断 t4 表的记录和 t5 表的记录是否匹配,不匹配则继续读取缓冲区中 t4 表的下一条记录 如果匹配,则把 4 个表组成的完整的记录发送给客户端(只发送 SELECT 语句中客户端需要的字段)
  • 按照上面的步骤循环读取 t3 表的记录,并发送给客户端,直到读取完所有表中符合条件的记录

发送到客户端的记录都会先写入网络缓冲区,等到网络缓冲区写满之后,才会把整个网络缓冲区的所有数据一次性发送给客户端

2. 缓冲区中一条记录的组成

缓冲区中的记录是一条挨着一条存储的,每一条记录又包含哪些内容呢?先看一张图:

从上图可见,一条记录包含 2 大块内容:记录长度、记录内容,其中记录长度部分存储的就是记录内容这部分的所有内容的长度之和

记录长度 这个字段如果存在,就是在记录的最开始,不过它只有在使用 BKA 算法,或者是记录中存储了 匹配标记 时才会存在

记录内容 包含 4 部分,下面详细说明:

  • 前一个缓冲区关联记录的 Offset:如果连接缓冲区不是缓冲区链表中的第一个(也就是说还有前一个缓冲区),那么当前缓冲区中的每一条记录,都会关联到前一个缓冲区中的一条记录,这个字段存储的内容就是当前缓冲区的当前记录关联的前一个缓冲区中的记录,在前一个缓冲区中的 Offset,以 第 1 节 t1、t5、t4、t3 表的执行为例,对于 t4 表的缓冲区,它有前一个缓冲区(t5 表的缓冲区),那么 t4 表缓冲区中的每一条记录(t5 表的记录)中,都会保存一个该记录关联的 t5 表缓冲区中的记录(t1 表的记录)的 Offset,实际上就是连接缓冲区中 t5 表的记录中会保存记录匹配的 t1 表的那条记录在缓冲区中的 Offset(在执行连接的过程中会用到这个 Offset)
  • 标记字段
    • 匹配标记:这个字段可能不会存在,但是只要存在,就一定是在标记字段部分的最前面。该字段在两种情况下存在:
      • 连接缓冲区所属的那个表,是外连接的第一个内表,因为外连接中,外表(驱动表)的某些记录,内表(被驱动表)中可能会没有记录和它们匹配,那么外表中的这种记录(就是存储在第一个内表的连接缓冲区中)就需要有一个匹配标记,来标识该记录是否被内表中记录匹配过,如果没有的话,在连接处理结束之前,会把匹配标记的值为 false 的记录,对应的内表中的字段都填充上 NULL
      • 连接缓冲区所属的那个表,是使用首次匹配的半连接的第一个内表,因为半连接使用首次匹配时,对于外表(驱动表)的某条记录来说,只要有一条内表(被驱动表)的记录和它匹配过,那么内表后续读取的记录,不管是否和外表的该记录匹配都会被忽略,所以外表的这种记录(就是存在第一个内表的连接缓冲区中)就需要一个匹配标记,来标识该记录是否被内表中的记录匹配过,如果匹配过,则会忽略和该记录匹配的内表记录(当然,不匹配的记录肯定是会被忽略的)
    • 字段 NULL 标记:用于标识是否要读取行缓冲区(TABLE::record[0])中的 NULL 标识区域的内容
    • 记录 NULL 标记:用于标记外连接的某些记录对应的内表所有字段都需要用 NULL 来填充(注意和匹配标记的区域,只有当匹配标记字段存在,并且基值为 true 时,才会设置记录 NULL 标记这个字段的值为 true)
  • 数据字段区域:留在下一节详细说明
  • 数据字段在记录中的 Offset:每个数据字段在记录中的 Offset,是连接缓冲区中数据字段开始处的地址,减去标记字段区域的开始处得到的数值(如果标记字节区域的 3 个标记字段都不存在,那就是减去第一个数据字段开始处的址值得到的数据)

什么叫数据字段?是为了和标记字段区分取的个名字,实际就是往连接缓冲区写入的表里面的那些字段

3. 数据字段内容的存储格式

连接缓冲区中不同的数据字段,存储方式也不太一样,还是先上一张图:

这是个示意图,只是表示各类型的字段在连接缓冲区中是怎么存储的,并不是说字段是按这种顺序存储的

接下来详细说说每种类型的字段是怎么存储的:

  • varchar 1:len 1 表示 varchar、varbinary 字段的内容长度需要用 1 字节存储
  • varchar 2:len 2 表示 varchar、varbinary 字段的内容长度需要用 2 字节存储
  • char:len 2 表示 char、binary 字段的内容需要用 2 字节存储,char、binary 字段在 InnoDB 存储引擎中是用固定长度存储的,实际内容长度小于字段定义长度时,会在实际内容后面补上空格,但是当 char、binary 字段存入连接缓冲区时,为了节省空间,统一用 2 字节来保存实际内容的长度,并且会把存储引擎返回的内容中补充的空格给去掉,等到把记录发送给客户端时再补上相应的空格
  • 缓冲区第 1~n-1 条记录的 blob 字段:即缓冲区第 1 条记录 ~ 倒数第二条记录的 blob 字段 len 1~4 表示 blob 字段的内容长度需要用 1~4 字节存储(具体内容第 4 小节说明) blob 数据表示会把 blob 字段的内容从存储引擎的内存拷贝到连接缓冲区
  • 缓冲区最后一条记录的 blob 字段:len 1~4 表示 blob 字段内容的长度需要用 1~4 字节存储 blob 指针表示只会把存储引擎内存中存储 blob 字段内容的开始处的地址拷贝到连接缓冲区中,而不会拷贝实际的内容到连接缓冲区,以减少一次不必要的拷贝
  • 其它类型的字段:除了 varchar、char、blob 类型的字段,其它字段因为是固定长度,根据字段本身的信息就可以知道内容长度了,所以在连接缓冲中不需要存储长度,只需要存储内容即可

4. 说说 blob 字段

第 3 小节中提到的 blob 字段,并不单指定义表结构时,类型为 BLOB 的字段,在数据库的内部实现,有多种类型的字段是实现为 blob,同时,blob 类型的字段,根据可存储的内容的最大长度不同,细分为 4 种,接下来详细说说:

  • tinyblob:内容长度用 1 字节存储,在内部实现中,TINYTEXTTINYBLOB 这两种字段类型都是 tinyblob,也都是用 Field_blob 类表示的
  • blob:内容长度用 2 字节存储,在内部实现中,TEXTBLOB 这两种字段类型都是 blob,也都是用 Field_blob 类表示的
  • mediumblob:内容长度用 3 字节存储,在内部实现中,MEDIUMTEXTMEDIUMBLOB 这两种字段都是 mediumblob,也都是用 Field_blob 类表示的 另外还有 3 种不常用的,可能是为了兼容老版本 MySQL 而保留的类型,也在内部实现为 mediumblob:LONGVARBINARY、LONGVARCHAR、LONG
  • longblob:内容长度用 4 字节存储,在内部实现中,LONGBLOBLONGTEXTJSONspatial_type 这四种字段都是 longblob,也都是用 Field_blob 类表示的

4.1 关于 LONGVARBINARY、LONGVARCHAR、LONG

对于 LONGVARBINARYLONGVARCHAR 在 5.7 和 8.0 的官方文档中都找到了一段说明(LONG 字段暂时没有找到相关的说明),如下:

MySQL Connector/ODBC defines BLOB values as LONGVARBINARY and TEXT values as LONGVARCHAR

意思是:MySQL Connector/ODBC 定义 BLOB 字段时,会使用 LONGVARBINARY 关键字,定义 TEXT 字段时,会使用 LONGVARCHAR 关键字

不过在 MySQL 5.7.35 中实测,对于 LONGVARBINARYLONGVARCHARLONG 都会实现为 MEDIUMBLOB 而不是 BLOB

4.2 关于 spatial_type

spatial_type 比较特殊,它并不是某一个类型,而是一种类型的统称,这种类型在使用时实际对应多个具体的字段类型,分类两类:

保存单个值的类型:GEOMETRY、POINT、LINESTRING、POLYGON

保存多个值的类型:GEOMETRYCOLLECTION、MULTIPOINT、MULTILINESTRING、MULTIPOLYGON




欢迎扫码关注公众号,我们一起学习更多 MySQL 知识: