【 TiDB 使用环境】生产环境
【 TiDB 版本】V7.1.3
【遇到的问题：问题现象及影响】 执行计划异常, 添加了一个过滤条件就变成全表扫描了， hint尝试过 leading no_hash_join use_index force_index 都没用， 表也手动分析过
PS: tt1 主键 OB_OBJECT_ID
tt2 opdate 字段有索引，单独查询可以快速过滤出数据
mysql> explain
-> SELECT tt1.*
-> FROM wind.tb_object_6757 tt1,
-> (select rec_id
-> from wind_replicator.wind_tb_object_6757_oplog
-> where opdate > date_sub(NOW(), INTERVAL 1 DAY) limit 100) tt2
-> where tt1.ob_object_id = tt2.rec_id;
+--------------------------------------+---------+-----------+-----------------------------------------------------------------------------------+
| id | estRows | task | access object |
+--------------------------------------+---------+-----------+-----------------------------------------------------------------------------------+
| IndexJoin_18 | 100.00 | root | |
| ├─Projection_39(Build) | 100.00 | root | |
| │ └─IndexLookUp_38 | 100.00 | root | partition:P202403,P202404 |
| │ ├─Limit_37(Build) | 100.00 | cop[tikv] | |
| │ │ └─IndexRangeScan_35 | 100.00 | cop[tikv] | table:WIND_TB_OBJECT_6757_OPLOG, index:idx_WINDTBOBJECT6757OPLOG_TIME(OPDATE, ID) |
| │ └─TableRowIDScan_36(Probe) | 100.00 | cop[tikv] | table:WIND_TB_OBJECT_6757_OPLOG |
| └─IndexLookUp_17(Probe) | 100.00 | root | |
| ├─IndexRangeScan_15(Build) | 100.00 | cop[tikv] | table:tt1, index:PRIMARY(OB_OBJECT_ID) |
| └─TableRowIDScan_16(Probe) | 100.00 | cop[tikv] | table:tt1 |
+--------------------------------------+---------+-----------+-----------------------------------------------------------------------------------+
9 rows in set (0.01 sec)
mysql> explain
-> SELECT tt1.*
-> FROM wind.tb_object_6757 tt1,
-> (select rec_id
-> from wind_replicator.wind_tb_object_6757_oplog
-> where opdate > date_sub(NOW(), INTERVAL 1 DAY) limit 100) tt2
-> where tt1.ob_object_id = tt2.rec_id
->
-> and instr(tt1.f5_6757, 'xxxx') <> 0;
+--------------------------------------+--------------+-----------+-----------------------------------------------------------------------------------+
| id | estRows | task | access object |
+--------------------------------------+--------------+-----------+-----------------------------------------------------------------------------------+
| HashJoin_17 | 100.00 | root | |
| ├─Projection_28(Build) | 100.00 | root | |
| │ └─IndexLookUp_27 | 100.00 | root | partition:P202403,P202404 |
| │ ├─Limit_26(Build) | 100.00 | cop[tikv] | |
| │ │ └─IndexRangeScan_24 | 100.00 | cop[tikv] | table:WIND_TB_OBJECT_6757_OPLOG, index:idx_WINDTBOBJECT6757OPLOG_TIME(OPDATE, ID) |
| │ └─TableRowIDScan_25(Probe) | 100.00 | cop[tikv] | table:WIND_TB_OBJECT_6757_OPLOG |
| └─Selection_29(Probe) | 190620123.20 | root | |
| └─TableReader_31 | 238275154.00 | root | |
| └─TableFullScan_30 | 238275154.00 | cop[tikv] | table:tt1 |
+--------------------------------------+--------------+-----------+-----------------------------------------------------------------------------------+
9 rows in set, 1 warning (0.01 sec)
