记录一次慢SQL优化：大表关联小表-＞拆解为单表查询

前言：

最近一段时间总是会收到数据库CPU报警

一查发现有一个特别慢的SQL，调用的频率很高，并且查一次SQL15s以上，十分影响性能

SQL分析

这个sql是一个分页查询的sql，每次查出10条主表是cfg_category_organization （大约50W+数据）
需要补充部分信息：cfg_category （大约1000条数据）、cfg_org （大约2W数据）

按说这个数据量不大，但是就是非常慢

select
	b.*,
	co.organization_path,
	co.organization_full_name,
	a.name,
	a.status
from
	cfg_category_organization b
inner join cfg_category a on b.category_id = a.id
inner join cfg_org co on b.organization_code = co.organization_code
where
	b.is_delete = 0
	and co.is_delete = 0
	and a.is_delete = 0
	and co.organization_path like concat('/001/002/003/004', "%")
	and b.category_id = 7
order by
	b.status desc,
	b.update_time desc
limit 10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

用explain看一下，发现表cfg_category 、cfg_org 的rows是很少的，只有cfg_category_organization 几乎进行了全表扫描，开销较大。

SQL优化

因此我们可以采用，把小表查询出来的结果作为条件，in到大表中，并且对应的大表字段都是有索引的
1.cfg_category 表单独拎出来作为一个SQL，当有这张表的字段作为查询条件时，就select出这张表的key：

select id from cfg_category where is_delete = 0 and id = 7
1

如果说返回参数为0，后面的内容均不需要查了，肯定联查不出来任何数据，这样就减少了一小部分无效查询

2.同理cfg_org 表也单独拎出来，如果有这张表的字段作为查询条件的时候，就select出这张表的key：

select organization_code   from cfg_org where is_delete = 0 and organization_path like concat('/001/002/003/004', "%")
1

同理，如果说返回参数为0，后面的内容均不需要查了，肯定联查不出来任何数据，这样就减少了一小部分无效查询

3.如果前两步都能查出对应的数据，则对最大的表进行查询：

select
	b.*
from
	cfg_category_organization b
where
	b.is_delete = 0
	and b.organization_code in('004', '005')
	and b.category_id in (7)
order by
	b.status desc,
	b.update_time desc
limit 10
1
2
3
4
5
6
7
8
9
10
11
12

如果说没有任何查询条件，则SQL就是如下情况:

select
	b.*
from
	cfg_category_organization b
where
	b.is_delete = 0
order by
	b.status desc,
	b.update_time desc
limit 10
1
2
3
4
5
6
7
8
9
10

分页查询，充其量单表查询返回的结果就是10条，那么查出的10条，再组装一下，category_id 、organization_code 字段，分别去小表查一次，再利用Java代码拼接出来即可

select name,status from cfg_category where id in (7);
select organization_path,organization_full_name from cfg_org where organization_code in ('004','005');
1
2

优化后效果不错：平均15-20s查询的慢SQL，优化到0.3-0.6s

总结与改进

在上面优化，将一次慢SQL查询修改为5次单表查询+Java代码的辅助拼接，实际上还可以再次优化，如果说cfg_category 、cfg_org是有字段作为查询条件的，那么在前面查的那次就可以将对应的信息查出来，然后利用java代码获取key值(category_id、organization_code)
并且，根据业务场景来说，这两张小表的更新频率是比较低的，可以按照查询条件来做缓存，较少数据库的压力，进一步优化。

以上就是本次优化的心得，欢迎大家与我交流~