背景：优化一个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;