Description:
Description:
When using join buffer (hash join), the explain execution plan is inconsistent with MySQL's execution order of SQL when using explain analyze
mysql> analyze table sbtest1;
+--------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| test.sbtest1 | analyze | status | OK |
+--------------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> analyze table sbtest2;
+--------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| test.sbtest2 | analyze | status | OK |
+--------------+---------+----------+----------+
1 row in set (0.00 sec)
explain 1：
mysql> EXPLAIN SELECT * FROM sbtest1 JOIN sbtest2 ON (sbtest1.k = sbtest2.k);
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+
| 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 98712 | 100.00 | NULL |
| 1 | SIMPLE | sbtest2 | NULL | ALL | NULL | NULL | NULL | NULL | 986400 | 10.00 | Using where; Using join buffer (hash join) |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
explain 2：
mysql> EXPLAIN analyze SELECT * FROM sbtest1 JOIN sbtest2 ON (sbtest1.k = sbtest2.k);
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (sbtest2.k = sbtest1.k) (cost=9736969017.40 rows=9736951825) (actual time=345.360..76310.340 rows=1000000000 loops=1)
-> Table scan on sbtest2 (cost=0.17 rows=986400) (actual time=0.034..362.477 rows=1000000 loops=1)
-> Hash
-> Table scan on sbtest1 (cost=10215.95 rows=98712) (actual time=0.057..27.898 rows=100000 loops=1)
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (5 min 7.46 sec)
explain 1 It uses sbtest1 as the drive table
explain 2 It uses sbtest2 as the drive table，
so The two execution plans are inconsistent
How to repeat:
mysql> show create table sbtest1;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table sbtest2;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest2 | CREATE TABLE `sbtest2` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
EXPLAIN SELECT * FROM sbtest1 JOIN sbtest2 ON (sbtest1.k = sbtest2.k);
EXPLAIN analyze SELECT * FROM sbtest1 JOIN sbtest2 ON (sbtest1.k = sbtest2.k);
Description: Description: When using join buffer (hash join), the explain execution plan is inconsistent with MySQL's execution order of SQL when using explain analyze mysql> analyze table sbtest1; +--------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+---------+----------+----------+ | test.sbtest1 | analyze | status | OK | +--------------+---------+----------+----------+ 1 row in set (0.01 sec) mysql> analyze table sbtest2; +--------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+---------+----------+----------+ | test.sbtest2 | analyze | status | OK | +--------------+---------+----------+----------+ 1 row in set (0.00 sec) explain 1： mysql> EXPLAIN SELECT * FROM sbtest1 JOIN sbtest2 ON (sbtest1.k = sbtest2.k); +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+ | 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 98712 | 100.00 | NULL | | 1 | SIMPLE | sbtest2 | NULL | ALL | NULL | NULL | NULL | NULL | 986400 | 10.00 | Using where; Using join buffer (hash join) | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) explain 2： mysql> EXPLAIN analyze SELECT * FROM sbtest1 JOIN sbtest2 ON (sbtest1.k = sbtest2.k); +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (sbtest2.k = sbtest1.k) (cost=9736969017.40 rows=9736951825) (actual time=345.360..76310.340 rows=1000000000 loops=1) -> Table scan on sbtest2 (cost=0.17 rows=986400) (actual time=0.034..362.477 rows=1000000 loops=1) -> Hash -> Table scan on sbtest1 (cost=10215.95 rows=98712) (actual time=0.057..27.898 rows=100000 loops=1) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (5 min 7.46 sec) explain 1 It uses sbtest1 as the drive table explain 2 It uses sbtest2 as the drive table， so The two execution plans are inconsistent How to repeat: mysql> show create table sbtest1; +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | sbtest1 | CREATE TABLE `sbtest1` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table sbtest2; +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | sbtest2 | CREATE TABLE `sbtest2` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) EXPLAIN SELECT * FROM sbtest1 JOIN sbtest2 ON (sbtest1.k = sbtest2.k); EXPLAIN analyze SELECT * FROM sbtest1 JOIN sbtest2 ON (sbtest1.k = sbtest2.k);