인덱스(Index)의 개념 정리 및 실습을 통해 이해하기

 

인덱스(Index)의 개념 정리 및 실습을 통해 이해하기

2024. 8. 3. 13:09· 데이터베이스/이론

목차

1. 인덱스란?
2. 인덱스의 종류
3. 클러스터형 인덱스(Clustered Index)
4. 보조형 인덱스(Secondary Index)
5. 인덱스 생성하기
6. 자동으로 생성
7. 수동으로 생성
8. 인덱스의 장단점
9. 장점
10. 단점
11. 인덱스를 사용하면 좋은 경우
12. 인덱스 실습해보기
13. 사용한 데이터셋
14. 실습방식
15. 실습 환경
16. 1번 실습
17. 2번 실습

인덱스란?

---

인덱스(Index)란 데이터베이스에서 데이터를 더 빠르게 검색할 수 있게 해주는 자료구조이다.

인덱스를 이해하는 가장 쉬운 방법은 책을 떠올리는 것이다. 책에는 '목차'라는 것이 존재한다.

이 목차를 통해 내가 원하는 내용을 쉽게 찾아서 읽을 수 있다. 반면 이러한 목차가 존재하지 않는다면 독자는 앞에서부터 페이지를 넘겨가며 해당 데이터를 찾아야 할 것이다. 운이 좋게 앞쪽 페이지에 나왔다 하더라도 뒤에 그 내용이 안나온다는 보장이 없기 때문에 결국 끝까지 찾아봐야 한다. 이것이 바로 인덱스의 기본적인 원리이다.

 

 

인덱스의 종류

---

인덱스는 크게 클러스터형과 보조형으로 나눌 수 있다.

 

클러스터형 인덱스(Clustered Index)

클러스터형 인덱스(Clustered Index)란 데이터 행의 원래 순서대로 저장되어 있는 인덱스를 의미한다.

데이터의 저장 순서 그대로 되어있기 때문에 각 테이블은 단 하나의 클러스터형 인덱스를 가질 수 있다.

또한 클러스터형 인덱스는 행 데이터를 지정한 열에 맞춰 자동으로 정렬한다.

 

클러스터형 인덱스는 '영어사전'이다. 영어사전은 책 내용자체가 알파벳 순서로 되어 있기 때문에 그 자체가 인덱스가 된다.

 

 

보조형 인덱스(Secondary Index)

보조형 인덱스(Seconday Index)란 실제 데이터의 위치와는 별개로 저장되는 인덱스를 의미한다.

보조형 인덱스는 인덱스의 기준이 되는 컬럼을 기준으로 실제 데이터와는 다른 별도의 순서를 가진다.

그렇기 때문에 각 테이블은 여러 개의 보조형 인덱스를 가질 수 있다.

 

보조형 인덱스는 '일반적인 목차가 있는 책'이다. 책 내용은 특정 키워드 순서로 진행되는 것은 아니지만 목차를 통해 그 키워드를 찾아 접근할 수 있듯, 보조형 인덱스도 데이터의 저장 순서와는 상관없이 존재한다.

 

 

 

인덱스 생성하기

---

 

자동으로 생성

1. 기본 키로 생성

테이블 생성시 기본키를 지정해주게 되면 그 기본키를 기준으로 하는 하나의 클러스터형 인덱스를 가진다.

이 인덱스를 기본 키 인덱스(Primary Key Index)라고도 한다.

 

• DDL에 기본 키 제약조건 명시

CREATE TABLE employees (
    employee_id INT NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    PRIMARY KEY (employee_id)
);

 

• 만약 DDL에서 기본 키 제약조건을 명시하지 않았다면

CREATE TABLE employees (
    employee_id INT NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

ALTER TABLE employees
ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id)

 

 

2. UNIQUE 제약조건으로 생성

테이블의 컬럼에 유일 값을 가지게 하는 UNIQUE로 설정하게 되면 그 컬럼을 기준으로 하는 하나의 보조형 인덱스를 가진다.

 

• DDL로 UNIQUE 제약조건 명시

CREATE TABLE employees (
    employee_id INT NOT NULL,
    email VARCHAR(100) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    PRIMARY KEY (employee_id),
    UNIQUE (email)
);

 

• DDL에 UNIQUE 제약조건을 명시하지 않은 경우

CREATE TABLE employees (
    employee_id INT NOT NULL,
    email VARCHAR(100) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    PRIMARY KEY (employee_id)
);

ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);

 

 

수동으로 생성

CREATE INDEX [인덱스 이름] ON [테이블 이름](컬럼 이름)

(실제로는 CREATE와 INDEX사이에 옵션이 있지만 해당 포스트에선 생략하겠다.)

 

• 예시

CREATE TABLE employees (
    employee_id INT NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    PRIMARY KEY (employee_id)
);
CREATE INDEX idx_department_id ON employees (department_id);

 

 

 

인덱스의 장단점

---

장점

• 검색(Select)하는 속도가 매우 빨라진다.(항상 그런 것은 아님)
• 쿼리의 부하가 줄어들어 시스템의 성능이 향상됨

단점

• 데이터베이스 크기의 10% 정도의 추가적인 공간이 필요해진다.
• 데이터의 변경(Insert, Update, Delete)작업이 자주 일어나는 경우 오히려 성능이 나빠질 수도 있다.

인덱스를 사용하면 좋은 경우

• 해당 컬럼이 Where조건에 자주 사용되는 경우
• 해당 컬럼이 Join의 조건이 되는 경우
• 해당 테이블의 데이터의 변경(Insert, Update, Delete) 작업이 자주 일어나지 않는 경우

 

인덱스 실습해보기

---

인덱스의 특성을 더 잘 이해하기 위해 실습을 진행해보았다.

 

사용한 데이터셋

아무래도 데이터의 갯수가 많아야 그 차이가 체감이 될 것 같아 최대한 큰 데이터 셋을 찾아보았다.

 

 

테스트할 테이블인 transaction_history는 다음과 같다.

 

해당 테이블은 541,909개의 데이터셋을 갖고 있다.

 

다음 sql을 입력하면 해당 테이블의 인덱스 목록을 볼 수 있다.

show index from transaction_history;

 

기본키인 ID로 인덱스가 자동생성되어 있는 것을 알 수 있다.

Table              |Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|Visible|Expression|
-------------------+----------+--------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+-------+----------+
transaction_history|         0|PRIMARY |           1|ID         |A        |     539015|        |      |    |BTREE     |       |             |YES    |          |

 

 

실습방식

1번 실습

• transaction_history 테이블을 무작위 순서로 복사해서 기본 키 인덱스가 없는 테이블을 만들고 각각 Where 조건을 id로 하는 Select 쿼리를 날려 인덱스가 얼마나 영향을 미치는지 확인

2번 실습

• 중복이 많은 컬럼을 인덱스로 하여서 실제로 해당 인덱스가 성능향상이 이루어지지 않는지 확인

 

실습 환경

OS: MAC

RDBMS: MySQL

DB 관리툴: DBeaber

 

1번 실습

transaction_history 테이블을 무작위 순서로 복사해서 기본 키 인덱스가 없는 테이블을 만들고 각각 Where 조건을 id로 하는 Select 쿼리를 날려 인덱스가 얼마나 영향을 미치는지 확인해보겠다.

우선 transaction_history 테이블의 데이터가 무작위 순서로 복사된 transaction_history_cp 테이블을 만들었다.

CREATE table transaction_history_cp
SELECT * FROM transaction_history order by RAND();

 

테이블이 잘 생성되었다.

 

 

당연히 기본키 제약조건을 주지 않았으므로 transaction_history_cp 테이블엔 현재 인덱스가 존재하지 않는다.

show index from transaction_history_cp;

 

자 이제 본격적으로 실습을 진행해보겠다.

기본 키 인덱스가 생성되어있는 transaction_history테이블과 생성되어있지 않은 transaction_history_cp 테이블에 각각
SELECT * FROM … WHERE id = 6; 이라는 쿼리를 날렸을 때 실행시간에 얼마나 차이가 나는지 확인해보겠다.

 

 

1. transaction_history

SELECT * FROM transaction_history WHERE id = 6;

0.002초가 소요되었다.

 

2. transaction_history_cp

SELECT * FROM transaction_history_cp WHERE id = 6;

0.173초가 소요되었다. 무려 81.5배 차이가 난다.

 

결과

중복이 없는 기본 키 인덱스를 사용했을 때가 인덱스를 사용하지 않았을 때보다 쿼리 실행속도가 81.5배정도 더 빠르게 실행되었다.

 

 

2번 실습

중복이 많은 컬럼을 인덱스로 하여서 실제로 해당 인덱스가 성능향상에 악영향을 끼치는지 확인해보는 실습을 해보겠다.

 

transaction_history에서 country 컬럼은 53만개 데이터 중에서 38개의 고유한 값을 가진다.

SELECT COUNT(DISTINCT country) FROM transaction_history;

이 country를 통해 인덱스를 생성해 실습해보도록 하겠다.

 

먼저 country를 기준으로 인덱스를 만들어줬다.

CREATE index country on transaction_history(country);

 

그리고 쿼리를 실행해주었다.

SELECT * FROM transaction_history WHERE country = 'United Kingdom';

0.792초가 소요되었다.

 

반대로 인덱스를 다시 삭제하고 인덱스 없이 실행해보았다.

ALTER TABLE transaction_history DROP INDEX country;
SELECT * FROM transaction_history WHERE country = 'United Kingdom';

0.576초가 소요되었다.

오히려 인덱스를 사용하지 않았을 때가 더 빠르게 실행되었다.

 

결과

중복이 많이 발생하는 컬럼을 통해 보조인덱스를 만들어 사용했더니 오히려 쿼리의 속도가 더 느려졌다.

출처: https://jaehee1007.tistory.com/131