DB 인덱스 기초지식

* 사내 위키에 정리했던 발표 자료를 블로그로 옮겨왔습니다. 

* 발표일: 2020.07.21

인덱스란 (기본 동작 원리)

인덱스를 비유하자면.. 책의 목차!

e.g. 100000페이지 책에서 'DEADLOCK'을 찾으려 한다. 어떻게 찾을까요?

-> 특정 카테고리에 속해있을 것. 해당 카테고리 찾자

인덱스의 목적

인덱스 없이 테이블 풀스캔시 비용이 커집니다.

-> WHERE 구문과 일치하는 열을 빨리 찾기

-> 모든 데이터 조사 필요 없이 중간에서 검색 위치를 빠르게 잡아 내기

 

인덱스, 어떻게 빠르게 동작하나요?

인덱스 탐색 원리 중 하나: B 트리! (Balanced Tree)

중간 값을 기준으로 좌우 작은 값, 큰 값으로 나뉩니다.

인덱스 사용 언제가 적합할까?

- 데이터 양이 많고 검색이 변경 (Update, Delete, Insert)보다 빈번한 경우
  (재정렬에 대한 부담)

- 인덱스를 걸고자 하는 필드의 값이 다양한 값을 가지는 경우  (예 : 주민번호 (870612- XXXX) )

= Cardinality가 높은 경우.=  중복도가 낮은 경우

e.g. 이름은 주민등록 번호에 대해 카디널러티가 낮다.

구체적으로 어떤 컬럼에 인덱스를 걸면 될까요?

• WHERE절 뒤에 자주 사용되는 컬럼
• ORDER BY에 자주사용되는 컬럼
• JOIN으로 자주사용되는 컬럼
• NOT연산자는 긍정문으로 바꿔서 쓴다
• 삽입과 삭제가 빈번한 컬럼은 인덱스로 좋지않다
  (인덱스를 만드는데 사용되는 공간과, 정렬하는데 걸리는 시간등이 추가적으로 필요하다.)

출처 : https://nive.tistory.com/185

인덱스 사용 예시

Java를 공부하고 싶은 비상이는 도서관 DB에게 부탁합니다.
"JAVA에 관련된 책을 모두 찾아주세요"

DB는 아래의 책 만권을 모두 뒤집니다.

SELECT name, location FROM book_store where category = "Java"

책 목록

rowidnamecategorylocation

1	let’s start java	java	A
2	python basic	python	K
3	js for seinor	javascript	B
4	let’s start java	java	C
…	…	…	…
4222	java? java!	java	Z
4223	pythonic thinking	python	A
…	…	…	…
9999	javavara	java	K
10000	i love C++	C++	N

디비가 가져온 결과입니다.

namelocation

let’s start java	A
let’s start java	C
java? java!	Z
javavara	K

비상이릉 위해 Full scan을 하는 DB. 고생이 많습니다.

열일하는 DB를 위해 인덱스(=목차)를 만들어줍시다.

DB는 생각합니다.

"좋은 기준이란 뭘까?
아! Java에 관련된 거니까 category를 기준으로 정렬하자.
그리고 내가 쉽게 찾아갈 수 있게 rowid (=주소값)를 같이 넣어주자."

 

categoryid

…	…
C++	10000
…	…
java	1
java	4
java	4222
java	9999
javascript	3
…	…
python	2
python	4223
…	…

인덱스는 문자열 순서대로 정렬되어있기 때문에,

‘java’ 라는 문자열을 계속 검색하다가 ‘javascript’ 라는 문자열을 만나는 순간 이제 더이상 ‘java’ 라는 문자열을 존재하지 않는다고 단정짓고 탐색을 종료할 수 있습니다.

게다가 내부적으로 데이터를 B-Tree라는 구조에 저장하기 때문에, ‘java’라는 문자열을 찾아낼 때 맨 처음부터 순차적으로 조회하는 것 보다 훨씬 빠릅니다.

이제 DB는 아래 스킬을 익혔습니다.

SELECT name, location FROM book_store WHERE rowid IN (1, 4, 4222, 9999)

근데, 우린 rowid를 매번 눈으로 체크하고 싶지 않습니다.
그냥 DB가 알아서 찾도록 하고 싶어요. -> 인덱스를 겁시다.

CREATE INDEX index_category ON book_store(category)

이 후 아래와 같이 사용하면 됩니다.

SELECT name, location FROM book_store WHERE category = 'java'

참고 : https://itholic.github.io/database-index/

인덱스 종류 : Clustered vs Non-clustered index

 

cluster 단어의 뜻

• cluster : 군집
• clustered 군집된
• clustered index: 군집화된 인덱스

 

Cluster와 non-cluster의 저장 방식 차이점

클러스터 인덱스는 해당 테이블을 row들을 인덱스의 순서에 따라 물리적으로 재정렬. 

 
넌클러스터 인덱스는 인덱스 페이지를 별도로 생성. 주소값 만듦. 이를 이용해서 데이터에 접근하는 방식이다.

Cluster와 non-cluster의 동작 방식 차이점

클러스터 인덱스는 데이터 위치를 바로 알기때문에 그 데이터로 바로 접근할 수 있고,
 
넌클러스터 인덱스는 인덱스 페이지를 한번 거쳐서 데이터에 접근하는 방식이다.

 

Cluster와 non-cluster의 최대 개수

클러스터 인덱스 : 물리적으로 재정렬하므로 딱 1개만 생성 가능. 2개가 가능할경우 데이터 정렬이 꼬이므로 1개만!!
 
넌클러스터 인덱스 : 별도의 페이지 생성. 따라서 n(n>1)개. (최대 개수는 ..: 찾아봐주세요! :) )

예시. 숫자 8을 검색하는 방법

Cluster와 non-cluster의 성능비교

클러스터 인덱스 : 범위 쿼리, 포인트 쿼리 모두 성능 발휘
 
넌클러스터 인덱스 : 포인트쿼리는 성능을 발휘하나, 범위쿼리는 성능을 보장할 수 없다.

*범위 쿼리(Range Query) : 조회되는 값이 여러 행

* 포인트 쿼리(Point Query): 조회되는 값이 한 행인 쿼리

어떤 인덱스가 좋은 인덱스일까?

-  분별력(Cardinality)가 높은 컬럼 / where 절에서 equal 값으로 자주 쓰이는 컬럼

예. USER_MST_ST의 Status(삭제여부. e.g. Y/N), USER_GRADE(정,준,휴 회원값  e.g. 001,002,003) 보다는 USER_ID(e.g. AAA0292) 혹은 USER_IDX (e.g.6192)값

-  복합 인덱스 (여러 컬럼으로 조합된 인덱스) 일 경우, 분별력이 높은 컬럼이 앞으로 올 것!!!

인덱스의 잘못된 사용 예제

4.1 인덱스 컬럼을 가공

e.g. WHERE SUBSTR(ORDER_NO, 1,4) = '2019'
    -> WHERE OREDER_NO LIKE '2019%'

4.2 인덱스 컬럼의 묵시적 형 변환 (같은 타입으로 비교할 것)

e.g.

SQL> desc myemp1
 이름                                                        유형
 ----------------------------------------- -------- ---------------
 EMPNO                                    NOT NULL NUMBER
 ENAME                                              VARCHAR2(100)
 DEPTNO                                            VARCHAR2(1)
 ADDR                                              VARCHAR2(100)
 SAL                                                NUMBER
 SUNGBYUL                                          VARCHAR2(1)

테이블 상태: myemp1 데이터건수 2000만건이며 empno 칼럼에는 primary key 인덱스, deptno 칼럼에도 인덱스가 생성되어 있다

올바른 실행
 SQL> select count(*) from myemp1
  2  where deptno = '3';

  COUNT(*)
----------
  5000001

경  과: 00:00:00.23

잘못된 실행
SQL> select count(*) from myemp1
  2  where deptno = 3;   칼럼의 데이터 타입이 달라 숫자를 기준으로 아래처럼 내부적으로 변환된다.

SQL> select count(*) from myemp1
  2  where to_number(deptno) = 3;

  COUNT(*)
----------
  5000001
경  과: 00:00:20.59

출처: http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=259

4.3 인덱스 컬럼 부정형 비교

e.g. WHERE MEMBER_TYPE != '10'
 > WHERE MEMBER_TYPE IN = ('20', '30')

4.4 LIKE 연산자 사용시 %가 앞에 위치  (NO! NO!)

 

4.5 OR 조건 사용 > UNION ALL로 대체