大佬说的对，同意+1
短数据的explain
mysql> explain select t1.id id1, t1.name name1, t2.id id2, t2.name name2 from t1,t2 where t1.name = t2.name;
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------+
| HashJoin_12 | 12500.00 | root | | inner join, equal:[eq(Column#5, Column#6)] |
| ├─Projection_17(Build) | 10000.00 | root | | test.t2.id, test.t2.name, cast(test.t2.name, double BINARY)->Column#6 |
| │ └─TableReader_19 | 10000.00 | root | | data:TableFullScan_18 |
| │ └─TableFullScan_18 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─Projection_14(Probe) | 10000.00 | root | | test.t1.id, test.t1.name, cast(test.t1.name, double BINARY)->Column#5 |
| └─TableReader_16 | 10000.00 | root | | data:TableFullScan_15 |
| └─TableFullScan_15 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------+
7 rows in set (0.00 sec)
长数据的explain
mysql> explain select t1.id id1, t1.name name1, t2.id id2, t2.name name2 from t1,t2 where t1.name = t2.name;
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------+
| HashJoin_12 | 12500.00 | root | | inner join, equal:[eq(Column#5, Column#6)] |
| ├─Projection_17(Build) | 10000.00 | root | | test.t2.id, test.t2.name, cast(test.t2.name, double BINARY)->Column#6 |
| │ └─TableReader_19 | 10000.00 | root | | data:TableFullScan_18 |
| │ └─TableFullScan_18 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─Projection_14(Probe) | 10000.00 | root | | test.t1.id, test.t1.name, cast(test.t1.name, double BINARY)->Column#5 |
| └─TableReader_16 | 10000.00 | root | | data:TableFullScan_15 |
| └─TableFullScan_15 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------+
7 rows in set (0.01 sec)
长数据转signed后的explain
mysql> explain select t1.id id1, t1.name name1, t2.id id2, t2.name name2 from t1,t2 where cast(t1.name as signed) = t2.name;
+-------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------+
| Projection_10 | 3.75 | root | | test.t1.id, test.t1.name, test.t2.id, test.t2.name |
| └─HashJoin_12 | 3.75 | root | | inner join, equal:[eq(test.t2.name, Column#5)] |
| ├─TableReader_14(Build) | 3.00 | root | | data:TableFullScan_13 |
| │ └─TableFullScan_13 | 3.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─Projection_15(Probe) | 3.00 | root | | test.t1.id, test.t1.name, cast(test.t1.name, bigint(22) BINARY)->Column#5 |
| └─TableReader_17 | 3.00 | root | | data:TableFullScan_16 |
| └─TableFullScan_16 | 3.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+---------------+---------------------------------------------------------------------------+
7 rows in set (0.00 sec)
