본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
정렬 방식의 성능 비교
웹 서비스용 쿼리에는 LIMIT가 필수적으로 사용되곤 한다. LIMIT는 테이블이나 처리 결과의 일부만 가져오기 때문에 MySQL 서버가 처리해야 할 작업량을 줄이는 역할을 한다. ORDER BY나 GROUP BY 같은 작업은 WHERE 조건을 만족하는 레코드를 LIMIT 건수만큼만 가져와서 처리될 수 없다. WHERE 조건이 인덱스를 잘 활용하도록 튜닝해도 잘못된 ORDER BY나 GROUP BY 때문에 쿼리가 느려지는 경우가 자주 발생한다.
쿼리에서 인덱스를 사용하지 못하는 정렬이나 그룹핑 작업이 왜 느리게 작동할 수밖에 없는지 한번 살펴보자. 이를 위해 쿼리가 처리되는 방법을 "스트리밍 처리"와 "버퍼링 처리" 2가지 방식으로 구분해보자.
- 스트리밍 방식
그림과 같이 서버 쪽에서 처리해야 할 데이터가 얼마나 될지에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식을 의미한다. 이 방식으로 쿼리를 처리할 경우 쿼리를 요청하고 곧바로 원했던 첫 번째 레코드를 전달받는다 가장 마지막 레코드는 언제 받을지 알 수 없다. 클라이언트는 서버로부터 조건에 일치하는 레코드를 찾는 즉시 전달받기 때문에 동시에 데이터 가공이 가능하다. 스트리밍 방식은 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답시간을 보장해 준다.
스트리밍 방식으로 처리되는 쿼리에서 LIMIT 같이 결과 건수를 제한하는 조건은 쿼리 전체 실행 시간을 줄여줄 수 있다. 다시 말해 LIMIT 조건을 추가하면 전체적으로 가져오는 레코드 건수가 줄어들기 때문에 마지막 레코드를 가져오기까지 시간을 상당히 줄일 수 있다.
- 버퍼링 방식
ORDER BY나 GROUP BY와 같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능하게 한다. 우선 WHERE 조건에 일치하는 모든 레코드를 가져온 후, 정렬하거나 그룹핑을해서 차례대로 보내야 하기 때문이다. MySQL 서버에서는 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 응답 속도가 느려지는 것이다. 이 방식을 스트리밍의 반대 표현으로 버퍼링(Buffering)이라고 표현해 본 것이다.
위 그림과 같이 버퍼링 방식으로 처리되는 쿼리는 결과를 모아서 MySQL 서버에서 일괄 가공해야 하므로 모든 결과를 스토리지 엔진으로부터 가져올 때까지 기다려야 한다. 쿼리에 LIMIT 조건이 있어도 성능 향상에 도움이 되지 않는다.
인덱스를 사용한 정렬 방식만 스트리밍 형태의 처리이며 나머지는 버퍼링된 후에 정렬된다. 인덱스를 사용한 정렬 방식은 LIMIT로 제한된 건수만큼 읽으면서 바로바로 클라이언트로 결과를 전송해줄 수 있다.
조인과 함께 ORDER BY와 LIMIT 가 함께 사용될 경우, 처리 방식별로 정렬이 어떤 차이가 있는지 살펴보자.
SELECT *
FROM tb_test1 t1, tb_test2 t2
WHERE t1.col1=t2.col1
ORDER BY t1.col2
LIMIT 10;
tb_test1 테이블의 레코드가 100건이고, tb_test2 테이블의 레코드가 1,000건이며, 두 테이블의 조인 결과는 전체 1,000건 이라고 가정하고 정렬의 처리 방식별로 읽어야 하는 레코드 건수와 정렬을 수행해야하는 레코드 건수를 비교하자
- tb_test1이 드라이빙이 되는 경우
- tb_test2가 드라이빙이 되는 경우
어느 테이블이 드라이빙되어 조인되는지도 중요하지만, 어떤 정렬 방식으로 처리되는지는 더 큰 성능 차이를 만든다. 가능한 인덱스를 사용한 정렬로 유도하고 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도하는 것이 좋은 튜닝 방법이다.
정렬 관련 상태 변수
MySQL 서버는 주요한 처리 작업에 실행 횟수를 상태 변수로 저장하고 있다. 정렬과 관련해서도 몇건의 레코드가 정렬 처리 되었는지, 소트 버퍼 간의 병합 작업은 몇 번이나 발생했는지 등을 확인할 수 있다.
mysql> SHOW SESSION STATUS LIKE 'Sort%';
- Sort_merge_passes 멀티 머지 처리 횟수를 의미한다.
- Sort_range 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수다.
- Sort_scan 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수다. Sort_scan, Sort_range는 둘 다 정렬 작업 횟수를 누적하고 있는 상태 값이다.
- Sort_rows 지금까지 정렬한 전체 레코드 건수이다.
