인덱스 적용기

6 minute read

어떤 컬럼을 인덱스로 설정할까?

카디널리티와 활용도 2가지 기준을 사용해서 인덱스를 설정하였다.

카디널리티

카디널리티는 전체 행에 대한 특정 컬럼의 중복 수치를 나타내는 지표이다 행에 대한 데이터가 중복 정도가 낮을수록 카디널리티가 높다고 표현한다.

왜 카디널리티가 높은 칼럼에 인덱스를 적용하는 것이 좋을까?

예를 들어 10000개의 데이터가 있는데, A칼럼은 중복되는 데이터가 10개라고하고 B칼럼은 중복되는 데이터가 1000개라고 해보자. 어떠한 조건으로 데이터를 검색할 때, B칼럼보다 A칼럼이 데이터가 다양하니 검색하기 더 힘들 것이다. 인덱스는 저장, 수정, 삭제에 대한 비용을 희생하면서 조회에 대한 이익을 얻고자 하는 것이기 때문에 조회 성능에 최대한 효율을 뽑아내기 위해해서는, 해당 인덱스로 많은 부분을 걸러내는 것이 좋기 때문이다.

활용도

말 그대로 해당 칼럼이 검색 조건으로 많이 사용되고 있는지다. 활용이 많이 될수록 인덱스의 이익을 많이 볼 수 있기 때문에, 활용도가 높을수록 인덱스를 설정하기 좋은 칼럼이다. 서비스에서 쿼리를 날릴 때 WHERE 절에 자주 활용되는지를 판단하면 될 것이다.

체크메이트 테이블 파악하기

각 테이블의 칼럼들에 대해, 카디널리티와 활용도를 기준으로 대략적인 순위를 매겨보았다. 또한 조회보다 추가, 수정, 삭제가 자주 일어나는 데이터에 대해서는 인덱스 후보에 배제시켰다.

User 테이블

기준	id (pk)	email	password (X)	nickname	provider
카디널리티	1	1	2	2	4
활용도	1	2 (4번 정도사용)	활용 x	3	4

id는 primary key로 프라이머리 인덱스가 존재한다.
password : OAuth 회원인 경우에는 password 칼럼이 null이기 때문에 인덱스를 적용하지 않았고 where문에서도 많이 사용되고 있지 않기때문에 배제시켰다.
provider : 체크메이트 자체 회원가입을 한 회원인지, google Oauth로 가입한 회원인지 구분하는 enum 타입이다. 카디널리티가 낮기 때문에 배제시켰다.
nickname : 사용자를 초대할 때 검색하는 경우에만 사용되고 있다. 활용도는 높지 않지만 카디널리티 측면에서 카디널리티가 높은편이다. 하지만 email 보다 카디널리티가 낮을 것이라고 판단이 되고, 활용도 측면에서도 email이 더 높기 때문에 적용시키지 않았다.
email : 이메일 중복 검사, 자체 로그인시 가입한 이메일인지 판단 여부, 구글 로그인을 할 때 자체 회원으로 가입한 이메일인지 판단 여부, 사용자를 초대할 때 검색하는 경우에 사용되고 있다. 이 테이블에서는 활용도가 가장 높았고 카디널리티도 높았기 때문에 인덱스를 적용시켰다.

Meeting 테이블

기준	id (pk)	name
카디널리티	1	2
활용도	1	활용 x

id : primary key로 프라이머리 인덱스가 존재한다.
name : 활용이 아예 안되고 있기 때문에 배제시켰다.

Participant 테이블

기준	id (pk)	isMaster	user_id (fk)	meeting_id (fk)
카디널리티	1	4	2	2
활용도	3	4	1	1

id : parimary key로 프라이머리 인덱스가 존재한다.
user_id, meeting_id : 외래키로 인덱스가 존재한다.
isMaster : boolean 타입으로 카디널리티가 매우 낮기 때문에 배제시켰다.

Event 테이블

기준	id (pk)	date	startTme	endTime	meeting_id (fk)
카디널리티	1	2	2	2	4
활용도	3	1	4	활용 x	1

id : primary key로 프라이머리 인덱스가 존재한다.
meeting_id : 외래키로 인덱스가 존재한다.
startTime : 카디널리티가 높지만 활용이 출석 시간과 비교해 출석 상태를 변경해주는 경우에만 사용되었고 이때 또한 가장 활용도가 높은 date도 where 조건에서 사용되고 있어 적용시키지 않았다.
endTime : 카디널리티가 높지만 활용이 되지 않고 있어 배제시켰다.
date : 카디널리티가 높고 출석이나 일정 조회할 때 거의 항상 사용되고 있어 활용도가 매우 높아 인덱스로 결정했다.

-> date가 where 조건으로 사용될 때 meeting_id와 대부분 함께 사용되고 있어 meeting_id와 다중 칼럼 인덱스로 결정했다.

Attendance 테이블

기준	id (pk)	status	disabled	participant_id (fk)	event_id (fk)
카디널리티	1	4	5	3	2
활용도	5	3	4	1	1

id : primary key로 프라이머리 인덱스가 존재한다.
participant_id, event_id : 외래키로 인덱스가 존재한다.
status : Enum 타입으로 카디널리티가 낮아 배제시켰다.
disabled : boolean 타입으로 카디널리티가 낮아 배제시켰다.

성능 테스트

테스트 환경

meeting : 값이 다른 데이터 1000개

event : meeting 1개 당 값이 다른 데이터 1000개 -> 총 100만개

Local 환경에서 진행

1번째

java

sql

select
    count(event.id)
from
    event 
left outer join
    meeting 
      on event.meeting_id=meeting.id
where
    meeting.id=?
    and event.date<=?

인덱스 생성 전

실행 시간

쿼리 실행 계획

인덱스 (date만)

실행 시간

쿼리 실행 계획

인덱스 (meeting_id, date)

실행 시간

쿼리 실행 계획

인덱스 (date, meeting_id)

실행 시간

쿼리 실행 계획

-> (meeting_id, date) 순으로 복합 인덱스를 설정하는 것이 가장 시간이 단축되었다. (약 50배)

쿼리 자체가 meeting_id와 같은 것을 추려내고 date 범위를 탐색하는 것이다보니, 인덱스 레인지 스캔 방식으로 탐색이 된다면 효과적일 것 같다.

동등 조건과 같이 개수가 적은 데이터를 조회하는 컬럼을 다중 컬럼 인덱스의 앞 쪽에 설정하고, 범위 검색과 같이 개수가 많은 데이터를 조회하는 컬럼을 다중 컬럼 인덱스 뒤 쪽에 설정하는 것이 효과적일 것 같다.

2번째

java

sql

select
    event.id,
    event.date,
    event.end_time,
    event.meeting_id,
    event.start_time
from
    event
left outer join
    meeting
        on event.meeting_id = meeting.id
where
    meeting.id=?
    and event.date=?

인덱스 생성 전

쿼리 실행 시간

쿼리 실행 계획

인덱스 (date만)

쿼리 실행 시간

쿼리 실행 계획

인덱스 (meeting_id, date)

쿼리 실행 시간

쿼리 실행 계획

인덱스 (date, meeting_id)

쿼리 실행 시간

쿼리 실행 계획

-> where문 조건으로 meeting_id와 date 둘다 사용하고 있으니 단일 인덱스보다 다중 칼럼 인덱스를 설정한 것이 더 시간이 많이 단축된 것을 확인할 수 있었다. (약 50배)

meeting_id와 date 둘다 동등 조건으로 탐색하고 있으니 다중 칼럼 인덱스 칼럼의 순서가 큰 영향을 미치진 않은 것 같다.

5개의 표본에서는 (date, meeting_id) 가 조금 더 빠른듯한 느낌이 있으나 인덱스를 결정하게 할 결정적인 요인은 되지 못했다.

3번째

java

sql

select
    event.id,
    event.date,
    event.end_time,
    event.meeting_id,
    event.start_time
from
    event
left outer join
    meeting 
        on event.meeting_id = meeting.id
where
    meeting.id = ?
    and event.date >= ?
order by
    event.date asc limit 1

인덱스 생성 전

쿼리 실행 시간

쿼리 실행 계획

인덱스 (date만)

쿼리 실행 시간

쿼리 실행 계획

인덱스 (meeting_id, date)

쿼리 실행 시간

쿼리 실행 계획

인덱스 (date, meeting_id)

쿼리 실행 시간

쿼리 실행 계획

-> 이 케이스 또한 where문 조건으로 meeting_id와 date 둘다 사용하고 있으니 단일 인덱스보다 다중 칼럼 인덱스를 설정한 것이 더 시간이 많이 단축된 것을 확인할 수 있었다. (약 100배)

이번에도 다중 칼럼 인덱스의 순서로 인해 많은 시간 차이가 나지 않기 때문에 쿼리 실행 계획을 파악해보았다.

쿼리 실행 계획의 rows 칼럼을 보면 (meeting_id, date)인 경우에는 약 976 건의 레코드를 읽어서 MySQL 엔진에서 필터링 후에도 976건이 남았다는 것을 파악할 수 있다.

(date, meeting_id) 인 경우에는 약 2022400 건의 레코드를 읽어서 MySQL 엔진에서 필터링 후에는 3842 건인 남았다는 것을 파악할 수 있다.

정확한 수치는 아니고 옵티마이저가 통계적으로 예측한 수치이지만, 이 지표만 보았을 때는 (meeting_id, date) 인 경우에 읽은 레코드의 수도 적고 mysql 엔진의 필터링 과정에서도 많은 필터링이 일어나지 않을 것으로 판단되는 것이 더 효율적인 인덱스인 것 같다.

쿼리 실행 계획

row 칼럼

옵티마이저가 비용을 산정하기 위해 얼마나 많은 레코드를 읽고 비교해야하는지 예측해본 레코드의 수

filtered 칼럼

MySQL 엔진에 의해 필터링되어 제거된 레코드는 제외하고 최종적으로 레코드가 얼마나 남았는지의 비율

스토리지 엔진에서 얼마나 레코드가 읽히고 MySQL 엔진에서 얼마나 필터링 되었는지 계산하는 방법 : row 칼럼 * filterd / 100

4번 테스트

java

mysql

select
    event.id,
    meeting.id,
    event.date,
    event.end_time,
    event.meeting_id,
    event.start_time,
    meeting.name
from
    event 
inner join
    meeting 
        on event.meeting_id = meeting.id
where
    meeting.id=?
    and (? is null or event.date>=?)
    and (? is null or event.date<=?)

인덱스 생성 전

쿼리 실행 시간

쿼리 실행 계획

인덱스 (date만)

쿼리 실행 시간

쿼리 실행 계획

인덱스 (meeting_id, date)

쿼리 실행 시간

쿼리 실행 계획

인덱스 (date, meeting_id)

쿼리 실행 시간

쿼리 실행 계획

-> (meeting_id, date) 순으로 복합 인덱스를 설정하는 것이 가장 시간이 단축되었다. (약 50배)

1번 테스트와 거의 동일한 상황으로 인덱스 레인지 스캔이 적용되면서 가장 효율적이었던 것 같다.

5번 테스트

java

mysql

select
    event.id,
    event.date,
    event.end_time,
    event.meeting_id,
    event.start_time
from
    event
left outer join
    meeting
        on event.meeting_id = meeting.id
where
    meeting.id=?
    and (event.date in (?))