MySQL - 인덱스를 경험해보자

인트로

대략 3M 개 레코드가 존재하는 테이블에서 범위 검색을 진행할 때, 성능을 개선할 수 있는 방법에 대해서 모색했습니다. 개선 방법은 스케일 아웃, 스케일 업, 테이블 변경 등이 있었지만, 도입하기 위해서는 많은 개발 비용이 발생했습니다. 그래서 개발 비용 없이 성능 개선할 수 있는 인덱스를 고려했습니다.

쿼리

SELECT createdDate, memberId,count(id) as count
FROM POST
WHERE memberId = 3 AND createdDate BETWEEN '2022-02-01' and '2022-05-01'
GROUP BY memberId, createdDate;

AS-IS 인덱스 없이

처리 결과 확인

300 만 건 중 90 개의 레코드를 반환하는데 10s가 소요됐습니다.

실행계획

실행계획을 보면 전체 테이블을 스캔하는 모습을 볼 수 있습니다. 전체 테이블을 스캔하면서 Sequential IO가 발생한다는 것을 예상할 수 있습니다.

서비스를 제공하면서 DB에서 데이터 조회에 소요되는 시간이 1초가 넘어간다면 많은 사용자들이 이탈하게 될 것입니다. 그래서 조회 성능을 개선할 필요가 있는데, 시스템이나 테이블 변경 없이 조회 성능을 높일 수 있는 방법인 인덱스를 사용할 수 있습니다.

TO-BE 잘못된 인덱스 설계 1 - 생성 날짜 컬럼으로 인덱스 생성

인덱스 추가

생성 날짜(createdDate)를 기준으로 인덱스를 추가했습니다.

CREATE INDEX POST__index_created_date
    ON POST (createdDate);

처리 결과 확인

처리 결과는 처참합니다… 인덱스를 설정하지 않았을 경우 대비 2배 더 느려졌습니다.

실행 계획 확인

실행 결과를 확인해보면 Index Range Scan으로 724K 레코드를 읽게 되면서 724K의 Random IO가 발생했다는 것을 알 수 있는데, 날짜 범위 내의 레코드가 724K 개 있기 때문에 이러한 결과가 발생한 모습을 볼 수 있습니다.

TO-BE 잘못된 인덱스 설계 2 - 회원 식별자로 인덱스 생성

인덱스 추가

회원 식별자(memberId)로 인덱스를 추가해서 쿼리를 실행했습니다.

CREATE INDEX POST__index_member_id
         ON POST (memberId);

처리 결과 확인

**Error Code: 2013. Lost connection to MySQL server during query : 쿼리가 정해진 제한 시간 내에 종료되지 못해서 발생한 에러인데, 해당 에러가 발생할 만큼 결과가 처참합니다.

실행 계획 확인

실행 결과를 확인해보면 Index Range Scan으로 1.5M 레코드를 읽게 되면서 1.5M의 Random IO가 발생했다는 것을 알 수 있습니다.

실패 이유

1.5M의 Random IO가 발생하면서 쿼리가 제한 시간 내에 처리하지 못한 모습을 볼 수 있습니다. 따라서 멀티 컬럼 인덱스를 활용해 인덱스로 가져오는 레코드의 개수를 줄일 필요가 있습니다.

TO-BE : 성능 개선

인덱스 추가

CREATE INDEX POST__index_member_id_created_date
         ON POST (memberId, createdDate);

처리 결과 확인

처리에 소요되는 시간이 1.157s 입니다. 인덱스를 설정하지 않았을 때 대비 10배 빨라졌고, 인덱스를 잘못 설정했을 대비 대략 20배 정도 빨라진 모습을 볼 수 있습니다.

실행 계획 확인

실행 결과를 확인해보면 Index Range Scan으로 484K 레코드를 읽는 다는 것을 볼 수 있는데, 찾는 사용자의 포스트 중에서 일정 범위를 가져오게 되면서 484K 레코드를 읽게 됩니다.

마지막으로

• 데이터가 많을 때 인덱스를 활용해서 조회 성능을 높일 수 있습니다!
• 인덱스를 잘못 설정할 경우 성능이 더 느려집니다.
• 멀티 컬럼 인덱스를 활용해 성능을 높였지만, 추가적으로 개선할 수 있어보입니다!