子查询改写的优化场景

转载

wx5b8b656889613 2024-07-16 14:10:47

文章标签 java 开发语言子查询 SQL sed 文章分类 Html/CSS 前端开发

技术社群的这篇文章《技术分享 | 一则子查询改写 SQL 优化小案例》给我们讲解了一个数据库SQL子查询改写的案例场景，解决路径是"减少不必要的JOIN和子查询的引用"，提高执行效率。

场景不是很复杂，但这个优化过程和方向，还是值得我们关注和了解，它涉及到的一种路径就是"少做事儿"。如果一个SQL中不需要多次访问同一张表，或者能通过索引，避免不必要数据的访问，甚至通过评估，这条SQL就根本是不需要执行的，不需要"做事儿"，自然就会提高SQL执行的效率，这是显而易见的，可能是相对简单，但是又可能被经常忽略的。

1事情是这样的

近日开发那边又丢了一条 SQL 过来，查询 9 秒，看看有什么优化建议。作为 DBA，优化 SQL 乃是祖传技能，咱们就不废话，直接干他！

原 SQL

原 SQL 查询竟然需要 9 秒出结果……

SELECT
 a.id,
 a.order_no,
 a.line_no,
 a.customer_code,
 a.customer_name,
 a.cost_item,
 a.cost_item_name,
 a.payee_type,
 a.payee_code,
 a.payee_name,
 a.tax_plan,
 a.expenses_amount,
 a.pre_tax_amount,
 a.tax_amount,
 a.start_date,
 a.end_date,
 a.accrued_amount,
 a.paid_amount,
 a.reimbursed_amount,
 a.is_close,
 a.payment_type,
 a.settlement_date,
 a.source_order_no,
 a.source_order_line,
 a.invoice_type,
 a.del_flag,
 a.create_time,
 a.create_by_id,
 a.create_by,
 a.update_time,
 a.update_by_id,
 a.update_by,
 a.remark,
 a.shop_code,
 a.close_reason,
 a.close_content,
 a.budget_period,
 a.shop_name,
 a.product_category_code,
 a.product_category_name,
 a.product_code,
 a.product_name,
 a.product_path_code,
 a.shop_classify_name,
 a.shop_level_require,
 a.strategy_code,
 a.product_path_name
FROM
 expense_application_detail a
LEFT JOIN expense_application b ON a.order_no = b.order_no
AND b.del_flag = 0
WHERE
 a.del_flag = 0
AND b.data_source = 8
AND b.order_type = 'B2BFYSQ01'
AND b.order_status IN (3, 8)
AND a.is_close = 0
AND EXISTS (
 SELECT
  1
 FROM
  expense_application_detail d1
 LEFT JOIN base_customer_info c1 ON d1.customer_code = c1. CODE
 AND c1.del_flag = 0
 LEFT JOIN budget_system_ecommerce_channel d ON d. CODE = c1.business_channel_code
 AND d.del_flag = 0
 WHERE
  d1.order_no = a.order_no
 AND d.del_flag = 0
 AND d.business_category_code = '01'
 AND (
  d.business_mode_code = '33'
  OR (
   d.business_mode_code = 'C010106'
   AND d. CODE = 'C01010602'
  )
 )
);

子查询改写的优化场景_sed

查询结果

执行计划

执行计划看 type 都是最优了，难道不能再优化了吗？不，仔细分析下还是有的。

子查询改写的优化场景_SQL_02

2优化思路

减少不必要的 JOIN 和子查询引用。

子查询中的 expense_application_detail 表与外部查询中的 a 表是同一张表，尝试 将子查询条件提到外面和驱动表 JOIN，避免 EXISTS 子查询 expense_application_detail 表的重复扫描。

优化后 SQL

优化后 SQL 只需要 0.2 秒出结果，效率了提高几个数量级！

SELECT
    a.id,
    a.order_no,
    a.line_no,
    a.customer_code,
    a.customer_name,
    a.cost_item,
    a.cost_item_name,
    a.payee_type,
    a.payee_code,
    a.payee_name,
    a.tax_plan,
    a.expenses_amount,
    a.pre_tax_amount,
    a.tax_amount,
    a.start_date,
    a.end_date,
    a.accrued_amount,
    a.paid_amount,
    a.reimbursed_amount,
    a.is_close,
    a.payment_type,
    a.settlement_date,
    a.source_order_no,
    a.source_order_line,
    a.invoice_type,
    a.del_flag,
    a.create_time,
    a.create_by_id,
    a.create_by,
    a.update_time,
    a.update_by_id,
    a.update_by,
    a.remark,
    a.shop_code,
    a.close_reason,
    a.close_content,
    a.budget_period,
    a.shop_name,
    a.product_category_code,
    a.product_category_name,
    a.product_code,
    a.product_name,
    a.product_path_code,
    a.shop_classify_name,
    a.shop_level_require,
    a.strategy_code,
    a.product_path_name
FROM
    expense_application_detail a
LEFT JOIN expense_application b ON a.order_no = b.order_no
    AND b.del_flag = 0
WHERE
    a.del_flag = 0
    AND b.data_source = 8
    AND b.order_type = 'B2BFYSQ01'
    AND b.order_status IN (3, 8)
    AND a.is_close = 0
    AND EXISTS (
        SELECT 1
        FROM base_customer_info c1
        LEFT JOIN budget_system_ecommerce_channel d ON c1.business_channel_code = d.code
            AND d.del_flag = 0
        WHERE c1.code = a.customer_code
            AND c1.del_flag = 0
            AND d.business_category_code = '01'
            AND (
                d.business_mode_code = '33'
                OR (d.business_mode_code = 'C010106' AND d.code = 'C01010602')
            )
    );

子查询改写的优化场景_sed_03