[DB] 인덱스(Index)로 조회 성능 개선하기

개요

 그동안 데이터베이스 조회 성능 향상을 위해서 인덱스를 종종 사용했었는데, 따로 포스팅으로 정리한 적은 없어서 정리해보고자 한다.

인덱스를 한 마디로 표현하면 "별도의 메모리를 추가로 할당하여 데이터베이스 조회 성능을 향상시킬 수 있는 방법"이라고 할 수 있다.

 

 

인덱스

인덱스에 대해서 이야기하기 전에, 데이터베이스에서 어떻게 데이터를 조회하는지 서술하도록 하겠다.

 

우리가 흔히 말하는 Oracle이나 Mysql을 '데이터베이스 시스템' 이라고 부르는데, 데이터베이스 시스템은 데이터가 저장되는 '데이터베이스'와 데이터베이스를 관리하는 소프트웨어인 'DBMS(DataBase Management System)'으로 구성된다.

 

DBMS에서 데이터베이스에 있는 데이터를 조회하기 위해서는 DBMS 내의 '질의 처리기(Query Processor)'와 '저장 데이터 관리자(Stored Data Manager)'를 사용하는데, 일반적으로 다음과 같은 순서를 따르게 된다.

 

1. 구문 분석: 애플리케이션에서 SQL 쿼리가 데이터베이스 시스템으로 전달되면, DBMS의 질의 처리기는 쿼리를 받아서 구문 분석을 수행한다. 이 단계에서 문법 오류를 확인하게 된다.

2. 최적화: 파싱이 완료되고 나면, 쿼리를 실행하기 위한 최적의 수행 계획을 수립하여 캐시에 저장해둔다.
(p.s 이 부분을 '옵티마이저'라고 부르기도 한다)
이는 테이블을 full-scan을 통해 조회할지, 인덱스를 사용할지, 조인을 어떠한 순서로 수행할지와 같은 정보들을 포함한다. 또한, 캐싱된 데이터를 활용하여 추후 동일 쿼리가 다시 실행되면 속도를 향상시킬 수 있다.

3. 실행: 옵티마이저가 수행한 계획에 따라서 쿼리를 실행한다. 이 때 저장 데이터 관리자를 사용하여 데이터베이스에서 데이터를 실제로 조회하게 된다.

 

그런데 최적화 단계에서, 인덱스가 존재하지 않으면 모든 데이터를 순차 탐색을 통해서 읽게 되는데, 이를 Table Full Scan 방식이라고 한다. 이러한 방법은 테이블의 모든 행을 차례대로 읽어야 하므로, 높은 I/O cost(O(N) in worst case)로 인해 속도가 매우 느려지게 된다. 따라서 B+Tree를 사용하여 조회 속도를 O(logN)으로 줄일 수 있는 방법이 인덱스이다.

 

 

 

Mysql의 인덱스

 Mysql은 5.5버전부터 InnoDB를 기본 스토리지 엔진으로 사용하고 있다.

이전에 사용하던, 테이블 수준의 락을 지원하던 MyISAM과 InnoDB의 가장 큰 차이점 중에 하나는 InnoDB가 레코드 수준의 락을 지원한다는 것이다. 즉, 하나의 레코드에 대한 변경 작업이 발생하면 해당 테이블 전체에 락이 걸려서 테이블의 데이터를 수정할 수 없게 되는 것이다. 이는 다수의 트랜잭션이 동시에 작동하는 상황에서 성능 저하를 일으키게 된다.

 

반면 InnoDB는 레코드 수준의 잠금을 지원한다. 이는 한 번에 여러 트랜잭션이 하나의 테이블에 여러 레코드를 수정할 수도 있음을 의미한다. 따라서 인덱스를 사용할 경우 레코드를 찾기 위해서 검색한 인덱스의 레코드가 모두 잠기기 때문에, 인덱스를 잘 설정해주면 성능을 향상시킬 수 있다.

 

 

예시

아래와 같은 orders 테이블이 존재하고, 클러스터링 인덱스 외에 아무런 인덱스가 설정되지 않았다고 가정하자.

 

UPDATE orders SET total_amount = total_amount + 50 WHERE customer_id = 101;

 

위와 같은 update쿼리를 날리게 되면, customer_id가 101인 레코드를 탐색하기 위해서 테이블 전체에 대해서 순차 탐색을 해야 하기 때문에 결국 테이블 전체에 대한 락을 필요로 하게 된다.

 

이때 외래키인 customer_id 컬럼에 인덱스가 설정되어 있다면, order_id가 1, 3인 두개의 레코드만 락이 걸리게 된다. 이는 order_id가 2,4인 레코드에 대해서는 동시에 트랜잭션이 실행될 수 있음을 의미한다.

 

(이러한 이유에서인지 InnoDB는 자동으로 외래 키에 대해서 인덱스를 생성하도록 설정되어 있다.)

 

 

인덱스 종류

클러스터링 인덱스

https://golf-dev.tistory.com/67

CREATE TABLE users(
    id int primary key,
    name varchar(255),
    email varchar(255) unique
)

 

 관계형 데이터베이스는 '기본키 제약 조건'에 의해서 반드시 unique한 pk가 존재하는데, 데이터베이스는 이를 기준으로 B+Tree를 사용하여 데이터를 저장한다. 이렇게 pk를 기준으로 하는 기본적으로 존재하는 B+Tree를 클러스터링 인덱스라고 한다.

 

 

논클러스터링 인덱스

CREATE TABLE users(
    id int primary key, # 클러스터링 인덱스
    name varchar(255),
    email varchar(255) unique # 논클러스터링 인덱스
)

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

 

반면 우리가 unique조건 등을 사용하거나, CREATE INDEX 명령어를 통해서 인덱스를 생성하게 되면 기본적으로 존재하는 클러스터링 인덱스 외에 추가로 B+Tree를 사용한 인덱스를 생성할 수 있다. 이렇게 생성된 인덱스를 논클러스터링 인덱스라고 한다.

 

그림에서 볼 수 있듯이, 논클러스터링 인덱스는 클러스터링 인덱스와 다르게 실제 데이터값을 저장하고 있지 않고, 대신 데이터가 존재하는 페이지 번호, 즉 참조값을 가지고 있다는 차이가 있다. 따라서 인덱스를 조회한 후, 해당 페이지 번호로 이동하여 실제 데이터를 조회하는 과정을 거치게 된다.

 

 

 

간단한 성능 테스트

이제 인덱스를 설정해주는 것이 얼마나 효율성을 가져오는지 확인해보자. 아래 실습은

• MySQL 8.1.0(InnoDB 8.1.0)
• MacOS Sonoma 14.5

에서 진행했다.

 

1. 데이터베이스 테이블 생성

mysql> create table bulk (
    ->  id int primary key,
    ->  name varchar(255),
    ->  email varchar(255)
    -> );

 

 

2. 대용량 데이터 정보를 담는 .csv 파일 생성

public class Main {
    public static void main(String[] args) {
        String fileName = "data.csv";
        int totalRecords = 1_000_000;

        try(FileWriter writer = new FileWriter(fileName)) {
            writer.append("id,name,email\n");  // 헤더 추가

            Set<String> emails = new HashSet<>();
            Random random = new Random();

            for (int i=1; i<=totalRecords; i++) {
                String name = "name_"+UUID.randomUUID().toString().substring(0, 8);

                String email;
                do {
                    email = "user"+random.nextInt(1_000_000)+"@example.com";
                } while (!emails.add(email));

                writer.append(String.format("%d,%s,%s\n", i, name, email));
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

data.csv

 

100만건의 더미 데이터를 만들어서 넣어주면, 위와 같은 형태로 파일이 생성된다.

 

 

3. MySQL의 LOAD DATA INFILE을 통해서 .csv파일을 데이터베이스 테이블에 삽입하기

LOAD DATA INFILE '.csv 파일 절대경로'
INTO TABLE bulk
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES(id, name, email);

 

위 명령어를 실행해주면 된다. 

만약 위와 같은 오류가 발생한다면 MySQL이 특정 경로에서만 파일을 읽도록 설정되어 있는 것인데, MySQL 설정파일인 my.cnf 파일을 찾아서 이 설정값을 바꾸어 주어야 한다.

 

my.cnf파일의 위치를 찾으려면 

이렇게 명령어를 사용해서 basedir 하위에 위치한다고 하는데, 나는 저 위치에 없고 /opt/homebrew/etc 아래에 있었다. homebrew로 MySQL을 깔아서 그런가?

 

mysql.server restart

어쨌든 해당 파일을 찾고 나서 이렇게 'secure-file-priv' 한 줄을 추가해주고, MySQL을 재시작해주고 나면 LOAD DATA INFILE명령어 실행시 오류가 발생하지 않는다.

 

성공

 

4. 쿼리 수행시간 측정 - 인덱스 적용 전

간단하게 MySQL에서 제공하는 Profile 기능을 통해서 쿼리 수행 시간을 측정해보자.

 

특정 이메일 값을 갖는 데이터를 찾기 위한 select쿼리 수행에는 0.19초 가량이 소요된 것을 확인할 수 있다.

 

 

5. 쿼리 수행시간 측정 - 인덱스 적용 후

아래와 같이 조회할 컬럼에 인덱스를 적용하였다.

 

이제 다시 쿼리 성능을 측정해보자.

 

인덱스를 적용했을 때 전체 쿼리 수행시간은 0.187s에서 0.002s로 감소하였고, 특히 executing 단계가 0.187378s에서 0.0012s로 단순 조회시 대략 150배 가량의 조회 성능이 향상됨을 확인할 수 있었다.

 

 

정리

 이렇게 보면 인덱스가 조회 속도를 향상시켜주므로, 반드시 사용하는 것이 좋아 보일 수도 있다.

하지만 인덱스 자체도 메모리를 차지하며, 데이터를 수정할 경우 인덱스를 구성하는 B+Tree 또한 수정해주어야 하며, 인덱스의 크기가 매우 커질 수도 있다. 따라서 수정, 삭제가 드물고 조회가 잦은 컬럼에만 인덱스를 사용하는 것이 좋다.

 

(언젠가 조회:수정 비율이 최소 8:2는 되어야 인덱스를 적용해볼 만 하다고 들은 적이 있는데, 구글링을 아무리 해봐도 그러한 자료는 아직 못 찾음 - 추가 예정)