SQL Tuning summary No.2

12 Apr 2024

index 구성 보기

oracle에서 comment 없이 index 구성만 보는 법은 아래와 같다.

column_position이 1번이면 leading column, 2번이면 그 뒤에 붙는 컬럼이라고 보면 된다.

SELECT a.table_name 
   , a.index_name 
   , a.column_name 
   , a.column_position 
FROM all_ind_columns a 
 WHERE a.table_name = '테이블 이름' 
 ORDER BY a.index_name
      , a.column_position

index design

index를 설계할 때는 위의 starting point, end point, random access, table filter를 알아야 한다.
이를 기준으로 조건절에 쓰이는 column들을 구분하면 아래와 같다.
- index access
  - index 구성 column이어야 한다.
  - index range scan을 정한다
  - starting point와 end point를 지정한다
  - = 조건으로 계속되면 전부 index access조건이다.
- index filter
  - index 구성 column이어야 한다.
  - = 조건이 끝나고 난 다음 column부터는 range scan에 영향이 없다.
  - 대신 range scan이 끝나고 나온 index record 중 어떤 record가 random access할 지 결정하는 filter역할을 한다.
  - index filter는 random access를 줄여준다는 점에서 매우 중요한 역할을 한다.
- table filter
  - index 구성 column이 아니어야 한다.
  - index filter까지해서 random access를 수행한 뒤, 실제 table data block까지 읽어온 resultSet에서 filtering한다.
위의 3가지 조건을 생각하면서 어떻게 index를 구성할 지 생각해야 한다.
다시말하면 아래와 같다.
where 조건문에 쓰는 조건은 index access 조건과 index filter 조건, table filter 조건으로 나뉜다.
- index access는 수직탐색(시작점)과 수평탐색(끝점)을 결정하는 용도의 조건이다.
- index filter는 table에 실제로 access를 할지 결정하는 용도의 조건이다.
- table filter는 index가 아예 없는 column의 조건문인 경우다. table record를 얻어온 뒤 filtering하는 조건이다.
- index로 사용된 컬럼 중 범위처리된 조건까지만이 index access 조건이며, 그 뒤부터는 index filter조건이다.
index access 조건으로 index key scan을 할 범위를 정한다.
- 그 중에서 어떤 index record의 rowid를 가지고 table access할 지는 index filter조건이 정한다.
  - 그렇게 가져온 table record를 다시 table filter조건으로 걸러서 resultSet을 만드는 것이다.
아래와 같은 조건절들이 있다고 해보자.

WHERE 고객등급 = :v1
AND   고객번호 = :v2
AND   거래일자 >= :v3

WHERE 고객등급 = :v1
AND 고객번호  = :v2
AND 거래일자 >= :v3
AND 거래유형 = :v4

WHERE 고객등급 = :v1
AND 고객번호 = :v2
AND 거래일자 >= :v3
AND 상품번호 = :v5

WHERE 고객등급 = :v1
AND 고객번호 = :v2
AND 거래일자 >= :v3
AND 거래유형 = :v4
AND 상품번호 = :v5

위에서 고객등급, 고객번호, 거래일자는 필수 조건이다. 거래유형과 상품번호는 옵션조건이다.
- 고객등급, 고객번호는 어떤 컬럼이 앞에 오든 index range scan에 영향이 없다. 둘 모두 = 조건이기 때문이다.
- 거래유형과 상품번호도 어떤 컬럼이 앞에 오든 index range scan에 영향이 없다.
  - index scan범위가 고객등급, 고객번호, 거래일자에 의해 결정되기 때문이다.
따라서 고객등급, 고객번호, 거래일자는 index를 만들 떄 반드시 포함해줘야 한다.

옵션 조건은 아래와 같이 두 개로 만들어 둘 수 있다.

X01: 고객등급, 고객번호, 거래일자, 거래유형
X02: 고객등급, 고객번호, 거래일자, 상품번호

하지만 위를 보면 사실 거래일자에서 index range scan이 끝난다.
따라서 두 index를 나눌 필요가 없다. 거래유형, 상품번호는 index filter조건이기 때문이다.

따라서 두 index를 하나로 합쳐준다.

X01: 고객등급, 고객번호, 거래일자, 거래유형, 상품번호

고객번호가 PK라서 아래와 같이 바꿔야 한다고 생각할 수도 있다.

X01: 고객번호, 거래일자, 고객등급, 거래유형
X02: 고객번호, 거래일자, 고객등급, 상품번호

하지만 = 조건으로 걸리는 경우, 어느것이 선두컬럼이든 range scan의 양이 줄어들진 않는다.
따라서 어떻게 만들어도 상관없다.
오히려 고객번호가 필수인데 고객등급이 범위검색일 떄는 고객등급이 먼저 오는 게 좋다.
- 그래야 index skip scan을 하는 데 있어 유리하다. 고객등급이 2인 경우, 1인 index record는 전부 skip해야 skip 성능이 극대화되기 때문이다.
- 선두컬럼에서 skip scan을 해줌으로써 scan 량이 확 줄어들 수 있는 것이다.

index design example 1 -index 마지막에 column 더하기

그런데 현실적으로 운영 index를 새로 짜는 건 쉽지 않은 작업이다.
관리의 문제 및 DML 성능 저하의 문제가 있다.
그래서 기존 index column의 끝에 새로운 index field를 더하는 형태로 진행된다.

아래와 같은 sql이 있다고 해보자.

select /*+ index(emp emp_x01) */
from emp
where deptno = 30
and sal >= 2000

현재 deptno + job으로 index가 구성되었다고 해보자.
해당 sql을 만족하는 data를 찾기위해서 index는 6개를 모두 scan한다.
그리고 table에도 6번 모두 access할 수밖에없다. scan은 6번은 아니지만, 6번의 access가 필요하다.
sal이 2000 이상인 record가 더 있는 지 알수가 없기 때문이다.
deptno + job 대신 deptno + sal로 index를 바꾸고 싶지만 아래와 같이 index를 사용하는 sql이 있다.
```
SELECT *
FROM emp
where deptno = 30
and job = 'CLERK'
```
따라서 차선책으로 deptno + job + ‘sal’로 sal을 맨 끝에다가 추가해준다.
물론 index range scan량이 줄어들지는 않는다.
하지만 index에 sal이 포함되어 정렬되기 때문에, table access양이 6번에서 1번으로 줄어든다.
random access양이 줄어드는 획기적인 개선이다.
실제 개선사례를 살펴보도록 하자.
아래 sql에서 서비스 번호만을 index로 사용하고 있었다.
like지만 전방일치기 때문에 range scan이 가능하다.

시작점을 한정지을 수 있기 때문이다.

select 렌탈관리번호, 고객명, 서비스관리번호, 서비스번호, 예약접수일시
      ,방문국가코드1, 방문국가코드2, 방문국가코드3, 로밍승인번호, 자동로밍여부
from 로밍렌탈
where 서비스번호 like '010%'
and 사용여부 = 'Y'

아래는 query 실행결과를 report로 만든것이다.
index를 scan해서 얻은 건수(rows)는 266476인데, 그 건수만큼 table을 random access했다.
table access 쪽에 보면 cr=266968인데 index scan시 읽은 block 1011개를 뺴면 265,957개 만큼 읽었다.
table access가 266,476인데 블록 I/O가 265,957이면 CF 계수가 매우 낮은 것이다. 데이터가 워낙 많아서 서비스 번호를 만족하는 데이터가 뿔뿔이 흩어진 것이다.
거기다 사용여부 조건 체크 이후 남은 rows는 겨우 1909개다. 테이블 access 이후 사용여부 = ‘Y’ 조건을 필터링하면서 대부분 걸러졌다.
이렇게 비효율적으로 scan과 I/O를 하지 않게 사용여부를 index에 포함시켜보면 획기적으로 성능이 개선된다.

index design example 2 -수행빈도 고려하기

인덱스 설계에는 여러 가지 고려 사항이 있다.
- 인덱스 설계의 가장 큰 조건은 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정하는 것이다.
  - 특히 = 조건으로 자주 조회하는 컬럼은 앞으로 가야한다.
- 그 외에는 수행빈도도 중요하다.
  - 자주 쓰이지 않는 SQL은 조금 비효율적이어도 괜찮다.
  - 하지만 자주 쓰이는 SQL은 효율적으로 수행되게 짜야 한다.
  - 특히 join 시 driving table은 알고리즘 상 비효율 scan이 한번에 그친다.
  - 그러나 driven table은 record마다 일어나기에, 매우 효율적인 index로 구성해야 한다.
  - driven table은 되도록이면 조건절 column을 index에 넣어 최소한 index filter조건으로는 사용되게 하여 table access를 줄여야 한다.
이제는 실질적인 index 설계 팁을 살펴보자.
- 가장 큰 팁은 10개의 sql이라면, 그 중 액세스 경로 1-2개만 최적 인덱스를 설계하는 것이다.
- 나머지는 조금 비효율적이어도 목표한 성능만 나오게 설계하는 것이다.
예를 들어, 취급부서-취급지점,취급자,입력자,대리점설계사,대리점지사와 청약일자, 보험개시일자, 보험종료일자, 데이터 생성일시를 모두 짝지으면 24개나 된다.

그것보단 4개의 index로 줄여준다. 그 이유는 24개의 index는 insert, update 같은 DML에서 성능을 떨어뜨리기 때문이다.

X01: 청약일자, 취급부서, 취급지점, 취급자, 입력자, 대리점설계사, 대리점지사
X02: 보험개시일자, 취급부서, 취급지점, 취급자, 입력자, 대리점설계사, 대리점지사
X03: 보험종료일자, 취급부서, 취급지점, 취급자, 입력자, 대리점설계사, 대리점지사
X04: 데이터생성일시, 취급부서, 취급지점, 취급자, 대리점설계사, 대리점지사

위와 같이 설계한 이유는 2가지이다.
- 일자 조회구간이 길지 않으면 인덱스 스캔 비효율은 성능에 미치는 영향이 적다
  - 실제로 가계약은 주로 3일 내를 조회하며, 보통 전일자 조회다.
- 인덱스 스캔 효율보다 테이블 액세스가 더 큰 부하요소다.

index design example 3- 중복 index 제거하기

아래와 같은 index의 경우 X03만 남기면 된다. X01, X02를 X03이 포함하기 때문이다.

X01: 계약ID, 청약일자
X02: 계약ID, 청약일자, 보험개시일자
X03: 계약ID, 청약일자, 보험개시일자, 보험종료일자

아래와 같은 경우는 달라보여도 중복이나 다름없을 수도 있다. 계약ID의 카디널리티가 낮을 때 그러하다.(선택율이 높을 때, 중복값이 많을 떄)

그럼 X05를 제외하곤 나머지는 전부 없애도 된다.

X01: 계약ID, 청약일자
X02: 계약ID, 보험개시일자
X03: 계약ID, 보험종료일자
X04: 계약ID, 데이터생성일시
X05: 계약ID, 청약일자, 보험개시일자, 보험종료일자, 데이터생성일시

index design example 4- sort 연산 고려하기

소트연산을 줄이기 위해 조건절에는 없어도 index에 포함시킬 수도 있다.

SELECT *
FROM 계약
WHERE 취급지점ID = :trt_brch_id
AND 청약일자 BETWEEN :sbcp_dt1 AND :sbcp_dt2
AND 입력일자 >= trunc(sysdate -3)
AND 계약상태코드 in (:ctr_stat_cd1, :ctr_stat_dt2, :ctr_stat_cd3)
ORDER BY 청약일자, 입력자ID

비록 입력자ID는 조건절에는 없지만, sort 연산을 줄이기 위해 index에 추가해준다.
따라서 취급지점ID, 청약일자, 입력자ID로 index를 구성하게 된다.
그냥 청약일자, 입력자ID로만 구성하면 조건절이 index를 제대로 타지못해 index full scan이 수행된다.

in절의 경우, order by가 소트 연산을 생략하기 위해서는 조건 column이 index filter로 작동해야 한다.

index는 (거주지역, 혈액형, 연령)으로 구성됐다.

SELECT 고객번호, 고객명, 거주지역
FROM 고객
WHERE 거주지역 = '서울'
AND 혈액형 IN ('A','O')
ORDER BY 연령

in절 변환이 in-list 형식으로 풀려선 안된다. 즉 union all로 풀려선 안된다는 의미다.
만약 in-list로 풀린다면, index를 바꾸거나 index 중 하나를
- index를 거주지역, 혈액형, 연령으로 하면 3개가 모두 index access 조건이 된다.
- 따라서 index를 거주지역, 연령, 혈액형으로 비틀어준다.
index를 일단 생성하기로 했다면, 설계 시 중요한 것은 사용빈도다.
- 선택도는 생성을 할 지에 관한 판단기준이다.
- 생성을 했다면 사용빈도가 선두컬럼에 관한 판단기준이다.
- 그 중 = 조건을 앞쪽에 위치시키는 게 좋다.

index의 sort효과

index는 자체로 정렬이 되어있기 때문에, order by를 써도 optimizer가 sort 연산을 하지 않을 수 있다.
index가 (장비번호, 변경일자, 변경순번)이라고 해보자.
조건절에는 장비번호, 변경일자만 쓰이고 변경순번은 쓰이지 않았지만 별 상관없다.
order by에 변경순번이라고 한다면 index는 이미 장비번호, 변경일자 대로 정렬되어 있기에 그 다음 변경일자도 정렬되어 있다.
따라서 order by 변경순번을 붙이든 안 붙이든 상관이 없는 것이다.

대신 order by를 index에 없는 column을 하면 sort by 연산이 추가될 것이다.

select *
from 상태변경이력
where 장비번호 = 'C'
and 변경일자 = '20180316'

index를 가공해선 안된다는 점은 order by에서도 동일하다.
물론 조건절에서 가공한 것보단 덜 치명적이다. order by에서는 sort by 연산이 추가되는 것이기 때문이다.
별칭과 관련된 어처구니 없는 실수를 살펴보면서 별칭에 주의해주자.
주문번호 column을 TO_CHAR로 가공한 게 보인다. 그런데 ORDER BY를 가공한 주문번호로 정렬했다.
주의 깊지 못한 별칭으로 인해 sort by 연산이 추가된다.

SELECT *
FROM (
  SELECT TO_CHAR(A.주문번호, 'FM000000') AS 주문번호, A.업체번호, A.주문금액
  FROM 주문 A
  WHERE A.주문일자 = :dt
  AND A.주문번호 > NVL(:next_ord_no, 0)
  ORDER BY 주문번호 /**여길 A.주문번호 라고 해야... */
)
WHERE ROWNUM <=30

sort by의 효과는 order by에서만 보이는 게 아니다.
select에서도 볼 수 있다.
아래와 같이 (장비번호, 변경일자, 변경순번)으로 구성되었다고 해보자.
장비번호와 변경일자 순으로 scan을 하기 때문에 그다음 변경순번은 반드시 정렬되어 있다.

따라서 sort by 연산이 필요없다.

SELECT MIN(변경순번)
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'

아래와 같이 MAX도 마찬가지다.
블록은 양방향연결 리스트기 때문에 MAX든 MIN이든 상관없다.

sort by연산이 필요없는 것은 MAX도 마찬가지라는 의미다.

SELECT MAX(변경순번)
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'

그러나 이는 같은 type일 때를 기준으로 한다.
아래와 같이 type을 바꿔버리면 sort by가 필요하다.
저기서 NVL은 문제가 없다. 문제는 TO_NUMBER다.
number로 바꾸는 순간, sort by 연산이 필요해진다.
애초에 숫자type으로 설계했어야 했다. 모든걸 char, varchar로 바꾸는 것은 tuning의 가능성을 제한해버린다.
```
SELECT NVL(MAX(TO_NUMBER(변경순번)), 0)
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'
```
오라클 11g부터는 prefetch와 배치 I/O도 도입됐다.
prefetch나 배치 I/O나 버퍼블록에서 읽으면 차이가 없다. 디스크 I/O일 때 차이가 난다.
테이블 배치 I/O를 하게되면 order by를 명시하지 않으면 index를 통한 암묵적 결과 정렬이 통하지 않는다.
아래처럼 맨 끝에 ORDER BY를 넣어줘야 순서가 정렬된 채로 resultSet이 나온다.
물론 order by를 추가한다고 sort 연산이 추가되는 것은 아니다.

SELECT A.등록일시, A.번호, A.제목, B.회원명, A.게시판유형, A.질문유형
FROM (
  SELECT A.*, ROUWNUM NO
  FROM (
        SELECT 등록일시, 번호, 제목, 작성자번호, 게시판유형, 질문유형
        FROM 게시판
        WHERE 게시판유형 = :TYPE
        ORDER BY 등록일시 DESC /**TOP N 알고리즘을 위한 order by */
      ) A
  WHERE ROWNUM <= (:page * 10) /**TOP N 알고리즘을 위한 rownum*/
    ) A, 회원 B
WHERE A.NO >= (:page - 1) * 10 + 1
AND B.회원번호 = A.작성자번호
ORDER BY A.등록일시 DESC  /**11g부터 여기에 order by를 명시해야 정렬 순서 보장 */

heo-jae-won@home:~$

Archive

About

RSS