MySQL filesort

데이터베이스에서 대규모 데이터를 다룰 때, 정렬 작업은 필수적이다. MySQL에서 정렬 작업을 수행하는 주요 메커니즘 중 하나가 바로 filesort이다. 이는 테이블 데이터를 정렬해야 하지만, 해당 작업을 인덱스를 통해 처리하지 못할 경우 사용된다. 이름은 filesort이지만, 정렬이 반드시 디스크에서 수행되는 것은 아니며, 정렬 작업은 메모리에서 시작하고 필요한 경우 디스크로 이동된다.

 

filesort의 동작 원리

---

MySQL이 정렬 작업을 처리할 때, 다음 두 가지 방식 중 하나를 선택한다.

 

1. 인덱스 기반 정렬

• ORDER BY나 GROUP BY 절에서 사용된 컬럼이 인덱스에 포함된 경우, 정렬 작업은 인덱스를 활용해 효율적으로 처리된다.
• filesort가 사용되지 않는다.

 

2. filesort 기반 정렬

• ORDER BY나 GROUP BY 컬럼이 인덱스에 포함되지 않거나, 인덱스를 활용할 수 없는 경우, MySQL은 filesort를 사용하여 정렬 작업을 수행한다.
• 데이터 전체를 읽어 메모리 또는 디스크에서 정렬을 수행한다.

 

filesort가 사용되는 상황

---

1

 

 

4. WHERE 조건과 ORDER BY가 서로 다른 인덱스를 참조할 때

 

SELECT * FROM orders WHERE hash_email = 'test@example.com' ORDER BY product_name;

 

• WHERE 조건이 hash_email을 기준으로 데이터를 필터링하고, 정렬 기준이 다른 컬럼(product_name)일 때, MySQL은 인덱스를 사용할 수 없습니다.

 

1. ORDER BY 절에 인덱스가 없는 경우

EXPLAIN SELECT * FROM customer_orders ORDER BY product_name;

• 테이블에 product_name에 대한 인덱스가 없다면, 정렬을 위해 filesort를 사용한다.

 

2. 복합 인덱스가 정렬 순서와 일치하지 않는 경우

EXPLAIN SELECT * FROM customer_orders ORDER BY order_date, product_name;

• 복합 인덱스 idx_customer_product_date (customer_id, product_name, order_date)가 있다고 해도, ORDER BY 순서와 일치하지 않는 경우 filesort를 사용한다.

 

3. SELECT 로 모든 컬럼을 가져오는 경우

EXPLAIN SELECT * FROM orders ORDER BY customer_id, order_date;

• 인덱스에 포함되지 않은 컬럼을 함께 가져오면, MySQL은 전체 데이터를 읽고 정렬해야 하므로 filesort를 사용한다.
• SELECT * 는 모든 컬럼 데이터를 가져오므로, 커버링 인덱스를 사용할 수 없어 filesort가 발생한다.

 

4. WHERE 조건과 ORDER BY 가 서로 다른 인덱스를 참조할 때

EXPLAIN SELECT * 
FROM orders 
WHERE hash_email = 'test@naver.com'
ORDER BY customer_id, order_date;

• WHERE 조건이 hash_email을 기준으로 데이터를 필터링하고, 정렬 기준이 다른 인덱스인 경우 filesort를 사용한다.
• MySQL은 한 번에 하나의 인덱스만 사용 가능하다.
  • WHERE 절과 ORDER BY 절이 서로 다른 인덱스를 참조하면, MySQL은 정렬 작업에 사용할 인덱스를 선택할 수 없게 되어 filesort를 사용한다.

 

filesort의 정렬 방식

---

MySQL의 filesort는 다음 두 가지 방식으로 정렬 작업을 수행한다.

 

첫번째 방식

• 정렬 키만을 대상으로 정렬한 후, 필요한 데이터를 테이블에서 다시 조회한다.
• 데이터가 작을 때 사용된다.

 

두번째 방식

• 정렬 키와 SELECT에 필요한 모든 데이터를 함께 정렬한다.
• 데이터가 클 때 사용된다.
• 데이터가 많아 메모리를 초과하는 경우, 정렬 작업이 디스크로 이동된다.

 

filesort가 성능에 미치는 영향

---

 

filesort는 정렬 작업을 처리하기 위해 추가적인 비용이 발생하므로, 가능하면 피하는 것이 이상적이다.

 

메모리와 디스크 사용

• 정렬 대상 데이터가 sort_buffer_size(MySQL의 정렬 버퍼 크기)보다 크면, 정렬 작업이 디스크로 이동하여 성능이 저하된다.

 

(2) CPU와 I/O 부하

• filesort는 데이터를 읽고 정렬하는 동안 CPU와 I/O 작업이 추가로 발생한다.

 

filesort 최적화 방법

---

인덱스 사용

• ORDER BY 또는 GROUP BY 컬럼에 적합한 인덱스를 생성하여 정렬 작업을 인덱스로 처리한다.

 

SELECT 컬럼 최소화

• SELECT * 대신 필요한 컬럼만 선택하여, MySQL이 커버링 인덱스를 사용할 수 있도록 한다.

 

LIMIT 사용

• 정렬 대상 데이터를 줄이기 위해 LIMIT을 사용한다.

 

정렬 버퍼 크기 조정

• sort_buffer_size 값을 증가시켜, 정렬 작업이 메모리 내에서 완료되도록 설정한다.

 

결론

---

• filesort는 인덱스를 활용할 수 없는 경우 발생하는 정렬 알고리즘으로, 성능 저하를 유발할 수 있다.
• filesort를 피하려면 적절한 인덱스 설계와 SELECT 및 ORDER BY 최적화가 필요하다.