8장. INDEX (인덱스)

1. 인덱스(INDEX)란 무엇인가?

 

 인덱스는 책 뒤편에 있는 '찾아보기'에 해당한다. 정렬된 값을 보고 원하는 값을 빨리 찾을 수 있다. 메모리에 비해 하드디스크는 데이터를 가져오는 것이 느리다. 하드디스크에서 데이터를 가져오기 위해서는 인덱스가 필수이다. 그래야지 오라클이 사용자에게 빨리 값을 리턴시킬 수 있다.

 

 

 

 

 

 

---

 

 

 

 

 

2. 인덱스의 생성 원리

 

 인덱스는 하드 디스크에 데이터가 저장되는 데이터 파일이다. 블럭(block) 단위로 저장된다. 이 데이터들은 랜덤(random)한 위치에 흩어진다.

* PGA (Program Global Area)

오라클에서 사용하는 메모리 영역(프로그램에서 사용하는 메모리 영역)

 

데이터를 잘 찾기 위해서는 정렬해야 한다.

1. 하드 디스크에 있던 랜덤한 위치에 있던 데이터들을 메모리로 불러온다.
2. 랜덤으로 배치되었던 데이터를 순서대로 정렬한 후 위치하고 있는 좌표를 저장해둔다.
3. 정리한 하드 디스크에 저장한다. 데이터와 데이터 위치 정보를 같이 저장해둔다.

이러한 방법을 쓴다면 위치 데이터도 저장하기 때문에 파일 사이즈가 커진다.

 

 

 

 

 

---

 

 

 

 

 

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

 

 기본적인 트리 구조로 인덱스를 만든다. 데이터에 따라 개수가 달라진다. 이때 B-TREE 인덱스는 데이터베이스 테이블의 특정 열(또는 열들)의 값에 대해 효율적인 검색, 삽입, 삭제 작업을 가능하게 하는 인덱스 구조이다.

 

 

 

구체적인 작동 원리

 

① B-트리 인덱스 생성

 

② 검색; 행을 찾는 쿼리 실행

SELECT *
FROM 테이블
WHERE 컬럼=데이터;

 

1. 루트 노드에서 시작하여 데이터 값 검색
2. 노드의 키와 비교하여 데이터가 포함된 자식 노드로 이동
3. 리프 노드에 도달할 때까지 이 과정을 반복하여 데이터 값 탐색
4. 해당 키가 발견되면, 테이블의 실제 행으로 이동하여 나머지 열 값 반환

 

③ 삽입; 테이블에 새로운 행 삽입

INSERT INTO employees (컬럼1, 컬럼2, 컬럼3, ···)
VALUES (데이터1, 데이터2, 데이터3, ···);

 

1. 데이터가 삽입될 위치를 찾기 위해 현재 인덱스 구조 검색.
2. 데이터를 삽입할 노드가 가득 차 있으면 노드를 분할하여 공간을 만든다.
3. 새로운 데이터를 적절한 위치에 삽입. 필요한 경우 부모 노드와 조정.

 

④ 삭제; 삭제할 행의 컬럼 값을 사용하여 인덱스 업데이트

DELETE FROM 테이블
WHERE 컬럼=데이터;

 

1. 테이블에서 조건에 부합하는 행 검색.
2. 컬럼에서 찾고자 하는 데이터가 있다면 해당 키를 B-트리 인덱스에서 삭제.
3. 삭제 후, 리프 노드에서 최소 키 수 유지.
   노드가 최소 키 수보다 적어지면, 형제 노드와 병합하거나 균형을 맞추기 위해 키를 빌려온다.

 

 

원래의 데이터 파일 (기본적인 데이터)

인덱스를 만들어야 하는데, 데이터 위치로 물리적 저장 주소를 찾아간다.

 

 

 

 

 

---

 

 

 

 

 

4. 인덱스의 종류

 

1) B-TREE 인덱스

 note 블록을 실행해서 node를 찾아가는 과정.

 

 Branch Block은 Internal Node라고 부른다. Leaf Node라고도 부르는데, 오라클에서는 Leaf Block으로 부른다. 데이터가 추가되면 비율을 맞추기 위해 Branch Block이 늘어날 수도 있다.

 비트맵 인덱스, 비트맵은 중간에 하나만 바뀌어도 전체 다 바꿔줘야 한다. 따라서 변화가 많이 없는 경우에 많이 쓰인다.

 

 

 

OLTP (Online Transaction Processing)

• 쓰기가 비슷하거나 더 많을 수 있다. 수정이 빈번한 경우, 실시간으로 변경이 필요한 경우(변화가 많음)
• 읽기, 쓰기가 다 포함되어 있다. 쓰기 위주이지만 그렇게 비중이 높지 않다.
• 데이터가 수정되었을 때 바로바로 적용되므로 B-TREE 인덱스에 적합하다.

OLAP (Online Analytical Processing)

• Anyalytical: 분석, 읽는 경우가 상대적으로 많다. 기존의 실적 데이터의 판독을 많이 한다.
• 읽기 위주이다. (인덱스 혹은) 데이터의 변화가 적다.
• 비트맵으로 만들어도 변화가 없기 때문에(수정이 적기 때문에) 속도가 증가한다. 따라서 비트맵 인덱스에 적합하다.

 

 

1) UNIQUE INDEX

 

 UNIQUE : 차례대로 정렬. 중복이 발생하면 안된다.

 

CREATE UNIQUE INDEX 인덱스명
ON 테이블명(컬럼명1 [ ASC | DESC ], 컬럼명, ···);

 

 

(2) NON-UNIQUE INDEX

 

 UNIQUE하지 않다. 똑같은 데이터가 있을 수 있어 UNIQUE를 사용하면 인덱스를 처리할 수 없기 때문에 Not null만 한다.

CREATE INDEX 인덱스명
ON 테이블명(컬럼명1 [ ASC | DESC, 컬럼명2, ···);

 

 

(3) Function Based INDEX (FBI - 함수 기반 인덱스)

 

 우리가 인덱스를 사용할 때 보통 인덱스면 데이터로 인덱스를 작성한다. 연산식에 작성한 것은 지정값 또는 범위가 입력된다. 산술 연산식으로 값을 고정한다. 인덱스가 이미 만들어진 상태에서 사용자가 입력한 산술 연산식을 비교했을 때 일치하지 않는다면 인덱스가 작동되지 않는다.

 내용 상으로는 혼란스럽다. 따라서 함수 인덱스를 만들어서 이 상황을 해결한다.

CREATE INDEX 인덱스명
ON 테이블명(연산식);

 

INDEX Suppressing Error란? 인덱스를 만들었지만 인덱스가 작동이 되지 않을 때 인덱스를 억누른 상태를 말한다.

 

 

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

 

 보통 인덱스가 작동될 때 ASC가 작동된다. 최근부터 보이는 것은 정렬 순서가 내림차순이다. 오름차순으로 정렬했을 때 유리한 경우가 있고, 내림차순으로 정렬했을 때 유리한 경우가 있다. 게시판은 DESC를, 순차적으로 사용하는 데이터는 ASC를 사용하는 것이 좋다.

CREATE INDEX 인덱스명
ON 테이블명(컬럼1 DESC);

 

 

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

 

 인덱스 두 개를 연결하는 방법은 두 개를 합쳐 한 개로 만들어서 블럭 단위를 두는 것이다. 빨리 찾을 수 있는 장점이 있다. 전제 조건은 AND 조건으로 몰아 보는 경우가 많아야 효과적이다. 두 번 찾을 걸 한번에 찾을 수 있다.

CREATE INDEX 인덱스명
ON 테이블명(컬럼1, 컬럼2, ···);

 

 

 

 

2) BITMAP INDEX

 

 컴퓨터 상에서 비트(0, 1)를 연속적으로 묶어두면 bitmap이 된다. 원래의 원본으로 저장하는 것보다 비트 단위로 쪼개서 저장했을 때 돌아갈 수 있는 유리한 상황이 있다. 그 경우 비트 단위로 쪼개서 저장하면 처리하는 속도가 빨라질 수 있다.

 

CREATE BITMAP INDEX 인덱스명
ON 테이블명(컬럼1, 컬럼2, ···);

 

 

 새 데이터가 추가될 경우, B-TREE는 더 추가하고 끝난다. 일부분만 수정되고 끝난다. BITMAP INDEX는 추가된 것만 수정되는 것이 아닌 전체를 다시 수정해야 한다. 수정할 일이 있으면 수정할 부분이 많이 발생하게 된다. 따라서 BITMAP INDEX는 수정의 거의 없는 읽기 위주의 Column(컬럼)에 적합하다.

 

 

 

 

 

 

---

 

 

 

 

 

5. 인덱스의 주의사항

 

1) DML에 취약하다

Data Manipelation Language

 

(1) INSERT 작업 시 인덱스에 발생하는 현상

(2) Delete - Index 데이터는 지워지지 않는다.

(3) Update - Delete + Insert 현상이 발생한다.

 

 SELECT는 DQL로 분류하므로 들어가지 않는다. I, U, D가 포함되므로 수정이 일어나지 않지만, SELECT 문은 READ로 수정이 일어나지 않는다. INDEX도 고쳐야 한다. 부하가 걸린다. 심한 경우에 큰 부하가 걸릴 수 있다. 추가되는 경우 때에 따라 균형을 맞춰야 한다. split(분리, 밑으로 확장)과 같은 것들이 부하를 만든다.

 SELECT 문에서는 수정이 불가능하기 때문에 INDEX 수정도 안된다. 수정이 일어날 시 index에 갱신 작업이 뒤따른다. 따라서 시간이 조금 걸릴 수도 있다. 가장 좋은 경우는?

 

 

 

2) 타 SQL 실행에 악영향을 줄 수 있다.

 SQL에는 실행 계획과 여러 index가 있다. 빨리 실행하기 위해 모든 index를 동원해서 생각한다. 하지만 index가 삭제됐을 경우 생각을 따라가는 데 시간이 걸린다. 실행 계획을 잘못 실행할 수 있다. 결과는 다 똑같다. 더 빠른 실행계획을 선택하지 못할 수도 있다. 적응하고 나서 제대로 실행할 수 있다.

 

 

 

 

 

---

 

 

 

 

 

6. 인덱스 관리 방법

 

1) 인덱스 조회하기

어떤 이름으로 컬럼이 저장되고 있다는 것을 알려준다.

SELECT 컬럼1, 컬럼2, ···
FROM 테이블명
WHERE 조건식;

 

 

 

2) 사용 여부 모니터링하기

 인덱스에 데이터가 들어올 때마다 인덱스가 일을 해야 한다. 편의상 데이터를 받아놓고 인덱스를 수정하는 방법도 있다. 위와 같은 경우는 index를 만들어놓고 on/off가 가능하다. 감시, 측정이 불가능하다.

 index를 재사용하면 냅둔다. index 사용하지 않는다면 삭제한다. index가 잘 사용되는지 확인하기 위해 모니터링한다. 위와 같은 경우는 잘 사용하고 있는지 감시, 측정해서 우리가 따질 수 있다.

 

모니터링 시작하기

ALTER INDEX 인덱스명 MONITORING USAGE;

 

모니터링 중단하기

ALTER INDEX 인덱스명 NOMONITORING USAGE;

 

사용 유무 확인하기

SELECT 인덱스명, used
FROM v$object_usage
WHERE 인덱스명='확인할-인덱스명';

 

 

위 내용은 자신이 만든 인덱스만 확인 가능하다.

 

 

 

3) INDEX Rebuild하기

 System이 index를 관리한다. 매번 썼다 지웠다했을 때 벨런스 형태가 흐트러진다. 제일 깨끗하고 효율적인 경우는 최초로 index를 만들었을 때이다. 쓰면서 흐트러진다. DML 작업은 인덱스의 밸런싱 상태를 흩트려서 성능에 나쁜 영향을 준다. 주기적으로 점검해서 밸런싱 상태를 좋게 유지해야 한다.

 Rebuild하면 초심으로 돌아가진다. 깔끔해진다. 처음으로 데이터를 가져오면서 Indexing하는 것과 동일하다.

 

 

[ STEP-UP 1 ] 다양한 인덱스 활용 방법들

 

1) 인덱스를 활용하여 정렬한 효과를 내는 방법

 정렬하는데 시간이 소요되는 작업이 있다. 굳이 정렬하지 않고 데이터가 Index로 존재하는지 체크한다. 존재한다면 따라가며 포팅을 하면 된다는 것이다.

 

2) 인덱스를 활용하여 최소값(MIN) / 최대값(MAX)을 구하는 방법

일반적으로는 데이터를 다 읽어서 최소값 혹은 최대값을 찾은 후 그 값을 출력한다.

최대값 구하기

 

 

[ STEP-UP 2 ] ROUND에 대해 알아봅시다

 

ROWID의 구조

ROWID를 사용하고 싶으면 SELECT 값을 사용한다.

 

SELECT ROWID, 컬럼1, 컬럼2, ···
FROM 테이블
WHERE 조건식;

# ROWID와 선택한 컬럼의 데이터 출력

 

 

 전체적으로 큰 데이터 번호를 붙이고 그곳에 여러 개의 데이터를 하나로 구성하는데, 그 안에는 block으로 또 나뉜다. block을 확대해보면 그 안에 데이터가 여러 건 존재한다. 해당 데이터는 데이터 오브젝트 번호, 파일 번호, BLCK 번호, ROW 번호 (등)이 있다.