디프만에서 프로젝트를 하면서 성능 향상에 관심을 가지다 보니, MySQL 실행 계획을 분석하게 되었습니다.
그러다 타임라인 조회에 사용되는 쿼리의 실행계획을 보던 도중, Using Filesort가 사용되는 것을 발견하였습니다.
MySQL 실행계획
실행 계획은 클라이언트가 MySQL 서버에 요청한 SQL 문을 어떻게 데이터를 불러올 것인지에 관한 계획, 경로를 의미합니다.
참고로 실제 수행 순서가 아닌 MySQL 통계 정보를 기반으로 계산한 예측값입니다.
실행 계획은 SQL 문 앞에 Explain 을 붙여 확인할 수 있습니다.
실행계획에 대한 자세한 설명은 아래 링크에 적어두었고, 이 포스트에서는 해결 과정에 대해서 작성하도록 하겠습니다.
https://velog.io/@penrose_15/MySQL-실행계획-분석하기
Using Filesort
쿼리에서 정렬(ORDER BY) 를 처리하기 위해 2가지의 방법을 사용합니다.
- 인덱스
- filesort
조회 쿼리의 결과가 인덱스를 통해 정렬할 수 있는 상태라면 인덱스의 순서대로만 내려주면 되지만, 인덱스의 컬럼의 순서와 어긋나는 정렬이 요구되면 정렬에 인덱스를 사용하지 못하게 됩니다.
이때 filesort가 사용되는데 filesort는 서버에서 직접 조회한 레코드를 정렬용 메모리 버퍼에 복사하여 퀵 소트/힙 소트로 정렬을 합니다.
아래는 인덱스를 정렬에 사용할 수 있는 경우입니다.
- ORDER BY에 명시된 컬럼이 제일 먼저 읽는 테이블에 속해야 한다.
- ORDER BY절의 순서가 인덱스와 일치해야 한다.
- WHERE 절에서 첫번째로 읽는 테이블에 대한 조건이 있다면, ORDER BY도 같은 인덱스를 사용할 수 있어야 합니다.
저 같은 경우, WHERE memory.member_id = 1 ORDER BY memory.record_at DESC
에서 where 절의 동등 조건(WHERE memory.member_id = 1)에서 인덱스를 탔으나,
ORDER BY memory.record_at DESC의 경우 where 절에 사용된 인덱스를 타지 못해 filesort가 발생한 것이었습니다. (3번 케이스)
Using filesort는 많은 부하를 일으키므로 이를 튜닝하는 것이 좋습니다.
해결 과정
저 같은 경우, 복합 인덱스를 추가하여 해결하였습니다.
CREATE INDEX idx_member_record on memory_entity (member_id, record_at DESC);WHERE 절의 동등 조건절에 사용된 컬럼을 인덱스의 첫번째 칼럼으로 설정하고, ORDER BY에 사용된 컬럼을 두 번째 인덱스 칼럼으로 설정하였습니다.
이후 실행계획 확인 결과, filesort가 삭제되었다는 것을 확인할 수 있었습니다.
추가적으로 인덱스 조회 전/후의 SQL 속도 차이를 정확히 알아보고자 MySQL의 Profiling을 활용하였습니다.
profiling 설정을 활성화하면 실행되는 쿼리문의 수행 시간을 기록해줍니다.
profiling 속성은 부하를 생성하므로 기본적으로 꺼져 있어 사용을 하려면 profiling 을 켜놔야 합니다.
SET profiling=1;활성화 시킨 후, 속도를 측정할 쿼리를 실행한 후, SHOW profiles 로 확인을 해볼 수 있습니다. (오차가 있을 수 있으므로 여러 번 측정하여 평균을 구했습니다.)
(유저 10000명, 유저 1명당 Memory 개수 30개, Memory 1 개당 Memory_detail 1개, Stroke 2개, pool 1개)
인덱스 추가 전 측정 시간(평균 0.5725초)
인덱스 추가 후 측정 시간(평균 0.003초)
인덱스 적용 시 sql 수행 시간이 50% 향상 된 것을 확인 할 수 있었습니다.
