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

原创
07/05 16:21
阅读数 1.1K

一篇短小精悍的 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')
            )
    );

查询结果

总结

在原始查询中,expense_application_detail 表被多次引用,一次在主查询中,另一次在嵌套的 EXISTS 子查询中。这种结构可能导致数据库引擎多次扫描相同的表,即使是在同一个查询中。

优化思路:减少不必要的 JOIN 和子查询引用

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

更多技术文章,请访问:https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台,覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库,为开发和运维提供流程自动化能力,提升上线效率,提高数据质量。

✨ Github:https://github.com/actiontech/sqle

📚 文档:https://actiontech.github.io/sqle-docs/

💻 官网:https://opensource.actionsky.com/sqle/

👥 微信群:请添加小助手加入 ActionOpenSource

🔗 商业支持:https://www.actionsky.com/sqle

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
1 收藏
0
分享
返回顶部
顶部