[혼공SQL] chapter 6. 인덱스(2)

여정이·2024년 7월 30일

혼자 공부하는 SQL

목록 보기

19/28

📒 요약 : 클러스터형 인덱스와 보조 인덱스는 모두 내부적으로 균형 트리로 만들어진다. 트리 구조를 이해하면 인덱스가 내부적으로 어떻게 작동하는 지 이해할 수 있다.

인덱스의 내부 작동

1. 인덱스의 내부 작동 원리

인덱스의 내부 작동 원리를 이해하면 인덱스를 사용해야 할 경우와 사용하지 말아야 할 경우를 선택할 때 도움이 된다. 그러니 내부적으로 인덱스가 어떻게 작동하는 지 알아두도록 하자. 먼저 인덱스는 균형 트리의 형태를 가진다는 것에서부터 시작한다.

균형 트리의 개념

균형 트리 구조에서 데이터가 저장되는 공간을 노드라고 한다. 노드에는 루트 노드와 리프 노드, 그리고 중간 노드가 있는데, 루트 노드는 노드들 중 가장 상위에 있는 노드를 말했다. 모든 출발은 루트 노드에서 시작한다. 리프 노드는 가장 마지막에 있는 노드를 의미한다. 루트 노드와 리프 노드의 중간에 있는 노드들은 중간 노드라고 부른다. SQL에서는 이 노드들을 페이지라고 부른다. 페이지는 최소한의 저장 단위로, 16Kb의 크기를 가진다. 아주 작은 크기의 데이터를 저장하려고 해도 무조건 1개의 페이지가 필요하다.

균형 트리는 데이터를 검색할 때, 즉 SELECT 문을 이용하여 데이터를 조회할 때 뛰어난 성능을 보인다. 인덱스를 사용하지 않은 데이터베이스에서 데이터를 검색할 때에는 모든 데이터를 처음부터 끝까지 찾아봐야 하지만, 균형 트리에서 데이터를 검색할 경우 균형된 트리 구조 안에서 루트 페이지부터 시작하여 원하는 경로를 따라 이동하여 데이터를 효율적으로 검색할 수 있다.

균형 트리의 페이지 분할

페이지 분할이란, 새로운 페이지를 준비해서 데이터를 나누는 작업을 말한다. 페이지 분할이 일어나면 데이터 변경 작업 (INSERT, UPDATE, DELETE)을 할 때 성능이 나빠진다. 특히 INSERT 작업을 할 때 이 성능 저하가 두드러지게 발생한다. 인덱스 구조가 없는 데이터베이스에 있는 데이터가 변경될 때에는 해당 데이터에 대한 정보만 변경되지만, 트리 구조글 가진 데이터베이스의 데이터가 변경될 때에는 새로 페이지를 할당하거나, 다른 페이지에 있는 데이터가 영향을 받아 데이터의 구조가 변경될 수 있다. 이러한 과정을 거치기 때문에 데이터 변경 작업 속도가 느리다.

2. 인덱스의 구조

이번에는 인덱스 구조를 통해 인덱스를 생성하면 왜 데이터가 정렬되는지, 어떤 인덱스가 더 효율적인지 알아보자.

클러스터형 인덱스 구성하기

우선 아래와 같이 인덱스 없이 테이블을 생성하고 데이터를 입력해보자.

USE market_db;
CREATE TABLE cluster  -- 클러스터형 테이블 
( mem_id      CHAR(8) , 
  mem_name    VARCHAR(10)
 );
INSERT INTO cluster VALUES('TWC', '트와이스');
INSERT INTO cluster VALUES('BLK', '블랙핑크');
INSERT INTO cluster VALUES('WMN', '여자친구');
INSERT INTO cluster VALUES('OMY', '오마이걸');
INSERT INTO cluster VALUES('GRL', '소녀시대');
INSERT INTO cluster VALUES('ITZ', '잇지');
INSERT INTO cluster VALUES('RED', '레드벨벳');
INSERT INTO cluster VALUES('APN', '에이핑크');
INSERT INTO cluster VALUES('SPC', '우주소녀');
INSERT INTO cluster VALUES('MMU', '마마무');

만약 1개의 페이지에 4개의 행이 입력된다고 가정한다면, 이 데이터는 10개의 행을 가지고 있으므로 총 3개의 페이지에 입력될 것이다. 아직은 인덱스가 없는 상태임을 유의하자.

그림1. 인덱스가 없는 테이블

이제 이 테이블에서 데이터가 정렬된 순서를 확인해보면 입력된 순서와 동일하게 보인다. 그런데 이 테이블의 mem_id에 PK 제약조건을 걸어 클러스터형 인덱스를 구성한다면 6-1장에서 배웠던 것처럼 클러스터형 인덱스가 생성되어 데이터가 알파벳 순서대로 정렬되어 있음을 확인할 수 있다.

ALTER TABLE cluster
    ADD CONSTRAINT 
    PRIMARY KEY (mem_id);

SELECT * FROM cluster;

그림2. 클러스터형 인덱스가 생성된 테이블

이제 이 테이블에는 균형 트리 형태의 인덱스가 생성되었다. 먼저 클러스터형 인덱스를 구성하기 ㅜ이해 행 데이터를 지정한 열로 정렬한다. 그리고 각 페이지의 인덱스로 지정된 열의 첫 번째 값을 가지고 루트 페이지를 만든다.

보조 인덱스 구성하기

이번에는 동일한 데이터로 보조 인덱스를 만들어 보자.

USE market_db;
CREATE TABLE second  -- 보조 인덱스 테이블 
( mem_id      CHAR(8) , 
  mem_name    VARCHAR(10)
 );
INSERT INTO second VALUES('TWC', '트와이스');
INSERT INTO second VALUES('BLK', '블랙핑크');
INSERT INTO second VALUES('WMN', '여자친구');
INSERT INTO second VALUES('OMY', '오마이걸');
INSERT INTO second VALUES('GRL', '소녀시대');
INSERT INTO second VALUES('ITZ', '잇지');
INSERT INTO second VALUES('RED', '레드벨벳');
INSERT INTO second VALUES('APN', '에이핑크');
INSERT INTO second VALUES('SPC', '우주소녀');
INSERT INTO second VALUES('MMU', '마마무');

일단 인덱스가 없으므로 순서대로 데이터가 입력된다. 첫 번재 페이지의 첫 번째 데이터는 트와이스, 두 번째 페이지의 첫 번재 데이터에는 소녀시대가 들어간다. 이번에는 UNIQUE 제약조건을 이용하여 보조 인덱스를 생성하고, 데이터를 확인해보자.

그림3. 인덱스가 없는 테이블

ALTER TABLE second
    ADD CONSTRAINT 
    UNIQUE (mem_id);

SELECT * FROM second;

그림4. 보조 인덱스를 생성한 테이블 역시 마찬가지로 데이터를 입력한 순서대로 출력이 된다. 보조 인덱스가 생성되었음에도 똑같이 출력되는 이유는 다음와 같다. 먼저 보조 인덱스는 데이터 페이지를 건드리지 않는다. 그리고 별도의 장소에 인덱스 페이지를 생성한다.

앞서 보조 인덱스는 책의 찾아보기와 같다고 했다. 찾아보기가 있다고 해서 책의 본문이 바뀌지 않는 것과 같은 원리라고 이해하자. 보조 긴덱스 역시 찾아보기처럼 별도의 공간에 만들어진 것이다. 보조 인덱스에서 데이터의 위치는 페이지 번호 +#위치로 기록되어 있다. TWC를 예로 들면 1000번 페이지의 첫 번째에 데이터가 있다고 기록될 것이다. (1000#1)