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

这是在一个项目中遇到的问题，项目原来的代码使用了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`

通过explain可以看出前后的差距

修改前：

修改后：