Order By (소트 버퍼)

MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당 받아서 사용하는데, 이 메모리 공간을 소트 버퍼(Sort Buffer)라고 한다. 최대 사용한 공간은 sort_buffer_size 시스템 변수로 설정할 수 있으며 이 값은 4KB ~ 8MB 사이의 값을 가질 때 가장 효율적이다. 더 큰 값을 가지더라도 큰 메모리 공간 할당 때문에 성능이 떨어질 수 있다.

SHOW VARIABLES LIKE 'sort_buffer_size'; -- 262144 (256KB)

• sort_key, rowid : 정렬 키와 레코드 식별자만 가져와서 정렬하는 방식 (투 패스)
• sort_key, additional_fields : 정렬 키와 추가 필드를 가져와서 정렬하는 방식 (싱글 패스)
• sort_key, packed_additional_fields : 정렬 키와 전체 레코드를 가져와서 정렬하는 방식 (싱글 패스)

싱글 패스 : 정렬 된 데이터를 그대로 반환
투 패스 : 정렬 된 데이터를 Primary Key로 다시 SELECT해서 반환

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

• 스트리밍 방식 : 필요한 레코드를 그 때 그 때 반환하는 방식, limit가 성능 향상에 도움이 됨
  • 인덱스를 사용한 정렬 방식
• 버퍼링 방식 : 그룹핑이 완료 된 후 정렬하는 방식, limit가 성능 향상에 도움이 되지 않음
  • 나머지는 모두 버퍼링 방식

Group By

• Having은 Group By 결과에 대한 Filtering을 하는 것을 말한다.
• Group By에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 Having 절을 사용하면 인덱스를 사용하지 않고 테이블을 Full Scan하게 된다.
• Group By 할 때 묶는 조건을 index가 사용된 컬럼으로 사용하면 인덱스를 활용해서 Group By를 할 수 있다.

루스 인덱스 스캔

• 인덱스의 레코드를 건너뛰면서 필요한 부분만 가져오는 것.
• 루스 인덱스 스캔을 사용할 때는 실행 계획의 Extra 컬럼에 Using index for group-by라고 표시된다.
• 루스 인덱스 스캔은 유니크한 값의 수가 적을수록 성능이 향상된다.
• 루스 인덱스 스캔을 사용할 수 있는 경우는 MIN, MAX 함수를 사용하거나 select절에 인덱스를 사용하는 경우이다.

col1, col2에 인덱스가 걸려있다고 가정할 때 아래와 같은 경우에 index를 사용할 수 없다.

-- // MIN()과 MAX() 이외의 집합 함수를 사용하는 경우
SELECT col1, COUNT(col2) FROM table1 GROUP BY col1;
-- // GROUP BY 절에 사용된 컬럼이 인덱스 구성 컬럼의 왼쪽부터 일치하지 않기 때문에 사용 불가
SELECT col1, col2 FROM table1 GROUP BY col2;
-- // SELECT 절의 컬럼이 GROUP BY와 일치하지 않기 때문에 사용 불가
SELECT col1, col3 FROM table1 GROUP BY col1;