SQL中有传参时没有走索引

  • 【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

请问,这是怎么回事呢?怎么解决这个问题?

你好,可以参考下这个文档,固定执行计划:

没有用,在 mongo_mobile_raw_data_mobile_details a 后面加了 use index(orderid) 也没有效果。执行计划不变,还是走全表扫描。

你用 explain analyze sql_statement 试试 刚刚的计划应该是 explain 吧

是的,我一开始用的是 explain。 不过我用 explain analyze 跑不出来,跑了十多分钟,挂了。

嗯,我向我们研发反馈一下。

后面有进展我再继续更新这个帖子。

你们现在暂时先别用这种传参模式了,可以考虑使用 prepare 写法,或者直接继续 hard code 。

好的。我们也试试其他变通的方式。谢谢!

:+1::+1::+1: