7부 - 애플리케이션 입장에서의 SQL 튜닝

1. 기본 방향

• 프로그램 단위에서 접근하는 경우 아래의 6가지 접근법만 이해하면 여러 성능 개선 항목을 도출할 수 있다.

  • 중복 수행 제거
  • 불필요한 수행 제거
  • 최적 수행
  • 필요 이상으로 많은 데이터 건수를 조회하는가?
  • 조회한 항목은 다 사용하는 값인가?
  • 조회하는 항목은 모두 사용자에게 보여줘야 하는가?
  • 업므 데이터의 성격상 쿼리를 개선할 부분이 있는가?
  • 데이터 검색 범위는 적절한가?
  • 수행 통합
  • 주종 관계로 수행되는 쿼리를 한 개의 쿼리로 통합할 수 있는가?
  • 상관관계는 없으나 DB 호출을 줄이기 위해 쿼리를 합칠 수 있는가?
  • 집합 처리
  • 여러 건 처리 시 입력/수정/삭제를 건별로 처리하고 있는가?
  • 데이터 처리 단위는 적절한가?
  • 트랜잭션 처리

2. 중복 SQL 수행 제거

• CBC(Component Based Developmnet) 기반 개발이 보편화되어 재활용성과 유지보성은 좋아졌지만 기초 정보가 동일한 조건으로 반복적으로 호출되는 경우가 빈번하게 발생한다.

• 빈번한 유효성 검증

  • 컴포넌트 단위로 안정성과 정합성이 유지될 수 있도록 모듈 단위로 입력 값에 검증을 추가로 수행하는 것이 일반화되어 검증이 반복적으로 수행된다.

• 동일 SQL/모듈의 반복 호출

  • 상부와 하부 컴포너트 간 호출 빈도나 쓰임새를 고려하지 않고 모든 컴포넌트가 동일한 방식으로 개발되어 SQL/모듈이 한 프로그램 내에서 많게는 수십에서 수천 까지 반복 호출되는 문제를 유발한다.

• 불필요한 기능 수행

  • 모듈이 범용화되는 과정을 거치게 되는데, 이로 인해 한 모듈이 여러 서비스에서 필요로 하는 다양한 정보를 제공하려는 경향이 있다.

2.1 애플리케이션 캐시의 종류

2.1.1 블록 캐시

• 블록 캐시는 애플리케이션의 특정 블록(함수) 내에서 SQL 중복 수행을 제거하나 처리 결과 중간 값을 저장하는 등의 목적으로 사용하는 캐시다.
• 데이터 저장소가 함수 내 로컬 변수로 선언되어 해당 블록 내에서만 유효하다는 특징이 있다.
• 특정 함수 내에서 for/while 문으로 여러 개의 데이터를 처리할 때 유용하다.
• 저바의 경우 데이터 저장소로 HashMap을 많이 사용한다.
• 필요할 때마다 건별로 조회하지 않고 필요 데이터를 한 번에 캐시를 만든 후 while이나 for 문 안에서 참조할 때 캐시에서 찾음으로써 DB 호출을 제거한다.

2.1.2 요청 캐시

• 요청 캐시는 사용자 요청 서비스가 시작되어 종료될 때까지 유지되는 캐시다.
• 서비스 요청이 들어와 애플리케이션이 시작될 때 캐시가 초기화되어 비어 있고 애플리케이션이 종료될 때 캐시가 지워져 사라지는 구조로 사용자 서비스 간에는 데이터가 공유되지 않는다.
• 자바의 경우 캐시가 ThreadLocal이나 서비스 컨텍스트에 위치하게 한다.
• 서비스 시작과 종료 시 캐시가 프레임워크에서 자동으로 초기화 및 삭제된다.
• 고객관리 시스템에서는 고객 기본정보 조회가 한 서비스 내에서 반복 호출되는 경우가 있는데 기존에 조회된 데이터가 없으면 DB를 조회해 캐시한 후 내보내고, 이미 조회된 데이터가 있으면 캐시된 데이터를 내보낸다.

2.1.3 사용자 세션 캐시

• 시스템에 접속한 사용자 세션 단위로 캐시를 가지는 구조로서, 접속한 사용자에 대한 기초 데이터를 보관해서 DB 조회를 최소화하는 방식이다

2.1.4 프로세스 캐시

• WAS 인터턴스 단위 내에서 공유되는 식으로 프로세스 내에서 처리되는 모든 서비스 요청이 캐시를 공유해서 사용한다.
• 프로세스 단위로 공유되므로 자바의 싱글턴 구조로 구현된다.
• 빈번하게 사용되는 코드성 테이블에 대한 DB 조회를 최소화하기 위해 많이 사용된다.

2.1.5 프로세스간 공유 캐시

• 프로세스 단위로 캐시를 구성하는 경우 시스템 전체로 봤을 때 동일한 데이터가 여러 개의 프로세스에 캐시되어 메모리 중복 손실이 발생하고, 힙을 대량으로 사용하면 GC에 악영향을 미치게 된다.
• 자바 메모리의 한계를 극보하기 위해 메모리 캐시 전용 솔류션이 사용되고 있는데 대표적으로 Redis가 있다.

2.1.6 캐시 특성 분류

2.2 요청 캐시 구현

2.3 캐시 적용

3. 불필요한 SQL 수행 제거

• 공통 모듈을 사용하거나 서로 다른 개발자가 개발한 모듈을 사용한 애플리케이션의 경우 로직과 수행 SQL은 최적화가 안되는 경우가 많다.

  • 특정 컬럼만 조회하면 되지만 공통 모듈의 무거운 쿼리를 사용해서 조회하는 경우

4. 최적 수행

4.1 Rownum 추가

• DB에는 한 번에 보내는 레코드 건수를 설정할 수 있는데 이를 Fetch 크기라고 한다.
• Fetch size가 크면 DB와 애플리케이션 간의 턴을 줄일 수 있지만, 필요한 데이터보다 더 많은 데이터를 가져올 수 있다.

4.2 파티션 키 추가

• 파티션 테이블은 배치 메인 쿼리의 경우 전체 테이블 탐색 범위를 줄이기 위해 사용하고 입력에서는 부하분산 등 성능 목적으로 사용하며, 대량 데이터를 관리하는 목적으로 주로 사용한다.
• 파티션 테이블이 글로벌 인덱스를 사용하는 경우 주기적으로 보관연한이 지난 파티션을 제거하게 되면 글로벌 인데그삭 사용불가 상태로 바뀐다.
• 이 같은 상황을 방지하기 위해 파티션 테이블을 사용할 때는 가급적 로컬 인덱스를 사용하는 것을 기본으로 하고, 단지 로컬 인덱스로 성능 목표를 달성하지 못하는 경우에만 글로벌 인덱스를 사용하는 것을 고려한다.
• 파티션 키가 Where 조건에 없다면 모든 파티션 테이블을 순차적으로 조회하는 것과 동일한 성능 저하가 발생할 수 있으므로 어떤 식으로든 파티션 키가 Where 조건에 포함될 수 있게 한다.

4.3 조회 항목 사용 여부(테이블 조인 제거)

• 공통으로 사용하는 쿼리는 최대한 많은 컬럼을 조회해서 제공하려 한다.
• 조회하는 컬럼을 줄이는 것도 의미가 있지만 테이블의 조인을 제거하것도 효과적이다.

5. 쿼리 통합

• 쿼리 통합은 Select에 대한 집합 처리를 의미한다.
• 여러 번 수행하는 쿼리를 한 번에 수행해 성능을 개선하는 것이다.

5.1 메인/서브 쿼리

5.2 병렬 쿼리 통합

• 고객 통합정보 조회 하면을 떠올려 보자.

  • 기본정보, 결제이력, 카드 보유 개수, 마케팅 대상 등등..
  • 20~30개의 쿼리가 순차적으로 수행되는 구조가 된다.
• 이런 경우에는 Union all이나 일대일 조인 형태로 하나의 쿼리로 통합하면 DB 호출 횟수가 줄어들어 성능 개선이 가능하다.

6. DB 집합 처리

• 10건의 데이터를 개별적으로 입력한다면 애플리케이션 서버와 DB 서버 간 애플리케이션 턴도 10회 생기고 SQL 실행과 결과도 10회 발생한다.

• 집합 처리는 애플리케이션 턴 1회에 10건의 데이터를 모두 전송함으로서 성능 개선 효과가 있다.

  • 단건 처리 (PreparedStatement.excuteUpdate())
  • 집합 처리 (PreparedStatement.addBatch(),PreparedStatement.executeBatch())
• 집합 처리를 수행할 때 수십만 건씩 처리하면 자바 힙 메모리가 부족해지는 현상이 발생해지므로 최대 만 건 이내로, 일반적으로는 천 건 단위로 수행한다.

7. 트랜잭션 처리

• 동일한 레코드를 Update 하는 서비스가 있다고 할 때, Update 문의 위치가 응답시간에 영향을 미친다.
• 락을 흭득하는 위치가 트랜잭션 중간이면 다른 트랜잭션이 락을 대기하는 시간이 늘어나기 때문에 트랜잭션의 마지막에 위치하는게 좋다.

8. 기타

8.1 복합적인 기능을 수행하는 쿼리 제거

• 복합 기능 쿼리란 하나의 쿼리에 1개 이상의 기능이 담긴 쿼리를 말한다.
• 복합 기능 쿼리에서 성능 저하가 발생하는 이유는 DB에서 쿼리가 바인드 변수 값에 따라 실행될 때마다 실행계획을 수립하는 것이 아니라 최초 실행 시 실행계획을 수립하고 이후에는 기존 실행계획을 재사용하기 때문이다.
• 쿼리 한 개는 한 개의 실행계획만 가지고 있어 다양한 입력 조건에 맞게 실행계획을 수립할 수 없다.
• Union all 을 이용해서 쿼리를 분리하면 하나의 실행계획으로 안정적인 성능을 기대할 수 있다.

8.2 스칼라 서브 쿼리 사용 시 주의사항

• Select 절에 사용된 쿼리를 스칼라 서브 쿼리라고 한다.
• 스칼라 서브 쿼리를 사용하는 것은 많은 경우에 있어서 나쁜 성능을 보이기 때문에 Where 조건이 분산도가 좋은 인덱스를 사용하고 전체 쿼리 결과가 소량인 경우에만 사용한다.

8.3 페이징 처리

• 페이징 처리 시, 다음과 같은 이유로 테이블을 전체 범위 처리함하면 성능 저하게 발생한다.

  • 전체 건수/페이지 수의 표시 문제
  • 무의미한 전체 건수 표시 제거
  • 변경이 적은 목록은 전체 건수 및 페이지 수를 초기에만 생성 후 재사용
  • Order By 이용한 목록 정렬 문제
  • Order By 순서와 일치하는 페이징 인덱스를 생성해서 사용
  • 페이징 인덱스 컬럼 값을 페이지 이동 시 다음 키값으로 사용
  • 분산도가 좋은 필수 입력항목 부재
  • 분산도가 좋은 입력항목을 지정하고, 기본값 설정

9. 배치 성능 개선

• 배치는 DB에서 처리 대상을 추출하는 메인리더 부분과 프로세싱하는 부분, 처리 결과를 DB에 기록하는 부분으로 나뉜다.

9.1 전체 테이블 탐색

• 메인리더가 수만 건 내외의 소량건을 조회해서 처리한다면 테이블 인덱스를 사용하는 것이 효과적이지만 수만 건 이상 조회해야 한다면 테이블 전체를 읽어서 조회하는 편이 성능이 좋은 경우가 많다.

• 인덱스를 사용하면 읽는 블록수가 상대적으로 적어지지만 응답시간이 늦어진다.

  • 그 이유는 인덱스 탐색은 인덱스를 읽어 조건에 맞는 레코드를 한 건 한건을 순차적으로 읽는 랜덤엑세스가 이뤄지는 반면 전체 테이블 탐색은 멀티블록 읽기 설정에 따라 여러 블록을 한번에 읽어 들일 수 있다.

9.2 파티션

• 대량 건을 조회할 때 전체 테이블 탐색이 성능이 우수하다고 하지만 사이즈가 커질 수록 부담스러운 작업이다.
• 이 같은 상황에서 해결책으로 고려해볼 수 있는것이 파티션이다.
• Where 조건에 파티션의 범위를 지정한다면 전체 탐색을 하더라도 지정된 파티션 범위 내에서만 전체 탐색이 발생한다.

9.3 해시 조인

• 테이블 조인에서 조회하는 건수가 소량이고 모든 테이블에 적절한 인덱스가 존재한다면 내포 조인이 가장 효과적이다.
• 대량 건을 조회한다면 내포 조인대신 해시 조인을 사용하는 것이 성능상 유리하다.

9.5 쿼리 통합

9.5.1 메인리더와 건별 처리 쿼리 통합

• 메인리더에서 읽은 데이터를 기반으로 건별 처리 로직에서 부가 정보를 획득하거나 확인을 위해 추가 쿼리를 수행하는 경우가 많은데 메인리더와 통합하면 성능 개선효과가 있다.

9.5.2 메인리더와 Insert 통합

• 건별로 조회하해 데이터를 처리하는 것이 가장 느리다.
• 1000건씩 Fetch 하여 집합 처리(batch)를 활용한다면 더 빠를 것 이다.
• 마지막으로 시도해볼 수 있는 방법은 Insert ... Select 구문을 사용하는 것이다.
• 사실상 애플리케이션으로 처리 데이터가 오지 않고 전부 DB 내부에서 처리하는 것 이다.

9.5.3 Select / Insert / Update 통합

• 오라클에서는 Merge를 통해 Select Insert Update 쿼리를 통합할 수 있다.
• Mysql에서는 ON DUPLICATE KEY 로 통합할 수 있다.