[index] 인덱스 개념잡기

 

1. What?

---

- Index는 RDBMS에서 검색속도를 높이기 위한 기술이다.

  DB는 원하는 내용을 찾기 위해 테이블 처음부터 끝까지 풀 스캔한다.

  DB의 수고를 덜기 위해, 해당 테이블의 컬럼을 색인화 한 Index 파일을 만들게 된다.

 

- Index는 어떤 테이블의 주요내용이 어디에 있는지 알려주는 색인정보이다.

  (데이터 - 위치정보) 쌍으로 구성된다.

 

- Index의 데이터는 정렬되어 있다.

 

- 내부적으로 B-tree(Balance Search Tree) 구조에 저장하기 때문에, 완전탐색보다 훨씬 빠르다.

- ORACLE이나 MSSQL은 여러 Tree를 선택할 수 있다.

 

- Index는 컬럼 단위로 생성된다.

- Primary Key, Unique Key 는 자동으로 Unique Index로 만들어진다.

 

- 여러 컬럼으로 이루어진 다중 Index도 있다. 이를 사용하면 첫 컬럼 값이 같은 행도 구분할 수 있다.

  주로 AND 연산자에 의해 자주 질의되는 컬럼들을 지정한다.

- 연산자를 이용한 Index도 있다.

CREATE INDEX name_table
ON table SUBSTR(name, 1, 1)

SELECT name
FROM table
WHERE SUBSTR(name, 1, 1) = 'A'

 

- 데이터의 중복도가 높은 컬럼은 효과가 미미하다. (ex : 성별)

 

 

2. Why?

---

- DB는 테이블에 데이터를 삽입할 때, rowid라는 고유한 주소값을 생성한다.

  rowid는 데이터가 위치한 하드디스크의 주소값이다.

  이 rowid를 통한 검색이 db에서 가장 빠른 검색방법이다.

 

- Index를 만드는 이유는 이 rowid로 데이터 탐색을 할 수 있게 유도하기 때문이다.

  Index를 만들어 놓으면 내부적으로 알아서 rowid를 이용하여, 검색이 빠르다.

 

 

3. Positive?

---

- 테이블의 검색, 정렬, 그룹화 속도를 향상시킨다.

 

 

 

4. Nagative?

---

- Index된 컬럼을 이용한 UPDATE, INSERT, DELETE문 속도를 저하시킨다.

- Index를 저장하기 위해 DB 용량이 필요하다.

- 데이터 변경 작업이 자주 일어나는 테이블은 Index 재작성이 필요하여 성능을 저하시킨다.

 

 

 

5. How?

---

- 주로 WHERE절과 JOIN 조건으로 들어가는 컬럼을 Index로 만든다.

- 아래는 여러 분류에 따른 생성법이다.

 

1) 컬럼 값의 유일성에 따른 분류

① Unique Index : 값이 중복되지 않는 Index

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

 

② Non Unique Index : 값이 중복될 수 있는 Index

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

 

2) Index를 구성하는 컬럼수에 따른 분류

① 단일 Index : 컬럼수가 1개인 Index

 

② 복합 Index : 컬럼수가 2개 이상인 Index

 

 

3) Index의 물리적 구성 방식에 따른 분류

① B* tree Index

- B-Tree는 트리 구조의 최상위에 하나의 루트 노드가 존재하고 그 하위에 자식 노드가 붙어 있는 형태이다.

- 트리 구조의 가장 하위에 있는 노드 = 리프 노드

  루트 노드도 아니고 리프 노드도 아닌 중간 노드 = 브랜치 노드

- DB에서 Index와 실제 데이터는 따로 관리되는데, 인덱스의 리프 노드는 항상 실제 데이터 레코드의 주소 값을 가지고 있다.

② BITMAP Index

- 비트를 직접 관리하므로 저장공간이 크게 감소한다.

- 대량의 데이터를 한꺼번에 입력한 뒤 주로 분석이나 통계 정보를 출력할 때 많이 사용한다.

 

③ Clustered Index : 추가 필요

 

 

6. Example

예제 1)

- 아래 쿼리에서 사용된 컬럼이 인덱스로 지정된 경우와 그렇지 않은 경우의 예시를 살펴본다.

SELECT*
FROM table
WHERE infoNo = 1024

- 데이터 파일의 블록이 10만개가 있다.

  i) 서버 프로세스가 구문파싱 과정을 마친 후 DB Buffer 캐시에 infoNo가 1024인 정보가 있는지 확인한다.

  ii) 해당 정보가 캐시에 없다면, 디스크 파일에서 해당 정보를 가진 블럭을 찾아 DB Buffer 캐시로 가져온 뒤 사용자에게 보여준다.

 

- 이때,

  i) Index가 없는 경우 : 정보가 디스크의 어떤 블럭에 있는지 모르기 때문에, 10만개 전부 DB Buffer 캐시로 복사 >> Full Scan

  ii) Index가 있는 경우 : WHERE절 조건인 컬럼이 Index의 Key로 생성되어 있는지 확인

                                            > Index에서 정보가 가진 ROWID를 확인

                                            > 해당 ROWID에 있는 블럭으로 바로 찾아가서 DB Buffer 캐시로 복사 >> 해당 블록만 Scan

 

예제 2)

- DB는 옵티마이저를 이용하여 Index를 사용할지 말지를 결정한다.

  따라서, Index를 만들어둔 컬럼을 이용하여 검색한다고 무조건 Index가 사용되는 것은 아니다.

*옵티마이저(Optimizer)?

SQL을 가장 빠르고 효율적으로 수행할 수 있는 최저비용의 처리경로를 생성하는 DBMS 내부 엔진이다.

옵티마이저가 생성한 SQL 처리경로를 실행계획이라고 한다.

(사용자가 쿼리 실행  >  실행계획 탐색  >  데이터 딕셔너리 내 통계정보를 통해 각 실행계획의 예상비용 산정  >  최저비용의 한 계획 선택 후 실행)

 

- 아래는 B* tree 구조의 Index의 사용할 수 없는 경우이다.

① 컬럼의 내외부 변형

② is null, is not null을 사용한 비교

③ 부정형 비교

④ like 검색 시 변수 앞 '%' 사용

⑤ 여러 컬럼에 대한 or 조건 사용

 

① 컬럼의 내외부 변형

[사용 가능]

SELECT a FROM table
WHERE age > 300 / 30

SELECT a FROM table
WHERE birthday = to_date('920101', 'ddmmyy')

[사용 불가]

SELECT a FROM table
WHERE age * 30 > 300

SELECT a FROM table
WHERE to_char(birthday, 'yymmdd') = '920101'

 

② is null, is not null을 사용한 비교

[사용 가능]

SELECT a
FROM table
WHERE name > ''

SELECT a
FROM table
WHERE name >= 0

[사용 불가]

SELECT a
FROM table
WHERE name IS NULL

SELECT a
FROM table
WHERE name IS NOT NULL      >> 버전에 따라 다름

 

③ 부정형 비교

[사용 가능]

SELECT a
FROM table
WHERE age < 20 OR age > 20

[사용 불가]

SELECT a
FROM table
WHERE age != 20

 

④ like 검색 시 변수 앞 '%' 사용

[사용 가능]

SELECT *
FROM table
WHERE name LIKE 'M%'

[사용 불가]

SELECT *
FROM table
WHERE name LIKE '%M%'

 

 

 

 

*참고 자료

- https://lalwr.blogspot.com/2016/02/db-index.html

DB Index 란?

- https://rongscodinghistory.tistory.com/113

ORACLE 인덱스(Index) 개념/종류/주의사항/활용,관리