执行计划异常，使用到了其他库的同名表

zhujunhui · 2025 年8 月 27 日 08:13

【TiDB 使用环境】生产环境
【TiDB 版本】v6.1
【操作系统】Centos7
【部署方式】本地部署
【集群数据量】2t
【问题复现路径】可以复现
【遇到的问题：问题现象及影响】
在集群内有分别为sp_order和fresh_sp_order的冷热数据库，其中fresh_sp_order为实时库,sp_order为全量库，里面表结构一致。在执行join类的sql时候，执行计划会用sp_order的表，但实际sql中指定的为fresh_sp_order

zhujunhui · 2025 年8 月 27 日 08:13

类似sql语句

select count(*) from (
select swwi.cityflag
,swwi.createdTime
,swwi.productId
,swwi.id
,swwi.orderPrice
,swwi.orderPrice - coalesce(swwi.presaleDiscountAmt,0) - coalesce(swwi.groupDiscountAmt,0) - ifnull( sif.PromotionCost, 0 )- ifnull( sif.CouponCost, 0 )- ifnull( sif.PointCost, 0 ) as freeze_price
from fresh_sp_order.so_item swwi
inner join fresh_sp_order.so_master as szmso on szmso.id = swwi.soid
left JOIN fresh_sp_order.so_item_frontproportion sif on swwi.soId=sif.orderId and swwi.ItemGuid=sif.ItemGuid
left join station.work_station sws on szmso.workstationId=sws.id
left join rpt.shihang_order_classify_v dim_o on dim_o.order_id=szmso.id
where swwi.createdTime >= date_add(current_date,interval -15 day)
and swwi.createdTime < date_add(date_format(CURRENT_TIMESTAMP() ,‘%Y-%m-%d %H:00:00’), interval (MINUTE(CURRENT_TIMESTAMP()) div 10 * 10) minute)
and szmso.createdTime >= date_add(current_date,interval -15 day)
and szmso.createdTime < date_add(date_format(CURRENT_TIMESTAMP() ,‘%Y-%m-%d %H:00:00’), interval (MINUTE(CURRENT_TIMESTAMP()) div 10 * 10) minute)
and szmso.orderStatus not in (0,120)
and swwi.status<>2
and swwi.splitflag=0 and szmso.splitflag=0 and sif.splitflag=0
and dim_o.shihang_order_channel_desc=‘C端’
and dim_o.order_date>=date_add(current_date,interval -20 day) ) a

zhujunhui · 2025 年8 月 27 日 08:14

HashAgg_58	1.00	root		funcs:count(1)->Column#522
└─HashJoin_61	677698.47	root		inner join, equal:[eq(fresh_sp_order.so_master.id, Column#224)]
├─IndexHashJoin_68(Build)	677698.47	root		inner join, inner:IndexLookUp_65, outer key:fresh_sp_order.so_item.itemguid, inner key:fresh_sp_order.so_item_frontproportion.itemguid, equal cond:eq(fresh_sp_order.so_item.itemguid, fresh_sp_order.so_item_frontproportion.itemguid), eq(fresh_sp_order.so_item.soid, fresh_sp_order.so_item_frontproportion.orderid)
│ ├─HashJoin_92(Build)	676896.52	root		inner join, equal:[eq(fresh_sp_order.so_master.id, fresh_sp_order.so_item.soid)]
│ │ ├─HashJoin_102(Build)	562600.82	root		left outer join, equal:[eq(fresh_sp_order.so_master.workstationid, station.work_station.id)]
│ │ │ ├─IndexReader_111(Build)	8312.00	root		index:IndexFullScan_110
│ │ │ │ └─IndexFullScan_110	8312.00	cop[tikv]	table:sws, index:idx_stationid(stationId)	keep order:false
│ │ │ └─IndexLookUp_107(Probe)	562600.82	root
│ │ │ ├─IndexRangeScan_104(Build)	3443459.72	cop[tikv]	table:szmso, index:idx_splitFlag_test(splitFlag)	range:[0,0], keep order:false
│ │ │ └─Selection_106(Probe)	562600.82	cop[tikv]		ge(fresh_sp_order.so_master.createdtime, 2025-08-12), lt(fresh_sp_order.so_master.createdtime, 2025-08-27 16:10:00.000000), not(in(fresh_sp_order.so_master.orderstatus, 0, 120))
│ │ │ └─TableRowIDScan_105	3443459.72	cop[tikv]	table:szmso	keep order:false
│ │ └─IndexLookUp_121(Probe)	676896.52	root
│ │ ├─IndexRangeScan_118(Build)	738886.51	cop[tikv]	table:swwi, index:idx_createdTime(createdTime)	range:[2025-08-12 00:00:00,2025-08-27 16:10:00), keep order:false
│ │ └─Selection_120(Probe)	676896.52	cop[tikv]		eq(fresh_sp_order.so_item.splitflag, 0), ne(fresh_sp_order.so_item.status, 2)
│ │ └─TableRowIDScan_119	738886.51	cop[tikv]	table:swwi	keep order:false
│ └─IndexLookUp_65(Probe)	1.00	root
│ ├─IndexRangeScan_62(Build)	1.10	cop[tikv]	table:sif, index:idx_ItemGuid(ItemGuid)	range: decided by [eq(fresh_sp_order.so_item_frontproportion.itemguid, fresh_sp_order.so_item.itemguid)], keep order:false
│ └─Selection_64(Probe)	1.00	cop[tikv]		eq(fresh_sp_order.so_item_frontproportion.splitflag, 0)
│ └─TableRowIDScan_63	1.10	cop[tikv]	table:sif	keep order:false
└─HashJoin_129(Probe)	108887007.35	root		left outer join, equal:[eq(sp_order.so_master.dcpchannel, dcp.distribution_channel.channel_code)]
├─IndexJoin_258(Build)	0.00	root		left outer join, inner:TableReader_255, outer key:dcp.distribution_channel.partner_id, inner key:dcp.distribution_platform.id, equal cond:eq(dcp.distribution_channel.partner_id, dcp.distribution_platform.id)
│ ├─IndexJoin_269(Build)	0.00	root		left outer join, inner:TableReader_266, outer key:dcp.distribution_channel.partner_id, inner key:dcp.distribution_partner.id, equal cond:eq(dcp.distribution_channel.partner_id, dcp.distribution_partner.id)
│ │ ├─TableReader_278(Build)	0.00	root		data:Selection_277
│ │ │ └─Selection_277	0.00	cop[tikv]		eq(dcp.distribution_channel.delete_flag, 0), eq(dcp.distribution_channel.status_id, 1)
│ │ │ └─TableFullScan_276	11.00	cop[tikv]	table:dc	keep order:false, stats:pseudo
│ │ └─TableReader_266(Probe)	1.00	root		data:TableRangeScan_265
│ │ └─TableRangeScan_265	1.00	cop[tikv]	table:dp	range: decided by [dcp.distribution_channel.partner_id], keep order:false, stats:pseudo
│ └─TableReader_255(Probe)	1.00	root		data:TableRangeScan_254
│ └─TableRangeScan_254	1.00	cop[tikv]	table:dpf	range: decided by [dcp.distribution_channel.partner_id], keep order:false, stats:pseudo
└─Selection_131(Probe)	108887007.35	root		eq(case(not(isnull(sp_order.so_master.dcpchannel)), case(eq(sp_order.so_master.dcpchannel, 91sfm3nz2ai25lvv), B端分销商城, C端分销商城), not(isnull(community_solitaire.gp_order.order_id)), B端, not(isnull(starter_coupon.coupon.order_id)), B端, or(eq(sp_order.so_master.ordersource, 2200), not(isnull(sp_order.so_master.fulipoolcode))), B端, and(and(eq(sp_order.so_master.cityflag, sh), ge(sp_order.so_master.shippingtime, 2022-09-01 00:00:00.000000)), not(in(sp_order.so_master.ordertype, 3129, 3078, 3139, 4020, 3350, 3360, 3370, 3380, 3148, 3150))), B端, eq(sp_order.so_master.ordertype, 6000), 智慧菜场, in(sp_order.so_master.ordertype, 1920, 1910, 1930), 线下店, in(sp_order.so_master.ordertype, 1811, 1821, 1831), B端, in(sp_order.so_master.ordertype, 1300, 1310), case(eq(sp_order.so_master.workstationname, 向阳路工厂店), C端, eq(sp_order.so_master.workstationname, 新区管委会会员店), B端, eq(sp_order.so_master.workstationname, 金塘园社区店), 线下店, not(in(sp_order.so_master.workstationname, 向阳路工厂店, 新区管委会会员店, 金塘园社区店)), 线下店, 其它), in(sp_order.so_master.ordertype, 1600, 3057, 4027, 4028), C端, in(sp_order.so_master.ordertype, 5200, 5300), C端, in(sp_order.so_master.ordertype, 4025, 4026), B端, eq(sp_order.so_master.ordertype, 3016), B端, eq(sp_order.so_master.ordertype, 4029), B端, in(sp_order.so_master.ordertype, 1300, 1310), B端, in(sp_order.so_master.ordertype, 1320, 1330, 1340, 1321, 1331, 3129, 3078, 3139, 3134, 3310, 3320, 3330, 3340, 4051, 4020, 3146), C端, in(sp_order.so_master.ordertype, 3350, 3360, 3370, 3380, 3148, 4022, 3150), C端, and(eq(sp_order.so_master.ordertype, 3149), ge(sp_order.so_master.createdtime, 2025-05-01 00:00:00.000000)), C端, C端), C端)
└─Projection_132	136108759.19	root		sp_order.so_master.id, sp_order.so_master.ordersource, sp_order.so_master.ordertype, sp_order.so_master.cityflag, sp_order.so_master.createdtime, sp_order.so_master.workstationname, sp_order.so_master.shippingtime, sp_order.so_master.dcpchannel, sp_order.so_master.fulipoolcode, starter_coupon.coupon.order_id, community_solitaire.gp_order.order_id
└─HashJoin_133	136108759.19	root		left outer join, equal:[eq(sp_order.so_master.couponid, starter_coupon.coupon.id) eq(Column#525, Column#526)]
├─Projection_212(Build)	4494267.86	root		starter_coupon.coupon.id, starter_coupon.coupon.order_id, cast(starter_coupon.coupon.order_id, double BINARY)->Column#526
│ └─IndexHashJoin_220	4494267.86	root		inner join, inner:IndexLookUp_217, outer key:starter_coupon.coupon_batch.id, inner key:starter_coupon.coupon.coupon_batch_id, equal cond:eq(starter_coupon.coupon_batch.id, starter_coupon.coupon.coupon_batch_id)
│ ├─IndexReader_247(Build)	127.03	root		index:IndexRangeScan_246
│ │ └─IndexRangeScan_246	127.03	cop[tikv]	table:cb, index:idx_coupon_batch_activity_type(activity_type, batch_approval_status, delivery_active_end_date, batch_name)	range:[188,188], [191,191], keep order:false
│ └─IndexLookUp_217(Probe)	35380.62	root
│ ├─IndexRangeScan_215(Build)	35380.62	cop[tikv]	table:c, index:idx_batch_id_and_used(coupon_batch_id, is_used)	range: decided by [eq(starter_coupon.coupon.coupon_batch_id, starter_coupon.coupon_batch.id) eq(starter_coupon.coupon.is_used, 1)], keep order:false
│ └─TableRowIDScan_216(Probe)	35380.62	cop[tikv]	table:c	keep order:false
└─HashJoin_153(Probe)	136108759.19	root		left outer join, equal:[eq(sp_order.so_master.id, community_solitaire.gp_order.order_id)]
├─TableReader_207(Build)	552.91	root		data:Selection_206
│ └─Selection_206	552.91	cop[tikv]		eq(community_solitaire.gp_order.round_id, 21), not(isnull(community_solitaire.gp_order.order_id))
│ └─TableFullScan_205	2371786.00	cop[tikv]	table:go2	keep order:false
└─Projection_176(Probe)	136108759.19	root		sp_order.so_master.id, sp_order.so_master.ordersource, sp_order.so_master.ordertype, sp_order.so_master.cityflag, sp_order.so_master.createdtime, sp_order.so_master.workstationname, sp_order.so_master.shippingtime, sp_order.so_master.couponid, sp_order.so_master.dcpchannel, sp_order.so_master.fulipoolcode, cast(case(eq(sp_order.so_master.parentid, 0), sp_order.so_master.id, sp_order.so_master.parentid), double UNSIGNED BINARY)->Column#525
└─HashJoin_187	136108759.19	root		left outer join, equal:[eq(sp_order.so_master.workstationid, station.work_station.id)]
├─IndexReader_204(Build)	8312.00	root		index:IndexFullScan_203
│ └─IndexFullScan_203	8312.00	cop[tikv]	table:sws, index:idx_stationid(stationId)	keep order:false
└─TableReader_200(Probe)	136108759.19	root		data:Selection_199
└─Selection_199	136108759.19	cop[tiflash]		eq(sp_order.so_master.splitflag, 0), ge(cast(date_format(sp_order.so_master.createdtime, %Y-%m-%d), datetime(6) BINARY), 2025-08-07)
└─TableFullScan_198	171150575.00	cop[tiflash]	table:szso	keep order:false

TiDBer_HErMeXDz · 2025 年8 月 27 日 08:18

这。。提交给官方吧。。

TiDBer_xTvoCh2f · 2025 年8 月 27 日 08:25

简单测试下，指定库名的执行计划没问题
例子很奇怪

zhujunhui · 2025 年8 月 27 日 08:38

我调了很多次，这里面最奇怪的是一部分join出现了异常

TiDBer_xTvoCh2f · 2025 年8 月 27 日 09:01

把库名加上``反引号试试

zhujunhui · 2025 年8 月 27 日 09:07

找到原因了业务人员写的时候用到了视图视图里面用的是全量库

TiDBer_xTvoCh2f · 2025 年8 月 27 日 09:15

视图和表名应该不能重复，你的表名和视图是相同的名字？

zhujunhui · 2025 年8 月 27 日 09:23

rpt.shihang_order_classify_v dim_o 这个实际是个视图里面用到了相关库

TiDBer_xTvoCh2f · 2025 年8 月 27 日 09:37

好，我看有好几个表都是 sp_order.so_master.id呢
解决就好

system · 2025 年9 月 3 日 09:38

此话题已在最后回复的 7 天后被自动关闭。不再允许新回复。