DB 설계 PART.최적화와 인덱스

서론

※ 이 포스트는 다음 강의의 학습이 목표임을 밝힙니다.

https://www.youtube.com/playlist?list=PL9hiYwOHVUQduJN7Pf_kOR8htpJU7K1H8 

2020 데이터베이스

 

본론

- 인덱스

인덱스란 배열의 번호, 책갈피 인덱스 등 빨리 검색을 하기 위한 것 입니다. 인덱스가 없으면 풀 스캔을 해야 찾을 수 있습니다. 인덱스의 특징은 정렬이 되어있습니다. 책의 부록 인덱스를 보면 가나다 순으로 정렬된 인덱스에서 몇 장에 원하는 내용이 있는 지 알 수 있습니다. 이렇게 원하는 데이터 대신에 인덱스를 대신 빨리 찾아 원하는 데이터에 접근할 수 있습니다.

 

- 데이터 베이스 인덱스

인덱스 성능 비교를 위한 더미 데이터를 준비합니다. 30만 행 이상의 직원 데이터입니다.

 

성이 토마스인 사람을 찾아보면 233 행이 나오고 0.091초에 걸려서 찾았습니다. 이 방법은 30만 건을 전부 다 풀 스캔해 본 것입니다. 30만 건을 0.091초가 걸린 건 엄청 오래걸린 것으로 몇 억 건의 데이터를 찾으려면 엄청난 부하가 생깁니다. 현재 데이터는 기준 없이 저장된 상태로 만약 데이터가 특정 기준으로 정렬되어 있다면 검색을 빠르게 할 수 있을 것입니다.(참고 1)

 

-> 인덱스 사용

이것을 빨리 찾으려면 인덱스를 성으로 정하면 됩니다. 그러면 현재 데이터는 성으로 정렬된 30만 건 이상 데이터로 바뀔 것 입니다. 이때 다시 성으로 조회를 하고 나면 0.003초로 속도가 10배 이상 빨라졌습니다. 성에 인덱스를 주면 성으로 정렬을 하고 DB 어디에 있는지 인덱스를 DB 내부적으로 생성해서 가지게 됩니다. 성 컬럼에 대해 인덱스를 만들어둬서 테이블 전체를 검색하지 않아 훨씬 검색 속도가 빨라집니다.

where 절로 검색을 해야 인덱스를 사용할 수 있으며 인덱스를 설정하더라도 where를 하지 않으면 인덱스가 사용되지 않습니다. (참고 1)

 

-> 인덱스 특징

1. 인덱스는 항상 최신의 정렬상태를 유지(삽입, 삭제 적용)
2. 인덱스도 하나의 데이터 베이스 객체
3. DB 크기의 약 10% 정도의 저장공간 필요

 

- 인덱스 알고리즘

※ 페이지 : 데이터가 저장되는 단위

 

1. 풀 테이블 스캔

풀 스캔은 순차적으로 처음부터 찾게 됩니다. 만약 ppp를 찾는 다면 순차적으로  ppp를 찾게 되고 총 3개의 페이지와 12번의 검색이 있습니다.

 

-> 특징

1. 순차적으로 접근
2. 접근 비용 감소

정렬을 하지 않고 저장된 데이터 그대로 검색을 하므로 접근 비용은 감소하지만 검색 성능이 안좋습니다.

 

-> 풀 테이블 스캔을 사용하는 경우

1. 적용 가능한 인덱스가 없는 경우
2. 인덱스 처리 범위가 넓은 경우
3. 크기가 작은 테이블에 엑세스 하는 경우(DB가 인덱스를 적용하여도 성능상 이점이 별로 없는 경우)

 

2. B-Tree

이진 탐색 트리를 이용한 인덱스 알고리즘입니다. 균형 있는 이진탐색 트리일 경우 log n의 시간 복잡도를 가지고 균형이 없을 경우 n의 복잡도를 가집니다. 이는 이진탐색트리의 단점이라고 할 수 있는데 이 단점을 개선한 것이 B-Tree입니다. Balanced-Tree라고도 하며 트리 높이가 같고 자식 노드를 2개 이상 가질 수 있습니다.

 

-> 검색 과정 설명

루트, 브랜치, 리프 페이지라는 게 있는데 루트, 브랜치는 자식 테이블의 정보를 가지고 있습니다. 루트 페이지는 최상단에 위치하고 브랜치 페이지는 루트 페이지와 리프 페이지의 중간에 위치하며 리프 페이지는 실제 데이터 페이지입니다.

 

풀 스캔 예시를 B-Tree로 해보면 먼저 리프 페이지가 있을 때

 

루트 페이지와 리프 페이지가 연결됩니다. 여기서 ppp를 찾으려면 ppp를 찾으려면 루트 페이지를 먼저 찾아 P는 L 다음에 있기 때문에 루트의 L에 연결된 리프 페이지로 가서 p를 찾게 됩니다. 총 2개의 페이지, 7번의 검색을 하며 인덱스를 통해 select의 성능이 향상되는 것을 확인할 수 있습니다.

 

-> insert

insert의 경우를 보겠습니다. ooo를 삽입해보면 o는 n과 P 사이에 있기에 사이에 들어가고 이는 p의 이동이 있지만 페이지 내부에서 작업되기에 현재까지는 큰 부담이 없습니다.

 

여기서 z를 삽입하면 페이지가 꽉 차있어서 z를 삽입할 수 없어서 DB는 비어 있는 페이지를 확보를 하고 문제가 있는 페이지의 데이터를 공평하게 나누어 저장합니다. 이는 DB에 부담이 되는 작업으로 이를 페이지 분할이라고 합니다.

 

+ 페이지 분할

페이지에 새로운 데이터를 추가할 여유 공간이 없어 페이지에 변화가 발생하는 것을 말하며 DB가 느려지고 성능에 영향을 줍니다.

 

- delete + update

인덱스의 삭제는 인덱스의 데이터가 실제로 지워지지 않고 '사용 안 함' 표시를 합니다. 수정은 인덱스에는 수정의 개념이 없어서 삭제를 하고 insert를 하여 수정을 진행합니다.

 

삭제와 수정 또한 사용하지 않는 인덱스가 적용되어 불필요한 처리량이 증가하고 사용 안 함 표시로 페이지 낭비가 발생하여 select만 인덱스가 성능을 향상시킵니다.

 

- 인덱스 종류
-> 클러스터란?

 

무리를 이루는 것을 말하며 DB에서는 실제 데이터와 무리를 이룸을 의미하고 논 클러스터는 실제 데이터와 무리를 이루지 않음을 의미합니다.

 

1. 클러스터링 인덱스

실제 데이터와 같은 무리의 인덱스(= 사전의 인덱스, 배열의 인덱스)로 실제 데이터와 인덱스가 같이 있습니다.

2. 논 클러스터링 인덱스

실제 데이터와 다른 무리의 별도의 인덱스(부록의 인덱스)로 실제 데이터와 떨어져 있습니다.

 

=> 인덱스의 종류

이렇게 테이블을 생성하면 2개의 인덱스가 생성되었습니다. pk는 클러스터링 이메일은 unique로 논 클러스터링 인덱스가 자동으로 생성됩니다. 인덱스가 생성되는 것은 밑에서 알아봅니다.

 

1. 클러스터링 인덱스

회원 테이블에 어떠한 제약도 걸지 않으면 어떠한 인덱스도 생성되지 않습니다. 여기에 데이터를 넣고 클러스터링 인덱스를 적용해봅니다. 클러스터링 인덱스를 하려면 pk를 하거나 한 컬럼에 Not Null과 UQ 제약조건을 같이 걸면 됩니다.

 

왼쪽 : 정렬전, 오른쪽 : 정렬후

인덱스를 생성한 후 동작 과정을 보겠습니다. 먼저 클러스터링 인덱스를 적용한 컬럼을 기준으로 데이터가 정렬되게 됩니다. 인덱스를 적용한 열이 정렬됨에 따라 행 전체가 이동하게 됩니다.

 

그리고 정렬된 데이터 페이지가 리프 페이지가 되고 루트 페이지와 연결되어 B-Tree 구조를 가지게 됩니다. 위에서는 루트 페이지가 리프 페이지의 데이터를 가지고 참조하는 줄 알았는데 루트 페이지는 리프 페이지의 첫번째 데이터와 페이지 주소를 가지고 참조하게 됩니다.

 

데이터 페이지는 실제 데이터가 저장된 페이지고 루트 페이지는 실제 데이터가 아닌 별도의 페이지입니다. where로 검색을 해보면 앞서 배웠을 때는 데이터를 보고 루트페이지에서 리프 페이지로 가는 줄 알았는데 루트 페이지가 리프 페이지의 5와 9 사이의 페이지 주소를 보고 가는 것입니다.

 

-> 클러스터링 인덱스의 특징

1. 실제 데이터 자체가 정렬
2. 테이블당 1개만 존재
3. 리프 페이지 == 데이터 페이지
4. pk와 uq + nn 제약 조건 시 pk 우선

pk와 uq + nn 제약 조건이 함께 존재할 때 테이블 생성시 pk가 우선순위가 생겨 인덱스를 생성하게 됩니다. 즉 클러스터링 인덱스에서는 여러개의 속성에 인덱스가 적용되어도 하나의 인덱스만 생깁니다.

 

2. 논 클러스터링 인덱스

name에 인덱스를 적용해봅니다. 한 컬럼에 UQ만 적용하거나 CREATE INDEX로 인덱스를 직접 생성할 때 논 클러스터링 인덱스가 생성됩니다. 인덱스 생성도 UQ로 중복을 불허할 수도 있고 허용할 수도 있습니다.

 

논 클러스터링 인덱스가 적용된 후에 실제 데이터가 저장된 페이지는 어떠한 정렬이나 변경도 일어나지 않습니다. 리프 페이지가 데이터 페이지가 아닌 별도의 인덱스 페이지가 추가되고 인덱스 페이지가 name을 기준으로 정렬되어 인덱스 페이지에 B-Tree가 적용됩니다.

클러스터링 인덱스에서는 인덱스와 데이터가 함께 있어서 바로 데이터를 찾을 수 있었는데 논 클러스터링에서는 별도의 인덱스 페이지가 있고 인덱스 페이지에 데이터가 아닌 새로운 값이 있습니다. 새로운 값은 실제 데이터 페이지의 주소와 원하는 데이터의 행 번호입니다.

 

1002 + #3이면 1002는 데이터 페이지를 의미하고 3은 데이터 페이지의 3번째에 데이터가 있음을 의미합니다. 실제 데이터 페이지는 정렬되지 않고 인덱스 페이지에 B-Tree가 적용되어 정렬된 인덱스로 지정한 컬럼과 새로운 값이 생기며 그 값으로 검색을 하게 됩니다.

 

-> 특징

1. 실제 데이터 페이지는 그대로
2. 별도의 인덱스 페이지 생성 = 추가 공간 필요
3. 테이블당 여러개 존재
4. 리프 페이지에 실제 데이터 페이지 주소를 담고 있음

클러스터링 인덱스는 테이블당 한개만 가능했는데 논 클러스터링은 별도의 인덱스 테이블을 새로 만들기 때문에 테이블당 여러개 존재 가능합니다.

 

-> 클러스터링과 논 클러스터링 인덱스를 함께 적용

 

하나의 테이블에 id는 클러스터링, name은 논 클러스터링 인덱스를 적용하면

 

클러스터링 인덱스는 데이터 페이지에 적용되고 논 클러스터링은 별도의 인덱스 페이지를 만들어서 적용할 수 있을 것입니다.

 

하지만 실제로는 인덱스 페이지에 데이터 페이지의 주소 값이 아닌 클러스터링 인덱스가 적용된 컬럼인 id 컬럼이 들어가서 논 클러스터링에서는 클러스터링 인덱스의 루트 페이지에 원하는 검색 데이터를 전달하고 실제 검색은 클러스터링 인덱스로 이뤄집니다.

 

-> 예상 구조의 문제점

만약 id가 3인 파랑의 데이터가 추가로 삽입 된다고 하면 실제 데이터 페이지가 클러스터링 인덱스로 정렬이 되어있으니 2 밑에 들어와야 하고 4, 5는 페이지 분할이 발생합니다. 그렇게 되면 별도의 인덱스 페이지의 1000 + #3, 1000 + #4는 변경이 일어나야 합니다. 데이터가 추가되거나 삭제될 때마다 인덱스 페이지에 변경을 줘서 위와 같은 구조로 하면 안됩니다.

 

(실제) 구조는 논 클러스터링 인덱스의 인덱스 페이지에 데이터 페이지의 주소가 아닌 데이터가 들어가기에 실제 데이터 페이지는 동일하게 페이지 변환이 발생하더라도 인덱스 페이지에는 추가 되는 것 외에 기존 데이터가 이동하는 변화는 없습니다.

 

- 인덱스 적용 기준

1. 카디널리티

어떤 컬럼에 인덱스를 적용해야 할까요? 카디널리티가 높은 컬럼입니다. 즉 중복이 되지 않는 컬럼에 적용하면 되고 따라서 pk와 UQ에 대해 적용합니다.

 

2. where, join, order by 절에 자주 사용되는 컬럼

조건 절이 없다면 인덱스가 적용되지 않습니다. 추가로 성능을 위해 insert, update, delete가 자주 발생하지 않는 컬럼과 규모가 작지 않은 테이블에 적용하는 것이 좋습니다.

- 인덱스 실습

-> 인덱스 조회

위처럼 인덱스를 적용하고 인덱스를 조회하면 다음과 같은 내용을 확인할 수 있습니다.

중복을 허용하면 1, 불허하면 0으로 id는 pk, email은 UQ를 적용했기에 0이 나옵니다. 키 이름은 인덱스의 이름으로 기본키라면 primary를 적용합니다. 카디널리티 수치도 적용할 수 있으며 현재 아무 데이터가 없어서 0이고 데이터를 넣으면 수치가 올라갑니다.

 

- 성능 비교 및 실습

필자의 환경에 적용하여 실습해보겠습니다.

 

 

-> 인덱스(키)의 종류

인덱스의 종류에는 4가지가 있습니다. pk, UQ, key, fulltext입니다. pk는 테이블을 만들 때 주면 바로 인덱스로 정렬이됩니다. UQ는 테이블에 한 개 밖에 없는 컬럼에 주고 일반적으로 주는 것은 KEY입니다. 만들 때는 키를 삭제하거나 수정할 때 사용하기 위해 키 이름을 만들어야 하고 인덱스를 만들 때 조합으로 여러개 줄 수도 있어서 키 이름을 생략하면 조합 중 첫 번째 컬럼명이 키 이름으로 들어갑니다.

 

왼쪽 : 클러스터링/ 오른쪽 : 비클러스터링

이미 있는 테이블은 키를 이렇게 만들고 create table 할 때 KEY를 줘도 인덱스를 만들 수 있습니다.

 

show index를 하면 해당 테이블의 인덱스를 볼 수 있습니다. 0번으로 나온게 Unique 1번으로 나온게 UnUnique라고 했습니다. 성은 현재 UQ나 PK가 아닌 alter add로 인덱스를 준 거라서 중복이 가능합니다. 카디널리티를 봤을 때 pk는 전부 다 중복이 안되고 성 은 1236개의 종류가 있는 것을 알 수 있습니다. 

 

create 할 때도 보면 pk를 emp_no을 줬고 first_name이라는 컬럼에 first_name이라는 키 이름으로 인덱스를 준 것을 알 수 있습니다. pk는 기본이 클러스터링, 성은 add KEY를 했기에 클러스터링 인덱스입니다.

 

성을 인덱스를 사용해서 검색해보면

 

1행 조회나 233행 조회나 차이가 없을 정도로 빠르게 조회가 가능합니다.

 

인덱스를 제거하고 조회를 해보면 0.2초나 걸리고 컴퓨터 세계에서 0.2초는 엄청난 시간입니다. 데이터의 크기가 클수록 이 차이는 더 커집니다.

 

성은 인덱스가 있는데 이름은 인덱스가 없으면 성으로 먼저 찾은 233개 중에서 호프만을 찾습니다.

 

근데 성과 이름이 둘 다 같이 인덱스가 잡혀있으면 더 빨라집니다. 이는 테이블이 두개의 컬럼으로 정렬된 것으로 볼 수 있습니다. 클러스터링 인덱스가 일어나서 데이터 페이지에 정렬을 할 때 두 개의 컬럼으로 order by 한 것과 같은 상태가 됩니다.

 

먼저 성으로 정렬이 되어있고 거기서 또 이름으로 정렬이 되어있으니 성 = 토마소, 이름 = 호프만을 더 빨리 찾을 수 있습니다. 보통 한 개씩만 조회하면 인덱스를 하나만 적용하고 둘 씩 묶어서 조회하면 인덱스도 묶는 것이 좋을 것입니다. 시스템에 많이 쓰는 쿼리에 where 절에 조건에 맞춰서 인덱스를 주는 것이 좋습니다.

 

 

근데 이렇게 잡혀있을 때 last_name으로만 조회하면 인덱스를 사용하지 않게 됩니다. 성은 이렇게 해도 먼저 정렬되니 인덱스가 적용이 되는데 이름은 성 다음에 정렬이 되니 이름으로만 조회하면 인덱스가 적용이 안됩니다.

 

다시 0.2초가 걸리는 것을 확인할 수 있습니다.

 

그래서 이럴 때는 키를 추가로 줘서 이름으로만 조회할 때는 이름 인덱스를 사용하고 성과 이름 조합으로 조회할 때는 성 + 이름 인덱스를 사용하도록 해야합니다. 이처럼 자주 사용하는 조건문을 잘 분석해서 그 조건에 사용되는 컬럼을 인덱스로 생성하는 것이 좋습니다.

 

- 인덱스 사용시 주의사항

1. 잘 활용되지 않는 인덱스는 과감히 제거
2. 적절한 컬럼에 적용

결론

인덱스를 미적용했을 때 단건 조회에서는 성능에 큰 차이가 없겠지만 어떤 작업을 위해 선행되어야 하는 작업이라면 성능에 큰 영향을 줄 수 있습니다.

 

인덱스를 생성하면 빠른 조회를 할 수 있지만 그렇다고 인덱스를 도배하면 금방 찾을 거를 인덱스를 사용해 찾느라 더 느려지게 되고 데이터를 추가, 수정, 삭제할 때마다 인덱스도 바꿔줘야 해서 성능에 역효과가 납니다. 좋은 경우에 적절한 컬럼에 적용하여 사용해야할 것입니다.

 

참고

데이터베이스 인덱스

더미데이터 셋팅