조회 성능 개선하기 ( ① 쿼리 최적화 )

 

 

이번엔 저번 화면 응답 개선에 이어 조회 성능을 개선하는 것을 배웠다!

먼저 쿼리 최적화에 대해서 정리하자. 

 

 

 

 

 

쿼리 동작 방식 

출처 NEXTSTEP 프로젝트 공방

• 쿼리 캐싱 
  • KEY : SQL문, VALUE : 쿼리의 실행결과 인 Map
  • 데이터 변경시 모두 삭제되어 동시처리 성능저하를 일으키기 때문에 MySQL 8.0부터 사라졌다 
• Parsing
  • 사용자로부터 요청된 SQL을 작게 만들어 서버가 이해할 수 있는 수준으로 분리 
• Preprocessor ( 전처리기 )
  • 해당쿼리의 문법을 확인하여 오류가 있다면 처리중단 (일괄 처리내에 있으면 일괄처리 전체 중단)
  • 실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러짐
• Optimization
  • 실행계획(Exception Plan)은 이 단계에서의 출력을 의미
  • 사용자의 요청으로 들어온 쿼리 문장을 어떻게 저렴한 비용으로 가장 빠르게 처리할 지 결정하는 역할 
  • 쿼리분석 : WHERE 절의 검색 조건인지 JOIN 조건인지 판단
  • 인덱스 선택 : 각테이블의 조건과 인덱스 통계정보를 이용해 사용할 인덱스 선택
  • 조인처리 : 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블 읽을 지 결정
• Execution Engine
  • 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할 
• Handler (Storage Engine)
  • 실행엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할 담당 
  • Sequential Access
    • 물리적으로 인접한 페이지를 차례대로 읽는 순차 접근 방법
    • 다중페이지 읽기 방식
    • ex)인접리스트 
  • Random Access 
    • 물리적으로 떨어진 페이지들에 임의로 접근하는 방식
    • 다중페이지 읽기 불가능
    • 데이터의 접근 수행시간이 오래걸림
    • ex)배열 

 

 

 

 

 

쿼리 최적화 체크리스트  

• SELECT 시에는 꼭 필요한 칼럼만 불러오기 
  • 불필요한 불러오면 DB는 불필요하게 그만큼의 로드를 부담하게 된다. 
• 조건에는 별도의 연산을 걸지 않고 값으로 조건 걸기
  •  연산을 걸게되면 Full Table Scan을 이용하여 모든 Cell을 탐색하고 수식을 이용한 조건 충족을 판단하게 된다.
• LIKE 사용시 와일드 카드 문자열 (%)은 String 앞부분에 배치하지 않기 
  •  문자열 앞에 %을 걸게되면 Full Table Scan을 하게된다. 
• DISTINCT를 사용한 중복값 제거 연산은 최대한 사용하지 않기 
  • 시간이 많이 걸린다. 
• GROUP BY 연산 시에 HAVING 보다는 WHERE절 사용
  • WHERE절이 HAVING 절보다 먼저 실행되기 때문에 미리 데이터 크기를 줄일 수 있다. 
• 3개 인상의 테이블을 INNER JOIN시에는 크기가 가장 작은 테이블을 FROM 절에 배치하기
  • 드라이븐 테이블의 액세스를 최소화 할 수 있다. 
  •  대부분의 Query Planner에서 INNER JOIN의 경우 효과적인 순서대로 바꿔주기는 하지만 Planning의 비용 또한 생각하여 미리 최적화된 INNER JOIN 순서를 입력단계에서 조정해두자. 
• 자주 사용되는 데이터 형식에 대해서는 미리 전처리 테이블을 따로 보관/관리하기
  •  이 부분은 DB의 실시간이 필수적(운영계)이지 않은 분석계에서 많이 쓰인다. 
• ORDER BY는 연산 중간에 사용하지 말기 
• LIMIT 활용하기 

 

 

 

페이징 쿼리 

페이징 쿼리란 테이블의 레코드를 일정 단위로 잘라서 조회하는 것을 뜻한다(LIMIT).

## id 순서대로 정렬하고 20번째부터 10개를 읽는다
SELECT * FROM subway.programmer ORDER BY id LIMIT 20, 10;

그런데 LIMIT을 이용하여 조회할 경우 원하는 범위의 데이터만을 읽는 것이 아니라 처음부터 원하는 범위까지 다 읽고 앞에 부분을 버려 성능 저하된다. 아래와 같이 WHERE 조건을 사용하도록 하도록 하자. 

SELECT * FROM subway.programmer WHERE subway.programmer.id >= 20 ORDER BY id LIMIT 0, 10;

 

JPQL은 다음과 같이 쿼리에 조건을 추가해준다. 

@RestController
public class StationController {
    private StationService stationService;
    public StationController(StationService stationService) {
        this.stationService = stationService;
    }
    
    @GetMapping(value = "/stations", produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity<List<StationResponse>> showStations() {
        int page = 1;
        return ResponseEntity.ok().body(stationService.findAllStations(page));
    }
}

public class StationService {
    private StationRepository stationRepository;

    public static final int STATION_SIZE_PER_PAGE = 5;

    public StationService(StationRepository stationRepository) {
        this.stationRepository = stationRepository;
    }
    
    @Transactional(readOnly = true)
    public List<StationResponse> findAllStations(int page) {
        Pageable pageable = PageRequest.of(0, STATION_SIZE_PER_PAGE, Sort.by("id"));
        Page<Station> stations = stationRepository.findAllByPage((long) (page * STATION_SIZE_PER_PAGE), pageable);

        return stations.stream()
            .map(StationResponse::of)
            .collect(Collectors.toList());
    }
}

public interface StationRepository extends JpaRepository<Station, Long> {

    @Query("SELECT s FROM Station s WHERE s.id >= :offset")
    Page<Station> findAllByPage(Long offset, Pageable pageable);

}

 

 

 

 

쿼리 최적화 예시 

SELECT A.사원번호, A.이름, A.연봉, 직급.직급명, 사원출입기록.입출입시간, 사원출입기록.지역
FROM 
	(
	SELECT 사원.사원번호, 사원.이름, 급여.연봉 
	FROM 
		사원 
	JOIN 부서관리자 
		ON 사원.사원번호 = 부서관리자.사원번호 
	JOIN 부서
		ON 부서관리자.부서번호 = 부서.부서번호
	JOIN 급여 
		ON 사원.사원번호 = 급여.사원번호 
	WHERE  부서관리자.종료일자 >= NOW() 
	AND 부서.비고 = 'active' 
	AND 급여.종료일자 >= NOW()
	ORDER BY 연봉 DESC
	LIMIT 5
	) AS A
JOIN 직급 
	ON A.사원번호 = 직급.사원번호
JOIN 사원출입기록 
	ON A.사원번호 = 사원출입기록.사원번호 
WHERE 직급.종료일자 >= NOW()
AND 사원출입기록.입출입구분 = 'o'

최적화를 적용한 쿼리는 위와 같다. 

 

 

 

 

 

 

 

 

(참고한 사이트)

NextStep 프로젝트 공방 

https://edu.nextstep.camp/

NEXTSTEP

https://medium.com/watcha/%EC%BF%BC%EB%A6%AC-%EC%B5%9C%EC%A0%81%ED%99%94-%EC%B2%AB%EA%B1%B8%EC%9D%8C-%EB%B3%B4%EB%8B%A4-%EB%B9%A0%EB%A5%B8-%EC%BF%BC%EB%A6%AC%EB%A5%BC-%EC%9C%84%ED%95%9C-7%EA%B0%80%EC%A7%80-%EC%B2%B4%ED%81%AC-%EB%A6%AC%EC%8A%A4%ED%8A%B8-bafec9d2c073

✅ 쿼리 최적화 첫걸음 — 보다 빠른 쿼리를 위한 7가지 체크 리스트

https://12bme.tistory.com/73

[MySQL] 쿼리 실행 구조 및 쿼리 캐시