인덱싱

[10분 테코톡] 🍫 찰리의 인덱싱을 들으며 정리한 글입니다.

Index란?

보통 배열의 위치를 인덱스라고 한다. 
결국 데이터가 있는 위치를 특징할 수 있는 정보 !

단어의 뜻 

색인 - 어떤 것을 뒤져서 찾아내거나 필요한 정보를 밝힘 

데이터베이스의 Index 사용 목적 

대용량 데이터에서 원하는 데이터를 빠르게 조회하기 위해서이다.
결국 SELECT 쿼리의 대용량 데이터 조회 시 조회문 조회 속도를 향상시키기 위함이다. 
하지만 INSERT, UPDATE, DELETE 시 성능을 조금 희생해야 한다. 

혹은 대용량이 아니면 조회 시 성능 향상이 없나 ?

있긴 하지만 큰 효과는 볼 수 없을 것이다. 

Index의 작성 / 삭제

Index도 하나의 데이터베이스 객체이다. 
Oracle, DB2 등에서는 스키마 객체, 
MySQL, SQL Server 등에서는 테이블 내의 객체이다. 
즉 Index도 저장 공간이 필요한 객체이다. 

작성

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

컬럼명을 여러개 적어 다중 컬럼 인덱스를 만들 수도 있다. 
다중 컬럼 인덱스는 순서에 따라 효율이 달라질 수도 있다.

삭제

DROP INDEX 인덱스명 ON 테이블명;

Index 검색에 사용하는 알고리즘

Full Table Scan 

데이터가 1억건이 있다면 1억번의 값을 비교한다. 
인덱스가 걸려있지 않다면 기본적으로 풀 테이블 스캔을 한다. 
전체 검색에 있어 효율성이 떨어진다. 

B-Tree

인덱스에서 가장 일반적으로 사용하는 알고리즘이다. 

이진 탐색은 정렬 후 중간 값을 구한 뒤 왼쪽은 작은 값, 오른쪽은 큰값으로 해서 값을 찾아나간다. 

이진 트리

이진 트리는 루트와 노드가 있으며 마지막에 있는 노드가 리프노드이다. 
이진트리는 한쪽으로만 치우칠 수 있는 단점이 있다. 

가장 왼쪽 아래 노드로 갈 때 검색 속도가 오래 걸리게 된다.
이런 경우가 발생할 수 있기 때문에 B-Tree가 탄생했다. 

B-Tree

노드를 2개 이상 가질 수도 있다. 
높이가 같다는 것이 가장 큰 특징이다.

Clustered Index VS Non-Clustered Index

Clustered Index

• 무리를 이룬 Index
• 군집 인덱스

Non-Clustered Index

• 무리를 이루지 않은 Index
• 비 군집 인덱스 

Clustered Index 

• pk를 생성하면 자동으로 해당 인덱스가 생성된다. (밀접한 관련)
• id 컬럼에는 이미 인덱스가 생성되어 있다. 

특징

• 테이블 당 1개만 존재
• PK 제약조건으로 컬럼을 생성하면 자동 생성
• 인덱스에 데이터 페이지가 함께 존재
• B-Tree의 리프 페이지 == 데이터 페이지
• 데이터가 정렬된 상태

예시

여기서 DDD라는 데이터를 넣는다고 가정해보자. 
(여기서 클러스터 인덱스는 데이터가 정렬이 되어 있어야 함)
DDD는 CCC 뒤에 들어가야하지만 해당 페이지에는 더이상 공간이 없다. 

이럴 때 페이지 분할이 일어난다. 
페이지 분할은 DB가 상당히 힘들어 하는 작업이라 (UPDATE, DELETE 등에 손해를 봄)

여기서 KKK라는 데이터를 넣는다고 가정하자. 
역시 LLL 뒤에는 공간이 없기 때문에 페이지 분할이 일어난다.

만약 LLL을 루트 페이지에 넣는다면 ? 
마찬가지로 공간이 없기 때문에 페이지 분할이 일어난다. 
또 그 위에 새로운 루트 페이지가 만들어진다. 

브랜치 페이지 : 루트 페이지와 리프 페이지를 연결해준다. 덕분에 높이를 유지하는 트리구조를 만들 수 있다. 

루트 페이지에서는 페이지 번호를 가리키고, 브랜치 페이지에서는 데이터 페이지를 가리킨다. 
만약 G라는 데이터를 찾는다고 가정을 해보자.

풀페이지 스캔에서는 a -b - c - d - g, 총 5번의 데이터 조회가 일어난다.
하지만 현재 G라는 데이터를 찾기 위해서는 루트, 브랜치 이동을 하면 바로 찾을 수 있다. 
대용량 데이터가 아니면 큰 효율은 없지만 조회수가 줄어듦을 볼 수 있다. 

Non-Clustered Index

특징

• Secondary Index(보조 인덱스) 라고도 한다.
• 테이블에 여러개가 존재할 수 있음
• Unique 제약 조건으로 컬럼을 생성하면 자동으로 생성됨
• 인덱스와 데이터 페이지가 따로 존재 (데이터와 무리를 이루지 않음)
•  리프 페이지에서 데이터가 있는 곳의 주소를 가집
• 데이터 페이지에 데이터가 정렬되지 않아도 됨
• Clustered Index와 비교해서 조회 속도가 약간 느림 (리프페이지에 데이터가 바로 존재하지 않기 때문)
• Clustered Index보다 Insert/ Update /Delete 시 부하가 적음

리프 페이지는 데이터를 바로 가지고 있지 않고, 데이터가 있는 페이지 주소를 가지고 있다. 
또한 이름순으로도 정렬이 되어 있지 않다. (닉네임에 Non - Clustered Index를 걸어준 것임)
인덱스 데이터는 정렬 되어 있지만, 데이터 페이지에는 데이터가 정렬되어 있지 않다. 

여기서 `검프`라는 데이터를 추가한다.

해당 데이터는 리프 페이지의 가장 앞단에 위치하게 되고, 루트 페이지에도 변경이 일어난다. 
이런식으로 데이터 페이지는 변경되지 않고 리프 페이지만 변동이 일어나기 때문에 Insert 시 더 성능이 좋다. 
하지만 조회시 성능 저하가 발생한다. 
현재는 리프 페이지에서 데이터 페이지의 주소를 가지기 때문에 한번 더 리프에서 데이터 페이지로 이동이 필요하다.
또한 범위 검색에서 미키와 아마찌를 조회하면 각각 다른 페이지를 조회해와야 하므로 이런 경우 성능이 떨어진다. 

Clustered Index + Non-Clustered Index

위에가 Non-Clustered Index, 아래가 Clustered Index.
여기서 Non-Clustered Index의 데이터 페이지의 주소값이 들어있던 리프페이지가,
Non-Clustered Index가 걸린 컬럼으로 변경이 되었다. 
이럴경우 조회에서 성능이 떨어질 수 있다. (지그를 검색 시 리프 페이지를 한 번 더 타야한다.)

삽입의 경우

이렇게 구성한 이유는 클러스터 인덱스는 데이터가 삽입되었을 때 정렬이 필요하다. 
이럴 경우 주소값이 변경될 수 있는데, 데이터 페이지 주소값에 변경이 일어나면
논 클러스터 인덱스에서는 리프 페이지의 데이터만 변경하면 된다.

삭제의 경우

만약 논 클러스터 인덱스와 클러스터 인덱스를 동시에 삭제한다고 가정하자. 
클러스터 인덱스를 먼저 제거하면 논 클러스터 인덱스가 가지고 있는 주소값이 데이터 페이지의 주소값으로 변경되어야 한다. 
때문에 데이터 페이지의 주소값을 바꾸는 작업이 필요하다. 
때문에 논 클러스터 인덱스를 삭제하고 클러스터 인덱스를 삭제하는 것이 더 좋다.

실습

• emp_no_index : emo_no 컬럼에 인덱스가 존재하지 않음
• emp_clustered_index : emp_no 컬럼에 클러스터 인덱스만 존재
• emp_non_clustered_index : emp_no 컬럼에 논 클러스터 인덱스만 존재

인덱스가 존재하지 않는 테이블 -> 풀테이블 스캔

Clustered Index가 있는 테이블

Non-Clustered Index가 있는 테이블

카디널리티

어떤 컬럼에 인덱스를 생성해야하는가 -> 중복된 수치가 낮은 것을 선택하라!
중복된 수치가 낮다는 것은 원소의 종류가 많다는 것이다. 
카디널리티가 높다는 것은 중복된 수치가 높다는 것이다. 

이 경우 emp_no인 pk가 카디널리티가 가장 높다. 

인덱스 사용 시 주의 사항 

• 컬럼에 생성된 인덱스가 사용되는 기준은 Where 절에서 해당 컬럼을 사용했는가 아닌가이다. 
• 사용하지 않는 인덱스는 삭제해야한다. (Insert, Update, Delete 성능 저하를 방지)
• 외래키를 지정한 열에는 자동으로 외래 키 인덱스가 생성된다. 
• Where 절에 사용되는 컬럼이라도 자주 사용되어야 인덱스의 가치가 있다. 
• Where 절의 '컬럼'에 연산을 사용한 경우 인덱스를 사용하지 않는다. 
  • select * from user where balance * 10 < 100;
  • 대신 아래와 같이 사용하면 인덱스를 사용할 수 있다. 
  • select * from user where balance < 100 * 10;

 

참고 자료

• [10분 테코톡] 🍫 찰리의 인덱싱