MySQL의 Using temporary, Using filesort (+ 정렬 방식) 정리!
서론
서버 성능 향상을 위해 쿼리를 튜닝할 일이 잦아지면서, 최근에는 아무리 간단해보이는 쿼리라도 explain을 통해 실행 계획을 반드시 살펴보는 습관이 생겼다.
실행 계획에서 확인할 수 있는 정보는 매우 다양한데, 그 중 Extra 컬럼에 나오는 Using temporary와 Using filesort은 가능한 제거할 수 있도록 해야한다는 말을 동료 및 선임 개발자분들에게 많이 들었다.
해당 키워드가 대략적으로 무엇을 의미하는지는 느낌적으로 이해하고 있었지만, 왜 발생하는지 그리고 정확하게 무엇을 의미하는지 정리해보고자 한다.
MySQL의 정렬 방식
Using Temporary와 Using filesort는 모두 MySQL의 데이터 정렬 방식과 연관이 있다.
MySQL이 조회한 데이터를 정렬하는 방식은 크게 두 가지, 인덱스를 사용하는 방식과 filesort를 사용하는 방식으로 나눌 수 있다.
1. 인덱스를 이용해 정렬
조회 쿼리의 결과가 인덱스를 통해 정렬할 수 있는 상태라면 데이터를 그냥 인덱스 순서대로 내려주면되므로 별도의 정렬 작업을 거칠 필요가 없다.
하지만 만약 인덱스 컬럼의 순서와 어긋나는 정렬이 요구된다면 인덱스 순서로 정렬된 데이터를 다시 한 번 재정렬해줘야 하므로 정렬에 인덱스를 사용할 수 없게된다.
예컨대 (a, b, c)의 순서로 인덱스가 설정된 데이터에 대해서 아래와 같은 조합의 ORDER BY문에선 인덱스를 이용해 데이터를 정렬할 수 있다.
ORDER BY (a, b, c); // 정렬 컬럼과 인덱스 컬럼이 일치
ORDER BY (a, b); // 정렬 컬럼이 인덱스의 맨 앞 컬럼을 포함한 부분집합이면서 순서가 동일
아래는 인덱스를 정렬에 사용할 수 없는 경우이다.
ORDER BY b, c // 인덱스의 앞 쪽 컬럼 누락
ORDER BY a, c // 중간에 사용된 b가 누락
ORDER BY a, c, b // 순서 다름
ORDER BY a, b, c, d // 인덱스에 포함되지 않은 컬럼 존재
ORDER BY a, b desc, c // 순서가 일치하지만 b 컬럼의 정렬 방식이 달라 불가
다행히 모든 컬럼이 ORDER BY에만 명시될 필요는 없다.
ORDER BY에 포함되지 않더라도 인덱스 순서를 지키는 한 WHERE 조건에 컬럼이 명시되어도 정렬에 인덱스를 사용할 수 있다.
해당 케이스에 대한 인덱스 사용 예시는 아래와 같다.
// 사용 가능
WHERE a = ? ORDER BY b, c;
WHERE a = ? ORDER BY b;
WHERE a = ? and b = ? ORDER BY c;
WHERE a = ? and b = ? and c = ?;물론 WHERE 절에 컬럼을 명시하더라도, ORDER BY에서 컬럼의 순서가 잘못되거나 특정 컬럼이 누락되면 동일한 이유로 정렬에 인덱스를 사용할 수 없다. (WHERE절에서는 당연히 컬럼 순서가 바뀌어도 무관하다)
참고로, WHERE 절이 동등비교가 아닐 때에도 정렬 시 인덱스를 사용할 수 없으므로, 그럴 땐 차라리 ORDER BY 조건에 명시적으로 해당 컬럼을 넣어주는 것이 더 나을 수 있다.
// 동등비교가 아니므로 인덱스 정렬 불가
WHERE a like 'something%'
ORDER BY b, c;
// 인덱스 정렬 가능
WHERE a like 'something%'
ORDER BY a, b, c;
2. filesort를 이용한 정렬
filesort를 이용한 정렬은 조회된 데이터를 인덱스로 정렬할 수 없어 내부적으로 추가적인 정렬 작업을 수행하는 경우를 말한다.
그리고 filesort를 이용하는 방식은 두 가지로 나뉘는데, 이것이 글의 찐 주제인 Using temporary와 Using filesort이다.
Using filesort
Using filesort는 쿼리에서 첫 번째로 조회하는 테이블(드라이빙 테이블이라고 한다)에 대해서만 정렬이 필요한 경우이다.
일반적으론 단일 테이블에 대해 실행한 쿼리가 정렬 시 인덱스를 사용하지 못하는 경우 발생한다.
예컨대, 위에서 예시로 들었던 아래의 쿼리 모두에서 Using filesort가 발생한다.
ORDER BY b, c // 인덱스의 앞 쪽 컬럼 누락
ORDER BY a, c // 중간에 사용된 b가 누락
ORDER BY a, c, b // 순서 다름
ORDER BY a, b, c, d // 인덱스에 포함되지 않은 컬럼 존재
ORDER BY a, b desc, c // 순서가 일치하지만 b 컬럼의 정렬 방식이 달라 불가filesort가 필요한 상황이 발생하면, MySQL에선 내부적으로 테이블을 Sort Buffer에 옮겨 정렬하는 작업을 거친다.
정렬이 완료된 후, 결합이 필요한 데이터가 있다면 합치는 작업을 거친 후 데이터를 내려줄 것이다. (물론 단일 테이블인 경우엔 그대로 내려준다)
Using temporary
Using temporary는 정렬 작업을 위해 임시 테이블이 필요한 경우를 말한다.
인덱스를 통해 정렬을 수행할 수 없거나, filesort만으로 정렬을 완료할 수 없는 경우 임시 테이블을 생성해 그곳에 조회된 데이터들을 모두 밀어넣고 정렬하는 작업을 거친다.
조인 등을 통해 테이블 여러 개가 혼합되는 상황에서 아무래도 발생할 가능성이 높겠다.
임시테이블을 사용하는 경우, 조회된 데이터 조합을 모두 밀어넣어야하므로 당연히 인덱스나 filesort보다 성능이 좋지 못하다.
조회되는 데이터가 많으면 많을수록 속도는 더 느려질 것이다.
메모리 기반 임시테이블, 디스크 기반 임시테이블
임시테이블은 테이블에 올릴 데이터의 크기에 따라 메모리 혹은 디스크에 저장된다.
임시테이블을 통한 작업이 메모리에서 모두 처리가 된다면 그나마 다행이다라고 말할 수 있지만(물론 그나마가 포인트다), 디스크에 임시테이블이 생성되어 정렬 작업 시 디스크 I/O가 발생하면 성능에 치명적인 영향을 끼칠 수 있다.
임시테이블 작업이 메모리에서 처리되는지 디스크에서 처리되는지는 MySQL의 tmp_table_size 속성에 따라서 결정된다.
MySQL의 공식문서에 정의된 tmp_table_size 속성에 대한 설명은 아래와 같다.
Defines the maximum size of any individual in-memory internal temporary table created by the TempTable storage engine. When the tmp_table_size limit is reached, MySQL automatically converts the in-memory internal temporary table to an InnoDB on-disk internal temporary table. The default tmp_table_size setting is 16777216 bytes (16 MiB).
출처: https://dev.mysql.com/doc/refman/8.3/en/internal-temporary-tables.html
만약 임시테이블에 사용될 데이터가 tmp_table_size 값을 넘어가면 디스크 기반의 임시 테이블이 사용될 것이며, tmp_table_size의 기본값은 16MB라고 명시되어있다.
한글 문자 하나가 2바이트임을 고려해볼 때, 16MB의 크기는 약 한글 830만자 정도의 크기이다.
데이터 블록 하나가 대략 한글 500자 정도(자기소개서 문항 하나 정도 분량)로 구성되어있다고 가정했을 때 대략 1만 7천개 이상의 데이터가 임시 테이블에 올라오면 디스크 기반 임시테이블이 사용될 것으로 추측할 수 있다. (역시 그래도 생각보단 널널하다)
실제 Using temporary가 발생하는 테이블에 대하여 한 개의 데이터 레코드의 평균적인 크기를 기반으로 대략적인 수치를 계산해 임시 테이블이 메모리 방식으로 작동할지 디스크 기반 방식으로 작동할지 예상해볼 수 있겠다.
물론 최대한 filesort가 발생하는 상황을 없앨 수 있도록 쿼리를 튜닝하는 시도가 필요하며, 불가피하다면 해당 부분을 애플리케이션 서버 단에 맡기는 것 또한 충분히 합리적인 고려대상이 될 것이다.
참고