[Mysql] Mysql 5.7 정렬 최적화(Optimization)

안녕하세요. 오늘은 Mysql 5.7 버전에서 정렬을 최적화 하는 방법에 대해서 알아보도록 하겠습니다.

RDMBS 정렬

RDBMS를 사용하다보면 정렬된 정보를 이용해야할 경우가 많이 생깁니다. 이럴경우 우리는 조회하는 쿼리에 ORDER BY를 이용해서 RDBMS에서 정렬 시킨 후 정렬된 데이터를 가져와서 어플리케이션에서 이를 이용하곤 합니다.

하지만 이런 정렬도 잘 이용하지 않으면 RDBMS로부터 빠른 응답을 기대할 수가 없는데요. 그 이유는 간단히 설명드리면 데이터는 디스크에 저장되고 이 걸 select 쿼리에 의해서 가져옵니다. 그리고 Mysql 엔진에서 내부에서 임시테이블을 만들어 filesort를 진행하게 되는데 됩니다. 그런데 데이터가 커지면 디스크에서 한번에 가져올 수 없으며 여러번 나눠서 가져오게되는데 디스크의 찾기(seek)는 상대적으로 시간이 오래걸리는 작업인데 이게 많아지게 되기때문에 오래걸리게 되는 것입니다.

따라서 정렬을 사용할 때 이미 정렬되어있는 index를 이용할 수 있다면 이용하는게 빠르게, 그리고 적은 리소스 사용으로 정렬된 데이터를 가져오는 방법입니다.

정렬 사용시 나올 수 있는 실행계획 (explain)

쿼리를 작성하고 해당 쿼리의 성능을 판단할 때 실행계획을 통해서 얼마나 최적화 되어있는지 판단할 수 있습니다.  실행계획에서 주로 보아야할 부분은 type과 key, 그리고 extra 입니다. type은 해당 인덱스 사용 타입, key는 인덱스를 사용했을 때 어떤 인덱스를 사용했는가, 그리고 extra는 부가정보를 나타냅니다. 아래의 예제들로 한번 확인해보도록 합시다.

먼저 위 이미지는 인덱스를 사용하지 않았을때 나오는 실행계획입니다. 주로 봐야할 내용을 정리해보면 아래와 같습니다.

• type : ALL
  • 테이블 full search를 진행했다는 의미
• key : null
  • index로 사용된게 없다는 의미
• Extra : Using filesort
  • 임시테이블을 이용하여 Mysql 서버에서 정렬을 진행했다는 의미

500 rows retrieved starting from 1 in 17 s 126 ms (execution: 1 s 404 ms, fetching: 15 s 722 ms)

그리고 위 경우 정렬에 대한 시간은 17초 정도가 소모된 것을 확인할 수 있습니다.

그리고 위 이미지는 인덱스만을 이용하여 정렬하였고 그 정보를 반환하였을 때의 실행계획입니다.

• type : index
  • 인덱스 full search를 진행했다는 의미
• key
  • 인덱스로 사용한 기 정의된 이름
• Extra : using index
  • 테이블에 접근하지 않고 인덱스만으로 결과를 만들어냈다는 의미 (커버링 인덱스)

이 경우에 실행 시간은 아래와 같이 1초 가량이 걸렸다는 것을 확인할 수 있었습니다.

500 rows retrieved starting from 1 in 1 s 560 ms (execution: 1 s 532 ms, fetching: 28 ms)

실행계획을 으로 파악했을 때 아래의 예제에서는테이블에 전혀 접근하지 않고 결과를 만들어내었습니다. 그렇기 때문에 테이블에 접근했을 때 경우 보다 훨씬 빠르게 결과를 얻어올 수 있습니다.

자세한 상황과 설명

쿼리에 따라 인덱스를 사용하는 정렬과 사용하지 않는 쿼리가 있습니다. 이를 잘 구분해서 사용하면 좋은데요. mysql docs에 나오는 문서를 정리해보도록 하겠습니다.

Index를 사용하는 정렬과 사용하지 않는 정렬

t1 이라는 테이블이 있고 아래와 같이 key_part1, key_part2를 인덱스로 가지고 있습니다.

create index IDX_table_key_parts on t1 (key_part1, key_part2);

쿼리를 어떻게 만들어내냐에 따라서 위의 인덱스를 사용하더라도 인덱스만을 사용할 수도 있고 테이블을 통하여 정렬을 할 수도 있습니다. 그런 부분을 잘 파악하여 쿼리를 적절하게 짜는것이 중요합니다.

아래 쿼리는 눈으로 보기에는 인덱스를 사용할 것 처럼 보입니다. 하지만 모든 컬럼을 가져오는 부분에 있어서 인덱스에 포함되어 있지 않은 컬럼을 가져오기 위해서 결국 디스크에 접근을 하여 가져와야합니다. 이런부분을 보았을 때 옵티마이저는 인덱스를 접근하는 것과 그냥 디스크에서 가져오는 것을 비교하여 인덱스를 먼저 이용하고 데이터를 이어서 가져올지, 아니면 처음부터 디스크에 접근해서 모든 데이터를 가져올지를 정합니다. 따라서 인덱스를 사용할수도 있고 안할 수도 있습니다.

SELECT * FROM t1
      ORDER BY key_part1, key_part2;

아래의 쿼리는 인덱스만을 이용하여 정렬합니다. 그리고 select 하는 컬럼도 인덱스를 모두 가지고 있는 컬럼과 primary key입니다. 이런 경우는 굳이 디스크에 접근하여 데이터를 가져올 필요가 없습니다. 왜냐하면 pk는 clustered index 이기 때문에 디스크까지 가지 않아도 되기 때문입니다. 이런 인덱스들은 이미 정렬 되어있기 때문에 별도의 정렬 프로세스도 타지 않습니다.

SELECT pk, key_part1, key_part2 FROM t1
  ORDER BY key_part1, key_part2;

정렬에서 Index를 사용할 수 없을 경우

인덱스가 설정되어있다고 모두 인덱스를 사용하진 않습니다. 아래의 케이스들은 공식 docs에서 이야기하는 정렬에 인덱스를 사용하지 않는 케이스들입니다. 알아두고 튜닝에 사용하면 좋을것 같습니다.

• 인덱스가 아닌 컬럼으로 정렬하는 쿼리

SELECT * FROM t1 ORDER BY key1, key2;

 

• 복합 인덱스의 경우 키를 순서대로 대로 정렬에 사용하지 않는 쿼리

SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3;

 

• DESC와 ASC를 섞어서 사용하는 쿼리

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

 

• row를 가져오는데 사용한 인덱스(where 절)가 정렬에 사용한 인덱스와 다른 쿼리

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

 

• ABS 등 추가로 사용하는 문법이 있는 쿼리

SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;

 

• 많은 테이블을 조인하여 컬럼의 숫자가 너무 많아진 쿼리

The query has different ORDER BY and GROUP BY expressions.

filesort를 사용하는 order 성능 향상시키기

인덱스를 사용하면 최상이지만 상황에 따라서 그렇지 못한 경우가 있습니다. 그럴경우에는 mysql 엔진에서 임시테이블을 이용하여 정렬을 하게 됩니다. 임시테이블은 sort buffer pool 이라는 곳에 저장이됩니다. 그렇기 때문에 이쪽 관련 설정을 적절히 튜닝하면 좋은 성능으로 정렬하여 결과를 가져올 수 있습니다.

• sort_buffer_size
  • sort buffer에서 정렬할 때 sort buffer의 사이즈 보다 크면 전체 정렬 필요 부분중 일부를 정렬한 후 임시테이블에 저장하고를 나머지를 가져와서 다시 정렬하는 식을 반복합니다. 따라서 sot buffer의 사이즈가 크다면 빠른 정렬을 기대할 수 있습니다.
• max_sort_length
  • text와 blob같은 long string을 가진 데이터를 기준으로 정렬할 때 정렬에 사용할 데이터의 크기를 나타냅니다. 기본값은 1024 바이트이며 해당 값이 넘어가는 부분은 정렬에서는 사용하지 않습니다.
• Sort_merge_passes
  • show variables where variable_name like ‘sort%’ 명령어를 통하여 Sort_merge_passes 값을 확인할 수 있습니다. 해당값이 높다면 임시테이블이 크게 만들어진다는 의미이기때문에 튜닝이 필요할 수 있습니다.

마무리

오늘은 이렇게 정렬에 대한 실행계획부터 인덱스를 사용하지 않는 쿼리, 그리고 sort buffer pool을 튜닝하는 방법까지 알아보는 시간을 가져보았습니다.

이번 포스팅에는 이전에 다루지 않은 생소한 개념인 클러스티드 인덱스(clusted index), mysql engine 등의 개념이 포함되어 있습니다. 이런부분에서 해당 포스팅에서는 정보가 미흡하다고 생각되어집니다. 이후에 요런 생소한 개념들은 다시한번 정리하도록 하겠습니다. 🙇

감사합니다.

참조

Real MariaDB

mysql_order-by-optimization