study MySQL 스트리밍 정렬 처리 버퍼링 groub by 타이트 인덱스 스캔 tight index scan 루스 인덱스 스캔 loose index scan 프리픽스 인덱스 prefix index 옵티마이저와 힌트 옵티마이저 힌트

정렬 처리 방법의 성능 비교

스트리밍 방식과 버퍼링 방식

책너두 6기 29일차

백은빈, 이성욱의 Real MySQL8.0 1권 p.299 ~ p.308

내용정리

09 옵티마이저와 힌트

9.2.3.3.3 임시 테이블을 이용한 정렬

2개 이상의 테이블을 조인해서 그 결과를 정렬해야 한다면 임시 테이블이 필요할 수 있다.

이 방법은 3가지 정렬의 방법 중 가장 느리다.

9.2.3.3.4 정렬 처리 방법의 성능 비교

퀄에서 인덱스를 사용하지 못하는 정렬이나 그루핑 작업이 왜 느리게 작동할 수 밖에 없는지 한번 살펴보자. 쿼리가 처리되는 방법을 스트리밍 처리와 버퍼링 처리 2가지로 구분했다.

  • 스트리밍 방식
    • 서버 쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될때마다 바로바로 클라이언트로 전송해주는 방식
    • 클라이언트는 쿼리를 요청하고 곧바로 원했던 첫 번째 레코드를 전달받는다.
    • 빠른 응답 시간을 보장해 준다.
    • 결과 건수를 제한하면 전체 실행 시간을 줄일 수 있다.
  • 버퍼링 방식
    • where 조건에 일치하는 모든 레코드를 가져온 후, 정렬하거나 그루핑해서 차례대로 보낸다.
    • 작업동안 클라이언트는 기다려야 해서 응답 속도가 느려진다.
select *
from tb_test1 t1, tb_test2 t2
where t1.col=t2.col1
order by t1.col2
limit 10;

tb_test1 테이블의 레코드가 100건이고, tb_test2 테이블의 레코드가 1,000건이며 두 테이블의 조인 결과는 전체 1000건이라고 가정하고 정렬 처리 방법의 성능을 비교해보자

  • tb_test1이 드라이빙되는 경우
정렬 방법 읽어야 할 건수 조인 횟수 정렬해야 할 대상 건수
인덱스 사용 tb_test1: 1건, tb_test2: 10건 1 0건
조인의 드라이빙 테이블만 정렬 tb_test1: 100건, tb_test2: 10건 1 10건(tb_test1 테이블의 레코드 건수만큼 정렬 필요)
임시 테이블 사용 후 정렬 tb_test1: 100건, tb_test2: 1000건 100(tb_test1 테이블의 레코드 건수만큼 조인 발생) 1000건(조인된 결과 레코드 건수를 전부 정렬해야 함)
  • tb_test2가 드라이빙되는 경우
정렬 방법 읽어야 할 건수 조인 횟수 정렬해야 할 대상 건수
인덱스 사용 tb_test1: 10건, tb_test2: 10건 10 0건
조인의 드라이빙 테이블만 정렬 tb_test1: 1000건, tb_test2: 10건 10 1000건(`tb_test12 테이블의 레코드 건수만큼 정렬 필요)
임시 테이블 사용 후 정렬 tb_test1: 1000건, tb_test2: 100건 1000(tb_test2 테이블의 레코드 건수만큼 조인 발생) 1000건(조인된 결과 레코드 건수를 전부 정렬해야 함)

어느 테이블이 먼저 드라이빙되어 조인되는지도 중요하지만 어떤 정렬 방식으로 처리되는지는 더 큰 성능 차이를 만든다. 가능하다면 인덱스를 사용한 정렬로 유도하고, 그렇지 못하다면 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도하는 것이 좋다.

9.2.3.4 정렬 관련 상태 변수

MySQL 서버는 처리하는 주요 작업에 대해서는 해당 작업의 실행 횟수를 상태 변수로 저장한다.

9.2.4 group by 처리

order by와 같이 쿼리가 스트리밍된 처리를 할 수 없게 하는 처리 중 하나다. 이 작업도 인덱스를 사용하는 경우와 그렇지 않은 경우로 나눠볼 수 있다. 인덱스를 이용할 때는 차례대로 읽는 인덱스 스캔 방법과 건너뛰면서 읽는 루스 인덱스 스캔 방법으로 나뉜다. 인덱스를 사용하지 못하는 쿼리에서 group by 작업은 임시 테이블을 사용한다.

9.2.4.1 인덱스 스캔을 이용하는 group by(타이트 인덱스 스캔)

조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때 groub by 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리한다.

9.2.4.2 루스 인덱스 스캔을 이용하는 group by

인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것을 의미한다. 단일 테이블에 대해 수행되는 groub by 처리에만 사용할 수 있으며 프리픽스 인덱스(Prefix index, 칼럼값의 앞쪽 일부만으로 생성된 인덱스)는 루스 인덱스 스캔을 사용할 수 없다.

9.2.4.3 임시 테이블을 사용하는 group by

기준 칼럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할 떄는 이 방식으로 처리된다.

CATALOG
    MySQL