mysql, 옵티마이저(optimizer)의 실행 계획 최적화 방식

옵티마이저란?

• 옵티마이저란 MySQL에서 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조하며 최적의 실행 계획을 수립하기 위한 기능.
• 현재 글은 특히 SELECT에서 옵티마이저가 어떤 방식으로 최적화를 하는지 알아보겠다.

쿼리 실행 절차

• 쿼리는 아래와 같은 방식으로 분석하고 처리된다.
  1. SQL 파싱한다. SQL 파서로 SQL 문장을 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(SQL 파스 트리)
  2. 파스 트리로 테이블의 읽는 순서와 사용할 인덱스를 결정
  • 불필요한 조건 제거 및 복잡한 연산의 단순화
  • 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
  • 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
  • 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정 3. 결정한 내용에 따라 스토리지 엔진으로부터 데이터를 추출
• MySQL 엔진은 1, 2를 처리하고 스토리지 엔진은 3번을 처리한다. 특히 2번은 옵티마이저가 처리한다.

옵티마이저의 종류

• 비용 기반 최적화(Cost-based optimizer)
  • 대상 테이블의 정보를 기반으로 실행 계획별 비용을 산출하여 최적의 방법을 선택.
  • MySQL을 포함한 현재의 대부분의 RDBMS가 채택하는 방법.
• 규칙 기반 최적화(Rule-based optimizer)
  • 옵티마이저 내부의 우선 순위가 존재하며, 그 우선 순위에 따라 동작.
  • 비용 계산이 부담스러웠던 예전에 사용하던 방법.

풀 테이블 스캔과 풀 인덱스 스캔

• 옵티마이저가 풀 테이블 스캔을 선택할 때
  • 테이블의 레코드 건수가 너무 작아 인덱스를 사용하는 것보다 빠른 경우 (일반적으로 테이블이 페이지 1개로 구성된 경우)
  • WHERE 절이나 ON 절에서 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
  • 인덱스의 레인지 스캔를 사용할 수 있더라도 스캔하거나 가져와야 할 레코드 건수가 너무 많은 경우
• 풀 테이블 스캔과 리드 어헤드(read ahead)
  • 풀 스캔을 할 경우 백그라운드 스레드를 사용하여 효과적으로 처리하여 이를 리드 어헤드라 한다.
  • 처음에는 포그라운드 스레드로 풀 스캔을 수행한다. 특정 읽기 시점부터 백그라운드 스레드와 함께 풀스캔을 수행한다. 백그라운드 스레드는 포그라운드 스레드가 사용할 레코드를 버퍼 풀에 미리 가져다 두며 풀스캔에 대한 성능 최적화를 달성한다.
  • innodb_read_ahead_threshold를 통해 백그라운드 스레드를 사용하기 위한 임계점을 설정한다. 일반적으로 디폴트 값을 수정할 필요는 없으나 데이터 웨어하우스용으로 서버를 사용할 경우 값을 낮은 값으로 설정하여 더 빨리 리드 어헤드의 시작을 유도할 수 있다.
• 풀 인덱스 스캔과 리드 어헤드
  • select count(1) from table의 경우 단순한 갯수만을 필요하므로 인덱스 풀 스캔을 할 사용할 가능성이 높다.
  • 마찬가지로 리더 어헤드를 사용한다.

병렬처리

• 하나의 쿼리를 여러 스레드가 나누어 동시에 처리하는 기능으로 MySQL 8.0 이후 사용 가능.
• innodb_parallel_read_threads로 설정.
• 현재는 select * from table 과 같이 어떤 조건이 없는 경우에서만 사용 가능하다.

ORDER BY 처리

인덱스와 Filesort

• 인덱스의 사용
  • 이미 정렬된 인덱스를 사용하여 매우 빠름.
  • INSERT, UPDATE, DELETE을 처리하는 과정에서 인덱스 추가/삭제 작업이 발생. 이로 인한 성능 저하.
  • 인덱스를 위한 별도의 디스크/버퍼풀 공간 필요.
• Filesort 이용
  • 인덱스를 사용하지 않는 경우 Filesort를 사용.
  • 인덱스의 사용 비용이 존재하지 않음.
  • 정렬할 레코드가 많지 않으면 충분히 빠르지만, 정렬할 레코드가 많을 수록 느려짐
  • 실행 계획에서 Extra 칼럼에 “Using filesort” 여부를 통해 사용 여부 확인 가능.
• 다만, 다음과 같은 상황에서는 인덱스를 사용할 수 없다.
  • 정렬 기준이 너무 많음
  • GROUP BY나 DISTINCT의 결과를 정렬
  • UNION이나 임시 테이블을 정렬
  • 랜덤하게 레코드를 가져와야 할 때

소트 버퍼(sort buffer)

• 소트 버퍼란 정렬을 수행하기 위한 별도의 메모리 공간.
• 소트 버퍼의 크기는 각 정렬 상황마다 가변적. 다만 최대치를 결정할 수 있으며 sort_buffer_size를 사용. 해당 버퍼는 쿼리 실행 완료 후 바로 반납.
• 만약 정렬에 필요한 메모리가 최대치보다 큰 경우 디스크를 사용. 소트 버퍼에서는 정렬을 수행하고 그 결과를 디스크에 반영하며 정렬이 필요한 데이터를 다시 소트 버퍼에 가져오는 것을 반복. 이런 병합 작업을 멀티 머지라 하며 그 횟수는 Sort_merge_passes라는 상태 변수에 집계됨.
• 다만, 버퍼의 크기에 따른 정렬 성능은 완전하게 비례하지 않으며 책에서는 56KB - 1MB 사이를 추천. 소트 버퍼는 커질수록 성능이 좋아질 가능성이 높겠지만, 세션마다 할당되어 그 총량이 커넥션의 갯수에 따라 엄청나게 클 수 있음. OOM이 발생할 수 있음.
• 필요한 경우 특정 세션에 대해서만 소트 버퍼를 일시적으로 늘려야 함.

정렬 알고리즘

• 싱글 패스: 소트 버퍼에 필요로 한 모든 레코드를 한 번에 가지고 와서 처리한다. 레코드의 갯수가 적거나 칼럼이 적을 수록 유리함.
• 투 패스: 소트 버퍼에 PK와 정렬 및 검색에 필요한 칼럼만 가져온다. 쿼리 결과를 반환할 때 다시 한 번 DB에 호출하며 나머지 칼럼을 채운다.
• 최신 버전의 MySQL은 싱글 패스 정렬을 우선한다. 다만, 아래 상황에서는 투 패스를 사용한다.
  • 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
  • BLOB이나 TEXT 타입 칼럼이 SELECT 대상에 포함될 때

ORDER BY의 정렬 방식

• ORDER BY를 사용하는 경우 아래 세 개의 처리 방법 중 하나를 사용 한다.

정렬 처리 방법	실행 계획의 extra 칼럼 내용
인덱스를 사용한 정렬	별도 표기 없음
조인에서 드라이빙 테이블만 정렬	“Using filesort”
조인에서 조인 결과를 임시 테이블로 저장 후 정렬	“Using temporary; Using filesort”

인덱스를 사용한 정렬

• 인덱스를 사용한 정렬를 사용하기 위해서는 다음과 같은 조건이 필요하다.
  • order by의 칼럼이 드라이빙 테이블이 되어야 하며
  • order by의 순서대로 인덱스가 있어야 한다.
  • where 조건이 있는 경우, order by에서 사용하는 인덱스를 where 에서도 사용해야 한다.
• 인덱스를 사용한 정렬은 nested loop join을 사용한다. 그러므로 레코드는 드라이빙 테이블의 인덱스에 맞춰 정렬된다. 하지만 order by를 누락하지 않고 명시해야 한다.
  • 쿼리를 정상적으로 작성했다면 order by를 사용하더라도 추가적인 작업이 없다.
  • order by를 제거하여 예상치도 못한 잘못된 결과물이 생길 경우 이로 인한 손해가 더 크다.

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

• 인덱스로 정렬이 불가능할 경우, 드라이빙 테이블을 정렬한 후 조인을 실행하여 정렬 작업을 최소화한다.
• 드라이빙 테이블의 칼럼만 order by의 조건으로 있어야 한다.
• 필터링한 드라이빙 테이블을 소트 버퍼에 보관 후 정렬한다. 정렬한 후 드리븐 테이블을 조인한다.

임시 테이블로 저장 후 정렬

• 모든 방법을 사용하지 못하는 경우, 조인한 결과를 임시 테이블에 저장한 후 그 결과를 정렬한다.
• 실행 계획에서 “Using temporary; Using filesort”란 코멘트가 표시된다.

정렬 처리 방식에 따른 성능 차이(+ LIMIT)

스트리밍 방식

• 서버 쪽에서 처리할 데이터가 얼마인지에 관계 없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트에 전송한다.
• 인덱스를 통한 정렬 방식은 추가적인 정렬이 필요 없다. 그러므로 처리하는 순서대로 바로 데이터를 보낼 수 있다.
• LIMIT으로 인한 성능 개선이 발생한다. 필요한 갯수만큼 처리하고 종료한다.
• 스트리밍 방식은 인덱스를 통한 정렬 방식에서만 동작한다.

버퍼링 방식

• 서버에서 group by, order by, where 등으로 만들어진 전체 데이터를 완성한 후 처리하는 방식
• 클라이언트는 서버의 결과를 모와 일괄 가공할 때까지 기다려야 한다.
• LIMIT으로 인한 성능 개선이 크지 않다.

JDBC와 스트리밍

• JBCD의 경우 스트리밍으로 데이타가 처리된다 하더라도 버퍼링 방식을 채택한다.
• 스트리밍으로 자바 클라이언트에 레코드를 전달하는 것보다 전체 데이터를 한 번에 처리하는 제공하여 처리하는 것을 효과적이라 판단하기 때문이다.
• 필요한 경우 스트리밍 방식으로 변경하여 처리한다.

GROUP BY 처리

• group by는 스트리밍 처리가 어렵고 having절로 필터링을 사용하는 경우 인덱스를 사용할 수 없다.
• 최적화를 위한 제약이 많지만 아래와 같은 조건에서는 인덱스를 사용할 수 있다.
  • 인덱스를 차례 대로 읽는 인덱스 스캔
  • 인덱스를 건너뛰면서 읽는 루스 인덱스 스캔
  • 인덱스를 사용하지 못하면 임시 테이블로 처리한다.

인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)

• 타이트 인덱스 스캔이란 인덱스만 사용하여 쿼리를 최적화하는 방법을 의미하여 커버링 인덱스가 여기에 포함된다.
• 조인의 드라이빙 테이블에 인덱스가 있고 해당 인덱스를 사용하여 그루핑을 하는 경우, 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리한다. 이 경우 인덱스에 따라 정렬된다.
• 실행계획에서 group by에 대한 extra 칼럼에서의 코멘트가 없다.

루스 인덱스 스캔을 이용하는 GROUP BY

• 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 방식.
• 실행 계획(extra)에서 “Using index for group-by”로 표시된다.
• 해당 쿼리의 실행 순서
  • SELECT emp_no FROM salaries WHERE from_date=1958-03-01' GROUP BY emp_no;
  • (emp_no, from_date) 인덱스를 차례대로 스캔하면서 emp_no의 첫 번째 유일한 값(그룹 키) “10001”을 찾아낸다.
  • (emp_no, from_date) 인덱스에서 emp_no가 ‘10001’인 것 중에서 from_date 값이 ‘1985-03-01’인 레코드만 가져온다. 이 검색 방법은 1번 단계에서 알아낸 ‘10001’ 값과 쿼리의 WHERE 절에 사용된 “from_date=’1985-03-01’” 조건을 합쳐서 “emp_no=10001 AND from_date=’1985-03-01’” 조건으로 (emp_ no, from_date) 인덱스를 검색하는 것과 거의 흡사하다.
  • (emp_no, from_date) 인덱스에서 emp_no의 그다음 유니크한(그룹 키) 값을 가져온다.
• 일반적인 인덱스는 카디널리티가 높을 수록 (유니크한 값이 많을 수록) 성능이 좋지만, 반대로 루스 인덱스 스캔은 카디널리티가 낮을 수록(유니크한 값이 적을 수록) 성능이 좋다.

임시 테이블을 사용하는 GROUP BY

• 인덱스를 전혀 사용하지 못하는 경우 임시 테이블을 사용한다.
• 실행 계획(extra)에서 “Using temporary”로 표시된다.
• 정렬(Using filesort)이 묵시적으로 수행되었으나 MySQL 8.0 이후에는 정렬이 수행되지 않는다.

SELECT e.last_name, AVG(s.salary) 
FROM employees e, salaries s 
WHERE s.emp_no=emp_no 
GROUP BY e.last_name;

• 위 쿼리에서 last_name은 인덱스가 존재하지 않는다. 그러므로 group by로 인해 임시 테이블을 사용한다.
• 임시테이블은 다음과 같이 동작한다.
  • group by 절의 칼럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만든다. (e.last_name이 유니크가 된다)
  • 조인의 결과를 한 건씩 가져온다. 임시 테이블에 중복 체크를 하면서 INSERT 혹은 UPDATE를 실행한다.

DISTINCT 처리

• 특정 칼럼의 유니크한 값만 조회할 때 SELECT 쿼리에 DISTINCT를 사용한다.
• DISTINCT의 경우 집합 함수를 1) 사용하는 경우와 2) 사용하지 않는 경우를 구분한다.

SELECT DISTINCT …

• 단순하게 SELECT되는 레코드 중 유니크한 레코드를 가져오는 경우 SELECT DISTINCT를 사용한다. group by와 동일하게 동작한다.
• DISTINCT에 대한 오해가 있는데 유니크한 칼럼을 괄호를 통해 한정할 수 있다는 주장이다. DISTINCT를 사용하는 순간, 특정 칼럼만 유니크하게 만드는 것이 아닌, 모든 칼럼의 조합에 대한 유니크한 레코드를 가져온다. 쿼리 파싱 과정에서 괄호는 제거된다.
• SELECT DISTINCT(emp_no), last_name FROM employees;를 작성한다 하더라도 실제 쿼리는 괄호가 제거되고 emp_no와 last_name의 유니크한 값으로 출력된다.

집합 함수와 함께 사용된 DISTINCT

• COUNT() 또는 MIN(), MAX() 같은 집합 함수 안에서 DISTINCT 키워드를 사용할 수 있다. 이 경우 함수에 삽입되는 인자 중 유니크한 값만을 사용한다.
• 이 경우 실행 계획에서 “Using temporary”를 표시하지 않지만 임시 테이블을 사용한다.
• 아래 테이블은 두 개의 COUNT 함수에 대하여 각 각 임시 테이블을 생성한다.

SELECT COUNT(DISTINCT s.salary), COUNT(DISTINCT e.last_name)
       FROM employees e, salaries s
       WHERE e.emp_no=s.emp_no
       AND e.emp_no BETWEEN 100001 AND 100100;

• 다만 아래와 같이 인덱스가 존재하는 칼럼에 대해서는 임시 테이블 없이 최적화 한다.

SELECT COUNT(DISTINCT emp_no) FROM dept_emp GROUP BY dept_no; -- IX(dept_no, emp_no)

내부 임시 테이블 활용

• MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때는 내부적인 임시 테이블을 사용한다. “내부적인” 임시 테이블이라 한 이유는 일반적으로 임시테이블은 CREATE TEMPORARY TABLE 명령을 사용하기 때문이다. 내부적인 임시 테이블은 필요에 따라 자동으로 생성하고 사용한 후 제거된다.
• 내부적인 임시 테이블은 일반적인 임시 테이블과 마찬가지로 다른 세션에서 볼 수 없다. 더불어 같은 세션의 다른 쿼리에서도 볼 수 없다. 해당 쿼리 처리가 완료되면 자동으로 삭제된다.

메모리 임시 테이블과 디스크 임시 테이블

• MySQL 8.0 이전에는 메모리에서는 MEMORY 스토리지 엔진을 사용하며 디스크에 저장될 때는 MyISAM 스토리지 엔진을 사용한다.
• MySQL 8.0 이후에는 메모리에서는 TempTable을 사용하며 디스크에서는 InnoDB를 사용한다.
• 기존 MEMORY 엔진의 경우 VARCHAR 등 가변 길이 타입을 지원하지 못하므로 메모리 낭비가 발생하였다. 디스크에서는 MyISAM이 트랜잭션을 지원하지 못하는 문제가 존재하였다. 8.0 이후에는 가변 길이 타입와 트랜잭션을 지원하게 된다.
• internal_tmp_mem_storage_engine : 메모리용 임시 테이블로서 TempTable 이나 MEMORY 중 선택
• temptable_max_ram : TempTable의 최대 사용량. 기본 1GB.
• temptable_use_mmap: temptable_max_ram을 초과할 경우 디스크로 저장한다. InnoDB 혹은 MMAP 중 선택 가능하다. InnoDB의 오버헤드로 인하여 주로 MMAP을 사용한다. 기본값은 MMAP이다.
• internal_tmp_disk_storage_engine: 임시 테이블이 내부 메모리에서 디스크로 전환되는 것이 아닌 처음부터 디스크에서 임시테이블을 생성하는 경우가 있다. 이때 테이블을 무엇으로 할지 설정할 수 있으며 기본값은 InnoDB이다.

임시 테이블이 필요한 쿼리

• 아래는 대표적으로 내부 임시 테이블을 생성하는 케이스이다. 이밖에도 인덱스를 사용할 수 없는 경우 임시 테이블을 생성한다.
  • ORDER BY나 GROUP BY에 명시된 칼럼이 다른 쿼리
  • ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
  • DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
  • UNION이나 UNION DISTINCT가 사용된 쿼리(select_type 칼럼이 UNION RESULT인 경우)
  • 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리
• 쿼리에 따라 임시테이블을 사용한다는 의미에서 extra 칼럼에 “Using temporary”를 표시한다. 하지만 마지막 3개의 패턴은 표시하지 않는다.
• 첫 번째부터 네 번째까지의 쿼리 패턴은 유니크 인덱스를 가지는 내부 임시 테이블을 만들어 처리한다. 성능이 좋지 않다.
• MySQL 8.0 이후 UNION ALL의 경우 임시 테이블을 사용하지 않도록 개선하였으나 UNION과 UNION DISTINCT는 여전히 임시테이블 사용.

RealMySQL 8.0을 참고하여 작성하였습니다.