1. Index(인덱스)란?

• Index(인덱스)는 SELECT를 사용하여 DB를 조회할 때 성능 향상을 위한 기능.

• 일반적으로 1개의 컬럼에 1개의 Index(인덱스)를 사용함.

  • 1개의 컬럼에 여러 개의 인덱스를 생성할 수도 있고, 여러 컬럼을 묶어서 인덱스를 생성할 수도 있음.

---

1-1. 종류.

• 클러스터형 인덱스(Clustered Index)
  • 기본 키(PK)로 지정하면 자동 생성됨.
  • 테이블에 1개만 만들 수 있음.
    • 테이블에 기본 키(PK)는 1개만 지정할 수 있기 때문.
  • 해당 컬럼을 기준으로 자동 정렬됨.
  • Ex) 영어단어 사전.
• 보조 인덱스(Secondary Index)
  • 고유 보조 인덱스 (Unique Secondary Index), 단순 보조 인덱스 (Simple/Non-Unique Secondary Index)로 나눌 수 있음.
  • CREATE INDEX 또는 고유 키(UK)를 이용하여 생성.
    • 고유 키(UK)로 지정하면 자동 생성됨.
  • 테이블에 여러 개 만들 수 있음.
    • 보조 인덱스를 생성할 때마다 DB의 공간을 차지하게 되므로 꼭 필요한 경우에만 적절히 생성하는 것이 좋음.
  • 자동 정렬 되지 않음.
  • Ex) 책 뒤쪽에 있는 찾아보기.

---

1-2. 장단점.

• 장점

  • SELECT 성능 향상.
  • 전체 시스템 성능 향상.

• 단점

  • DB 추가적인 저장 공간 필요.
    • Index를 만들 때 추가적인 저장공간을 사용하기 때문. (테이블 크기의 약 10%)
  • 인덱스 생성시 시간 소요.
  • 쓰기(INSERT, UPDATE, DELETE)작업이 자주 발생하면 오히려 성능이 저하됨.

---

1-3. 예시.

---

1-3-1. 클러스터 인덱스.

CREATE TABLE tmp (
col1 INT PRIMARY KEY,
col2 INT,
col3 INT
);

SHOW INDEX FROM tmp;

• SHOW INDEX: 인덱스 정보 확인.
  
  • Key_name: PRIMARY
    • PK를 통해 자동으로 생성된 인덱스. 즉, 클러스터 인덱스.
  • Non_unique: 0 (False)
    • !!UNIQUE = UNIQUE. 즉, 중복 허용 X.
  • Column_name: col1
    • col1 컬럼에 인덱스가 생성되었음.

---

CREATE TABLE tmp (
    code CHAR(5),
    name VARCHAR(50),
    age INT,
    status CHAR(1)
);
INSERT INTO tmp (code, name, age, status) VALUES
('A0005', 'Kim', 25, 'Y'),
('A0003', 'Lee', 30, 'Y'),
('A0004', 'Park', 28, 'N'),
('A0002', 'Choi', 35, 'Y'),
('A0001', 'Jung', 22, 'N');

• ↑ 위 상황에서 code 열을 PK로 지정해서 클러스터 인덱스를 생성.

ALTER TABLE tmp ADD CONSTRAINT
PRIMARY KEY (code);

• ↑ 위 처럼 PK로 지정된 열, 즉 클러스터 인덱스가 생성된 컬럼이 자동으로 정렬되었음.

---

1-3-2. 보조 인덱스.

DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp (
col1 INT PRIMARY KEY,
col2 INT UNIQUE,
col3 INT UNIQUE
);

SHOW INDEX FROM tmp;

• SHOW INDEX: 인덱스 정보 확인.
  
  • 고유 키(UK) 또한 인덱스가 자동으로 생성됨.
    • 고유 키(UK)로 생성된 인덱스는 보조 인덱스.
  • Key_name: col2, col3
    • Key_name에 컬럼명이 써 있으면 보조 인덱스
  • Non_unique: 0 (False)
    • !!UNIQUE = UNIQUE. 즉, 중복 허용 X.
  • 고유 키(UK)는 여러 개 지정할 수 있으므로 보조 인덱스를 여러 개 만들 수 있음.

---

CREATE TABLE tmp (
    code CHAR(5),
    name VARCHAR(50),
    age INT,
    status CHAR(1)
);
INSERT INTO tmp (code, name, age, status) VALUES
('A0005', 'Kim', 25, 'Y'),
('A0003', 'Lee', 30, 'Y'),
('A0004', 'Park', 28, 'N'),
('A0002', 'Choi', 35, 'Y'),
('A0001', 'Jung', 22, 'N');

• ↑ 위 상황에서 code 열을 UK로 지정해서 보조 인덱스를 생성.

ALTER TABLE tmp ADD CONSTRAINT
UNIQUE KEY (code);

• ↑ 위 처럼 UK로 지정된 열, 즉 보조 인덱스가 생성된 컬럼은 자동으로 정렬되지않음.

---

2. Index 내부 동작.

---

2-1. B-tree

• 클러스터 인덱스, 보조 인덱스 둘 다 내부적으로 균형 트리(B-tree)로 만들어짐.
  • 균형 트리(B-tree)에서 데이터 탐색은 항상 루트 노드에서부터 시작함.
• 자료구조 트리(tree)를 설명할 때 각 잎을 노드라고 부르는데 MySQL에는 이를 페이지(page)라고 부름.
  • 페이지(page)는 최소한의 저장 단위로, 16Kbyte(16384byte)크기를 가짐.
    즉, 데이터를 1건만 저장하려고 해도 1개 페이지(16Kbyte)가 필요함.

• ↑ 트리 구조를 이루지 못하고 위 같은 상황에서 hhh까지 도달하려면
  • aaa -> bbb -> ccc -> ... -> ggg -> hhh. 총 3개 페이지의 데이터를 전부 조회해야 됨.
    • 이처럼 데이터를 처음부터 끝까지 검색하는 것을 전체 테이블 검색(Full Table Scan)이라고 함.

• ↑ 이처럼 B-tree 구조를 일 경우 hhh까지 도달하려면
  • aaa -> ddd -> ggg -> hhh. 2개의 페이지만 조회하면 됨.

---

2-2. 페이지 분할.

• 앞서 1-2 인덱스의 장단점에서 쓰기(INSERT, UPDATE, DELETE)작업이 자주 발생하면 오히려 성능이 저하됨.라고 말했는데 그 이유가 페이지 분할이라는 작업이 발생하기 때문임.
  • 페이지 분할이란? 새로운 페이지를 생성해서 데이터를 나누는 작업임.

• ↑ 위 이미지에서 1개의 데이터를 INSERT하면 빈칸을 채워넣으면 되지만 복수 개의 데이터를 추가로 INSERT하다면?
  • 페이지 분할 작업이 발생할 수 밖에 없음.

• ↑ 기존 구조에서 iii, ggg 2개의 데이터가 INSERT 됐을 경우.
  • 페이지 분할이 1회 발생하였음.

• ↑ ooo, ppp 데이터가 추가로 INSERT 됐을 경우.
  • 3개의 페이지가 추가로 생성됐고 페이지 분할이 2회 발생했음.
• 이를 통해 인덱스를 구성하면 왜 쓰기작업(INSERT, UPDATE, DELETE), 특히 INSERT 작업이 느려지는 지 알 수 있음.

---

3. 인덱스 사용.

---

3-1. 생성 및 제거.

• PK 지정시 클러스터 인덱스, UK 지정시 보조 인덱스가 자동 생성됨.
  • PK 또는 UK 지정해서 자동으로 생성된 인덱스는 DROP INDEX로 제거하지 못함.
    • ALTER TABLE문을 사용해서 PK, UK를 제거하면 자동으로 생성된 인덱스를 제거할 수 있음.
  • 테이블에 클러스터 인덱스와 보조 인덱스 둘 다 있을 경우
    인덱스를 제거할 때 보조 인덱스를 먼저 제거하는 게 좋음.
• 인덱스 생성 및 제거.
  • 생성: CREATE INDEX 문.
    • CREATE INDEX를 사용해서 만든 인덱스는 보조 인덱스임.
  • 제거: DROP INDEX 문.
    • CREATE INDEX를 사용해서 만든 인덱스를 제거할 수 있음.

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

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

• CREATE [UNIQUE] INDEX는 고유 보조 인덱스 (Unique Secondary Index)를 생성할 수 있는데
  이 인덱스를 생성하려면 기존에 입력된 값들 중 중복이 있으면 안됨.
  • 또한 고유 인덱스 생성 후 입력되는 데이터와는 중복될 수 없음.

---

3-2. 사용.

• 인덱스 사용여부는 Execution Plan창을 확인. (MySQL Workbench 기준)

• 인덱스를 사용하려면 인덱스가 생성된 컬럼명이 SQL 문에 있어야함.

  • SELECT에 사용 됐다고 인덱스를 사용하진 않음.
  • WHERE절에 인덱스가 생성된 컬럼명을 사용하면 인덱스를 사용하여 조회함.
    • WHERE절에 인덱스가 생성된 컬럼명에 연산을 할 경우 인덱스를 사용하지 않음.

• 인덱스가 있더라도 MySQL이 인덱스를 사용해서 조회하는 것보다 전체 테이블 검색(Full Table Scan)이 낫다고 판단할 시 Full Table Scan을 통해 조회함.

---

3-2-1. Ex

CREATE TABLE tmp (
    code CHAR(5),
    name VARCHAR(50),
    age INT,
    status CHAR(1)
);
ALTER TABLE tmp ADD CONSTRAINT
PRIMARY KEY (age);

SELECT * FROM tmp;

SELECT age FROM tmp;

SELECT age FROM tmp
WHERE age = 22;

SELECT age FROM tmp
WHERE age > 25;

SELECT age FROM tmp
WHERE age*2 > 25;

• WHERE 절에 인덱스가 생성된 컬럼명에 연산을 할 경우 인덱스를 사용하지 않음.

SELECT age FROM tmp
WHERE age >= 15*2;

• WHERE 절에 연산이 사용되었지만 인덱스가 생성된 컬럼명에 사용한 것이 아니므로 인덱스가 사용되었음.

---

4. 효과적으로 사용.

---

4-1. 인덱스는 컬럼 단위로 생성.

• 인덱스는 컬럼 단위로 생성됨.

• 1개의 열에 2개 이상의 인덱스를 만들 수 있고, 2개 이상의 열을 묶어서 인덱스로 만들 수도 있지만
  일반적으로 1개의 열에 1개의 인덱스를 사용함.

---

4-2. WHERE 절에 사용되는 열에 인덱스.

• WHERE 절에 사용되는 열에 인덱스를 만들어야함.

• SELECT 할 때 WHERE 절의 조건에 인덱스가 생성된 컬럼이 나와야 인덱스를 사용함.
  • 그 외에 다른 컬럼에 인덱스를 만드는 건 일반적으로 낭비가 됨.

---

4-3. 자주 사용해야 가치가 있음.

• WHERE 절에 사용되더라도 자주 사용해야 가치가 있음.

• Ex) 인덱스를 사용해서 SELECT 성능이 향상 됐다 하더라도
  해당 SELECT 문을 1년에 1, 2회 정도만 사용하고 그 외에는 전부 INSERT일 경우 오히려 인덱스로 인해 성능이 나빠지는 결과로 이어짐.

---

4-4. 중복이 많은 열은 인덱스 효율 낮음.

• 데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없음.

• 컬럼에 들어가는 데이터의 종류가 몇 가지 되지 않는다면 인덱스가 별 효과를 내지 못함.

• Ex) 성별(F/M)이나 대중교통 수단 등 종류가 제한된 것에는 인덱스를 만들어도 큰 효과가 없음.

---

4-5. 클러스터 인덱스는 테이블당 1개.

• 클러스터 인덱스는 테이블당 1개만 생성할 수 있음.

• 클러스터 인덱스(PK 지정)는 페이지(page)를 읽는 수가 보조 인덱스보다 적기 때문에 성능이 더 좋음.
  • 따라서 SELECT할 때 가장 많이 사용되는 컬럼에 지정하는 것이 효과적임.

---

4-6. 사용 안 하는 인덱스는 제거.

• 사용하지 않는 인덱스는 제거함.

• WHERE절 조건에 사용되지 않는 열의 인덱스는 제거할 필요가 있음.
  • DB 공간을 확보할 수 있음 & 데이터 입력시 발생되는 부하도 많이 줄일 수 있음.

---

5. 참고.

• 정재화 - 데이터베이스시스템
• 우재남 - 혼자 공부하는 SQL