공부흔적

4 인덱스의 종류

1) B-Tree 인덱스

인덱스가 생성될 경우 leaf block -> branch block -> root block의 순서

leaf block : 실제 데이터를 저장하고 있는 데이터 블록들의 주소

branch block : leaf blocks 에 대한 정보

root block : branch block 에 대한 정보

데이터를 찾는 순서 

root block -> branch block -> leaf block 정보를 찾음 

-> 해당데이터의 rowid 를 찾은 후 데이터가 들어있는 블록을 메모리로 복사 

특징  

B-Tree 에서 B는 Binary이란 의미와 함께 Balance의 의미도 있음

Root block를 기준으로 왼쪽과 오른쪽에 들어있는 데이터의 Balance가 맞을 때, 성능이 가장 좋음

일반적으로 가장 많이 사용됨

아래는 다양한 B-Tree의 인덱스들...

(1) UNIQUE INDEX

인덱스를 만드는 key 값에 중복되는 데이터가 없다는 뜻

해당 테이블 컬럼에 중복된 값이 없고, 앞으로도도 중복된 값이 들어올 수 없다는 의미

일반 인덱스와 비교시에도 속도가 빠르기 때문에 둘 중에서는 유니크 인덱스 권함

키 컬럼의 데이터들이 중복없는 유일성을 보장하는 인덱스라서 속도는 빠름

주의 - 나중에 중복되는 데이터가 들어올 수 없는 단점이 있기 때문에 잘 생각해서 사용해야 함

생성문법

CREATE UNIQUE INDEX 인덱스명

ON 테이블이름(컬럼명1 ASC|DESC, 컬럼명2,.....);

생성예제

CREATE UNIQUE INDEX IDX_DEPT2_DNAME

ON dept2(dname);

(2) NON-UNIQUE INDEX

중복되는 데이터가 들어가야 하는 컬럼일 경우에는 UNIQUE INDEX 를 생성할 수 없기 때문에

NON-UNIQUE INDEX 를 생성함

생성문법

CREATE INDEX 인덱스명

ON 테이블명(컬럼명1 ASC|DESC, 컬럼명2, ....);

생성예제

dept2 테이블의 area 컬럼에 NON UNIQUE INDEX 를 생성

CRAETE INDEX idx_dept2_area

ON dept2(area);

(3) FUNCTION BASED INDEX(FBI-함수기반 인덱스)

인덱스는 WHERE 절에 오는 조건에 컬럼이나 조인컬럼 등에 만들어야 함 *

(단 몇 가지 특별한 경우에 SELECT에 있는 컬럼에 생성하기도 함)

WHERE pay=1000이란 조건일 경우 pay 컬럼에 인덱스를 만들어야 함

pay 컬럼에 인덱스를 생성 후 위와 같이 사용하면 인덱스를 이용함 

WHERE pay + 1000 = 2000 의 조건 검색은 인덱스 이용 못 함 - 연산자를 사용하는 경우 

WHERE ename != 'FORD'; 인덱스 이용 못 함- NOT을 사용하는 경우

=> INDEX Suppressing Error : 인덱스는 잘 생성하고, SQL 를 잘못 작성해서 인덱스를 사용할 수 없는 경우

인덱스를 사용하려면 WHERE 절의 조건을 절대로 다른 형태로 가공해서 사용하면 안 됨

WHERE pay+1000 = 2000 을 인덱스를 사용하고 싶으면?

(pay+1000) 이 형태로 사용해 줌

이런 형태의 인덱스를 함수기반 인덱스라고 함

pay+1000 이라는 컬럼이 없지만 함수처럼 연산을 해서 인덱스를 만들어준다는 의미

생성예제

CREATE INDEX idx_prof_pay_fbi

ON professor(pay+1000);

단점

pay+1000 이라는 컬럼이 생성됨, 오라클이 인덱스를 만들 때, 이 연산을 이용해 인덱스를 만들어 주기 때문

하지만! 임시적인 해결책임, pay+1000 의 조건이 변하면 인덱스도 다시 만들어줘야하는 단점이 있고

FBI는 기존 인덱스를 활용할 수 없음 

(4) DESCENDING INDEX( 내림차순 인덱스 )

여러 인덱스를 생성할 때 큰 값이 먼저오게, 내림차순 순으로 인덱스를 생성함

1000

999

998 

...

이런식으로 인덱스 생성

사용 

큰 값을 많이 조회하는 SQL에 사용하는 것이 좋음

예) 

계좌조회시 최근 날짜부터 나오게 하는 경우(최근 날짜가 큰 날짜)

매출 테이블의 상위 매장이나 사원을 보는 경우(매출이 큰 값을 먼저 조회하는 경우)

문제) 하나의 메뉴에 오름차순과 내림차순을 한꺼번에 조회할 경우는?

A 최근 날짜 부터 계좌조회  

B 예전 날짜 부터 계좌조회

인덱스를 오름차순, 내림차순으로 두번이나 만들면 (인덱스가 많으면) DML 성능에 악영향

해결 : 인덱스를 위나 아래에서 읽게 하는 힌트라는 방법을 제공

         힌트들을 이용해 튜닝에서는 정렬하지 않고, 정렬효과를 내고, 최소값과 최대값을 구하기도 함

(5) COMPOSITE INDEX ( 결합인덱스 )

인덱스를 생성할 때 두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것

사용

SQL문장에서 WHERE 절의 조건 컬럼이 2개 이상이 AND로 연결되어 함께 사용되는 경우에 사용

아주 많이 사용되나 , 인덱스를 잘못 생성하게 되면 굉장히 나쁜 영향을 미침

예) emp 테이블에 100명의 인원 중 남자(M)은 50명, 여자(F) 50명

50명의 남자 중 이름이 'SMITH'인 사람이 단, 2명 있다고 가정

일반쿼리문

SELECT ename, sal

FROM emp

WHERE ename='SMITH'

AND sex='M';

결합인덱스 생성 구문 예

CREATE INDEX idx_emp_comp

ON emp(ename, sex); 이 부분이 위와 같음  WHERE ename='SMITH'

  AND sex='M';

해당 데이터를 찾을때 두 개의 컬럼을 동시에 만족하는 블록을 검색하겠다는 의미

같은 테이블에 같은 SQL이지만 결합 인덱스를 어떻게 생성하는가에 따라서 속도나 검사횟수가 완전히 달라질 수도 있음

ON emp(ename, sex) 100건의 성별 중 2건의 이름 찾은 후 성별검사 - 2건 출력

On emp(sex, ename)  100건의 성별 중 50건을 찾은 후 다시 이름을 검색해서 - 2건 출력

=> 첫번째 조건에서 최대한 많은 데이터를 걸러서 두번째 검사를 쉽게 만들어야 함

결합인덱스는 경우의 수도 많고, 고려해야할 사항이 많으므로, 아주 신중하게 고려하고 사용해야 함 

1. 인덱스란 ?

* 인덱스  

어떤 데이터가 어디에 있다라는 주소록의 개념

잘 활용하면 아주 빠르게 데이터를 찾을 수 있음

어떤 데이터의 주소는 어디다. 라는 식으로 

모든 데이터들의 주소록을 만들어 관리하는 기법

=> 

인덱스가 있으면 모든 블록을 다 읽지 않고, 원하는 데이터가 있는 블록 주소를 찾아내

그 블록만 메모리로 복사해 와서 작업이 빨리 끝남

어떤 데이터가 어디에 저장되어 있는지 알고 있기 때문에 데이터 조회,변경, 호출해야 할 때 아주 빠르게 작업 가능

BUT 인덱스 생성이나 관리를 잘못할 경우는 성능이 떨어지는 역효과가 발생하기도 함

* 쿼리 실행시 데이터 검색 과정

많이 사용되는 데이터는 데이터베이스 버퍼캐시에 캐싱해두고 

많이 사용하지 않는 데이터는 디스크에 저장했다가 

필요할 때 메모리로 복사해 와서 조회나 변경 작업등을 함

* Table full scan

사용자가 어떤 데이터를 찾을 때 

메모리에서 해당 데이터를 못찾을 경우 

파일에 가서 데이터를 찾아 메모리로 복사해와야 하는데 

하드디스크에 데이터가 너무 많을 경우(예 1억개)에는

해당 데이터가 어떤 블록이 들어 있는지 모른다면

모든 블록을 읽어서 확인함

2. 인덱스의 생성 원리

해당 테이블의 내용들을 모두 다 읽어 메모리로 가져옴

어떤 내용이 있는지 내용을 파악해서 목록만듬

인덱스를 만드는 동안 데이터가 변경되면 문제가 되므로

해당 데이터들이 변경되지 못하도록 조치한 후 PGA의 Sort area에서 정렬을 함

PGA 메모리가 부족하면 임시테이블스페이스(Temporary Tablespace)를 사용해서 정렬하게 됨

메모리에서 정렬과정이 끝난 데이터들은 인덱스를 저장하는 파일의 블록에 순서대로 기록함

인덱스 생성

전체 테이블 스캔 -> 정렬(sort) -> Block 기록

인덱스는 데이터가 정렬되어서 들어감 

3. 인덱스 구조와 작동원리(B-Tree  인덱스 기준)

인덱스는 key 칼럼와 rowid 칼럼으로 이루어짐

key 칼럼 : 인덱스를 생성하라고 지정한 컬럼 값

rowid 칼럼 : 데이터가 저장되어 있는 주소 

실제 데이터 - Data file

인덱스 정보 - Index file 에 저장되어 있음

1 가장 먼저 메모리의 데이터베이스버퍼캐쉬에서 해당 데이터가 있는지 확인

2 인덱스 파일 찾는 순서는 트리구조의 오른쪽 상단그림 

root 9 - branch 7 - leaf 1 순으로 읽음 

3 인덱스에 있는 블록 데이터를 가지고 옴

경우에 따라 인덱스를 사용하지 않는 것이 성능에 더 좋을 수도 있음

여러건의 데이터를 조회할 경우 인덱스를 사용할 때는 한 번에 하나의 블록만 읽을 수 있음                 

single block I/O : 1번에 1개의 블록만 읽어서 메모리로 가져옴( db_block_size )

multi block I/O : 인덱스를 쓰지 않았을 경우에 한 번에 여러 블록을 가져오는 것 ( DB_FIEL_MUTLIBLOCK_COUNT ( MBRC ))