注册

sql加了id查询的条件,结果返回贼慢,麻烦能协助定位

pfttm 2025/01/09 386 6

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【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没有任何问题。
索引也都存在:
image.png
尝试过重建索引,重建统计信息.

回答 0
暂无回答
扫一扫
联系客服