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에 옮겨 정렬하는 작업을 거친다.

 

정렬이 완료된 후, 결합이 필요한 데이터가 있다면 합치는 작업을 거친 후 데이터를 내려줄 것이다. (물론 단일 테이블인 경우엔 그대로 내려준다)

https://velog.io/@jsj3282/31.-MySQL-%EC%8B%A4%ED%96%89-%EA%B3%84%ED%9A%8D-MySQL%EC%9D%98-%EC%A3%BC%EC%9A%94-%EC%B2%98%EB%A6%AC-%EB%B0%A9%EC%8B%9D2

 

Using temporary

Using temporary는 정렬 작업을 위해 임시 테이블이 필요한 경우를 말한다.

 

인덱스를 통해 정렬을 수행할 수 없거나, filesort만으로 정렬을 완료할 수 없는 경우 임시 테이블을 생성해 그곳에 조회된 데이터들을 모두 밀어넣고 정렬하는 작업을 거친다.

 

조인 등을 통해 테이블 여러 개가 혼합되는 상황에서 아무래도 발생할 가능성이 높겠다.

https://velog.io/@jsj3282/31.-MySQL-%EC%8B%A4%ED%96%89-%EA%B3%84%ED%9A%8D-MySQL%EC%9D%98-%EC%A3%BC%EC%9A%94-%EC%B2%98%EB%A6%AC-%EB%B0%A9%EC%8B%9D2

 

임시테이블을 사용하는 경우, 조회된 데이터 조합을 모두 밀어넣어야하므로 당연히 인덱스나 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가 발생하는 상황을 없앨 수 있도록 쿼리를 튜닝하는 시도가 필요하며, 불가피하다면 해당 부분을 애플리케이션 서버 단에 맡기는 것 또한 충분히 합리적인 고려대상이 될 것이다.

 

참고

https://velog.io/@jsj3282/31.-MySQL-%EC%8B%A4%ED%96%89-%EA%B3%84%ED%9A%8D-MySQL%EC%9D%98-%EC%A3%BC%EC%9A%94-%EC%B2%98%EB%A6%AC-%EB%B0%A9%EC%8B%9D2

https://jojoldu.tistory.com/481

https://dev.mysql.com/doc

https://enterone.tistory.com/233