delete + in/not in 优化、优化与db的交互次数、优化多表关联、慢SQL场景分析

delete + in 优化

delete from table_a where id in (select id from table_b);

优化后：
delete a 代表： 删除 a表中的记录

delete a from table_a a, table_b b  where a.id = b.id;

delete + not in 优化

delete t1 from  t1  left join t2 on t1.id=t2.id  where t2.id is null

not in优化

select name as Customers 
from Customers c 
	where id not in
		(select CustomerId from Orders )

优化后：

SELECT
    c.Name AS Customers
FROM
    Customers  c LEFT JOIN Orders o ON c.Id = o.CustomerId
WHERE
    o.CustomerId IS NULL

优化与db的交互次数

优化 与db的 交互次数
1、批量查询、插入
2、优化大事务

优化 多表关联

多表关联，可以优化为 多次查询单表

select name
from SalesPerson
where sales_id not in (select s.sales_id
                       from Orders o
                                left join Company c on c.com_id = o.com_id
                                left join SalesPerson s on s.sales_id = o.sales_id
                       where c.name = 'RED')

select name
from SalesPerson
where sales_id not in (
   select sales_id
   from Orders
   where com_id = 
 		  (select com_id from Company where name = 'RED'))

慢SQL场景分析