[13주 5일차] 인덱스

 

Chapter06. 인덱스

 

 

 

06-1. 인덱스의 개념

 

인덱스는 데이터를 빠르게 찾을 수 있도록 도와주는 도구이다.

인덱스에는 클러스터형 인덱스와 보조인덱스가 있다.

클러스터형 인덱스는 기본키(PK)로 지정하면 자동으로 생성되며 테이블 당 하나만 만들 수 있다. (PK로 지정한 열을 기준으로 자동 정렬함)

보조인덱스는 고유키(unique)로 지정하면 자동 생성하며 여러 개를 만들 수 있지만 정렬은 되지 않는다.

위와 같은 이유로 인덱스는 pk와 unique와 밀접한 관계를 가진다.

 

 

 

 

인덱스의 장점과 단점

 

장점

• select 문으로 검색하는 속도가 매우 빨라진다.
• 컴퓨터의 부담이 줄어들어 전체 시스템의 성능이 향상된다.

 

단점

• 인덱스도 공간을 차지하기 떄문에 데이터베이스 안에 추가적인 공간이 필요한다.
• 처음에 인덱스를 만드는데 시간이 오래 걸릴 수 있다.
• select가 아닌 데이터 변경 작업(insert, update, delete)이 자주 일어나면 오히려 성능이 나빠질 수도 있다.

 

 

 

 

인덱스의 종류

인덱스는 테이블의 열(컬럼) 단위에 생성되면 하나의 열에는 하나의 인덱스를 생성할 수 있다.

인덱스는 클러스터형 인덱스와 보조 인덱스로 나눌 수 있다.

 두개 모두 자동으로 인덱스가 생성되는데 차이점이 있다.

클러스터형 인덱스는 해당컬럼이 기본키로 설정되었을 경우 생성되고 보조 인덱스는 해당 컬럼이 고유키로 설정되었을때 생성된다.

 

 

 

 

해당 컬럼이 기본 키인 경우 생성되는 인덱스 보기

 

테이블을 생성한 후 col1이라는 컬럼을 pk로 지정해주었다.

그 후 show 명령어를 사용하여 인덱스를 조회했다.

 

use market_db;

create table table1(col1 int primary key, col2 int, col3 int);

show index from table1;

 

 

조회 결과 primary key 가 자동으로 생성된 것을 볼 수 있었고 pk로 지정된 컬럼의 이름은 예상했던 것과 같이 col1이었다.

 

 

 

 

 

 

해당 컬럼이 고유키와 pk일 경우 인덱스 생성

 

테이블을 생성한 후 컬럼에 각각 pk, 고유키 설정을 했다.

pk는 테이블 당 하나만 존재할 수 있으므로 이전과 같이 col1에 설정했고

고유키는 하나의 테이블에 여러개가 존재할 수 있으므로 나머지를 모두 고유키로 설정해주었다.

 

create table table2(col1 int primary key, col2 int unique, col3 int unique);

show index from table2;

 

설정이 끝난 후 인덱스를 조회한 결과

col1은 pk로 지정되어 인덱스가 생성되었고 col2와 col3도 보조 인덱스가 생성된 것을 볼 수 있었다.

 

 

 

 

 

 

 

 

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

위에서 클러스터형 인덱스는 기본 키로 지정하면 자동 생성된다는 것을 보았다.

그리고 테이블 당 하나만 생성된다는 특징 또한 보았다.

이외에도 다른 특성들도 존재한다.

클러스터형 인덱스는 자동으로 생성 뿐만 아니라 정렬된다.

컬럼을 기본 키로 지정하면 그 컬럼을 기준으로 자동으로 정렬한다.

예를 들어 영어사전과 같다. 영어사전은 A-Z or 가-하 로 정렬되어 찾기 쉽도록 하기 때문에 클러스터형 인덱스와 같은 특징을 가지고 있다.

 

 

 

 

 

 

자동으로 정렬되는 클러스트형 인덱스 예시

 

먼저 아무것도 지정하지 않은 테이블을 생성해주었다.

그 후 데이터를 삽입해주었다.

drop table if exists buy, member;
create table member
( member_id char(8),
mem_name varchar(10),
mem_number int,
addr char(2) );

insert into member values('TWC', '트와이스', 9, '서울');
insert into member values('BLK', '블랙핑크', 4, '경남');
insert into member values('WMM', '여자친구', 6, '경기');
insert into member values('OMY', '오마이걸', 7, '서울');

select * from member;

 

 

테이블을 조회한 결과 아래와 같은 테이블을 볼 수 있었다.

 

 

 

생성한 테이블의 컬럼에 primary key 설정을 해주었다.

그리고 테이블 전체를 조회했다.

alter table member add constraint primary key(member_id);
select * from member;

 

그럼 아까와는 정렬이 다르 것을 확인할 수 있는데

정렬이 바뀐 이유는 pk때문이다.

pk를 지정하면 클러스터형 인덱스가 생성되고 자동으로 정렬되기 때문에 pk로 지정한 member_id를 기준으로 a ~ z 순서로 자동 정렬이 된다.

 

 

 

 

 

이전에 설정했던 pk를 제거한 후 mem_name을 pk로 새로 설정해주었다.

그 후 전체 테이블을 조회했다.

alter table member drop primary key;

alter table member add constraint primary key(mem_name);
select * from member;

 

 

그 결과 이전과는 다르게 정렬된것을 볼 수 있었다.

mem_name을 기준으로 정렬이 바뀌었다.

 

 

 

 

해당 테이블에 데이터 삽입 후 정렬 확인해보았다.

그 후 전체 테이블을 조회했다.

insert into member values('GRL', '소녀시대', 8, '서울');
select * from member;

 

 

조회 결과 가나다 순으로 삽입된 데이터가 정렬된 것을 볼 수 있었다.

 

 

 

 

 

 

 

보조 인덱스(Secondary Index)

보조 인덱스는 컬럼을 고유키로 지정하면 자동으로 생성된다.

그리고 한 테이블에 여러개를 설정할 수 있다. 하지만 정렬은 자동으로 되지 않는다.

예를 들어 보조인덱스는 정렬은 되지 않지만 특정단어로 찾을 수 있는 책 맨뒷자의 인덱스 찾아보기 와 같다.

특정 단어로 빠른 검색을 돕는다.

 

 

 

 

 

정렬되지 않는 보조 인덱스 예시

 

아무것도 설정하지 않은 테이블을 생성해주었다.

그후 데이터를 삽입하고 전체 테이블을 조회했다.

drop table if exists member;

create table member
(mem_id char(8),
mem_name varchar(10),
mem_number int,
addr char(2));

insert into member values('TWC', '트와이스', 9, '서울');
insert into member values('BLK', '블랙핑크', 4, '경남');
insert into member values('WMM', '여자친구', 6, '경기');
insert into member values('OMY', '오마이걸', 7, '서울');

select * from member;

 

아무것도 설정 안한 테이블의 조회 결과이다.

 

 

 

 

 

 

결과를 확인한 후 테이블 설정을 바꾸기위해 alter tabel 명령어를 사용했다.

mem_id를 고유키로 설정한 후 정렬 확인을 하기 위해 전체 테이블을 조회했다.

 

alter table member add constraint unique(mem_id);
select * from member;

 

앞서 아무것도 설정하지 않은 테이블과 같은 조회결과를 보여주었다.

 

 

 

그리고 같은 테이블에 유니크 설정을 추가해보았다.

그 결과 오류 없이 고유키가 추가 설정되는 것을 볼 수 있었다.

그 후 전체 테이블을 조회했는데 그 결과는 앞선 결과들과 동일 했다.

alter table member add constraint unique(mem_name);
select * from member;

 

 

 

 

 

데이터를 추가로 삽입해주었다.

추가된 데이터는 맨 마지막에 삽입된 것을 볼 수 있었다.

이것으로 인해 보조인덱스는 정렬에 어떠한 영향을 미치지않는다는 것을 알 수 있었다.

 

insert into member values('GRL', '소녀시대', 8, '서울');
select * from member;

 

 

 

 

 

 

 

06-2. 인덱스의 내부 작동

 

인덱스는 내부적으로 균형 트리로 만들어진다.

균형트리(B-tree)는 자료구조에 나오는 범용적으로 사용되는 데이터 구조이다.

트리에서 제일 상단의 뿌리를 루트, 줄기를 중간, 끝에 달린 잎을 리프(데이터를 담는다)라고 부른다.

쉽게 나무를 거꾸로 표현한 자료구조라고 생각하면된다.

 

 

 

 

균형 트리

균형 트링 구조에서 데이터가 저장되는 공간을 노드라고한다.

노드는 mysql에서는 페이지라고 부른다.

페이지는 최소 저장 단위로 16Kbyte 크기를 가진다. 만약 데이터를 하나만 입력해도 하나의 페이지가 필요하기 때문에 16Kbyte가 필요하다.

 

 

 

루트 노드는 가장 상위 노드를 말한다. 모든 출발은 루트 노드에서 시작된다.

리프 노트는 제일 마지막에 존재하는 노드를 말한다.

그림은 2단계만 표현되었지만 데이터가 많으면 3단계 혹은 그 이상이 될 수 있다.

루트 노드와 리프 노드의 중간에 끼인 노드들은 중간 노드라고 부른다.

 

균형 트리에서 검색하는 절차를 보면 무조건 루트 페이지부터 검색을 한다.

모든 데이터는 정렬이 되어 있기 때문에 MMM을 검색하고자하는 경우 먼저 루트 페이지의 AAA , FFF, LLL 3개를 읽은 다음에 나온다 그렇기 때문에 M이 위치하는 곳은 LLL일것이다. 

세번째 LLL로 이동해 리프페이지에서 MMM을 찾으면 된다.

총 페이지 2개를 읽는다. 이것으로 보아 균형 트리가 아닌 구조보다 더욱 경로를 짧게 하여 빠르게 검색할 수 있었다.

 

 

 

 

 

 

균형 트리의 페이지 분할

 

select를 제외하고 데이터 변경 작업을 할 시 성능이 나빠진다.

그 이유는 페이지 분할때문이다.

페이지 분할은 새로운 페이지를 준비해서 데이터를 나누는 작ㅇ버을 말한다.

 

III라는 데이터를 입력했다고 하면 정렬시 JJJ보다 앞에 위치하므로 JJJ가 한칸 이동을 한다.

그 후 III가 삽입된다.

그럼 아래와 같이 페이지 분할이 이루어진다.

 

 

 

 

 

 

 

이후로 GGG라는 데이터를 추가로 삽입해주었다고 하면 한칸을 이동해야하는데

4칸이 꽉 차서 이동할 곳이 없다.

그럴때에 페이지 분할 작업이 이루어져 새로운 페이지가 만들어진다.

III JJJ가 있는 새로운 리프 페이지가 만들어진다.

그럼 리프 페이지의 첫번째 데이터를 담고 있는 루트 페이지에도 III를 추가해주어야한다.

 

 

 

 

 

마지막으로 PPP 와 QQQ를 입력해주었다.

입력을 하면 또 4자리가 꽉 차기 때문에 페이지 분할을 해 새로운 페이지가 만들어진다.

그럼 루트 페이지에 데이터를 입력하는데

기존의 루트 페이지에 데이터 자리가 꽉 차 페이지 분할로 하나의 페이지가 더 생긴다.

그럼 루트 페이지는 두개가 되므로 더이상 루트 페이지가 아닌 중간 페이지가 된다.

그리고 중간 페이지의 첫번째 데이터를 담는 새로운 루트가 생성된다.

 

 

 

 

* 인덱스가 저장될때 실제로는 주소로 위치를 찾는다.

이때 보조인덱스 동작 원리를 알아둘 필요가 있는데

보조 인덱스는 우리 눈에는 정렬이되지않은것처럼 보이지만 페이지 번호 + # 위치로 기록되어 있다.

 

 

 

* 인덱스 검색

인덱스 검색할때는 where 절에 pk 혹은 고유키(unique)를 검색해야한다.

겹치지않는 유일값이기 떄문에 검색 속도를 더 높일 수 있다.