DB2

기술노트

인덱스의 필요성

인덱스는 데이터를 빠르게 찾을 수 있는 하나의 장치이다. 예를 들어 책의 마지막 장에 있는 찾아보기를 생각하면 된다. 책의 본문이 있고 그 본문 안에 내가 찾고자 하는 '항목'을 찾아보기를 통해 빠르게 찾을 수 있다. 이와 마찬가지로 인덱스를 설정하면 테이블 안에 내가 찾고자 하는 데이터를 빠르게 찾을 수 있다.

B-트리

인덱스는 보통 B-트리라는 자료 구조로 이루어져 있다. 이는 루트 노드, 리프 노드, 그리고 루트 노드와 리프 노드 사이에 있는 브랜치 노드로 나뉜다.

인덱스가 효율적인 이유와 대수확장성 인덱스가 효율적인 이유는 효율적인 단계를 거쳐 모든 요소에 접근할 수 있는 균형 잡힌 트리 구조와 트리 구조와 트리 깊이의 대수확장성 때문이다.

대수확장성이란 트리 깊이가 리프 노드 수에 비해 매우 느리게 성정하는 것을 의미한다. 기본적으로 인덱스가 한 깊이씩 증가할 때마다 최대 인덱스 항목의 수는 4배씩 증가한다.

|트리 깊이|인덱스 항목의 수 |:|:| |3|64| |4|256| |5|1,024| |6|4,096| |7|16,384| |8|65,536| |9|262,144| |10|1,048,576|

앞의 표처럼 트리 깊이는 열 개짜리로, 100만 개의 레코드를 검색할 수 있다는 의미이다. 참고로 실제 인덱스는 이것보다 훨씬 더 효율적이며, 그렇기 때문에 인덱스가 효율적이라고 볼 수 있다.

인덱스 만드는 방법

MySQL MySQL의 경우 클러스터 형 인덱스와 세컨더리 인덱스가 있으며, 클러스터형 인덱스는 테이블당 하나를 설정할 수 있다. primary key 옵션으로 기본키를 만들면 클러스터형 인덱스를 생성할 수 있고, 기본키로 만들지 않고 unique not null 옵션을 붙이면 클러스터형 인덱스로 만들 수 있다.

create index... 명령어를 기반으로 세컨더리 인덱스를 만들 수 있다. 하나의 인덱스만 생성할 것이라면 클러스터형 인덱스를 만드는 것이 세컨더리 인덱스를 만드는 것보다 성능이 좋다.

세컨더리 인덱스는 보조 인덱스로 여러 개의 필드 값을 기반으로 쿼리를 많이 보낼 때 생성해야 하는 인덱스이다. 예를 들어 age라는 하나의 필드만으로 쿼리를 보낸다면 클러스터형 인덱스만 필요하다. 하지만 age, name, email 등 다양한 필드를 기반으로 쿼리를 보낼 때는 세컨더리 인덱스를 사용해야 한다.

MongoDB

MongoDB의 경우 도큐먼트를 만드면 자동으로 ObjectID가 형성되며, 해당 키가 기본키로 설정된다. 그리고 세컨더리키도 부가적으로 설정해서 기본키와 세컨더리키를 같이 쓰는 복합 인덱스를 설정할 수 있다.

인덱스 최적화 기법

1. 인덱스는 비용이다

먼저 인덱스는 두 번 탐색하도록 강요한다. 인덱스 리스트, 그다음 컬렉션 순으로 탐색하기 때문이며, 관련 읽기 비용이 들게 된다.

또한, 컬렉션이 수정되었을 때 인덱스도 수정되어야 한다. 마치 책의 본문이 수정되었을 때 목차나 찾아보기도 수정해야 하듯이 말이다. 이때 B-트리의 높이를 균형 있게 조절하는 비용도 들고, 데이터를 효율적으로 조회할 수 있도록 분산시키는 비용도 들게 된다.

그렇기 때문에 쿼리에 있는 필드에 인덱스를 무작정 다 설정하는 것은 답이 아니다. 또한, 컬렉션에서 가져와야 하는 양이 많을수록 인덱스를 사용하는 것은 비효율적이다.

2. 항상 테스팅하라

인덱스 최적화 기법은 서비스 특징에 따라 달라진다. 서비스에서 사용하는 객체의 깊이, 테이블의 양 등이 다르기 때문이다. 그렇기 때문에 항상 테스팅하는 것이 중요하다. explain() 함수를 통해 인덱스를 만들고 쿼리를 보낸 이후에 테스팅을 하며 걸리는 시간을 최소화해야 한다. 

SQL
EXPLAIN
SELECT * FROM t1
JOIN t2 ON t1.c1 = t2.c1
3. 복합 인덱스는 같음, 정렬, 다중 값, 카디널리티 순이다

보통 여러 필드를 기반으로 조회를 할 때 복합 인덱스를 생성하는데, 이 인덱스를 생성할 때는 순서가 있고 생성 순서에 따라 인덱스 성능이 달라진다. 같음, 정렬, 다중 값, 카디널리티 순으로 생성해야 한다.

1. 어떠한 값과 같음을 비교하는 `==`이나 `equal`이라는 쿼리가 있으면 제일 먼저 인덱스로 설정한다. 2. 정렬에 쓰는 필드라면 그다음 인덱스로 설정한다. 3. 다중 값을 출력해야 하는 필드, 즉 쿼리 자체가 `>`이거나 `<` 등 많은 값을 출력해야 하는 쿼리에 쓰는 필드라면 나중에 인덱스를 설정한다. 4. 유니크한 값의 정도를 카티널리티라고 한다. 이 카디널리티가 높은 순서를 기반으로 인덱스를 생성해야 한다. 예를 들어 age와 email이 있다고 해보자. 어떤 것이 더 높을까? 당연히 email이다. 즉, email이라는 필드에 대한 인덱스를 먼저 생성해야 하는 것이다.

조인의 종류

조인(join)이란 하나의 테이블이 아닌 두 개 이상의 테이블을 묶어서 하나의 결과물을 만드는 것을 말한다. MySQL에서는 JOIN이라는 쿼리로, MongoDB에서는 lookup이라는 쿼리로 이를 처리할 수 있다.

참고로 MongoDB를 사용할 때 lookup은 되도록 사용하지 말아야 한다. MongoDB는 조인 연산에 대해 관계형 데이터베이스보다 성능이 떨어진다고 여러 벤치마크 테스트에서 알려져 있다.

따라서 여러 테이블을 조인하는 작업이 많을 경우 MongoDB보다는 관계형 데이터베이스를 써야 한다.

조인의 종류 중 대표적인 내부 조인, 왼쪽 조인 오른쪽 조인, 합집합 조인을 알아보자.

![](https://velog.velcdn.com/images/cnj9912/post/33ba1370-046c-46c8-bb19-8666bf4d415b/image.png)

  • 왼쪽 조인(left outer join) : 왼쪽 테이블의 모든 행이 결과 테이블에 표기된다.
  • 오른쪽 조인(right outer join) : 오른쪽 테이블의 모든 행이 결과 테이블에 표기된다.
  • 내부 조인(inner join) : 왼쪽 테이블과 오른쪽 테이블의 두 행이 모두 일치하는 행이 있는 부분만 표기된다.
  • 합집합 조인(full outer join) : 두 개의 테이블을 기반으로 조인 조건에 만족하지 않는 행까지 모두 표기한다.

앞으로 설명 시 왼쪽 테이블을 A, 오른쪽 테이블을 B라고 칭하겠음.

왼쪽 조인

왼쪽 조인은 테이블 B의 일치하는 부분의 레코드와 함께 테이블 A를 기준으로 완전한 레코드 집합을 생성한다. 만약 테이블 B에 일치하는 항목이 없으면 해당 값은 null 값이 된다. 

SQL
SELECT * FROM TableA A
LEFT JOIN TableB B ON
A.key = B.key

오른쪽 조인

오른쪽 조인은 테이블 A에서 일치하는 부분의 레코드와 함께 테이블 B를 기준으로 완전한 레코드 집합을 생성한다. 만약 테이블 A에 일치하는 항목이 없으면 해당 값은 null 값이 된다. 

SQL
SELECT * FROM TableA A
RIGHT JOIN TableB B ON
A.key = B.key

내부 조인

내부 조인은 두 테이블 간에 교집합을 나타낸다. 

SQL
SELECT * FROM TableA A
INNER JOIN TableB B ON
A.key = B.key

합집합 조인

합집합 조인(완전 외부 조인)은 양쪽 테이블에서 일치하는 레코드와 함께 테이블 A와 테이블 B의 모든 레코드 집합을 생성한다. 이때 일치하는 항목이 없으면 누락된 쪽에 null 값이 포함되어 출력된다. 

SQL
SELECT * FROM TableA A
FULL OUTER JOIN TableB B ON
A.key = B.key

조인의 원리

앞서 설명한 조인은 조인의 원리를 기반으로 조인 작업이 이루어졌다. 조인의 원리인 중첩 루프 조인, 정렬 병합 조인, 해시 조인에 대해 알아보자.

중첩 루프 조인

중첩 루프 조인(NLJ, Nested Loop Join)은 중첩 for 문과 같은 원리로 조건에 맞는 조인을 하는 방법이며, 랜덤 접근에 대한 비용이 많이 증가하므로 대용량의 테이블에서는 사용하지 않는다.

예를 들어 "t1, t2 테이블을 조인한다."라고 했을 때 첫 번째 테이블에서 행을 한 번에 하나씩 읽고 그다음 테이블에서도 행을 하나씩 읽어 조건에 맞는 레코드를 찾아 결괏값을 반환한다. 

 
// 의사코드
for each row in t1 matching reference key {
	for each row in t2 matching reference key {
    	if row satisfies join conditions, send to client
    }
}

참고로 중첩 루프 조인에서 발전한 조인할 테이블을 작은 블록으로 나눠서 블록 하나씩 조인하는 블록 중첩 루프 조인(BNL, Block Nested Loop)이라는 방식도 있다.

정렬 병합 조인

정렬 병합 조인이란 각각의 테이블을 조인할 필드 기준으로 정렬하고 정렬이 끝난 이후에 조인 작업을 수행하는 조인이다. 조인할 때 쓸 적절한 인덱스가 없고 대용량의 테이블들을 조인하고 조인 조건으로 <, >등 범위 비교 연산자가 있을 때 쓴다.

해시 조인

해시 조인은 해시 테이블을 기반으로 조인하는 방법이다. 두 개의 테이블을 조인한다고 했을 때 하나의 테이블이 메모리에 온전히 들어간다면 보통 중첩 루프 조인보다 더 효율적이다. (메모리에 올릴 수 없을 정도로 크다면 디스크를 사용하는 비용이 발생된다.) 또한 동등(=)조인에서만 사용할 수 있다.

원본 주소 "https://technote.wiki/index.php?title=DB2&oldid=280"