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'))