【 TiDB 使用环境】生产环境
【 TiDB 版本】v7.1.3
【遇到的问题：问题现象及影响】
t 表主键 ob_object_id
t2表有唯一性索引 F1_6511+F2_6511
执行计划异常:
当t表指定一条具体数据的时候，执行计划正常，t t2 关联走 index_join
当t表指定一条数据（来自一个子查询时），执行计划异常，t t2关联走hash_join
index_join:
mysql> explain analyze
-> SELECT t.* FROM
-> WIND.TB_OBJECT_6757 t,wind.tb_object_6511 t2
-> WHERE t.ob_object_id in('5efcb925-d53b-4676-ac90-7353391ac49e')
-> AND f9_6757 = 1
-> and f1_6511 = f1_6757
-> AND f3_6511 = '2010400017';
+---------------------------------+------------+---------+-----------+----------------------------------------------------+
| id | estRows | actRows | task | access object |
+---------------------------------+------------+---------+-----------+----------------------------------------------------+
| IndexJoin_12 | 0.87 | 0 | root | |
| ├─Selection_25(Build) | 0.84 | 0 | root |
| │ └─Point_Get_24 | 1.00 | 1 | root | table:TB_OBJECT_6757, index:PRIMARY(OB_OBJECT_ID)
| └─IndexLookUp_11(Probe) | 0.87 | 0 | root |
| ├─IndexRangeScan_8(Build) | 5006121.81 | 0 | cop[tikv] | table:t2, index:UIDX_TB6511_F1F2(F1_6511, F2_6511)
| └─Selection_10(Probe) | 0.87 | 0 | cop[tikv] |
| └─TableRowIDScan_9 | 5006121.81 | 0 | cop[tikv] | table:t2
+---------------------------------+------------+---------+-----------+----------------------------------------------------+
**7 rows in set (0.00 sec)**
hash_join 导致全表扫描：
mysql> explain analyze
-> SELECT t.* FROM
-> WIND.TB_OBJECT_6757 t,wind.tb_object_6511 t2
-> WHERE t.ob_object_id in(SELECT REC_ID FROM wind_replicator.wind_tb_object_6757_oplog limit 1)
-> AND f9_6757 = 1
-> and f1_6511 = f1_6757
-> AND f3_6511 = '2010400017';
+------------------------------------+--------------+-----------+-----------+--------------------------------------------------------------------------------+
| id | estRows | actRows | task | access object |
+------------------------------------+--------------+-----------+-----------+--------------------------------------------------------------------------------+
| HashJoin_18 | 1.00 | 0 | root | |
| ├─HashAgg_72(Build) | 1.00 | 1 | root |
| │ └─Limit_73 | 1.00 | 1 | root |
| │ └─IndexReader_78 | 1.00 | 1 | root | partition:all
| │ └─Limit_77 | 1.00 | 1 | cop[tikv] |
| │ └─IndexFullScan_76 | 1.00 | 1 | cop[tikv] | table:WIND_TB_OBJECT_6757_OPLOG, index:idx_WINDTBOBJECT6757OPLOG_RECID(REC_ID
| └─IndexHashJoin_27(Probe) | 115.71 | 2275499 | root |
| ├─TableReader_68(Build) | 44.35 | 663505 | root |
| │ └─Selection_67 | 44.35 | 663505 | cop[tikv] |
| │ └─TableFullScan_66 | 254688690.00 | 254669900 | cop[tikv] | table:t2
| └─IndexLookUp_24(Probe) | 115.71 | 2275499 | root |
| ├─IndexRangeScan_21(Build) | 137.79 | 5290761 | cop[tikv] | table:t, index:IDX_TB6757_F1(F1_6757)
| └─Selection_23(Probe) | 115.71 | 2275499 | cop[tikv] |
| └─TableRowIDScan_22 | 137.79 | 5290761 | cop[tikv] | table:t
+------------------------------------+--------------+-----------+-----------+--------------------------------------------------------------------------------+
**14 rows in set (18.99 sec)**
