一、left join 优化

一、left join 优化

优化前：

SELECT distinct bs.online_store_name onlineStoreName,
                ms.online_store_id   onlineStoreId,
                '未生效'             monitorStatus,
from t_warning_monitor_task mk
         left join t_warning_monitor_online_store ms on mk.id = ms.task_id
         left join t_base_online_store bs on ms.online_store_id = bs.online_store_id
where mk.monitor_type = 2
  and (monitor_status = 1 or monitor_status = 2)
  and ms.online_store_id not in (<include refid="notInOnlineStoreSql"></include>)

优化后：

        SELECT
        bs.online_store_name onlineStoreName,
        mks.online_store_id onlineStoreId,
        '未生效' monitorStatus
        from (select ms.online_store_id online_store_id, mk.id id
                from (
                        select * from t_warning_monitor_task where monitor_type = 2 and (monitor_status = 1 or monitor_status = 2)) mk
                        join t_warning_monitor_online_store ms on mk.id = ms.task_id
                        group by ms.online_store_id, mk.id
            ) mks
        left join (<include refid="notInOnlineStoreSql"></include>) r on r.online_store_id = mks.online_store_id
        join t_base_online_store bs on mks.online_store_id = bs.online_store_id
        where r.online_store_id is null

优化三个地方：

1.left join的主表尽量小

优化前：主表是没经过过滤的，且条件跟此表没有关联
即：select * from A left join B on ... where A.xxx = xxx
优化后：先将原先的主表进行Where查询条件后，再做主表
即：select * from (select * from A where A.xxx=xxx) left join B on ...

2.干掉distinct，用group by

优化前：因为要去重某一个字段而distinct了所有字段
优化后：只group by自己需要去重的字段

3.干掉not in ，解决查询存在A表而不存在B表的数据

not in 可能走索引
但是如果not in的数据较多，则不会走索引
优化后：
select A.ID from A left join B on A.ID=B.ID where B.ID is null

mysql

sql优化