sql优化实例

1.尽量不生成子表或者将子表的数据量控制到最小（子表只能顺序读取，没有任何索引）

原有sql

    from
      (SELECT task_id, user_id FROM mtcrm_task.item WHERE status = 0 and is_deleted = 0 and is_archived = 0) item
    left join
      (SELECT id, status FROM  mtcrm_task.task WHERE status = 1 AND is_deleted = 0 ) task
    on item.task_id = task.id
    group by item.user_id

生成大量子表，而且因为left join 的原因有很多错误数据。（正常应该是inner join）

修改后

select item.user_id from mtcrm_task.item item
, mtcrm_task.task task
where item.status = 0 and item.is_deleted = 0 and item.is_archived = 0
and task.status = 1 and task.is_deleted = 0
and item.task_id = task.id
group by item.user_id

相当于 inner join

也等同于

select item.user_id from mtcrm_task.item item
left join mtcrm_task.task task
on item.task_id = task.id
where item.status = 0 and item.is_deleted = 0 and item.is_archived = 0
and task.status = 1 and task.is_deleted = 0
group by item.user_id

explain 看一下发现使用了索引

分析执行顺序 ​TODO @胡晓婕 感觉是先执行 where，结果才group by ,而结果是一个临时表

优化效果

30-70m -> 1s内

https://www.cnblogs.com/huminxxl/p/3149097.html