[MySQL] 옵티마이저 및 정렬 처리 방식

기존의 Real MySQL (5.0, 5.1 버전) 책을 너무 유익하게 봤는데, Real MySQL 8.0이 전면 개정판이 나와서... 설레는 마음에 보면서 개인적인 공부용으로 정리하고 있습니다.
역시 믿고 보는 Real MySQL... 👍 한번 사서 보시는 것을 강력 추천드립니다!

실행계획

---

MySQL 서버로 요청된 쿼리는 결과는 동일하지만 내부적으로 그 결과를 만들어내는 방법은 매우 다양하다.

• 이런 다양한 방법 중 어떤 방법이 최적이고 최소의 비용이 소모될지 결정해야 한다.

MySQL에서는 쿼리를 최적으로 실행하기 위해 각 테이블이 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조하며, 그런 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업이 필요하다.

• 대부분의 DBMS에서는 옵티마이저가 이러한 기능을 담당한다. (MySQL 포함)

EXPLAIN

MySQL에서는 EXPLAIN이라는 명령으로 쿼리의 실행 계획을 확인할 수 있다.

쿼리 실행 절차

MySQL 서버에서 쿼리가 실행되는 과정은 다음과 같다

1. SQL 파싱: 사용자로부터 요청된 SQL 문장을 잘게 쪼개어 MySQL 서버가 이해할 수 있는 수준으로 분리
2. 최적화 및 실행 계획 수립: SQL 파스 트리를 확인하면서 어떤 테이블로부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택
   • 불필요한 조건 제거 및 복잡한 연산의 단순화
   • 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
   • 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
   • 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
3. 2번 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.
   • 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행.

옵티마이저의 종류

규칙 기반 최적화

• 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식

비용 기반 최적화

• 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출한다. 이렇게 산출된 실행 방법별로 비용이 최소로 소요되는 처리 방식을 선택해 최종적으로 쿼리를 실행한다.
• 현재 대부분의 RDBMS가 비용 기반 최적화를 채택. (MySQL 포함)

기본 데이터 처리

---

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

풀 테이블 스캔

• 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 나은 경우 (일반적으로 테이블이 페이지 1개로 구성된 경우)
• WHERE이나 ON절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
• 인덱스 레인지 스캔을 사용할 수 있더라도 조건 일치 레코드 건수가 너무 많은 경우

동작 방식

• MyISAM에서는 디스크로부터 페이지를 하나씩 읽어오는 방식
• InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작된다.

리드 어헤드

• 어떤 영역의 데이터가 앞으로 필요해질 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것
• 즉 처음 몇 개의 데이터 페이지는 포그라운드 스레드가 페이지 읽기를 실행하지만 특정 시점부터 읽기 작업을 백그라운드 스레드로 넘겨서 가져온다.
• innodb_read_ahead_threadshold 시스템 변수를 이용해 언제 리드 어헤드를 시작할지 임계값을 설정할 수 있다.

병렬 처리

MySQL 8.0에 새롭게 나온 기능.

• innodb_parallel_read_threads라는 시스템 변수를 이용해 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지를 변경할 수 있다.
• MySQL 8.0에서는 아무런 WHERE 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있다.

SET SESSION innodb_parallel_read_threads=2;

SELECT COUNT(*) FROM member;

ORDER BY 처리

---

인덱스를 이용하는 방식

• 이미 인덱스가 정렬돼 있어서 순서대로 읽기만 하면 되므로 매우 빠르다.
• 부가적인 인덱스 추가/삭제 작업이 필요하며, 추가적인 인덱스 디스크 공간이 더 필요.
• 인덱스의 개수가 늘어날수록 InnoDB 버퍼 풀을 위한 메모리가 많이 필요하다.

Filesort를 이용하는 방식

• 인덱스를 이용할 때의 단점이 Filesort 방식의 장점
• 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 느리다.

다음과 같은 경우 인덱스를 이용하지 못한다.

• 정렬 기준이 너무 많아서 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우
• GROUP BY의 결과 또는 DISTINCT 같은 처리의 결과를 정렬해야 하는 경우
• UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
• 랜덤 하게 결과 레코드를 가져와야 하는 경우

소트 버퍼

MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용한다. (소트 버퍼)

• 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가하지만, 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size라는 시스템 변수로 설정할 수 있다.
• 정렬해야 할 레코드의 건수가 소트 버퍼로 할당된 공간보다 크다 MySQL은 정렬해야 할 레코드를 여러 조각으로 나눠서 처리하는데, 임시 저장을 위해 디스크를 사용한다.
• 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행해야 하는데, 이 병합 과정을 멀티 머지라고 한다.

소트 버퍼의 크기

• 정렬을 위해 할당하는 소트 버퍼는 세션 메모리 영역에 해당한다. (커넥션이 많으면 많을수록, 정렬 작업이 많으면 많을수록 소트 버퍼로 소비되는 메모리 공간이 커진다는 의미.)
• MySQL 서버의 데이터가 많거나 디스크 I/O 성능이 낮은 장비라면 소트 버퍼의 크기를 더 크게 설정하는 것이 도움이 될 수 있다. 하지만 소트 버퍼를 너무 크게 설정하면 서버의 메모리가 부족해져서 MySQL 서버가 메모리 부족을 겪을 수도 있어 적절히 설정해야 한다.

정렬 알고리즘

---

싱글패스 정렬 방식

• SELECT 대상이 되는 칼럼 전부를 담아서 (정렬이 필요하지 않은 칼럼까지 전부 읽음) 정렬을 수행하는 방식

투 패스 정렬 방식

• 정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT 할 칼럼을 가져오는 정렬 방식.

싱글패스 vs 투 패스

• 투 패스 방식은 테이블을 두 번 읽어야 하기 때문에 상당히 불합리해서, 일반적으로 싱글 패스가 사용된다.
• 싱글 패스 정렬 방식은 더 많은 소트 버퍼 공간이 필요하기 때문에 다음과 같은 상황에선 투 패스 정렬 방식을 사용한다.
  • 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
  • BLOB이나 TEXT 타입의 칼럼이 SELECT 대상에 포함될 때

정렬 처리 방법

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

인덱스를 사용한 방법

• 반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다.
• 인덱스를 이용한 정렬이 처리되는 경우, 실제 인덱스 값이 정렬돼 있기 때문에 인덱스의 순서대로 읽기만 하면 된다. (MySQL 엔진에서 별도의 정렬을 위한 추가 작업을 수행하지는 않는다)

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

스트리밍 방식

• 서버 쪽에서 처리할 데이터 수에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식
• 클라이언트는 MySQL 서버가 일치하는 레코드를 찾는 즉시 전달받기 때문에 동시에 데이터의 가공 작업을 시작할 수 있다.
• LIMIT처럼 결과 건수를 제한하는 조건들은 쿼리의 전체 실행 시간을 상당히 줄여줄 수 있다.
• 정렬 처리 방법 중 "인덱스를 사용한 방법"

버퍼링 방식

• ORDER BY나 GROUP BY 같은 처리는 쿼리의 결과가 스트리밍 되는 것을 불가능하게 한다.
• 먼저 결과를 모아서 MySQL 서버에서 일괄 가공해야 하므로 모든 결과를 스토리지 엔진으로부터 가져올 때까지 기다려야 한다.
• LIMIT처럼 결과 건수를 제한하는 조건이 있어도 성능 향상에 별로 도움이 되지 않는다.
• 정렬 처리 방법 중 "인덱스를 사용한 방법" 이외의 나머지