4. SQL 고급 문법 - 인덱스

1. 인덱스란?

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

클러스터형 인덱스	특정 열을 PRIMARY KEY로 지정하면 자동으로 해당 열에 클러스터형 인덱스가 생성됨.
보조 인덱스	고유 보조 인덱스	특정 열을 UNIQUE 지정하면 자동으로 해당 열에 고유 보조 인덱스가 생성됨. 특정 열에 인덱스를 생성할 때 UNIQUE를 지정하면 고유 보조 인덱스가 생성됨.
	단순 보조 인덱스	특정 열에 인덱스를 생성할 때 UNIQUE를 지정하지 않으면 단순 보조 인덱스가 생성됨.

cf) 불필요하게 인덱스를 만들면 데이터베이스의 공간만 낭비하게 될 수도 있음.

 

2. 자동 지정되는 인덱스 생성하기

 

1) PRIMARY KEY로 클러스터형 인덱스 생성하기

<코드>

CREATE TABLE table1 (
	column1 INT,
    	column2 INT,
    	column3 INT
);

ALTER TABLE table1 ADD CONSTRAINT PRIMARY KEY (column1);

SHOW INDEX FROM table1;

<조회화면>

• 중복 허용하지 않음. (Non_unique false)
• NULL값 허용하지 않음. (Null false)

2) UNIQUE 속성으로 고유 보조 인덱스 생성하기

<코드>

CREATE TABLE table2 (
	column1 INT PRIMARY KEY,
    	column2 INT UNIQUE,
    	column3 INT UNIQUE
);
SHOW INDEX FROM table2;

<조회화면>

• 중복 허용하지 않음, (Non_unique false)
• NULL값 허용 (NULL true)

3. 인덱스 생성하기

CREATE [UNIQUE] INDEX index_name ON table_name (column_name);
ANALYZE TABLE table_name;
-- UNIQUE 붙이면 고유 보조 인덱스, 그렇지 않으면 단순 보조 인덱스.
-- 인덱스 생성 후 ANALYZE TABLE 해줘야 함.

• PRIMARY KEY, UNIQUE를 사용하면 자동으로 인덱스가 생성됨.
• 자동 생성이 아니라 직접 인덱스를 생성하려면 CREATE INDEX 문을 사용해야함.
• CREATE INDEX로 생성되는 인덱스는 보조 인덱스임.
• CREATE문 끝에 ASC, DESC를 입력하여 오름차순 혹은 내림차순으로 정렬할 수 있음.

DROP INDEX index_name ON table_name; -- 보조 인덱스의 삭제
ALTER TABLE member DROP PRIMARY KEY; -- 클러스터형 인덱스의 삭제

• 보조 인덱스의 경우 DROP INDEX를 이용하여 삭제함.
• 클러스터형 인덱스의 경우 ALTER TABLE DROP을 이용하여 삭제함.
• PRIMARY KEY의 경우 FOREIGN KEY와 연동되어 있을 시 FOREGIN KEY를 먼저 삭제해줘야 함.

4. 인덱스의 원리

• 인덱스는 데이터를 트리 형식으로 정리함.
• 트리의 노드 부분을 페이지 (page) 라고 부름.
• 페이지는 최소한의 저장 단위로 16Kbyte의 크기를 가짐.
• SELECT의 경우 탐색 횟수가 전제 탐색보다 줄어들어 속도가 빨라지지만, INSERT, DELETE, UPDATE는 페이지 분할로 인하여 오히려 성능이 저하될 수 있음.

1) 클러스터형 인덱스의 원리

클러스터형 인덱스 구조

• 루트페이지부터 리프페이지까지 따라 내려가면서 탐색을 진행함.
• 리프페이지가 곧 데이터페이지임.
• 데이터페이지도 인덱스에 포함됨. (정렬 되어 있음.)

2) 보조 인덱스의 원리

보조 인덱스 구조

• 루트 페이지부터 리프 페이지까지 따라 내려가면서 탐색을 진행함.
• 리프 페이지에는 데이터마다 페이지번호와 페이지번호 내부 위치가 기록되어 있음.
• 해당하는 페이지로 이동하여 데이터를 가져옴.
• 클러스터형 인덱스와 다르게 인덱스가 데이터페이지와 별도의 공간에 만들어짐,

5. 인덱스를 통한 탐색

•   WHERE을 사용하지 않을 경우 모든 테이블을 탐색함.
•   WHERE을 사용한 경우 MySQL 프로그램이 인덱스 탐색과 모든 테이블 탐색 중 어느것이 더 나을지 판단해 탐색함.
•  WHERE에 연산이 들어간 경우 모든 테이블을 탐색함.