- 【TiDB 版本】：3.0.5
- 【问题描述】：
如下SQL语句，hard code 参数时，能正确使用索引：
-- 创建整体处理的临时表
insert into tmp_mongo_mobile_yys_order_info_20191104
select b.apply_id,b.user_sid,b.product_cid,b.product_name,b.apply_time,a.orderid
,replace(thedata->"$.start_time",'"',"") start_time
,replace(thedata->"$.call_type",'"',"")call_type
,TIME_TO_SEC(replace(thedata->"$.use_time",'"',"")) use_time
,replace(thedata->"$.the_other_type",'"',"")the_other_type
,replace(thedata->"$.the_other",'"',"") the_other
,replace(thedata->"$.self_place",'"',"")self_place
,c.province
,c.city
,b.phone
,d.province local_province
,d.city local_city
from mongo_mobile_yys_order_info b
left join indexs_of_2version_marketing_model cc on b.yys_order_sn=cc.orderid
left join mongo_mobile_raw_data_mobile_details a on a.orderid=b.yys_order_sn
left join mongo_mobile_raw_data_mobile_detail_address c on a.orderid=c.orderid and replace(a.thedata->"$.the_other_type",'"',"")=c.the_other_type
left join mongo_mobile_raw_data_mobile_detail_address d on b.phone=d.the_other_type and b.yys_order_sn=d.orderid
where a.orderid='61f08d24-3958-42ba-b41c-aa8ea6bb79d9' and cc.orderid is null
;
（ps. 最后一行 where a.orderid='61f08d24-3958-42ba-b41c-aa8ea6bb79d9' ... 是 hard code 参数的位置）
这是相应的执行计划，
Projection_15 0.00 root mongo.b.apply_id, mongo.b.user_sid, mongo.b.product_cid, mongo.b.product_name, mongo.b.apply_time, mongo.a.orderid, replace(cast(json_extract(mongo.a.thedata, "$.start_time")), """, ""), replace(cast(json_extract(mongo.a.thedata, "$.call_type")), """, ""), time_to_sec(cast(replace(cast(json_extract(mongo.a.thedata, "$.use_time")), """, ""))), replace(cast(json_extract(mongo.a.thedata, "$.the_other_type")), """, ""), replace(cast(json_extract(mongo.a.thedata, "$.the_other")), """, ""), replace(cast(json_extract(mongo.a.thedata, "$.self_place")), """, ""), mongo.c.province, mongo.c.city, mongo.b.phone, mongo.d.province, mongo.d.city
└─IndexJoin_21 0.00 root left outer join, inner:IndexLookUp_20, outer key:mongo.b.phone, mongo.b.yys_order_sn, inner key:mongo.d.the_other_type, mongo.d.orderid
├─HashLeftJoin_23 0.00 root left outer join, inner:IndexLookUp_51, equal:[eq(replace(cast(json_extract(mongo.a.thedata, $.the_other_type)), ", ), mongo.c.the_other_type)]
│ ├─Projection_24 0.00 root mongo.b.apply_id, mongo.b.user_sid, mongo.b.product_cid, mongo.b.product_name, mongo.b.yys_order_sn, mongo.b.apply_time, mongo.b.phone, mongo.cc.orderid, mongo.a.orderid, mongo.a.thedata, replace(cast(json_extract(mongo.a.thedata, "$.the_other_type")), """, "")
│ │ └─HashRightJoin_26 0.00 root CARTESIAN inner join, inner:Selection_27
│ │ ├─Selection_27 0.00 root isnull(mongo.cc.orderid)
│ │ │ └─HashLeftJoin_28 0.00 root CARTESIAN left outer join, inner:IndexReader_36
│ │ │ ├─IndexLookUp_34 0.00 root
│ │ │ │ ├─IndexScan_32 0.00 cop table:b, index:yys_order_sn, range:["61f08d24-3958-42ba-b41c-aa8ea6bb79d9","61f08d24-3958-42ba-b41c-aa8ea6bb79d9"], keep order:false
│ │ │ │ └─TableScan_33 0.00 cop table:mongo_mobile_yys_order_info, keep order:false
│ │ │ └─IndexReader_36 10.00 root index:IndexScan_35
│ │ │ └─IndexScan_35 10.00 cop table:cc, index:orderid, range:["61f08d24-3958-42ba-b41c-aa8ea6bb79d9","61f08d24-3958-42ba-b41c-aa8ea6bb79d9"], keep order:false, stats:pseudo
│ │ └─IndexLookUp_44 28117.39 root
│ │ ├─IndexScan_42 28117.39 cop table:a, index:orderid, range:["61f08d24-3958-42ba-b41c-aa8ea6bb79d9","61f08d24-3958-42ba-b41c-aa8ea6bb79d9"], keep order:false
│ │ └─TableScan_43 28117.39 cop table:mongo_mobile_raw_data_mobile_details, keep order:false
│ └─IndexLookUp_51 339.08 root
│ ├─IndexScan_49 339.08 cop table:c, index:orderid, range:["61f08d24-3958-42ba-b41c-aa8ea6bb79d9","61f08d24-3958-42ba-b41c-aa8ea6bb79d9"], keep order:false
│ └─TableScan_50 339.08 cop table:mongo_mobile_raw_data_mobile_detail_address, keep order:false
└─IndexLookUp_20 1.00 root
├─Selection_19 1.00 cop not(isnull(mongo.d.orderid)), not(isnull(mongo.d.the_other_type))
│ └─IndexScan_17 1.00 cop table:d, index:orderid, the_other_type, range: decided by [eq(mongo.d.orderid, mongo.b.yys_order_sn) eq(mongo.d.the_other_type, mongo.b.phone)], keep order:false
└─TableScan_18 1.00 cop table:mongo_mobile_raw_data_mobile_detail_address, keep order:false, stats:pseudo
（IndexScan_17 1.00 cop table:d, index:orderid, the_other_type, range: decided by [eq(mongo.d.orderid, mongo.b.yys_order_sn) eq(mongo.d.the_other_type, mongo.b.phone)], keep order:false 显示它用到了相应的索引。实际执行时间很快，几十毫秒。）
但换成如下传参的SQL语句，就不走索引了，
set @orderid='61f08d24-3958-42ba-b41c-aa8ea6bb79d9';
-- 创建整体处理的临时表
insert into tmp_mongo_mobile_yys_order_info_20191104
select b.apply_id,b.user_sid,b.product_cid,b.product_name,b.apply_time,a.orderid
,replace(thedata->"$.start_time",'"',"") start_time
,replace(thedata->"$.call_type",'"',"")call_type
,TIME_TO_SEC(replace(thedata->"$.use_time",'"',"")) use_time
,replace(thedata->"$.the_other_type",'"',"")the_other_type
,replace(thedata->"$.the_other",'"',"") the_other
,replace(thedata->"$.self_place",'"',"")self_place
,c.province
,c.city
,b.phone
,d.province local_province
,d.city local_city
from mongo_mobile_yys_order_info b
left join indexs_of_2version_marketing_model cc on b.yys_order_sn=cc.orderid
left join mongo_mobile_raw_data_mobile_details a on a.orderid=b.yys_order_sn
left join mongo_mobile_raw_data_mobile_detail_address c on a.orderid=c.orderid and replace(a.thedata->"$.the_other_type",'"',"")=c.the_other_type
left join mongo_mobile_raw_data_mobile_detail_address d on b.phone=d.the_other_type and b.yys_order_sn=d.orderid
where a.orderid=@orderid and cc.orderid is null
;
（ps. 最后一行 where a.orderid=@orderid ... 是传参位置）
这是相应的执行计划，
Projection_15 9817485579.87 root mongo.b.apply_id, mongo.b.user_sid, mongo.b.product_cid, mongo.b.product_name, mongo.b.apply_time, mongo.a.orderid, replace(cast(json_extract(mongo.a.thedata, "$.start_time")), """, ""), replace(cast(json_extract(mongo.a.thedata, "$.call_type")), """, ""), time_to_sec(cast(replace(cast(json_extract(mongo.a.thedata, "$.use_time")), """, ""))), replace(cast(json_extract(mongo.a.thedata, "$.the_other_type")), """, ""), replace(cast(json_extract(mongo.a.thedata, "$.the_other")), """, ""), replace(cast(json_extract(mongo.a.thedata, "$.self_place")), """, ""), mongo.c.province, mongo.c.city, mongo.b.phone, mongo.d.province, mongo.d.city
└─Selection_16 9817485579.87 root eq(mongo.a.orderid, getvar("orderid"))
└─HashLeftJoin_23 12271856974.83 root left outer join, inner:TableReader_82, equal:[eq(mongo.b.phone, mongo.d.the_other_type) eq(mongo.b.yys_order_sn, mongo.d.orderid)]
├─HashLeftJoin_24 7766220639.95 root left outer join, inner:TableReader_73, equal:[eq(mongo.a.orderid, mongo.c.orderid) eq(replace(cast(json_extract(mongo.a.thedata, $.the_other_type)), ", ), mongo.c.the_other_type)]
│ ├─Projection_25 7766220639.95 root mongo.b.apply_id, mongo.b.user_sid, mongo.b.product_cid, mongo.b.product_name, mongo.b.yys_order_sn, mongo.b.apply_time, mongo.b.phone, mongo.cc.orderid, mongo.a.orderid, mongo.a.thedata, replace(cast(json_extract(mongo.a.thedata, "$.the_other_type")), """, "")
│ │ └─IndexJoin_31 7766220639.95 root left outer join, inner:IndexLookUp_30, outer key:mongo.b.yys_order_sn, inner key:mongo.a.orderid
│ │ ├─Selection_51 6280733.60 root isnull(mongo.cc.orderid)
│ │ │ └─HashLeftJoin_57 7850917.00 root left outer join, inner:IndexReader_61, equal:[eq(mongo.b.yys_order_sn, mongo.cc.orderid)]
│ │ │ ├─TableReader_59 7850917.00 root data:TableScan_58
│ │ │ │ └─TableScan_58 7850917.00 cop table:b, range:[-inf,+inf], keep order:false
│ │ │ └─IndexReader_61 9990.00 root index:IndexScan_60
│ │ │ └─IndexScan_60 9990.00 cop table:cc, index:orderid, range:[-inf,+inf], keep order:false, stats:pseudo
│ │ └─IndexLookUp_30 1523.63 root
│ │ ├─Selection_29 1523.63 cop not(isnull(mongo.a.orderid))
│ │ │ └─IndexScan_27 1523.63 cop table:a, index:orderid, range: decided by [eq(mongo.a.orderid, mongo.b.yys_order_sn)], keep order:false
│ │ └─TableScan_28 1523.63 cop table:mongo_mobile_raw_data_mobile_details, keep order:false, stats:pseudo
│ └─TableReader_73 18964526.00 root data:Selection_72
│ └─Selection_72 18964526.00 cop not(isnull(mongo.c.orderid))
│ └─TableScan_71 18964526.00 cop table:c, range:[-inf,+inf], keep order:false
└─TableReader_82 18964526.00 root data:Selection_81
└─Selection_81 18964526.00 cop not(isnull(mongo.d.orderid)), not(isnull(mongo.d.the_other_type))
└─TableScan_80 18964526.00 cop table:d, range:[-inf,+inf], keep order:false
请问，这是怎么回事呢？怎么解决这个问题？
