【概述】 场景 + 问题概述
场景：我们的查询场景里，有一些略为复杂的查询，相同数据的情况下 mysql查询速度非常快，tidb查询速度非常慢。
我们的查询语句由多个子条件组成，观察到Tidb似乎会对有临时表的子条件进行分别查询，但是子条件的过滤效率差异很大，尤其是临时表起到主要过滤作用的时候，整个查询的效率会被 非临时表部分 的子条件严重拖累
我的诉求是：是否有类似
/*+ STREAM_AGG() */
之类的方法 使得查询过程不是 子条件分别过滤再将结果合并（交、并），而是 子条件同时发挥作用将结果筛选出来， 或者有没有别的办法能达到类似的效果，就是：子条件不分别执行再整合结果，而是直接同时发挥过滤效果
【背景】 做过哪些操作
查询分析
【现象】 业务和数据库现象
无
【问题】 当前遇到的问题
相对于mysql tidb在一些带临时表的查询场景查询速度慢
【业务影响】
业务服务响应被拖累
【TiDB 版本】
5.7.25-TiDB-v4.0.8 但是在 5.7.25-TiDB-v5.0.1 上现象也存在
【应用软件及版本】
无
【附件】 相关日志及配置信息
简化的例子:
查询语句：
SELECT
rps.contract_id,max(rps.phase) AS max_phase , max(rps.id) AS max_id
FROM
contract_info AS c,
repay_plan_states AS rps,
repay_plan AS rp
WHERE
rps.repay_plan_id = rp.repay_plan_id
AND rps.contract_id = c.contract_id
AND rps.contract_id IN (
SELECT DISTINCT
c.contract_id
FROM
repay_plan_states AS drs,contract_info AS c,repay_plan AS rp
WHERE
(drs.state_end_time >= '2017-12-26' OR drs.state_end_time IS NULL )
AND drs.state_start_time < '2017-12-26' AND drs.remain_principal > 0
AND c.contract_id = drs.contract_id
AND rp.repay_plan_id = drs.repay_plan_id
AND '2017-12-26' > rp.repay_date
) AND ( (rps.plan_status > 100 AND rps.state_start_time < '2017-12-26' ) OR (rps.plan_status = 100 AND rps.phase=1 AND (rps.state_end_time IS NULL OR rps.state_end_time >= '2017-12-26' )) )
GROUP BY rps.contract_id；
explain analyze:
+-------------------------------------------------------+-------------+---------+-----------+-------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------------------------------------+-------------+---------+-----------+-------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+
| Projection_26 | 1222073.18 | 0 | root | | time:3.982800702s, loops:1, Concurrency:4 | bmd_bi_udata.repay_plan_states.contract_id, Column#177, Column#178 | 8.71875 KB | N/A |
| └─HashAgg_29 | 1222073.18 | 0 | root | | time:3.982761482s, loops:1, PartialConcurrency:4, FinalConcurrency:4 | group by:bmd_bi_udata.repay_plan_states.contract_id, funcs:max(bmd_bi_udata.repay_plan_states.phase)->Column#177, funcs:max(bmd_bi_udata.repay_plan_states.id)->Column#178, funcs:firstrow(bmd_bi_udata.repay_plan_states.contract_id)->bmd_bi_udata.repay_plan_states.contract_id | 8.71875 KB | N/A |
| └─IndexHashJoin_38 | 1225522.49 | 0 | root | | time:3.982698529s, loops:1, | inner join, inner:IndexReader_35, outer key:bmd_bi_udata.repay_plan_states.repay_plan_id, inner key:bmd_bi_udata.repay_plan.repay_plan_id | 0 Bytes | N/A |
| ├─HashJoin_43(Build) | 1222073.18 | 0 | root | | time:3.982854198s, loops:1, build_hash_table:{total:1.805783ms, fetch:1.805783ms, build:0s} | inner join, equal:[eq(bmd_bi_udata.repay_plan_states.contract_id, bmd_bi_udata.contract_info.contract_id)] | 0 Bytes | 0 Bytes |
| │ ├─HashAgg_99(Build) | 193492.25 | 0 | root | | time:1.777252ms, loops:1, PartialConcurrency:4, FinalConcurrency:4 | group by:bmd_bi_udata.contract_info.contract_id, funcs:firstrow(bmd_bi_udata.contract_info.contract_id)->bmd_bi_udata.contract_info.contract_id | 2.90625 KB | N/A |
| │ │ └─IndexHashJoin_107 | 193492.25 | 0 | root | | time:1.741824ms, loops:1, | inner join, inner:IndexReader_104, outer key:bmd_bi_udata.repay_plan_states.contract_id, inner key:bmd_bi_udata.contract_info.contract_id | 0 Bytes | N/A |
| │ │ ├─HashJoin_126(Build) | 192131.85 | 0 | root | | time:1.943557ms, loops:1, build_hash_table:{total:1.072346ms, fetch:1.072346ms, build:0s} | inner join, equal:[eq(bmd_bi_udata.repay_plan.repay_plan_id, bmd_bi_udata.repay_plan_states.repay_plan_id)] | 0 Bytes | 0 Bytes |
| │ │ │ ├─IndexLookUp_147(Build) | 181231.21 | 0 | root | | time:887.524µs, loops:1, cop_task: {num: 1, max:805.164µs, proc_keys: 0, rpc_num: 1, rpc_time: 795.784µs, copr_cache_hit_ratio: 0.00} | | 180 Bytes | N/A |
| │ │ │ │ ├─IndexRangeScan_145(Build) | 181231.21 | 0 | cop[tikv] | table:rp, index:idx_repayDate(repay_date) | time:0s, loops:1 | range:[-inf,2017-12-26), keep order:false | N/A | N/A |
| │ │ │ │ └─TableRowIDScan_146(Probe) | 181231.21 | 0 | cop[tikv] | table:rp | time:0ns, loops:0 | keep order:false | N/A | N/A |
| │ │ │ └─IndexLookUp_154(Probe) | 191591.08 | 18 | root | | time:1.850874ms, loops:1, cop_task: {num: 1, max:942.635µs, proc_keys: 18, rpc_num: 1, rpc_time: 936.622µs, copr_cache_hit_ratio: 0.00} | | 10.09765625 KB | N/A |
| │ │ │ ├─IndexRangeScan_151(Build) | 304598.21 | 18 | cop[tikv] | table:drs, index:idx_stateStartTime(state_start_time) | time:0s, loops:1 | range:[-inf,2017-12-26 00:00:00), keep order:false | N/A | N/A |
| │ │ │ └─Selection_153(Probe) | 191591.08 | 0 | cop[tikv] | | time:0ns, loops:0 | gt(bmd_bi_udata.repay_plan_states.remain_principal, 0), or(ge(bmd_bi_udata.repay_plan_states.state_end_time, 2017-12-26 00:00:00.000000), isnull(bmd_bi_udata.repay_plan_states.state_end_time)) | N/A | N/A |
| │ │ │ └─TableRowIDScan_152 | 304598.21 | 0 | cop[tikv] | table:drs | time:0ns, loops:0 | keep order:false | N/A | N/A |
| │ │ └─IndexReader_104(Probe) | 1.00 | 0 | root | | time:0ns, loops:0 | index:IndexRangeScan_103 | N/A | N/A |
| │ │ └─IndexRangeScan_103 | 1.00 | 0 | cop[tikv] | table:c, index:uniq_contractId(contract_id) | time:0ns, loops:0 | range: decided by [eq(bmd_bi_udata.contract_info.contract_id, bmd_bi_udata.repay_plan_states.contract_id)], keep order:false | N/A | N/A |
| │ └─HashJoin_89(Probe) | 23829161.24 | 1024 | root | | time:3.98272277s, loops:1, build_hash_table:{total:3.981582995s, fetch:436.692431ms, build:3.544890564s}, probe:{concurrency:5, total:19.915784057s, max:3.983599885s, probe:6.476121ms, fetch:19.909307936s} | inner join, equal:[eq(bmd_bi_udata.contract_info.contract_id, bmd_bi_udata.repay_plan_states.contract_id)] | 77.62786865234375 MB | 0 Bytes |
| │ ├─IndexReader_93(Build) | 9878903.00 | 9879841 | root | | time:426.77104ms, loops:9653, cop_task: {num: 10, max: 691.085497ms, min: 406.241326ms, avg: 539.336816ms, p95: 691.085497ms, max_proc_keys: 1024702, p95_proc_keys: 1024702, tot_proc: 5.168s, rpc_num: 10, rpc_time: 5.393137494s, copr_cache_hit_ratio: 0.00} | index:IndexFullScan_92 | 68.1714391708374 MB | N/A |
| │ │ └─IndexFullScan_92 | 9878903.00 | 9879841 | cop[tikv] | table:c, index:uniq_contractId(contract_id) | proc max:636ms, min:364ms, p80:540ms, p95:636ms, iters:9692, tasks:10 | keep order:false | N/A | N/A |
| │ └─TableReader_96(Probe) | 23661623.20 | 6163 | root | | time:356.347441ms, loops:6, cop_task: {num: 2, max: 355.791487ms, min: 1.257546ms, avg: 178.524516ms, p95: 355.791487ms, max_proc_keys: 316709, p95_proc_keys: 316709, tot_proc: 348ms, rpc_num: 2, rpc_time: 357.032417ms, copr_cache_hit_ratio: 0.00} | data:Selection_95 | 72.06530857086182 MB | N/A |
| │ └─Selection_95 | 23661623.20 | 44569 | cop[tikv] | | proc max:344ms, min:0s, p80:344ms, p95:344ms, iters:315, tasks:2 | or(and(gt(bmd_bi_udata.repay_plan_states.plan_status, 100), lt(bmd_bi_udata.repay_plan_states.state_start_time, 2017-12-26 00:00:00.000000)), and(eq(bmd_bi_udata.repay_plan_states.plan_status, 100), and(eq(bmd_bi_udata.repay_plan_states.phase, 1), or(isnull(bmd_bi_udata.repay_plan_states.state_end_time), ge(bmd_bi_udata.repay_plan_states.state_end_time, 2017-12-26 00:00:00.000000))))) | N/A | N/A |
| │ └─TableRangeScan_94 | 29577029.00 | 316709 | cop[tikv] | table:rps | proc max:284ms, min:0s, p80:284ms, p95:284ms, iters:315, tasks:2 | range:[0,+inf], keep order:false | N/A | N/A |
| └─IndexReader_35(Probe) | 1.00 | 0 | root | | time:0ns, loops:0 | index:IndexRangeScan_34 | N/A | N/A |
| └─IndexRangeScan_34 | 1.00 | 0 | cop[tikv] | table:rp, index:uniq_repayPlanId(repay_plan_id) | time:0ns, loops:0 | range: decided by [eq(bmd_bi_udata.repay_plan.repay_plan_id, bmd_bi_udata.repay_plan_states.repay_plan_id)], keep order:false | N/A | N/A |
+-------------------------------------------------------+-------------+---------+-----------+-------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+
实际执行的时候，我们需要HashAgg_99 AND HashJoin_89同时起作用
HashAgg_99 主要是生成一张临时表，是很快的，数据量也很少
HashJoin_89 是非常缓慢的，但是在实际查询中是为了作为HashAgg_99的补充
tidb在执行时时间基本上都花费在了HashJoin_89上