인덱스의 개념(클러스터, 논클러스터, B-tree)과 인덱스 성능 테스트

안녕하세요. 오늘은 인덱스에 대해 알아보겠습니다. 

 

인덱스를 생성하면 DB의 검색성능이 크게 향상된다는 얘기를 많이 들어보았지만

 

그동안 제가 사용하고 관리했던 DB는 저장된 정보가 많지 않아 인덱스의 필요성을 크게 느끼지 못하였습니다.

(실제로 저장된 행이 많지 않으면 사용하지 않는게 좋다고 합니다.)

 

허나 최근 DB에 많은 정보가 쌓인 프로젝트를 진행하며 인덱스를 사용하게되어 

 

좀더 구체적으로 알아보고자 인덱스의 개념과 이를 실습을 통해 알아보았습니다.

 

인덱스는 클러스터드 인덱스, 논클러스터드 인덱스가 있습니다.

 

그리고 이를 이해하기 위해서는 DB의 인덱스 자료구조인 B-tree자료 구조를 알아야합니다. 

 

이 세가지 개념을 이해한다면 인덱스를 사용하는데 큰 도움이 될 것입니다.

 

 

 

작업환경

DB : mysql 8.0

DB관리 툴 : mysql workbench

 

 

클러스터드 인덱스(Clustered Index)

 

• 테이블당 한개만 생성가능
• pk를 설정한 경우 default로 pk가 클러스터드 인덱스로 설정
• 인덱스 자체에 data가 저장되어 있음
• 실제 물리적인 정렬순서와 인덱스의 정렬순서와 같음

 

논클러스터드 인덱스(NoneClustered Index)

 

• 하나의 테이블에 여러개의 인덱스 생성 가능
• 인덱스 자체에 data가 저장되지 않고 data의 주소가 저장됨
• 실제 물리적인 정렬순서와 인덱스의 정렬 순서는 다름

 

 

 

클러스터드 인덱스와 논클러스터드 인덱스는 위와 같은 성질이 있습니다. 

 

B-tree 자료구조에 대해서 알고있다면 더욱 구체적으로 이해할 수 있지만,

 

우선 테이블의 Primary Key가 선언되어 있는 경우 클러스터드 인덱스로 자동 설정된다고 하니

 

primary key의 유무 차이를 통해 클러스터드 인덱스의 개념에 대해 알아보겠습니다.

 

 

제가 아래와 같이 pk를 선언하지 않고 테이블을 만들었고 정보를 저장하였습니다.

 

 

지금 id칼럼에 1부터 8의 값을 역순으로 저장하였는데 이상태에서 id칼럼에 pk를 선언해보겠습니다.

 

 

alter table FruitTable add primary key(id);

 

 

 

primary key를 설정하니 primary key를 설정한 값으로 정렬이 되었습니다.

 

우리는 이를 통해 클러스터드 인덱스의 성질 중

 

• pk를 설정한 경우 default로 pk가 클러스터드 인덱스로 설정
• 클러스터드 인덱스를 생성하면 실제 데이터의 정렬이 인덱스의 순서로 정렬됨

이 두가지에 대한 내용을 확인할 수 있습니다.

 

 

자 그리고 이제 성능 테스트를 위해 40만건의 데이터가 저장된 샘플로 테스트를 해보겠습니다.

 

아래와 같이 denouement(대단원), sub(중단원), question_no(문제번호), creation_time(생성시간) 칼럼으로 이루어진

 

테이블을 만들었습니다.

 

 

 

 

 

pk를 question_no에 선언할 것이기 때문에 pk를 설정하기 전에 먼저 쿼리 실행 결과가 어느정도 나오는지 테스트

 

해보겠습니다.

 

select sql_no_cache * from math_book where question_no='374513';

 

위의 쿼리 실행 결과 0.219sec가 걸린것을 확인 할 수 있다.

 

 

 

이제 인덱스를 생성(pk 생성)하고 조회해보겠습니다.

alter table Math_book add primary key (question_no);
select sql_no_cache * from math_book where question_no='374513';

 

쿼리 실행결과 0.000sec 입니다. 

 

인덱스를 생성하니 굉장히 빠르게 조회하였습니다. 

 

인덱스 생성전과 인덱스 생성 후 어떤 차이점 때문에 성능이 빨라졌을까요??

 

 

B-tree 자료구조

 

인덱스가 없는 경우,

 

테이블의 모든 행을 Full-Scan하게 됩니다. 

 

처음부터 끝까지 모든 행을 검사하기 때문에 시간이 굉장히 오래 걸립니다.

 

인덱스가 있는 경우,

 

B-tree라는 자료구조로 생성된 인덱스 페이지에서 데이터를 찾을 수 있습니다.

 

이해를 돕기 위해 1부터 27까지의 데이터가 저장되어 있는 테이블에서 7이라는 숫자를 어떻게 찾는지 예시를 들어보겠습니다.

 

 

B-tree 자료구조는 위와 같이 생성이 되며

 

최상위 노드에서 7이 9보다 작으므로 9보다 작은 연산으로 보내고 

 

그 다음 노드에서 7이 6보다 크므로 6보다 큰 연산으로 보내

 

마지막 노드에서 대소관계를 비교해 7을 찾아 데이터 정보를 가져옵니다. 

 

따라서 1부터 27까지 모두 스캔하지 않고 

 

단 3번의 연산을 통해 해당하는 데이터를 찾아냈습니다. 

 

이와 같은 방식으로 데이터를 조회하는 방식이 DB에서 인덱스를 통하여 데이터를 조회하는 것입니다. 

 

실제 DB는 위의 B-tree 구조를 통해 아래와 같이 인덱스 페이지의 주소를 따라가며 마지막에

 

실제 id 7에 해당하는 칼럼을 찾아 해당하는 행의 칼럼 값들을 가져올 수 있습니다.

 

클러스터드 인덱스는 인덱스 페이지에 데이터가 직접 저장되어 데이터의 물리적인 정렬순서가

 

인덱스의 정렬순서와 같습니다.

 

 

 

 

 

논클러스터드 인덱스(NoneClustered Index)

 

클러스터드 인덱스에서 B-tree 자료구조를 통해 db를 조회하는 방식에 대해 알아보았습니다. 

 

논클러스터드 인덱스에서도 마찬가지로 B-tree 자료구조를 통해서 db를 조회하는데요. 

 

차이점은 인덱스 페이지에 데이터가 직접 저장되지 않고 자료의 주소가 저장된다는 것입니다.  

위의 그림과 같이 인덱스 페이지에서 찾고자 하는 데이터의 주소를 인덱스 페이지에서 찾고

 

데이터 페이지에 가서 해당 주소로 접근하여 데이터를 가져옵니다. 

 

실제 데이터의 정렬순서는 변경시키지 않고 인덱스 페이지에 데이터의 주소를 입력하기 때문에

 

인덱스의 정렬순서와 데이터의 정렬순서는 다릅니다.

 

 

 

위와 같이 구성된 테이블에서 denoument칼럼에 인덱스를 생성후 조회해보겠습니다.

 

create index col_index on MAth_BOOK(denouement);	//논클러스터드 인덱스 생성 명령어
select * from math_book limit 100;

 

 

정렬순서가 변하지 않고 그대로입니다. 

 

이전 클러스터드 인덱스에서는 인덱스로 설정한 칼럼의 가나다 순으로 정렬됬던 것에 반해

 

논클러스터드 생성 명령어 실행 후 데이터 정렬 순서가 변하지 않음을 확인할 수 있습니다.

 

인덱스 생성 전후 성능 테스트를 해보았는데

 

 

 

생성전 0.234sec였던 반면 생성후 0.000sec로  인덱스가 정상적으로 생성됬고 성능이 좋아졌음을 확인했습니다.

 

논클러스터드 인덱스가 클러스터드 인덱스와 또다른 특징 중 하나가 인덱스를 여러개 생성할 수 있다고 하였으니

 

여러개 생성해보겠습니다.

 

create index col_index on MAth_BOOK(denouement, question_no);

 

denouement칼럼과 question_no 칼럼에 인덱스를 생성했습니다.

 

생성 전후 성능 테스트를 비교해보면

 

select sql_no_cache * from math_book where denouement='수열' and question_no='387072';

 

생성 전 0.407sec에서 생성후 0.000sec로 상당히 빨라졌음을 확인 할 수 있습니다. 

 

참고로 인덱스를 생성할 때 denoument칼럼을 첫번째에 넣고 question_no를 두번째에 넣었습니다. 

 

인덱스 사용시 첫번째 인덱스 칼럼 사용하지 않으면 인덱스 사용되지 않습니다. 

 

select sql_no_cache * from math_book where question_no='387072';

 

위와같이 첫번째 인덱스 칼럼인 denouement없이 question_no만 사용한 경우 

 

인덱스 생성 전후 성능이 똑같습니다. 

 

논클러스터드 인덱스를 여러개 생성하면 반드시 첫번째 인덱스를 사용해야 인덱스페이지에 접근하게 되는 것입니다.

 

논클러스터드 인덱스는 클러스터드 인덱스에 비해 조회속도는 빠르지 않지만 여러개의 칼럼을 인덱스로 설정할 수 있습니다.

 

또한 검색 속도가 느린 대신 insert나 delete가 클러스터드 인덱스에 비해 빠른 성능을 보입니다. 

 

 

 

자 이렇게 해서 클러스터드 인덱스, 논클러스터드 인덱스, B-tree 구조에 대해서 간단히 알아보았는데요.

 

이러한 장점들을 본다면 인덱스를 사용하는 것이 사용하지 않는 경우보다 더욱 효과적이어 보이는데

 

반드시 인덱스를 사용하는 것이 효과적일까요??

 

실제로는 꼭 그렇지 않습니다. 

 

인덱스는 사실 조회하는 성능은 빠르게 하지만 insert, delete와 같은 작업은 인덱스가 없을때보다

 

속도가 느려지게 합니다. 

 

새로운 행이 추가되거나 제거될때 위의 인덱스 페이지에도 B-tree 구조가 유지되어야 하기 때문에 

 

insert나 delete가 빈번한 테이블에서는 성능을 더욱 느리게 합니다.

 

또한 B-tree구조를 통해 조회하는 예시를 설명할 때 연산하는 시간은 전혀 고려는 전혀하지 않았습니다. 

 

각 노드에 가서 특정값이 어떤 자식 노드로 가야할지 연산하는 시간이 Scan 하는 시간에 비해 길다면 

 

인덱스 없이 Full-Scan 하는 방식이 더욱 효율적일 수 있습니다.

 

 

 

인덱스 생성시 고려사항

• 업데이트가 많이 일어나는 칼럼은 인덱스 칼럼으로 선언하지 않는다.
  인덱스는 update시 실제 update 작업이 아닌 사용하지 않음 처리가 되어
  여전히 남아 있다. 따라서 업데이트가 빈번하게 되면 데이터보다 인덱스가 더 많이 생성될 수 있다.
• 기본키 및 외부키(조인의 연결고리가 되는 컬럼)로 인덱스를 생성한다.
• 지나치게 많은 인덱스는 많은 오버헤드를 초래한다.
• 분포도가 좋은 컬럼으로 선정한다.
  분포도 = (인덱스의 특정값의  행의수 / 전체 행의수 ) * 100
• 조건절에서 자주 사용되는 칼럼
  조건절에서 자주 사용한다고 하여 무조건 선정해야하는건 아니지만 자주 사용하지 않는 조건절의 칼럼을 
  굳이 인덱스로 선정하기보다는 자주 사용하는 조건절 칼럼이 낫다.(과도한 인덱스 추가는 성능저하)
• insert, delete, update 구문이 자주 사용하지 않는 테이블

 

 

 

[추가 개념]

 

추가 개념으로 클러스터드 인덱스는 테이블당 한개만 설정 가능하다고 하였는데

 

pk로 생성된 클러스터드 인덱스가 반드시 1개의 칼럼은 아닐 수 있다는 개념을 설명하겠습니다. 

pk는 반드시 1개, 그렇다면 pk칼럼도 반드시 1개??

 

pk 기본키는 반드시 1개이어야 합니다. 

 

같은 개념이라면 pk로 선언된 칼럼도 1개여야겠죠.

 

하지만 pk는 여러개의 칼럼으로 선언될 수 있습니다. 

 

왜냐하면 pk가 여러개로 선언되면 여러개의 칼럼들이 각각 독립적으로 pk로 동작하는게 아닌

 

여러개의 칼럼이 한쌍으로 결국 1개의 기본키가 됩니다. 

 

이전에 예시로 들었던 MATH_BOOK테이블의 denouement칼럼과 question_no 칼럼을 pk로 선언하면

 

 

pk값이 좌표평면 위에서 좌표를 나타내는것처럼  한 쌍을 나타냅니다.

 

x축의 값과 y축의 값이 한쌍으로 (x, y)라는 좌표를 이루듯이 

 

denouement와 question_no 값이 (denouement, question_no) 한쌍을 이룹니다. 

 

위의 이미지파일을 보면 pk값이 (수열,0), (급수,1) 이런식으로 선언이 되겠죠. 

 

마찬가지로 인덱스 또한 두 칼럼의 쌍으로 생성 됩니다. 

 

한번 두 칼럼을 pk로 선언 후 조회 결과를 살펴 보겠습니다.

alter table Math_book add primary key (denouement, question_no);
select * from MATH_BOOK limit 100;

 

denouement 칼럼이 가나다 순으로 정렬되어 급수가 맨앞으로 오고 그 안에서 question_no 순으로 정렬되고 있습니다. 

 

클러스터드 인덱스 성질에서 pk를 설정한 경우 pk가 클러스터드 인덱스로 설정이 되고 클러스터드 인덱스가 설정되면

 

실제 데이터 정렬순서도 인덱스 정렬 순서에 맞게 변경이 된다고 했습니다.

 

우리가 pk로 설정한 denouement칼럼과 question_no칼럼이 한쌍으로 클러스터드 인덱스로 설정됨을 확인할 수 있는 

 

부분입니다.