Real MySQL 9장

 

 

어떤 DBMS든 쿼리의 실행 계획을 수립하는 옵티마이저는 가장 복잡한 부분으로 알려져 있고, 옵티마이저가 만들어 내는 실행 계획을 이해하는 것 또한 상당히 어려운 부분입니다. 하지만 실행 계획을 이해할 수 있어야 실행 계획의 불합리한 부분을 찾아내고, 더 최적화된 방법으로 실행 계획을 수립하도록 유도할 수 있습니다.

쿼리 실행 절차

MySQL 서버에서 쿼리가 실행 되는 과정은 아래와 같습니다.

1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해 가능한 수준으로 분리(파스 트리) 합니다.
2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택합니다.
3. 2번에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져옵니다.

1번을 SQL 파싱 이라고 하고, MySQL 서버의 SQL 파서 라는 모듈로 처리합니다. 이후 SQL 파스 트리 가 만들어지고, MySQL은 SQL 문장 자체가 아닌 SQL 파스 트리를 이용해 쿼리를 실행합니다.

2번은 최적화 및 실행 계획 수립 단계이고, 옵티마이저에서 처리합니다. 그리고 1번에서 만들어진 SQL 파스 트리를 참조해 아래와 같은 내용을 처리합니다.

• 불필요한 조건 제거 및 복잡한 연산의 단순화
• 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
• 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
• 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정

이후 실행 계획이 만들어집니다.

3번에서는 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행합니다.

옵티마이저의 종류

옵티마이저 = 데이터베이스 서버의 두뇌

옵티마이저는 비용 기반 최적화(Cost-based optimizer, CBO) 와 규칙 기반 최적화 방법(Rule-based optimizer, RBO)으로 나눌 수 있습니다. (현재 대부분의 DBMS는 CBO를 선택)

 

• 규칙 기반 최적화
  • 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고, 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식입니다.
  • 통계 정보(테이블의 레코드 건수나 칼럼의 값 분포도)를 조사하지 않고 실행 계획이 수립되기 때문에 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 만들어 냅니다.
  • 하지만 데이터의 분포도는 매우 다양해서 거의 사용되지 않습니다.
• 비용 기반 최적화
  • 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출합니다.
  • 실행 방법별 비용이 최소로 소요되는 처리 방식을 선택해 쿼리를 실행합니다.

기본 데이터 처리

RDBMS는 데이터를 정렬하거나 그루핑하는 등의 기본 데이터 가공 기능을 가지고 있습니다.

하지만 결과물이 동일하더라도 RDBMS별로 결과를 만들어내는 과정은 매우 다릅니다.

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

풀 테이블 스캔이란 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어 요청된 작업을 처리하는 작업입니다. MySQL은 아래와 같은 조건에서 풀 테이블 스캔을 사용합니다.

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

InnoDB 스토리지 엔진에서는 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드(Read ahead) 작업이 자동으로 시작됩니다.

리드 어헤드란 어떤 영역의 데이터가 앞으로 필요해질 것이라 예측해서 요청이 오기 전에 미리 디스크에서 읽어 버퍼 풀에 가져다 두는 것을 의미합니다.

즉, 풀 테이블 스캔이 실행되면 처음 몇 개의 데이터 페이지는 포그라운드 스레드가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘깁니다. 이후 한 번에 4개 또는 8개씩의 페이지를 읽으면서 계속 그 수를 증가시킵니다.(한 번에 64개의 데이터 페이지를 읽어 버퍼 풀에 저장) 포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면 돼서 쿼리가 상당히 빨리 처리됩니다.

풀 인덱스 스캔은 마찬가지로 인덱스의 처음부터 끝까지를 스캔하는 것입니다.

SELECT COUNT(*) FROM employees; 와 같은 쿼리가 있을 때, 조건이 없어 풀 테이블 스캔을 할 것 같지만, 풀 인덱스 스캔을 하게 될 가능성이 높습니다.

레코드의 건수만 요구하는 쿼리는 용량이 작은 인덱스를 선택하는 것이 디스크 읽기 횟수를 줄일 수 있기 때문입니다.

하지만 SELECT * FROM employees; 와 같이 칼럼이 필요한 경우는 풀 테이블 스캔을 합니다.

병렬 처리

병렬 처리란 하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리한다는 것입니다. MySQL 8.0부터 가능하게 됐는데, 이전에는 하나의 쿼리가 아닌 여러 스레드가 각각의 쿼리를 처리하는 것만 가능했습니다.

병렬 처리용 스레드 개수가 늘어날수록 쿼리 처리에 걸리는 시간이 줄어들 수 있지만, 서버에 장착된 CPU의 코어 개수를 넘어서는 경우에는 성능이 떨어질 수 있습니다.

ORDER BY 처리(Using filesort)

대부분의 SELECT 쿼리에서 정렬은 필수적인데, 정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리가 실행될 때 Filesort라는 별도의 처리를 이용하는 방법이 있습니다.

• 인덱스 이용
  • INSERT, UPDATE, DELETE 쿼리 실행 시 인덱스가 정렬되어 있어 순서대로 읽기만 하면 되기 때문에 매우 빠릅니다.
  • 부가적인 인덱스 추가/삭제 작업이 필요하므로 느려질 수 있습니다.
  • 디스크 공간과 InnoDB 버퍼 풀을 위한 메모리가 많이 필요합니다.
• Filesort
  • 정렬해야할 레코드가 많지 않을면 메모리에서 Filesort되어 충분히 빠릅니다.
  • 정렬 작업이 쿼리 실행 시 처리되어 레코드 대상 건수가 많아지면 응답 속도가 느립니다.

소트 버퍼

MySQL이 정렬을 수행하기 위해 할당 받는 메모리 공간을 소트 버퍼라고 합니다. 소트 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 변하고, 쿼리 실행이 완료 시 즉시 반납합니다.

정렬해야 할 레코드의 건수가 소트 버퍼에 할당된 크기보다 크다면, 소트 버퍼에서 정렬을 수행하고, 결과를 디스크에 저장 후, 다음 레코드를 가져와 정렬 후 디스크에 저장하는 과정을 반복합니다.

버퍼 크기만큼 정렬된 레코드를 다시 병합하며 정렬해야 하는데, 이 작업을 멀티 머지라고 합니다.

정렬 알고리즘

레코드를 정렬할 때 전체를 소트 버퍼에 담을지 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 싱글 패스와 투 패스 2가지 정렬 모드로 나눌 수 있습니다.

싱글 패스 정렬 방식

소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 정렬 방식입니다. 그리고 정렬이 완료되면 정렬 버퍼의 내용을 그대로 클라이언트로 넘겨줍니다.

투 패스 정렬 방식

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

정렬 처리 방법

쿼리에 ORDER BY가 사용되면 아래 3가지 방법 중 하나로 처리됩니다.

정렬 처리 방법 실행 계획의 Extra 칼럼 내용

인덱스를 사용한 정렬	별도 표기 없음
조인에서 드라이빙 테이블만 정렬	“Using filesort” 메시지가 표시됨
조인에서 조인 결과를 임시 테이블로 저장 후 정렬	“Using temporary; Using filesort” 메시지가 표시됨

옵티마이저는 정렬 처리를 위해 인덱스를 이용할 수 있을지 검토하고, 이용할 수 있다면 “Filesort” 과정 없이 인덱스를 순서대로 읽어서 결과를 반환합니다. 인덱스를 이용할 수 없다면 WHERE 조건에 일치하는 레코드를 검색해서 정렬 버퍼에 저장하면서 정렬을 처리할 것입니다. 이때 MySQL 옵티마이저는 정렬 대상 레코드를 최소화하기 위해 아래 2가지 방법 중 하나를 선택합니다.

• 조인의 드라이빙 테이블만 정렬한 다음 조인을 수행
• 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행

인덱스를 이용한 정렬

인덱스를 이용한 정렬을 위해서는 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 합니다. 또한 WHERE절에 첫 번째로 읽는 테이블의 칼럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 합니다.

그리고 B-Tree 계열의 인덱스가 아닌 해시나 전문 검색 인덱스 등에서는 인덱스를 이용한 정렬을 사용할 수 없습니다. (R-Tree도 사용할 수 없음)

실제 인덱스 값이 정렬되어 있어 인덱스의 순서대로 읽기만 하면 됩니다. ORDER BY가 있든 없든 같은 인덱스를 레인지 스캔해서 나온 결과는 같은 순서로 출력됩니다.

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

조인이 수행될 때 결과 레코드의 건수가 몇 배로 불어나고, 레코드의 크기도 늘어나기 떄문에 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 좋습니다. 이를 위해 첫 번째로 읽히는 테이블의 칼럼만으로 ORDER BY 절을 작성해야 합니다.

임시 테이블을 이용한 정렬

이 방법은 정렬해야 할 레코드가 많을 때 가장 느린 방식입니다. ORDER BY에 명시된 컬럼이 드리븐(Driven) 테이블에 속해 있다면, 조인된 데이터를 정렬할 수밖에 없습니다. 따라서 쿼리 성능 최적화를 위해 정렬이 필요한 경우에는 인덱스를 활용하는 것이 중요합니다.

---

성능 비교

쿼리에서 인덱스를 사용하지 못하는 정렬이나 그룹핑 작업은 크게 두 가지 방식으로 처리됩니다.

스트리밍 방식

스트리밍 방식은 서버가 처리해야 할 데이터의 양과 관계없이, 조건에 맞는 레코드를 검색하는 즉시 클라이언트로 전송하는 방식입니다. 이 방식에서는 클라이언트가 쿼리를 요청하면 즉시 첫 번째 레코드를 전달받을 수 있습니다.

스트리밍 방식이 가능하다면, MySQL 서버가 데이터를 찾는 즉시 클라이언트에 전달하기 때문에 동시에 데이터 가공 작업을 수행할 수 있습니다. 특히 웹 서비스 같은 OLTP(Online Transaction Processing) 환경에서는 쿼리를 실행하고 첫 번째 결과를 받아오기까지의 응답 시간이 중요합니다. 스트리밍 방식으로 처리된 쿼리는 조회해야 할 레코드 수에 상관없이 빠른 응답 시간을 보장할 수 있습니다.

또한, LIMIT 조건이 포함된 경우에는 전체 실행 시간을 더욱 단축할 수 있습니다.

버퍼링 방식

ORDER BY나 GROUP BY 같은 연산이 포함되면 쿼리 결과를 스트리밍 방식으로 처리할 수 없습니다. 이 경우, 먼저 WHERE 조건을 만족하는 모든 레코드를 검색한 후 정렬하거나 그룹핑한 다음 결과를 반환해야 합니다.

어떤 테이블을 드라이빙(Driving) 테이블로 선택하는지도 중요하지만, 정렬 방식이 어떻게 처리되는지는 성능에 더 큰 영향을 미칩니다. 가능하다면 인덱스를 활용하여 정렬을 수행하는 것이 좋으며, 인덱스를 사용할 수 없다면 최소한 드라이빙 테이블만 정렬하도록 유도하는 것이 성능 최적화에 도움이 됩니다.

---

GROUP BY 처리

GROUP BY 역시 스트리밍 방식으로 처리할 수 없는 연산 중 하나입니다. HAVING 절을 인덱스로 최적화하는 것은 불가능하므로, 쿼리 성능을 개선하려면 인덱스를 생성하거나 다른 최적화 방법을 고민해야 합니다.

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

조인의 드라이빙 테이블에 속한 컬럼만을 이용해 그룹핑할 때, GROUP BY 컬럼에 인덱스가 존재한다면 그 인덱스를 순차적으로 읽으면서 그룹핑 작업을 수행할 수 있습니다. 이 방식은 이미 정렬된 인덱스를 활용하므로 별도의 정렬 작업이나 내부 임시 테이블을 사용할 필요가 없습니다.

이러한 방식으로 실행되는 쿼리는 실행 계획의 Extra 컬럼에서 GROUP BY 관련 코멘트나 임시 테이블 사용 여부가 나타나지 않습니다.

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

루스 인덱스 스캔(Loose Index Scan)은 인덱스 레코드를 건너뛰면서 필요한 데이터만 조회하는 방식입니다. MySQL 옵티마이저가 이 방식을 사용할 경우, 실행 계획의 Extra 컬럼에 "Using index for group-by"라는 메시지가 표시됩니다.

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

GROUP BY 기준 컬럼이 드라이빙 테이블이나 드리븐 테이블에 존재하더라도, 인덱스를 전혀 사용하지 못하는 경우에는 내부적으로 임시 테이블을 생성하여 그룹핑을 수행합니다. 이 경우 성능이 크게 저하될 수 있습니다.

---

DISTINCT 처리

DISTINCT는 특정 컬럼의 유니크한 값만 조회하기 위해 사용됩니다. 하지만 DISTINCT가 포함된 쿼리는 GROUP BY와 동일한 방식으로 처리됩니다. 예를 들어, 아래 두 쿼리는 내부적으로 같은 연산을 수행합니다.

sql
복사편집
SELECT DISTINCT emp_no FROM salaries;
SELECT emp_no FROM salaries GROUP BY emp_no;

DISTINCT는 특정 컬럼만 유니크하게 조회하는 것이 아니라, 결과 레코드 전체를 유니크하게 조회한다는 점을 유의해야 합니다.

집합 함수와 함께 사용된 DISTINCT

집합 함수와 함께 DISTINCT가 사용되면 다른 방식으로 해석됩니다. 집합 함수 내에서 사용된 DISTINCT는 해당 함수의 인자로 전달된 컬럼 값 중 유니크한 값만을 대상으로 연산을 수행합니다.

이 경우 내부적으로 임시 테이블이 사용되지만, 실행 계획에서는 명시적으로 표시되지 않습니다. 실제로 두 테이블을 조인한 결과에서 특정 컬럼 값만 저장해야 하므로 레코드 수가 많아질수록 성능이 저하될 가능성이 큽니다.

---

내부 임시 테이블 활용

MySQL은 레코드를 정렬하거나 그룹핑할 때 내부적으로 임시 테이블을 사용합니다. 기본적으로 임시 테이블은 메모리에 생성되지만, 테이블 크기가 일정 수준을 초과하면 디스크로 이동하여 저장됩니다.

임시 테이블이 필요한 쿼리

다음과 같은 경우 내부적으로 임시 테이블이 생성됩니다.

• 유니크 인덱스를 가지는 내부 임시 테이블이 만들어지는 경우
  • ORDER BY와 GROUP BY에 명시된 컬럼이 서로 다른 경우
  • ORDER BY 또는 GROUP BY 컬럼이 조인의 첫 번째 테이블에 속하지 않는 경우
  • DISTINCT와 ORDER BY가 동시에 포함된 쿼리
  • DISTINCT가 인덱스를 활용하지 못하는 경우
  • UNION 또는 UNION DISTINCT가 사용된 경우 (select_type 컬럼이 UNION RESULT인 경우)
• 유니크 인덱스가 없는 내부 임시 테이블이 만들어지는 경우
  • 실행 계획에서 select_type이 DERIVED인 서브쿼리가 포함된 경우

일반적으로, 유니크 인덱스가 있는 내부 임시 테이블이 존재하는 경우보다 유니크 인덱스가 없는 경우의 성능이 더 저하될 수 있습니다.

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

내부 임시 테이블은 기본적으로 메모리에서 생성되지만, 다음과 같은 조건을 만족하면 디스크 기반 임시 테이블로 변경됩니다.

• UNION 또는 UNION ALL에서 SELECT 되는 컬럼 중 하나 이상이 512바이트 이상인 경우
• GROUP BY 또는 DISTINCT에서 사용된 컬럼 중 하나 이상이 512바이트 이상인 경우
• 임시 테이블의 크기가 tmp_table_size, max_heap_table_size, 또는 temptable_max_ram 시스템 변수 값보다 큰 경우

디스크 기반 임시 테이블이 사용되면 쿼리 성능이 크게 저하될 수 있으므로, 가급적 메모리 내에서 처리할 수 있도록 시스템 변수 설정을 조정하는 것이 좋습니다.

9.3 고급 최적화

서버의 옵티마이저는 통계 정보와 옵티마이저 옵션을 결합하여 최적의 실행 계획을 수립했습니다. 이러한 옵션은 크게 조인 관련 옵션과 스위치로 구분할 수 있습니다.

옵티마이저 스위치 옵션

• 스위치 옵션은 optimizer_switch 시스템 변수를 통해 제어했습니다. 이는 글로벌과 세션별 모두 설정할 수 있는 변수이므로 서버 전체 및 커넥션에 대해 설정이 가능했습니다.

MRR과 배치 키 액세스

• Multi-Range Read는 조인 시 드라이빙 테이블에서 하나의 레코드를 읽어 드리븐 테이블의 일치하는 레코드를 찾는 방식입니다. 이를 네스티드 루프 조인이라고 불렀습니다.
• MySQL 엔진이 내부적으로 조인을 처리했지만, 레코드를 검색하고 읽는 것은 스토리지 엔진이 담당했습니다.
• MySQL 서버는 이를 최적화하기 위해 조인 대상 테이블 중 하나로부터 레코드를 읽어 조인 버퍼에 버퍼링했습니다.
• 드라이빙 테이블의 레코드를 읽어 조인을 즉시 실행하지 않고 대상을 버퍼링함으로써, 스토리지 엔진은 데이터 페이지에 정렬된 순서로 접근하여 디스크 읽기를 최소화할 수 있었습니다.
• 이러한 방식을 MRR이라 했으며, 실행 중인 조인 방식을 BKA(Batched Key Access) 조인이라고 불렀습니다.

블록 네스티드 루프 조인

• 서버의 대부분 조인은 네스티드 루프 조인이었으며, 이는 연결 조건이 되는 칼럼에 모두 인덱스가 있는 경우 사용되는 조인 방식이었습니다.
• 네스티드 루프 조인과의 차이점은 조인 버퍼의 사용 여부와 조인에서 드라이빙 테이블과 드리븐 테이블의 조인 순서였습니다.

실행 과정은 다음과 같았습니다

1. dept_emp 테이블의 ix_fromdate 인덱스를 이용해 레코드를 검색했습니다.
2. 조인에 필요한 나머지 칼럼을 모두 dept_emp 테이블로부터 읽어서 조인 버퍼에 저장했습니다.
3. employees 테이블의 프라이머리 키를 이용해 조건에 만족하는 레코드를 검색했습니다.
4. 검색된 결과에 캐시된 조인 버퍼의 레코드를 결합하여 반환했습니다.

• 주목할 점은 조인 버퍼가 사용되는 쿼리에서는 조인 순서가 거꾸로인 것처럼 실행되었다는 것입니다.
• employee 테이블을 기준으로 병합했다는 의미였으며, 이로 인해 결과의 정렬 순서가 흐트러질 수 있었습니다.

인덱스 컨디션 푸시다운

인덱스 컨디션 푸시다운은 쿼리의 처리 성능을 크게 향상시킬 수 있는 중요한 기능입니다.

• last_name='Action' 조건으로 인덱스 레인지 스캔 후 레코드를 찾고, first_name LIKE '%sal' 조건을 파악하는 과정에서, 첫 번째 조건에 부합하는 레코드가 3건이더라도 10만 건 중 1건만 사용한다면 불필요한 작업이 많이 발생할 수 있었습니다.
• 이러한 문제를 해결하기 위해 MySQL 5.6 버전부터는 인덱스를 범위 제한 조건으로 사용하지 못하더라도, 인덱스에 포함된 칼럼의 조건이 있다면 모두 모아서 스토리지 엔진으로 전달할 수 있도록 핸들러 API가 개선되었습니다.
  • 이는 인덱스를 이용해 최대한 필터링을 완료하여 1건의 레코드만 테이블 읽기를 수행할 수 있게 되었다는 의미입니다.
• 인덱스 비교 작업은 실제 InnoDB 스토리지 엔진이 수행하지만, 테이블에서의 비교 작업은 MySQL 엔진이 수행하는 작업이었습니다.
  • 이러한 개선은 고도의 기술력을 요하는 기능은 아니지만, 쿼리의 성능을 수십 배로 향상시킬 수 있는 매우 중요한 기능이 되었습니다.

인덱스 확장

• use_index_extensions 옵션은 InnoDB 스토리지 엔진을 사용하는 테이블에서 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지를 결정하는 옵션입니다.
• 이는 보조 인덱스의 효율성을 높이고 쿼리 성능을 개선하는 데 도움을 주었습니다.

인덱스 머지

• 일반적으로 옵티마이저는 테이블별로 하나의 인덱스만 사용하도록 실행 계획을 수립했습니다.
• 하지만 인덱스 머지 옵션을 통해 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리할 수 있었습니다.
  • 이는 각각의 조건이 서로 다른 인덱스를 활용할 수 있고, 그 조건을 만족하는 레코드가 많을 것으로 예상될 때 선택되었습니다.

인덱스 머지 - 교집합

• 두 개의 칼럼에 각각의 인덱스가 존재할 때 인덱스 머지 최적화 기법을 사용할 수 있었습니다.
  • 예를 들어, A를 만족하는 레코드가 253건, B를 만족하는 레코드가 10,000건이며, A,B를 모두 만족하는 레코드가 14건인 경우, 옵티마이저는 각 인덱스를 검색해 두 결과의 교집합만을 찾아 반환함으로써 효율적인 처리가 가능했습니다.

인덱스 머지 - 합집합

• 인덱스 머지 합집합은 2개 이상의 조건이 각각의 인덱스를 사용하되 OR 연산자로 연결된 경우에 사용하는 최적화 방식이었습니다.
  • 이는 각각의 인덱스를 통해 검색된 결과를 하나로 합쳐 최종 결과를 만들어내는 방식으로 동작했습니다.

인덱스 머지 - 정렬 후 합집합

• Union 알고리즘은 두 결과 집합의 중복을 제거하기 위해 정렬된 결과가 필요한 경우에 사용되었습니다.
  • 이는 여러 인덱스를 사용한 검색 결과에서 중복된 레코드를 효과적으로 제거하면서도 정렬된 상태를 유지할 수 있게 해주는 중요한 최적화 방식이었습니다.

세미 조인

• 세미 조인은 다른 테이블과 실제 조인을 수행하지는 않고, 단지 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리였습니다.
  • 이는 효율적인 쿼리 처리를 위한 중요한 최적화 방식이었습니다.

테이블 풀-아웃

• 테이블 풀-아웃은 세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태의 최적화였습니다.
  • 이는 서브쿼리 최적화가 도입되기 전부터 DBA들이 수동으로 쿼리를 튜닝하던 대표적인 방법이었습니다.

퍼스트 매치

• First Match 최적화 전략은 IN 형태의 세미 조인을 EXISTS 형태로 튜닝하는 것과 비슷한 방법으로 실행되었습니다.
  • 이 방식은 조건을 만족하는 첫 번째 레코드만을 찾아 처리함으로써 쿼리의 효율성을 높일 수 있었습니다.

루스 스캔

• 루스 스캔은 GROUP BY 최적화 방법에서의 루스 인덱스 스캔과 비슷한 읽기 방식이었습니다.
  • 예를 들어, 루스 인덱스 스캔을 통해 유니크한 dept_no만 읽어 부서 테이블을 조회한다면 효과적으로 쿼리를 실행할 수 있었습니다. 이는 불필요한 데이터 읽기를 최소화하여 성능을 향상시켰습니다.

구체화

• 구체화는 세미 조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화하는 방식이었습니다. 여기서 구체화란 내부 임시 테이블을 생성한다는 것을 의미했습니다.
  • 이 방식은 복잡한 서브쿼리를 효율적으로 처리할 수 있게 해주었습니다.

중복 제거

• 중복 제거는 세미 조인 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꿔 실행하고 마지막에 중복된 레코드를 제거하는 방법이었습니다.
  • 이 방식은 때로는 단순하지만 효과적인 최적화 방법이 될 수 있었습니다.

컨디션 팬아웃

• 컨디션 팬아웃에서는 조인을 실행할 때 테이블의 순서가 쿼리의 성능에 매우 큰 영향을 미쳤습니다.
  • 이러한 이유로 옵티마이저는 여러 테이블이 조인되는 경우 가능한 일치하는 레코드 건수가 적은 순서대로 조인을 실행했습니다. 이는 전체적인 쿼리 실행 시간을 최소화하는데 큰 도움이 되었습니다.

파생 테이블 머지

• 파생 테이블 머지는 서버 내부적으로 임시 테이블이 만들어질 때 발생할 수 있는 성능 저하를 방지하기 위한 최적화 방식이었습니다.
• 레코드 건수가 많아지면 임시 테이블이 디스크에 저장되므로, 이러한 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합해서 서브쿼리 부분을 제거하는 최적화가 도입되었습니다.

인비저블 인덱스

• 인비저블 인덱스는 인덱스의 가용 상태를 제어할 수 있는 기능이었습니다. 이를 통해 인덱스의 효율적인 관리와 테스트가 가능해졌습니다.

스킵 스캔

• 인덱스의 핵심은 정렬이었으며, 이로 인해 인덱스 구성 칼럼의 순서가 매우 중요했습니다.
  • (A, B, C) 칼럼으로 구성된 인덱스는 B, C로 구성된 조건에 활용되기 어려웠지만, 인덱스 스킵 스캔은 제한적이나마 이러한 제약 사항을 뛰어넘을 수 있는 최적화 기법이었습니다.
• 인덱스의 선행 칼럼이 조건절에 사용되지 않더라도 후행 칼럼만으로도 인덱스를 이용한 쿼리 성능 개선이 가능했습니다.
• 옵티마이저는 테이블에 존재하는 모든 선행 칼럼 값을 가져와 조건이 있는 것처럼 최적화했습니다.
• 다만 선행 칼럼이 매우 다양한 값을 가지면 비효율적일 수 있었지만, 소수의 유니크한 값이라면 충분히 활용할 수 있었습니다.

해시 조인

해시 조인은 빌드 단계와 프로브 단계로 나뉘어 처리되었습니다.

• 빌드 단계에서는 조인 대상 테이블 중 레코드 건수가 적어 해시 테이블로 만들기 용이한 테이블을 골라 메모리에 해시 테이블을 생성했습니다.
  • 이때 사용되는 원본 테이블을 빌드 테이블이라고 불렀습니다. 프로브 단계에서는 나머지 테이블의 레코드를 읽어 해시 테이블의 일치 레코드를 찾는 과정을 수행했습니다.
• 해시 조인은 높은 성능을 보였지만, 첫 번째 레코드를 찾는 데에는 상대적으로 긴 시간이 걸렸습니다.
• 따라서 해시 조인 쿼리는 최고 스루풋 전략에 적합했으며, 네스티드 루프 조인은 최고 응답 속도 전략에 적합했습니다.

인덱스 정렬 선호

• 옵티마이저는 ORDER BY 혹은 GROUP BY를 인덱스를 사용해 처리 가능한 경우, 쿼리 실행 계획에서 인덱스의 가중치를 높이 설정해 실행되었습니다.
• 그러나 두 개의 인덱스 중 가끔 잘못된 실행 계획으로 비효율적인 인덱스가 선택될 수 있었습니다.
• 이전에는 이런 경우를 방지하기 위해 특정 인덱스를 사용하지 못하도록 힌트를 사용했습니다.
• 하지만 이후에는 인덱스에서 ORDER BY를 위한 인덱스에 너무 많은 가중치를 부여하지 않도록 하는 옵션이 추가되어 더욱 효율적인 쿼리 최적화가 가능해졌습니다.