我的书签
添加书签
移除书签用 EXPLAIN 查看 JOIN 查询的执行计划
SQL 查询中可能会使用 JOIN 进行表连接，可以通过 EXPLAIN 语句来查看 JOIN 查询的执行计划。
示例
我们这里准备一个简单的示例，帮助你理解使用 EXPLAIN 解读 JOIN 查询的执行计划。
1. > drop table if exists t1;
2. > create table t1 (id int,ti tinyint unsigned,si smallint,bi bigint unsigned,fl float,dl double,de decimal,ch char(20),vch varchar(20),dd date,dt datetime);
3. > insert into t1 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11');
4. > insert into t1 values(2,2,5,2,2252.05,225205,2252.05,'bye','sub query','2022-04-28','2022-04-28 22:40:11');
5. > insert into t1 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11');
6. > insert into t1 values(4,7,1,5,4715.22,471522,4715.22,'good morning','my subquery','2022-04-28','2022-04-28 22:40:11');
7. > insert into t1 values(5,1,2,6,51.26,5126,51.26,'byebye',' is subquery?','2022-04-28','2022-04-28 22:40:11');
8. > insert into t1 values(6,3,2,1,632.1,6321,632.11,'good night','maybe subquery','2022-04-28','2022-04-28 22:40:11');
9. > insert into t1 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11');
10. > insert into t1 values(8,7,5,8,8758.00,875800,8758.11,'nice to meet','just subquery','2022-04-28','2022-04-28 22:40:11');
11. > insert into t1 values(9,8,4,9,9849.312,9849312,9849.312,'see you','subquery','2022-04-28','2022-04-28 22:40:11');
13. > drop table if exists t2;
14. > create table t2 (id int,ti tinyint unsigned,si smallint,bi bigint unsigned,fl float,dl double,de decimal,ch char(20),vch varchar(20),dd date,dt datetime);
15. > insert into t2 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11');
16. > insert into t2 values(2,2,5,2,2252.05,225205,2252.05,'bye','sub query','2022-04-28','2022-04-28 22:40:11');
17. > insert into t2 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11');
18. > insert into t2 values(4,7,1,5,4715.22,471522,4715.22,'good morning','my subquery','2022-04-28','2022-04-28 22:40:11');
19. > insert into t2 values(5,1,2,6,51.26,5126,51.26,'byebye',' is subquery?','2022-04-28','2022-04-28 22:40:11');
20. > insert into t2 values(6,3,2,1,632.1,6321,632.11,'good night','maybe subquery','2022-04-28','2022-04-28 22:40:11');
21. > insert into t2 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11');
22. > insert into t2 values(8,7,5,8,8758.00,875800,8758.11,'nice to meet','just subquery','2022-04-28','2022-04-28 22:40:11');
23. > insert into t2 values(9,8,4,9,9849.312,9849312,9849.312,'see you','subquery','2022-04-28','2022-04-28 22:40:11');
Hash Join
在 Hash Join 操作中，MatrixOne 首先读取表 t1 与 t2 中相对较小的一个表对其中每个需要被连接的值使用哈希函数，得到一个哈希表；然后对另一个表的每一行进行扫描并计算哈希值，与上一步生成的哈希表进行比对，如果有符合连接标准的值，则根据连接谓词生成一个新的连接表。
MatrixOne 中的 Hash Join 算子是多线程的，并且可以并发执行。
下面是一个 Hash Join 示例：
1. > SELECT /*+ HASH_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id = t2.id;
2. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
3. | id | ti | si | bi | fl | dl | de | ch | vch | dd | dt | id | ti | si | bi | fl | dl | de | ch | vch | dd | dt |
4. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
5. | 1 | 1 | 4 | 3 | 1113.32 | 111332 | 1113 | hello | subquery | 2022-04-28 | 2022-04-28 22:40:11 | 1 | 1 | 4 | 3 | 1113.32 | 111332 | 1113 | hello | subquery | 2022-04-28 | 2022-04-28 22:40:11 |
6. | 2 | 2 | 5 | 2 | 2252.05 | 225205 | 2252 | bye | sub query | 2022-04-28 | 2022-04-28 22:40:11 | 2 | 2 | 5 | 2 | 2252.05 | 225205 | 2252 | bye | sub query | 2022-04-28 | 2022-04-28 22:40:11 |
7. | 3 | 6 | 6 | 3 | 3663.21 | 366321 | 3663 | hi | subquery | 2022-04-28 | 2022-04-28 22:40:11 | 3 | 6 | 6 | 3 | 3663.21 | 366321 | 3663 | hi | subquery | 2022-04-28 | 2022-04-28 22:40:11 |
8. | 4 | 7 | 1 | 5 | 4715.22 | 471522 | 4715 | good morning | my subquery | 2022-04-28 | 2022-04-28 22:40:11 | 4 | 7 | 1 | 5 | 4715.22 | 471522 | 4715 | good morning | my subquery | 2022-04-28 | 2022-04-28 22:40:11 |
9. | 5 | 1 | 2 | 6 | 51.26 | 5126 | 51 | byebye | is subquery? | 2022-04-28 | 2022-04-28 22:40:11 | 5 | 1 | 2 | 6 | 51.26 | 5126 | 51 | byebye | is subquery? | 2022-04-28 | 2022-04-28 22:40:11 |
10. | 6 | 3 | 2 | 1 | 632.1 | 6321 | 632 | good night | maybe subquery | 2022-04-28 | 2022-04-28 22:40:11 | 6 | 3 | 2 | 1 | 632.1 | 6321 | 632 | good night | maybe subquery | 2022-04-28 | 2022-04-28 22:40:11 |
11. | 7 | 4 | 4 | 3 | 7443.11 | 744311 | 7443 | yes | subquery | 2022-04-28 | 2022-04-28 22:40:11 | 7 | 4 | 4 | 3 | 7443.11 | 744311 | 7443 | yes | subquery | 2022-04-28 | 2022-04-28 22:40:11 |
12. | 8 | 7 | 5 | 8 | 8758 | 875800 | 8758 | nice to meet | just subquery | 2022-04-28 | 2022-04-28 22:40:11 | 8 | 7 | 5 | 8 | 8758 | 875800 | 8758 | nice to meet | just subquery | 2022-04-28 | 2022-04-28 22:40:11 |
13. | 9 | 8 | 4 | 9 | 9849.312 | 9849312 | 9849 | see you | subquery | 2022-04-28 | 2022-04-28 22:40:11 | 9 | 8 | 4 | 9 | 9849.312 | 9849312 | 9849 | see you | subquery | 2022-04-28 | 2022-04-28 22:40:11 |
14. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
15. 9 rows in set (0.00 sec)
17. mysql> EXPLAIN SELECT /*+ HASH_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id = t2.id;
18. +------------------------------------+
19. | QUERY PLAN |
20. +------------------------------------+
21. | Project |
22. | -> Join |
23. | Join Type: INNER |
24. | Join Cond: (t1.id = t2.id) |
25. | -> Table Scan on db1.t1 |
26. | -> Table Scan on db1.t2 |
27. +------------------------------------+
28. 6 rows in set (0.01 sec)
MatrixOne 会按照以下顺序执行 Hash Join 算子：
- 分别并行扫描表 t2 和 t1。
- 执行 JOIN 的过滤查询：
(t1.id = t2.id)。 - 执行 INNER JOIN。
