[SQL튜닝] 인덱스

[SQL튜닝] 인덱스

---

주요내용

• 인덱스의 필요성
• B*Tree 구조
• 인덱스 선정 절차
• 인덱스 생성 및 변경 시 고려할 사항
• 인덱스 스캔의 원리
• 인덱스 사용

학습목표

• 인덱스에 대한 이해를 바탕으로 비효율적으로 수행되는 SQL 문장을 진단할 수 있다.
• 인덱스를 활용하여 SQL 문장을 성능목표에 부합하도록 개선할 수 있다.
• 인덱스 활용을 통해 개선된 SQL 문장이 성능목표에 부합하는지 테스트할 수 있다.

---

인덱스의 필요성

• 사용자가 인덱스를 사용하는 이유 : 데이터베이스에 저장된 자료를 더욱 빠르게 조회하기 위해 인덱스를 생성하여 사용함
• 모든 SQL이 인덱스를 사용해야 하는가? => 일반적으로, 인덱스는 테이블의 전체 데이터 중에서 10~15% 이하의 데이터를 처리하는 경우에 효율적이며, 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 나음

---

B*Tree 구조

• 가장 많이 사용되는 인덱스의 구조라 할 수 있으며, 인덱스의 데이터 저장 방식이기도 함
• Root(기준) / Branch(중간) / Leaf(말단) Node로 구성됨
• Branch 노드는 Leaf 노드에 연결되어 있으며, 조회하려는 값이 있는 Leaf 노드까지 도달하기 위해 비교/분기해야 될 값들이 저장됨
• Leaf 노드 = 인덱스 컬럼의 값(오름 또는 내림차순으로 Sort되어 저장됨) + ROWID(테이블에 있는 해당 row를 찾기 위해 사용되는 논리적인(저장위치) 정보)
• B*Tree 구조의 핵심은 Sort
  • ORDER BY에 의한 Sort를 피할 수 있음
  • MAX/MIN의 효율적인 처리가 가능함
• B*Tree 구조 활용 예 1
  • (COURSE_CODE, YEAR, COURSE_SQ_NO)로 구성된 인덱스 (EC_COURSE_SQ_PK)가 있음

    SELECT course_code, year.course_sq_no
    FROM ec_course_sq
    WHERE course_code = 1960
    AND year = '2002'
    ORDER BY course_sq_no DESC;

• B*Tree 구조 활용 예 2
  • (COURSE_CODE, YEAR, COURSE_SQ_NO)로 구성된 인덱스 (EC_COURSE_SQ_PK)가 있음

    SELECT MAX(course_sq_no)
    FROM ec_course_sq
    WHERE course_code = 1960
    AND year = '2002';

---

인덱스 선정 절차

• 프로그램 개발에 이용된 모든 테이블에 대하여 Access Path 조사
• 인덱스 칼럼 선정 및 분포도 조사
• Critical Access Path 결정 및 우선 순위 선정
• 인덱스 칼럼의 조합 및 순서 결정
• 시험 생성 및 테스트
• 결정된 인덱스를 기준으로 프로그램 반영
• 실제 적용

---

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

• 기존 프로그램의 동작에 영향성 검토
• 필요할 때마다 인덱스 생성으로 인한 인덱스 개수의 증가와 이로 인한 DML 작업의 속도
• 비록 개별 칼럼의 분포도가 좋지 않을지라도 다른 칼럼과 결합하여 자주 사용되고, 결합할 경우에 분포도가 양호하다면 결합 인덱스 생성을 긍정적으로 검토

---

인덱스 스캔의 원리

• 옵티마이저가 인덱스 사용을 위한 실행계획을 수립함
  • 조건을 만족하는 최초의 인덱스 row를 찾음
  • Access된 인덱스 row의 ROWID를 이용해서 테이블에 있는 row를 찾음(Random Access)
  • 처리 범위가 끝날 때까지 차례대로 다음 인덱스 row를 찾으면서 Scan을 반복함
• 인덱스 스캔 시에는 한 번의 I/O가 발생할 때 마다 한 개의 Block 씩을 처리함

---

인덱스 사용

• 고유(Unique) 인덱스의 Equal(=) 검색

  SELECT * FROM emp WHERE empno = 7788;

• 고유(Unique) 인덱스의 범위(Range) 검색

  SELECT * FROM emp WHERE empno >= 7654;

• 중복(Non Unique) 인덱스의 범위(Range) 검색

  CREATE INDEX job_index ON emp (job);

  SELECT * FROM emp WHERE job LIKE 'SALE%';

• OR & IN 조건 - 결과의 결합

  SELECT * FROM emp WHERE empno IN(7654, 7788);

• NOT BETWEEN 검색

  SELECT * FROM emp WHERE empno NOT BETWEEN 7654 AND 7788;