[혼공] 6-2장 인덱스의 내부 작동

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

 

균형 트리 (Balanced tree)

 

 - 클러스터형 인덱스와 보조 인덱스는 모두 내부적으로 균형 트리로 만들어짐

 - 균형 트리는 루트 페이지부터 검색

 - 노드(node) : 데이터가 저장되는 공간

 - 루트 노드(root node) : 노드의 가장 상위 노드 

 - 리프 노드 (leaf node) : 제일 마지막에 존재하는 노드 

 - 중간 노드 : 루프노드와 리프 노드의 중간에 끼인 노드

 - MySQL에서는 노드를 페이지(page)라고 부름

 - 페이지 : 최소한의 저장단위, 16Kbyte

 

균형 트리의 페이지 분할

 

- 인덱스를 만들면 SELECT의 속도를 향상시킬 수 있음

- BUT 페이지 분할 작업으로 데이터 변경 작업(INSERT,UPDATE,DELETE)시 성능이 나빠짐

 

 

2. 인덱스의 구조 

 

1) 클러스터형 인덱스 구성

 

- 인덱스 없는 테이블 구성

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','마마무');

SELECT * FROM cluster;

- 입력된 순서와 동일한 순서 출력

 

- 테이블의 mem_id에 클러스터형 인덱스 구성

- mem_id를 Primary Key로 지정 

ALTER TABLE cluster
    ADD CONSTRAINT
    PRIMARY KEY (mem_id);
SELECT * FROM cluster;

- 실제 데이터는 데이터 페이지가 정렬되고 균형 트리 형태의 인덱스가 형성됨

- 클러스터형 인덱스를 구성하기 위해 행 데이터를 지정한 열로 정렬

- 각 페이지의 인덱스로 지정된 열의 첫 번째 값을 가지고 루트 페이지를 만듬

- 인덱스 페이지의 리프 페이지는 데이터 그 자체 

 

 

2) 보조 인덱스 구성 

 

- mem_id열에 UNIQUE를 지정하여 보조 인덱스 생성

USE market_db;
DROP TABLE IF EXISTS second;
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','마마무');

ALTER TABLE second
	ADD CONSTRAINT
    UNIQUE (mem_id);
SELECT * FROM second;

- 보조 인덱스를 생성하였는데도 입력한것과 순서 동일

 

- 보조 인덱스는 데이터 페이지를 건드리지 않음

- 별도의 장소에 인덱스 페이지 생성

- 보조 인덱스를 생성해도 데이터 페이지는 변경되지 않음

- 인덱스 페이지의 리프 페이지에 인덱스로 구성한 열을 정렬 

- 데이터의 위치는 페이지 번호 + #위치로 기록