SQL 처리 과정과 최적화

서론

데이터 베이스를 통해 데이터를 처리하고 관리하는데 SQL을 사용하였지만 데이터베이스에서 어떻게 SQL을 동작시키고 최적화시키는지 이해하고 쓰지 못하고 사용을 하면서 쿼리튜닝을 하는데 있어서 어려움을 겪었던 적이 있다. 이러한 삽질을 줄이고 쿼리의 성능을 개선해야될 때 쿼리의 동작을 이해하고 예상가능한 범위내에서 동작시키는 것이 중요하기 때문에 공부를 시작했다.

SQL 옵티마이저 

SQL 옵티마이저는 우리가 요청한 작업을 효율적으로 수행할 수 있도록 도와주는 DBMS의 핵심 엔진이다. 옵티마이저의 최적화 시키는 동작은 보통 아래와 같이 데이터 딕셔너리에 미리 수집해둔 오브젝트 및 시스템 통계 정보를 통해 예상비용을 산정하고 최저 비용이 드는 경로를 선택한다.

 

실행 계획과 비용

SQL 옵티마이저는 자동차의 내비게이션과 같은 역활을 가지고 있다. 해당 데이터를 조회할 때 아래처럼 예상 I/O 횟수나 예상 소요시간을 통해 비교하여 최적의 경로를 선택합니다. 네비게이션과 비교하자면 1번 도로와 2번 도로 중 어느 도로로 갈지 계산 합니다. 네비는 거리와 제한 속도등을 계산하여 최적의 경로를 안내해줍니다.
옵티마이저도 마찬가지로 1번 인덱스, 풀스캔 중 어느 경로로 데이터를 가져올지 계산합니다. 경로를 계산할 때 예상되는 처리 시간, I/O 횟수 등을 통해 최적의 경로를 통해 쿼리를 동작합니다. 하지만 SQL 실행 계획은 언제까지나 예상이기 때문에 특정 상황에는 더 느릴 수 있습니다. 

옵티마이저의 힌트

내비게이션은 보편적으로 좋은 경로를 추천하여 안내해주지만 모든 경로가 최적의 경로를 안내해줄수는 없듯이 옵티마이저도 마찬가지로 항상 최적의 SQL 실행계획을 가질 수 없습니다. 쿼리가 간단하다면 적중률이 높겠지만 복잡하고 어려운 쿼리를 사용한다면 그만큼 실수할 가능성이 있습니다. 개발을 하다보면 쉬운 쿼리보다 어렵고 복잡한 쿼리를 요구할 때가 자주 있기 때문에 힌트를 줘서 최적의 경로를 가도록 유도해야합니다.

 

Mysql에서 힌트의 사용 방법 아래와 같습니다. 이러한 옵티마이저를 힌트를 통해 사용할지 아니면 옵티마이저에게 맡길지는 애플리케이션 환경에 따라 다릅니다. 가끔 실수해도 되는 상황이면 힌트를 명시하지 않아도 상관 없다. 하지만 작은 실수라도 기업에 큰 손실을 일으킬 수 있는 환경이면 옵티마이저에 자율적인 판단을 맡길 수 없다. 그리고 힌트를 사용한다면 빈틈없이 기술해야 옵티마이저가 실수하는 상황이 줄어들 것입니다.

table_name [[AS] alias] [index_hint_list]

index_hint_list:
    index_hint [index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...

 

SQL 공유 및 재사용

소프트 파싱 vs 하드 파싱

https://notemusic.tistory.com/14

위 그림과 같이 SQL 파싱과 최적화, 로우 생성 과정을 거친 내부 프로시저를 반복사용할 수 있도록 캐싱해두는 공간을 라이브러리 캐시라고 합니다. SGA는 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어하는 구조를 캐싱하는 메모리 공간입니다. MySQL도 마찬가지로 쿼리를 캐싱 해놓은 저장공간을 가지고 있습니다.

사용자가 SQL문을 전달하고 DMBS는 SQL을 파싱한 후 라이브러리에 존재하는지 안하는지 확인합니다.

https://n1tjrgns.tistory.com/204

위그림 처럼 캐시를 찾으면 곧바로 실행단계로 넘어가지만 찾지 못하면 최적화 단계를 거쳐서 쿼리를 실행합니다. 이처럼 캐시를 찾아서 바로 실행 단계로 넘어가는 것을 소프트 파싱(Soft Parsing)이라고 하고 최적화 단계를 거치고 동작하는 것을 하드 파싱(Hard Parsing)이라고 합니다.

 

SQL 최적화 과정이 어려운 이유

서울에서 부산까지 이동하는 경로를 계산하면 아마 n!의 가짓수나 나오게 될 것이다. 이러한 모든 경우의 수를 다 고려하고 계산하려면 아마 경로를 찾는데 한참의 시간을 기다리게 될 것이다. 그래서 주요한 정보만 가져와서 계산하게 되는데 보통 위치 정보, 구간별 평균속도, 실시간 교통 정보 등을 통해 계산하게 될 것입니다. 옵티마이저도 마찬가지로 여러가지의 사항을 고려하여 최적 경로를 계산해줍니다.

 

5개의 테이블의 조인 순서만 생각해도 120(5!)가지인데 여기에도 조인 방식도 NL 조인, 해시 조인, 소트 머지 조인등 여러가지의 조인방식중 어떤 방법으로 조인할지 테이블 풀스캔을 할지 인덱스를 통해 스캔을 할지 인덱스를 사용한다면 Index Range Scan, Index Unique Scan, Index Full Scan 등 다양한 스캔 방식 중으로 선택해야 한다. 물론 하드웨어와 알고리즘이 상당히 발전하면서 잘 느끼지 못하지만 아래와 같은 많은 사항을 고려하여 동작하게 됩니다.

• 테이블, 컬럼, 인덱스 정보
• 오브젝트 통계: 테이블 통계, 인덱스 통계, 컬럼 통계
• 시스템 통계: CPU 속도, Single Block I/O, Multiple Block I/O
• 옵티마이저 관련 파라미터 등

하나의 쿼리를 동작하게 이렇게 수많은 사항을 고려하고 최적경로를 계산하게 되는데 결코 쉬운 과정일 수 없다. 그렇기 때문에 하드 파싱을 하게 되면 데이터베이스의 리소스가 많이 소비가 되는 것이다. 그렇기 때문에 이러한 복잡한 작업을 줄이기 위해 라이브러리 캐시에 저장해둔다.

 

이름없는 SQL의 문제 

함수/프로시저, 트리거 등은 생성할 때부터 이름을 가지고 있어서 딕셔너리에 저장되어 삭제하지 않는 이상 영구적으로 보관이 됩니다. 실행할때 지정한 이름으로 라이브러리 캐시에 가져다 쓰기 때문에 여러 사용자가 재사용할 수 있다. 반면에 SQL은 따로 이름을 가지고 없다. 그렇기 때문에 SQL 자체가 이름 역활하게 된다. 

 

SQL도 함수와 프로시저 처럼 아이디를 저장하게 되면 안될까?

SQL	ID
SELECT * FROM USERS WHERE ID = 100;	1
SELECT * FROM USERS WHERE id = 100;	2
SELECT * FROM USERS WHERE ID = 101;	3

SQL자체를 이름으로 보기 때문에 아이디와 SQL은 1대1의 매칭을 하게된다.

그렇기 때문에 일회성 정보와 무효화된 SQL까지 전부 아이디를 저장하게 되고 그만큼 저장공간의 낭비와 속도저하를 가져오기 때문에 SQL 정보를 전부 저장하지 않는 이유가 그것이다. 

 

공유가 가능한 SQL

SQL문은 키값 그자체라고 했다. 그렇기에 실행할 때 마다 최적화를 진행하고 라이브러리 캐시저장하게 됩니다. 디비에 부하는 대부분 슬로우쿼리나 많은 I/O를 통해 이뤄집니다. 그렇기에 아래처럼 코드를 작성하면 조회가 실행될 때마다 새로운 쿼리를 전달받고 최적화하여 CPU 사용율이 급격히 올라갈 수 있습니다.

public List<Board> findBoard(Long id) {

    String findQuery = "SELECT * FROM BOARDS WHERE ID = " + id + ";";
	
    return jdbcTemplate.query(findQuery, (rs, num) -> {
    	// anything
    })
}

많은 CPU 사용량을 차지 않는 쿼리임에도 불구하고 조금의 I/O만으로도 CPU 사용율이 급격히 올라가게 됩니다. 그이유는 요청되는 동시다발적인 서로 다른 쿼리에 대한 최적화 작업을 하기 때문입니다. 내부 동작인 같은 쿼리인 경우 아래와 같이 처리를 하여 같은 프로시저를 사용하도록 하는 것이 효율적입니다.

public List<Board> findBoard(Long id) {

    String findQuery = "SELECT * FROM BOARDS WHERE ID = ?;";
	
    return jdbcTemplate.query(findQuery, id, (rs, num) -> {
    	// anything
    })
}