DB 인덱스의 구조는 어떻게 되어있나요? 인덱스는 언제 적용해야하나요?

데이터베이스 인덱스(Database Index)

데이터베이스의 인덱스, 개발을 하면서 상당히 많이 들었다.

RDBMS에서 대용량의 데이터(레코드)가 있을 때, 특정 데이터를 검색하기 위해서 테이블의 레코드를 full scan하는 것이 아니라, 인덱스가 적용된 컬럼의 테이블(컬럼, 인덱스주소)을 따로 파일로 저장해놓고 그것을 검색해서 검색 효율을 높이는 방법이다.

SELECT /*+INDEX(EMP EMPNO_INDEX) */
EMPNO, ENAME
FROM EMP
WHERE DEPTNO=10

 /*+HINT~~~~ */   이런식으로 사용하는 것까지는 누구나 안다. (HINT의 종류는 검색해보면 많음)

그러나 '왜 이렇게 일어나지?', '어떻게 동작하지?' 이런 고민을 해보지 않고 + 직접 '체득'하지 않은 사람들이 많을 것이다. (필자 포함)

인덱스는 면접에서 자주 물어보는 것중에 하나다.

추측으로는 면접+실무에서 포인트는 인덱스에 대해서 정확하게 알고 있어야만 적재적소에 적용할 수 있기 때문에 꼼꼼하게 정리하고 이해했는지를 물어보는 것 같다.

---

인덱스는 범위 스캔(Range Scan)을 한다

인덱스는 키 컬럼순으로 정렬되어 있기때문에 특정값을 찾다가 해당 범위를 넘어서는 값을 만나면 멈춘다. (=Range Scan)

인덱스에 가장 많이 사용되는 구조 = B-tree

인덱스를 저장하는 블럭들이 트리구조를 이루고 있다.

root block과 branch block, leaf block이 있고 B-tree는 기본적으로 leaf block의 깊이가 모두 동일하게 균형(Balanced)이 잡혀있다.

또한 각 노드에 값도 가지고 있다. (B+tree는 값은 없고 탐색을 위한 인덱스 정보만 있음)

<출처 : http://www.dbguide.net/db.db?cmd=view&boardUid=148209&boardConfigUid=9&boardIdx=136&boardStep=1>

leaf블록간의 양방향 링크가 걸려있어서 내림차순, 오름차순 검색이 쉽게 가능한 것은 B+tree인데, 해당 출처에서 B트리를 왜 이렇게 표현했는지는 알 수 없다. (잘못된 부분있으면 지적 부탁드립니다.)

탐색하는 방법에서 예를들면, between 10 and 20 이라는 조건이 있었을 때는 최소값인 10을 기준으로 찾은 후에 해당 노드에서 값을 찾고(ex. 15, 17, ...) 만약 20이 해당 노드에 없으면 부모노드로 올라가서 20이 있는지도 검사하게 된다.

20보다 큰 인덱스 값이 있다면 범위 검색을 멈춘다.

20보다는 작은 값을 찾길 원하는데 B-tree가 정렬이 된 상태로 유지하기 때문에 그 이상의 값은 무의미 하다.

* B-tree 인덱스 탐색 과정

찾고자 하는 값이 branch block에서 가장 왼쪽 값보다 작거나 같을 때는 왼쪽 포인터, branch block 사이에 있으면 사이 포인터, branch block에서 가장 큰 값보다 크면 오른쪽 포인터로 찾아간다.

이 과정을 통해 leaf block을 찾고 그 안에서 찾고자 하는 값이 있으면 성공이고 없으면 실패다.

인덱스는 언제 적용해야 좋을까?

* 인덱스는 select문의 where, join에서 좋은 성능을 발휘한다. 대신 insert, update, delete문에서 성능이 떨어진다.

=> insert문의 경우 새로운 데이터를 삽입하면서 테이블뿐만 아니라 인덱스 테이블에도 생성을 해줘야 하며, 만약 인덱스의 leaf block이 꽉찼는데(정렬되어있음) 그 사이에 값이 들어온다면 다른 block으로 밀려나야할 데이터가 생기고, 이렇게되면 새로운 블럭에 key를 옮길 때 모든 과정이 redo에 기록되는 수고가 생김

delete문의 경우 기존 테이블에서는 그냥 레코드를 삭제하고 그 공간을 다른 레코드가 사용할 수 있지만 인덱스 테이블은 사용 안함 표시만 하고 자리를 그대로 차지하기 때문에 유념해야함.

update문은 delete하고 insert하는 식으로 처리하기 때문에 insert, delete문이 인덱스에 동시에 작용하기 때문에 부하가 많아짐.

: 결론은 검색(SELECT)이 많고 INSERT, UPDATE, DELETE문이 적게 일어나는 테이블에서 인덱스를 사용하면 좋음.

* 인덱스 컬럼의 분포도가 좋아야(골고루 유일성을 갖게 분포) 한다.

=> 성별같이 남,여로 나눌 수 있는 컬럼에는 의미가 없고 사용하는 경우는 bitmap index를 적용할 때가 있음. 대용량 데이터 분석할 때 특정 성별만 뽑는다든지 할 때 의미가 있다.

만약 남,여뿐만 아니라 추가로 컬럼의 종류가 생기면 bitmap index의 모든 맵을 수정해야하는 큰! 문제가 생김

* 기타 인덱스 적용할 때 유의사항

- 인덱스 key(컬럼)은 가능하면 작게 설계

- 단일인덱스 여러개를 사용해야할 때는 다중컬럼 인덱스 생성을 고려

- 되도록 동등비교(=)사용

---

1) 인덱스 컬럼 변경하면 안됨(함수, 수식등 사용X)

대신 대입하는 값에 사용

SELECT column_name FROM table_name WHERE TO_CHAR(column_name, 'YYYYMMDD') = '20180101';
=> SELECT column_name FROM table_name WHERE column_name = TO_DATE('20180101','YYYYMMDD');
 
SELECT column_name FROM table_name WHERE column_name * 100 > 10000;
=> SELECT column_name FROM table_name WHERE column_name > 10000 / 100; 

2) 내부 형변환이 없게 타입을 맞춰줘야 함 (*****)

SELECT column_name FROM table_name WHERE column_name = '20180101'; // column이 DATE타입
=> SELECT column_name FROM table_name WHERE column_name = TO_DATE('20180101','YYYYMMDD');
 
SELECT column_name FROM table_name WHERE column_name = 100; // column이 문자타입
=> SELECT column_name FROM table_name WHERE column_name = '100'; 

3) 조건절에 NULL, NOT NULL 사용 자제

SELECT column_name FROM table_name WHERE column_name IS NULL;
=> SELECT column_name FROM table_name WHERE column_name > '';
 
SELECT column_name FROM table_name WHERE column_name IS NOT NULL;
=> SELECT column_name FROM table_name WHERE column_name >= 0; 

4) !=, NOT 부정형 조건 사용 자제

SELECT column_name FROM table_name WHERE column_name != 30;
=> SELECT column_name FROM table_name WHERE column_name < 30 AND column_name > 30;
=> SELECT column_name FROM table_name WHERE NOT EXISTS
(SELECT column_name FROM table_name WHERE column_name = 30);
//두 가지 해법

5) LIKE연산자를 사용 할때 앞에 '%' 넣지 않기

SELECT column_name FROM table_name WHERE column_name LIKE '%문자%';
=> SELECT column_name FROM table_name WHERE column_name LIKE '문자%';

6)OR조건 사용 자제

SELECT column_name
FROM tbl1 t1, tbl2 t2
WHERE (t1.col1 = t2.col1 OR t1.col2 = t2.col2) AND t1.col3 = 'value';
=>
SELECT column_name
FROM tbl1 t1, tbl2 t2
WHERE t1.col1 = t2.col1 AND t1.col3 = 'value'
UNION ALL
SELECT column_name
FROM tbl1 t1, tbl2 t2
WHERE t1.col2 = t2.col2 AND t1.col3 = 'value'

* 참고 사이트

http://www.gurubee.net/lecture/1108

https://www.ibm.com/support/knowledgecenter/ko/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/c0005300.html

http://www.dbguide.net/db.db?cmd=view&boardUid=148209&boardConfigUid=9&boardIdx=136&boardStep=1

http://isstory83.tistory.com/131