mysql查询优化记录
mysql查询优化
原来也优化过select,但是没有总结,总结记录下
1.根据查询条件,动态拼接sql,将不需要的连表去除
得益于mybatis动态拼接,在条件查询中,可以是否为空判断,是否需要连表进行查询
FROM tbl_melt mat
<choose>
<when test="((request.archiveName != null and request.archiveName != '') or (request.archiveNum != null and request.archiveNum != '') ) and (request.matType == @com.voiceai.common.constant.MatConstant$sourceType@NON_STANDARD)">
LEFT JOIN tbl_melt_fb fbjc ON mat.id = fbjc.mat_id
</when>
<when test="(request.archiveName != null and request.archiveName != '') or (request.archiveNum != null and request.archiveNum != '')">
LEFT JOIN tbl_melt_ajxx archive ON archive.id = mat.ajxx_id
</when>
</choose>
<if test="(request.personType != null) or (request.nameOrAccount != null)">
LEFT JOIN tbl_melt_ajxx_person_info person ON mat.person_id = person.id
</if>
<if test="request.tagIds != null and request.tagIds.size() != 0">
LEFT JOIN tbl_system_tag_business tag ON tag.jc_code = mat.jcbh
</if>
2.单表分页查询,在索引不满足需要回表的情况下,采用小表连大表的方式
如果在单表条件查询,并且查询的返回列需要回表的时候。这种方式一定是命中索引并且需要回表的时候 ,这种在查分页页数较大时,效果最好
select
( mat.id ) AS jcxxId,
mat.jcbh,
mat.jcmc,
mat.quality_detection,
mat.detection_info,
mat.reject_reason,
mat.state,
mat.submitter,
mat.submit_time,
mat.reviewer,
mat.review_time
from tbl_melt_jcxx mat
inner join
(SELECT
id
FROM
tbl_melt_jcxx FORCE INDEX(i_melt_state_sourcetype_reviewtime)
WHERE
state IN (-3, 4, -4)
AND source_type = 3
ORDER BY review_time DESC
LIMIT 0, 100) inner_mat on inner_mat.id = mat.id
3.在无条件需要连表的情况下,去除连表,分组查询出来后再通过查出的相关主键进行查询
有时候查询的结果在几张不同的表中,但是关联表没有需要查询的字段,表数据确实很大,先通过第一次sql查询出来相关关联字段,再由代码层面去查询其他表数据。
这个方式在表关联4张以上的表,可以试试,
同时分组查询出来的数据,尽量先查出来关联的集合id,再通过sql一次查询出来,不要放在循环里面去查,这样很浪费数据库连接资源,这些操作可以通过代码来操作
List<GroupMatListResponse> materials = tblMeltJcxxMapper.getMatsByCondition(request,offset,actualSize);
List<Integer> personIds=new ArrayList<>();
List<Integer> jcxxIds=new ArrayList<>();
List<String> codes=new ArrayList<>();
List<Integer> ajIds=new ArrayList<>();
materials.forEach(GroupMatListResponse -> {
personIds.add(GroupMatListResponse.getPersonId());
jcxxIds.add(GroupMatListResponse.getMatId());
codes.add(GroupMatListResponse.getMaterialId());
ajIds.add(GroupMatListResponse.getAjxxId());
});
//优化条件下的连表
Map<String, List<SearchTagByJcbhsVo>> tagByJcbhsVoMap=tblSystemTagService.getTagByJcbhs(codes);
Map<Integer, MatAjxxVo> matAjxxVoMap=fbjcService.getAjxx(jcxxIds,ajIds,request.getMatType());
//优化条件下的连表
Map<Integer,TblMeltAjxxPersonInfo> personInfoMap=ajxxPersonInfoService.queryPersonByIds(personIds);
if (CollectionUtils.isNotEmpty(materials)) {
for (GroupMatListResponse mat : materials) {
mat.setType(type(request.getMatType()));
mat.setMatTagList(tagByJcbhsVoMap.get(mat.getMaterialId()));
mat.setPersonInfo(personInfoMap);
mat.setAjInfo(matAjxxVoMap,request.getMatType());
}
}
4.分组查询中的排序问题
有些比较特殊的(奇葩)的排序条件,甚至多个排序条件,这个时候建议先和产品干架😒,好吧,干不过的话建议好好商量
1.减少排序条件,如果多个条件,真的很影响效率,在数据量稍大的情况下,甚至在十万级别就扛不住,更不提百万级别,在第一个排序条件下,再在里面找符合第二个的条件
2.尽量根据排序条件建联合索引,或者单个索引,这样大幅提升速度,在单表的情况下还可以联合 第二个方案 检索主键查
5.全部采用手动分页,统计总数,分页插件会导致查询一些不必要的字段,增加耗时
在查询大批量的情况下,分页统计总数,建议只查一个主键字段。在现在的分页插件拼装下,会将插叙语句包装一层,导致回表。可以自己的需求封装一个自定义拦截器实现。(或者有其他方式?)
6.select字段因1对多关联关系需要distinct
有时候需要查询distinct字段,是因为1对多的关联关系,那么可以先查关联关系的id
mat.organization_name AS orgName, mat.organization_code AS orgNo, mat.submitter, mat.submit_time, mat.reviewer mat.review_time,
per.type AS personType, per. NAME AS personName, per.zh_account
FROM
tb1_melt_joxx mat
LEFT JOIN tb1 melt_fbjc fbjc ON mat.id = fbjc.mat_id
LEFT JOIN tb1 _melt_ajxx _person_info per ON mat.person_id = per.id
LEFT JOIN tb1 system tag business tag ON tag.jc_code = mat.jcbh
WHERE
mat.state IN (- 3, 4,
- 4 )
AND mat.source type = 3 |
AND mat.jebh in (select je_code from tb1_system_tag_business where tag_id in (1, 2))
7.使用强制索引FORCE INDEX或者灵活使用其他方式索引
在经过mysql优化器后,mysql不一定会选择你想命中的索引,尤其是存在in的情况,或者其他函数情况。如果确定该语句长期使用,预估数据的情况下,可以酌情考虑使用强制索引。我在使用in条件后,使用强制索引的情况确实会好些。
8.explain关键字使用
这个是分析sql的利器,就不多说了
总结
sql优化需要结合业务,和mysql的一些配置,如果允许的条件下,可以修改一些配置,如mysql缓存,超时时间等
其他大佬总结的
链接: MySQL模糊查询性能优化
MySql基础知识总结(SQL优化篇)