on
인덱스의 개념(클러스터, 논클러스터, B-tree)과 인덱스 성능 테스트[1]
인덱스의 개념(클러스터, 논클러스터, B-tree)과 인덱스 성능 테스트[1]
반응형
안녕하세요. 오늘은 인덱스에 대해 알아보겠습니다.
인덱스를 통하면 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부터 18까지의 데이터가 저장되어 있는 테이블에서 5라는 숫자를 어떻게 찾는지 예시를 들어보겠습니다.
B-tree 자료구조는 위와 같이 생성이 되며
최상위 노드에서 5가 6보다 작으므로 6보다 작은 연산으로 보내고
그 다음 노드에서 5가 4보다 크므로 4보다 큰 연산으로 보내
마지막 노드에서 대소관계를 비교해 5를 찾아 데이터 정보를 가져옵니다.
따라서 1부터 18까지 모두 스캔하지 않고
단 3번의 연산을 통해 해당하는 데이터를 찾아냈습니다.
이와 같은 방식으로 데이터를 조회하는 방식이 DB에서 인덱스를 통하여 데이터를 조회하는 것입니다.
실제 DB는 위의 B-tree 구조를 통해 아래와 같이 인덱스 페이지의 주소를 따라가며 마지막에
실제 id 5에 해당하는 칼럼을 찾아 해당하는 행의 칼럼 값들을 가져올 수 있습니다.
이러한 장점을 본다면 인덱스를 사용하는 것이 더욱 효과적이어 보이는데
반드시 인덱스를 사용하는 것이 효과적일까요??
실제로는 꼭 그렇지 않습니다.
제가 위의 예시를 설명할 때 연산하는 시간에 대한 고려는 전혀하지 않았습니다.
각 노드에 가서 5가 6보다 큰지 작은지, 4보다 큰지 작은지 이러한 연산을 하기 위한 시간을 고려한다면
인덱스 없이 Full-Scan 하는 방식이 더욱 효율적일 수 있습니다.
또한 인덱스는 사실 조회하는 성능은 빠르게 하지만 insert, delete와 같은 작업은 인덱스가 없을때보다
속도가 느려지게 합니다.
새로운 행이 추가되거나 제거될때 위의 인덱스 페이지에도 B-tree 구조가 유지되어야 하기 때문에
insert나 delete가 빈번한 테이블에서는 성능을 더욱 느리게 합니다.
인덱스 생성시 몇가지 고려사항이 있는데 아래와 같은 내용들이 있습니다.
업데이트가 많이 일어나는 칼럼은 인덱스 칼럼으로 선언하지 않는다.
인덱스는 update시 실제 update 작업이 아닌 사용하지 않음 처리가 되어
여전히 남아 있다. 따라서 업데이트가 빈번하게 되면 데이터보다 인덱스가 더 많이 생성될 수 있다.
인덱스는 update시 실제 update 작업이 아닌 사용하지 않음 처리가 되어 여전히 남아 있다. 따라서 업데이트가 빈번하게 되면 데이터보다 인덱스가 더 많이 생성될 수 있다. 기본키 및 외부키(조인의 연결고리가 되는 컬럼)로 인덱스를 생성한다.
지나치게 많은 인덱스는 많은 오버헤드를 초래한다.
분포도가 좋은 컬럼으로 선정한다.
분포도 = (인덱스의 특정값의 행의수 / 전체 행의수 ) * 100
분포도 = (인덱스의 특정값의 행의수 / 전체 행의수 ) * 100 조건절에서 자주 사용되는 칼럼
조건절에서 자주 사용한다고 하여 무조건 선정해야하는건 아니지만 자주 사용하지 않는 조건절의 칼럼을
굳이 인덱스로 선정하기보다는 자주 사용하는 조건절 칼럼이 낫다.(과도한 인덱스 추가는 성능저하)
조건절에서 자주 사용한다고 하여 무조건 선정해야하는건 아니지만 자주 사용하지 않는 조건절의 칼럼을 굳이 인덱스로 선정하기보다는 자주 사용하는 조건절 칼럼이 낫다.(과도한 인덱스 추가는 성능저하) insert, delete, update 구문이 자주 사용하지 않는 테이블
인덱스는
오늘 우리가 알아본 내용은 클러스터드 인덱스와 B-tree 구조에 대해 알아보았습니다.
2편에서 좀더 구체적인 B-tree 구조와 논클러스터드 인덱스에 대해 자세히 알아볼것이구요.
추가 개념으로 클러스터드 인덱스는 테이블당 한개만 설정 가능하다고 하였는데
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칼럼이 한쌍으로 클러스터드 인덱스로 설정됨을 확인할 수 있는
부분입니다.
추가개념까지 알아보았구요.
2편에서는 좀더 구체적인 B-tree구조와 클러스터드와 논클러스터드 인덱스의 개념에 대해 예시와 실습을 통해
알아보겠습니다.
반응형
from http://developer111.tistory.com/75 by ccl(A) rewrite - 2021-11-07 20:27:00