1. 개요

옵티마이저는 MYSQL에서 최적의 쿼리 실행계획을 수립하는 기능입니다.

1.1 쿼리 실행 절차

SQL파서: 사용자로부터 요청된 SQL 문장을 쪼개서 MYSQL 서버가 이해할 수있는 수준으로 분리(파스 트리)합니다.
옵티마이저: 파싱 정보를 보면서 어떤 테이블부터 읽을지, 어떤 인덱스를 이용해 테이블을 이용해 읽을지 선택합니다.
MYSQL 엔진, 스토리지 엔진: 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져옵니다.

1.2 옵티마이저의 종류

옵티마이저는 테이터 베이스 서버에서 두뇌와 같은 역할을 담당합니다.

규칙 기반 최적화(RBO, Rule-Based Optimizer)
- 초기 버전에 많이 사용
- 내장된 우선순위에 따라 실행 계획 수립
- 통계 정보(테이블의 레코드 건수나 칼럽값의 분포도)를 조사하지 않아 항상 같은 실행 방법을 만듦
비용 기반 옵티마이저(CBO, Cost-Based Optimizer)
- 요즘 대부분의 RDBMS가 채택, MYSQL도 마찬가지
- 여러 가진 경우의 수중 최소한의 비용 선택

2. 기본 데이터 처리

모든 RDBMS는 데이터를 정렬하거나 그루핑하는 등의 기본 데이터 가공 기능을 가지고 있습니다. MYSQL 서버가 어떤 알고리즘을 사용하는지 살펴보겠습니다.

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

풀 테이블 스캔: 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽음
- 발생조건:
  - 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우( 일반적으로 테이블이 페이지 1개로 구성된 경우)
  - WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
  - 인덱스 레인지 스캔의 범위가 너무 넓은 경우 (전체 테이블의 20~25% 이상)
풀 인덱스 스캔: 인덱스를 처음부터 끝까지 읽음

⭐️리드 어헤드: 디스크 I/O 성능을 최적화하기 위해 디스크에서 데이터를 한 페이지씩 읽는 것이 아니라, 연속된 여러 페이지를 한 번에 읽어 버퍼 풀에 적재하는 기술

처음에는 포그라운드 스레드가 페이지 읽기를 실행하고
특정 시점부터는 백그라운드 스레드가 4개 또는 8개씩의 페이지를 읽으면서 계속 그 수를 증가시킵니다. 최대 64개 페이지까지 읽어서 버퍼 풀에 저장해둡니다.
포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면 되므로 쿼리가 빨리 처리됩니다.

MYSQL 서버에서는 innodb_read_ahead_threshold 시스템 변수를 이용해 언제 리드 어헤드를 시작할지 임계값을 설정할 수 있습니다.

2.2 병렬 처리

MYSQL 8.0 버전 이후부터는 하나의 쿼리에서 여러 스레드가 작업을 나누어 동시에 처리하는 것이 가능해졌습니다.(병렬 처리)

장점: 대량 데이터 처리 속도 향상.
주의사항: CPU 코어가 부족할 경우 컨텍스트 스위칭으로 오히려 성능 저하.

2.3 ORDER BY 처리(Using filesort)

ORDER BY 절은 데이터를 정렬할 때 사용됩니다. 인덱스를 활용하면 효율적으로 처리할 수 있지만, 인덱스를 활용하지 못하면 Using filesort가 발생합니다. 이는 정렬 작업이 메모리나 디스크에서 수행됨을 의미하며, 성능 저하의 원인이 됩니다. 여기서 'filesort'는 실제 파일을 사용하는 것이 아니라, MySQL이 정렬 작업을 수행하기 위해 별도의 정렬 알고리즘을 사용하는 것을 의미합니다.

Filesort 확인:

EXPLAIN SELECT * FROM employees ORDER BY name;

실행 계획의 Extra 필드에 Using filesort가 표시되면 Filesort가 발생.

최적화 방법:

ORDER BY 절에 포함된 컬럼 순서를 인덱스와 동일하게 설계.
LIMIT 절을 사용하여 정렬 작업 범위를 줄임.

2.3.1 소트 버퍼

MySQL은 정렬을 수행하기 위해 소트 버퍼라는 별도의 메모리 공간을 할당받아 사용한다.
소트 버퍼는 정렬이 필요한 경우에만 할당되는 가변적인 공간이며, 쿼리 실행이 완료되면 즉시 반납된다.
정렬해야 할 레코드 건수가 소트 버퍼 공간보다 크다면, 레코드를 여러 조각으로 나눠서 처리하는데, 이때 임시저장을 위해 디스크를 사용한다.👎 정렬하고 병합하는 과정에서 디스크 읽기, 쓰기가 유발된다.

2.3.2 filesort 정렬 알고리즘

싱글 패스 방식:

정렬 대상 + SELECT 칼럼을 모두 소트 버퍼에 저장하여 정렬 수행 후 즉시 레코드 반환

장점: 디스크 읽기가 한 번만 발생 단점: 메모리(소트 버퍼) 많이 필요

투 패스 방식:

정렬 키 + PK만 소트 버퍼에 저장하여 정렬 수행 후 정렬된 순서대로 다시 디스크에서 레코드 읽기

장점: 메모리 적게 사용 단점: 디스크 읽기가 두 번 발생

투 패스 방식은 디스크에 두 번 접근해야 하기 때문에 보통 싱글 패스 방식 선호한다고 합니다.

2.3.2 정렬 처리 방법

MySQL은 ORDER BY를 처리할 때 세 가지 주요 전략을 사용합니다:

1️⃣ 첫 번째는 인덱스를 활용한 정렬입니다. 이는 가장 효율적인 방법으로, B-Tree 인덱스의 정렬된 특성을 활용합니다. 하지만 이 방법을 사용하기 위해서는 까다로운 조건들이 충족되어야 합니다. ORDER BY에 명시된 칼럼이 반드시 드라이빙 테이블에 속해야 하며, 해당 칼럼들이 순서대로 인덱스로 구성되어 있어야 합니다. 또한 WHERE 절의 조건과 ORDER BY가 동일한 인덱스를 사용할 수 있어야 합니다.

2️⃣ 두 번째는 드라이빙 테이블만을 정렬하는 방식입니다. 이는 조인을 수행하기 전에 첫 번째 테이블의 데이터를 먼저 정렬한 후 조인을 실행합니다. 이 방법은 ORDER BY 절이 드라이빙 테이블의 칼럼만으로 구성될 때 사용할 수 있습니다.

3️⃣ 세 번째는 임시 테이블을 사용한 정렬입니다. ORDER BY의 정렬 기준이 드리븐 테이블의 칼럼일 때 이 방식이 사용됩니다. 실행 계획에서 Using temporary라는 표시로 확인할 수 있습니다.

이러한 정렬 처리는 데이터를 클라이언트에 전달하는 방식에도 영향을 미칩니다. MySQL은 두 가지 전송 방식을 사용하는데, 스트리밍 방식과 버퍼링 방식입니다. 스트리밍 방식은 데이터를 찾는 즉시 클라이언트에 전송하여 빠른 응답을 가능하게 합니다. 특히 LIMIT 절과 함께 사용될 때 효과적입니다. 반면 버퍼링 방식은 모든 데이터를 처리한 후에야 결과를 전송할 수 있어 응답 시간이 더 깁니다. ORDER BY나 GROUP BY를 사용할 때는 대부분 버퍼링 방식이 필요합니다.

성능 관점에서 볼 때, 1️⃣ 인덱스를 활용한 정렬이 가장 효율적이므로 가능하다면 이 방식을 사용하도록 쿼리를 설계해야 합니다. 불가능한 경우라면, 최소한 드라이빙 테이블만 정렬하는 방식을 사용하는 것이 좋습니다. 3️⃣ 임시 테이블을 사용한 정렬은 가장 비효율적이므로 가능한 피해야 합니다.

SELECT e.*, d.dept_name
FROM employees e
JOIN departments d ON e.dept_no = d.dept_no
WHERE e.salary > 50000;

드라이빙 테이블(outer 테이블) : employees
드리븐 테이블(inner 테이블): departments

2.4 GROUP BY 처리

GROUP BY도 쿼리가 스트리밍 처리를 할 수 없게 하는 처리입니다
GROUP BY에서 사용되는 HAVING절은 GROUP BY 결과에 대해 필터링 역할을 수행하므로 인덱스로 튜닝이 불가능합니다.
GROUP BY는 인덱스를 이용할 때는 인덱스 스캔, 루스 인덱스 스캔을 사용하고, 인덱스를 사용하지 못하는 쿼리에서는 임시 테이블을 사용합니다.

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

조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리합니다.

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

루스 인덱스 스캔 방식은 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있다. 프리픽스 인덱스는 이 방식을 사용할 수 없습니다.
- Why only 단일테이블? 인덱스 순서 유지가 JOIN에선 불가능하기 때문입니다.

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

인덱스를 전혀 사용하지 못할 때는 이 방식으로 처리된다.
8.0버전부터는 ORDER BY 절이 없을 때, 묵시적 정렬이 실행되지 않는다.

2.5 DISTINCT 처리

2.5.1 SELECT DISTINCT …

단순히 유니크한 레코드만 가져오고자 한다면 GROUP BY와 동일한 방식으로 처리된다.

2.6 내부 임시 테이블 활용

MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때는 내부적인 임시 테이블을 사용한다. 이 내부적인 임시 테이블은 “CREATE TEMPORARY TABLE” 명령으로 만든 임시 테이블과는 다르다. 내부적인 임시 테이블은 쿼리 처리가 완료되면 자동으로 삭제된다.

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

메모리는 TempTable이라는 스토리지 엔진을 사용, 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용한다.
메모리의 TempTable의 크기가 1GB를 넘으면 MySQL 서버는 메모리의 TempTable을 MMAP 파일로 전환하거나, InnoDB 테이블로 전환할 수 있다. MMAP 파일로 전환하는 것이 InnoDB 테이블로 전환하는 것보다 오버헤드가 적기 때문에 MMAP 파일로 전환하는 것이 기본값이다.
MAAP파일: 임시 테이블을 디스크에 저장하되, 파일을 메모리에 맵핑하여 디스크 I/O 오버헤드를 줄입니다.

2.6.2 임시 테이블이 필요한 쿼리

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

2.6.3 임시 테이블이 디스크에 생성되는 경우

기본적으로 메모리상에 만들어지지만 다음의 경우 디스크에 생성됩니다.
- UNION이나 UNION ALL에서 SELECT되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
- GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
- 메모리 임시 테이블의 크기가 tmp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나 temptable_max_ram 시스템 변수 값보다 큰 경우

2.6.4 임시 테이블 관련 상태 변수

실행 계획에 Using temporary가 표시됐다고해서, 임시 테이블이 메모리에서 처리됐는지 디스크에서 처리됐는지 알 수 없습니다. 한 번 표시됐다고 하나만 사용했다는 것을 의미하지도 않습니다.
이를 알기 위해, MySQL 서버의 상태 변수(SHOW SESSION STATUS LIKE ‘Created_tmp%’;)를 확인하면 된다.

3. 고급 최적화

3.1 옵티마이저 스위치 옵션

옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용해서 제어합니다.

(자세한 스위치 옵션은 책 참조)

3.1.1 MRR과 배치 키 액세스(Multi-Range Read & batch_key_access)

MRR (multi-range read), DS-MRR(disk sweep multi-range read)라고도 합니다.
기존에는 네스티드 루프 조인 방식을 사용했습니다. 드라이빙 테이블의 레코드를 한 건 읽어서 드리븐 테이블의 일치하는 레코드를 찾아서 조인을 수행하는 방식
네스티드 루프 조인 방식에서는 조인 처리를 MySQL 엔진이 담당하고, 실제 레코드를 검색하고 읽는 부분은 스토리지 엔진이 담당합니다. 이 방식에서는 스토리지 엔진에서는 최적화를 수행할 수 없습니다.
이런 단점을 보완하기 위해 조인 버퍼에 조인 대상을 버퍼링합니다. 조인 버퍼에 레코드가 가득 차면 MySQL 엔진은 버퍼링된 레코드를 스토리지 엔진으로 한 번에 요청합니다. 이렇게 해서 디스크 읽기를 최소화할 수 있습니다. 이 방식을 MRR이라고 합니다.
MRR을 응용해서 실행되는 조인 방식을 BKA(Batched Key Access) 조인이라고 한다. 부가적인 정렬 작업이 필요해서 성능이 저하되기도 합니다.

3.1.2 블록 네스티드 루프 조인

조인의 연결 조건이 되는 칼럼에 모두 인덱스가 있는 경우 사용되는 조인 방식입니다.
네스티드 루프 조인과 차이점은 조인 버퍼 사용 여부와 조인에서 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되느냐입니다. 블록 네스티드 루프 조인에서는 조인 버퍼가 사용됩니다. 실행 계획에서 Extra 칼럼에 Using join buffer가 표시되면 조인 퍼버를 사용한다는 것을 의미합니다.
조인은 드라이빙 테이블에서 일치하는 레코드의 건수만큼 드리븐 테이블을 검색하면서 처리됩니다. 그래서 드리븐 테이블을 검색할 때 인덱스를 사용할 수 없는 쿼리는 느려집니다.
옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시(조인 버퍼)한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리합니다.
조인 버퍼가 사용되는 쿼리에서는 조인의 순서가 거꾸로인 것처럼 실행됩니다. A가 드라이빙, B가 드리븐 테이블일 때, A에서 검색된 레코드를 조인 버퍼에 담아두고, B의 레코드를 먼저 읽고 조인 버퍼에서 일치하는 레코드를 찾는 방식으로 처리됩니다.

3.1.3 인덱스 컨디션 푸시다운

first_name, last_name 칼럼이 있는 테이블에서 두 칼럼에 대한 인덱스 ix_lastname_firstname이 있다고 가정해보겠습니다.

이때, SELECT * FROM employees WHERE last_name=’Action’ AND first_name LIKE ‘%sal’; 쿼리를 살펴보겠습니다.

last_name=’Action’은 ix_lastname_firstname을 인덱스 레인지 스캔으로 사용할 수 있지만, first_name LIKE ‘%sal’ 는 아닙니다.

인덱스 컨디션 푸시다운이 없다면, last_name=’Action’ 조건으로 인덱스 레인지 스캔을 하고 테이블 레코드를 읽은 후, first_name LIKE ‘%sal’ 조건에 부합되는지 여부를 비교하여 레코드를 반환하게 된다. 2건의 테이블 읽기가 발생합니다. 인덱스를 범위 제한 조건으로 사용하지 못하는 두번째 조건은 MySQL 엔진이 스토리지 엔진으로 넘기지 않기 때문입니다.
인덱스 컨디션 푸시다운을 활성화하면, 인덱스를 범위 제한 조건으로 사용하지 못하는 조건이라도 인덱스에 포함된 칼럼의 조건이라면 같이 모아서 스토리지 엔진으로 전달합니다. 그러면 불필요한 2건의 테이블 읽기가 발생하지 않습니다.

3.1.4 인덱스 확장(use_index_extensions)

이 옵션은 InnoDB 스토리지 엔진을 사용하는 테이블에서 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지를 결정하는 옵션입니다.
InnoDB 스토리지 엔진은 프라이머리 키를 클러스터링 키로 생성합니다. 그래서 모든 세컨더리 인덱스는 리프 노드에 프라이머리 키 값을 가집니다. 예를 들어, A테이블의 프라이머리 키가 (a, b)이고, ix_c라는 c 칼럼만을 포함하는 세컨더리 인덱스가 있다고 가정해보면, 이 세컨더리 인덱스는 데이터 레코드를 찾아가기 위해 프라이머리 키인 a, b 칼럼을 순서대로 포함합니다.(리프 노드에) 그래서 ix_c 인덱스는 (c, a, b) 조합으로 인덱스를 생성한 것과 흡사하게 작동합니다.

3.1.5 인덱스 머지

인덱스를 이용해 쿼리를 실행할 때, 대부분 옵티마이저는 테이블별로 하나의 인덱스만 사용하는 실행 계획을 수립합니다. 인덱스 머지 실행 계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리합니다.
쿼리에 사용된 각각의 조건이 서로 다른 인덱스를 사용할 수 있고, 그 조건을 만족하는 레코드 건수가 많을 것으로 예상될 때 MySQL 서버는 인덱스 머지 실행 계획을 선택한다.
각각의 결과를 어떤 방식으로 병합할지에 따라 아래 세가지로 구분합니다.
- index_merge_intersection
- index_merge_sort_union
- index_merge_union

3.1.5.1 인덱스 머지 - 교집합(index_merge_intersection)

2개 이상의 where 조건을 가지고 있고, 모두 인덱스를 사용할 수 있을 때 두 키를 모두 사용해서 쿼리를 처리하는 방식입니다. 실행 계획의 Extra 칼럼에 Using intersect라고 표시되면, 이 쿼리가 여러 개의 인덱스를 각각 검색하여 그 결과의 교집합만 반환한 것을 의미합니다.
옵티마이저가 각각의 조건에 일치하는 레코드 건수를 예측해본 결과, 이 조건들이 모두 상대적으로 많은 레코드를 가져와야할 때 이 방식을 사용합니다.
- 예: 인덱스 age와 city 모두 1만 건 이상을 반환할 경우.

3.1.5.2 인덱스 머지 - 합집합(index_merge_union)

인덱스 머지의 Using union은 where절에 사용된 2개 이상의 조건이 각각의 인덱스를 사용하되 OR 연산자로 연결된 경우에 사용되는 최적화합니다.
두 집합에서 하나씩 가져와서 서로 비교하면서 프라이머리 키 기준으로 중복된 값을 정렬 없이 걸러냅니다. 중복 제거를 수행할 때 사용되는 알고리즘은 우선순위 큐이다.
Extra 칼럼에 Using union으로 표시됩니다.

3.1.5.3인덱스 머지 - 정렬 후 합집합(index_merge_sort_union)

만약 인덱스 머지 작업을 하는 도중에 결과의 정렬이 필요한 경우 sort union 알고리즘을 사용합니다.
Extra 칼럼에 Using sort union으로 표시됩니다.

3.1.6 세미 조인(semijoin)

세미조인이란 EXISTS 및 IN 절과 함께 서브쿼리를 사용하는 것을 의미합니다.
다른 테이블과 실제 조인을 수행하지는 않고, 단지 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리를 세미 조인이라고 합니다.

3.1.6.1 세미 조인(semijoin) - 테이블 풀-아웃

Table pullout 최적화는 세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태의 최적화입니다.
Table pullout 최적화가 사용됐는지 정확하게 확인하는 방법은 EXPLAIN 명령 실행 직후, SHOW WARNINGS 명령으로 MySQL 옵티마이저가 재작성한 쿼리를 보는 것입니다. IN (subquery) 형태가 사라지고, JOIN으로 쿼리가 재작성되어 있습니다.
Table pullout 최적화는 모든 형태의 서브쿼리에서 사용될 수 있는 것은 아니고, 몇 가지 제한 사항이 있습니다.

3.1.6.2 세미 조인(semijoin) - 퍼스트 매치

First Match 최적화 전략은 IN (subquery) 형태의 세미 조인을 EXISTS (subquery) 형태로 튜닝한 것과 비슷한 방법으로 실행됩니다.
First Match 최적화가 사용되면 실행 계획의 Extra 칼럼에는 FirstMatch(table-N) 문구가 표시됩니다.

3.1.6.3 세미 조인(semijoin) - 루스 스캔(loosescan)

세미 조인 서브쿼리 최적화의 루스 스캔은 루스 인덱스 스캔과 비슷한 읽기 방식을 사용합니다.
루스 스캔 최적화는 루스 인덱스 스캔으로 서브쿼리 테이블을 읽고, 그다음으로 아우터 테이블을 드리븐으로 사용해서 조인을 수행합니다. 그래서 서브쿼리 부분이 루스 인덱스 스캔을 사용할 수 있는 조건이 갖춰져야 사용할 수 있는 최적화입니다.

3.1.6.4 세미 조인(semijoin) - 구체화(Materialization)

Materialization 최적화는 세미 조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화한다는 의미입니다. 구체화는 내부 임시 테이블을 생성한다는 것을 의미한합니다.
다른 서브쿼리 최적화와는 달리, 서브쿼리 내에 GROUP BY절이 있어도 사용할 수 있습니다.

3.1.6.5 세미 조인(semijoin) - 중복 제거(Dubplicated Weed-out)

Dubplicated Weed-out은 세미 조인 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제거하는 방법으로 처리되는 최적화 알고리즘입니다.
실행 계획에 Dubplicated Weed-out이 표시되지는 않지만, Extra 칼럼에 start temporary, end temporary 문구가 별도로 표기됩니다. start/end temporary 문구의 구간이 Dubplicated Weed-out 최적화의 처리 과정이라고 보면 됩니다.

3.1.7 컨디션 팬아웃(condition_fanout_filter)

조인을 실행할 때 테이블의 순서는 쿼리의 성능에 매우 큰 영향을 미칩니다.
옵티마이저는 여러 테이블이 조인되는 경우, 가능하다면 일치하는 레코드 건수가 적은 순서대로 조인을 실행한다.
옵티마이저가 조건을 만족하는 레코드 건수를 정확하게 예측할 수 있다면, 더 빠른 실행 계획을 만들 수 있습니다. 실행 계획에서 예측된 컬럼 비율은 filtered 컬럼에서 확인할 수 있습니다.
condition_fanout_filter 최적화 기능을 활성화하면 옵티마이저는 더 정교한 계산을 하므로 더 많은 시간, 컴퓨팅 자원이 소모됩니다. 그러므로 쿼리가 간단한 경우에는 큰 도움이 되지 않을 수 있습니다.

3.1.8 파생 테이블 머지(derived_merge)

예전 버전의 MySQL 서버에서는 FROM 절에 사용된 서브쿼리는 먼저 실행해서 그 결과를 임시 테이블로 만든 다음 외부 쿼리 부분을 처리했다. 이렇게 FROM 절에 사용된 서브쿼리를 파생 테이블이라고 부른니다.
임시 테이블에 레코드가 많아진다면 임시 테이블로 레코드를 복사하고 읽는 오버헤드로 인해 쿼리 성능이 저하됩니다.
5.7 버전부터는 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합해서 서브쿼리 부분을 제거하는 최적화가 도입됐습니다. (derived_merge 최적화 옵션)

3.1.9 인비저블 인덱스(use_invisible_indexes)

인덱스를 삭제하지 않고, 해당 인덱스를 사용하지 못하게 제어하는 기능입니다. ALTER TABLE … ALTER INDEX … [ VISIBLE | INVISIBLE ]
use_invisible_indexes 옵션을 on으로 설정하면, INVISIBLE 상태의 인덱스도 옵티마이저가 볼 수 있습니다. (기본값은 off)

3.1.10 스킵 스캔

인덱스의 핵심은 값이 정렬돼 있다는 것이며, 이로 인해 인덱스를 구성하는 칼럼의 순서가 매우 중요합니다. 예를 들어, (A, B, C) 칼럼으로 구성된 인덱스가 있을 때, WHERE 절에 B와 C 칼럼에 대한 조건을 가지고 있다면 이 쿼리는 인덱스를 활용할 수 없습니다. 인덱스 스킵 스캔은 제한적이긴 하지만 인덱스의 이런 제약 사항을 뛰어넘을 수 있는 최적화 기법입니다.
인덱스의 선행 칼럼이 조건절에 사용되지 않더라도 후행 칼럼의 조건만으로도 인덱스를 이용한하여 쿼리 성능을 개선한 것입니다.
하지만 인덱스의 선행 칼럼이 매우 다양한 값을 가지는 경우에는 비효율적일 수 있으므로, 옵티마이저는 인덱스의 선행 칼럼이 소수의 유니크한 값을 가질 때만 인덱스 스킵 스캔 최적화를 사용합니다.

3.1.11 해시 조인

해시 조인은 첫 번째 레코드를 찾는 데는 시간이 많이 걸리지만 최종 레코드를 찾는 데까지는 시간이 많이 걸리지 않으므로, 최고 스루풋(best throughput) 전략에 적합합니다.
네스티드 루프 조인은 마지막 레코드를 찾는 데까지는 시간이 많이 걸리지만 첫 번째 레코드를 찾는 것은 상대적으로 훨씬 빠르므로, 최고 응답 속도(best response-time) 전략에 적합합니다.
일반적인 웹 서비스는 온라인 트랜잭션 서비스이므로 응답 속도가 더 중요합니다. 분석 서비스는 전체 스루풋이 더 중요합니다. MySQL은 범용 RDBMS이므로 응답 속도에 더 집중해야 합니다. 즉, MySQL 서버의 해시 조인 최적화는 네스티드 루프 조인이 사용되기에 적합하지 않은 경우를 위한 차선책입니다.
해시 조인은 빌드 단계와 프로브 단계로 나뉘어 처리됩니다. 빌드 단계에서는 조인 대상 테이블 중에서 레코드 건수가 적어서 해시 테이블로 만들기에 용이한 테이블을 골라서 메모리에 해시 테이블을 생성하는 작업을 수행합니다. 해시 테이블을 만들 때 사용되는 원본 테이블을 빌드 테이블이라고 합니다. 프로브 단계는 나머지 테이블의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는 과정입니다. 이 나머지 테이블을 프로브 테이블이라고 합니다.
해시 테이블을 메모리에 저장할 때 조인 버퍼를 사용합니다. 해시 테이블이 조인 버퍼 메모리보다 큰 경우 빌드 테이블과 프로브 테이블을 청크 단위로 나눠 저장된 청크 개수만큼 반복 처리하여 완성된 조인 결과를 만들어냅니다.
옵티마이저는 빌드 테이블 크기에 따라 클래식 해시 조인 알고리즘(메모리에서 모두 처리 가능한 경우), 그레이스 해시 조인 알고리즘(해시 테이블이 조인 버퍼 메모리보다 큰 경우)을 하이브리드하게 활용하도록 구현돼 있다.

3.1.12 인덱스 정렬 선호(prefer_ordering_index)

옵티마이저도 실수할 수 있습니다. 옵티마이저가 ORDER BY를 위한 인덱스에 너무 가중치를 부여하지 않도록 prefer_ordering_index 옵션을 사용할 수 있습니다. 기본값은 on이지만, 옵티마이저가 자주 실수한다면 off로 설정할 수 있습니다.

3.2 조인 최적화 알고리즘

3.2.1 Exhaustive 검색 알고리즘

FROM 절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법입니다.
테이블이 n개라면 가능한 조인 조합은 n!개이므로, 테이블이 많아지면 시간이 오래 걸립니다.

3.2.2 Greedy 검색 알고리즘

Exhaustive 검색 알고리즘의 시간 소모적인 문제점을 해결하기 위해 도입된 최적화 기법이다.
MySQL에서는 조인 최적화를 위한 시스템 변수로 optimizer_prune_level, optimizer_search_depth가 제공됩니다.
- optimizer_search_depth 시스템 변수는 Greedy 검색 알고리즘과 Exhaustive 검색 알고리즘 중에서 어떤 알고리즘을 사용할지 결정하는 시스템 변수입니다. optimizer_search_depth는 0~62 사이의 정수로 설정할 수 있고, 1~62는 지정된 개수로 한정해서 최적의 실행 계획을 산출하고, 0은 옵티마이저가 자동으로 결정합니다. optimizer_search_depth 설정 값과 쿼리의 조인 테이블 개수에 따라 Exhaustive 검색만 사용되거나 Greedy 검색과 Exhaustive 검색이 동시에 사용됩니다.
- optimizer_prune_level 시스템 변수는 Heuristic 검색이 작동하는 방식을 제어한다.

4. 쿼리 힌트

쿼리 힌트는 옵티마이저에게 쿼리의 실행 계획을 어떻게 수립해야 할지 알려줄 수 있는 방법입니다.

4.1 인덱스 힌트

인덱스 힌트는 모두 MySQL 서버에 옵티마이저 힌트가 도입되기 전에 사용되던 기능들입니다. 가능하다면 옵티마이저 힌트를 사용하는 것이 낫습니다.

인덱스 힌트는 SELECT, UPDATE 명령에서만 사용할 수 있습니다.

4.1.1 STRAIGHT_JOIN

STRAIGHT_JOIN은 SELECT, UPDATE, DELETE 쿼리에서 여러 개의 테이블이 조인되는 경우, 조인 순서를 고정하는 역할을 합니다.
여러 개의 테이블을 조인할 때 어느 테이블이 드라이빙(Driving) 테이블이 되고, 어느 테이블이 드리븐(Driven) 테이블이 될지 알 수 없습니다. 따라서 조인 순서를 고정해야 하는 경우에 STRAIGHT_JOIN을 사용할 수 있습니다.
사용 방법: 인덱스 힌트는 SELECT, UPDATE 명령 바로 뒤에 사용합니다.
sql 코드 복사 SELECT STRAIGHT_JOIN e.first_name, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id;
STRAIGHT_JOIN 힌트는 옵티마이저가 FROM 절에 명시된 테이블 순서대로 조인을 수행하도록 유도합니다.
조인 순서를 고정해야 하는 경우:
- 임시 테이블과 일반 테이블 조인:임시 테이블을 드라이빙 테이블로 선택하는 것이 좋습니다.
- 임시 테이블끼리 조인:임시 테이블은 항상 인덱스가 없으므로, 크기가 작은 테이블을 드라이빙 테이블로 선택하는 것이 좋습니다.
- 일반 테이블끼리 조인:
  - 양쪽 테이블 모두 조인 칼럼에 인덱스가 존재하거나, 인덱스가 없는 경우:레코드 건수가 적은 테이블을 드라이빙 테이블로 선택하는 것이 좋습니다.
  - 한쪽만 인덱스가 있는 경우:조인 칼럼에 인덱스가 없는 테이블을 드라이빙 테이블로 선택하는 것이 일반적입니다.
  - 여기서 말하는 레코드 건수는 전체 테이블의 레코드 수가 아니라, WHERE 조건을 만족하는 레코드 수를 의미합니다.

4.1.2 USE INDEX / FORCE INDEX / IGNORE INDEX

사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시합니다.
옵티마이저는 일반적으로 사용할 인덱스를 잘 선택하지만,비슷한 인덱스가 여러 개 있을 때 실수를 할 수 있습니다.이럴 때 특정 인덱스를 강제로 사용하거나 사용하지 않도록 설정할 수 있습니다.
옵티마이저 인덱스 힌트 종류:
- USE INDEX:
```
sql
코드 복사
SELECT * FROM users USE INDEX (idx_age) WHERE age > 30;
```
- 특정 인덱스를 사용하도록 권장하는 힌트.
- FORCE INDEX:그러나 USE INDEX도 충분히 강력하기 때문에 일반적으로 FORCE INDEX는 잘 사용하지 않습니다.
- USE INDEX보다 강한 힌트로, 특정 인덱스 사용을 강제합니다.
- IGNORE INDEX:
```
sql
코드 복사
SELECT * FROM users IGNORE INDEX (idx_age) WHERE age > 30;
```
- 특정 인덱스를 사용하지 못하게 하는 힌트.
세부 인덱스 힌트 사용 가능:
USE INDEX FOR JOIN, USE INDEX FOR ORDER BY와 같이 특정 용도로만 인덱스를 지정할 수 있습니다.

4.1.3 SQL_CALC_FOUND_ROWS

MySQL의 LIMIT을 사용하는 경우, 조건을 만족하는 레코드가 더 많더라도 LIMIT에 명시된 수만큼 데이터를 찾으면 검색을 멈춥니다.
그러나 SQL_CALC_FOUND_ROWS를 사용하면, 검색을 멈추지 않고 끝까지 검색하여 총 레코드 수를 계산합니다.
이 기능은 성능 최적화 목적이 아니라 개발 편의성을 위한 기능이므로 사용을 지양하는 것이 좋습니다.

sql
코드 복사
SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT 10;
SELECT FOUND_ROWS();

4.2 옵티마이저 힌트

4.2.1 옵티마이저 힌트 종류

옵티마이저 힌트는 적용 범위에 따라 다음 4가지 그룹으로 나뉩니다.
- 인덱스 힌트
- 테이블 힌트
- 쿼리 블록 힌트
- 글로벌 힌트 (쿼리 전체)

4.2.2 MAX_EXECUTION_TIME

쿼리의 최대 실행 시간을 설정하는 힌트입니다.
지정한 시간을 초과하면 쿼리는 실패 처리됩니다.

sql
코드 복사
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM users WHERE age > 20;

4.2.3 SET_VAR

정렬 버퍼(소트 버퍼)나 조인 버퍼 크기를 일시적으로 조정하여 대용량 데이터 처리 성능을 향상시키는 힌트입니다.

sql
코드 복사
SELECT /*+ SET_VAR(sort_buffer_size=2097152) */ * FROM users WHERE age > 20;

4.2.4 SEMIJOIN & NO_SEMIJOIN

SEMIJOIN 힌트는 세미 조인 최적화 방식을 사용하도록 강제하는 힌트입니다.
NO_SEMIJOIN은 반대로 세미 조인 최적화를 사용하지 않도록 합니다.

4.2.5 INDEX_MERGE & NO_INDEX_MERGE

INDEX_MERGE는 다중 인덱스 병합 최적화를 사용하도록 합니다.
NO_INDEX_MERGE는 다중 인덱스 병합 최적화 비활성화.

sql
코드 복사
SELECT /*+ INDEX_MERGE(users idx_age, idx_city) */ * FROM users WHERE age > 30 AND city = 'Paris';

4.2.6 BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

BNL (Block Nested Loop)와 HASHJOIN은 조인 방식을 강제하는 힌트입니다.
NO_BNL과 NO_HASHJOIN은 해당 조인 방식을 비활성화합니다.

4.2.7 INDEX & NO_INDEX

INDEX는 특정 인덱스를 사용하도록 지정합니다.
NO_INDEX는 특정 인덱스를 사용하지 않도록 지정합니다.
옵티마이저 힌트에서는 테이블명과 인덱스명을 함께 명시해야 합니다.

sql
코드 복사
SELECT /*+ INDEX(users idx_age) */ * FROM users WHERE age > 30;

저작자표시 (새창열림)

'CS > 데이터베이스' 카테고리의 다른 글

레디스 철학, 자료구조, 클라이언트, 인코딩(’이것이 레디스다’ 책) (1)	2025.05.16
레디스 직렬화 4총사 (1)	2025.05.16
[Real MYSQL 10장] 실행 계획 (0)	2025.01.03
공유 락과 배타 락 (0)	2024.12.10
MySQL 아키텍쳐 (1)	2024.11.26

[Real MySQL 9장] 옵티마이저와 힌트