mysql in查询速度慢，试试改用join

最新推荐文章于 2025-04-14 14:48:53 发布

XuYYY

最新推荐文章于 2025-04-14 14:48:53 发布

阅读量1.7k

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/XuYYY/article/details/109227282

版权

mysql 专栏收录该内容

1 篇文章

订阅专栏

本文探讨了一种SQL查询优化策略，通过将原本使用的IN子查询替换为LEFTJOIN，显著提升了查询速度。在项目中，对zt_proj_invoice_req表的查询由IN操作改为了LEFTJOIN，结合内部查询优化，减少了数据处理时间，从而提高了整体查询效率。这种方法对于大数据量的数据库操作尤其有益，是数据库性能调优的一个实例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

这是在一个项目中遇到的问题，项目原来的代码使用了in进行查询，速度非常慢，后面我使用left join之后速度得到很大的提升

修改前：

SELECT
	`c`.*,
	pir_total_amount,
CASE
		
		WHEN pci.count IS NULL THEN
		0 ELSE pci.count 
	END AS contract_count 
FROM
	zt_contract_info c
	LEFT JOIN ( SELECT contract_id, count( 1 ) AS count FROM zt_proj_contract_info zpci WHERE zpci.del = 0 GROUP BY contract_id ) pci ON `pci`.`contract_id` = `c`.`id`
	LEFT JOIN (
	SELECT
		pci.contract_id,
		sum( pir.total_amount ) AS pir_total_amount 
	FROM
		zt_proj_contract_info pci
		//这里使用了in进行查询
		LEFT JOIN zt_proj_invoice_req pir ON pir.del = 0 
		AND pir.parent_id IN (
		SELECT
			max( pf.id ) AS id 
		FROM
			zt_proj_form_info pf 
		WHERE
			pf.pid = pci.pid 
			AND form_id = 14 
			AND pf.del = 0 
			AND pf.stage = 99 
		) 
		//==============
	WHERE
		pci.del = 0 
	GROUP BY
		pci.contract_id 
	) sum_pir ON `sum_pir`.`contract_id` = `c`.`id` 
WHERE
	`c`.`del` = 0 
	AND ( c.del = 0 AND c.confirm_amount != sum_pir.pir_total_amount OR sum_pir.pir_total_amount IS NULL ) 
ORDER BY
	`c`.`id`

修改后：


	SELECT
		`c`.*,
		pir_total_amount,
	CASE
			
			WHEN pci.count IS NULL THEN
			0 ELSE pci.count 
		END AS contract_count 
	FROM
		zt_contract_info c
		LEFT JOIN ( SELECT contract_id, count( 1 ) AS count FROM zt_proj_contract_info zpci WHERE zpci.del = 0 GROUP BY contract_id ) pci ON `pci`.`contract_id` = `c`.`id`
		LEFT JOIN (
		SELECT
			pci.contract_id,
			sum( maxpir.total_amount ) AS pir_total_amount 
		FROM
			zt_proj_contract_info pci
			//改为LEFT JOIN进行查询
			LEFT JOIN (
			SELECT * FROM
				zt_proj_invoice_req pir
				LEFT JOIN (
				SELECT
					pf.pid,
					pf.id AS pfid 
				FROM
					zt_proj_form_info pf 
				WHERE
					form_id = 14 
					AND pf.del = 0 
					AND pf.stage = 99 
				) maxpf ON maxpf.pfid = pir.parent_id 
				AND pir.del = 0 
			) AS maxpir ON maxpir.pid = pci.pid 
			//==============
		WHERE
			pci.del = 0 
		GROUP BY
			pci.contract_id 
		) sum_pir ON `sum_pir`.`contract_id` = `c`.`id` 
	WHERE
		`c`.`del` = 0 
		AND ( c.del = 0 AND c.confirm_amount != sum_pir.pir_total_amount OR sum_pir.pir_total_amount IS NULL ) 
ORDER BY
	`c`.`id`