MYSQL 정렬 방식(indexsort와 filesort)

개요

• CURSOR 방식의 조회를 공부하던 중, 정렬 방식에 대한 공부가 필요한 것 같아 정리하고자 합니다.

실행 계획 분석

• 먼저 아래의 쿼리를 보겠습니다.

select 
	p1_0.product_uid,
	p1_0.name,
	p1_0.price,
	p1_0.thumbnail_image_url 
from products p1_0
where product_uid > 'KRvlj5cFxz08PiR'
order by p1_0.created_at desc 
limit  10;

 

• 위 쿼리는 간단한 쇼핑몰의 상품조회 SELECT문으로, 커서 페이지네이션 방식으로 작성되어 있습니다. 정렬 방식은 상품이 등록된 최신순으로 조회를 합니다.
• 해당 쿼리에 대한 실행계획을 조회하면 다음과 같은 결과가 나왔습니다.
  

  id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
  1	SIMPLE	p1_0	NULL	ALL	UK_mx7edqmlfuwyuxcswleoomxjt	NULL	NULL	NULL	2436840	50.00	Using where; Using filesort

• 해당 쿼리를 분석하자면 UK index(product_uid)를 사용가능 하지만 모종의 이유로 사용하지 않았고, Extra 절을 확인하면 filesort라는 것을 사용해 정렬한 것을 확인할 수 있습니다.

MYSQL의 물리적인 정렬 방법

• MYSQL의 다양한 정렬 전략을 이해하기 위해선, 먼저 물리적으로 MYSQL이 어떤식으로 정렬을 처리하는지에 대한 이해가 필요합니다.
• MYSQL에는 크게 두가지의 정렬 방식, 스트리밍 정렬 방식과 버퍼링 정렬 방식이 있습니다.
  • 스트리밍 정렬 방식
    • 스트리밍 정렬 방식은 클라이언트(사용자)가 MYSQL로 SELECT 쿼리를 날렸을 때, MYSQL 서버(엔진)은 데이터가 이미 정렬되어 있다고 판단하는 경우 조회된 행을 바로바로 클라이언트에게 전달하는 방식입니다. LIMIT 절로 조회하는 데이터의 갯수를 제한하는 경우에 LIMIT 절이 효과적으로 동작할 수 있습니다.
  • 버퍼링 정렬 방식
    • 버퍼링 정렬 방식은 클라이언트가 MYSQL로 SELECT 쿼리를 날렸을 때, WHERE 조건에 일치하는 모든 레코드를 가져온 후, 정렬하여 이를 반환하는 방식입니다. LIMIT 절로 조회하는 데이터의 갯수를 제한하더라도 일단 모든 데이터를 읽어 들인 후 정렬을 수행해야 하기 때문에 LIMIT 절로 인한 성능 향상을 기대하기는 어렵습니다.

• MYSQL에서 정렬 전략에는 크게 3가지가 존재합니다.
  • Using index
    • 인덱스를 사용하여 데이터를 검색하는 경우에, 정렬 기준이 옵티마이저가 사용한 인덱스로 이미 정렬된 상태라면, 추가적인 정렬이 필요 없기 때문에 스트리밍 방식을 사용해 데이터를 반환하는 것이 가능합니다.
    • 인덱스 정렬에는 아래와 같은 조건을 만족해야 한다고 합니다.
      • ORDER BY 에 정의 되어 있는 칼럼이 드라이빙 테이블에 속해 있어야 합니다.
      • 옵티마이저가 선택한 인덱스와 ORDER BY 는 같은 인덱스를 사용해야 하며, 이는 ORDER BY 순서대로 생성된 인덱스여야 합니다.
      • 인덱스는 B_TREE 계열의 인덱스여야 합니다.
  • Using filesort:
    • MySQL에서 쿼리 결과를 정렬하기 위해 추가적인 정렬 작업을 수행하는 경우입니다. 이 때 MYSQL Engine은 모든 데이터를 읽어와 소트 버퍼라는 별도의 메모리 공간(소트 버퍼이상의 메모리 공간을 필요로 하는 경우는 디스크를 함께 사용)에서 정렬을 진행한 후, 결과 데이터를 반환합니다.
    • JOIN, 서브 쿼리 등이 포함된 SQL문의 경우 ORDER_BY절이 드라이빙 테이블에 존재해 드라이빙 테이블을 먼저 정렬하고 난 후 나머지 쿼리를 수행하는 방식입니다.
  • Using Temporary:
    • MySQL이 임시 테이블을 사용하여 정렬 작업을 수행함을 의미하며, JOIN, 서브 쿼리 등이 포함된 쿼리문에서 발생하며, JOIN을 수행한 후에 정렬을 진행하기 때문에 정렬을 수행해야할 데이터의 수가 가장 많고, 테이블을 임시로 생성해 정렬을 수행하는 방식입니다.
• 위 3가지 방식 중 Using index만이 스트리밍 방식으로 동작하고, 나머지 2개의 방식은 버퍼링 방식으로 동작합니다. 따라서 2개의 방식은 성능 최적화 대상이 됩니다.
• 특히나 커서 페이지네이션의 장점은 행의 갯수를 필요한 만큼만 가져온다는 장점이 있어 사용한 것이였는데, 버퍼링 방식의 정렬을 수행할 경우 커서 페이지네이션의 장점이 사라지기 때문에 필수적으로 스트리밍 방식의 정렬로 수정해야 합니다.

문제 해결

• 문제를 해결하는 가장 간단한 방법은 ORDER_BY 조건에 해당하는 컬럼에 대한 인덱스를 생성하는 것입니다.
• 아래와 같이 정렬 조건인 created_at에 대해 인덱스를 생성해 주었습니다.

 

create index products_created_at on jpashop.products (created_at);

• 그리고 Cursor 방식의 페이지네이션일 경우, where절을 ORDER 절과 동일한 컬럼으로 수정해줘야 합니다.

select 
	p1_0.product_uid,
	p1_0.name,
	p1_0.price,
	p1_0.thumbnail_image_url 
from products p1_0
where created_at > ?
order by p1_0.created_at desc 
limit  10;

 

 

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	p1_0		range	products_created_at	products_created_at	9	NULL	1218420	100.00	Using index condition; Backward index scan

• 위 실행계획을 보면, created_at 인덱스를 사용해 20개의 데이터를 가져온 후, 이에 대해 where 조건절에 해당하는 추가적인 필터링을 통해 데이터를 필터링 후 리턴하는 방식으로 동작할 것을 예상하고 있는 것으로 보입니다. 따라서 추가적인 정렬 없이 인덱스만으로 정렬을 수행했습니다.

 

 

문제점

• 근데 위 SQL문도 하나의 조건이 필요합니다. 바로 정렬 조건이 Unique 값이여야 한다는 점입니다!
• 정렬 조건이 Unique하지 않으면 동일한 값들끼리의 순서가 정의되지 않기 때문에 제대로 페이지네이션을 했다고 말할 수 없습니다.
• 따라서 사용자 정렬 조건 + uid 오름차순을 추가 정렬로 주어 값이 동일하더라도 순서를 정의하도록 했습니다. 이에 대한 쿼리는 다음과 같습니다.

select 
	p1_0.product_uid,
	p1_0.name,
	p1_0.price,
	p1_0.thumbnail_image_url
from products p1_0
where created_at < (SELECT p.created_at from products p where p.product_uid = '5HpYfiFsBtS7W44')
      and product_uid < '5HpYfiFsBtS7W44'
order by created_at desc, product_uid desc
limit  10;

 

• 이에 대해 Explain을 실행해보았습니다.

id	select_type	tables	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	p1_0		range	UK_mx7edqmlfuwyuxcswleoomxjt,products_created_at	products_created_at	9		1	50.00	Using index condition; Using where; Backward index scan
2	SUBQUERY	p		const	UK_mx7edqmlfuwyuxcswleoomxjt	UK_mx7edqmlfuwyuxcswleoomxjt	1022	const	1	100.00

 

시간 비교

• 먼저 인덱스를 적용하지 않고 filesort를 적용한 상태에서 커서 페이지네이션의 시간을 측정해보았습니다.

page	SQL Time
1	18,620ms
2	19,170ms
3	20,494ms
4	19,499ms
5	19,381ms
6	19,772ms
7	19,685ms
8	19,127ms
9	18,853ms
10	18,597ms
합계	193,198ms

 

• 그 후 인덱스를 적용하여 시간을 측정하였습니다.

page	SQL Time
1	105ms
2	24ms
3	5ms
4	6ms
5	4ms
6	5ms
7	4ms
8	5ms
9	4ms
10	5ms
합계	167ms

 

참고자료

Real MySQL [6-7] 실행계획 - ORDER BY 정렬 방식 성능 비교, 정렬 관련 상태 변수

MySQL 옵티마이저란? MySQL 기본 데이터 처리 [ORDER BY - 소트 버퍼, 정렬 방식]

[MySQL] 옵티마이저 및 정렬 처리 방식

[MySQL] 정렬 처리 방식