SQL语句如何进行优化？

Question

最近有个项目的slow SQL需要优化，各表数据不是很大（最多的表50W）关联的表也不多，但就是跑了4S。

SELECT
    t1.id,
    t1.type_code,
    t1.type_name,
    t1.cjr,
    t1.create_time,
    t1.parent_id,
    t1.is_parent,
    t1.type_sort,
    GROUP_CONCAT( DISTINCT t2.id ) AS regime_id_list 
FROM
    hr_regime_category t1
    JOIN hr_regime_info t2 ON t1.id = t2.category
    JOIN hr_regime_department t3 ON t2.id = t3.regime_id_ 
    AND t3.type_ = 2
    JOIN sys_dept d ON t3.dept_id_ = d.dept_id
    JOIN sys_user u ON d.dept_id = u.dept_id 
GROUP BY
    t1.type_code 
ORDER BY
    t1.type_sort,
    t1.create_time ASC

没优化过SQL语句，explain里出现using where是什么情况？语句没有where查询条件啊。
请各位给出具体意见，如建立怎么样的索引或者联合索引谢谢！

Answer 1

你试下这个sql:

SELECT
t1.id,
t1.type_code,
t1.type_name,
t1.cjr,
t1.create_time,
t1.parent_id,
t1.is_parent,
t1.type_sort,
GROUP_CONCAT( DISTINCT t2.id ) AS regime_id_list
FROM
hr_regime_category t1
JOIN hr_regime_info t2 ON t1.id = t2.category
where EXISTS (
select 1 from hr_regime_department t3 JOIN sys_dept d ON t3.dept_id_ = d.dept_id
JOIN sys_user u ON d.dept_id = u.dept_id
where t2.id = t3.regime_id_ AND t3.type_ = 2
)
GROUP BY
t1.type_code
ORDER BY
t1.type_sort,
t1.create_time ASC

你这个我觉得应该从设计上解决这个问题，你这个t2表加个是否发生过t3表业务得字段，在t3发生业务得时候更新一下t2表，那样你查就相当于直接查t1 t2表关联就行了