데이터베이스 성능 향상[Index]

데이터베이스의 성능을 향상시키는 것은 작업 속도에 굉장한 영향을 미친다.

같은 실행 결과가 나오는 SQL문도 처리 과정이 어떻게 되는지에 따라 성능이 달라진다.

 

사용자가 SQL로 결과 집합을 요구하면, 이를 생성하는데 필요한 처리경로를 DBMS에서 자동으로 생성해 주는데 이것이 바로 옵티마이저 (optimizer) 이다.

 

옵티마이저(optimizer)?

• SQL을 효율적인 절차로 처리하는 질의 최적화 도구
• SQL을 가장 빠르고 효율적으로 수행할 최적(최저비용)의 처리경로를 생성해주는 DBMS 내부의 핵심엔진

옵티마이저 종류

• 규칙 기반 옵티마이저 (Rule Based Optimizer)
  • 오라클7 버전 아래 버전에서의 기본환경
  • 미리 정해져 있는 규칙에 따라 액세스 경로를 평가하고, 실행 계획을 선택
  • 사용한 연산자나 인덱스 구조등을 기준으로 부여된 우선 순위를 기준으로 최적경로 선택
  • 데이터의 구성상태를 반영하는 통계정보를 전혀 가지지 않음
• 비용 기반 옵티마이저(Cost Based Optimizer)
  • 객체 통계(조건을만족하는데이터행수등), 시스템 통계 (CPU 속도 등) 등 통계 정보들을 이용하여 예상비용 계산
  • 계산한 예상 비용으로부터 비용이 적은 실행계획 선택
  • 통계정보를 이용해 실질적인 비용을 계산하여 최소비용 선택
  • 수립될 처리 경로 예측 어려움
  • 사용자가 원하는 처리경로로 유도하기가 어려움

통계 정보를 100% 확보 불가능하고 예측 프로그램도 틀릴 때가 있기 때문에 옵티마이저 결과가 완벽하다고 볼 수는 없다.

 

 

인덱스(index)

빠른 검색을 위해 책의 목록과 같은 역할을 함!

• 검색 속도가 빨라 짐
• 인덱스를 위한 추가적인 공간 필요 
• 인덱스를 생성하는데 시간이 걸림
• 데이터의 변경이 자주 일어나면 오히려 성능 저하

 

트리 기반 인덱스

• 데이터의 탐색 시간이 (데이터의양이아닌) 트리의높이(height)에 의해 결정되기 때문에, 탐색속도가 빠름
• 동등 검색과 범위 검색에 모두 효과적
• 트리 자료구조중 B-트리구조를 가장 많이 사용, 그 중에서 B+ 트리를 가장 많이 사용

 

B+ 트리

• 높이균형 m-원 트리(height balanced m-way tree)
• non-leaf 노드 : 인덱스 엔트리
  • 인덱스 세트(index set)
  • 리프에 있는 키들에 대한 경로 제공
• leaf 노드: 데이터 엔트리
  • 순차 세트(sequence set) 
  • 모든 키(search key) 값들을 포함
  • 순차처리 지원

 

1. (B-트리) 인덱스 검색: 인덱스 루트 노드에서 리프노드까지 탐색
2. (B-트리) 인덱스 검색: 인덱스 리프 노드(순차세 트, 데이터엔트리) 순차 검색
3. 액세스된 인덱스 검색키에 해당하는 테이블의 실제행을 액세스

=> 액세스되는 테이블행의 순서는 인덱스행의 순서와 일치

 

index 생성

CREATE INDEX 인덱스이름 ON 테이블(Column, ...)
DROP INDEX 인덱스이름

• 사용자가 인덱스를 생성하지 않았어도 기본키나 유일키에 대해서 자동으로 인덱스가 생성됨

 

인덱스를 이용한 접근 경로

옵티마이저가 각 실행 계획에 따른 비용을 산정하는데 있어 테이블이나 뷰의 데이터를 읽어오는 방식

• Full Table Scan
  • 인덱스를 사용하지 않는 검색방식
  • 질의를 수행하기 위해 테이블 전체를 읽는 방식
  • 인덱스가 없는 경우
  • 테이블에 있는 블록 대부분을 읽어, 인덱스 사용이 의미가 없는 경우
  • 데이터 크기가 작아 한번에 데이터를 읽어들일 수 있는 경우
• Index Range Scan
  • 인덱스를 사용하는 검색방식
  • B-트리 기반 인덱스의 가장 일반적인 형태의 액세스 방식
  • 인덱스 루트 블록에서 리프블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위(range)만 스캔하는 방식
• Index Unique Scan
  • 인덱스의 검색 키값이 중복되지 않는 인덱스(UNIQUE, Primary Key 속성에 대한 인덱스)를, ‘=‘ 조건으로 탐색하는 경우사용
  • 유일한 값을 검색하게 되므로 이 방법을 사용한 검색 결과로 산출되는 튜플 수는1개
  • 인덱스 루트 블록에서 리프블록까지의 수직적 탐색만으로 데이터를 찾음

 

인덱스 후보 컬럼

1. 분포도가 좋은 컬럼
2. 변경이 자주 발생하지 않는 컬럼
3. SELECT문의 조건절에서 자주 사용되는 컬럼
4. 정렬(sort) 발생을 제거하는 컬럼 => ORDER BY절에 있는 컬럼은 인덱스 후보컬럼이 될 수 있다.

분포도(%) = (1/식별가능한수) X 100 => 분포도 값이 작을수록 분포도가 좋은 것

 

인덱스가 있어도 사용하지 못하는 경우

1. 인덱스 컬럼이 비교되기 전에 변형이 일어나는 경우
   1. 외부적변형: 사용자가 인덱스를 가진 컬럼을 SQL함수, 사용자 정의함수, 연산, 결합(||) 등으로 가공을 시킨 후 비교할 때 발생
   2. 사용자가 직접 컬럼을 가공하지 않았더라도, 서로 다른 데이터타입을 비교할 때 DBMS가 어느 한쪽을 기준으로 동일한 타입이 되도록 내부적인 변형을 일으키게 됨으로써 발생
2. 부정형(Not, <>)으로 조건을 기술한 경우
3. 인덱스 컬럼이 NULL로 비교되는 경우
4. 옵티마이저의 선택에 따른 경우

 

옵티마이저 설정

SQL> ALTER SESSION SET OPTIMIZER_MODE=RULE;

SQL> ALTER SESSION SET OPTIMIZER_MODE=CHOOSE;

 

 

Hint?

• 사용자가 액세스 경로의 변경을 위해서 SQL 내에 요구사항을 기술하면 옵티마이저가 액세스 경로를 결정할 때 이를 참조하도록 하는 사용자 인터페이스
• 옵티마이저에게 모든 것을 맡기지 않고 사용자가 원하는 보다 좋은 액세스 경로를 직접 선택할 수 있도록 함

--SAL 인덱스사용
SELECT  /*+ INDEX(EMPBIG SAL2_IDX) */ 
*  FROM  EMPBIG                                              
WHERE   SAL > 1000                              
AND  JOB   LIKE 'SA%'
--ENAME 인덱스사용하지않음
SELECT   /*+ NO_INDEX(EMPBIG ENAME2_IDX) */
 *    FROM  EMPBIG                                             
WHERE  ENAME LIKE 'AB%'                              
AND  JOB = 'CLERK'
--FULL TABLE SCAN
SELECT   /*+ FULL (EMPBIG) */
 *    FROM  EMPBIG                                              
WHERE  ENAME LIKE 'AB%'                              
AND  JOB = 'CLERK'

 

 

결합 컬럼 인덱스

• 결합 인덱스가 많아질수록 인덱스 개수와 크기가 증가하여, 데이터 변경 시 인덱스 수정에 따른 부하 발생
• 결합 인덱스는 특정한 액세스 형태에 대해서만 효과가 있음