SQL튜닝 입문 - INDEX, 결합INDEX

1. 필요성

- 일반적으로 테이블의 전체 데이터 중에서 10~15% 이하의 데이터를 처리하는 경우에 효율적

2. B* TREE 구조

- 가장 많이 사용되는 인덱스 구조, 데이터 저장 방식

- ROOT/BRANCH/LEAF NODE 구성

- BRANCH NODE: 조회하려는 값이 있는 LEAF 노드까지 도달하기 위해 비교/분기해야 될 값들이 저장됨

LEAF NODE = 인덱스 칼럼의 값 + ROWID(저장위치정보)

 

- ORDER BY에 의한 SORT를 피할 수 있음

- MAX/MIN의 효율적 처리가 가능

3. 인덱스 선정 절차

1) 프로그램 개발에 이용된 모든 테이블에 대하여 Access Path 조사

2) 인덱스 칼럼 선정 및 분포도 조사

3) Critical Access Path 결정 및 우선순위 선정

4) 인덱스 칼럼의 조합 및 순서 결정

5) 시험 생성 및 테스트

6) 결정된 인덱스를 기준으로 프로그램 반영

7) 실제 적용

4. 인덱스 생성 및 변경 시 고려사항

1) 기존 프로그램 동작 영향도 검토

2) 인덱스 생성으로 인한 인덱스 개수 증가와 이로 인한 DML 작업 속도

3) 개별 칼럼의 분포도가 좋지 않더라도, 다른 칼럼과 결합하여 자주 사용되고 결합할 경우에 분포도가 양호하다면 결합 인덱스 긍정적 검토

5. 인덱스 스캔의 원리

- 옵티마이저가 인덱스 사용을 위한 실행계획을 수립

  1) 조건 만족 최초의 인덱스 ROW 서칭

  2) Access된 인덱스 row의 ROWID를 이용 테이블 row 서칭(Random Access)

  3) 차례대로 서칭

- ROWID = 데이터를 가진 테이블을 가진 정보+테이블 스페이스를 구성하는 파일에 대한 정보+해당 데이터가 들어가 있는 블락의 정보+블락 안에서 몇 번째 있는 정보

 

6. 인덱스 사용

- 고유 인덱스 equal 검색(pk가 지정된 칼럼을 조회할 경우)

SELECT *
FROM EMP
WHERE EMPNO = 1234;

 

- 고유 인덱스 범위(Range) 검색

SELECT *
FROM EMP
WHERE EMPNO >= 1234;

 

- 중복 인덱스 범위(Range) 검색(여러 건의 데이터가 검색되는 경우)

SELECT *
FROM EMP
WHERE JOB LIKE 'COMMUNICATION%';

 

- OR, IN 조건

SELECT *
FROM EMP
WHERE EMPNO IN ('1234', '5678');

SELECT *
FROM EMP
WHERE EMPNO = 1234 OR EMPNO = 5678;

 

- NOT BETWEEN 검색(NOT은 인덱스 사용이 불가능하지만 BETWEEN이 들어가면서 사용할 수 있음)

SELECT *
FROM EMP
WHERE EMPNO NOT BETWEEN '1234' AND '5678';

 

7. 개선 예시

/*EMP의 인덱스로 EMPPK(EMPNO+DEPT+ENTER_YEAR)가 있을 경우*/
SELECT MAX(EMPNO), MIN(EMPNO)
FROM EMP
WHERE DEPT = '인사부'
AND ENTER_YEAR = '2010';

 

> MAX, MIN을 따로 분리해서 하나씩 조회한 결과를 UNION ALL로 묶어버리자

SELECT SUM(T.MIN_EMPNO) AS MINNO, SUM(T.MAX_EMPNO) AS MAXNO
FROM
(
    SELECT /*+ INDEX_ASC(EMPPK) */ A.EMPNO AS MIN_EMPNO, 0 AS MAX_EMPNO
    FROM EMP A
    WHERE A.DEPT = '인사부' AND A.ENTER_YEAR = '2010' AND ROWNUM = 1
    UNION ALL
    SELECT /*+ INDEX_DESC(EMPPK) */ 0 AS MIN_EMPNO, A.EMPNO AS MAX_EMPNO
    FROM EMP A
    WHERE A.DEPT = '인사부' AND A.ENTER_YEAR = '2010' AND ROWNUM = 1
)
;

---

8. INDEX MERGE/결합인덱스

- INDEX MERGE: 각각 단 하나의 칼럼으로 구성된 인덱스가 하나의 테이블에 2개 이상이 있었을 때, 그 인덱스를 동시에 사용하는 현상

- 결합인덱스: 두 칼럼을 사용하는 인덱스 

9. 결합인덱스의 구성

1) 결합인덱스 칼럼 선택

    - WHERE 절에서 AND 조건으로 자주 결합되어 사용되면서 각각의 분포도보다 두 개 이상의 칼럼이 결합될 때 분포도가 좋아지는 칼럼

    - 다른 테이블과 조인의 연결고리로 자주 사용되는 칼럼

    - 하나 이상의 키 칼럼 조건으로 같은 테이블의 칼럼들이 자주 조회될 때의 칼럼

 

2) 칼럼 순서 결정

    - WHERE 절 조건에 많이 사용되는 칼럼

    - EQUAL('=')로 사용되는 칼럼

    - 분포도가 좋은 칼럼 우선

    - 자주 이용되는 SORT의 순서로 결정

10. 결합인덱스 사용 방법

1) 결합 인덱스로 조합한 칼럼들 중 첫번째 칼럼을 조건절로 사용할 경우 적용

2) INDEX SKIP SCANNING(첫번째 칼럼이 누락될 경우인데 사용하는 경우)

    - 결합인덱스의 첫 번째 칼럼이 WHERE절에서 제외되어 있고, 두 번째 칼럼부터 WHERE절에 조건으로 기술되어 있는 경우에도 인덱스가 사용되는 경우

    - 힌트 사용 시 결합인덱스 사용

INDEX_SS(테이블명 INDEX명)

INDEX_SS_ASC(테이블명 INDEX명)

INDEX_SS_DESC(테이블명 INDEX명)

11. 결합인덱스 칼럼 '=' 의미

1) 범위 제한 조건

2) 체크 조건: 모든 조건이 = 이 아닌 LIKE이나 결합 인덱스의 모든 조건을 사용하지 않는 경우

    - 결합인덱스의 첫 조건이 =이 아닐 경우:나머지는 체크 조건

    - 결합인덱스의 첫 조건이 = 지만(첫 조건은 범위 제한 조건), 나머지가 아닐 경우는 체크 조건으로 해석

3) 인덱스 매칭률

인덱스 매칭률 = WHERE절에서 1ST 칼럼부터 연속된 칼럼에 대해 상수(값)를 '='로 비교하는 칼럼의 개수 / 인덱스를 구성하는 칼럼의 총 개수

 

EX)

-- AREA_INDEX(시, 구, 동)
WHERE 시 = '서울특별시'; -- 매칭률 1/3

WHERE 시 = '서울특별시'
AND 구 = '은평구'; -- 매칭률 2/3

 

4) 인덱스 매칭률 향상을 통한 속도 개선

--EMP_PAY_INDEX(YEAR, SAL_GUBUN, EMPNO)
WHERE YEAR LIKE '2024%'
AND SAL_GUBUN = '급여';

-- 튜닝
WHERE YEAR IN ('202401'
, '202402'
, '202403'
, '202404'
, '202405'
, '202406'
, '202407'
, '202408'
, '202409'
, '202410'
, '202411'
, '202412')
AND SAL_GUBUN = '급여';

 

12. 예시

-- UNIV_COURSE_INDEX(COURSE_CODE, YEAR, UNIV_NO, STD_ID)
SELECT	/*+ RULE*/ 
	A.COURSE_CODE, COUNT(STD_ID) AS CNT
FROM UNIV_COURSE A
WHERE A.COURSE_CODE < 100 -- 체크조건
AND A.YEAR = '2024' --체크조건
GROUP BY A.COURSE_CODE;
-- 매칭률 0/4

----------튜닝 후----------------
SELECT	/*+ RULE*/ 
	A.COURSE_CODE, COUNT(STD_ID) AS CNT
FROM UNIV_COURSE A, UNIV_COURSE_DEF B -- UNIV_COURSE_DEF는 COURSE_CODE를 가진 마스터 테이블이라 가정
WHERE A.COURSE_CODE = B.COURSE_CODE -- EQUAL을 넣으면서 매칭률을 높이는 효과
AND B.COURSE_CODE < 100 
AND A.YEAR = '2024' -- 매칭
GROUP BY A.COURSE_CODE;
-- 두번째 쿼리의 매칭률은 2/4

 

출처: 멀티캠퍼스 '업무에 바로 쓰는 SQL 튜닝 입문'