mysql sql查询优化记录

数1000羊的星星

已于 2022-12-05 12:08:11 修改

阅读量522

点赞数

分类专栏： mysql 文章标签： mysql sql mybatis

于 2022-12-05 11:55:19 首次发布

本文链接：https://blog.csdn.net/liujia_It/article/details/128182778

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

mysql查询优化记录

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))