一则 MySQL 子查询改写 SQL 优化小案例

作者：马文斌时间：2024-07-09 阅读数：178 人阅读

一篇短小精悍的 SQL 优化案例！
作者：马文斌，MySQL/Redis 爱好者～
爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 600 字，预计阅读需要 2 分钟。

事情是这样的

近日开发那边又丢了一条 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'
)
)
);

执行计划

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

优化思路

减少不必要的 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')
)
);