[DB] MySQL8.0 Index

안녕하세요. 회사와 함께 성장하고 싶은 KOSE입니다.

이번 포스팅은 RealMySQL8.0 (백은빈님 지음) 책을 읽고, 가상의 데이터를 생성하여 인덱스 쿼리 성능 비교를 하는 글을 작성하도록 하겠습니다.

 

Spring의 JPA나 QueryDsl을 사용할 때, index에 대한 개념을 명확하게 정리하지 않는 체 구현에만 급급하여 프로젝트를 진행했었습니다. RealMySQL8.0을 읽으면서 인덱스의 구조와 실행 계획을 확인하니, Like의 '%'의 위치에 따라 인덱스로 데이터 접근이 가능하거나 불가능하다는 사실을 확인할 수 있었습니다. 먼저 인덱스에 대한 개념과 B-Tree에 대한 설명, 그리고 쿼리를 통한 성능 분석을 진행하도록 하겠습니다.

 

1. 인덱스란?

DBMS는 테이블의 모든 데이터를 검색해서 원하는 정보를 가져오려면 시간이 오래 걸립니다. 그래서, 컬럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 삼아 인덱스를 생성하면, 시간을 단축할 수 있습니다. 또한, 정렬 기능이 추가된다면 사전을 찾을 때처럼 단어를 찾는 속도를 향상할 수 있는데, DBMS도 마찬가지로 컬럼의 값을 주어진 순서로 미리 정렬해서 보관합니다.

 

인덱스의 역할로 구분하기

- 프라이머리 키: 프라이머리키는 레코드를 대표하는 컬럼의 값으로 만들어진 인덱스입니다. 이 컬럼은 테이블에서 해당 레코드를 식별할 수 있는 기준값이 되기 때문에, 이를 식별자라고 부릅니다. 프라이머리 키는 NULL 값과 중복 값을 허용하지 않는 특징이 있습니다.

- 세컨더리 키: 프라이머리 키를 제외한 모든 인덱스를 세컨더리 인덱스라 부릅니다. 유니크 인덱스는 프라이머리 키와 성격이 비슷하고 프라이머리 키를 대체하여 사용할 수 있어 대체키로 부르기도 합니다.

 

 

2. B-Tree 인덱스

B-Tree는 Balanced Tree의 약자로 데이터베이스 인덱싱 알고리즘 가운데 가장 일반적으로 사용되고, 가장 먼저 도입된 알고리즘입니다. B-Tree의 변형된 알고리즘 형태는 B+-Tree와 B*-Tree가 있습니다.

RealMySQL8.0

- 구조 :

B-Tree는 트리구조의 최상위에 하나의 "루트 노드(Root node)"가 존재하고 그 하위에 자식 노드가 붙어 있는 행태입니다.  트리 구조의 가장 하위에 있는 노드를 "리프 노드(Leaf node)"라 하고, 트리 구조에서 루트 노드와 리프 노드가 아닌 중간 노드를 브랜치(Branch node)라 부릅니다. 리프 노드는 실제 데이터 레코드를 찾아가기 위한 주소값을 가지고 있습니다.

대부분 RDBMS의 데이터 파일에서 레코드는 특정 기준으로 정렬되지 않고 임의의 순서로 저장됩니다. 하지만 InnoDB 테이블에서 레코드는 클러스터 되어 디스크에 저장되므로 기본적으로 프라이머리 키 순서로 정렬되어 저장됩니다,!

 

 

3. 인덱스 사용과 성능

인덱스는 사용 방법에 따라, 성능이 달라집니다. 인덱스 테이블을 사용하더라도, 잘못 사용하면 인덱스의 주요 기능을 활용할 수 없습니다. 인덱스 성능 테스트를 위해 인텔리제이를 MySQL와 연동하여 약 1000만 개의 데이터를 생성하여 쿼리를 파악하는 시간을 가져보았습니다.

 

1) 인텔리제이 Mysql 콘솔 활용하기

 

오른쪽 상단에 데이터베이스를 클릭하고 생기는 데이터 소스 프로퍼티를 클릭하면, 인텔리제이와 MySQL을 연동할 수 있습니다.

호스트, 사용자, 비밀번호, URL을 입력하면 현재 root 계정에 있는 MySQL database에 연결할 수 있습니다.

 

 

 

2) 테이블 생성과 더미 데이터 저장

member의 테이블 명과 member_id (primary_key),  age, gender, user_id, username의 컬럼을 가진 테이블을 생성하였습니다.

다른 컬럼과 비교하기 위해 인덱스 컬럼은 user_id로 설정하였습니다.

# 인덱스 생성하기
CREATE INDEX member_user_id_idx ON member(user_id);

 

더미 데이터는 insert into table(컬럼) values(값)으로 일부 저장한 후,  아래 콘솔을 여러 번 반복하여 데이터를 추가하였습니다. insert into table 이후, select 절을 활용하면 select 된 데이터를 다시 insert 하는 데 사용할 수 있습니다. 이러한 방법을 활용하면 현재 있는 데이터 *2 만큼 데이터를 추가할 수 있습니다.

INSERT INTO member SELECT NULL, age, gender, user_id, username FROM member;

 

 

3) 성능 비교하기 인덱스 비교

 

먼저 user_id라는 인덱스를 활용하여 질의한 실행계획은 다음과 같습니다. type에 ref로 동등 조건이 실행되었고, possible_keys로 실행 가능한 인덱스 키를 확인할 수 있습니다. 즉, member_user_id_idx라는 인덱스로 ref(동등 조건) 비교를 실행할 수 있다는 의미입니다.

결과를 파악하면, 80ms를 기록하였습니다.

 

 

반면, 인덱스를 활용하지 않은 경우는 실행계획과 결과가 다음과 같습니다. type은 All 처리, pssible_keys는 null이 기록되었습니다. 인덱스로 활용 가능하지 않으므로 해당 값이 기록되지 않은 것입니다.

추가로, SQL_NO_CACHE는 실행되는 쿼리가 반복되더라도 캐시를 활용하지 않겠다는 의미입니다. 이는 여러 번 반복되는 행위에 MySQL이 캐시화하는 과정이 숨어있는데 이를 제거하지 않는다면 명확한 비교를 하기 어렵기 때문입니다. 결과를 확인하면 128ms로 인덱스보다 훨씬 느린 것을 볼 수 있었습니다.

 

5) 성능 비교하기 Like '단어%'

인덱스는 Like를 매우 효율적으로 활용할 수 있습니다. 하지만, 사용하는 방법에 따라, 인덱스를 활용하지 못할 수도 있습니다. 

전자는 '%'를 뒤에 적용하여 질의하는 것입니다. 실행계획을 살펴보면 member_user_id_idx로 range 질의가 실행되고 있습니다.

쿼리 실행 결과 90ms입니다.

 

 

인덱스를 활용하지 않은 경우는 다음과 같습니다. 적용가능한 possible_keys가 없으므로 all 질의가 진행되었고 성능은 3s가 기록되었습니다.

 

6) 성능 비교 Like '%단어'

'단어%' 형태로 질의한 경우, 많은 성능 차이를 보였지만, %가 앞에 위치하게 된다면 인덱스를 활용할 수 없습니다. 인덱스는 기본적으로 왼쪽 값을 기준으로 오른쪽 값이 정렬되어 있습니다. 따라서 '단어%'의 경우, 해당 단어가 기록되기 시작 한 곳부터 범위 계산을 할 수 있지만, '%단어' 형태가 된다면, 앞에서부터 해당 단어가 포함되는지 확인해야 하므로, 인덱스를 사용하는 의미가 없어지는 것입니다. 예시를 보면 다음처럼, 인덱스를 활용한 결과와 활용하지 않은 결과가 동일한 것을 보실 수 있습니다.

 

 

 

7) 함수를 이용한 인덱스 검색

MySQL8.0 버전은 함수형 인덱스를 제공한다는 장점이 있습니다. 데이터가 많을 때, 인덱스 컬럼을 추가한다면 비효율적일 수 있습니다. 이때, 자주 질의되는 형태를 함수형으로 구현한다면 인덱스를 활용할 수 있습니다. 

 

CONCAT(username, ' ',gender

 

member_username_gender_full_idx라는 인덱스로 ref(동등비교) 질의가 실행되는 것을 확인할 수 있었습니다.

 

 

하지만, 중요한 점은 같은 결과를 반환할지라도, 반드시 정의한 함수형 원형을 사용해야 합니다. 

저는 gender를 M, WM으로 대문자로 선언했기에 upper를 쓰더라도 결과는 같은 결과를 반환합니다.

하지만, 원형에 변형을 준다면 인덱스를 활용할 수 없습니다.

 

 

 

이상으로 인덱스를 활용한 예제 몇 가지를 실습하고 정리해 보았습니다.

이외에도 많은 index 활용 전략이 있습니다. 이는 추후 2편으로 나눠서 진행하도록 하겠습니다.

활용소스는 깃허브 링크 하단에 추가하도록 하겠습니다.

 

읽어주셔서 감사드립니다.!

 

참고자료: RealMySQL8.0 (백은빈님 지음)

활용 소스: https://github.com/gosekose/MySQL-Study/blob/main/issue6/Issue6.sql