not in 优化

总记录：

number_balance   8000

no_attribution   4000

business_products_num  50000

关系：

number_balance  1-1 no_attribution ;

number_balance   1:n business_products_num  

 

 

NOT IN 查询  28s

SELECT * FROM number_balance  n WHERE n.`status` = 1  AND number_id 

NOT  IN (SELECT number_id  FROM no_attribution   UNION SELECT number_id  FROM business_products_num  )

 

两个 NOT  EXISTS 查询  22s

SELECT * FROM number_balance  n WHERE n.`status` = 1  AND 

NOT  EXISTS (SELECT 1  FROM no_attribution noa WHERE n.`number_id` = noa.number_id )

AND  NOT EXISTS (SELECT 1  FROM business_products_num nob WHERE n.`number_id` = nob.number_id)

 

1个 NOT  EXISTS 查询  <1s

 SELECT * FROM number_balance  n WHERE  n.status = 1    AND NOT  EXISTS (SELECT 1 FROM ( SELECT number_id FROM no_attribution UNION SELECT number_id FROM business_products_num) 

 AS haved WHERE n.`number_id` = haved.number_id)