JOIN 查询的执行计划 比较

作者： TiDB_ 猫原文来源：https://tidb.net/blog/cf459d89

【是否原创】 是

【首发渠道】TiDB 社区

【首发渠道链接】其他平台首发请附上对应链接

【正文】

在 TiDB 中，SQL 优化器需要确定数据表的连接顺序，且要判断对于某条特定的 SQL 语句，哪一种 Join 算法最为高效。（ Index Join ， Hash Join , Merge Join ）

🎯推荐使用场景：

Index Join 算法 ：预计需要连接的行数较少（一般小于 1 万行），内存消耗较小

Hash Join ：需要连接的行数很多，运行速度会比 Index Join 快, 算子是多线程的，并且可以并发执行。

Merge Join : 一种特殊的 Join 算法 , 当两个关联表要 Join 的字段需要按排好的顺序读取时。占用的内存要远低于 Hash Join， 但不能并发执行

举例来说明执行顺序，重点关注

🎯执行顺序

EXPLAIN SELECT /*+ INL_JOIN(t1, t2) */ * FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id;

Index Join ：TableReader_28(Build) 算子首先读取表 t1 ，然后根据在 t1 中匹配到的每行数据，依次探查表 t2 中的数据

EXPLAIN SELECT /*+ HASH_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id = t2.id;

Hash Join 按照以下顺序执行 HashJoin_27 算子：

1. 将 Build 端数据缓存在内存中。

2. 根据缓存数据在 Build 端构造一个 Hash Table。

3. 读取 Probe 端的数据。

4. 使用 Probe 端的数据来探查 Hash Table。

5. 将符合条件的结果返回给用户。

EXPLAIN SELECT /*+ MERGE_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id = t2.id;

Merge Join 执行顺序：

1. 从 Build 端把一个 Join Group 的数据全部读取到内存中。

2. 读取 Probe 端的数据。

3. 将 Probe 端的每行数据与 Build 端的一个完整 Join Group 比较，依次查看是否匹配（除了满足等值条件以外，还有其他非等值条件，这里的“匹配”主要是指查看是否满足非等值条件）。Join Group 指的是所有 Join Key 上值相同的数据。

4. 

🎯性能受以下系统变量影响

Index Join 算法

tidb_index_join_batch_size（默认值：25000）- index lookup join 操作的 batch 大小。

tidb_index_lookup_join_concurrency（默认值：4）- 可以并发执行的 index lookup 任务数。

Hash Join 算法：

tidb_mem_quota_query（默认值：1GB）- 如果某条查询的内存消耗超出了配额，TiDB 会尝试将 Hash Join 的 Build 端移到磁盘上以节省内存。

tidb_hash_join_concurrency（默认值： 5 ）- 可以并发执行的 Hash Join 任务数量。

💥注意：

有时候 SQL 优化器会选择性能较差的算法，这时候如果需要频繁调用这个查询，建议通过执行计划管理的方式将 Hint 与 SQL 语句绑定，这样要比在发送给 TiDB 的 SQL 语句中插入 Hint 更容易管理

🔥🔥🔥执行计划绑定 (SQL Binding)（从 V4.0 版本开始使用），tidb 的这个功能使用起来比 oracle 中要方便些

1、目前支持的可创建执行计划绑定的 SQL 类型 (BindableStmt) 包括：SELECT，DELETE，UPDATE 和带有 SELECT 子查询的 INSERT/REPLACE

2、隐式作用域 SESSION ，一般来说使用 GLOBAL 作用域的绑定

3、如果存在多个可匹配的执行计划，优化器会从中选择代价最小的一个进行绑定

4、创建绑定时，TiDB 要求 session 处于某个数据库上下文中，也就是执行过 use ${database} 或者客户端连接时指定了数据库。

5、创建新的绑定会删除之前所有绑定（对于一条标准化 SQL）

CREATE [GLOBAL | SESSION] BINDING FOR BindableStmt USING BindableStmt;

比如：

create global binding for

select * from t1, t2 where t1.id = t2.id

using

select /*+ merge_join(t1, t2) */ * from t1, t2 where t1.id = t2.id;

还有

CREATE BINDING FOR SELECT * FROM t WHERE a > 1 USING SELECT * FROM t use index(idx) WHERE a > 2;