요구사항
•
인덱스 추가를 통해 쿼리의 성능을 개선한다.
1. 콘서트 정보 전체 목록 조회
쿼리분석
인덱스를 추가하여 성능을 개선할만한 쿼리에 대해 찾아보았다.
1.
콘서트 정보 리스트 조회
추후 서비스가 거대해져 서비스에서 예약을 하는 콘서트의 개수가 많아지고 점점 더 많은 양의 콘서트 데이터가 쌓일 것이다. (과거에 진행했던 콘서트도 계속해서 테이블에 쌓이고 있을 것이기 때문에..)
2.
콘서트 id로 콘서트 정보들을 조회
어느 한 콘서트가 성공을
콘서트 목록을 조회하는 쿼리는 다음과 같다.
create table concert_detail
(
id bigint auto_increment primary key,
concert_id bigint not null,
concert_date datetime not null,
available_reservation_date datetime not null,
create_date datetime default CURRENT_TIMESTAMP not null,
update_date datetime default CURRENT_TIMESTAMP not null
);
SELECT * FROM concert_detail cd
INNER JOIN concert c
ON cd.concert_id = c.id
WHERE cd.concert_date > NOW() and cd.available_reservation_date < NOW();
SQL
복사
기존 테이블에는 concert_id 컬럼에 대해서만 index가 걸려있었다.
더미 데이터의 양이 많아 인덱스를 변경하면서 테스트하기보다는
같은 컬럼을 가진 테이블을 여러 개 만들고 각 테이블마다 인덱스를 다르게 설정하여 테스트를 진행하였다.
테이블 별 데이터 세팅 및 인덱스 설정
concert 테이블에는 5만건의 데이터
concert_detail 테이블에는 200만건의 데이터를 생성하여 진행하였다.
쿼리는 동일하게 아래의 쿼리를 실행해보았다.
SELECT * FROM concert_detail cd
WHERE
cd.concert_date > NOW() and
cd.available_reservation_date < NOW();
SQL
복사
각 테이블 쿼리 실행 결과
1.
Table Name : concert_detail
Index : concert_id (단일 인덱스)
쿼리 실행 결과
TYPE: ALL
소모 시간: 1m 8s
2.
Table Name : concert_detail2
Index : concert_id, concert_date, available_reservation_date (단일인덱스)
쿼리 실행 결과
TYPE: ALL
소모 시간: 1m 6s
3.
Table Name : concert_detail3
Index : concert_id, concert_date, available_reservation_date (단일인덱스)
(concert_date, available_reservation_date) 복합인덱스
쿼리 실행 결과
TYPE: ALL
소모시간 : 1m 4s
4.
Table Name : concert_detail4
Index : concert_id, concert_date, available_reservation_date (단일인덱스)
(concert_date, available_reservation_date) 복합인덱스
(concert_id, concert_date, available_reservation_date) 복합인덱스
쿼리 실행 결과
TYPE : ALL
소모시간: 1m
테스트 결과 분석
200만건의 콘서트 정보에 대해서 datetime 컬럼에 대해 인덱스를 설정하였지만 인덱스를 사용하지 않고 테이블에 대해 풀 스캔이 적용되었다.
datetime 컬럼의 카디널리티는 365일 * 24시간 * 60분 으로 52만 정도의 경우의 수가 있었지만
더미 데이터의 양과 카디널리티가 적합하지 않아 인덱스가 적용되지 않은 모습이였고
실제로 인덱스의 유무, 개수, 복합키 유무와는 상관없이 비슷한 시간이 걸렸다.
위와 같은 결과가 나온 원인으로는
1.
쿼리가 적합하지 않다.
2.
데이터의 양과 카디널리티가 충분하지 않다.
3.
옵티마이저가 NOW() 시간을 기반으로 어느 한쪽에 데이터가 몰려있어 인덱스를 사용하기보단 풀스캔을 선택했다.
세 가지 정도로 생각해 보았다.
2번과 3번은 확인결과 원인이 아닌 것 같았다.
데이터의 양을 400만건으로 늘리고 카디널리티도 초까지 랜덤으로 설정하여 중복도를 낮췄지만 결과는 같았다.
LocalDateTime의 인자값으로 랜덤값을 넣어 일정한 분포로 데이터를 넣었기 때문에 어느 한쪽에 데이터가 극단적으로 몰리는 상황은 일어나지 않았다.
따라서 쿼리를 개선해보기로 결정하였다.
쿼리 개선
단일 인덱스와 복합인덱스를 가지고 있는 concert_detail4 테이블에 명령하는 쿼리를 수정해 보기로 하였다.
•
concert_detail4 의 index
concert_id, concert_date, available_reservation_date (단일인덱스)
(concert_date, available_reservation_date) 복합인덱스
(concert_id, concert_date, available_reservation_date) 복합인덱스
기존에는 SELECT *으로 모든 컬럼을 조회하였는데
커버링 인덱스가 사용될 수 있도록 쿼리를 아래와 같이 수정해 보았다.
SELECT
cd.id,
cd.concert_id,
cd.concert_date,
cd.available_reservation_date
FROM concert_detail4 cd
WHERE
cd.concert_date > NOW() and
cd.available_reservation_date < NOW();
SQL
복사
•
쿼리 실행 결과
EXPLAIN 결과 인덱스를 사용하여 조회를 하였고
기존에 1분이였던 쿼리 실행 시간이 35초로 절반 가까이 줄어들었다.
테이블 개선
concert_detail4 테이블과 같이 인덱스를 사용하기 위해선
여러 개별 인덱스와 복합 인덱스를 추가했어야 했다.
위의 테이블 구조에서 예약이 가능한 상태인지 아닌지에 대한 status값을 컬럼으로 추가하고
status에 인덱스를 걸어 WHERE조건으로 찾아오면 어떨까 라는 생각을 해보게 되었다.
시간이 지나면서 이미 끝난 콘서트들이 계속해서 쌓일것이다.
그렇게 되면 콘서트 정보 테이블에서는 현재 예약가능한 콘서트 row수는 이미 끝난 콘서트 row수에 비해 압도적으로 적을 것이다.
비록 status값을 index를 걸게 되면 카디널리티는 매우 낮겠지만 (status값은 기껏해야 3~4개정도 될거같다…)
내가 조회해와야 할 콘서트정보 (예약이 가능하면서 콘서트가 아직 시작을 안한 경우)를 조회하기 위해서
수많은 다른 상태값들의 row들을 이 index로 걷어내고 조회를 할 수 있겠다는 생각을 하였다.
•
status 컬럼을 추가한 테이블
create table status_concert_detail
(
id bigint auto_increment
primary key,
concert_id bigint not null,
concert_date datetime not null,
available_reservation_date datetime not null comment '예약 가능한 날짜',
create_date datetime default CURRENT_TIMESTAMP not null,
update_date datetime default CURRENT_TIMESTAMP not null,
status smallint default 0 null
);
create index status_concert_detail_concert_id_index
on status_concert_detail (concert_id);
create index status_concert_detail_status_index
on status_concert_detail (status);
---------------------------------------------------------------------------------------------
// 실행한 쿼리
SELECT * FROM status_concert_detail cd
WHERE cd.status = 4;
SQL
복사
◦
200만개의 데이터 중에서 6만개의 예약 가능한 콘서트를 조회했다.
▪
status 컬럼에 인덱스를 건 테이블 조회 결과 (테이블 명 : status_concert_detail)
▪
status 컬럼없는 테이블 (테이블 명 : concert_detail4)
다른 두 테이블에 대해 결과 rows가 6만건 근처로 나오도록 더미 데이터를 세팅하고 성능을 비교해보았더니
status 컬럼에 인덱스를 건 테이블의 속도가 2배 가까이 빨랐다.
결론
•
범위 조건에서의 인덱스 추가로 인한 성능 향상은 기대하기 힘들다.
◦
범위 조건은 시작점부터 끝점까지 연속적으로 데이터를 스캔해야 하므로 ‘=’연산에 비해 상대적으로 많은 데이터에 대해 접근이 필요하다.
◦
이런 경우에는 데이터 파티셔닝을 통해 특정 기간의 데이터를 물리적으로 분리하는 방식을 선택하는 것이 적합할 수 있다.
◦
필요한 컬럼만 조회하도록 쿼리를 개선하여 쿼리의 처리량을 줄여 성능 향상을 이끌어 낼 수 있다.
•
Index를 설정하는 기준이 꼭 카디널리티만은 아니다.
◦
내가 조회해야 할 데이터가 전체 테이블 데이터 중 소량이라면 Index로 인한 성능향상이 충분히 가능하다.
◦
테이블의 데이터가 매우 많은 상태라면 (몇 억건 이상) Index를 건 컬럼의 카디널리티가 낮더라도 많은 양의 데이터를 제외하고 조회를 할 수 있으므로 인덱스를 충분히 설정할 만 하다.
