为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】: --03134284294-20241009-244896-20119 Pack3
【操作系统】:openEuler 22.03 (LTS-SP4) Docker
【CPU】: 10
【问题描述】*:
如下sql
SELECT DISTINCT odPlan."Id", ps."Id" ProductionSegmentId FROM
"DEV_SPAS_MANUFACTURE"."Manufacture.Order" odPlan
JOIN "DEV_SPAS_MANUFACTURE"."Manufacture.OrderDemandSegmentation" odsPlan ON odPlan."Id" = odsPlan."OrderPlanId"
JOIN "DEV_SPAS_MANUFACTURE"."Manufacture.OrderDemandSegmentation" ods ON odsPlan."Id" = ods."OrderPlanSegmentationId"
JOIN (
SELECT
*
FROM
"DEV_SPAS_MANUFACTURE"."Manufacture.ProductionBatchDetail" tempPbd
CROSS JOIN JSON_TABLE(
tempPbd."DemandSegmentIds",
'$[*]' COLUMNS("demandSegmentId" NVARCHAR(100) PATH '$')
) "tempSegmentIdTable"
WHERE
tempPbd."TenantId" = 795351072901173248
AND tempPbd."IsDeleted" = 0
) pbd ON pbd."demandSegmentId" = ods."Id"
JOIN "DEV_SPAS_MANUFACTURE"."Manufacture.ProductionSegment" ps
ON pbd."Id" = ps."ProductionBatchDetailId"
WHERE
odPlan."TenantId" = 795351072901173248
AND odPlan."IsDeleted" = 0
AND odPlan."OrderType" = 2
AND odsPlan."IsDeleted" = 0
AND odsPlan."TenantId" = 795351072901173248
AND ods."IsDeleted" = 0
AND ods."TenantId" = 795351072901173248
AND ps."IsDeleted" = 0
AND ps."TenantId" = 795351072901173248
AND odPlan."Id" IN (1060860764732002304,1060869947825065984,1060967054762315776,1060967261050769408,1060967473903308800,1060971395040677888,1060971445158416384,1060971502238699520,1060971928434511872,1060971984160034816,1060972264498925568,1060972333574918144,1060974021849387008,1061004708874817536,1061225852097794048,1061226337638813696,1061229491180802048,1061230527228416000,1061282284818141184,1061298304509284352,1061305444561063936,1061306096792113152,1061306641837723648,1061307286657437696,1061274439317983232,1061272602061836288,1061632725464649728,1061635139987050496,1061635530992652288,1060994736875114496,1060948610687963136,1060967996895268864,1060929899650355200,1060942267969179648,1060964105155776512,1061220968115998720,1061227847445647360,1061236935344787456,1061306950580441088,1060994395748175872,1060912081798172672)
当 最后一个条件 AND odPlan."Id" IN 加上的时候,
速度是: 95.624
去掉之后 速度是 0.198
秒
并且同样的环境下,mysql没有任何问题。
索引也都存在:
尝试过重建索引,重建统计信息.
要看着直接计划,有什么变化