[MySQL] 1개의 테이블에 복수개의 인덱스 생성하기

들어가며

 

정규화과정에 맞춰 테이블을 설계하고나면 테이블에 PK 컬럼이 필요하게되고 테이블 생성시 Primary key 컬럼이 존재하면 보통 PRIMARY 타입 인덱스가 자동으로 생성된다.

PK 값을 조건으로 대량의 데이터가 존재하는 테이블을 조회한다면 PRIMARY 타입 인덱스가 수행되기 때문에 성능에는 문제가 되지 않을 것이다.

 

하지만 정책에 맞게 비즈니스로직을 구현하다보면은 테이블의 PK 값을 조건으로 데이터를 조회하는것 뿐만이 아닌 FK나 일반 컬럼을 조건으로 데이터를 조회(기본으로 생성된 PRIMARY 타입 인덱스가 수행되지 않아 Full scan) 하는 일이 생길 수 밖에 없다.

 

이는 인덱스를 추가적으로 생성하여 해결할 수 있다.

 

 

시나리오

 

가정

 

게임과 어느 한 게임에 등장하는 캐릭터가 존재한다고 가정하자.

다음과 같이 두개의 테이블로 구성했다.

 

 

또한 각 테이블마다 100만개의 더미 데이터가 삽입되어있다.

 

그리고 각 테이블은 PK가 존재하기 때문에 테이블 생성시 다음과 같이 자동으로 PRIMARY 타입 인덱스가 생성된다.

 

SHOW INDEX FROM test.game

 

 

SHOW INDEX FROM test.character

 

---

시나리오 1 

 

하나의 캐릭터의 정보를 조회하는 기능이 있다고 가정하자.

예를 들면 character_id가 55555인 캐릭터 조회해보자.

 

SELECT * FROM test.character WHERE character_id = 555555

 

 

결과

 

EXPLAIN SELECT * FROM test.character WHERE character_id = 555555

 

PRIMARY 타입 인덱스가 수행되어 100만개의 데이터가 있음에도 불구하고 조회시간이 0.00022sec 밖에 소요되지 않았음을 볼 수 있다.

 

---

시나리오 2

 

이번엔 어느 한 게임에 등장하는 모든 캐릭터의 정보를 조회하는 기능이 있다고 가정하자.

예를들면 game_id가 5555인 캐릭터를 모두 조회해보자.

 

SELECT * FROM test.character WHERE game_id = 5555

 

 

결과

 

EXPLAIN SELECT * FROM test.character WHERE game_id = 5555

 

인덱스가 수행되지 않아 시나리오 1과는 다르게 0.183sec 소요 됐음을 확인할 수 있다.

이 slow query를 해결하기위해 character 테이블의 game_id 컬럼으로 인덱스를 추가로 생성해보자.

 

 

인덱스 추가 생성

 

인덱스를 추가로 생성하기 앞서 인덱스 타입 종류를 살펴보자

 

INDEX Type

기본적으로 유니크하지 하지 않은 중복되는 값을 허용한다.

제약을 줄순 없고 오로지 접근에만 쓰인다.

 

UNIQUE Type

유니크한 값을 참조하는 인덱스 타입이다.

행마다 중복되는 값은 허용되지 않으나 NULL값은 허용된다.

고유값 규칙이 손상되지 않도록 특정데이터에 빠르게 접근하는것뿐만아니라 데이터에 제약도 줄 수 있다.

 

PRIMARY Type

인덱스이름이 PRIMARY 인것과 오로지 하나의 테이블에 하나의 PRIMARY 타입 인덱스가 존재하기를 강제한다.(일부 시스템에서는 다를 수 있음)

중복되는 값은 허용하지 않으며 NULL값 또한 허용하지 않는다.

 

FULLTEXT Type

보통 MATCH 또는 AGAINST 절에 사용되며 한컬럼의 값에 많은 형태의 데이터가 담겨있을 때 효율적으로 데이터를 조회하기위해 사용되는 인덱스 타입이다. 검색서비스를 구현할 때 필요한 기능으로 보인다.

Ex) “오늘 점심은 vintage 1988에서 먹었다.”

 

---

 

character 테이블의 game_id 가 유니크한 값을 가졌다면 UNIQUE Type의 인덱스가 적합하겠지만 그렇지 않으니 INDEX Type의 인덱스로 생성하는게 적합해 보인다.

 

CREATE INDEX game_idx ON test.character (game_id ASC);

SHOW INDEX FROM test.character

인덱스가 잘 생성됐음을 확인할 수 있다.

 

 

테스트

 

조회 시간이 0.183sec이나 소요됐던 쿼리를 다시 실행시켜보자.

 

SELECT * FROM test.character WHERE game_id = 5555

 

EXPLAIN SELECT * FROM test.character WHERE game_id = 5555

 

쿼리 실행시 이전에는 옵티마이저가 사용할 수 있는 인덱스가 없었지만 game_idx 인덱스가 추가, 사용하여 조회 시간이 0.183sec -> 0.00035sec로 대폭 감소했음을 확인할 수 있다.

 

 

인덱스를 생성할 컬럼 결정하기

 

인덱스로 설정된 해당 컬럼으로 최대한 많은 필터링을 수행해야 조회성능이 높아지기 때문에 카디널리티(Cardinality)가 높은 컬럼으로 인덱스를 생성해야 한다.

카디널리티란? 데이터의 한 집합에서 고유한 값의 개수, 수치의 정도를 뜻한다.

카디널리티가 높다 == 컬럼이 가진 값의 중복 수치가 낮다.

 

* 추가적으로 하나의 인덱스에 복수개의 컬럼을 설정할 경우 카디널리티가 높은 컬럼 순으로 구성하는게 좋다.

 

 

결론

 

저장된 데이터가 많지 않다면 굳이 인덱스를 생성할 필요는 없다. 인덱스 생성 후 삽입, 삭제, 수정 연산에 대해 인덱스를 재구성해야하는 비용이 들기 때문이다.

그러나 조회가 더 빈번히 일어난다면 카디널리티가 높은 컬럼순으로 인덱스를 생성해 조회성능을 높여 보자.

 

 

 

참고

https://stackoverflow.com/questions/707874/differences-between-index-primary-unique-fulltext-in-mysql

Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?