mysql - MariaDB/MySQL 跨表连接时是否缓存键值关系？

Question

假设我们有一个包含列(id、username、team_id)的用户表。

我们还有一个包含列(id、名称)的团队表。

假设我们有很多用户，并且团队规模通常很大。

SELECT users.id, users.username, teams.name 
FROM users 
LEFT JOIN teams ON users.team_id = teams.id;

数据库引擎是否缓存诸如team_id(14) = "Team Yellow"之类的关联，或者它是否在每次连接时从磁盘获取数据(我实际上无法想象)？

如果此处未应用缓存，则最好仅获取用户，然后聚合 team_ids 并使用第二个查询来获取团队数据。

编辑:我实际上是在问，因为看起来 Laravel 正是在这里这样做的 (3:37):https://youtu.be/bZlvzvGpCEE?t=217

Answer 1

在 InnoDB 中，通过其 buffer_pool，主要的“缓存”位于 block 级别。一个 block 包含(通常)一百行数据或索引。

通常JOIN是通过“嵌套循环连接”(NLJ) 完成的。这意味着代码开始读取一个表(可能使用索引)，然后重复访问“下一个”表。优化器查看统计信息(等)来决定从哪个表开始。请参阅 EXPLAIN SELECT ... 以查看它选择的顺序。

在您的简单示例中，从任一表开始都差不多。这假设您拥有索引ON users.team_id = team.id所需的内容:如果从用户到团队，则id需要一个索引。如果采用其他方式，则 team_id 需要一个索引。

其中一个区 block 中的某个位置恰好是 team_id(14) = "Team Yellow"，但这实际上是无关紧要的。

有时使用的另一个优化是 BNL， block 嵌套查找(？)。这是第二个表所需的整个查找被转换为散列并在查询的生命周期内保存在内存中的地方。

“数据”保存在按主键排序的B+树中。二级索引本质上类似于数据 BTree，只不过“数据”只是 PK 的列。

有两种使用 B+Tree 的方法:向下钻取到特定值，或向前(或向后)按索引顺序走到“下一个”项目。在查询评估中经常使用其中一个或两者。

这就是全部内容。