SQL语句如何进行优化？

最近有个项目的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查询条件啊。
请各位给出具体意见，如建立怎么样的索引或者联合索引谢谢！

你试下这个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表关联就行了

on就相当于where
你所有需要进行筛选的列，都要加索引
加了索引之后搜索时间复杂度是O(log(N)),不加索引的时间复杂度是O(N)，那差的不是一点半点

可以考虑在一下表中的字段上建立索引：
t1表 type_code独立索引、 type_sort和create_time建立联合索引
t2表 category
t3表 dept_id_
u表 dept_id

然后执行sql看下执行时间

这条SQL语句没有where条件，查询的时候会查询所有的数据。观察一下数据中有没有字段是空值不需要查询的，或者加上不需要查询的数据限制条件。会提高一些效率的。

where或者on 条件语句尽量要建索引,排序字段也要注意索引, 整体语句避免全表形式的查询,有条件的话可以根据实际业务形式优化数据库数据结构

-- 添加索引这几个字段的索引没建的话，创建下
alter table hr_regime_info add index hr_regime_info_idx (category) ;
alter table hr_regime_department add index hr_regime_department_idx (regime_id_) ;
alter table hr_regime_department add index hr_regime_department_idx2 (type_) ;
alter table hr_regime_department add index hr_regime_department_idx3 (dept_id_) ;
alter table sys_dept add index sys_dept_idx (dept_id) ;
alter table sys_user add index sys_user_idx (dept_id) ;

-- 查询语句，试试效果
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
left JOIN hr_regime_info t2 ON t1.id = t2.category
left JOIN hr_regime_department t3 ON t2.id = t3.regime_id_ AND t3.type_ = 2
left JOIN sys_dept d ON t3.dept_id_ = d.dept_id
left 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

慢在这个地方：

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
)

请验证采纳，谢谢！

1.注意基础表选择。
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。其他数据库雷同。所以开始优化：

1.Oracle 最后（右）的表为基础表,JOIN就以from A JOIN B,A为基础表，where就以from A,B where...,B为基础表
2.当2个表关联以大表（条数多）为基础表关联小表。
3.当3个以上的表关联，以交叉表为基础表。例如A关联B，B关联C,则以B为基础表

2.where顺序
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

更多参考：
《Oracle对于多个大表关联操作如何优化速度？》, 一起来围观吧 https://blog.csdn.net/WuLex/article/details/79418479?utm_source=app&app_version=5.0.1&code=app_1562916241&uLinkId=usr1mkqgl919blen

初步优化后脚本（请根据表条数可以进一步调整优化）
【以下是以 d,u表关联t3,如果t1,t2是大表（比d,u表大）可以让t3先与d,u关联】

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
(
select t3.regime_id_ from
sys_dept d
JOIN hr_regime_department t3
ON t3.dept_id_ = d.dept_id and t3.type_ = 2
JOIN sys_user u
ON d.dept_id = u.dept_id
) t3，
hr_regime_info t2，
hr_regime_category t1
where
t2.category = t1.id
and t2.id = t3.regime_id_
GROUP BY
t1.type_code
ORDER BY
t1.type_sort,
t1.create_time ASC

我觉得,把 t3.type_ = 2 这个条件,从on 里去掉,放到where后面,可能会比你目前要好

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_ 
    JOIN sys_dept d ON t3.dept_id_ = d.dept_id
    JOIN sys_user u ON d.dept_id = u.dept_id 
where t3.type_ = 2
GROUP BY
    t1.type_code 
ORDER BY
    t1.type_sort,
    t1.create_time ASC