[Mysql] Index로 조회 성능 개선하기

데이터 베이스 테이블에 수백만건의 데이터가 존재한 상태로 특정 값을 조회하고자 할 때 모든 열을 다 살펴보는 것은 효율적이지 않습니다.

이 비효율성을 개선하기 위해 MySQL에는 index라는 개념이 존재합니다.

이번 포스팅에서는 MySQL을 기준으로 index에 대해 학습하고 조회 성능을 개선하는 사례를 공유하고자 합니다.

Index

인덱스는 목차입니다. 혹은 특정 내용을 기반으로 쉽게 데이터를 탐색할 수 있도록 제공하는 무언가를 인덱스라고 부릅니다.

특정 데이터를 쉽게 탐색할 수 있도록 목차를 제공해주니 데이터의 양이 많아질 수록 SELECT 성능은 좋아집니다. 하지만 매 데이터를 INSERT, UPDATE, DELETE할 때 마다 새 목차를 생성하다 보니 데이터 변경 작업이 많아질수록 MySQL 자체의 성능이 떨어질 수 있습니다.

UPDATE, DELETE의 경우 Index를 기준으로 탐색하고 변경한다면 그나마 괜찮지만 INSERT의 경우 특히 성능이 떨어집니다.

간단한 작동 원리

가장 기본적인 MySQL의 인덱스 내부 작동은 B-TREE로 이뤄집니다.

페이지라고 불리는 노드를 만들어 루트 페이지를 탐색하고 원하는 데이터가 존재하는 리프 페이지로 이동하여 모든 로우를 스캔하지 않고 쉽게 데이터를 탐색할 수 있습니다.

 

하지만 이 페이지를 분할하는 과정에서 성능의 저하가 발생하는데요. 페이지에 더이상 데이터를 담을 공간이 존재하지 않는다면, 새로운 페이지를 생성하고 기존 페이지와 데이터를 나눕니다. 그리고 생겨난 빈 공간에 데이터를 담습니다. 이런 복잡한 과정 탓에 인덱스를 부여하는 것이 항상 좋은 선택은 아닙니다.

Index 생성

그렇다면 어디에 어떻게 Index를 생성하는 것이 좋을까요?

create table coupons(
    id bigint not null primary key auto_increment,
    name varchar(255) not null unique key,
    member_id bigint not null,
    isUsed boolean not null default false);

alter table coupons
    add constraint fk_coupons_to_member
    foreign key (member_id) references member (id);

위 DDL을 실행시켜 보겠습니다.

자동으로 Primary Key와 Unique컬럼에 대해 인덱스를 생성한 것을 볼 수 있습니다. (MySQL은 FK에 대해 외래키 인덱스 또한 생성합니다. fk를 포함한 다른 인덱스를 미리 생성해 놔서 캡쳐화면에는 나오지 않았습니다.)

 

따라서 첫 번째 Index 생성 기준은 MySQL이 해주는 대로 생성하기입니다.

또, 인덱스의 효율을 최대화 하기위해 Cardinality가 높은 컬럼을 선택하는 것도 방법입니다.

Cardinality란 컬럼의 중복 수치입니다. 높을 수록 중복되지 않는다는 뜻입니다.

인덱스를 태워 최대한 많은 데이터를 필터링해야하기 때문에 Cardinality 또한 주요 기준이 될 수 있습니다.

 

위 내용처럼 몇가지 Index설정 기준이 있겠지만 사실 Index는 조회 성능 개선을 위해 존재합니다. 즉 WHERE, ORDER BY, GROUP BY 를 사용하는 조건이 되는 컬럼을 추가하는 것이 가장 중요하겠죠. 하지만 이 조건은 하나가 아닌 경우가 많습니다. 이제부터 여러 컬럼을 인덱스로 만들어 조회 성능을 개선해보겠습니다.

성능 개선

먼저 조회 상황을 한 번 가정해보겠습니다.

멤버가 사용하지 않은 쿠폰을 모두 조회하라 의 경우 어떤 쿼리가 발생할까요?

SELECT * FROM coupons WHERE member_id = ? and isUsed = false;

이 두 가지 조건으로 SELECT 쿼리가 DB에 발생합니다. 그렇다면 인덱스가 없는 상태의 실행계획을 확인해봅시다.

EXPLAIN SELECT * FROM coupons WHERE member_id = ? and isUsed = false;

 

FK를 기준으로 인덱스를 태우긴 했지만 이후 isUsed에 대해서는 모든 로우를 탐색하기 때문에 레코드를 읽고 최종적으로 필터링 된 예측값은 16.67 정도입니다.

그러면 이제 두 컬럼에 대한 인덱스를 생성해봅시다.

create index idx_coupons_member_isUsed on coupons (member_id, isUsed);

 

두 컬럼을 기준으로 인덱스를 태운 것을 볼 수 있습니다.

조회한 row가 모두 SELECT의 결과로 나오는 것을 filtered를 통해 알 수 있습니다. 즉, 불필요한 로우 조회가 없었던 것이죠.

현재 데이터 양에서는 큰 효과가 없겠지만 데이터가 많아질 경우 큰 성능 개선을 가져올 수 있습니다.

여러 컬럼에 대한 인덱스 사용 주의사항

1. 현재 isUsed컬럼은 member_id 컬럼에 의존하여 인덱스가 생성되어 있습니다. 즉, 첫 번째 인덱스 조건이 없다면 isUsed는 인덱스를 태우지 못합니다.

1. <, >, between 등의 범위 조건 뒤의 컬럼은 인덱스를 태우지 못합니다. (여러 인덱스가 존재하는 경우 MySQL은 범위 조건이 가장 뒤에 있는 컬럼을 가진 인덱스를 선택합니다.)

2. 컬럼값을 연산하면 안됩니다.
3. 여러 컬럼에 대한 인덱스 사용 주의사항은 아니지만 LIKE 뒤에 바로 등장하는 와일드 카드 % 는 인덱스를 태우지 못합니다. (여러 문자열의 각 텍스트를 기준으로 인덱스를 생성하는 FULL TEXT INDEX가 존재하니 공부해 보는 것을 추천합니다.)

정리

Index가 항상 답은 아니지만 MySQL의 성능을 튜닝하는데 key가 되는 존재인 것은 확실합니다!

잘 알아보고 사용하면 좋을 듯 합니다.