SELECT에서 JOIN과 WHERE의 속도 차이

개요

1 : N의 연관관계를 맺고 있는 두 테이블이 있습니다. 이 두 테이블에서 데이터를 찾아오는 방법은 JOIN 키워드를 사용해 FK를 통해 조회하는 것과  서브쿼리를 사용해 where 키워드를 사용해 조회하는 방법이 있습니다. 물론 버전에 따라나 상황에 따라 차이가 있지만 일반적인 상황에서 두 방법 중 어떤 쿼리가 더 빠를까요?

 

 

준비

우선 환경은 다음과 같습니다. 이 부분은 테스트를 하기 위한 환경을 준비하는 단계이므로 그렇구나 하고 보시면 됩니다.

M1 MacBook Pro 2020
Memory : 16GB

MySQL : 8.0.26

또한 테스트를 위해 데이터를 많이 입력할 텐데 일일이 하기는 힘드니 스프링을 활용할 것입니다.

프로젝트 설정은 다음 깃허브에서 확인할 수 있습니다.

https://github.com/H-SChan/DB-Overload

GitHub - H-SChan/DB-Overload: 데이터베이스 쿼리 속도

 

 

우선 스프링을 사용해 데이터베이스에 데이터를 넣습니다.

그러기 위해 우선 엔티티를 만듭니다.

엔티티 구조는 다음과 같습니다.

erd

Information.class

package com.example.overloadtest.domain.main;

import com.example.overloadtest.domain.sub.SubInfo;
import lombok.Getter;

import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;

@Getter
@Entity
public class Information {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "information_id", nullable = false)
    private Long idx;

    private String divide;

    @OneToMany(mappedBy = "information", fetch = FetchType.EAGER)
    private List<SubInfo> subInfos = new ArrayList<>();

    public Information() {
    }

    public Information(String divide) {
        this.divide = divide;
    }

    // 연관관계 편의 메서드
    public void addSubInfo(SubInfo subInfo) {
        subInfo.setInformation(this);
    }

    @Override
    public String toString() {
        return "Information{" +
                "idx=" + idx +
                ", divide='" + divide + '\'' +
                '}';
    }
}

SubInfo.class

package com.example.overloadtest.domain.sub;

import com.example.overloadtest.domain.main.Information;
import lombok.Getter;

import javax.persistence.*;

@Getter
@Entity
public class SubInfo {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "sub_info_id", nullable = false)
    private Long idx;

    private String uuid;

    @ManyToOne(fetch = FetchType.LAZY)
    private Information information;

    public SubInfo() {
    }

    public SubInfo(String uuid) {
        this.uuid = uuid;
    }

    // 연관관계 편의 메서드
    public void setInformation(Information information) {
        if (this.information != null) {
            this.information.getSubInfos().remove(this);
        }
        this.information = information;
        information.getSubInfos().add(this);
    }

    @Override
    public String toString() {
        return "SubInfo{" +
                "idx=" + idx +
                ", uuid='" + uuid + '\'' +
                '}';
    }
}

대충 1 : N의 연관관계를 맺었습니다.

 

다음은 쿼리를 위한 리포지토리를 작성합니다.

 

InformationRepository.class

package com.example.overloadtest.domain.main;

import com.example.overloadtest.domain.sub.SubInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.Optional;

public interface InformationRepository extends JpaRepository<Information, Long> {
    Optional<Information> findInformationBySubInfos(SubInfo subInfos);

    @Query(value = "select * from information i where i.information_id = (select information_information_id from sub_info s where s.sub_info_id = ?)", nativeQuery = true)
    Optional<Information> findBySubInfos(Long subInfoIdx);
}

 

SubInfoRepository.class

package com.example.overloadtest.domain.sub;

import org.springframework.data.jpa.repository.JpaRepository;

public interface SubInfoRepository extends JpaRepository<SubInfo, Long> {
}

 

다음은 이제 진짜로 데이터를 입력해줍니다.

 

InitDB.class

package com.example.overloadtest;

import com.example.overloadtest.domain.main.Information;
import com.example.overloadtest.domain.main.InformationRepository;
import com.example.overloadtest.domain.sub.SubInfo;
import com.example.overloadtest.domain.sub.SubInfoRepository;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.PostConstruct;
import java.util.UUID;

@Component
public class InitDB {

    private final InitDBService initDBService;

    @Autowired
    public InitDB(InitDBService initDBService) {
        this.initDBService = initDBService;
    }

    @PostConstruct
    public void initDB() {
        initDBService.putTwentyThousands();
    }

    @Slf4j
    @Component
    @Transactional
    private static class InitDBService {
        private final InformationRepository informationRepository;
        private final SubInfoRepository subInfoRepository;

        @Autowired
        public InitDBService(InformationRepository informationRepository, SubInfoRepository subInfoRepository) {
            this.informationRepository = informationRepository;
            this.subInfoRepository = subInfoRepository;
        }

        public void putTwentyThousands() {
            if (informationRepository.count() > 0) return;
            for (int i = 1; i <= 100000; i++) {
                if (i % 7 == 0) {
                    // i가 7로 나눠질 때 main Information 생성 / 내용 -> divide7
                    Information divide7Information = new Information("divide7");

                    // i가 7로 나눠진다면 SubInfo 를 5개 추가 / 내용 -> i-j-uuid
                    for (int j = 0; j < 5; j++) {
                        SubInfo subInfoIn7 = new SubInfo(i + "-" + j + "-" + UUID.randomUUID());
                        divide7Information.addSubInfo(subInfoIn7);
                        subInfoRepository.save(subInfoIn7);
                    }
                    informationRepository.save(divide7Information);

                    log.info("divide7-{} saved", i);
                } else if (i % 5 == 0) {
                    // i가 5로 나눠질 때 main Information 생성 / 내용 -> divide7
                    Information divide5Information = new Information("divide5");

                    // i가 5로 나눠진다면 SubInfo 를 3개 추가 / 내용 -> i-j-uuid
                    for (int j = 0; j < 3; j++) {
                        SubInfo subInfoIn5 = new SubInfo(i + "-" + j + "-" + UUID.randomUUID());
                        divide5Information.addSubInfo(subInfoIn5);
                        subInfoRepository.save(subInfoIn5);
                    }
                    informationRepository.save(divide5Information);

                    log.info("divide5-{} saved", i);
                } else {
                    // 나머지 상황은 그냥 저장
                    Information dataInformation = new Information("not divided");
                    informationRepository.save(dataInformation);
                    log.info("data-{} saved", i);
                }
            }
        }
    }
}

 

빈에 등록되고 바로 실행되는 @PostConstruct를 사용하였습니다.

또한 putTwentyThousands()의 함수에선 만약 데이터가 입력되어 있다면 실행되지 않도록 하였습니다.

 

데이터는 information에 100000개, sub_info에 122854개가 입력됩니다.

 

이제 마지막으로 테스트를 할 서비스 로직을 준비합니다.

QueryService.class

package com.example.overloadtest;

import com.example.overloadtest.domain.main.Information;
import com.example.overloadtest.domain.main.InformationRepository;
import com.example.overloadtest.domain.sub.SubInfo;
import com.example.overloadtest.domain.sub.SubInfoRepository;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

@Slf4j
@Component
public class QueryService {
    private final InformationRepository informationRepository;
    private final SubInfoRepository subInfoRepository;

    @Autowired
    public QueryService(InformationRepository informationRepository, SubInfoRepository subInfoRepository) {
        this.informationRepository = informationRepository;
        this.subInfoRepository = subInfoRepository;
    }

    @Transactional(readOnly = true)
    public void findAllByJoin(SubInfo subInfo) {
        Information information = informationRepository.findInformationBySubInfos(subInfo).orElseThrow(
                () -> new IllegalArgumentException("없음")
        );
        log.info(information.toString());
        information.getSubInfos().forEach(e -> log.info(e.toString()));
    }

    @Transactional(readOnly = true)
    public void findAllByWhere(SubInfo subInfo) {
        Information information = informationRepository.findBySubInfos(subInfo.getIdx()).orElseThrow(
                () -> new IllegalArgumentException("없음")
        );
        log.info(information.toString());
        information.getSubInfos().forEach(e -> log.info(e.toString()));
    }

    @Transactional(readOnly = true)
    public SubInfo getSubInfoByIdx(Long idx) {
        return subInfoRepository.findById(idx).orElseThrow();
    }
}

 

 

 

테스트

이제 테스트 로직을 짭니다.

package com.example.overloadtest.query;

import com.example.overloadtest.QueryService;
import com.example.overloadtest.domain.sub.SubInfo;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.transaction.annotation.Transactional;

@SpringBootTest
public class QueryTest {

    @Autowired
    QueryService queryService;

    @Test
    @Transactional
    void join() {
        SubInfo subInfo = queryService.getSubInfoByIdx(120000L);

        long beforeTime = System.currentTimeMillis();

        queryService.findAllByJoin(subInfo);

        long afterTime = System.currentTimeMillis();
        long secDiffTime = (afterTime - beforeTime);
        System.out.println("경과 시간(ms) : " + secDiffTime);
    }

    @Test
    @Transactional
    void where() {
        SubInfo subInfo = queryService.getSubInfoByIdx(120000L);

        long beforeTime = System.currentTimeMillis();

        queryService.findAllByWhere(subInfo);

        long afterTime = System.currentTimeMillis();
        long secDiffTime = (afterTime - beforeTime);
        System.out.println("경과 시간(ms) : " + secDiffTime);
    }

}

sub_info의 idx가 120000인 것을 찾아 이를 포함하고 있는 information레코드를 찾아오는 코드들입니다.

 우리는 저 경과시간을 확인하면 됩니다.

 

일단 쿼리를 확인해볼까요?

Hibernate: 
    select
        subinfo0_.sub_info_id as sub_info1_1_0_,
        subinfo0_.information_information_id as informat3_1_0_,
        subinfo0_.uuid as uuid2_1_0_ 
    from
        sub_info subinfo0_ 
    where
        subinfo0_.sub_info_id=?
Hibernate: 
    select
        informatio0_.information_id as informat1_0_,
        informatio0_.divide as divide2_0_ 
    from
        information informatio0_ 
    left outer join
        sub_info subinfos1_ 
            on informatio0_.information_id=subinfos1_.information_information_id 
    where
        subinfos1_.sub_info_id=?
Hibernate: 
    select
        subinfos0_.information_information_id as informat3_1_0_,
        subinfos0_.sub_info_id as sub_info1_1_0_,
        subinfos0_.sub_info_id as sub_info1_1_1_,
        subinfos0_.information_information_id as informat3_1_1_,
        subinfos0_.uuid as uuid2_1_1_ 
    from
        sub_info subinfos0_ 
    where
        subinfos0_.information_information_id=?

join의 쿼리입니다. 첫번째 쿼리는 경과 시간에 들어가지 않으니 신경쓰지 않아도 됩니다.

정상적으로 left outer join이 적용되었습니다.

그럼 경과시간은 몇 ms일까요?

2022-07-18 15:23:29.831  INFO 75243 --- [    Test worker] com.example.overloadtest.QueryService    : Information{idx=97675, divide='divide5'}
2022-07-18 15:23:29.832  INFO 75243 --- [    Test worker] com.example.overloadtest.QueryService    : SubInfo{idx=119998, uuid='97675-0-4e312941-2cf9-4838-ad4c-63962d6f2afb'}
2022-07-18 15:23:29.832  INFO 75243 --- [    Test worker] com.example.overloadtest.QueryService    : SubInfo{idx=119999, uuid='97675-1-8e68df54-a9f0-4333-a5ff-b4c37b984314'}
2022-07-18 15:23:29.832  INFO 75243 --- [    Test worker] com.example.overloadtest.QueryService    : SubInfo{idx=120000, uuid='97675-2-9d4350f7-430d-42aa-a4ce-dbb20d35bc5f'}
경과 시간(ms) : 37

37초가 나왔습니다.

 

그렇다면 where를 사용한다면 어떻게 될까요?

Hibernate: 
    select
        subinfo0_.sub_info_id as sub_info1_1_0_,
        subinfo0_.information_information_id as informat3_1_0_,
        subinfo0_.uuid as uuid2_1_0_ 
    from
        sub_info subinfo0_ 
    where
        subinfo0_.sub_info_id=?
Hibernate: 
    select
        * 
    from
        information i 
    where
        i.information_id = (
            select
                information_information_id 
            from
                sub_info s 
            where
                s.sub_info_id = ?
        )
Hibernate: 
    select
        subinfos0_.information_information_id as informat3_1_0_,
        subinfos0_.sub_info_id as sub_info1_1_0_,
        subinfos0_.sub_info_id as sub_info1_1_1_,
        subinfos0_.information_information_id as informat3_1_1_,
        subinfos0_.uuid as uuid2_1_1_ 
    from
        sub_info subinfos0_ 
    where
        subinfos0_.information_information_id=?

우선 쿼리는 정상적으로 작동하였습니다.

 

2022-07-18 15:23:29.862  INFO 75243 --- [    Test worker] com.example.overloadtest.QueryService    : Information{idx=97675, divide='divide5'}
2022-07-18 15:23:29.862  INFO 75243 --- [    Test worker] com.example.overloadtest.QueryService    : SubInfo{idx=119998, uuid='97675-0-4e312941-2cf9-4838-ad4c-63962d6f2afb'}
2022-07-18 15:23:29.862  INFO 75243 --- [    Test worker] com.example.overloadtest.QueryService    : SubInfo{idx=119999, uuid='97675-1-8e68df54-a9f0-4333-a5ff-b4c37b984314'}
2022-07-18 15:23:29.862  INFO 75243 --- [    Test worker] com.example.overloadtest.QueryService    : SubInfo{idx=120000, uuid='97675-2-9d4350f7-430d-42aa-a4ce-dbb20d35bc5f'}
경과 시간(ms) : 14

경과 시간은 14ms가 나왔습니다.

37ms가 나온 join과 비교해봤을 때 약 2.6배 빠른것을 확인할 수 있습니다. 이 테스트에서 left outer join을 사용하였다는 것을 감안하더라도 꽤 큰 차이입니다.

 

5번 정도 더 실행시켜 보니 다음과 같이 나왔습니다

join	where
26	15
35	16
29	16
29	19
31	16

얼마나 빠른지는 조금씩 다 다르지만 모두 where이 join보다 빠른 것을 확인할 수 있습니다.

 

이렇게 보면 서브쿼리를 통한 where을 사용하는 것이 무조건 좋아 보입니다.

하지만 jpa에서 기본적으로 join을 사용하는 이유가 무엇일까요?

이는 서브쿼리를 사용하는 방법이 MySQL의 특정 버전 이후로 최적화가 되었기 때문입니다. 또한 이 서브쿼리가 최적회 되기위한 조건이 있기 때문에 모든 상황을 고려해 봤을 때 join을 기본적으로 사용하는 것이 성능에 편차를 줄일 수 있기 때문일 것입니다.

 

따라서 개발자는 쿼리의 성능을 최적화하기 위해 무조건적으로 어떠한 방법을 선택하는 것이 아닌, 여러가지 상황에 따라 요건을 따지고 쿼리를 선택해야 할 것입니다.