인덱싱을 이용한 조회 쿼리 성능 향상 및 삽입 시의 영향 지표화
데이터를 빠르게 검색하고 조회하기 위해 사용되는 특정 칼럼 또는 칼럼의 조합에 대한 정렬된 데이터 세트이다.
데이터가 정렬된채 삽입이 일어나기에 삽입 속도가 느리지만, 그만큼 조회에서의 이점을 얻는다.
본격적인 사용내용을 말하기에 앞서 DDIP 프로젝트에 Index를 적용하게된 이유는 다음과 같다.
DDIP은 선착순 기반의 링크 시스템이기 때문에, 많은 사용자가 하나의 이벤트에 대해조회하거나 이벤트에 참여할 것을 고려해야하였다.
그렇기에 동시성처리와 더불어 조회 쿼리의 성능을 향상시키는 것은 상당히 중요한 관건으로 고려되었고 중요도가 높은 API(사용자 조회가 많을 것으로 예상되는 API)와 관련된 컬럼에 인덱스를 설정하는 작업이 필요로 되었다.
우선 Index를 설정하기에 확인해야하는 부분이 몇가지 존재한다.
- Create(데이터 삽입)에 어떠한 영향을 끼치는가
- 인덱스 카디널리티를 고려하였는가
- filtered를 고려하였는가?
우선적으로 컬럼에 인덱스를 설정하면 삽입이 느려진다라는 사실을 보편적으로 알려져있다.
그 이유는 데이터의 삽입 작업으로 인해 인덱스의 노드가 분리되거나 새로운 노드가 추가되게 된다.
이로 인해 인덱스의 재조정 작업이 필요할 수 있다.
재조정 작업은 인덱스의 구조를 최적화하여 빠른 검색을 유지하기 위해 수행되는 작업이지만, 삽입 작업에는 추가적인 시간과 리소스가 소요되게된다.
결론적으로 삽입하게된다면 인덱스 순서에 맞춰 정렬이 일어남으로 삽입이 느려지는 것이다.
인덱스 카디널리티(Index Cardinality)는 데이터베이스 인덱스의 유일한 값을 나타내는 데 사용되는 메트릭으로 인덱스 카디널리티는 인덱스에 저장된 고유한 값의 수를 의미한다.
그렇기에 해당값이 유일하지 않고 다수가 존재할 경우 인덱스 카디날리티는 떨어져 인덱스 선택도가 떨어져 인덱스를 타지않는 상황이 발생한다.
EXPLAIN 을 통하여 SQL문의 실행계획을 조회하게 되면, 여러 Filed값과 함께 filtered 라는 값을 볼 수 있다.
이 filtered란 쿼리 실행 결과에서 필터링된 행의 비율을 나타내는 값으로서 0부터 100까지의 범위로 표현되며, 100은 모든 행이 필터링되지 않았음을 나타내고, 0은 모든 행이 필터링되었음을 나타낸다.
보통 filtered 값이 100이면 필터링 조건에 맞는 모든 행이 포함되어 있음을 의미한다. 반면에 filtered 값이 0에 가까울수록 필터링 조건에 맞는 행이 거의 없음을 의미한다.
- 결국 인덱싱이나 Not Null, Unique를 걸지말지를 판단하는 것 또한
EXPLAIN을 통한 해당 SQL 문의 실행 계획을 파악하여, 성능 수치가 어느정도인지가 파악한 후 개선시키는 점이 중요하다. - 또한, 기존의 SQL문을 보고 최적화 지점을 찾고 이를 지표로 만들어 차이를 분석하는 것이 중요하다.
자 그럼 이제 띱에 적용을 하러가보자
- 사용 Column : memberId, startDateTime, endDateTime
select
e1_0.id,
e1_0.limit_count,
e1_0.remain_count,
e1_0.created_at,
e1_0.end_date_time,
e1_0.start_date_time,
e1_0.member_id,
e1_0.success_content,
e1_0.success_form,
e1_0.success_image_url,
e1_0.thumbnail_image_url,
e1_0.title,
e1_0.updated_at,
e1_0.uuid
from
event e1_0
where
e1_0.member_id=?
order by
e1_0.created_at desc
limit
?,?인덱싱 사용 전
테이블 스캔 방식 : Ref (memberId Foriegn Key사용)
인덱스 적용 목표 및 기대 효과
- memberId와 date 메타데이터를 조합하여 인덱스를 만들면 조회 효율 성능폭이 더 좋을거라는 기대가존재함
- 하지만, 실제 멀티컬럼 인덱스로 테스트한 결과 인덱스를 타지 않음
- 현재 상황에선 memberId로만 값을 두어야함
- 만약 날짜 open 상태값을 DB에 저장시키면? 인덱스를 더 빠르게 타지않을까?
- 근데 이러면 컬럼 증가로 인해 고려사항(서비스 쪽에서도 사용가능)
- 인덱스 카디널리티가 낮아지게 된다.
인덱싱 사용 후
- 지정한 인덱스
-
name= "event_idx_member_id_date" -
columnList= "member_id, start_date_time, end_date_time”
-
- 직접 SQL을 작성하여 비교를 해본결과 filetered 값이 현저히 낮음
- filterd 값이란, 쿼리 실행 결과에서 필터링된 행의 비율을 나타내는 값이다. 즉, filterd가 높을수록 적은 수의 행을 가져와 인덱스가 잘 먹혔다고 볼수 있다.
- 그렇기에 Disk I/O작업이 더 많고 불필요한 컬럼을 읽어 오는 경우가 많기에 옵티마이저가 지정한 인덱스를 실행계획으로 잡지않고 FK를 이용한 ref index방법을 선택하는 것으로 판단하여 인덱스를 적용하지 않는 것으로 판단하였다.
해당 인덱스 사용 시 Create 및 Delete 작업 지표
- 인덱스를 사용하는 결과 기대치는 이벤트 생성 / 삭제 시 작업이 느려진다는 점이 있지만, 실제적으로 인덱스를 타지않기 때문에 지표상의 변경은 없을 것으로 예상된다.
- 사용 Column : UUID
select
e1_0.id,
e1_0.limit_count,
e1_0.remain_count,
e1_0.created_at,
e1_0.end_date_time,
e1_0.start_date_time,
e1_0.member_id,
m1_0.id,
m1_0.email,
m1_0.password,
e1_0.success_content,
e1_0.success_form,
e1_0.success_image_url,
e1_0.thumbnail_image_url,
e1_0.title,
e1_0.updated_at,
e1_0.uuid
from
event e1_0
join
member m1_0
on m1_0.id=e1_0.member_id
where
e1_0.uuid=?인덱싱 사용 전
- Event 정보에 대해선
Full Table Scan을 진행 - Join에 사용되는 MemberId의 경우 eq_ref 사용전략
인덱스 적용 목표 및 기대효과
- UUID를 unique column으로 지정할 경우 Unique Index Scan을 통해 조회 속도 증가
인덱싱 사용 후
- Unique Index Scan을 이용하기에 filtered 값이 대폭 상승하였으며, 그로인한 조회시간도 단축되게된다.
해당 인덱스 사용 시 Create 및 Delete 작업 지표
UUID를 Index로 설정하였기 때문에 Create 속도가 느려
INSERT INTO event
(limit_count, remain_count, created_at, end_date_time, start_date_time, member_id, success_content, success_form, success_image_url, thumbnail_image_url, title, updated_at, uuid)
VALUES
(2, 2, '2023-12-01 18:00:00', '2023-12-05 18:00:00', '2023-12-01 18:00:00', 1001, NULL, NULL, NULL, NULL, 'test', NULL, UNHEX(REPLACE(UUID(), '-', '')));
단건의 경우 성능적으로 차이점을 느낄 수 없다.
그렇다면 대량의 건수를 비교해보자. (5000개 삽입 시나리오)
UUID에 Unique Index가 존재하는 경우
UUID에 Unique Index가 없는 경우
근소한차이로 Unique Index가 존재하지않는 Create더 빠르다.
5000건이라는 적은 수치로 비교하였기에 수치가 체감상 크지않지만 만약 1000000건의 이벤트가 들어간다면? 그차이는 명확할 것이다.
- 사용 column : uuid(event),token(uuid)
//이벤트 검색
select
e1_0.id,
e1_0.limit_count,
e1_0.remain_count,
e1_0.created_at,
e1_0.end_date_time,
e1_0.start_date_time,
e1_0.member_id,
e1_0.success_content,
e1_0.success_form,
e1_0.success_image_url,
e1_0.thumbnail_image_url,
e1_0.title,
e1_0.updated_at,
e1_0.uuid
from
event e1_0
where
e1_0.uuid=? for update
// event uuid와 token으로 참여 이력 검색
select
s1_0.id
from
success_record s1_0
join
event e1_0
on e1_0.id=s1_0.event_id
where
e1_0.uuid=?
and s1_0.token=?
limit
?인덱스 사용 전
- 사용 column uuid
Full Table Scan(이벤트 조회)
- 사용 column uuid, token (이미 참여한 회원인지 조회)
-
Full Table Scan을 통해 모든 이벤트를 탐색 - 이후 join된 이벤트 id와 같은 컬럼을(success record) 찾음
인덱스 적용 목표 및 기대효과
- UUID를 unique column으로 지정할 경우 Unique Index Scan을 통해 조회 속도 증가
- token의 경우 인덱스 사용이 목적에 맞지않음 (조회 보다는 삽입이 더많을 것으로 생각)
인덱스 사용 후
- Unique Index Scan을 통해 조회 속도향상
해당 인덱스 사용 시 Create 및 Delete 작업 지표
- 데이터 삽입시 UUID를 사용하지는 않기 때문에 삽입은 인덱스 미사용과 동일 지표
Application Level(Logic)에서 최적화를 진행하여보자
현재의 로직같은 경우에는 event의 uuid와 successRecord를 통해 이미 참여한 회원인지를 비교하지만, 이 때문에 불필요한 join이 발생하게되기에 event_id를 통해 조회하는 것으로 로직을 변경
select
s1_0.id
from
success_record s1_0
where
s1_0.event_id=?
and s1_0.token=?💡 그렇다면 event_id를 사용하는김에 멀티컬럼 인덱싱까지?
- 현재의 경우 event_id(fk)와 token unique을 사용하여 fitered 수치가 높음
- event_id + token 을 멀티컬럼인덱스로 만든다면?
- 하지만, 실행결과 인덱스를 타지않음
- 사용 column: uuid
//이벤트 탐색
select
e1_0.id,
e1_0.limit_count,
e1_0.remain_count,
e1_0.created_at,
e1_0.end_date_time,
e1_0.start_date_time,
e1_0.member_id,
m1_0.id,
m1_0.email,
m1_0.password,
e1_0.success_content,
e1_0.success_form,
e1_0.success_image_url,
e1_0.thumbnail_image_url,
e1_0.title,
e1_0.updated_at,
e1_0.uuid
from
event e1_0
join
member m1_0
on m1_0.id=e1_0.member_id
where
e1_0.uuid=?
//이벤트 uuid + token 검색
select
s1_0.id,
s1_0.created_at,
s1_0.event_id,
s1_0.form_input_value,
s1_0.token,
s1_0.updated_at
from
success_record s1_0
join
event e1_0
on e1_0.id=s1_0.event_id
where
e1_0.uuid=?
order by
s1_0.created_at
limit
?,?인덱스 사용 전
- join된 member는 fk 인덱스를 통해 실행됨
- 마찬가지로 UUID를 Full Table Scan을 실행
인덱스 적용 목표 및 기대효과
- UUID를 unique column으로 지정할 경우 Unique Index Scan을 통해 조회 속도 증가
인덱스 사용 후
- Unique Index Scan을 통해 조회속도향상
- 현재 띱의 도메인 구조가 방대하지 않고, 서비스가 방대하진 않기에 인덱스를 별도적으로 적용하거나, Multi Column Index를 사용하여 이점을 얻을 부분(개선 부분)을 발견할 수는 없었다.
- 현재 시점에선 Unique Index Scan만으로도 조회 속도를 충분히 개선할 수 있었지만, 추후 서비스의 확장에 따라 인덱스는 최우선으로 고려되어야하는 사항인 만큼 그 중요도가 높다고 볼 수 있다.
- 효율적인 인덱스를 사용하기 위해선 충분히 지표를 비교한 후 조합 및 사용을 하는 것을 추천한다.