当前位置：首页 > ops >正文

03-生产问题-慢SQL-20250926

ops 2025/9/7 8:59:37

1 背景说明

业务表： tis_manual_catalog (目录)
tis_manual_catalog_feature 跟epc_aic1_model_module_spec 特征值的匹配关系

2 第一版

2.1 SQL

SELECT DISTINCT AA.*FROM tis_manual_catalog AALEFT JOIN tis_manual_catalog_feature BB ON AA.MANUAL_ID=BB.MANUAL_ID AND AA.`CODE`=BB.MANUAL_CATALOG_CODELEFT JOIN (SELECT B.spec_list AS FEATURE_CODEFROM tis_model_attr ALEFT JOIN epc_aic1_model_module_spec B ON A.AIC1_CODE=B.aic1_codeWHERE A.ENGINE_EMOTOR='YS210XY584,TZ210XY584') CC ON 1=1WHERE AA.IS_VALID=10041001 AND AA.IS_DELETED=0 AND AA.TYPE=2 AND BB.MANUAL_ID=1920800061312004098AND(-- 反选不命中， 正选命中(为空)(BB.ID IS NOT NULLAND NOT EXISTS (SELECT 1FROM tis_model_attr ALEFT JOIN epc_aic1_model_module_spec B ON A.AIC1_CODE=B.aic1_codeWHERE JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(BB.FEATURE_INVERSE,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(B.spec_list,' ', ''), ',', '","'), '"]') AS JSON))AND A.ENGINE_EMOTOR='YS210XY584,TZ210XY584')AND ((JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(BB.FEATURE,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(CC.FEATURE_CODE,' ', ''), ',', '","'), '"]') AS JSON))ORBB.FEATURE IS NULL)OR(JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(BB.FEATURE_OR,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(CC.FEATURE_CODE,' ', ''), ',', '","'), '"]') AS JSON))ORBB.FEATURE_OR IS NULL)))OR-- 反选为空，正选命中(为空)(BB.FEATURE_INVERSE IS NULL AND BB.ID IS NOT NULLAND ((JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(BB.FEATURE,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(CC.FEATURE_CODE,' ', ''), ',', '","'), '"]') AS JSON))ORBB.FEATURE IS NULL)OR(JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(BB.FEATURE_OR,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(CC.FEATURE_CODE,' ', ''), ',', '","'), '"]') AS JSON))ORBB.FEATURE_OR IS NULL))))

2.2 执行计划

2.3 问题原因

每条数据都要匹配这个逻辑，导致性能很慢

3 第二版

去exist，使用left join 子查询

3.1 SQL

SELECT DISTINCT AA.*
FROM tis_manual_catalog AA
LEFT JOIN tis_manual_catalog_feature BB ON AA.MANUAL_ID=BB.MANUAL_ID AND AA.`CODE`=BB.MANUAL_CATALOG_CODE
LEFT JOIN sbom_new_bom_vehicle_feature CC ON CC.VEHICLE_MATERIAL_NO="SC6481ECABEV.CNH1001+00069.WE1" AND CC.IS_VALID=10041001 AND CC.IS_DELETED=0
LEFT JOIN (-- 反选有交集SELECT DISTINCT A.MANUAL_ID, A.MANUAL_CATALOG_CODEFROM tis_manual_catalog_feature ALEFT JOIN sbom_new_bom_vehicle_feature B ON B.VEHICLE_MATERIAL_NO="SC6481ECABEV.CNH1001+00069.WE1" AND B.IS_VALID=10041001 AND B.IS_DELETED=0WHERE A.MANUAL_ID=1946533880278863873AND JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(A.FEATURE_INVERSE,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(B.FEATURE_CODE,' ', ''), ',', '","'), '"]') AS JSON))
) DD ON BB.MANUAL_ID=DD.MANUAL_ID AND BB.MANUAL_CATALOG_CODE=DD.MANUAL_CATALOG_CODE
WHERE AA.IS_VALID=10041001 AND AA.IS_DELETED=0 AND AA.TYPE=2 AND BB.MANUAL_ID=1946533880278863873
AND
(-- 反选不命中， 正选命中(为空)(DD.MANUAL_CATALOG_CODE IS NULLAND JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(BB.FEATURE_INVERSE,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(CC.FEATURE_CODE,' ', ''), ',', '","'), '"]') AS JSON))=0AND ((JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(BB.FEATURE,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(CC.FEATURE_CODE,' ', ''), ',', '","'), '"]') AS JSON))ORBB.FEATURE IS NULL)OR(JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(BB.FEATURE_OR,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(CC.FEATURE_CODE,' ', ''), ',', '","'), '"]') AS JSON))ORBB.FEATURE_OR IS NULL)))OR-- 反选为空，正选命中(为空)(BB.FEATURE_INVERSE IS NULL AND BB.ID IS NOT NULLAND ((JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(BB.FEATURE,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(CC.FEATURE_CODE,' ', ''), ',', '","'), '"]') AS JSON))ORBB.FEATURE IS NULL)OR(JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(BB.FEATURE_OR,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(CC.FEATURE_CODE,' ', ''), ',', '","'), '"]') AS JSON))ORBB.FEATURE_OR IS NULL)))
)

3.2 执行计划

生产执行时间：5.281527秒

3.3 改进

3.4 问题原因

猜测可能是因为JSON_OVERLAPS导致的性能过低，换成了find_in_set

4 第三版本

4.1 SQL

SELECT DISTINCT AA.*
FROM tis_manual_catalog AA
LEFT JOIN tis_manual_catalog_feature BB ON AA.MANUAL_ID=BB.MANUAL_ID AND AA.`CODE`=BB.MANUAL_CATALOG_CODE
LEFT JOIN sbom_new_bom_vehicle_feature CC ON CC.VEHICLE_MATERIAL_NO="SC6481ECABEV.CNH1001+00069.WE1" AND CC.IS_VALID=10041001 AND CC.IS_DELETED=0
LEFT JOIN (-- 反选有交集SELECT DISTINCT A.MANUAL_ID, A.MANUAL_CATALOG_CODEFROM tis_manual_catalog_feature ALEFT JOIN sbom_new_bom_vehicle_feature B ON B.VEHICLE_MATERIAL_NO="SC6481ECABEV.CNH1001+00069.WE1" AND B.IS_VALID=10041001 AND B.IS_DELETED=0WHERE A.MANUAL_ID=1946533880278863873 AND A.FEATURE_INVERSE IS NOT NULLAND FIND_IN_SET(B.FEATURE_CODE, A.FEATURE_INVERSE)
) DD ON BB.MANUAL_ID=DD.MANUAL_ID AND BB.MANUAL_CATALOG_CODE=DD.MANUAL_CATALOG_CODE
WHERE AA.IS_VALID=10041001 AND AA.IS_DELETED=0 AND AA.TYPE=2 AND BB.MANUAL_ID=1946533880278863873
AND
(-- 反选不命中， 正选命中(为空)(DD.MANUAL_CATALOG_CODE IS NULLAND (BB.FEATURE_INVERSE IS NULL OR FIND_IN_SET(CC.FEATURE_CODE, BB.FEATURE_INVERSE)=0)AND ((FIND_IN_SET(CC.FEATURE_CODE, BB.FEATURE)ORBB.FEATURE IS NULL)OR(FIND_IN_SET(CC.FEATURE_CODE, BB.FEATURE_OR)ORBB.FEATURE_OR IS NULL)))OR-- 反选为空，正选命中(为空)(BB.FEATURE_INVERSE IS NULL AND BB.ID IS NOT NULLAND ((FIND_IN_SET(CC.FEATURE_CODE, BB.FEATURE)ORBB.FEATURE IS NULL)OR(FIND_IN_SET(CC.FEATURE_CODE, BB.FEATURE_OR)ORBB.FEATURE_OR IS NULL)))
)

4.2 执行计划

生产执行时间：时间降低了很多，是不是说明FIND_IN_SET性能比JSON_OVERLAPS好呢?

5 补充

后续发现在第二个版本中，因为BB.FEATURE_INVERSE、BB.FEATURE、BB.FEATURE_OR这三个字段为NULL的数据量特别多，走JSON_OVERLAPS的频率变高导致性能下降，其实也可以在第二版本上增加NULL值处理

SELECT DISTINCT AA.*
FROM tis_manual_catalog AA
LEFT JOIN tis_manual_catalog_feature BB ON AA.MANUAL_ID=BB.MANUAL_ID AND AA.`CODE`=BB.MANUAL_CATALOG_CODE
LEFT JOIN sbom_new_bom_vehicle_feature CC ON CC.VEHICLE_MATERIAL_NO="SC6481ECABEV.CNH1001+00069.WE1" AND CC.IS_VALID=10041001 AND CC.IS_DELETED=0
LEFT JOIN (-- 反选有交集SELECT DISTINCT A.MANUAL_ID, A.MANUAL_CATALOG_CODEFROM tis_manual_catalog_feature ALEFT JOIN sbom_new_bom_vehicle_feature B ON B.VEHICLE_MATERIAL_NO="SC6481ECABEV.CNH1001+00069.WE1" AND B.IS_VALID=10041001 AND B.IS_DELETED=0WHERE A.MANUAL_ID=1946533880278863873 AND A.FEATURE_INVERSE IS NOT NULLAND JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(A.FEATURE_INVERSE,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(B.FEATURE_CODE,' ', ''), ',', '","'), '"]') AS JSON))
) DD ON BB.MANUAL_ID=DD.MANUAL_ID AND BB.MANUAL_CATALOG_CODE=DD.MANUAL_CATALOG_CODE
WHERE AA.IS_VALID=10041001 AND AA.IS_DELETED=0 AND AA.TYPE=2 AND BB.MANUAL_ID=1946533880278863873
AND
(-- 反选不命中， 正选命中(为空)(BB.FEATURE_INVERSE IS NOT NULLAND DD.MANUAL_CATALOG_CODE IS NULLAND JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(BB.FEATURE_INVERSE,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(CC.FEATURE_CODE,' ', ''), ',', '","'), '"]') AS JSON))=0AND ((BB.FEATURE IS NOT NULLAND JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(BB.FEATURE,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(CC.FEATURE_CODE,' ', ''), ',', '","'), '"]') AS JSON))ORBB.FEATURE IS NULL)OR(BB.FEATURE_OR IS NOT NULLAND JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(BB.FEATURE_OR,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(CC.FEATURE_CODE,' ', ''), ',', '","'), '"]') AS JSON))ORBB.FEATURE_OR IS NULL)))OR-- 反选为空，正选命中(为空)(BB.FEATURE_INVERSE IS NULL AND BB.ID IS NOT NULLAND ((BB.FEATURE IS NOT NULLAND JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(BB.FEATURE,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(CC.FEATURE_CODE,' ', ''), ',', '","'), '"]') AS JSON))ORBB.FEATURE IS NULL)OR(BB.FEATURE_OR IS NOT NULLAND JSON_OVERLAPS(CAST(CONCAT('["', REPLACE(REPLACE(BB.FEATURE_OR,' ', ''), ',', '","'), '"]') AS JSON), CAST(CONCAT('["', REPLACE(REPLACE(CC.FEATURE_CODE,' ', ''), ',', '","'), '"]') AS JSON))ORBB.FEATURE_OR IS NULL)))
)