MySQL의 Using filesort와 Using temporary

MySQL의 filesort

MySQL 8.0 버전 기반으로 공식문서에는

“filesort is the algorithm MySQL uses to sort results when it cannot use an index to retrieve the rows in sorted order.”

• MySQL 8.0 Reference Manual, 8.2.1.14 How MySQL Optimizes ORDER BY

즉, 인덱스를 통해 정렬된 순서로 가져올 수 없을 때 MySQL이 사용하는 정렬 알고리즘을 의미합니다.

“파일”이라는 단어 때문에 디스크에 쓰는 정렬이라고 오해하기 쉽지만, filesort는 ‘정렬 알고리즘’이며, 반드시 파일(디스크)를 쓰는 것은 아닙니다.

 

 

MySQL의 정렬 방식

MySQL이 조회한 데이터를 정렬하는 방식은 크게 두 가지입니다.

인덱스를 사용하는 방식과 filesort 를 사용하는 방식.

 

인덱스를 이용하여 정렬

조회 쿼리의 결과가 인덱스를 통해 정렬할 수 있는 상태라면 데이터를 인덱스 순서대로 내려주면 되므로 별도로 정렬할 필요가 없습니다.

 

하지만 인덱스 칼럼의 순서와 어긋나는 정렬이 요구되는 인덱스를 사용할 수 없는 상태라면 재정렬을 해줘야 합니다.

예를 들어, (post_id, user_id, created_at)의 순서로 복합 인덱스가 걸려있다고 가정해보겠습니다.

 

ORDER BY (post_id, user_id, created_at); // -> 정렬 칼럼이 인덱스 칼럼 순서와 일치하므로 인덱스 이용가능
ORDER BY (post_id, user_id); // -> 정렬 칼럼이 인덱스의 선행 칼럼을 포함한 부분집합이고 순서 동일이므로 인덱스 이용가능

 

 

즉, 복합인덱스의 부분집합 중 선행 칼럼들의 순서가 같은 정렬은 인덱스를 사용할 수 있다.

 

아래는 인덱스 사용이 불가능한 경우입니다.

 

ORDER BY user_id, post_id, created_at; // 순서가 다름
ORDER BY user_id, created_at; // 선행 칼럼 누락
ORDER BY post_id, created_at; // 2번째 칼럼 누락
ORDER BY post_id, user_id, created_at, updated_at; // 인덱스에 포함되지 않은 칼럼 존재
ORDER BY post_id DESC, user_id, created_at; // 정렬 방식이 다름

다행히 모든 칼럼이 ORDER BY 에만 명시될 필요는 없습니다.

where 에 명시되어도 충분히 사용 가능하며, 아래는 그 예시입니다.

 

 

 

1. where 로 선행 칼럼이 고정된 경우

select * from posts where post_id = 10 order by user_id, created_at;

• post_id = 10으로 선행 칼럼이 고정되었기 때문에, 인덱스 입장에서는 사실상 (user_id, created_at) 으로 정렬된 구간을 스캔하는 것과 같으므로
• 따라서, 인덱스를 활용한 정렬이 가능합니다.

select * from posts where post_id = 10 and user_id = 882837 order by created_at;

• post_id와 user_id 둘 다 동등 조건.
• 남는 정렬 키는 created_at 뿐인데, 인덱스 상에서 post_id=10,user_id=882837 구간은 이미 created_at 기준으로 정렬되어 있습니다. 이 경우도 인덱스를 사용한 정렬이 가능합니다.

인덱스의 선행 칼럼들이 where 에서 동등조건으로 완전히 고정되어 있으면, order by에서 그 이후 칼럼들을 써도 인덱스 정렬을 그대로 활용할 수 있다.

 

 

 

2. where + order by 를 함께 사용할 때의 패턴

 

select * from posts where post_id=10 order by user_id, created_at;
// -> 인덱스 활용 가능

select * from posts where post_id=10 and user_id=773277 order by created_at;
// -> 이것도 가능

select * from posts where post_id = 10 order by post_id, user_id, created_at;
// -> OK post_id는 어차피 전부 10이라 의미가 없지만 인덱스 정렬 순서는 일치

하지만, 인덱스 정렬을 깨는 대표적인 예시로,

선행 칼럼에 범위 조건(>, <, between, like)을 사용하면 인덱스 정렬을 깨게 됩니다.

 

select * from posts where post_id > 10 order by user_id, created_at;

• post_id 는 범위 조건이기 때문에, 인덱스는 post_id 까지는 정렬/탐색에 사용하지만 그 이후에는 보통 filesort가 발생합니다.
  • 동등 비교시에는 한개 구간만 보면 됐지만, 범위 조건 시 여러 연속 구간을 봐야하고 각, 구간 안에서는 정렬이 되어 있지만 모든 구간을 통틀어 봤을 때는 전혀 정렬되어 있지 않습니다. 따라서, 인덱스 정렬이 안되는 것입니다.

 

 

3. where 절에서 조건의 나열순서는 상관이 없나?

 

where 절에 SQL 문법상 어떤 순서로 적어놨는지는 전혀 중요하지 않습니다.

DB 엔진은 where 절을 파싱해서 논리적인 조건으로만 바라보고 정렬 사용 가능성도 동등하게 평가합니다.

-- 1
WHERE post_id = 10
  AND user_id = 20
  AND created_at >= '2025-01-01'

-- 2
WHERE created_at >= '2025-01-01'
  AND user_id = 20
  AND post_id = 10

1의 쿼리와 2의 쿼리가 완전히 동등합니다.

 

 

---

filesort를 이용한 정렬 방식

인덱스를 사용할 수 없는 경우는 내부적으로 조회된 데이터에 정렬을 수행합니다.

그 방법이 바로 filesort 입니다.

filesort 가 필요한 상황이 발생하면 MySQL 내부에서는 테이블을 Sort Buffer 에 옮겨 정렬 작업을 거칩니다.

filesort 를 이용하는 방식은 두 가지로 나뉘는데, 이것이 Using temporary 와 Using filesort 입니다.

 

• Using filesort

Using filesort 는 쿼리에서 첫 번째로 조회하는 테이블(드라이빙 테이블)에 대해서만 정렬이 필요한 경우 입니다.

MySQL은 조인을 할 때 어떤 테이블을 먼저 읽을지(=드라이빙 테이블) 을 정합니다.

그 다음에 그 테이블의 각 row를 기준으로 나머지 테이블들을 붙여 나가는 구조입니다.

즉, posts와 comment 를 조인할 때 posts에 대해서만 정렬을 수행하면 되므로,

Using filesort는 쿼리에서 첫 번째로 읽는 테이블에 대해서만 정렬이 필요한 경우라고 표현합니다.

SELECT  p.*, c.*
FROM    posts p       -- 드라이빙 테이블
LEFT JOIN comments c  -- 드리븐 테이블
       ON c.post_id = p.id
WHERE   p.is_deleted = 0
ORDER BY p.created_at DESC;

 

 

이번엔 ORDER BY에 조인된 다른 테이블의 컬럼이 같이 있는 경우를 보겠습니다.

SELECT  p.*, c.*
FROM    posts p
LEFT JOIN comments c
       ON c.post_id = p.id
WHERE   p.is_deleted = 0
ORDER BY c.created_at DESC;

 

이번엔 정렬 기준이 드리븐 테이블인 comments가 됐습니다.

드라이빙 테이블(posts) 만 정렬한다고 해도 comments.created_at 기준 순서를 보장할 수가 없습니다.

 

⇒ 그래서 MySQL은 posts + comments 를 조인해서 한 덩어리의 결과를 만들고 그 결과를 다시 정렬해야 합니다.

이 과정에서 조인 결과를 임시 테이블에 쌓고(Using temporary) 그 임시 테이블을 다시 정렬하면서 Using filesort 가 발생합니다.

 

 

 

최대한 filesort 가 발생하는 상황을 없앨 수 있도록 쿼리를 튜닝하는 시도가 필요하며, 불가피하다면 해당 부분을 애플리케이션 서버 단에 맡기는 것 또한 충분히 합리적인 고려대상이 될 수 있습니다.