TIDB查询优化

1、小表驱动大表

1.1、MySQL和TIDB中小表驱动大表使用区别

由于底层执行引擎的不同，TiDB 和 MySQL 在处理 Join 时的核心算法完全不同，这导致了“小表驱动大表”这一原则在两者中的含义和具体 SQL 写法截然相反。

 

• MySQL 中的原则：真正的小表驱动大表（小表在左，大表在右）

MySQL（主要指 InnoDB 引擎）处理 Join 的主力算法是 Nested Loop Join（嵌套循环连接）。

1. MySQL 中的 SQL 写法：

• 小表 LEFT JOIN 大表
• 效果：小表作为左表（驱动表），循环去大表（被驱动表）里查数据。

 

2. 执行逻辑：

1. 从驱动表（左表）中取出一条数据。
2. 拿着这条数据的关联字段，去被驱动表（右表）中通过索引查找匹配。
3. 循环步骤 1 和 2，直到驱动表的所有数据遍历完。

 

3. 为什么需要小表驱动大表？在这种嵌套循环中，驱动表会被全表扫描（或走索引扫描）一遍，而被驱动表会被查找 N 次（N 为驱动表返回的行数）。

• 如果小表做驱动表，循环次数少，去大表查索引的次数就少。
• 如果大表做驱动表，循环次数极多，去查小表的次数就极多，极其消耗 CPU 和 IO。

 

• TiDB 中的原则：本质是小表建 Hash 表，大表探测（小表在右，大表在左）

TiDB 是分布式数据库，面临的是海量数据。它的主力 Join 算法是 Hash Join（哈希连接）。

1. TiDB 中的 SQL 写法：

TIDB和MySQL的写法刚好相反，小表在右，大表在左。

• 大表 LEFT JOIN 小表
• 效果：小表作为右表，被读入内存建 Hash 表（Build 端）；大表作为左表，流式读取去探测（Probe 端）。

2. 执行逻辑：

1. Build（构建）阶段：把 右表（被驱动表） 的数据全部读出来，在 TiDB Server 的内存里构建成一个 Hash 表。
2. Probe（探测）阶段：读取 左表（驱动表） 的数据，逐行去刚才建好的 Hash 表里探测匹配。

3. 为什么 TiDB 需要“大表在左，小表在右”？ 在 Hash Join 中，作为 Build 端的表（右表）必须被完整加载到内存中。

• 如果右表是小表，内存轻松放下，Hash 表建得快，大表流式读取去探测，效率极高。
• 如果右表是大表，内存放不下，TiDB 会被迫使用磁盘做 Grace Hash Join，性能断崖式下跌。

 

在 TIDB 执行计划中在 Build 侧的表表示被数据库判断为是小表，而 Probe 侧表示被判断为大表。如果优化器判断不准确，可以通过 SQL Hints（优化器提示） 来强制指定哪张表作为“小表”（驱动表/Build 端），哪张表作为“大表”（被驱动表/Probe 端）。

 

• 核心对比总结

为了清晰对比，我们假设 orders 是大表（1亿行），users 是小表（10万行）。

 

对比维度	MySQL (单机数据库)	TiDB (分布式数据库)
主力 Join 算法	Nested Loop Join (嵌套循环)	Hash Join (哈希连接)
性能瓶颈点	驱动表太大 -> 循环次数过多	Build端(右表)太大 -> 内存放不下
LEFT JOIN 推荐写法	小表 LEFT JOIN 大表	大表 LEFT JOIN 小表
原则核心思想	用小表循环，减少查大表的次数	用小表建内存Hash表，大表流式探测

 

如果你用的是 INNER JOIN（内连接），并不需要操心写法，因为无论是 MySQL 还是 TiDB，它们的优化器（CBO）都非常聪明，都会自动帮你调整表的执行顺序，选择最优的那一方作为驱动表/Build端，因为 INNER JOIN 满足交换律（A JOIN B 等同于 B JOIN A）， 

• 在 MySQL 中写 INNER JOIN，优化器会自动把小表放前面做驱动表。
• 在 TiDB 中写 INNER JOIN，优化器会自动把小表放后面做 Build 端。