MYSQL一个优化的过程

背景：优化一个20数据的表查询 ###优化前 EXPLAIN SELECT DISTINCT ect.contract_no AS contractCode, ect.contract_name AS contractName, NULL AS supplierId, ect.b_name AS supplierName FROM `eva_test` ect LEFT JOIN td_outline_test toa ON ect.`contract_no` = toa.`contract_no` WHERE toa.`contract_no` IS ORDER BY ect.`id`;

###优化后 EXPLAIN SELECT ect.contract_no AS contractCode, ect.contract_name AS contractName, NULL AS supplierId, ect.b_name AS supplierName FROM `eva_test` ect LEFT JOIN td_outline_test toa ON ect.`contract_no` = toa.`contract_no` WHERE toa.`contract_no` IS NULL ORDER BY ect.`id`; ###查询合同编号的数量大于1的合同

###使用EXISTS ，在使用优化后 EXPLAIN SELECT ect_out.contract_no AS contractCode, ect_out.contract_name AS contractName, NULL AS supplierId, ect_out.b_name AS supplierName FROM eva_test ect_out WHERE NOT EXISTS (SELECT toa.`contract_no` FROM td_outline_test toa WHERE ect_out.`contract_no` = toa.`contract_no`) ORDER BY ect_out.`id`; ###使用eva_test.contract_no字段的索引,不推荐使用 SELECT ect_out.contract_no AS contractCode, ect_out.contract_name AS contractName, NULL AS supplierId, ect_out.b_name AS supplierName FROM eva_test ect_out WHERE ect_out.`contract_no` NOT IN (SELECT DISTINCT ect.contract_no FROM `eva_test` ect LEFT JOIN td_outline_test toa ON ect.`contract_no` = toa.`contract_no` WHERE toa.`contract_no` IS NOT NULL) ; ####辅助查询 SELECT contract_no, COUNT(contract_no) AS COUNT FROM eva_test GROUP BY contract_no HAVING COUNT(contract_no) > 1 ORDER BY countDESC;