【原】SQL慢查询追踪与优化，从工单案例看性能提升之道

慢查询sql语句

UPDATE  A
SET CORPORATION_NAME = (
    SELECT DISTINCT CORPORATION_NAME
    FROM (
        SELECT CONTRACT_NO, 
               COOP_SERVICE_TYPE, 
               CORPORATION_NAME, 
               PROJECT_NAME, 
               ROW_NUMBER() OVER (PARTITION BY CONTRACT_NO, COOP_SERVICE_TYPE ) AS SEQ
        FROM O_PLIS_PROC B
        WHERE B.BDHA_TX_DATE='2024-06-10' AND A.LM_CT1_NO = B.CONTRACT_NO
    ) B
    WHERE B.COOP_SERVICE_TYPE='01' AND B.SEQ = 1
)
WHERE LM_CT1_NO IN (
    SELECT  CONTRACT_NO
    FROM O_PLIS_PROC C
    WHERE C.CONTRACT_NO=A.LM_CT1_NO
    AND C.COOP_SERVICE_TYPE='01'
    AND C.BDHA_TX_DATE='2024-06-10'
);

执行sql语句等待很长时间，尤其in 查询后面返回百万多行数据，update set ... 可以理解成标量子查询，相当于 update set 标量子查询也要百万多次。sql计划根本没走索引

增加索引

CREATE INDEX TEST ON O_PLIS_PROC(　　BDHA_TX_DATE,　　COOP_SERVICE_TYPE,　　CONTRACT_NO,　　COOP_SERVICE_TYPE,　　CORPORATION_NAME);

创建索引后，sql语句数秒完成。

优化后sql

with tmp as (
 SELECT 
  CONTRACT_NO,
  COOP_SERVICE_TYPE,
  CORPORATION_NAME FROM O_PLIS_PROC 
  WHERE BDHA_TX_DATE='2024-06-10' AND COOP_SERVICE_TYPE='01'
)

update a set 
 CORPORATION_NAME = (
    SELECT  CORPORATION_NAME 
        FROM tmp B 
    WHERE A.LM_CT1_NO = B.CONTRACT_NO  GROUP BY CONTRACT_NO, COOP_SERVICE_TYPE LIMIT 1 ) 
WHERE LM_CT1_NO IN (
    SELECT  CONTRACT_NO
    FROM tmp C
    WHERE C.CONTRACT_NO=A.LM_CT1_NO
);

修改后sql执行效果，提升不大。

总结

遇到慢查询sql语句，先创建索引进行优化，合理的索引解决90%的性能问题，如果解决不了再改写sql语句。最后索引、改写sql都解决不了，尝试调整业务、数据库技术来解决，这种方法成本非常高。