[MySQL] ORDER BY, GROUP BY, DISTINCT 절에서 옵티마이저는 어떻게 동작하는가

Loopy·2023년 8월 30일
db
0
post-thumbnail

ORDER BY 처리

MySQL에서 정렬을 처리하는 방법은 크게 두가지이다.

1. 인덱스를 통해 정렬하는 방법

INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬이 되어있으므로 별도의 작업이 필요 없이 순서대로 읽기만 하면 되서 성능이 매우 빠르다.

하지만 당연히 조회 말고 다른 변경 쿼리에 대해서는 느리고, 인덱스가 저장될 때 디스크 공간과 InnoDB 버퍼 풀을 위한 메모리가 많이 필요하다는 단점도 있다.

이렇게 인덱스를 타서 성능을 향상시킬 수 있는 경우는 한정적이다.

  1. 정렬 기준이 많아서 모두 인덱스를 생성하는 것이 불가능한 경우
  2. GROUP BY 나 DISTINCT 같은 처리의 결과를 정렬해야 하는 경우
  3. UNION 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
  4. 랜덤하게 결과 레코드를 가져와야 하는 경우

2. FileSort를 이용하는 방법

FileSort 는 인덱스를 이용하지 않고 MySQL 서버가 별도의 정렬 처리를 수행했다는 것을 나타낸다. 문제는 정렬을 수행하기 위해, 소트 버퍼(Sort buffer)라고 하는 별도의 메모리 공간을 할당받는다는 것이다.

물론 메모리에 할당된 소트 버퍼 공간으로만으로 정렬 처리를 수행할 수 있다면 문제가 발생하지 않는다. 하지만 정렬 처리를 해야할 데이터가 너무 많아서 할당된 소트 버퍼 공간을 초과한다면?

MySQL는 이런 경우 정렬 레코드를 여러 조각으로 나누고, 임시 저장을 위해 디스크 공간을 사용해버린다. 결국 디스크를 왔다갔다 하면서 정렬을 하기 때문에 수많은 디스크 I/O 가 발생하고, 마지막에 각 정렬된 레코드들을 다시 병합하면서 정렬이 완료되므로 성능이 매우 느려진다.

그렇다면 소트 버퍼의 크기를 매우 크게 늘려주면 되는거 아닌가요?

소트 버퍼도 메모리이고, 특히나 여러 클라이언트가 공유하는 영역이 아닌 세션 메모리 영역에 해당한다.

따라서 해당 영역 크기가 커지게 될수록 OS는 메모리 부족 현상을 겪게 될것이고, 이는 결국 OOM-Killer가 여유 메모리를 확보하기 위해 가장 많은 메모리를 사용하고 있는 대상 1순위인 MySQL 서버를 강제종료시키는 지경까지 이르를 수 있다.

☁️ 소트 버퍼 저장 기준 정렬 방식

위에서 레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지, 혹은 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 또 방식이 2가지로 나눠진다.

싱글 패스 정렬 방식

싱글 패스 정렬 방식은, 정렬 키와 SELECT 칼럼을 모두 가져와서 정렬한다. 이러한 경우 정렬에 필요하지 않은 칼럼들까지 불필요하게 소트 버퍼에 담기기 때문에 공간이 많이 필요하다.

따라서, 정렬 대상 레코드 크기나 건수가 작은 경우 빠른 성능을 보이게 된다.

SELECT emp_no, first_name, last_name
FROM employees
ORDER BY first_name;

참고로 최신 버전의 MySQL에서는 해당 방식을 기본적으로 사용하지만, 다음과 같은 경우는 싱글 패스 정렬 방식을 사용하지 못한다.

투 패스 정렬 방식

투 패스 정렬 방식은 정렬 대상 칼럼과 PK 값만 소트 버퍼에 담아 정렬을 수행한다. 우선 정렬을 하고, PK를 통해 나머지 필요한 칼럼들을 가져와서 클라이언트에게 넘기는 방식이다.

하지만 그만큼 DISK I/O가 2번이나 발생하기 때문에(테이블을 한번 더 읽어야 하므로) 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 싱글 패스 정렬 방식보다 효과적이다.

🫧 SELECT * 를 지양해야 하는 이유?
앞서 말했듯이 * 는 모든 칼럼을 가져오게 하므로, 정렬 버퍼에 불필요한 칼럼이 들어가 비효율적으로 동작한다. 따라서 꼭 필요한 칼럼만 조회하도록 하자.

☁️ 정렬 처리 방법

앞서 말했듯이, ORDER BY 는 무조건 아래 두 가지 방식 중 하나로 실행된다.

  1. 인덱스를 사용할 수 있다면 순서대로 읽어서 반환
    이 경우, ORDER BY 에 명시된 칼럼이 제일 먼저 읽는 테이블(조인에서 드라이빙 테이블) 에 속해야 하고 해당 칼럼의 인덱스가 있어야 한다. 또한, WHERE 절 칼럼과 ORDER BY 절 칼럼이 같아야 한다.
SELECT * 
	FROM employees e, salaries s
	WHERE s.emp_no = e.emp_no AND e.emp_no BETWEEN 10002 AND 100020
    ORDER BY e.emp_no;

🫧 Nested Loop 조인
두 테이블이 조인을 할 때, 드라이빙 테이블(Outer 테이블)에서 결합 조건에 일치하는 레코드를 내부 테이블(Inner Table)에서 조인하는 방식

  1. 인덱스를 사용할 수 없는 경우 조건에 해당하는 레코드를 정렬 버퍼에 저장하면서 정렬을 처리

이때, Filesort 를 이용하는 2번 과정에서 옵티마이저는 정렬 대상 레코드를 최소화하기 위해 다음 2가지 방법 중 하나를 선택한다.

1. 조인의 드라이빙 테이블만 정렬

조인이 수행되면 레코드 크기가 배로 불어나기 때문에, 조인 수행 전에 첫 번째 테이블의 레코드를 먼저 정렬한 이후 조인을 수행하는 방식이다. 단, ORDER BY 칼럼이 드라이빙 테이블의 칼럼만으로 구성되어야 한다.

SELECT * 
	FROM employees e, salaries s
	WHERE s.emp_no = e.emp_no AND e.emp_no BETWEEN 10002 AND 100020
    ORDER BY e.last_name;

ORDER BY 절에 인덱스가 걸려있지 않은 상황이여도, 옵티마이저는 드라이빙 테이블에 포함된 칼럼임을 보고 알아서 정렬을 먼저 수행할 수 있다.

  1. emp_no 인덱스를 이용해 BETWEEN 조건 만족 레코드 검색
  2. 해당 검색 결과를 소트 버퍼를 이용해 last_name 칼럼 기준으로 정렬 수행
  3. 정렬된 결과를 차례로 읽으면서 salaries 테이블과 조인 수행

2. 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬 수행

이 경우, 조인 수행한 결과를 소트 버퍼가 아닌 임시 테이블에 저장한다. 메모리에 위치하는 소트 버퍼와 달리 임시 테이블은 디스크 영역에 있으므로, 당연히 세가지중 가장 성능이 느리다.

  1. 조인을 수행한다.
  2. 조인 수행한 모든 결과를 임시 테이블에 저장하고, 정렬한다. 
SELECT * 
	FROM employees e, salaries s
	WHERE s.emp_no = e.emp_no AND e.emp_no BETWEEN 10002 AND 100020
    ORDER BY s.salary;   // salries 테이블 칼럼으로 정렬

위 쿼리에서 임시 테이블이 사용된 이유는, ORDER BY 칼럼이 드라이빙 테이블이 아닌 드리븐 테이블에 속해있기 때문이다. 정렬을 수행하기 전에 salaries 테이블의 데이터가 필요하므로, 조인이 먼저 일어나게 된다.

🫧 정렬에 따른 Extra 표기법
1. 인덱스를 사용한 정렬 : 별도 표기 X
2. 조인에서 드라이빙 테이블만 정렬 : Using filesort
3. 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 : Using temporary; Using filesort

☁️ 정렬 처리 방법 성능 비교: Limit의 효과

그렇다면 이제 왜 ORDER BY 와 GROUP BY 작업이 LIMIT 을 써도 성능이 좋지 않은지에 대해 살펴보자.

LIMIT 은 레코드 처리 결과의 일부만 가져오도록 해서, MYSQL 서버가 처리해야할 작업량을 줄이는 역할을 한다. 결론부터 말하면, ORDER BY 는 버퍼링 방식으로 동작하기 때문에 LIMIT 을 써도 느리다.

1. 스트리밍 처리 방식

서버 쪽에서 처리할 데이터가 어느정도 되는지에 관계없이, 조건에 일치하는 레코드가 검색될 때 마다 바로 클라이언트로 전송하므로 매우 빠른 응답 시간을 보장한다.

주로 인덱스를 사용해 정렬을 할 때 스트리밍 방식으로 처리된다. limit 가 없어도 스캔의 결과가 아무런 버퍼링 처리나 필터링 과정 없이 바로 클라이언트로 전송되기에 빠르다.

하지만 여기에 limit 까지 붙이면 전체적으로 가져오는 레코드 건수를 줄이기 때문에 훨씬 빨라진다. 클라이언트에 바로바로 반환을 하기에 개수를 충족하는 순간 바로 동작을 멈추기 때문이다.

2. 버퍼링 처링 방식

ORDER BY 나 GROUP BY 조건이 들어가있는 상황에서 인덱스를 사용할 수 없다면, 스트리밍 방식을 사용할 수 없다.

우선 WHERE 조건에 일치하는 레코드를 모두 가져온 이후 정렬하거나 그루핑하는 과정이 일어나야 하기 때문인데, 결국 클라이언트로 응답 속도가 매우 늦어진다.

따라서 해당 방식은 limit 로 결과 건수를 제한해도, 네트워크로 전송되는 레코드의 건수만 줄일 뿐 MySQL 작업의 성능 향상에는 효과가 크지 않다.

GROUP BY 처리

GROUP BY 역시 ORDER BY과 마찬가지로 스트리밍 처리를 할 수 없게 한다. 즉, 조건에 일치하는 레코드가 검색되어도 바로바로 클라이언트로 전송되지 않는다.

따라서 굳이 필터링 역할을 하는 HAVING 절을 튜닝하려고 인덱스를 생성하거나 할 필요는 없다.

인덱스를 사용하는 경우

group by 에서 인덱스가 사용되는 경우, 인덱스 스캔과 루스 인덱스 스캔이 실행되는 두 가지 방식이 있다.

인덱스 스캔 이용

조인의 드라이빙 테이블에 속하는 컬럼을 이용해 grouping 을 할 때 해당 칼럼에 인덱스가 있다면, 당연하게 정렬된 인덱스를 차례대로 읽으면서 그루핑 작업을 수행한다.

하지만, 예외로 MAX 와 같은 그룹 함수를 처리해야 하는 경우나, 인덱스를 사용하지 못하는 경우 그룹핑을 처리할 임시 테이블이 따로 필요하므로 실행 계획에서 Extra 칼럼에 아래와 같이 Using temporary 가 표시된다.

루스 인덱스 스캔 이용

만약 인덱스가 (emp_no, from_date) 처럼 멀티 인덱스로 생성되어 있는 상태에서 아래와 같이 where 절 조건이 첫 번째 인덱스에 포함되지 않는 SQL 쿼리문에 대해서는, 루스 인덱스 스캔이 실행된다.

explain select emp_no
  from salaries
  where from_date='1985-03-01'
  GROUP BY emp_no;

루스 인덱스 스캔에 대한 자세한 설명은 여기를 참조하고, 쿼리 실행 과정은 다음과 같다.

  1. (emp_no, from_date) 를 스캔하면서 emp_no 의 첫번째 유일한 값을 찾아낸다.
  2. 알아낸 emp_no 값과 from date 조건을 and 로 합쳐서 해당 레코드만 가져온다.
  3. 다시 인덱스를 스캔하면서 emp_no 의 그 다음 유니크한 값을 가져온다.(이후 반복)

하지만 주의할점은 인덱스 레인지 스캔과 달리 카디널리티(중복)가 높을수록 성능이 향상된다. 중복이 낮은 경우, MySQL 옵티마이저가 인덱스에서 스캔해야할 시작 지점을 검색하는 작업이 많이 필요해지기 때문이다.

🫧 인덱스 스킵 스캔
MySQL 8.0 이후 인덱스 스킵 스캔 최적화 방식이 등장하면서, 꼭 GROUP BY 절이 아니더라도 WHERE 조건절이라면 루스 인덱스 스캔 방식을 사용할 수 있어졌다. 인덱스 스킵 스캔은 루스 인덱스 스캔을 이용해서 동작하기 때문이다.

임시 테이블을 사용하는 경우

GROUP BY 기준 칼럼이 드라이빙/드리븐 테이블 어디에 있느냐에 상관 인덱스를 전혀 사용하지 못할때는, 임시 테이블 방식으로 실행된다.

임시 테이블이란?

GROUP BY 절 칼럼들로 구성된 유니크 인덱스를 가진 테이블을 의미한다. 해당 별도로 생성하고, 중복 제거와 집합 함수 연산을 수행한다.

🫧 임시 테이블이 저장되는 위치
ORDER BY나 GROUP BY 처럼 별도의 데이터 가공 작업이 수행되기 위해 만들어지는 임시 테이블은 메모리에 우선 생성되었다가 크기가 커지면 디스크로 이동한다.

  1. 메모리 에 저장될 때 : 가변 길이 타입을 지원하는 TempTable 스토리지 엔진
  2. 디스크 에 저장될 때 : 트랜잭션이 가능한 InnoDB 스토리지 엔진
EXPLAIN
	SELECT e.last_name, AVG(s.salary)
	FROM employees e, salaries s
	WHERE s.emp_no=e.emp_no
	GROUP BY e.last_name;

위와 같은 쿼리에서, 인덱스를 전혀 사용하지 못하므로 아래와 같은 임시 테이블이 생성된다.

CREATE TEMPORARY TABLE ... (
	last_name VARCHAR(16),
    salary INT,
    UNIQUE INDEX ux_lastname (last_name)  // 유니크 인덱스
);
  1. 임시 테이블이 생성된다.
  2. 조인 이후, 결과를 한건씩 가져와서 INSERT 한다. 이때, 유니크 인덱스이므로 중복 체크 과정이 필요한데 읽기 잠금이 사용되고 쓸 때는 다시 쓰기 잠금이 사용된다.
  3. 이미 last_name 칼럼이 있다면 해당 칼럼에 salary 값을 업데이트 한다.

이렇게 인덱스 자체에서 정렬이 되어있기 때문에, 별도로 ORDER BY e.last_name 조건을 명시하지 않는 이상 MySQL 8.0 부터는 Using Filesort 와 같이 별도의 정렬 연산을 수행하지 않는다.

MySQL 5.7 까지는 GROUP BY 조건에 있는 그루핑 칼럼을 기준으로 정렬이 사용되었다.

DISTICNT 처리

칼럼이 아닌 레코드의 중복을 제거하기 위해 사용되는 DISTICNT 는 집합 함수와 함께 사용되는 경우와 그렇지 않은 경우 영향을 미치는 범위가 다르다.

순수 DISTINCT

순수 SELECT 절에서 사용된 DISTINCT 는 GROUP BY와 동일한 방식으로 처리된다. 아래 쿼리와 같이 인덱스를 사용할 수 있다면 임시 테이블을 사용하지 않지만, 인덱스를 사용할 수 없는 경우에도 임시 테이블의 유니크 인덱스를 통해 중복이 제거되기 때문이다.

SELECT DISTINCT emp_no FROM salaries;
SELECT emp_no FROM salaries GROUP BY emp_no;

또한 이렇게 SELECT 절에 사용된 DISTINCT 키워드는 조회되는 모든 칼럼에 영향을 미친다. 예를 들어, 아래와 같은 경우 특정 first_name 이 유니크한 칼럼을 가져오는게 아니라 (first_name, last_name) 조합 자체가 유니크한 칼럼을 가져온다.

SELECT DISTINCT first_name, last_name FROM employees;

집합 함수와 함께 사용된 DISTINCT

반면, 집합 함수와 함께 사용된다면 모든 칼럼 조합이 유니크한 것이 아닌 주어진 특정 칼럼값이 유니크한 것들만 가져온다.

EXPLAIN SELECT COUNT(DISTINCT s.salary)  // 임시 테이블 생성
    FROM employees e, salaries s 
	WHERE e.emp_no=s.emp_no
    AND e.emp_no BETWEEN 100001 AND 100100;
  1. 인덱스를 사용할 수 있다면 일반 GROUP BY 처럼 동작한다.
  2. 인덱스를 사용할 수 없다면 유니크 인덱스를 가진 임시 테이블을 생성한다.

이 역시 COUNT(DISTINCT emp_no) 처럼 인덱스를 사용할 수 있다면 임시 테이블 없이 최적화된 처리를 수행할 수 있지만, 예제에서 salary 에는 인덱스가 걸려 있지 않은 상황이다.

따라서 salary 값만 저장하기 위한 임시 테이블이 생성되는데, 이때 유니크 인덱스가 걸리므로 특정 칼럼만이 아닌 다른 칼럼까지 고려된다면 레코드 건수가 많아질수록 디스크 I/O가 많아져 느려질 수 있기 때문이다.

반면 예외적으로 DISTINCT 를 사용하는 쿼리에서는 임시 테이블을 사용한다는 메시지인 Using temporary 가 표시되지는 않으니 성능에 주의하자.

그렇다면, 이제 최종적으로 언제 임시 테이블이 생성되는지 정리해보자.

☁️ 임시 테이블이 필요한 쿼리

해당 조건을 가진 경우는 유니크 인덱스를 가지는 내부 임시 테이블이 생성된다.

  1. ORDER BY 와 GROUP BY 에 명시된 칼럼이 다른 경우
  2. ORDER BY 와 GROUP BY 에 명시된 칼럼이 조인의 순서상 첫 번재 테이블이 아닌 경우
  3. DISTINCT 와 ORDER BY 가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
  4. UNION이나 UNION DISTINCT 가 사용되는 쿼리

그리고 만약 GROUP BY 나 DISTINCT 칼럼 크기가 512 byte 이상이라면, 임시 테이블이 디스크에 생성된다.

profile
Loopy
개인용으로 공부하는 공간입니다. 잘못된 부분은 피드백 부탁드립니다!
이전 포스트

[Spring Boot] MySQL 검색 쿼리 성능 개선기

다음 포스트

[MySQL] 옵티마이저 최적화

0개의 댓글