본문 바로가기

신입 개발자 면접 기초

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

반응형

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

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

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

1
2
3
4
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)

대신 대입하는 값에 사용

1
2
3
4
5
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
cs

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

1
2
3
4
5
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'
cs

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

1
2
3
4
5
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
cs

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

1
2
3
4
5
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);
//두 가지 해법
cs

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

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

6)OR조건 사용 자제

1
2
3
4
5
6
7
8
9
10
11
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'
cs


* 참고 사이트

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

반응형
  • Favicon of https://jeong-pro.tistory.com BlogIcon JEONG_AMATEUR 2018.09.10 12:11 신고

    풀스택개발자가 되기 위해 알아야 할 것, 가이드
    https://medium.com/coderbyte/a-guide-to-becoming-a-full-stack-developer-in-2017-5c3c08a1600c

  • 하이욤 2018.12.17 11:45

    질문이 있습니다. 학교에서 배울때는 B tree가 아니라 B+ tree 를 더 많이 사용한다고 들었는데, B tree가 맞습니까?

    여담으로 올려주신 모든 링크가 클릭이 안됩니다. 복사도 안되구요.... 고쳐주실수 있으면 감사합니다.

    • Favicon of https://jeong-pro.tistory.com BlogIcon JEONG_AMATEUR 2018.12.17 17:52 신고

      질문 감사드립니다.
      실제 통계로 B tree가 많이 쓰이냐 B+tree가 많이 쓰이냐는 모릅니다.
      다만 B tree가 "일반적이다"는 내용은 타 블로그에서도 자주 언급되는 부분입니다.
      참고부탁드립니다.

      참고사이트를 링크로 넣는게 맞는것 같습니다.
      앞으로 새로 쓰는 포스트에는 링크로 적용하도록 하겠습니다.
      추가로 같이 공부하시는 이용자분들을 위해서 복사금지를 풀어야 겠다는 생각을 예전부터 해왔습니다.
      (소스 코드 적용 등...)
      다만, 포스트를 그대로 복사해서 자기가 쓴 글 인양하는 사람들이 생길까봐 풀지 않고 있었습니다.
      그렇지만 이번에 의견을 적극 반영해서 복사금지를 풀겠습니다.
      문제가 생기면 다시 잠글 수 있습니다.
      의견 감사합니다.

    • 하이욤 2018.12.17 19:55

      답변 감사합니다. ^^

  • 박수빈 2019.07.02 18:17

    그림에 나온 leaf node가 링크드리스트로 되어 있는 트리가 B+ Tree로 알고 있는데
    B Tree는 최소 key를 갖는 노드를 찾은 후 중위 순회를 통하여 Range scan하는 것이 아닌가요??

    • Favicon of https://jeong-pro.tistory.com BlogIcon JEONG_AMATEUR 2019.07.03 14:20 신고

      말씀해주신 부분이 맞는 것 같습니다.
      dbguide.net은 자격증 시험도 있는 기관이라 믿고 인용했는데, 다른 블로그들로 알아보니 대부분 B+tree가 링크드리스트로 다른 노드와 연결되어있는 것을 확인할 수 있었습니다.

      추가로 Btree에서 범위 데이터를 찾을 때 말씀하신대로 1차적인 최소 인덱스 탐색 후, 2차적으로 중위순회를 통해 가져오는 것도 맞는 것 같습니다.

      정정, 공부에 도움을 주셔서 감사합니다.

태그