[SQL] 인덱스(Index)의 개념과 구조를 알아보자.

1. 인덱스(Index)

1-1. 인덱스란?

Index란 테이블의 조회(SELECT) 속도를 높여주는 자료구조이다. Index를 사용하여 테이블에 저장된 데이터의 위치를 빠르게 찾을 수 있다. Index를 사용하지 않는 경우 Full Table Scan이 발생하지 때문에 성능이 저하된다. Full Table Scan 이란 테이블의 첫 번째 행부터 마지막행까지 모두 탐색하는 것을 의미한다.

그러나 반대로 Index를 사용하는 경우 데이터 쓰기 작업(UPDATE, INSERT, DELETE)의 속도는 저하된다. 쓰기 작업이 발생하고 B-Tree 구조를 재조정해야 하기 때문이다.

1-2. 인덱스 특징

• 장점
  • 데이터 조회(SELECT) 성능을 개선할 수 있다.
• 단점
  • 조회가 아닌 데이터 변경 작업(UPDATE, INSERT, DELETE)가 자주 일어나면 성능이 저하된다

 

2. 인덱스(Index) 종류

인덱스는 클러스터형 인덱스(Clustered Index)와 보조 인덱스(Secondary Index)로 구분한다.

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

• 테이블당 1개만 존재한다.
• 기본 키(Primary Key)로 지정한 컬럼이 클러스터형 인덱스가 된다.

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

• 한 테이블에 여러 개 생성할 수 있다.
• UNIQUE 속성을 사용하는 컬럼은 자동으로 보조 인덱스가 생성된다.
• CREATE INDEX 문으로 직접 보조 인덱스 생성 가능하다.

 

3. 인덱스 구조

3-1. B-Tree를 사용하여 인덱스를 관리한다.

인덱스는 B-Tree(Balanced Tree, 균형 트리) 자료 구조를 사용한다. B-Tree는 Root 노드, Branch 노드 그리고 Leaf 노드로 이루어져 있다. Root → Branch → Leaf 노드 순으로 탐색을 이어나간다. MySQL에서는 노드 대신에 페이지라고 부른다.

• Root(루트) 노드
  • 트리의 최상단에 위치하며 탐색의 시작점이다.
  • 컬럼 값과 자식 노드로 가는 포인터를 가지고 있다.
• Branch(중간) 노드
  • 루트 노드와 리프 노드에 중간에 위치하여, 리프 노드로 가기 위해 경유하는 노드이다.
  • 컬럼 값의 범위를 좀 더 세분화하여 탐색을 이어나간다.
  • 루트 노드와 마찬가지로 컬럼 값과 자식 노드로 가는 포인터를 가진다.
• Leaf(리프) 노드
  • 트리의 최하단에 위치하며 탐색의 종료점이다.
  • 트리의 마지막 지점이기 때문에 자식의 주소를 갖는 게 아닌, 실제 데이터를 갖는다.
   

위 사진의 경우 dept_no와 emp_no 컬럼으로 이루어진 복합 인덱스이다. 페이지에 값이 저장될 때는 첫 번째 인덱스를 기준으로 먼저 정렬하고, 똑같은 값을 갖는 값은 두 번째 인덱스를 기준으로 정렬된다. 위 사진에서는 dept_no를 기준으로 먼저 정렬하고, emp_no를 기준으로 다시 한 번 정렬된다.

3-2. 데이터 쓰기 작업 시에 영향을 미치는 이유

인덱스를 구성한 상태에서 데이터 쓰기 작업(INSERT, UPDATE, DELETE)가 느려지는 이유는 트리 구조가 재조정되기 때문이다. 데이터가 추가되었을 때, 리프 노드의 공간이 충분하지 않은 경우 기존의 페이지를 두 개로 분리해야 한다. 즉, 하나의 페이지가 두 개의 페이지로 분할되면서 트리 구조를 재조정해야 한다. 페이지 분할이 잦을수록 오버헤드가 발생한다.

3-3. INSERT 작업이 미치는 영향

예를 들어, 다음과 같은 테이블이 존재한다.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT,
    Grade CHAR(1)
);

 

Name과 Age 컬럼에 대한 각각의 인덱스(idx_name, idx_age)를 갖도록 한다. 이때, 각 인덱스에 대한 B-Tree 집합이 두 개가 생성된다.

• idx_name 인덱스의 B-Tree : Name 컬럼에 대한 값을 기준으로 페이지를 정렬하여 관리한다. Name 컬럼에 대한 데이터와 실제 값이 저장된 리프 노드로 향하는 포인터 값을 갖는다.
• idx_age 인덱스의 B-Tree : Age 컬럼에 대한 값을 기준으로 페이지를 정렬하여 관리한다. Age 컬럼에 대한 데이터와 실제 값이 저장된 리프 노드로 향하는 포인터 값을 갖는다.

CREATE INDEX idx_name ON Students(Name);
CREATE INDEX idx_age ON Students(Age);

 

두 개의 인덱스(idx_name, idx_age)가 있는 상황에서 아래의 INSERT 쿼리문이 발생한다.

• idx_name 인덱스
  • Name 컬럼에 대한 인덱스이므로, Alice 데이터에 참조할 수 있는 포인터가 페이지에 추가된다.
• idx_age 인덱스
  • Age 컬럼에 대한 인덱스이므로, 20 데이터에 참조할 수 있는 포인터가 페이지에 추가된다.

이때, Alice 또는 20이 추가될 페이지에 공간이 부족할 경우, 해당 페이지를 두 개의 페이지로 분할하고, B-Tree 구조를 재조정한다.

INSERT INTO Students (StudentID, Name, Age, Grade) VALUES (101, 'Alice', 20, 'A');

3-4. UPDATE 작업이 미치는 영향

3-3에서 사용한 테이블과 인덱스가 존재할 때, 아래의 UPDATE 쿼리가 발생한다. Age 컬럼의 값만 변경되기 때문에 idx_age 인덱스를 관리하는 B-Tree에서만 업데이트가 발생한다. Name 컬럼은 바뀌지 않았으므로 idx_name 인덱스를 관리하는 B-Tree에는 영향을 미치지 않는다.

UPDATE Students SET Age = 21 WHERE StudentID = 101;

3-5. DELETE 작업이 미치는 영향

마찬가지로 위에서 사용한 테이블과 인덱스에서 아래의 DELETE 쿼리가 발생한다. 이번에는 Name과 Age 컬럼을 모두 갖는 Students 데이터를 지우는 것이므로 idx_name과 idx_age를 관리하는 B-Tree에 모두 영향을 미친다.

DELETE FROM Students WHERE StudentID = 101;

 

위는 단일 인덱스(idx_name, idx_age)에 대한 설명이였고, 반대로 복합 인덱스(idx_name_age)라면 (Name, Age, 자식 노드 포인터)를 갖는 B-Tree가 생성된다. 이땐, Name 또는 Age 하나의 컬럼에 대해서 쓰기 작업이 발생해도 B-Tree에 영향을 미친다.

 

4. 인덱스(Index) 생성하기

4-1. 인덱스 관련 SQL

• 인덱스 생성 SQL
  • 복합 인덱스 구성 시에는 주로 검색되는 순서와 필드의 카디널리티를 고려하여 컬럼 순서를 결정한다.

# 단일 인덱스
CREATE INDEX {인덱스 이름} ON {테이블 이름}(필드);

# 복합 인덱스
CREATE INDEX {인덱스 이름} ON {테이블 이름}(필드1, 필드2, ...);

• 인덱스 조회 SQL

SHOW INDEX FROM {테이블 이름};

• 인덱스 삭제 SQL

ALTER TABLE {테이블 이름} DROP INDEX {인덱스 이름};

4-2. 테이블 생성 시에 단일 인덱스 정의

• 단일 인덱스 SQL 문

CREATE TABLE table1(
    uid INT(11) NOT NULL auto_increment,
    id VARCHAR(20) NOT NULL,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL,
    PRIMARY KEY('uid'),
    key idx_name(name),
    key idx_address(address)
)

 

table1의 경우 name 컬럼과 address 컬럼에 대하여 단일 인덱스를 생성하였다. 따라서 다음과 같이 조회 쿼리문을 작성하는 경우 더 빠르게 조회되는 컬럼을 먼저 조회하고 그다음 컬럼을 조회하게 된다.

SELECT * FROM table1 WHERE name = '홍길동' AND address='경기도';

4-3. 테이블 생성 시에 복합 인덱스 정의

CREATE TABLE table2(
    uid INT(11) NOT NULL auto_increment,
    id VARCHAR(20) NOT NULL,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL,
    PRIMARY KEY('uid'),
    key idx_name(name, address)    
)

 

table2의 경우 name컬럼과 address 컬럼으로 이루어진 복합 인덱스를 생성하였다. 아래 조회 쿼리문의 경우 복합 인덱스에 해당하는 name과 address를 사용하여 조회하므로 한 번에 조회하게 된다.

SELECT * FROM table2 WHERE name = '홍길동' AND address = '경기도';

 

그러나 아래와 같이 name이 아닌 address로만 탐색하는 경우 비효율적인 탐색이 될 수 있다. name 컬럼으로 우선적으로 정렬하고 탐색하므로, address 조건만으로는 인덱스를 효율적으로 사용할 수 없다. 사실상 Full Table Scan으로 address가 경기도인 레코드를 찾아야 한다.

SELECT * FROM table2 WHERE address = '경기도';

 

위와 반대로 아래는 데이터 조회 시에 인덱스가 적용된다. 복합 인덱스 생성 시에 첫 번째 컬럼으로 name을 주었기 때문에 BTree 자료구조 탐색으로 인해 인덱스가 적용된다.

SELECT * FROM table2 WHERE name = '홍길동';

 

5. 인덱스 설계 방법

• 인덱스를 많이 생성하지 않는다. 데이터 쓰기 작업에서 성능이 오히려 떨어진다.
• 데이터 조회 시에 많이 사용되는 컬럼으로 인덱스를 생성한다.
• UPDATE가 빈번히 일어니지 않는 컬럼을 인덱스로 사용한다.
• JOIN시에 자주 사용하는 컬럼을 인덱스로 사용한다.
• WHERE 절에 자주 등장하는 컬럼을 인덱스로 사용한다.
• ORDER BY 절에 자주 등장하는 컬럼을 잘 조합해서 인덱스로 구성한다.
• 카디널리티(Cardinality)가 높은 컬럼을 인덱스로 사용한다.
  • 카디널리티란 컬럼에서 중복된 값의 수치를 말하는데, 컬럼 값이 중복되지 않고 고유한 값이 많다는 것을 의미한다.
  • ex) 성별은 남/녀 두 가지 값만 가지므로 카디널리티가 낮고, 계좌 번호는 중복되지 않으므로 카디널리티가 높다고 한다. 이 경우 계좌 번호를 인덱스로 결정한다.
  • 카디널리티가 높은 경우 인덱스를 사용하면 특정 값을 더 쉽게 찾아낼 수 있다.

 

---

참고

https://annajin.tistory.com/96

https://jojoldu.tistory.com/243

https://rachel0115.tistory.com/entry/MySQL-인덱스-INDEX-정리-동작-방식-생성-삭제-설계