(5주차) Chapter 06

내용 정리

Chapter 06. 인덱스

06-1. 인덱스 개념을 파악하자

1) 인덱스 정의

 : 데이터를 빠르게 찾을 수 있도록 도와주는 도구

 : 실무에서는 현실적으로 인덱스 없이 데이터베이스 운영이 불가

 : 데이터를 찾을 때 인덱스 사용 여부에 따라 결과값의 차이는 없음.

2) 인덱스의 장단점

- 장점

 : SELECT 문으로 검색하는 속도의 향상

 : 그 결과 컴퓨터의 부담이 줄어 전체 시스템 성능이 향상

- 단점

 : 인덱스도 공간을 차지하기에 데이터베이스 안에 추가적인 공간이 필요

(대략 테이블의 10% 정도의 공간이 추가로 필요)

 : SELECT가 아닌 데이터의 변경 작업(INSERT, DELETE, UPDATE)이 자주 일어나면 오히려 성능이 나빠질 수 있음.

 : 데이터에 인덱스를 생성해 놓아도 인덱스를 사용해서 검색하는 것이 빠를지 아니면 전체 테이블을 검색하는지 빠를지 MySQL이 판단하여 사용하기 때문에 만약 인덱스를 사용하지 않는다면 사용하지도 않는 찾아보기를 만든 것이므로 공간을 낭비하는 일임.

3) 인덱스의 종류

3)-1. 클러스터형 인덱스(Clustered Index)

 : 영어사전처럼 책의 내용이 이미 알파벳처럼 순서대로 정렬되어 있는 것.

 : 어떤 열을 기본키(클러스터형 인덱스)로 지정하면 그 열을 기준으로 자동 정렬됨.

 : 기본키를 지정하면 자동으로 생성되며 따라서 테이블 하나만 만들 수 있음.

 : 이미 대용량의 데이터가 있는 상태에서 기본키를 지정하면 시간이 엄청 오래 걸릴 수 있음.

 : 또한 중복이 없는 열로 기본키를 변경하도록 유의해야 함.

 

-- 테이블 생성 시 클러스터형 인덱스 생성
USE market_db;
CREATE TABLE table1(
col1 INT PRIMARY KEY,
col2 INT,
col3 INT
);

-- 테이블 생성 후 클러스터형 인덱스 생성
ALTER TABLE member
ADD CONSTRAINT
PRIMARY KEY(mem_id);

-- 기본키가 있을 경우 클러스터형 인덱스 생성
ALTER TABLE member DROP PRIMARY KEY;
ALTER TABLE member
ADD CONSTRAINT
PRIMARY KEY(mem_name);

 

3)-2. 보조 인덱스(Secondary Index)

 : 찾아보기가 따로 있고, 찾아보기에서 해당 단어를 찾은 후에 옆에 표시된 페이지를 펼쳐야 실제 찾는 내용이 있는 것을 말함.

 : 고유키 지정 시 보조 인덱스가 생성됨.

 : 고유키가 하나의 테이블에 여러 개 있어도 되듯이 보조 인덱스도 여러 개 있어도 됨.

 : 보조 인덱스를 생성해도 데이터의 순서는 변경되지 않고, 별도로 인덱스가 생성되는 것임.

 : 중복을 허용하는 단순 보조 인덱스와 중복이 안 되는 고유 보조 인덱스로 구성됨.

 

-- 테이블 생성 시 보조 인덱스 생성
USE market_db;
CREATE TABLE table1(
col1 INT PRIMARY KEY,
col2 INT UNIQUE,
col3 INT UNIQUE
);

-- 테이블 생성 후 보조 인덱스 생성
ALTER TABLE member
ADD CONSTRAINT
UNIQUE(mem_id);

-- 보조 인덱스 삭제
ALTER TABLE table_name
DROP INDEX col_name;

 

4)  인덱스 정보 확인 : SHOW INDEX

SHOW INDEX FROM table1;

 

---

 

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

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

1)-1. 균형트리

 : 나무를 거꾸로 표현한 자료구조로, 트리에서 제일 상단의 뿌리를 루트, 줄기를 중간, 끝에 달린 잎을 리프라고 함.

 : 균형 트리에서 데이터가 저장되는 공간을 노드(node)라고 함.

 : MySQL에서 노드는 페이지(page)라고 부르며 최소 저장 단위로, 16Kbyte를 가짐.

 : 예를 들어, 데이터를 1건 입력해도, 1개의 페이지(16Kbyte)가 필요함.

 : 균형 트리로 구성되어 있지 않으면 전체 페이지를 검색하는 방법 밖에 없는데 데이터를 처음부터 끝까지 검색하는 것을 전체 테이블 검색(Full Table Scan)이라고 부름.

 : 균형트리는 무조건 루트 페이지부터 검색함.

 : 효율성은 검색한 데이터의 개수가 아닌 페이지 수에 달림.

1)-2. 페이지 분할

 : 새로운 페이지를 준비해서 데이터를 나누는 작업

 : 페이지 분할이 일어나면 MySQL이 느려지고, 너무 자주 일어나면 성능에 큰 영향을 미침.

 : 루트 페이지가 분할하게 되면 중간 페이지가 생김.

2) 인덱스의 구조

2)-1.  클러스터형 인덱스 구성하기

 (1) 행 데이터를 지정한 열로 정렬

 (2) 각 페이지의 인덱스로 지정된 열의 첫 번째 값을 가지고 루트 페이지로 만듦.

 : 인덱스 페이지의 리프 페이지는 데이터 그 자체임.(즉, 클러스터형 인덱스를 구성하면 데이터 페이지도 인덱스에 포함됨.)

 : 보조 인덱스에 비해 클러스터형 인덱스의 검색이 빠름.

2)-2. 보조 인덱스 구성하기

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

 (2) 별도의 장소에 인덱스 페이지가 생성(데이터의 위치는 페이지 번호 + #위치)

 

 

---

06-3. 인덱스의 실제 사용

1) 인덱스 생성 : CREATE INDEX

 : Primary key 문법을 사용하면 클러스터형 인덱스가, Unique 문법을 사용하면 보조 인덱스가 자동으로 생성

 : 그외 직접 인덱스를 생성하려면 CREATE INDEX 사용

 : CREATE INDEX로 생성되는 인덱스는 보조 인덱스

CREATE [UNIQUE] INDEX 인덱스_이름
ON 테이블_이름 (열_이름) [ASC|DESC]

 

2) 생성한 인덱스를 실제로 적용 : ANALYZE TABLE

 : 생성한 인덱스를 실제로 적용시키려면 ANALYZE TABLE문으로 먼저 테이블을 분석/처리해줘야 함.

3) 인덱스 제거 : DROP INDEX

DROP INDEX 인덱스_이름 ON 테이블_이름

 : 주의할 점은 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거 못함.

 : ALTER TABLE문으로 기본 키나 고유 키를 제거하면 자동으로 생성된 인덱스도 제거할 수 있음.

 : 인덱스를 제거할 때 보조 인덱스부터 제거하는 것이 더 좋음. 클러스터형 인덱스부터 제거하면 내부적으로 데이터가 재구성되기 때문.

 : 기본키-왜래키 관계의 경우 기본키를 제거하기 전에 외래키를 제거해야 함.

4) 인덱스 정보 확인 : SHOW INDEX

SHOW INDEX FROM member;

5) 인덱스의 크기 확인 : SHOW TABLE STATUS

 : Data_length는 클러스터형 인덱스(또는 데이터)의 크기를 Byte단위로 표기한 것임.

SHOW TABLE STATUS LIKE 'member';

6) 인덱스를 사용하지 않는 경우

 : WHERE문에서 열에 연산이 가해지면 인덱스를 사용하지 않음.

(ex. WHERE mem_number*2 >=14)

 : 대부분의 행을 확인해야 하는 경우처럼 인덱스를 사용하는 것이 오히려 비효율적일 때 인덱스를 사용하지 않음.

 

기본 미션

 

p. 310 인덱스 생성하고 key_name이 PRIMARY로 출력된 결과 화면 캡처하기

 

선택 미션

인덱스 생성, 제거하는 기본 형식 작성하기

1) 인덱스 생성하는 기본 형식

CREATE [UNIQUE] INDEX 인덱스_이름
ON 테이블_이름(열_이름) [ASC|DESC]

2) 인덱스 제거하는 기본 형식

DROP INDEX 인덱스_이름 ON 테이블_이름