SQL 튜닝을 위해 SQL 문을 확인하다 보면, SQL 문의 일부만 조정해도 성능을 향상할 수 있는 간단한 경우가 있는 반면 테이블을 설정을 바꾸거나, 인덱스 설계를 다시하거나, SQL 문을 아예 다시 작성해야 할 정도로 규모가 큰 작업을 하게 되는 경우도 있다.

여기서부터는 여러 게시글에 걸쳐 SQL 튜닝 예제를 하나씩 기록하고자 한다. SQL 문을 튜닝할 때 어떤 사항들을 중점적으로 눈여겨봐야 할지 막막해 할 미래의 나에게 약간 도움이 되길 바라면서 글을 작성한다.

1. SQL 튜닝의 개략적인 절차

SQL 튜닝을 위해서는 SQL 문의 실행 결과, 튜닝 대상 SQL 문의 구성요소, SQL 문 실행 결과, 그리고 SQL 문 구조 등을 함께 확인해 봐야 할 것이다.

실제로 SQL 튜닝을 수행하는 절차와 단계별로 하는 일은 다음과 같다.

1-1. SQL 문 실행결과 & 현황 파악

1. 실행 결과 및 소요시간 확인
2. 조인/서브쿼리 구조
3. 동등/범위 조건
4. 등..

1-2. SQL 문의 구성요소 확인

1. 가시적 요소
   1. 테이블 현황
      1. 테이블의 명칭과 의미
      2. 각 테이블의 열의 의미
      3. 각 테이블의 데이터 건 수
   2. 조건절 컬럼 분석
   3. SELECT절 컬럼 분석
   4. 그루핑/정렬 컬럼 분석
2. 비가시적 요소
   1. 실행계획
   2. 각 테이블의 인덱스 현황
      1. 각 테이블의 인덱스 목록
      2. 키 유형(PK, INDEX, UNIQUE INDEX 등)
      3. 키명
      4. 키 구성열(단일 or 혼합)
   3. 조건절 컬럼들의 데이터 분포
   4. 데이터 적재 속도
   5. 업무적 특징

1-3. 실제 튜닝 수행

1, 2에서 확인한 요소들을 세밀하게 분석하여 튜닝의 방향성을 판단하고, 실제 개선 작업을 수행한다.

사용되지 않는 구문이나 불필요한 구문이 있는지 확인하고, 문제가 있는 쿼리를 발견했다면 기본적인 실행 계획과 소요 시간을 확인하여 쿼리 튜닝의 방향성을 유추하고 테스트한다. 마지막으로, 튜닝된 SQL 문은 결과 단계에서 검증한다.

2. 튜닝의 범위 판단하기

SQL문의 문제의 원인을 발견했다면, 어느 부분을 조정해야 할지 감이 잡힐 것이다. 예를 들어 SQL 문이 불필요하게 형변환으로 인해 느리다면 SQL 문 단순 수정만으로도 충분할 것이고, 테이블 풀 스캔이 발생하고 있다면 인덱스가 잘 설정되어있는지, 범위 스캔이 왜 작동하고 있지 않은가를 확인해야 할 것이다. 이렇듯 SQL 튜닝의 대상 및 범위는 문제의 원인에 따라 달라진다. 이제부터 살펴볼 사례들은 튜닝 범위에 따라 다음과 같이 구분해 볼 수 있다.

1. SQL 문 단순 수정을 통해 튜닝하기
2. 테이블 조인 설정 변경을 통해 튜닝하기
3. SQL 문 재작성을 통해 튜닝하기
4. 인덱스 조정을 통해 튜닝하기
5. 적절한 테이블 및 열 속성 설정을 통해 튜닝하기

먼저 비교적 간단하고 난이도가 높지 않은 편에 속하는 SQL 튜닝의 예시들부터 시작해 본다. 튜닝 대상의 SQL 문에서 일부만 변경하여 성능을 향상할 수 있는 예제부터 살펴보자.

3. PRIMARY KEY를 변형하는 나쁜 SQL 문

PRIMARY KEY(기본 키)를 변형하여 비효율적인 스캔이 일어나는 경우이다.

3-1. 현황 분석

사원 번호가 1100으로 시작하면서 5자리인 사원의 정보를 모두 출력하는 쿼리가 있다고 하자. 튜닝 전 SQL 문과 실행 결과는 아래와 같다.

튜닝 전 실행 계획은 아래와 같다.

사원 테이블 하나만 존재하므로 실행 계획도 1개 행으로 출력된다. Type이 ALL이므로 테이블 풀 스캔 방식이며, 인덱스를 사용하지 않는다. 필요한 범위에만 접근하지 않고 테이블에 바로 접근하여 처음부터 끝까지 스캔하므로 비효율적일 수 있다.

3-2. 튜닝 수행

튜닝 대상 SQL 문에서 사용한 사원 테이블을 분석해 보자. 해당 테이블에는 약 30만 건의 데이터가 있고, 기본 키 및 인덱스 현황을 확인해 보니 아래와 같은 결과가 출력되었다.

참고로 Key_name 항목에서 I_성별_성 인덱스는 성별(gender) 컬럼과 성(last name) 컬럼의 순서대로 구성된 복합 키(composite key)임을 알 수 있다.

WHERE 절에서 사용한 사원번호 열은 기본 키를 사용했다면 데이터에 빠르게 접근할 수 있었겠지만, 튜닝 전 SQL 문에서는 SUBSTRING(사워번호, 1, 4)과 LENGTH(사원번호)와 같이 가공하여 작성했으므로 기본키를 사용하지 않고 테이블 풀 스캔을 수행하게 된 것이다. 따라서 가공된 사원번호 열으 변경하여 기본 키를 사용할 수 있도록 조정하는 방향으로 튜닝을 수행한다.

3-3. 튜닝 결과

가공된 기본 키인 사원번호를 변형하지 않는 방향으로 조정하여 기본 키나 인덱스를 활용해 보자. 5자리이면서 1100으로 시작하는 사원번호를 찾아야 하므로 BETWEEN 연산자를 이용해 조건에 해당하는 범위 검색을 수행하거나, 비교 연산자를 활용해 본다.

튜닝을 수행한 결과 0.31초에서 0.01초로 향상되었다.

튜닝 후 실행 계획을 살펴보면, 튜닝된 SQL 문은 기본 키(key 항목: PRIMARY)의 특정 범위만 스캔(type 항목: range)한다는 것을 알 수 있다.

• 출처: 업무에 바로 쓰는 SQL 튜닝