2019独角兽企业重金招聘Python工程师标准>>> 
背景：优化一个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;