MySQL 옵티마이저와 힌트 기본 데이터 처리

https://www.aladin.co.kr/shop/wproduct.aspx?ItemId=278488709

아래 내용은 RealMySQL 8.0 책 내용을 참고하여 정리하였습니다.

기본 데이터 처리

MySQL 서버에서 쿼리가 실행되는 과정

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

옵티마이저의 종류

  1. CBO Cost-based optimizer
    통계 정보를 이용해 실행 계획별 비용을 산출하고, 최소 비용이 소요되는 처리방식을 최종적으로 실행
  2. RBO Rule-based optimizer
    통계 정보를 조사하지 않고, 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립한다.

풀 테이블 스캔을 하는 경우

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

InnoDB에서의 풀 테이블 스캔

특정 테이블의 연속된 데이터 페이지가 읽히면, 백그라운드 스레드에 의해 리드 어헤드 작업이 시작된다.
미리 디스크에서 데이터를 버퍼 풀에 가져다 두면서, 쿼리 처리속도가 빨라지게 된다.
innodb_read_ahead_threshold 변수를 조정해서 리드 어헤드 시작의 임계값을 설정 가능
innodb_read_ahead_threshold 개수만큼 연속된 데이터 페이지가 읽히면 백그라운 스레드를 이용해 대량으로 페이지를 읽음 -> Multi Block I/O 수행

Read ahead 리드 어헤드

풀 테이블 스캔 뿐 아니라 풀 인덱스 스캔에서도 동일하게 사용됨

Mysql8.0 병렬처리

MySQL 8.0 부터 병렬 처리가 가능해짐
여기서 말하는 병렬 처리는 하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리하는 것.
여러 개의 쿼리가 동시에 처리되는 것을 의미하는 것이 아님
innodb_parallel_read_threads 변수를 이용해 하나의 쿼리를 최대 몇개의 스레드를 이용해서 처리할 수 있을지 설정 가능
병렬처리 스레드 개수를 늘리면 쿼리 처리에 걸리는 시간이 줄어들지만, CPU 코어 개수를 넘어서는 경우 오히려 성능이 떨어질 수도 있다.
AWS RDS Aurora 에서는 5.6 5.7에 대해 병렬쿼리가 가능한 Aurora Version들이 존재한다.
Aurora 에서도 Parallel Query가 가능하지만, VolumeRead 비용이 증가할 수 있다.

Aurora MySQL 클러스터가 병렬 쿼리를 사용하는 경우 VolumeReadIOPS 값이 증가할 수 있습니다. 병렬 쿼리는 버퍼 풀을 사용하지 않습니다. 따라서 쿼리는 빠르지만 이렇게 최적화된 프로세싱은 읽기 작업 및 관련 비용을 증가시킬 수 있습니다.

https://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/aurora-mysql-parallel-query.html#aurora-mysql-parallel-query-monitoring

ORDER BY 처리(Using filesort)

  1. 인덱스를 이용해 정렬을 처리
  2. Filesort로 별도로 처리

인덱스 이용
장점: INSERT, UPDATE, DELETE 쿼리가 실행될 때 인덱스가 이미 정렬되어 있으므로, 매우 빠르다.
단점 : INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가 삭제 작업이 필요하다, 인덱스가 디스크 공간을 차지함, 인덱스가 늘어날수록 InnoDB 버퍼 풀을 위한 메모리가 많이 필요함
Filesort 이용
장점 : 인덱스가 필요없으며, 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리된다.
단점: 정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 느리다.

인덱스를 이용해 정렬 하는것이 불가능한 경우

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

소트 버퍼(정렬을 위한 메모리 공간)

MySQL은 정렬을 위해 별도의 메모리 공간을(소트 버퍼) 할당받아서 사용한다.(세션별)
메모리는 정렬 이후에 시스템에 반환된다.
최대 사용 가능한 소트 버퍼의 크기는 sort_buffer_size 변수로 설정할 수 있다.

소트 버퍼가 문제가 되는 이유

정렬해야 할 레코드 건수가 소트 버퍼로 감당이 안되는 경우(레코드가 많은 경우) 정렬을 위해 레코드를 여러 조각으로 나눠서 디스크를 이용해서 처리한다.
소트 버퍼를 크게 설정하더라도 실제로 쿼리 응답 속도가 빨라지지는 않는다.
정렬하는 데이터의 특징, 서버의 메모리나 디스크 특성에 따라 결과가 달라질 수는 있다.
큰 메모리를 할당하는 경우, System Crash가 일어날 수도 있다. 과도한 메모리 할당으로 인해 OOM킬러가 MySQL 서버를 죽일 수 있다.
리눅스 계열에서 너무 큰 sort_buffer_size를 사용하면 큰 메모리 공간 할당 때문에 성능이 훨씬 떨어질 수도 있다.

또한 리눅스에서 메모리 공간할당을 하면서 oversize된 메모리 할당은 memory allocator가 싱글 스레드로 작동하게 한다고 한다. 아마 이 때문이지 않을까 싶다.

In any situation, the use of an oversized allocation will cause the allocator to become single-threaded.

https://www.oracle.com/technical-resources/articles/it-infrastructure/dev-mem-alloc.html

멀티 머지(Multi-merge)

메모리의 소트 버퍼에서 정렬 -> 임시로 디스크에 기록 -> 다음 레코드를 소트 버퍼에서 정렬 -> 반복
정렬된 레코드를 다시 병합하면서 정렬을 수행
멀티 머지 횟수는 Sort_merge_passes 변수에 누적 집계된다.

싱글 패스 정렬 방식

소트 버퍼에 정렬 기준 칼럼을 포함한, SELECT 대상 모든 칼럼을 전부 담아서 정렬을 수행하는 방식 ( 더 많은 소트 버퍼 공간이 필요함)

투 패스 정렬 방식

정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에서 담아서 정렬 후, 정렬된 순서대로 다시 PK로 테이블을 읽어서 SELECT할 칼럼을 가져오는 방식 (테이블을 두 번 읽어야 한다.)

특정 조건에서는 투 패스 정렬 방식을 사용

  • 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
  • BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함될 때

정렬 처리 방법

  1. 인덱스를 이용한 정렬
  2. 조인에서 드라이빙 테이블만 정렬 (Using filesort)
  3. 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 (Using temporary; Using filesort)

인덱스를 이용한 정렬

인덱스의 값이 이미 정렬이 돼있기 때문에, 인덱스의 순서대로 읽기만 하면 된다.
스트리밍 방식으로 처리

조건

  • ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 함.
  • WHERE절에 첫 번째로 읽는 테이블의 칼럼에 대한 조건이 있다면, 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 함.
  • B-Tree 계열의 인덱스만 정렬이 가능(R-Tree도 불가능)
  • 여러 테이블이 조인되는 경우 Nested-loop 방식의 조인에서만 인덱스를 이용한 정렬이 가능(조인 버퍼가 사용되면, 순서가 흐트러질 수 있다.)

정렬

정렬 X

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

인덱스를 이용해 드라이빙 테이블을 검색 -> 드라이빙 테이블의 칼럼으로 검색 결과를 정렬 -> 정렬된 결과를 읽으면서 조인을 수행해 드리븐 테이블의 레코드를 가져와서 결과를 출력
버퍼링 방식으로처리

조건

조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 칼럼만 ORDER BY 절을 작성해야 한다.

정렬

드라이빙 테이블의 레코드 건수 만큼 정렬

임시 테이블을 이용한 정렬

2개 이상의 테이블을 조인해서 결과를 정렬해야 한다면, 임시 테이블이 필요할 수도 있다.
조인의 드라이빙 테이블만 정렬이 가능하지 않은 패턴의 쿼리에서는 항상 조인 결과를 임시 테이블에 저장하고, 임시 테이블을 다시 정렬해야 한다.
버퍼링 방식으로처리

정렬

조인된 결과 레코드 건수를 전부 정렬

쿼리가 처리되는 방법

스트리밍 방식

서버에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될때마다 바로바로 클라이언트로 전송해주는 방식
쿼리 요청 -> 원했던 첫 번째 레코드를 받음

이 방식으로 처리되는 쿼리는 레코드 수에 상관없이 빠른 응답시간이 보장된다.

LIMIT같이 결과를 건수를 제한하는 조건들이 쿼리 전체의 실행 시간을 줄여줄 수 있다

버퍼링 방식

ORDER BY 나 GROUP BY 같은 처리가 들어가면 쿼리 결과가 스트리밍 되는 것이 불가능해진다.
레코드를 가져온 후, 일괄로 가공해서 차례대로 보내야 하기 때문.

LIMIT같이 결과를 건수를 제한하는 조건들이 있어도, 성능 향상에는 별로 도움이 되지 않는다.
네트워크로 전송되는 레코드의 건수를 줄일 수는 있다.

MySQL 서버에서 정렬과 관련된 상태변수

Sort_merge_passes : 멀티 머지 처리 횟수
Sort_range : 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수
Sort_scan : 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수
Sort_rows : 지금까지 정렬한 전체 레코드 건수

GROUP BY 처리

GROUP BY 에서 사용된 조건(HAVING 절)은 인덱스를 사용해서 처리될 수 없다. -> GROUP BY 이후 결과에 대한 필터링 이기 때문

MySQL 8.0 이전 버전까지는 GROUP BY 가 사용된 쿼리는 그루핑되는 칼럼을 기준으로 묵시적인 정렬까지 함께 수행했다.

GROUP BY 에서 인덱스를 사용하는 경우

인덱스 스캔(타이트 인덱스 스캔) 하는 경우

ORDER BY와 마찬가지로 조인의 드라이빙 테이블에 속한 칼럼만을 이용해 그루핑 할 때, GROUP BY 칼럼으로 인덱스가 이미 있다면, 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고, 그 결과로 조인을 처리한다.
인덱스를 차례대로 읽기 때문에, 추가적인 정렬 작업이나 내부 임시 테이블이 필요하지 않다.

그룹 함수(Aggregation function)등의 그룹값을 처리해야하는 경우 임시 테이블이 필요하다.

루스 인덱스 스캔(인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것) 하는 경우

실행 계획에서 Using index for group-by 코멘트가 표시됨

단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용 가능
Prefix index 는 인덱스 스캔을 사용할 수 없다.

GROUP BY 에서 인덱스를 사용하지 못한 경우

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

GROUP BY의 기준이 드라이빙 테이블 이던지, 드리븐 테이블 이던지에 관계없이 인덱스를 전혀 사용하지 못할 때 처리되는 방식

DISTINCT 처리

특정 칼럼의 유니크한 값만 조회 할때 SELECT 쿼리에 DISTINCT를 사용한다.
DISTINCT는 SELECT하는 레코드(튜플)을 유니크하게 SELECT하는 것이다. 즉, DISTINCT키워드는 조회되는 모든 칼럼에 영향을 미친다.

DISTINCT 처리가 인덱스를 사용하지 못하면 항상 임시 테이블이 필요하지만, 실행 계획상에서는 표시되지 않는다.(5.7.37에서는 왜 표시가 되는걸까요..??)

집합 함수가 없는 경우(SELECT DISTINCT … 형태)

GROUP BY 와 동일한 방식으로 처리 된다. 특히 8.0 에서는 GROUP BY에서 자동으로 정렬을 하지 않기 때문에 아래 쿼리는 내부적으로 같다.

SELECT DISTINCT first_name, last_name FROM test.authors where id BETWEEN 10 AND 50;

SELECT first_name, last_name FROM test.authors where id BETWEEN 10 AND 50 GROUP BY first_name, last_name

집합 함수와 함께 사용되는 경우

COUNT() 또는 MAX(), MIN() 같은 집합 함수내에서 DISTINCT를 사용하면, SELECT DISTINCT 와 다른 형태로 해석된다.
함수 내에서 사용된 DISTINCT는 그 함수의 인자로 전달된 칼럼값이 유니크한 것들을 가져온다.

DISTINCT 처리를 위해 인덱스를 이용할 수 없으면 임시 테이블이 필요하나, 인덱스 칼럼에 대해 DISTINCT를 처리할 때는 인덱스 풀 스캔이나 레인지 스캔을 하면서 임시 테이블 없이 쿼리를 수행하는 것이 가능하다.

내부 임시 테이블 활용

MySQL 엔진이 레코드를 정렬하거나 그루핑할 때는 임시 내부 임시 테이블을 사용한다.
내부 임시 테이블은 CREATE TEMPORARY TABLE 명령으로 만든 임시 테이블과는 다르다.
임시 테이블은 처음에 메모리에 생성됐다가 테이블에 크기가 커지면 디스크로 옮겨진다. 예외적으로 바로 디스크에 생성되는 경우도 있다.

내부 임시 테이블은 다른 세션이나 다른 쿼리에서 볼 수 없으며, 사용도 불가능하다. 쿼리의 처리가 완료되면 자동으로 삭제된다.

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

MySQL 8.0 이전

임시 테이블이 메모리 사용 : MEMORY 스토리지 엔진, 디스크에 저장 : MyISAM 스토리지 엔진
메모리 임시 테이블의 최대 사이즈를 tmp_table_size, max_heap_table_size 두 변수로 설정함

Memory 스토리지 엔진 = VARBINARY, VARCHAR등 가변 길이 타입을 지원하지 못함 -> 최대 사이즈로 메모리를 할당해서 사용 -> 메모리 낭비가 심하다.

MyISAM 스토리지 엔진 = 트랜잭션이 지원되지 않음

MySQL 8.0 이후

임시 테이블이 메모리 사용 : TempTable 스토리지 엔진, 디스크에 저장 : InnoDB 스토리지 엔진
internal_tmp_mem_storage_engine 시스템 변수를 이용해 메모리용 임시 테이블을 선택할 수 있다.
TempTable이 사용가능한 최대 메모리 크기는 temptable_max_ram 변수로 제어할 수 있다.

TempTable 스토리지 엔진 = 가변 길이 타입 지원

InnoDB 스토리지 엔진 = 트랜잭션 지원

임시 테이블의 크기가 temptable_max_ram보다 커지는 경우 MMAP 파일로 디스크에 기록, InnoDB 테이블로 기록 둘 중의 하나의 방식으로 디스크로 기록하게 된다.
MMAP 파일로 전환하는 것이 InnoDB 테이블로 전환하는 것보다 오버헤드가 적다.
temptable_use_mmap 의 기본값은 ON으로 되어있음

디스크의 임시 테이블을 생성할 때, 파일 오픈 후 즉시 파일 삭제를 실행한다. -> 파일을 참조하는 프로세스가 모두 없어지면 그때 바로 파일이 삭제된다.

내부 임시테이블이 처음부터 디스크 테이블로 생성되는 경우

  • UNION 이나 UNION ALL 에서 SELECT 되는 칼럼 중에서 길이가 512 바이트 이상인 크기의 칼럼이 있는 경우
  • GROUP BY 나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
  • 메모리 임시 테이블의 크기가 temptable_max_ram 시스템 변수보다 큰 경우

internal_tmp_disk_storage_engine 시스템 변수에 설정된 스토리지 엔진이 사용됨. 기본값 = InnoDB

임시 테이블이 필요한 쿼리

임시 테이블을 생성하는 대표적인 케이스

  • ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리 ( w/ UNIQUE INDEX)
  • ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리 ( w/ UNIQUE INDEX)
  • DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리 ( w/ UNIQUE INDEX)
  • UNION이나 UNION DISTINCT가 사용된 쿼리(select_type 칼럼이 UNION RESULT인 경우) ( w/ UNIQUE INDEX)
  • 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리
  • UNION ALL이 사용된 쿼리(select_type 칼럼이 UNION RESULT인 경우) ( MySQL Version < 8.0)

이탤릭 처리 된 3개의 경우에는 Using temporary가 표시되지 않을 때도, 임시 테이블을 사용할 수 있다.

임시 테이블 관련 상태 변수

SHOW SESSION STATUS LIKE 'Created_tmp%'; 로 확인 가능

Created_tmp_tables : 쿼리 처리를 위해 만들어진 내부 임시 테이블의 개수를 누적하는 상태 값(메모리, 디스크 구분 X)
Created_tmp_disk_tables : 디스크에 내부 임시 테이블이 만들어진 개수만 누적하는 상태 값